Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 22 April 2013

Disaster Recovery on New Server Using RMAN

Consider we have production server RMAN hotbackup script: full backup of Friday and incremental backup of Sat/Sun/Mon/Wed/Thurs day and Cumulative backup of Tuesday. Unfortunately on Wednesday after noon our database is completely offline due to major crashed and the server requires a complete rebuild. The only way to recover the server is to rebuild the entire server from the RMAN backup either on the same or another server.
In this article I am trying to show rebuild the entire server on different host. For the same host the process is almost same. You can also use the same step to create Test Environment.
Steps:
1.      Install the same OS and Oracle Version including patch
2.      Copy RMAN backup on the same location of destination server.
3.      Create the Oracle Services.
4.      Create the relevant folder on the destination server.
5.      Create the password file for destination host
6.      Restore the SPFILE from autobackup, and MOUNT the database.
7.      Restore the Database from RMAN backup files.
8.      Recover the Database from RMAN backup files, and OPEN the database with RESETLOGS.
Find the DBID for the database you want to restore. Check my other post to know the steps how to get DBID in different circumstances when the database is open or not open: How to Find DBID
1.      Install the same OS and Oracle version (software only).
2.      Create the Oracle Services
You need to create an Oracle Service using 'oradim' utility, without passing any PFILE information.
C:\>oradim -new -sid SADHAN -intpwd SADHAN
Instance created.
Note: Oracle 10g onwards, we can use directly SPFILE for oracle service creation. It is always better to keep PFILE of production database to any where else.
3.      Create the relevant folder on destination server
Create BDUMP/UDDUMP/CDUMP directories under ORACLE_BASE\admin directory.
Create <DATABASE_NAME> directory under ORACLE_BASE\oradata\sadhan
4.      Perform Listener Configuration to ensure that the RMAN catalog and new host db is visible
Tnsping PRODdb
Tnsping RMANdb
Caution: If you performing this restoration as a Test on the other server then never connect RMAN with catalog otherwise you cannot use RMAN to restore the production server again.
5.      Set up the Oracle password file on destination host.
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle.
6.      Start the destination database with the nomount and restore the spfile.
C:\>set oracle_sid=SADHAN
C:\>rman target /
connected to target database (not started)
RMAN> set dbid= 63198018;
executing command: SET DBID
RMAN> startup force nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file D:\oracle\ora92\dbs\INITSADHAN.ORA'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area     898704708 bytes
Fixed Size                      456004 bytes
Variable Size                251658240 bytes
Database Buffers             645922816 bytes
Redo Buffers                    667648 bytes
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'\\dbserver\sadhan\%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog
RMAN> restore spfile from autobackup;   ---default location from autobackup
RMAN> restore spfile to 'D:\oracle\database\spsadhan01.ora' from autobackup;  -- non default location from autobackup
RMAN> restore spfile to 'D:\oracle\database\spsadhan02.ora' from "\\dbserver\sadhan\C-63198018-20121003-01";   ---non default location from backup location.
Now we need to restart the instance. You need just to bounce back the instance if restored the spfile on default location otherwise you need to reference the pfile location to startup and create spfile from that pfile.
RMAN> shutdown immediate
Oracle instance shut down
RMAN> Startup force nomount;  --default location
RMAN> startup force pfile= ‘D:\oracle\ora92\dbs\INITSADHAN.ORA’ nomount;  --non default location
7.      Restore the Control file and Mount the database:
RMAN> restore controlfile from autobackup;
RMAN> restore controlfile from "\\dbserver\sadhan\C-63198018-20121003-01";
RMAN> alter database mount;
8.      Restore and Recover database:
RMAN> RESTORE DATABASE;
Starting restore at 03-OCT-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\SADHAN\UNDOTBS01.DBF
restoring datafile 00009 to G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2981_1.DB tag=SADHAN_FULL_D
BBACKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to D:\ORACLE\ORADATA\SADHAN\TOOLS01.DBF
restoring datafile 00011 to G:\ORA_DBF_EXTENDED\SDH_SHTR01.DBF
restoring datafile 00012 to G:\ORA_DBF_EXTENDED\SDH_FIN01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2983_1.DB tag=SADHAN_FULL_D
BBACKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00007 to D:\ORACLE\ORADATA\SADHAN\SDH_TIMS01.DBF
restoring datafile 00013 to F:\ORACLE\SADHAN\SDH_EDSS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2985_1.DB tag=SADHAN_FULL_D
BBACKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to D:\ORACLE\ORADATA\SADHAN\INDX01.DBF
restoring datafile 00010 to F:\ORACLE\SADHAN\SDH_EDSS01.DBF
restoring datafile 00014 to D:\ORACLE\ORADATA\SADHAN\INDX02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2982_1.DB tag=SADHAN_FULL_D
BBACKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to D:\ORACLE\ORADATA\SADHAN\EXAMPLE01.DBF
restoring datafile 00006 to D:\ORACLE\ORADATA\SADHAN\USERS01.DBF
restoring datafile 00008 to D:\ORACLE\ORADATA\SADHAN\SDH_TIMS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2984_1.DB tag=SADHAN_FULL_D
BBACKUP params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 03-OCT-12
RMAN>
RMAN>RECOVER DATABASE;
Starting recover at 03-OCT-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
destination for restore of datafile 00002: D:\ORACLE\ORADATA\SADHAN\UNDOTBS01.DBF
destination for restore of datafile 00009: G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3036_1.DB tag=CUMULATIVE_L1_
DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: D:\ORACLE\ORADATA\SADHAN\TOOLS01.DBF
destination for restore of datafile 00011: G:\ORA_DBF_EXTENDED\SDH_SHTR01.DBF
destination for restore of datafile 00012: G:\ORA_DBF_EXTENDED\SDH_FIN01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3038_1.DB tag=CUMULATIVE_L1_
DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: D:\ORACLE\ORADATA\SADHAN\INDX01.DBF
destination for restore of datafile 00010: F:\ORACLE\SADHAN\SDH_EDSS01.DBF
destination for restore of datafile 00014: D:\ORACLE\ORADATA\SADHAN\INDX02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3037_1.DB tag=CUMULATIVE_L1_
DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: D:\ORACLE\ORADATA\SADHAN\SDH_TIMS01.DBF
destination for restore of datafile 00013: F:\ORACLE\SADHAN\SDH_EDSS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3040_1.DB tag=CUMULATIVE_L1_
DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: D:\ORACLE\ORADATA\SADHAN\EXAMPLE01.DBF
destination for restore of datafile 00006: D:\ORACLE\ORADATA\SADHAN\USERS01.DBF
destination for restore of datafile 00008: D:\ORACLE\ORADATA\SADHAN\SDH_TIMS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3039_1.DB tag=CUMULATIVE_L1_
DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
destination for restore of datafile 00002: D:\ORACLE\ORADATA\SADHAN\UNDOTBS01.DBF
destination for restore of datafile 00009: G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3041_1.DB tag=DIFFERENTIAL_L1
_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: D:\ORACLE\ORADATA\SADHAN\TOOLS01.DBF
destination for restore of datafile 00011: G:\ORA_DBF_EXTENDED\SDH_SHTR01.DBF
destination for restore of datafile 00012: G:\ORA_DBF_EXTENDED\SDH_FIN01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3043_1.DB tag=DIFFERENTIAL_L1
_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: D:\ORACLE\ORADATA\SADHAN\INDX01.DBF
destination for restore of datafile 00010: F:\ORACLE\SADHAN\SDH_EDSS01.DBF
destination for restore of datafile 00014: D:\ORACLE\ORADATA\SADHAN\INDX02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3042_1.DB tag=DIFFERENTIAL_L1
_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: D:\ORACLE\ORADATA\SADHAN\SDH_TIMS01.DBF
destination for restore of datafile 00013: F:\ORACLE\SADHAN\SDH_EDSS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3045_1.DB tag=DIFFERENTIAL_L1
_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: D:\ORACLE\ORADATA\SADHAN\EXAMPLE01.DBF
destination for restore of datafile 00006: D:\ORACLE\ORADATA\SADHAN\USERS01.DBF
destination for restore of datafile 00008: D:\ORACLE\ORADATA\SADHAN\SDH_TIMS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3044_1.DB tag=DIFFERENTIAL_L1
_DATAFILE params=NULL
channel ORA_DISK_1: restore complete

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/03/2012 20:00:52
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed start
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [37833581], [1], [4504], [70575], [244], [], []
ORA-10567: Redo is inconsistent with data block (file# 9, block# 84845)
ORA-10564: tablespace SDH_HRMS_DBF
ORA-01110: data file 9: 'G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 26918
Don't panic when you see the above Error Message. You just need to open the database with RESETLOGS option, as there are no more archivelogs to apply.
RMAN> ALTER DATABASE OPEN RESETLOGS;
database opened
RMAN>
-or- you need to Login with SQL*Plus will be in mount phase
SQL>RECOVER DATABASE UNTIL CANCEL;
Press enter as long as you reach to the missing log
SQL>ALTER DATABASE OPEN RESETLOGS;
Note: You must take fresh full backup after opening the database with resetlogs option and must perform "reset database" to connect rman target for fresh backup (only if you perform alter database resetlogs option from SQL*Plus.)
C:\>RMAN TARGET SYS/SYSMAN@SADHAN.WORD CATALOG CATALOG/CATALOG@RMAN
RMAN> RESET DATABASE;
For more detail Tested scenario please follow the other post: Disaster Recovery from Scratch

1 comments:

  1. Having some sort of backup & recovery is one of the most important things to have when it comes to working with technology. Whether you're doing primarily online work or only using a computer for personal use, always make sure your files have backup!!

    ReplyDelete