Restore table drop by mistake

If there is table drop by mistake

1. Performing the incomplete recovery manually

Bring all offline datafiles online

SQL> select name,status from v$datafile ;

SQL>shutdown immediate ;

Perform complete close backup ,by copying the database file to another location this
backup must be taken before performing incomplete recovery .

SQL>startup mount;

SQL>alter session set NLS_DATE_FORMAT=’DD-MON-YYYY HH24:MI:SS’ ; this is optional

SQL>recover database until time ’07-DES-2008 10:00:00’ ;

SQL>alter database open resetlogs ;

Now you can see the deleted table,,,,,,

2. Incomplete recovery using RMAN
The set until command can take one of the following forms

• SET UNTIL TIME ‘DD MON YYYY HH:MI:SS’
• SET UNTIL LOG SEQUENCE n
• SET UNTIL SCN n

Using The LogMiner utility to determine the SCN(system change number)
The logminer utility used to analyze redo log files ,and the following steps describe how you can read the contents of redo log file

SQL>alter system set UTL_FILE_DIR=’c:\LOGMINIER’ SCOPE=SPFILE ;

SQL>SHUTDOWN IMMEDIATE ;

SQL>STARTUP ;

Create directory called LOGMINIER in c:\ drive

SQL>EXECUTE DBMS_LOGMINR_D.BUILD(‘LOGDICT.ORA’,’C:\LOGMINER’) ;

To see the log files

SQL>select member from v$logfile

MEMBER
D:\ORACLE\ORADATA\BOSST\REDO01.LOG
D:\ORACLE\ORADATA\BOSST\REDO02.LOG
D:\ORACLE\ORADATA\BOSST\REDO03.LOG
D:\ORACLE\ORADATA\BOSST\REDO04.LOG

SQL>EXECUTE DBMS_LOGMINR.ADD_LOGFILE(LOGFILENAME=>’D:\ORACLE\ORADATA\BOSST\REDO01.LOG’ ,OPTIONS => DBMS_LOGMNR.NEW) ;
SQL>EXECUTE DBMS_LOGMINR.ADD_LOGFILE(LOGFILENAME=>'D:\ORACLE\ORADATA\BOSST\REDO02.LOG’ ,OPTIONS => DBMS_LOGMNR.ADDFILE) ; etc…

Next start the mining process using DBMS_LOGMNR.START_LOGMNR procedure .The DDL_DICT_TRACKING option that mean you will track DDL command In the redo log file

SQL> DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR .DDL_DICT_TRACKING) ;

SQL>select SCN ,SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_NAME =’TABLE NAME’;

Now you can get the SCN , and after that you can stop the mining session by
executing this command

SQL>EXECUTE DBMS_LOGMNR.END_LOGMNR ;

SQL> select file# ,status from v$datafile ;

If there is any file offline you must bring them online by this command

SQL>ALTER DATABASE DATAFILE ONLINE;

Shutdown database and perform complete backup

Connect to RMAN and to the target database

RMAN>STARTUP MOUNT
RMAN>RUN{
ALLOCATE CHANNEL C1 TYPE DISK ;
SET UNTIL SCN 8767887 ;
RESTORE DATABASE;
RECOVER DATABASE ;
ALTER DATABASE OPEN RESETLOGS;
}
Now you can see the dropped table

Changing Oracle Database Character Set to Arabic

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE AR8MSWIN1256;
SHUTDOWN IMMEDIATE;
STARTUP;

Picture from Kurdistan