分类目录归档：Oracle

系统中数据文件第一个数据块和oracle 中第一个数据块关系

发表于 2012 年 5 月 29 日由惜分飞

数据文件第一个数据块到底有没有纳入数据块的数据块计算中,也就是我们通常所说的rdba(file#,block),是否真的是从数据文件的第一个数据块开始计算的？下面通过实验验证
相关信息和准备工作

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.xifenfei.com" from dual;

www.xifenfei.com
-------------------
2012-05-29 19:39:48

SQL> select name,block_size from v$datafile where file#=9;

NAME                                                         BLOCK_SIZE
------------------------------------------------------------ ----------
/u01/oracle/oradata/xifenfei/users01.dbf                           8192


--dd出来数据文件第一和第二个数据块
[oracle@xifenfei ~]$ dd if=/u01/oracle/oradata/xifenfei/users01.dbf of=user.01 bs=8192 count=1
1+0 records in
1+0 records out
[oracle@xifenfei ~]$ dd if=/u01/oracle/oradata/xifenfei/users01.dbf of=user.02 bs=8192 count=1 skip=1
1+0 records in
1+0 records out

[oracle@xifenfei ~]$ ll user.*
-rw-r--r--  1 oracle oinstall 8192 May 26 04:43 user.01
-rw-r--r--  1 oracle oinstall 8192 May 26 04:44 user.02

bbed验证

[oracle@xifenfei ~]$ bbed password=blockedit blocksize=8192  listfile=/home/oracle/bbed_new.file mode=edit

BBED: Release 2.0.0.0.0 - Limited Production on Sat May 26 04:56:49 2012

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

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

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /u01/oracle/oradata/xifenfei/users01.dbf                             0
     2  /home/oracle/user.01                                                 0
     3  /home/oracle/user.02                                                 0

--users01.dbf(完整数据文件,第一个数据块)
BBED> set file 1
        FILE#           1

BBED> set block 1
        BLOCK#          1

BBED> d /v count 128
 File: /u01/oracle/oradata/xifenfei/users01.dbf (1)
 Block: 1       Offsets:    0 to  127  Dba:0x00400001
-------------------------------------------------------
 0b020000 01004002 00000000 00000104 l ......@.........
 7f4b0000 00002009 00000008 cdb41453 l .K.... ........S
 58494645 4e464549 c7010000 800c0000 l XIFENFEI........
 00200000 09000300 00000000 00000000 l . ..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 47180000 00000000 cf4d851e l ....G........M..
 8f40512e 78ab0200 00000000 00000000 l .@Q.x...........

 <16 bytes per line>

--直接设置file 2错误(后续提供其他方法)
BBED> set file 2
BBED-00307: incorrect blocksize (8192) or truncated file

--查看users01.dbf(第二个数据块)
BBED> set file 3
        FILE#           3

BBED> set block 1
        BLOCK#          1

BBED> d /v count 128
 File: /home/oracle/user.02 (3)
 Block: 1       Offsets:    0 to  127  Dba:0x00c00001
-------------------------------------------------------
 0b020000 01004002 00000000 00000104 l ......@.........
 7f4b0000 00002009 00000008 cdb41453 l .K.... ........S
 58494645 4e464549 c7010000 800c0000 l XIFENFEI........
 00200000 09000300 00000000 00000000 l . ..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 47180000 00000000 cf4d851e l ....G........M..
 8f40512e 78ab0200 00000000 00000000 l .@Q.x...........

 <16 bytes per line>

--查看users01.dbf(真正第一个数据块)
BBED> set filename 'user.01'
        FILENAME        user.01

BBED> d /v count 128
 File: user.01 (0)
 Block: 1       Offsets:    0 to  127  Dba:0x00000000
-------------------------------------------------------
 00020000 00200000 800c0000 5d5c5b5a l ..... ......]\[Z
 00000000 86280000 00000000 00000000 l .....(..........
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................

 <16 bytes per line>

通过这个对比可以知道:当我们直接使用bbed查看数据块内容的时候,自动屏蔽了数据文件上真正的第一个数据块.其实block 1是数据文件上的第二个数据块

hexdump验证

--users01.dbf(完整文件)
[oracle@xifenfei ~]$ hexdump -C /u01/oracle/oradata/xifenfei/users01.dbf|head -20
00000000  00 02 00 00 00 20 00 00  80 0c 00 00 5d 5c 5b 5a  |..... ......]\[Z|
00000010  00 00 00 00 86 28 00 00  00 00 00 00 00 00 00 00  |.....(..........|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002000  0b 02 00 00 01 00 40 02  00 00 00 00 00 00 01 04  |......@.........|
00002010  7f 4b 00 00 00 00 20 09  00 00 00 08 cd b4 14 53  |.K.... ........S|
00002020  58 49 46 45 4e 46 45 49  c7 01 00 00 80 0c 00 00  |XIFENFEI........|
00002030  00 20 00 00 09 00 03 00  00 00 00 00 00 00 00 00  |. ..............|
00002040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002060  00 00 00 00 47 18 00 00  00 00 00 00 cf 4d 85 1e  |....G........M..|
00002070  8f 40 51 2e 78 ab 02 00  00 00 00 00 00 00 00 00  |.@Q.x...........|
00002080  00 00 00 00 00 00 00 00  00 00 04 00 58 0d 0b c0  |............X...|
00002090  2c 0b 00 00 5a ea be 2e  01 00 aa bd 15 00 00 00  |,...Z...........|
000020a0  02 00 00 00 10 00 ff bf  02 00 00 00 00 00 00 00  |................|
000020b0  5a 00 00 00 4f ea be 2e  59 00 00 00 00 00 00 00  |Z...O...Y.......|
000020c0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
000020f0  00 00 00 00 09 00 00 00  05 00 55 53 45 52 53 00  |..........USERS.|
00002100  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|

--users01.dbf(第一个数据块文件)
[oracle@xifenfei ~]$ hexdump -C user.01
00000000  00 02 00 00 00 20 00 00  80 0c 00 00 5d 5c 5b 5a  |..... ......]\[Z|
00000010  00 00 00 00 86 28 00 00  00 00 00 00 00 00 00 00  |.....(..........|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002000

--users01.dbf(第二个数据块文件)
[oracle@xifenfei ~]$ hexdump -C user.02|head -20
00000000  0b 02 00 00 01 00 40 02  00 00 00 00 00 00 01 04  |......@.........|
00000010  7f 4b 00 00 00 00 20 09  00 00 00 08 cd b4 14 53  |.K.... ........S|
00000020  58 49 46 45 4e 46 45 49  c7 01 00 00 80 0c 00 00  |XIFENFEI........|
00000030  00 20 00 00 09 00 03 00  00 00 00 00 00 00 00 00  |. ..............|
00000040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00000060  00 00 00 00 47 18 00 00  00 00 00 00 cf 4d 85 1e  |....G........M..|
00000070  8f 40 51 2e 78 ab 02 00  00 00 00 00 00 00 00 00  |.@Q.x...........|
00000080  00 00 00 00 00 00 00 00  00 00 04 00 58 0d 0b c0  |............X...|
00000090  2c 0b 00 00 5a ea be 2e  01 00 aa bd 15 00 00 00  |,...Z...........|
000000a0  02 00 00 00 10 00 ff bf  02 00 00 00 00 00 00 00  |................|
000000b0  5a 00 00 00 4f ea be 2e  59 00 00 00 00 00 00 00  |Z...O...Y.......|
000000c0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
000000f0  00 00 00 00 09 00 00 00  05 00 55 53 45 52 53 00  |..........USERS.|
00000100  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000110  00 00 00 00 00 00 00 00  09 00 00 00 00 00 00 00  |................|
00000120  00 00 00 00 f9 e9 be 2e  00 00 00 00 00 00 00 00  |................|
00000130  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|

通过hexdump对三个文件的对比可以知道users01.dbf的头两个数据文件确实是由第一和第二个数据块组成.然后结合上面bbed dump出来的结果.可以再次证明数据文件第一个数据块,不能被bbed识别(从第二个数据文件开始)

实验总结
我们的数据文件其实是从文件的第二个数据块开始记录起(该数据块为block 1).也就是说系统的数据块和oracle中的rdba标示的数据块不是一致.而是系统数据块比oracle数据块多1.
因这个原因解释了以前的一个疑问:Oracle数据文件大小的限制为什么指定数据文件最大值为(2^22-1*block_size),而不是根据rowid的2^22*block_size
关于类此问题在windows验证请见:在UltraEdit中定位数据文件内容

发表在 Oracle | 一条评论

拷贝windows中datafile header方法(ocopy)

发表于 2012 年 5 月 27 日由惜分飞

在很多时候,我们需要对数据文件的头部进行分析,但是因为人不在本地,数据文件本身很大,网络又不好.这个时候我们可能要求对方传过来文件文件的头部几M即可.在unix/linux中可以使用dd实现该需求;在win中可以使用ocopy实现该需求.dd实现请参考:dd操作数据文件;这里讲win下面实现方法:
ocopy语法

D:\>ocopy
OCOPY v2.0 - Copyright 1989-1993 Oracle Corp.  All rights reserved.
Usage:
    ocopy from_file [to_file [a | size_1 [size_n]]]
    ocopy -b from_file to_drive
    ocopy -r from_drive to_dir

ocopy拷贝数据文件header

D:\>ocopy  E:\oracle\oradata\xifenfei\SYSAUX01.DBF d:\sysaux.dbf 20480 1
D:\SYSAUX.DBF
OCOPY - Write error.
--忽略(未找到原因)

D:\>dir sysaux*
 驱动器 D 中的卷没有标签。
 卷的序列号是 000B-FBCB

 D:\ 的目录

2012/05/07  22:28             1,024 SYSAUX.DB2
2012/05/07  22:28             1,024 SYSAUX.DB3
2012/05/07  22:28             1,024 SYSAUX.DB4
2012/05/07  22:28             1,024 SYSAUX.DB5
2012/05/07  22:28             1,024 SYSAUX.DB6
2012/05/07  22:28             1,024 SYSAUX.DB7
2012/05/07  22:28             1,024 SYSAUX.DB8
2012/05/07  22:28             1,024 SYSAUX.DB9
2012/05/07  22:28        20,971,520 SYSAUX.DBF
               9 个文件     20,979,712 字节
               0 个目录 28,771,282,944 可用字节

--SYSAUX.DBF是我们需要的文件

上传到linux中bbed验证

[oracle@xifenfei ~]$ bbed
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Fri May 25 08:31:12 2012

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

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

BBED> set filename '/home/oracle/sysaux.dbf'
        FILENAME        /home/oracle/sysaux.dbf

BBED> set blocksize 8192
        BLOCKSIZE       8192

--从win中拷贝的数据库,第一个block非bbed有效块

BBED> set block 2
        BLOCK#          2

BBED> map
 File: /home/oracle/sysaux.dbf (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header

 struct kcvfh, 360 bytes                    @0       

 ub4 tailchk                                @8188

BBED> map /v
 File: /home/oracle/sysaux.dbf (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header

 struct kcvfh, 360 bytes                    @0       
    struct kcvfhbfh, 20 bytes               @0       
    struct kcvfhhdr, 76 bytes               @20      
    ub4 kcvfhrdb                            @96      
    struct kcvfhcrs, 8 bytes                @100     
    ub4 kcvfhcrt                            @108     
    ub4 kcvfhrlc                            @112     
    struct kcvfhrls, 8 bytes                @116     
    ub4 kcvfhbti                            @124     
    struct kcvfhbsc, 8 bytes                @128     
    ub2 kcvfhbth                            @136     
    ub2 kcvfhsta                            @138     
    struct kcvfhckp, 36 bytes               @140     
    ub4 kcvfhcpc                            @176     
    ub4 kcvfhrts                            @180     
    ub4 kcvfhccc                            @184     
    struct kcvfhbcp, 36 bytes               @188     
    ub4 kcvfhbhz                            @224     
    struct kcvfhxcd, 16 bytes               @228     
    word kcvfhtsn                           @244     
    ub2 kcvfhtln                            @248     
    text kcvfhtnm[30]                       @250     
    ub4 kcvfhrfn                            @280     
    struct kcvfhrfs, 8 bytes                @284     
    ub4 kcvfhrft                            @292     
    struct kcvfhafs, 8 bytes                @296     
    ub4 kcvfhbbc                            @304     
    ub4 kcvfhncb                            @308     
    ub4 kcvfhmcb                            @312     
    ub4 kcvfhlcb                            @316     
    ub4 kcvfhbcs                            @320     
    ub2 kcvfhofb                            @324     
    ub2 kcvfhnfb                            @326     
    ub4 kcvfhprc                            @328     
    struct kcvfhprs, 8 bytes                @332     
    struct kcvfhprfs, 8 bytes               @340     
    ub4 kcvfhtrt                            @356     

 ub4 tailchk                                @8188  
--数据块拷贝出来正常

发表在 Oracle, 操作系统 | 一条评论

使用dbms_metadata.get_ddl出现ORA-31605错误

发表于 2012 年 5 月 26 日由惜分飞

使用dbms_metadata.get_ddl出现ORA-31605错误

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.xifenfei.com" from dual;

www.xifenfei.com
-------------------
2012-05-26 23:10:22

SQL> select dbms_metadata.get_ddl('TABLE','XFF_IOT','CHF1') from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLResetAllVars in routine kuxslResetParams:
LPX-1: NULL pointer
ORA-06512: at "SYS.UTL_XML", line 0
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3320
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4148
ORA-06512: at "SYS.DBMS_METADATA", line 458
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

no rows selected

错误原因
dbms_metadata.get_ddl需要调用Oracle dictionary table “sys.metastylesheet.”中的XSL stylesheets,但是由于某种原因,使得调用失败,出现上述错误.因为该错误可能有:
1.XSL stylesheets没有安装
2.使用alter database 修改数据库字符集(本库是因为昨天修改字符集导致)

解决办法(sys用户执行)
1.在10g及其以上版本中(不带参数)

SQL> exec dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.

2.在9i版本中(带dir参数)

SQL> exec dbms_metadata_util.load_stylesheets('/u01/oracle/9.2.0/db_1/rdbms/xml/xsl');

PL/SQL procedure successfully completed.

SQL> select dbms_metadata.get_ddl('TABLE','XFF_IOT','CHF1') from dual;

DBMS_METADATA.GET_DDL('TABLE','XFF_IOT','CHF1')
--------------------------------------------------------------------------------

  CREATE TABLE "CHF1"."XFF_IOT"
   (    "ID" NUMBER,
        "NAME" VARCHAR2(30),
         CONSTRAINT "CHF_IOT_ID#_PK" PRIMARY KEY ("ID") ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
 PCTTHRESHOLD 50


DBMS_METADATA.GET_DDL('TABLE','XFF_IOT','CHF1')
--------------------------------------------------------------------------------

发表在 ORA-xxxxx | 标签为 ORA-31605 | 评论关闭

17813235971

QQ 咨询

分类目录归档：Oracle

系统中数据文件第一个数据块和oracle 中第一个数据块关系

拷贝windows中datafile header方法(ocopy)

使用dbms_metadata.get_ddl出现ORA-31605错误

文章分类

最近发表

分类目录归档：Oracle

系统中数据文件第一个数据块和oracle 中第一个数据块关系

拷贝windows中datafile header方法(ocopy)

使用dbms_metadata.get_ddl出现ORA-31605错误

标签云

文章分类

最近发表