Oracle DBA Tips and Techniques

By Arjun Raja

10g RMAN restore on another machine

Posted by Gavin Soorma on July 7, 2009

TASK: Restoring a tape backup onto another machine and bringing up the database.

ORIGINAL DATABASE: RPSP ON ITLINUX25 AND TARGET IS RPSP ON ITLINUX26.

PRE-REQUISITES:

Make sure that the tdpo.opt file uses the same TDP library pointer on the new machine as the original machine – only then will RMAN restore the backup from the correct library.

In ITLINUX26 ( NEW MACHINE)

rpsp:/opt/tivoli/tsm/client/oracle/bin>ls –lrt
total 1364
tdpo.opt.smp*
tdpoconf*
libobk.so*
agent.lic*
tdposync*
dsm.opt
tdpo.rpspd.opt*….should point to TDP on ITLINUX25.

1. BACKUP DATABASE PLUS ARCHIVE LOGS….ON ITLINUX25…DATABASE RPSP.

Backup script will look like this…

run {
allocate channel ch1 type ‘sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
Backup database plus archivelog;
release channel ch1 ;
}

Get dbid name of rpsp….select dbid from v$database;

565953070

2. Now in ITLINUX26 machine…ALL RESTORATION AND RECOVERY COMMANDS MUST BE RUN ONLY IN ITLINUX26.

A:

SCENARIO ONE: ORIGINAL MACHINE IS AVAILABLE…

Copy setrpsp, initrpsp.ora to ITLINUX26 machine in appropriate areas and make required changes especially the new path of the controlfiles….

Create spfile from pfile….

Export ORACLE_HOME
EXPORT ORACLE_SID

sql>startup nomount;

After db is in nomount stage…exit

Then move directly to the RESTORE CONTROLFILE part…ignore next section…

B:

SCENARIO TWO: ORIGINAL MACHINE COMPLETELY LOST AND NO SPFILE AVAILABLE.

In case you do not have an spfile and the database has to restored and recovered….

Firstly startup nomount force the database….it will force an instance start without using an spfile….

This command must be run in an RMAN block.

vi nospfile.rcv

set dbid= 565953070;
run {
startup nomount force ;
}

Save file…

From command line..

rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=nospfile.rcv

rpsp:/opt/oracle/scripts>rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=nospfile.rcv

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Nov 22 13:37:31 2005

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database (not started)
connected to recovery catalog database

RMAN> set dbid= 565953070;
2> run {
3> startup nomount force ;
4> }
5>
6>
executing command: SET DBID
database name is “RPSP” and DBID is 565953070

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/opt/oracle/product10g/dbs/initrpsp.ora’

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 1218244 bytes
Variable Size 58722620 bytes
Database Buffers 92274688 bytes
Redo Buffers 7168000 bytes

Recovery Manager complete.

Once the instance is in nomount state, restore the spfile.

vi restorespfile.rcv

set dbid= 565953070;
run {
allocate channel ch1 type ‘sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
restore spfile ;
release channel ch1 ;

rpsp:/opt/oracle/scripts>rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=restorespfile.rcv

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Nov 22 14:00:52 2005

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: RPSP (not mounted)
connected to recovery catalog database

RMAN> set dbid= 565953070;
2> run {
3> allocate channel ch1 type ‘sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
4> restore spfile ;
5> release channel ch1 ;
6> }
7>
8>
executing command: SET DBID
database name is “RPSP” and DBID is 565953070

allocated channel: ch1
channel ch1: sid=35 devtype=SBT_TAPE
channel ch1: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting restore at 22-NOV-05

channel ch1: starting datafile backupset restore
channel ch1: restoring SPFILE
output filename=/opt/oracle/product10g/dbs/spfilerpsp.ora
channel ch1: reading from backup piece 20h4bm92_1_1
channel ch1: restored backup piece 1
piece handle=20h4bm92_1_1 tag=TAG20051122T030003
channel ch1: restore complete, elapsed time: 00:03:27
Finished restore at 22-NOV-05

released channel: ch1

Recovery Manager complete.
rpsp:/opt/oracle/scripts>

Now that the spfile is restored, create pfile from spfile…..edit pfile…make changes to point to this machine and then recreate spfile from pfile.

Next restore controlfile..

Make a restore.rcv file…TO RESTORE CONTROLFILE

Include following…

set dbid= 565953070;
run {
allocate channel ch1 type ‘sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
restore controlfile ;
release channel ch1 ;
}

Run at command prompt

rpsp:/opt/oracle/scripts>rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=restore.rcv log=restore.log

tail – f restore.log

allocated channel: ch1
channel ch1: sid=323 devtype=SBT_TAPE
channel ch1: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting restore at 21-NOV-05

channel ch1: starting datafile backupset restore
channel ch1: restoring control file
channel ch1: reading from backup piece 1ph491rn_1_1
channel ch1: restored backup piece 1
piece handle=1ph491rn_1_1 tag=TAG20051121T030004
channel ch1: restore complete, elapsed time: 00:04:46
output filename=/itlinux26db06/ORACLE/rpsp/control01.ctl
output filename=/itlinux26db06/ORACLE/rpsp/control02.ctl
output filename=/itlinux26db06/ORACLE/rpsp/control03.ctl
Finished restore at 21-NOV-05

released channel: ch1

Recovery Manager complete.

Next step …since controlfile is restored…MOUNT THE DATABASE.

SQL> alter database mount;

Database altered.

SQL> select name from v$controlfile;

NAME
——————————————————————————–
/itlinux26db06/ORACLE/rpsp/control01.ctl
/itlinux26db06/ORACLE/rpsp/control02.ctl
/itlinux26db06/ORACLE/rpsp/control03.ctl

Now create new .rcv for restoration of DATAFILES……

Use this script to generate the files to be renamed…

select ‘set newname for datafile ‘||file_id||’ to ”’||’/’||
decode(substr(file_name,instr(file_name,’/’,1)+ 1,instr(file_name,’/’,1,2) – 2),
‘patriot7′,’itsun103ekcl07′)||’/’||substr(file_name,(instr(file_name,’ORACLE’)))||”’;’
from dba_data_files;

vi restoredb.rcv

run {
allocate channel ch1 type ‘sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
set newname for datafile 4 to ‘/itlinux26db06/ORACLE/rpsp/users01.dbf’;
set newname for datafile 3 to ‘/itlinux26db06/ORACLE/rpsp/sysaux01.dbf’;
set newname for datafile 2 to ‘/itlinux26db06/ORACLE/rpsp/undotbs01.dbf’;
set newname for datafile 1 to ‘/itlinux26db06/ORACLE/rpsp/system01.dbf’;
set newname for datafile 5 to ‘/itlinux26db06/ORACLE/rpsp/example01.dbf’;
set newname for datafile 6 to ‘/itlinux26db06/ORACLE/rpsp/perfstat01.dbf’;
set newname for datafile 7 to ‘/itlinux26db06/ORACLE/rpsp/tools01.dbf’;
set newname for datafile 8 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_dat01.dbf’;
set newname for datafile 9 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_dat02.dbf’;
set newname for datafile 10 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_dat03.dbf’;
set newname for datafile 11 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_dat04.dbf’;
set newname for datafile 12 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_ind01.dbf’;
set newname for datafile 13 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_ind02.dbf’;
set newname for datafile 14 to ‘/itlinux26db06/ORACLE/rpsp/rpsp_ind03.dbf’;
set newname for datafile 15 to ‘/itlinux26db06/ORACLE/rpsp/undotbs02.dbf’;
restore database ;
switch datafile all;
release channel ch1 ;
}
SAVE THE FILE.

From command prompt…

rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=restoredb.rcv log=restoredb.log

:/opt/oracle/scripts>tail -f restoredb.log

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 21-NOV-05

channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /itlinux26db06/ORACLE/rpsp/system01.dbf
restoring datafile 00002 to /itlinux26db06/ORACLE/rpsp/undotbs01.dbf
restoring datafile 00003 to /itlinux26db06/ORACLE/rpsp/sysaux01.dbf
restoring datafile 00004 to /itlinux26db06/ORACLE/rpsp/users01.dbf
restoring datafile 00005 to /itlinux26db06/ORACLE/rpsp/example01.dbf
restoring datafile 00006 to /itlinux26db06/ORACLE/rpsp/perfstat01.dbf
restoring datafile 00007 to /itlinux26db06/ORACLE/rpsp/tools01.dbf
restoring datafile 00008 to /itlinux26db06/ORACLE/rpsp/rpsp_dat01.dbf
restoring datafile 00009 to /itlinux26db06/ORACLE/rpsp/rpsp_dat02.dbf
restoring datafile 00010 to /itlinux26db06/ORACLE/rpsp/rpsp_dat03.dbf
restoring datafile 00011 to /itlinux26db06/ORACLE/rpsp/rpsp_dat04.dbf
restoring datafile 00012 to /itlinux26db06/ORACLE/rpsp/rpsp_ind01.dbf
restoring datafile 00013 to /itlinux26db06/ORACLE/rpsp/rpsp_ind02.dbf
restoring datafile 00014 to /itlinux26db06/ORACLE/rpsp/rpsp_ind03.dbf
channel ch1: reading from backup piece 1gh47mvc_1_1

Now RECOVER DATABASE USING ANY INCREMENTAL BACKUP AND ARCHIVE LOGS…RMAN WILL DO THE NEEDFUL.

To get list of archive logs backed up by RMAN…

Sqlplus rman10rg2/rman10gr2@rmanp

select sequence# from rc_backup_redolog where db_name=’RPSP’;

Last seq is 466.

vi recoverdb.rcv

run {
allocate channel ch1 type ‘sbt_tape’ parms ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.rpspd.opt)’;
recover database until logseq=466 ;
release channel ch1 ;
}

Save the file…

Command prompt…

rman target / catalog rman10gr2/rman10gr2@rmanp cmdfile=rescover.rcv log=recover.log

RMAN does a recovery until log archive log sequence number 466…

allocated channel: ch1
channel ch1: sid=323 devtype=SBT_TAPE
channel ch1: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting recover at 21-NOV-05
channel ch1: starting incremental datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /itlinux26db06/ORACLE/rpsp/system01.dbf
destination for restore of datafile 00002: /itlinux26db06/ORACLE/rpsp/undotbs01.dbf
destination for restore of datafile 00003: /itlinux26db06/ORACLE/rpsp/sysaux01.dbf
destination for restore of datafile 00004: /itlinux26db06/ORACLE/rpsp/users01.dbf
destination for restore of datafile 00005: /itlinux26db06/ORACLE/rpsp/example01.dbf
destination for restore of datafile 00006: /itlinux26db06/ORACLE/rpsp/perfstat01.dbf
destination for restore of datafile 00007: /itlinux26db06/ORACLE/rpsp/tools01.dbf
destination for restore of datafile 00008: /itlinux26db06/ORACLE/rpsp/rpsp_dat01.dbf
destination for restore of datafile 00009: /itlinux26db06/ORACLE/rpsp/rpsp_dat02.dbf
destination for restore of datafile 00010: /itlinux26db06/ORACLE/rpsp/rpsp_dat03.dbf
destination for restore of datafile 00011: /itlinux26db06/ORACLE/rpsp/rpsp_dat04.dbf
destination for restore of datafile 00012: /itlinux26db06/ORACLE/rpsp/rpsp_ind01.dbf
destination for restore of datafile 00013: /itlinux26db06/ORACLE/rpsp/rpsp_ind02.dbf
destination for restore of datafile 00014: /itlinux26db06/ORACLE/rpsp/rpsp_ind03.dbf
channel ch1: reading from backup piece 1nh491pl_1_1
channel ch1: reading from backup piece 1nh491pl_1_1
channel ch1: restored backup piece 1
piece handle=1nh491pl_1_1 tag=TAG20051121T030004
channel ch1: restore complete, elapsed time: 00:04:26
channel ch1: starting incremental datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00015: /itlinux26db06/ORACLE/rpsp/undotbs02.dbf
channel ch1: reading from backup piece 1oh491rm_1_1
channel ch1: restored backup piece 1
piece handle=1oh491rm_1_1 tag=TAG20051121T030004
channel ch1: restore complete, elapsed time: 00:17:57

starting media recovery

channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=456
channel ch1: reading from backup piece 1qh495a4_1_1
channel ch1: restored backup piece 1
piece handle=1qh495a4_1_1 tag=TAG20051121T040004
channel ch1: restore complete, elapsed time: 00:00:02
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.456.1.556053936.log thread=1 sequence=456
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=457
channel ch1: restoring archive log
archive log thread=1 sequence=458
channel ch1: restoring archive log
archive log thread=1 sequence=459
channel ch1: restoring archive log
archive log thread=1 sequence=460
channel ch1: restoring archive log
archive log thread=1 sequence=461
channel ch1: reading from backup piece 1rh49jc6_1_1
channel ch1: restored backup piece 1
piece handle=1rh49jc6_1_1 tag=TAG20051121T080006
channel ch1: restore complete, elapsed time: 00:01:06
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.457.1.556053936.log thread=1 sequence=457
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.458.1.556053936.log thread=1 sequence=458
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.459.1.556053936.log thread=1 sequence=459
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.460.1.556053936.log thread=1 sequence=460
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.461.1.556053936.log thread=1 sequence=461
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=462
channel ch1: restoring archive log
archive log thread=1 sequence=463
channel ch1: reading from backup piece 1sh49jdk_1_1
channel ch1: restored backup piece 1
piece handle=1sh49jdk_1_1 tag=TAG20051121T080006
channel ch1: restore complete, elapsed time: 00:02:56
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.462.1.556053936.log thread=1 sequence=462
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.463.1.556053936.log thread=1 sequence=463
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=464
channel ch1: restoring archive log
archive log thread=1 sequence=465
channel ch1: reading from backup piece 1th4a1e6_1_1
channel ch1: restored backup piece 1
piece handle=1th4a1e6_1_1 tag=TAG20051121T120006
channel ch1: restore complete, elapsed time: 00:00:36
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.464.1.556053936.log thread=1 sequence=464
archive log filename=/itlinux26db06/ORACLE/rpsp/arch/arch.465.1.556053936.log thread=1 sequence=465
media recovery complete, elapsed time: 00:00:26
Finished recover at 21-NOV-05

released channel: ch1

Recovery Manager complete.

Now rename redolog files to point to this machine…

Now select member from v$logfile;…..rename the redo log files to area in ITLINUX26 machine

Alter database rename file ‘/….’ to ‘/itlinux26db06/ORACLE/rpsp/redo…..

Alter database open resetlogs…(IF THIS FAILS DUE TO THE PRESENCE OF A BLOCK CHANGE TRACKING FILE….

alter database disable block change tracking;

Database altered.

SQL> alter database open;

Database altered.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: