|
|
OPEN
AUL needs a config file to specify where the datafiles are. It's purely a text file so you can edit it with any text editor, such as notepad, ultraedit etc. The text file should contain three columns: file#, rfile#, file name. There should be a blank space between the columns, and no blank space in file name, because I use blank space as the column seperator. Following is an example of the config file. If you want to comment a line, place "#" at the line header:
#This is demo database
#fileno rfileno filename
1 1 system01.dbf
...
If the data file header is not corrupted, then you can specify wrong file# and rfile#. When AUL opens the config file, it will read this information from datafiles' header. But if your datafile header is corrupted, you must specify the correct value here. After AUL runs the open command, the output should look like this.
AUL> open db8i.cfg
* fno rfn ver bsize blocks filename
- ---- ---- --- ----- ---------- ---------------
Y 1 1 02 8192 16384 system01.dbf
The first column labeled with "*" should be "Y", which meana AUL has successfully read the required information from datafile header, else you may not have given the correct BLOCK_SIZE or BYTE_ORDER, or else datafile header is corrupted.
CHECK
Sometimes you may open your config file without specifying the correct BYTE_ORDER, BLOCK_SIZE, or HEAD_SIZE, you can then use "SET" command to correct them, and then use "CHECK" command to reread the required information from the datafile header. This command has the same effect on "OPEN" command; just that you do not need to specify a configuration file name.
AUL : AnySQL UnLoader for Oracle 8/8i/9i/10g, release 3.0.8
(@)Copyright Lou Fangxin 2005, all rights reserved.
AUL> set byte_order big
Current BYTE_ORDER is : BIG
AUL> open tool.txt
* ts# fno rfn ver bsize blocks filename
- ---- ---- ---- --- ----- ---------- -----------------------
N 0 1 4 00 8192 16776960 DATA01.DBF
AUL> set byte_order little
Current BYTE_ORDER is : LITTLE
AUL> check
* ts# fno rfn ver bsize blocks filename
- ---- ---- ---- --- ----- ---------- -----------------------
Y 21 22 22 02 8192 371200 DATA01.DBF
AUL>
This command is not critical. You can just reopen the config file.
SET
To correctly unload data, you need to tell AUL some option values by the "SET" command. Currently 12 options are available. Some are used to tell AUL the datafile format, some to set the output file format, and some to tune AUL performance, or to limit the rows to be unloaded.
Options to tell AUL the datafile format:
- BLOCK_SIZE: Tell AUL the Oralce database's block size of the datafile, valid values are 2048, 4096, 8192, 16384, 32768, you must specify the correct value for this option. Default value is 8192.
- BYTE_ORDER: Tell AUL the endianness of the datafile. Because of hardware platform difference, when different OSes write multibyte type data to datafiles, it can be saved in different orders, little byte first or big byte first. Solaris on Sun SPARC, AIX, HPUX, IBM zSeries Linux, Mac are big byte endian, and Windows, Linux IA, Tru64, Open VMS, Linux 64(AMD) are little endian. The default for AUL is LITTLE. Adding this option enables AUL for Windows platform to correctly recover datafiles from Solaris SPARC platform.
- HEAD_SIZE: On some platforms (IBM AIX), if you use raw device, OS will keep some bytes at the datafile header to save private information. If you use AUL to recover data, you may need to specify HEAD_SIZE to skip these bytes. The default value is 0, which works for most platforms. There're no reserved bytes for datafile.
Options used to set the output file format:
- OUTPUT_STYLE: AUL supports two types of output file, plain text file (TXT) and v817 dmp file (DMP). Dmp format is useful in some cases, for example rows with long/long raw columns, or text with new lines. The default output style is TXT.
- FIELD_TAG: set the field seperator when output style is plain text. The default is ",". You can set it to any string less than 128 bytes. I use "\" as an escape char, for example "\b" means back, "\l" means "|", "\t" means tab, "\s" means space, "\r" means return, "\n" means new line. You can also specify two hex codes for a char by "\x" prefixed, for example, "\x07", "\x08" etc.
- RECORD_TAG: set the record seperator when output style is plan text. The default is "\r\n". You can set it to any string less than 128 bytes. I use "\" as an escape char, for example "\b" means back, "\l" means "|", "\t" means tab, "\s" means space, "\r" means return, "\n" means new line. You can also specify two hex codes for a char by "\x" prefixed, for example, "\x07", "\x08" etc.
- LONG_TAG: This is used for setting an enclose string for long/lob value, because it mainly contains multiple lines. If you set this value, the long/lob value will be output as "<LONG_TAG>lob/long value</LONG_TAG>". It's only valid when output style is set to "TXT"
- CHARSET: When you unload data to dmp format, you may need to set this, the default value for this is 367(UTF8).
Options used to limit the rows to be exported:
- COMMITED_ROW: if set to true, AUL will try to unload committed rows only. The default value is FALSE. Even if you set it to true, AUL will not try to find old value from RBS, so you may lose more data.
- DELETED_ROW: if set to true, AUL will ignore the deleted tag in row header, and try to unload the rows. However because of Oracle's delayed block cleanout, illegible data is possible when you set this option to TRUE. The default option for this is FALSE.
Options used to tune the AUL:
- CACHE_SIZE: setting the read buffer of AUL. The default is 256kb. For most cases, the default is enough. You should set this value to multiples of your block size.
- BLOCK_CHECK: if set to true, AUL will check the block header and block tail. If they don't match, AUL will not process the rows in this block. The default value is FALSE. You rarely need to set it to TRUE.
"SET" is the most complex command in AUL. You need to know some Oracle internals to get familiar with it.
SCAN
This command is used to find out the segment header, sample block, or extent map. It's very useful when there is no system available. In case the segment header or extent map is damaged, you must use "SCAN" command to read the extent map and save it to "AULEXT.TXT" plain text file.
Currently you can only scan "HEADER", "DATA", "EXTENT" only, where they're defined as:
- HEADER
It scans the the segment header only. With this command you can easily list which objects are stored in a specific datafile. The screen output will tell you the type of segment (data, index etc).
- DATA
It lists some sample blocks for each object or specified object. Usually it will report two data blocks per extent. It can be used to analyze the table structure when system is unavailable, in which case, you need to find some sample blocks, and then you can guess the data type of each column.
- EXTENT
It will generate the file "AULEXT.TXT", which contains the extent allocation information. Usually it's stored in the segment header and extent map block, but when segment header is damaged or extent map block is corrupted, you can use "SCAN EXTENT" to scan all the datafile or a specific datafile to reconstruct the extent map. If this file exists, AUL will not try to read extent map from segment header or extent map, which means if you have incorrect "AULEXT.TXT" file, you may not unload the correct data.
To better understand this command, you can find a datafile with one or two object, and try to run the above command, then take a look at the output. You will easily master this command. For example:
AUL> scan header file 4
2005-02-25 14:40:20
RDBA=0x01000009(4/9),type=0x10,fmt=0x02,seq=0x01,flag=0x00
seg/obj=0x00001071=4209
RDBA=0x01000021(4/33),type=0x10,fmt=0x02,seq=0x01,flag=0x00
seg/obj=0x00001072=4210
2005-02-25 14:40:30
AUL>
UNLOAD (With System Tablespace)
After you open the config file, you can use "UNLOAD" command to recover data now. Before you start to run "UNLOAD" command, you should run the "SET" command to config the field_tag / record_tag / output_style / charset properly. Here we will discuss only the case where the system tablespace is available to get the dict information.
In this case we need to unload the system dict information first. There are four special unload commands for this:
- UNLOAD TABLE USER$; //AULUSR.TXT, contains user_id and username.
- UNLOAD TABLE OBJ$; //AULOBJ.TXT, contains obj#,name,subname,objtype etc.
- UNLOAD TABLE TAB$; //AULTAB.TXT, contains the segment header information.
- UNLOAD TABLE COL$; //AULCOL.TXT, contains the column information.
When you run these four commands, AUL actually does not dump all the information as a normal table. Instead it will generate four text files containing the dict rows which can be used by AUL later. Not all the columns are unloaded for these dict tables. You should run it in the order shown above, because "UNLOAD TABLE SYS.TAB$" will query the AULOBJ.TXT file to get the object id of sys.ind$, sys.tabpart$, sys.indpart$, sys.tabsubpart$, sys.indsubpart$.
Now it's very easy to unload table data. You can just run "UNLOAD TABLE username.tablename TO filename;" to unload the table rows. Here is an example to unload a table to text file:
AUL : AnySQL UnLoader for Oracle 8/8i/9i/10g, release 3.0.8
(@)Copyright Lou Fangxin 2005, all rights reserved.
AUL> host ls AUL*.txt
AULCOL.TXT AULOBJ.TXT AULTAB.TXT AULUSR.TXT
AUL> open db10g.cfg
* ts# fno rfn ver bsize blocks filename
- ---- ---- ---- --- ----- ---------- -----------------------------------
Y 0 1 1 a2 8192 39936 C:\ORACLE\ORADATA\SYSTEM01.DBF
Y 1 2 2 a2 8192 16384 C:\ORACLE\ORADATA\UNDOTBS01.DBF
Y 2 3 3 a2 8192 8192 C:\ORACLE\ORADATA\SYSAUX01.DBF
AUL> unload table sys.file$;
2005-11-10 11:46:47
Unload OBJD=17 FILE=1 BLOCK=113 CLUSTER=0 ...
1,2,32768,0,1,0,0,0,8,,4194306
2,2,16384,1,2,0,0,0,5024,,8388610
3,2,8192,2,3,0,0,0,5153,,12582914
2005-11-10 11:46:48
AUL>
The trial version I provided can read the first 64k block (for 8k block, it supports files less than 512mb). This is enough for testing. Download and enjoy!
DESC
When SYSTEM dict is avaiable, and you have unloaded the dict to four text files, you can use "DESCRIBE / DESC" command to view table structure and storage information. AUL will look forward through the four dict text files and get the required information. However the columns may not display as the defined order. It follows the unload order. You can also add a partition name or sub-partition name after table name, since the partition and sub-partition names are in the same namespace. You just need to provide a sub-name, no matter whether it's with partition or sub-partition.
DESC owner.tablename [partition]
The following is an example for a non-partitoned table. If you just describe a partitioned table without specifying the partition name or if you specify a wrong partition name, you will not get storage information.
AUL> desc sys.file$
Storage(OBJ#=17 OBJD=17 TS=0 FILE=1 BLOCK=113 CLUSTER=0)
No. SEQ Column Name Type
--- --- ----------------------------- ----------------
1 1 FILE# NUMBER NOT NULL
2 2 STATUS$ NUMBER NOT NULL
3 3 BLOCKS NUMBER NOT NULL
4 4 TS# NUMBER
5 5 RELFILE# NUMBER
6 6 MAXEXTEND NUMBER
7 7 INC NUMBER
8 8 CRSCNWRP NUMBER
9 9 CRSCNBAS NUMBER
10 10 OWNERINSTANCE VARCHAR2(30)
11 11 SPARE1 NUMBER
12 12 SPARE2 NUMBER
13 13 SPARE3 VARCHAR2(1000)
14 14 SPARE4 DATE
The first line is the storage information for the table, "OBJ#" is object id, "OBJD" is data object id, "TS" is tablespace id,
and
"FILE" and "BLOCK" construct the segment header information. "FILE" is always relative file number within the tablespace. "CLUSTER" is the order of the object created on a specific cluster. For a non-clustered table, it's always zero. In AUL, when performing unload, we always use "OBJD" to identify the data block.
This feature is only available when system is available. You can successfully unload the dict information, or you manually create the four text files. If you don't know which table you have, you can also run the "LIST" command. When you run "LIST TABLE" command, it will print the unload command for you, just for convenience.
LIST { TABLE | VIEW | PROCEDURE | FUNCTION |INDEX | PACKAGE } username
AUL> list view system
MVIEW_WORKLOAD
MVIEW_FILTER
MVIEW_LOG
MVIEW_FILTERINSTANCE
......
AUL> LIST TABLE SYSTEM
UNLOAD TABLE SYSTEM.MVIEW$_ADV_WORKLOAD TO MVIEW$_ADV_WORKLOAD.txt;
UNLOAD TABLE SYSTEM.MVIEW$_ADV_BASETABLE TO MVIEW$_ADV_BASETABLE.txt;
UNLOAD TABLE SYSTEM.MVIEW$_ADV_SQLDEPEND TO MVIEW$_ADV_SQLDEPEND.txt;
......
|