Experiences with moving to multitenant in 220.127.116.11
Lately I’ve been upgrading our 11g Standard Edition databases in test environments to 12c Enterprise Edition and also plugging them in to a multitenant container database.
It’s a new technology for Oracle, but I was still quite surprised about the number of issues I faced when trying to plug in an existing non-CDB database. After resolving all these issues it has been quite painless process since then.
In short, upgrading 11g database to 12c pluggable database involves the following steps:
* Upgrade 11g database to 12c using the normal database upgrade procedures. This step will result in 12c non-CDB database.
* In the target CDB database plug in the new upgraded database as a new pluggable database.
* Run $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql in the new PDB. This step will convert the non-CDB data dictionary to a PDB data dictionary. After this step you can open the newly added PDB.
This post is mostly about the issues I encountered when running the last described step – executing $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql. Hopefully it’ll be helpful if you hit similar problems.
Version: 18.104.22.168, 2-node RAC
Patches: April 2016 PSU + OJVM PSU
Platform: Oracle Linux 6 x86-64
noncdb_to_pdb.sql takes a really long time to execute
This was the first problem I encountered. After 1,5 hours I killed my session. That was really weird, because executing it should onbly take about 20 minutes according to Oracle documentation. The step script was stuck on was:
-- mark objects in our PDB as common if they exist as common in ROOT
Looking at the wait events the session was not waiting for a blocker, it was actively executing many parallel sessions.
I found the following blog post that described the same problem and the described solution also helped for me: Link to Bertrand Drouvot blog
But one addition, instead of modifying the noncdb_to_pdb.sql script, I executed ALTER SESSION before running noncdb_to_pdb.sql.
SQL> alter session set container=newpdb; SQL> alter session set optimizer_adaptive_features=false; SQL> $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
noncdb_to_pdb.sql hangs at alter pluggable database close
Next issue I faced – noncdb_to_pdb.sql just hanged mid-execution and the statement it was executing was
SQL> alter pluggable database "&pdbname" close;
The session was waiting for opishd.
Solution: Apply bugfix for Bug 20172151 – NONCDB_TO_PDB.SQL SCRIPT HANGS DURING UPGRADE. This will just update noncdb_to_pdb.sql script itself to execute alter pluggable database “&pdbname” close IMMEDIATE instances = all; instead of normal close.
noncdb_to_pdb.sql fails with ORA-600 [kspgsp2]
That was a fun one 🙂 Not every time, but most executions noncdb_to_pdb.sql failed almost at the end with the following message:
SQL> alter session set "_ORACLE_SCRIPT"=true; Session altered. SQL> SQL> drop view sys.cdb$tables&pdbid; drop view sys.cdb$tables5 * ERROR at line 1: ORA-00600: internal error code, arguments: [kspgsp2], [0xBF3C9E3F8], , [recyclebin], , , , , , , , 
Every time at the same drop view statement. Search in Oracle support did not give me anything helpful, there were many ORA-600 [kspgsp2] issues, but nothing matched my case. Finally I noticed that one argumnt was [recyclebin] and decided to try turning the recyclebin off for the session. It helped.
Successful non-CDB to PDB conversion
Getting successful execution of noncdb_to_pdb.sql required me to:
* Apply patch 20172151
* Running noncdb_to_pdb.sql using the following sequence of commands:
SQL> alter session set container=newpdb; SQL> alter session set optimizer_adaptive_features=false; SQL> alter session set recyclebin=off; SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
Take care of the services!
This problem may be our environment specific, but I’ll describe it anyway.
We use services a lot, all applications that connect to the database get their own dedicated service. So the applications connect using JDBC connection string that looks something like this:
Where application is configured as a service using srvctl and scrum.example.com is database domain name depending on the environment. The same application in QA environment will have connection string:
We decided to use only one CDB for all environments, but db_domain parameter cannot be different for each PDB. In order to not change the application connection strings I had to create the new services in srvctl using the FULL service name, then Oracle will not append the database domain name to the service name:
srvctl add service -database cdb -preferred cdb1,cdb2 -pdb newpdb -service application.scrum.example.com srvctl start service -database cdb -service application.scrum.example.com
After adding the services for the first database all of them started just fine and applications connected just fine, but when starting the services for the second environment (qa) I got the following error:
srvctl add service -database cdb -preferred cdb1,cdb2 -pdb newpdbqa -service application.qa.example.com srvctl start service -database cdb -service application.qa.example.com ... ORA-44311: service application not running ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 40 ORA-06512: at "SYS.DBMS_SERVICE", line 421 ORA-06512: at line 1 ...
But when I tried to add a new service that did not exist previously it started just fine. I started digging into services on CDB level and found that all imported PDB-s also imprted their old short name services to CDB:
SQL> alter session set container=cdb$root; Session altered. SQL> select name from cdb_services order by 1; NAME ------------------------------------------------ SYS$BACKGROUND SYS$USERS ... application application application.scrum.example.com application2 application2 application2.scrum.example.com ...
I just assumed it can be confusing for CDB if different PDB-s have conflicting services running and I manually went into each PDB and removed the old short service names.
SQL> alter session set container=newpdb; SQL> exec dbms_service.delete_service('application'); SQL> exec dbms_service.delete_service('application2'); SQL> alter session set container=newpdbqa; SQL> exec dbms_service.delete_service('application'); SQL> exec dbms_service.delete_service('application2');
After that new services started just fine.
SQL> alter session set container=cdb$root; SQL> select name from cdb_services order by 1; NAME ------------------------------------------------ SYS$BACKGROUND SYS$USERS ... application.scrum.example.com application2.scrum.example.com application.qa.example.com application2.qa.example.com ...