Description:-
In this article we are going to see RMAN recovery through RESETLOGS.
Advantages of using Recovery Through Resetlogs feature:-
- 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.
Let’s start the Demo:-
Steps to perform RMAN recovery through RESETLOGS :-
To perform recovery through RESETLOGS you must have all archived logs generated after the most recent backup.
Step 1:- Check the log_archive format as ‘%r’
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.
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
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.
Step 2:-Let us check what the current incarnation of the database
[oracle@ram ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 16 19:08:39 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBWR (DBID=1337475478)
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 DBWR 1337475478 PARENT 1 04-FEB-19
2 2 DBWR 1337475478 PARENT 1880541 19-MAR-19
3 3 DBWR 1337475478 CURRENT 2410539 16-MAY-19
Step 3:- Note the CURRENT_SCN of the database.
RMAN> select current_scn from v$database;
CURRENT_SCN
-----------
2624738
Step 4:- 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.
Simulate the wrong DML execution,
SQL> conn hari/oracle
Connected.
SQL> select count(*) from emp;
COUNT(*)
----------
0
SQL> begin
for i in 1 .. 100000 loop
insert into emp values(i);
end loop;
end; 2 3 4 5
6 /
PL/SQL procedure successfully completed.
SQL> select count(*) from emp;
COUNT(*)
----------
100000
SQL> commit;
Commit complete.
SQL> delete from emp where rownum < 1001;
1000 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
99000
Step 5:- Shutdown and mount the database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1778381832 bytes
Fixed Size 8897544 bytes
Variable Size 536870912 bytes
Database Buffers 1224736768 bytes
Redo Buffers 7876608 bytes
Database mounted.
Step 6:- Now we will rollback the database to an SCN before the delete operation was performed.
RMAN> run
2> {
3> set until scn 2624738;
4> restore database;
5> recover database;
6> }
executing command: SET until clause
Starting restore at 16-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 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/app/oracle/oradata/DBWR/datafile/o1_mf_system_g91ppy2b_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/DBWR/datafile/dbwrtbs02.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_g91pxgmq_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_g91q0521_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/DBWR/datafile/dbwrtbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_g91q08bm_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/0ou1n6sh_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0ou1n6sh_1_1 tag=TAG20190516T185056
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:46
Finished restore at 16-MAY-19
Starting recover at 16-MAY-19
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 16-MAY-19
Step 7:- After open resetlogs,check the table count.
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 hari.emp;
COUNT(*)
---------
100000
Step 8:- Now let us check what 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 DBWR 1337475478 PARENT 1 04-FEB-19
2 2 DBWR 1337475478 PARENT 1880541 19-MAR-19
3 3 DBWR 1337475478 CURRENT 2410539 16-MAY-19
The current incarnation of the database is 9 and since we have recovered the database until SCN 2624738 , the RESET SCN has been set to the SCN 2410539.
The alert log file has lines below:
Incomplete Recovery applied until change 2624738 time 05/16/2019 19:09:29
2019-05-16T20:08:08.546732+05:30
Media Recovery Complete (dbwr)
2019-05-16T20:08:09.030828+05:30
Completed: alter database recover
if needed start until change 2624738
Step 9:- Reset incarnation of database.
RMAN> reset database to incarnation 3;
database reset to incarnation 3
Step 10:- Set the SCN number of incarnation 3 and try to restore & recover the database
RMAN> run
2> {
3> set until scn 2624738;
4> restore database;
5> recover database;
6> }
executing command: SET until clause
Starting restore at 16-MAY-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 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/app/oracle/oradata/DBWR/datafile/o1_mf_system_g91ppy2b_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/DBWR/datafile/dbwrtbs02.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_g91pxgmq_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_g91q0521_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/DBWR/datafile/dbwrtbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_g91q08bm_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/0ou1n6sh_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0ou1n6sh_1_1 tag=TAG20190516T185056
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:07
Finished restore at 16-MAY-19
Starting recover at 16-MAY-19
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-MAY-19
Step 11:- Open the database using resetlogs option
Now we open the database with the ALTER DATABASE OPEN RESETLOGS command and see that a new incarnation key (4) has been allocated to the database as the CURRENT incarnation.
SQL> alter database open resetlogs;
Database altered.
[oracle@ram trace]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 16 20:41:33 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBWR (DBID=1337475478)
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 DBWR 1337475478 PARENT 1 04-FEB-19
2 2 DBWR 1337475478 PARENT 1880541 19-MAR-19
3 3 DBWR 1337475478 PARENT 2410539 16-MAY-19
4 4 DBWR 1337475478 CURRENT 2624739 16-MAY-19
Step 12:- Check the CURRENT_SCN and recovered table count.
RMAN> select current_scn from v$database;
CURRENT_SCN
-----------
2625697
SQL> select count(*) from hari.emp;
COUNT(*)
----------
100000
The table recovered to the previous incarnation successfully and a new incarnation has been created.
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba