Showing posts with label Listener. Show all posts
Showing posts with label Listener. Show all posts

Saturday, March 19, 2016

ORA-12505 TNS:listener does not currently know of SID after 12c DBCA

[The problem]

Created a portable database RCATPDB1 and a contained database RCATT using 12.1.0.2 dbca, but not able to connect to it through LISTENER, however tnsping returns okay.

$ tnsping RCATT

TNS Ping Utility for Solaris: Version 12.1.0.2.0 - Production on 11-MAR-2016 14:11:07

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

Used parameter files:
/software/orarman/product/12.1.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = testdbdev)(PORT = 1555)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RCATT)))
OK (10 msec)


$ sqlplus system/dsafdsa@RCATPDB1

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 11 16:35:34 2016

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

ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect
descriptor

[Troubleshooting]
Googled and find this is the exact same problem as mine. 

[Solution]

Then the solution is define local_listener in pfile for dynamic registration.

Define the listener in tnsnames.ora

LISTENER_RCATT =
  (ADDRESS = (PROTOCOL = TCP)(HOST = testdbdev)(PORT = 1555))

RCATT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testdbdev)(PORT = 1555))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RCATT)
    )
  )

RCATPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testdbdev)(PORT = 1555))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RCATPDB1)
    )


SQL> show parameter local_listen

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string



SQL> alter system set local_listener='RCATT' scope=both;

System altered.


SQL> alter system register;

System altered.


$ lsnrctl service LISTENER_RCATT

LSNRCTL for Solaris: Version 12.1.0.2.0 - Production on 11-MAR-2016 16:44:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testdbdev)(PORT=1555)))
Services Summary...
Service "RCATT" has 1 instance(s).
  Instance "RCATT", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "RCATTXDB" has 1 instance(s).
  Instance "RCATT", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=testdbdev)(PORT=41100))
Service "rcatpdb1" has 1 instance(s).
  Instance "RCATT", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully



$ sqlplus  system@RCATT

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 11 16:46:51 2016

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

Last Successful login time: Wed Mar 09 2016 12:01:10 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
$ sqlplus  system@RCATPDB1

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 11 16:46:57 2016

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

Last Successful login time: Fri Mar 11 2016 16:46:51 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
$ tnsping RCATT

TNS Ping Utility for Solaris: Version 12.1.0.2.0 - Production on 11-MAR-2016 16:47:04

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

Used parameter files:
/software/orarman/product/12.1.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = testdbdev)(PORT = 1555)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RCATT)))
OK (10 msec)
$ tnsping RCATPDB1

TNS Ping Utility for Solaris: Version 12.1.0.2.0 - Production on 11-MAR-2016 16:47:12

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

Used parameter files:
/software/orarman/product/12.1.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = testdbdev)(PORT = 1555)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RCATPDB1)))
OK (10 msec)
Posted by at
Labels: ,

Friday, November 20, 2015

when oracle listener listens on all netowrk interfaces?

Learned below from https://community.oracle.com/thread/2126662?start=0&tstart=0

When you use HOSTNAME in listener definition and the HOSTNAME matches the result of `hostname` command, the listener will listen on all interfaces.

If you use an IP in listener definition, the listener will listen on THAT interface only.


So here is my test  to confirm this is correct.


--the test server info


orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> hostname
orarac2poc
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> ping orarac2poc
PING orarac2poc (10.139.90.42) 56(84) bytes of data.
64 bytes from orarac2poc (10.139.90.42): icmp_seq=1 ttl=64 time=0.021 ms
64 bytes from orarac2poc (10.139.90.42): icmp_seq=2 ttl=64 time=0.027 ms

--- orarac2poc ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1000ms
rtt min/avg/max/mdev = 0.021/0.024/0.027/0.003 ms



--ifconfig output


[root@orarac2poc ~]# ifconfig
eth0      Link encap:Ethernet  HWaddr 00:50:56:9C:3D:C2
          inet addr:10.139.90.42  Bcast:10.139.255.255  Mask:255.255.0.0
          inet6 addr: fe80::250:56ff:fe9c:3dc2/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:29146147 errors:0 dropped:0 overruns:0 frame:0
          TX packets:20491711 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:33283609829 (30.9 GiB)  TX bytes:2225739822 (2.0 GiB)

eth0:1    Link encap:Ethernet  HWaddr 00:50:56:9C:3D:C2
          inet addr:10.139.92.42  Bcast:10.139.255.255  Mask:255.255.0.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

eth0:2    Link encap:Ethernet  HWaddr 00:50:56:9C:3D:C2
          inet addr:10.139.90.43  Bcast:10.139.255.255  Mask:255.255.0.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

eth1      Link encap:Ethernet  HWaddr 00:50:56:9C:3D:C1
          inet addr:10.139.91.42  Bcast:10.139.255.255  Mask:255.255.0.0
          inet6 addr: fe80::250:56ff:fe9c:3dc1/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:4225633 errors:0 dropped:0 overruns:0 frame:0
          TX packets:3336 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:477256264 (455.1 MiB)  TX bytes:761551 (743.7 KiB)

eth2      Link encap:Ethernet  HWaddr 00:50:56:9C:1D:68
          inet addr:10.149.0.12  Bcast:10.149.255.255  Mask:255.255.0.0
          inet6 addr: fe80::250:56ff:fe9c:1d68/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:203522763 errors:0 dropped:0 overruns:0 frame:0
          TX packets:192410252 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:86546647557 (80.6 GiB)  TX bytes:143237765923 (133.4 GiB)

eth2:1    Link encap:Ethernet  HWaddr 00:50:56:9C:1D:68
          inet addr:169.254.231.131  Bcast:169.254.255.255  Mask:255.255.0.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:459889795 errors:0 dropped:0 overruns:0 frame:0
          TX packets:459889795 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:145470946165 (135.4 GiB)  TX bytes:145470946165 (135.4 GiB)


--use the IP of `hostname`


-- use physical IP

orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl start LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 15:34:41

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

Starting /u01/app/oracle/product/11.2.0.4/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orarac2poc)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orarac2poc)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                20-NOV-2015 15:34:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orarac2poc)(PORT=1522)))
Services Summary...
Service "DGS_DGMGRL.POC" has 1 instance(s).
  Instance "DGS", status UNKNOWN, has 1 handler(s) for this service...
Service "RCAT" has 1 instance(s).
  Instance "RCAT", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> netstat -an |grep 1522
tcp        0      0 :::1522                     :::*                        LISTEN
tcp        0      0 ::ffff:10.139.90.42:1522    ::ffff:10.139.90.42:48332   TIME_WAIT
udp        0      0 127.0.0.1:51522             0.0.0.0:*
udp        0      0 169.254.231.131:51522       0.0.0.0:*
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl stop LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 15:35:42

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orarac2poc)(PORT=1522)))
The command completed successfully



We can see the LISTENER1 listeners on all IPs'  1522 port.

-- now use other ip


--Use virtual IP

orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl start LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 15:36:39

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

Starting /u01/app/oracle/product/11.2.0.4/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.139.92.42)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.139.92.42)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                20-NOV-2015 15:36:39
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.139.92.42)(PORT=1522)))
Services Summary...
Service "DGS_DGMGRL.POC" has 1 instance(s).
  Instance "DGS", status UNKNOWN, has 1 handler(s) for this service...
Service "RCAT" has 1 instance(s).
  Instance "RCAT", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> netstat -an |grep 1522
tcp        0      0 10.139.92.42:1522           0.0.0.0:*                   LISTEN
tcp        0      0 10.139.92.42:31765          10.139.92.42:1522           TIME_WAIT
udp        0      0 127.0.0.1:51522             0.0.0.0:*
udp        0      0 169.254.231.131:51522       0.0.0.0:*
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/adm


orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl stop LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 15:37:44

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.139.92.42)(PORT=1522)))
The command completed successfully


We can see the LISTENER1 only listens the exact IP we specified. 



--more testing

orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl start LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 15:38:40

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

Starting /u01/app/oracle/product/11.2.0.4/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.139.91.42)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.139.91.42)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                20-NOV-2015 15:38:40
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.139.91.42)(PORT=1522)))
Services Summary...
Service "DGS_DGMGRL.POC" has 1 instance(s).
  Instance "DGS", status UNKNOWN, has 1 handler(s) for this service...
Service "RCAT" has 1 instance(s).
  Instance "RCAT", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> netstat -an |grep 1522
tcp        0      0 10.139.91.42:1522           0.0.0.0:*                   LISTEN
tcp        0      0 10.139.91.42:51369          10.139.91.42:1522           TIME_WAIT
udp        0      0 127.0.0.1:51522             0.0.0.0:*
udp        0      0 169.254.231.131:51522       0.0.0.0:*
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl stop LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 15:39:50

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.139.91.42)(PORT=1522)))
The command completed successfully
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> vi listener.ora
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl start LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 15:40:23

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

Starting /u01/app/oracle/product/11.2.0.4/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.149.0.12)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.149.0.12)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                20-NOV-2015 15:40:23
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orarac2poc/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.149.0.12)(PORT=1522)))
Services Summary...
Service "DGS_DGMGRL.POC" has 1 instance(s).
  Instance "DGS", status UNKNOWN, has 1 handler(s) for this service...
Service "RCAT" has 1 instance(s).
  Instance "RCAT", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> netstat -an |grep 1522
tcp        0      0 10.149.0.12:1522            0.0.0.0:*                   LISTEN
tcp        0      0 10.139.91.42:58897          10.139.91.42:1522           TIME_WAIT
tcp        0      0 10.149.0.12:36482           10.149.0.12:1522            TIME_WAIT
udp        0      0 127.0.0.1:51522             0.0.0.0:*
udp        0      0 169.254.231.131:51522       0.0.0.0:*
orarac2poc:ORCL2:/u01/app/oracle/product/11.2.0.4/network/admin> lsnrctl stop LISTENER1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-NOV-2015 16:47:15

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.149.0.12)(PORT=1522)))
The command completed successfully




Conclusion:

The finding is correct. Thanks.



Posted by at
Labels: ,

Friday, June 22, 2007

WARNING: Subscription for node down event still pending

After I add "
SUBSCRIBE_FOR_NODE_DOWN_EVENT_=OFF
" to listener.ora, notice that below message is found in listener.log


20-JUN-2007 10:50:57 * ping * 0
WARNING: Subscription for node down event still pending
According to Net Services Administrator's Guide,

16 Troubleshooting Oracle Net Services

I think it should be safe to ignore, as I don't have ONS.


Listener Subscription for ONS Node Down Event Information

Listener will subscribe to the Oracle Notification Service (ONS) node down event on startup if ONS configuration file is available. This subscription enables the listener to remove the affected service when it receives node down event notification from ONS. The listener uses asynchronous subscription for the event notification. The following warning message will be recorded to listener log file on each STATUS command if the subscription has not completed; for example if the ONS daemon is not running on the host.

WARNING: Subscription for node down event still pending

Listener will not be able to receive the ONS event while subscription is pending. Other than that, no other listener functionality is affected.

Posted by at
Labels:
Subscribe to: Posts (Atom)