Link to Bertrand Drouvot blog<\/a><\/p>\nBut one addition, instead of modifying the noncdb_to_pdb.sql script, I executed ALTER SESSION before running noncdb_to_pdb.sql.<\/p>\n
SQL> alter session set container=newpdb;\nSQL> alter session set optimizer_adaptive_features=false;\nSQL> $ORACLE_HOME\/rdbms\/admin\/noncdb_to_pdb.sql\n<\/code><\/pre>\nnoncdb_to_pdb.sql hangs at alter pluggable database close<\/h2>\n
Next issue I faced – noncdb_to_pdb.sql just hanged mid-execution and the statement it was executing was<\/p>\n
SQL> alter pluggable database \"&pdbname\" close;\n<\/code><\/pre>\nThe session was waiting for opishd<\/strong>.<\/p>\nSolution: Apply bugfix for Bug 20172151 – NONCDB_TO_PDB.SQL SCRIPT HANGS DURING UPGRADE<\/strong>. This will just update noncdb_to_pdb.sql script itself to execute alter pluggable database “&pdbname” close IMMEDIATE instances = all;<\/strong> instead of normal close.<\/p>\nnoncdb_to_pdb.sql fails with ORA-600 [kspgsp2]<\/h2>\n
That was a fun one \ud83d\ude42 Not every time, but most executions noncdb_to_pdb.sql failed almost at the end with the following message:<\/p>\n
SQL> alter session set \"_ORACLE_SCRIPT\"=true;\n\nSession altered.\n\nSQL>\nSQL> drop view sys.cdb$tables&pdbid;\ndrop view sys.cdb$tables5\n*\nERROR at line 1:\nORA-00600: internal error code, arguments: [kspgsp2], [0xBF3C9E3F8], [2174],\n[recyclebin], [], [], [], [], [], [], [], []\n<\/code><\/pre>\nEvery 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]<\/strong> and decided to try turning the recyclebin off for the session. It helped.<\/p>\nSuccessful non-CDB to PDB conversion<\/h2>\n
Getting successful execution of noncdb_to_pdb.sql required me to:
\n* Apply patch 20172151
\n* Running noncdb_to_pdb.sql using the following sequence of commands:<\/p>\n
SQL> alter session set container=newpdb;\nSQL> alter session set optimizer_adaptive_features=false;\nSQL> alter session set recyclebin=off;\nSQL> @$ORACLE_HOME\/rdbms\/admin\/noncdb_to_pdb.sql\n<\/code><\/pre>\nTake care of the services!<\/h2>\n
This problem may be our environment specific, but I’ll describe it anyway.
\nWe 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:<\/p>\n
jdbc:oracle:thin:@\/\/clusername.example.com\/application.scrum.example.com\n<\/code><\/pre>\nWhere application<\/strong> is configured as a service using srvctl and scrum.example.com<\/strong> is database domain name depending on the environment. The same application in QA environment will have connection string:<\/p>\njdbc:oracle:thin:@\/\/clusername.example.com\/application.qa.example.com\n<\/code><\/pre>\nWe 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:<\/p>\n
srvctl add service -database cdb -preferred cdb1,cdb2 -pdb newpdb -service application.scrum.example.com\nsrvctl start service -database cdb -service application.scrum.example.com\n<\/code><\/pre>\nAfter 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:<\/p>\n
srvctl add service -database cdb -preferred cdb1,cdb2 -pdb newpdbqa -service application.qa.example.com\nsrvctl start service -database cdb -service application.qa.example.com\n\n...\nORA-44311: service application not running\nORA-06512: at \"SYS.DBMS_SYS_ERROR\", line 86\nORA-06512: at \"SYS.DBMS_SERVICE_ERR\", line 40\nORA-06512: at \"SYS.DBMS_SERVICE\", line 421\nORA-06512: at line 1\n...\n<\/code><\/pre>\nBut 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:<\/p>\n
SQL> alter session set container=cdb$root;\n\nSession altered.\n\nSQL> select name from cdb_services order by 1;\n\nNAME\n------------------------------------------------\nSYS$BACKGROUND\nSYS$USERS\n...\napplication\napplication\napplication.scrum.example.com\napplication2\napplication2\napplication2.scrum.example.com\n...\n<\/code><\/pre>\nI 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.<\/p>\n
SQL> alter session set container=newpdb;\nSQL> exec dbms_service.delete_service('application');\nSQL> exec dbms_service.delete_service('application2');\nSQL> alter session set container=newpdbqa;\nSQL> exec dbms_service.delete_service('application');\nSQL> exec dbms_service.delete_service('application2');\n<\/code><\/pre>\nAfter that new services started just fine.<\/p>\n
SQL> alter session set container=cdb$root;\nSQL> select name from cdb_services order by 1;\n\nNAME\n------------------------------------------------\nSYS$BACKGROUND\nSYS$USERS\n...\napplication.scrum.example.com\napplication2.scrum.example.com\napplication.qa.example.com\napplication2.qa.example.com\n...\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[47,4],"class_list":["post-328","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-multitenant","tag-oracle"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/328","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=328"}],"version-history":[{"count":10,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/328\/revisions"}],"predecessor-version":[{"id":339,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/328\/revisions\/339"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=328"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=328"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=328"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}