RMAN Point-In-Time Recovery
Whenever request from application team to flashback or restore database to point in time these setps will help you.
Questions:
Restore DEVDB database to 18-NOV-2022 22:20:22 IST.
Answer:
Follow the below steps
High Level steps:
1. Check proper backups available or not (If no backups we can not do point in time recovery)
rman target /
RMAN> list backup;
2. Shutdown DB and start DB in mount mode
rman target /
RMAN> shutdown immediate;
RMAN> startup mount;
3. Restore and Recover database to point in time
RMAN> run
{
allocate channel ch1 type disk;
set until time "to_date('2022-11-18:22:20:22', 'yyyy-mm-dd:hh24:mi:ss')";
restore database;
recover database;
}
4. Open database with resetlogs
RMAN> alter database open resetlogs;
Logs:
[oracle@oraclelab1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Nov 18 22:09:58 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEVDB (DBID=1016989223)
RMAN> run {
allocate channel ch1 device type disk;
crosscheck backup;
crosscheck archivelog all;
backup as backupset database format '/u01/backup/Fullback_%T_%U'
plus archivelog format '/u01/backup/Archive_%T_%U';
backup current controlfile format '/u01/backup/Controlback_%T_%U';
2> 3> release channel ch1;
}4> 5> 6> 7> 8> 9>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=17 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Fullback_20221010_1s19ui6l_60_1_1 RECID=52 STAMP=1117735125
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Fullback_20221010_1t19ui6l_61_1_1 RECID=53 STAMP=1117735125
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/RMAN/database/full_backup_DEVDB_20221018_291airdf_73_1_1 RECID=64 STAMP=1118399919
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221019_2e1aldgt_78_1_1 RECID=68 STAMP=1118483997
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221019_2d1aldgt_77_1_1 RECID=69 STAMP=1118483997
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Fullback_20221019_2g1aldh0_80_1_1 RECID=70 STAMP=1118484000
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Fullback_20221019_2f1aldh0_79_1_1 RECID=71 STAMP=1118484000
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221019_2h1aldhf_81_1_1 RECID=72 STAMP=1118484015
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Controlback_20221019_2i1aldhh_82_1_1 RECID=73 STAMP=1118484018
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221019_2l1aldqc_85_1_1 RECID=75 STAMP=1118484300
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221019_2k1aldqc_84_1_1 RECID=76 STAMP=1118484300
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Fullback_20221019_2n1aldqg_87_1_1 RECID=77 STAMP=1118484304
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Fullback_20221019_2m1aldqg_86_1_1 RECID=78 STAMP=1118484304
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221019_2o1aldqh_88_1_1 RECID=79 STAMP=1118484305
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Controlback_20221019_2p1aldqi_89_1_1 RECID=80 STAMP=1118484307
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221019_2s1alffi_92_1_1 RECID=82 STAMP=1118486002
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221019_2r1alffi_91_1_1 RECID=83 STAMP=1118486002
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Fullback_20221019_2u1alffl_94_1_1 RECID=84 STAMP=1118486006
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Fullback_20221019_2t1alffl_93_1_1 RECID=85 STAMP=1118486006
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221019_2v1alfg5_95_1_1 RECID=86 STAMP=1118486021
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Controlback_20221019_301alfg6_96_1_1 RECID=87 STAMP=1118486023
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221019_341algkt_100_1_1 RECID=90 STAMP=1118487197
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221019_331algkt_99_1_1 RECID=91 STAMP=1118487197
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Fullback_20221019_361algl1_102_1_1 RECID=92 STAMP=1118487201
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Fullback_20221019_351algl1_101_1_1 RECID=93 STAMP=1118487201
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221019_371alglg_103_1_1 RECID=94 STAMP=1118487216
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Controlback_20221019_381alglh_104_1_1 RECID=95 STAMP=1118487218
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221020_3a1ao0kd_106_1_1 RECID=97 STAMP=1118569101
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221020_3b1ao175_107_1_1 RECID=98 STAMP=1118569701
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Fullback_20221020_3c1ao178_108_1_1 RECID=99 STAMP=1118569705
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20221020_3d1ao17o_109_1_1 RECID=100 STAMP=1118569720
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Controlback_20221020_3e1ao17p_110_1_1 RECID=101 STAMP=1118569722
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_10_20/o1_mf_s_1118569723_ko1m13jj_.bkp RECID=102 STAMP=1118569723
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_11_07/o1_mf_s_1120125661_kpk2j5m7_.bkp RECID=103 STAMP=1120125661
Crosschecked 34 objects
validation succeeded for archived log
archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_195_kqhfbn6k_.arc RECID=187 STAMP=1121119789
validation succeeded for archived log
archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_196_kqhfbohz_.arc RECID=188 STAMP=1121119792
validation succeeded for archived log
archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_197_kqhfbon5_.arc RECID=189 STAMP=1121119793
Crosschecked 3 objects
Starting backup at 18-NOV-22
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=195 RECID=187 STAMP=1121119789
input archived log thread=1 sequence=196 RECID=188 STAMP=1121119792
input archived log thread=1 sequence=197 RECID=189 STAMP=1121119793
input archived log thread=1 sequence=198 RECID=190 STAMP=1121119816
channel ch1: starting piece 1 at 18-NOV-22
channel ch1: finished piece 1 at 18-NOV-22
piece handle=/u01/backup/Archive_20221118_3h1d5ri8_113_1_1 tag=TAG20221118T221016 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:03
Finished backup at 18-NOV-22
Starting backup at 18-NOV-22
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_kh3nrlj4_.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_kh3nqhcn_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_kh3nscnc_.dbf
input datafile file number=00007 name=/tmp/users.dbf
channel ch1: starting piece 1 at 18-NOV-22
channel ch1: finished piece 1 at 18-NOV-22
piece handle=/u01/backup/Fullback_20221118_3i1d5rib_114_1_1 tag=TAG20221118T221019 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:15
Finished backup at 18-NOV-22
Starting backup at 18-NOV-22
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=199 RECID=191 STAMP=1121119834
channel ch1: starting piece 1 at 18-NOV-22
channel ch1: finished piece 1 at 18-NOV-22
piece handle=/u01/backup/Archive_20221118_3j1d5riq_115_1_1 tag=TAG20221118T221034 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-NOV-22
Starting backup at 18-NOV-22
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 18-NOV-22
channel ch1: finished piece 1 at 18-NOV-22
piece handle=/u01/backup/Controlback_20221118_3k1d5ris_116_1_1 tag=TAG20221118T221036 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-NOV-22
Starting Control File and SPFILE Autobackup at 18-NOV-22
piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_11_18/o1_mf_s_1121119838_kqhfd6d5_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-NOV-22
released channel: ch1
RMAN> exit
Recovery Manager complete.
You have new mail in /var/spool/mail/oracle
[oracle@oraclelab1 ~]$
[oracle@oraclelab1 ~]$ cd /u01/backup/
[oracle@oraclelab1 backup]$ ll
total 3173388
-rw-r-----. 1 oracle oinstall 560015360 Nov 18 22:10 Archive_20221118_3h1d5ri8_113_1_1
-rw-r-----. 1 oracle oinstall 29184 Nov 18 22:10 Archive_20221118_3j1d5riq_115_1_1
-rw-r-----. 1 oracle oinstall 10715136 Nov 18 22:10 Controlback_20221118_3k1d5ris_116_1_1
-rw-r-----. 1 oracle oinstall 2678784000 Nov 18 22:10 Fullback_20221118_3i1d5rib_114_1_1
[oracle@oraclelab1 backup]$
[oracle@oraclelab1 2022_11_18]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 18 22:18:10 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> conn mallik/mallik
Connected.
SQL> create table before_pit_table(SLNO number(2));
Table created.
SQL>
SQL> insert into before_pit_table values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from before_pit_table;
SLNO
----------
1
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> !date
Fri Nov 18 22:20:22 IST 2022
SQL> create table after_pit_table(SLNO number(2));
Table created.
SQL> insert into after_pit_table values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from after_pit_table;
SLNO
----------
2
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> !date
Fri Nov 18 22:22:38 IST 2022
SQL>
SQL> conn / as sysdba
Connected.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
You have new mail in /var/spool/mail/oracle
[oracle@oraclelab1 2022_11_18]$
[oracle@oraclelab1 2022_11_18]$ rman TARGET /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Nov 18 22:23:52 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 3690985856 bytes
Fixed Size 8903040 bytes
Variable Size 1577058304 bytes
Database Buffers 2097152000 bytes
Redo Buffers 7872512 bytes
RMAN> run
{
allocate channel ch1 type disk;
set until time "to_date('2022-11-18:22:20:22', 'yyyy-mm-dd:hh24:mi:ss')";
restore database;
recover database;
}2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=259 device type=DISK
executing command: SET until clause
Starting restore at 18-NOV-22
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_kh3nqhcn_.dbf
channel ch1: restoring datafile 00003 to /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_kh3nrlj4_.dbf
channel ch1: restoring datafile 00004 to /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_kh3nscnc_.dbf
channel ch1: restoring datafile 00007 to /tmp/users.dbf
channel ch1: reading from backup piece /u01/backup/Fullback_20221118_3i1d5rib_114_1_1
channel ch1: piece handle=/u01/backup/Fullback_20221118_3i1d5rib_114_1_1 tag=TAG20221118T221019
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
Finished restore at 18-NOV-22
Starting recover at 18-NOV-22
starting media recovery
archived log for thread 1 with sequence 199 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_199_kqhfd2s3_.arc
archived log for thread 1 with sequence 200 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_200_kqhfog2t_.arc
archived log for thread 1 with sequence 201 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_201_kqhfrq49_.arc
archived log for thread 1 with sequence 202 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_202_kqhfxslo_.arc
archived log for thread 1 with sequence 203 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_203_kqhfxxc3_.arc
archived log for thread 1 with sequence 204 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_204_kqhfy0g4_.arc
archived log for thread 1 with sequence 205 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_205_kqhfy3js_.arc
archived log for thread 1 with sequence 206 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_206_kqhfy6ho_.arc
archived log for thread 1 with sequence 207 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_207_kqhfy9k3_.arc
archived log for thread 1 with sequence 208 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_208_kqhfydhc_.arc
archived log for thread 1 with sequence 209 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_209_kqhg0gq5_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_199_kqhfd2s3_.arc thread=1 sequence=199
archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_200_kqhfog2t_.arc thread=1 sequence=200
archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_201_kqhfrq49_.arc thread=1 sequence=201
archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_202_kqhfxslo_.arc thread=1 sequence=202
archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_203_kqhfxxc3_.arc thread=1 sequence=203
archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_204_kqhfy0g4_.arc thread=1 sequence=204
archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_205_kqhfy3js_.arc thread=1 sequence=205
archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_206_kqhfy6ho_.arc thread=1 sequence=206
archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_207_kqhfy9k3_.arc thread=1 sequence=207
archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_208_kqhfydhc_.arc thread=1 sequence=208
archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_209_kqhg0gq5_.arc thread=1 sequence=209
media recovery complete, elapsed time: 00:00:05
Finished recover at 18-NOV-22
released channel: ch1
RMAN>
RMAN> alter database open resetlogs;
Statement processed
RMAN> exit
Recovery Manager complete.
You have new mail in /var/spool/mail/oracle
[oracle@oraclelab1 2022_11_18]$
[oracle@oraclelab1 2022_11_18]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 18 22:27:20 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> conn mallik/mallik
Connected.
SQL> select * from before_pit_table;
SLNO
----------
1
SQL> select * from after_pit_table;
select * from after_pit_table
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
Regards,
Mallik
