2010-07-21

Restoring 10g database from tape to different location


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.


  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
echo $ORACLE_SID
echo $ORACLE_SID_SOURCE
echo $TDPO_OPTFILE

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

  1. Delete old data files, redo logs and clean admin directories
  2. 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

  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)';
}
EOF
  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';
}
EOF

rman target / <<EOF
alter database mount;
EOF

  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
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

  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;
}
EOF

  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"
@create_control_file.sql
alter database open resetlogs;
exit;

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

2010-07-07

Control files deleted

Environment
Oracle 10.2.0.4, Solaris 10

Problem
Oracle control files were accidentally deleted when database was open.
No backups were available. Database is in noarchivelog mode.

Solution
Luckily redo logs were not overwritten since database startup so media recovery is possible.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
Prepare SQL statement to create control file with NORESETLOGS option, e.g.
CREATE CONTROLFILE REUSE DATABASE "DHMERT01" NORESETLOGS  NOARCHIVELOG ...

SQL> @create_controlfile.sql
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/01/DHMERT01/system01.dbf'

SQL> alter database recover database;
Database altered.

SQL> alter database open;
Database altered.