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
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
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
Hi Juan,
Yes you are right. We have miss this parameters which has cause the issue.
With Regards
Ashutosh Chaturvedi
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.
Thanks Ashutosh Chaturvedi
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
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
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