oracle创建表results,在Oracle 11.2的数据库中建表时遇到 RESULT_CACHE (MODE DEFAULT) ORA-00922: missing or invalid o...

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