How to restore DB from tape if DBID has been changed

You are requested to restore a database (into another one) from RMAN backup on tape which was done 2 month ago. You requested tape from your storage company, you have your backup copied over to your server - if that is your preference. And all of a sudden you realize that DBID has been changed AFTER you took your backup - say your database has been refreshed from production and this is a part of the process.

No problem! Just do that:

Eestore the controlfile, then the backup as follows:
RMAN> startup nomount force;
RMAN> restore controlfile from '/db/backup/MYDB/25jul09/c-3431719427-20090723-00';
RMAN> alter database mount;
This should mount the controlfile with the old DBID

Now you may proceed with the database restore - catalog you backup pieces, run restore & recover, apply archive logs (from the same tape) if necessary...

Script to restart database if down

#!/usr/bin/sh
# By GregZ 10/13/2009 to check DB status
##set -v
export ORACLE_HOME=/oracle/app/oracle/product/10.2.0
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=CRBURN
greg=gregz@gregzllc.com
#*************************************************************
# Test to see if Oracle is up...
#*************************************************************
$ORACLE_HOME/bin/sqlplus -s /< /tmp/check_$ORACLE_SID.ora
select * from v\$database;
quit
EOF
#*************************************************************
# If not, restart and e-mail...
#*************************************************************
check_stat=`cat /tmp/check_$ORACLE_SID.ora I grep -i error Iwc -l`;
oracle_num=`expr $check_stat`
echo $oracle_num
if [ $oracle_num -ne 0 ]
then
echo "$ORACLE_SID is down!" I mailx -s "$ORACLE_SID is down!" $greg
sqlplus "/ as sysdba" < /tmp/check_$ORACLE_SID.ora
startup
select * from v\$database;
quit
EOF1
check_stat=`cat /tmp/check_$ORACLE_SID.ora I grep -i error I wc -l`;
oracle_num=`expr $check_stat`
###echo $oracle_num
if [ $oracle_num -eq 0 ]
then
echo "$ORACLE_SID is brought up" I mailx -s "$ORACLE_SID is back up" $greg
fi
##else
## echo "$ORACLE_SID is up" I mailx -s "$ORACLE_SID is up" $greg
fi
exit 0

Restore database from old RMAN backup

1. As usual, you use catalog command to recatalog your backup pieces.
catalog backuppiece '/db/backup/MYDB/old_backup/your_piece_1_1';
2. Startup nomount and restore controlfile.
RMAN>restore controlfile;
3. Alter database mount and restore database
RMAN>alter database mount;
RMAN>restore database;
4. Try recover database;
It will give you the required archivelogs but you don't need them. You are just trying to restore from the old backup as it was. So check you max last archivelog in thi sbackup:
select max(sequence#) from v$log_history;
1425
Now do this:
Recover database until logseq=1426;
5. You are done.
Run:
Alter database open
6. And don't forget to create you tempfiles. RMAN backup does not have them!

ORA-01652:unable to extend temp segment

Don't rush to add additional tempfile or increase the size of existing ones if you are getting this.

If you are happy with your tempfile sizes (I usually allocate 10 to 20G to them), try increasing pga_aggregate_target. If this is not enouph, retrieve the sql from either v$sql or v$sqlarea (or better yet from the Grid Control) & see if execution plan is good. If not, tune it, otherwise recreate statistics on the tables involved. If you'd use cascade=true parameter in dbms_stats.gather_table_stats, indexes will be analyzed as well.

ORA-00997: Illegal use of datatype

One day you've decided to do a simple backup for your table:

create tab1_bkp as select * from tab1;

And ops - you are getting this:

ORA-00997: Illegal use of datatype.

The reason for it is that one or more columns in your table have LONG data type which could only be used for select.

The same error you will get when you try to move your table to a different tablespace.

There are a couple of ways to get around that. One is to do the export and import. This will work if you need to bring the data from this table into a different database (because you would not be able to use a database link or a COPY command due to the same reason).

The other one is to create your backup table by converting this LONG column to CLOB:

create table tab1_bkp as select a, b, c,...,to_lob(k), l,m from tab1;

RMAN restore from un-cataloged backup

What if you need to create a duplicate database from RMAN backup on tape but because of your redundancy policy your backup is gone from RMAN catalog?
The steps below will guide you on how to do it. Same steps are applicable to a plain restore, with running restore controlfile from autobackup & restore database commands rather than a duplicate script.

Just use catalog command.
Let's assume you are doing backupsets (the approach will be the same if your are not). Copy (or ask yor sysadmin to copy) all the contents of your backup directory from tape into some location on the server, say /db/backup/DB1/. Also, copy init.ora file into $ORACLE_HOME/dbs/ and entire archive log directory into /db/archives/DB1. You will need to specify fully qualified backup piece name in the catalog command.

1.Do export ORACLE_SID=DB1
2. Connect to recovery catalog and use catalog command on every backup piece & if you want (but not necessary) on every archive log.
3. Run you duplicate script (see example in my blog).
In the script you will need to use "set until". You could do something like that:
set until time "to_date('10-01-2008' 09:05:00','mm-dd-yyyy hh24:mi:ss')";
4. If the script is missing some archive logs, you still can go into sqlplus and do:
recover database until cancel using backup controlfile;
and use cancel with no ";".
5. Alter database open resetlogs
6. Voila!

One of the reasons your script would fail is that: if you do controlfile autobackup by default it goes into $ORACLE_HOME/dbs (unless you specified differently). Catalog this piece as well. On my system it is name something like "c-167655678-85745849843-00. RMAN will tell you if the one you are trying to catalog is the wrong one.

Unique Constraint Violation

Sometimes you have to run an insert-select statement which produces ORA-00001 "Unique Constraint Violation". Of course, that's because of an attemp to insert a duplicate key.
What to do if your select statement produces 300 rows out of which only 100 are duplicate keys and the rest you still need to insert?

One way to do that is to create a staging table & use MERGE statement to do the job. Lets say you have a table tab1 with a unique index on col1 and col2.

1. Create table tab1_bkp as select * from tab1;
2. Truncate table tab1;
3. Use your insert-select statement on tab1_bkp.
4. Merge into tab1 a using tab1_bkp b
on (a.col1=b.col1 and
a.col2=b.col2)
when not matched then
insert (col1,col2,....coln)
values (b.col1, b.col2,...b.coln);.
5. Drop tab1_bkp;

You are done