Datapatch seems to "hang" or taking an extraordinary long time to complete. What could be wrong?

When using the utility datapatch to either apply or rollback patches in my Oracle 19c ORACLE_HOME, I was suprised at the time it took to (in this case) rollback the patches from version 19.23 to 19.22.

The patching was just "hanging" without any sign of progress. I left the shell for several hours, thinking progress would eventually be made and patience would be needed.

When I came back the morning after, it had not progressed at all.

The solution was found in the logfiles located $ORACLE_BASE/cfgtoollogs:

cd $ORACLE_BASE/cfgtoollogs/sqlpatch

# list your directories. Enter the most recent ones:
ls -latr 

drwxr-xr-x  2 oracle dba 4.0K Feb  1 18:25 sqlpatch_9072_2024_02_01_18_13_47
drwxr-xr-x  2 oracle dba 4.0K May 14 09:21 sqlpatch_2561578_2024_05_14_09_20_33
-rw-r--r--  1 oracle dba    0 May 23 18:12 36199232_25601966.lock
drwxr-xr-x  3 oracle dba 4.0K May 23 18:12 36199232
-rw-r--r--  1 oracle dba    0 May 23 18:12 36420641_25643730.lock
drwxr-xr-x  3 oracle dba 4.0K May 23 18:12 36420641
-rw-r--r--  1 oracle dba    0 May 23 18:12 36233263_25638263.lock
drwxr-xr-x  3 oracle dba 4.0K May 23 18:12 36233263
drwxr-xr-x  2 oracle dba 4.0K May 23 18:25 sqlpatch_4642_2024_05_23_18_11_41
drwxr-xr-x  2 oracle dba 4.0K Jul 12 10:30 sqlpatch_1073748_2024_07_12_10_13_30

cd sqlpatch_1073748_2024_07_12_10_13_30
 ls -altrh
total 75M
-rw-r--r--  1 oracle dba  12K Jul 12 10:14 install1.sql
-rw-------  1 oracle dba 3.7M Jul 12 10:26 sqlpatch_catcon_0.log
-rw-------  1 oracle dba  689 Jul 12 10:26 sqlpatch_catcon__catcon_1073748.lst
-rw-r--r--  1 oracle dba  12K Jul 12 10:26 sqlpatch_summary.json
-rw-r--r--  1 oracle dba  133 Jul 12 10:26 sqlpatch_progress.json
-rw-r--r--  1 oracle dba 5.5M Jul 12 10:26 sqlpatch_invocation.log
-rw-r--r--  1 oracle dba  66M Jul 12 10:26 sqlpatch_debug.log

The source of the error was found in the file sqlpatch_catcon_0.log:

CREATE OR REPLACE PACKAGE BODY ku$_dpload AS
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.SOURCE$ by 8192 in tablespace SYSTEM

This simple error caused the rest of the script to fail:

SQL> --
SQL> -- ============================================================================
SQL> -- BEGINNING OF APPLY EXECUTION
SQL> -- ============================================================================
SQL> --
SQL> -- Initial phase sets up internal infrastructure for rest of dpload.
SQL> --
SQL> SELECT ku$_dpload.prepare('setup') AS fname FROM sys.dual;
SELECT ku$_dpload.prepare('setup') AS fname FROM sys.dual
       *
ERROR at line 1:
ORA-04067: not executed, package body "SYS.KU$_DPLOAD" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "SYS.KU$_DPLOAD"
ORA-06512: at line 1

Obvious solution: Increase the tablespace datafile, or add a new file:

sqlplus / as sysdba
alter session set container=PDB01;
select f.file_name,f.bytes/1024/1024 "MB", f.autoextensible,f.maxbytes/1024/1024 "MaxMB", t.bigfile
from dba_data_files f, v$tablespace t
where f.tablespace_name='SYSTEM'
and f.tablespace_name = t.name;




FILE_NAME
MB
AUTOEXTENSIBLE
MaxMB
BIGFILE

/oradata/pdb01/system01.dbf
32712
YES
32767,984375
NO


So we have a smallfile tablespace which is full!


Add a datafile, still in the same session connected to the PDB01:
alter tablespace system 
add datafile '/oradata/pdb01/system02.dbf' size 256M autoextend on next 128M maxsize unlimited;

How to solve RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process

In one of my cloned databases, the FRA was filling up and pushing towards the limit of 2 TB.

Since it was cloned from a production database using Golden Gate, the capture processes followed along, but was not dropped in the cloned database.

This caused the deletion policy in RMAN to be overriden because Oracle thinks it will need the archivelogs for Golden Gate, even though no Golden Gate is configured for this particular database.

When deleting an archivelog, RMAN would throw an error:

RMAN> delete archivelog sequence 47447;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1352 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1354 device type=DISK
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u04/fra/CDB/archivelog/2024_03_13/o1_mf_1_47447__j5wx4r32_.arc thread=1 sequence=47447

The query below revealed the name of the blocking capture processes:

sqlplus / as sysdba --> log on to root container
SYS@_container_name SQL>  SELECT CAPTURE_NAME,
            CAPTURE_TYPE, STATUS,
            to_char(REQUIRED_CHECKPOINT_SCN,'999999999999999')as REQ_SCN ,
            to_char(OLDEST_SCN,'999999999999999')as OLDEST_SCN
     FROM DBA_CAPTURE;   2    3    4    5

CAPTURE_NAME         CAPTURE_TY STATUS   REQ_SCN          OLDEST_SCN
-------------------- ---------- -------- ---------------- ----------------
OGG$CAP_MYDB2ABC     LOCAL      DISABLED     426508588124     426508588124
OGG$CAP_MYDB2DEF     LOCAL      DISABLED     426508561845     426508561845

Solution was to use the package dbms_capture_adm and drop the captures:

sqlplus / as sysdba --> log on to root container

SYS@_container_name SQL> exec dbms_capture_adm.drop_capture('OGG$CAP_MYDB2ABC');
SYS@_container_name SQL> exec dbms_capture_adm.drop_capture('OGG$CAP_MYDB2DEF');

Thanks to Bobby Curtis for pointing me in the righ direction with his post about the same topic

Stupid mistake that causes TNS-12533: TNS:illegal ADDRESS parameters during tns names resolution

Consider the following tnsnames.ora file:

cdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver1.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb)
    )
  )


 pdb1 =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver1.oric.no)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = pdb1.oric.no)
        (INSTANCE_NAME = cdb)
      )
    )

A tnsping against the cdb would return

 tnsping cdb

Used parameter files:
$ORACLE_HOME/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myserver1.oric.no)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb))) pdb1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myserver1.oric.no)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1.oric.no) (INSTANCE_NAME = cdb) ) )
TNS-12533: TNS:illegal ADDRESS parameters

For the pdb1 the error was:

TNS-03505: Failed to resolve name

The solution is simple: open tnsnames.ora in an editor and remove the blank space erroniously indented before "pdb1":

 pdb1 =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver1.oric.no)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = pdb1.oric.no)
        (INSTANCE_NAME = cdb)
      )
    )

Make sure every tnsnames.ora entry starts at the very left end of the file, and this problem goes away.

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.