Oracle 19c RMAN Restore / Recover to a Previous Incarnation
In this article we will restore and recover database to it’s previous incarnations.
This article assumes that DBA has executed restore/recover database at wrong scn and then he tried to go back to previous SCN post database open resetlogs.
Step 1=> Get current status of database as below.
[oracle@racsetn1 ~]$ rman target /
connected to target database: NTNT (DBID=3125572952)
RMAN> list incarnation;
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 NTNT 3125572952 PARENT 1 17-APR-19
2 2 NTNT 3125572952 CURRENT 1920977 17-DEC-22
Step 2=> Take database backup
[oracle@racsetn1 bkp]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Dec 21 17:35:47 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: NTNT (DBID=3125572952)
RMAN> RUN
{
ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;
BACKUP
FORMAT '/ora_home/bkp/%d_D_%T_%u_s%s_p%p'
DATABASE
CURRENT CONTROLFILE
FORMAT '/ora_home/bkp/%d_C_%T_%u'
SPFILE
FORMAT '/ora_home/bkp/%d_S_%T_%u'
PLUS ARCHIVELOG
FORMAT '/ora_home/bkp/%d_A_%T_%u_s%s_p%p';
RELEASE CHANNEL ch11;
}
input archived log thread=2 sequence=1 RECID=1 STAMP=1123699835
input archived log thread=2 sequence=2 RECID=2 STAMP=1124035935
input archived log thread=1 sequence=6 RECID=4 STAMP=1124035946
input archived log thread=2 sequence=3 RECID=5 STAMP=1124039396
…
piece handle=/ora_home/bkp/NTNT_A_20221221_081fv084_s8_p1 tag=TAG20221221T173603 comment=NONE
…
piece handle=/ora_home/bkp/NTNT_D_20221221_091fv08c_s9_p1 tag=TAG20221221T173612 comment=NONE
..
piece handle=/ora_home/bkp/NTNT_C_20221221_0b1fv08l tag=TAG20221221T173612
….
channel ch11: backup set complete, elapsed time: 00:00:01
Finished backup at 21-DEC-22
released channel: ch11
Step 3=> Create Sample table and insert data and record SCN as below,
[oracle@racsetn1 bkp]$ sqlplus ABHI_TEST/Oracle_4U
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2194658
SQL> create table Sample_incar (id int , stamp timestamp);
Table created.
SQL> insert into SAMPLE_INCAR Values (1,systimestamp);
1 row created.
…
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2194804
SQL>
SQL> set lines 500
SQL> /
ID STAMP
---------- ---------------------------------------------------------------------------
1 21-DEC-22 05.37.58.699188 PM
2 21-DEC-22 05.38.36.863260 PM
3 21-DEC-22 05.38.48.497741 PM
4 21-DEC-22 05.39.04.694198 PM
SQL>
Step 4=> Delete record from Sample table and record SCN as below,
SQL> delete from SAMPLE_INCAR;
4 rows deleted.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2195667
SQL>
Step 5=> Drop Sample table and record SCN as below,
SQL> drop table SAMPLE_INCAR;
Table dropped.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2195733
SQL> exit
Step 6=> We will Wrongfully perform PITR for database till 2195667 (post execution of DELETE statement) as below.
1=> Shutdown database and start in NOMOUNT to restore spfile as below
[oracle@racsetn1 bkp]$ sqlplus "/as sysdba"
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2751463160 bytes
Fixed Size 8900344 bytes
Variable Size 637534208 bytes
Database Buffers 2097152000 bytes
Redo Buffers 7876608 bytes
SQL> exit
[oracle@racsetn1 bkp]$ rman target /
connected to target database: NTNT (not mounted)
RMAN> restore spfile to '/tmp/spfile.ora' from '/ora_home/bkp/NTNT_S_20221221_0c1fv08q';
Starting restore at 21-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 instance=NTNT1 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /ora_home/bkp/NTNT_S_20221221_0c1fv08q
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 21-DEC-22
RMAN> exit
2=> Edit PFILE and set cluster_Database= FALSE and start database in again NOMOUNT as below .
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"
SQL> startup nomount pfile='/tmp/spfile.ora';
ORACLE instance started.
Total System Global Area 2751463160 bytes
Fixed Size 8900344 bytes
Variable Size 637534208 bytes
Database Buffers 2097152000 bytes
Redo Buffers 7876608 bytes
SQL> exit
3=> Restore controlfile and mount database as below.
[oracle@racsetn1 ~]$ rman target /
connected to target database: NTNT (not mounted)
RMAN> restore controlfile from '/ora_home/bkp/NTNT_C_20221221_0b1fv08l';
Starting restore at 21-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=268 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA/NTNT/CONTROLFILE/current.281.1123698521
output file name=+FRA/NTNT/CONTROLFILE/current.793.1123698521
Finished restore at 21-DEC-22
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 NTNT 3125572952 PARENT 1 17-APR-19
2 2 NTNT 3125572952 CURRENT 1920977 17-DEC-22
4=> Restore database upto SCN 2195667
RMAN> RUN
{
set until scn=2195667;
RESTORE DATABASE;
RECOVER DATABASE;
}2> 3> 4> 5> 6>
executing command: SET until clause
Crosschecked 3 objects
Finished implicit crosscheck backup at 21-DEC-22
Starting implicit crosscheck copy at 21-DEC-22
using channel ORA_DISK_1
Finished implicit crosscheck copy at 21-DEC-22
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +FRA/NTNT/ARCHIVELOG/2022_12_21/thread_1_seq_10.801.1124040989
File Name: +FRA/NTNT/ARCHIVELOG/2022_12_21/thread_2_seq_6.806.1124040991
File Name: +FRA/NTNT/ARCHIVELOG/2022_12_21/thread_1_seq_11.805.1124041381
using channel ORA_DISK_1
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: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 21-DEC-22
Starting recover at 21-DEC-22
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 10 is already on disk as file
…..
archived log file name=+FRA/NTNT/ARCHIVELOG/2022_12_21/thread_1_seq_11.805.1124041381 thread=1 sequence=11
archived log file name=+DATA/NTNT/ONLINELOG/group_3.279.1123699801 thread=2 sequence=7
media recovery complete, elapsed time: 00:00:03
Finished recover at 21-DEC-22
5=> Open DATABASE with resetlogs as below.
[oracle@racsetn1 ~]$ rman target /
connected to target database: NTNT (DBID=3125572952, not open)
RMAN> ALTER DATABASE OPEN RESETLOGS;
using target database control file instead of recovery catalog
Statement processed
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 NTNT 3125572952 PARENT 1 17-APR-19
2 2 NTNT 3125572952 PARENT 1920977 17-DEC-22
3 3 NTNT 3125572952 CURRENT 2195668 21-DEC-22
RMAN> exit
Recovery Manager complete.
[oracle@racsetn1 ~]$
Step 7=> Now verify data using SAMPLE schema as below, As checked no data available due to Wrong SCN restore values.
[oracle@racsetn1 ~]$ sqlplus ABHI_TEST/Oracle_4U
SQL> select * from SAMPLE_INCAR;
no rows selected
SQL> desc SAMPLE_INCAR;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
STAMP TIMESTAMP(6)
Step 8=> Let we try to go to backward in SCN and restore/recover until 2194804 (when data was inserted in SAMPLE table).
1=> Shutdown current running database .
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
2=> Startup database in Mount Mode (using same earlier restored control_file)
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"
SQL> startup mount pfile='/tmp/spfile.ora';
ORACLE instance started.
Total System Global Area 2751463160 bytes
Fixed Size 8900344 bytes
Variable Size 637534208 bytes
Database Buffers 2097152000 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> exit
3=> Restore and Recover database before current INCARNATION as below.
[oracle@racsetn1 ~]$ rman target /
connected to target database: NTNT (DBID=3125572952, not open)
RMAN> list incarnation;
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 NTNT 3125572952 PARENT 1 17-APR-19
2 2 NTNT 3125572952 PARENT 1920977 17-DEC-22
3 3 NTNT 3125572952 CURRENT 2195668 21-DEC-22
RMAN> RUN
{
set until scn=2194804;
RESTORE DATABASE;
RECOVER DATABASE;
}
2> 3> 4> 5> 6>
executing command: SET until clause
Starting restore at 21-DEC-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/21/2022 18:08:49
RMAN-20208: UNTIL CHANGE is before RESETLOGS change
We are getting this ERROR because we are trying to restore/recover database before to RESETLOGS SCN.
As current resetlog scn is 2195668 and we are trying to restore/recover till 2194804 which is lower that current RESETLOG SCN.
STEP 9=> Let we RESET DATABASE INCARNATION to lower to current restore/recover SCN as below.
[oracle@racsetn1 ~]$ rman target /
connected to target database: NTNT (DBID=3125572952, not open)
RMAN> list incarnation;
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 NTNT 3125572952 PARENT 1 17-APR-19
2 2 NTNT 3125572952 PARENT 1920977 17-DEC-22
3 3 NTNT 3125572952 CURRENT 2195668 21-DEC-22
RMAN> reset database to incarnation 2;
database reset to incarnation 2
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 NTNT 3125572952 PARENT 1 17-APR-19
2 2 NTNT 3125572952 CURRENT 1920977 17-DEC-22
3 3 NTNT 3125572952 ORPHAN 2195668 21-DEC-22
STEP 10=> Let we restore/recover database until SCN as below and Open Resetlogs (Using same controlfile as above)
RMAN> RUN
{
set until scn=2194804;
RESTORE DATABASE;
RECOVER DATABASE;
}
2> 3> 4> 5> 6>
executing command: SET until clause
Starting restore at 21-DEC-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
…
Finished restore at 21-DEC-22
Starting recover at 21-DEC-22
using channel ORA_DISK_1
starting media recovery
..
…
media recovery complete, elapsed time: 00:00:02
Finished recover at 21-DEC-22
RMAN> alter database open resetlogs;
Statement processed
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 NTNT 3125572952 PARENT 1 17-APR-19
2 2 NTNT 3125572952 PARENT 1920977 17-DEC-22
4 4 NTNT 3125572952 CURRENT 2194805 21-DEC-22
3 3 NTNT 3125572952 ORPHAN 2195668 21-DEC-22
RMAN> exit
Recovery Manager complete.
Step 11=> Verify Sample data as below.
[oracle@racsetn1 ~]$ sqlplus ABHI_TEST/Oracle_4U
SQL> set lines 500
SQL> select * from SAMPLE_INCAR;
ID STAMP
---------- ---------------------------------------------------------------------------
1 21-DEC-22 05.37.58.699188 PM
2 21-DEC-22 05.38.36.863260 PM
3 21-DEC-22 05.38.48.497741 PM
4 21-DEC-22 05.39.04.694198 PM
SQL>
Hence we have traversed back to ANCESTRAL SCN value with incarnation.
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444