Oracle Performance and Engineering

Contd. from the earlier post -

10) Oracle Database 10g: Maximize Availability - Human Error Correction

Flashback Database - Flashback logs == Before images of data blocks. Created in the flashback recovery area (DB_RECOVERY_FILE_DEST) by (new) background process RVWR (Recovery Writer).

V$FLASHBACK_DATABASE_LOG

V$FLASHBACK_DATABASE_STAT

Following a FLASHBACK DATABASE (TO TIMESTAMP | TO SCN), db must be opened in RESETLOGS mode.

To exclude a tablespace - ALTER TABLESPACE .. FLASHBACK OFF;

FLASHBACK TABLE BEFORE DROP;

DROP TABLE PURGE; -- cannot flashback. No more stored in recycle bin

DBA_RECYCLEBIN

PURGE RECYCLEBIN;

Flashback Versions -

VERSIONS BETWEEN (SCN | TIMESTAMP)

SCN_TO_TIMESTAMP
TIMESTAMP_TO_SCN

RETENTION GUARANTEE for UNDO tablespace.

New pseudo-colums - VERSIONS_STARTTIME, VERSIONS_ENDTIME

Flashback Transaction Query -

Must enable "Supplemental Logging" to use this feature. Additional columns are logged into undo segments and redo log files when this is enabled. SELECT ANY TRANSACTION privilege must be granted as well.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

V$DATABASE - shows if this is on.

Query FLASHBACK_TRANSACTION_QUERY (operation, undo_sql, table_name). Find the XID of the transaction using Flashback Versions query before.

Flashback Table (TO SCN | TO TIMESTAMP) - doesn't work for AQ, data dictionary tables. Much faster than traditional media recovery.

Must 'ENABLE ROW MOVEMENT' before using it.

Triggers are by default disabled before flashback table operation.

Flashing back before a DDL operation is not possible.

Flashing back during a transaction is not allowed.

posted by Nilendu  # 9:58 PM