Restore Database To New Host & New ASM DiskGroup 

Few weeks back, we need to perform a mock test of disaster i.e. Server Failure and check the response times of our recovery process. Since we are using 11g with Grid infrastructure we need to consider the dependency of ASM disk group while restoring the database. 

So to start with following is the snipped we used to take backup on source database.

run {
sql 'alter system archive log current';
backup tag level0_database format '/gold_backups/omsupg_bkP/%d_DAT_%U_%t' database plus archivelog;
backup current controlfile tag CURR_CONTROLFILE format '/gold_backups/omsupg_bkP/%d_CONTROL_%U_%t';
sql 'alter system archive log current';
}
On the target server the 11g binaries are already installed and patched as per source binaries. 

#export ORACLE_HOME=/u01/app/oracle/product/11.2.3/dbhome_1

We created a temporary parameter file to cut the task.

Transient Parameter file -


*.audit_file_dest='/u01/app/oracle/admin/OMSUPG/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.control_files='+DATA_PSFT_HR/OMSUPG/CONTROLFILE/current.3884.820500121'
-- THE ABOVE LOCATION MAY NOT BE KNOWN AS START OF THE DATABASE AND MAY POINT TO NAME ON OLD SERVER. ONE NEEDS TO CHANGE THIS POST CONTROL FILE RESTORE.*.db_create_file_dest='+DATA_PSFT_HR'
*.db_create_online_log_dest_1='+DATA_PSFT_HR'*.db_file_name_convert='+DATA_PSOFTHR','+DATA_PSFT_HR'
*.log_file_name_convert='+DATA_PSOFTHR','+DATA_PSFT_HR'
*.db_name='OMSUPG'
*.diagnostic_dest='/u01/app/oracle'
*.instance_number=1
OMSUPG.job_queue_processes=1000
*.log_archive_format='%t_%s_%r.dbf'*.db_domain=''
*.memory_target=4G
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=300
*.undo_tablespace='UNDOTBS1'

SQL> create spfile from pfile;File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 4.0486E+10 bytes
Fixed Size                  2237088 bytes
Variable Size            1.9327E+10 bytes
Database Buffers         2.1072E+10 bytes
Redo Buffers               84631552 bytes-- Now connect to recently started database thru RAN and perform as follows.

RMAN> set DBID=3245417037executing command: SET DBID
RMAN> restore controlfile  to '+DATA_PSFT_HR' from '/oraclebackup/omsupg_bkP/snapcf_OMSUPG.f' ;

Starting restore at 11-JUL-13
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 11-JUL-13

Note - 
ONCE CONTROL FILE IS RESTORED YOU NEED TO MODIFY PFILE TO POINT TO ABSOLUTE PATH OR OTHERWISE DATABASE WILL NOT BE ABLE TO FIND CORRECT CONTROL FILE

*.control_files='+DATA_PSFT_HR/OMSUPG/CONTROLFILE/current.3884.820500121'

THEN START THE DB IN MOUNT MODE

[oracle@hofsvdorarac2e ~]$ rman target / nocatalogRecovery Manager: Release 11.2.0.3.0 - Production on Thu Jul 11 13:20:13 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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

-- If your control file is backed up after backup is taken then control file will have backup info OR otherwise one has catalog the backup pieces with control file. 

RMAN> CATALOG START WITH '/oraclebackup/omsupg_bkP/';

searching for all files that match the pattern /oraclebackup/omsupg_bkP/

List of Files Unknown to the Database
=====================================
File Name: /oraclebackup/omsupg_bkP/OMSUPG_DAT_1joefcg0_1_1_820490752
File Name: /oraclebackup/omsupg_bkP/OMSUPG_DAT_1moefcj8_1_1_820490856
File Name: /oraclebackup/omsupg_bkP/OMSUPG_DAT_1loefcj5_1_1_820490853
File Name: /oraclebackup/omsupg_bkP/OMSUPG_CONTROL_1noefcj9_1_1_820490857
File Name: /oraclebackup/omsupg_bkP/OMSUPG_DAT_1koefcgq_1_1_820490778
File Name: /oraclebackup/omsupg_bkP/snapcf_OMSUPG.f

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oraclebackup/omsupg_bkP/OMSUPG_DAT_1joefcg0_1_1_820490752
File Name: /oraclebackup/omsupg_bkP/OMSUPG_DAT_1moefcj8_1_1_820490856
File Name: /oraclebackup/omsupg_bkP/OMSUPG_DAT_1loefcj5_1_1_820490853
File Name: /oraclebackup/omsupg_bkP/OMSUPG_CONTROL_1noefcj9_1_1_820490857
File Name: /oraclebackup/omsupg_bkP/OMSUPG_DAT_1koefcgq_1_1_820490778
File Name: /oraclebackup/omsupg_bkP/snapcf_OMSUPG.f
-- Since I am restoring the backup on new server which as has different disk group name, I need to rename all my data files to new disk group name
+DATA_PSOFTHR  --> +DATA_PSFT_HR

RMAN> RUN{
SET NEWNAME FOR DATAFILE 1 TO '+DATA_PSFT_HR';
SET NEWNAME FOR DATAFILE 2 TO '+DATA_PSFT_HR';
SET NEWNAME FOR DATAFILE 3 TO '+DATA_PSFT_HR';
SET NEWNAME FOR DATAFILE 4 TO '+DATA_PSFT_HR';
SET NEWNAME FOR DATAFILE 5 TO '+DATA_PSFT_HR';
SET NEWNAME FOR DATAFILE 6 TO '+DATA_PSFT_HR';
SET NEWNAME FOR DATAFILE 7 TO '+DATA_PSFT_HR';SQL "ALTER DATABASE RENAME FILE ''+DATA_PSOFTHR/omsupg/redo01.log'' TO ''+DATA_PSFT_HR'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA_PSOFTHR/omsupg/redo02.log'' TO ''+DATA_PSFT_HR'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA_PSOFTHR/omsupg/redo03.log'' TO ''+DATA_PSFT_HR'' ";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

executing command: SET NEWNAMEexecuting command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
sql statement: ALTER DATABASE RENAME FILE ''+DATA_PSOFTHR/omsupg/redo01.log''    TO ''+DATA_PSFT_HR''
sql statement: ALTER DATABASE RENAME FILE ''+DATA_PSOFTHR/omsupg/redo02.log''    TO ''+DATA_PSFT_HR''
sql statement: ALTER DATABASE RENAME FILE ''+DATA_PSOFTHR/omsupg/redo03.log''    TO ''+DATA_PSFT_HR''

Starting restore at 11-JUL-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=456 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA_PSFT_HR
channel ORA_DISK_1: restoring datafile 00002 to +DATA_PSFT_HR
channel ORA_DISK_1: restoring datafile 00003 to +DATA_PSFT_HR
channel ORA_DISK_1: restoring datafile 00004 to +DATA_PSFT_HR
channel ORA_DISK_1: restoring datafile 00005 to +DATA_PSFT_HR
channel ORA_DISK_1: restoring datafile 00006 to +DATA_PSFT_HR
channel ORA_DISK_1: restoring datafile 00007 to +DATA_PSFT_HR
channel ORA_DISK_1: reading from backup piece /gold_backups/omsupg_bkP/OMSUPG_DAT_1koefcgq_1_1_820490778
channel ORA_DISK_1: errors found reading piece handle=/gold_backups/omsupg_bkP/OMSUPG_DAT_1koefcgq_1_1_820490778
channel ORA_DISK_1: failover to piece handle=/oraclebackup/omsupg_bkP/OMSUPG_DAT_1koefcgq_1_1_820490778 tag=LEVEL0_DATABASE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 11-JUL-13

datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=820503843 file name=+DATA_PSFT_HR/omsupg/datafile/system.3899.820503797
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=820503843 file name=+DATA_PSFT_HR/omsupg/datafile/sysaux.3902.820503797
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=820503843 file name=+DATA_PSFT_HR/omsupg/datafile/undotbs1.3870.820503801
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=820503843 file name=+DATA_PSFT_HR/omsupg/datafile/users.3900.820503801
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=820503843 file name=+DATA_PSFT_HR/omsupg/datafile/mgmt_ecm_depot_ts.3897.820503801
datafile 6 switched to datafile copy
input datafile copy RECID=14 STAMP=820503844 file name=+DATA_PSFT_HR/omsupg/datafile/mgmt_tablespace.3901.820503789
datafile 7 switched to datafile copy
input datafile copy RECID=15 STAMP=820503844 file name=+DATA_PSFT_HR/omsupg/datafile/mgmt_ad4j_ts.3898.820503801

Starting recover at 11-JUL-13
using channel ORA_DISK_1

starting media recovery

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/11/2013 13:44:06
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 203 and starting SCN of 2855435 found to restore

RMAN> alter database open resetlogs;database opened

-- if you see the above recovery failed during archive log recovery. The reason being is that the backup set does not contain any archive log files beyond that point. So when you log in to database, you will see that database is open in read write mode. 

Since I am restoring this database with Oracle Grid, I need to add the database to OCR and start it using SRVCTL utility. However, during start of database I hit following error. 

ERROR: failed to establish dependency between database OMSUPG and diskgroup resource ora.DATA_PSFT_HR.dg
Thu Jul 11 12:42:10 2013
SUCCESS: diskgroup DATA_PSFT_HR was dismounted
alter database mountORA-00210: cannot open the specified control file
ORA-00202: control file: '+DATA_PSFT_HR'
ORA-17503: ksfdopn:2 Failed to open file +DATA_PSFT_HR
ORA-15045: ASM file name '+DATA_PSFT_HR' is not in reference form
ORA-205 signalled during: alter database mount...
Thu Jul 11 12:42:16 2013

Solution to above error is as follows as one needs to change the hard dependency using srvctl command.

# srvctl add database -d OMSUPG -o /u01/app/oracle/product/11.2.3/dbhome_1 -c SINGLE  -i OMSUPG -x host1e

# srvctl modify database -d OMSUPG -a "DATA_PSFT_HR"

Once done, you will now be able to start the start the database with SRVCTL successfully.