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

6 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

    • Sanil,

      I fear this is the only way. You can try to recreate and start the service with DBMS_SERVICE but my attempts failed.
      Therefore your w/a may be the only option. That’s why we’ll block disabling the default service in future releases.

      Cheers,
      Mike

  2. As this is top ranked resource for ORA-44787 I would add another possible cause for getting this error when trying to switch into a PDB.
    If one “messes” with service_names of the CDB trying to change the service name or add an additional one using the well known “alter system set service_names” but fails to include the original service name with proper (original) case the services for PDB might not come up.

    So for example if You have originaly set service_names to “orcl”:
    SQL> show parameter service_names
    NAME TYPE VALUE
    ———————————— ———– ——————————
    service_names string orcl

    It can happen that You try to not mess with original service name and just add another name with for example
    SQL> alter system set service_names = orcl,cdb1;
    System altered.
    SQL> alter system register;
    (or possibly shutdown/startup)

    But the result is of this is the following:
    SQL> show parameter service_names
    NAME TYPE VALUE
    ———————————— ———– ——————————
    service_names string ORCL,CDB1

    Which can cause the PDB not to come up because original service name is not preserved but exchanged from lower case orcl to upper case ORCL.

    So to recover from this (or avoid this at all) the alter system parameter to be used must ensure the the original service name is included in original case, for example:
    SQL> alter system set service_names = “orcl”,”cdb1″;
    System altered.
    (Note the quotation mark around “orcl”)
    Then after restart this problem is gone (probably alter system register and restart of the pdb is sufficent).

    At least I encountered this issue with Oracle 18.3..

Leave a Reply

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

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.