Flashback
April 28th, 2008 . by admin-the flash recovery area can be a single directory, an entire file system or an AMS disk group
-a flash back recovery area can shared by more than one database
-flashback revovery area can contains:
.Control files
.Archived log files
.Flashback logs
.Control file and SPFILE autobackups
.Data file copies
.When flash recovery are configured, parameter LOG_ARCHIVE_DEST_10 is automatically set to the flash recovery area location.
Setup Flash Recovery Area with dynamic parameters:
-DB_RECOVERY_FILE_DEST_SIZE
-DB_RECOVERY_FILE_DEST
SQL> alter system set db_recovery_file_dest_size = 8g scope=both;
note: all instances in RAC must have the same
-DB_RECOVERY_FILE_DEST_SIZE
- DB_RECOVERY_FILE_DEST
Flash recovery area should be large enough to hold a copy of all datafiles, all incremental backups, online redo logs, control files and control file autobackups
Flash Recovery Area Management
-File no longer needed are deleted, a message is written to alert log
-when recovery area used space over 85%, a warning is issued
-when recovery area used space over 97%, a critical message is issued
-Message can be viewed per DBA_OUTSTANDING_ALERTS
-Infos about flash recovery: View V$RECOVERY_FILE_DEST
SQL> select name, space_limit_max_size, space_used_used, space_reclaimable, obsolete from v$recovery_file_dest;
-Config Flasback Database
SQL> shutdown immediate; SQL> startup mount exclusive; SQL> alter system set db:flashback_retention_target=60 scope=both; --60 min SQL> alter database flashback on; SQL> alter database open; SQL> select flashback_on from v$database; --to see, db flashback? ...and off! SQL> alter database flashback off;
Flask back database (reset db back to time)
SQL> shutdown immediate; SQL> startup mount exclusive; SQL> flashback database to timestamp(sysdate -(1/48)); --back 30min SQL> alter database open resetlogs;
Monitoring flashback database
V$FLASHBACK_DATABASE_LOG
V$FLASHBACK_DATABASE_STAT
see the actual size of flashback log
Excluding tablespace from flashback database
Excluding a tablespace NOT flashback!
SQL>aler tablespace abc flashback off;
Retrieving Dropped Tables from Recycle Bin
SQL> flashback table order_items to before drop;
Flashback and rename a table
SQL> flashback table order_items to before drop rename to order_items_old_version;
Recycle Bin
Views
USER_RECYCLEBIN
RECYCLEBIN
DBA_RECYLCEBIN
Show RECYCLEBIN
SQL> show RECYCLEBIN
Empty Recylce Bin
SQL> purge table users2 user gary; --remove all of gary's objects from recyle bin for users2 tablespace SQL> purge user_recylebin; -- empty all object of the current user
Bypass the Recycle Bin
SQL> drop table abc purge; --object go not to recycle bin SQL> drop user..cascade; --all objects are purged -drop tablespace..including contents
-Only non-SYSTEM locally managed tablespace can have a recyle bin
-Table using Fine Grained Auditing, Virtual Private Database cannot reside in a recyle bin
Flashback Query
-Flashback Version Query: retrieves all versions of all rows in a table between 2 times stamp or SCNs
-Flashback Transaction Query: provide a diff. point of view by retrieving all rows affected by transaction
-View FLASHBACK_TRANSACTION_QUERY contains transaction infos. for all tables involved in a transaction
SELECT..FROM..VERSION BETWEEN
{scn | timestamp expr | MINVALUE AND expr | MAXVALUE}
Flashback Table
-allow to recover one or more tables to a spec. point in time
exm:
SQL> alter table hr.employees enable row movement; SQL> alter table hr.departments enable row movement; SQL> select * from hr.departments where department_name = 'IT'; SQL> flashback table hr.employees, hr.departments to timestamp systimestamp - interval '15'minute; SQL> select * from hr.departments where department_name = 'IT';
SCN and Timestamp Mapping
-convert SCN to Timestamp: SCN_TO_TIMESTAMP
-convert Timestamp to SCN:TIMESTAMP_TO_SCN
SQL> select timestamp_to_scn(systimestamp) from dual;
(compare with CURRENT_SCN in V$DATABASE)
Flashback Privileges
-Flashback database: SYSDBA
-Flashback table: FLASHBACK TABLE privilege and object privilege
-Flashback Version Query: FLASHBACK TABLE privilege and object privilege
-Flashback Transaction Query: SELECT ANY TRANSACTION
-Flashback Drop: Object privileges
Block change tracking
-Enable:
SQL> alter database enable block change tracking;
-When using OMF, Block change tracking file are auto. named and placed in the directory spec. by DB_CREATE_FILE_DEST
-When NOT using OMF block change tracking file must be defined
SQL> alter database enable block change tracking using file 'u01/oradata/changetracking/chg01.dbf';
-and turn off:
SQL> alter database disable block change tracking;
-View V$BLOCK_CHANGE_TRACKING
