Flashback Database allows the DBA to “roll back” a table, set of tables or the entire database to a previous point-in-time.
First of all change the undo_retention parameter of your database.
SQL>show parameter undo_retention;
# The default value is 900 seconds.
SQL>alter system set undo_retention=2400 scope=both;
# I am changing it to 40 minutes which equals 2400 seconds.
Also enable the retention guarantee parameter for your database'e undo tablespace.
SQL>show parameter tablespace;
# My current undo tablespace is named as UNDOTBS1
SQL>select tablespace_name, retention from dba_tablespaces
where tablespace_name = 'UNDOTBS1';
#shows NOGUARANTEE which means retention guarantee
#is not enabled.
SQL>alter tablespace undotbs1 retention guarantee; #enable the retention guarantee with the above command.
SQL>select tablespace_name, retention from dba_tablespaces
where tablespace_name = 'UNDOTBS1';
# shows GUARANTEE
FLASHBACK QUERY Concept :
In oracle 10g, the "as of" clause is available in a select query to retrieve the state of a table as of a given timestamp or SCN.
Unlock the users to work with.
SQL>alter user hr identified by hr account unlock;
SQL>alter user scott identified by tiger account unlock;
SQL>grant insert, update, delete, select
on hr.employees to scott;
SQL>grant insert, update, delete, select
on hr.departments to scott;
SQL>grant flashback on hr.employees to scott;
SQL>grant flashback on hr.departments to scott;
SQL>grant flashback on hr.employees to hr;
SQL>grant flashback on hr.departments to hr;
SQL>grant select any transaction to scott;
SQL>grant create any table to scott;
SQL>grant drop any table to scott;
SQL>grant select any table to scott;
SQL>grant select on v_$database to scott;
SQL>grant select on v_$database to hr;
SQL>connect scott/tiger;
#Connect to the database as scott.
SQL>select current_scn from v$database;
#check the current scn number before making any changes.
481166
SQL>delete from hr.employees where employee_id in (195,196);#delete employees which are no longer working in the company.
SQL>commit;
SQL>SELECT * FROM HR.EMPLOYEES
as of timestamp systimestamp - interval '5' minute where hr.employees.employee_id not in
(select employee_id from hr.employees);
#displays the list of records that were deleted 5 minutes ago.
SQL>create table hr.employees_deleted as SELECT * FROM HR.EMPLOYEES
as of timestamp systimestamp - interval '5' minute where hr.employees.employee_id not in
(select employee_id from hr.employees);
#restore those deleted rows to a new table "employees_deleted".
FLASHBACK TABLE Concept:
With Oracle 10g flashback feature the state of rows in a table can be restored to a point of in the past along with all the indexes, triggers and constraints while the database is online,
increasing the overall availability of the database. The table can be restored as of a timestamp or an SCN.
In order to use flashback table on a table, you must enable row movement on the table before performing the flashback operation.
SQL>connect sys/sys as sysdba;
SQL>alter table hr.employees enable row movement;
SQL>alter table hr.departments enable row movement;
SQL>connect scott/tiger;
Connect to database as scott.
SQL>delete from hr.employees
where employee_id in (195,196);
# Query repeated
# values which were deleted earlier.
SQL>commit;
If two or more than two tables having parent-child relationship along with constraints and rows were deleted from both tables, they can be flashed back with the following command.
SQL>flashback table hr.employees, hr.departments
to scn (481166);
#Recommended approach.
#Flashback to timestamp can also be used.
SQL>flashback table hr.employees, hr.departments to timestamp systimestamp - interval '10' minute;
In order to flashback a single table use the following command.
SQL>flashback table hr.employees to timestamp systimestamp - interval '10' minute;
#flashback the tables to 10 minutes in the past.
SQL>select employee_id, first_name, last_name from hr.employees where employee_id in (195,196);
#check the records
RECOVERING A DROPPED TABLE USING FLASHBACK TABLE COMMAND.
To flash back a table to before a drop table operation, you only need the privileges necessary to drop the table.
Connect to the database as hr
SQL>drop table hr.employees cascade constraints; # employees table dropped accidently
SQL>select * from RECYCLEBIN; # check for the dropped table in the recyclebin.
SQL>flashback table hr.employees to before drop; # recover the dropped table from the recyclebin.
FLASHBACK VERSION QUERY
With this feature you can view the entire history of changes of a given row between two SCNs or timestamps.
Suppose some of the following changes are made to the employees and departments table in the hr schema.
Connect to the database as scott.
Before starting check the current SCN number.
SQL>connect scott/tiger
SQL>select dbms_flashback.get_system_change_number from dual;
1673400
SQL> update hr.employees set salary = salary*1.5 where employee_id=195;
SQL>delete from hr.employees where employee_id = 196;
SQL>insert into hr.departments values (660, 'Security', 100, 1700);
SQL>update hr.employees set manager_id = 100 where employee_id = 195;
SQL>commit;
SQL>update hr.employees set department_id = 660 where employee_id = 195;
SQL>update hr.employees set salary = salary*1.5 where employee_id=195;
SQL>commit;
Check the ending SCN number.
SQL> select dbms_flashback.get_system_change_number from dual;
1673495
Now by using Flashback Version Query, the HR user can see the entire history of changes between specified timestamps or SCNs.
Now let us see the changes made for the two employees with IDs 195 and 196.
SQL>select versions_startscn startscn, versions_endscn endscn, versions_xid xid, versions_operation oper,employee_id empid, last_name name, manager_id mgrid, salary sal
from hr.employees versions between scn 1673400 and 1673495
where employee_id in (195,196);
Meaning of Pseudocolumn:
VERSIONS_START{SCN|TIME} The starting SCN or timestamp when the
change was made to the row.
VERSIONS_END{SCN|TIME} The ending SCN or timesamp.
VERSIONS_XID The transaction ID of the transaction that created the row.
VERSIONS_OPERATION The operation that was performed on the row (I=insert, D=Delete, U=Update).
FLASHBACK TRANSACTION QUERY
After identifying the above changes, we can use Flashback Transaction Query to identify any other changes that were made by the transaction containing the inappropriate changes.
Once identified, all changes within the transaction can be reversed as a group to maintain referential integrity.
It is recommended that the supplemental log data should be enabled in the database from the very beginning.If it is not enabled, then enable it by the following sql command.
SQL> alter database add supplemental log data;
Suppose if the last update query to the employees table needs to be reversed in the FLASHBACK QUERY section we will query the
FLASHBACK_TRANSACTION_QUERY.
SQL>select start_scn, commit_scn, logon_user,
operation, table_name, undo_sql
from flashback_transaction_query
where xid = hextoraw('04000F0097030000');
In the undo_sql column it will show a sql entry which can be used to reverse the effects of the original transaction.
update "HR"."EMPLOYEES" set "SALARY" = '2800' where ROWID ='AAAMAEAAFAAAABYABc';
In order to restore the changes execute the above command in the SQL prompt.
SQL>update "HR"."EMPLOYEES" set "SALARY" = '2800' where ROWID ='AAAMAEAAFAAAABYABc';
SQL> commit;
Do you like this post? Please share this article.
HTML Link Code:
Post a Comment