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;

metalink

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 9.2.0.7 and 10.2.0.1, 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 10.2.0.1 : doing

chmod -R 755 $ORACLE_HOME

before root.sh.

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 10.1.0.4
lsc: no it is not fixed in 10.1.0.4
metalink: it is an internal bug
…months later…
lsc: still not fix in 10.2.0.1
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?