Follow Us on Twitter

Database incarnations: Recovering through RESETLOGS

by Ian Hoogeboom on November 3, 2009 · 7 comments

In Oracle 10g, when recovering through RESETLOGS, you need to have good view of the incarnations of your database. An incarnation is a seperate ‘life path’ of your database, which is created at the time when a RESETLOGS is issued.

By default, the RMAN ‘UNTIL’ clause uses the current incarnation. When you want to restore/recover to a time before or after a RESETLOGS, you need to set the INCARNATION you want to ‘go to’. Luckily the CONTROLFILE keeps information about incarnations, which can be queried:

Solution Incarnation

RMAN> LIST INCARNATION [OF DATABASE];

Take a look at the following two scenarios of restoring through a RESETLOGS, one to AFTER a RESETLOGS, and one BEFORE a RESETLOGS. I’ll try to explain what happens…

Recover to a point after RESETLOGS

Lets assume the current incarnation of the database is 8.

10:37 Complete backup
10:40 (…)
10:42 Create table X and inserts
10:50 Complete backup
11:00 Restore/recover to 10:40
11:03 RESETLOGS (birth of incarnation 9)
11:05 (…)
11:14 Restore/recover to 11:05

At 11:03 a new incarnation 9 is created of the database, whithout table X, because at 10:40 it did not exist yet. Althought RMAN reports it has a backup of the database taken at 10:50, this has never happened in incarnation 9, because it started with the database’s situation of 10:40.

When at 11:14, you want to restore to 11:05, incarnation 9 is assumed and RMAN uses the backup from 10:37. Table X does not exists after the recovery, again because it was never created in incarnation 9.

When you want your table X, you need to set the incarnation before restoring, because this table was created in incarnation 8:

RMAN> LIST INCARNATION;
DB Key  Inc Key DB Name  DB ID       STATUS  Reset SCN  Reset Time
------- ------- -------- ----------- ------- ---------- ----------
(1..7 removed)
8       8       IANH     3640854324  PARENT  2892737    03-11-2009 10:21:42
9       9       IANH     3640854324  CURRENT 2896602    03-11-2009 11:03:05

RMAN> RESET DATABASE TO INCARNATION 8;

Now when you restore and recover to 11:05, RMAN uses the backup taken at 10:50 and the database does include table X, but remember when you open the database with RESETLOGS, you create a new incarnation (10) again!

Recover to a point before RESETLOGS

Lets go back in time and assume the current incarnation of the database is still 8.

10:37 Complete backup
10:40 (…)
10:42 Create table X and inserts
10:45 (…)
10:50 Complete backup
11:00 Restore/recover to 10:40
11:03 RESETLOGS (birth of incarnation 9)
11:14 Restore/recover to 10:45

The SET UNTIL TIME ’10:45′ command at 11:14 will result in a RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time. True, because incarnation 9 is assumed by RMAN, and there is no ‘life’ of incarnation 9 before 11:03.

You need to go to incarnation 8, because it’s life started at 10:21, reset time.

RMAN> LIST INCARNATION;
DB Key  Inc Key DB Name  DB ID       STATUS  Reset SCN  Reset Time
------- ------- -------- ----------- ------- ---------- ----------
(1..7 removed)
8       8       IANH     3640854324  PARENT  2892737    03-11-2009 10:21:42
9       9       IANH     3640854324  CURRENT 2896602    03-11-2009 11:03:05

RMAN> RESET DATABASE TO INCARNATION 8;

When restoring to 10:45 now, table X is included, because is was created in incarnation 8.

Conclusion

Remember an incarnation is created when you open the database with RESETLOGS. When you want to restore (especially when a RESETLOGS was issues not long before) remember to check these incarnations!

Database incarnations: Recovering through RESETLOGS, 4.7 out of 5 based on 3 ratings
Ratings:
VN:F [1.9.22_1171]
Rating: 4.7/5 (3 votes cast)

{ 6 comments… read them below or add one }

Eric March 24, 2011 at 9:57 pm

Thank you for all the information. It has been usefull.

Reply

Alex May 6, 2011 at 8:35 pm

very helpful in understanding resetlogs and incarnation. thank you!

Reply

Mike January 27, 2012 at 3:44 pm

Brilliant article, thanks a lot!

Reply

shah August 4, 2012 at 12:25 pm

i issued the command but give me this message
database reset to incarnation 2
ORA-19910: can not change recovery target incarnation in control file

Reply

Dhananjay November 23, 2017 at 7:25 am

Best Blog . It clear my concept about Incarnation.

Reply

Dhananjay November 23, 2017 at 7:32 am

Will you please explain category of Incarnation as well. like Current, Parent, Ancestor, Sibling.

Reply

Leave a Comment

 

{ 1 trackback }

Previous post:

Next post:

About Whitehorses
Company profile
Services
Technology

Whitehorses website

Home page
Whitebooks
Jobs

Follow us
Blog post RSS
Comment RSS
Twitter