月归档:二月 2017

Oracle dul支持Oracle 12.2(12c)

随着oracle 12.2正式版的临近,通过最新版dul(Data UnLoader: 11.2.0.1.1)的测试,发现对于12c的版本支持依旧有问题,虽然作者在版本中较为明确的表示了支持12c但是测试效果不尽人意.
dul 11对oracle 12c的支持(Compatible参数表明支持12c)

[oracle@localhost dul]$ ./dul

Data UnLoader: 11.2.0.1.1 - Internal Only - on Tue Feb 21 23:39:08 2017
with 64-bit io functions and the decompression option

Copyright (c) 1994 2016 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


Within one week you will need a more recent DUL version for this os
DUL: Warning: Compatible is set to 11 Values can be 6|7|8|9|10|11|12

在12.2的版本中运行dul结果如下

[oracle@xifenfei ~]$ ss

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 21 23:54:43 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set lines 150
SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                        0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0


[root@xifenfei dul]# ./dul

Data UnLoader: 11.2.0.1.1 - Internal Only - on Tue Feb 21 23:53:37 2017
with 64-bit io functions and the decompression option

Copyright (c) 1994 2016 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


Within one week you will need a more recent DUL version for this os
Found db_id = 766547632
Found db_name = ORCL12C
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

DUL: Warning: Recreating file "BOOTSTRAP.ctl"
      60 rows unloaded
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 12
 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$
DUL: Warning: Recreating file "OBJ.ctl"
   72655 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
    2162 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
  114785 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
     130 rows unloaded
Reading USER.dat 130 entries loaded
Reading OBJ.dat
DUL: FATAL Error: File OBJ.dat, line 22079: identifier too long

这个报错DUL: FATAL Error: File OBJ.dat比较明显由于dul在加载obj$对象的时候有对象字符太长导致加载失败
dul-error


这个是由于在oracle 12c中已经允许对象名/列名程度为128,而不是以前版本的30,从而引起了这个问题,虽然dul的作者已经在dict.ddl中做了限制,但是目前看尚未生效
dul-clear

特殊处理后的dul可以完美支持12c

[root@xifenfei dul]# ./dul

Data UnLoader: 11.2.0.1.1 - Internal Only - on Wed Feb 22 00:04:40 2017
with 64-bit io functions and the decompression option

Copyright (c) 1994 2016 Bernard van Duijnen All rights reserved.


 Strictly Oracle Internal Use Only


Within one week you will need a more recent DUL version for this os
Found db_id = 766547632
Found db_name = ORCL12C

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

DUL: Warning: Recreating file "BOOTSTRAP.ctl"
      60 rows unloaded
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 12
 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$
DUL: Warning: Recreating file "OBJ.ctl"
   72655 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
    2162 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
  114785 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
     130 rows unloaded
Reading USER.dat 130 entries loaded
Reading OBJ.dat 72655 entries loaded and sorted 72655 entries
Reading TAB.dat 2162 entries loaded
Reading COL.dat 114785 entries loaded and sorted 114785 entries
DUL> unload table sys.undo$;
. unloading table                     UNDO$      21 rows unloaded
DUL> exit

Life is DUL without it

这里是通过特殊处理之后dul才能够支持12c版本,期待作者在后续版本中完善相关工作,在恢复的经验中dul还是一大利器.

发表在 Oracle | 标签为 , , , | 评论关闭

oracle 12.2依旧支持bbed

随着oracle 12.2的全面正式发布,通过测试,在12.2中依旧支持bbed,处理方法完全与11g相同
linux版本

[oracle@xifenfei db_1]$ more /etc/oracle-release 
Oracle Linux Server release 7.2
[oracle@xifenfei db_1]$ uname -a
Linux xifenfei 3.8.13-98.7.1.el7uek.x86_64 #2 SMP Wed Nov 25 13:51:41 PST 2015 x86_64 x86_64 x86_64 GNU/Linux
[oracle@xifenfei db_1]$ 

数据库版本

SQL>  select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                        0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

bbed需要文件

[oracle@xifenfei bbed10_linux_64]$ ls -ltr
total 32
-rw-r--r-- 1 oracle oinstall 10270 Jul 25  2000 bbedus.msg
-rw-r--r-- 1 oracle oinstall  3306 Apr 20  2010 ssbbded.o
-rw-r--r-- 1 oracle oinstall  3976 Apr 20  2010 sbbdpt.o
-rw-r--r-- 1 oracle oinstall  8704 Apr 20  2010 bbedus.msb

编译bbed

[oracle@xifenfei bbed10_linux_64]$ 
[oracle@xifenfei bbed10_linux_64]$ cp *.o $ORACLE_HOME/rdbms/lib/
[oracle@xifenfei bbed10_linux_64]$ cp bbed* $ORACLE_HOME/rdbms/mesg/
[oracle@xifenfei bbed10_linux_64]$ cd $ORACLE_HOME
[oracle@xifenfei db_1]$ make -f ./rdbms/lib/ins_rdbms.mk BBED=./bin/bbed ./bin/bbed

Linking BBED utility (bbed)
rm -f bin/bbed
/u01/app/oracle/product/12.2.0/db_1/bin/orald -o bin/bbed -m64 -z noexecstack -Wl,--disable-new-dtags -L
/u01/app/oracle/product/12.2.0/db_1/rdbms/lib/ -L/u01/app/oracle/product/12.2.0/db_1/lib/ -L/u01/app/oracle/product/12.2.0/db_1/lib/stubs/  /u01/app/oracle/product/12.2.0/db_1/lib/s0main.o 
/u01/app/oracle/product/12.2.0/db_1/rdbms/lib/ssbbded.o /u01/app/oracle/product/12.2.0/db_1/rdbms/lib/sbbdpt.o `cat 
/u01/app/oracle/product/12.2.0/db_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12
 -ldbtools12 -lclntsh -lclntshcore  `cat /u01/app/oracle/product/12.2.0/db_1/lib/ldflags`    -lncrypt12 -lnsgr12
 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnro12 `cat /u01/app/oracle/product/12.2.0/db_1/lib/ldflags`    
-lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnnz12 -lzt12 -lztkg12 -lztkg12 -lclient12 -lnnetd12  
-lvsn12 -lcommon12 -lgeneric12 -lmm -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 
-lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 `cat /u01/app/oracle/product/12.2.0/db_1/lib/ldflags
`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnro12 `cat /u01/app/oracle/product/12.2.0/db_1/lib/ldflags
`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lclient12 -lnnetd12  -lvsn12 -lcommon12 -lgeneric12  
 -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 
-lcore12 -lnls12 -lclient12 -lnnetd12  -lvsn12 -lcommon12 -lgeneric12 -lsnls12 -lnls12  -lcore12 -lsnls12
 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12  
 `cat /u01/app/oracle/product/12.2.0/db_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/12.2.0/db_1/lib -lm   
 `cat /u01/app/oracle/product/12.2.0/db_1/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/12.2.0/db_1/lib

测试bbed

[oracle@xifenfei db_1]$ bbed blocksize=8192 filename=/u01/app/oracle/oradata/orcl12c/system01.dbf
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Fri Feb 17 00:57:56 2017

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

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

BBED> map
 File: /u01/app/oracle/oradata/orcl12c/system01.dbf (0)
 Block: 1                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header

 struct kcvfh, 1248 bytes                   @0       

 ub4 tailchk                                @8188    


BBED> p kcvfh
struct kcvfh, 1248 bytes                    @0       
   struct kcvfhbfh, 20 bytes                @0       
      ub1 type_kcbh                         @0        0x0b
      ub1 frmt_kcbh                         @1        0xa2
      ub2 wrp2_kcbh                         @2        0x0000
      ub4 rdba_kcbh                         @4        0x00400001
      ub4 bas_kcbh                          @8        0x00000000
      ub2 wrp_kcbh                          @12       0x0000
      ub1 seq_kcbh                          @14       0x01
      ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)
      ub2 chkval_kcbh                       @16       0x38d0
      ub2 spare3_kcbh                       @18       0x0000
   struct kcvfhhdr, 76 bytes                @20      
      ub4 kccfhswv                          @20       0x00000000
      ub4 kccfhcvn                          @24       0x0c200000  <-------数据文件版本
      ub4 kccfhdbi                          @28       0x2db096b0
      text kccfhdbn[0]                      @32      O
      text kccfhdbn[1]                      @33      R
      text kccfhdbn[2]                      @34      C
      text kccfhdbn[3]                      @35      L
      text kccfhdbn[4]                      @36      1
      text kccfhdbn[5]                      @37      2
      text kccfhdbn[6]                      @38      C
      text kccfhdbn[7]                      @39       
      ub4 kccfhcsq                          @40       0x00000714
      ub4 kccfhfsz                          @44       0x00019500
      s_blkz kccfhbsz                       @48       0x00
      ub2 kccfhfno                          @52       0x0001
      ub2 kccfhtyp                          @54       0x0003
      ub4 kccfhacid                         @56       0x00000000
      ub4 kccfhcks                          @60       0x00000000

通过测试,bbed可以完美支持12.2的数据文件,而且文件头格式无大变化

发表在 Oracle | 标签为 , | 评论关闭

修改bootstrap$影响数据库执行计划

在以前的文章中,我写过通过ue修改oracle二进制文件实现数据库启动的sql执行计划,这里再次提供另外一种方法,通过修改bootstrap$表实现数据库启动sql执行计划。这里试验的是数据库不走i_undo1 index。[此方法危害性巨大仅供测试]
跟踪数据库正常启动过程

[oracle@localhost .oradata]$ ss

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 4 23:07:41 2017

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2421825536 bytes
Fixed Size                  2215744 bytes
Variable Size            1828716736 bytes
Database Buffers          570425344 bytes
Redo Buffers               20467712 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
Statement processed.
SQL> oradebug TRACEFILE_NAME
/opt/oracle/diag/rdbms/test/test/trace/test_ora_19003.trc
SQL> alter database open;

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

分析10046 trace文件

PARSING IN CURSOR #2 len=196 dep=1 uid=0 oct=9 lid=0 tim=1486220893978359 hv=24291558 ad='ef95ff70' sqlid='fqkyj700r5a76'
CREATE UNIQUE INDEX I_UNDO1 ON UNDO$(US#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE 
(  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 34 EXTENTS (FILE 1 BLOCK 320))
END OF STMT
PARSE #2:c=0,e=565,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=241391319,tim=1486220893978358
EXEC #2:c=0,e=123,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=241391319,tim=1486220893978566
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='INDEX BUILD UNIQUE I_UNDO1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=0)'
STAT #2 id=3 cnt=0 pid=2 pos=1 obj=15 op='TABLE ACCESS FULL UNDO$ (cr=0 pr=0 pw=0 time=0 us)'
CLOSE #2:c=0,e=5,dep=1,type=0,tim=1486220893978690

…………

PARSING IN CURSOR #5 len=142 dep=1 uid=0 oct=3 lid=0 tim=1486220894169144 hv=361892850 ad='ef934cb0' sqlid='7bd391hat42zk'
select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
END OF STMT
PARSE #5:c=0,e=474,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=0,tim=1486220894169143
BINDS #5:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f4f4556b0a8  bln=22  avl=02  flg=05
  value=1
EXEC #5:c=1000,e=802,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=906473769,tim=1486220894170055
WAIT #5: nam='db file sequential read' ela= 1018 file#=1 block#=321 blocks=1 obj#=34 tim=1486220894171138
WAIT #5: nam='db file sequential read' ela= 2620 file#=1 block#=225 blocks=1 obj#=15 tim=1486220894173817
FETCH #5:c=0,e=3770,p=2,cr=2,cu=0,mis=0,r=1,dep=1,og=3,plh=906473769,tim=1486220894173859
STAT #5 id=1 cnt=1 pid=0 pos=1 obj=15 op='TABLE ACCESS BY INDEX ROWID UNDO$ (cr=2 pr=2 pw=0 time=0 us)'
STAT #5 id=2 cnt=1 pid=1 pos=1 obj=34 op='INDEX UNIQUE SCAN I_UNDO1 (cr=1 pr=1 pw=0 time=0 us)'
CLOSE #5:c=0,e=9,dep=1,type=0,tim=1486220894173944

这些trace文件比较明显的展示了数据库在启动过程中,先是create index i_undo1,然后from undo$ where us#=:1走I_UNDO1 index访问。

删除i_undo1 在bootstrap$中信息

SQL> select line#,obj#,sql_text from bootstrap$ where sql_text like '%I_UNDO1%';

     LINE#       OBJ#
---------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
        34         34
CREATE UNIQUE INDEX I_UNDO1 ON UNDO$(US#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STO
RAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
OBJNO 34 EXTENTS (FILE 1 BLOCK 320))


SQL> DELETE FROM BOOTSTRAP$ WHERE OBJ#=34;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

再次跟踪数据库启动

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 2421825536 bytes
Fixed Size                  2215744 bytes
Variable Size            1828716736 bytes
Database Buffers          570425344 bytes
Redo Buffers               20467712 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
Statement processed.
SQL> oradebug TRACEFILE_NAME
/opt/oracle/diag/rdbms/test/test/trace/test_ora_19205.trc
SQL> alter database Open;

Database altered.

再次分析10046 trace文件

PARSING IN CURSOR #5 len=142 dep=1 uid=0 oct=3 lid=0 tim=1486221250365628 hv=361892850 ad='ef935ce0' sqlid='7bd391hat42zk'
select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
END OF STMT
PARSE #5:c=1000,e=536,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=0,tim=1486221250365627
BINDS #5:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f89004340a8  bln=22  avl=02  flg=05
  value=1
EXEC #5:c=1000,e=727,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=3995376916,tim=1486221250366467
WAIT #5: nam='db file sequential read' ela= 12 file#=1 block#=224 blocks=1 obj#=15 tim=1486221250366534
WAIT #5: nam='db file sequential read' ela= 10 file#=1 block#=225 blocks=1 obj#=15 tim=1486221250366606
FETCH #5:c=0,e=147,p=2,cr=3,cu=0,mis=0,r=1,dep=1,og=3,plh=3995376916,tim=1486221250366647
STAT #5 id=1 cnt=1 pid=0 pos=1 obj=15 op='TABLE ACCESS FULL UNDO$ (cr=3 pr=2 pw=0 time=0 us)'
CLOSE #5:c=0,e=39,dep=1,type=0,tim=1486221250366717

这次的trace文件中已经没有了create index i_undo1,而且对于undo$ where us#=:1的访问是通过全表扫描undo$来实现的。通过这里已经清晰证明通过修改bootstrap$表内容,可以影响数据库启动的sql执行计划。

发表在 Oracle | 标签为 , | 评论关闭