Provide Sudo access to user to check Listener status and start/stop Listener

Case Study:

Scorfield working in support team and he need to check Listeners status when there is any disturbance and start/stop the listener at night when DBA’s are not available.

Solution:

Provide sudo access to user scorfield:

[root@stagedb3 ~]# vi /etc/sudoers

scorfield ALL=(oracle)    NOPASSWD: /opt/oracle/product/11.2.0.1/bin/lsnrctl

Check Listener Status:

[scorfield@stagedb3 ~]$ sudo -i -u oracle /opt/oracle/product/11.2.0.1/bin/lsnrctl status

[sudo] password for scorfield:

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-AUG-2014 15:45:34

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.3.0 - Production

Start Date                08-AUG-2014 11:27:13

Uptime                    0 days 4 hr. 18 min. 20 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/oracle/product/11.2.0.3/network/admin/listener.ora

Listener Log File         /opt/oracle/diag/tnslsnr/stagedb3/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stagedb3.ipx.com)(PORT=1521)))

Services Summary...

Service "ipxtest.ipx.com" has 1 instance(s).

  Instance "ipxtest", status READY, has 1 handler(s) for this service...

The command completed successfully

 

Stop Listener:

[scorfield@stagedb3 ~]$ sudo -i -u oracle /opt/oracle/product/11.2.0.1/bin/lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-AUG-2014 15:45:51

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

The command completed successfully

 

[scorfield@stagedb3 ~]$ sudo -i -u oracle /opt/oracle/product/11.2.0.1/bin/lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-AUG-2014 15:46:05

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

TNS-12541: TNS:no listener

 TNS-12560: TNS:protocol adapter error

  TNS-00511: No listener

   Linux Error: 111: Connection refused

 

Start Listener:

[scorfield@stagedb3 ~]$ sudo -i -u oracle /opt/oracle/product/11.2.0.1/bin/lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-AUG-2014 15:46:21

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /opt/oracle/product/11.2.0.3/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production

System parameter file is /opt/oracle/product/11.2.0.3/network/admin/listener.ora

Log messages written to /opt/oracle/diag/tnslsnr/stagedb3/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stagedb3.ipx.com)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date                08-AUG-2014 15:46:21

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/oracle/product/11.2.0.3/network/admin/listener.ora

Listener Log File         /opt/oracle/diag/tnslsnr/stagedb3/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stagedb3.ipx.com)(PORT=1521)))

The listener supports no services

The command completed successfully

[scorfield@stagedb3 ~]$ sudo -i -u oracle /opt/oracle/product/11.2.0.1/bin/lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-AUG-2014 15:47:06

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.3.0 - Production

Start Date                08-AUG-2014 15:46:21

Uptime                    0 days 0 hr. 0 min. 44 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/oracle/product/11.2.0.3/network/admin/listener.ora

Listener Log File         /opt/oracle/diag/tnslsnr/stagedb3/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stagedb3.ipx.com)(PORT=1521)))

Services Summary...

Service "ipxtest.ipx.com" has 1 instance(s).

  Instance "ipxtest", status READY, has 1 handler(s) for this service...

The command completed successfully

Comments