Friday 12 January 2018

How to Recover Drop/Truncate table from database

If by mistakenly droped any table from database and our database size in TB then how to recover that droped table then we can use given below steps.

Step 1
We need valid backup of droped/Truncated table on Source.

Step 2.
Backup Transfer to another Test/Dev/UAT server.

Step 3. We make the Fresh new instance for recovery purpose and doing some entry in below file.
ORACLE_USER$vi /etc/oratab (Make the Entry in oratab file dummy Entry).

Step 4.
ORACLE_USER$. oraenv (then provide the dummy SID name that made the enty).

Step 5.
ORACLE_USER$rman target /

Step 6.
RMAN>startup nomount force ----command on rman.

Step 7.
RMAN>restore spfile from 'Backup Path'.

Step 8.
SQL>create pfile from spfile; ---command.

Step 9.
ORACLE_USER$ vi $ORACLE_HOME/dbs/init_SID(changes the some parameter values.i.e controlefile,datafile,redolog etc.)

Step 10.
SQL>create spfile from pfile.

Step 11.
ORACLE_USER$ kill -9 (pmon)

Step 12.
ORACLE_USER$. oraenv
ORACLE_USER$rman target /

Step 13.
RMAN>startup nomount.

Step 14.
restore controlfile from 'Backup Path'.

Step 15.
RMAN>alter database mount.

RMAN>run
{
set newname for datafile 1 to '/u01/app/oracle/oradata/prod/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/prod/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/prod/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/prod/users01.dbf';
restore tablespace system, sysaux, undotbs1, users;
switch datafile all;
sql "alter database datafile 1,2,3,4 online";
recover database skip forever tablespace TEMP,APEX_1680608425593888,APEX,example;
}

--------if directory same then no need to rename of redo otherwise  rename the redo log---------------
sql "alter database rename file ''/u01/app/oracle/oradata/prod/REDO01.LOG'' to ''/u01/app/oracle/oradata/REDO01.LOG''";
sql "alter database rename file ''/u01/app/oracle/oradata/prod/REDO02.LOG'' to ''/u01/app/oracle/oradata/REDO02.LOG''";

Step 16.
SQL> alter database open resetlogs.

Step 17.
use expdp/impdp