restore to a new host : nid++

Great challenge today: restore to a new host from a closed noarchivelog backup on tape library.

In oracle 8i and before, the only way to rename a database was to recreate the controlfile. In 9i, I could change it with nid, in 10gR2, I should never have a reason again to recreate the controlfile, because even the MAXDATAFILES and MAXINSTANCES could be changed. Change controlfile is bad. Once a collegue forget one file, once he noticed it, only months later, he wondered how to recover it. Create a controlfile is way to much sensible thing too do. It is almost as bad as changing the dictionary!

Well. How to do than?

Ok, I have a database called LSC67 on prod, I want to recover it to LSC66 in devl. I have NO ACCESS to production, but I have access to tapes (one may wonder if this is a good security practice…)

Let’s start.

First, if LSC66 already exists, shutdown abort. Remove datafiles. Remove controlfiles. Remove redo logs.

Now I restore the data/control/redo files from prod:/dbms/oracle/LSC67 to devl in /dbms/oracle/LSC66.

First I rename the files, some are called systemLSC67.dbf. I do not want that…

find do the trick

find /dbms/oracle/LSC66 -name “*LSC67*” |
nawk ‘{printf “mv “$1” “; gsub(src,target);print}’ src=LSC67 target=LSC66 |

I must just change the DB_NAME in my parameter file (which already exists at my site), to reflect the prod controlfile dbname

startup quiet force nomount
alter system set db_name=’LSC66′ scope=spfile;
startup quiet force mount

now I generate some statements for dynamically renaming the files

set ver off emb on pages 0 newp 0 lin 9999 trims on head off feedb off termout off
spool /tmp/rename_LSC67_to_LSC66.sql
select ‘alter database rename file ”’||name||”’ to ”’||replace(name,’LSC67′,’LSC66′)||”’;’
from (
select name from v$datafile
union all
select member from v$logfile
where name like ‘%LSC67%’;
spool off
spool /tmp/drop_temp_LSC67_to_LSC66.sql
select ‘alter database tempfile ”’||||”’ drop;’
from v$tempfile tf
where like ‘%LSC67%’;
spool off
spool /tmp/create_temp_LSC67_to_LSC66.sql
select ‘alter tablespace “‘||||'” add tempfile ”’||
”’ size 128M reuse autoextend on next 128M maxsize 2048M;’
from v$tablespace ts , v$tempfile tf
where like ‘%LSC67%’ and tf.ts#=ts.ts#;
spool off

ok, now I am in mount, I do a rename datafile and drop tempfile. after I open database, and add tempfile. I am not taking care of the original size and autoextend clause of the original tempfiles, just 128M next 128M max 2G.

set echo on termout on feedb 6
alter database open;

now I nid

shutdown immediate
startup quiet mount restrict

nid dbname=LSC66 target=/

and I change the db name and open resetlogs

startup quiet force nomount
alter system set db_name=’LSC66′ scope=spfile;
startup quiet force mount
alter database open resetlogs;

7 thoughts on “restore to a new host : nid++

  1. Michael Dinh

    That is really slick.

    This is one reason why I like Oracle Managed File (OMF).

    OMF for creating redo logs is awesome. The logs can be multiplexed by setting db_create_online_log_dest_n.

    Don’t have to worry about where to put things.

  2. Laurent Schneider

    well, even if you use omf, if you restore your productive data to a new structure, in my example /dbms/oracle/LSC66, than you must move your files manually, because the new location of the files is not registred in the controlfile. Ok?

  3. Laurent Schneider

    I just optimized my procedure :
    1) do not restore the redo logs, because it costs time and it is unnecessary
    2) rename the redologs after nid but before open resetlogs
    3) add temp files to temporary tablespace at the very end

  4. Anonymous


    I just need to change the SID not Database name.
    Database name is “testDb” and the SID is “tDBSID”.

    Do you think I have to use NID, cause everybody keep telling me to use NID to change SID.

    Could you please guide me a little bit? I’m new in the Oracle’s wrold.

    I use 10gR2 on Fedora Core 4.

    Thanks in Advance,

Comments are closed.