What is Oracle Flashback Technology



1.What is Oracle Flashback Technology?

Oracle Flashback Technology provides a set of features available on 9i or higher version that support viewing and rewinding data back and forth in time to recover from Logical Corruptions while the database is online.

Flashback Technology gives you 6 different ways to track and fix logical corruption through different approach.

- Oracle Flashback Query feature lets you specify a target time and then run queries against your database, viewing results as they would have appeared at that time.

- Oracle Flashback Version Query lets you view all the versions of all the rows that ever existed in one or more tables in a specified time interval.

- Oracle Flashback Transaction Query lets you view changes made by a single transaction, or by all the transactions during a period of time.

- Oracle Flashback Table returns a table to its state at a previous point in time.

- Oracle Flashback Drop reverses the effects of a DROP TABLE statement.

- Oracle Flashback Database provides a more efficient alternative to database point-in-time recovery.

This Document contain the step by step example of how to enable flash back on a DB, how to flash back some deleted rows, how to flash back dropped tables and how to flash back a database for point in time recovery.

2. How to check Flashback Status.

Flashback status of a database can be checked from the below query and system parameters.

- SQL> select NAME,FLASHBACK_ON from v$database;

- SQL> archive log list

- SQL> show parameter undo_retention

- SQL> show parameter DB_FLASHBACK_RETENTION_TARGET

- SQL> show parameter db_recovery_file_dest

- SQL> show parameter db_recovery_file_dest_size

3. How to enable Flashback Feature on a Database.

- The Database must be started through SPFILE.

SQL> show parameter pfile

NAME TYPE VALUE

------------------------ ----------- ------------------------------

spfile string /opt/oracle/product10gR2/dbs/spfileboxirep.ora

- The Database must be in Archive log mode.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /itlinux68ar01/ORACLE/boxirep/arch

Oldest online log sequence 645

Next log sequence to archive 647

Current log sequence 647

SQL>

- Undo management should be AUTO

SQL> show parameter undo_management

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

- Set the Recovery file destination or flashback area which will contain all flash back logs depending on the undo retention period.

SQL> alter system set db_recovery_file_dest='/itlinux68db05/ORACLE/flashback' scope=both;

- Set the recovery file destination size. This is the hard limit on the total space to be used by target database recovery files created in the flash recovery area.

SQL> alter system set db_recovery_file_dest_size=3G scope=both;

System altered.

- Set the flash back retention target . This is the upper limit (in minutes) on how far back in time the database may be flashed back. How far back one can flashback a database depends on how much flashback data Oracle has kept in the flash recovery area.

SQL> alter system set db_flashback_retention_target=1440 scope=both;

- Convert the Database to FLASH BACK ON state.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 830472192 bytes

Fixed Size 2074760 bytes

Variable Size 213911416 bytes

Database Buffers 608174080 bytes

Redo Buffers 6311936 bytes

Database mounted.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select NAME, FLASHBACK_ON from v$database;

NAME FLASHBACK_ON

--------- ------------------

BOXIREP YES

SQL>

4. Row level recovery using Flashback Query.

User has deleted some data mistakenly on one table before some times and them to recover.

- Connect to the schema and identify the table .

SQL> connect fsbtest/fsbtest@boxirep

Connected.

SQL> CREATE TABLE FTEST ( OBJECTID NUMBER NOT NULL, ALIAS VARCHAR2 (20)) TABLESPACE USERS;

Table created.

SQL> SELECT TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD

-------------------

05-02-2008 10:21:46

SQL> insert into ftest values(101,'TABLE');

insert into ftest values(102,'TABLE');

insert into ftest values(103,'TABLE');

insert into ftest values(104,'TABLE');

1 row created.

1 row created.

1 row created.

1 row created.

SQL>

SQL> select * from ftest;

OBJECTID ALIAS

---------- --------------------

101 TABLE

102 TABLE

103 TABLE

104 TABLE

SQL> commit;

Commit complete.

- Delete some data and commit.

SQL> delete from ftest where OBJECTID in(103,104);

2 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from ftest;

OBJECTID ALIAS

---------- --------------------

101 TABLE

102 TABLE

SQL> SELECT TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD

-------------------

05-02-2008 10:23:45

- Insert some more Data and commit.

SQL> insert into ftest values(105,'VIEW');

insert into ftest values(106,'VIEW');

insert into ftest values(107,'VIEW');

3 rows created.

SQL> SELECT TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD

-------------------

05-02-2008 10:24:41

- Now try to identify the deleted records as requested by the user in flashback area.

select versions_starttime, versions_endtime, versions_xid,versions_operation, objectid

from ftest versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME

/

VERSIONS_STARTTIME

---------------------------------------------------------------------------

VERSIONS_ENDTIME VERSIONS_XID V

--------------------------------------------------------------------------- ---------------- -

OBJECTID

----------

05-FEB-08 10.22.26 AM

01000E00280A0000 I

101

05-FEB-08 10.22.26 AM

05-FEB-08 10.23.02 AM 01000E00280A0000 I

103

VERSIONS_STARTTIME

---------------------------------------------------------------------------

VERSIONS_ENDTIME VERSIONS_XID V

--------------------------------------------------------------------------- ---------------- -

OBJECTID

----------

05-FEB-08 10.22.26 AM

05-FEB-08 10.23.02 AM 01000E00280A0000 I

104

05-FEB-08 10.22.26 AM

01000E00280A0000 I

- Query these records using timestamp before actually recovering them. Use the VERSIONS_ENDTIME column value of the last query.

SQL> SELECT * FROM ftest AS OF TIMESTAMP TO_TIMESTAMP('05-02-2008 10:23:02','DD-MM-YYYY HH:MI:SS');

OBJECTID ALIAS

---------- --------------------

101 TABLE

102 TABLE

103 TABLE

104 TABLE

SQL>

- Now insert back these records to the original table. You may insert these records to a new table in case the DML statement is an update to compare the old value with the new value.

SQL>INSERT INTO ftest (SELECT * FROM ftest AS OF TIMESTAMP TO_TIMESTAMP('05-02-2008 10:23:02','DD-MM-YYYY HH:MI:SS');

4 rows created.

- Oracle Flashback Table: Returning Individual Tables to Past States

- Identify the table to flashback and check the prerequisite.

SQL> select OWNER,TABLE_NAME,STATUS,PARTITIONED,ROW_MOVEMENT from dba_tables

2 where TABLE_NAME like '%FTEST%';

OWNER TABLE_NAME STATUS PAR

------------------------------ ------------------------------ -------- ---

ROW_MOVE

--------

FSBTEST FTEST VALID NO

DISABLED

SQL> grant flashback on fsbtest.ftest to fsbtest;

Grant succeeded.

SQL> alter table fsbtest.ftest enable row movement;

Table altered.

SQL>SELECT * FROM ftest AS OF TIMESTAMP TO_TIMESTAMP('05-02-2008 10:23:02','DD-MM-YYYY HH:MI:SS');

- Flash back the table till that time stamp

SQL> connect fsbtest/fsbtest

Connected.

SQL> FLASHBACK TABLE ftest TO TIMESTAMP TO_TIMESTAMP('05-02-2008 10:22:45','DD-MM-YYYY HH:MI:SS');

Flashback complete.

SQL>

SQL> select * from ftest;

OBJECTID ALIAS

---------- --------------------

101 TABLE

102 TABLE

103 TABLE

104 TABLE

5.Undo a DROP TABLE Operation using FLASH BACK

- Check the recycle bin to confirm that the dropped object has not been purged.

SQL> create table ftest_drop as select * from ftest;

Table created.

SQL> drop table ftest_drop;

Table dropped.

SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME

------------------------------ --------------------------------

BIN$RWou3ZujSqXgQBQKHZhmkg==$0 FTEST_DROP

BIN$RF6Njc00PfngQBQKHZgNEg==$0 DROP_TEST

SQL>

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------

DROP_TEST BIN$RF6Njc00PfngQBQKHZgNEg==$0 TABLE 2008-01-23:10:08:24

FTEST_DROP BIN$RWou3ZujSqXgQBQKHZhmkg==$0 TABLE 2008-02-05:17:26:07

SQL>

- Check that Dropped table contains all rows.

SQL> SELECT * FROM "BIN$RWou3ZujSqXgQBQKHZhmkg==$0";

OBJECTID ALIAS

---------- --------------------

101 TABLE

102 TABLE

103 TABLE

104 TABLE

SQL>

- Flash back the table to it’s before drop state.

SQL> FLASHBACK TABLE fsbtest.ftest_drop TO BEFORE DROP;

Flashback complete.

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------

DROP_TEST BIN$RF6Njc00PfngQBQKHZgNEg==$0 TABLE 2008-01-23:10:08:24

SQL> select TABLE_NAME, STATUS, DROPPED from user_tables where TABLE_NAME like'%FTEST_DROP%';

TABLE_NAME STATUS DRO

------------------------------ -------- ---

FTEST_DROP VALID NO

SQL>

We can also use globalUID Name to flash back in case of Multiple Objects With the Same Original Name.

FLASHBACK TABLE "BIN$RWou3ZujSqXgQBQKHZhmkg==$0" TO BEFORE DROP

RENAME TO fsbtest.ftest1;

6. Freeing Space in the Recycle Bin.

SQL> PURGE TABLE ftest;

SQL> PURGE TABLE “BIN$RWou3ZujSqXgQBQKHZhmkg==$0”;

SQL> PURGE TABLESPACE USER; - - All deleted objects of this tablespace.

SQL> PURGE RECYCLEBIN;

SQL> PURGE DBA_RECYCLEBIN;

7. Oracle Flashback Database to facilitate Point-In-Time Recovery

Oracle Flashback Database, accessible from both RMAN (by means of the FLASHBACK DATABASE command) and SQL*Plus (by means of the FLASHBACK DATABASE statement), and is used to quickly recover the entire database from logical data corruptions or user errors. It does not help in case of Physical Data corruption or Media failure.

To enable Flashback Database, you set up a flash recovery area, and set a flashback retention target, to specify how far back into the past you want to be able to restore your database with Flashback Database.

- Check the Pre flashback steps.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS

------------

OPEN

SQL> select NAME,FLASHBACK_ON from v$database;

NAME FLASHBACK_ON

--------- ------------------

BOXIREP YES

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /itlinux68ar01/ORACLE/boxirep/arch

Oldest online log sequence 697

Next log sequence to archive 699

Current log sequence 699

SQL> show parameter undo_retention

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_retention integer 172800

SQL> show parameter DB_FLASHBACK_RETENTION_TARGET

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_flashback_retention_target integer 1440

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /itlinux68db05/ORACLE/flashback

db_recovery_file_dest_size big integer 3G

- Determining the Current Flashback Database Window

SQL> SELECT OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,'DD-MON-YYYY HH:MI:SS') FROM V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN TO_CHAR (OLDEST_FLASH

-------------------- --------------------

11259774 03-FEB-2008 04:29:38

SQL>

- Select current SCN Of the Database.

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN

-----------

11676843

SQL>

Execute the FLASHBACK DATABASE Command from RMAN

$ rman TARGET /

RMAN> FLASHBACK DATABASE

   2> TO TIME = TO_DATE

   3> '05-02-2008 10:23:45','DD-MM-YYYY HH:MI:SS');

In SQL mode The database must be in mount state to issue these commands:

SQL> FLASHBACK DATABASE TO SCN 11259789;

SQL> FLASHBACK DATABASE TO SEQUENCE 5304;

SQL> FLASHBACK DATABASE TO TIME (SYSDATE-1/24);

SQL> FLASHBACK DATABASE TO TIME TO_TIMESTAMP('05-02-2008 10:23:45','DD-MM-YYYY HH:MI:SS');

You must issue the following command afterwards:

SQL> ALTER DATABASE RESETLOGS;

- Verify the SCN after the Recovery.

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

NOTE: Flashback database cannot be used in order to flashback through

1. Recreation of controlfile

2. Drop tablespace

3. Shrunk datafile

4. Resetlogs

******************************** END *****************************************

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download