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:ORACLE1ORADATADEL *.DBF

C:ORACLE1DATABASEDEL 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:ORACLE1ORADATAORCL3EXAMPLE01.DBF

restoring datafile 00010 to C:ORACLE1ORADATAORCL3XDB01.DBF

restoring datafile 00012 to C:ORACLE1ORADATAORCL3LOGMNRTS.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPWEEKLY_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:ORACLE1ORADATAORCL3INDX01.DBF

restoring datafile 00007 to C:ORACLE1ORADATAORCL3ODM01.DBF

restoring datafile 00008 to C:ORACLE1ORADATAORCL3TOOLS01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPWEEKLY_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:ORACLE1ORADATAORCL3UNDOTBS01.DBF

restoring datafile 00009 to C:ORACLE1ORADATAORCL3USERS01.DBF

restoring datafile 00013 to C:ORACLE1ORADATAORCL3OEM_REPOSITORY.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPWEEKLY_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:ORACLE1ORADATAORCL3SYSTEM01.DBF

restoring datafile 00003 to C:ORACLE1ORADATAORCL3CWMLITE01.DBF

restoring datafile 00004 to C:ORACLE1ORADATAORCL3DRSYS01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPWEEKLY_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:ORACLE1ORADATAORCL3MAIN_DBF01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPWEEKLY_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:ORACLE1ORADATAORCL3EXAMPLE01.DBF

destination for restore of datafile 00010: C:ORACLE1ORADATAORCL3XDB01.DBF

destination for restore of datafile 00012: C:ORACLE1ORADATAORCL3LOGMNRTS.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_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:ORACLE1ORADATAORCL3UNDOTBS01.DBF

destination for restore of datafile 00009: C:ORACLE1ORADATAORCL3USERS01.DBF

destination for restore of datafile 00013: C:ORACLE1ORADATAORCL3OEM_REPOSITORY.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_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:ORACLE1ORADATAORCL3SYSTEM01.DBF

destination for restore of datafile 00003: C:ORACLE1ORADATAORCL3CWMLITE01.DBF

destination for restore of datafile 00004: C:ORACLE1ORADATAORCL3DRSYS01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_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:ORACLE1ORADATAORCL3INDX01.DBF

destination for restore of datafile 00007: C:ORACLE1ORADATAORCL3ODM01.DBF

destination for restore of datafile 00008: C:ORACLE1ORADATAORCL3TOOLS01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_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:ORACLE1ORADATAORCL3MAIN_DBF01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_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:ORACLE1ORADATAORCL3EXAMPLE01.DBF

destination for restore of datafile 00010: C:ORACLE1ORADATAORCL3XDB01.DBF

destination for restore of datafile 00012: C:ORACLE1ORADATAORCL3LOGMNRTS.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_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:ORACLE1ORADATAORCL3UNDOTBS01.DBF

destination for restore of datafile 00009: C:ORACLE1ORADATAORCL3USERS01.DBF

destination for restore of datafile 00013: C:ORACLE1ORADATAORCL3OEM_REPOSITORY.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_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:ORACLE1ORADATAORCL3SYSTEM01.DBF

destination for restore of datafile 00003: C:ORACLE1ORADATAORCL3CWMLITE01.DBF

destination for restore of datafile 00004: C:ORACLE1ORADATAORCL3DRSYS01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_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:ORACLE1ORADATAORCL3INDX01.DBF

destination for restore of datafile 00007: C:ORACLE1ORADATAORCL3ODM01.DBF

destination for restore of datafile 00008: C:ORACLE1ORADATAORCL3TOOLS01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_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:ORACLE1ORADATAORCL3MAIN_DBF01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:BACKUPRMANHOTBACKUPDAILY_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:ORACLE1ORADATAORCL3ARCHIVE148.ARC

archive log thread 1 sequence 149 is already on disk as file C:ORACLE1ORADATAORCL3ARCHIVE149.ARC

archive log thread 1 sequence 150 is already on disk as file C:ORACLE1ORADATAORCL3ARCHIVE150.ARC

archive log filename=C:ORACLE1ORADATAORCL3ARCHIVE148.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.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s