Lokeshaggarwal's DBA

Live the life you love. Love the life you live

Recover the dropped table in Oracle


SQL> select count(*) from t
  2  /
 
  COUNT(*)
———-
         0
 
SQL> begin
  2    for i in 1..200
  3    loop
  4     insert into t values (i);
  5    end loop;
  6  end;
  7  .
SQL> /
 
PL/SQL procedure successfully completed.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> select count(*) from t
  2  /
 
  COUNT(*)
———-
        200
 
SQL> select to_char(sysdate,’DD-MM-YYYY HH24:MI:SS’)
  2    from dual
  3  /
 
TO_CHAR(SYSDATE,’DD
——————-
16-11-2009 1:19:20
 

we are having 200 reocords in the table
SQL> delete from t where a between 1 and 100
  2  /
 
100 rows deleted.
I deleted 100 out 0f 200

SQL> commit
  2  /
 
Commit complete.

SQL> select count(*) from t
  2  /
 
  COUNT(*)
———-
        100
 
RMAN> run
2> {
3> shutdown immediate;
4> startup mount;
5> set until time “to_date(’16-11-2009 1:19:20′,’DD-MM-YYYY HH24:MI:SS’)”;
6> restore database;
7> recover database;
8> }

database dismounted
Oracle instance shut down
 
connected to target database (not started)
Oracle instance started
database mounted
 
Total System Global Area     171966464 bytes
 
Fixed Size                      787988 bytes
Variable Size                145488364 bytes
Database Buffers              25165824 bytes
Redo Buffers                    524288 bytes
 
executing command: SET until clause
Starting restore at 16-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DB
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\BACKUPSET\2009_
_16\O1_MF_NNNDF_TAG20091116T155846_42LX37BF_.BKP tag=TAG20091116T155846
channel ORA_DISK_1: restore complete
Finished restore at 16-NOV-09
Starting recover at 16-NOV-09
using channel ORA_DISK_1
 
starting media recovery
media recovery complete
 
Finished recover at 16-NOV-09

RMAN> alter database open resetlogs
2> ;
 
database opened
 
SQL> conn sys/sys as sysdba
Connected.
SQL> desc t
 Name                                      Null?    Type
 —————————————– ——– —————————
 A                                                  NUMBER
 
SQL> select count(*)
  2    from t
  3  /
 
  COUNT(*)
———-
        200
 
SQL>

 

 

 

 

 

Advertisements

November 16, 2009 - Posted by | Uncategorized

1 Comment »

  1. Hi lokesh,

    Thnks for nice explanation, in case if we are havng corrupted backup then how to reocver the same.

    Comment by Sweta | January 20, 2010 | Reply


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: