[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)
----------------------------------------------
