I installed Oracle Database 11gR2 as well as Oracle Grid Infrastructure on a Linux box, then I created the orcl
database.
I have always been able to connect to my database using SQL*Plus or OEM. But lately, I'm facing an issue when typing the command lsnrctl status
, and so I'm not able to connect to the database.
My listener.ora
file:
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = pc.company.com)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
My tnsnames.ora file :
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pc.company.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.example.com)
)
)
When typing lsnrctl status
, I get the following :
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 21-JUL-2014 03:35:48
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 21-JUL-2014 03:25:58
Uptime 0 days 0 hr. 9 min. 50 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/pc151/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pc.company.com)(PORT=1521)))
The listener supports no services
The command completed successfully
When typing sqlplus / as sysdba
then startup
, I get this :
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/orcl/spfileorcl.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-29701: unable to connect to Cluster Synchronization Service
And the srvctl start database
command fails to start the database up too.
My oratab
file is the following :
#Backup file is /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/oratab.bak.pc150 line added by Agent
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
+ASM:/u01/app/oracle/product/11.2.0/grid:N
orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N # line added by Agent
Rationale. In most cases, the message indicates that the database instance is closed, no any database services are available. You can just startup your database and make sure the service names are correct. If you're pretty sure that your database instance is up and running, please check the parameter of local listener.
The listener is a separate process that runs on the database server computer. It receives incoming client connection requests and manages the traffic of these requests to the database server.
Start Oracle Network Manager from Windows start menu. Then, click on “Listeners” and the “+” button. Give a name to the listener and click “OK”. Next, select “Listening Locations” and click on “Add Address” to indicate the server remote access details.
The database registers its service name(s) with the listener when it starts up. If it is unable to do so then it tries again periodically - so if the listener starts after the database then there can be a delay before the service is recognised.
If the database isn't running, though, nothing will have registered the service, so you shouldn't expect the listener to know about it - lsnrctl status
or lsnrctl services
won't report a service that isn't registered yet.
You can start the database up without the listener; from the Oracle account and with your ORACLE_HOME
, ORACLE_SID
and PATH
set you can do:
sqlplus /nolog
Then from the SQL*Plus prompt:
connect / as sysdba
startup
Or through the Grid infrastructure, from the grid account, use the srvctl start database
command:
srvctl start database -d db_unique_name [-o start_options] [-n node_name]
You might want to look at whether the database is set to auto-start in your oratab
file, and depending on what you're using whether it should have started automatically. If you're expecting it to be running and it isn't, or you try to start it and it won't come up, then that's a whole different scenario - you'd need to look at the error messages, alert log, possibly trace files etc. to see exactly why it won't start, and if you can't figure it out, maybe ask on Database Adminsitrators rather than on Stack Overflow.
If the database can't see +DATA
then ASM may not be running; you can see how to start that here; or using srvctl start asm
. As the documentation says, make sure you do that from the grid home, not the database home.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With