分类目录归档：Oracle备份恢复

Oracle 传输表空间

发表于 2011 年 9 月 11 日 由 惜分飞

0、检查平台信息
所有tts支持平台
SELECT * FROM V$TRANSPORTABLE_PLATFORM;

当前系统平台情况
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

一、源端操作
检查是否符合TTS要求
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘ODU’, TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL> SELECT COUNT(*) FROM DBA_TABLES WHERE TABLESPACE_NAME=’ODU’;

COUNT(*)
———-
59

SQL> SELECT file_name from dba_data_files where tablespace_name=’ODU’;

FILE_NAME
————————————————–
/opt/oracle/oradata/chf/odu01.dbf
/opt/oracle/oradata/chf/odu02.dbf

需要传输表空间至于readonly模式
SQL> ALTER TABLESPACE ODU READ ONLY;

Tablespace altered.

导出表空间元数据
[oracle@node1 ~]$ exp userid=\’/ as sysdba\’ tablespaces=ODU file=/tmp/ODU.dmp transport_tablespace=y

Export: Release 10.2.0.4.0 – Production on Sun Sep 11 10:01:52 2011

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata…
For tablespace ODU …
. exporting cluster definitions
. exporting table definitions
. . exporting table T_ODU_03
. . exporting table T_ODU_01
. . exporting table T_ODU
. . exporting table DB
. . exporting table NODE
. . exporting table CONF
. . exporting table DBINC
. . exporting table CKP
. . exporting table TS
. . exporting table TSATT
. . exporting table DF
. . exporting table DFATT
. . exporting table TF
. . exporting table TFATT
. . exporting table OFFR
. . exporting table RR
. . exporting table RT
. . exporting table ORL
. . exporting table RLH
. . exporting table AL
. . exporting table BS
. . exporting table BP
. . exporting table BCF
. . exporting table CCF
. . exporting table XCF
. . exporting table BSF
. . exporting table BDF
. . exporting table CDF
. . exporting table XDF
. . exporting table BRL
. . exporting table BCB
. . exporting table CCB
. . exporting table SCR
. . exporting table SCRL
. . exporting table CONFIG
. . exporting table XAL
. . exporting table RSR
. . exporting table FB
. . exporting table GRSP
. . exporting table ROUT
. . exporting table RCVER
. . exporting table F_DROP
. . exporting table T_QUERY
. . exporting table T_UNDO
. . exporting table A
. . exporting table T1
. . exporting table T2_1
. . exporting table T2
. . exporting table T_MV
. . exporting table TAB2
. . exporting table MLOG$_T_MV
. . exporting table T_N
. . exporting table T_M
. . exporting table MLOG$_T_N
. . exporting table T_1
. . exporting table T_2
. . exporting table T_3
. . exporting table T_4
. . exporting table T_5
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

SQL> alter tablespace odu read write;

Tablespace altered.

传输到目标段
[oracle@node1 ~]$ scp /opt/oracle/oradata/chf/odu0* 192.168.11.12:/opt/oracle/oradata/test
The authenticity of host ’192.168.11.12 (192.168.11.12)’ can’t be established.
RSA key fingerprint is db:3c:b4:34:7f:d7:e4:97:ab:b6:8b:b0:ab:22:43:35.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ’192.168.11.12′ (RSA) to the list of known hosts.
oracle@192.168.11.12′s password:
odu01.dbf 100% 100MB 3.3MB/s 00:30
odu02.dbf 100% 11GB 2.8MB/s 1:05:00
[oracle@node1 ~]$ scp /tmp/ODU.dmp 192.168.11.12:/tmp
oracle@192.168.11.12′s password:
Permission denied, please try again.
oracle@192.168.11.12′s password:
ODU.dmp 100% 456KB 456.0KB/s 00:00

二、目标端操作
导入元数据库
[oracle@ECP-UC-DB1 ~]$ imp userid=\’/ as sysdba\’ tablespaces=ODU file=/tmp/ODU.dmp transport_tablespace=y datafiles=/opt/oracle/oradata/test/odu01.dbf, /opt/oracle/oradata/test/odu02.dbf fromuser=chf touser=chf

Import: Release 10.2.0.4.0 – Production on Sun Sep 11 11:13:25 2011

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata…
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF’s objects into CHF
. . importing table “T_ODU_03″
. . importing table “T_ODU_01″
. . importing table “T_ODU”
. . importing table “DB”
. . importing table “NODE”
. . importing table “CONF”
. . importing table “DBINC”
. . importing table “CKP”
. . importing table “TS”
. . importing table “TSATT”
. . importing table “DF”
. . importing table “DFATT”
. . importing table “TF”
. . importing table “TFATT”
. . importing table “OFFR”
. . importing table “RR”
. . importing table “RT”
. . importing table “ORL”
. . importing table “RLH”
. . importing table “AL”
. . importing table “BS”
. . importing table “BP”
. . importing table “BCF”
. . importing table “CCF”
. . importing table “XCF”
. . importing table “BSF”
. . importing table “BDF”
. . importing table “CDF”
. . importing table “XDF”
. . importing table “BRL”
. . importing table “BCB”
. . importing table “CCB”
. . importing table “SCR”
. . importing table “SCRL”
. . importing table “CONFIG”
. . importing table “XAL”
. . importing table “RSR”
. . importing table “FB”
. . importing table “GRSP”
. . importing table “ROUT”
. . importing table “RCVER”
. . importing table “F_DROP”
. . importing table “T_QUERY”
. . importing table “T_UNDO”
. . importing table “A”
. . importing table “T1″
. . importing table “T2_1″
. . importing table “T2″
. . importing table “T_MV”
. . importing table “TAB2″
. . importing table “MLOG$_T_MV”
. . importing table “T_N”
. . importing table “T_M”
. . importing table “MLOG$_T_N”
. . importing table “T_1″
. . importing table “T_2″
. . importing table “T_3″
. . importing table “T_4″
. . importing table “T_5″
About to enable constraints…
Import terminated successfully without warnings.

SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME STATUS
—————————— ———
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
XFF ONLINE
ODU READ ONLY

7 rows selected.

修改为readwrite模式（根据需求）
SQL> alter tablespace odu read write;

Tablespace altered.

SQL> SELECT COUNT(*) FROM DBA_TABLES WHERE TABLESPACE_NAME=’ODU’;

COUNT(*)
———-
59

三、相关说明
1、如果平台字节顺序不同，需要使用rman convert转换
2、导出导入元数据可以使用data pump实现
3、检查视图、触发器、包、过程、函数等对象，如果没有需要使用exp/imp row=n导入或者人工建立

发表在 逻辑备份/恢复 | 评论关闭

bbed 修改datafile header

发表于 2011 年 8 月 31 日 由 惜分飞 
SQL> startup
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/opt/oracle/oradata/xifenfei/xff01.dbf'
提示数据文件2需要恢复，首选是recover datafile 2;，如果失败，可以考虑bbed修改scn的办法

SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;

     FILE# TO_CHAR(CHECK
---------- -------------
         1  107374278108
         2  107374278108
         3  107374278108
         4  107374278108
         5  107374278108
         6  107374278108
         7  107374278108
         8  107374278108
         9  107374278108
        10  107374278108
        11  107374278108

11 rows selected.

SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
         2 ONLINE   107374277136

[oracle@localhost tmp]$ bbed parfile=/tmp/parfile.cnf
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Mon Aug 22 06:05:34 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

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

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /opt/oracle/oradata/xifenfei/system01.dbf                         1280
     2  /opt/oracle/oradata/xifenfei/xff01.dbf                            1280

BBED> set dba 1,1
        DBA             0x00400001 (4194305 1,1)

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @140     
   struct kcvcpscn, 8 bytes                 @140     
*    ub4 kscnbas                           @140      0x000175dc
      ub2 kscnwrp                           @144      0x0019
* ub4 kcvcptim                             @148      0x2d49fbbb
   ub2 kcvcpthr                             @152      0x0001
   union u, 12 bytes                        @156     
      struct kcvcprba, 12 bytes             @156     
         ub4 kcrbaseq                       @156      0x00000005
         ub4 kcrbabno                       @160      0x00008e05
         ub2 kcrbabof                       @164      0x0010
      struct kcvcptr, 12 bytes              @156     
         struct kcrtrscn, 8 bytes           @156     
            ub4 kscnbas                     @156      0x00000005
            ub2 kscnwrp                     @160      0x8e05
         ub4 kcrtrtim                       @164      0x09110010
   ub1 kcvcpetb[0]                          @168      0x02
   ub1 kcvcpetb[1]                          @169      0x00
   ub1 kcvcpetb[2]                          @170      0x00
   ub1 kcvcpetb[3]                          @171      0x00
   ub1 kcvcpetb[4]                          @172      0x00
   ub1 kcvcpetb[5]                          @173      0x00
   ub1 kcvcpetb[6]                          @174      0x00
   ub1 kcvcpetb[7]                          @175      0x00

BBED> p kcvfhcpc
*ub4 kcvfhcpc                                @176      0x0000007a

BBED> p kcvfhccc
*ub4 kcvfhccc                                @184      0x00000079
星号表示使用bbed修改datafile header scn需要考虑的地方

SQL> select to_char(to_number('19000175dc','xxxxxxxxxxxx'),'999999999999') from dual;

TO_CHAR(TO_NU
-------------
 107374278108
证实system01.dbf的scn为107374278108和v$datafile查询到的一致

BBED> set dba 2,1
        DBA             0x00800001 (8388609 2,1)

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @140     
   struct kcvcpscn, 8 bytes                 @140     
      ub4 kscnbas                           @140      0x00017210
      ub2 kscnwrp                           @144      0x0019
   ub4 kcvcptim                             @148      0x2d49fa27
   ub2 kcvcpthr                             @152      0x0001
   union u, 12 bytes                        @156     
      struct kcvcprba, 12 bytes             @156     
         ub4 kcrbaseq                       @156      0x00000005
         ub4 kcrbabno                       @160      0x00006f18
         ub2 kcrbabof                       @164      0x0010
      struct kcvcptr, 12 bytes              @156     
         struct kcrtrscn, 8 bytes           @156     
            ub4 kscnbas                     @156      0x00000005
            ub2 kscnwrp                     @160      0x6f18
         ub4 kcrtrtim                       @164      0x09110010
   ub1 kcvcpetb[0]                          @168      0x02
   ub1 kcvcpetb[1]                          @169      0x00
   ub1 kcvcpetb[2]                          @170      0x00
   ub1 kcvcpetb[3]                          @171      0x00
   ub1 kcvcpetb[4]                          @172      0x00
   ub1 kcvcpetb[5]                          @173      0x00
   ub1 kcvcpetb[6]                          @174      0x00
   ub1 kcvcpetb[7]                          @175      0x00

BBED> p kcvfhcpc
ub4 kcvfhcpc                                @176      0x00000034

BBED> p kcvfhccc
ub4 kcvfhccc                                @184      0x00000033


SQL> select to_char(to_number('1900017210','xxxxxxxxxxxx'),'999999999999') from dual;

TO_CHAR(TO_NU
-------------
 107374277136
和v$recover_file视图中查询出来一致

BBED> set dba 2,1 offset 140
        DBA             0x00800001 (8388609 2,1)
        OFFSET          140

BBED> show 
        FILE#           2
        BLOCK#          1
        OFFSET          140
        DBA             0x00800001 (8388609 2,1)
        FILENAME        /opt/oracle/oradata/xifenfei/xff01.dbf
        BIFILE          bifile.bbd
        LISTFILE        /tmp/list
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           32
        LOGFILE         log.bbd
        SPOOL           No

BBED> m /x dc750100
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 1                Offsets:  140 to  171           Dba:0x00800001
------------------------------------------------------------------------
 dc750100 19000000 27fa492d 01000000 05000000 186f0000 10001109 02000000 

 <32 bytes per line>

BBED> m /x bbfb492d offset 158
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 1                Offsets:  158 to  189           Dba:0x00800001
------------------------------------------------------------------------
 bbfb492d 00001000 11090200 00000000 00003400 00004cc0 492d3300 00000000 

 <32 bytes per line>

BBED> m /x 7a000000 offset 176
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 1                Offsets:  176 to  207           Dba:0x00800001
------------------------------------------------------------------------
 7a000000 4cc0492d 33000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> m /x 79000000 offset 184
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 1                Offsets:  184 to  215           Dba:0x00800001
------------------------------------------------------------------------
 79000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>
注意：p打印出来的值和m修改的值可能不是完全一致（和cpu的计算类型有关，常用的intel cpu中是倒序存储），可以选择先dump正确的相关值，然后m修改
如：p打印出来的值为：80084d1f时，m修改时要为： 1f4d0880

BBED> sum apply dba 2,1
Check value for File 2, Block 1:
current = 0x7ece, required = 0x7ece


SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '/opt/oracle/oradata/xifenfei/xff01.dbf'
ORA-01207: file is more recent than controlfile - old controlfile
提示控制文件太老，需要重建控制文件

SQL> shutdown abort
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XIFENFEI" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/opt/oracle/oradata/xifenfei/redo01.log'  SIZE 100M,
  GROUP 2 '/opt/oracle/oradata/xifenfei/redo02.log'  SIZE 100M,
  GROUP 3 '/opt/oracle/oradata/xifenfei/redo03.log'  SIZE 100M
DATAFILE
  '/opt/oracle/oradata/xifenfei/system01.dbf',
  '/opt/oracle/oradata/xifenfei/xff01.dbf',
  '/opt/oracle/oradata/xifenfei/cwmlite01.dbf',
  '/opt/oracle/oradata/xifenfei/drsys01.dbf',
  '/opt/oracle/oradata/xifenfei/example01.dbf',
  '/opt/oracle/oradata/xifenfei/indx01.dbf',
  '/opt/oracle/oradata/xifenfei/odm01.dbf',
  '/opt/oracle/oradata/xifenfei/tools01.dbf',
  '/opt/oracle/oradata/xifenfei/users01.dbf',
  '/opt/oracle/oradata/xifenfei/xdb01.dbf',
  '/opt/oracle/oradata/xifenfei/UNDOTBS01.dbf'
CHARACTER SET ZHS16GBK
;
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   
Control file created.

SQL> alter database open;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/xifenfei/temp01.dbf'
  2       SIZE 32505856  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
重建控制文件后，需要添加临时文件

补充说明：
1、datafile 的file header 存储在第一个block里
2、Oracle considers four attributes of this data structure when determining if a datafile is sync with the other data files of the database:（不同oracle版本offset可能不同）
（1）kscnbas (at offset 140) – SCN of last change to the datafile.
（2）kcvcptim (at offset 148) -Time of the last change to the datafile.
（3）kcvfhcpc (at offset 176) – Checkpoint count.
（4）kcvfhccc (at offset 184) – Unknown, but is always 1 less than thecheckpoint point count.
Oracle有4个属性来判断datafile 是否和其他的datafile 一致，如果都一致，可以正常操作，如果不一致，那么会报ORA-01113错误
使用Oracle Recovery Tools一键式解决给问题

发表在 非常规恢复 | 标签为 | 评论关闭

通过bbed查看数据块结构

发表于 2011 年 8 月 30 日 由 惜分飞 
BBED> map /v
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 530                                   Dba:0x00800212
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       
    ub1 type_kcbh                           @0       
    ub1 frmt_kcbh                           @1       
    ub1 spare1_kcbh                         @2       
    ub1 spare2_kcbh                         @3       
    ub4 rdba_kcbh                           @4       
    ub4 bas_kcbh                            @8       
    ub2 wrp_kcbh                            @12      
    ub1 seq_kcbh                            @14      
    ub1 flg_kcbh                            @15      
    ub2 chkval_kcbh                         @16      
    ub2 spare3_kcbh                         @18      

 struct ktbbh, 72 bytes                     @20      
    ub1 ktbbhtyp                            @20      
    union ktbbhsid, 4 bytes                 @24      
    struct ktbbhcsc, 8 bytes                @28      
    b2 ktbbhict                             @36      
    ub1 ktbbhflg                            @38      
    ub1 ktbbhfsl                            @39      
    ub4 ktbbhfnx                            @40      
    struct ktbbhitl[2], 48 bytes            @44      

 struct kdbh, 14 bytes                      @92      
    ub1 kdbhflag                            @92      
    b1 kdbhntab                             @93      
    b2 kdbhnrow                             @94      
    sb2 kdbhfrre                            @96      
    sb2 kdbhfsbo                            @98      
    sb2 kdbhfseo                            @100     
    b2 kdbhavsp                             @102     
    b2 kdbhtosp                             @104     

 struct kdbt[1], 4 bytes                    @106     
    b2 kdbtoffs                             @106     
    b2 kdbtnrow                             @108     

 sb2 kdbr[101]                              @110     

 ub1 freespace[4270]                        @312     

 ub1 rowdata[3606]                          @4582    

 ub4 tailchk                                @8188    


BBED> 


////////////////////////////////////////////////////////////////////////////////////////
//////////// Block Header Structure,  20 bytes//////////////////////////////////////////
BBED> p kcbh
struct kcbh, 20 bytes                       @0       
    ub1 type_kcbh                           @0    -- Block Type 
                                                  -- 01 - Undo segment header
                                                  -- 02 - Undo data block
                                                  -- 03 - Save undo header
                                                  -- 04 - Save undo data block
                                                  -- 05 - Data segment header  
                                                  -- 06 - Trans data, KTB managed data block(with ITL)
                                                  -- 07 - Temp table data block (no ITL)
                                                  -- 08 - Sort key
                                                  -- 09 - Sort Run
                                                  -- 10 - Segment free list block
                                                  -- 11 - Data file header
    ub1 frmt_kcbh                           @1    -- Block Format 1=Oracle7, 2=Oracle8+   
    ub1 spare1_kcbh                         @2    -- Not used, filler field   
    ub1 spare2_kcbh                         @3    -- Not used, filler field   
    ub4 rdba_kcbh                           @4    -- RDBA (4 bytes) - Relative Data Block Address   
    ub4 bas_kcbh                            @8    -- SCN Base (4 bytes)  
    ub2 wrp_kcbh                            @12   -- SCN Wrap (2 bytes)   
    ub1 seq_kcbh                            @14   -- Sequence Number, incremented for every change made to the block at the same SCN    
    ub1 flg_kcbh                            @15   -- Flag:   
                                                  -- 0x01 New Block
                                                  -- 0x02 Delayed Logging Chang advanced SCN/seq 
                                                  -- 0x04 Check value saved - block XOR's to Zero
                                                  -- 0x08 Temporary block
    ub2 chkval_kcbh                         @16   -- Optional block checksum (if DB_BLOCK_CHECKSUM=TRUE)   
    ub2 spare3_kcbh                         @18   -- Not used, filler field  
 
 /////////////////////////////////////////////////////////////////////////////////////////
/////////Transaction Fixed Header Structure, 72 Bytes////////////////////////////////////
BBED> p ktbbh
 struct ktbbh, 72 bytes                     @20      
    ub1 ktbbhtyp                            @20     -- Block type (1=DATA, 2=INDEX) 
    union ktbbhsid, 4 bytes                 @24     -- Segment/Object ID
    struct ktbbhcsc, 8 bytes                @28     -- SCN at last block cleanout 
    b2 ktbbhict                             @36     -- Number of ITL slots 
    ub1 ktbbhflg                            @38     -- 0=on the freelist 
    ub1 ktbbhfsl                            @39     -- ITL TX freelist slot 
    ub4 ktbbhfnx                            @40     -- DBA of next block on the freelist 
    struct ktbbhitl[2], 48 bytes            @44     -- ITL list index, each ITL takes up 24 bytes 

//////////////////////////////////////////////////////////////////////////////////////////
///////////////Data Header Structure, 14 bytes////////////////////////////////////////////
BBED> p kdbh
 struct kdbh, 14 bytes                      @100     
    ub1 kdbhflag                            @100    -- N=pctfree hit(clusters)
                                                    -- F=do not put on freelist
                                                    -- K=flushable cluster keys 
    b1 kdbhntab                             @101    -- Number of tables (>1 in clusters) 
    b2 kdbhnrow                             @102    -- Number of rows (2 bytes)
    sb2 kdbhfrre                            @104    -- First free row entry index; -1=you have to add one 
    sb2 kdbhfsbo                            @106    -- Freespace begin offset 
    sb2 kdbhfseo                            @108    -- Freespace end offset 
    b2 kdbhavsp                             @110    -- Available space in the block 
    b2 kdbhtosp                             @112    -- Total available space when all TXs commit 

////////////////////////////////////////////////////////////////////////////////////////
/////////////////////Table Directory Entry Structure, 4 bytes///////////////////////////
BBED> p kdbt
 struct kdbt[1], 4 bytes                    @114     
    b2 kdbtoffs                             @114     
    b2 kdbtnrow                             @116     

////////////////////////////////////////////////////////////////////////////////////////
////////////////// Row Directory ///////////////////////////////////////////////////////
BBED> p kdbr[100]
 sb2 kdbr[100]                                @310     


////////////////////////////////////////////////////////////////////////////////////////
///////////////// Free Space ///////////////////////////////////////////////////////////
BBED> p freespace[4269]
 ub1 freespace[4269]                        @4581     

///////////////////////////////////////////////////////////////////////////////////////
/////////////////////Row Data//////////////////////////////////////////////////////////
BBED> p rowdata[3605]
ub1 rowdata[3605]                           @8187     0x00   
 
//////////////////////////////////////////////////////////////////////////////////////
/////////////////////Block Tail Check, 4 bytes////////////////////////////////////////
BBED> p tailchk
ub4 tailchk                                 @8188     0x24500601

说明事宜：
1、tailchk=Lower order two bytes of SCN Base(bas_kcbh) + Block Type(type_kcbh) + SCN Seq(seq_kcbh)
2、块的scn为：scn=wrp_kcbh+bas_kcbh
求scn语句：select to_char(to_number(‘scn’,’xxxxxxxxxx’),’999999999999′) from dual;
3、dba求文件号，块号为：

set serveroutput on
declare
   p_dba   VARCHAR2 (255) :='0x00800212';
   l_str   VARCHAR2 (255) DEFAULT NULL;
BEGIN
    l_str :=
         'datafile# is:'
      || DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'))
      || chr(10)||'datablock is:'
      || DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'));
   dbms_output.put_line(l_str);
END;
发表在 非常规恢复 | 标签为 | 评论关闭