network

关于12c Pluggable database注册listener问题

August 9, 2013 12c, network, oracle No comments

演示一下12c中关于pdb的listener register,unregister问题

[oracle@db-42 ~]$ lsnrctl status listener1

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 09-AUG-2013 14:53:56

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-42)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                08-AUG-2013 21:04:57
Uptime                    0 days 17 hr. 48 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/app2/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /data/app2/oracle/diag/tnslsnr/db-42/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "testcdb" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
Service "testcdbXDB" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
The command completed successfully

——注册pdb到listener1

[oracle@db-42 ~]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 9 14:54:20 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> desc v$pdbs;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CON_ID 					    NUMBER
 DBID						    NUMBER
 CON_UID					    NUMBER
 GUID						    RAW(16)
 NAME						    VARCHAR2(30)
 OPEN_MODE					    VARCHAR2(10)
 RESTRICTED					    VARCHAR2(3)
 OPEN_TIME					    TIMESTAMP(3)
 CREATE_SCN					    NUMBER
 TOTAL_SIZE					    NUMBER

SQL> select con_id,name from v$pdbs;

    CON_ID NAME
---------- ------------------------------
	 2 PDB$SEED
	 3 CON_CDB_NEW

SQL> alter session set container=CON_CDB_NEW;

Session altered.

SQL> exec dbms_service.start_service('CON_CDB_NEW');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status listener1

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 09-AUG-2013 14:55:35

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-42)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                08-AUG-2013 21:04:57
Uptime                    0 days 17 hr. 50 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/app2/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /data/app2/oracle/diag/tnslsnr/db-42/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "con_cdb_new" has 1 instance(s). ------------------> new pdb service register in listener
  Instance "testcdb", status READY, has 1 handler(s) for this service...
Service "testcdb" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
Service "testcdbXDB" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
The command completed successfully


——- unregister pdb service

SQL> exec dbms_service.stop_service('CON_CDB_NEW');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status listener1;

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 09-AUG-2013 14:59:05

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-42)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                08-AUG-2013 21:04:57
Uptime                    0 days 17 hr. 54 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/app2/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /data/app2/oracle/diag/tnslsnr/db-42/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "testcdb" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
Service "testcdbXDB" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
The command completed successfully


SQL> ! sqlplus 'sys/oracle@TESTCDB as sysdba'

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 9 15:00:04 2013

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


同理在12c之前包括12c中可以管理多个service_name

[oracle@db-42 ~]$ source ~/.bash_profile_11 
[oracle@db-42 ~]$ ora si 

SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 9 15:01:13 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> !lsnrctl status  

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-AUG-2013 15:01:19

Copyright (c) 1991, 2011, 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.3.0 - Production
Start Date                09-AUG-2013 14:29:46
Uptime                    0 days 0 hr. 31 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /data/app1/oracle/diag/tnslsnr/db-42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1521)))
Services Summary...
Service "liuyang" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show parameter service_names;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 testdb, liuyang
SQL> exec dbms_service.stop_service('liuyang');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-AUG-2013 15:01:47

Copyright (c) 1991, 2011, 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.3.0 - Production
Start Date                09-AUG-2013 14:29:46
Uptime                    0 days 0 hr. 32 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /data/app1/oracle/diag/tnslsnr/db-42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1521)))
Services Summary...
Service "testdb" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> exec dbms_service.start_service('liuyang');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-AUG-2013 15:02:12

Copyright (c) 1991, 2011, 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.3.0 - Production
Start Date                09-AUG-2013 14:29:46
Uptime                    0 days 0 hr. 32 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /data/app1/oracle/diag/tnslsnr/db-42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1521)))
Services Summary...
Service "liuyang" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> exec dbms_service.stop_service('testdb');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status 

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-AUG-2013 15:02:41

Copyright (c) 1991, 2011, 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.3.0 - Production
Start Date                09-AUG-2013 14:29:46
Uptime                    0 days 0 hr. 32 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /data/app1/oracle/diag/tnslsnr/db-42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1521)))
Services Summary...
Service "liuyang" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully

注意无法直接停止testdb service,只能对service ‘liuyang’进行操作。

Summary of DBMS_SERVICE Subprograms

Table 140-5 DBMS_SERVICE Package Subprograms

Subprogram Description

CREATE_SERVICE Procedure

Creates service

DELETE_SERVICE Procedure

Deletes service

DISCONNECT_SESSION Procedure

Disconnects sessions running under this service

MODIFY_SERVICE Procedure

Modifies service

START_SERVICE Procedure

Activates service

STOP_SERVICE Procedure

Stops service

Logon Storm

November 16, 2012 network, oracle No comments

A Logon storm is a sudden spike in the number of client connection requests. Logon storms can occur due to a variety of factors. They could be malicious like a DoS attack. Or they could occur due to administrative actions – such as a middle tier coming online.
The process of starting a database session has inherent CPU costs associated with it – from the creation of a new OS process to attaching to the SGA. Every system has a limit above which the spawning of new database sessions can starve existing
sessions of CPU resources. This could result in many unexpected consequences including application timeouts, increased response times, un-responsiveness and other cascading effects.

The problem

errlog:

Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
 
        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:386)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1054)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:308)

[grid@dcb-srv-0173 trace]$ fgrep “07-NOV-2012 16:” listener.log |fgrep “establish” |awk ‘{print $1 ” ” $2}’ |awk -F: ‘{print $1 “:” $2 }’ |sort |uniq -c
1396 07-NOV-2012 16:00
394 07-NOV-2012 16:01
218 07-NOV-2012 16:02
285 07-NOV-2012 16:03
364 07-NOV-2012 16:04
414 07-NOV-2012 16:05
436 07-NOV-2012 16:06
349 07-NOV-2012 16:07
290 07-NOV-2012 16:08
367 07-NOV-2012 16:09
551 07-NOV-2012 16:10
579 07-NOV-2012 16:11
502 07-NOV-2012 16:12
501 07-NOV-2012 16:13
468 07-NOV-2012 16:14
461 07-NOV-2012 16:15
685 07-NOV-2012 16:16
451 07-NOV-2012 16:17
448 07-NOV-2012 16:18
298 07-NOV-2012 16:19
358 07-NOV-2012 16:20
321 07-NOV-2012 16:21
451 07-NOV-2012 16:22
303 07-NOV-2012 16:23
475 07-NOV-2012 16:24
399 07-NOV-2012 16:25
412 07-NOV-2012 16:26
307 07-NOV-2012 16:27
358 07-NOV-2012 16:28
444 07-NOV-2012 16:29
510 07-NOV-2012 16:30
420 07-NOV-2012 16:31
461 07-NOV-2012 16:32
408 07-NOV-2012 16:33
299 07-NOV-2012 16:34
425 07-NOV-2012 16:35
350 07-NOV-2012 16:36
545 07-NOV-2012 16:37
324 07-NOV-2012 16:38
389 07-NOV-2012 16:39
339 07-NOV-2012 16:40
385 07-NOV-2012 16:41
455 07-NOV-2012 16:42
366 07-NOV-2012 16:43
304 07-NOV-2012 16:44
319 07-NOV-2012 16:45
465 07-NOV-2012 16:46
384 07-NOV-2012 16:47
480 07-NOV-2012 16:48
362 07-NOV-2012 16:49
563 07-NOV-2012 16:50
484 07-NOV-2012 16:51
402 07-NOV-2012 16:52
350 07-NOV-2012 16:53
339 07-NOV-2012 16:54
454 07-NOV-2012 16:55
451 07-NOV-2012 16:56
409 07-NOV-2012 16:57
444 07-NOV-2012 16:58
452 07-NOV-2012 16:59

16:00瞬间涌入了大量连接,由于这台机器的配置十分强大导致web服务器集体中断了1-2s.并没有在DB服务器端引起大的负载。

[root@dcb-srv-0173 sa]# sar -f /var/log/sa/sa07 -s 15:50:00 -e 16:05:00
Linux 2.6.18-308.el5 (dcb-srv-0173) 11/07/2012

可以明显看到16:00的波谷

Some relative posts:

cursor: pin S waits, sporadic CPU spikes
A close encounter with real world performance issues

Solution:

Using listener RATE_LIMIT parameter
This parameter indicates that a particular listening endpoint is rate limited. 
This is specified in the ADDRESS section of the listener endpoint 
configuration. For example, 
LISTENER=(ADDRESS= 
(PROTOCOL=tcp) 
(HOST=sales-server)(PORT=1521) 
(RATE_LIMIT=yes)) 
This parameter can be configured in two ways: 
1. When the parameter is set to “yes”, the endpoint is included in 
the enforcement of a listener-wide connection rate. This has to 
be used in conjunction with the CONNECTION_RATE_LISTENER
parameter.  
2. When a value greater than 0 is specified, the rate limit is enforced 
at the endpoint level.  
eg:
LISTENER=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales)(PORT=1521)(RATE_LIMIT=3))
(ADDRESS=(PROTOCOL=tcp)(HOST=lmgmt)(PORT=1522)(RATE_LIMIT=no)))
Use dedicated for:
 High-performance connections
 Active, long-running, data transfer intensive operations
 
Use shared for:
 Sessions that may be idle for some time
 Clients that frequently connect and disconnect
 
Use DRCP (11g):
 When you have thousands of clients which need access to a database server session for a short period of time
 Applications mostly use same database credentials, and have 
identical session settings
PHP (OCI8 extension), Python (cx_Oracle), Perl (DBI)

同理以上两种对于logon storm 有限制作用

11gR2 RAC listener

August 18, 2012 network, oracle No comments

oracle 11gR2 rac listener引入了3项主要的改变:

1. listener默认owner为grid

[oracle@db-42 ~]$ srvctl config listener
Name: LISTENER
Network: 1, Owner: grid
Home:
End points: TCP:1521

2. listener 被oracle agent 所管理 (oraagent)
[grid@db-41 admin]$ ps -ef |grep oraagent
grid 22195 18519 0 16:42 pts/0 00:00:00 grep oraagent
grid 25674 1 0 Jul17 ? 02:51:35 /data/11.2.0/grid/bin/oraagent.bin
grid 27720 1 0 Jul17 ? 00:47:49 /data/11.2.0/grid/bin/oraagent.bin
oracle 32300 1 0 Aug02 ? 01:13:35 /data/11.2.0/grid/bin/oraagent.bin

[grid@db-41 admin]$ cat endpoints_listener.ora
LISTENER_DB-41=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=db-41-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.41)(PORT=1521)(IP=FIRST)))) # line added by Agent

3. SCAN listener被引入

关于 scan listener以下图为例

oracle 使用3个scan-ip来实现scans的HA与loadbalance架构,这里需要重点提一下

1:当scan-ip少于节点总数时候 对应的scan listener 只存在于对应节点,也就是说不可能出现one node more scans的情况

举个例子:

[grid@db-42 admin]$ crsctl stat res -t |grep -i listener
ora.LISTENER.lsnr
ora.LISTENER_SCAN1.lsnr

[grid@db-42 admin]$ crsctl stat res ora.LISTENER_SCAN1.lsnr
NAME=ora.LISTENER_SCAN1.lsnr
TYPE=ora.scan_listener.type
TARGET=ONLINE
STATE=ONLINE on db-42
TARGET=ONLINE
STATE=ONLINE on dm01db02

一个4节点rac只存在一个scan listener 此时的scan-listener存活于node2上面,因为只有一个scan-ip 也就失去了HA的功效

2:对于scan-listener多于节点数的情况,必定会出现一个节点多个scan bundle的情况。

[grid@db-42 admin]$ srvctl config scan
SCAN name: scan-ip, Network: 1/10.0.0.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /scan-ip/10.0.0.145

可以看到对于现在的情况 只能存在one bye one的情况,对于使用多个SCAN实现HA与LB 可以通过DNS实现scan-ip动态分配。其实在exadata的默认设置中
也是使用了3个scan-ip 但是由于客户的某种问题,往往也是使用第一种情况。

[grid@dm01db02 ~]$ cat /etc/hosts |grep scan
# you will have to decide on which scan address to uncomment – choose only 1
10.0.1.205 dm01-scan.yihaodian.com dm01-scan
#10.0.1.206 dm01-scan.yihaodian.com dm01-scan
#10.0.1.207 dm01-scan.yihaodian.com dm01-scan

可以看到默认是3个scan-ip 但是由于我们DNS服务种种限制,没有使用HA架构。其实一直觉得SCAN这个东西很鸡肋,一套成熟的RAC系统
必须通过精准的应用切割实现,这也就是为什么很多RAC使用了人为控制,不让也不允许系统的自动负载均衡。不过对于exadata来说,由于
使用了RDS传输协议,使得这种影响降低了很多。

SCAN listener的连接过程如下:

1. app 通过 scan-ip 发起一个connection请求。
2. scan listener 接受并传递这个请求给 local listener.在这个过程中 scan-listener会通过负载均衡算法选择一个负载较低的local listener 传递
3. local listener 接受这个请求 从而创建了一个DB connection.

另外关于朋友提到的 Endpoints_listener 问题:

1. Endpoints_listener.ora file is there for backward compatibility with pre-11.2 databases.

2. DBCA needs to know the endpoints location to configure database parameters and tnsnames.ora file.

3. It used to use the listener.ora file, 11.2 RAC listener.ora by default only has IPC entries.

4. should not be used LSNRCTL management LISTENER, and need to use SRVCTL or CRSCTL tool management, for LSNRCTL will not recognize endpoints_listener. The information ora, cause monitoring not necessary to address, work on the port 

Problem : Fatal NI Connect Error 12170, ‘TNS-12535: TNS:operation timed out’ Reported in 11g Alert Log

December 1, 2011 network, oracle No comments

Applies to:

Oracle Net Services – Version: 11.1.0.6 to 11.2.0.2 – Release: 11.1 to 11.2
Oracle Server – Enterprise Edition – Version: 11.1.0.6 to 11.2.0.2 [Release: 11.1 to 11.2]
Information in this document applies to any platform.
TNS-12170, ORA-12170, TNS-12535, TNS-00505 alert.log
Symptoms

nt secondary err code: 110 Monitoring of the 11g database Alert log(s) may show frequent timeout related messages such as:

– On Oracle Solaris:

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

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Solaris: Version 11.2.0.1.0 – Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.1.0 – Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.1.0 – Production
Time: 22-JAN-2011 21:48:23
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Operation timed out
nt secondary err code: 145
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.159.34.117)(PORT=1092))

———
The “nt secondary err code” will be different based on the operating system.

Linux x86 or Linux x86-64: “nt secondary err code: 110”
HP-UX Server: “nt secondary err code: 238”
AIX: “nt secondary err code: 78”

Changes

No change are necessary, but may have recently upgraded the database to 11g release 1 or higher, or installed a new Oracle11g database.

Note: Prior to 11gR1 these same ‘Fatal NI connect error 12170’ are written to the sqlnet.log
Cause

These time out related messages are mostly informational in nature. The messages indicate the specified client connection (identified by the ‘Client address:’ details) has experienced a time out. The ‘nt secondary err code’ identifies the underlying network transport, such as (TCP/IP) timeout limits after a client has abnormally terminated the database connection.

The ‘nt secondary err code’ translates to underlying network transport timeouts for the following Operating Systems:

For the Solaris system: nt secondary err code: 145:
ETIMEDOUT 145 /* Connection timed out */

For the Linux operating system: nt secondary err code: 110
ETIMEDOUT 110 Connection timed out

For the HP-UX system: nt secondary err code: 238:
ETIMEDOUT 238 /* Connection timed out */

For AIX: nt secondary err code: 78:
ETIMEDOUT 78 /* Connection timed out */

For Windows based platforms: nt secondary err code: 60 (which translates to Winsock Error: 10060)

Description: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

The reason the messages are written to the alert log is related to the use of the new 11g Automatic Diagnostic Repository (ADR) feature being enabled by default. See (Doc ID 454927.1).
Solution

Suggested Actions:

– Search the corresponding text version of the listener log located on the database server for the corresponding client connection referenced by the Client address details referenced in the alert log message.

For the message incident below you would search the listener log for the ‘Client address’ string:

(ADDRESS=(PROTOCOL=tcp)(HOST=10.159.34.117)(PORT=1092))

The search of the listener log should find the most recent connection before the time reference displayed in the alert log message, e.g. ’22-JAN-2011 21:48:23′.

-Corresponding listener log entry:

22-JAN-2011 21:20:12 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AMN11264.us.oracle.com)(CID=(PROGRAM=D:\app\mcassady\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=mcassady-lap)(USER=mca
ssady))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.159.34.117)(PORT=1092)) * establish * AMN11264.us.oracle.com * 0

– Alert log entry:
————

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Solaris: Version 11.2.0.1.0 – Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.1.0 – Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.1.0 – Production
Time: 22-JAN-2011 21:48:23
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Operation timed out
nt secondary err code: 145
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.159.34.117)(PORT=1092))

————

Note the time of the client corresponding client connection(s) in the listener log. Here you may find a particular client, set of clients or particular applications that are improperly disconnecting causing the timeout errors to be raised and recorder in the database alert log.

You may choose to revert from the new Automatic Diagnostic Repository (ADR) method to prevent the Oracle Net diagnostic details from being written to the alert log(s) by setting the following Oracle Net configuration parameters:

To revert to Oracle Net Server tracing/logging, set following parameter in the server’s sqlnet.ora :

DIAG_ADR_ENABLED = OFF

Also, to back out the ADR diag for the Listener component, set following parameter in the server’s listener.ora:

DIAG_ADR_ENABLED_ = OFF

– Where the would be replaced with the actual name of the configured listener(s) in the listener.ora configuration file. For example, if the listener name is ‘LISTENER’, the parameter would read:

DIAG_ADR_ENABLED_LISTENER = OFF

-Reload or restart the TNS Listener for the parameter change to take effect.

sqlnet.ora 控制访问IP

October 19, 2011 network, oracle 3 comments

sqlnet.ora中进行下列参数的设置可以限制或允许用户从特定的客户机连接到数据库中

[root@Hong ~]# ifconfig -a
eth0 Link encap:Ethernet HWaddr 00:0C:29:24:93:F5
inet addr:192.168.1.99 Bcast:192.168.1.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe24:93f5/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:10471 errors:0 dropped:0 overruns:0 frame:0
TX packets:10026 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:925836 (904.1 KiB) TX bytes:1022351 (998.3 KiB)
Interrupt:10 Base address:0x2024

[oracle@Hong ~]$ cd /u01/app/oracle/product/10.2.0.1/db_1/network/admin/
[oracle@Hong admin]$ cat tnsnames.ora
css =
(description =
(address =(protocol =tcp)(host=192.168.1.223)(port=1521))
(connect_data=
(server=dedicated)
(service_name=css)
)
)

我们切换到另一台主机 查看sqlnet.ora

[root@yang ~]# ifconfig -a
eth0 Link encap:Ethernet HWaddr 00:0C:29:40:56:87
inet addr:192.168.1.223 Bcast:192.168.1.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe40:5687/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:65490 errors:0 dropped:0 overruns:0 frame:0
TX packets:73327 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:10821555 (10.3 MiB) TX bytes:21653642 (20.6 MiB)
Interrupt:185 Base address:0x2024

[root@yang ~]# ps -ef |grep css
oracle 3313 1 0 Oct18 ? 00:00:00 ora_pmon_css
oracle 3315 1 0 Oct18 ? 00:00:00 ora_psp0_css
oracle 3318 1 0 Oct18 ? 00:00:00 ora_mman_css
oracle 3320 1 0 Oct18 ? 00:00:00 ora_dbw0_css
oracle 3322 1 0 Oct18 ? 00:00:00 ora_lgwr_css
oracle 3324 1 0 Oct18 ? 00:00:00 ora_ckpt_css
oracle 3326 1 0 Oct18 ? 00:00:02 ora_smon_css
oracle 3328 1 0 Oct18 ? 00:00:00 ora_reco_css
oracle 3330 1 0 Oct18 ? 00:00:01 ora_mmon_css
oracle 3332 1 0 Oct18 ? 00:00:00 ora_mmnl_css
oracle 3334 1 0 Oct18 ? 00:00:00 ora_d000_css
oracle 3336 1 0 Oct18 ? 00:00:00 ora_s000_css
oracle 3340 3291 0 Oct18 ? 00:00:03 oraclecss (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 3374 1 0 Oct18 ? 00:00:00 ora_cjq0_css
root 4176 3719 0 00:09 pts/3 00:00:00 grep css

[oracle@yang admin]$ cat sqlnet.ora
tcp.validnode_checking = yes
#tcp.excluded_nodes = (192.168.1.99)
NAMES.DIRECTORY_PATH= (TNSNAMES)
[oracle@yang admin]$

[oracle@Hong admin]$ tnsping css

TNS Ping Utility for Linux: Version 10.2.0.1.0 – Production on 19-OCT-2011 00:10:31

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/product/10.2.0.1/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (description = (address =(protocol =tcp)(host=192.168.1.223)(port=1521)) (connect_data= (server=dedicated) (service_name=css)))
OK (290 msec)
[oracle@Hong admin]$ sqlplus ‘sys/oracle@css as sysdba’

SQL*Plus: Release 10.2.0.1.0 – Production on Wed Oct 19 00:10:39 2011

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

此时可以正常访问源端服务器,下面我们来设置excluded参数

[oracle@yang admin]$ cat sqlnet.ora
tcp.validnode_checking = yes
tcp.excluded_nodes = (192.168.1.99)
NAMES.DIRECTORY_PATH= (TNSNAMES)
[oracle@yang admin]$ lsnrctl reload

LSNRCTL for Linux: Version 10.2.0.4.0 – Production on 19-OCT-2011 00:11:58

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.223)(PORT=1521)))
The command completed successfully
[oracle@yang admin]$

[oracle@Hong ~]$ tnsping css

TNS Ping Utility for Linux: Version 10.2.0.1.0 – Production on 19-OCT-2011 00:14:10

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/product/10.2.0.1/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (description = (address =(protocol =tcp)(host=192.168.1.223)(port=1521)) (connect_data= (server=dedicated) (service_name=css)))
TNS-12537: TNS:connection closed
[oracle@Hong ~]$ sqlplus ‘sys/oracle@css as sysdba’

SQL*Plus: Release 10.2.0.1.0 – Production on Wed Oct 19 00:14:48 2011

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

ERROR:
ORA-12537: TNS:connection closed

Enter user-name:

来自192.168.1.99的访问被close

另外提供一种trigger禁止特性ip访问:

–>限制单用户从单IP登录,下面限制scott用户从客户端的登录

SQL> CREATE OR REPLACE TRIGGER disablelogin
2 AFTER logon ON liu.schema –>注意使用方式为username.schema
DECLARE
ipaddr VARCHAR2(30);
BEGIN
SELECT sys_context(‘userenv’, ‘ip_address’)
INTO ipaddr
FROM dual;
IF ipaddr = ‘192.168.1.103’ THEN
raise_application_error(‘-20001’,
‘You can not login,Please contact administrator’);
END IF;
END disablelogin;
/
Trigger created.

–>限制IP段登录
CREATE OR REPLACE TRIGGER refuse_range_ip
AFTER logon ON liu.schema
DECLARE
ipaddr VARCHAR2(30);
BEGIN
SELECT sys_context(‘userenv’, ‘ip_address’)
INTO ipaddr
FROM dual;
IF ipaddr LIKE (‘192.168.1.%’) THEN
raise_application_error(‘-20001’,
‘You can not login,Please contact administrator’);
END IF;
END refuse_range_ip;
/