How to use alter system register in LISTENER to solve the slow Registration problem of dynamic Monitoring 01/15 Update SLTechnology News&Howtos

How to use alter system register in LISTENER to solve the slow Registration problem of dynamic Monitoring

2025-01-15 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >

Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about how to use alter system register to solve the slow registration problem of dynamic monitoring in LISTENER. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Feel the dynamic snooping registration details of the Oracle database instance. There is such a rule as follows, which is summarized here:

① if the monitoring is started first, and then the database instance is started, the dynamic monitoring will automatically identify the database instance started.

If ② restarts listening when the database instance is running normally, the database instance will wait a long time before it can be successfully registered in dynamic monitoring, which takes about 1 minute.

If ③ starts the database instance first, and then starts listening, the effect is the same as ②.

If ④ does not want to wait a long time for the dynamic listening registration process, it can use the "alter system register;" command to speed up.

Really experience the process. After personal experience, these conclusions will seem so natural and innocent.

1. Simulation of the ① scenario

1) start the listener when the database instance is not started

Ora11g@secdb / home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 18 20:47:22 2011

Connected to an idle instance.

NotConnected@ >! lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 18-JAN-2011 20:48:58

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1521)

STATUS of the LISTENER

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 29-DEC-2010 02:03:55

Uptime 20 days 18 hr. 45 min. 3 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File / oracle/ora11gR2/diag/tnslsnr/secdb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC1521)

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=secdb) (PORT=1521))

The listener supports no services

The command completed successfully

Because the database instance is not started, the monitor is, of course, in a state where no instance is detected.

2) start the database instance

NotConnected@ > startup

ORACLE instance started.

Total System Global Area 535662592 bytes

Fixed Size 1337720 bytes

Variable Size 411043464 bytes

Database Buffers 117440512 bytes

Redo Buffers 5840896 bytes

Database mounted.

Database opened.

3) check the monitoring status immediately

Sys@ora11g >! lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 18-JAN-2011 20:49:15

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1521)

STATUS of the LISTENER

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 29-DEC-2010 02:03:55

Uptime 20 days 18 hr. 45 min. 20 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File / oracle/ora11gR2/diag/tnslsnr/secdb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC1521)

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=secdb) (PORT=1521))

Services Summary...

Service "ora11g" has 1 instance (s).

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

The command completed successfully

The conclusion of the ① scenario is confirmed:

① if the monitoring is started first, and then the database instance is started, the dynamic monitoring will automatically identify the database instance started.

two。 Simulation of the ② scenario

1) manually stop the listener

Sys@ora11g >! lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 18-JAN-2011 20:52:55

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1521)

The command completed successfully

Sys@ora11g >! lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 18-JAN-2011 20:53:03

Starting / oracle/ora11gR2/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0-Production

System parameter file is / oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora

Log messages written to / oracle/ora11gR2/diag/tnslsnr/secdb/listener/alert/log.xml

Listening on: (DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC1521)

Listening on: (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=secdb) (PORT=1521)

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1521)

STATUS of the LISTENER

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 18-JAN-2011 20:53:03

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File / oracle/ora11gR2/diag/tnslsnr/secdb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC1521)

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=secdb) (PORT=1521))

The listener supports no services

The command completed successfully

2) check the monitoring status every other second

Sys@ora11g >! lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 18-JAN-2011 20:53:11

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1521)

STATUS of the LISTENER

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 18-JAN-2011 20:53:03

Uptime 0 days 0 hr. 0 min. 7 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File / oracle/ora11gR2/diag/tnslsnr/secdb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC1521)

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=secdb) (PORT=1521))

The listener supports no services

The command completed successfully

…… Omit part of the status check information.

Sys@ora11g >! lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 18-JAN-2011 20:53:14

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1521)

STATUS of the LISTENER

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 18-JAN-2011 20:53:03

Uptime 0 days 0 hr. 0 min. 11 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File / oracle/ora11gR2/diag/tnslsnr/secdb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC1521)

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=secdb) (PORT=1521))

The listener supports no services

The command completed successfully

…… Omit part of the status check information.

Sys@ora11g >! lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 18-JAN-2011 20:53:22

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1521)

STATUS of the LISTENER

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 18-JAN-2011 20:53:03

Uptime 0 days 0 hr. 0 min. 18 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File / oracle/ora11gR2/diag/tnslsnr/secdb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC1521)

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=secdb) (PORT=1521))

The listener supports no services

The command completed successfully

…… Omit part of the status check information.

Sys@ora11g >! lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 18-JAN-2011 20:53:58

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1521)

STATUS of the LISTENER

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 18-JAN-2011 20:53:03

Uptime 0 days 0 hr. 0 min. 55 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File / oracle/ora11gR2/diag/tnslsnr/secdb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC1521)

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=secdb) (PORT=1521))

The listener supports no services

The command completed successfully

It can be seen that the database instance is still not registered with the monitoring until 20:53:58, which is 55 seconds since 20:53:03 when the monitoring was started.

Finally, the registration was successfully completed at 20:53:59.

Sys@ora11g >! lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 18-JAN-2011 20:53:59

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1521)

STATUS of the LISTENER

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 18-JAN-2011 20:53:03

Uptime 0 days 0 hr. 0 min. 56 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File / oracle/ora11gR2/diag/tnslsnr/secdb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC1521)

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=secdb) (PORT=1521))

Services Summary...

Service "ora11g" has 1 instance (s).

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

The command completed successfully

It took 56 seconds to start the listening time, and it took about 1 minute to complete the dynamic registration of the database instance to the monitoring.

The conclusion of the ② scenario is confirmed:

② if you start the database instance first and then start listening, the database instance will wait a long time before it can be successfully registered in dynamic monitoring, which takes about 1 minute

3. The style of ③ scenario and ② scenario will not be discussed in detail.

4. Simulation of the ④ scenario

Simulating this scenario is relatively simple, and you only need to execute the following commands in succession.

! lsnrctl stop

! lsnrctl start

! lsnrctl status

Alter system register

! lsnrctl status

The following is the result of continuous execution.

1) stop the listener

Sys@ora11g >! lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 18-JAN-2011 21:30:40

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1521)

The command completed successfully

2) start the listener

Sys@ora11g >! lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 18-JAN-2011 21:30:44

Starting / oracle/ora11gR2/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0-Production

System parameter file is / oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora

Log messages written to / oracle/ora11gR2/diag/tnslsnr/secdb/listener/alert/log.xml

Listening on: (DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC1521)

Listening on: (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=secdb) (PORT=1521)

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1521)

STATUS of the LISTENER

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 18-JAN-2011 21:30:44

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File / oracle/ora11gR2/diag/tnslsnr/secdb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC1521)

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=secdb) (PORT=1521))

The listener supports no services

The command completed successfully

3) View the monitoring status

Sys@ora11g >! lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 18-JAN-2011 21:30:44

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1521)

STATUS of the LISTENER

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 18-JAN-2011 21:30:44

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File / oracle/ora11gR2/diag/tnslsnr/secdb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC1521)

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=secdb) (PORT=1521))

The listener supports no services

The command completed successfully

At this point, the monitor did not detect the database instance information.

4) manually force the database instance to register with the monitor

Sys@ora11g > alter system register

System altered.

5) finally confirm the monitoring status

Sys@ora11g >! lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 18-JAN-2011 21:30:44

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1521)

STATUS of the LISTENER

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0-Production

Start Date 18-JAN-2011 21:30:44

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File / oracle/ora11gR2/diag/tnslsnr/secdb/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC1521)

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=secdb) (PORT=1521))

Services Summary...

Service "ora11g" has 1 instance (s).

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

The command completed successfully

Through the method of manually registering the database instance, you can see the shadow immediately, and the instance is immediately registered to the listener.

The conclusion of the ④ scenario is confirmed:

If ④ does not want to wait a long time for the dynamic listening registration process, it can use the "alter system register;" command to speed up.

5. Summary

If you can "hands-on" this process, you may have this wonderful feeling: everything is so self-contained and harmonious.

Once again, the conclusions about dynamic monitoring are attached here:

① if the monitoring is started first, and then the database instance is started, the dynamic monitoring will automatically identify the database instance started.

If ② restarts listening when the database instance is running normally, the instance will wait a long time before it can be successfully registered in dynamic monitoring, which takes about 1 minute.

If ③ starts the database instance first, and then starts listening, the effect is the same as ②.

If ④ does not want to wait a long time for the dynamic listening registration process, it can use the "alter system register;" command to speed up.

This is how to use alter system register to solve the slow registration problem of dynamic monitoring in LISTENER shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.