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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- what is a technology roadmap
- what is educational technology pdf
- what is information technology pdf
- what is instructional technology pdf
- what is technology integration education
- what is information technology quizlet
- what is emerging technology wikipedia
- what is technology transfer pdf
- what is technology assessment
- what is oracle 19c
- what is technology stack management
- what is education technology pdf