[20170808]Spfile is in old pre-11 format and compatible.txt
--//今天检查alert.log,发现如下提示:
Spfile /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilebook.ora is in old pre-11 format and compatible >= 11.0.0;
converting to new H.A.R.D. compliant format.
$ grep 'H.A.R.D' alert_book.log |wc
4 68 652
--//出现4次.google找到如下链接:
https://jhdba.wordpress.com/2008/12/12/asm-11g-compatibility-settings-and-conversion-to-hard-format/
Firstly H.A.R.D. refers to Oracle's Hardware Assisted Resilient Data (H.A.R.D) Initiative which Metalink note 227671.1
summarises as
To use HARD validation, all datafiles and log files are placed on HARD-compliant storage.
The user must also enable the HARD validation feature on the storage, using the vendor-provided interface. When Oracle
writes data to the storage, the storage system validates the data. If it appears to be corrupted, then the write is
rejected with an error.
--//我的数据库没有使用asm,应该不是这个.
http://blog.itpub.net/29154652/viewspace-773560/
从MOS 1352070.1上获知：
When the spfile is created without an instance, the spfile is always created with the lowest compatibility. Only upon
complete startup will the parameter layer know which compatibility to use and it will update the disk structures as
appropriate.
--//噢!就是我可能在一次维护中导致spfile无法启动.因为修改processes=450 ,太大,导致共享池设置太小,无法启动.
Wed Jul 26 10:09:39 2017
ALTER SYSTEM SET processes=450 SCOPE=SPFILE;
Wed Jul 26 10:10:59 2017
Adjusting the default value of parameter parallel_max_servers
from 480 to 420 due to the value of parameter processes (450)
Starting ORACLE instance (normal)
Wed Jul 26 10:12:19 2017
Adjusting the default value of parameter parallel_max_servers
from 480 to 150 due to the value of parameter processes (180)
Starting ORACLE instance (normal)
在没有启动数据库的情况下:
create pfile='/tmp/a.ora' from spfile;
--//改正参数错误processes=180,然后建立spfile.
create spfile from pfile='/tmp/a.ora';
--//下面对比看看2种情况下差别在那里.
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
$ cd /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ cp spfilebook.ora spfilebook.old
2.建立新的spfile覆盖旧的:
SYS@book> create pfile='/tmp/a.ora' from spfile ;
File created.
SYS@book> create spfile from pfile='/tmp/a.ora';
create spfile from pfile='/tmp/a.ora'
*
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance
--//不能这样操作!!
SYS@book> create spfile='/tmp/spfilebook.ora' from pfile='/tmp/a.ora';
File created.
$ cp /tmp/spfilebook.ora /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
/bin/cp: overwrite `/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilebook.ora'? y
3.对比分析:
$ md5sum spfilebook.ora spfilebook.old
f4da0790c9d167036bb297ab0442fa40 spfilebook.ora
c830fa47197fc0e60bfc34fa9136479d spfilebook.old
$ xxd -c 16 spfilebook.ora > /tmp/a1.txt
$ xxd -c 16 spfilebook.old > /tmp/a2.txt
$ diff -Nur /tmp/a1.txt /tmp/a2.txt
--- /tmp/a1.txt 2017-08-08 08:45:29.000000000 +0800
+++ /tmp/a2.txt 2017-08-08 08:45:37.000000000 +0800
@@ -1,10 +1,10 @@
0000000: 4322 0000 0100 0000 0000 0000 0000 0104 C...............
-0000010: b31a 0000 0000 0000 0000 0000 0000 0000 ................
+0000010: b006 0000 0000 0000 0000 0000 0000 0000 ................
0000020: 0000 0000 0000 0000 0000 0000 0900 0000 ................
0000030: 0002 0000 0000 0000 0000 0000 0000 0000 ................
0000040: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000050: 0000 0000 0000 0000 0000 0000 0000 0000 ................
-0000060: 0002 0000 8607 0000 c840 b638 0000 0000 ........菮.8....
+0000060: 0002 0000 8607 0000 c85c b538 0000 0000 ........萛?....
0000070: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000080: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000090: 0000 0000 0000 0000 0000 0000 0000 0000 ................
--//不用生成临时文件,也可以这样写.
$ diff -Nur <( xxd -c 16 spfilebook.ora ) <(xxd -c 16 spfilebook.old)
--- /dev/fd/63 2017-08-08 08:49:24.497471386 +0800
+++ /dev/fd/62 2017-08-08 08:49:24.497471386 +0800
@@ -1,10 +1,10 @@
0000000: 4322 0000 0100 0000 0000 0000 0000 0104 C...............
-0000010: b31a 0000 0000 0000 0000 0000 0000 0000 ................
+0000010: b006 0000 0000 0000 0000 0000 0000 0000 ................
0000020: 0000 0000 0000 0000 0000 0000 0900 0000 ................
0000030: 0002 0000 0000 0000 0000 0000 0000 0000 ................
0000040: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000050: 0000 0000 0000 0000 0000 0000 0000 0000 ................
-0000060: 0002 0000 8607 0000 c840 b638 0000 0000 ........菮.8....
+0000060: 0002 0000 8607 0000 c85c b538 0000 0000 ........萛?....
0000070: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000080: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000090: 0000 0000 0000 0000 0000 0000 0000 0000 ................
4.继续分析:
--//偏移0x60处感觉应该是时间有关的信息.也就是时间stamp.
$ stat spfilebook.ora spfilebook.old
File: `spfilebook.ora'
Size: 4608 Blocks: 16 IO Block: 4096 regular file
Device: 6806h/26630d Inode: 62488583 Links: 1
Access: (0640/-rw-r-----) Uid: ( 502/ oracle) Gid: ( 502/oinstall)
Access: 2017-08-08 08:44:47.000000000 +0800
Modify: 2017-08-08 08:44:26.000000000 +0800
Change: 2017-08-08 08:44:26.000000000 +0800
File: `spfilebook.old'
Size: 4608 Blocks: 16 IO Block: 4096 regular file
Device: 6806h/26630d Inode: 70746113 Links: 1
Access: (0640/-rw-r-----) Uid: ( 502/ oracle) Gid: ( 502/oinstall)
Access: 2017-08-08 08:44:47.000000000 +0800
Modify: 2017-08-08 08:41:02.000000000 +0800
Change: 2017-08-08 08:41:02.000000000 +0800
$ ls -l spfilebook.ora spfilebook.old
-rw-r----- 1 oracle oinstall 4608 2017-08-08 08:41:02 spfilebook.old
-rw-r----- 1 oracle oinstall 4608 2017-08-08 08:44:26 spfilebook.ora
--//注大小头对调:
SYS@book> @ &r/16to10 38b640c8
16 to 10 DEC
------------
951468232
SYS@book> @ &r/stamp 951468232
STAMP STAMP_CONV_TIME
---------- -------------------
951468232 2017-08-08 08:43:52
SYS@book> @ &r/16to10 38b55cc8
16 to 10 DEC
------------
951409864
SYS@book> @ &r/stamp 951409864
STAMP STAMP_CONV_TIME
---------- -------------------
951409864 2017-08-07 16:31:04
--//估计如果修改spfile文件信息,这个位置会变动,应该对应的是时间.如果看建立在/tmp目录下spfilebook.ora就可以发现:
$ stat /tmp/spfilebook.ora
File: `/tmp/spfilebook.ora'
Size: 4608 Blocks: 16 IO Block: 4096 regular file
Device: 6802h/26626d Inode: 10518537 Links: 1
Access: (0640/-rw-r-----) Uid: ( 502/ oracle) Gid: ( 502/oinstall)
Access: 2017-08-08 08:44:26.000000000 +0800
Modify: 2017-08-08 08:43:52.000000000 +0800
Change: 2017-08-08 08:43:52.000000000 +0800
--//Change时间是2017-08-08 08:43:52.000000000 +0800.正好对上.
5.继续分析0x10不同,按照以前分析的惯例,这里应该是检查和.
--//文件大小4608,估计块大小512字节.
$ xxd -c 16 -l 512 spfilebook.ora | cut -c10-50 | xor.sh
....
0000
xor result: 0
--//也说明0x10处是检查和信息.这样看来在没有启动实例的情况2者并没有存在什么不同,为什么会报上面的错误呢?
6.再仔细看提示是否提示转化已经发生了呢?
Spfile /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilebook.ora is in old pre-11 format and compatible >= 11.0.0;
converting to new H.A.R.D. compliant format.
--//怎么会发生4次,难道不是1次完成的吗?难道我以前这样做过4次.而且都是最近的时间.....
SYS@book> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> create spfile='/tmp/spfilebook.ora2' from pfile='/tmp/a.ora';
File created.
$ ls -l /tmp/spfilebook.ora*
-rw-r----- 1 oracle oinstall 4608 2017-08-08 08:43:52 /tmp/spfilebook.ora
-rw-r----- 1 oracle oinstall 4608 2017-08-08 08:52:37 /tmp/spfilebook.ora1
-rw-r----- 1 oracle oinstall 4608 2017-08-08 09:49:56 /tmp/spfilebook.ora2
--//再次对比,可以发现差别很大.
$ diff -Nur <( xxd -c 16 /tmp/spfilebook.ora1 ) <(xxd -c 16 /tmp/spfilebook.ora2) | head -20
--- /dev/fd/63 2017-08-08 09:53:43.128678811 +0800
+++ /dev/fd/62 2017-08-08 09:53:43.129678811 +0800
@@ -1,10 +1,10 @@
-0000000: 4322 0000 0100 0000 0000 0000 0000 0104 C...............
-0000010: ae18 0000 0000 0000 0000 0000 0000 0000 ................
-0000020: 0000 0000 0000 0000 0000 0000 0900 0000 ................
-0000030: 0002 0000 0000 0000 0000 0000 0000 0000 ................
-0000040: 0000 0000 0000 0000 0000 0000 0000 0000 ................
-0000050: 0000 0000 0000 0000 0000 0000 0000 0000 ................
-0000060: 0002 0000 8607 0000 d542 b638 0000 0000 ........誃.8....
+0000000: 0000 0000 0000 0000 0000 0000 0000 0000 ................
+0000010: 0000 0000 0000 0000 0900 0000 0002 0000 ................
+0000020: 0000 0000 0000 0000 0000 0000 0000 0000 ................
+0000030: 0000 0000 0000 0000 0000 0000 0000 0000 ................
+0000040: 0000 0000 0000 0000 0000 0000 0002 0000 ................
+0000050: 8607 0000 4450 b638 0000 0000 0000 0000 ....DP.8........
+0000060: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000070: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000080: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000090: 0000 0000 0000 0000 0000 0000 0000 0000 ................
$ cp /tmp/spfilebook.ora2 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilebook.ora
/bin/cp: overwrite `/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilebook.ora'? y
7.再次启动数据库:
SYS@book> startup
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.
--//也没有报错误在alert文件中..
$ grep 'H.A.R.D' alert_book.log |wc
4 68 652
$ xxd -c 16 -l 128 spfilebook.ora
0000000: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000010: 0000 0000 0000 0000 0900 0000 0002 0000 ................
0000020: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000030: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000040: 0000 0000 0000 0000 0000 0000 0002 0000 ................
0000050: 8607 0000 4450 b638 0000 0000 0000 0000 ....DP.8........
0000060: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000070: 0000 0000 0000 0000 0000 0000 0000 0000 ................
--//spfile文件也没变.前面还都是0.奇怪!!难道问题就在这里吗?
$ xxd -c 16 -l 512 spfilebook.ora | cut -c10-50 | xor.sh
..
xor result: 7D6F
--//检查和也是不对的.
--//修改参数文件看看:
SYS@book> alter system set processes=150 scope=spfile;
System altered.
--//再次看alert提示:
Spfile /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilebook.ora is in old pre-11 format and compatible >= 11.0.0; converting to new H.A.R.D. compliant format.
ALTER SYSTEM SET processes=150 SCOPE=SPFILE;
--//噢!激活了这个提示.只有在修改spfile参数时激活这个提示.
$ xxd -c 16 -l 512 spfilebook.ora | cut -c10-50 | xor.sh
...
0000
xor result: 0
总结:
1.前面的分析存在错误,提示
Spfile /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilebook.ora is in old pre-11 format and compatible >= 11.0.0;
converting to new H.A.R.D. compliant format.
说明转化已经完成,我拿转化后来分析自然不行.
2.我仔细看alert文件.出现错误的地方并没有修改参数的信息.难道是这些参数:
$ strings -t d spfilebook.ora | head
532 book.__db_cache_size=419430400
563 book.__java_pool_size=12582912
594 book.__large_pool_size=29360128
626 book.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
696 book.__pga_aggregate_target=209715200
734 book.__sga_target=637534208
762 book.__shared_io_pool_size=0
791 book.__shared_pool_size=184549376
825 book.__streams_pool_size=0
--//这些隐含参数变动触发了.什么会存在4次,我一直不理解.
3.附上测试脚本:
--做异或计算的脚本在
http://blog.itpub.net/267265/viewspace-2134945/
$ cat ~/bin/xor.sh
#! /bin/bash
# just play , calc xor!!
s='0'
for i in $(cat $1| tr 'a-f' 'A-F')
do
#echo "obase=16;ibase=16; xor($s,$i)"
echo $i
[ $i != '0000' ] && s=$(echo "obase=16;ibase=16; xor($s,$i)" | bc -l ~/bc/logic.bc)
done
echo -e "\nxor result: $s \n"
--//logic.bc 在这里下载phodd.net/gnu-bc/index.html.
4.stamp脚本:
SELECT &&1 stamp,to_date(yyyy||'/'||mm||'/'||dd||' '||hh||':'||mi||':'||ss,'yyyy-mm-dd hh24:mi:ss') stamp_conv_time from (
SELECT &&1
,FLOOR (&&1 / (86400*31*12))+1988 yyyy
,FLOOR (MOD (&&1 / (86400*31),12))+1 mm
,FLOOR (MOD (&&1 / 86400, 31))+1 dd
,FLOOR (MOD (&&1 / 3600, 24)) hh
,FLOOR (MOD (&&1 / 60, 60)) mi
,MOD (&&1, 60) ss
from dual);
5.我仔细回忆一下,最大的可能最rman备份:
--//关闭数据库
$ cp /tmp/spfilebook.ora2 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilebook.ora
--//重新启动后在rman下做备份:
RMAN> backup datafile 6 format '/home/oracle/backup/datafile6_%U';
Starting backup at 2017-08-08 10:29:31
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=80 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=94 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/mnt/ramdisk/book/tea01.dbf
channel ORA_DISK_1: starting piece 1 at 2017-08-08 10:29:33
channel ORA_DISK_1: finished piece 1 at 2017-08-08 10:29:34
piece handle=/home/oracle/backup/datafile6_fnsbcmcd_1_1 tag=TAG20170808T102933 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-08-08 10:29:34
Starting Control File and SPFILE Autobackup at 2017-08-08 10:29:34
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2017_08_08/o1_mf_s_951474574_drl8jgf9_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2017-08-08 10:29:35
--//备份完成后在alert出现如下:
Tue Aug 08 10:29:34 2017
Spfile /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilebook.ora is in old pre-11 format and compatible >= 11.0.0; converting to new H.A.R.D. compliant format
--//看看参数文件:
$ xxd -c 16 -l 512 spfilebook.ora | head -10
0000000: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000010: 0000 0000 0000 0000 0900 0000 0002 0000 ................
0000020: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000030: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000040: 0000 0000 0000 0000 0000 0000 0002 0000 ................
0000050: 8607 0000 4450 b638 0000 0000 0000 0000 ....DP.8........
0000060: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000070: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000080: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000090: 0000 0000 0000 0000 0000 0000 0000 0000 ................
--//还是0开头,终于明白为什么前面会出现4次这样的提示.实际上在备份时要同时做1次备份spfile以及控制文件的备份.
--//转换的实际上是备份文件中的spfile文件,而不是使用正在使用中的spfile文件.
--//但是我发现以后再使用rman备份不在出现上面的提示.除非重启数据库再做rman备份才会出现,这也很好解析我为什么遇到4次.
$ grep 'H.A.R.D' alert_book.log |wc
7 119 1141