I had one environment installed Oracle 11g R2 for few months, it was working well for past few months, but this week, I got error when I connect.
My environment: Windows 7 64 bit with SP1
So I trying to investigate:
Here is my listener.ora
# listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
(GLOBAL_DBNAME=orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.100.134)(PORT = 1521))
)
)
)
ADR_BASE_LISTENER = D:\app\Administrator\product\11.2.0\dbhome_1\log
and my tnsnames.ora
# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.100.134)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
(GLOBAL_NAME = orcl)
)
)
Here is my investigate process:
- using LSNRCTL to check listener status(sorry I translate from Chinese to English)
LSNRCTL> status
Connect to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Listener status
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 24-12-2014 11:13:04
Normal operation Time 0 day 0 hour 21 minute 32 second
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener config file D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener log file d:\app\administrator\product\11.2.0\dbhome_1\log\diag\tnslsnr\1300313-PC\listener\alert\log.xml
Listener summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.100.134)(PORT=1521)))
Service Summary...
service "PLSExtProc" has 1 execute process.
execute process "PLSExtProc", status UNKNOWN, has 1 handler for this service...
service "orcl" has 1 execute process.
execute process "orcl", status UNKNOWN, has 1 handler for this service...
command complete
- Using TNSPING
D:\app\Administrator\product\11.2.0\dbhome_1\BIN>tnsping orcl
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 24-12-2014 11:39:23
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Config file:
D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
Using TNSNAMES adaptor to parse alias
try connecting (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.100.134)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl) (GLOBAL_NAME = orcl)))
OK (40 msec)
- Using SQLPLUS
D:\app\Administrator\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wednesday 12.24 11:43:17 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connect to idle execution process.
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_ORCL'
SQL> exit
disconnected
D:\app\Administrator\product\11.2.0\dbhome_1\BIN>sqlplus scott/tiger@orcl
SQL*Plus: Release 11.2.0.1.0 Production on Wednesday 12.24 11:44:01 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Sequence: 0
my environment variable as
D:\app\Administrator\product\11.2.0\dbhome_1\BIN>echo %ORACLE_HOME%
D:\app\Administrator\product\11.2.0\dbhome_1
D:\app\Administrator\product\11.2.0\dbhome_1\BIN>echo %ORACLE_SID%
orcl
Appreciated if any idea.
