This forum is currently locked. You can't register or post questions at this time. (read more)

ORA 12564 Connection Refused

All posts relating to Oracle database administration.

Moderator: Tim...

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

ORA 12564 Connection Refused

Postby jnrpeardba » Fri Apr 26, 2013 11:40 am

Hi Tim,

I have noticed in my alert log a number of entries resulting in ORA 12564 errors

This is my primary node and it appears it is refusing connections to the standby. However logs are being shipped and applied successfully even with this errors

This is what is contained in the alert log. I should also point out that none of the other instances which use the same servers are experiencing these issues / errors

Code: Select all

Fatal NI connect error 12514, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=pipdlr3hdb02)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dsprd01_stby_DGB)(UR=A)(
CID=(PROGRAM=oracle)(HOST=pipdl3rhdb01)(USER=oracle))))

VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 25-APR-2013 17:35:27
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564

TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0

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


And yet logs are shopped and applied successfully to the standby node (db02)

Code: Select all

SQL> show parameter sessions

sessions                             integer     168

SQL> show parameter processes

processes                            integer     90

SQL> select resource_name, max_utilization from v$resource_limit where resource_name in ('processes','sessions');

RESOURCE_NAME                  MAX_UTILIZATION
------------------------------ ---------------
processes                                   90
sessions                                   133


My tnsnames.ora on primary (where the alert are being generated)

Code: Select all

DSPRD01 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pipdl3rhdb01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dsprd01)
      (UR = A)
    )
  )


DSPRD01_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pipdlr3hdb02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dsprd01_stby)
      (UR = A)
    )
  )



Is there anything that you may suggest to point me in the right direction

Many thanks

Jnrpeardba

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA 12564 Connection Refused

Postby Tim... » Fri Apr 26, 2013 1:26 pm

Hi.

It looks like your process limit is very low. Making a connection involves spawning new processes. That will a cause a problem if you hit the limit. I would definitely up that.

This won't affect applying of redo to the standby, since it will just be retried until the processes are below the limit.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: ORA 12564 Connection Refused

Postby jnrpeardba » Mon Apr 29, 2013 11:02 am

Many thanks Tim,

I've set the process and sessions parameter's accordingly via spfile and will reboot later and check :lol:

Jnrpeardba

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA 12564 Connection Refused

Postby Tim... » Mon Apr 29, 2013 11:13 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: ORA 12564 Connection Refused

Postby jnrpeardba » Wed May 01, 2013 7:45 am

Hi Tim,

I increased the processes to 150 and sessions to 264, but still receiving those tns errors.

I will increase both parameters again, bounce and inform you if those messages have been removed

regards

Jnrpeardba

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA 12564 Connection Refused

Postby Tim... » Wed May 01, 2013 9:22 am

Hi.

Are you hitting your max processes after the last change though? If not, then this will not be the cause of the issue...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: ORA 12564 Connection Refused

Postby jnrpeardba » Thu May 02, 2013 9:31 am

Morning Tim,

in response to your previous point, if you are asking whether I am experiencing the error

Code: Select all

ORA-00020: maximum number of processes exceeded

then that is not the case. And you may be correct that it is not a process or sessions issue.

Code: Select all

SQL> select * from v$resource_limit;

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOC
------------------------------ ------------------- --------------- -------------
processes                                       69              77        300
sessions                                       105             119        504
enqueue_locks                                   31             102       6430
enqueue_resources                               26              52       2436
ges_procs                                        0               0          0
ges_ress                                         0               0          0
ges_locks                                        0               0          0
ges_cache_ress                                   0               0          0
ges_reg_msgs                                     0               0          0
ges_big_msgs                                     0               0          0
ges_rsv_msgs                                     0               0          0

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOC
------------------------------ ------------------- --------------- -------------
gcs_resources                                    0               0  UNLIMITED
gcs_shadows                                      0               0  UNLIMITED
smartio_overhead_memory                          0               0          0
smartio_buffer_memory                            0               0          0
smartio_metadata_memory                          0               0          0
smartio_sessions                                 0               0          0
dml_locks                                        0               0       2216
temporary_table_locks                            0               3  UNLIMITED
transactions                                     0               0        554
branches                                         0               0        554
cmtcallbk                                        0               5        554

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOC
------------------------------ ------------------- --------------- -------------
max_rollback_segments                           11              11        554
sort_segment_locks                               0               6  UNLIMITED
k2q_locks                                        0               0       1008
max_shared_servers                               1               1  UNLIMITED
parallel_max_servers                             0               0        285

27 rows selected.



Last night I increased both the processes and sessions parameter, bounced the db and this morning I am still seeing the same errors in the alert.log file.

In addition, one of our applications on a difference db, received a network time-out error this morning and generated an ORA-609 and TNS 12537.
Researching this error gives a solution of increasing the sqlnet.ora and listener.ora connect_timeout values from the default of 60.

I am wondering, whether the two issues are inter-linked and the initial problem / error is not a process issue but more of a time-out issue

Any thoughts much appreciated,

Jnrpeardba :)
Last edited by jnrpeardba on Thu May 02, 2013 10:02 am, edited 1 time in total.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA 12564 Connection Refused

Postby Tim... » Thu May 02, 2013 9:43 am

Hi.

MOS has a general note on troubleshooting this error:

https://support.oracle.com/epmos/faces/ ... d=555609.1

You might want to work your way through that.

The fact you've started to see this on another DB makes me a little suspicious about your network. Are you sure you are not having network issues here?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: ORA 12564 Connection Refused

Postby jnrpeardba » Thu May 02, 2013 10:15 am

Hi Tim,

I will have a look at the note you sent but as for network issue, not all support staff are as meticulous as us DBA personnel :D

I have already asked the network and OS guys if there were any issues with the network and specifically can they see an error which occurred
at 08:15 this morning, needless to say the response was..... let's just say I will continue to investigate from my end

thanks again for your input

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA 12564 Connection Refused

Postby Tim... » Thu May 02, 2013 12:13 pm

Hi.

The note includes enabling trace, so you will be able to detect network problems and tell them about them, if they are the cause. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 3 guests

cron