At (let’s say) around 08:30 this morning something (probably someone) messed up the database. I would like to go get back the database to 08:00 this morning:
- Startup the database in nomount
- For future reference: look at the incarnations we know
- Set an until time (this must be done in a run block)
- Restore the database
- Recover the database
- Open the database with resetlogs
RMAN> startup force mount;
Oracle instance started
database mounted
Total System Global Area 620756648 bytes
Fixed Size 9137832 bytes
Variable Size 339738624 bytes
Database Buffers 268435456 bytes
Redo Buffers 3444736 bytes
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 CDB01 1590200411 PARENT 1 17-APR-19
2 2 CDB01 1590200411 PARENT 1920977 07-OCT-20
RMAN> run {
2> set until time "to_date('2020-10-24 08:00:00', 'yyyy-mm-dd hh24:mi:ss')";
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 24-OCT-20
flashing back control file to SCN 7644445
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=442 device type=DISK
skipping datafile 5; already restored to file /u02/datafiles/CDB01/pdbseed/system01.dbf
skipping datafile 6; already restored to file /u02/datafiles/CDB01/pdbseed/sysaux01.dbf
skipping datafile 8; already restored to file /u02/datafiles/CDB01/pdbseed/undotbs01.dbf
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 /u02/datafiles/CDB01/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/datafiles/CDB01/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u02/datafiles/CDB01/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u02/datafiles/CDB01/users01.dbf
channel ORA_DISK_1: reading from backup piece /u04/backups/bloemkool/sgvdajcc_1_1
channel ORA_DISK_1: piece handle=/u04/backups/bloemkool/sgvdajcc_1_1 tag=TAG20201019T000012
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 00009 to /u02/datafiles/CDB01/PDB01/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u02/datafiles/CDB01/PDB01/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u02/datafiles/CDB01/PDB01/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u02/datafiles/CDB01/PDB01/users01.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u02/datafiles/CDB01/PDB01/martijn01.dnf
channel ORA_DISK_1: reading from backup piece /u04/backups/bloemkool/shvdaje6_1_1
channel ORA_DISK_1: piece handle=/u04/backups/bloemkool/shvdaje6_1_1 tag=TAG20201019T000012
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 24-OCT-20
Starting recover at 24-OCT-20
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2090
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2091
... restoring a lot of archivelogs
archived log thread=1 sequence=2133
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2134
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2135
channel ORA_DISK_1: reading from backup piece /u04/backups/bloemkool/ttvdp86t_1_1
channel ORA_DISK_1: piece handle=/u04/backups/bloemkool/ttvdp86t_1_1 tag=TAG20201024T132132
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
archived log file name=/u03/archiving/CDB01/1_2090_1053215134.dbf thread=1 sequence=2090
archived log file name=/u03/archiving/CDB01/1_2091_1053215134.dbf thread=1 sequence=2091
.... Recovering the just restored archivelogs
archived log file name=/u03/archiving/CDB01/1_2132_1053215134.dbf thread=1 sequence=2132
archived log file name=/u03/archiving/CDB01/1_2133_1053215134.dbf thread=1 sequence=2133
archived log file name=/u03/archiving/CDB01/1_2134_1053215134.dbf thread=1 sequence=2134
archived log file name=/u03/archiving/CDB01/1_2135_1053215134.dbf thread=1 sequence=2135
media recovery complete, elapsed time: 00:01:10
Finished recover at 24-OCT-20
RMAN> alter database open resetlogs;
Statement processed
RMAN>Just for fun..let’s have a look at the incarnations:
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 CDB01 1590200411 PARENT 1 17-APR-19
2 2 CDB01 1590200411 PARENT 1920977 07-OCT-20
3 3 CDB01 1590200411 CURRENT 7836659 24-OCT-20
RMAN>We’re in a new incarnation….The work of the “open database resetlogs”