Restoring 10g database from tape to different location

Oracle, UNIX AIX, IBM Tivoli Storage Manager (TSM)
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.

All operations are done on destination host, assuming that source host and database are not available.

  1. TSM specific: If don’t have proper tdpo.opt file, then create one. Make sure you have correct (source) host name in this file.
  2. Set environment variables.
export ORACLE_SID=destdb
export ORACLE_SID_SOURCE=sourcedb
export TDPO_OPTFILE=/oracle/RMAN/etc/tdpo.opt

  1. If spfile is used, create pfile.
  2. Shut down database and listener
lsnrctl stop listener_10g_$ORACLE_SID
sqlplus "/ as sysdba"
shutdown abort;

  1. Delete old data files, redo logs and clean admin directories
  2. Create temporary pfile, as db_name must be source database name.
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

  1. 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)';
  1. 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';

rman target / <<EOF
alter database mount;

  1. 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
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 };
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
cat create_rman_duplicate_script.sql

  1. prepare RMAN script
sqlplus "/ as sysdba" @create_rman_duplicate_script.sql
cat rman_duplicate.rcv

  1. restore database
rman target / @rman_duplicate.rcv

  1. 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;

  1. open database
sqlplus "/ as sysdba"
alter database open resetlogs;
alter database backup controlfile to trace;
shutdown immediate;

  1. 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"
alter database open resetlogs;

  1. Add files to TEMP tablespace.
  2. Startup listener

No comments:

Post a Comment