Oracle static listener registration details
There are a lot of articles on Oracle listening for static registration on the Internet, but most of them are just a simple introduction. There are no detailed examples. Here, we will combine Oracle 10gR2 under Linux as4. 0.1 here is a specific example.
1. Add a static registered node to the $ ORACLE_HOME/network/admin/listener. ora file.
[Oracle @ prudent oracle] $ cd $ ORACLE_HOME/network/admin
[Oracle @ prudent admin] $ vi listener. ora
# Listener. ora Network Configuration File:/mydatafile2/app/oracle/product/11.2.0/db_1/network/admin/listener. ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/mydatafile2/app/oracle/product/11.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME =/mydatafile2/app/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME = WOO. COM)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = prudent) (PORT = 1521 ))
)
)
Note that GLOBAL_DBNAME = WOO. COM
SID_NAME = ORCL
This SID_NAME should be consistent with the $ ORACLE_SID that you provide external services.
[Oracle @ prudent admin] $ echo $ ORACLE_SID
ORCL
2. Configure the nodes in tnsnames. ora.
[Oracle @ prudent admin] $ vi tnsnames. ora
# Tnsnames. ora Network Configuration File:/mydatafile2/app/oracle/product/11.2.0/db_1/network/admin/tnsnames. ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = prudent) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
WOOORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = prudent) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = WOO. COM)
)
)
SERVICE_NAME = WOO. COM in tnsname WOOORCL
The service name here is WOO. COM instead of the general ORCL, because in listener. WOO has been registered in ora. COM, lsnrctl will listen to WOO. and corresponding to SID_NAME = ORCL.
3. Start listeners and services
[Oracle @ prudent oracle] $ cat dbstart
Lsnrctl start
Sqlplus/nolog <EOF
Connect/as sysdba
Startup
EOF
[Oracle @ prudent oracle] $./dbstart
LSNRCTL for Linux: Version 11.2.0.1.0-Production on 13-FEB-2011 20:11:15
Copyright (c) 1991,200 5, Oracle. All rights reserved.
Starting/mydatafile2/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0-Production
System parameter file is/mydatafile2/app/oracle/product/11.2.0/db_1/network/admin/listener. ora
Log messages written to/mydatafile2/app/oracle/product/11.2.0/db_1/network/log/listener. log
Listening on: (DESCRIPTION = (ADDRESS = (PROTOCOL = ipc) (KEY = EXTPROC1 )))
Listening on: (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = prudent) (PORT = 1521 )))
Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1 )))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0-Production
Start Date 13-FEB-2011 20:11:15
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File/mydatafile2/app/oracle/product/11.2.0/db_1/network/admin/listener. ora
Listener Log File/mydatafile2/app/oracle/product/11.2.0/db_1/network/log/listener. log
Listening Endpoints Summary...
(DESCRIPTION = (ADDRESS = (PROTOCOL = ipc) (KEY = EXTPROC1 )))
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = prudent) (PORT = 1521 )))
Services Summary...
Service "WOO. COM" has 1 instance (s ).
Instance "ORCL", status UNKNOWN, has 1 handler (s) for this service...
Service "ORCL" has 1 instance (s ).
Instance "ORCL", status UNKNOWN, has 1 handler (s) for this service...
Service "PLSExtProc" has 1 instance (s ).
Instance "PLSExtProc", status UNKNOWN, has 1 handler (s) for this service...
The command completed successfully
SQL * Plus: Release 11.2.0.1.0-Production on Sun Feb 13 20:11:16 2011
Copyright (c) 1982,200 5, Oracle. All rights reserved.
SQL> Connected to an idle instance.
SQL> ORA-32004: obsolete and/or deprecated parameter (s) specified
ORACLE instance started.
Total System Global Area 461373440 bytes
Fixed Size 1220000 bytes
Variable Size 75498080 bytes
Database Buffers 381681664 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
We can see that
Service "WOO. COM" has 1 instance (s ).
Instance "ORCL", status UNKNOWN, has 1 handler (s) for this service...
Being monitored.
4. Verify that the service can arrive
[Oracle @ prudent oracle] $ tnsping WOOORCL
TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 13-FEB-2011 20:14:59
Copyright (c) 1997,200 5, Oracle. All rights reserved.
Used parameter files:
/Mydatafile2/app/oracle/product/11.2.0/db_1/network/admin/sqlnet. ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = prudent) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = WOO. COM )))
OK (10 msec)
5. Use the static registration service to log on to oracle
[Oracle @ prudent oracle] $ sqlplus system/oracle @ WOOORCL
SQL * Plus: Release 11.2.0.1.0-Production on Sun Feb 13 20:17:27 2011
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
SQL> select count (*) from date_log;
COUNT (*)
----------
SQL>
So far: the static registration has been verified to be successfully resolved, listened, and connected.
Oracle Database listening is very slow, and hang basically handles faults
Dynamic and Static monitoring features of Oracle listeners
Non-default listeners and port configurations for placing an instance in the Oracle 11g RAC Environment
Configure and manage Oracle listener logs
Oracle error-ORA-12514: TNS: No listener
ORA-12514 listening error solved
Six connection problems and solutions for Oracle listeners
Oracle LISTENER does not listen to Oracle instances. Problem solved
Set the Oracle LISTENER password (LISTENER)