..: RMAN Point-in-Time Recovery (DBPITR) in 19c to before resetlogs

RMAN point-in-time recovery will restore database to the exact time in the past, it helps database recovery in db crash, data corrupt and erroneous delete situations

lets try database restore using time stamps,

Start db in nomount:

/home/oracle> sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 12 00:00:47 2021

Version 19.8.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1.0737E+10 bytes

Fixed Size 12446800 bytes

Variable Size 1711276032 bytes

Database Buffers 8992587776 bytes

Redo Buffers 21106688 bytes

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

Restore controlfile and mount db;

[oracle@racnode01] -->labdb19c<--

/home/oracle> rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Aug 12 00:04:27 2021

Version 19.8.0.0.0

connected to target database: LABDB19C (not mounted)

RMAN> restore controlfile from '+POC19C_DATA/LABDB19C/AUTOBACKUP/2021_08_09/s_108104810.310.108104811';

Starting restore at 20210812.000509

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=277 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=+POC19C_DATA/LABDB19C/CONTROLFILE/current.344.1077323871

output file name=+POC19C_DATA/LABDB19C/CONTROLFILE/current.345.1077323871

Finished restore at 20210812.000510

released channel: ORA_DISK_1

RMAN> sql 'alter database mount';

sql statement: alter database mount

Restore database until time:

RMAN> run{

allocate channel ch1 type disk;

allocate channel ch2 type disk;

set until time="to_date('2021/08/11 02:55:48', 'yyyy/mm/dd hh24:mi:ss')";

restore database;

recover database;

}

allocated channel: ch1

channel ch1: SID=352 device type=DISK

allocated channel: ch2

channel ch2: SID=122 device type=DISK

executing command: SET until clause

Starting restore at 20210812.000856

Starting implicit crosscheck backup at 20210812.000856

Crosschecked 9 objects

Crosschecked 11 objects

Finished implicit crosscheck backup at 20210812.000858

Starting implicit crosscheck copy at 20210812.000858

Finished implicit crosscheck copy at 20210812.000858

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_11/thread_1_seq_1158.314.108272057

File Name: +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_11/thread_1_seq_1.340.108342897

File Name: +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_11/thread_1_seq_2.281.108342905

File Name: +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_11/thread_1_seq_3.282.108342907

File Name: +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_11/thread_1_seq_4.727.108342909

File Name: +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_11/thread_1_seq_5.726.108342913

File Name: +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_11/thread_1_seq_6.725.108342915

File Name: +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_11/thread_1_seq_7.724.108343051

File Name: +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_11/thread_1_seq_8.723.108343593

File Name: +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_11/thread_1_seq_9.722.108343607

File Name: +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_10/thread_1_seq_1156.311.108176411

File Name: +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_10/thread_1_seq_1157.312.108252007

File Name: +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_09/thread_1_seq_1155.283.108122643

File Name: +POC19C_DATA/LABDB19C/AUTOBACKUP/2021_08_11/s_108272060.316.108272061

File Name: +POC19C_DATA/LABDB19C/AUTOBACKUP/2021_08_09/s_108104810.310.108104811

File Name: +POC19C_DATA/LABDB19C/AUTOBACKUP/2021_08_09/s_108105347.285.108105347

File Name: +POC19C_DATA/LABDB19C/DATAFILE/tb01.352.1078956129

released channel: ch1

released channel: ch2

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 08/12/2021 00:08:58

RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

Note: Db restore failed with error RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

Check db incarnation details:

RMAN> list incarnation;

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1 1 LABDB19C 4148792351 PARENT 1 20190417.005559

2 2 LABDB19C 4148792351 PARENT 1920977 20210708.003753

3 3 LABDB19C 4148792351 CURRENT 6299511 20210811.033416

Current incarnation reset time is 2021-08-11 3.34AM , we are trying to restore db before current incarnation time ,

current incarnation time should be earlier than restore time to resolve rman error

reset incarnation:

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 LABDB19C 4148792351 PARENT 1 20190417.005559

2 2 LABDB19C 4148792351 CURRENT 1920977 20210708.003753

3 3 LABDB19C 4148792351 ORPHAN 6299511 20210811.033416

Now current incarnation reset time ‘2021-07-08’ is earlier than restore time '2021-08-11’

Try restore db until time now:

RMAN> run{

allocate channel ch1 type disk;

allocate channel ch2 type disk;

set until time="to_date('2021/08/11 02:55:48', 'yyyy/mm/dd hh24:mi:ss')";

restore database;

recover database;

}

allocated channel: ch1

channel ch1: SID=352 device type=DISK

allocated channel: ch2

channel ch2: SID=122 device type=DISK

executing command: SET until clause

Starting restore at 20210812.001339

channel ch1: starting datafile backup set restore

channel ch1: specifying datafile(s) to restore from backup set

channel ch1: restoring datafile 00001 to +POC19C_DATA/LABDB19C/DATAFILE/system.349.1077323759

channel ch1: restoring datafile 00002 to +POC19C_DATA/LABDB19C/DATAFILE/mts01.288.1077348575

channel ch1: restoring datafile 00003 to +POC19C_DATA/LABDB19C/DATAFILE/sysaux.348.1077323793

channel ch1: restoring datafile 00004 to +POC19C_DATA/LABDB19C/DATAFILE/undotbs1.347.1077323809

channel ch1: restoring datafile 00005 to +POC19C_DATA/LABDB19C/DATAFILE/tejaswi.313.1077349553

channel ch1: restoring datafile 00007 to +POC19C_DATA/LABDB19C/DATAFILE/users.346.1077323809

channel ch1: restoring datafile 00008 to +POC19C_DATA/LABDB19C/DATAFILE/mts01.287.1077350069

channel ch1: restoring datafile 00009 to +POC19C_DATA/LABDB19C/DATAFILE/jts0101.dbf

channel ch1: restoring datafile 00010 to +POC19C_DATA/LABDB19C/DATAFILE/jts01.279.1077352257

channel ch1: restoring datafile 00011 to +POC19C_DATA/LABDB19C/DATAFILE/mts01.dbf

channel ch1: restoring datafile 00012 to +POC19C_DATA/LABDB19C/DATAFILE/jts01.343.1077353335

channel ch1: restoring datafile 00013 to +POC19C_DATA/LABDB19C/DATAFILE/teja.350.1078365639

channel ch1: restoring datafile 00014 to +POC19C_DATA/LABDB19C/DATAFILE/tb01

channel ch1: restoring datafile 00015 to +POC19C_DATA/LABDB19C/DATAFILE/tb02.351.1078956401

channel ch1: restoring datafile 00016 to +POC19C_DATA/LABDB19C/DATAFILE/users.339.1078977183

channel ch1: restoring datafile 00017 to +POC19C_DATA/LABDB19C/DATAFILE/users.338.1078979345

channel ch1: restoring datafile 00018 to +POC19C_DATA/LABDB19C/DATAFILE/users.337.1078979349

channel ch1: restoring datafile 00019 to +POC19C_DATA/LABDB19C/DATAFILE/incent_data.335.1078983489

channel ch1: restoring datafile 00020 to +POC19C_DATA/LABDB19C/DATAFILE/users.334.1079063905

channel ch1: restoring datafile 00021 to +POC19C_DATA/LABDB19C/DATAFILE/users.333.1079063949

channel ch1: restoring datafile 00022 to +POC19C_DATA/LABDB19C/DATAFILE/users.329.1079067891

channel ch1: restoring datafile 00023 to +POC19C_DATA/LABDB19C/DATAFILE/users.328.1079067923

channel ch1: restoring datafile 00024 to +POC19C_DATA/LABDB19C/DATAFILE/users.327.1079069033

channel ch1: restoring datafile 00025 to +POC19C_DATA/LABDB19C/DATAFILE/incent_data.326.1079069273

channel ch1: reading from backup piece /flashblade_backup/exports/lab19c_bkps/LABDB19C_full_108103964

channel ch1: piece handle=/flashblade_backup/exports/lab19c_bkps/LABDB19C_full_108103964 tag=TAG2021089T045244

channel ch1: restored backup piece 1

channel ch1: restore complete, elapsed time: 00:13:45

Finished restore at 20210812.002725

Starting recover at 20210812.002725

starting media recovery

archived log for thread 1 with sequence 1155 is already on disk as file +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_09/thread_1_seq_1155.283.108122643

archived log for thread 1 with sequence 1156 is already on disk as file +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_10/thread_1_seq_1156.311.108176411

archived log for thread 1 with sequence 1157 is already on disk as file +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_10/thread_1_seq_1157.312.108252007

archived log for thread 1 with sequence 1158 is already on disk as file +POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_11/thread_1_seq_1158.314.108272057

archived log file name=+POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_09/thread_1_seq_1155.283.108122643 thread=1 sequence=1155

archived log file name=+POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_10/thread_1_seq_1156.311.108176411 thread=1 sequence=1156

archived log file name=+POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_10/thread_1_seq_1157.312.108252007 thread=1 sequence=1157

archived log file name=+POC19C_DATA/LABDB19C/ARCHIVELOG/2021_08_11/thread_1_seq_1158.314.108272057 thread=1 sequence=1158

media recovery complete, elapsed time: 00:00:05

Finished recover at 20210812.002731

released channel: ch1

released channel: ch2

Restore and recover database completed successfully,

rman restore database from full and incremental backups , once restore completed it will recover database up to specified time using archive backups and available archives in db server.

Point-in-Time Recovery(PITR) is sometimes called incomplete recovery because it does not use all of the available redo or completely recover all changes to database.

Incomplete recovery allows db to open in resetlogs options only,

Open database:

/home/oracle> sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 12 00:30:00 2021

Version 19.8.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

--------- --------------------

LABDB19C READ WRITE

Once db open with resetlogs option it will create new incarnation

/home/oracle> rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Aug 12 00:31:03 2021

Version 19.8.0.0.0

connected to target database: LABDB19C (DBID=4148792351)

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 LABDB19C 4148792351 PARENT 1 20190417.005559

2 2 LABDB19C 4148792351 PARENT 1920977 20210708.003753

3 3 LABDB19C 4148792351 ORPHAN 6299511 20210811.033416

4 4 LABDB19C 4148792351 CURRENT 6300585 20210812.003015

..

labels

Thursday, 12 August 2021

RMAN Point-in-Time Recovery (DBPITR) in 19c to before resetlogs

No comments:

Post a Comment