Upgrade to 19c Oracle database with EBS created a new service EBS_<PDB>. And all the connection from apps goes through that service.
It is seen that this service disappear on server reboot and database reboot.
$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-APR-2020 16:32:48 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER Alias VISCDB Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 22-APR-2020 21:27:31 Uptime 6 days 19 hr. 5 min. 17 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/oracle/db/tech/19.3/network/admin/listener.ora Listener Log File /u01/oracle/db/tech/19.3/admin/TEST_HOSTNAME/diag/tnslsnr/HOSTNAME/cdb/alert/log.xml Listening Endpoints Summary… (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HOSTNAME.)(PORT=1521))) Services Summary… Service "2809223196ec2af8e053a740d20a4db6" has 1 instance(s). Instance "CDB", status READY, has 1 handler(s) for this service… Service "TEST" has 2 instance(s). Instance "TEST", status UNKNOWN, has 1 handler(s) for this service… Instance "CDB", status READY, has 1 handler(s) for this service… Instance "CDB", status READY, has 1 handler(s) for this service… Service "CDB" has 1 instance(s). ---> CDB service , not used by EBS but needed for connections to CDB Instance "CDB", status READY, has 1 handler(s) for this service… Service "CDBXDB" has 1 instance(s). --> optional XDB service Instance "CDB", status READY, has 1 handler(s) for this service… Instance "CDB", status READY, has 1 handler(s) for this service… The command completed successfully
Now there are two ways to solve the issue.
(1)Run Autoconfig on database side. But it is not good to run the autoconfig everytime after server reboot.
(2)This can be solved by making sure we follow below steps for first database reboot and then we will be set for all the further reboot
(i) In 19c database service_name parameter should be set to container name. Connect to the CDB and run the following commands:
alter System Set SERVICE_NAMES='<CDB_NAME>' SCOPE=BOTH; alter System Register;
(ii) Listener should be started using CDB environment file
cd $ORACE_HOME
.CDB_HOSTNAME.env
lsnrcrt stop CDBNAME
lsnrctl start CDBNAME
(iii). To start PDB, Source the CDB environment file, connect to the CDB as SYSDBA, then execute the SQL shown in the example to start the PDB:
$.CDB_HOSTNAME.env $ sqlplus "/ as sysdba" SQL>alter pluggable database TEST close; SQL> alter pluggable database TEST open read write services=all;
(iv) There is the option to save the state of the PDB. Oracle will then preserve the mode when you restart the CDB. You can find the current saved state by querying DBA_PDB_SAVED_STATES:
$ .CDB_HOSTNAME.env
$ sqlplus "/ as sysdba"
SQL> alter pluggable database save state;
Now EBS_PDB service will come active. And you do not to do it again in any reboot
$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-APR-2020 16:32:48 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER Alias VISCDB Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 22-APR-2020 21:27:31 Uptime 6 days 19 hr. 5 min. 17 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/oracle/db/tech/19.3/network/admin/listener.ora Listener Log File /u01/oracle/db/tech/19.3/admin/TEST_HOSTNAME/diag/tnslsnr/HOSTNAME/cdb/alert/log.xml Listening Endpoints Summary… (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HOSTNAME.)(PORT=1521))) Services Summary… Service "2809223196ec2af8e053a740d20a4db6" has 1 instance(s). Instance "CDB", status READY, has 1 handler(s) for this service… Service "TEST" has 2 instance(s). Instance "TEST", status UNKNOWN, has 1 handler(s) for this service… Instance "CDB", status READY, has 1 handler(s) for this service… Instance "CDB", status READY, has 1 handler(s) for this service… Service "CDB" has 1 instance(s). Instance "CDB", status READY, has 1 handler(s) for this service… Service "CDBXDB" has 1 instance(s). --> optional XDB service Instance "CDB", status READY, has 1 handler(s) for this service… Service "ebs_TEST" has 1 instance(s). Instance "CDB", status READY, has 1 handler(s) for this service… The command completed successfully
(iv) You can also verify the service state using the below query
SQL> select name from v$active_services;
This should return the EBS_PDB service