ORACLE DATABASE INCARNATION USING RMAN
Description:-
In this blog, we are going to see the Oracle database incarnation using RMAN backup with the demo.
INCARNATION:-
The current online redo logs are archived, the log sequence number is reset to 1, a new database incarnation is created, and the online redo logs are given a new timestamp and SCN.
Database incarnation falls into the following category:-
Current, Parent, Ancestor, and 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.
Overall steps:-
- Backup full database using RMAN
- After backup create a table in the database note the SCN number.
- Delete and Drop table take SCN number.
- Do log switching and check incarnation status.
- Bounce the database started in the mount stage.
- Connect RMAN and recover the database mention the sequence number
where the table record was deleted.
- After the recovery is completed perform resetlogs to open the database.
- Connect a database to check incarnation also check the table can be retrieved.
- Shut the database and start the mount stage again.
- Reset the database incarnation 2 to retrieve the table records mention the
scn where the table was created.
- Check record can be retrieved.
Step 1: Backup full database:-
[oracle@test ~]$ . livedb.env
[oracle@test ~]$ 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
Step 2: After backup create a table in the database note SCN number :-
[oracle@test ~]$ . livedb.env
[oracle@vtest ~]$ sqlplus / as sysdba
SQL> create table sample as select * from all_objects;
Table created.
SQL> select current_scn from v$database;
CURRENT_SCN
———–———–
2146558
Step 3: Delete and Drop table take SCN number:-
Delete the rows in the sample table:
SQL> delete from the sample;
71297 rows deleted.
Get the scn number also,
SQL> select current_scn from v$database;
CURRENT_SCN
———-———–
2146578
Drop the table structure:
SQL> drop table sample;
Table dropped.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
——————–
2146601
Step 4: Do log switching and check incarnation status:-
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
Step 5: Bounce the database start in mount stage:-
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1694495520 bytes
Fixed Size 8897312 bytes
Variable Size 402653184 bytes
Database Buffers 1275068416 bytes
Redo Buffers 7876608 bytes
Database mounted.
Step 6: Connect RMAN and recover the database mention the sequence number where the table record was deleted:-
[oracle@test ~]$ rman target /
connected to target database: LIVEDB (DBID=3038906043, not open)
RMAN> run{
set until scn=2146578;
restore database;
recover database;
}
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
Step 7: After the recovery is completed perform resetlogs to open the database:-
RMAN> alter database open resetlogs;
Statement processed
RMAN> exit
Step 8 : Connect database to check incarnation also check the table can be retrieved:-
[oracle@test ~]$ sqlplus / as sysdba
SQL> select incarnation#, resetlogs_change# from v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE#
———— —————–
1 1
2 1920977
3 2146579
The table structure is retrieved but the record can be deleted from the scn number stage.
SQL> select * from the sample;
no rows selected
Step 9: Shut the database and start the mount stage again:-
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1694495520 bytes
Fixed Size 8897312 bytes
Variable Size 402653184 bytes
Database Buffers 1275068416 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> exit
Step 10: Reset 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;
}
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
Step 11: Check the record can be retrieved from the table:-
[oracle@test ~]$ sqlplus / as sysdba
SQL> select count(1) from sample;
COUNT(1)
———-
71297
SQL>
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
Description :
In this blog, we are going to see the Oracle database restore point and its types with the demo.
Normal Restore Point:
