ORA-44787 – Don’t mess with the Default Oracle Service

ORA-44787 - Don't mess with the Default Oracle ServiceAt the moment I work with one of our reference customers, Swiss Mobiliar Insurance, on their Multitenant upgrade to Oracle Database 12.2.0.1. 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 12.1.0.2.BP170117, destination version is Oracle Database 12.2.0.1.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 12.2.0.1.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.

Short Testcase

The testcase is done with our HOL (Hands-On Lab) environment using CDB1 and CDB2.

Basic setup:

-- 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 12.1.0.2 and Oracle Database 12.2.0.1 happens.
In Oracle Database 12.1.0.2 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 12.2.0.1 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 12.1.0.2 TO 12.2.0.1. It is available for Oracle Database 12.2.0.1 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.

–Mike

2 thoughts on “ORA-44787 – Don’t mess with the Default Oracle Service

  1. Hi Mike,
    >> leave the default Oracle service up and running
    Then, if you have primary and standby on the same server, with PDB on standby opened read-only (Active Data Guard), you will have two instances registering the same service. Do you think it can have bad side effects even when we don’t use the default service? Maybe better to register to different listeners?
    Regards,
    Franck.

    • Franck,

      first of all, having the standby on the same host is not a great idea 😉
      But you can’t use the PDB’S default service for failover purposes anyways. For HA you’ll have to create your own service.

      Cheers
      Mike

Leave a Reply

Your email address will not be published. Required fields are marked *