AnySQL.net English

    All the table structure informations are stored in system tablespace, in the data tablespace, there are only a data object id and the rows you wanted. So we could scan all the data files and recover all the rows, but we actually do not know which table the rows from. Following is a step by step example of using AUL/MyDUL to do recovery.

    I create a very small tablespace contains only one datafile (Download it for demo), and create several tables in this tablespace, insert few rows, then do a checkpoint or shutdown normal to force Oracle write everything to disk, as following:

SQL> CREATE TABLE TEST1 (COL1 VARCHAR2(10));
SQL> CREATE TABLE TEST2 (COL1 VARCHAR2(10));
SQL> CREATE TABLE TEST3 (COL1 VARCHAR2(10));

    Then we open the config file, use "SCAN TABLE TO filename" to get the object list by scanning all the data blocks. As following:

AUL> open db10g.cfg
*  ts#  fno  rfn ver bsize     blocks filename
- ---- ---- ---- --- ----- ---------- ----------------------
Y    5    5    5 a2   8192        256 AULTEST01.DBF
AUL> scan table to scan_table.log
2006-08-12 13:02:43
2006-08-12 13:02:43

    Then we open the scan_table.log file, you could see the following information, the line started with "CMD:" is the unload command AUL/MyDUL guessed for you, why I call it guess is because the column type maybe incorrect for unloading. For each distinct object, 5 sample rows will be printed in hex mode. By grep utilities in Unix/Linux, we could quickly get a script to unload all the tables (include dropped or truncated tables, even piece of dropped or truncated tables). Following is the result of the scan table:

RDBA=0x01400015(5/21),type=0x06,fmt=0xa2,seq=0x03,flag=0x06
seg/obj=0x000026fb=9979,csc=0x0000.000999cd,itc=2,typ=1 - DATA
tab#=  0     nrow=  11     offs=   0

545f50415254
545f4f424a454354
545f4c4f42
545f434c4f42
42494e24773455692b46
Column Count=1
CMD:UNLOAD OBJECT 9979 CLUSTER 0 COLUMN  VARCHAR

RDBA=0x0140001d(5/29),type=0x06,fmt=0xa2,seq=0x03,flag=0x06
seg/obj=0x000026fc=9980,csc=0x0000.000999cd,itc=2,typ=1 - DATA
tab#=  0     nrow=  11     offs=   0

545f50415254
545f4f424a454354
545f4c4f42
545f434c4f42
42494e24773455692b46
Column Count=1
CMD:UNLOAD OBJECT 9980 CLUSTER 0 COLUMN  VARCHAR

RDBA=0x01400025(5/37),type=0x06,fmt=0xa2,seq=0x03,flag=0x06
seg/obj=0x000026fd=9981,csc=0x0000.000999cd,itc=2,typ=1 - DATA
tab#=  0     nrow=  11     offs=   0

545f50415254
545f4f424a454354
545f4c4f42
545f434c4f42
42494e24773455692b46
Column Count=1
CMD:UNLOAD OBJECT 9981 CLUSTER 0 COLUMN  VARCHAR

    Then we test the unload commands generated, you will see rows are recovered, in this case the three tables contain same rows:

AUL> UNLOAD OBJECT 9979 CLUSTER 0 COLUMN  VARCHAR;
2006-08-12 13:17:52
T_PART
T_OBJECT
T_LOB
T_CLOB
BIN$w4Ui+F
BIN$JRhlBf
TEST
TEST2
BIN$IRebiY
TEST1
TEST3
2006-08-12 13:17:52
AUL> UNLOAD OBJECT 9980 CLUSTER 0 COLUMN  VARCHAR;
2006-08-12 13:18:01
T_PART
T_OBJECT
T_LOB
T_CLOB
BIN$w4Ui+F
BIN$JRhlBf
TEST
TEST2
BIN$IRebiY
TEST1
TEST3
2006-08-12 13:18:01
AUL> UNLOAD OBJECT 9981 CLUSTER 0 COLUMN  VARCHAR;
2006-08-12 13:18:09
T_PART
T_OBJECT
T_LOB
T_CLOB
BIN$w4Ui+F
BIN$JRhlBf
TEST
TEST2
BIN$IRebiY
TEST1
TEST3
2006-08-12 13:18:09

    Even with this command, the recovery of lost system tablespace is more complex. Enjoy it.

    In Oracle we know that it's possible to contain some corrupted blocks that cannot be read, We could use dbv or rman to check them. But in AUL/MyDUL, it's still possible to recover rows from these corrupted blocks, unless the corruption caused the AUL/MyDUL abort the running, due to the corruption caused the AUL/MyDUL memory access out of bound. I do not want to call them corrupted blocks, I named them as dead blocks. We could get them skipped with some extra steps.

    First we need to identify them out, I will introduce "set verbose 1" command to you, with this setting, AUL/MyDUL will print out the relative data block address (RDBA) of the last processing block, so if program died, we just mark the last block as corrupted, then we restart the processing. If there are more than one dead blocks, we may take some round trips. As following:

AUL> set verbose 1
  Current VERBOSE is : 1
AUL> unload table mydul.t_lob to t_lob.txt;
2006-08-12 14:11:15
Unload OBJD=9946 FILE=4 BLOCK=219 CLUSTER=0 ...
Recover rows from RDBA=16777436 ...
Recover rows from RDBA=16777437 ...
Recover rows from RDBA=16777438 ...
Recover rows from RDBA=16777439 ...
Recover rows from RDBA=16777440 ...
<< program aborted here>>

    Then we mark the last block as corrupted and restart the processing:

AUL> corrupt rdba 16777440
AUL> corrupt list
Corrupted Blocks List:
  RDBA = 16777440 , FILE = 4 , BLOCK = 224
AUL> unload table mydul.t_lob to t_lob.txt;
2006-08-12 14:13:46
Unload OBJD=9946 FILE=4 BLOCK=219 CLUSTER=0 ...
Recover rows from RDBA=16777436 ...
Recover rows from RDBA=16777437 ...
Recover rows from RDBA=16777438 ...
Recover rows from RDBA=16777439 ...
Recover rows from RDBA=16777440 ...
Block check failed at RDBA=0x010000e0=16777440
Recover rows from RDBA=16777553 ...
Recover rows from RDBA=16777554 ...
2006-08-12 14:13:49

    The "Block check failed ..." line means AUL/MyDUL skip this marked block. Enjoy it!

    I have wrote several DBA utilities, in Windows, it can run under 8i/9i/10g client, but in Linux/Unix, it require the Oracle client version library on which compiled when run. Finally I found a solution.

    Found a development machine, we will modify the script "genclntsh" under $ORACLE_HOME/bin first as following:

#
# Library names and locations
CLNT_NAM=clntsh                          # (short) library name
CLNT_VER=9.0                            # library version number
CLNT_LNK=lib${CLNT_NAM}.so              # name of symlink to library
CLNT_LIB=${CLNT_LNK}.${CLNT_VER}        # actual library file name
LIB_DIR=${ORACLE_HOME}/${LIB}           # lib. destination directory

#
# Library names and locations
CLNT_NAM=clntsh                         # (short) library name
CLNT_VER=9.0                            # library version number
CLNT_LIB=lib${CLNT_NAM}.so              # name of symlink to library
CLNT_LNK=${CLNT_LNK}.${CLNT_VER}        # actual library file name
LIB_DIR=${ORACLE_HOME}/${LIB}           # lib. destination directory

    Second, run genclntsh (For 32 bit client library on 64Bit unix, add command option "-32"), do not run this on server host with database running:

    Third, We now can use gcc to compile the OCI program as following:

gcc -o ocidemo.bin ocidemo.c -I${ORACLE_HOME}/rdbms/demo -L${ORACLE_HOME}/lib -lclntsh -Wl,-Bdynamic

    Forth, Write a shell executable to run the OCI program, for example (ocidemo.bin):

#!/bin/sh

if [ "A${ORACLE_HOME}A" = "AA" ]; then
   echo "ORACLE_HOME environment variable not setted."
   exit
fi

if [ "A${LD_LIBRARY_PATH}A" = "AA" ];then
   LD_LIBRARY_PATH=/lib:/usr/lib
fi

if [ -d ${ORACLE_HOME}/lib32 ]; then
   LD_LIBRARY_PATH=${ORACLE_HOME}/lib32:${LD_LIBRARY_PATH}
else
   LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
fi

export LD_LIBRARY_PATH

ocidemo.bin $*

    I have test this method on RedHat Linux and Solaris, so all my OCI based utilities are ok to run under 8i/9i/10g version client.

    Oracle client is not required when using AnySQL, and you do not need to configure tnsnames when connecting to database, as following:

ASQL> conn system/oracle@localhost:1521:xe
Database connected.

    I combine some useful SQLs in AnySQL as "ora key arg ..." command, it's very easy to use, such as get the space information of tablespaces:

ASQL> ora tsfree

TABLESPACE FILES SIZE_MB FREE_MB MAXFREE PCT_USED PCT_FREE
---------- ----- ------- ------- ------- -------- --------
SYSAUX        1    430      3    2.44    99.3      0.7
USERS          1    100  98.06  98.06    1.94    98.06
SYSTEM        1    340    6.75    5.94    98.01    1.99
UNDO          1      90  14.19      3    84.24    15.76

4 rows returned.

    And in AnySQL you do not need to setting the column length when querying, it will automatically calculate the maximum length, so you can omit lots of "col ... format ..." commands, lets query the view v$datafile now:

ASQL> SELECT NAME,BYTES FROM V$DATAFILE;

NAME                                  BYTES
--------------------------------- ---------
C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840
C:\ORACLEXE\ORADATA\XE\UNDO.DBF    94371840
C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 450887680
C:\ORACLEXE\ORADATA\XE\USERS.DBF  104857600

    And lots of other news, I will introduce them later.

    These days my notebook always report unknown errors and cause some program to abort. My notebook haven't be reinstalled for two years. Few months ago, some rascal softwares became resident in my computer, like IE-BAR, etc. It's very hard to remove then, so a fresh re-installation become the easiest way.

    It took me one hour to backup all the required files to another windows server, and then ask IT engineers to reinstall my computer, took another hour, finally took a third hour to copy the files back, and some other time to reinstall some software, like Putty, Miranda IM, MSN Messenger, Skype.

    After reinstall, the notebook run faster than before. I will be very carefully to reject rascal softwares. Actually I am not so sure how to prevent them, do you have some good ideas or practices? Can you share with me?

    Few days ago, I lost 4 months of blog entries on AnySQL.net. I just lost blog entries, no others are lost, have no impact to my jobs. But we should be really careful.

    Two years ago (2004.08.15), I joined current company as an Oracle DBA.