在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>