Recovering a table when lost

How to recover a lost table?

Till now we have seen recovering of physical files. Now we will be doing recovery of a logical object say table.

Let us take an example case as follows:

Someone has dropped my table at 4 pm today by mistake. After some time(6 PM same day) that guy came to me and asked for his table back as it is required for business continuity saying the approximate time that he had dropped the table. Now i have to get the table and its contents.

I can do it in 2 ways.

(A)I can flash back it till before drop (if and only if flash back is enabled)

(B)Table point in time recovery

Now before proceeding further try to understand the plan in the next 5 points mentioned below.

1. Yesterday i took the database HOT backup at 2 A.M. and i had archive logs available with me till now.

2. I will stop the database, restore it and recover the database till 3:59 PM today.so my database is at 3:59 today.

3. After finding my table i will export it to a dump file and keep it in some path.

4. Now i will take trace of control file and start recovering database from 3:59 pm to till 6 pm.

5. Now the database came to as usual state without table. Now i will import the table.

So finally i will get my table back.

We shall follow the steps by doing it practically as below:

1. Now the time is 6 pm. Best time to leave office. But we (DBA) are doers.so let’s do it. Take the HOT backup before proceeding.

SQL>alter database begin backup;

]~ cp   /u01/oracle/app/oradata/pavan/database_1/  /u01/oracle/app/oradata/pavan/database_1/HOT_BKP/*.dbf

SQL>alter database end backup;

SQL>alter database backup controlfile to trace as '/tmp/ttt.sql';

In the above step i am taking back up of controlfile in text format for future recovery purposes. I found my archive log files also in the path mentioned in init.ora.

2. Shut down the database in normal mode.

SQL>shut immediate/shutdown

SQL>select status from v$instance;

If you are not confident about the success of rest of activity you can take cold backup also along with it.

Now we are going restore the backup taken at 2AM yesterday night.

SQL>alter database begin backup;

}~cp /u01/oracle/app/oradata/pavan/database_1/lastnight_bkp/*.dbf /u01/oracle/app/oradata/pavan/database_1/

SQL>startup mount

Recover the database till 3:59 pm

SQL>alter database recover automatic using backup controlfile until time '2014:03:20:15:59:00';

SQL>alter database open RESETLOGS;

3. Now we took our database to 3:59 pm today. So this is the right time. Find your table and export it to any path.

cd /tmp/

]~exp system/system file=ts2.dmp log=ts2.log table_name=TS2;

4. Exporting of table is done. Now i have to take my database to 6 pm today.

Tell me one thing: should i restore backup of yesterday night (2 A.M) and recover till 6 pm?

Or else

Should i recreate my control file using backup trace taken at step-1 and then start recovering till 6 pm (3:59 pm to 6 pm)?

The second one is preferable because it will reduce downtime.

So start recovering from 3:59 pm today. But for doing recovery some disaster should happen. What shall i do? I will take backup of my control file and will drop it.

So it needs recovery now.

]~ cp ontrol.ctl /tmp/

]~ rm control.ctl

SQL>shut abort

How shall i do now?

Now open your /tmp/ttt.sql and remove unnecessary rows

SQL>startup nomount

SQL>@/tmp/ttt.sql

SQL>select status from v$instance;

Now the database will be in mount state and control file will be created during above step.

Now start recovering database

SQL> recover database using backup controlfile until cancel;

SQL>alter database open resetlogs;

5. Now i had my database to normal stage and i have dump of table. Import it and check in v$table;

 imp system/system file =ts2.dmp tables='TS2'

Errors i faced during above process: 1. ORA-10562: Error occurred while applying redo to data block (file# 2, block#414) ORA-10564: table SYSAUX ORA-01110: data file 2: '/u01/oracle/app/oradata/pavan/database_1/sysaux01.dbf' ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 15929 ORA-00607: Internal error occurred while making a change to a data block ORA-00602: internal programming exception ORA-07445: exception encountered: core dump [kdxlin()+4088] [SIGSEGV] [ADDR:0xC] [PC:0x95FB47E] [Address not mapped to object] [] It occurred to me at step-4 while recovering database until cancel This error means it applied all archive logs and unable to apply transactions from redo files. So while recovering gives the file name of redo which is active and current then it will recover easily.

Cheers.....!!!!!