At 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
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
If the default service is dropped is there any way to recover it other than doing an unplug and plug. Thank you.
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
This work for me on version 12.2.0.1.0:
Shutdown immediate and Startup mount the CDB containing the lost PDB
> shutdown immediate;
> startup mount;
> alter database open;
> alter session set container=’your_lost_pdb’;
> select sys_context(‘USERENV’, ‘CON_NAME’) from dual;
It should show your lost pdb’s name
> alter database open;
You’ll receive:
ORA-44304: service does not exist
ORA-44777: Pluggable database service cannot be started.
But you’re still sort-of “connected” to your lost pdb:
> select sys_context(‘USERENV’, ‘CON_NAME’) from dual;
> exec dbms_service.CREATE_SERVICE(‘def_pdb_service’,’def_pdb_net_service_name’);
> exec dbms_service.START_SERVICE(‘def_pdb_service’);
> alter system register
> alter session set container=’CDB$ROOT’;
> select service_id,name,network_name,pdb,con_id from cdb_services order by con_id;
Few!
Hope this will help.
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..
Thanks a lot, Elias!!!
Cheers,
Mike
Hi,
I have create custom services for my PDB. How can i get this services to start once the PDB restart and open even after I “save state” for the pdb that doesn’t restart the services.
Hi Ann,
does this answer your question:
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2006021.1
There’s also an underscore available since 12.2:
_auto_start_pdb_service
which is FALSE by default but can be set to TRUE.
Can you give it a try please and feedback if it works?
Cheers,
Mike
Whereas it makes sense to prevent messing with internal services, but with bugs like:
Bug 31756568 AFTER UNPLUGGING / DROPPING A PDB ON PRIMARY THE TNS SERVICE OF LISTENER REMAINS ACTIVE ON ADG STANDBY
it would be nice to have some workaround to stop the service for not existing PDB (it gives ORA-44793
cannot stop internal services).
Hi Ruslan,
I fear you need to request such a fix via an SR. There is not much I can do.
But I fully see your point.
Cheers,
Mike