监听日志查看

   查看那些IP登录过数据库，都可以通过监听日志文件来查看。

在10g中$ORACLE_HOME/ network/log下和sqlnet.ora放在一起。在10.98.100.140中可以看到。

在listener.log日志中查看：

 

11-7月 -2012 16:05:28 * (CONNECT_DATA=(SID=MES)(CID=(PROGRAM=perl@xtmesser01)(HOST=xtmesser01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.98.100.140)(PORT=34407)) * establish * MES * 0

11-7月 -2012 16:06:02 * (CONNECT_DATA=(SID=MES)(CID=(PROGRAM=D:\install\plsql\plsqldev.exe)(HOST=WANGMIN)(USER=wangmin))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.86.7.75)(PORT=2785)) * establish * MES * 0

 

 

在sqlnet.ora查看：

 

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.86.7.75)(PORT=4138))

 

 

***********************************************************************

Fatal NI connect error 12170.

 

  VERSION INFORMATION:

        TNS for Linux: Version 10.2.0.1.0 - Production

        Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.1.0 - Production

        TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.1.0 - Production

  Time: 10-7月 -2012 17:27:45

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12535

    TNS-12535: TNS:operation timed out

    ns secondary err code: 12606

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.86.7.75)(PORT=4146))

 

 

***********************************************************************

Fatal NI connect error 12170.

 

  VERSION INFORMATION:

        TNS for Linux: Version 10.2.0.1.0 - Production

        Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.1.0 - Production

        TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.1.0 - Production

  Time: 10-7月 -2012 17:50:48

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12535

    TNS-12535: TNS:operation timed out

    ns secondary err code: 12606

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.86.7.75)(PORT=4690))

 

 

 

注：在sqlnet.ora可以找出登录失败的原因。

 

 

11g中监听日志文件存放在$ORACLE_BASE/diag/tnslsnr/meslqprimary/listener/trace下：

 

11-7? -2012 16:14:23 * (CONNECT_DATA=(SID=mes)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.80.1.17)(PORT=48671)) * establish * mes * 0

11-7? -2012 16:14:23 * (CONNECT_DATA=(SID=mes)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.80.1.17)(PORT=48672)) * establish * mes * 0

11-7? -2012 16:14:25 * service_update * MES * 0

Wed Jul 11 16:14:26 2012

11-7? -2012 16:14:26 * (CONNECT_DATA=(SID=dg4msql)(CID=(PROGRAM=)(HOST=meslqprimary)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.80.1.16)(PORT=16673)) * establish * dg4msql * 0

11-7? -2012 16:14:26 * (CONNECT_DATA=(SID=dg4msql)(CID=(PROGRAM=)(HOST=meslqprimary)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.80.1.16)(PORT=16672)) * establish * dg4msql * 0

11-7? -2012 16:14:28 * service_update * MES * 0

11-7? -2012 16:14:31 * service_update * MES * 0

11-7? -2012 16:14:33 * (CONNECT_DATA=(SID=mes)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.80.1.17)(PORT=48673)) * establish * mes * 0

11-7? -2012 16:14:33 * (CONNECT_DATA=(SID=mes)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.80.1.17)(PORT=48674)) * establish * mes * 0

11-7? -2012 16:14:34 * service_update * MES * 0

11-7? -2012 16:14:36 * (CONNECT_DATA=(SID=dg4msql)(CID=(PROGRAM=)(HOST=meslqprimary)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.80.1.16)(PORT=16677)) * establish * dg4msql * 0

Wed Jul 11 16:14:36 2012

11-7? -2012 16:14:36 * (CONNECT_DATA=(SID=dg4msql)(CID=(PROGRAM=)(HOST=meslqprimary)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.80.1.16)(PORT=16678)) * establish * dg4msql * 0

11-7? -2012 16:14:37 * service_update * MES * 0

 

 

而sqlnet.ora存放在$ORACLE_HOME/network/log下面。

 

NL-08013:  ADR 基目录无效

NL-08014: 无法初始化可诊断性框架, 回退到旧网络跟踪/日志记录

 User inputted base directory is invalid [48187] [/oracle]

 

 NL-08013:  ADR 基目录无效

NL-08014: 无法初始化可诊断性框架, 回退到旧网络跟踪/日志记录

 User inputted base directory is invalid [48187] [/oracle]

 

 NL-08013:  ADR 基目录无效

NL-08014: 无法初始化可诊断性框架, 回退到旧网络跟踪/日志记录

 User inputted base directory is invalid [48187] [/oracle]

 

 NL-08013:  ADR 基目录无效

NL-08014: 无法初始化可诊断性框架, 回退到旧网络跟踪/日志记录

 User inputted base directory is invalid [48187] [/oracle]

 

 NL-08013:  ADR 基目录无效

NL-08014: 无法初始化可诊断性框架, 回退到旧网络跟踪/日志记录

 User inputted base directory is invalid [48187] [/oracle]

 

 

 

总结：

 

1 监听日志能够查看得出，那些IP登录数据库

2 只要操作就会在监听日志中记录信息，有具体的时间段

3 sqlnet.log能够查看登录报错信息

4  两者是很好的诊断工具