RMAN Flashback Database to a Specific SCN
Assume that you inserted corrupted rows in many tables at 5:00 p.m. on February 14. You connect SQL*Plus to the database and query the earliest SCN in the flashback window:
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
2 FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK
-------------------- ----------------
411010 2013/02/14 16:49
You then open a new terminal, start the RMAN client, and connect to the target database and recovery catalog. You enter RMAN commands as follows (sample output for the FLASHBACK DATABASE is included):
RMAN> SHUTDOWN IMMEDIATE RMAN> STARTUP MOUNT RMAN> FLASHBACK DATABASE TO SCN 411010; Starting flashback at 15-FEB-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=104 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:07 Finished flashback at 15-FEB-13 RMAN> ALTER DATABASE OPEN RESETLOGS;
Example 2-100 FLASHBACK DATABASE to a Restore Point
Assume that you are preparing to load a massive number of updates to the database. You create a guaranteed restore point before the performing the updates:
SQL> CREATE RESTORE POINT before_update GUARANTEE FLASHBACK DATABASE;
The bulk update fails, leaving the database with extensive corrupted data. You start an RMAN session, connect to the target database and recovery catalog, and list the guaranteed restore points:
RMAN> LIST RESTORE POINT ALL; SCN RSP Time Type Time Name ---------------- --------- ---------- --------- ---- 412742 GUARANTEED 15-FEB-13 BEFORE_UPDATE
You mount the database, RMAN flashback database to the restore point (sample output included), and then open the database with the RESETLOGS option:
RMAN> SHUTDOWN IMMEDIATE RMAN> STARTUP MOUNT RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPDATE'; Starting flashback at 15-FEB-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=104 device type=DISK starting media recovery archived log for thread 1 with sequence 34 is already on disk as file /disk2/oracle/oradata/prod/arch/archive1_34_614598462.dbf media recovery complete, elapsed time: 00:00:01 Finished flashback at 15-FEB-13 RMAN> ALTER DATABASE OPEN RESETLOGS;
FLASHBACK DATABASE for a PDB to a Guaranteed PDB Restore Point
Assume that you need to upgrade an application that performs DML operations on the tables in the PDB hr_pdb. Before you perform the application upgrade, you create a guaranteed PDB restore point in hr_pdb when connected to the PDB (the PDB is mounted):
SQL> CREATE RESTORE POINT hr_pdb_grp_before_upgrade GUARENTEE FLASHBACK DATABASE;
The application upgrade fails leaving the PDB with corrupted data. You want to rewind the PDB to its state before the upgrade failure. You start SQL*Plus, connect to the CDB as a common user with the SYSDBA or SYSBACKUP privilege, and then run the following command to view all the restore points:
SQL> SELECT name, guarantee_flashback_database, pdb_restore_point, con_id
FROM v$restore_point;
NAME GUARANTEE_FLASHBACK_DATABASE PDB_RESTORE_POINT CON_ID
----------------------------- --------------------------- ----------------- ------
CDB_GRP_BEFORE_PATCH YES NO 0
HR_PDB_GRP_BEFORE_UPGRADE YES YES 1The output indicates that the restore point HR_PDB_GRP_BEFORE_UPGRADE is a guaranteed PDB restore point. You can reverse the effects of data corruption by rewinding hr_pdb to this guaranteed PDB restore point. To perform a flashback operation for hr_pdb, this PDB must be closed. All other PDBs in the CDB can remain open and operational.
You place the CDB in mount mode, flash back the PDB to the guaranteed PDB restore point, and then open the PDB with resetlogs. In this example, the CDB uses shared undo and, therefore, an auxiliary instance is used to store temporary files during the flashback operation.
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> FLASHBACK PLUGGABLE DATABASE hr_pdb TO RESTORE POINT hr_pdb_grp_before_upgrade
AUXILIARY DESTINATION '/temp/aux_dest';
RMAN> ALTER PLUGGABLE DATABASE hr_pdb OPEN RESETLOGS;Also See: