13 January 2010

Flashback from commandline

Before one can Flashback a table or row, one has to determine how far you want to go back in time. The most obvious choice is one day because a backup should be made each evening.A parameters has to be set called UNDO_RETENTION (to specify one day one has to set 1440 minutes). The parameter can be set within the DB Console or with SQLPLUS with the command specified below:


SQL> ALTER SYSTEM SET UNDO_RETENTION = 1440;

In order to check how far the database can be brought back in time, one has to set  the parameter DB_FLASHBACK_RETENTION_TARGET. In order to check if the setting was successful one can perform the query below.

SQL> show parameter db_flashback_retention_target

NAME                                                          TYPE                                  VALUE
------------------------------------                         ---------------------------------   ------------------------------
db_flashback_retention_target                integer                              1440



SQL> alter system set db_flashback_retention_target=2880 scope=both;
System altered.

SQL> select estimated_flashback_size,flashback_size,oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

ESTIMATED_FLASHBACK_SIZE  FLASHBACK_SIZE  OLDEST_FLASHBACK_SCN  OLDEST_F
------------------------                            --------------                  --------------------                           --------
0                                                       155648000                 6048406                                     24/07/05


What is the  actual SCN of the database?

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
6052503



Turning on Flashback:

1) SQL> select flashback_on from v$database;

    FLASHBACK
    ---------
    NO

2) SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

3) SQL> startup mount;
    ORACLE instance started.

    Total System Global Area 209715200 bytes
    Fixed Size 788524 bytes
    Variable Size 162887636 bytes
    Database Buffers 41943040 bytes
    Redo Buffers 4096000 bytes
    Database mounted.


4) SQL> alter database flashback on;
    Database altered.

    SQL> select flashback_on from v$database;

    FLASHBACK
    ---------
    YES

5) SQL> alter database open;
    Database altered.






Querying what the recyclke bin contains can be doen with the query below:

SQL> SET PAGESIZE 120
SQL> SET LINESIZE 100
SQL> TTITLE 'Current Recycle Bin Contents'
SQL> COL object_name     FORMAT A30         HEADING 'Object Name'
SQL> COL type                   FORMAT A8           HEADING 'Object|Type'
SQL> COL original_name   FORMAT A20         HEADING 'Original Name'
SQL> COL droptime            FORMAT A20         HEADING 'Dropped On'
SQL> COL dropscn             FORMAT 9999999  HEADING 'Drop|SCN'
SQL> SELECT
  2       object_name
  3      ,type
  4      ,original_name
  5      ,droptime
  6    FROM dba_recyclebin
  7   WHERE owner = 'DSSOWNER'
  8  ;

Wo Jan 21                                                                                pagina    1
                                    Current Recycle Bin Contents

                                                                 Object
Object Name                                            Type         Original Name                         Dropped On
------------------------------                              --------      --------------------                         --------------------
BIN$SPDLZLO+TCmczdoVcAFYlw==$0 TABLE    FLASH_BACK_DROPPEDTA  2009-01-21:13:59:32






The general command for the flashback table statement:

FLASHBACK TABLE
   [ schema. ]table
     [, [ schema. ]table ]...
TO { { SCN | TIMESTAMP } expr
        [ { ENABLE | DISABLE } TRIGGERS ]
      | BEFORE DROP [ RENAME TO table ]
      } ;
In order to select from the recycle use following statements:

·         SELECT * FROM RECYCLEBIN;
·         SELECT * FROM USER_RECYCLEBIN;

Flashback of the  table is done with the statements:

  • FLASHBACK TABLE TO BEFORE DROP;
  • FLASHBACK TABLE TO BEFORE DROP RENAME TO

If oner has multiple dropped versions of a table and one wants to restore the oldest version, 
One has to query the content of the recycle bin to determine the object_name and droptime of the table in question and the table can be restored with the  FLASHBACK TABLE statement.
 
SELECT object_name, droptime FROM user_recyclebin 
   WHERE original_name = 'employees';
 
OBJECT_NAME                    DROPTIME
------------------------------ -------------------
RB$$45703$TABLE$0              2003-06-03:15:26:39
RB$$45704$TABLE$0              2003-06-12:12:27:27
RB$$45705$TABLE$0              2003-07-08:09:28:01




In order to restore a tablet o a certain point in time one has to set the table property ENABLE ROW MOVEMENT to  YES. This can be done in two ways. With an SQL statement or with the DB Console.

With an SQL statement, as follows:

SQL> ALTER TABLE tablename ENABLE ROW MOVEMENT;

And the flashback of the table to one minute back in time, is done with the command:
 
SQL> FLASHBACK TABLE employees_demo
  TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);

Share:

0 reacties:

Post a Comment