在Oracle 11.2的數據庫中建表時遇到 RESULT_CACHE (MODE DEFAULT) ORA-00922: missing or invalid option
hostdr:[/home/oracle]$sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 9 12:52:11 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> CREATE TABLE USERA."TABLE_NAME_AA" 2 ( 3 C1 CHAR(8 BYTE) DEFAULT ' ', 4 C2 CHAR(2 BYTE) DEFAULT ' ', 5 C3 NUMBER(12) DEFAULT 0, 6 C4 NUMBER(16) DEFAULT 0, 7 C5 NUMBER(16) DEFAULT 0, 8 C6 NUMBER(12) DEFAULT 0, 9 C7 NUMBER(16) DEFAULT 0, 10 C8 NUMBER(16) DEFAULT 0, 11 C9 NUMBER(12) DEFAULT 0, 12 C10 NUMBER(16) DEFAULT 0, 13 C11 NUMBER(16) DEFAULT 0 14 ) 15 TABLESPACE USERS 16 RESULT_CACHE (MODE DEFAULT) 17 PCTUSED 0 18 PCTFREE 10 19 INITRANS 1 20 MAXTRANS 255 21 STORAGE ( 22 INITIAL 64K 23 NEXT 1M 24 MINEXTENTS 1 25 MAXEXTENTS UNLIMITED 26 PCTINCREASE 0 27 BUFFER_POOL DEFAULT 28 FLASH_CACHE DEFAULT 29 CELL_FLASH_CACHE DEFAULT 30 ) 31 LOGGING 32 NOCOMPRESS 33 NOCACHE 34 NOPARALLEL 35 MONITORING 36 / RESULT_CACHE (MODE DEFAULT) * ERROR at line 16: ORA-00922: missing or invalid option SQL>
背景介紹：
此db是11.2.0.3.8的linux下的單機，是OGG的備庫，OGG的主庫是11.2.0.3.8下的linux下的rac。這兩個db通過OGG進行災備。最初發現“ORA-00922: missing or invalid option”錯誤，就是因為OGG備端上的rep進程abend，abend時的rpt（report）如下：
2015-05-18 10:14:14 INFO OGG-01407 Setting current schema for DDL operation to [USERA].
2015-05-18 10:14:15 INFO OGG-01408 Restoring current schema for DDL operation to [goldengate].
2015-05-18 10:15:30 INFO OGG-01407 Setting current schema for DDL operation to [USERA].
Source Context :
SourceModule : [ggapp.ddl]
SourceID : [/scratch/mmar/view_storage/mmar_20199012/oggcore/OpenSys/src/gglib/ggapp/ddlrep.c]
SourceFunction : [DDLREP_handleDDLError(const UString &, const UString &, const UString &, UString &, int, RepConfig_t *, const UString &, const UString &, const UStr
ing &, CDBObjName<7> &, CDBObjName<1> &, long *)]
SourceLine : [684]
2015-05-18 10:15:30 ERROR OGG-00519 Fatal error executing DDL replication: error
[Error code [922], ORA-00922: ^Z^Z^Z^Z^Z^Z^Z SQL CREATE TABLE USERA."TABLE_NAME_AA"
(
C1 CHAR(8 BYTE) DEFAULT ' ',
C2 CHAR(2 BYTE) DEFAULT ' ',
C3 NUMBER(12) DEFAULT 0,
C4 NUMBER(16) DEFAULT 0,
C5 NUMBER(16) DEFAULT 0,
C6 NUMBER(12) DEFAULT 0,
C7 NUMBER(16) DEFAULT 0,
C8 NUMBER(16) DEFAULT 0,
C9 NUMBER(12) DEFAULT 0,
C10 NUMBER(16) DEFAULT 0,
C11 NUMBER(16) DEFAULT 0
)
TABLESPACE USERS
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING /* GOLDENGATE_DDL_REPLICATION */], no error handler present.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
以下的報錯忽略。
看到OGG的rep進程報錯，一開始還以為是ogg的問題，後來就從上面單獨截取出create 腳本，放在sqlplus裡邊跑,結果還是報錯（就是本文一開始可以看到的），那就可以說是Oracle database 的問題了，不是OGG的問題。
後來仔細想了想ogg主庫和ogg備庫的差異，ogg主庫是dbca新創建的，ogg備庫是從10.2.0.5升級升上來的。想到這裡，有經驗的DBA就會立即去檢查OGG備庫的compatible參數，檢查的結果是compatible參數值為10.2.0.5.於是就要求修改該參數，修改為11.2.0.0.0（跟主庫一樣），當然，修改此參數有幾個注意點：
1. 修改compatible參數是重啟數據庫生效。
2. 一旦修改完成此參數後（指重啟數據庫後），就不能降低此參數到原來的值，否則實例到mount狀態時會報如下的錯誤：
ORA-00201: control file version 11.2.0.0.0 incompatible with ORACLE version 10.2.0.5 ORA-00202: control file: 'D:\IM\CONTROLFILE\CONTROL01.CTL'
本案例的知識點：
1. 查看db是dbca新建的還是upgrade上來的方法：
SQL> set pages 100 lines 120 SQL> col action format a16 SQL> col namespace format a10 SQL> col version format a10 SQL> col comments format a26 SQL> col action_time format a30 SQL> col bundle_series format a15 SQL> set line 200 SQL> alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff'; SQL> select * from dba_registry_history; ACTION_TIME ACTION NAMESPACE VERSION ID BUNDLE_SERIES COMMENTS ------------------------------ ---------------- ---------- ---------- ---------- --------------- -------------------------- 2015-01-03 12:15:56.136897 VIEW INVALIDATE 8289601 view invalidation 2015-01-03 12:16:30.178980 UPGRADE SERVER 11.2.0.3.0 Upgraded from 10.2.0.5.0 2015-01-03 12:16:39.788907 APPLY SERVER 11.2.0.3 8 PSU PSU 11.2.0.3.8 SQL>
