ORACLE DATABASE INCARNATION
The current online redo logs are archived, the log sequence number is reset to 1, new database incarnation is created, and the online redo logs are given a new time stamp and SCN.
Database incarnation falls into following category:-
Current, Parent, Ancestor, Sibling
i)
Current Incarnation : The database incarnation in which
the database is currently generating redo.
ii)
Parent Incarnation : The database incarnation from which
the current incarnation branched following an OPEN RESETLOGS operation.
iii)
Ancestor Incarnation : The parent of the parent
incarnation is an ancestor incarnation. Any parent of an ancestor incarnation
is also an ancestor incarnation.
iv) Sibling Incarnation : Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.
View incarnation history :-
Using SQL> select * from
v$database_incarnation;
Using RMAN>LIST INCARNATION;
RMAN> LIST INCARNATION;
List
of Database Incarnations
DB
Key Inc Key DB Name DB ID STATUS Reset SCN
Reset Time
-------
------- -------- ---------------- --- ---------- ----------
1 1
LIVEDB 3038906043 PARENT
1 17-APR-19
2 2
LIVEDB 3038906043 PARENT
1920977 13-SEP-21
4 4
LIVEDB 3038906043 CURRENT 2146559 15-SEP-21
3 3
LIVEDB 3038906043 ORPHAN
2146579 15-SEP-21
backup full database
[oracle@vignesh
~]$ . livedb.env
[oracle@vignesh ~]$ rman target /
connected to target database: LIVEDB (DBID=3038906043)
RMAN> backup database;
Starting
backup at 15-SEP-21
using
target database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=82 device type=DISK
channel
ORA_DISK_1: starting full datafile backup set
channel
ORA_DISK_1: specifying datafile(s) in backup set
input
datafile file number=00001
name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_system_jmz2qqys_.dbf
input
datafile file number=00004
name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_undotbs1_jmz2snrh_.dbf
input
datafile file number=00003
name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_sysaux_jmz2rvhs_.dbf
input
datafile file number=00007
name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_users_jmz2sovr_.dbf
channel
ORA_DISK_1: starting piece 1 at 15-SEP-21
channel
ORA_DISK_1: finished piece 1 at 15-SEP-21
piece
handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp
tag=TAG20210915T035943 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 15-SEP-21
Starting
Control File and SPFILE Autobackup at 15-SEP-21
piece
handle=/u01/app/oracle/fast_recovery_area/LIVEDB/autobackup/2021_09_15/o1_mf_s_1083297650_jn28ntoh_.bkp
comment=NONE
Finished Control File and SPFILE Autobackup at 15-SEP-21
RMAN> exit
After backup create a table in the database
[oracle@vignesh ~]$ . livedb.env
[oracle@vignesh ~]$ sqlplus / as sysdba
SQL> create table sample as select * from all_objects;
Table
created.
After that take current scn number
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2146558
Delete the rows in the sample table
SQL> delete from sample;
71297 rows deleted.
Get the scn number also
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2146578
Do the log switching
SQL> alter system switch logfile;
System altered.
Check the incarnation using v$database_incarnation
SQL> select incarnation#, resetlogs_change# from v$database_incarnation;
INCARNATION#
RESETLOGS_CHANGE#
------------
-----------------
1 1
2 1920977
Shutdown and start the database in mount stage
SQL> shutdown immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
startup mount
ORACLE instance started.
Database
mounted.
Connect RMAN and recover the database mention the sequence number where the table record deleted
[oracle@vignesh ~]$ rman target /
connected to target database: LIVEDB (DBID=3038906043, not open)
RMAN>
run{
set
until scn=2146578;
restore
database;
recover
database;
}2> 3> 4> 5>
executing command: SET until clause
Starting
restore at 15-SEP-21
using
target database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 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/LIVEDB/datafile/o1_mf_system_jmz2qqys_.dbf
channel
ORA_DISK_1: restoring datafile 00003 to
/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_sysaux_jmz2rvhs_.dbf
channel
ORA_DISK_1: restoring datafile 00004 to
/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_undotbs1_jmz2snrh_.dbf
channel
ORA_DISK_1: restoring datafile 00007 to
/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_users_jmz2sovr_.dbf
channel
ORA_DISK_1: reading from backup piece
/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp
channel
ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp
tag=TAG20210915T035943
channel
ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 15-SEP-21
Starting
recover at 15-SEP-21
using
channel ORA_DISK_1
starting
media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-SEP-21
After recovery completed perform resetlogs to open the database
RMAN>
alter database open resetlogs;
Statement processed
RMAN> exit
Connect database to check incarnation also check the table can be retrieved
[oracle@vignesh ~]$ sqlplus / as sysdba
SQL> select incarnation#, resetlogs_change# from v$database_incarnation;
INCARNATION#
RESETLOGS_CHANGE#
------------
-----------------
1 1
2 1920977
3 2146579
The table structure retrieved but record can be deleted the scn number stage
SQL> select * from sample;
no rows selected
Shut the database and start mount stage again
SQL>
shut immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
startup mount
ORACLE instance started.
Database
mounted.
SQL> exit
Connect RMAN again
[oracle@vignesh ~]$ rman target /
connected to target database: LIVEDB (DBID=3038906043, not open)
Reset the the database incarnation 2 to retrieve the table records mention the scn where table created
RMAN> reset database to incarnation 2;
using
target database control file instead of recovery catalog
database reset to incarnation 2
RMAN>
run{
set
until scn=2146558;
restore
database;
recover
database;
}2> 3> 4> 5>
executing command: SET until clause
Starting
restore at 15-SEP-21
allocated
channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 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/LIVEDB/datafile/o1_mf_system_jmz2qqys_.dbf
channel
ORA_DISK_1: restoring datafile 00003 to
/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_sysaux_jmz2rvhs_.dbf
channel
ORA_DISK_1: restoring datafile 00004 to
/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_undotbs1_jmz2snrh_.dbf
channel
ORA_DISK_1: restoring datafile 00007 to
/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_users_jmz2sovr_.dbf
channel
ORA_DISK_1: reading from backup piece
/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp
channel
ORA_DISK_1: piece
handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp
tag=TAG20210915T035943
channel
ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 15-SEP-21
Starting
recover at 15-SEP-21
using channel ORA_DISK_1
starting media recovery
archived
log for thread 1 with sequence 18 is already on disk as file
/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_15/o1_mf_1_18_jn28r8l8_.arc
archived
log file
name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_15/o1_mf_1_18_jn28r8l8_.arc
thread=1 sequence=18
media
recovery complete, elapsed time: 00:00:02
Finished recover at 15-SEP-21
Perform reset logs method
RMAN> alter database open resetlogs;
Statement processed
RMAN> exit
Check the record can be retrieved the table
[oracle@vignesh ~]$ sqlplus / as sysdba
SQL> select count(1) from sample;
COUNT(1)
----------
71297
No comments:
Post a Comment