Flashback database

I don't really like using flashback, if given the choice would use pluggable databases and clone the pdb prior to doing any work and if you hit a problem clone the pdb back to it's original state (but keep that for another day).

Here are the steps for flashback of a database. The DB needs to be in achieve log mode and forced logging enabled. Verify that the flash recovery area is setup and large enough for the upgrade.

NB - This process is slightly different if using DataGuard please see - 

https://www.thegeekdiary.com/how-to-create-and-drop-guaranteed-restore-point-in-oracle-data-guard/


You don’t need to enable flashback on the whole database, but I did it anyway. The restore point needs to be guaranteed.

Verify Flashback status, size and location:

select flashback_on, LOG_MODE, FORCE_LOGGING, status from v$database, v$instance;

show parameter DB_RECOVERY

show parameter FLASHBACK

Steps before the upgrade:

ALTER DATABASE FLASHBACK ON;

CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Steps after the upgrade:

DROP RESTORE POINT before_upgrade;

ALTER DATABASE FLASHBACK OFF;



Steps if need to flashback the Database:

su – oracle

sqlplus / as sysdba;


select current_scn from v$database;

shutdown immediate;

startup mount;

select * from v$restore_point;

flashback database to restore point before_upgrade;

alter database open resetlogs;

Check flashback space usage:

select * from v$flash_recovery_area_usage;

select name, time, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE from v$restore_point;

More detailed instructions (when running an application upgrade and you hit problems) :

Instruction Steps
Task Steps		Reference/Screenshots
1.	Verify Flashback Recovery status, size and location: If db_recovery_file_dest and db_recovery_file_dest_size is blank, add them before proceeding.	select flashback_on, log_mode, force_logging, status from v$database, v$instance; show parameter db_recovery; show parameter flashback;
2.
3.	Steps before the Application upgrade: Note: Save value for current_scn to compare if recovery is performed	alter database flashback on; select current_scn from v$database; create restore point "before_upgrade" guarantee flashback database;
4.
5.	Steps after the Application upgrade: Note: Only perform these steps if upgrade has been tested and declared successful	drop restore point "before_upgrade"; alter database flashback off;
6.
7.	If Application upgrade was not successful and a roll back is required, perform these steps to roll back database changes: Note: Compare SCN from step 3 to ensure flashback was successful. SCN’s may not match, but should be close in range Replace db_name with your RAC database name	For single database instance: sqlplus / as sysdba; shutdown immediate; startup mount; flashback database to restore point "before_upgrade"; alter database open resetlogs; select current_scn from v$database; For multi-node RAC instances: srvctl stop database -db db_name export ORACLE_SID=db_name_1 sqlplus / as sysdba; startup mount; flashback database to restore point "before_upgrade"; alter database open resetlogs; select current_scn from v$database; shutdown immediate; quit; srvctl start database -db db_name
8.
9.	Check flashback space usage:	select * from v$flash_recovery_area_usage; select name, time, guarantee_flashback_database, storage_size from v$restore_point;
10.