//
you're reading...
Administration, Flashback

Flashback DROP

Oracle flashback technology is an alternate to traditional recovery methods. Unlike RMAN, which most of his part deals with physical corruption, flashback technology deals with logical corruptions like a user has accidentally dropped a table or a query changes some data sets.

Flashback technologies basically introduced in Oracle 9i as Flashback Queries which uses UNDO segments. With later releases, Flashback technology improved a lot and gives us variety of techniques. Following are some Flashback techniques you can use to undo the logical/users errors at various levels:

In this post I’ll demonstrate the use of Flashback Drop and Recycle Bin. Other options will be covered in future posts.

Flashback Drop and Recycle Bin

Flashback Drop is introduced in the Oracle Database 10g. It basically provides a facility to restore accidentally dropped tables with its indexes and constraints.

Before Oracle 10g, when we drop a table using DROP statement it immediate remove the table with all its dependent objects and release all the space consumed by that particular table segment. The only way to recover from this loss is to use the backup to restore the table using import/export which in itself is risky as well as time-consuming.

Starting with Oracle Database 10g, when you drop a table (as well as its associated indexes, constraints etc.), Oracle doesn’t remove it immediately instead the tables and its dependent objects are retained in a logical container called Recycle Bin. It means that the space consumed by the segment is still maintained by the tablespace. These objects remain in the Recycle Bin until they are specifically purged from the Recycle Bin or permanently removed due to Space Pressure.

hr@O11R2>DROP TABLE emp;
Table dropped.

hr@O11R2>SHOW RECYCLEBIN;

ORIGINAL NAME      RECYCLEBIN NAME                  OBJECT TYPE    DROP TIME
---------------    ------------------------------   -----------    -----------------
EMP                BIN$WUaQOHBYSiGcgwHP1tepZA==$0   TABLE          2012-08-31:03:47:18

As you can see that table is in Recycle Bin but has been renamed. Oracle assign system generated name based on the renaming convention as follows:
BIN$unique_id$version
where:

  • unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases
  • version is a version number assigned by the database

This naming convention is useful when user drops a table having the same name as the previous one.

hr@O11R2>CREATE TABLE emp AS SELECT * FROM employees;
Table created.

hr@O11R2>DROP TABLE emp;
Table dropped.

hr@O11R2>SHOW RECYCLEBIN;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP              BIN$WUaQOHBYSiGcgwHP1tepZA==$0 TABLE        2012-08-31:03:47:18
EMP              BIN$/D+DvShqSlafxySOi1ivkA==$0 TABLE        2012-08-31:03:53:09

As you can see the second version of EMP has a different Recyclebin Name. So you choose to restore either version of table using the RECYCLEBIN NAME identifier.

Apart from SHOW RECYCLEBIN command you can query USER_RECYCLEBIN and DBA_RECYCLEBIN views to more information about the objects in Recycle Bin. One important difference between SHOW RECYCLEBIN statement and *_RECYCLEBIN views is the former shows only those objects that can be undrop and filters out dependent objects [since dependent objects can be restored only after their respective table is restored] whereas later offers more information about dependent objects.

hr@O11R2>SELECT object_name, original_name, operation, type, ts_name, droptime, can_undrop, can_purge
         FROM user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME OPERATION TYPE  TS_NAME DROPTIME              CAN CAN  
------------------------------ ------------- --------- ----- ------- -------------------   --- --- 
BIN$+O79sLLGRzueTfCwJHiP5g==$0 IND_EMP_NAME  DROP      INDEX USERS   2012-08-31:03:47:18   NO  YES      
BIN$z+rv0Cn2TZK6ZdKzK7sk/g==$0 PK_EMP        DROP      INDEX USERS   2012-08-31:03:47:18   NO  YES      
BIN$WUaQOHBYSiGcgwHP1tepZA==$0 EMP           DROP      TABLE USERS   2012-08-31:03:47:18   YES YES      

You can query objects residing in the Recycle Bin using the system-generated name (OBJECT_NAME) enclosed in double quotes.

hr@O11R2>SELECT * FROM "BIN$WUaQOHBYSiGcgwHP1tepZA==$0";

        ID NAME                          SALARY
---------- ------------------------- ----------
       198 OConnell                        2600
       199 Grant                           2600
       200 Whalen                          4400
       201 Hartstein                      13000
       202 Fay                             6000
       203 Mavris                          6500
       204 Baer                           10000
       205 Higgins                        12008
       206 Gietz                           8300
       100 King                           24000
       101 Kochhar                        17000
       102 De Haan                        17000
       103 Hunold                          9000
       104 Ernst                           6000
       . . . . . . . 

107 rows selected.

You can enable or disable Recycle Bin with the initialization parameter RECYCLEBIN.

ALTER SESSION SET RECYCLEBIN = ON/OFF;
ALTER SYSTEM SET RECYCLEBIN = ON/OFF SCOPE=SPFILE;

You can use RECYCLEBIN parameter in init.ora file.

RECYCLEBIN = ON/OFF;

By default, Recycle Bin is enabled. When the parameter is set to ON, all objects placed in the Recycle Bin and you can recover them using FLASHBACK DROP. If Recycle Bin is disabled dropped objects do not go to Recycle Bin, they are dropped permanently and the only way to recover them from backup.

One more thing to remember if there are objects in the Recycle Bin and you disabled it, it doesn’t affect objects currently in the Recycle Bin. You can still recover those objects.

We can recover dropped table by using FLASHABCK DROP command as long as they are in Recycle Bin.

FLASHBACK TABLE <table> TO BEFORE DROP [RENAME TO ...]
hr@O11R2>FLASHBACK TABLE "BIN$WUaQOHBYSiGcgwHP1tepZA==$0" TO BEFORE DROP;
Flashback complete.

hr@O11R2>SELECT * FROM emp;

        ID NAME                          SALARY
---------- ------------------------- ----------
       198 OConnell                        2600
       199 Grant                           2600
       200 Whalen                          4400
       201 Hartstein                      13000
       202 Fay                             6000
       203 Mavris                          6500
       204 Baer                           10000
       205 Higgins                        12008
       206 Gietz                           8300
       100 King                           24000
       101 Kochhar                        17000
       102 De Haan                        17000
       103 Hunold                          9000
       104 Ernst                           6000
       105 Austin                          4800
       106 Pataballa                       4800
       107 Lorentz                         4200
       108 Greenberg                      12008
       . . . . . . . .

107 rows selected.

hr@O11R2>SELECT index_name, index_type FROM user_indexes
          WHERE table_name='EMP';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
BIN$z+rv0Cn2TZK6ZdKzK7sk/g==$0 NORMAL
BIN$+O79sLLGRzueTfCwJHiP5g==$0 NORMAL

hr@O11R2>SELECT constraint_name, constraint_type FROM user_constraints
          WHERE table_name='EMP';

CONSTRAINT_NAME                C
------------------------------ -
BIN$wbpiCQf4RV+GAfyTax3qgw==$1 P

When we recover a table, the dependent objects recovers as well but still have system-generated name. You have to rename these objects to their former names.

hr@O11R2>ALTER INDEX "BIN$+O79sLLGRzueTfCwJHiP5g==$0" RENAME TO IND_EMP_NAME;
Index altered.

hr@O11R2>ALTER INDEX "BIN$z+rv0Cn2TZK6ZdKzK7sk/g==$0" RENAME TO PK_EMP;
Index altered. 

You can also use System-generated name for restore.

hr@O11R2>FLASHBACK TABLE "BIN$/D+DvShqSlafxySOi1ivkA==$0" TO BEFORE DROP RENAME TO emp_dup;
Flashback complete.

If you want remove objects permanently, you can use one of the following PURGE command.

hr@O11R2>PURGE TABLE emp_dup;

hr@O11R2>DROP TABLE emp_dup PURGE;

You can also purge the Recycle Bin entirely. It will permanently remove all the objects from Recycle Bin.

hr@O11R2>PURGE RECYCLEBIN;

Once you use PURGE command to drop the objects or empty the Recycle Bin, you cannot use FLASHBACK DROP feature to restore those objects.

NOTE:
Privileges, Limitations and Restrictions on Flashback Drop can be found here.

Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: