de Haan - Flash me Back, Scotty by rocker12

Hotsos Symposium Oracle Database 10g: Flash Me Back, Scotty …

Dallas, March 2005 Lex de Haan (lex.de.haan@naturaljoin.nl)

Who Am I Lex de Haan lex.de.haan@naturaljoin.nl

Applied Maths, TU Delft Oracle employee 1990-2004 – Teacher, delivery manager, product manager seminars, curriculum developer/manager, ST development

ISO SQL Standardization March 2004: Natural Join B.V. http://www.naturaljoin.nl

Flash Me Back, Scotty - 1

Topics 1. Flashback (Versions) Query – Flashback pseudocolumns – Guaranteed undo retention – SCN <=> TIMESTAMP conversion functions

2. Flashback transaction query 3. Flashback table 4. Flashback drop – The recyclebin – Purging the recyclebin

5. Flashback database 3

Flashback (Versions) Query

Retrieve row versions back in time from undo segments Purposes: – Table auditing – Transaction mining

Levels: – Statement level – Session level

select ... from ... AS OF {SCN n|TIMESTAMP t} where ... select ... from ... VERSIONS BETWEEN {SCN {n|MINVALUE} AND {m|MAXVALUE} |TIMESTAMP t1 AND t2 } where ... dbms_flashback.ENABLE_AT_TIME(t); ... dbms_flashback.DISABLE; 4

Flash Me Back, Scotty - 2

Flashback Versions Pseudocolumns Pseudocolumn

Description

VERSIONS_STARTTIME Version validity range: Lower bound VERSIONS_STARTSCN VERSIONS_ENDTIME VERSIONS_ENDSCN

Version validity range: Upper bound

VERSIONS_XID

Transaction that created the version (useful for further investigations)

VERSIONS_OPERATION Operation that produced the version

Guaranteed Undo Retention SQL> alter tablespace undotbs1 2 RETENTION [NO]GUARANTEE; SQL> select tablespace_name, RETENTION 2 from dba_tablespaces; TABLESPACE_NAME --------------UNDOTBS1 ...

RETENTION --------GUARANTEE ...

ÂŠ2005, Natural Join B.V.

Flash Me Back, Scotty - 3

New SQL Conversion Functions Precision: about 3 seconds Retention: minimum 5 days (depends on undo retention) SCN_TO_TIMESTAMP

NUMBER -> TIMESTAMP

TIMESTAMP_TO_SCN

TIMESTAMP -> NUMBER

SQL> select current_scn, 2 SCN_TO_TIMESTAMP(current_scn) 3 from v$database; CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) ----------- ------------------------------4479369 14-DEC-04 10.27.31.000000000 PM 7

Flashback Transaction Query View database changes at the transaction level Perform transaction auditing Recover from user or application errors Derived from undo segments; faster than LogMiner Protected by a system privilege SQL> select operation, undo_sql, table_name 2 from FLASHBACK_TRANSACTION_QUERY 3 where xid = hextoraw('...'); SQL> 2 3 4

select from where and

operation, undo_sql, table_name FLASHBACK_TRANSACTION_QUERY start_timestamp >= '...' commit_timestamp <= '...'; 8

Flash Me Back, Scotty - 4

Combining Flashback Versions and Flashback Transaction SQL> 2 3 4 5 6 SQL> 2 3 4

select versions_xid , salary from hr.employees versions between scn minvalue and maxvalue where last_name = 'De Haan';

select , from where

operation, undo_sql table_name FLASHBACK_TRANSACTION_QUERY xid = hextoraw('xxxxxxxxxxxxxxxx');

Flashback Table

Recover tables to a specific point in time: fast, easy, in-place Executed as a single-statement (autocommit) transaction Exclusive DML table locks needed Triggers temporarily disabled by default; indexes maintained Recorded in the alert log file Tip: Record the current SCN first (from v$database) SQL> alter table hr.employees 2 ENABLE ROW MOVEMENT; SQL> FLASHBACK TABLE hr.employees [, ...] 2 TO {SCN n|TIMESTAMP t} 3 [{ENABLE|DISABLE} TRIGGERS] 10

Flash Me Back, Scotty - 5

Flashback Table: Error Messages SQL> flashback table hr.employees to scn ...; flashback table hr.employees * ERROR at line 1: ORA-02091: transaction rolled back ORA-02291: integrity constraint(HR.EMP_JOB_FK) violated - parent key not found SQL> flashback table hr.jobs to scn ...; flashback table hr.jobs * ERROR at line 1: ORA-02091: transaction rolled back ORA-02292: integrity constraint(HR.EMP_JOB_FK) violated - child record found 11

Flashback Table: Error Messages SQL> flashback table hr.jobs to scn ...; flashback table hr.jobs * ERROR at line 1: ORA-01466: unable to read data table definition has changed SQL> flashback table hr.employees to scn ...; flashback table hr.employees * ERROR at line 1: ORA-08183: Flashback cannot be enabled in the middle of a transaction

ÂŠ2005, Natural Join B.V.

Flash Me Back, Scotty - 6

Flashback Drop Feature

Tables (and any associated objects) are renamed and marked as dropped; space is not released Flashback drop does not depend on undo Several implicit purge policies (quota, space allocation algorithm) Recycle bin can be disabled with a hidden parameter

drop table employees; FLASHBACK table employees TO BEFORE DROP [RENAME TO ...]; 13

Flashback Drop Feature Drop objects without storing them in the recycle bin: SQL> drop table <table_name> PURGE;

Query the recycle bin: SQL> select * from [USER_|DBA_]RECYCLEBIN; SQL> show recyclebin [original_name]

Flash Me Back, Scotty - 7

Querying the Recycle Bin You can query tables in the recycle bin using object names Tables in the recycle bin are read only – Flashback queries are possible – Queries may fail with “ORA-1410: invalid ROWID” SQL> select ... 2 from "BIN$VoMP0begR7qB4RxzSz5CzQ==$0" 3 [AS OF ...] 4 where ...

PURGE Command SQL> purge {TABLE <table_name> |INDEX <index_name>}; SQL> purge TABLESPACE <ts_name> [USER <user_name>]; SQL> purge [USER_|DBA_]RECYCLEBIN;

Flash Me Back, Scotty - 8

SQL Commands and Implicit Purging The recycle bin is purged: – If you drop a tablespace or a user – If you reach your tablespace quota – If a tablespace needs more space (purge before extend) SQL> drop TABLESPACE <ts_name> [including contents]; SQL> drop USER <user_name> [cascade]

Flashback Database

Flashback database is much faster than point-in-time recovery – “Database rewind” functionality

Useful in cases of logical data corruption Useful in Data Guard configurations – Real time apply: enable flashback on standby db – Avoid recreating physical standby db after open resetlogs on primary – Convert old primary db into a new standby db after a failover

For flashback database: – The database must be in archivelog mode – The database must be mounted exclusive 18

Flash Me Back, Scotty - 9

Flashback Database You cannot use flashback database in the following situations: – – – –

The control file has been restored or re-created A tablespace has been dropped A data file has been shrunk You recovered through a RESETLOGS operation

After flashback database, you can open the database: – In read-only mode, to verify that the desired situation is reached – With a RESETLOGS operation, to allow for updates

Architectural Components Instance Flashback buffer

Flash recovery area

Database

RVWR Flashback logs

Redo log buffer

LGWR Redo log files

Flash Me Back, Scotty - 10

Defining a Flash Recovery Area You define the flash recovery area by setting parameters: – DB_RECOVERY_FILE_DEST_SIZE (size, in bytes) – DB_RECOVERY_FILE_DEST (the location) – DB_FLASHBACK_RETENTION_TARGET (in mins; default 1 day)

Centralized area for recovery purposes SQL> alter system set 2 DB_RECOVERY_FILE_DEST_SIZE=64G scope=both; SQL> alter system set 2 DB_RECOVERY_FILE_DEST='/oradata/fr_area' scope=both; SQL> select name, space_limit, space_used 2 , space_reclaimable, number_of_files 2 from V$RECOVERY_FILE_DEST; 21

Configuring Flashback Database

1. Configure flash recovery area

2. Set retention target

3. Enable flashback database

SQL> alter database FLASHBACK {ON|OFF}; SQL> select flashback_on, current_scn 2 from v$database; 22

Flash Me Back, Scotty - 11

Monitoring Flashback Database Monitor the flashback database retention target; adjust flash recovery area size when needed SQL> select oldest_flashback_scn 2 , retention_target 3 from V$FLASHBACK_DATABASE_LOG;

Monitor the overhead of logging flashback data (view contains 24 hours of information, one row per hour) SQL> select begin_time, end_time 2 , flashback_data, redo_data 3 from V$FLASHBACK_DATABASE_STAT;

Flashback Database: Examples RMAN> flashback database 2> to TIME = to_date 3> ('2004-08-11 16:15:14', 4> 'YYYY-MM-DD HH24:MI:SS'); RMAN> flashback database to SCN=87654; RMAN> flashback database 2> to SEQUENCE=42 THREAD=1; SQL> flashback database 2 to TIMESTAMP (systimestamp-2/24); SQL> flashback database to scn 76543; 24

Flash Me Back, Scotty - 12

Errors During Flashback Database SQL> flashback database to scn ...; ERROR at line 1: ORA-38729: Not enough flashback database log data to do FLASHBACK.

SQL> flashback database to scn ...; ERROR at line 1: ORA-38753: Cannot flashback data file 4; no flashback log data. ORA-01110: data file 4: '/oradata/10g/users01.dbf' ORA-38753: Cannot flashback data file 5; no flashback log data. ORA-01110: data file 5: '/oradata/10g/example01.dbf' 25

Excluding Tablespaces from Flashback Database If you disable flashback for a tablespace: – You must take it offline before you can perform a flashback database – After flashback database: drop it, or recover the offline data files with traditional point-in-time recovery SQL> alter tablespace <ts> FLASHBACK {ON|OFF}

SQL> select tablespace_name, FLASHBACK_ON 2 from v$tablespace;

Flash Me Back, Scotty - 13

Granting Flashback Privileges To use flashback features, you need the following privileges: For flashback database: – SYSDBA database connection

For flashback table or flashback versions query: – FLASHBACK ANY TABLE – Appropriate object privileges

For flashback transaction query: – SELECT ANY TRANSACTION

By the Way … Every Oracle table has a new pseudocolumn in 10g: SQL> select empno, ename, ORA_ROWSCN 2 from scott.emp;

For maximum precision, for the price of six bytes per row: SQL> create table my_emp ROWDEPENDENCIES 2 as select * from scott.emp; SQL> select empno, ename 2 , SCN_TO_TIMESTAMP(ORA_ROWSCN) 3 from scott.emp; 28

Flash Me Back, Scotty - 14

That’s All ...

Flash Me Back, Scotty - 15