Flashback feature depends upon on how much undo retention time you have specified. If you have set the UNDO_RETENTION parameter to 2 hours then, Oracle will not overwrite the data in undo tablespace even after committing until 2 Hours have passed. Users can recover from their mistakes made since last 2 hours only.
For example, suppose John gives a delete statement at 10 AM and commits it. After 1 hour he realizes that delete statement is mistakenly performed. Now he can give a flashback AS.. OF query to get back the deleted rows like this.
Flashback Query
SQL>select * from emp as of timestamp sysdate-1/24;
Or
SQL> SELECT * FROM emp AS OF TIMESTAMP
TO_TIMESTAMP('2007-06-07 10:00:00', 'YYYY-MM-DD HH:MI:SS')
To insert the accidently deleted rows again in the table he can type
SQL> insert into emp (select * from emp as of timestamp sysdate-1/24)
Using Flashback Version Query
You use a Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A new row version is created whenever a COMMIT statement is executed.
The Flashback Version Query returns a table with a row for each version of the row that existed at any time during the time interval you specify. Each row in the table includes pseudocolumns of metadata about the row version. The pseudocolumns available are
VERSIONS_XID :Identifier of the transaction that created the row version
VERSIONS_OPERATION :Operation Performed. I for Insert, U for Update, D for Delete
VERSIONS_STARTSCN :Starting System Change Number when the row version was created
VERSIONS_STARTTIME :Starting System Change Time when the row version was created
VERSIONS_ENDSCN :SCN when the row version expired.
VERSIONS_ENDTIME :Timestamp when the row version expired
To understand let’s see the following example
Before Starting this example let’s us collect the Timestamp
SQL> select to_char(SYSTIMESTAMP,’YYYY-MM-DD HH:MI:SS’) from dual;
TO_CHAR(SYSTIMESTAMP,’YYYYY
---------------------------
2007-06-19 20:30:43
Suppose a user creates a emp table and inserts a row into it and commits the row.
SQL> Create table emp (empno number(5),name varchar2(20),sal
number(10,2));
SQL> insert into emp values (101,’Sami’,5000);
SQL>commit;
At this time emp table has one version of one row.
Now a user sitting at another machine erroneously changes the Salary from 5000 to 2000 using Update statement
SQL> update emp set sal=sal-3000 where empno=101;
SQL> commit;
Subsequently, a new transaction updates the name of the employee from Sami to Smith.
SQL>update emp set name=’Smith’ where empno=101;
SQL> commit;
At this point, the DBA detects the application error and needs to diagnose the problem. The DBA issues the following query to retrieve versions of the rows in the emp table that correspond to empno 101. The query uses Flashback Version Query pseudocolumns
SQL> Connect / as sysdba
SQL> column versions_starttime format a16
SQL> column versions_endtime format a16
SQL> set linesize 120;
SQL> select versions_xid,versions_starttime,versions_endtime,
versions_operation,empno,name,sal from emp versions between
timestamp to_timestamp(‘2007-06-19 20:30:00’,’yyyy-mm-dd hh:mi:ss’)
and to_timestamp(‘2007-06-19 21:00:00’,’yyyy-mm-dd hh:mi:ss’);
VERSION_XID V STARTSCN ENDSCN EMPNO NAME SAL
----------- - -------- ------ ----- -------- ----
0200100020D U 11323 101 SMITH 2000
02001003C02 U 11345 101 SAMI 2000
0002302C03A I 12320 101 SAMI 5000
The Output should be read from bottom to top, from the output we can see that an Insert has taken place and then erroneous update has taken place and then again update has taken place to change the name.
The DBA identifies the transaction 02001003C02 as erroneous and issues the following query to get the SQL command to undo the change
SQL> select operation,logon_user,undo_sql
from flashback_transaction_query
where xid=HEXTORAW(’02001003C02’);
OPERATION LOGON_USER UNDO_SQL
--------- ---------- ---------------------------------------
U SCOTT update emp set sal=5000 where ROWID =
'AAAKD2AABAAAJ29AAA'
Now DBA can execute the command to undo the changes made by the user
SQL> update emp set sal=5000 where ROWID ='AAAKD2AABAAAJ29AAA'
1 row updated
Using Flashback Table to return Table to Past States.
Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations.
Flashback Table uses information in the undo tablespace to restore the table. Therefore, UNDO_RETENTION parameter is significant in Flashing Back Tables to a past state. You can only flash back tables up to the retention time you specified.
Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement:
ALTER TABLE table ENABLE ROW MOVEMENT;
The following example performs a FLASHBACK TABLE operation the table emp
FLASHBACK TABLE emp TO TIMESTAMP
TO_TIMESTAMP('2007-06-19 09:30:00', `YYYY-MM-DD HH24:MI:SS');
The emp table is restored to its state when the database was at the time specified by the timestamp.
Example:-
At 17:00 an HR administrator discovers that an employee "JOHN" is missing from the EMPLOYEE table. This employee was present at 14:00, the last time she ran a report. Someone accidentally deleted the record for "JOHN" between 14:00 and the present time. She uses Flashback Table to return the table to its state at 14:00, as shown in this example:
FLASHBACK TABLE EMPLOYEES TO TIMESTAMP
TO_TIMESTAMP('2007-06-21 14:00:00','YYYY-MM-DD HH:MI:SS')
ENABLE TRIGGERS;
You have to give ENABLE TRIGGERS option otherwise, by default all database triggers on the table will be disabled.
Recovering Drop Tables (Undo Drop Table)
In Oracle Ver. 10g Oracle introduced the concept of Recycle Bin i.e. whatever tables you drop the database does not immediately remove the space used by table. Instead, the table is renamed and placed in Recycle Bin. The FLASHBACK TABLE…BEFORE DROP command will restore the table.
This feature is not dependent on UNDO TABLESPACE so UNDO_RETENTION parameter has no impact on this feature.
To Recover Drop tables the RECYCLEBIN parameter must be turned ON, otherwise you will not be able to recover drop tables
For Example, suppose a user accidently drops emp table
SQL>drop table emp;
Table Dropped
Now for user it appears that table is dropped but it is actually renamed and placed in Recycle Bin. To recover this dropped table a user can type the command
SQL> Flashback table emp to before drop;
You can also restore the dropped table by giving it a different name like this
SQL> Flashback table emp to before drop rename to emp2;
Purging Objects from Recycle Bin
If you want to recover the space used by a dropped table give the following command
SQL> purge table emp;
If you want to purge objects of logon user give the following command
SQL> purge recycle bin;
If you want to recover space for dropped objects of a particular tablespace give the command
SQL> purge tablespace hr;
You can also purge only objects from a tablespace belonging to a specific user, using the following form of the command:
SQL> PURGE TABLESPACE hr USER scott;
If you have the SYSDBA privilege, then you can purge all objects from the recycle bin, regardless of which user owns the objects, using this command:
SQL> PURGE DBA_RECYCLEBIN;
To view the contents of Recycle Bin give the following command
SQL> show recycle bin;
Permanently Dropping Tables
If you want to permanently drop tables without putting it into Recycle Bin, drop tables with purge command like this
SQL> drop table emp purge;
This will drop the table permanently and it cannot be restored.
Flashback Drop of Multiple Objects With the Same Original Name
You can create, and then drop, several objects with the same original name, and they will all be stored in the recycle bin. For example, consider these SQL statements:
CREATE TABLE EMP ( ...columns ); # EMP version 1
DROP TABLE EMP;
CREATE TABLE EMP ( ...columns ); # EMP version 2
DROP TABLE EMP;
CREATE TABLE EMP ( ...columns ); # EMP version 3
DROP TABLE EMP;
In such a case, each table EMP is assigned a unique name in the recycle bin when it is dropped. You can use a FLASHBACK TABLE... TO BEFORE DROP statement with the original name of the table, as shown in this example:
FLASHBACK TABLE EMP TO BEFORE DROP;
The most recently dropped table with that original name is retrieved from the recycle bin, with its original name. You can retrieve it and assign it a new name using a RENAME TO clause. The following example shows the retrieval from the recycle bin of all three dropped EMP tables from the previous example, with each assigned a new name:
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VER_3;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VER_2;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VER_1;
Important Points:
1. There is no guarantee that objects will remain in Recycle Bin. Oracle might empty recycle bin whenever Space Pressure occurs i.e. whenever tablespace becomes full and transaction requires new extents then, oracle will delete objects from recycle bin
2. A table and all of its dependent objects (indexes, LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together.
3. There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months
Posts
0 comments:
Post a Comment