ORA-12514 within a RAC Environment

Questions relating to Oracle Real Application Clusters (RAC) and Clusterware.

Moderator: Tim...

ORA-12514 within a RAC Environment

Postby jnrpeardba » Wed Oct 03, 2012 12:22 pm

Hi Tim,

I have a 2-node RAC working fine on an AIX box. I'm in the process of setting up RMAN to be used with TSM etc...
One of the phases is to verify the connectivity to the target nodes and catalog - performing an rman/target @ blah blah works fine and both db's are registered alongside the necessary parameters - so far so good

When I attempt to test the connection I receive an ORA-12514 error, but the tnsnames.ora is fine and the lsnrctl status and services seems fine
I have tried to provide as much info as possible in the hope that it is a simple fix if you can assist.

Code: Select all
oracle@rvecrcdrprep > lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 03-OCT-2012 12:53:26

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecrcdrprep.hscni.net)(PORT=1521)))
TNS-01189: The listener could not authenticate the user
oracle@rvecrcdrprep > id
uid=203(oracle) gid=204(oinstall) groups=205(dba),206(oper),208(asmdba),209(asmadmin),210(asmoper)

oracle@rvecrcdrprep > lsnrctl services

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 03-OCT-2012 12:54:01

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecrcdrprep.hscni.net)(PORT=1521)))
TNS-01189: The listener could not authenticate the user
oracle@rvecrcdrprep > lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 03-OCT-2012 12:54:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecrcdrprep.hscni.net)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
Start Date                27-SEP-2012 14:40:01
Uptime                    5 days 22 hr. 14 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /mware/oracle/11.2/grid/network/admin/listener.ora
Listener Log File         /mware/oracle/11.2/grid/log/diag/tnslsnr/rvecrcdrprep/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.210.2.126)(PORT=1521)))
Services Summary...
Service "CDRPREP" has 2 instance(s).
  Instance "CDRPREP1", status READY, has 1 handler(s) for this service...
  Instance "CDRPREP2", status READY, has 1 handler(s) for this service...
Service "CDRPREPXDB" has 2 instance(s).
  Instance "CDRPREP1", status READY, has 1 handler(s) for this service...
  Instance "CDRPREP2", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@rvecrcdrprep >


Attempting connection into the database as sys - this will be required for rman backup within RAC

Code: Select all
oracle@rvecrcdrprep > sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Oct 3 12:56:33 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> connect sys/password@cdrprep1 as sysdba
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

SQL> connect sys/password@cdrprep2 as sysdba
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


But I have no trouble connecting to the database

Code: Select all
oracle@rvecrcdrprep > sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Wed Oct 3 12:57:12 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
CDRPREP1         OPEN

SQL>


So I checked my tnsnames.ora

Code: Select all
oracle@rvecrcdrprep > more tnsnames.ora
# tnsnames.ora Network Configuration File: /mware/oracle/11.2/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CDRPREP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ecrcdrprep.hscni.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDRPREP)
    )
  )

CDRPREP2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bvecrcdrprep.hscni.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDRPREP2)
    )
  )

CDRPREP1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rvecrcdrprep.hscni.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDRPREP1)
    )
  )

BSORMANCAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oralive1.hpss.n-i.nhs.uk)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BSORMANCAT)
    )
  )

$


Then I looked at the listener parameter

Code: Select all
SQL> show parameter local listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
log_archive_local_first              boolean     TRUE
parallel_force_local                 boolean     FALSE
SQL>


Having looked through some of the posts in this forum it seems like it may be an issue with the service name within the tnsnames.ora file. Firstly is this the case and second if so, what do I change that to ?

Many thanks in advance

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

Re: ORA-12514 within a RAC Environment

Postby Tim... » Wed Oct 03, 2012 4:25 pm

Hi.

Forget about everything else and start with just tnsping first. Make sure you can tnsping the locations before you worry about trying to make connections. Check the hostname, port and service information is consistent in your listener and tnanames configuration.

If this looks correct and you stil can't connect, you need to check things aren't getting blocked by a firewall.

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
Tim...
Site Admin
 
Posts: 17951
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: ORA-12514 within a RAC Environment

Postby jnrpeardba » Wed Oct 03, 2012 4:41 pm

Hi Tim,

thanks,

so I issued a tnsping from the oracle account first to the cluster name, then to the first node and then to the 2nd node in the cluster. I did this from the first node in the cluster. Seemed to work fine

Code: Select all
oracle@rvecrcdrprep > tnsping cdrprep

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 03-OCT-2012 17:35:49

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
/mware/oracle/11.2/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ecrcdrprep.hscni.net)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDRPREP)))
OK (120 msec)
oracle@rvecrcdrprep > tnsping cdrprep1

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 03-OCT-2012 17:35:55

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
/mware/oracle/11.2/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rvecrcdrprep.hscni.net)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDRPREP1)))
OK (0 msec)
oracle@rvecrcdrprep > tnsping cdrprep2

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 03-OCT-2012 17:36:02

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
/mware/oracle/11.2/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bvecrcdrprep.hscni.net)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDRPREP2)))
OK (0 msec)
oracle@rvecrcdrprep >

And having checked with the Network and Systems team there is no firewall in place

Hope this helps
jnrpeardba
Advisor
 
Posts: 392
Joined: Wed May 04, 2011 3:14 pm

Re: ORA-12514 within a RAC Environment

Postby Tim... » Wed Oct 03, 2012 5:08 pm

Hi.

I'm a little confused then. So you can tnsping OK, but when you try to make a connection it fails using the same service name?

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
Tim...
Site Admin
 
Posts: 17951
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: ORA-12514 within a RAC Environment

Postby jnrpeardba » Wed Oct 03, 2012 5:29 pm

Hi Tim,

that's correct as you can see. If I did something wrong just let me know

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

Re: ORA-12514 within a RAC Environment

Postby Tim... » Wed Oct 03, 2012 5:33 pm

That's the point. I can't see anything wrong.

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
Tim...
Site Admin
 
Posts: 17951
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: ORA-12514 within a RAC Environment

Postby jnrpeardba » Wed Oct 03, 2012 7:32 pm

Hi Tim,

I see something strange here

First I cannot connect as sys
Code: Select all
oracle@rvecrcdrprep > sqlplus sys/password@cdrprep1

SQL*Plus: Release 11.2.0.2.0 Production on Wed Oct 3 20:20:58 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


So I look at the listener

Code: Select all
oracle@rvecrcdrprep > lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 03-OCT-2012 20:21:14

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecrcdrprep.hscni.net)(PORT=1521)))
TNS-01189: The listener could not authenticate the user

Then I check the listener services

Code: Select all
oracle@rvecrcdrprep > lsnrctl services

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 03-OCT-2012 20:21:23

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecrcdrprep.hscni.net)(PORT=1521)))
TNS-01189: The listener could not authenticate the user


Hmmmm

so I try again

Code: Select all
oracle@rvecrcdrprep > lsnrctl services

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 03-OCT-2012 20:21:30

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecrcdrprep.hscni.net)(PORT=1521)))
Services Summary...
Service "CDRPREP" has 2 instance(s).
  Instance "CDRPREP1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:144 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.210.1.96)(PORT=1521)))
  Instance "CDRPREP2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:29 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.210.0.96)(PORT=1521)))
Service "CDRPREPXDB" has 2 instance(s).
  Instance "CDRPREP1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: rvecrcdrprep, pid: 8912922>
         (ADDRESS=(PROTOCOL=tcp)(HOST=rvecrcdrprep.hscni.net)(PORT=62992))
  Instance "CDRPREP2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: bvecrcdrprep, pid: 13500582>
         (ADDRESS=(PROTOCOL=tcp)(HOST=bvecrcdrprep.hscni.net)(PORT=48386))
The command completed successfully
oracle@rvecrcdrprep >


And it works - but not with the listener status

Code: Select all
oracle@rvecrcdrprep > lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 03-OCT-2012 20:21:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecrcdrprep.hscni.net)(PORT=1521)))
TNS-01189: The listener could not authenticate the user
oracle@rvecrcdrprep >


Then on the 3rd attempt without changing anything it - listener status works
Code: Select all
oracle@rvecrcdrprep > lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 03-OCT-2012 20:25:29

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecrcdrprep.hscni.net)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
Start Date                27-SEP-2012 14:40:01
Uptime                    6 days 5 hr. 45 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /mware/oracle/11.2/grid/network/admin/listener.ora
Listener Log File         /mware/oracle/11.2/grid/log/diag/tnslsnr/rvecrcdrprep/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.210.2.126)(PORT=1521)))
Services Summary...
Service "CDRPREP" has 2 instance(s).
  Instance "CDRPREP1", status READY, has 1 handler(s) for this service...
  Instance "CDRPREP2", status READY, has 1 handler(s) for this service...
Service "CDRPREPXDB" has 2 instance(s).
  Instance "CDRPREP1", status READY, has 1 handler(s) for this service...
  Instance "CDRPREP2", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@rvecrcdrprep >


The only reason I am trying to connect to the node as the user SYS is for the rman RAC backup and a test is the connectivity - as mentioned below :

Code: Select all
2. Verify connectivity to the target nodes and catalog if used.

a. % setenv TNS_ADMIN $ORACLE_HOME/network/admin
b. % sqlplus /nolog
c. SQL> connect sys/pwd@node1 as sysdba
d. SQL> connect sys/pwd@node2 as sysdba
e. SQL> connect rman/rman@rcat


It appears everytime I attempt to make a connection to the database as SYS, the listener does not display any results. It seems to take a while (15 secs or so) and then will give me the status results from Listener

Does that make things any clearer?


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

Re: ORA-12514 within a RAC Environment

Postby Tim... » Wed Oct 03, 2012 7:42 pm

Hi.

Not really. Like I said, I can't see anything wrong with the setup stuff you've sent to me, so I don't understand why it is not working. Of course, it's possible I've missed something, because I am at OpenWorld at the moment, so my time is limited. :)

Typically, problems with the listener are caused by incorrect network configuration. This seems unlikely here as it does intermittently work. I think it is worth checking the hostname you are using matches between all these points:

- The hostname listed in your listener.ora file.
- The hostname displayed by the "hostname" command on the server.
- The entry in the "/etc/hosts" file.
- The ORACLE_HOSTNAME environment variable.

If all these match, I've not seen any specific problems with the lsnrctl command itself.

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
Tim...
Site Admin
 
Posts: 17951
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle RAC and Clusterware

Who is online

Users browsing this forum: No registered users and 1 guest