9、oracle trace监听连接
trace（跟踪）监听
有时候会出现这么一种情况：就是客户端连接不上监听，或者连接上监听以后有各种各样的故障，比如：连接慢、报错等等相关的信息，所以有时候需要对监听起一个trace（跟踪），看一下客户端对数据库监听的连接的一个情况
如果客户端连接监听连不上的一个排错过程
1、确认数据库是否启动
[oracle@db11g admin]$ sqlplus system/oracle
SQL> select open_mode from v$database;
确认数据库实例的名字
[oracle@db11g admin]$ echo $ORACLE_SID
确认数据库的名字
[oracle@db11g ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl
2、确认监听是否启动
[oracle@db11g admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JAN-2017 20:05:16
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.12)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 16-JAN-2017 15:09:04
Uptime 0 days 4 hr. 56 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/db11g/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.12)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service "cis" has 1 instance(s).
Instance "cis", status READY, has 1 handler(s) for this service…
Service "cisXDB" has 1 instance(s).
Instance "cis", status READY, has 1 handler(s) for this service…
The command completed successfully
3、确认监听里面的service_name（主要看这个）、instance_name
状态是什么：
ready： 没问题
blocked：有问题
unknown：要确认一下是否有问题
4、服务器本地使用ezconnect（不解析服务名）进行连接
[oracle@db11g admin]$ sqlplus system/oracle@192.168.56.70:1521/cis
5、客户端测试1521端口
[oracle@db11g admin]$ telnet 192.168.56.70 1521
6、或者使用tnsping
测试：解析、ip、端口，但是不能测试service_name
7、客户端使用ezconect去测试
sqlplus system/oracle@192.168.56.70:1521/cis
8、客户端使用tnsnames去测试
sqlplus system/oracle@192.168.56.70:1521/cistest
9、如果还有问题，就启动trace
[oracle@db11g admin]$ lsnrctl
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JAN-2017 20:20:43
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:
start stop status
services version reload
save_config trace spawn
change_password quit exit
set* show*
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.12)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 16-JAN-2017 15:09:04
Uptime 0 days 5 hr. 12 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/db11g/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.12)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service "cis" has 1 instance(s).
Instance "cis", status READY, has 1 handler(s) for this service…
Service "cisXDB" has 1 instance(s).
Instance "cis", status READY, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL> help trace
trace OFF | USER | ADMIN | SUPPORT [<listener_name>] : set tracing to the specified level
LSNRCTL> trace SUPPORT
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.12)(PORT=1521)))
Opened trace file: /u01/app/oracle/diag/tnslsnr/db11g/listener/trace/ora_1583_139803420210944.trc
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.12)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 16-JAN-2017 15:09:04
Uptime 0 days 5 hr. 13 min. 6 sec
Trace Level support
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/db11g/listener/alert/log.xml
Listener Trace File /u01/app/oracle/diag/tnslsnr/db11g/listener/trace/ora_1583_139803420210944.trc
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.12)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service "cis" has 1 instance(s).
Instance "cis", status READY, has 1 handler(s) for this service…
Service "cisXDB" has 1 instance(s).
Instance "cis", status READY, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL> help
LSNRCTL> status
LSNRCTL> show
LSNRCTL> show trc_file
LSNRCTL> show trc_directory
LSNRCTL> show trc_level
LSNRCTL> trace off
注意，我们开启跟踪是有三个级别的，分别为user、admin和support级别。
具体含义如下：
Off或者数值0：表示对当前的监听器不开启跟踪；
User或者数值4：user trace information;
Admin或者数值10：administration trace information；
Support或者数值16：Oracle support Services trace information；
要查看trace文件，需要格式化一下，使用trcasst命令来格式化：
[oracle@db11g ~]$ cd /u01/app/oracle/diag/tnslsnr/db11g/listener/trace
[oracle@db11g trace]$ ls
listener.log ora_1583_139803420210944.trm
ora_1583_139803420210944.trc
[oracle@db11g trace]$ trcasst ora_1583_139803420210944.trm > ora_1583_139803420210944.txt
[oracle@db11g trace]$ cat ora_1583_139803420210944.txt
Trace Assistant Utility: Version 11.2.0.3.0 Production on January 16, 2017 8:31:42 PM
Copyright (c) 2001, 2011, Oracle. All rights reserved.
*************************************************************************
* Trace Assistant *
*************************************************************************
----------------------
Trace File Statistics:
----------------------
Total number of Sessions: 0
DATABASE:
Operation Count: 0 OPENS, 0 PARSES, 0 EXECUTES, 0 FETCHES
ORACLE NET SERVICES:
Total Calls : 0 sent, 0 received, 0 oci
Total Bytes : 0 sent, 0 received
Average Bytes: sent per packet, received per packet
Maximum Bytes: 0 sent, 0 received
Grand Total Packets: 0 sent, 0 received
*************************************************************************
* Trace Assistant has completed *
*************************************************************************
一个java程序连接Oracle的例子
监听的配置文件
3、sqlnet.ora文件（注意一下，虽然不是很重要）
sqlnet.ora文件，在客户端和服务器端都起作用(在客户端和服务器端都有)
sqlnet.ora文件：是用来配置和控制监听的一些工作方式的
sqlnet.ora文件的作用：
1、可以设置buffer的大小
2、可以设置TCP.INVITED_NODES：客户端连接监听的时候，TCP.INVITED_NODES后面设置了一堆的IP地址，监听就使用TCP.INVITED_NODES这个参数来对比客户端的IP地址，哪个可以连接，哪个不能连接，只有在TCP.INVITED_NODES参数里面的IP地址可以连接
3、控制数据的加密
