When i am trying to connect newly created database I am getting below error
ERROR
ORA-12514: TNS: Listener does not currently know of service requested in connect descriptor
When I checked my listener status it shown below
$ /mnt/ora01/app/oracle/product/11.2.0/db_1/bin/lsnrctl status LISTENER_QA
SQL> shut immediate
Total System Global Area 4.1823E+10 bytes
NAME TYPE VALUE
ERROR
ORA-12514: TNS: Listener does not currently know of service requested in connect descriptor
When I checked my listener status it shown below
$ /mnt/ora01/app/oracle/product/11.2.0/db_1/bin/lsnrctl status LISTENER_QA
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on
13-JAN-2016 13:47:33
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.240.230)(PORT=1530)))
STATUS of the LISTENER
------------------------
Alias
LISTENER_QA
Version
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date
13-JAN-2016 13:44:43
Uptime
0 days 0 hr. 2 min. 50 sec
Trace Level
off
Security
ON: Local OS Authentication
SNMP
OFF
Listener Parameter File
/mnt/ora01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File
/mnt/ora01/app/oracle/diag/tnslsnr//listener_qa/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))
The listener supports no services
The command completed successfully
I tried multiple option but no luck.
Reason behind the issue:
================
- Listener is a process that runs on database server and is responsible for receiving client connection and passing connection to database.
- If you are using default port (1521) for listener, then database will automatically register the instance with listener.
- But what if you are not using default listener port (1521). Instead, you are using some other port – Say 1530. Oracle doesnt do dyamic registration on non-default listener port automatically.
- If you are using non default port (other than 1521). In local_listener parameter we can specify non-default ports on which listener is running and Oracle will register services on those ports.
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))' sid='QA' scope=spfile;
- As soon as I set the value of local_listener, PMON will know the port number on which listener is running and it will dynamically register the instance with listener.
As sys/system user
Reason behind the issue:
================
- Listener is a process that runs on database server and is responsible for receiving client connection and passing connection to database.
- If you are using default port (1521) for listener, then database will automatically register the instance with listener.
- But what if you are not using default listener port (1521). Instead, you are using some other port – Say 1530. Oracle doesnt do dyamic registration on non-default listener port automatically.
- If you are using non default port (other than 1521). In local_listener parameter we can specify non-default ports on which listener is running and Oracle will register services on those ports.
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))' sid='QA' scope=spfile;
- As soon as I set the value of local_listener, PMON will know the port number on which listener is running and it will dynamically register the instance with listener.
As sys/system user
SQL> alter system set
local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))'
sid='QA' scope=spfile;
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Fixed Size
2237488 bytes
Variable Size
4429188048 bytes
Database Buffers
3.7313E+10 bytes
Redo Buffers
78639104 bytes
Database mounted.
sDatabase opened.
SQL>show parameter local
------------------------------------ -----------
------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=172.16.240.230)(PO
RT=1530)))
log_archive_local_first boolean TRUE
parallel_force_local boolean FALSE
SQL> alter system register;
System altered.
Above steps fixed my issue.
Check the listener status now
$/mnt/ora01/app/oracle/product/11.2.0/db_1/bin/lsnrctl status
LISTENER_QA
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on
13-JAN-2016 14:25:41
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.240.230)(PORT=1530)))
STATUS of the LISTENER
------------------------
Alias
LISTENER_QA
Version
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date
13-JAN-2016 14:22:39
Uptime
0 days 0 hr. 3 min. 2 sec
Trace Level
off
Security
ON: Local OS Authentication
SNMP
OFF
Listener Parameter File
/mnt/ora01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File
/mnt/ora01/app/oracle/diag/tnslsnr//listener_qa/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))
Services Summary...
Service "QA.tiermeddc.inc" has 1 instance(s).
Instance
"QA", status READY, has 1 handler(s) for this service...
Service "QAXDB.tiermeddc.inc" has 1
instance(s).
Instance
"QA", status READY, has 1 handler(s) for this service...
The command completed successfully
My TNSNAMES.ora as below
QA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.240.230)(PORT = 1530))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = QA.domain.inc)
)
)
Also add host name to /etc/hosts
1 comment:
worked
Post a Comment