In order to connect remotely to an instance that is not started and not registered against the listener, it is necessary to configure a static entry in the listener.ora file for the service.
Note the 2nd (SID_DESC= below where SID is MYDB and GLOBAL_DBNAME is MYDB.ORACLE.COM.
LISTENER.ORA Example:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = C:oracle) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = MYDB) (ORACLE_HOME = C:oracle) (GLOBAL_DBNAME=MYDB.ORACLE.COM) ) )
Once this change has taken place, it is necessary to restart or reload the listener:
LSNRCTL>reload <listener_name>
TNSNAMES.ORA remains the same using (SERVICE_NAME=MYDB.ORACLE.COM)
ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTO = TCP)(HOST = myhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = MYDB.ORACLE.COM) ) )
–> Please note that SID is case-sensitive on Unix systems.
Once the listener is reloaded, check the lsnrctl status output to be sure that the static SERVICE for MYDB.ORACLE.COM is among the services in the summary. You’ll notice that this handler is “unknown” versus “Ready”. That’s because PMON did not register it dynamically.
C:oracleBIN>lsnrctl status
LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 – Production on 29-MAY-2009 17:34:32
Copyright (c) 1991, 2006, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1521))) STATUS of the LISTENER ———————— Alias LISTENER Version TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 – Production Start Date 29-MAY-2009 17:34:18 Uptime 0 days 0 hr. 0 min. 13 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File C:oraclenetworkadminlistener.ora Listener Log File C:oraclenetworkloglistener.log Listening Endpoints Summary… (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)(PORT=1521))) Services Summary…
Service “PLSExtproc” has 1 instance(s). Instance “PLSExtproc”, status UNKNOWN, has 1 handler(s) for this service…
Service “MYDB.ORACLE.COM” has 1 instance(s). Instance “MYDB”, status UNKNOWN, has 1 handler(s) for this service… The command completed successfully
NOTE: Please make sure that you connect with a user that has the appropriate privileges to start the database (e.g. SYSDBA). Also ensure that you have created the password file.
Comments