Tuesday, 7 August 2018

Oracle - Containers and Pluggables - ORA-65096: invalid common user or role name

Having successfully created my database earlier: -

dbca -silent -createDatabase \
     -templateName General_Purpose.dbc \
     -gdbname bpm856.uk.ibm.com -sid orcl -responseFile NO_VALUE \
     -characterSet AL32UTF8 \
     -sysPassword Qp455w0rd \
     -systemPassword Qp455w0rd \
     -createAsContainerDatabase true \
     -numberOfPDBs 1 \
     -pdbName pdb1 \
     -pdbAdminPassword Qp455w0rd \
     -databaseType MULTIPURPOSE \
     -automaticMemoryManagement false \
     -totalMemory 1536 \
     -storageType FS \
     -datafileDestination "/home/oracle/app/oracle/oradata" \
     -redoLogFileSize 50 \
     -emConfiguration NONE \
     -ignorePreReqs

I attempted to create a few schemas ( schemae ?? ) as part of an IBM BPM 8.5.6 deployment: -

sqlplus / as sysdba

CREATE USER cmnuser IDENTIFIED BY passw0rd;

CREATE USER cmnuser IDENTIFIED BY passw0rd
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

Hmmmmm :-(

This gave me a clue: -


specifically this: -

You must have created the database as a container database. While, you are trying to create user in the container, i.e. CDB$ROOT, however, you should create the user in the PLUGGABLE database.

You are not supposed to create objects in the container, the container holds the metadata for the pluggable databases. You should use the pluggable database for you general database operations. Else, do not create it as container, and not use multi-tenancy.

Looking at the dbca command above, can you see where I went wrong ??

     -createAsContainerDatabase true \

Yep :-)

I had to drop the database and then recreate it: -

dbca -silent -createDatabase \
     -templateName General_Purpose.dbc \
     -gdbname bpm856.uk.ibm.com -sid orcl -responseFile NO_VALUE \
     -characterSet AL32UTF8 \
     -sysPassword Qp455w0rd \
     -systemPassword Qp455w0rd \
     -createAsContainerDatabase false \
     -numberOfPDBs 1 \
     -pdbName pdb1 \
     -pdbAdminPassword Qp455w0rd \
     -databaseType MULTIPURPOSE \
     -automaticMemoryManagement false \
     -totalMemory 1536 \
     -storageType FS \
     -datafileDestination "/home/oracle/app/oracle/oradata" \
     -redoLogFileSize 50 \
     -emConfiguration NONE \
     -ignorePreReqs

and then re-test: -

 sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 7 18:56:11 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
CREATE USER cmnuser IDENTIFIED BY passw0rd;

User created.

SQL> drop user cmnuser;

User dropped.

SQL>
exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

and then continue with my BPM -> Oracle configuration: -

echo "passw0rd" | sqlplus / as sysdba @ /opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/cmnuser/createUser.sql
echo "passw0rd" | sqlplus / as sysdba @ /opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/psuser/createUser.sql
echo "passw0rd" | sqlplus / as sysdba @ /opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/pdwuser/createUser.sql


exit | sqlplus / as sysdba @/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/cmnuser/createSchema_Standard.sql
exit | sqlplus / as sysdba @/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/cmnuser/createSchema_Messaging.sql
exit | sqlplus / as sysdba @/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/psuser/createSchema_Standard.sql
exit | sqlplus / as sysdba @/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/psuser/createProcedure_Standard.sql
exit | sqlplus / as sysdba @/opt/ibm/WebSphereProfiles/Dmgr01/dbscripts/PCCell1.De1/Oracle/bpm856.uk.ibm.com/pdwuser/createSchema_Standard.sql

Again, like Manuel, I learn!!!!

No comments: