Saturday, 6 December 2014

FLASHBACK FEATURES - Oracle database

FLASHBACK QUERY

1.      CREATE TABLE flashback_query_test (  id  NUMBER(10));

2.      SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
     722452 2014-09-04 13:34:12

3.      INSERT INTO flashback_query_test (id) VALUES (1);

4.      COMMIT;

5.      SELECT COUNT(*) FROM flashback_query_test;

  COUNT(*)
----------
         1

6.      SELECT COUNT(*) FROM flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP('2014-09-04 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

  COUNT(*)
----------
         0

7.      SELECT COUNT(*) FROM flashback_query_test AS OF SCN 722452;

  COUNT(*)
----------
         0

FLASHBACK VERSION QUERY1.      CREATE TABLE flashback_version_query_test (  id           NUMBER(10),
  description  VARCHAR2(50));

2.      INSERT INTO flashback_version_query_test (id, description) VALUES (1, 'ONE');

3.      COMMIT;

4.      SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;


5.      UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;

6.      COMMIT;

7.      UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;

8.      COMMIT;

9.      SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;


COLUMN versions_startscn FORMAT 99999999999999999
COLUMN versions_starttime FORMAT A24
COLUMN versions_endscn FORMAT 99999999999999999
COLUMN versions_endtime FORMAT A24
COLUMN versions_xid FORMAT A16
COLUMN versions_operation FORMAT A1
COLUMN description FORMAT A11
SET LINESIZE 200

10.  SELECT versions_startscn, versions_starttime,
       versions_endscn, versions_endtime,
       versions_xid, versions_operation,
       description
       FROM   flashback_version_query_test
       VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2014-09-04 14:59:08', 'YYYY-MM-DD HH24:MI:SS')
       AND TO_TIMESTAMP('2014-09-04 14:59:36', 'YYYY-MM-DD HH24:MI:SS')
       WHERE  id = 1;

11.  SELECT versions_startscn, versions_starttime,
       versions_endscn, versions_endtime,
       versions_xid, versions_operation,
       description
       FROM   flashback_version_query_test
       VERSIONS BETWEEN SCN 725202 AND 725219
       WHERE  id = 1;

FLASHBACK TRANSACTION QUERY
1.      SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM   flashback_transaction_query
WHERE  xid = HEXTORAW('0600030021000000');

2.      update "SCOTT"."FLASHBACK_VERSION_QUERY_TEST" set "DESCRIPTION" = 'ONE' where ROWID = 'AAAMP9AAEAAAA
AYAAA';

FLASHBACK TABLEFlashback table requires following privileges
1) FLASHBACK ANY TABLE or FLASHBACK object
2) SELECT,INSERT,DELETE and ALTER privs on table
3) Row movement must be enabled
CREATE TABLE flashback_table_test (
  id  NUMBER(10)
);

ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;

SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
     715315

INSERT INTO flashback_table_test (id) VALUES (1);
COMMIT;

SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
     715340

FLASHBACK TABLE flashback_table_test TO SCN 715315;

SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
         0

FLASHBACK TABLE flashback_table_test TO SCN 715340;

SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
         1
FLASHBACK DATABASE

  • Database must be in archivelog mode and flashback should be enabled for performing this. When placed in flashback mode, we can observe flashback logs getting generated in flash_recovery_area
-- Create a dummy table.
CONN scott/tiger
CREATE TABLE flashback_database_test (
  id  NUMBER(10)
);

-- Flashback 5 minutes.
CONN sys/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
ALTER DATABASE OPEN RESETLOGS;

-- Check that the table is gone.
CONN scott/tiger
DESC flashback_database_test
We can use following commands also in flashback database
FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;


Popular Posts