As I have cover the "
Architecture Of Flashback" in Oracle 10g in my previous post. Here i am going further to explain and perform the some demo of the flashback features of Oracle
10g.
Flashback status of a database can be checked from the below query and system parameters.
SQL>
select NAME,FLASHBACK_ON from v$database ;
SQL>
show parameter undo_retention
NAME TYPE VALUE
--------------- ---------- -----------
undo_retention integer 900
SQL>
show parameter db_flashback_retention
NAME TYPE VALUE
------------------------------------ -------- ---------
db_flashback_retention_target integer 1440
SQL>
show parameter db_recovery_file_dest
NAME TYPE VALUE
---------------------------- --------- -----------------------------------------------------
db_recovery_file_dest string D:\oracle\product\10.2.0\flash_recovery_area
db_recovery_file_dest_size big integer 5G
If the database Flashback feature is off then follow the below steps :
1.) The Database must be started through SPFILE.
SQL>
show parameter spfile
NAME TYPE VALUE
--------- --------- ----------------------------------------------
spfile string D:\ORACLE\PRODUCT\10.2.0\DB_1\
DATABASE\SPFILENOIDA.ORA
2.) The Database must be in Archive log mode.
SQL>
shut immediate
SQL>
startup mount
SQL>
alter database archivelog ;
SQL>
alter database open ;
3.) Undo management should be AUTO
SQL> show parameter undo_management
NAME TYPE VALUE
-------------------- --------- ----------
undo_management string AUTO
4.) Set the Recovery file destination or flashback area which will contain all flashback logs depending on the undo retention period
SQL> alter system set db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area' scope=both;
System altered.
5.) 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=5G scope=both;
System altered.
6.) 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;
System altered.
7.) Convert the Database to FLASHBACK 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>
select NAME, FLASHBACK_ON from v$database ;
NAME FLASHBACK_ON
--------- ----------------------
NOIDA YES
Flashback technology provides a set of features to view and rewind data back and forth in time. The flashback features offer the capability to query past versions of schema objects, query historical data, perform change analysis, and perform self-service repair to recover from logical corruption while the database is online.Here we will discuss some more features of FlashBack .
The Flashback features are :
1.) Flashback Query
2.) Flashback Version Query
3.) Flashback Transaction Query
4.)Flashback Table
5.) Flashback Drop (Recycle Bin)
6.) Flashback Database
7.) Flashback Query Functions
1.) Flashback Query : Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause. Essentially it is the same as the DBMS_FLASHBACK functionality , but in a more convenient form. For example, Here is a Demo of Flashback Query :
SQL>
CREATE TABLE flashback_query_test (id NUMBER(10));
Table created.
SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
------------------- ------------------------------
1365842 2011-08-12 13:44:15
SQL>
INSERT INTO flashback_query_test (id) VALUES (1);
1 row created.
SQL>
commit;
Commit complete.
SQL>
SELECT COUNT(*) FROM flashback_query_test;
COUNT(*)
----------
1
SQL>
SELECT COUNT(*) FROM flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP('2011-08-12 13:44:15', 'YYYY-MM-DD HH24:MI:SS');
COUNT(*)
----------
0
SQL>
SELECT COUNT(*) FROM flashback_query_test AS OF SCN 1365842;
COUNT(*)
----------
0
2.) Flashback Version Query : Oracle Flashback Versions Query is an extension to SQL that can be used to retrieve the versions of rows in a given table that existed in a specific time interval. Oracle Flashback Versions Query returns a row for each version of the row that existed in the specified time interval. For any given table, a new row version is created each time the COMMIT statement is executed. Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS
BETWEEN clause . Here is Demo of Flashback Version Query :
SQL> CREATE TABLE flashback_version_query_test (id NUMBER(10),description VARCHAR2(50));
Table created.
SQL>
INSERT INTO flashback_version_query_test (id, description) VALUES (1, 'ONE');
1 row created.
SQL>
COMMIT;
Commit complete.
SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAMP)
------------------ ---------------------------------
1366200 2011-08-12 13:53:16
SQL>
UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;
1 row updated.
SQL>
COMMIT;
Commit complete.
SQL>
UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;
1 row updated.
SQL>
COMMIT;
Commit complete.
SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
------------------- ---------------------------------
1366214 2011-08-12 13:54:38
SQL>
SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_operation, description FROM flashback_version_query_test
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2011-08-12 13:53:11', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2011-08-12 13:54:38', 'YYYY-MM-DD HH24:MI:SS')
WHERE id = 1;
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_OPERATION DESCRIPTION
1366212 12.08.11 13:53:35.000 U THREE
1366209 12.08.11 13:53:35.000 1366212 12.08.11 13:53:35.000
U TWO
1366209 12.08.11 13:53:35.000 ONE
3 rows selected
The available pseudocolumn meanings are:
- VERSIONS_STARTSCN or VERSIONS_STARTTIME - Starting SCN and TIMESTAMP when row took on this value. The value of NULL is returned if the row was created before the lower bound SCN or TIMESTAMP.
- VERSIONS_ENDSCN or VERSIONS_ENDTIME - Ending SCN and TIMESTAMP when row last contained this value. The value of NULL is returned if the value of the row is still current at the upper bound SCN ot TIMESTAMP.
- VERSIONS_XID - ID of the transaction that created the row in it's current state.
- VERSIONS_OPERATION - Operation performed by the transaction ( (I)nsert, (U)pdate or (D)elete) .
3.) Flashback Transaction Query : Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries. The VERSIONS_XID column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY view.
SQL>
SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('0600030021000000');
XID OPERATION START_SCN COMMIT_SCN LOGON_USER
--------------- -------------- -------------- ---------------- --------------
UNDO_SQL
--------------
0600030021000000 UPDATE 725208 725209 SCOTT
update "SCOTT"."FLASHBACK_VERSION_QUERY_TEST" set "DESCRIPTION" = 'ONE' where ROWID = 'AAAMP9AAEAAAAAYAAA' ;
1 rows selected.
4.) Flashback Table : There are two distinct table related flashback table features in oracle, flashback table which relies on undo segments and flashback drop which lies on the recyclebin not the undo segments.
Flashback table lets we recover a table to a previous point in time, we don't have to take the tablespace offline during a recovery, however oracle acquires exclusive DML locks on the table or tables that we are recovering, but the table continues to be online. When using flashback table oracle does not preserve the ROWIDS when it restores the rows in the changed data blocks of the tables, since it uses DML operations to perform its work, we must have enabled row movement in the tables that we are going to flashback, only flashback table requires we to enable row movement. If the data is not in the undo segments then we cannot recover the table by using flashback table, however we can use other means to recover the table.
Restriction on flashback table recovery : we cannot use flashback table on SYS objects we cannot flashback a table that has had preceding DDL operations on the table like table structure changes, dropping columns, etc The flashback must entirely exceed or it will fail, if flashing back multiple tables all tables must be flashed back or none. Any constraint violations will abort the flashback operation we cannot flashback a table that has had any shrink or storage changes to the table (pct-free, initrans and maxtrans. The following example creates a table, inserts some data and flashbacks to a point prior to the data insertion. Finally it flashbacks to the time after the data insertion.Here is demo of the Flashback Table :
SQL>
CREATE TABLE flashback_table_test (id NUMBER(10));
Table created.
SQL>
ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;
Table altered.
SQL>
SELECT current_scn FROM v$database;
CURRENT_SCN
---------------
1368791
SQL>
INSERT INTO flashback_table_test (id) VALUES (1);
1 row created.
SQL>
COMMIT;
Commit complete.
SQL>
SELECT current_scn FROM v$database;
CURRENT_SCN
----------------
1368802
SQL>
FLASHBACK TABLE flashback_table_test TO SCN 1368791;
Flashback complete.
SQL>
SELECT COUNT(*) FROM flashback_table_test;
COUNT(*)
----------
0
SQL>
FLASHBACK TABLE flashback_table_test TO SCN 1368802;
Flashback complete.
SQL>
SELECT COUNT(*) FROM flashback_table_test;
COUNT(*)
-------------
1
Flashback of tables can also be performed using timestamps.
SQL>
FLASHBACK TABLE flashback_table_test TO TIMESTAMP TO_TIMESTAMP('2004-03-03 10:00:00', 'YYYY-MM-DD HH:MI:SS');
5.) Flashback Drop (Recycle Bin) : Prior to Oracle 10g, a DROP command permanently removed objects from the database. In Oracle 10g, a DROP command places the object in the recycle bin. The extents allocated to the segment are not reallocated until we purge the object. we can restore the object from the recycle bin at any time. This feature eliminates the need to perform a point-in-time recovery operation. Therefore, it has minimum impact to other database users.
In Oracle 10g the default action of a DROP TABLE command is to move the table to the recycle bin (or rename it), rather than actually dropping it. The PURGE option can be used to permanently drop a table.
The recycle bin is a logical collection of previously dropped objects, with access tied to the DROP privilege. The contents of the recycle bin can be shown using the SHOW RECYCLEBIN command and purged using the PURGE TABLE command. As a result, a previously dropped table can be recovered from the recycle bin.
Recycle Bin : A recycle bin contains all the dropped database objects until :
- we permanently drop them with the PURGE command.we
- recover the dropped objects with the UNDROP command.
- There is no room in the tablespace for new rows or updates to existing rows.
- The tablespace must be extended.
- We can view the dropped objects in the recycle bin from two dictionary views:
user_recyclebin — list all dropped user objects.
dba_recyclebin — list all dropped system-wide objects.
Here is Demo of Flashback Drop :
SQL>
CREATE TABLE flashback_drop_test ( 2 id NUMBER(10) ) ;
Table created.
SQL>
INSERT INTO flashback_drop_test (id) VALUES (1) ;
1 row created.
SQL>
COMMIT ;
Commit complete.
SQL>
DROP TABLE flashback_drop_test ;
Table dropped.
SQL>
SHOW RECYCLEBIN ;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROPTIME
---------------------- ------------------------------------ ------------- ---------------
flashback_drop_test BIN$KEZB6YXdRfW1925mCoGOlg==$0 table 201108:15:58:31EST
SQL>
FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;
Flashback complete.
SQL>
SELECT * FROM flashback_drop_test;
ID
----------
1
If an object is dropped and recreated multiple times all dropped versions will be kept in the recycle bin, subject to space. Where multiple versions are present it's best to reference the tables via the recyclebin_name. For any references to the ORIGINAL_NAME it is assumed the most recent object is drop version in the referenced question. During the flashback operation the table can be renamed.
FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test_old;
Several purge options exist :
PURGE TABLE tablename; -- Specific table.
PURGE INDEX indexname; -- Specific index.
PURGE TABLESPACE ts_name; -- All tables in a specific tablespace.
PURGE TABLESPACE ts_name USER username; -- All tables in a specific tablespace for a specific user.
PURGE RECYCLEBIN; -- The current users entire recycle bin.
PURGE DBA_RECYCLEBIN; -- The whole recycle bin.
Several restrictions apply relating to the
recycle bin :
- Only available for non-system, locally managed tablespaces.
- There is no fixed size for the recycle bin. The time an object remains in the recycle bin can vary.
- The objects in the recycle bin are restricted to query operations only (no DDL or DML).
- Flashback query operations must reference the recycle bin name.
- Tables and all dependent objects are placed into, recovered and purged from the recycle bin at the same time.
- Tables with Fine Grained Access policies are not protected by the recycle bin.
- Partitioned index-organized tables are not protected by the recycle bin.
- The recycle bin does not preserve referential integrity .
- Flashback Database
6.) The FLASHBACK DATABASE : Flashback Database command is a fast alternative to performing an incomplete recovery. In order to flashback the database we must have SYSDBA privilege and the flash recovery area must have been prepared in advance.The database can be taken back in time by reversing all work done sequentially. The database must be opened with resetlogs as if an incomplete recovery has happened. This is ideal if we have a database corruption (wrong transaction, etc) and require the database to be rewound before the corruption occurred. If we have media or a physical problem a normal recovery is required.
Flashback database is not enabled by default, when enabled flashback database a process (RVWR – recovery Writer) copies modified blocks to the flashback buffer. This buffer is then flushed to disk (flashback logs). Remember the flashback logging is not a log of changes but a log of the complete block images. Not every changed block is logged as this would be too much for the database to cope with, so only as many blocks are copied such that performance is not impacted. Flashback database will construct a version of the data files that is just before the time we want. The data files probably will be in a inconsistent state as different blocks will be at different SCN’s, to complete the flashback process, Oracle then uses the redo logs to recover all the blocks to the exact time requested thus synchronizing all the data files to the same SCN. Archiving mode must be enabled to use flashback database. An important note to remember is that Flashback can never reserve a change only to redo them.
The advantage in using flashback database is speed and convenience with which we can take the database back in time.we can use rman, sql and Enterprise manager to flashback a database. If the flash recovery area does not have enough room the database will continue to function but flashback operations may fail. It is not possible to flashback one tablespace, we must flashback the whole database. If performance is being affected by flashback data collection turn some tablespace flashbacking off .
we cannot undo a resized data file to a smaller size. When using ‘backup recovery area’ and ‘backup recovery files’ controlfiles , redo logs, permanent files and flashback logs will not be backed up.
SQL>
CREATE TABLE flashback_database_test (id NUMBER(10));
Table created.
SQL>
conn / as sysdba
SQL>
shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
startup mount exclusive;
Database mounted.
SQL>
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12) ; -----( 5 min back)
Flashback complete.
SQL>
alter database open resetlogs ;
Database altered.
SQL>
conn neer/neer@noida
Connected.
SQL>
desc flashback_database_test
ERROR : ORA-04043 : object flashback_database_test does not exist .
Some other
variations of the flashback database command includes :
- 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;
The window of time that is available for flashback is determined by the db_flashback_retention_target parameter . The maximum flashback can be determined by querying the v$flashback_database_log view . It is only possible to flashback to a point in time after flashback was enabled on the database and since the last RESETLOGS command.
7.)Flashback Query Functions : The TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions have been added to SQL and PL/SQL to simplify flashback operations.
SQL>
selet * from emp as of scn timestamp_to_scn(systimestamp - 1/24) ;
SQL>
select * from emp as of timestamp scn_to_timestamp(9945365);
SQL>
declare
l_scn number ;
l_timestamp timestamp;
begin
l_scn := timestamp_to_scn(systimestamp - 1/24);
l_timestamp := scn_to_timestamp(l_scn);
end ;
/
Enjoy
:-)
