bootstrap连接mysql并修改_修改bootstrap$ – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 134296...

在以前的文章中，我写过通过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执行计划。