Category Archives: Oracle备份恢复

table中各种类型block坏块是否能被跳过

Posted on by

在table遇到的各种坏块中，大部分情况，我们都可以通过设置event 10231或者dbms_repair来跳过坏块，抢救其他数据；但是在部分情况下，我们设置了他们依然不能跳过坏块，数据库依然报ORA-01578,本文测试了table中各种类型的block，证明在哪些blog出现异常之后不能被跳过.
如果是事务数据块出现坏块可以通过dbms_repair.skip_corrupt_blocks来标记坏块(也可以使用event 10231)来实现；对于BITMAP BLOCK如果出现坏块不会对于读取数据无影响(至于其他操作,请见后续blog)；SEGMENT HEADER如果出现坏块，无法通过跳过坏块来实现获取其他数据(正常block)
创建测试表

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create table t_xifenfei 
  2  tablespace users 
  3  as
  4  select * from dba_objects;

Table created.

SQL>  select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     74663

查询相关block信息

SQL> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,blocks,extents from DBA_SEGMENTS 
  2  WHERE OWNER='CHF' AND SEGMENT_NAME='T_XIFENFEI';

SEGMENT_NAME    HEADER_FILE HEADER_BLOCK     BLOCKS    EXTENTS
--------------- ----------- ------------ ---------- ----------
T_XIFENFEI                4          378       1152         24

SQL>   select   
  2    dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3    max(dbms_rowid.rowid_block_number(rowid)) max_block,
  4    min(dbms_rowid.rowid_block_number(rowid)) min_block
  5    from chf.t_xifenfei 
  6    group by dbms_rowid.rowid_relative_fno(rowid);

   REL_FNO  MAX_BLOCK  MIN_BLOCK
---------- ---------- ----------
         4       1728        379

SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,blocks from dba_extents where owner='CHF'
   2  AND SEGMENT_NAME='T_XIFENFEI';

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          4        376          8
         1          4        640          8
         2          4        648          8
         3          4        656          8
         4          4        664          8
         5          4        672          8
         6          4        680          8
         7          4        688          8
         8          4        696          8
         9          4        704          8
        10          4        712          8
        11          4        720          8
        12          4        728          8
        13          4        736          8
        14          4        744          8
        15          4        752          8
        16          4        768        128
        17          4        896        128
        18          4       1024        128
        19          4       1152        128
        20          4       1280        128
        21          4       1408        128
        22          4       1536        128
        23          4       1664        128

通过这里可以知道:真正的存储数据是从block 379开始,至于block 376、377、378是什么，使用dump block分析

验证block类型

SQL> alter system dump datafile 4 block 376;

System altered.

SQL> alter system dump datafile 4 block 377;

System altered.

SQL> alter system dump datafile 4 block 378;

System altered.

SQL> alter system dump datafile 4 block 379;

System altered.

--该block是另外extent的开始，所以也尝试分析是否有特殊之处
SQL> alter system dump datafile 4 block 640;

System altered.

--dump 文件header信息
Start dump data blocks tsn: 4 file#:4 minblk 376 maxblk 376
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777592
Block dump from disk:
buffer tsn: 4 rdba: 0x01000178 (4/376)
scn: 0x0b8c.3bfc6517 seq: 0x04 flg: 0x04 tail: 0x65172004
frmt: 0x02 chkval: 0xc8b3 type: 0x20=FIRST LEVEL BITMAP BLOCK


Start dump data blocks tsn: 4 file#:4 minblk 377 maxblk 377
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777593
Block dump from disk:
buffer tsn: 4 rdba: 0x01000179 (4/377)
scn: 0x0b8c.3bfc6517 seq: 0x18 flg: 0x04 tail: 0x65172118
frmt: 0x02 chkval: 0x9e8c type: 0x21=SECOND LEVEL BITMAP BLOCK


Start dump data blocks tsn: 4 file#:4 minblk 378 maxblk 378
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777594
BH (0x2a7f7f0c) file#: 4 rdba: 0x0100017a (4/378) class: 4 ba: 0x2a742000
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
  dbwrid: 0 obj: 76372 objn: 76372 tsn: 4 afn: 4 hint: f
  hash: [0x3150a748,0x3150a748] lru: [0x2a7f8094,0x2a7f7ee4]
  lru-flags: hot_buffer
  ckptq: [NULL] fileq: [NULL] objq: [0x2f72dc34,0x2f72dc34] objaq: [0x2f72dc2c,0x2f72dc2c]
  st: XCURRENT md: NULL fpin: 'ktewh25: kteinicnt' tch: 1
  flags:
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
Block dump from disk:
buffer tsn: 4 rdba: 0x0100017a (4/378)
scn: 0x0b8c.3bfc651b seq: 0x01 flg: 0x04 tail: 0x651b2301
frmt: 0x02 chkval: 0xb2ae type: 0x23=PAGETABLE SEGMENT HEADER

Start dump data blocks tsn: 4 file#:4 minblk 379 maxblk 379
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777595
Block dump from disk:
buffer tsn: 4 rdba: 0x0100017b (4/379)
scn: 0x0b8c.3bfc6494 seq: 0x01 flg: 0x04 tail: 0x64940601
frmt: 0x02 chkval: 0x0567 type: 0x06=trans data

Start dump data blocks tsn: 4 file#:4 minblk 640 maxblk 640
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777856
Block dump from disk:
buffer tsn: 4 rdba: 0x01000280 (4/640)
scn: 0x0b8c.3bfc6496 seq: 0x01 flg: 0x04 tail: 0x64960601
frmt: 0x02 chkval: 0x0efe type: 0x06=trans data

这里可以知道：
1.block 376、377为BITMAP BLOCK
2.block 378为SEGMENT HEADER(和dba_segments视图中一致)
3.除extent 0中有特殊(含BITMAP BLOCK和SEGMENT HEADER)block，其他extent只包含事务数据

测试block 640

--block 640包含条数
SQL> select   count(rowid)
  2    from chf.t_xifenfei 
  3     where dbms_rowid.rowid_block_number(rowid)=640
  4  and dbms_rowid.rowid_relative_fno(rowid)=4;

COUNT(ROWID)
------------
          79

--bbed修改tailchk
BBED> set filename '/u01/oracle/oradata/ora11g/users01.dbf'
        FILENAME        /u01/oracle/oradata/ora11g/users01.dbf

BBED> set block 640
        BLOCK#          640

BBED> set mode edit
        MODE            Edit

BBED> p tailchk
ub4 tailchk                                 @8188     0x64960601

BBED> m /x 64960602
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 640              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 64960602 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 640:
current = 0xf80b, required = 0xf80b

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 640

Block 640 is corrupt
Corrupt block relative dba: 0x01000280 (file 0, block 640)
Fractured block found during verification
Data in bad block:
 type: 6 format: 2 rdba: 0x01000280
 last change scn: 0x0b8c.3bfc6496 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x02069664
 check value in block header: 0xf80b
 computed block checksum: 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
Message 531 not found;  product=RDBMS; facility=BBED

--查询坏块
SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 640)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'

--跳过坏块
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');

PL/SQL procedure successfully completed.

SQL>  select skip_corrupt from dba_tables where table_name='T_XIFENFEI' AND OWNER='CHF';

SKIP_COR
--------
ENABLED

SQL>  select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     74584

--修复坏块
BBED> m /x 01069664
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 640              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 01069664 

 <32 bytes per line>

BBED> p tailchk
ub4 tailchk                                 @8188     0x64960601

BBED> sum apply
Check value for File 0, Block 640:
current = 0x0efe, required = 0x0efe

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 640


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
Message 531 not found;  product=RDBMS; facility=BBED

--除掉标记表坏块
SQL> BEGIN
  2  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
  3  SCHEMA_NAME => 'CHF',
  4  OBJECT_NAME => 'T_XIFENFEI',
  5  OBJECT_TYPE => dbms_repair.table_object,
  6  FLAGS => dbms_repair.NOSKIP_FLAG);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> select skip_corrupt from dba_tables where table_name='T_XIFENFEI' AND OWNER='CHF';

SKIP_COR
--------
DISABLED

--查询表记录正常
SQL>  select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     74663

在后续的操作中，也是按照类似步骤操作，考虑到篇幅有限，部分过程不再贴出来

测试block 379

SQL>  select count(*) from chf.t_xifenfei;
 select count(*) from chf.t_xifenfei
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 379)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'


SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');

PL/SQL procedure successfully completed.

SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     74575

测试block 378

BBED> set block 378
        BLOCK#          378

--segment header 不支持bbed查看结构
BBED> p tailchk
BBED-00400: invalid blocktype (35)

BBED> map
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 378                                   Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (35)


BBED> set offset 8188
        OFFSET          8188

BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 378              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 01231b65 

 <32 bytes per line>

BBED> m /x 651b2302
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 378              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 651b2302 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 378:
current = 0xedf2, required = 0xedf2

--验证坏块
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 378

Block 378 is corrupt
Corrupt block relative dba: 0x0100017a (file 0, block 378)
Fractured block found during verification
Data in bad block:
 type: 35 format: 2 rdba: 0x0100017a
 last change scn: 0x0b8c.3bfc651b seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x02231b65
 check value in block header: 0xedf2
 computed block checksum: 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
Message 531 not found;  product=RDBMS; facility=BBED


SQL>  select count(*) from chf.t_xifenfei;
 select count(*) from chf.t_xifenfei
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 378)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'

--标记跳过坏块
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');

PL/SQL procedure successfully completed.

--查询依然失败
SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 378)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'

测试block 377

BBED> m /x 18211766
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 377              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 18211766 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 377:
current = 0x9d8c, required = 0x9d8c

--bbed验证为坏块
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 377

Block 377 is corrupt
Corrupt block relative dba: 0x01000179 (file 0, block 377)
Fractured block found during verification
Data in bad block:
 type: 33 format: 2 rdba: 0x01000179
 last change scn: 0x0b8c.3bfc6517 seq: 0x18 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x66172118
 check value in block header: 0x9d8c
 computed block checksum: 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
Message 531 not found;  product=RDBMS; facility=BBED

--dbv验证为坏块
[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/ora11g/users01.dbf

DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jan 18 03:32:18 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/users01.dbf
Page 377 is influx - most likely media corrupt
Corrupt block relative dba: 0x01000179 (file 4, block 377)
Fractured block found during dbv: 
Data in bad block:
 type: 33 format: 2 rdba: 0x01000179
 last change scn: 0x0b8c.3bfc6517 seq: 0x18 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x66172118
 check value in block header: 0x9d8c
 computed block checksum: 0x0



DBVERIFY - Verification complete

Total Pages Examined         : 2560
Total Pages Processed (Data) : 1434
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 10
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 213
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 902
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 1
Total Pages Encrypted        : 0
Highest block SCN            : 1006486374 (2956.1006486374)

--查询表记录
SQL> select skip_corrupt from dba_tables where table_name='T_XIFENFEI' AND OWNER='CHF';

SKIP_COR
--------
DISABLED

SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     74663

测试block 376

BBED> m /x 04201766
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 376              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 04201766 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 376:
current = 0xcbb3, required = 0xcbb3

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 376

Block 376 is corrupt
Corrupt block relative dba: 0x01000178 (file 0, block 376)
Fractured block found during verification
Data in bad block:
 type: 32 format: 2 rdba: 0x01000178
 last change scn: 0x0b8c.3bfc6517 seq: 0x4 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x66172004
 check value in block header: 0xcbb3
 computed block checksum: 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
Message 531 not found;  product=RDBMS; facility=BBED

SQL>  select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     74663

通过测试证明，如果是事务数据块出现坏块可以通过dbms_repair.skip_corrupt_blocks来标记坏块(也可以使用event 10231)来实现；对于BITMAP BLOCK如果出现坏块不会对于读取数据无影响(至于其他操作,请见table中各种坏块对select/dml操作影响)；SEGMENT HEADER如果出现坏块，无法通过跳过坏块来实现获取其他数据(正常block)

ORACLE 12C备份与恢复测试

Posted on by

12C引进了pdb的概念,使得rman的恢复相对来说复杂了一些,这里对pdb的常规备份和恢复进行了简单测试,供大家参考
cdb启动和pdb关系测试

[oracle@xifenfei tmp]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.0.2 Beta on Wed Dec 12 23:48:02 2012

Copyright (c) 1982, 2012, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit     
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> startup
ORACLE instance started.

Total System Global Area  939495424 bytes
Fixed Size                  2267128 bytes
Variable Size             662702088 bytes
Database Buffers          268435456 bytes
Redo Buffers                6090752 bytes
Database mounted.
Database opened.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4043918109 PDB$SEED                       READ ONLY
         3 2346805300 LX1                            MOUNTED
         4 2385557792 LX2                            MOUNTED
         5 1565384817 FF                             MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4043918109 PDB$SEED                       READ ONLY
         3 2346805300 LX1                            READ WRITE
         4 2385557792 LX2                            READ WRITE
         5 1565384817 FF                             READ WRITE

证明直接startup cdb里面的pdb不会自动open,需要手工进行open

rman使用cdb备份数据库

[oracle@xifenfei ~]$ rman target /

Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 21:36:08 2012

Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.

connected to target database: xifenfei (DBID=2412861330)

RMAN> backup filesperset = 5 as compressed backupset database format '/tmp/full_db_%U';

Starting backup at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/xifenfei/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/xifenfei/LX2/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/xifenfei/LX2/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_06nsn3uq_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00019 name=/u01/app/oracle/oradata/xifenfei/xffexample01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/xifenfei/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
input datafile file number=00018 name=/u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_07nsn407_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/xifenfei/pdbseed/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/xifenfei/pdbseed/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_08nsn41l_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/xifenfei/sysaux01.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/xifenfei/users01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/xifenfei/LX2/LX2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_09nsn440_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_0ansn45d_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-DEC-12

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213250
2       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626
3       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626
4       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626
5       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626
6       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626

RMAN> report schema;

Report of database schema for database with db_unique_name xifenfei

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               ***     /u01/app/oracle/oradata/xifenfei/system01.dbf
2    210      PDB$SEED:SYSTEM      ***     /u01/app/oracle/oradata/xifenfei/pdbseed/system01.dbf
3    550      SYSAUX               ***     /u01/app/oracle/oradata/xifenfei/sysaux01.dbf
4    165      PDB$SEED:SYSAUX      ***     /u01/app/oracle/oradata/xifenfei/pdbseed/sysaux01.dbf
5    310      UNDOTBS1             ***     /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
6    5        USERS                ***     /u01/app/oracle/oradata/xifenfei/users01.dbf
7    210      LX1:SYSTEM           ***     /u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
8    165      LX1:SYSAUX           ***     /u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
9    5        LX1:USERS            ***     /u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf
10   210      LX2:SYSTEM           ***     /u01/app/oracle/oradata/xifenfei/LX2/system01.dbf
11   165      LX2:SYSAUX           ***     /u01/app/oracle/oradata/xifenfei/LX2/sysaux01.dbf
12   5        LX2:USERS            ***     /u01/app/oracle/oradata/xifenfei/LX2/LX2_users01.dbf
16   270      FF:SYSTEM            ***     /u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
17   570      FF:SYSAUX            ***     /u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
18   5        FF:USERS             ***     /u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
19   341      FF:EXAMPLE           ***     /u01/app/oracle/oradata/xifenfei/xffexample01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    530      TEMP                 32767       /u01/app/oracle/oradata/xifenfei/temp01.dbf
2    20       PDB$SEED:TEMP        32767       /u01/app/oracle/oradata/xifenfei/pdbseed/temp01.dbf
3    20       LX1:TEMP             32767       /u01/app/oracle/oradata/xifenfei/LX1/temp01.dbf
4    20       LX2:TEMP             32767       /u01/app/oracle/oradata/xifenfei/LX2/temp01.dbf
5    20       FF:TEMP              32767       /u01/app/oracle/oradata/xifenfei/xfftemp01.dbf

试验证明:通过rman通过cdb库的备份,可以实现对对应的cdb和所包含的pdb进行备份

配置pdb访问tns

[oracle@xifenfei ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.0.2        on 12-DEC-2012 22:33:27

Copyright (c) 1991, 2012, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.0.2       
Start Date                12-DEC-2012 22:31:55
Uptime                    0 days 0 hr. 1 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/xifenfei/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=5500))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ff" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xifenfei" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "lx1" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "lx2" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xffXDB" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@xifenfei admin]$ vi tnsnames.ora 
lx1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lx1)
    )
  )
ff =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ff)
    )
  )

sqlplus访问pdb

[oracle@xifenfei admin]$ sqlplus sys@lx1 as sysdba

SQL*Plus: Release 12.1.0.0.2 Beta on Wed Dec 12 22:35:07 2012

Copyright (c) 1982, 2012, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit     
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show con_name;

CON_NAME
------------------------------
LX1

SQL> create user xff identified by xifenfei;

User created.

SQL> GRANT SYSDBA TO XFF;

Grant succeeded.

rman备份pdb数据库

[oracle@xifenfei admin]$ rman target xff/xifenfei@lx1

Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 22:44:46 2012

Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.

connected to target database: xifenfei (DBID=2412861330)

RMAN> backup filesperset = 5 as compressed backupset database format '/tmp/lx1_db_%U';

Starting backup at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=256 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/lx1_db_0bnsn80f_1_1 tag=TAG20121212T224534 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 12-DEC-12

RMAN> report schema;

Report of database schema for database with db_unique_name xifenfei

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
7    210      LX1:SYSTEM           ***     /u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
8    165      LX1:SYSAUX           ***     /u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
9    5        LX1:USERS            ***     /u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
3    20       LX1:TEMP             32767       /u01/app/oracle/oradata/xifenfei/LX1/temp01.dbf

rman通过cdb备份pdb

[oracle@xifenfei admin]$ rman target /

Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 23:02:07 2012

Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.

connected to target database: xifenfei (DBID=2412861330)

RMAN> backup filesperset = 5 as compressed backupset pluggable database FF format '/tmp/ff_db_%U';

Starting backup at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=262 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
input datafile file number=00019 name=/u01/app/oracle/oradata/xifenfei/xffexample01.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
input datafile file number=00018 name=/u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/ff_db_0cnsn8vm_1_1 tag=TAG20121212T230214 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
Finished backup at 12-DEC-12

模拟pdb库全库恢复

SQL> conn xff/xifenfei@lx1 as sysdba
Connected.

SQL> create table t_xifenfei as select * from dba_objects;

Table created.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     19121

--在pdb中不能切换日志(因为日志是全局的)
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

SQL> shutdown immediate;
Pluggable Database closed.

--删除数据文件
[oracle@xifenfei admin]$ rm /u01/app/oracle/oradata/xifenfei/LX1/*

--rman基于cdb恢复pdb库
[oracle@xifenfei ~]$ rman target /

Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 23:11:22 2012

Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.

connected to target database: xifenfei (DBID=2412861330)

RMAN> restore pluggable database lx1;

Starting restore at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/lx1_db_0bnsn80f_1_1
channel ORA_DISK_1: piece handle=/tmp/lx1_db_0bnsn80f_1_1 tag=TAG20121212T224534
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 12-DEC-12

RMAN> recover pluggable database lx1;

Starting recover at 12-DEC-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 12-DEC-12

RMAN> alter pluggable database lx1 open;

Statement processed

--验证恢复结果
SQL> conn xff/xifenfei@lx1 as sysdba
Connected.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     19121

试验证明:对于pdb库的备份,使用rman可以在cdb级别进行还原和恢复

模拟数据文件恢复

SQL> create table t_xifenfei tablespace example
  2   as
  3    select * from dba_objects;

Table created.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     89604

SQL> col name for a60 
SQL> set lines 134
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------
         5 /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
        16 /u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
        17 /u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
        18 /u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
        19 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf

--离线含测试数据的数据文件
SQL> alter database datafile 19 offline;

Database altered.

--删除数据文件
SQL> !rm /u01/app/oracle/oradata/xifenfei/xffexample01.dbf

SQL> !ls -l /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
ls: /u01/app/oracle/oradata/xifenfei/xffexample01.dbf: No such file or directory


--尝试pdb级别恢复
[oracle@xifenfei ~]$ rman target sys/xifenfei@ff

Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 23:29:03 2012

Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.

connected to target database: xifenfei (DBID=2412861330)

RMAN> restore datafile 19;

Starting restore at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/12/2012 23:29:15
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 19 found to restore

--pdb级别不能识别对应数据文件(一种可能是我在cdb级别备份FF库,另一种可能bug)
RMAN> list backup of datafile 19;

specification does not match any backup in the repository

--在cdb级别还原
[oracle@xifenfei tmp]$ rman target /

Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 23:44:21 2012

Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.

connected to target database: xifenfei (DBID=2412861330)

RMAN> list backup of datafile 19;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    76.81M     DISK        00:00:44     12-DEC-12      
        BP Key: 3   Status: AVAILABLE  Compressed: YES  Tag: TAG20121212T213626
        Piece Name: /tmp/full_db_07nsn407_1_1
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  19      Full 1860043    12-DEC-12 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    189.52M    DISK        00:01:25     12-DEC-12      
        BP Key: 8   Status: AVAILABLE  Compressed: YES  Tag: TAG20121212T230214
        Piece Name: /tmp/ff_db_0cnsn8vm_1_1
  List of Datafiles in backup set 8
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  19      Full 1860043    12-DEC-12 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf

RMAN> restore datafile 19;

Starting restore at 12-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00019 to /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/ff_db_0cnsn8vm_1_1
channel ORA_DISK_1: piece handle=/tmp/ff_db_0cnsn8vm_1_1 tag=TAG20121212T230214
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 12-DEC-12

--cdb级别恢复数据文件
RMAN> recover datafile 19;

Starting recover at 12-DEC-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 12-DEC-12

--cdb级别不能直接online
RMAN> alter database datafile 19 online;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 12/12/2012 23:46:15
ORA-01516: nonexistent log file, data file, or temporary file "19"

RMAN> alter pluggable database ff datafile 19 online;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 12/12/2012 23:47:53
ORA-65046: operation not allowed from outside a pluggable database

--进入pdb库进行online
SQL> alter database datafile 19 online;               

Database altered.

--验证数据
SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     89604

试验证明:rman通过cdb级别操作,还是一步步恢复了pdb中离线异常的数据文件

总结说明
1.rman可以比较好的操作cdb和pdb备份
2.pdb的备份和恢复可以通过cdb来完成

使用dbms_pumpdata执行expdp操作

Posted on by

使用dbms_pumpdata执行expdp操作

set serverout on 
declare 
  h1 number; -- Datapump handle 
  dir_name varchar2(30); -- Directory Name 
  job_status VARCHAR2(30); 
begin 
  dir_name := 'DATA_PUMP_DIR'; 
  h1 := dbms_datapump.open(
  operation =>'EXPORT', --是export还是impport
--导出表配置
  job_mode =>'TABLE',  --job_mode可以为SCHEMA/TABLE等
--导出用户配置
  job_mode =>'SCHEMA',
  remote_link => NULL, --是否使用dblink导出(就是NETWORK_LINK）
  job_name =>'TABLE_XFF' --job_name expdpjob的名称
  ); 

  dbms_datapump.add_file(handle =>h1, 
                         filename => 'XIFENFEI.DMP', 
                         directory => dir_name, 
                         filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE, 
                         reusefile => 1); -- value of 1 instructs to overwrite existing file 

  dbms_datapump.add_file(handle =>h1, 
                         filename => 'XIFENFEI.LOG', 
                         directory => dir_name, 
                         filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE, 
                         reusefile => 1); 

--导出表配置
  dbms_datapump.metadata_filter(handle =>h1, 
                         name => 'TABLE_FILTER', 
                         value => 'CHF.T_XIFENFEI'); 

--导出用户配置
  dbms_datapump.metadata_filter (handle => dp_handle, 
                                 name => 'SCHEMA_EXPR', 
                                 value => 'IN (''CHF'')'); 
-- Start the job. 
  dbms_datapump.start_job(h1); 
  dbms_datapump.wait_for_job (handle => dp_handle, 
                              job_state => job_status); 
  dbms_output.put_line ('DataPump Export - '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS')||' Status '||job_status); 
  begin 
     dbms_datapump.detach(handle => h1); 
   end; 
end; 
/

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

Posted on by

在某种情况下，数据库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的基表等),所以遇到这样的情况,在不确定异常的system中包含的内容之时,还是建议直接使用dul或者第三方工具来抽取数据.

recover遇到坏块处理本质探讨

Posted on by

如果在还原出来的数据文件中有坏块,而归档日志和联机日志是正常的,那么在应用日志恢复过程中,会出现什么情况,这里通过一个简单的测试给予其中一种情况的说明
创建测试表

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create table t_xifenfei(object_id,object_name) tablespace xifenfei
  2  as
  3  select object_id,object_name from dba_objects      
  4  where rownum<11;

Table created.

SQL> col object_name for a30
SQL> select   object_id,object_name,
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno
  4   from chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME                       REL_FNO    BLOCKNO
---------- ------------------------------ ---------- ----------
        20 ICOL$                                   5         12
        44 I_USER1                                 5         12
        28 CON$                                    5         12
        15 UNDO$                                   5         12
        29 C_COBJ#                                 5         12
         3 I_OBJ#                                  5         12
        25 PROXY_ROLE_DATA$                        5         12
        39 I_IND1                                  5         12
        51 I_CDEF2                                 5         12
        26 I_PROXY_ROLE_DATA$_1                    5         12

10 rows selected.

SQL> select name from v$datafile where file#=5;

NAME
--------------------------------------------------------------
/u01/oracle/oradata/XFF/xifenfei01.dbf

SQL> update t_xifenfei set object_name='WWW.XIFENFEI.COM';

10 rows updated.

SQL> commit;

Commit complete.

SQL> create table t_xifenfei_new(object_id,object_name) tablespace xifenfei
  2  as
  3  select object_id,object_name from dba_objects      
  4  where rownum<11;

Table created.

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

备份数据文件

[oracle@xifenfei XFF]$ cp xifenfei01.dbf  ../tmp/
[oracle@xifenfei XFF]$ ll ../tmp/xifenfei01.dbf 
-rw-r----- 1 oracle oinstall 10493952 Sep 28 19:05 ../tmp/xifenfei01.dbf
[oracle@xifenfei XFF]$ date
Fri Sep 28 19:05:42 CST 2012

bbed破坏备份文件

[oracle@xifenfei XFF]$ bbed password=blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Fri Sep 28 19:05:59 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename '/u01/oracle/oradata/tmp/xifenfei01.dbf'
        FILENAME        /u01/oracle/oradata/tmp/xifenfei01.dbf

BBED> set block 12
        BLOCK#          12

BBED> set mode edit
        MODE            Edit

BBED> map
 File: /u01/oracle/oradata/tmp/xifenfei01.dbf (0)
 Block: 12                                    Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 96 bytes                     @20      

 struct kdbh, 14 bytes                      @124     

 struct kdbt[1], 4 bytes                    @138     

 sb2 kdbr[10]                               @142     

 ub1 freespace[7666]                        @162     

 ub1 rowdata[360]                           @7828    

 ub4 tailchk                                @8188    


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        0x0140000c
   ub4 bas_kcbh                             @8        0x0004d7b0
   ub2 wrp_kcbh                             @12       0x000a
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)
   ub2 chkval_kcbh                          @16       0xe573
   ub2 spare3_kcbh                          @18       0x0000

BBED> d offset 8188
 File: /u01/oracle/oradata/tmp/xifenfei01.dbf (0)
 Block: 12               Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0106b0d7 

 <32 bytes per line>

BBED> m /x 11
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/tmp/xifenfei01.dbf (0)
 Block: 12               Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 1106b0d7 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 12:
current = 0xe563, required = 0xe563

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/tmp/xifenfei01.dbf
BLOCK = 12

Block 12 is corrupt
Corrupt block relative dba: 0x0140000c (file 0, block 12)
Fractured block found during verification
Data in bad block:
 type: 6 format: 2 rdba: 0x0140000c
 last change scn: 0x000a.0004d7b0 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xd7b00611
 check value in block header: 0xe563
 computed block checksum: 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

修改数据库记录

SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             109054428 bytes
Database Buffers          201326592 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> conn chf/xifenfei
Connected.
SQL> update t_xifenfei set object_name='惜分飞';

10 rows updated.

SQL> update t_xifenfei_new set object_name='惜分飞';

10 rows updated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

利用备份数据文件恢复数据库

[oracle@xifenfei XFF]$ cp xifenfei01.dbf xifenfei01.dbf_bak
[oracle@xifenfei XFF]$ cp ../tmp/xifenfei01.dbf  xifenfei01.dbf
[oracle@xifenfei XFF]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Sep 28 19:13:59 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             109054428 bytes
Database Buffers          201326592 bytes
Redo Buffers                7118848 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/oracle/oradata/XFF/xifenfei01.dbf'
--提示数据需要恢复

SQL> recover datafile 5;
ORA-00279: change 42949990720 generated at 09/28/2012 19:04:10 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/XFF/archivelog/1_24_792679299.dbf
ORA-00280: change 42949990720 for thread 1 is in sequence #24


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.
--利用被破坏的数据文件+归档日志恢复数据库正常

SQL> col object_name for a30
SQL> select   object_id,object_name from t_xifenfei;
select   object_id,object_name from t_xifenfei
                                    *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 12)
ORA-01110: data file 5: '/u01/oracle/oradata/XFF/xifenfei01.dbf'
--提示被破坏的数据块,查询不能完成

--证明坏块之外的数据块还是被正常应用日志
SQL>  select   object_id,object_name from t_xifenfei_new;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
        20 惜分飞
        44 惜分飞
        28 惜分飞
        15 惜分飞
        29 惜分飞
         3 惜分飞
        25 惜分飞
        39 惜分飞
        51 惜分飞
        26 惜分飞

10 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

dbv检查坏块

[oracle@xifenfei XFF]$ dbv file=xifenfei01.dbf

DBVERIFY: Release 10.2.0.4.0 - Production on Fri Sep 28 19:14:52 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = xifenfei01.dbf

DBV-00200: Block, DBA 20971532, already marked corrupt
--这里可以看出来，该数据块已经被标志为坏块

DBVERIFY - Verification complete

Total Pages Examined         : 1280
Total Pages Processed (Data) : 2
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 14
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1264
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 318700 (10.318700)

查看恢复过程alert日志

Fri Sep 28 19:14:06 2012
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Fri Sep 28 19:14:06 2012
ALTER DATABASE OPEN
ORA-1113 signalled during: ALTER DATABASE OPEN...
Fri Sep 28 19:14:11 2012
ALTER DATABASE RECOVER  datafile 5  
Media Recovery Start
 parallel recovery started with 2 processes
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 5  ...
Fri Sep 28 19:14:16 2012
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Fri Sep 28 19:14:16 2012

--恢复数据库的时候,发现坏块
Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_24_792679299.dbf
Fri Sep 28 19:14:16 2012
Hex dump of (file 5, block 12) in trace file /u01/oracle/admin/XFF/bdump/xff_p001_23011.trc
Corrupt block relative dba: 0x0140000c (file 5, block 12)
Fractured block found during media recovery
Data in bad block:
 type: 6 format: 2 rdba: 0x0140000c
 last change scn: 0x000a.0004d7b0 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xd7b00611
 check value in block header: 0xe563
 computed block checksum: 0x0
Reread of rdba: 0x0140000c (file 5, block 12) found same corrupted data

--继续恢复
Fri Sep 28 19:14:16 2012
Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0
  Mem# 0: /u01/oracle/oradata/XFF/redo01.log
Fri Sep 28 19:14:16 2012
Recovery of Online Redo Log: Thread 1 Group 2 Seq 26 Reading mem 0
  Mem# 0: /u01/oracle/oradata/XFF/redo02.log
Fri Sep 28 19:14:16 2012
Recovery of Online Redo Log: Thread 1 Group 3 Seq 27 Reading mem 0
  Mem# 0: /u01/oracle/oradata/XFF/redo03.log
Fri Sep 28 19:14:16 2012
Media Recovery Complete (XFF)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Fri Sep 28 19:14:31 2012
alter database open

bbed查看修改相关信息

BBED> set filename '/u01/oracle/oradata/XFF/xifenfei01.dbf'
        FILENAME        /u01/oracle/oradata/XFF/xifenfei01.dbf

BBED> set block 12
        BLOCK#          12

BBED> map
 File: /u01/oracle/oradata/XFF/xifenfei01.dbf (0)
 Block: 12                                    Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 96 bytes                     @20      

 struct kdbh, 14 bytes                      @124     

 struct kdbt[1], 4 bytes                    @138     

 sb2 kdbr[10]                               @142     

 ub1 freespace[7666]                        @162     

 ub1 rowdata[360]                           @7828    

 ub4 tailchk                                @8188    


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        0x0140000c
   ub4 bas_kcbh                             @8        0x00000000
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0xff   <--因为被标记为坏块,所以为ff
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0xe77d
   ub2 spare3_kcbh                          @18       0x0000

--查看数据块中记录
BBED> p *kdbr[5]
rowdata[69]
-----------
ub1 rowdata[69]                             @7897     0x2c

BBED> x /rnc
rowdata[69]                                 @7897    
-----------
flag@7897: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7898: 0x02
cols@7899:    2

col    0[2] @7900: 3 
col   1[16] @7903: WWW.XIFENFEI.COM  <--确实没有被恢复,而是直接被跳过


BBED> set mode edit
        MODE            Edit

BBED> m /x 01 offset 14
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/xifenfei01.dbf (0)
 Block: 12               Offsets:   14 to  525           Dba:0x00000000
------------------------------------------------------------------------
 01047de7 00000100 00000dcc 000098d7 ………… 

 <32 bytes per line>

BBED> d offset 8188
 File: /u01/oracle/oradata/XFF/xifenfei01.dbf (0)
 Block: 12               Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 ff060000    <--这么说明:数据块被标志为坏块的时候，同时会修改tailchk值

 <32 bytes per line>

BBED> m /x 01 offset 8188
 File: /u01/oracle/oradata/XFF/xifenfei01.dbf (0)
 Block: 12               Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 01060000 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 12:
current = 0xe77d, required = 0xe77d

--验证块已经标记为正常块
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/XFF/xifenfei01.dbf
BLOCK = 12


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

启动数据库测试

SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             109054428 bytes
Database Buffers          201326592 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> conn chf/xifenfei
Connected.
SQL> col object_name for a30
SQL> select   object_id,object_name from t_xifenfei;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
        20 WWW.XIFENFEI.COM
        44 WWW.XIFENFEI.COM
        28 WWW.XIFENFEI.COM
        15 WWW.XIFENFEI.COM
        29 WWW.XIFENFEI.COM
         3 WWW.XIFENFEI.COM
        25 WWW.XIFENFEI.COM
        39 WWW.XIFENFEI.COM
        51 WWW.XIFENFEI.COM
        26 WWW.XIFENFEI.COM

10 rows selected.

--通过修改数据块的seq_kcbh和tailchk,让这个块恢复正常,但是记录依然丢失，
--因为应用日志恢复之时标记为坏块跳过该块的日志应用

通过实验证明:
1.如果只有数据块异常,应用日志恢复,不一定会出现ORA-600[3020],而是直接把该块标记为坏块,继续应用日志
2.标记坏块其实就是修改seq_kcbh为ff,同时也修改tailchk值
3.经验值:如果在数据库应用日志恢复的时候,如果出现ORA-600[3020]错误,可以使用allow 2 corruption来跳过坏块处理,其实也是修改seq_kcbh为ff,然后让数据库跳过该块的恢复.