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  /
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  /
SQL> select to_char(sysdate,’DD-MM-YYYY HH24:MI:SS’)
  2    from dual
  3  /
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  /
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
_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
SQL> desc t
 Name                                      Null?    Type
 —————————————– ——– —————————
 A                                                  NUMBER
SQL> select count(*)
  2    from t
  3  /







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: