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

Thursday, 5 April 2012

RECOVERY: Complete loss of all database


Database Name=ORCL3
Oracle 9i Windows 2003 Env.
DBID=691421794
Backup Available: Full RMAN Online Backup dated: 15/03/2012
                      : Cumulative and Incremental dated: 18/03/2012
                      : Recovery dated: 19/03/2012.

Note: You can find the DBID from alert.log with the Controlfile Backupset. For precaution you must keep record of important information of your database.
-----------------------------------------------------------------------------------------------------------------------------

For Test Environment:
Shutdown your database and delete all the datafiles and controlfile along with spfile.
C:\ORACLE1\ORADATA\DEL *.DBF
C:\ORACLE1\DATABASE\DEL SPFILEORCL3.ORA

Step 1: Create pfile and spfile or restore your old spfile

If you have pfile (you can create from alert. log) then you can startup the database in nomount phase using the pfile and then create spfile from this pfile.

C:\SQLplus /nolog
SQL> startup nomount pfile=’Location of pfile’;
SQL> create spfile from pfile = ’Location of pfile’;

Now connect the target database through RMAN and restore controlfile.

Otherwise you can try to restore the spfile directly through RMAN

Create orcl3_spfile.rcv as:
set dbid= 691421794
run {
startup nomount force  ;
 };

C:\rman target sys/oracle@orcl3 catalog rman/rman@shaan cmdfile=orcl3_spfile.rcv

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

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

RMAN>
executing command: SET DBID
database name is "ORCL3" and DBID is 691421794

Oracle instance started

Total System Global Area     135338868 bytes

Fixed Size                      453492 bytes
Variable Size                109051904 bytes
Database Buffers              25165824 bytes
Redo Buffers                    667648 bytes


RMAN>set dbid=691421794
RMAN>restore spfile ;

Step 2: Restore Controlfile

Same Steps as spfile with the restore command changed. So the new script is

RMAN>set dbid=691421794
RMAN>restore controlfile ;

Step 3: Restore and Recover the database

Since you have the controlfiles now mount the database

SQL> connect sys/oracle@orcl3 as sysdba
Connected.
SQL> alter database mount;

Database altered.

Now get the log sequence number of the database from the catalog database:

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

RMAN> restore database ;
Starting restore at 19-MAR-12

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to C:\ORACLE1\ORADATA\ORCL3\EXAMPLE01.DBF
restoring datafile 00010 to C:\ORACLE1\ORADATA\ORCL3\XDB01.DBF
restoring datafile 00012 to C:\ORACLE1\ORADATA\ORCL3\LOGMNRTS.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\WEEKLY_20120315_L0_ORCL3-1186_1.DB tag=WEEKLY_FULL_DATABASE 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 00006 to C:\ORACLE1\ORADATA\ORCL3\INDX01.DBF
restoring datafile 00007 to C:\ORACLE1\ORADATA\ORCL3\ODM01.DBF
restoring datafile 00008 to C:\ORACLE1\ORADATA\ORCL3\TOOLS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\WEEKLY_20120315_L0_ORCL3-1189_1.DB tag=WEEKLY_FULL_DATABASE 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 00002 to C:\ORACLE1\ORADATA\ORCL3\UNDOTBS01.DBF
restoring datafile 00009 to C:\ORACLE1\ORADATA\ORCL3\USERS01.DBF
restoring datafile 00013 to C:\ORACLE1\ORADATA\ORCL3\OEM_REPOSITORY.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\WEEKLY_20120315_L0_ORCL3-1187_1.DB tag=WEEKLY_FULL_DATABASE 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 00001 to C:\ORACLE1\ORADATA\ORCL3\SYSTEM01.DBF
restoring datafile 00003 to C:\ORACLE1\ORADATA\ORCL3\CWMLITE01.DBF
restoring datafile 00004 to C:\ORACLE1\ORADATA\ORCL3\DRSYS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\WEEKLY_20120315_L0_ORCL3-1188_1.DB tag=WEEKLY_FULL_DATABASE 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 00011 to C:\ORACLE1\ORADATA\ORCL3\MAIN_DBF01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\WEEKLY_20120315_L0_ORCL3-1190_1.DB tag=WEEKLY_FULL_DATABASE params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 19-MAR-12

RMAN> recover database;
Starting recover at 19-MAR-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 00005: C:\ORACLE1\ORADATA\ORCL3\EXAMPLE01.DBF
destination for restore of datafile 00010: C:\ORACLE1\ORADATA\ORCL3\XDB01.DBF
destination for restore of datafile 00012: C:\ORACLE1\ORADATA\ORCL3\LOGMNRTS.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\DAILY_20120318_CUMUL_ORCL3-1212_1.DB tag=DAILY_CUMULATIVE_BACKUP 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 00002: C:\ORACLE1\ORADATA\ORCL3\UNDOTBS01.DBF
destination for restore of datafile 00009: C:\ORACLE1\ORADATA\ORCL3\USERS01.DBF
destination for restore of datafile 00013: C:\ORACLE1\ORADATA\ORCL3\OEM_REPOSITORY.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\DAILY_20120318_CUMUL_ORCL3-1213_1.DB tag=DAILY_CUMULATIVE_BACKUP 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: C:\ORACLE1\ORADATA\ORCL3\SYSTEM01.DBF
destination for restore of datafile 00003: C:\ORACLE1\ORADATA\ORCL3\CWMLITE01.DBF
destination for restore of datafile 00004: C:\ORACLE1\ORADATA\ORCL3\DRSYS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\DAILY_20120318_CUMUL_ORCL3-1214_1.DB tag=DAILY_CUMULATIVE_BACKUP 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 00006: C:\ORACLE1\ORADATA\ORCL3\INDX01.DBF
destination for restore of datafile 00007: C:\ORACLE1\ORADATA\ORCL3\ODM01.DBF
destination for restore of datafile 00008: C:\ORACLE1\ORADATA\ORCL3\TOOLS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\DAILY_20120318_CUMUL_ORCL3-1215_1.DB tag=DAILY_CUMULATIVE_BACKUP 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 00011: C:\ORACLE1\ORADATA\ORCL3\MAIN_DBF01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\DAILY_20120318_CUMUL_ORCL3-1216_1.DB tag=DAILY_CUMULATIVE_BACKUP 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: C:\ORACLE1\ORADATA\ORCL3\EXAMPLE01.DBF
destination for restore of datafile 00010: C:\ORACLE1\ORADATA\ORCL3\XDB01.DBF
destination for restore of datafile 00012: C:\ORACLE1\ORADATA\ORCL3\LOGMNRTS.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\DAILY_20120318_INCR_ORCL3-1220_1.DB tag=DAILY_DIFFERENTIAL_BACKUP 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 00002: C:\ORACLE1\ORADATA\ORCL3\UNDOTBS01.DBF
destination for restore of datafile 00009: C:\ORACLE1\ORADATA\ORCL3\USERS01.DBF
destination for restore of datafile 00013: C:\ORACLE1\ORADATA\ORCL3\OEM_REPOSITORY.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\DAILY_20120318_INCR_ORCL3-1221_1.DB tag=DAILY_DIFFERENTIAL_BACKUP 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: C:\ORACLE1\ORADATA\ORCL3\SYSTEM01.DBF
destination for restore of datafile 00003: C:\ORACLE1\ORADATA\ORCL3\CWMLITE01.DBF
destination for restore of datafile 00004: C:\ORACLE1\ORADATA\ORCL3\DRSYS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\DAILY_20120318_INCR_ORCL3-1222_1.DB tag=DAILY_DIFFERENTIAL_BACKUP 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 00006: C:\ORACLE1\ORADATA\ORCL3\INDX01.DBF
destination for restore of datafile 00007: C:\ORACLE1\ORADATA\ORCL3\ODM01.DBF
destination for restore of datafile 00008: C:\ORACLE1\ORADATA\ORCL3\TOOLS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\DAILY_20120318_INCR_ORCL3-1223_1.DB tag=DAILY_DIFFERENTIAL_BACKUP 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 00011: C:\ORACLE1\ORADATA\ORCL3\MAIN_DBF01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\HOTBACKUP\DAILY_20120318_INCR_ORCL3-1224_1.DB tag=DAILY_DIFFERENTIAL_BACKUP params=NULL
channel ORA_DISK_1: restore complete

starting media recovery

archive log thread 1 sequence 148 is already on disk as file C:\ORACLE1\ORADATA\ORCL3\ARCHIVE\148.ARC
archive log thread 1 sequence 149 is already on disk as file C:\ORACLE1\ORADATA\ORCL3\ARCHIVE\149.ARC
archive log thread 1 sequence 150 is already on disk as file C:\ORACLE1\ORADATA\ORCL3\ARCHIVE\150.ARC
archive log filename=C:\ORACLE1\ORADATA\ORCL3\ARCHIVE\148.ARC thread=1 sequence=148
media recovery complete
Finished recover at 19-MAR-12      

Step 4: Open the database
RMAN> Alter database open;
database opened

NOTE: If your doing incomplete recovery (Recovery by log sequence and by point of time) then you must use resetlogs option to open the database.



0 comments:

Post a Comment