SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
To check If Flashback database is enable
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
NO
Enabling Flashback database
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
YES
To Enable FLASHBACK on specified Tablespace
ALTER TABLESPACE tbs_3 FLASHBACK OFF;
ALTER TABLESPACE tbs_3 FLASHBACK ON;
select TABLESPACE_NAME, LOGGING, FORCE_LOGGING from dba_tablespaces;
TABLESPACE_NAME LOGGING FOR
------------------------------ --------- ---
SYSTEM LOGGING YES
SYSAUX LOGGING YES
UNDOTBS1 LOGGING NO
TEMP NOLOGGING NO
USERS LOGGING NO
select NAME, FLASHBACK_ON from v$tablespace;
NAME FLA
------------------------------ ---
SYSTEM YES
SYSAUX YES
UNDOTBS1 YES
TEMP YES
USERS YES
Estimating Disk Space Requirements for Flashback Database Logs
According with backup retention requirements
SELECT ESTIMATED_FLASHBACK_SIZE
FROM V$FLASHBACK_DATABASE_LOG;
Determining the Current Window for Flashback Database
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FL
-------------------- ---------
10361692 16-SEP-17
Creating Normal and Guaranteed Restore Points
create a normal restore point
CREATE RESTORE POINT before_upgrade;
create a guaranteed restore point
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
To List Restore Points
To List of the currently defined restore points
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;
To list only the guaranteed restore points
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
To Drop Restore Point
DROP RESTORE POINT before_app_upgrade;
RESTORE DATABASE WITH FLASHBACK DATABASE
1# you have to determine SCN, restore point or point in time for the FLASHBACK DATABASE command
2#
echo $ORACLE_SID
rman TARGET /
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
Determining the Current Window for Flashback Database
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
SELECT CURRENT_SCN FROM V$DATABASE;
Allocate channel if you need to restore some archivelog
RMAN> FLASHBACK DATABASE TO SCN 6886675;
RMAN> FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;
RMAN> FLASHBACK DATABASE TO TIME "TO_DATE('09/20/00','MM/DD/YY')";
checking if is It right restore point
RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';
If point is right you can open with reset log option.
RMAN> ALTER DATABASE OPEN RESETLOGS;
OPTIONS AFTER FLASHBACK DATABASE TO THE WRONG TIME
#earlier than current SCN
RMAN> FLASHBACK DATABASE TO SCN 42963;
#later than current SCN
RMAN> RECOVER DATABASE UNTIL SCN 56963;
