SQL for troubleshooting services in multitenant environment

Query for troubleshooting services in a multitenant environment:

set lines 200
col name format a40
col network_name format a40
col pdb format a20
col global format a20

select name,network_name,con_id 
from v$active_services
order by con_id,name;
  
select name,network_name,con_id,pdb,global 
from v$services 
order by con_id,name;
 
select con_id#,name,network_name 
from  cdb_service$ 
order by con_id#,name ;
exit

Some Oracle Net related terms and what they mean

The terms used in Oracle Networking can be very similar and somewhat confusing.

Here is an attempt to explain some of them.

What is a network service name?

The network service name is shown in yellow below:

BRNY01 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oric-sandbox-db01.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = brny01.oric.no)
    )
  )

A network service name resolves to a connect descriptor.

What is a connect descriptor?

A specially-formatted description of the destination for a network connection. A connect descriptor contains destination service and network route information.
Everyhing contained within the DESCRIPTION part constitutes the connect description:

(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oric-sandbox-db01.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = brny01.oric.no)
    )
  )

What is a connect identifier?

A connect identifier can be a network service name, database service name, or network service alias. Users will have to pass along username and password together with the connect identifier, to connect to a database.

For all intents and purposes, think about a connect identifier as a database name, or a nicname for a database name that you make up yourself.

What is a connect string?

By connect string we mean the information the user passes to a service to connect, such as user name, password and connect identifier

CONNECT scott/tiger@net_service_name

Solution to ORA-29548: Java system class reported: release of Java system classes in the database (19.0.0.0.240116 1.8) does not match that of the oracle executable (19.0.0.0.240416 1.8)

When I was trying to drop and recreate a schema in my PDB, I received the following error:

drop user myuser cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-29548: Java system class reported: release of Java system classes in the database (19.0.0.0.240116 1.8) does not match that of the oracle executable (19.0.0.0.240416 1.8)

This occured since I had cloned the database from a source with a lower CPU level. To correct the situation: shutdown the entire cdb

SYS@_container_name SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SYS@_container_name SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

Start up the database in upgrade mode:

SYS@_container_name SQL> startup upgrade
Database mounted.
Database opened.
SYS@_container_name SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MIGRATE    YES
         4 pdb1                           MOUNTED

Open the pdb1 in upgrade mode, too:

SYS@_container_name SQL> alter pluggable database all open upgrade ;

Pluggable database altered.

SYS@_container_name SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MIGRATE    YES
         4 pdb1                           MIGRATE    YES

Run datapatch:

cd $ORACLE_HOME/OPatch/
datapatch -verbose

Shutdown the database, open normally:

SYS@_container_name SQL> startup
SYS@_container_name SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 pdb1                           MOUNTED
SYS@_container_name SQL> alter pluggable database all open;

Pluggable database altered.

SYS@_container_name SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 pdb1                           READ WRITE NO

You can now drop the user:

SYS@_container_name SQL> alter session set container="pdb1";

Session altered.

SYS@_container_name SQL> drop user myuser cascade;

User dropped.