Khurram Siddiqui: how to recover database with previous incarnation

Assume this demo is in 9i not in 10g


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> alter database archivelog
  2  /

Database altered.

SQL> alter database open
  2  /

Database altered.


SQL> create table t (a number)
  2  /

Table created.


RMAN> backup database
2> ;

Starting backup at 12-DEC-07
.
.

Finished backup at 12-DEC-07

RMAN>



SQL> select to_char(sysdate,'DD-MM-YYYY:HH24:MI:SS')
  2    from dual
  3  /

TO_CHAR(SYSDATE,'DD
-------------------
12-12-2007 15:09:42

SQL> drop table t
  2  /

Table dropped.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> 

RMAN> connect target sys/sys

connected to target database: ORCL1 (DBID=1025591162)

RMAN> run
2> {
3>  set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')";
4>  restore database;
5>  recover database;
6> }

executing command: SET until clause
using target database controlfile instead of recovery catalog

.
.

Finished restore at 12-DEC-07

Starting recover at 12-DEC-07
using channel ORA_DISK_1

starting media recovery
media recovery complete

Finished recover at 12-DEC-07



RMAN> alter database open resetlogs
2> ;

database opened

RMAN> list incarnation
2> ;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL1    1025591162       PARENT  1          09-MAR-04
2       2       ORCL1    1025591162       PARENT  318842     12-DEC-07<---------set this
3       3       ORCL1    1025591162       CURRENT 331575     12-DEC-07<-----its current

SQL> conn sys/sys as sysdba
Connected.

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER


SQL> drop table t
  2  /

Table dropped.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> 


RMAN> connect target sys/sys

connected to target database: ORCL1 (DBID=1025591162)

RMAN> run
2> {
3>  set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')";
4>  restore database;
5>  recover database;
6> }

executing command: SET until clause
using target database controlfile instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 12/12/2007 15:31:22
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time


RMAN> shutdown immediate

using target database controlfile instead of recovery catalog
database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     171966464 bytes

Fixed Size                      787988 bytes
Variable Size                145488364 bytes
Database Buffers              25165824 bytes
Redo Buffers                    524288 bytes


RMAN> restore controlfile from 'C:\oracle\..\ORCL1\BACKUPSET\2007_12_12\O1_MF_NCSNF_TAG20071212T150529_3OZDPGX9_.BKP'
2> ;

Starting restore at 12-DEC-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL01.CTL
output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL02.CTL
output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL03.CTL
Finished restore at 12-DEC-07

RMAN> startup mount

database is already started
database mounted

RMAN> reset database to incarnation 2
2> ;

Starting implicit crosscheck backup at 12-DEC-07
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 12-DEC-07

Starting implicit crosscheck copy at 12-DEC-07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 12-DEC-07

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_12_12\O1_MF
_1_5_3OZFHKNJ_.ARC
File Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_MF_
NCSNF_TAG20071212T150529_3OZDPGX9_.BKP

database reset to incarnation 2



RMAN> run
2> {
3>  set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')";
4>  restore database;
5>  recover database;
6> }

executing command: SET until clause

ORACLE error from target database:
ORA-19922: there is no parent row with id 0 and level 0
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5038
ORA-06512: at line 1

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT row
s
Starting restore at 12-DEC-07
using channel ORA_DISK_1

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:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSTEM01.DBF

restoring datafile 00002 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\UNDOTBS01.DB
F
restoring datafile 00003 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSAUX01.DBF

restoring datafile 00004 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M
F_NNNDF_TAG20071212T150529_3OZDMCGN_.BKP tag=TAG20071212T150529
channel ORA_DISK_1: restore complete
Finished restore at 12-DEC-07

Starting recover at 12-DEC-07
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 5 is already on disk as file C:\ORACLE\FLASH_RECOV
ERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_12_12\O1_MF_1_5_3OZFHKNJ_.ARC
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_1
2_12\O1_MF_1_5_3OZFHKNJ_.ARC thread=1 sequence=5
media recovery complete
Finished recover at 12-DEC-07


RMAN> alter database open resetlogs
2> ;

database opened

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER

SQL> select name from v$database
  2  /

NAME
---------
ORCL1

11 comments:

Anonymous said...: Our database is not archive enabled..It crashed and user have not taken a backup in years.

While recovering the database it asks for ARc file..

but since we dont have ARC files, how do we recover atleast to a day ago / week ago; September 30, 2008 at 11:37 PM
Khurram Siddiqui said...: Rajeev as yours database is in no archive log mode then probably you have taken cold backup which will make yours database restore till that time when you backed up yours database.

You cannot recover at yours own disposal time within no archivelog mode.; October 7, 2008 at 11:23 PM
Anonymous said...: Thanks.......; April 6, 2009 at 9:55 AM
Ricardinho said...: hey Khurram
Is it neccessary to restore the controlfile in your scenerio; April 13, 2009 at 1:10 AM
Khurram Siddiqui said...: yes ricardinho ,you will have to restore the controlfile which should be before the resetlogs.

Both restored datafiles and controlfile should be of same incarantion otherwise you will get the errorr

ORA-01190: control file or data file 1 is from before the last RESETLOGS

Thanks GOD for 10g which saved this hectic recovery for peviosu incarnation,you can restore and recover the database with diffrent incarnation.; April 15, 2009 at 4:21 AM
Jeremy Quaid said...: Hi Khurram

Thanks for posting this; saved me a lot of trouble. I'm on 10G R2, but still had to reset the incarnation.
I had already restored/recovered the database, but needed to re-restore/recover it to an older SCN.

Jeremy; April 16, 2010 at 5:54 AM
pavan said...: DB version : 10.2.0.2, OS: windows 2003 server
1. on 6th august, client asked to restore the database to 24th july and i did it using rman backup (we don't have recovery catalog configured)
i used set until time clause to do this.

2. today client is asking again to restore to same date i.e 24th july.

when i tried to check through "restore database preview" command, i am getting following error which is not a wonder
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

as backups of previous incarnation will not be recognized, its ok to get the above error.

I know there is an option of reset incarnation, but not sure if it helps in this situation. The last time i used resetlogs in on 6th aug and it is showing as current incarnation below.
Below is my output from list incarnation command

RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 QFILENET 150646127 PARENT 1 23-FEB-06
2 2 QFILENET 150646127 PARENT 516049 16-APR-09
3 3 QFILENET 150646127 CURRENT 110832047 06-AUG-10 ---- this is the restore i did on 6th aug to 24th july

Plz let me know if its possible to do this? If so how can i proceed.; August 17, 2010 at 4:21 AM
Anonymous said...: Hi Khurram,
With all my heart, I extend a BIG THANK YOU!!!!

This saved me a lot of headache and heartache....

Much appreciated.....; September 25, 2010 at 4:30 PM
daspeac said...: I have heard about another way of how can recover formatted sql server data. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues.; October 23, 2010 at 11:54 AM
file repir said...: For mission critical databases, you should have redundant backup sets that can even extend for several backup periods. There are scenarios in which organizations keep databases backups for years to meet the legal compliance.; March 25, 2011 at 4:04 AM
Anonymous said...: Dear Khurram,

sorry for posting in an old post..

we have a situation like below.

we have around 60 datafiles which went offline and was asking for media recovery.

we did recovered 59 files which had archive files.

only 1 file doesn't have archive file, so couldn't be recovered.

so whenever i tried to make all the files online, and so the tablespace, it is showing this 1 file should be recovered.

is there any chance we can recover it with out archive log.

or

is there anyway we can make the tablespace online, by ignoring this unrecovered datafile?

please do reply.; December 10, 2012 at 11:24 PM

Khurram Siddiqui

Tuesday, March 18, 2008

how to recover database with previous incarnation

11 comments:

Followers

About Me

Blog Archive