oracle 12.2依旧支持bbed

发表于 2017 年 2 月 17 日 由 惜分飞

随着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$影响数据库执行计划

发表于 2017 年 2 月 4 日 由 惜分飞

在以前的文章中，我写过通过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 | 标签为 , | 评论关闭

oracle database 12.2 发布计划表

发表于 2017 年 2 月 1 日 由 惜分飞

在无穷的等待中，终于看到了官方给出来了准确的oracle 12c r2的发布时间表：2017年3月15日可以和Linux与Solaris平台的12.2见面了，aix、hp、win需要等到2017年第二个季度。

Release Dates (base and patch sets)

Platform 12.2.0.1 12.1.0.211 12.1.0.12,12 11.2.0.410 11.2.0.3 11.2.0.2 11.2.0.12 11.1.0.71 10.2.0.53 10.2.0.44 10.1.0.5

Oracle Public Cloud Releases
Exadata Express Cloud Service 18-Sep-2016 N/A
Database Cloud Service 4-Nov-2016 Sep-2014 N/A Sep-2014 N/A
Exadata Cloud Service 4-Nov-2016 Oct-2015 N/A Oct-2015 N/A
Exadata Cloud Machine 1HCY2017 Dec-2016 N/A
Oracle Cloud Machine 1HCY2017 Aug-2016 N/A

On-Premises Engineered Systems Same software as released for other platforms but tested on Engineered Systems
Oracle Database Appliance 1HCY2017 Apr 2014 N/A Oct 2013
Exadata 15-Feb-2017 Oct 2014 Mar 2013 Nov 2013
Supercluster 15-Feb-2017 Nov 2014 Apr 2014 Dec 2013
MiniCluster (Solaris SPARC) 1HCY2017

On-Premises Server Releases (includes client)
Linux x86 Not planned Not planned Not planned 28-Aug-2013 23-Sep-2011 13-Sep-2010 1-Sep-2009 18-Sep-2008 30-Apr-2010 22-Feb-2008 30-Jan-2006
Linux x86-64 15-Mar-2017 22-Jul-2014 25-Jun-2013 27-Aug-2013 23-Sep-2011 13-Sep-2010 1-Sep-2009 18-Sep-2008 30-Apr-2010 17-Mar-2008 24-Feb-2006
Oracle Solaris SPARC (64-bit) 15-Mar-2017 22-Jul-2014 25-Jun-2013 29-Aug-2013 1-Oct-2011 24-Sep-2010 6-Nov-2009 06-Oct-2008 19-May-2010 30-Apr-2008 05-Feb-2006
Oracle Solaris x86-64 (64-bit) 15-Mar-2017 22-Jul-2014 25-Jun-2013 29-Aug-2013 1-Oct-2011 24-Sep-2010 25-Nov-2009 Not planned 19-May-2010 13-Nov-2008 Not planned
Microsoft Windows x64 (64-bit) Q2CY2017 25-Sep-2014 9-Jul-2013 25-Oct-2013 11-Nov-2011 15-Dec-2010 2-Apr-2010 13-Nov-2008 27-Jul-2010 16-May-2008 Not planned
HP-UX Itanium9 Q2CY2017 14-Nov-2014 9-Jan-2014 10-Oct-2013 29-Oct-2011 19-Oct-2010 22-Dec-2009 06-Oct-2008 3-Jun-2010 30-Apr-2008 07-Jun-2006
HP-UX PA-RISC (64-bit)
See footnote 8 below		 Platform desupported 8 Platform desupported 8 Platform desupported 8 2-Jan-2014 16-Feb-2012 15-Mar-2011 20-May-2010 11-Nov-2008 15-Dec-2010 02-Jun-2008 05-Feb-2006
IBM AIX on POWER Systems Q2CY2017 14-Nov-2014 9-Jan-2014 10-Oct-2013 29-Oct-2011 19-Oct-2010 22-Dec-2009 06-Oct-2008 3-Jun-2010 15-May-2008 05-Feb-2006
IBM Linux on System z Q2CY2017 14-Nov-2014 9-Jan-2014 9-Jan-2014 1-Dec-2011 30-Mar-2011 Not planned Not planned 3-Jan-2011 16-Dec-2008 26-Aug-2006
Microsoft Windows (32-bit) Not planned Not planned Not planned 25-Oct-2013 11-Nov-2011 15-Dec-2010 5-Apr-2010 10-Oct-2008 19-Jul-2010 17-Mar-2008 13-Feb-2006
Platform 12.2.0.1 12.1.0.211 12.1.0.12,12 11.2.0.410 11.2.0.3 11.2.0.2 11.2.0.12 11.1.0.71 10.2.0.53 10.2.0.44 10.1.0.5

Instant Client-Only Releases
Apple Mac OS X (Intel) TBD Planned Not planned 20-Apr-2014
Download		 31-Jan-2013
Instant Client only		 Not planned Not planned Not planned Sched TBA 10-April-2009
Single Instance only		 Not planned
IBM Linux on POWER (Big Endian)  18-Dec-2015 (download) 4-Dec-2014
IBM Linux on POWER (Little Endian) 18-Dec-2015 (download)

Older Server Releases
Apple Mac OS X (PowerPC)
Platform desupported
 08-Jan-2007
HP OpenVMS Alpha
Platform desupported
 31-Oct-2012 15-Dec-2008 15-Feb-2008
HP OpenVMS Itanium 23-Jul-2015 31-Oct-2012 15-Dec-2008 Not planned
HP Tru64 UNIX
Platform desupported
 21-Apr-2011 20-Feb-2009 18-Oct-2006
IBM Linux on POWER
Platform desupported (see Doc ID 1310584.1)
 17-Mar-2011 9-Jan-2009 Not planned
IBM z/OS on System z
Platform desupported (see Doc ID 461234.1)
 26-Oct-2012 Not planned 06-Mar-2006
Linux Itanium9
Platform desupported (see Doc ID 1130325.1)
 17-Mar-2011 24-Sep-2008 30-Apr-2006
Microsoft Windows Itanium (64-bit) 9
Platform desupported (see Doc ID 1307745.1)
 12-May-2011 2-Feb-2009 30-Jan-2006
Oracle Solaris x86 (32-bit)
Platform desupported
 14-Nov-2008
Last patch set for this platform		 18-Jun-2006
Platform 12.2.0.1 12.1.0.211 12.1.0.12,12 11.2.0.410 11.2.0.3 11.2.0.2 11.2.0.12 11.1.0.71 10.2.0.53 10.2.0.44 10.1.0.5

Sched TBA = Schedule To Be Announced

DD-MMM-YYYY: Available, date shown is when the patch set was made available on My Oracle Support/MetaLink

1H or 2H CYyyyy: Date falls within the 1st half (six months) or 2nd half of Calendar Year. For example 1H CY2009 means “some time within the first six months of 2009″.

Qn CYyyyy: Date falls within the nth Quarter (3 month period) of the Calendar Year specified. For example Q2 CY 2009 means “sometime within the second quarter of 2009, ie between April and June 2009″.

Unsupported platform - means that no further Database releases will be ported to this platform

Patching for previous release ends: explained in next section.

参考：lease Schedule of Current Database Releases (文档 ID 742060.1)

发表在 Oracle | 评论关闭