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 |
sh

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 ”’||tf.name||”’ drop;’
from v$tempfile tf
where tf.name like ‘%LSC67%’;
spool off
spool /tmp/create_temp_LSC67_to_LSC66.sql
select ‘alter tablespace “‘||ts.name||'” add tempfile ”’||
replace(tf.name,’LSC67′,’LSC66′)||
”’ size 128M reuse autoextend on next 128M maxsize 2048M;’
from v$tablespace ts , v$tempfile tf
where tf.name 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
@/tmp/rename_LSC67_to_LSC66.sql
@/tmp/drop_temp_LSC67_to_LSC66.sql
alter database open;
@/tmp/create_temp_LSC67_to_LSC66.sql

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;