Prior to Oracle 10g, the redo log files generated
after opening the database with RESETLOGS could not be used with the backups
taken before the RESETLOGS was performed.
Therefore, whenever a resetlogs was done, it was important to take
an immediate full database backup, since all previous backups became invalid.
Just to recap, a RESETLOGS needs to be performed when we
1) Do a point in time
recovery
What does a RESETLOGS do?
• Archives the
current online redo logs (if they are accessible) and then erases the contents
of the online redo logs and resets the log sequence number to 1.
The Recovery through Resetlogs feature provides the
following benefits:
* There is no need to perform a full backup after an incomplete
recovery.
Important:
To perform recovery through RESETLOGS you must have all archived
logs generated after the most recent backup.
The new log_archive_format introduced in 10g
Oracle 10g introduces a new format specification for archived log
files. This new format avoids overwriting archived redo log files with the same
sequence number across incarnations.
SQL> show parameter log_archive_format
The format specification of the log_archive_format string
"%"r represents the resetlogs id. It will ensure that a unique name
is constructed for the archived redo log file during RMAN restore and as well
as restoring via SQL*plus auto recovery mode.
Note: The database would not start if you remove the %r from the
log archive format specification.
What is an incarnation?
A database incarnation is created whenever you open the database
with the RESETLOGS option.
The Current incarnation is the one in which the database is running
at present
The incarnation from which the current incarnation branched after
a ALTER DATABASE OPEN RESETLOGS was
performed is called the Parent incarnation.
If you want to go back to some SCN which is not part of the
Current database incarnation, we need to use the RESET DATABASE TO
INCARNATION
The purpose of incarnations
An incarnation helps to identify redo streams which have the same
SCN, but occurred at different points in time. This prevents the possibility of
applying the wrong archive log file from a previous incarnation which could
corrupt the database.
Suppose we are at incarnation 1 and are at SCN 100 in the
database. I do a resetlogs and now the incarnation of the database becomes 2.
Suppose we do another resetlogs and it so happens that the SCN at the time we
did the resetlogs was also 100. Somewhere down the line later we want to do a point in time recovery and
want to recover the database until SCN 100. How does Oracle know which is the
correct SCN and how far back in time should the database be rolled back to and
which archive logs need to be applied?
This is where incarnations come in and we will see how we can set
the database to a particular incarnation to enable RMAN to do a point in time
recovery.
Example
Let us now run the LIST INCARNATION OF DATABASE command from RMAN
and this is the output.
List of Database Incarnations
What is the current SCN of the database?
SQL> select current_scn from v$database;
CURRENT_SCN
Now, let us make a change in the database which we will then try
to undo by restoring and recovering the
database to a point in time before the media failure or as in this case, a
wrong transaction has happened.
SQL> conn sh/sh
SQL> select count(*) from sales;
COUNT(*)
SQL> delete from sales where rownum < 1001;
1000 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from sales;
COUNT(*)
We now will roll the database back to an SCN before the delete
operation was performed.
SQL> conn / as sysdba
[oracle@orasql-001 u01]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 18
09:41:08 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST11G (DBID=916751459, not open)
RMAN> run {
executing command: SET until clause
Starting restore at 18-OCT-11
….
channel ORA_DISK_1: restored backup piece 1
Starting recover at 18-OCT-11
starting media recovery
Finished recover at 18-OCT-11
RMAN> alter database open resetlogs;
database opened
We can now see that the deleted rows have been recovered and the
number of rows in the table is now the same as before the delete operation was
performed.
SQL> select count(*) from sh.sales;
COUNT(*)
Let us check what the incarnation of the database is.
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
The current incarnation of the database is 3 and since we have
recovered the database until SCN 991395, the RESET SCN has been set to the SCN
991396.
The alert log of the database will also log this resetlogs
operation and we will see lines the ones shown below:
Incomplete Recovery applied until change 991395 time 10/18/2011
09:39:19
Setting recovery target incarnation to 3
Now let us make another delete from the sales table.
SQL> delete from sh.sales where rownum < 10001;
10000 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from sh.sales;
COUNT(*)
Now, let us shutdown the database, startup and mount it and try
the same incomplete recovery which we tried earlier until the SCN 991395
oracle@orasql-001 u01]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 18
09:46:29 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST11G (DBID=916751459, not open)
RMAN> run {
executing command: SET until clause
Starting restore at 18-OCT-11
We get the error RMAN-20208 because since the current incarnation
of the database is 3 and we trying to go to an SCN before this incarnation.
So how do we go back to SCN 993195?
For this to happen, we need to change the current incarnation (3)
of the database to an older incarnation (2). The SCN 993195 was present during
the incarnation 2.
After resetting the incarnation to 2, we now see that the restore
is proceeding fine.
RMAN> reset database to incarnation 2;
database reset to incarnation 2
RMAN> run {
executing command: SET until clause
Starting restore at 18-OCT-11
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Starting recover at 18-OCT-11
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as
file /u01/oradata/TEST11G/arch/1_4_764845989.arch
Now we open the database with the ALTER DATABASE OPEN RESETOGS
command and see that a new incarnation key (4) has been allocated to the
database as the CURRENT incarnation and the previous incarnation (3) has become
an ORPHAN incarnation.
RMAN> list incarnation of database;
List of Database Incarnations
Now let us do the same procedure once again:
Delete some rows
Now list the incarnation of the database
RMAN> list incarnation of database;
List of Database Incarnations
The incarnation key 5 now is the CURRENT incarnation of the
database, and incarnations 3 and 4 both have become ORPHAN.
SQL> select current_scn from v$database;
CURRENT_SCN
What happens if we change the structure of the database? And we do not take any new backup after this
change was made, but try and recover using a backup before we had changed the
structure of the database. We add another datafile to the USERS tablespace. So
the current control file has knowledge of the fact that the USERS tablespace now has not one, but two
datafiles.
We now try and do the same delete of rows and try and go back to
our old SCN 991395. At this point in time, the control file had only knowledge
of the fact that the USERS tablespace has one data file and not two and the
restore and recovery process will not try to do anything with regards to the
newly added datafile..
This is what we see in the alert log of the database. Note that
datafile 6 which is the new datafile added is being taken offline and dropped
because at SCN 991735, this datafile did not exist in the database.
Wed Oct 19 10:03:42 2011
Dictionary check beginning
Prior to Oracle 10g, the redo log files generated after opening the database with RESETLOGS could not be used with the backups taken before the RESETLOGS was performed.
Therefore, whenever a resetlogs was done, it was important to take
an immediate full database backup, since all previous backups became invalid.
Just to recap, a RESETLOGS needs to be performed when we
1) Do a point in time
recovery
2) Recover a database
using a backup of the control file
What does a RESETLOGS do?
• Archives the
current online redo logs (if they are accessible) and then erases the contents
of the online redo logs and resets the log sequence number to 1.
• Creates the
online redo log files if they do not currently exist.
• Updates all
current datafiles and online redo logs and all subsequent archived redo logs
with a new RESETLOGS SCN and time stamp
The Recovery through Resetlogs feature provides the
following benefits:
* There is no need to perform a full backup after an incomplete
recovery.
* There is no need to recreate a new standby database after a
failover operation.
* You can take incremental backups based on full backups of a
previous incarnation when you use RMAN.
* Block media recovery can restore backups from parent incarnation
backups and recover the corrupted blocks through a RESETLOGS operation.
* You can use the newly generated logs with an earlier incarnation
of the database.
Important:
To perform recovery through RESETLOGS you must have all archived
logs generated after the most recent backup.
The new log_archive_format introduced in 10g
Oracle 10g introduces a new format specification for archived log
files. This new format avoids overwriting archived redo log files with the same
sequence number across incarnations.
SQL> show parameter log_archive_format
NAME TYPE VALUE
----------------------- ----------- ----------------
log_archive_format string %t_%s_%r.dbf
The format specification of the log_archive_format string
"%"r represents the resetlogs id. It will ensure that a unique name
is constructed for the archived redo log file during RMAN restore and as well
as restoring via SQL*plus auto recovery mode.
Note: The database would not start if you remove the %r from the
log archive format specification.
What is an incarnation?
A database incarnation is created whenever you open the database
with the RESETLOGS option.
The Current incarnation is the one in which the database is running
at present
The incarnation from which the current incarnation branched after
a ALTER DATABASE OPEN RESETLOGS was
performed is called the Parent incarnation.
If you want to go back to some SCN which is not part of the
Current database incarnation, we need to use the RESET DATABASE TO
INCARNATION
command as shown in the example below
The purpose of incarnations
An incarnation helps to identify redo streams which have the same
SCN, but occurred at different points in time. This prevents the possibility of
applying the wrong archive log file from a previous incarnation which could
corrupt the database.
Suppose we are at incarnation 1 and are at SCN 100 in the
database. I do a resetlogs and now the incarnation of the database becomes 2.
Suppose we do another resetlogs and it so happens that the SCN at the time we
did the resetlogs was also 100. Somewhere down the line later we want to do a point in time recovery and
want to recover the database until SCN 100. How does Oracle know which is the
correct SCN and how far back in time should the database be rolled back to and
which archive logs need to be applied?
This is where incarnations come in and we will see how we can set
the database to a particular incarnation to enable RMAN to do a point in time
recovery.
Example
Let us now run the LIST INCARNATION OF DATABASE command from RMAN
and this is the output.
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN
Reset Time
------- ------- -------- ---------------- --- ----------
----------
1 1 TEST11G
916751459 PARENT 1
15-AUG-09
2 2 TEST11G
916751459 CURRENT
945184 18-OCT-11
What is the current SCN of the database?
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
991395
Now, let us make a change in the database which we will then try
to undo by restoring and recovering the
database to a point in time before the media failure or as in this case, a
wrong transaction has happened.
SQL> conn sh/sh
Connected.
SQL> select count(*) from sales;
COUNT(*)
----------
918843
SQL> delete from sales where rownum < 1001;
1000 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from sales;
COUNT(*)
----------
917843
We now will roll the database back to an SCN before the delete
operation was performed.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
[oracle@orasql-001 u01]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 18
09:41:08 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST11G (DBID=916751459, not open)
RMAN> run {
2> set until scn 991395;
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 18-OCT-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup
set
channel ORA_DISK_1: restoring datafile 00001 to
/u01/oradata/TEST11G/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to
/u01/oradata/TEST11G/sysaux01.dbf
….
….
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 18-OCT-11
Starting recover at 18-OCT-11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-OCT-11
RMAN> alter database open resetlogs;
database opened
We can now see that the deleted rows have been recovered and the
number of rows in the table is now the same as before the delete operation was
performed.
SQL> select count(*) from sh.sales;
COUNT(*)
----------
918843
Let us check what the incarnation of the database is.
RMAN> list incarnation of database;
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 TEST11G
916751459 PARENT 1
15-AUG-09
2 2 TEST11G
916751459 PARENT 945184
18-OCT-11
3 3 TEST11G
916751459 CURRENT
991396 18-OCT-11
The current incarnation of the database is 3 and since we have
recovered the database until SCN 991395, the RESET SCN has been set to the SCN
991396.
The alert log of the database will also log this resetlogs
operation and we will see lines the ones shown below:
Incomplete Recovery applied until change 991395 time 10/18/2011
09:39:19
Media Recovery Complete (TEST11G)
Completed: alter database recover if needed
start until change 991395
Tue Oct 18 09:42:14 2011
alter database open resetlogs
Archived Log entry 3 added for thread 1 sequence 5 ID 0x36a3e663
dest 1:
RESETLOGS after incomplete recovery UNTIL CHANGE 991395
Setting recovery target incarnation to 3
Tue Oct 18 09:42:15 2011
Assigning activation ID 916768223 (0x36a4c5df)
Now let us make another delete from the sales table.
SQL> delete from sh.sales where rownum < 10001;
10000 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from sh.sales;
COUNT(*)
----------
908843
Now, let us shutdown the database, startup and mount it and try
the same incomplete recovery which we tried earlier until the SCN 991395
oracle@orasql-001 u01]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 18
09:46:29 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST11G (DBID=916751459, not open)
RMAN> run {
2> set until scn 991395;
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 18-OCT-11
using target database control file instead of recovery catalog
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571:
===========================================================
RMAN-03002: failure of restore command at 10/18/2011 09:46:55
RMAN-20208: UNTIL CHANGE is before RESETLOGS change
We get the error RMAN-20208 because since the current incarnation
of the database is 3 and we trying to go to an SCN before this incarnation.
So how do we go back to SCN 993195?
For this to happen, we need to change the current incarnation (3)
of the database to an older incarnation (2). The SCN 993195 was present during
the incarnation 2.
After resetting the incarnation to 2, we now see that the restore
is proceeding fine.
RMAN> reset database to incarnation 2;
database reset to incarnation 2
RMAN> run {
2> set until scn 991395;
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 18-OCT-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup
set
channel ORA_DISK_1: restoring datafile 00001 to
/u01/oradata/TEST11G/system01.dbf
…
…
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 18-OCT-11
Starting recover at 18-OCT-11
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as
file /u01/oradata/TEST11G/arch/1_4_764845989.arch
archived log for thread 1 with sequence 5 is already on disk as
file /u01/oradata/TEST11G/arch/1_5_764845989.arch
archived log file
name=/u01/oradata/TEST11G/arch/1_4_764845989.arch thread=1 sequence=4
archived log file name=/u01/oradata/TEST11G/arch/1_5_764845989.arch
thread=1 sequence=5
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-OCT-11
Now we open the database with the ALTER DATABASE OPEN RESETOGS
command and see that a new incarnation key (4) has been allocated to the
database as the CURRENT incarnation and the previous incarnation (3) has become
an ORPHAN incarnation.
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN
Reset Time
------- ------- -------- ---------------- --- ----------
----------
1 1 TEST11G
916751459 PARENT 1
15-AUG-09
2 2 TEST11G
916751459 PARENT 945184
18-OCT-11
3 3 TEST11G 916751459 ORPHAN
991396 18-OCT-11
4 4 TEST11G
916751459 CURRENT
991396 18-OCT-11
Now let us do the same procedure once again:
Delete some rows
Set the database to incarnation 2
Recover the database until the same SCN as previous case – 991395
Open the database with resetlogs
Now list the incarnation of the database
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN
Reset Time
------- ------- -------- ---------------- --- ----------
----------
1 1 TEST11G
916751459 PARENT 1
15-AUG-09
2 2 TEST11G
916751459 PARENT 945184
18-OCT-11
4 4 TEST11G
916751459 ORPHAN 991396
18-OCT-11
5 5 TEST11G
916751459 CURRENT
991396 18-OCT-11
3 3 TEST11G
916751459 ORPHAN 991396
18-OCT-11
The incarnation key 5 now is the CURRENT incarnation of the
database, and incarnations 3 and 4 both have become ORPHAN.
The current SCN now is 991734
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
991734
What happens if we change the structure of the database? And we do not take any new backup after this
change was made, but try and recover using a backup before we had changed the
structure of the database. We add another datafile to the USERS tablespace. So
the current control file has knowledge of the fact that the USERS tablespace now has not one, but two
datafiles.
We now try and do the same delete of rows and try and go back to
our old SCN 991395. At this point in time, the control file had only knowledge
of the fact that the USERS tablespace has one data file and not two and the
restore and recovery process will not try to do anything with regards to the
newly added datafile..
This is what we see in the alert log of the database. Note that
datafile 6 which is the new datafile added is being taken offline and dropped
because at SCN 991735, this datafile did not exist in the database.
Wed Oct 19 10:03:42 2011
alter database datafile 6 offline drop
Completed: alter database datafile 6 offline drop
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover datafile list
1 , 2 , 3 , 4 , 5
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5
alter database recover if needed
start until change 991735
Dictionary check beginning
File #6 in the controlfile not found in data dictionary.
Removing file from controlfile.
data file 6: '/u01/oradata/TEST11G/users02.dbf'
Dictionary check complete
