• 案例:Oracle dul数据挖掘 非常规对ORACLE 12C CDB数据库进行恢复


    没有数据库备份的情况下,非常规对ORACLE 12C CDB数据库进行恢复

    熟悉dul的朋友都知道dul是通过file# 1 block 1的kcvfhrdb找到bootstarp$的segment header(其实kcvfhrdb就是bootstarp$ segment header的rdba地址),然后通过bootstarp$中存储的相关sql找对一些基础的基表对象(obj$,tab$,col$,seg$等),然后通过他们定位到具体的对象的segment记录,从而通过segment找到extent分布,然后按照extent恢复数据(如果丢失system的情况,是通过扫描来确定extent属于哪个segment,然后恢复,该情况不在本次讨论范围之类)。在ORACLE 12C之前,一个实例最多都只有一个数据库,也就是说,在一个完整的数据库中只会存在一个bootstarp$,只要通过file# 1 block 1 定位到kcvfhrdb就可以读取数据库中的所有内容.但是从12C开始数据库引入了CDB的概念,也就是在一个CDB数据库中有了多个PDB数据库,那这些PDB数据库如果要编写类似dul之类工具将如何恢复出来,这里根据自己对于CDB的理解,先普及一些在CDB数据库中和bootstarp$表有关知识
    bootstarp$表在每个PDB中都存在,可以通过bbed证明

    --查看pdb相关信息
    SQL> show pdbs;
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             2 PDB$SEED                       READ ONLY  NO
             3 PDB1                           MOUNTED
             4 PDB2                           READ WRITE NO
             5 ORA11G                         MOUNTED
    
    SQL>  select con_id,header_file,header_block from cdb_segments where segment_name='BOOTSTRAP$';
    
        CON_ID HEADER_FILE HEADER_BLOCK
    ---------- ----------- ------------
             4          11          520
             1           1          520
             2           5          520
    ----因为有部分库未read write,所以查询cdb_segments未显示
    
    --file 1
    RMAN> copy datafile 1 to '/tmp/system_01.dbf';
    
    BBED> set block 1
            BLOCK#          1
    
    BBED> map
     File: /tmp/system_01.dbf (0)
     Block: 1                                     Dba:0x00000000
    ------------------------------------------------------------
     Data File Header
    
     struct kcvfh, 1112 bytes                   @0       
    
     ub4 tailchk                                @8188    
    
    BBED> p kcvfhrdb
    ub4 kcvfhrdb                                @96       0x00400208
    
    SQL> select to_number('400208','xxxxxxxxxx') from dual;
    
    TO_NUMBER('400208','XXXXXXXXXX')
    --------------------------------
                             4194824
    
    SQL> select dbms_utility.data_block_address_block(4194824) "block",
      2  dbms_utility.data_block_address_file(4194824) "file" from dual;
    
         block       file
    ---------- ----------
           520          1
    
    ----可以知道bootstarp$起点的rdba为4194824,在rfile# 1 block# 520上
    
    --file 11
    RMAN> copy datafile 11 to '/tmp/system_11.dbf';
    
    BBED> set filename '/tmp/system_11.dbf'
            FILENAME        /tmp/system_11.dbf
    
    BBED> set block 1
            BLOCK#          1
    
    BBED> p kcvfhrdb
    ub4 kcvfhrdb                                @96       0x00400208
    
    ---显示的rdba地址完全与file# 1中的kcvfhrdb相同,也就是表示rfile# 1 block# 520
    
    --验证未mount pdb,并且从11.2.0.4升级到12.1.0.1
    ASMCMD> cp system01.dbf /tmp/system_18.dbf
    copying +data/ora11g/system01.dbf -> /tmp/system_18.dbf
    
    BBED> set filename '/tmp/system_18.dbf'
            FILENAME        /tmp/system_18.dbf
    
    BBED>  set block 1
            BLOCK#          1
    
    BBED> p kcvfhrdb
    ub4 kcvfhrdb                                @96       0x0041ad40
    
    SQL> select to_number('41ad40','xxxxxxxxx') from dual;
    
    TO_NUMBER('41AD40','XXXXXXXXX')
    -------------------------------
                            4304192
    
    SQL> select dbms_utility.data_block_address_block(4304192) "block",
      2  dbms_utility.data_block_address_file(4304192) "file" from dual;
    
         block       file
    ---------- ----------
        109888          1
    ----可以知道bootstarp$起点的rdba为4304192,在rfile# 1 block# 109888上
    

    1.查询contrainer$视图确认bootstarp$

    SQL> select a.con_id#, a.dbid, a.rdba, dbms_utility.data_block_address_file(a.rdba) "file",
    2    dbms_utility.data_block_address_block(a.rdba) "block"from container$ a;
    
       CON_ID#       DBID       RDBA       file      block
    ---------- ---------- ---------- ---------- ----------
             1 1922813718    4194824          1        520
             5 4211303690    4304192          1     109888
             2 4048821679    4194824          1        520
             4 3872456618    4194824          1        520
             3 3313918585    4194824          1        520
    

    通过上面的知识点,我们明确,在ORACLE 12C CDB设计理念中,为了和12C之前的版本兼用(12C之前的版本可以通过PDB插入到CDB中),也为了方便用户在操作PDB时候和传统数据库一样,没有任何区别,所以它把每个PDB的rdba的计算方法认为PDB内部的RELFILE#是从1开始(也就是说每个rdba都是相对于自己的pdb而言),所以这里的contrainer$查询出来的rdba的地址就比较好理解(并非是绝对文件号,而是相对文件号,即表示pdb的第一个数据文件[传统的system01.dbf])

    2.rdba中的file#和cdb中的file#关系

    SQL> show con_name;
    
    CON_NAME
    ------------------------------
    PDB2
    SQL> select file#, RELFILE#   from file$;
    
         FILE#   RELFILE#
    ---------- ----------
            12          4
            11          1
            13         13
    
    SQL> show con_name;
    
    CON_NAME
    ------------------------------
    CDB$ROOT
    SQL> select file#, RELFILE#   from file$;
    
         FILE#   RELFILE#
    ---------- ----------
             1          1
             3          3
             5
             6          6
             2
             4          4
    
    6 rows selected.
    

    通过这里的分析,就可以清晰的知道当前的dul是完全可以处理ORACLE 12C的CDB数据库.

    3.dul恢复CDB中PDB数据

    --在pdb中创建测试表
    SQL> show pdbs;
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             2 PDB$SEED                       READ ONLY  NO
             3 PDB1                           MOUNTED
             4 PDB2                           READ WRITE NO
             5 ORA11G                         MOUNTED
    
    SQL> alter session set container=pdb2;
    
    Session altered.
    
    SQL> show con_name;
    
    CON_NAME
    ------------------------------
    PDB2
    
    SQL> show con_id
    
    CON_ID
    ------------------------------
    3
    
    SQL> create user xff identified by xifenfei;
    
    User created.
    
    SQL> grant dba to xff;
    
    Grant succeeded.
    
    SQL> create table xff.t_xifenfei tablespace users 
      2  as select * from dba_objects;
    
    Table created.
    
    SQL> alter system checkpoint;
    
    System altered.
    
    SQL> select count(*) from xff.t_xifenfei;
    
      COUNT(*)
    ----------
         90756
    
    --使用dul抽取数据
    [oracle@xifenfei dul]$ ./dul
    
    
     Strictly Oracle Internal Use Only
    
    
    DUL: Warning: Recreating file "dul.log"
    Disk group DATA, dul group_cid 0
    Discovered disk /dev/sdb as diskgroup DATA, disk number 0 size 20480 Mb File1 starts at 10, dul_disk_cid 0
    
    DUL: Warning: Dictionary cache DC_ASM_EXTENTS is empty
    Probing for attributes in File9, the attribute directory, for disk group DATA
    attribute name "_extent_sizes", value "1 4 16"
    attribute name "_extent_counts", value "20000 20000 214748367"
    Oracle data file size 283123712 bytes, block size 8192
    Found db_id = 1922813718
    Found db_name = CDB
    Oracle data file size 713039872 bytes, block size 8192
    DUL> bootstrap;
    Probing file = 1, block = 520
    . unloading table                BOOTSTRAP$
    DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
          60 rows unloaded
    DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
    Reading BOOTSTRAP.dat 60 entries loaded
    Parsing Bootstrap$ contents
    DUL: Warning: Recreating file "dict.ddl"
    Generating dict.ddl for version 11
     OBJ$: segobjno 18, file 1 block 240
     TAB$: segobjno 2, tabno 1, file 1  block 144
     COL$: segobjno 2, tabno 5, file 1  block 144
     USER$: segobjno 10, tabno 1, file 1  block 208
    Running generated file "@dict.ddl" to unload the dictionary tables
    . unloading table                      OBJ$   90758 rows unloaded
    . unloading table                      TAB$    2363 rows unloaded
    . unloading table                      COL$  106731 rows unloaded
    . unloading table                     USER$     124 rows unloaded
    Reading USER.dat 124 entries loaded
    Reading OBJ.dat 90758 entries loaded and sorted 90758 entries
    Reading TAB.dat 2363 entries loaded
    Reading COL.dat 106685 entries loaded and sorted 106685 entries
    Reading BOOTSTRAP.dat 60 entries loaded
    
    DUL: Warning: Recreating file "dict.ddl"
    Generating dict.ddl for version 11
     OBJ$: segobjno 18, file 1 block 240
     TAB$: segobjno 2, tabno 1, file 1  block 144
     COL$: segobjno 2, tabno 5, file 1  block 144
     USER$: segobjno 10, tabno 1, file 1  block 208
     TABPART$: segobjno 692, file 1 block 4528
     INDPART$: segobjno 697, file 1 block 4568
     TABCOMPART$: segobjno 714, file 1 block 9880
     INDCOMPART$: segobjno 719, file 0 block 0
     TABSUBPART$: segobjno 704, file 1 block 9928
     INDSUBPART$: segobjno 709, file 0 block 0
     IND$: segobjno 2, tabno 3, file 1  block 144
     ICOL$: segobjno 2, tabno 4, file 1  block 144
     LOB$: segobjno 2, tabno 6, file 1  block 144
     COLTYPE$: segobjno 2, tabno 7, file 1  block 144
     TYPE$: segobjno 619, tabno 1, file 1  block 1528
     COLLECTION$: segobjno 619, tabno 2, file 1  block 1528
     ATTRIBUTE$: segobjno 619, tabno 3, file 1  block 1528
     LOBFRAG$: segobjno 725, file 1 block 4616
     LOBCOMPPART$: segobjno 728, file 0 block 0
     UNDO$: segobjno 15, file 1 block 224
     TS$: segobjno 6, tabno 2, file 1  block 176
     PROPS$: segobjno 126, file 1 block 1096
    Running generated file "@dict.ddl" to unload the dictionary tables
    . unloading table                      OBJ$
    DUL: Warning: Recreating file "OBJ.ctl"
       90758 rows unloaded
    . unloading table                      TAB$
    DUL: Warning: Recreating file "TAB.ctl"
        2363 rows unloaded
    . unloading table                      COL$
    DUL: Warning: Recreating file "COL.ctl"
      106731 rows unloaded
    . unloading table                     USER$
    DUL: Warning: Recreating file "USER.ctl"
         124 rows unloaded
    . unloading table                  TABPART$     234 rows unloaded
    . unloading table                  INDPART$     155 rows unloaded
    . unloading table               TABCOMPART$       1 row  unloaded
    
    DUL: Error: dc_segment_header(dataobj#=719, ts#=0, fil=0, blk=0) failed
    DUL: Warning: Nothing to unload from empty delayed segment creation table INDCOMPART$
    . unloading table               TABSUBPART$      32 rows unloaded
    
    DUL: Error: dc_segment_header(dataobj#=709, ts#=0, fil=0, blk=0) failed
    DUL: Warning: Nothing to unload from empty delayed segment creation table INDSUBPART$
    . unloading table                      IND$    4237 rows unloaded
    . unloading table                     ICOL$    6290 rows unloaded
    . unloading table                      LOB$     849 rows unloaded
    . unloading table                  COLTYPE$    2567 rows unloaded
    . unloading table                     TYPE$    3651 rows unloaded
    . unloading table               COLLECTION$    1345 rows unloaded
    . unloading table                ATTRIBUTE$   13755 rows unloaded
    . unloading table                  LOBFRAG$       6 rows unloaded
    
    DUL: Error: dc_segment_header(dataobj#=728, ts#=0, fil=0, blk=0) failed
    DUL: Warning: Nothing to unload from empty delayed segment creation table LOBCOMPPART$
    . unloading table                     UNDO$       1 row  unloaded
    . unloading table                       TS$       4 rows unloaded
    . unloading table                    PROPS$      38 rows unloaded
    Reading USER.dat 124 entries loaded
    Reading OBJ.dat 90758 entries loaded and sorted 90758 entries
    Reading TAB.dat 2363 entries loaded
    Reading COL.dat 106685 entries loaded and sorted 106685 entries
    Reading TABPART.dat 234 entries loaded and sorted 234 entries
    Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
    Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
    Reading INDPART.dat 155 entries loaded and sorted 155 entries
    Reading IND.dat 4237 entries loaded
    Reading LOB.dat 849 entries loaded
    Reading ICOL.dat 6290 entries loaded
    Reading COLTYPE.dat 2567 entries loaded
    Reading TYPE.dat 3651 entries loaded
    Reading ATTRIBUTE.dat 13755 entries loaded
    Reading COLLECTION.dat
    DUL: Warning: Increased the size of DC_COLLECTIONS from 1024 to 8192 entries
     1345 entries loaded
    Reading BOOTSTRAP.dat 60 entries loaded
    Reading LOBFRAG.dat 6 entries loaded and sorted 6 entries
    Reading UNDO.dat 1 entries loaded
    Reading TS.dat 4 entries loaded
    Reading PROPS.dat 38 entries loaded
    Database character set is ZHS16GBK
    Database national character set is AL16UTF16
    DUL> unload table xff.t_xifenfei;
    . unloading table                T_XIFENFEI   90756 rows unloaded
    

    4.核对数据恢复的结果

    SQL> create table xff.t_xifenfei_new as select  * from xff.t_xifenfei where 1=0;
    
    Table created.
    
    [oracle@xifenfei dul]$ sqlldr xff/xifenfei@pdb2 control=XFF_T_XIFENFEI.ctl 
    
    SQL*Loader: Release 12.1.0.1.0 - Production on Sun Jun 2 18:08:04 2013
    
    Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
    
    Path used:      Conventional
    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 90589
    Commit point reached - logical record count 90653
    Commit point reached - logical record count 90717
    Commit point reached - logical record count 90756
    
    Table "XFF"."T_XIFENFEI_NEW":
      90756 Rows successfully loaded.
    
    Check the log file:
      XFF_T_XIFENFEI.log
    for more information about the load.
    
    SQL> select count(*) from xff.t_xifenfei_new;
    
      COUNT(*)
    ----------
         90756
    

    通过分析12C的bootstarp$表分布,和dul恢复数据库原理,通过变动实现dul完美恢复CDB中的pdb数据

    --------------------------------------ORACLE-DBA----------------------------------------

    最权威、专业的Oracle案例资源汇总之案例:Oracle dul数据挖掘 非常规对ORACLE 12C CDB数据库进行恢复

    原文唯一网址:http://www.oracleplus.net/arch/oracle-20160522-218.html

    Oracle研究中心

    关键词:

    Oracle dul数据挖掘

    非常规对ORACLE 12C CDB数据库进行恢复

  • 相关阅读:
    pip错误:'utf-8' codec can't decode byte解决方法
    windows中python2与python3共存
    Alpha 冲刺 (3/10)
    Alpha 冲刺 (2/10)
    Alpha 冲刺 (1/10)
    项目需求分析报告答辩总结
    项目选题报告答辩总结
    项目UML设计(团队)
    # 第七次作业--项目需求分析(团队)
    结对项目--第二次作业
  • 原文地址:https://www.cnblogs.com/piguanjia/p/5608274.html
Copyright © 2020-2023  润新知