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.

How I removed a data guard config completely

Here is my config:

DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  kej01       - Primary database
    kej01_stby1 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 66 seconds ago)

1. stop the redo apply process:

DGMGRL>  edit database kej01_stby1 set state='APPLY-OFF';
Succeeded.

2. remove the standby database from the configuration:

DGMGRL> remove database kej01_stby1;
Removed database "kej01_stby1" from the configuration

3. Remove the configuration itself:

DGMGRL> remove configuration;
Removed configuration

4. On both the primary and standby server, edit the $TNS_ADMIN/listener.ora by removing these entries:

(SID_DESC =
      (GLOBAL_DBNAME = kej01_DGMGRL)
      (ORACLE_HOME = /orasw/19c)
      (SID_NAME = kej01)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = kej01_DGMGRL.skead.no)
      (ORACLE_HOME = /orasw/19c)
      (SID_NAME = kej01)
    )
 

Make sure to stop/start the listener afterwards.
5. On previous standby, remove db_unique_name:

SYS@kej01>SQL>alter system set db_unique_name='kej01' scope=spfile;

6. On both servers, stop the broker processes:

SYS@kej01>SQL>alter system set dg_broker_start=false scope=both;

System altered.

6. On the standby, finish database recovery:

SYS@kej01>alter database recover managed standby database finish;

Database altered.

The database is still mounted as a physical standby:

SYS@kej01>SQL>select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

If you open it now, it will be in READ ONLY status:

SYS@kej01>SQL>alter database open;

Database altered.

SYS@kej01>select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY            PHYSICAL STANDBY

7. Instruct the former standby database that it is now indeed a normal ("primary") database:

SYS@kej01>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

The alert log will show that we are cutting all strings to the previous role:

2024-05-29T15:55:09.123174+02:00
ALTER DATABASE SWITCHOVER TO PRIMARY (kej01)
2024-05-29T15:55:09.125019+02:00
Maximum wait for role transition is 15 minutes.
TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2024-05-29 15:55:09.125651
TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2024-05-29 15:55:09.126990
TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2024-05-29 15:55:09.130097

Standby terminal recovery start SCN: 4695193
RESETLOGS after incomplete recovery UNTIL CHANGE 4695565 time 05/29/2024 15:28:15
ET  (PID:1614518): ORL pre-clearing operation disabled by switchover
Online log /log/oradata/kej01/redo1.log: Thread 1 Group 1 was previously cleared
Online log /log/oradata/kej01/redo2.log: Thread 1 Group 2 was previously cleared
Online log /log/oradata/kej01/redo3.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 4695192
2024-05-29T15:55:16.588617+02:00
Setting recovery target incarnation to 3
2024-05-29T15:55:16.662719+02:00
NET  (PID:1614518): Database role cleared from PHYSICAL STANDBY [kcvs.c:1133]
Switchover: Complete - Database mounted as primary
TMI: kcv_commit_to_so_to_primary Switchover from physical END 2024-05-29 15:55:16.667784
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
2024-05-29T15:55:27.547369+02:00
alter database open

At this point, my standby database is once again mounted, but it has now assumed the PRIMARY role, which is what I want:

SYS@kej01>SQL>select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PRIMARY

8. Open it now, and it will be in read write mode:

SYS@kej01>SQL>alter database open;

Database altered.

SYS@kej01>SQL>select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

According to the oracle documentation, the syntax above is pre-12.1 style, which is still compatible but DBAs are encouraged to use

ALTER DATABASE SWITCHOVER TO target_db_name [FORCE] [VERIFY];
and
ALTER DATABASE FAILOVER TO target_db_name;

instead.

Easy Connect syntax

Basic syntax:

sqlplus uid/pwd@//servername.domain.com:port_number/service_name

If you do not specify port, 1521 is assumed.

Example connecting to a cntainer databaser:

C:\Users\vegard>sqlplus system/mypassword@oric-sandbox-db01.oric.no/cdb.oric.no

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL> show user
USER is "SYSTEM"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

Example connecting to a pdb plugged into the same container database:

C:\Users\vegard>sqlplus system/mypassword@oric-sandbox-db01.oric.no/vksa.oric.no

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL> show user
USER is "SYSTEM"
SQL> show con_name

CON_NAME
------------------------------
VKSA01
SQL>

You can also use easy connect syntax with the tnsping utility, just to check if there is an oracle service running on a specific server.

Example using tnsping from my Windows 11 client, using a cmd/powershell terminal:

C:\Users\vegard>tnsping "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oric-sandbox-db01.oric.no)(PORT=1521)))"

Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oric-sandbox-db01.oric.no)(PORT=1521)))
OK (50 msec)

Documentation for the Net Services Administrator's Guide can be found here

syntax for dropping a temporary file from a temporary tablespace

For future reference, here is how to drop and recreate a temporary file in an existing temporary tablespace, but with a changed path: If applicable, set the container:

alter session set container=pdb1;

Drop the current tempfile, and recreate it in the desired location. Make it autoextensible:

alter database tempfile '/data/oradata/db01/temp1.dbf' drop including datafiles;
alter tablespace TEMP1 add tempfile '/data/oradata/db02/temp1.dbf' size 1G; 
alter database tempfile '/data/oradata/db02/temp1.dbf' autoextend on next 1G maxsize unlimited;

A good query for temporary tablespaces:

select t.ts#, t.name,t.bigfile, t.con_id, f.name "file_name", tg.group_name
from v$tempfile f join v$tablespace t
on (t.TS# = f.TS#)
and t.con_id = f.CON_ID join DBA_TABLESPACE_GROUPS tg on (tg.tablespace_name = t.name)

       TS# NAME                           BIG     CON_ID file_name                                GROUP_NAME
---------- ------------------------------ --- ---------- ---------------------------------------- ------------------------------
         3 TEMP1                          YES          3 /u02/oradata/pdb1/temp1.dbf                    TEMP
         4 TEMP2                          YES          3 /u02/oradata/pdb1/temp2.dbf                    TEMP

How to solve errors like "Interim patch num/num (DATAPUMP BUNDLE PATCH 19.21.0.0.0): Not installed in the CDB but installed in the PDB"

After running autoupgrade, I was left with the following message in the logfile $ORACLE_BASE/cfgtoollogs/upgrade/auto/status/status.log:

[Stage Name]    NONCDBTOPDB
[Status]        FAILURE
[Start Time]    2024-05-06 14:29:45
[Duration]      0:05:33
[Log Directory] /u01/oracle/txs01/101/noncdbtopdb
Cause:AutoUpgException [After running noncdb_to_pdb.sql, the pdb was closed or was opened in restricted mode]
Reason:None
Action:None
Info:None
ExecutionError:Yes
Error Message:AutoUpgException [After running noncdb_to_pdb.sql, the pdb was closed or was opened in restricted mode]

A query against the database shows that there are errors in the pdb_plug_in_violations view:

SELECT TIME,NAME,CAUSE,STATUS,MESSAGE FROM PDB_PLUG_IN_VIOLATIONS;

TIME                      NAME    CAUSE       STATUS     MESSAGE
-------------------       -----   ---------   --------   ------------------------------------------------------------------------------------------------------------------------------
06.05.2024 15.23.20       TXS01   SQL Patch   PENDING   Interim patch 35926646/25513953 (OJVM RELEASE UPDATE: 19.22.0.0.240116 (35926646)): Installed in the CDB but not in the PDB
06.05.2024 15.23.20       TXS01   SQL Patch   PENDING   Interim patch 36092868/25496280 (DATAPUMP BUNDLE PATCH 19.22.0.0.0): Installed in the CDB but not in the PDB
06.05.2024 15.23.20       TXS01   SQL Patch   PENDING   Interim patch 35648110/25365038 (OJVM RELEASE UPDATE: 19.21.0.0.231017 (35648110)): Not installed in the CDB but installed in the PDB
06.05.2024 15.23.20       TXS01   SQL Patch   PENDING   Interim patch 35787077/25410019 (DATAPUMP BUNDLE PATCH 19.21.0.0.0): Not installed in the CDB but installed in the PDB
06.05.2024 15.23.20       TXS01   SQL Patch   PENDING   '19.22.0.0.0 Release_Update 2401040239' is installed in the CDB but '19.21.0.0.0 Release_Update 2309301519' is installed in the PDB

Solution is in Doc ID 2604940.1 "Datapatch precheck failed : Release update is BEING installed to PDB but is not installed in CDB$ROOT.This will cause a patch mismatch between this PDB and CDB$ROOT":

datapatch when executed is trying to apply Release update in the PDB database first but is not applying in CDB$ROOT .

Solution is to execute datapatch individually in each of the containers, starting with CDB$ROOT :

datapatch -verbose -pdbs CDB\$ROOT
datapatch -verbose -pdbs TXS01

sqlplus / as sysdba
alter session set container=PDB$SEED;
alter session set "_oracle_script"=TRUE;
alter pluggable database pdb$seed close immediate instances=all;
alter pluggable database pdb$seed OPEN READ WRITE;
select open_mode from v$database;
exit

datapatch -verbose -pdbs PDB\$SEED

sqlplus / as sysdba
alter session set "_oracle_script"=FALSE;

You should now see that the status has changed from PENDING to RESOLVED:

TIME                      NAME    CAUSE       STATUS     MESSAGE
-------------------       -----   ---------   --------   ------------------------------------------------------------------------------------------------------------------------------
06.05.2024 15.23.20       TXS01   SQL Patch   RESOLVED   Interim patch 35926646/25513953 (OJVM RELEASE UPDATE: 19.22.0.0.240116 (35926646)): Installed in the CDB but not in the PDB
06.05.2024 15.23.20       TXS01   SQL Patch   RESOLVED   Interim patch 36092868/25496280 (DATAPUMP BUNDLE PATCH 19.22.0.0.0): Installed in the CDB but not in the PDB
06.05.2024 15.23.20       TXS01   SQL Patch   RESOLVED   Interim patch 35648110/25365038 (OJVM RELEASE UPDATE: 19.21.0.0.231017 (35648110)): Not installed in the CDB but installed in the PDB
06.05.2024 15.23.20       TXS01   SQL Patch   RESOLVED   Interim patch 35787077/25410019 (DATAPUMP BUNDLE PATCH 19.21.0.0.0): Not installed in the CDB but installed in the PDB
06.05.2024 15.23.20       TXS01   SQL Patch   RESOLVED   '19.22.0.0.0 Release_Update 2401040239' is installed in the CDB but '19.21.0.0.0 Release_Update 2309301519' is installed in the PDB

You may now clear the errors:

SYS@cdb>SQL>exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS( pdb_name => 'TXS01');

PL/SQL procedure successfully completed.

SYS@cdb>SQL>select time,name,cause,status,message from PDB_PLUG_IN_VIOLATIONS order by name;

no rows selected