Skip to content
Advertisements

Posts from the ‘Flashback’ Category

can we recover dropped table from recyclebin if flashback mode off

yes ,We can recover dropped table from recyclebin even flashback database mode is disabled.

What is Recycle Bin?

recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

Each user can be thought of as having his own recycle bin, since unless a user has the SYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the user owns. A user can view his objects in the recycle bin using the following statement:

SELECT * FROM RECYCLEBIN;

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:

  • When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.
  • When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.
  • When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO
SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
recyclebin                           string      OFF
SQL> alter system set recyclebin=ON scope=spfile;

System altered.

SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
recyclebin                           string      OFF
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             335544784 bytes
Database Buffers          192937984 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.
SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
recyclebin                           string      ON
SQL> conn testuser/testuser;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ABC                            TABLE
ABCD                           TABLE
XXX                            TABLE

SQL> create table test(empno number);

Table created.

SQL> insert into test values(001);

1 row created.

SQL> insert into test values(002);

1 row created.

SQL> insert into test values(003);

1 row created.

SQL> insert into test values(004);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

     EMPNO
----------
         1
         2
         3
         4

SQL> drop table test;

Table dropped.

SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> set lines 500
SQL> SELECT object_name,original_name,droptime,dropscn from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME               DROPSCN
------------------------------ -------------------------------- ---------
BIN$nkFizCIuTY23wgOmv1B1vA==$0 TEST                             2011-07-03:13:27:35    8811423

SQL> flashback table test to before drop;

Flashback complete.

SQL> select * from test;

     EMPNO
----------
         1
         2
         3
         4

SQL> SELECT object_name,original_name,droptime,dropscn from recyclebin;

no rows selected

SQL>

Ref : http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables011.htm#i1011362

Advertisements

Can we use Flashback database If noarchivelog exists

Can we use flashback database log after switching from archivelog mode to noarchivelog mode and back to archivelog mode?

According to  this below document reference

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV583

 

We cannot, because when we turn off flashback database, you lose the flashback logs unless they are guaranteed

If they guaranteed, We can’t alter no archivelog mode

Let us see some example :

Step 1: Ensure Flashback ON

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Step 2:

SQL> conn flashown/flashown;
Connected.
SQL> create table employee as select * from scott.emp;

Table created.

SQL> update employee set sal=sal+50;

14 rows updated.

SQL> commit;

Commit complete.

SQL> create restore point azar;

Restore point created.

SQL> update employee set sal=sal+50;

14 rows updated.

SQL> commit;

SQL>select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
19-DEC-10 09.53.55.726000 AM +03:00

Step 3: alter noarchivelog mode

SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount
Database mounted.

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled
SQL> alter database flashback off;

Database altered.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> update flashown.employee set sal=sal+50;

14 rows updated.

SQL> commit;

Commit complete.

Step 4: Again back to archivelog mode & Recover flashback database But we can’t.

SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> flashback database to restore point azar;
flashback database to restore point azar
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.
SQL> flashback database to timestamp to_date('19-DEC-10 09.54.00','DD-MM-YY HH24:MI:SS');
flashback database to timestamp to_date('19-DEC-10 09.54.00','DD-MM-YY HH24:MI:SS')
                                                             *
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.

Step 5: Create Guarantee Restore point

SQL> show parameter db_flashback_retention_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL> create restore point azardb guarantee flashback database;

Restore point created.

SQL> alter database open;

Database altered.

SQL> select sal from flashown.employee;

       SAL
----------
       955
      1755
      1405
      3130
      1405
      3005
      2605
      3255
      5155
      1655
      1255

       SAL
----------
      1105
      3155
      1455

14 rows selected.

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
19-DEC-10 10.11.43.716000 AM +03:00

SQL> update flashown.employee set sal=sal-5;

14 rows updated.

SQL> commit;

Commit complete.

SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount
Database mounted.
SQL> alter database flashback off;

Database altered.

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38781: cannot disable media recovery - have guaranteed restore points
SQL> alter database flashback on;

Database altered.

SQL> flashback database to timestamp to_date('19-DEC-10 10.11.44','DD-MM-YY HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select sal from flashown.employee;

       SAL
----------
       955
      1755
      1405
      3130
      1405
      3005
      2605
      3255
      5155
      1655
      1255

       SAL
----------
      1105
      3155
      1455

14 rows selected.
SQL>

So we can’t alter noarchivelog mode While Guarantee restore point exists in DB.

And the If flashback database off,  we can’t recover flashback database using flashback logs.

Flashback Data Archive

Flashback Data archive is one of the Oracle 11g feature (Oracle Total Recall).

A Flashback Data Archive (Oracle Total Recall) provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

 

How to Enable Flashback Data Archive;

Step 1:  Conn AS sys user & create tablespace

SQL> select flashback_on from v$database;

FLASHBACK_ON

SQL> create tablespace flashbacktbs datafile 'D:\backup\flbtbs01.dbf' size 50m autoextend on;

Tablespace created.
------------------
YES

Step 2: Create Flashback Data Archive with retention period

SQL> create flashback archive fla1 tablespace flashbacktbs retention 1 year;

Flashback archive created.

Step 3:

SQL> conn scott/tiger;
Connected.
SQL> alter table emp flashback archive fla1;
alter table emp flashback archive fla1
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive

So Need to give privilege for scott user

Step 4: Grant privilege

SQL> conn sys/Admin123 as sysdba
Connected.

SQL> grant flashback archive on fla1 to scott;

Grant succeeded.

Step 5:

SQL> conn scott/tiger;
Connected.
SQL> alter table emp flashback archive fla1;

Table altered.

Step 6:

SQL> select empno,ename,sal from emp where empno=7788;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7788 SCOTT            3000

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
18-DEC-10 10.53.41.521000 AM +03:00

SQL>  update emp set sal=sal+100 where empno=7788;

1 row updated.

SQL>  select sal from emp as of timestamp to_timestamp('18-DEC-10 10:53:41','DD-MM-YY HH24:MI:SS')  WHERE EMPNO=7788;

       SAL
----------
      3000

SQL> select empno,ename,sal from emp where empno=7788;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7788 SCOTT            3100

Some Other operations:

Modify Retention Period:

SQL>  alter flashback archive FLA1 modify retention 2 year;

Flashback archive altered.

Purge all data

SQL> alter flashback archive FLA1 purge all;

Flashback archive altered.

Disable Falshback data archive for table

SQL> alter table scott.emp no flashback archive;

Table altered.

ADD/MODIFY/REMOVE tablespace

SQL> alter flashback archive fla1 add tablespace testdb;

Flashback archive altered.

Ref :

https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#BJFIEEII

 

Flashback versions query

This post show you Easy understanding about Flashback versions query and also How we can restore these data from flashback using EM DB console.

What Is Flashback Versions 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.

You specify a Flashback Version Query using the VERSIONS BETWEEN clause of the SELECT statement. Here is the syntax:

VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}

where start and end are expressions representing the start and end of the time interval to be queried, respectively. The interval is closed at both ends: the upper and lower limits specified (start and end) are both included in the time interval.

 The flashback versions query feature, like Flashback Table, utilizes data from the undo segments, so you must configure your instance to use automatic undo management. The amount of data that you can retrieve with the VERSIONS_BETWEEN clause is limited by the setting of the UNDO_RETENTION initialization parameter. Because the UNDO_RETENTION parameter determines your row history, you should set this parameter based on your needed timeline to utilize flashback versions query. Also, you can use the RETENTION GUARANTEE clause to ensure that you will retain all critical undo data when using flashback versions query.

Now See Exmaples :

Step 1:


SQL> create user fbuser identified by fbuser;

User created.

SQL> grant connect,resource,dba to fbuser;

Grant succeeded.

SQL> conn fbuser/fbuser;
Connected.
SQL> create table flashtest as select * from hr.employees;

Table created.

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
    4086851 2010-11-10 11:32:14

Step 2:

SQL> select first_name, email from flashtest where department_id = 30;

FIRST_NAME           EMAIL
-------------------- -------------------------
Den                  DRAPHEAL
Alexander            AKHOO
Shelli               SBAIDA
Sigal                STOBIAS
Guy                  GHIMURO
Karen                KCOLMENA

6 rows selected.

Step 3:

SQL> update flashtest set email = EMAIL||'@HRDEPT' where department_id = 30;

6 rows updated.

SQL> select first_name, email from flashtest where department_id = 30;

FIRST_NAME           EMAIL
-------------------- -------------------------
Den                  DRAPHEAL@HRDEPT
Alexander            AKHOO@HRDEPT
Shelli               SBAIDA@HRDEPT
Sigal                STOBIAS@HRDEPT
Guy                  GHIMURO@HRDEPT
Karen                KCOLMENA@HRDEPT

6 rows selected.

SQL> commit;

Commit complete.

SQL> delete flashtest where department_id = 30;

6 rows deleted.

SQL> commit;

Commit complete.

Step 4:

SQL> select first_name, email from flashtest where department_id = 30;

no rows selected

Step 5:


SQL> set lines 1000;
SQL> select versions_startscn, versions_endscn,versions_xid, versions_operation,
  2  first_name, email from flashtest VERSIONS BETWEEN SCN MINVALUE and MAXVALUE
  3  where department_id = 30;

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V FIRST_NAME           EMAIL
----------------- --------------- ---------------- - -------------------- -------------------------
          4086869                 05000400DE0A0000 D Karen                KCOLMENA@HRDEPT
          4086869                 05000400DE0A0000 D Guy                  GHIMURO@HRDEPT
          4086869                 05000400DE0A0000 D Sigal                STOBIAS@HRDEPT
          4086869                 05000400DE0A0000 D Shelli               SBAIDA@HRDEPT
          4086869                 05000400DE0A0000 D Alexander            AKHOO@HRDEPT
          4086869                 05000400DE0A0000 D Den                  DRAPHEAL@HRDEPT
          4086866         4086869 0700080067080000 U Karen                KCOLMENA@HRDEPT
          4086866         4086869 0700080067080000 U Guy                  GHIMURO@HRDEPT
          4086866         4086869 0700080067080000 U Sigal                STOBIAS@HRDEPT
          4086866         4086869 0700080067080000 U Shelli               SBAIDA@HRDEPT
          4086866         4086869 0700080067080000 U Alexander            AKHOO@HRDEPT

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V FIRST_NAME           EMAIL
----------------- --------------- ---------------- - -------------------- -------------------------
          4086866         4086869 0700080067080000 U Den                  DRAPHEAL@HRDEPT
                          4086866                    Den                  DRAPHEAL
                          4086866                    Alexander            AKHOO
                          4086866                    Shelli               SBAIDA
                          4086866                    Sigal                STOBIAS
                          4086866                    Guy                  GHIMURO
                          4086866                    Karen                KCOLMENA

18 rows selected.

Okay. Now i want to restore this update row only using DBconsole and also How can we use Flashback versions query in EM Dbconsole.

Step 1:

Step 2:

Step 3:

Step 4:

Step 5

Step 6:

Step 7: You need to check Dependency Options.

After submitted, The particular SCN transactions restored.

SQL> select first_name, email from flashtest where department_id = 30;

FIRST_NAME           EMAIL
——————– ————————-
Den                  DRAPHEAL@HRDEPT
Alexander            AKHOO@HRDEPT
Shelli               SBAIDA@HRDEPT
Sigal                STOBIAS@HRDEPT
Guy                  GHIMURO@HRDEPT
Karen                KCOLMENA@HRDEPT

6 rows selected.

Recover dropped tablespace using Flashback Oracle 11g R2

You can recover dropped tablespace using follwing method…

datapump reterive original data using flashback scn

Retrieve original data:-

It may be useful to export the image of a table the way it existed before a change was committed.if the database is properly configured, the database flashback query facility also integrated with expdp may be used.

SQL> conn / as sysdba
Connected.
SQL> create restore point original_emp;

Restore point created.

SQL> select scn,name from v$restore_point;

SCN
———-
NAME
——————————————————————————–
518527
ORIGINAL_EMP

SQL> conn scott/tiger;
Connected.

SQL> select sum(sal) from emp;

SUM(SAL)
———-
29025

SQL> update test set sal=sal*1.1;

14 rows updated.

Here Test table is copy of emp table.

SQL> commit;

Commit complete.

SQL> select sum(sal) from test;

SUM(SAL)
———-
31927.5

[oracle@localhost rlwrap-0.30]$ expdp scott/tiger dumpfile=original directory=test_dir tables=test flashback_scn=518527

Export: Release 10.2.0.1.0 – Production on Friday, 07 May, 2010 13:33:02

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “SCOTT”.”SYS_EXPORT_TABLE_01″:  scott/******** dumpfile=original directory=test_dir tables=test flashback_scn=518527
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “SCOTT”.”TEST”                              7.820 KB      14 rows
Master table “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/datapumptest/original.dmp
Job “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully completed at 13:33:11

SQL> drop table test;

Table dropped.

SQL> commit;

Commit complete.

[oracle@localhost rlwrap-0.30]$ impdp scott/tiger dumpfile=original directory=test_dir tables=test

Import: Release 10.2.0.1.0 – Production on Friday, 07 May, 2010 13:34:57

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Master table “SCOTT”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “SCOTT”.”SYS_IMPORT_TABLE_01″:  scott/******** dumpfile=original directory=test_dir tables=test
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “SCOTT”.”TEST”                              7.820 KB      14 rows
Job “SCOTT”.”SYS_IMPORT_TABLE_01″ successfully completed at 13:35:00

[oracle@localhost rlwrap-0.30]$

SQL> select sum(sal) from test;

SUM(SAL)
———-
29025

Managing Recyclebin

Recycle Bin :

  1. When a tablespace is completely filled up with recycle bin data such
    that the datafiles have to extend to make room for more data, the tablespace is said to be under “space pressure”.
  2. Objects are automatically purged from the recycle bin in a first in  first out manner.
  3. the dependent objects(such as indexes) are removed before a table is removed.
  4. Space pressure can occur with user quotes as defined for a particular tablespace.
  5. The tablespace may ahve enough free space, but the user may be running out of his or her allotted portion of it.
  6. In such situations, Oracle automatically purges objects belonging to that user in that tablespace.
  7. If your datafiles have the AUTOEXTEND attribute enabled, Oracle will not in fact autoextend  the datafiles until all space occupied by dropped objects has been reassigned.it will overwrite the recycle bin in preference to increasing the datafile size.

Example :-

  1. A 1MB tablesapce,called SMALL, has beeen completely filled by one table,called LARGE.
  2. The space usage alerts will have fired, and querying DBA_FREE_SPACE reports no space available. Then the table is dropped.
  3. The alert will clear itself and DBA_FREE_SPACE will report that the whole tablespace is empty ,but
    querying the recycle bin , or indeed DBA_SEGMENTS will tell the truth.

Sql>select sum(bytes) from dba_free_space where tablespace_name=’SMALL’;
sum(bytes)
———-

sql>select segment_name,bytes from dba_segments where tablespace_name=’SMALL’;
SEGMENT_NAME             BYTES
————-           ——
LARGE                   983040

sql>drop table large;
Table dropped.

sql>select sum(bytes) from dba_free_space where tablespace_name=’SMALL’;
sum(bytes)
———-
983040

sql>select segment_name,bytes from dba_segments where tablespace_name=’SMALL’;
SEGMENT_NAME             BYTES
————-           ——
BIN$                    983040

  1. This apparently contradictory state is resolved by Oracle reusing space as it needs it.
  2. If space is required in the tablespace for a new segment, then it will be taken and it will no longer be possible to retain the dropped table.
  3. If there are many deleted objects in the recycle bin, Oracle will overwrite the object that had been in there for the longest time.THIS FIFO or first in first out algorithm assumes that  ojects dropped recently are most likely candidates for a flashback.

—-

Each user has his own recycle bin

SQL> conn scott/tiger;
Connected.
SQL> show user;
USER is “SCOTT”
SQL> create table trial as select * from dept;

Table created.

SQL> drop table trial;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-

TRIAL            BIN$1vHeLSXYTROOaynkYZGpCg==$0 TABLE        2010-01-25:11:29:00

SQL> desc dba_recyclebin;
ERROR:
ORA-04043: object “SYS”.”DBA_RECYCLEBIN” does not exist

( Sys user only can access this above view).

SQL> select original_name,type,ts_name,droptime,can_undrop,space from user_recyc
lebin;

ORIGINAL_NAME                    TYPE
——————————– ————————-
TS_NAME                        DROPTIME            CAN      SPACE
—————————— ——————- — ———-
TRIAL                            TABLE
USERS                          2010-01-25:11:29:00 YES          8
SQL>

Drop the table and do not move it to the recycle bin

SQL> create table test as select * from dept;

Table created.

SQL> drop table test purge;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-

TRIAL            BIN$1vHeLSXYTROOaynkYZGpCg==$0 TABLE        2010-01-25:11:29:00

Remove the table from the recycle bin.If there are several objects with the same Original name, the oldest is removed.

SQL> drop table test;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-

TEST             BIN$a2qVloT+Q5q1roJlQz+ZKw==$0 TABLE        2010-01-25:11:37:21

TRIAL            BIN$1r9D5xVUQtu0IBhxh8q6kA==$0 TABLE        2010-01-25:11:36:44

SQL> purge table test;

Table purged.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-

TRIAL            BIN$1r9D5xVUQtu0IBhxh8q6kA==$0 TABLE        2010-01-25:11:36:44

Remove an index from the recycle bin

sql>purge index <index_name>;

Remove all dropped objects from the tablespace

SQL> purge tablespace users;

Tablespace purged.

SQL> show recyclebin;

Remove all dropped objects belonging to one user from the tablespace

sql>purge tablespace <tablespace_name> user <user_name>;

Remove all your dropped objects

sql>purge user_recyclebin

Remove all dropped objects.You will need DBA privileges to execte this.

sql>purge dba_recyclebin

Flashback DROP

Flashback DROP 

  • The Flashback drop command applies only to tables, but all associated objects will also be recovered,except for foreign key constraints.
  • In earlier releases of the Oracle Database, when a table was dropped all references to it were removed from the data dictionary.
  • If it were possible to see the source code for the old DROP TaBLE command, you would see that it was actually a series of DELETE commands against the various tables in the SYS schema that define a table and its space usage, followed by commit. 
  • There was no actual clearing of data from disk, but the space used by a dropped table was flagged as being unused and thus available for reuse.
  • Even though the blocks of the table were still there, there was no possible way of getting to them because the data dictionary would have no record of which blocks were part of the dropped table.the only way  to recover a dropped table was to do a point in time recovery, restoring a version of the database from before the drop when the data dictionary still knew about the table.
  • In release 10g of the Oracle database , the implementation of the DROP TABLE command has been completely changed.
  • tables are no longer dropped at all, they are renamed.
  • You can see that a table, EMP occupies one extent of 64KB, which starts in the 25the of file 4.After the rename to NEW_NAME, the storage is exactly the same; therefore the table is the same,
    Querying the view DBA_OBJECTS would show that the table’s object number had not changed either.
  • The release 10g implementation of the DROP TABLE command has been mapped internally onto a RENAME command, which affects the table all its associated indexes, triggers and constaints with the exception
    of foreign key constraints, which are dropped.
  • If they were maintained, even with a different name, then DML on the non dropped parent table would be constrained by the contents of a dropped table, which would be absurd.
  • The dropped objects can be queried by looking at the recycle bin to obtain their names.
  • There is a recycle bin for each other, visible in the USER_RECYCLEBIN data dictionary view or DBA_RECYCLEBIN.
  • The space occupied by the recycle bin objects will be reused automatically when a tablespace comes under space pressure(after whic
    time the objects cannot be recovered), or you can manually force Oracle to really drop the objects with the PURGE command.

SQL> select file_id,block_id,bytes from dba_extents where segment_name=’EMP’;
 

   FILE_ID   BLOCK_ID      BYTES
———- ———- ———-
         4                  25      65536
 

SQL> alter table EMP rename to EMPLOYEE;
alter table EMP rename to EMPLOYEE
*
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> conn scott/tiger
Connected.
SQL> alter table EMP rename to EMPLOYEE;
 

Table altered.

SQL> conn sys/sys@riyadh as sysdba
Connected.
SQL> select file_id,block_id,bytes from dba_extents where segment_name=’EMP’;
 

no rows selected
 

SQL> select file_id,block_id,bytes from dba_extents where segment_name=’EMPLOYEE’;
 

   FILE_ID   BLOCK_ID      BYTES
———- ———- ———-
         4               25             65536
 

Flashback DROP Example :
 

Step 1
 

Create user
 

SQL> show user;
USER is “SYS”
SQL> create user dropper identified by dropper;
 

User created.
 

SQL> grant connect,resource to dropper;
 

Grant succeeded.
 

Step 2
Create a table, with an index and a constraint and insert a row.
 

SQL> connect dropper/dropper@riyadh;
Connected.
SQL> create table test(name varchar2(10));  

Table created.  

SQL> create index test_idx on test(name);  

Index created.  

SQL> alter table test add(constraint name_u unique(name));  

Table altered. 
SQL> insert into test values(‘Azar’);
 

1 row created.
 

SQL> commit;
 

Commit complete.
 

SQL> select * from test;
 

NAME
———-
Azar
 

Step 3
 
Confirm the contents of your schema
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
TEST
TABLE
 

TEST_IDX
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
NAME_U                         U TEST
 

Step 4 

Drop the table
 

SQL> drop table test;
 

Table dropped.
 

Step 5
 
Re-run the queries from step 3.Note that the objects and the constaints do still exist but that they now
have system generated names, all prefixed with BIN$.
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
BIN$Zl4wALngQtKfJL+9mgzJgg==$0
TABLE
 

BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
BIN$BBKVasdjRsqyYqne8rM8VQ==$0 U BIN$Zl4wALngQtKfJL+9mgzJgg==$0
 

Step 6 

Query your recycle bin to see the mapping of the original name to the recyclebin names.
 

SQL> select object_name,original_name,type from user_recyclebin;
 

OBJECT_NAME                    ORIGINAL_NAME
—————————— ——————————–
TYPE
————————-
BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0 TEST_IDX
INDEX
 

BIN$Zl4wALngQtKfJL+9mgzJgg==$0 TEST
TABLE
 

Step 7
 

Query the recycle bin but that you cannot do DML against it.
 

SQL> select * from “BIN$Zl4wALngQtKfJL+9mgzJgg==$0”;
 

NAME
———-
Azar
 

SQL> insert into “BIN$Zl4wALngQtKfJL+9mgzJgg==$0” values (‘Mohd’);
insert into “BIN$Zl4wALngQtKfJL+9mgzJgg==$0” values (‘Mohd’)
            *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
 

Step 8
 

Recover the table with Flashback Drop 
SQL> flashback table test to before drop;
 

Flashback complete.
 

Step 9
 

Rerun the step 3.
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
TEST
TABLE
 

BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
BIN$BBKVasdjRsqyYqne8rM8VQ==$0 U TEST
 

Step 10
Rename the index and constraint to the original names.

SQL> alter index “BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0” rename to name_idx;
 

Index altered.
 

SQL> alter table test rename constraint “BIN$BBKVasdjRsqyYqne8rM8VQ==$0” to name_u;
 

Table altered.
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
TEST
TABLE
 

NAME_IDX
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
NAME_U                         U TEST
 

SQL> select * from test;
 

NAME
———-
Azar

Configuring Flashback Database

C:\Documents and Settings\mazar>set oracle_sid=riyadh

C:\Documents and Settings\mazar>sqlplus

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Jan 18 11:17:37 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: sys/sys as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

Step 1

Ensure that the database in archivelog mode.

Archivelog mode is a prerewuistite for enabling flashback database.

SQL> select log_mode from v$database;

LOG_MODE
————
ARCHIVELOG

Step 2

Set up a flash recovery area.

The flash recovery area is the location for the flashback logs. YOu have no control over them other than setting the flah recovery area directory and limiting its size. It is controlled with two instance paramenters: DB_RECOVERY_FILE_DEST specifies the destination directory;
DB_RECOVERY_FILE_DEST_SIZE restricts that the flash recovery area is used for purposes other than flashback logs and it will need to be sized appropriately.

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                string      D:\flashrecovery
db_recovery_file_dest_size           big integer 2G
SQL> alter system set db_recovery_file_dest_size=4G;

System altered.

Step 3

Set the lifespan for the flashback retention target.

This setting is controlled by the DB_FLASHBACK_RETENTION_TARGET instance parameter, which specifies a time in minutes (the default is one day).

The flashback log space is reused in a circular fashion, older data being overwritten by newer data.This parameter instructs Oracle to keep flashback data for a certan cumber of minutes before overwriting it

SQL> alter system set db_flashback_retention_target=240;

System altered.

It is only a target(four hour in this example), and if the flash recovery area is undersized, Oracle may not be able to keep it.but is principle, you should be able to flash back to any time within this target.

Step 4

Cleanly Shutdown the database and mount

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  247463936 bytes
Fixed Size                  1248332 bytes
Variable Size             104858548 bytes
Database Buffers          134217728 bytes
Redo Buffers                7139328 bytes
Database mounted.

Step 5

Enable flashback logging, while in mount mode.
SQL> alter database flashback on;

Database altered.

This will start the RVWR process and allocate a flashback buffer in the SGA. The process startup will be automatic from now on.

Step 6

Open the database.

SQL> alter database open;

Database altered.

Logging of data block images from the database buffer cache to the flashback buffer will be enabled from now on.

Step 7

Check Whether Flashback On or Off.

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES