Enabling/disabling Logging for Flashback Database
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE FLASHBACK OFF;
Estimating Disk Space Requirements for Flashback Database Logs
SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
By default, flashback logs are generated for all permanent tablespaces.
SQL> ALTER TABLESPACE tbs_3 FLASHBACK OFF;
SQL> ALTER TABLESPACE tbs_3 FLASHBACK ON;
Backup database
RMAN> backup database plus archivelog;
Backup flash recovery area
RMAN> backup recovery area;
Flashback usage
sql> select * from v$recovery_file_dest;
sql> select * from v$flash_recovery_area_usage;
Create Restore Point
sql> create restore point rp01;
sql> select name from v$restore_point;
sql> drop restore point rp01;
sql> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;
sql> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
-- Determining the Current Window for Flashback Database
sql> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
Restore to Restore Point using RMAN
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> LIST RESTORE POINT ALL;
RMAN> LIST RESTORE POINT RP03;
RMAN> flashback database to restore point rp01;
RMAN> alter database open resetlogs;
RMAN> FLASHBACK DATABASE TO SCN 46963;
RMAN> FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;
RMAN> FLASHBACK DATABASE TO TIME "TO_DATE('09/20/00','MM/DD/YY')";
RMAN> SQL 'ALTER DATABASE OPEN READ ONLY'; # VERIFY CHANGES
Restore to Restore Point using SQL
sql> shutdown immediate
sql> startup mount
sql> flashback database to restore point rp01;
sql> alter database open resetlogs;
Options After Flashback Database to the Wrong Time
RMAN> FLASHBACK DATABASE TO SCN 42963; #earlier than current SCN
RMAN> RECOVER DATABASE UNTIL SCN 56963; #later than current SCN
RMAN> RECOVER DATABASE;
Performing Flashback Database to Undo an OPEN RESETLOGS
sql> select resetlogs_change# from v$database;
sql> select oldest_flashback_scn from v$flashback_database_log;
sql> select resetlogs_change# from v$database;
sql> select oldest_flashback_scn from v$flashback_database_log;
Flashback Database To The Right of Open Resetlogs
sql> select oldest_flashback_scn from v$flashback_database_log;
SQL> select prior_incarnation# from v$database_incarnation where status = 'CURRENT';
RMAN> LIST INCARNATION OF DATABASE trgt;
RMAN> RESET DATABASE TO INCARNATION 1;
RMAN> FLASHBACK DATABASE TO SCN 1500;
Flashback Table
-- Prerequisites for Using Flashback Table
sql> ALTER TABLE table ENABLE ROW MOVEMENT;
sql> SELECT, INSERT, DELETE, and ALTER privileges on the table.
Performing Flashback Table
sql> FLASHBACK TABLE EMP TO SCN 123456;
sql> FLASHBACK TABLE EMP TO TIMESTAMP
TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
Viewing, Querying and flashback Objects in the Recycle Bin
SQL> show recyclebin;
SQL> SELECT object_name as recycle_name, original_name, type
FROM recyclebin;
SQL> SELECT * FROM "BIN$KSD8DB9L345KLA==$0";
SQL> FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;
OR
sql> FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;
FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP
RENAME TO hr.int2_admin_emp;
Hope this helps! Regards Rupam
