AnySQL.net English

    I got some requests of data recovery by drop operation, including drop tables and drop tablespaces. 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 table number you required. One time, the customer said there are 400 tables in their application, but from the data files they finally get more than 1500 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.

    After announcing the test version of offline oracle log miner for 10g, I spent a few day's free time in testing and bug fixing. Also I try to modify the code to support 8i/19 version oracle log files. Fortunately it's not so difficult thing to make the code work for 8i/9i, only few code need to be changed. Now you can download the MyLOG binary for oracle 8i/9i.

    No new command added, all the same as MyLOG 10g version.

    I also provide the binary file for RedHat (x86) and Solaris (Sparc64), for other platforms, I haven't the compile environments. But you can open log file from any platform by setting the BYTE_ORDER option. For example, I open an Solaris oracle log file on windows computer.

LOG> set BYTE_ORDER BIG
BYTE_ORDER = BIG
LOG> open c:\mydul\utility\gbcust1_24112.arc
DBID = 0x9d671cf9 = 2640780537
GROUP      = 8, SEQUENCE   = 24112
File Type  = 2, Next Block = 16059
Start SCN  = 0x031f.05c00824 = 3431775340580
Start Time = 2006-08-23 19:08:21
End SCN    = 0x031f.05c00990 = 3431775340944
End Time   = 2006-08-23 19:08:58
LOG>

    Do not use this script for any data recovery, it's just for testing, and no replace of Oracle's log miner work.

    We have a very old database which upgraded from Oracle 7, when I was creating an index on this database, the database run out of memory. Before I started to create the index, I did check the free memory of the host, it's 8GB free memory there.

    I run the following statement to tuning the index creation.

alter session set sort_area_size=100000000;
alter session set sort_area_retained_size=100000000;
alter session set db_file_multiblock_read_count=128;

    We found that each parallel process is allocated about 5G memory as PGA, this is abnormal. Did anyone else hit this problem before? We filed a tar, but Oracle did not give us a solution, just told us that do not specify 128 as the multiple block read count.

    After we change the value to 64, it works fine.

    I created a composite index on DEPTNO and ENAME columns for EMP table under SCOTT schema. Then run a SQL with OR in the where clause, check the execute plan:

SQL> SELECT /*+ first_rows no_expand */ * FROM EMP
   2 WHERE deptNO=10 AND (ENAME IS NULL OR ENAME > 'A');

---------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes |
---------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     3 |   330 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     3 |   330 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMP |     3 |       |
---------------------------------------------------------------

   1 - filter("ENAME" IS NULL OR "ENAME">'A')
   2 - access("DEPTNO"=10)

    The filter operation performed on the table access. Then I rewrite the SQL with a NVL function, and check execution plan again :

SQL> SELECT /*+ first_rows no_expand */ * FROM EMP
   2 WHERE deptNO=10 AND NVL(ENAME,'B') > 'A';

---------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes |
---------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     3 |   330 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     3 |   330 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMP |     1 |       |
---------------------------------------------------------------

   2 - access("DEPTNO"=10)
       filter(NVL("ENAME",'B')>'A')

    In a real case of our database, the first SQL need more than 10k consistent gets, while the second just need 150 consistent gets.

    When we cannot open the database for access, we can try AUL software. With system available we can use the DMP format for recovery, however TXT format is more robust according to the previous experience. And sometime DBA cannot find out the script to recreate the tables in new database, so generate a script to recreate the tables is useful during recovery. I made this change, when you recover database with TXT format, a new SQL file will be created.

    For example, describe the table structure in AUL.

AUL> desc anysql.emp

Storage(OBJ#=10560 OBJD=10560 TS=4 FILE=4 BLOCK=627 CLUSTER=0)
No. SEQ INT Column Name                   Type
--- --- --- ----------------------------- ----------------
  1   1   1 EMPNO                         NUMBER(4) NOT NULL
  2   2   2 ENAME                         VARCHAR2(30)
  3   3   3 JOB                           VARCHAR2(9)
  4   4   4 MGR                           NUMBER(4)
  5   5   5 HIREDATE                      DATE
  6   6   6 SAL                           NUMBER(7,2)
  7   7   7 COMM                          NUMBER(7,2)
  8   8   8 DEPTNO                        NUMBER(2)

    Recover the table by TXT format.

AUL> unload table anysql.emp to emp.txt;
2007-05-31 21:43:35
Unload OBJD=10560 FILE=4 BLOCK=627 CLUSTER=0 ...
2007-05-31 21:43:35
AUL>

    You will find that a new SQL file (tablename_syntax.sql) will be created under the directory where you run the AUL, for example (EMP_syntax.sql) :

CREATE TABLE "EMP" ( "EMPNO" NUMBER(4) NOT NULL , "ENAME" VARCHAR2(30) , "JOB" VARCHAR2(9) , "MGR" NUMBER(4) , "HIREDATE" DATE , "SAL" NUMBER(7,2) , "COMM" NUMBER(7,2) , "DEPTNO" NUMBER(2) );

    Hope this improvement can fasten the database recovery.