VALIDATE STATIC CONNECT IDENTIFIER failed for Primary Database - Dataguard

 I have been encountered below issue while validating static connect identifier.

ORA-12520: TNS:listener could not find available handler for requested type of server

DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR all;

Oracle Clusterware is not configured on database "ORCL11".

Connecting to database "ORCL11" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=198.164.1.74)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL11)(INSTANCE_NAME=ORCL11)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...

Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=198.164.1.74)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL11)(INSTANCE_NAME=ORCL11)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))

ORA-12520: TNS:listener could not find available handler for requested type of server

Failed.

Oracle Clusterware is not configured on database "ORCL11db".

Connecting to database "ORCL11db" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORCL11DBA01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL11DB)(INSTANCE_NAME=ORCL11DB)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...

Succeeded.

The static connect identifier allows for a connection to database "ORCL11db".

Checked : Below was my listener.ora file which was Dynamic that's why I was getting error  

$ cat listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ORCL11DBA01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

Solution :- 

I have added entry and made the statis listener 

$ cat listener.ora

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = ORCL11DBA01)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER=

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = ORCL11DB)

      (ORACLE_HOME = /applications/oracle/19.3.0.0)

      (SID_NAME =ORCL11DB)

    )

)

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

After this ORA-12520: TNS:listener could not find available handler for requested type of server issue resolved 

DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR all;

Oracle Clusterware is not configured on database "ORCL11".

Connecting to database "ORCL11" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=198.164.1.74)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL11)(INSTANCE_NAME=ORCL11)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...

Succeeded.

The static connect identifier allows for a connection to database "ORCL11".

Oracle Clusterware is not configured on database "ORCL11db".

Connecting to database "ORCL11db" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORCL11DBA01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL11DB)(INSTANCE_NAME=ORCL11DB)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...

Succeeded.

The static connect identifier allows for a connection to database "ORCL11db".

Now I can do switchover and switchback using dgmgrl utility :)

Happy Learning ...!!!

Database performance Note-1

 High resource intensive SQL queries.

**************************************

SQL_ID : 4ff82tr5w35f2 

SQL profiling is done with best available plan in OLTP production Database . 

Please find below current execution details . We will keep a eye on this SQL .

SQL_ID        MODULE     PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS    Bg/gets   Ela/Exec DT

------------- ---------- --------------- ------------ ---------- ---------- ---------- ------------

4ff82tr5w35f2 siebmtshmw      3743305940            1        147  125.85034 .002336075 02-SEP 15:25

SQL_ID : an5zgpp56sqd9 &cjdakywq1grpa

execution Plan of the SQl is already optimised , Execution of the SQL is high . 

So if any further filters can be applied which can further reduce dataset it is retuning can help . 

High Disk File IO Tablespace

*****************************

1. SIEBEL_DATA & SIEBE_INDEX  are kept at slow speed disc ( decision taken by management) also 

the IO ( read and write on these tablespaces is minimal 

( hardly 3-4 users have access to this data at any day) .

This decision is to be taken by ASG management if Archived data needs to be moved to High speed disk .

2. We can plan to move indexes from SIEBEL_DATA to SIEBEL_INDEX.

3. Stats Gather was done just after completion of archiving cycle . After that no data is changed in tables of these schema . 

  I would request Oracle to suggest if we need to keep gathering stats for static tables as well on weekly basis as best practices.

  

High Fragmentation

*****************************

In OLTP production database we have multiple time tried De fragmentation of tables ( along with ACS team - Santosh kumar) 

using traditional defragmentaion method ( Alter table <> move ) and also using Online Reorg . 

But in both cases we endup having bigger table size then before activity (ie. fragmentation further increases).

We have already raised a SR with oracle on this.

Please suggest if there is any other approach which can be followed to achieve desired results .

ITL Waits

**************

We can implement the change. But as users will keep increasing , concurrency will also increase hence we have to prepare 

a benchmarking till what value we can increase INITRANS for listed indexes in recommendation from Oracle.

Stale Gather Statistics

*****************************

We have stats gather policy implemented for All siebel tables( application) - Weekly Stats gather every sunday Night .

Please suggest if we need to gather stats for all objects/Tables in database on weekly basis even if these are static table. 

If yes then we have to get new Stats gather script generated from Oracle End to be implemented in Production.

SGA Analysis

*****************************

We can go ahead and implement changes suggested . As we are just increasing values of different component without 

increasing total memory allotted for OLTP instance .