Forgot your password?    
+ Reply to Thread
Results 1 to 3 of 3

Thread: Oracle Database 10g Flashback Technology.

  1. #1
    Oracle Administrator
    Join Date
    Dec 2011
    Posts
    92

    Oracle Database 10g Flashback Technology.

    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

    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

    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;


    FLASHBACK DATABASE

    The flashback database command returns the database to a past
    time or SCN, providing a fast alternative to performing
    incomplete database recovery. Database can be flashed back to
    scn ot timestamp.

    After a flashback database operation in order to have write
    access to the flashed back database, you must reopen it with
    an "alter database open resetlogs" command.

    To enable flashback database make sure your database is in
    archivelog mode after confirming do the following.

    SQL>shutdown immediate;
    SQL>startup mount exclusive;

    # flashback database command demands that the database should be
    # mounted in exclusive mode.
    SQL>alter database flashback on;
    SQL>alter database open;


    check the database's flashback status.

    SQL>select FLASHBACK_ON from V$DATABASE;
    this column should show a value of "yes".

    check the following view to check the how far you can flashback
    the database.

    SQL>select * from V$FLASHBACK_DATABASE_LOG;

    suppose the database is running for over 1 hour and you need to
    flash it back then do the following.

    SQL>shutdown;
    SQL>startup mount exclusive;
    SQL>flashback database to timestamp sysdate-1/24;


    The Database will be flashed back and all the changes made since the
    past hour will be lost.

    SQL>alter database open resetlogs;

    Disable Flashback logging.

    SQL>shutdown immediate;
    SQL>startup mount exclusive;
    SQL>alter database flashback off;
    SQL>alter database open;

  2. #2
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427
    Thanks for this comprehensive post Ajay.

  3. #3
    Flashback Recovery Concepts:--

    Type Of Flashback recovery:--

    1)Flashback Database

    2)Flashback Drop

    3)Flashback Table

    4)Flashback Query


    Requirement for Flashback:--

    1) Database must be in Archive log mode

    2) Fash recovery area must be enabled.


    Dependent Objects

    V_$FLASHBACK_DATABASE_LOG
    V_$FLASHBACK_DATABASE_LOGFILE
    V_$FLASHBACK_DATABASE_STAT

    For The RAC Database:--

    GV_$FLASHBACK_DATABASE_LOG
    GV_$FLASHBACK_DATABASE_LOGFILE
    GV_$FLASHBACK_DATABASE_STAT


    Syntax:--

    Syntax base on SCN:

    SCN FLASHBACK DATABASE [<database_name>]
    TO [BEFORE] SCN <system_change_number>;

    Syntax base on TIMESTAMP:--

    TIMESTAMP FLASHBACK DATABASE [<database_name>]
    TO [BEFORE] TIMESTMP <system_timestamp_value>;

    Syntax base on RESTORE POINT:---

    RESTORE POINT FLASHBACK DATABASE [<database_name>]
    TO [BEFORE] RESTORE POINT <restore_point_name>;



    How to OFF/ON Flashback:

    ALTER DATABASE FLASHBACK OFF;

    ALTER DATABASE FLASHBACK ON;


    Start/Stop flashback on a tablespace:--

    ALTER TABLESPACE <tablespace_name> FLASHBACK ON;

    ALTER TABLESPACE <tablespace_name> FLASHBACK OFF;


    Initialization Parameters:--

    db_recovery_file_dest=<location>

    db_recovery_file_dest_size= <Size> ------ keep in integer i,e 2G

    db_flashback_retention_target=<minute> ---Keep in minute.

    By Sql syntax:--

    ALTER SYSTEM SET db_flashback_retention_target = <number_of_minutes>;



    How to Enable Flashback:--

    Step 1: Verify the Database in flash back mode and the retention_target.

    SQL> SELECT flashback_on, log_mode
    FROM v$database;
    FLASHBACK_ON LOG_MODE
    ------------------ ------------
    NO ARCHIVELOG


    Step 2:-- shutdown the database

    SQL> shutdown immediate;


    Step 3:-- Enable the Archive log and Set the DB_FLASHBACK_RETENTION_TARGET, DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST.

    Flash Recovery Area contains the Flashback Logs, Redo Archive logs, backups files by RMAN and copies of control files. The destination and the size of the recovery area are setup using the db_recovery_file_dest and b_recovery_file_dest_size initializatin parameters.

    SQL> startup mount;

    SQL> alter database archivelog;

    SQL> alter system set db_recovery_file_dest=<location> scope=both;

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

    SQL> alter system set db_flashback_retention_target=600 ;---- In Minutes.


    Step 4:-- On the Flash back and open the database.

    SQL> alter database flashback on;

    SQL> alter database open;

    Step 05: Now Verify the Database flashback mode.

    SQL> SELECT flashback_on, log_mode
    FROM v$database;

    FLASHBACK_ON LOG_MODE
    ------------------ ------------
    YES ARCHIVELOG



    How to Recover Database from Flashback recovery area:-

    Step 01: Find the Current SCN and Flashback time.

    SQL> SELECT current_scn
    2 FROM v$database;

    CURRENT_SCN
    -----------
    1143033


    SQL> SELECT oldest_flashback_scn,oldest_flashback_time
    2 FROM gv$flashback_database_log;

    OLDEST_FLASHBACK_SCN OLDEST_FL
    -------------------- ---------
    1141575 23-Feb-12


    Step 02: Grant flashback to the user.

    GRANT flashback any table TO <user_name>;

    Step 03: Shutdown the database and start in exclusive mode

    SQL> SHUTDOWN immediate;

    SQL> startup mount exclusive;


    Step 04: Be sure to substitute your SCN and issue the following command

    SQL> FLASHBACK DATABASE TO SCN <SCN Number>;

    Flashback complete.


    Or
    If restore point create by the user

    FLASHBACK DATABASE TO RESTORE POINT <RESTORE POINT>;

    Or
    if flashback using TIMESTAMP

    FLASHBACK DATABASE TO TIMESTAMP Timestamp ‘2012-02-23 14:00:00’;

    Step 05: Now open database using resetlogs

    SQL> alter database open resetlogs;
    Last edited by vineettyagi; 02-24-2012 at 01:59 PM.

+ Reply to Thread

Similar Threads

  1. Recyclebin with Flashback Table in Oracle 10g
    By devesh_ocp in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 04-26-2012, 02:21 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics