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

This article is about troubleshooting Oracle 11g if you monitor it. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The monitoring of the virtual database server oracle11G aborted unexpectedly. The error is as follows

Execute lsnrctl start on the virtual machine to solve the problem.

1. When the connection is abnormal, you can find clues by analyzing the monitoring log.

~ [test] $find $ORACLE_HOME-name listener.log

/ opt/64bit/oracle/11.2.0/log/diag/tnslsnr/sinrndvud062/listener/trace/listener .log

~ [DBCHC] $vim / opt/64bit/oracle/11.2.0/log/diag/tnslsnr/sinrndvud062/listener/trace/listener.log

110856 Fri Oct 21 17:00:31 2018

110857 21-OCT-2018 17:00:31 (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DBCHC) (CID = (PROGRAM = sqlplus) (HOST = sinrndvud062) (USER = ora112) (ADDRESS = (PROTOCOL = tcp) (HOST = 10.160.164.131) (PORT = 47405)) establish DBCHC 12514

110858 TNS-12514:TNS: the listener currently does not know the requested service descriptor in the connection

110859 Fri Oct 21 17:02:47 2018

110860 21-OCT-2018 17:02:47 service_update OSMSRDB 0

110861 21-OCT-2018 17:02:53 service_update OSMSRDB 0

110862 21 ADDRESS Oct-2018 17:02:54 (CONNECT_DATA = (SID = OSMSRDB) (CID = (PROGRAM = JDBC thin client) (HOST = jdbc) (USER = linqus)) (ADDRESS = (PROTOCOL = tcp) (HOST = 10.160) .164.132) (PORT = 32823)) establish OSMSRDB 0

110863 21-OCT-2018 17:02:56 service_update OSMSRDB 0

110864 Fri Oct 21 17:03:07 2018

110865 21-OCT-2018 17:03:07 service_update XSONGDB 0

110866 21-OCT-2018 17:03:08 (CONNECT_DATA = (SID = OSMSRDB) (CID = (PROGRAM = JDBC thin client) (HOST = jdbc) (USER = linqus) (ADDRESS = (PROTOCOL = tcp) (HOST = 10.160). 164.132) (PORT = 32835)) establish OSMSRDB 0

110867 21-OCT-2018 17:03:08 service_update OSMSRDB 0

110868 Fri Oct 21 17:06:19 2018

110869 21-OCT-2018 17:06: 19 service_update XSONGDB 0

110870 21-OCT-2018 17:06:21 (CONNECT_DATA = (SID = XSONGDB) (CID = (PROGRAM = JDBC thin client) (HOST = jdbc) (USER = linqus)) (ADDRESS = (PROTOCOL = tcp) (HOST = 10.160.164.200) (PORT = 60498)) establish XSONGDB 0

110871 21-OCT-2018 17:06:22 service_update XSONGDB 0

110872 21-OCT-2018 17:06:24 (CONNECT_DATA = (SID = XSONGDB) (CID = (PROGRAM = JDBC thin client) (HOST = jdbc) (USER = linqus)) (ADDRESS = (PROTOCOL = tcp) (HOST = 10.160.164.200) (PORT = 60502)) establish XSONGDB 0

110873 21-OCT-2018 17:06:25 service_update XSONGDB * 0

In the RAC environment, you need to switch to the grid user to query the listening log

Test # su-grid

/ home / ora11grid [FRWK]: find $find ORACLE_HOME-name listener.log

Lookup: / opt / ora11grid / crf / admin / run / crflogd': permission denied Lookup: / opt / ora11grid / crf / admin / run / crfmond': permission denied

/ opt/ora11grid/log/diag/tnslsnr/vgerndpud852/listener/trace/listener.log

/ home / ora11grid [FRWK]: lsnrctl

LSNRCTL for Linux: version 11.2.0.3.0-produced at 04:28:53 in 17-NOV-2018

Copyright (c) 1991 Ji 2011, Oracle. copyright.

Welcome to LSNRCTL and type help for information.

LSNRCTL > show

Show

Asterisk (*) indicates that the following actions can be used after a modifier or extension command:

Rawmode displaymode

Rules trc_file

Trc_directory trc_level

Log_file log_directory

Log_status current_listener

Inbound_connect_timeout startup_waittime

Snmp_visible save_config_on_stop

Dynamic_registration enable_global_dynamic_endpoint

Oracle_home pid

Connection_rate_limit

LSNRCTL > show log_status

Connect to (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC) (KEY = LISTENER)

The LISTENER parameter "log_status" is set to ON

The command completed successfully.

2. The solution of monitoring abnormal abort

You can add the following parameters to listener.ora

SUBSCRIBE_FOR_NODE_DOWNEVENT= OFF

As follows:

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER = OFF

Edit the monitoring script and start the monitoring automatically when the monitoring exception is aborted (detect once per minute)

The script is as follows:

$cat listener.sh

#! / bin/bash

ORACLE_HOME = / U01 / product / oracle / 11.2.0 / db_1; export ORACLE_HOME

Pid = ps-ef | grep tnslsnr | grep-v grep | awk' {print $2}'

If [echo $pid | awk-v bi = 1'{print ($1 > bi)? "1": "0"}'- eq "1"]

Else

Echo "listeners are operating normally"

Else

Echo "listener has stopped"

$ORACLE_HOME / bin / lsnrctl start

Exit

$crontab-l

/ 1 sh / data/run/listener.sh 1 > > / data/run/ log / listener.log 2 > > / data/run/ log / listener.bad

3. Regular cleaning of monitoring logs

Monitor log files (listener.log) should be cleaned regularly. If you do not clean up periodically, you will encounter the following problems:

1) snooping log files (listener.log) are getting larger and larger, taking up additional storage space. (of course, there is no shortage of space for storing cabbage prices now. But we still have to keep improving in the spirit of craftsmen.

2) snooping log files (listener.log) become too large to cause some problems: the size of LISTENER.LOG logs cannot exceed 2GB, which will cause LISTENER listeners to fail to handle new connections.

3) the listening log file (listener.log) becomes too large, which brings some performance problems and troubles to writing and viewing.

Also: listening logs listener.log logs are placed in the $ORACLE_BASE/diag/tnslsnr/ machine name / listener/trace directory

4. Analysis of three kinds of files

1) sqlnet.ora- uses this file to decide how to find a connection string that appears in a connection.

For example, our client input

Sqlplus sys/oracle@ora

If my sqlnet.ora looks like this

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES,HOSTNAME,EZCONNECT)

Then, the client will first look for the ora record in the tnsnames.ora file. If there is no corresponding record, try to take ora as a host name, resolve its ip address through the network, and then connect to the instance of GLOBAL_DBNAME=ora on this ip. Of course, here ora is not a host name.

EZCONNECT said: you can connect to Oracle in a simple way, for example:

1 CONNECT username/password@host [: port] [/ service_name] [/ instance_name]

2 CONNECT username/password@host [: port] [/ service_name]

3 sqlplus / as sysdba

4 sqlplus sys as sysdba/sys@127.0.0.1:1521

If I were like this,

NAMES.DIRECTORY_PATH= (TNSNAMES)

Then the client will only look for ora records from tnsnames.ora, and there are other options in parentheses, such as LDAP, which are not commonly used.

2) the tnsnames.ora- file is similar to unix's hosts file, providing the correspondence from tnsname to hostname or ip.

This file will only be attempted if TNSNAMES is in the order in which the client parses the connection string, such as NAMES.DIRECTORY_PATH= (TNSNAMES) in sqlnet.ora.

Example:

ORA_TEST =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora)

)

)

ORA_TEST: the service alias used by the client to connect to the server. Be sure to write on the top line, otherwise the service alias will not be recognized.

PROTOCOL: the communication protocol between the client and the server, which is usually TCP, and this content generally does not need to be changed.

HOST:ORACLE server-side IP address or hostname. Make sure that the server-side listening starts properly.

PORT: the port on which the database is listening. You can view the listener.ora file on the server or through the lnsrctl status [listener name] command from the command prompt of the machine where the database is listening. The value of Port here must be the same as the port on which the database is listening.

SERVICE_NAME: on the server side, log in with the system user and view it with the command sqlplus > show parameter service_name.

3) configuration file of listener.ora-listener listener process

There is nothing more to say about the listener process, accepting remote requests for access to the database and transferring it to the server process of oracle. So if you are not using a remote connection, the listener process is not required, and shutting down the listener process will not affect the existing database connection.

Example:

# listener.ora Network Configuration File: # E:/oracle/product/10.1.0/Db_2/NETWORK/ADMIN/listener.ora

Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ora)

(ORACLE_HOME = E:/oracle/product/10.1.0/Db_2)

(SID_NAME = ora)

)

)

LISTENER =

(DESCRIPTION =

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

)

LISTENER: listener name. Multiple listeners can be configured, and the port numbers of multiple listeners should be distinguished.

GLOBAL_DBNAME: global database name. Through select * from global_name; query

ORACLE_HOME: the directory of oracle software

SID_NAME: server-side (native) SID

PROTOCOL: snooping protocol, usually using TCP

HOST: native IP address. Use floating IP for dual computers (IP address is easier to resolve)

PORT: the port number of the listener. Use netstat-an to check that the port is not occupied.

When you type sqlplus sys/oracle@orcl

1. Query sqlnet.ora to see how the name is resolved, and find that it is TNSNAME.

2. Query the tnsnames.ora file, find the record of orcl inside, and find the hostname, port and service_name

3. If there is nothing wrong with the listener process, establish a connection with the listener process.

4. Listener takes the next action according to different server modes, such as dedicated server mode or shared server mode. The default is dedicated server mode, and if there is no problem, the client connects to the server process of the database.

5. At this time, the network connection has been established and the historical mission of the listener process has been completed.

Thank you for reading! This is the end of this article on "Oracle 11g if monitoring and troubleshooting". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to 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.