Configuring Flashback Database

Querying the Recycle Bin:-

SQL> SELECT owner, original_name, object_name,

type, ts_name, droptime, related, space

FROM dba_recyclebin

WHERE can_undrop = 'YES';

SQL> SELECT original_name, object_name,

type, ts_name, droptime, related, space

FROM user_recyclebin

WHERE can_undrop = 'YES';

SQL> SHOW RECYCLEBIN

Configuring Flashback Database:-

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT EXCLUSIVE;

SQL> ALTER SYSTEM SET

     DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;

 Flashback Database:-

RMAN> FLASHBACK DATABASE TO TIME =

"TO_DATE('2004-05-27 16:00:00',

'YYYY-MM-DD HH24:MI:SS')";

RMAN> FLASHBACK DATABASE TO SCN=23565;

RMAN> FLASHBACK DATABASE

TO SEQUENCE=223 THREAD=1;

SQL> FLASHBACK DATABASE

TO TIMESTAMP(SYSDATE-1/24);

SQL> FLASHBACK DATABASE TO SCN 53943;

SQL> FLASHBACK DATABASE TO RESTORE POINT b4_load;

Excluding Tablespaces from Flashback Database:-

SQL> ALTER TABLESPACE <ts_name> FLASHBACK {ON|OFF}

SQL> SELECT name, flashback_on

FROM v$tablespace;

Monitoring Flashback Database:-

To monitor the ability to meet your retention target:-

• View the flash recovery area disk quota:-

SQL> SELECT estimated_flashback_size,

flashback_size

FROM V$FLASHBACK_DATABASE_LOG;

• Determine the current flashback window:-

SQL> SELECT oldest_flashback_scn,

oldest_flashback_time

FROM V$FLASHBACK_DATABASE_LOG;

 Monitor logging in the Flashback Database logs:-

SQL> SELECT *

FROM V$FLASHBACK_DATABASE_STAT;

SQL> SELECT begin_time, end_time, flashback_data, db_data,

    redo_data, estimated_flashback_size AS EST_FB_SZE

    FROM V$FLASHBACK_DATABASE_STAT;

SQL> SELECT name, space_limit AS quota,

2 space_used AS used,

3 space_reclaimable AS reclaimable,

4 number_of_files AS files

5 FROM v$recovery_file_dest ;

Guaranteed Restore Points:-

SQL> CREATE RESTORE POINT before_load

      GUARANTEE FLASHBACK DATABASE;