Case: Oracle Dul Data Mining Unconventional recovery of Oracle 12C CDB database

Source: Internet
Author: User
Tags dba


non-routine recovery of Oracle 12C CDB database without database backup


Familiar with Dul friends know Dul is file# 1 Block 1 kcvfhrdb find bootstarp$ Segment Header (in fact Kcvfhrdb is bootstarp$ segment Header RDBA address), Then through the related SQL stored in the bootstarp$ to find some base table objects (obj$,tab$,col$,seg$, etc.), and then through their positioning to the specific object of the segment records, so segment find extent distribution, Then follow extent to recover the data (if the system is missing, it is by scanning to determine which segment the extent belongs to and then recovering, which is not the case in this discussion). Before Oracle 12C, an instance had a maximum of one database, that is, there would only be one bootstarp$ in a complete database, as long as the file# 1 Block 1 Navigate to Kcvfhrdb to read everything in the database. But starting with the 12C database introduced the concept of CDB, that is, in a CDB database with multiple PDB databases, the PDB database if you want to write a tool like Dul, how to recover, Here, according to their understanding of CDB, first popularize some knowledge in the CDB database and the bootstarp$ table.
bootstarp$ tables are present in each PDB and can be proved by bbed


--View pdb related information
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
---- The query cdb_segments is not displayed because some libraries have not read write

--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

---- It can be known that the rdba of the starting point of bootstarp $ is 4194824, on 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

--- The displayed rdba address is exactly the same as kcvfhrdb in file # 1, which means rfile # 1 block # 520

-Verify that pdb is not mounted, and upgrade from 11.2.0.4 to 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
---- It can be known that the rdba of the starting point of bootstarp $ is 4304192, on rfile # 1 block # 109888
1. Query contrainer$ View Confirmation 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


Through the above knowledge point, we explicitly, in the Oracle 12C CDB design concept, in order to and 12C before the version of the dual use (12C before the version can be inserted into the CDB through the PDB), but also to facilitate the user in the operation of the PDB and the traditional database, no difference, So it thinks that the rdba of each PDB is based on the idea that the relfile# inside the PDB is starting from 1 (i.e. each RDBA is relative to its own PDB), so the RDBA address of the contrainer$ query here is better understood (not an absolute file number, Instead, the relative file number, which is the first data file representing the PDB [traditional SYSTEM01.DBF])


file# relationships in file# and CDB in 2.RDBA
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.


With this analysis, it is clear that the current Dul is fully capable of processing Oracle 12C's CDB database.


3.dul Recovery of PDB data in CDB
-Create a test table in 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

-Use dul to extract data
[[email protected] 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. Check the results of data recovery
SQL> create table xff.t_xifenfei_new as select  * from xff.t_xifenfei where 1=0;

Table created.

[[email protected] dul]$ sqlldr xff/[email protected] 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


Through the analysis of 12C bootstarp$ table distribution, and Dul recovery database principle, through the change to achieve dul perfect recovery of PDB data in CDB






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



The most authoritative and professional example of Oracle Case resource Summary: Oracle DUL Data Mining Unconventional recovery of Oracle 12C CDB database



Original URL: http://www.oracleplus.net/arch/oracle-20160522-218.html


Oracle Research Center Key Words:Oracle Dul Data Mining Unconventional recovery of Oracle 12C CDB database


Case: Oracle Dul Data Mining Unconventional recovery of Oracle 12C CDB database


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.