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/[email protected] catalog rman/[email protected] 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/[email protected] 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.
