RMAN restore to new host

The mission:

Restore a production 10.2.0.4.0 database to a new host in order to test our recoveries. The production database is part of a Data Guard configuration in Maximum Availability mode and it runs on Unix RAW devices. The restore will be to ASM.

Notes by my colleague Ian Baugaard:

If you perform a test restore only, then do not connect to the recovery catalog when restoring the datafiles. Otherwise, RMAN records information about the restored datafiles to the recovery catalog. This intereferes with future attempts to restore and recover the primary database. If you must use a recovery catalog because the control file is not large enough to contain the RMAN repository data on all of the backups that you need to restore, then export the catalog and import it into a different schema or database and use the copied recovery catalog for the test restore. Otherwise, the catalog considers the restored database as the current target database.

Also confirm all of the following before commencing:

- Ensure that the /etc/oratab file is correctly configured
- Ensure that the following environment variable are set to the values shown
o NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
o NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
- Ensure that the $ORACLE_BASE/admin/$ORACLE_SID/bdump directory is empty
- Ensure that the job_queue_processes parameter is set to 0 when editing the parameter file at step 3
- That the location specified in the log_archive_dest parameter is valid, writable and empty
- Confirm whether or not the source database is using a change tracking file, and if so, try to ensure that the corresponding path exists on the node where the restore is being performed
- Ensure that the tnsnames.ora file does not contain any references whatsoever to a production system

1. Ensure your environment is correctly configured, i.e. ORACLE_SID is set correctly and that the Oracle software installation matches the environment of the source system. When ready, start RMAN and connect to the target without connecting to the recovery catalog.

rman target / NOCATALOG

2. Start the instance without mounting it, using the DBID obtained from the v$database view on the source database. RMAN will fail to find the server parameter file, which has not yet been restored, but will start the instance with a "dummy" file.

RMAN> set DBID dbid ;
RMAN> startup nomount ;

3. Restore and edit the server parameter file. NB: The values for NSR Server and NSR Client will have to be adjusted accordingly, and can be derived from the values of the production script

run {
allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
restore spfile to pfile '$ORACLE_HOME/dbs/init$ORACLE_SID.ora' from autobackup;
shutdown abort;
}

4. From a second session, edit all appropriate parameters of the restored parameter file to cater for the environment you are restoring on, i.e. to reflect new directory structures, memory differences, etc. Once done, start the instance using the new file

RMAN> startup force nomount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora' ;

5. Proceed to restore the controlfile from an autobackup from your original session

run {
allocate channel c1 device type SBT PARMS=' ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
restore controlfile from autobackup;
alter database mount;
}

6. Query the database filenames recorded in the control file on the new host by running the following query in SQL*Plus

col name for a60
SELECT file# AS "File/Grp#", name FROM v$datafile
UNION
SELECT group#, member FROM v$logfile;
EXIT

7. Write the RMAN recovery script. The script must include the following steps:
a. For each datafile on the destination host that is restored to a different path than it had on the source host, use a SET NEWNAME command to specify the new path on the destination host. (If the file systems on the destination system are set up to have the same paths as the source host, then do not use SET NEWNAME for those files restored to the same path as on the source host.) See an example of a dynamic SQL script to generate the commands needed below

select
'set newname for datafile '|| file# ||' to '''|| replace( name, 'rabdg', 'rabbcv' ) ||''' ;'
from v$datafile
order by file#

b. For each online redo log that is to be created at a different location than it had on the source host, use SQL ALTER DATABASE RENAME FILE commands to specify the pathname on the destination host. (If the file systems on the destination system are set up to have the same paths as the source host, then do not use ALTER DATABASE RENAME FILE for those files restored to the same path as on the source host.) See an example of a dynamic SQL script to generate the commands needed below

select
'SQL "alter database rename file '''''|| member ||''''' to '''''|| replace ( member, 'rabdg', 'rabbcv' ) ||''''' " ;'
from v$logfile
order by member
c. Perform a SET UNTIL to limit media recovery to the end of the archived redo logs.
d. Run SWITCH so that the control file recognizes the new path names as the official new names of the datafiles
e. Restore and recover the database
NB: By default, RMAN does not restore read-only files when you issue the RESTORE DATABASE command.

run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
allocate channel c2 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
allocate channel c3 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';

# RENAME THE DATAFILES AND ONLINE REDO LOGS
set newname for datafile 1 to '+TUNXDG_RESTORE/casprd/datafile/casprd_SYSTEM01' ;
set newname for datafile 2 to '+TUNXDG_RESTORE/casprd/datafile/casprd_UNDO01' ;
SQL "alter database rename file ''/dev/vx/rdsk/redodg/casprd_REDO1'' to ''+TUNXDG_RESTORE/casprd/onlinelog/casprd_REDO1'' ";

# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20080310 08:33:12','YYYYMMDD HH24:MI:SS')" ;
# RESTORE THE DATABASE AND SWITCH THE DATAFILE NAMES
restore database check readonly force;
switch datafile all;

# RECOVER THE DATABASE
recover database;
}
EXIT

8. Now perform an OPEN RESETLOGS at the restored database.

RMAN> alter database open resetlogs ;

9. Issue a shutdown and start the restored database in mount mode, and correct the location of all tempfiles

10. If this was a test restore, and it was successful, then you can shut down the test database instance, and delete the test database with all of its files.

SQL> startup force mount restrict pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora' ;
SQL> drop database ;

rm $ORACLE_HOME/dbs/init$ORACLE_SID.ora
rm log_archive_dest/*

Scripts and Logfiles

casprd1:/opt/apps/oracle/database/10.2.0.4/rdbms/admin> rman target / NOCATALOG

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Feb 18 14:17:28 2009

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

connected to target database (not started)

RMAN> set dbid 2070848595;

executing command: SET DBID

RMAN> startup nomount ;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/opt/apps/oracle/database/10.2.0.4/dbs/initcasprd1.ora'

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

Total System Global Area     159383552 bytes

Fixed Size                     2054608 bytes
Variable Size                 67110448 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6332416 bytes

RMAN> run {
2> allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1,NSR_CLIENT=pcapa2)';
3> restore spfile to pfile '$ORACLE_HOME/dbs/init$ORACLE_SID.ora' from autobackup;
4> shutdown abort;
5> }

allocated channel: c1
channel c1: sid=39 devtype=SBT_TAPE
channel c1: NMO v4.5.0.0

Starting restore at 19-FEB-2009 12:40:16

Change init.ora file to:
*.audit_file_dest='/opt/apps/oracle/admin/casprd1/adump'
*.background_dump_dest='/opt/apps/oracle/admin/casprd1/bdump'
*.compatible='10.2.0'
*.control_file_record_keep_time=21
*.core_dump_dest='/opt/apps/oracle/admin/casprd1/cdump'
*.db_block_size=8192
*.db_cache_size=500000000
*.db_files=500
*.db_name='casprd'
*.db_writer_processes=4
*.instance_name='casprd1'
*.large_pool_size=50000000
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='casprd_%t_%s_%r.arc'
*.log_archive_max_processes=5
*.log_archive_min_succeed_dest=1
*.log_buffer=16384000
*.os_authent_prefix='ops$'
*.recovery_parallelism=4
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='casprd'
*.sga_target=2G
*.undo_management='AUTO'
*.undo_retention=28800
*.undo_tablespace='UNDO'
*.user_dump_dest='/opt/apps/oracle/admin/casprd1/udump'
*.workarea_size_policy='auto'
*.db_create_file_dest='+TUNXDG_RESTORE'
*.db_recovery_file_dest='+TUNXDG_RESTORE'
*.DB_RECOVERY_FILE_DEST_SIZE=2G
*.PROCESSES=200

RMAN> startup force nomount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora' ;

Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     2057496 bytes
Variable Size                503319272 bytes
Database Buffers            1610612736 bytes
Redo Buffers                  31494144 bytes

RMAN> run {
2> allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1,NSR_CLIENT=pcapa2,NSR_DEBUG_FILE=/home/oracle/nsr_icap2.log)';
3> restore controlfile from autobackup;
4> alter database mount;
5> }

allocated channel: c1
channel c1: sid=33 devtype=SBT_TAPE
channel c1: NMO v4.5.0.0

Starting restore at 19-FEB-2009 13:53:31

channel c1: looking for autobackup on day: 20090219
channel c1: autobackup found: c-2070848595-20090219-04
channel c1: control file restore from autobackup complete
output filename=+TUNXDG_RESTORE/casprd/controlfile/current.351.679240505
output filename=+TUNXDG_RESTORE/casprd/controlfile/current.352.679240507
Finished restore at 19-FEB-2009 13:55:17

database mounted
released channel: c1

Edit init.ora file to reflect new controlfile settings:
*.control_files='+TUNXDG_RESTORE/casprd/controlfile/current.351.679240505’,’+TUNXDG_RESTORE/casprd/controlfile/current.352.679240507'


casprd1:/home/oracle/meyert> cat icap_restore.ksh
rman target / nocatalog log='/home/oracle/meyert/icap_restore.log' cmdfile='/home/oracle/meyert/icap_restore.par'

casprd1:/home/oracle/meyert> cat icap_restore.par
run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel chnl1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl2 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl3 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';

# RENAME THE DATAFILES AND ONLINE REDO LOGS
set newname for datafile 1 to '+TUNXDG_RESTORE/casprd/datafile/casprd_SYSTEM01' ;
set newname for datafile 2 to '+TUNXDG_RESTORE/casprd/datafile/casprd_UNDO01' ;
…
set newname for datafile 321 to '+TUNXDG_RESTORE/casprd/datafile/casprd_SA_T_CMD_PARAM_0422' ;

SQL "alter database rename file ''/dev/vx/rdsk/redodg/casprd_REDO1'' to ''+TUNXDG_RESTORE/casprd/onlinelog/casprd_REDO1'' ";
…

# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20090303 06:00:00','YYYYMMDD HH24:MI:SS')" ;

# RESTORE THE DATABASE AND SWITCH THE DATAFILE NAMES
restore database check readonly force;
switch datafile all;

}
EXIT

casprd1:/home/oracle/meyert> cat icap_restore.log

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Mar 5 15:44:38 2009

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

connected to target database: CASPRD (DBID=2070848595, not open)
using target database control file instead of recovery catalog

allocated channel: chnl1
channel chnl1: sid=26 devtype=SBT_TAPE
channel chnl1: NMO v4.5.0.0

allocated channel: chnl2
channel chnl2: sid=33 devtype=SBT_TAPE
channel chnl2: NMO v4.5.0.0

allocated channel: chnl3
channel chnl3: sid=11 devtype=SBT_TAPE
channel chnl3: NMO v4.5.0.0

executing command: SET NEWNAME

executing command: SET NEWNAME

…

executing command: SET until clause

Starting restore at 05-MAR-2009 15:51:58

channel chnl1: starting datafile backupset restore
channel chnl1: specifying datafile(s) to restore from backup set
restoring datafile 00009 to +TUNXDG_RESTORE/casprd/datafile/casprd_ic_t_met2_12
restoring datafile 00027 to +TUNXDG_RESTORE/casprd/datafile/casprd_pp_t_conf_0401
restoring datafile 00156 to +TUNXDG_RESTORE/casprd/datafile/casprd_sa_i_ssb
restoring datafile 00193 to +TUNXDG_RESTORE/casprd/datafile/casprd_pp_t_conf_0409
channel chnl1: reading from backup piece /full_CASPRD_t680405445_s84501_p1/
…
Finished restore at 07-MAR-2009 01:19:52

datafile 1 switched to datafile copy
input datafile copy recid=665 stamp=680836812 filename=+TUNXDG_RESTORE/casprd/datafile/casprd_system01
datafile 2 switched to datafile copy
input datafile copy recid=666 stamp=680836813 filename=+TUNXDG_RESTORE/casprd/datafile/casprd_undo01
…
datafile 321 switched to datafile copy
input datafile copy recid=985 stamp=680837070 filename=+TUNXDG_RESTORE/casprd/datafile/casprd_sa_t_cmd_param_0422

Starting recover at 07-MAR-2009 01:25:06
channel chnl1: starting incremental datafile backupset restore
channel chnl1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: +TUNXDG_RESTORE/casprd/datafile/casprd_sa_t_cmd_param_0418
destination for restore of datafile 00035: +TUNXDG_RESTORE/casprd/datafile/casprd_sa_i_cmd
destination for restore of datafile 00042: +TUNXDG_RESTORE/casprd/datafile/casprd_sa_i_event
destination for restore of datafile 00192: +TUNXDG_RESTORE/casprd/datafile/casprd_ic_t_met2_05
channel chnl1: reading from backup piece /level1_CASPRD_t680504446_s84621_p1/
…
channel chnl3: reading from backup piece /level1_CASPRD_t680506557_s84697_p1/
channel chnl3: restored backup piece 1
piece handle=/level1_CASPRD_t680506557_s84697_p1/ tag=TAG20090303T050038
channel chnl3: restore complete, elapsed time: 00:17:46

starting media recovery

Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+TUNXDG_RESTORE/casprd/datafile/casprd_system01'

released channel: chnl1
released channel: chnl2
released channel: chnl3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/07/2009 08:19:08
RMAN-06557: unable to restore archived log thread 1, sequence 12670
RMAN-06558: archived log size of 2043389 kb is bigger than available space of 2020336 kb

Recovery Manager complete.

(My RECOVERY_DEST_SIZE was too small, so I increased it and simply ran a recovery – since the restore was completed)

casprd1:/home/oracle/meyert> cat icap_recovery.ksh
rman target / nocatalog log='/home/oracle/meyert/icap_recovery.log' cmdfile='/home/oracle/meyert/icap_recovery.par'

casprd1:/home/oracle/meyert> cat icap_recovery.par
run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel chnl1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl2 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl3 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';

# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20090303 06:00:00','YYYYMMDD HH24:MI:SS')" ;

# RECOVER THE DATABASE
recover database;
}
EXIT

casprd1:/home/oracle/meyert> cat icap_recovery.par
run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel chnl1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl2 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl3 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';

# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20090303 06:00:00','YYYYMMDD HH24:MI:SS')" ;

# RECOVER THE DATABASE
recover database;
}
EXIT


casprd1:/home/oracle/meyert> cat icap_recovery.log

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Mar 9 07:33:00 2009

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

connected to target database: CASPRD (DBID=2070848595, not open)
using target database control file instead of recovery catalog

allocated channel: chnl1
channel chnl1: sid=33 devtype=SBT_TAPE
channel chnl1: NMO v4.5.0.0

allocated channel: chnl2
channel chnl2: sid=12 devtype=SBT_TAPE
channel chnl2: NMO v4.5.0.0

allocated channel: chnl3
channel chnl3: sid=27 devtype=SBT_TAPE
channel chnl3: NMO v4.5.0.0

executing command: SET until clause

Starting recover at 09-MAR-2009 07:33:37

starting media recovery

channel chnl1: starting archive log restore to default destination
channel chnl1: restoring archive log
archive log thread=1 sequence=12668
channel chnl1: restoring archive log
archive log thread=1 sequence=12669
channel chnl1: reading from backup piece /arch_CASPRD_t680507135_s84703_p1/
channel chnl1: restored backup piece 1
piece handle=/arch_CASPRD_t680507135_s84703_p1/ tag=TAG20090303T054533
channel chnl1: restore complete, elapsed time: 00:01:17
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12668.354.681032213 thread=1 sequence=12668
channel default: deleting archive log(s)
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12668.354.681032213 recid=108554 stamp=681032266
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12669.353.681032215 thread=1 sequence=12669
channel default: deleting archive log(s)
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12669.353.681032215 recid=108553 stamp=681032231
channel chnl1: starting archive log restore to default destination
channel chnl1: restoring archive log
archive log thread=1 sequence=12670
channel chnl1: reading from backup piece /arch_CASPRD_t680515326_s84705_p1/
channel chnl1: restored backup piece 1
piece handle=/arch_CASPRD_t680515326_s84705_p1/ tag=TAG20090303T080205
channel chnl1: restore complete, elapsed time: 00:02:06
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12670.353.681033147 thread=1 sequence=12670
channel default: deleting archive log(s)
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12670.353.681033147 recid=108555 stamp=681033249
media recovery complete, elapsed time: 00:05:01
Finished recover at 09-MAR-2009 07:59:13
released channel: chnl1
released channel: chnl2
released channel: chnl3

Recovery Manager complete.

Startup fails with ORA-16072: a minimum of one standby database destination is required Instance terminated by LGWR
I found the solution on Stewart F's blog.

Metalink note 245731.1 this was caused by that fact that there was still some kind of reference in my Primary databases data dictionary: "The Primary Database has still the Protection Mode stored in the Data Dictionary. The Protection Mode requires a running connection to a Standby Database with LGWR SYNC as Log-Transportation Mode. Since the standby is not available anymore,LGWR of the primary terminates the instance."

Solution:

SYS@casprd1> startup mount;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2057496 bytes
Variable Size             503319272 bytes
Database Buffers         1610612736 bytes
Redo Buffers               31494144 bytes
Database mounted.
SYS@casprd1> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Database altered.

SYS@casprd1> shutdown immediate
SYS@casprd1> startup mount;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2057496 bytes
Variable Size             503319272 bytes
Database Buffers         1610612736 bytes
Redo Buffers               31494144 bytes
Database mounted.
SYS@casprd1> select name from v$tempfile ;

NAME
-----------------------------------------------------------------------------------------------------------------------------
/dev/vx/rdsk/oradg/casprd_TEMP01
/dev/vx/rdsk/oradg/casprd_TEMP02
/dev/vx/rdsk/oradg/casprd_TEMP04
/dev/vx/rdsk/oradg1/casprd_TEMP05
/dev/vx/rdsk/oradg1/casprd_TEMP06
/dev/vx/rdsk/oradg1/casprd_TEMP07
/dev/vx/rdsk/oradg1/casprd_TEMP08

7 rows selected.

SYS@casprd1> alter database rename file '/dev/vx/rdsk/oradg/casprd_TEMP01' to '+TUNXDG_RESTORE/casprd/tempfile/casprd_TEMP01';

Database altered.

Etc for all 7 tempfiles.

SYS@casprd1> alter database open;

Database altered.