0130compatible optimizer_features_enable

最新推荐文章于 2023-10-09 02:15:42 发布

cuixun7780

最新推荐文章于 2023-10-09 02:15:42 发布

阅读量96

点赞数

[20180130]参数compatible与optimizer_features_enable.txt

--//别人的系统修改compatible参数,导致数据库无法启动.实际上本来要修改optimizer_features_enable参数.
--//换一句话,不要在生产系统使用XX.1的版本.
--//自己做一个测试,说明情况:

1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------- ---------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> create pfile='/tmp/@.ora' from spfile ;
File created.

--//修改/tmp/book.ora 如下:
$ grep 11.2. /tmp/book.ora
*.compatible='11.2.0.3.0'
*.optimizer_features_enable='11.2.0.3'

2.使用参数启动数据库:
SYS@book> startup pfile='/tmp/@.ora'
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
ORA-00201: control file version 11.2.0.4.0 incompatible with ORACLE version 11.2.0.3.0
ORA-00202: control file: '/mnt/ramdisk/book/control01.ctl'

--//实际上compatible的信息已经写入控制文件,数据库文件,redo文件.只能升不能降.

$ grep 11.2. /tmp/book.ora
#*.compatible='11.2.0.3.0'
*.optimizer_features_enable='11.2.0.3'

--//注解缺省是11.2.0.0.0也不行.
SYS@book> startup pfile='/tmp/@.ora'
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
ORA-00201: control file version 11.2.0.4.0 incompatible with ORACLE version 11.2.0.0.0
ORA-00202: control file: '/mnt/ramdisk/book/control01.ctl'

$ grep 11.2. /tmp/book.ora
*.compatible='11.2.0.4.0'
*.optimizer_features_enable='11.2.0.3'

SYS@book> startup pfile='/tmp/@.ora'
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.
Database opened.

SYS@book> show parameter optimizer_features_enable
NAME TYPE VALUE
------------------------- ------ --------
optimizer_features_enable string 11.2.0.3

SYS@book> show parameter compatible
NAME TYPE VALUE
------------------------- ------ ----------
compatible string 11.2.0.4.0

3.bbed探究看看:
BBED> info
File# Name                             Size(blks)
----- ----                             ----------
     1 /mnt/ramdisk/book/system01.dbf            0
     2 /mnt/ramdisk/book/sysaux01.dbf            0
     3 /mnt/ramdisk/book/undotbs01.dbf           0
     4 /mnt/ramdisk/book/users01.dbf             0
     5 /mnt/ramdisk/book/example01.dbf           0
     6 /mnt/ramdisk/book/tea01.dbf               0
     7 /mnt/ramdisk/book/sugar01.dbf             0
   101 /mnt/ramdisk/book/control01.ctl           0
   102 /mnt/ramdisk/book/control02.ctl           0
   201 /mnt/ramdisk/book/temp01.dbf              0
   206 /home/oracle/backup/tea01.dbf             0
   501 /mnt/ramdisk/book/redo01.log              0
   504 /mnt/ramdisk/book/redostb01.log           0

BBED> p /x dba 1,1 kcvfhhdr.kccfhcvn
ub4 kccfhcvn @24 0x0b200400

BBED> p /x dba 6,1 kcvfhhdr.kccfhcvn
ub4 kccfhcvn @24 0x0b200400

--//记录在文件头的偏移量24的位置变量kcvfhhdr.kccfhcvn.
--//不知道这个什么算的0b=>11,后面可是20,....

BBED> dump /v dba 1,1 offset 24 count 8
File: /mnt/ramdisk/book/system01.dbf (1)
Block: 1 Offsets: 24 to 31 Dba:0x00400001
-----------------------------------------------------------------------------------------------------------
0004200b 6e21b74f l .. .n!.O

<32 bytes per line>

BBED> dump /v dba 101,1 offset 24 count 8
File: /mnt/ramdisk/book/control01.ctl (101)
Block: 1 Offsets: 24 to 31 Dba:0x19400001
-----------------------------------------------------------------------------------------------------------
0004200b 6e21b74f l .. .n!.O
<32 bytes per line>

BBED> dump /v dba 501,1 offset 20 count 8
File: /mnt/ramdisk/book/redo01.log (501)
Block: 1 Offsets: 20 to 27 Dba:0x7d400001
-----------------------------------------------------------------------------------------------------------
0004200b 6e21b74f l .. .n!.O
<32 bytes per line>

BBED> dump /v dba 504,1 offset 20 count 8
File: /mnt/ramdisk/book/redostb01.log (504)
Block: 1 Offsets: 20 to 27 Dba:0x7e000001
-----------------------------------------------------------------------------------------------------------
0004200b 6e21b74f l .. .n!.O
<32 bytes per line>

--//控制文件记录也是偏移24,而redo文件记录偏移是20.不知道通过修改这些文件实现降级,还是放弃,这样的需求还是太少,
--//根本不存在.

4.转储也可以确定这些信息:

SYS@book> alter session set events 'immediate trace name controlf level 3';
Session altered.

*** 2018-01-30 10:48:01.432
DUMP OF CONTROL FILES, Seq # 43692 = 0xaaac
V10 STYLE FILE HEADER:
    Compatibility Vsn = 186647552=0xb200400
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
    Activation ID=0=0x0
    Control Seq=43692=0xaaac, File size=652=0x28c
    File Number=0, Blksiz=16384, File Type=1 CONTROL

SYS@book> alter session set events 'immediate trace name FILE_HDRS level 12';
Session altered.

DATA FILE #1:
name #7: /mnt/ramdisk/book/system01.dbf
creation size=0 block size=8192 status=0xe head=7 tail=7 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:1110 scn: 0x0003.1770a7ac 01/30/2018 10:27:39
Stop scn: 0xffff.ffffffff 01/30/2018 10:20:00
Creation Checkpointed at scn: 0x0000.00000007 08/24/2013 11:37:33
thread:0 rba:(0x0.0.0)
...
V10 STYLE FILE HEADER:
    Compatibility Vsn = 186647552=0xb200400
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
    Activation ID=0=0x0
    Control Seq=43686=0xaaa6, File size=98560=0x18100
    File Number=1, Blksiz=8192, File Type=3 DATA

SYS@book> alter system dump logfile '/mnt/ramdisk/book/redo01.log' validate;
System altered.

DUMP OF REDO FROM FILE '/mnt/ramdisk/book/redo01.log'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
VALIDATE ONLY
FILE HEADER:
    Compatibility Vsn = 186647552=0xb200400
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
    Activation ID=1337448558=0x4fb7d86e
    Control Seq=43684=0xaaa4, File size=102400=0x19000
    File Number=1, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000860, SCN 0x000317709d1d-0x000317709d28"
thread: 1 nab: 0x9 seq: 0x0000035c hws: 0x3 eot: 0 dis: 0
resetlogs count: 0x35711eb0 scn: 0x0000.000e2006 (925702)
prev resetlogs count: 0x3121c97a scn: 0x0000.00000001 (1)
Low scn: 0x0003.17709d1d (13278158109) 01/30/2018 09:49:11
Next scn: 0x0003.17709d28 (13278158120) 01/30/2018 09:49:20
Enabled scn: 0x0000.000e2006 (925702) 11/24/2015 09:11:12
Thread closed scn: 0x0003.17709d1d (13278158109) 01/30/2018 09:49:11
Disk cksum: 0xbc9d Calc cksum: 0xbc9d
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 1 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x800000
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
Zero blocks: 8
Format ID is 2
redo log key is 10b5c0538c516f3ffc9b976b8868555e
redo log key flag is 5
Enabled redo threads: 1
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (SYNC): 3Kb in 0.01s => 0.34 Mb/sec
Total redo bytes: 3Kb Longest LWN: 0Kb, reads: 7
Last redo scn: 0x0003.17709d23 (13278158115)
----------------------------------------------