Environment
Oracle 10.2.0.4, UNIX AIX 5.3.0.0, IBM Tivoli Storage Manager (TSM)
Purpose
Restore database to different location.
Create test database from tape backup of production database.
RMAN DUPLICATE can not be used as connection to source (target) database is not available.
Destination database already exists.
Solution
All operations are done on destination host, assuming that source host and database are not available.
- TSM specific: If don’t have proper tdpo.opt file, then create one. Make sure you have correct (source) host name in this file.
- Set environment variables.
export ORACLE_SID=destdb
export ORACLE_SID_SOURCE=sourcedb
export TDPO_OPTFILE=/oracle/RMAN/etc/tdpo.opt
echo $ORACLE_SID
echo $ORACLE_SID_SOURCE
echo $TDPO_OPTFILE
- If spfile is used, create pfile.
- Shut down database and listener
lsnrctl stop listener_10g_$ORACLE_SID
sqlplus "/ as sysdba"
shutdown abort;
exit;
- Delete old data files, redo logs and clean admin directories
- Create temporary pfile, as db_name must be source database name.
cd $ORACLE_HOME/dbs
sed -e "s/db_name='$ORACLE_SID'/db_name='$ORACLE_SID_SOURCE'/1" init$ORACLE_SID.ora > init$ORACLE_SID.duplicate.ora
diff init$ORACLE_SID.ora init$ORACLE_SID.duplicate.ora
- Start oracle instance and test that RMAN can connect to TSM.
rman target / <<EOF
startup nomount pfile=init$ORACLE_SID.duplicate.ora
run {
allocate channel c1 device type sbt_tape parms='ENV=(TDPO_OPTFILE=$TDPO_OPTFILE)';
}
EOF
- Get appropriate name of control file backup, update following script, and restore control file
rman target / <<EOF
run {
allocate channel c1 device type sbt_tape parms='ENV=(TDPO_OPTFILE=$TDPO_OPTFILE)';
restore controlfile from 'c-1811786000-20100601-10-sourcedb';
}
EOF
rman target / <<EOF
alter database mount;
EOF
- Prepare SQL script create_rman_duplicate_script.sql to generate RMAN script. In this example TEMP files are dropped. You can keep them and/or rename them similar to redo logs.
Set any directory for your scripts:
cd /oracle/admin/$ORACLE_SID/create
cat >create_rman_duplicate_script.sql <<EOF
set heading off
set timing off
set flush off
set feedback off
set echo off
SET PAGESIZE 0
spool rman_duplicate.rcv
prompt run {;
prompt ALLOCATE CHANNEL ch1 device type sbt_tape parms='ENV=(TDPO_OPTFILE=$TDPO_OPTFILE)';;
prompt ALLOCATE CHANNEL ch2 device type sbt_tape parms='ENV=(TDPO_OPTFILE=$TDPO_OPTFILE)';;
select 'SET NEWNAME FOR DATAFILE '||FILE#||' TO '''||replace(name,'$ORACLE_SID_SOURCE','$ORACLE_SID')||''';' from V\$DBFILE order by FILE#;
select 'SET NEWNAME FOR TEMPFILE '||FILE#||' TO '''||replace(name,'$ORACLE_SID_SOURCE','$ORACLE_SID')||''';' from V\$TEMPFILE order by FILE#;
prompt restore database;;
prompt SWITCH DATAFILE ALL;;
prompt };
select 'sql "alter database rename FILE '''''|| MEMBER ||''''' TO '''''||replace(MEMBER,'$ORACLE_SID_SOURCE','$ORACLE_SID')||'''''";' from v\$logfile;
select 'sql "alter database tempfile '||FILE#||' drop";' from v\$TEMPFILE;
prompt exit;
spool off
exit
EOF
cat create_rman_duplicate_script.sql
- prepare RMAN script
sqlplus "/ as sysdba" @create_rman_duplicate_script.sql
cat rman_duplicate.rcv
- restore database
rman target / @rman_duplicate.rcv
- Recover database if you restored it from hot backup, otherwise go to next step
rman target / <<EOF
sql "alter system set LOG_ARCHIVE_DEST_1=''LOCATION=/oracle/export''";
run {
allocate channel c1 device type sbt_tape parms='ENV=(TDPO_OPTFILE=$TDPO_OPTFILE)';
recover database;
}
EOF
- open database
sqlplus "/ as sysdba"
alter database open resetlogs;
alter database backup controlfile to trace;
shutdown immediate;
- To rename database, prepare script to create controlfile. Pay attention to “REUSE SET DATABASE”, SID and NOARCHIVELOG. Pick up script from udump directory. Instance will start with default spfile or pfile. Parameter db_name must be set to new database name.
sqlplus "/ as sysdba"
@create_control_file.sql
alter database open resetlogs;
exit;
- Add files to TEMP tablespace.
- Startup listener
