AnySQL.net English

    Somebody told me that his database has about 900 tables, how to perform recovery quickly with AUL/MyDUL? If we typed one command by one command, it's really a trouble work. Although I do not design the feature to perform schema based recovery or whole database recovery command in AUL/MyDUL, I do provide an alternative solution with script file.

    Use "LIST TABLE username" command to generate the commands for you:

AUL> list table anysql
UNLOAD TABLE anysql.P_MV_FACT_SALES TO P_MV_FACT_SALES.txt;
UNLOAD TABLE anysql.TIME_DIM TO TIME_DIM.txt;
UNLOAD TABLE anysql.FACT_SALES TO FACT_SALES.txt;
UNLOAD TABLE anysql.MV_FACT_SALES TO MV_FACT_SALES.txt;
UNLOAD TABLE anysql.SEG$ TO SEG$.txt;
UNLOAD TABLE anysql.P_MV_FACT_SALES PARTITION P_MAX TO P_MV_FACT_SALES_P_MAX.txt;

    Copy & paste the output to a text file, or add "TO filename" to the "LIST TABLE" command to generate the script file.

AUL> list table anysql to unload_anysql.sql

    Then we could simply type "@ filename" to run the recovery command in batch:

AUL> @unload_anysql.sql
AUL>  UNLOAD TABLE anysql.P_MV_FACT_SALES TO P_MV_FACT_SALES.txt;
AUL>  UNLOAD TABLE anysql.TIME_DIM TO TIME_DIM.txt;
2006-11-20 09:39:59
Unload OBJD=9759 FILE=4 BLOCK=11 CLUSTER=0 ...
2006-11-20 09:40:21
AUL>  UNLOAD TABLE anysql.FACT_SALES TO FACT_SALES.txt;
2006-11-20 09:40:21
Unload OBJD=9761 FILE=4 BLOCK=19 CLUSTER=0 ...
2006-11-20 09:40:29
AUL>  UNLOAD TABLE anysql.MV_FACT_SALES TO MV_FACT_SALES.txt;
2006-11-20 09:40:29
Unload OBJD=9762 FILE=4 BLOCK=99 CLUSTER=0 ...
2006-11-20 09:40:37
AUL>  UNLOAD TABLE anysql.SEG$ TO SEG$.txt;
2006-11-20 09:40:37
Unload OBJD=9796 FILE=4 BLOCK=2483 CLUSTER=0 ...
2006-11-20 09:40:45
AUL>  UNLOAD TABLE anysql.P_MV_FACT_SALES PARTITION P_MAX TO P_MV_FACT_SALES_P_MAX.txt;
2006-11-20 09:40:45
Unload OBJD=9777 FILE=4 BLOCK=2475 CLUSTER=0 ...
2006-11-20 09:40:53
AUL>

    But you cannot nest the "@ filename" command, which mean you cannot call another script file in one script file.

    Without dictionary, AUL cannot recover rows to dmp format. But we could manually create them, first we talk about the user information stored in "AULUSR.TXT" text file, the file name must be in upper case under Linux/Unix, and contains two columns (User ID and User Name, split by ","). For examples:

0,SYS
1,PUBLIC
2,CONNECT
3,RESOURCE
4,DBA
5,SYSTEM
......
25,TEST

    If you have system tablespace left, it could be generated by "unload table user$;" command.

    Without dictionary, AUL cannot recover rows to dmp format. But we could manually create them. We will talk about objects information stored in "AULOBJ.TXT" file, the file name must be in upper case under Linux/Unix. The text file should contains the following columns (split by ",") :

1, Object ID
2, User ID
3, Object Name
4, Sub-Object Name, partition or sub-partition name
5, Object Type ID

    If you have system tablespace left, it could be generated by "unload table obj$;" command, for example:

9756,5,CUST_INFO,,2
9786,5,FILE$,,2
9776,25,TEST,,2
9787,5,DMPTEST,,2

    For the object type column, you could check the SQL definition of view "DBA_OBJECTS", if you have AnySQL installed on your computer, just run "SOURCE DBA_OBJECTS" to get the view's SQL definition.

    Without dictionary, AUL cannot recover rows to dmp format. But we could manually create them. We will talk about segment information stored in "AULTAB.TXT" file, the file name must be in upper case under Linux/Unix. The text file should contains the following columns (split by ",") :

1, Object ID
2, Data Object ID
3, Tablespace ID
4, The relative file number of segment header
5, The block number of segment header
6, The create order in cluster if table is clustered

    If you have system tablespace left, it could be generated by "unload table tab$;" command. This command will read rows from TAB$, IND$, TABPART$ etc, to get the data object id and segment header information, please run this command after you get the object information by "unload table obj$;" command. For example:

12,6,0,1,57,1
32,29,0,1,177,2
22,10,0,1,89,1
18,18,0,1,121,
4,2,0,1,25,1

    AUL will get object's data object id and segment header address from this file, and the locate the segment header and get the extent allocation information (Extent Map) to perform recovery. When there is "AULEXT.TXT" exists on the same directory, it will just get data object id from this file, and get extent allocation information from "AULEXT.TXT" file, so if you have a wrong "AULEXT.TXT" file, it could affect the recovery result.

    Without dictionary, AUL cannot recover rows to dmp format. But we could manually create them. We will talk about column information stored in "AULCOL.TXT" file, the file name must be in upper case under Linux/Unix. The text file should contains the following columns (split by ",") :

1, Object ID
2, Column Order
3, Column Storage Order
4, Column Name
5, Column Type ID
6, Column Maximum Storage Length
7, Precision
8, Scale
9, Nullable
10, INTCOL#, Column Created Order
11, Property, not used by AUL now
12, Character Set ID
13, Character Set Form, value 2 means NCHAR/NVARCHAR2 column

    If you have system tablespace left, it could be generated by "unload table col$;" command, as following:

20,1,2,OBJ#,2,22,,,1,1,0,0,0
20,2,1,BO#,2,22,,,1,2,0,0,0
20,3,3,COL#,2,22,,,1,3,0,0,0
20,4,4,POS#,2,22,,,1,4,0,0,0
20,5,5,SEGCOL#,2,22,,,1,5,0,0,0
20,6,6,SEGCOLLENGTH,2,22,,,1,6,0,0,0
......

    For the column type id column, you could check the SQL definition of view "DBA_TAB_COLS", if you have AnySQL installed on your computer, just run "SOURCE DBA_TAB_COLS" to get the view's SQL defination.