Skip to Main Content
  • Questions

  • SQLPLUS takes 10 seconds to connect locally as SYSDBA

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Marc.

Asked: January 24, 2017 - 9:30 pm UTC

Last updated: January 26, 2017 - 11:45 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Testing Oracle12c Standard Edition under Windows Server 2012R2 with Role Active Directory.

After the ORADIM -NEW -startmode MANUAL, I try a SQLPLUS SYS/pwd AS SYSDBA and it takes 10 seconds to connect to the idle instance.
I try with listener running and stopped.
ORACLE_SID is initialized.
I used procmon.exe to see what sqlplus.exe does and it has tried several times to connect via the network before it works.
Why sqlplus does not connect directly to oracle whithout trying the network ???

*** 2017-01-24 22:06:20.503
2017-01-24 22:06:20.503: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\olr cannot be opened. error 2
*** 2017-01-24 22:06:22.509
2017-01-24 22:06:22.509: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\olr cannot be opened. error 2
...

Output from PROCMON
"22:06:21,0329881","ORACLE.EXE","1660","TCP Reconnect","W2012R2.W2012R2dom.local:54344 -> W2012R2.W2012R2dom.local:61012","SUCCESS","Length: 0, seqnum: 0, connid: 0"
"22:06:21,5486093","ORACLE.EXE","1660","TCP Reconnect","W2012R2.W2012R2dom.local:54344 -> W2012R2.W2012R2dom.local:61012","SUCCESS","Length: 0, seqnum: 0, connid: 0"
"22:06:22,0642175","ORACLE.EXE","1660","TCP Reconnect","W2012R2.W2012R2dom.local:54345 -> W2012R2.W2012R2dom.local:61003","SUCCESS","Length: 0, seqnum: 0, connid: 0"
"22:06:22,5798467","ORACLE.EXE","1660","TCP Reconnect","W2012R2.W2012R2dom.local:54345 -> W2012R2.W2012R2dom.local:61003","SUCCESS","Length: 0, seqnum: 0, connid: 0"
"22:06:23,0954456","ORACLE.EXE","1660","TCP Reconnect","W2012R2.W2012R2dom.local:54346 -> W2012R2.W2012R2dom.local:61012","SUCCESS","Length: 0, seqnum: 0, connid: 0"
"22:06:23,5954550","ORACLE.EXE","1660","TCP Reconnect","W2012R2.W2012R2dom.local:54346 -> W2012R2.W2012R2dom.local:61012","SUCCESS","Length: 0, seqnum: 0, connid: 0"
"22:06:24,1110820","ORACLE.EXE","1660","TCP Reconnect","W2012R2.W2012R2dom.local:54347 -> W2012R2.W2012R2dom.local:61003","SUCCESS","Length: 0, seqnum: 0, connid: 0"
"22:06:24,6267100","ORACLE.EXE","1660","TCP Reconnect","W2012R2.W2012R2dom.local:54347 -> W2012R2.W2012R2dom.local:61003","SUCCESS","Length: 0, seqnum: 0, connid: 0"

Sorry for my english.
Thanks.

and Connor said...

You might be suffering from this issue

Slow Start of Sqlplus if Database is Down (Doc ID 2197760.1) To BottomTo Bottom 


APPLIES TO:

SQL*Plus - Version 12.1.0.2 and later
Information in this document applies to any platform.
SYMPTOMS

When database is down, sqlplus command has a delay of 1.5 minutes. 

However, when database is up, sqlplus responds immediately.

 

CHANGES

 Upgraded database from 11g to 12c.

( Fresh install of 12c DB does not show the issue.)

CAUSE

You are encountering known issue:

Bug 20562898 - CONN / AS SYSDBA TAKES MORE THAN A MINUTE ON A NOT STARTED INSTANCE
 


SOLUTION

The problem reproduces when neither of these files exist:

$ORACLE_HOME/network/admin/ldap.ora

or

$ORACLE_HOME/ldap/admin/ldap.ora


Workaround:

Copy the ldap.ora.sample file to ldap.ora.

 

Solution:

Apply patch for Bug 20562898.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Marc OTTEVAERE, January 25, 2017 - 11:44 am UTC

This is a fresh install of 12c and it is because the instance was not created, just the Windows service.
After the startup nomount, create database and create catalog, the problem dissapears.

But now the problem is for starting instance (startup), it take 4 minutes.
- connected at 12:36:35
- first log/trace created at 12:39:08
- disconnected at 12:40:41.
Why 3 minutes lost before starting Oracle ?
Wed Jan 25 12:39:08 2017
Starting ORACLE instance (normal) (OS id: 3976)

Why a lot of errors on ocr/olr for a standard database ?
2017-01-25 12:39:08.341: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\olr cannot be opened. error 2
2017-01-25 12:39:08.341: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\ocr cannot be opened. error 2
kwsnsmeInitNtfy:enter
kgfmIsAppliance=0 (due to init)
2017-01-25 12:39:08.373: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\olr cannot be opened. error 2
2017-01-25 12:39:08.373: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\ocr cannot be opened. error 2

*** 2017-01-25 12:39:09.388
2017-01-25 12:39:09.388: [ GIPCNET] gipcmodNetworkAsyncComplete: [network] error for endp 0000000001A534D0, req 0000000001A55250, type sgipcnreqtypeConnect (1), data 0000000000000000, sret sgipcretConnectionRefused (28)
2017-01-25 12:39:09.388: [ GIPCNET] gipcmodNetworkAsyncComplete: slos op : sgipcwWait
2017-01-25 12:39:09.388: [ GIPCNET] gipcmodNetworkAsyncComplete: slos dep : The remote computer refused the network connection. (1225)
2017-01-25 12:39:09.388: [ GIPCNET] gipcmodNetworkAsyncComplete: slos loc : GetQueueComp
2017-01-25 12:39:09.388: [ GIPCNET] gipcmodNetworkAsyncComplete: slos info: failed for over 0000000001A553A8
kgxgncin: clsssinit: CLSS init failed with status 3
kgxgncin: clsssinit: return status 3 (0 forced vacuo) from CLSS
2017-01-25 12:39:09.388: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\olr cannot be opened. error 2
2017-01-25 12:39:09.388: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\ocr cannot be opened. error 2
2017-01-25 12:39:09.388: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\olr cannot be opened. error 2
2017-01-25 12:39:09.388: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\ocr cannot be opened. error 2
NOTE: Cluster configuration type = NONE [2]
2017-01-25 12:39:09.497: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\olr cannot be opened. error 2
2017-01-25 12:39:09.497: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\ocr cannot be opened. error 2
Thks.
Connor McDonald
January 26, 2017 - 11:45 pm UTC

Did you try the advice from the metalink note ?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.