非常规恢复 – 第13页

乱用_allow_resetlogs_corruption参数导致悲剧

一个朋友11.2.0.1的数据库因为断电,出现不能正常open问题,自己尝试恢复,折腾了几天,最后让我帮忙的时候错误如下

SQL> startup
ORACLE 例程已经启动。
Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             545260968 bytes
Database Buffers          226492416 bytes
Redo Buffers                5259264 bytes
数据库装载完毕。
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 225)
ORA-01110: data file 1: 'F:\APP\ADMINISTRATOR\ORADATA\YFCLOUD\SYSTEM01.DBF'
进程 ID: 5964
会话 ID: 1144 序列号: 5

从启动的日志提示看初步判断就是悲剧了,因为根据经验值在11gr2版本中,该错误就是undo$(分析trace文件进步一确定是undo$),该block出现异常,数据库在启动的时候要扫描该表，把相关的回滚段给online起来,现在他异常了,数据库肯定无法正常启动
dbv检查数据库文件

F:\>dbv file='F:\APP\ADMINISTRATOR\ORADATA\YFCLOUD\SYSTEM01.DBF'
DBVERIFY: Release 11.2.0.1.0 - Production on 星期三 5月 22 11:06:00 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - 开始验证: FILE = F:\APP\ADMINISTRATOR\ORADATA\YFCLOUD\SYSTEM01.DBF
页 225 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x004000e1 (file 1, block 225)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x004000e1
 last change scn: 0x0000.00d65120 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xb98e0601
 check value in block header: 0xb307
 computed block checksum: 0xe8ae
DBVERIFY - 验证完成
检查的页总数: 134400
处理的页总数 (数据): 98226
失败的页总数 (数据): 0
处理的页总数 (索引): 14189
失败的页总数 (索引): 0
处理的页总数 (其他): 4178
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 17806
标记为损坏的总页数: 1
流入的页总数: 1
加密的总页数        : 0
最高块 SCN            : 14045769 (0.14045769)

看到这里,可以确定坏块的存在，根据上面的提示,我们发现tailchk值不正确,应该是5120+06+01,而不该是b98e0601,通过bbed查看

BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x004000e1
   ub4 bas_kcbh                             @8        0x00d65120
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x5ba9
   ub2 spare3_kcbh                          @18       0x0000
BBED> p tailchk
ub4 tailchk                                 @8188     0xb98e0601

进一步证明是tailchk异常导致,分析alert日志,数据库异常断电,然后启动的时候发现如下错误

Recovery of Online Redo Log: Thread 1 Group 2 Seq 431 Reading mem 0
  Mem# 0: F:\APP\ADMINISTRATOR\ORADATA\YFCLOUD\REDO02.LOG
RECOVERY OF THREAD 1 STUCK AT BLOCK 451 OF FILE 3
Aborting crash recovery due to error 1172
Errors in file f:\app\administrator\diag\rdbms\yfcloud\yfcloud\trace\yfcloud_ora_772.trc:
ORA-01172: 线程 1 的恢复停止在块 451 (在文件 3 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
Errors in file f:\app\administrator\diag\rdbms\yfcloud\yfcloud\trace\yfcloud_ora_772.trc:
ORA-01172: 线程 1 的恢复停止在块 451 (在文件 3 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
ORA-1172 signalled during: alter database open...
Tue May 21 14:27:29 2013
ALTER DATABASE RECOVER  datafile 3
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 2 Seq 431 Reading mem 0
  Mem# 0: F:\APP\ADMINISTRATOR\ORADATA\YFCLOUD\REDO02.LOG
Errors in file f:\app\administrator\diag\rdbms\yfcloud\yfcloud\trace\yfcloud_ora_772.trc  (incident=112164):
ORA-00600: 内部错误代码, 参数: [3020], [3], [451], [12583363], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 451, file offset is 3694592 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: 数据文件 3: 'F:\APP\ADMINISTRATOR\ORADATA\YFCLOUD\UNDOTBS01.DBF'
ORA-10560: block type 'KTU UNDO BLOCK'
Media Recovery failed with error 600
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 3  ...

因为file# 3, block# 451和redo信息不一致,出现ora-600[3020]错误,而file# 3为undo文件,朋友从而设置undo_management=’manual’并设置了_allow_resetlogs_corruption=true,然后进行不完全恢复,从而出现了如下错误提示

Tue May 21 14:41:23 2013
SMON: enabling cache recovery
Corrupt block relative dba: 0x004000e1 (file 1, block 225)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x004000e1
 last change scn: 0x0000.00d65120 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xb98e0601
 check value in block header: 0xb307
 computed block checksum: 0xe8ae
Reading datafile 'F:\APP\ADMINISTRATOR\ORADATA\YFCLOUD\SYSTEM01.DBF'
for corruption at rdba: 0x004000e1 (file 1, block 225)
Reread (file 1, block 225) found same corrupt data
Errors in file f:\app\administrator\diag\rdbms\yfcloud\yfcloud\trace\yfcloud_ora_4892.trc  (incident=120165):
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 225)
ORA-01110: 数据文件 1: 'F:\APP\ADMINISTRATOR\ORADATA\YFCLOUD\SYSTEM01.DBF'
Errors in file f:\app\administrator\diag\rdbms\yfcloud\yfcloud\trace\yfcloud_ora_4892.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 225)
ORA-01110: 数据文件 1: 'F:\APP\ADMINISTRATOR\ORADATA\YFCLOUD\SYSTEM01.DBF'
Errors in file f:\app\administrator\diag\rdbms\yfcloud\yfcloud\trace\yfcloud_ora_4892.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 225)
ORA-01110: 数据文件 1: 'F:\APP\ADMINISTRATOR\ORADATA\YFCLOUD\SYSTEM01.DBF'
Error 604 happened during db open, shutting down database
USER (ospid: 4892): terminating the instance due to error 604

从而的原因基本上可以从操作过程中了解到:数据库是因为file# 3 block# 451和redo不一致导致问题,而恢复的操作人员冲动的使用了_allow_resetlogs_corruption参数,从而使得数据库出现了不一致性,也就是导致file# 1 block# 225坏块的根本原因,针对这样的情况,完全没有到使用_allow_resetlogs_corruption隐含参数地步

使用bbed修改tailchk

BBED> p tailchk
ub4 tailchk                                 @8188     0xb98e0601
BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 225
Block 225 is corrupt
***
Corrupt block relative dba: 0x004000e1 (file 0, block 225)
Fractured block found during verification
Data in bad block -
 type: 6 format: 2 rdba: 0x004000e1
 last change scn: 0x0000.00d65120 seq: 0x1 flg: 0x06
 consistency value in tail: 0xb98e0601
 check value in block header: 0x5ba9, computed block checksum: 0x0
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 2
BBED> m /x 01062051
 File: system01.dbf (0)
 Block: 226              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 01062051
 <32 bytes per line>
BBED> p tailchk
ub4 tailchk                                 @8188     0x51200601
BBED> sum apply
Check value for File 0, Block 226:
current = 0xb307, required = 0xb307
BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 225
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

bbed修改block之后,数据库直接正常打开,完成数据库恢复任务,在这里很明显是因为错误的使用了_allow_resetlogs_corruption参数,屏蔽了redo前滚导致了相关的坏块,所以大家在数据库异常恢复的时候,需要知道各个参数的意义,而不要乱使用,很可能导致不可控结果

一次侥幸的OSD-04016 O/S-Error异常恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：一次侥幸的OSD-04016 O/S-Error异常恢复

一台数据库因为异常断电导致硬盘IO出现O/S-Error: (OS 23) 数据错误(循环冗余检查)错误，使得datafile 6无法完成实例恢复.使用dbv检查该数据文件也出现类似错误,尝试copy该文件,也出现了类似的错误.尝试dd拷贝完整,发现dd也只能拷贝81951个block.

Tue May 14 15:32:10 2013
Completed redo scan
 16941 redo blocks read, 1106 data blocks need recovery
Tue May 14 15:32:17 2013
Errors in file d:\oracle\product\10.2.0\admin\water\bdump\water_p002_1472.trc:
ORA-01115: IO error reading block from file 6 (block # 81951)
ORA-01110: data file 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\WATER\YD_DATA01.DBF'
ORA-27070: async read/write failed
OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 23) 数据错误(循环冗余检查)。

因为该数据库有一天前的备份,而且他们只要求恢复其中三张核心表的数据,通过分析数据字典,确定出来相关表的block均不在block 81951之上,也就是说,如果数据库只是该block异常了,可以通过跳过该block,从而copy相关block,来实现数据库恢复,因为是一个文件的中间部分异常了,所以决定使用dd来copy文件正常部分

dd if=D:\ORACLE\PRODUCT\10.2.0\ORADATA\WATER\YD_DATA01.DBF bs=8192 count=81951 of=h:\dd\yd_data01_1.dbf
dd if=D:\ORACLE\PRODUCT\10.2.0\ORADATA\WATER\YD_DATA01.DBF bs=8192  skip=81952   of=h:\dd\yd_data01_2.dbf

dd出来文件之后,因为我们跳过了block 81952(block 0 数据库为记录),所以我们需要通过dd来构造block 81952,并且把他们合并到一起

dd if=/dev/zero of=h:\dd\yd_data01_1.dbf seek=81951 bs=8192 count=1
dd if=h:\dd\yd_data01_2.dbf seek=81952 bs=8192 of=h:\dd\yd_data01_1.dbf

然后使用dul工具抽出来客户需要的三张核心表的数据,恢复工作算完成。
针对本次恢复,如果需求是open数据库,通过设置隐含参数,bbed之类原则上也可以实现.
这次的恢复算是比较侥幸:1.客户有一天前的exp,只需要恢复三张核心表数据;2.三张表的数据恰好都不在损坏的block中;3.数据库就损坏了一个block.
如果出现不幸情况,那可能需要先硬盘恢复,然后数据库恢复,最后折腾数据.
总之再次提醒各位:数据库备份很重要,很重要.对于需求是不能丢失数据的系统备份,一定要rman的方式备份,千万别选择exp/expdp

bbed处理ORA-01200故障

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：bbed处理ORA-01200故障

一个朋友的测试库出现ORA-01200错误,正好周末比较空闲,随手帮他使用bbed进行了恢复,给广大朋友提供一种解决该问题的方法
数据库启动报错

C:\Users\Administrator>sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on 星期日 5月 12 22:09:11 2013
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
SQL> connect/as sysdba
已连接到空闲例程。
SQL> startup force
ORACLE 例程已经启动。
Total System Global Area 1071333376 bytes
Fixed Size                  1334380 bytes
Variable Size             318768020 bytes
Database Buffers          746586112 bytes
Redo Buffers                4644864 bytes
数据库装载完毕。
ORA-01122: 数据库文件 1 验证失败
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
ORA-01200: 87946 的实际文件大小小于 88320 块的正确大小

这里的错误很明显是因为file 1的数据文件头记录block大小为88320个block,而该数据文件的实际大小只有87946个block,所以出现该问题.

dbv检测文件

D:\app\Administrator\oradata\orcl>dbv file=SYSTEM01.DBF
DBVERIFY: Release 11.1.0.6.0 - Production on 星期日 5月 12 22:30:29 2013
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - 开始验证: FILE = SYSTEM01.DBF
DBVERIFY - 验证完成
检查的页总数: 87040
处理的页总数 (数据): 62870
失败的页总数 (数据): 0
处理的页总数 (索引): 11055
失败的页总数 (索引): 0
处理的页总数 (其它): 2437
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 10678
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 980055 (0.980055)

检查发现该数据文件未发现坏块,减小了该数据文件通过bbed恢复异常的风险,数据库最怕就是system中出现很多坏块

使用bbed修改kccfhfsz
因为win的bbed问题,所以拷贝到我的电脑上进行修改

C:\Users\XIFENFEI\Desktop\temp>bbed filename=system01.dbf blocksize=8192
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sun May 12 23:27:26 2013
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set block 2
        BLOCK#          2
--从一台机器中拷贝到另外的机器，实际中的block可能发生改变，因为含block 0
BBED> map
 File: system01.dbf (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header
 struct kcvfh, 360 bytes                    @0
 ub4 tailchk                                @8188
BBED> p kcvfhhdr.kccfhfsz
ub4 kccfhfsz                                @44       0x0001578a
--通过ORA-01200错误报出来的文件头记录大小88320实际就是0x0001578a
BBED> set mode edit
        MODE            Edit
BBED> set count 32
        COUNT           32
BBED> d
 File: system01.dbf (0)
 Block: 2                Offsets:   44 to   75           Dba:0x00000000
------------------------------------------------------------------------
00590100 00200000 01000300 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> m /x 8A570100
 File: system01.dbf (0)
 Block: 2                Offsets:   44 to   75           Dba:0x00000000
------------------------------------------------------------------------
 8a570100 00200000 01000300 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
--通过ORA-01200错误报出来的数据文件实际大小,来修改该文件头的kcvfhhdr.kccfhfsz值,也可以通过文件实际大小计算出来
BBED> p kcvfhhdr.kccfhfsz
ub4 kccfhfsz                                @44       0x0001578a
BBED> sum apply
Check value for File 0, Block 2:
current = 0x0f79, required = 0x0f79
BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 1
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

打开数据库

SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> alter database open;
数据库已更改。

dul处理分区表

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：dul处理分区表

创建SALES分区表案例

CREATE TABLE SALES
(
  PRODUCT_ID VARCHAR2(5),
  SALES_DATE DATE,
  SALES_COST NUMBER(10),
  STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE)
  SUBPARTITION BY LIST (STATUS)
  SUBPARTITION TEMPLATE
  (
    SUBPARTITION SUB1 VALUES ('ACTIVE') ,
    SUBPARTITION SUB2 VALUES ('INACTIVE')
  )
(
  PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')),
  PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD'))
)
/
INSERT INTO SALES VALUES('00001','01-Jan-02',100,'ACTIVE')
/
INSERT INTO SALES VALUES('00002','01-Jan-01',200,'ACTIVE')
/
INSERT INTO SALES VALUES('00003','01-Feb-03',300,'INACTIVE')
/
INSERT INTO SALES VALUES('00004','04-Feb-03',300,'INACTIVE')
/
INSERT INTO SALES VALUES('00005','04-Feb-02',300,'INACTIVE')
/

查询结果

SQL> select * from sales;
PRODU SALES_DATE   SALES_COST STATUS
----- ------------ ---------- --------------------
00001 01-JAN-02           100 ACTIVE
00002 01-JAN-01           200 ACTIVE
00005 04-FEB-02           300 INACTIVE
00003 01-FEB-03           300 INACTIVE
00004 04-FEB-03           300 INACTIVE
SQL> select  * from sales PARTITION(p1);
PRODU SALES_DATE   SALES_COST STATUS
----- ------------ ---------- --------------------
00001 01-JAN-02           100 ACTIVE
00002 01-JAN-01           200 ACTIVE
00005 04-FEB-02           300 INACTIVE
SQL> select  * from sales PARTITION(p2);
PRODU SALES_DATE   SALES_COST STATUS
----- ------------ ---------- --------------------
00003 01-FEB-03           300 INACTIVE
00004 04-FEB-03           300 INACTIVE
SQL> select  * from sales SUBPARTITION(p1_sub1);
PRODU SALES_DATE   SALES_COST STATUS
----- ------------ ---------- --------------------
00001 01-JAN-02           100 ACTIVE
00002 01-JAN-01           200 ACTIVE
SQL> select  * from sales SUBPARTITION(p1_sub2);
PRODU SALES_DATE   SALES_COST STATUS
----- ------------ ---------- --------------------
00005 04-FEB-02           300 INACTIVE
SQL> select  * from sales SUBPARTITION(p2_sub1);
no rows selected
SQL> select  * from sales SUBPARTITION(p2_sub2);
PRODU SALES_DATE   SALES_COST STATUS
----- ------------ ---------- --------------------
00003 01-FEB-03           300 INACTIVE
00004 04-FEB-03           300 INACTIVE

启动dul

[oracle@xifenfei dul]$ ./dul
Data UnLoader: 10.2.0.5.20 - Internal Only - on Sat Jan 19 17:37:45 2013
with 64-bit io functions
Copyright (c) 1994 2013 Bernard van Duijnen All rights reserved.
 Strictly Oracle Internal Use Only
DUL: Warning: Recreating file "dul.log"
Reading USER.dat 91 entries loaded
Reading OBJ.dat 74764 entries loaded and sorted 74764 entries
Reading TAB.dat 2882 entries loaded
Reading COL.dat 94598 entries loaded and sorted 94598 entries
Reading SEG.dat 17 entries loaded
Reading EXT.dat 43 entries loaded and sorted 43 entries
Reading TABPART.dat 150 entries loaded and sorted 150 entries
Reading TABCOMPART.dat 3 entries loaded and sorted 3 entries
Reading TABSUBPART.dat 36 entries loaded and sorted 36 entries
Reading INDPART.dat 169 entries loaded and sorted 169 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 5150 entries loaded
Reading LOB.dat
DUL: Warning: Increased the size of DC_LOBS from 1024 to 8192 entries
 1286 entries loaded
Reading ICOL.dat 7569 entries loaded
Reading COLTYPE.dat 3003 entries loaded
Reading TYPE.dat 2872 entries loaded
Reading ATTRIBUTE.dat 11127 entries loaded
Reading COLLECTION.dat 985 entries loaded
Reading COMPATSEG.dat 0 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 10 entries loaded
Reading PROPS.dat 36 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16
Found db_id = 4188950066
Found db_name = ORA11G

unload 语法

UNLOAD [TABLE]  [  schema_name . ]  table_name
              [ PARTITION(  partition_name ) ]
              [ SUBPARTITION(  sub_partition_name ) ]
              [ (  column_definitions ) ]
              [  cluster_clause  ]
              [  storage_clause  ] ;

unload整个表

DUL> unload table chf.SALES;
. unloading table                     SALES
 . Unloading partition                      P1
 .   Unloading sub partition                 P1_SUB1
 .   Unloading sub partition                 P1_SUB2
 . Unloading partition                      P2
 .   Unloading sub partition                 P2_SUB1
 .   Unloading sub partition                 P2_SUB2
.           table SALES total        5 rows unloaded
[root@xifenfei dul]# ls -l CHF_SALES*
-rw-r--r-- 1 oracle oinstall 421 Jan 19 18:09 CHF_SALES.ctl
-rw-r--r-- 1 oracle oinstall 251 Jan 19 18:09 CHF_SALES.dat
[root@xifenfei dul]# more CHF_SALES.dat
|00001| |01-JAN-2002 AD 00:00:00| |100| |ACTIVE|
|00002| |01-JAN-2001 AD 00:00:00| |200| |ACTIVE|
|00005| |04-FEB-2002 AD 00:00:00| |300| |INACTIVE|
|00003| |01-FEB-2003 AD 00:00:00| |300| |INACTIVE|
|00004| |04-FEB-2003 AD 00:00:00| |300| |INACTIVE|

unload 分区表

DUL> unload table chf.SALES PARTITION(p1);
. unloading table                     SALES
 . Unloading partition                      P1
 .   Unloading sub partition                 P1_SUB1
 .   Unloading sub partition                 P1_SUB2
.           table SALES total        3 rows unloaded
[root@xifenfei dul]# ls -l CHF_SALES_P1*
-rw-r--r-- 1 oracle oinstall 424 Jan 19 18:10 CHF_SALES_P1.ctl
-rw-r--r-- 1 oracle oinstall 149 Jan 19 18:10 CHF_SALES_P1.dat
[root@xifenfei dul]# more CHF_SALES_P1.dat
|00001| |01-JAN-2002 AD 00:00:00| |100| |ACTIVE|
|00002| |01-JAN-2001 AD 00:00:00| |200| |ACTIVE|
|00005| |04-FEB-2002 AD 00:00:00| |300| |INACTIVE|

unload 子分区表

DUL> unload table chf.SALES SUBPARTITION(p2_SUB2);
. unloading table                     SALES
 . Unloading partition                      P1
 . Unloading partition                      P2
 .   Unloading sub partition                 P2_SUB2
.           table SALES total        2 rows unloaded
[root@xifenfei dul]# ls -l CHF_SALES_P2_SUB2*
-rw-r--r-- 1 oracle oinstall 429 Jan 19 18:14 CHF_SALES_P2_SUB2.ctl
-rw-r--r-- 1 oracle oinstall 102 Jan 19 18:14 CHF_SALES_P2_SUB2.dat
[root@xifenfei dul]# more CHF_SALES_P2_SUB2.dat
|00003| |01-FEB-2003 AD 00:00:00| |300| |INACTIVE|
|00004| |04-FEB-2003 AD 00:00:00| |300| |INACTIVE|

验证控制文件

[root@xifenfei dul]# ls -l CHF_SALES*
-rw-r--r-- 1 oracle oinstall 421 Jan 19 18:09 CHF_SALES.ctl
-rw-r--r-- 1 oracle oinstall 251 Jan 19 18:09 CHF_SALES.dat
-rw-r--r-- 1 oracle oinstall 424 Jan 19 18:10 CHF_SALES_P1.ctl
-rw-r--r-- 1 oracle oinstall 149 Jan 19 18:10 CHF_SALES_P1.dat
-rw-r--r-- 1 oracle oinstall 429 Jan 19 18:14 CHF_SALES_P2_SUB2.ctl
-rw-r--r-- 1 oracle oinstall 102 Jan 19 18:14 CHF_SALES_P2_SUB2.dat
[root@xifenfei dul]# more CHF_SALES.ctl
load data
CHARACTERSET ZHS16GBK
infile 'CHF_SALES.dat'
insert
into table "CHF"."SALES"
fields terminated by whitespace
(
  "PRODUCT_ID"                       CHAR(5) enclosed by X'7C'
 ,"SALES_DATE"                       DATE "DD-MON-YYYY AD HH24:MI:SS" enclosed by X'7C'
 ,"SALES_COST"                       CHAR(3) enclosed by X'7C'
 ,"STATUS"                           CHAR(8) enclosed by X'7C'
)
[root@xifenfei dul]# more CHF_SALES_P1.ctl
load data
CHARACTERSET ZHS16GBK
infile 'CHF_SALES_P1.dat'
insert
into table "CHF"."SALES"
fields terminated by whitespace
(
  "PRODUCT_ID"                       CHAR(5) enclosed by X'7C'
 ,"SALES_DATE"                       DATE "DD-MON-YYYY AD HH24:MI:SS" enclosed by X'7C'
 ,"SALES_COST"                       CHAR(3) enclosed by X'7C'
 ,"STATUS"                           CHAR(8) enclosed by X'7C'
)
[root@xifenfei dul]# more CHF_SALES_P2_SUB2.ctl
load data
CHARACTERSET ZHS16GBK
infile 'CHF_SALES_P2_SUB2.dat'
insert
into table "CHF"."SALES"
fields terminated by whitespace
(
  "PRODUCT_ID"                       CHAR(5) enclosed by X'7C'
 ,"SALES_DATE"                       DATE "DD-MON-YYYY AD HH24:MI:SS" enclosed by X'7C'
 ,"SALES_COST"                       CHAR(3) enclosed by X'7C'
 ,"STATUS"                           CHAR(8) enclosed by X'7C'
)

这里证明所有的控制文件中的表结构都是整个表的结构，而不是分区表，在实际处理过程中，可以考虑交换分区来实现

完美解决dul处理clob字段乱码问题

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：完美解决dul处理clob字段乱码问题

使用过dul的人都知道，dul在处理clob字段的时候，经常性出现乱码，而且官方没有提供好的处理方法，通过不断的测试折腾终于在2013年春节解决了2012年困惑在我心中的疑惑，不再因为dul不可以处理clob而觉得odu/aul更加高级。这个算是2013年给自己的第一份ORACLE数据库恢复方面大礼包.
在dul中，虽然提供了LDR_OUTPUT_IN_UTF8选项，让所有的clob变为UTF8，但是在实际测试中，没有成功.这里对于clob字段处理结果对比：
配置init.dul参数

osd_big_endian_flag=false
osd_dba_file_bits=10
osd_c_struct_alignment=32
osd_file_leader_size=1
osd_word_size = 32
feedback = 1000
dc_columns=2000000
dc_tables=10000
dc_objects=1000000
dc_users=400
dc_segments=100000
USE_LOB_FILES =TRUE
control_file = control.dul
LDR_ENCLOSE_CHAR=|
db_block_size=8192
export_mode=FALSE
compatible=11

正常情况下测试clob字段

[oracle@xifenfei dul]$ ./dul
Data UnLoader: 10.2.0.5.20 - Internal Only - on Sat Jan 19 00:19:05 2013
with 64-bit io functions
Copyright (c) 1994 2013 Bernard van Duijnen All rights reserved.
 Strictly Oracle Internal Use Only
DUL> unload table chf.EVT_T_COMMON_LOG;
. unloading (index organized) table     LOB024001aa
DUL: Warning: Recreating file "LOB024001aa.ctl"
       0 rows unloaded
. unloading (index organized) table     LOB024001da
DUL: Warning: Recreating file "LOB024001da.ctl"
       0 rows unloaded
. unloading (index organized) table     LOB0240020a
DUL: Warning: Recreating file "LOB0240020a.ctl"
       0 rows unloaded
. unloading (index organized) table     LOB0240023a
DUL: Warning: Recreating file "LOB0240023a.ctl"
       0 rows unloaded
. unloading (index organized) table     LOB0240026a
DUL: Warning: Recreating file "LOB0240026a.ctl"
       0 rows unloaded
Preparing lob metadata from lob index
Reading LOB024001aa.dat 0 entries loaded and sorted 0 entries
Preparing lob metadata from lob index
Reading LOB024001da.dat 0 entries loaded and sorted 0 entries
Preparing lob metadata from lob index
Reading LOB0240020a.dat 0 entries loaded and sorted 0 entries
Preparing lob metadata from lob index
Reading LOB0240023a.dat 0 entries loaded and sorted 0 entries
Preparing lob metadata from lob index
Reading LOB0240026a.dat 0 entries loaded and sorted 0 entries
. unloading table          EVT_T_COMMON_LOG
DUL: Warning: Recreating file "CHF_EVT_T_COMMON_LOG.ctl"
    1863 rows unloaded
--修改CHF_EVT_T_COMMON_LOG.ctl中的导入表名为TEST.T_TEST
--创建测试表
SQL> create table t_test as
  2   select * from chf.EVT_T_COMMON_LOG where 1=0;
Table created.
--导入数据
[oracle@xifenfei dul]$ sqlldr test/test control=CHF_EVT_T_COMMON_LOG.ctl
SQL*Loader: Release 11.2.0.3.0 - Production on Fri Jan 18 23:50:32 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Commit point reached - logical record count 64
Commit point reached - logical record count 128
…………
Commit point reached - logical record count 1856
Commit point reached - logical record count 1863
--测试数据是否乱码
SQL> desc chf.EVT_T_COMMON_LOG
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(64)
 CASES_OF_STATISTICAL_SITATION                      CLOB
…………
 DEPARTMENT_ID                                      VARCHAR2(40)
 MOTIFY_MAN_ID                                      VARCHAR2(40)
SQL>  select CASES_OF_STATISTICAL_SITATION from t_test where rownum<3;
CASES_OF_STATISTICAL_SITATION
--------------------------------------------------------------------------------
b*kb
     _SfZz

通过试验证明，在dul处理clob字段的时候，很容易出现乱码，因为这里涉及到很多中情况(NLS_LANG,LANG,LDR_OUTPUT_IN_UTF8参数等)，经过了多次试验，均不能成功，这里就是为了给出来一个大概的结论：dul在正常情况下不能完美的处理非英文的clob

修改后lob字段文件属性后测试clob字段

--修改CHF_EVT_T_COMMON_LOG.ctl中的导入表名为TEST.EVT_T_COMMON_LOG
--导入数据
[oracle@xifenfei dul]$ sqlldr test/test control=CHF_EVT_T_COMMON_LOG.ctl
SQL*Loader: Release 11.2.0.3.0 - Production on Fri Jan 18 23:50:32 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Commit point reached - logical record count 64
Commit point reached - logical record count 128
…………
Commit point reached - logical record count 1856
Commit point reached - logical record count 1863
--测试数据是否乱码
SQL> select CASES_OF_STATISTICAL_SITATION from EVT_T_COMMON_LOG where rownum<3;
CASES_OF_STATISTICAL_SITATION
--------------------------------------------------------------------------------
1、案件统计情况截止至交班时间Ｃ时间 0 分），今日立难点问题Ｃ7，国庆北路桂门岭社区丽都花园路口，多次上

测试证明:修改了clob文件的相关属性后，完美实现dul处理clob乱码问题

dul恢复drop表测试

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：dul恢复drop表测试

dul对被drop对象进行恢复，需要提供两个信息
1.被删除表所属表空间(非必须)
2.被删除表结构(必须)
模拟删除表

--创建测试表
SQL> create table t_dul_drop tablespace czum
  2  as
  3  select * from dba_tables;
Table created.
--备份被删除表数据，便于比较和提供测试表结构
SQL> create table t_dul_drop_bak tablespace users
  2  as select * from t_dul_drop;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> select count(*) from t_dul_drop;
  COUNT(*)
----------
      1785
SQL> drop table chf.t_dul_drop purge;
Table dropped.
SQL> alter system checkpoint;
System altered.

使用logminer找到data_object_id

delete from "SYS"."OBJ$" where "OBJ#" = '68474' and "DATAOBJ#" = '68474'
and "OWNER#" = '61' and "NAME" = 'T_DUL_DROP' and "NAMESPACE" = '1' and
"SUBNAME" IS NULL and "TYPE#" = '2' and "CTIME" = TO_DATE('04-FEB-13', 'DD-MON-RR')
and "MTIME" = TO_DATE('04-FEB-13', 'DD-MON-RR') and "STIME" = TO_DATE('04-FEB-13', 'DD-MON-RR')
and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0'
and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3" = '61' and
"SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAASAABAAAPzCAAV';

这里可以知道，被删除表的data_object_id为68474

DUL恢复被删除表

--dul版本
E:\dul10>dul.exe
Data UnLoader 10.2.4.37 - Oracle Internal Only - on Mon Feb 04 23:49:50 2013
with 64-bit io functions
Copyright (c) 1994 2010 Bernard van Duijnen All rights reserved.
 Strictly Oracle Internal use Only
DUL>  ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;
Parameter altered
--扫描所属表空间
DUL> scan tablespace 6;
Scanning tablespace 6, data file 6 ...
  13 segment header and 331 data blocks
  tablespace 6, data file 6: 1279 blocks scanned
Reading EXT.dat 13 entries loaded and sorted 13 entries
Reading SEG.dat 13 entries loaded
Reading COMPATSEG.dat 0 entries loaded
Reading SCANNEDLOBPAGE.dat 0 entries loaded and sorted 0 entries
--scan tables得到需求表(可以核对数据样例)
DUL> scan tables;
UNLOAD TABLE OBJNO68474 ( COL001 VARCHAR2(11), COL002 VARCHAR2(30), COL003 VARCHAR2(6)
        , COL004 VARCHAR2(20), COL005 VARCHAR2(30), COL006 VARCHAR2(5), COL007 NUMBER
        , COL008 NUMBER, COL009 NUMBER, COL010 NUMBER, COL011 NUMBER
        , COL012 NUMBER, COL013 NUMBER, COL014 NUMBER, COL015 CHAR
        , COL016 NUMBER, COL017 NUMBER, COL018 VARCHAR2(3), COL019 VARCHAR2(1)
        , COL020 NUMBER, COL021 NUMBER, COL022 NUMBER, COL023 NUMBER
        , COL024 NUMBER, COL025 NUMBER, COL026 NUMBER, COL027 NUMBER
        , COL028 VARCHAR2(10), COL029 VARCHAR2(10), COL030 VARCHAR2(5), COL031 VARCHAR2(7)
        , COL032 NUMBER, COL033 DATE, COL034 VARCHAR2(3), COL035 VARCHAR2(12)
        , COL036 VARCHAR2(1), COL037 VARCHAR2(1), COL038 VARCHAR2(3), COL039 VARCHAR2(7)
        , COL040 VARCHAR2(7), COL041 VARCHAR2(7), COL042 VARCHAR2(8), COL043 VARCHAR2(3)
        , COL044 VARCHAR2(2), COL045 VARCHAR2(15), COL046 VARCHAR2(8), COL047 VARCHAR2(3)
        , COL048 VARCHAR2(3), COL049 VARCHAR2(8), COL050 VARCHAR2(8), COL051 VARCHAR2(5)
        , COL052 VARCHAR2(2), COL053 VARCHAR2(2), COL054 VARCHAR2(3), COL055 VARCHAR2(7) )
    STORAGE( DATAOBJNO 68474 );
--恢复删除表(业务提供表结构)
DUL> unload table t_dul_drop(
  2  OWNER                              VARCHAR2(30),
  3  TABLE_NAME                         VARCHAR2(30),
  4  TABLESPACE_NAME                                    VARCHAR2(30),
  5  CLUSTER_NAME                                       VARCHAR2(30),
  6  IOT_NAME                                           VARCHAR2(30),
  7  STATUS                                             VARCHAR2(8) ,
  8  PCT_FREE                                           NUMBER      ,
  9  PCT_USED                                           NUMBER      ,
 10  INI_TRANS                                          NUMBER      ,
 11  MAX_TRANS                                          NUMBER      ,
 12  INITIAL_EXTENT                                     NUMBER      ,
 13  NEXT_EXTENT                                        NUMBER      ,
 14  MIN_EXTENTS                                        NUMBER      ,
 15  MAX_EXTENTS                                        NUMBER      ,
 16  PCT_INCREASE                                       NUMBER      ,
 17  FREELISTS                                          NUMBER      ,
 18  FREELIST_GROUPS                                    NUMBER      ,
 19  LOGGING                                            VARCHAR2(3) ,
 20  BACKED_UP                                          VARCHAR2(1) ,
 21  NUM_ROWS                                           NUMBER      ,
 22  BLOCKS                                             NUMBER      ,
 23  EMPTY_BLOCKS                                       NUMBER      ,
 24  AVG_SPACE                                          NUMBER      ,
 25  CHAIN_CNT                                          NUMBER      ,
 26  AVG_ROW_LEN                                        NUMBER      ,
 27  AVG_SPACE_FREELIST_BLOCKS                          NUMBER      ,
 28  NUM_FREELIST_BLOCKS                                NUMBER      ,
 29  DEGREE                                             VARCHAR2(20),
 30  INSTANCES                                          VARCHAR2(20),
 31  CACHE                                              VARCHAR2(10),
 32  TABLE_LOCK                                         VARCHAR2(8) ,
 33  SAMPLE_SIZE                                        NUMBER      ,
 34  LAST_ANALYZED                                      DATE        ,
 35  PARTITIONED                                        VARCHAR2(3) ,
 36  IOT_TYPE                                           VARCHAR2(12),
 37  TEMPORARY                                          VARCHAR2(1) ,
 38  SECONDARY                                          VARCHAR2(1) ,
 39  NESTED                                             VARCHAR2(3) ,
 40  BUFFER_POOL                                        VARCHAR2(7) ,
 41  FLASH_CACHE                                        VARCHAR2(7) ,
 42  CELL_FLASH_CACHE                                   VARCHAR2(7) ,
 43  ROW_MOVEMENT                                       VARCHAR2(8) ,
 44  GLOBAL_STATS                                       VARCHAR2(3) ,
 45  USER_STATS                                         VARCHAR2(3) ,
 46  DURATION                                           VARCHAR2(15),
 47  SKIP_CORRUPT                                       VARCHAR2(8) ,
 48  MONITORING                                         VARCHAR2(3) ,
 49  CLUSTER_OWNER                                      VARCHAR2(30),
 50  DEPENDENCIES                                       VARCHAR2(8) ,
 51  COMPRESSION                                        VARCHAR2(8) ,
 52  COMPRESS_FOR                                       VARCHAR2(12),
 53  DROPPED                                            VARCHAR2(3) ,
 54  READ_ONLY                                          VARCHAR2(3) ,
 55  SEGMENT_CREATED                                    VARCHAR2(3) ,
 56  RESULT_CACHE                                       VARCHAR2(7))
 57   STORAGE( DATAOBJNO 68474 );
. unloading table                T_DUL_DROP
DUL: Warning: Recreating file "T_DUL_DROP.ctl"
    1785 rows unloaded

模拟业务规则提供，创建表

SQL> create table t_dul_drop as select * from t_dul_drop_bak where 1=0;
Table created.

导入数据

e:\dul10>sqlldr chf/xifenfei control=T_DUL_DROP.ctl
SQL*Loader: Release 11.2.0.3.0 - Production on Mon Feb 4 23:35:57 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
Commit point reached - logical record count 448
Commit point reached - logical record count 512
Commit point reached - logical record count 576
Commit point reached - logical record count 640
Commit point reached - logical record count 704
Commit point reached - logical record count 768
Commit point reached - logical record count 832
Commit point reached - logical record count 896
Commit point reached - logical record count 960
Commit point reached - logical record count 1024
Commit point reached - logical record count 1088
Commit point reached - logical record count 1152
Commit point reached - logical record count 1216
Commit point reached - logical record count 1280
Commit point reached - logical record count 1344
Commit point reached - logical record count 1408
Commit point reached - logical record count 1472
Commit point reached - logical record count 1536
Commit point reached - logical record count 1600
Commit point reached - logical record count 1664
Commit point reached - logical record count 1728
Commit point reached - logical record count 1785

恢复数据结果

SQL> select count(*) from t_dul_drop;
  COUNT(*)
----------
      1785
SQL> select owner,table_name from t_dul_drop where rownum<10;
OWNER                          TABLE_NAME
------------------------------ ------------------------------
SYS                            IDL_CHAR$
SYS                            IDL_UB2$
SYS                            IDL_SB4$
SYS                            ERROR$
SYS                            SETTINGS$
SYS                            NCOMP_DLL$
SYS                            PROCEDUREJAVA$
SYS                            PROCEDUREC$
SYS                            PROCEDUREPLSQL$
9 rows selected.

dul恢复truncate表测试

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：dul恢复truncate表测试

dul 恢复truncate 测试
准备dul测试

SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     67854
SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)
SQL> select object_id,data_object_id from user_objects where object_name='T_XIFENFEI';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     68332          68332
SQL> TRUNCATE TABLE T_XIFENFEI;
Table truncated.
SQL> ALTER SYSTEM CHECKPOINT;
System altered.
SQL> select object_id,data_object_id from user_objects where object_name='T_XIFE
NFEI';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     68332          68468
SQL> SELECT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE SEGMENT_NAME='T_XIFENFEI';
TABLESPACE_NAME
------------------------------
USERS
SQL> SELECT FILE_ID FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='USERS';
   FILE_ID
----------
         4
SQL> SELECT file#,RFILE#,NAME FROM V$DATAFILE;
     FILE#     RFILE# NAME
---------- ---------- --------------------------------------------------
         1          1 E:\ORACLE\ORADATA\XIFENFEI\SYSTEM01.DBF
         2          2 E:\ORACLE\ORADATA\XIFENFEI\SYSAUX01.DBF
         3          3 E:\ORACLE\ORADATA\XIFENFEI\UNDOTBS01.DBF
         4          4 E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF
         5          5 E:\ORACLE\ORADATA\XIFENFEI\UNDO01.DBF
         6          6 E:\ORACLE\ORADATA\XIFENFEI\CZUM01.DBF
6 rows selected.

备注说明：因为我们为了测试，所以直接查询出来了data_object_id，在实际的恢复中，我们需要使用logminer来找出来历史dataobj#

dul恢复truncate 表

e:\dul10>dul.exe
Data UnLoader 10.2.4.37 - Oracle Internal Only - on Mon Feb 04 00:20:08 2013
with 64-bit io functions
Copyright (c) 1994 2010 Bernard van Duijnen All rights reserved.
 Strictly Oracle Internal use Only
DUL: Warning: Recreating file "dul.log"
Reading USER.dat 65 entries loaded
Reading OBJ.dat 67944 entries loaded and sorted 67944 entries
Reading SCANNEDLOBPAGE.dat 8 entries loaded and sorted 8 entries
Reading TAB.dat 1869 entries loaded
Reading COL.dat 77409 entries loaded and sorted 77409 entries
Reading SEG.dat 23 entries loaded
Reading EXT.dat 54 entries loaded and sorted 54 entries
Reading TABPART.dat 110 entries loaded and sorted 110 entries
Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
Reading INDPART.dat 127 entries loaded and sorted 127 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 3729 entries loaded
Reading LOB.dat
DUL: Warning: Increased the size of DC_LOBS from 1024 to 8192 entries
 1109 entries loaded
Reading ICOL.dat 4868 entries loaded
Reading COLTYPE.dat 2823 entries loaded
Reading TYPE.dat 2082 entries loaded
Reading ATTRIBUTE.dat 8736 entries loaded
Reading COLLECTION.dat 591 entries loaded
Reading COMPATSEG.dat 0 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 24 entries loaded
Reading TS.dat 7 entries loaded
Reading PROPS.dat 36 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16
Found db_id = 1422012639
Found db_name = XIFENFEI
DUL> SCAN DATAFILE 4;
DUL: Warning: Recreating file "EXT.dat"
DUL: Warning: Recreating file "SEG.dat"
DUL: Warning: Recreating file "COMPATSEG.dat"
DUL: Warning: Recreating file "SCANNEDLOBPAGE.dat"
Scanning tablespace 4, data file 4 ...
  23 segment header and 1601 data blocks
  tablespace 4, data file 4: 2079 blocks scanned
Reading EXT.dat 54 entries loaded and sorted 54 entries
Reading SEG.dat 23 entries loaded
Reading COMPATSEG.dat 0 entries loaded
Reading SCANNEDLOBPAGE.dat 8 entries loaded and sorted 8 entries
DUL>  ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;
Parameter altered
DUL> scan extents;
DUL: Warning: Recreating file "seen_tab.dat"
DUL: Warning: Recreating file "seen_col.dat"
Scanning extents without segment header
Scanning extent id (dba 0, obj 68332)
Analyzing segment: data object id 68332
  heap organized table
Col    Seen  Max PCT  PRINT  NUMBERS DATES TIMESTAMP WITH TZ INTRVAL  ROWIDS LOB
 no   count Size NUL 75%100% AnyNice AnyNice AnyNice AnyNice Y2M D2S AnyNice
  1   67854   18   0 100 100   0   0   0   0   0   0   0   0   0   0  44   0   0
…………
 14   67854    2   0   0   0 100 100   0   0   0   0   0   0   0   0   0   0   0
|SYS| |C_FILE#_BLOCK#| || |8| |8| |CLUSTER| |20-JAN-2013 AD 17:13:18| |20-JAN-20
…………
:18| |2013-01-20:17:13:18| |VALID| |N| |N| |N| |1|
UNLOAD TABLE OBJNO68332 ( COL001 VARCHAR2(18), COL002 VARCHAR2(30), COL003 VARCH
AR2(27)
        , COL004 NUMBER, COL005 NUMBER, COL006 VARCHAR2(18), COL007 DATE
        , COL008 DATE, COL009 VARCHAR2(19), COL010 VARCHAR2(7), COL011 VARCHAR2(
1)
        , COL012 VARCHAR2(1), COL013 VARCHAR2(1), COL014 NUMBER )
    STORAGE( DATAOBJNO 68332 );
--自己拼接语句
DUL> UNLOAD TABLE t_xifenfei_1(OWNER VARCHAR2(30),OBJECT_NAME  VARCHAR2(128),SUB
OBJECT_NAME    VARCHAR2(30),OBJECT_ID  NUMBER,DATA_OBJECT_ID    NUMBER,OBJECT_TY
PE  VARCHAR2(19),CREATED    DATE,LAST_DDL_TIME DATE,TIMESTAMP    VARCHAR2(19),ST
ATUS VARCHAR2(7),TEMPORARY    VARCHAR2(1),GENERATED    VARCHAR2(1),SECONDARY
VARCHAR2(1),NAMESPACE    NUMBER,EDITION_NAME VARCHAR2(30))  STORAGE( DATAOBJNO 6
8332 );
. unloading table              T_XIFENFEI_1
DUL: Warning: Recreating file "T_XIFENFEI_1.ctl"
   67854 rows unloaded

补充说明：
1.在最新的dul 10.2.0.5.20中，不支持scan extents命令
2.在最新的dul 10.2.0.5.20中，挖出来的无数据字典数据，字符串转换为16进制值

密码保护：dul扫描磁盘抢救数据

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：密码保护：dul扫描磁盘抢救数据

bbed打开丢失部分system数据文件库

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：bbed打开丢失部分system数据文件库

在某种情况下，数据库system表空间可能有多个数据文件，而意外的丢失了其中某个(不能为第一个),然后通过bbed来模拟一个数据文件来open库
system增加数据文件

SQL> alter tablespace system add datafile '/u01/oracle/oradata/ora11g/system02.dbf' size 10m;
Tablespace altered.
--创建表，为了使得数据库发生类此生产环境的部分操作，使得system表空间可能发生改变
SQL> create table t_xifenfei tablespace system
  2  as
  3  select * from dba_tables;
Table created.

删除system中某个文件(system02.dbf)

[oracle@xifenfei ora11g]$ mv system02.dbf system02.dbf_bak

尝试启动数据库

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  313860096 bytes
Fixed Size                  1344652 bytes
Variable Size             251661172 bytes
Database Buffers           54525952 bytes
Redo Buffers                6328320 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u01/oracle/oradata/ora11g/system02.dbf'

错误思路offline system数据文件

SQL> alter database datafile 8 offline;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 8 is offline
ORA-01110: data file 8: '/u01/oracle/oradata/ora11g/system02.dbf'
SQL> alter database datafile 8 online;
alter database datafile 8 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u01/oracle/oradata/ora11g/system02.dbf'

使用system表空间其他数据文件来模拟丢失数据文件

[oracle@xifenfei ora11g]$ cp system01.dbf system02.dbf

通过dul获取file$相关信息

FILE#
RELFILE#
CRSCNWRP
CRSCNBAS

bbed修改下面参数值

--rdba
 ub4 rdba_kcbh                         @4        0x02000001
--绝对文件号
 ub2 kccfhfno                          @52       0x0008
--scn
 ub4 kscnbas                           @100      0xc01a3581
 ub2 kscnwrp                           @104      0x0b2c
--相对文件号
 ub4 kcvfhrfn                             @368      0x00000008
--文件大小(不修改,为了重建欺骗数据库重建控制文件)
kccfhfsz
--文件创建时间(重建控制文件来实现控制文件和数据文件头一致)
kcvfhcrt

重建控制文件
1.因为复制来自同一个表空间下面的数据文件，数据文件大小和原数据文件一样, 所以不要修改kccfhfsz大小，不然会出现

CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS       ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01200: actual file size of 90880 is smaller than correct size of 10485760 blocks
ORA-01110: data file 8: '/u01/oracle/oradata/ora11g/system02.dbf'

2. 数据文件创建时间是通过kcvfhcrt参数值来控制的，而这个值是通过1988年01月01日00时00分00秒开始计时，按照每月31天计算的累计值，按照这个规则可以推断出来kcvfhcrt.因为数据库在启动的时候会验证控制文件中这个值和数据文件头的该值是否一致，所以如果你不修改kcvfhcrt,可以选择重建控制文件来完成.

再次open数据库

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.

操作到这里,库已经可以正常的被open,如果通过这种方面屏蔽掉的异常的system数据文件中数据字典的部分表信息时,可能数据库依然不能被正常逻辑导出(例如dba_segments,dba_extents的基表等),需要进一步特殊处理，如果不能自行解决相关问题,需要恢复支持,请联系我们
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

密码保护：dul抽取异常asm文件

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：密码保护：dul抽取异常asm文件

分类目录归档：非常规恢复