How to check if your PostgreSQL server is part of a replication setup

If the server is down, check the entries in postgresql.auto.conf.

The following postgres.auto.conf is from a slave server:

wal_level = 'replica'
hot_standby = on
hot_standby_feedback = 'on'
primary_slot_name = 'stby1'
max_connections = 636

primary_conninfo      = 'host=pgserver01.oric.no port=5432 user=replicate password=mysecretpassword'
promote_trigger_file  = '/tmp/MasterNow'

The master server may have a very simple postgres.auto.conf file:

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
wal_level = 'replica'

If the server is up, use queries:

1. On the primary server, check that data is shipped over to the replica server:

[postgres@pgserver01.oric.no /pgdata/data]$ echo 'select usename,state,sync_state,sync_priority,client_hostname from pg_stat_replication;' | psql
  usename  |   state   | sync_state | sync_priority |        client_hostname
-----------+-----------+------------+---------------+--------------------------------
 replicate | streaming | async      |             0 | pgserver02.oric.no
(1 row)

2. The following query should return f (false) on primary server, t (true) on the replica server:

[postgres@pgserver01 /pgdata/data/log]$ echo "select pg_is_in_recovery();" | psql
 pg_is_in_recovery
-------------------
 f
(1 row)

[postgres@pgserver02 /pgdata/data/log]$  echo "select pg_is_in_recovery();" | psql
 pg_is_in_recovery
-------------------
 t
(1 row)

How to check StaticConnectIdentifier and other properties using Data Guard Broker

DGMGRL> show database cdb 'StaticConnectIdentifier'
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oricdb01.oric.no)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cdb_DGMGRL.skead.no)(INSTANCE_NAME=cdb)(SERVER=DEDICATED)))'

There are several other properties that you can check, too. See this post for a couple of examples

The documentation differntiates between monitorable (read only) and configurable properties.

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