The RESULT_CACHE (mode default) ORA-00922: missing or invalid option was encountered when creating tables in Oracle 11.2 Databases

The RESULT_CACHE (mode default) ORA-00922: missing or invalid option was encountered when creating tables in Oracle 11.2 Databases

The RESULT_CACHE (mode default) ORA-00922: missing or invalid option was encountered when creating tables in Oracle 11.2 Databases

hostdr:[/home/oracle]$sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 9 12:52:11 2015Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>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 optionSQL>

 

Background:
This db is a single machine in linux 11.2.0.3.8 and a slave database in OGG. The master database in OGG is rac in linux under 11.2.0.3.8. The two databases are used for disaster recovery through OGG. The error "ORA-00922: missing or invalid option" was initially found because the rep process on the OGG slave end is abend, And the rpt (report) at abend is as follows:

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 UString &, 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 USERSRESULT_CACHE (MODE DEFAULT)PCTUSED    0PCTFREE    10INITRANS   1MAXTRANS   255STORAGE    (            INITIAL          64K            NEXT             1M            MINEXTENTS       1            MAXEXTENTS       UNLIMITED            PCTINCREASE      0            BUFFER_POOL      DEFAULT            FLASH_CACHE      DEFAULT            CELL_FLASH_CACHE DEFAULT           )LOGGINGNOCOMPRESSNOCACHENOPARALLELMONITORING   /* GOLDENGATE_DDL_REPLICATION */], no error handler present.************************************************************************                   ** Run Time Statistics **                         ************************************************************************

 

Ignore the following errors.

When I saw an error reported by the OGG rep process, I thought it was an ogg problem at first. Then I took out the create script from it and ran it in sqlplus, the result is still an error (which can be seen at the beginning of this article). It can be said that it is an Oracle database problem, not an OGG problem.

Later, I thought about the differences between the ogg master database and the ogg slave database. The ogg master database was newly created by dbca, And the ogg slave database was upgraded from 10.2.0.5. Now, experienced DBAs will immediately check the compatible parameter of the OGG slave database. The result is that the compatible parameter value is 10.2.0.5. therefore, you need to modify this parameter to 11.2.0.0.0 (same as that of the master database). Of course, there are several notes for modifying this parameter:
1. Modify the compatible parameter to restart the database.
2. Once this parameter is modified (after the database is restarted), the parameter cannot be lowered to the original value. Otherwise, the following error will be reported when the instance is in the mount status:

ORA-00201: control file version 11.2.0.0.0 incompatible with ORACLE version 10.2.0.5ORA-00202: control file: 'D:\IM\CONTROLFILE\CONTROL01.CTL'

Knowledge points in this case:

1. Check whether db is created by dbca or upgrade:

SQL> set pages 100 lines 120SQL> col action format a16SQL> col namespace format a10SQL> col version format a10SQL> col comments format a26SQL> col action_time format a30SQL> col bundle_series format a15SQL> set line 200SQL> 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 invalidation2015-01-03 12:16:30.178980     UPGRADE          SERVER     11.2.0.3.0                            Upgraded from 10.2.0.5.02015-01-03 12:16:39.788907     APPLY            SERVER     11.2.0.3            8 PSU             PSU 11.2.0.3.8SQL>