ORA-12519: TNS:no appropriate service handler found

ORA-12519: TNS: no appropriate service handler found

Cause: Number of session and process reached the MAX_UTILIZATION level when listener find the current number of connections has reached maximum load it may set the state of the service handler for an instance to "blocked" and begin refusing incoming client connections.

Solution: Check the value of process parameter and increase the value of the parameter.


[grid@DB ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-SEP-2017 13:32:38

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1158 refused:0 state:ready
         LOCAL SERVER
Service "SPFTLDB" has 1 instance(s).
  Instance "SPFTLDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:73880 refused:0 state:blocked
         LOCAL SERVER
Service "SPFTLDBXDB" has 1 instance(s).
  Instance "SPFTLDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: DB.spftl.com, pid: 18815>
         (ADDRESS=(PROTOCOL=tcp)(HOST=db.spftl.com)(PORT=43078))
The command completed successfully

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
db_writer_processes                  integer     3
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     150
SQL> ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;

System altered.

SQL>

Bounce the database.

Hanging ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Cause: Archive log are not applying into standby database but recovery process (MRP) are active, in this situation I decided to restart recovery process but whenever I am applying  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL, the command itself hanging situation.

Solution: Rather than bouncing the standby database just find and kill the MRP process and start the media recovery process.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL>

Log Details:

Archived Log entry 20997 added for thread 1 sequence 11504 ID 0xffffffffaec2031b dest 1:

Thu Jul 27 10:33:23 2017

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Thu Jul 27 10:45:35 2017

Managed Standby Recovery Canceled (erpdb)

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Thu Jul 27 10:46:23 2017

Recovery coordinator died, shutting down parallel recovery

Thu Jul 27 10:46:25 2017

RFS[516]: Selected log 13 for thread 1 sequence 11506 dbid -********** branch 928567359

Thu Jul 27 10:46:28 2017

Archived Log entry 20998 added for thread 1 sequence 11505 ID 0xffffffffaec2031b dest 1:

Thu Jul 27 10:46:41 2017

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

ORA-1153 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION...

Thu Jul 27 10:46:59 2017

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Thu Jul 27 10:47:24 2017

RFS[514]: Selected log 21 for thread 2 sequence 11340 dbid -********** branch 928567359

Thu Jul 27 10:47:27 2017

Archived Log entry 20999 added for thread 2 sequence 11339 ID 0xffffffffaec2031b dest 1:

Thu Jul 27 10:47:35 2017

Logmerger died, shutting down parallel recovery slaves

Recovery interrupted!

Recovery interrupted!

Thu Jul 27 10:47:37 2017

Managed Standby Recovery Canceled (erpdb)

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Thu Jul 27 10:48:08 2017

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

Attempt to start background Managed Standby Recovery process (erpdb)

Thu Jul 27 10:48:08 2017

MRP0 started with pid=38, OS id=9764900

MRP0: Background Managed Standby Recovery process started (erpdb)

 started logmerger process

Thu Jul 27 10:48:14 2017

Managed Standby Recovery starting Real Time Apply

Parallel Media Recovery started with 32 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

Media Recovery Log +DG_FRA/erpdb/archivelog/2017_07_25/thread_2_seq_11299.1849.950272469

Media Recovery Log +DG_FRA/erpdb/archivelog/2017_07_25/thread_1_seq_11451.1854.950272385

MRP0: Background Media Recovery process shutdown

Errors with log +FRA/erpdrdb/archivelog/2017_06_02/thread_2_seq_9952.2503.945625685
MRP0: Background Media Recovery terminated with error 10485
Errors in file /u01/app/oracle/diag/rdbms/erpdrdb/erpdrdb/trace/erpdrdb_pr00_9109800.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (erpdrdb)


Cause: After starting the database as upgrade mode you have to disconnect the recovery database.

Solution: Disconnect the recovery database, shutdown, startup and start the recovery process.

SQL> alter database recover managed standby database disconnect ;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 6.3069E+10 bytes
Fixed Size                  2393728 bytes
Variable Size            8388610432 bytes
Database Buffers         5.4660E+10 bytes
Redo Buffers               18116608 bytes
Database mounted.
SQL> alter database recover managed standby database using current logfile disconnect ;

Database altered.

SQL>