ORA-12514 caused by Archive log destination full in Oracle 19C:

ORA-12514 caused by Archive log destination full in Oracle 19C:

Symptom:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
[oracle@oraclevm01 dump]$ tnsping pdb01
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclevm01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb01_SERVICE)))
OK (0 msec)
[oracle@oraclevm01 dump]$ lsnrctl status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 08-OCT-2020 10:37:21
Uptime 0 days 0 hr. 18 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/product/grid/19.0/network/admin/listener.ora
Listener Log File /u01/app/admin/grid/diag/tnslsnr/oraclevm01/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclevm01.2vkltewovmnedaj0lyrd3oocle.ax.internal.cloudapp.net)(PORT=1521)))
Services Summary…
Service “+ASM” has 1 instance(s).
Instance “+ASM”, status READY, has 1 handler(s) for this service…
Service “+ASM_ARCH” has 1 instance(s).
Instance “+ASM”, status READY, has 1 handler(s) for this service…
Service “+ASM_DATA” has 1 instance(s).
Instance “+ASM”, status READY, has 1 handler(s) for this service…
Service “+ASM_FLASH” has 1 instance(s).
Instance “+ASM”, status READY, has 1 handler(s) for this service…
Service “+ASM_REDO” has 1 instance(s).
Instance “+ASM”, status READY, has 1 handler(s) for this service…
Service “cdb01” has 1 instance(s).
Instance “cdb01”, status UNKNOWN, has 1 handler(s) for this service…
Service “cdb01_A” has 1 instance(s).
Instance “cdb01”, status READY, has 1 handler(s) for this service…
Service “b026d6c9b67804f7e05345000a6eb23c” has 1 instance(s).
Instance “cdb01”, status READY, has 1 handler(s) for this service…
Service “pdb01” has 1 instance(s).
Instance “cdb01”, status READY, has 1 handler(s) for this service…
The command completed successfully

Debug:
1. Check service:
[grid@oraclevm01 ~]$ srvctl status service -db cdb01_A
Service pdb01_service is not running.
[grid@oraclevm01 ~]$ srvctl status service -db cdb01_A -service pdb01_service
Service pdb01_SERVICE is not running.
[grid@oraclevm01 ~]$ srvctl start service -db cdb01_A -service pdb01_service
PRCD-1084 : Failed to start service pdb01_SERVICE
PRCR-1079 : Failed to start resource ora.cdb01_a.pdb01_service.svc
CRS-2800: Cannot start resource ‘ora.cdb01_a.db’ as it is already in the INTERMEDIATE state on server ‘oraclevm01’
[grid@oraclevm01 ~]$ srvctl config service -db cdb01_A
Service name: pdb01_SERVICE
Cardinality: SINGLETON
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
Failover retries:
Failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name: pdb01
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
[grid@oraclevm01 ~]$ crsctl stat res -t
——————————————————————————–
Name Target State Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.ARCH.dg
ONLINE ONLINE oraclevm01 STABLE
ora.DATA.dg
ONLINE ONLINE oraclevm01 STABLE
ora.FLASH.dg
ONLINE ONLINE oraclevm01 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE oraclevm01 STABLE
ora.REDO.dg
ONLINE ONLINE oraclevm01 STABLE
ora.asm
ONLINE ONLINE oraclevm01 STABLE
ora.ons
ONLINE ONLINE oraclevm01 STABLE
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cdb01_a.db
1 ONLINE INTERMEDIATE oraclevm01 Stuck Archiver,HOME=
/u01/app/product/db/
19.0,STABLE
ora.cdb01_a.pdb01_service.svc
1 ONLINE OFFLINE STABLE
ora.cssd
1 ONLINE ONLINE oraclevm01 STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE oraclevm01 STABLE
——————————————————————————–

[grid@oraclevm01 ~]$ crsctl stat res ora.cdb01_a.db -t
——————————————————————————–
Name Target State Server State details
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cdb01_a.db
1 ONLINE INTERMEDIATE oraclevm01 Stuck Archiver,HOME=
/u01/app/product/db/
19.0,STABLE
[grid@oraclevm01 ~]$ srvctl status database -d cdb01_A
Database is running.

SQL> alter system disable restricted session;
SQL> set lines 190
set pages 150
select host_name,name,instance_name,database_role,status,open_mode,logins from v$instance,v$database;SQL> SQL>

HOST_NAME NAME INSTANCE_NAME DATABASE_ROLE STATUS OPEN_MODE LOGINS
—————————————————————- ——— —————- —————- ———— ——————– ———-
oraclevm01 cdb01 cdb01 PRIMARY OPEN READ WRITE ALLOWED

[grid@oraclevm01 ~]$ crsctl stop resource -all
[grid@oraclevm01 ~]$ crsctl stop resource -all
CRS-2500: Cannot stop resource ‘ora.diskmon’ as it is not running
CRS-2673: Attempting to stop ‘ora.ARCH.dg’ on ‘oraclevm01’
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘oraclevm01’
CRS-2673: Attempting to stop ‘ora.FLASH.dg’ on ‘oraclevm01’
CRS-2673: Attempting to stop ‘ora.REDO.dg’ on ‘oraclevm01’
CRS-2673: Attempting to stop ‘ora.cdb01_a.db’ on ‘oraclevm01’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘oraclevm01’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘oraclevm01’
CRS-2673: Attempting to stop ‘ora.ons’ on ‘oraclevm01’
CRS-2789: Cannot stop resource ‘ora.cdb01_a.pdb01_service.svc’ as it is not running on server ‘oraclevm01’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘oraclevm01’ succeeded
CRS-2677: Stop of ‘ora.ons’ on ‘oraclevm01’ succeeded
CRS-2677: Stop of ‘ora.DATA.dg’ on ‘oraclevm01’ succeeded
CRS-2677: Stop of ‘ora.FLASH.dg’ on ‘oraclevm01’ succeeded
CRS-2677: Stop of ‘ora.REDO.dg’ on ‘oraclevm01’ succeeded
CRS-2677: Stop of ‘ora.ARCH.dg’ on ‘oraclevm01’ succeeded
CRS-2677: Stop of ‘ora.evmd’ on ‘oraclevm01’ succeeded
CRS-2677: Stop of ‘ora.cdb01_a.db’ on ‘oraclevm01’ succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘oraclevm01’
CRS-2677: Stop of ‘ora.asm’ on ‘oraclevm01’ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘oraclevm01’
CRS-2677: Stop of ‘ora.cssd’ on ‘oraclevm01’ succeeded
CRS-4000: Command Stop failed, or completed with errors.
[grid@oraclevm01 ~]$ crsctl stat res -t
——————————————————————————–
Name Target State Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.ARCH.dg
OFFLINE OFFLINE oraclevm01 STABLE
ora.DATA.dg
OFFLINE OFFLINE oraclevm01 STABLE
ora.FLASH.dg
OFFLINE OFFLINE oraclevm01 STABLE
ora.LISTENER.lsnr
OFFLINE OFFLINE oraclevm01 STABLE
ora.REDO.dg
OFFLINE OFFLINE oraclevm01 STABLE
ora.asm
OFFLINE OFFLINE oraclevm01 Instance Shutdown,ST
ABLE
ora.ons
OFFLINE OFFLINE oraclevm01 STABLE
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cdb01_a.db
1 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
ora.cdb01_a.pdb01_service.svc
1 OFFLINE OFFLINE STABLE
ora.cssd
1 OFFLINE OFFLINE STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 OFFLINE OFFLINE STABLE
——————————————————————————–
[grid@oraclevm01 ~]$
[grid@oraclevm01 ~]$ crsctl start resource -all
CRS-2672: Attempting to start ‘ora.LISTENER.lsnr’ on ‘oraclevm01’
CRS-2672: Attempting to start ‘ora.cssd’ on ‘oraclevm01’
CRS-2672: Attempting to start ‘ora.diskmon’ on ‘oraclevm01’
CRS-2672: Attempting to start ‘ora.ons’ on ‘oraclevm01’
CRS-2672: Attempting to start ‘ora.evmd’ on ‘oraclevm01’
CRS-2676: Start of ‘ora.diskmon’ on ‘oraclevm01’ succeeded
CRS-2676: Start of ‘ora.evmd’ on ‘oraclevm01’ succeeded
CRS-2676: Start of ‘ora.LISTENER.lsnr’ on ‘oraclevm01’ succeeded
CRS-2676: Start of ‘ora.ons’ on ‘oraclevm01’ succeeded
CRS-2676: Start of ‘ora.cssd’ on ‘oraclevm01’ succeeded
CRS-2672: Attempting to start ‘ora.asm’ on ‘oraclevm01’
CRS-5017: The resource action “ora.asm start” encountered the following error:
ORA-27106: system pages not available to allocate memory
Additional information: 6125
Additional information: 1
. For details refer to “(:CLSN00107:)” in “/u01/app/admin/grid/diag/crs/oraclevm01/crs/trace/ohasd_oraagent_grid.trc”.
CRS-2674: Start of ‘ora.asm’ on ‘oraclevm01’ failed
CRS-2679: Attempting to clean ‘ora.asm’ on ‘oraclevm01’
CRS-2681: Clean of ‘ora.asm’ on ‘oraclevm01’ succeeded
CRS-2672: Attempting to start ‘ora.asm’ on ‘oraclevm01’
CRS-5017: The resource action “ora.asm start” encountered the following error:
ORA-27106: system pages not available to allocate memory
Additional information: 6125
Additional information: 1
. For details refer to “(:CLSN00107:)” in “/u01/app/admin/grid/diag/crs/oraclevm01/crs/trace/ohasd_oraagent_grid.trc”.
CRS-2674: Start of ‘ora.asm’ on ‘oraclevm01’ failed
CRS-2679: Attempting to clean ‘ora.asm’ on ‘oraclevm01’
CRS-2681: Clean of ‘ora.asm’ on ‘oraclevm01’ succeeded
CRS-2672: Attempting to start ‘ora.asm’ on ‘oraclevm01’
CRS-2672: Attempting to start ‘ora.diskmon’ on ‘oraclevm01’
CRS-2676: Start of ‘ora.diskmon’ on ‘oraclevm01’ succeeded
CRS-5017: The resource action “ora.asm start” encountered the following error:
ORA-27106: system pages not available to allocate memory
Additional information: 6125
Additional information: 1

Check ASM:
[grid@oraclevm01 ~]$ asmcmd
ASMCMD> lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 4096 512 4096 4194304 32768 36 0 36 0 N ARCH/
MOUNTED EXTERN N 4096 512 4096 4194304 98304 30156 0 30156 0 N DATA/
MOUNTED EXTERN N 4096 512 4096 4194304 32768 19480 0 19480 0 N FLASH/
MOUNTED EXTERN N 4096 512 4096 4194304 32768 32228 0 32228 0 N REDO/

Solution:
Purge old archive log.
[oracle@oraclevm01 dump]$ crsctl stat res -t
——————————————————————————–
Name Target State Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.ARCH.dg
ONLINE ONLINE oraclevm01 STABLE
ora.DATA.dg
ONLINE ONLINE oraclevm01 STABLE
ora.FLASH.dg
ONLINE ONLINE oraclevm01 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE oraclevm01 STABLE
ora.REDO.dg
ONLINE ONLINE oraclevm01 STABLE
ora.asm
ONLINE ONLINE oraclevm01 Started,STABLE
ora.ons
ONLINE ONLINE oraclevm01 STABLE
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cdb01_a.db
1 ONLINE ONLINE oraclevm01 Open,HOME=/u01/app/product/db/19.0,STABLE
ora.cdb01_a.pdb01_service.svc
1 ONLINE ONLINE oraclevm01 STABLE
ora.cssd
1 ONLINE ONLINE oraclevm01 STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE oraclevm01 STABLE

Reference:
https://docs.oracle.com/database/121/CWADD/GUID-41D9E921-F6FB-41C8-B87F-E71B44386192.htm#CWADD92237
https://docs.oracle.com/database/121/RACAD/GUID-B3BB6D75-C02F-4E97-B50A-210EBC214067.htm#RACAD8911

Leave a Reply

Your email address will not be published. Required fields are marked *