Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 29 October 2012

Resetting or Recovering the Database with old incarnation

If you run the RMAN command or the SQL statement ALTER DATABASE OPEN RESETLOGS (after incomplete recovery), then a new database incarnation record is automatically created in the recovery catalog (Use V$DATABASE_INCARNATION). The database also implicitly and automatically issues a RESET DATABASE command (in case when you perform ALTER DATABASE OPEN RESETLOGS from SQL prompt, you must need to run RESET DATABASE with RMAN catalog), which specifies that this new incarnation of the database is the current incarnation. All subsequent backups and log archiving done by the target database is associated with the new database incarnation.
Prior to Oracle 10g in that case you can not use your old backup then it was important to take a full database backup. Here in this case you may need to change the current incarnation of the database in the recovery catalog for some recovery tasks.
-         No matter if you do not perform full backup after an incomplete recovery.
-         You can take incremental backup based on full backup of previous incarnation through rman.
-         Thus there is no need to recreate a new standby database.
-         You can use newly generated logs with an earlier incarnation of database.
Oracle 10g introduces a new format specification for archive log files. This new format avoids overwriting archive with the same sequence number across incarnation.
SQL> show parameter log_archive_format
NAME TYPE VALUE
-------------------- ----------- -----------
log_archive_format string %t_%s_%r.dbf
Here “%r” specifies the resetlog id, will ensure the unique archivelog. The database would not start if you remove the “%r” from log archive format.
Note: To perform recovery through RESETLOGS you must have all archived logs generated after the most recent backup.
CONNECT RMAN catalog with the target database:
RMAN target sys/****@sadhan.world catalog catalog/catalog@rman
RMAN> LIST INCARNATION;
RMAN> LIST INCARNATION of database SADHAN;
Reset the database to the old incarnation. For example:
RMAN> RESET DATABASE TO INCARNATION 2;
If the control file of the previous incarnation is available and mounted, then skip to step 2 of this procedure. Otherwise, shut down the database and start it without mounting.
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
Restore a control file from the old incarnation. If you have a control file tagged, then specify the tag. Otherwise, you can run the SET UNTIL command, as in this example:
RUN
{
  SET UNTIL 'SYSDATE-245';
  RESTORE CONTROLFILE; # only if current control file is not available
}
ALTER DATABASE MOUNT;
LIST INCARNATION OF DATABASE trgt;
RESET DATABASE TO INCARNATION 2;
# Restore and recover the database to a point before the RESETLOGS
RESTORE DATABASE UNTIL SCN 154876;
RECOVER DATABASE UNTIL SCN 154876;
# Make this incarnation the current incarnation and then list incarnations:
ALTER DATABASE OPEN RESETLOGS;
LIST INCARNATION OF DATABASE trgt;
Step2: Mount the restored control file (if current control file is available)
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
Restrictions and Usage Notes:
·        Execute RESET DATABASE only at the RMAN prompt.
·        You must be connected to the target database.
·        A recovery catalog connection is optional. Unlike in catalog mode, RESET DATABASE in nocatalog mode changes the incarnation only for the current RMAN session.
·        You must issue a RESET DATABASE command before you can use RMAN with a target database that has been opened with the SQL statement ALTER DATABASE OPEN RESETLOGS option. If you do not, then RMAN refuses to access the recovery catalog because it cannot distinguish between a RESETLOGS operation and an accidental restore of an old control file. The RESET DATABASE command informs RMAN that you issued a RESETLOGS command.
·        If RMAN is connected NOCATALOG, then you can only specify TO INCARNATION if the database is mounted and the control file contains a record of the prior incarnation. If you do not run RESET DATABASE, RMAN recovers to the last incarnation recorded in the control file.
·        If RMAN is connected in CATALOG mode, then you can specify TO INCARNATION when the database is mounted. If database is mounted, however, then the control file must have a record of the prior incarnation.
For Example:
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       SADHAN   63198018        PARENT  1          07-JUL-12
2       2       SADHAN   63198018        CURRENT 435363     03-OCT-12
At this point find the current SCN of the database:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
     452896
Now, I am doing a change in the database which I will try to restore and recover with a point in time recovery.
SQL> conn hrms/hrms
Connected.
SQL> select * from partial_payment_sequence where seqcod = 57;
  COUNT(*)
----------
    887
SQL> delete from partial_payment_sequence where Employee_ Number < 3500;
87 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from partial_payment_sequence where seqcod = 57;
  COUNT(*)
----------
    800
Now we will perform incomplete recovery to 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;
$ rman target sys/***@sadhan.world catalog catalog/catalog@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Oct 23 10:31:07 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: SADHAN (DBID=63198018, not open)

RMAN> run {
set until scn 452896;
restore database;
recover database;
}
executing command: SET until clause
Starting restore at 23-OCT-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\SADHAN\UNDOTBS01.DBF
restoring datafile 00009 to G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF
….
….
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2984_1.DB tag=SADHAN_FULL_D
Finished restore at 23-OCT-12

Starting recover at 23-OCT-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-OCT-12

RMAN> alter database open resetlogs;
database opened
You can check to 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 * from partial_payment_sequence where seqcod = 57;
  COUNT(*)
----------
    887
Now we will check what the incarnation of the database is.
RMAN> list incarnation of database;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       SADHAN   63198018        PARENT  1               07-JUL-12
2       2       SADHAN   63198018        PARENT  435363     03-OCT-12
3       3       SADHAN   63198018        CURRENT 452896   23-OCT-12
Now the current incarnation of the database is 3 and since we have recovered until SCN 452896, so the RESET SCN has been set to the SCN 452896.
We can also check to see this resetlogs operation in alert.log:
Incomplete Recovery applied until change 452896 time 10/23/2012 10:30:19
Media Recovery Complete (SADHAN)
Completed: alter database recover if needed
start until change 452896
Tue Oct 23 10:30:14 2012
alter database open resetlogs
Archived Log entry 3 added for thread 1 sequence 5 ID 0x36a3e663 dest 1:
RESETLOGS after incomplete recovery UNTIL CHANGE 452896
Setting recovery target incarnation to 3
Tue Oct 23 10:30:15 2012
Assigning activation ID 63198018 (0x36a4c5df)

Again make the same delete and try to restore with the same SCN (452896) in the same way.

SQL> delete from partial_payment_sequence where Employee_ Number < 3500;
87 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from partial_payment_sequence where seqcod = 57;
  COUNT(*)
----------
    800
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
$ rman target sys/***@sadhan.world catalog catalog/catalog@rman
RMAN> run {
set until scn 452896;
restore database;
recover database;
}
executing command: SET until clause
Starting restore at 23-OCT-12
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/23/2012 10:43:53
RMAN-20208: UNTIL CHANGE is before RESETLOGS change

We are getting 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 to go back to SCN 452896, we need to change the current incarnation (3) of the database to an older incarnation (2).

RMAN> reset database to incarnation 2;
database reset to incarnation 2
RMAN>  run {
set until scn 452896;
restore database;
recover database;
}

executing command: SET until clause
Starting restore at 23-OCT-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\SADHAN\UNDOTBS01.DBF
restoring datafile 00009 to G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF
….
….
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2984_1.DB tag=SADHAN_FULL_D
Finished restore at 23-OCT-12

Starting recover at 23-OCT-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-OCT-12

RMAN> alter database open resetlogs;
database opened

RMAN> list incarnation of database;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       SADHAN   63198018        PARENT  1          07-JUL-12
2       2       SADHAN   63198018        PARENT  435363     03-OCT-12
3       3       SADHAN   63198018        ORPHAN  452896     23-OCT-12
4       4       SADHAN   63198018        CURRENT 452896     23-OCT-12

After resetting the incarnation to 2, we now see that the restore is proceeding fine.
Now what will happen if you are doing the same procedure with the same scn (SCN – 452896) once again (deleting some rows by resetting the incarnation 2)?
After performing recovery and opening the database with resetlogs option then try to list the incarnation.
RMAN> list incarnation of database;
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       SADHAN   63198018        PARENT  1          07-JUL-12
2       2       SADHAN   63198018        PARENT  435363     03-OCT-12
3       3       SADHAN   63198018        ORPHAN  452896     23-OCT-12
4       4       SADHAN   63198018        ORPHAN  452896     23-OCT-12
5       5       SADHAN   63198018        CURRENT 452896     23-OCT-12

Now the incarnation key 5 is the CURRENT incarnation, and incarnations 3 and 4 both have become ORPHAN.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     453140
At this point if we change structure of database such as adding third datafile to the tablespace SDH_TIMS_DBF, so the current control file has the knowledge of that SDH_TIMS_DBF has not two but three dbf.
We now try and do the same delete of rows and try and go back to our old SCN 452896. At this point in time, the control file had only knowledge of the fact that the SDH_TIMS_DBF tablespace has two data file and not three and the restore and recovery process will not try to do anything with the newly added datafile.

1 comments:

  1. TeethNightGuard is offering personalized fitting and high quality customized teeth protectors.

    ReplyDelete