AnySQL.net English

    Someone required a database recovery after a sudden server crash due to the CPU fans. He asked to use AUL software to recovery the table structure, procedure & package source code etc. AUL is not good at recovering them, it's good at recover the data. And a server crash should not cause such a big problem usually, you should be able to open it in normal way in most cases, even the database is running in noarchivelog mode.

    Let me tell you the correct steps you should take :

1, Mount the database. If you lost the control file, recreate it.

2, Get the active online log file and current online log file.

3, Try to open the database with "alter database open" command.

4, If failed to open the database, then issue the "recover database" command, input the absolute path of the active online log file, then current online log file when prompted for the archive log file.

    Usually you should be able to open the database after a server crash. Don't issue the "ALTER DATABASE OPEN RESETLOGS" first, always backup all the files before you open it with resetlogs option, so when anything wrong, you can restore to the original state.

    For today's case, the DBA try to open the database with resetlogs option first without backup, so I have use some hidden parameters to skip the consistent check, and offline some rollback segments to skip the transaction recovery. The problem is he have to create a new database and export the data from old database, and import them to the new database.

    The data volume is 210GB, is it easy and quick to perform export and import?

    How many files can be stored in on directory? I don't know the answer. But when one directory have a lot of files, it will be very hard to operate, and may slow down the file access speed.

    I performed two times of LOB recovery, all recovered in TXT mode. At this mode, each LOB values is stored as a single file in the directory where you run the AUL binary. For this two times, there are no so many LOB rows, the first time has about 12000 LOB values, the second time has about 15000 LOB values, so there is no problem for all of them are stored in one directory.

    But how about there are millions of LOB values? I add a new option to AUL to distribute the LOB files among different subdirectories. The option name is "MAXLOBDIR" with default value 500 (The maximum value you can give is 2000). Then the LOB files will be stored in different directories, which can support millions of LOB values recovery, the subdirectory name will look like "LOBxxxx" (xxxx is four digit). For example :

AUL> set MAXLOBDIR 1000
  Current MAXLOBDIR is : 1000
AUL>

    Now perform the LOB recovery, and check the current directory.

C:\MYDUL\LOBREC>ls
AULCOL.TXT      LOB0245       LOB0394
AULOBJ.TXT      LOB0246       LOB0395
AULTAB.TXT      LOB0247       LOB0396
AULUSR.TXT      LOB0248       LOB0397
LOB0212         LOB0377       LOB0398
LOB0213         LOB0378       LOB0399
LOB0214         LOB0379       LOB0400
LOB0215         LOB0380       LOB0402
LOB0216         LOB0381       LOB0403
LOB0233         LOB0382       LOB0404
LOB0234         LOB0383       LOB0405
LOB0235         LOB0384       LOB0406
LOB0236         LOB0386       LOB0407
LOB0237         LOB0387       LOB0408
LOB0238         LOB0388       LOB0413
LOB0239         LOB0389       T_LOBTEST_sqlldr.ctl
LOB0240         LOB0390       T_LOBTEST_syntax.sql
LOB0242         LOB0391       aul4b.exe
LOB0243         LOB0392       lobtest.txt
LOB0244         LOB0393       t_lobtest.txt

    I unload about 5000 LOB values in the example, it looks much better than store all of them in one directory.

    Some one used AUL utility to recover millions of LOB values, and told me the recovery was very slow, about 10MB generated per minute. I don't think so, and I want to tell him it's impossible slow. They may choose the wrong method to estimate the recovery speed. There are two storage modes of the LOB values. First mode is inline storage, store the LOB value with other columns value, the second mode is file storage, store each LOB value in a distinct file.

    Inline is the default method (SET LOB_STORAGE 0), so all the LOB value is stored in one file, the file increase should be very fast. When I try to unload the SYS.SOURCE$ table, the speed is usually 6-8MB per second on my notebook. So this is impossible.

    Under file storage mode (SET LOB_STORAGE 1), each LOB value will be stored in distinct file (LOB_xxxxxxxx_xxxx.dat), and the relative LOB column will record the LOB file name. In this mode, the file increase speed maybe slow for the text file which stores the table data, and 10MB per minute is reasonable. When you estimate the recovery speed, you should take the LOB files into account. Or you just count the rows of the text file contains the table data (BLOB Recovery Example).

    If AUL give you such a bad impression, it's really unlucky, for me not a big issue, but for you, it's critical because you are not choosing the right recovery methods, and spend a lot of time in doing nothing. On a Linux platform based Intel x86 CPUs, it will run very fast.

    When you download the AUL binary and extract it, you may find that the execution file name on Windows is aul4b.exe, Linux is aul4b_linux, and Solaris is aul4b_solaris. However it's not a beta version, the "4b" means version 4 with LOB support. Since release 4.0.2, it's a release version after two formal recoveries of LOB data.

    If you see "B" is added to the suffix of the version number as following, then it's beta version.

Register Code: TRBR-CCPF-F6KJ-ALTT-MNGQ
AUL : AnySQL UnLoader(MyDUL) for Oracle 8/8i/9i/10g, release 4.0.1B

(C) Copyright Lou Fangxin 2005-2007 (AnySQL.net), all rights reserved.

AUL> exit

    Just notice.

    I asked someone who have Oracle 11g beta version to send me a data file of Oracle 11g, and I got it this morning, great thanks to this unknown friend. Then I use AUL to test it, extract the zip file and create a AUL configuration file named "db.txt" as following.

1 1 users01.dbf

    Then run the AUL and open it, check the data file header block with ORADUMP command. "ver=0x0b100000" means the data file is from Oracle 11g, "fmt=0xa2" means the block format is the same with oracle 10g's.

AUL> open db.txt
*  ts#  fno  rfn ver bsize     blocks filename
- ---- ---- ---- --- ----- ---------- -----------------------
Y    4    4    4 a2   8192        640 users01.dbf
AUL> oradump file 4 block 1
RDBA=0x01000001(4/1),type=0x0b,fmt=0xa2, seq=0x01, flag=0x04
DBID=0x44814ad1=1149323985, db=ORCL, ts#=4, ts=USERS, file#=4, blksiz=8192, blks=640, ver=0x0b100000, fzy=--O-

    There is no system information of this data file, so try the method of non-system recovery. Run the "SCAN TABLE to scan_11g.txt" command, and then find the following command lines from the generated text file.

CMD:UNLOAD OBJECT 68415 CLUSTER 0 COLUMN  NUMBER VARCHAR VARCHAR TO OBJD0000068415C000.txt;
CMD:UNLOAD OBJECT 68417 CLUSTER 0 COLUMN  NUMBER VARCHAR VARCHAR NUMBER DATE NUMBER NUMBER NUMBER TO OBJD0000068417C000.txt;
CMD:UNLOAD OBJECT 68420 CLUSTER 0 COLUMN  NUMBER NUMBER NUMBER TO OBJD0000068420C000.txt;
CMD:UNLOAD OBJECT 70052 CLUSTER 0 COLUMN  NUMBER VARCHAR VARCHAR NUMBER DATE NUMBER NUMBER NUMBER TO OBJD0000070052C000.txt;

    By carefully check, the guessed column type is completely correct, just run these command, you will see rows are recovered from Oracle 11g data file. But still need more test on Oracle 11g, then I will announce that AUL formally support Oracle 11g.