Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to monitor Oracle, Xiaobian thinks it is very practical, so share it with you as a reference, I hope you can gain something after reading this article.

Oracle snooping

One: There is no listener.ora listening file

The file listener.ora is missing;

Without a listener.ora file, the listener is run by default, where it listens on any address that resolves to the host name of the computer (port 1521);

It is recommended that you always configure the listener.ora file so that the Oracle Net environment can record itself;

[oracle@chen admin]$ pwd

/u01/app/oracle/product/11.2.0.4/network/admin

[oracle@chen admin]$ ls

samples shrept.lst tnsnames.ora

Automatic use of dynamic registration when listener.ora is not configured

[oracle@chen admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUL-2017 15:05:25

Copyright (c) 1991, 2013, 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.4.0 - Production

Start Date 20-JUL-2017 15:03:54

Uptime 0 days 0 hr. 1 min. 30 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Log File /u01/app/oracle/diag/tnslsnr/chen/listener/alert/log.xml

Listening Endpoints Summary...

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

Services Summary...

Service "orcl" has 1 instance(s).

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

Service "orclXDB" has 1 instance(s).

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

The command completed successfully

Two: Listen to static registration

[oracle@chen admin]$ pwd

/u01/app/oracle/product/11.2.0.4/network/admin

[oracle@chen admin]$ cat listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4)

(SID_NAME = orcl)

)

)

Static registration:

Remote database startup (password file required);

C:\Users\Administrator>sqlplus sys/oracle@10.1.233.100:1521/orcl as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thursday Jul 20 13:33:09 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to idle routine.

SQL> startup

The ORACLE routine has been started.

Total System Global Area 839282688 bytes

Fixed Size 2257880 bytes

Variable Size 545262632 bytes

Database Buffers 289406976 bytes

Redo Buffers 2355200 bytes

Database loaded.

Database is open. [oracle@chen admin]$ lsnrctl status

.....

Listening Endpoints Summary...

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

Services Summary...

Service "orcl" has 2 instance(s).

Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...

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

Service "orclXDB" has 1 instance(s).

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

The command completed successfully

Configuration DG,EM, etc. Static registration is recommended

III: Monitoring dynamic registration

[oracle@chen admin]$ cat listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = chen)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

SQL> alter system register;

System altered.

[oracle@chen admin]$ lsnrctl status

Services Summary...

Service "orcl" has 1 instance(s).

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

Service "orclXDB" has 1 instance(s).

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

The command completed successfully

When the instance is not started, the service cannot be registered.

Services Summary...

Listening Endpoints Summary...

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

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

The listener supports no services

The command completed successfully

Database cannot be started remotely while dynamically registered

C:\Users\Administrator>sqlplus sys/oracle@10.1.233.100:1521/orcl as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thursday Jul 20 14:26:57 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:

ORA-12514: TNS: The listener currently does not recognize the service requested in the connection descriptor

Please enter your username:

IV: Monitoring dynamic registration + static registration

[oracle@chen admin]$ cat listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4)

(SID_NAME = orcl)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = chen)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

[oracle@chen admin]$ lsnrctl status

......

Services Summary...

Service "orcl" has 2 instance(s).

Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...

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

Service "orclXDB" has 1 instance(s).

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

The command completed successfully

Database can be started remotely

C:\Users\Administrator>sqlplus sys/oracle@10.1.233.100:1521/orcl as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thursday Jul 20 14:22:59 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to idle routine.

SQL> startup

The ORACLE routine has been started.

Total System Global Area 839282688 bytes

Fixed Size 2257880 bytes

Variable Size 545262632 bytes

Database Buffers 289406976 bytes

Redo Buffers 2355200 bytes

Database loaded.

Database is open. The official documentation is located as follows:

Oracle Database Net Services Reference

http://docs.oracle.com/cd/E11882_01/network.112/e10835/listener.htm#NETRF008

Oracle Net Listener Parameters (listener.ora)

http://docs.oracle.com/cd/E11882_01/network.112/e10835/listener.htm#NETRF008

About "how to monitor Oracle" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.

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.