Skip to Content
Author's profile photo Ashutosh Chaturvedi

SAP upgrade after upgrading oracle from 11g to 12C: ORA-00922

Dear Experts,

We have faced a issue while upgrading the SAP system from SAP Netweaver 7.0 to SAP Netweaver 7.4 in execution phase after upgrading the Oracle from 11g to 12c in SUBPHASE MAIN_NEWBAS/PARCONV_UPG_DS

Overview

In this phase, the application tables are adjusted to the structure of the target release. Here, several conversion program processes (in the SAP system) and tp processes run simultaneously.

It is essential that this phase is completed without any errors. If errors are ignored here, the view usually cannot be created in the PMVNTAB_UPG phase.

In this phase, problems may occur when you create secondary indexes. Since these are mainly required for performance optimization and not to ensure that the table key is unique, errors that occur when you create secondary indexes do not cause the phase to terminate, but instead they are displayed in the LONGPOST.LOG file after the downtime has passed.

Error Description

                                      SUMupg.PNG                        


After check the logs PARCONV.log and NCONV00.<SID> we find that it is not able to execute the CREATE command in command.

The error was  ORA-00922: missing or invalid option


4 EGT281 sql:

4 EGT231 “CREATE TABLE ‘QCM8SALV_WD_ADMI'”

4 EGT231 “(‘CLNT’ VARCHAR2 (000009)”

4 EGT231 “DEFAULT ‘000’ NOT NULL,”

4 EGT231 “‘OBJECT’ VARCHAR2 (000090)”

4 EGT231 “DEFAULT ‘ ‘ NOT NULL,”

4 EGT231 “‘VALUE’ VARCHAR2 (000090)”

4 EGT231 “DEFAULT ‘ ‘ NOT NULL)”

4 EGT231 “PCTFREE 10”

4 EGT231 “PCTUSED 00”

4 EGT231 “INITRANS 001”

4 EGT231 “TABLESPACE PSAPSR3”

4 EGT231 “NOCOMPRESS”

4 EGT231      “NO INMEMORY”

4 EGT231 “STORAGE (INITIAL 0000000016 K”

4 EGT231 “NEXT 0000000040 K”

4 EGT231 “MINEXTENTS 0000000001”

4 EGT231 “MAXEXTENTS UNLIMITED”

4 EGT231 “PCTINCREASE 0000”

4 EGT231 “FREELISTS 001”

4 EGT231 “FREELIST GROUPS 01)”

2 ED0314 ORA-00922: missing or invalid option




SQL command has come up with some invalid option which are not supported with 11g but supported on 12C.


Now the question comes,  After upgrading the ORACLE from 11g to 12C , why the error ORA-00922: missing or invalid option is coming?


Yes , we have upgraded the oracle to 12C , which is true ,  but we have not maintained the compatible parameter for oracle to 12C. It was having a value for 11g.



After changing the parameter of  COMPATIBLE to ‘12.1.0.2.0’  from ‘11.2.0’ the below sql query which was invalid earlier become valid for oracle 12c.


4 EGT281 sql:

4 EGT231 “CREATE TABLE ‘QCM8SALV_WD_ADMI'”

4 EGT231 “(‘CLNT’ VARCHAR2 (000009)”

4 EGT231 “DEFAULT ‘000’ NOT NULL,”

4 EGT231 “‘OBJECT’ VARCHAR2 (000090)”

4 EGT231 “DEFAULT ‘ ‘ NOT NULL,”

4 EGT231 “‘VALUE’ VARCHAR2 (000090)”

4 EGT231 “DEFAULT ‘ ‘ NOT NULL)”

4 EGT231 “PCTFREE 10”

4 EGT231 “PCTUSED 00”

4 EGT231 “INITRANS 001”

4 EGT231 “TABLESPACE PSAPSR3”

4 EGT231 “NOCOMPRESS”

4 EGT231      “NO INMEMORY”

4 EGT231 “STORAGE (INITIAL 0000000016 K”

4 EGT231 “NEXT 0000000040 K”

4 EGT231 “MINEXTENTS 0000000001”

4 EGT231 “MAXEXTENTS UNLIMITED”

4 EGT231 “PCTINCREASE 0000”

4 EGT231 “FREELISTS 001”

4 EGT231 “FREELIST GROUPS 01)”


After this , we resume the SUM and it passed the error with a lightening speed.

With Regards

Ashutosh Chaturvedi

Assigned tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Juan Reyes
      Juan Reyes

      Hi Ashutosh,

      The first thing to do after upgrading a DB is to go to the respective standard database parameter SAP Note for that release. In this case,

      1888485 - Database Parameter for 12.1.0.2

      Also, its very important to check in oracle the optimizer parameters they can cause some heartache

      Regards, JP

      Author's profile photo Ashutosh Chaturvedi
      Ashutosh Chaturvedi
      Blog Post Author

      Hi Juan,

      Yes you are right. We have miss this parameters which has cause the issue.

      With Regards

      Ashutosh Chaturvedi

      Author's profile photo Former Member
      Former Member

      You can also download the Parameter_check_12.txt from SAPNOTE:  1171650 to check the Oracle 12C Parameters.  Change the extension to .sql

      V/R,

      -Lexx N.

      Author's profile photo Former Member
      Former Member
      Author's profile photo Reinhard Jud
      Reinhard Jud

      Thanks, that Oracle parameter worked for me also! 🙂

      I had a similar error during BW 7.5 upgrade after Oracle Upgrading to 12:

      Phase: PARMVNT_PRE

      3 ETP000 PCTINCREASE 0000

      3 ETP000 FREELISTS 001

      3 ETP000 FREELIST GROUPS 01)

      3 ETP000 

      2WETP000 10:44:54: Retcode 1: error in DDL statement for "CRR4TABLES                    " - repeat

      2EETP345 10:45:06: Retcode 1: SQL-error "922-ORA-00922: missing or invalid option" in DDL statement

      2EETP345  for "CRR4TABLES                    "

      2 ETP000  --------------- DB-ROLLBACK() ---------------

      2EETP334 10:45:06: error in DDL, nametab for "CRR4TABLES" not activated

      Thank you.

      Reinhard

      Author's profile photo Lieve Ravyts
      Lieve Ravyts

      Hello Ashutosh,

      Was this the upgrade of a dual stack system (ABAP+JAVA)?
      I ask this because I try to understand what it the exact consequence of what mentionned in the sap note "2133079 - Oracle Database 12c: Integration in SAP environment" : "Limitations:

      If Oracle 12c is used there exists a limitation for Upgrades of older versions of SAP NetWeaver Java or Dual Stack based SAP systems. Java- and Dual Stack upgrades from NetWeaver versions 7.0x to 7.3x are not supported with Oracle version 12c. You need to upgrade those systems (NW 7.0x & NW 7.3x based ) at least to a NetWeaver version 7.40. Pure SP or EHP updates for SAP systems based on those older NetWeaver releases are supported."

      If we do upgrade oracle 12c now, is it still possible to do an upgrade to SAP Netweaver 7.40 later for a JAVA only stack or a Dual stack system ?

      Kind regards,
      Lieve Ravyts

      Author's profile photo Amogh Umalkar
      Amogh Umalkar

      HI Ashutosh,

      Please let me know where did you change the Paramter of Compatible.

      I am also facing the same issue and when i checked in initSID file the paramter is set.

      Thanks and Regards,

      Amogh