At the moment I work with one of our reference customers, Swiss Mobiliar Insurance, on their Multitenant upgrade to Oracle Database 22.214.171.124. And we encountered an “interesting” issue. After upgrade we received an ORA-44787 making it impossible to switch between containers with “
alter session set container=pdb1;” commands. Lesson learned now: If you don’t want ORA-44787 – Don’t mess with the Default Oracle Service.
What has happened?
Very simple case. We approach a database upgrade with a Multitenant deployment of over 90 PDBs. Source database version is Oracle Database 126.96.36.199.BP170117, destination version is Oracle Database 188.8.131.52.RU170718. The upgrade runs fine. But during recompilation the logs show a strange behavior:
$ORACLE_HOME/perl/bin/perl catcon.pl -n 16 -e -b utlrp -d '''.''' utlrp.sql vi utlrp0.log SQL*Plus: Release 184.108.40.206.0 Production on Tue Aug 8 16:34:58 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. SQL> Connected. ERROR: ORA-44787: Service cannot be switched into.
Alain Fuhrer from Mobiliar blogged about it:
Even a manual switch between containers with
ALTER SESSION SET CONTAINER=PDB1; results in the same error:
ORA-44787: Service cannot be switched into. But the PDBs are accessible from the outside with
$ sqlplus system/manager@pdb1_service.
The testcase is done with our HOL (Hands-On Lab) environment using CDB1 and CDB2.
-- You will have to exchange CDB1 with CDB2 when repeating it with Oracle 12.2 in the lab SQL> create pluggable database pdb1 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB1/pdbseed','/u02/oradata/CDB1/pdb1'); Pluggable database created. SQL> alter pluggable database pdb1 open; Pluggable database altered. SQL> alter session set container=pdb1; Session altered. SQL> exec DBMS_SERVICE.CREATE_SERVICE('mypdb1','mypdb1'); PL/SQL procedure successfully completed. SQL> SELECT NAME FROM V$ACTIVE_SERVICES; NAME --------------- pdb1 SQL> exec DBMS_SERVICE.START_SERVICE('mypdb1'); PL/SQL procedure successfully completed. SQL> SELECT NAME FROM V$ACTIVE_SERVICES; NAME --------------- mypdb1 pdb1 SQL> exec DBMS_SERVICE.STOP_SERVICE('pdb1'); PL/SQL procedure successfully completed. SQL> alter session set container=cdb$root; Session altered.
And now the different behavior between Oracle Database 220.127.116.11 and Oracle Database 18.104.22.168 happens.
In Oracle Database 22.214.171.124 you can switch between containers even though the default service has been disabled:
SQL> alter session set container=pdb3; Session altered. SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') "I am in container:" FROM DUAL; I am in container: ---------------------------- PDB1
But in Oracle Database 126.96.36.199 you can’t switch between containers anymore once you’ve disabled the default service:
SQL> alter session set container=pdb1; ERROR: ORA-44787: Service cannot be switched into. SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') "I am in container:" FROM DUAL; I am in container: -------------------------- CDB$ROOT
Additional Patch Required
Due to another issue which came up in relation to this case I recommend that you apply
patch 26272761 - MULTIPLE ORA-44787 REPORTED IN LOGS DURING UPGRADE FROM 188.8.131.52 TO 184.108.40.206. It is available for Oracle Database 220.127.116.11 and 12,2.0.1.RU170718. It fixes an issue with the internal services hashes and will be included into Oracle 18.1 as well.
ORA-44787 – Don’t mess with the Default Oracle Service
In summary you must not disable the default Oracle service. Nevertheless please create your own service as recommended by us and many external experts. But leave the default Oracle service up and running as it gets used for internal administration tasks. Otherwise you’ll see the above issues coming up.
And just to emphasize this:
The behavior in Oracle Database 12.2 and newer is the correct behavior.