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
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
No comments:
Post a Comment