LISTENER Status:
Below are the typical listener status:
Ready --- When Instance started in open mode with dynamic registration
Unknown --- When Instance started in open mode with static registration
Blocked --- When Instance in in nomount
Restricted --- When Instance is started with restricted session
1. Verify that database and listener are down
[oracle@oraclelab1 ~]$ ps -ef|grep smon
oracle 4009 3533 0 01:17 pts/2 00:00:00 grep --color=auto smon
[oracle@oraclelab1 ~]$ ps -ef|grep tns
root 23 2 0 Sep30 ? 00:00:00 [netns]
oracle 4018 3533 0 01:17 pts/2 00:00:00 grep --color=auto tns
[oracle@oraclelab1 ~]$
2. Start Database in nomount mode then we can see listener will be in blocked state:
[oracle@oraclelab1 ~]$ cat /etc/oratab
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.
# 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 field 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.
#
#
DEVDB:/u01/app/oracle/product/19.0.0.0/dbhome_1:N
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Oct 23 01:18:41 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 8899384 bytes
Variable Size 520093696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
DEVDB STARTED
SQL>
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string DEVDB
SQL>
[oracle@oraclelab1 admin]$ cat listener.ora
LISTENER_DEVDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
)
[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_DEVDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:20:03
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@oraclelab1 admin]$ lsnrctl start LISTENER_DEVDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:20:14
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DEVDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 23-OCT-2021 01:20:14
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@oraclelab1 admin]$
[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_DEVDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:21:09
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DEVDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 23-OCT-2021 01:20:14
Uptime 0 days 0 hr. 0 min. 54 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
Services Summary...
Service "DEVDB" has 1 instance(s).
Instance "DEVDB", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclelab1 admin]$
3. Start Database in mount mode with restricted exclusive mode then we can see listener will be in restricted state:
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
SQL> startup mount exclusive restrict;
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 8899384 bytes
Variable Size 520093696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
DEVDB MOUNTED
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
DEVDB MOUNTED
SQL>
[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_DEVDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:24:35
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DEVDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 23-OCT-2021 01:20:14
Uptime 0 days 0 hr. 4 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
Services Summary...
Service "DEVDB" has 1 instance(s).
Instance "DEVDB", status RESTRICTED, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclelab1 admin]$
4. Start Database in open mode and verify that the listener is running with dynamic registration then we can see listener will be in Ready state:
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup;
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 8899384 bytes
Variable Size 520093696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
DEVDB OPEN
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
DEVDB READ WRITE
SQL>
Dynamic Listener:
=================
LISTENER_DEVDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
)
[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_DEVDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:28:22
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DEVDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 23-OCT-2021 01:20:14
Uptime 0 days 0 hr. 8 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
Services Summary...
Service "DEVDB" has 1 instance(s).
Instance "DEVDB", status READY, has 1 handler(s) for this service...
Service "DEVDBXDB" has 1 instance(s).
Instance "DEVDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclelab1 admin]$
5. Start Database in open mode and verify that the listener is running with static registration then we can see listener will be in Unknown state:
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup;
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 8899384 bytes
Variable Size 520093696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
DEVDB OPEN
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
DEVDB READ WRITE
SQL>
Static Listener:
================
LISTENER_DEVDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
)
SID_LIST_LISTENER_DEVDB=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=DEVDB)
(SID_NAME=DEVDB)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1)
)
)
[oracle@oraclelab1 admin]$ cat listener.ora
LISTENER_DEVDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
)
SID_LIST_LISTENER_DEVDB=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=DEVDB)
(SID_NAME=DEVDB)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1)
)
)
[oracle@oraclelab1 admin]$ lsnrctl start LISTENER_DEVDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:30:30
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DEVDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 23-OCT-2021 01:30:30
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
Services Summary...
Service "DEVDB" has 1 instance(s).
Instance "DEVDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_DEVDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:31:21
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DEVDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 23-OCT-2021 01:30:30
Uptime 0 days 0 hr. 0 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
Services Summary...
Service "DEVDB" has 2 instance(s).
Instance "DEVDB", status UNKNOWN, has 1 handler(s) for this service...
Instance "DEVDB", status READY, has 1 handler(s) for this service...
Service "DEVDBXDB" has 1 instance(s).
Instance "DEVDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclelab1 admin]$
6. tnsnames entries in the tnsnames.ora files will be used for application or user connection via remotely
tnsnames:
=========
DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DEVDB)
)
)
[oracle@oraclelab1 admin]$ pwd
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin
[oracle@oraclelab1 admin]$ vi tnsnames.ora
[oracle@oraclelab1 admin]$ cat tnsnames.ora
DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DEVDB)
)
)
[oracle@oraclelab1 admin]$ tnsping DEVDB
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:33:05
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DEVDB)))
OK (0 msec)
[oracle@oraclelab1 admin]$
Note:
If DB is down and if we start the listener then then listener does not have any status and it will not registered with any service or Instance.
Its just black listener starts:
[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_DEVDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-OCT-2021 01:27:06
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DEVDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 23-OCT-2021 01:20:14
Uptime 0 days 0 hr. 6 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@oraclelab1 admin]$
Regards,
Mallik
No comments:
Post a Comment