I got some requests of data recovery by drop operation, including drop tables and drop tablespaces and drop schema. For this kind of recovery, the most complex step is to map the table with the data object id found from the remaining data files. For example, if you move a table, oracle actually allocate new extent and assign a new data object id to the table, and the old data object id still exists in the data file, when you have a lot of this kind of operation (move, drop, create), you will get more and more data object id from data files than the tables you have. One time, the customer said there are 400 tables in their application, but from the data files they finally get more than 1500 different data object ids.
Most of the recovery time is spent on the mapping of the data object id with the table name. But now, if you have the oracle log file (online log or archive log) exists, I can use MyLOG utility to find out the delete operation performed on SYS.OBJ$ table, then got the mapping of the data object id with table name as quickly as possible. So next time when you drop something by mistake, please back up the data file and the log file immediately for quick recovery.
Add the following lines to file "LOGTAB.TXT" for MyLOG.
9999,18,OBJ$,
Add the following lines to file "LOGCOL.TXT" for MyLOG.
9999,1,OBJ#,NUMBER
9999,2,DATAOBJ#,NUMBER
9999,3,OWNER#,NUMBER
9999,4,NAME,VARCHAR2
9999,5,NAMESPACE,NUMBER
9999,6,SUBNAME,VARCHAR2
9999,7,TYPE#,NUMBER
9999,8,CTIME,DATE
9999,9,MTIME,DATE
9999,10,STIME,DATE
9999,11,STATUS,NUMBER
9999,12,REMOTEOWNER,VARCHAR2
9999,13,LINKNAME,VARCHAR2
9999,14,FLAGS,NUMBER
9999,15,OID$,RAW
9999,16,SPARE1,NUMBER
9999,17,SPARE2,NUMBER
9999,18,SPARE3,NUMBER
9999,19,SPARE4,VARCHAR2
9999,20,SPARE5,VARCHAR2
9999,21,SPARE6,DATE
Then open the oracle log file with MyLOG utility, issue command "EXTRACT START 2 TABLE OBJ$", then you will get the lines as following :
RBA=0x000069.000010b4.00b0, XID=0x0009.016.00000093, RID=AAAAASAABAAAGsmAAI
DELETE OBJ$ WHERE OBJ# = 10566 AND DATAOBJ# = 10566 AND OWNER# = 25 AND NAME = 'MYLOG10G' AND NAMESPACE = 1 AND SUBNAME = NULL AND TYPE# = 2 AND CTIME = '2007-05-29 10:50:12' AND MTIME = '2007-05-29 10:50:12' AND STIME = '2007-05-29 10:50:12' AND STATUS = 1 AND REMOTEOWNER = NULL AND LINKNAME = NULL AND FLAGS = 0 AND OID$ = NULL AND SPARE1 = 6 AND SPARE2 = 1;
I don't know whether Oracle's log miner can extract the SQLs for system dictionary tables.
Comments (1)
Cool!
Posted by blue_prince | September 21, 2007 4:00 PM