Sev1: OCI Base Database apps fail with ORA-12514 after patching. Listener and service registration recovery

#Symptoms

• Applications: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor or ORA-12541: TNS:no listener.
• Started right after a DB system patch, a database home patch, or a node reboot.
• The DB system shows Available in the console.

#Step 1: is the database open? (2 minutes)

SSH to the DB node as opc, then:

sudo su - oracle
. oraenv <<< ORCL          # your DB name / SID
sqlplus -s / as sysdba <<'SQL'
select instance_name, status, database_role, open_mode from v$instance, v$database;
show pdbs
SQL

#Step 2: check the listener and services

sudo su - grid          # listener runs from the Grid home on DB systems
lsnrctl status
lsnrctl services | grep -i "service\|instance" | head -40

Is the application service listed? If not, the database has not registered it.

As oracle:

srvctl status database -d $(srvctl config database | head -1)
srvctl config service -d ORCL_xyz
srvctl status service -d ORCL_xyz

srvctl config database lists the unique database name (often with a suffix on OCI).

#Step 3: open PDBs and save state

alter pluggable database all open;
alter pluggable database all save state;

save state makes PDBs reopen after the next restart. Missing saved state is a frequent cause of ORA-12514 after patching: the PDB stays mounted and its services do not register.

#Step 4: start application services

Services managed by Clusterware do not always start automatically after a patch, especially services pinned to a PDB or with a specific management policy:

srvctl start service -d ORCL_xyz -s orders_app
srvctl status service -d ORCL_xyz -s orders_app
srvctl modify service -d ORCL_xyz -s orders_app -policy AUTOMATIC

If the service does not exist in Clusterware but the application expects it (it was created with DBMS_SERVICE only):

alter session set container = ORDERSPDB;
exec dbms_service.start_service('orders_app');

Prefer re-creating it with srvctl so it survives restarts:

srvctl add service -d ORCL_xyz -s orders_app -pdb ORDERSPDB -preferred ORCL1 -policy AUTOMATIC
srvctl start service -d ORCL_xyz -s orders_app

#Step 5: force registration

If the service exists and is running but the listener does not show it:

show parameter local_listener
show parameter remote_listener
alter system register;

local_listener should point at the node's listener address. After some patches it can be reset or point to the wrong address; correct it:

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.3.15)(PORT=1521))' scope=both sid='ORCL1';
alter system register;

#Step 6: listener not running (ORA-12541)

sudo su - grid
srvctl status listener
srvctl start listener
lsnrctl status

If it fails to start, check the listener log ($ORACLE_BASE/diag/tnslsnr/<host>/listener/trace/listener.log) for port conflicts or listener.ora errors after the patch.

Also confirm port 1521 is open on the DB system's NSG/security list and in the OS firewall:

sudo firewall-cmd --list-ports 2>/dev/null; sudo iptables -L -n | grep 1521

Warning

Do not edit listener.ora by hand on a Grid-managed listener unless Oracle Support tells you to. Use srvctl so Clusterware stays authoritative.

#Step 7: confirm from the client side

tnsping "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbnode.sub.vcn.oraclevcn.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orders_app.sub.vcn.oraclevcn.com)))"
sqlplus app_user@"dbnode.sub.vcn.oraclevcn.com:1521/orders_app.sub.vcn.oraclevcn.com"

Note the fully qualified service name: on OCI DB systems services usually include the DB domain. Clients using the short name get ORA-12514 even when everything is up.

#Prevent it next time

• After every patch run a scripted check: PDBs open, services running, listener knows each application service.
• save state for PDBs, -policy AUTOMATIC for services.
• Application connection strings use the fully qualified service name, and connect through services (not SIDs).
• Patch standby or a non-production DB system first and run the same check.