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;


I just remembered the woman who asked Lawrence Ellison why you need at least three days to get a qualified support representative when you open an iTar.

Since a few days, I have been wondering why a non-dba user was not able to do sqlplus in 10gR2. Well, I saw the bug 4516865 on metalink for and, but did not agree that it is a “desirable improvement”, imho it is a major lost of service.

Just a few minutes (seconds?) after I finished writing my iTar, severity 2, I received a call from Oracle Support.

Apparently a group of customers asked to prevent any non-dba user from using sqlplus on a database server (just on the client). This kind of answer I do not like. But we had a long talk, he finally showed me a workaround for : doing

chmod -R 755 $ORACLE_HOME


Well, this should have made me hurl. But ok, I can do this.

He also explained me, that I should escalate this bug if I want development starting working on it. Also there is another bug, 4533592

My satisfaction with this tar is good (4/5). I have been contacted immediatly, informed about the bugs, and informed about the procedure to escalate this case when I will require too.

My overall satisfaction with Metalink is about 3/5. But sometimes, it is really 0/5. The worst I have was :

lsc: how do you remove headers in the middle of your result in sqlplus ? I have a bug with set emb on and set pages 9999.
metalink: This is a bug in sqlplus. Workaround is to use Oracle Reports.

I could just have kill her! The “good” solution is “set pages 0”, ref: set pages 50000

One also like this :

lsc: When I try to do an executable in scheduler I got an ora-27371 (AIX)
metalink: switch to a new platfom
lsc: @*#!!
metalink: It will be fixed in
lsc: no it is not fixed in
metalink: it is an internal bug
…months later…
lsc: still not fix in
metalink: development is working on it

well, I have always known that AIX is not a strategic platform and many shell scripts function utterly wrong… still very frustrating one!

Larry just told that woman : just drop me an email. Should I write Larry each time I do receive an idiot answer?