如何诊断ORA-125XX连接问题

以下这个Action Script是我收集的在解决ORA-125XX(如ORA-12560)这类网络链接故障时的一些思路,主要包括 现有的网络配置(client & server side)、监听日志、SQLNET Client trace等信息– How to troubleshooting ORA-125** connection issues:

 

ORA-12560

 

# CHECK FIREWALL, WINDOWS FIREWALL , ANTI-Virus Software First !
ping hostname
tnsping TNS
trcroute TNS
telnet  <hostname> <port>  

tracert hostname

client side
sqlplus scott/tiger@TNS

&
server side
sqlplus scott/tiger@TNS

cat /etc/hosts
cat /etc/resolv.conf
cat /etc/nsswitch.conf
ipconfig -a
ping 127.0.0.1

$ORACLE_HOME/network/admin/sqlnet.ora
$ORACLE_HOME/network/admin/tnsnames.ora
$ORACLE_HOME/network/admin/listener.ora
$ORACLE_HOME/network/admin/endpoints_listener.ora
$ORACLE_HOME/network/log/*
sqlnet.log listener.log
/var/log/messages
/var/adm/messages
errpt -a

ls -ld $ORACLE_HOME
netstat -rn
ps -ef | grep -i tns
lsnrctl status {listener_name}
lsnrvtl services {listener_name}

ulimit -a

1. Complete database alert log.

2. If the database was not restarted from the time of last occurance of the
issue,

select * from v$resource_limit

3. RAM and SWAP configured on the server.

4. ulimit settings for oracle user:

ulimit -aS
ulimit -aH

5. Kernel parameter settings:

/etc/sysctl.conf

dblogin

show parameter cluster_database

show parameter listener

$srvctl config vip -n {nodename}

$lsnrctl status listener

agent.log and the crsd.log ..

crsd agent log and the crsd.log
$crsctl getperm resource ora.LISTENER.lsnr

sql net client trace , Client side tracing is done by adding the following syntax to the client’s sqlnet.ora file:

We will need a timestamped matching set of client/listener sqlnet traces while error is reproduced in order to find the root cause of the issue.

++ Enable client sqlnet tracing.
=======================

To do this add the following to client sqlnet.ora:

TRACE_LEVEL_CLIENT=16
TRACE_UNIQUE_CLIENT=TRUE
TRACE_DIRECTORY_CLIENT=path
TRACE_FILE_CLIENT=client
TRACE_TIMESTAMP_CLIENT=ON

replace path with a local directory for the trace files. (for example c:\temp)
Do a test connection from the problematic client and check if the trace files are created.
Upload the traces containing the error to me on metalink.

++ Enable listener sqlnet tracing.
==========================
To do this edit the listener.ora and add,

TRACE_LEVEL_{listener name}=16
TRACE_TIMESTAMP_{listener name}=TRUE
TRACE_DIRECTORY_{listener name}=/tmp {– this can be any directory other than a top level directory like / or c:\

Replace {listener name} with the name of the listener. For example if your listener was called LISTENER then TRACE_LEVEL_LISTENER=16

You need to restrict the amount of disk space used by the tracing then you must also set,

TRACE_FILELEN_{listener name}=500000 {– size of the files in K
TRACE_FILENO_{listener name}=10 {– number of files

This will limit the traces to 10 files of around 500Mb, so 5000Mb in total. When the 10th file is full it will reuse file number one.
You will need to stop/start the listener for this to take effect.
When the problem reproduces please can you upload the listener trace and the listener log.

Trace_level_client=16
Trace_directory_client={path_to_the_trace_directory} # use the full path to the trace directory
Trace_unique_client=on
Trace_timestamp_client=on
Diag_adr_enabled=off

trace Local listener or SCAN listeners
TRACE_LEVEL_{listener_name}= 16
TRACE_TIMESTAMP_{listener_name}=on
TRACE_DIRECTORY_{listener_name}={path_to_the_trace_directory}

truss -o /tmp/lisener.out -fae lsnrctl start {listener_name}

Some Useful Note:

Note.444705.1 TroubleShooting Guide For ORA-12514 TNS listener could not resolve SERVICE_NAME given in connect descriptor
Note.761740.1 Technicians Unable To Receive Orders While MWM Components Display ODBC Errors And Are Connected
Note.119007.1 ORA-12560: Administering the Listener on UNIX – Troubleshooting
Note 276812.1 TNS-12542 Error When Executing Batch Jobs or in High Transaction Environment
Note.219208.1 Ext/Pub Client Connection via Connect Manager Fails with TNS-12564
Note.393941.1 Ext/Mod ORA-12564 Reported When Using 10g Connection Manager
Note.1116960.1 ORA-609 TNS-12537 and TNS-12547 in 11g Alert.log
Note.550859.1 Abstract TROUBLESHOOTING GUIDE TNS-12518 TNS listener could not hand off client connection.
Note.207303.1 Client / Server / Interoperability Support Between Different Oracle Versions
Note.119706.1 Troubleshooting Guide TNS-12535 or ORA-12535 or ORA-12170 Errors

For database links between different Oracle versions connections must be supported in BOTH directions in the matrix found in Note 207303.1
eg: As 9.2 -} 7.3.4 is not supported then database links between these version
are not supported in either direction.
You are trying to connect two versions (client-server) that are not certified (as confirmed by Note 207303.1) and between which exist many technical incompatibilities.

CLIENT — LISTENER — SERVER RESULT

8 11.1 8 OK
9 11.1 9 OK
10 11.1 10 OK
11 11.1 11 OK

8 11.2 8 FAILS
9 11.2 9 OK
10 11.2 10 OK
11 11.2 11 OK

9 11.1 8 OK
10 11.1 8 OK
11 11.1 8 OK

9 11.2 8 FAILS
10 11.2 8 FAILS
11 11.2 8 FAILS

The relevant relationship that appears to be at issue is LISTENER and DATABASE. Client version is not a factor.

But if the ultimate outcome is that the 11.2 (11gR2) LISTENER is indicated (though I still haven’t seen documentation of this) as not compatible with use on a ORACLE 8i (8.1.7.0) DATABASE, then we’ll capture that here and move on. I would, however, like to see some evidence of this, if it is available. I can find notes in the KB about 10gR2’s listener not supporting 8i database, and I can find notes about 11gR1 having resolved that regression. But I can find nothing regarding listener/database compatibility that mentions 11gR2, that would explain our results.
Clients should be complied with Servers , For Sever 11.2 the only supported clients are 11.2.0 , 11.1.0 , 10.2.0 : 10g end MUST be at 10.2.0.2 (or higher) respectively in order to use PLSQL between those versions. See Note:4511371.8 for more details and finally 10.1.0.5 only with extended support .

On the other Side in order to connect from listener to DB server in a supported way , Listener version should be greater than or equal to the server version .

Note 207303.1 should still be followed.

Remote Listener Server side Connect-Time Load Balancing

这里不鏖述remote_listener参数的作用和服务器端连接时负载均衡的技术,只说明以下本次演示,想说明的是客户端连接原始LISTENER时只发生短暂的通信即断开,继而连接由初始连接节点分配的目标节点的LISTENER,且保持对监听端口之间的通信connection,这里在Unix/Linux上只用到监听端口,原理如下图:

 

 

我们通过client sql net trace来连接一些详细信息:

SQL> select instance_name,instance_number from gv$instance;

INSTANCE_NAME    INSTANCE_NUMBER
---------------- ---------------
VPROD2                         2
VPROD1                         1

SQL> show parameter remote_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_listener                      string      RAC_LISTENER

[grid@vrh1 admin]$ tnsping RAC_LISTENER

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 06-SEP-2012 08:15:42

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

Used parameter files:
/g01/11.2.0/grid/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vrh1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = vrh2-vip)(PORT = 1521)))
OK (10 msec)

从远程节点使用TNSNAMES连接1节点实例,
由于remote_listener 的server-side connect load balancing ,会分发到2节点上
通过设置SQLNET.ORA中的参数做client sql net trace

[oracle@nas sqlnet]$ tnsping SERVER

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 06-SEP-2012 13:16:48

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

Used parameter files:
/s01/orabase/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vrh1-vip)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = VPROD)))
OK (10 msec)

[oracle@nas sqlnet]$ sqlplus  system/oracle@SERVER

SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 6 13:17:25 2012

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

INSTANCE_NAME
----------------
VPROD2

 

 

以上利用remote_listener服务器端负载均衡成功由1及节点的listener路由到2节点上的LISTENER,并创建server process,我们来观察

SQL NET TRACE

 

 

(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 00 00 28 44 45 53 43 52  |..(DESCR|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 49 50 54 49 4F 4E 3D 28  |IPTION=(|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 43 4F 4E 4E 45 43 54 5F  |CONNECT_|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 44 41 54 41 3D 28 53 45  |DATA=(SE|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 52 56 49 43 45 5F 4E 41  |RVICE_NA|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 4D 45 3D 56 50 52 4F 44  |ME=VPROD|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 29 28 43 49 44 3D 28 50  |)(CID=(P|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 52 4F 47 52 41 4D 3D 73  |ROGRAM=s|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 71 6C 70 6C 75 73 29 28  |qlplus)(|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 48 4F 53 54 3D 6E 61 73  |HOST=nas|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 2E 6F 72 61 63 6C 65 2E  |.oracle.|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 63 6F 6D 29 28 55 53 45  |com)(USE|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 52 3D 6F 72 61 63 6C 65  |R=oracle|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 29 29 28 53 45 52 56 45  |))(SERVE|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 52 3D 64 65 64 69 63 61  |R=dedica|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 74 65 64 29 28 49 4E 53  |ted)(INS|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 54 41 4E 43 45 5F 4E 41  |TANCE_NA|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 4D 45 3D 56 50 52 4F 44  |ME=VPROD|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 32 29 29 28 41 44 44 52  |2))(ADDR|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 45 53 53 3D 28 50 52 4F  |ESS=(PRO|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 54 4F 43 4F 4C 3D 54 43  |TOCOL=TC|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 50 29 28 48 4F 53 54 3D  |P)(HOST=|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 31 39 32 2E 31 36 38 2E  |192.168.|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 31 2E 31 36 32 29 28 50  |1.162)(P|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 4F 52 54 3D 31 35 32 31  |ORT=1521|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 29 29 29                 |)))     |
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 259 bytes to transport
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: normal exit
(890354416) [000001 06-SEP-2012 13:29:11:567] nscon: exit (0)
(890354416) [000001 06-SEP-2012 13:29:11:567] snsbitts_ts: entry
(890354416) [000001 06-SEP-2012 13:29:11:567] snsbitts_ts: acquired the bit
(890354416) [000001 06-SEP-2012 13:29:11:567] snsbitts_ts: normal exit
................
(890354416) [000001 06-SEP-2012 13:29:11:560] snsbitcl_ts: normal exit
(890354416) [000001 06-SEP-2012 13:29:11:560] nsmfr: entry
(890354416) [000001 06-SEP-2012 13:29:11:560] nsmfr: 2760 bytes at 0x10a57910
(890354416) [000001 06-SEP-2012 13:29:11:560] nsmfr: normal exit
(890354416) [000001 06-SEP-2012 13:29:11:561] snsbitcl_ts: entry
(890354416) [000001 06-SEP-2012 13:29:11:561] snsbitcl_ts: normal exit
(890354416) [000001 06-SEP-2012 13:29:11:561] nsmfr: entry
(890354416) [000001 06-SEP-2012 13:29:11:561] nsmfr: 1528 bytes at 0x10a57310
(890354416) [000001 06-SEP-2012 13:29:11:561] nsmfr: normal exit
(890354416) [000001 06-SEP-2012 13:29:11:561] nsclose: normal exit
(890354416) [000001 06-SEP-2012 13:29:11:561] nscall: connecting...
(890354416) [000001 06-SEP-2012 13:29:11:561] nsc2addr: entry
(890354416) [000001 06-SEP-2012 13:29:11:561] nsc2addr: (ADDRESS=(PROTOCOL=TCP)(HOST=vrh2.oracle.com)(PORT=1521))
(890354416) [000001 06-SEP-2012 13:29:11:561] nttbnd2addr: entry
(890354416) [000001 06-SEP-2012 13:29:11:561] snlinGetAddrInfo: entry
(890354416) [000001 06-SEP-2012 13:29:11:561] snlinGetAddrInfo: getaddrinfo() failed with error -2
(890354416) [000001 06-SEP-2012 13:29:11:561] snlinGetAddrInfo: exit
(890354416) [000001 06-SEP-2012 13:29:11:561] nttbnd2addr: looking up IP addr for host: vrh2.oracle.com
(890354416) [000001 06-SEP-2012 13:29:11:561] snlinGetAddrInfo: entry
(890354416) [000001 06-SEP-2012 13:29:11:561] snlinGetAddrInfo: exit
(890354416) [000001 06-SEP-2012 13:29:11:561] snlinFreeAddrInfo: entry
(890354416) [000001 06-SEP-2012 13:29:11:561] snlinFreeAddrInfo: exit
(890354416) [000001 06-SEP-2012 13:29:11:561] nttbnd2addr: exit

 

 

通过netstat 可以查看当前client远程连接的端口,可以看到已经与1节点断开了connection:

 

 

[root@nas ~]# netstat -anp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   
tcp        0      0 127.0.0.1:2208              0.0.0.0:*                   LISTEN      2959/hpiod          
tcp        0      0 127.0.0.1:199               0.0.0.0:*                   LISTEN      5424/snmpd          
tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN      2526/portmap        
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      5396/sshd           
tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN      3000/cupsd          
tcp        0      0 0.0.0.0:632                 0.0.0.0:*                   LISTEN      2570/rpc.statd      
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      3046/sendmail: acce 
tcp        0      0 127.0.0.1:2207              0.0.0.0:*                   LISTEN      2964/python         
tcp        0    888 192.168.1.178:22            192.168.1.6:65179           ESTABLISHED 21508/0             
tcp        0      0 192.168.1.178:22            192.168.1.6:64535           ESTABLISHED 21187/1             
tcp        0      0 192.168.1.178:44496         192.168.1.163:1521          ESTABLISHED 21482/sqlplus

Connection pool,sqlnet,listener related issues/questions

Advise required on inbound paramters , prespawn servers and db resident connection

Question:

The application does not support persistent connection. currently we find time out errors. In order to fix the issue we seek your advise on the following:
currently all of the following are default values
SQLNET.INBOUND_CONNECT_TIMEOUT, — can we make it 10 seconds ? or what value you suggest

SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT

INBOUND_CONNECT_TIMEOUT_listenernam — planning to make 5 seconds; what are your suggestios.

2. ours is 11.2.0.1 ; whether prspawn server is still there in 11g R2. Will it be helpful for application supporting persistent connection or not

3. Regarding DRCP for those applications not supporting persistent connection , similarly do we have any answer for applications having persistent connections but at the same time avoiding logon storms.

 

Answer:

Well it sounds like you need to increase the processes parameters to allow the logon storms. Or setup a connection pool from the apache side to prevent the number of connections from hitting the server to increase.

Otherwise from the server side any values you set other then increasing the processes value to handle the load will report errors to the client trying to connect.
For example if you set the INBOUND_CONNECT_TIMEOUT then the client has to authenticate in that timeframe or it will error and report the 3136 error. But that defaults to 60 seconds to prevent any good connections from being booted just for being slow.

Now if you hit the processes value it will start reporting the 12518 and should trigger a connection error so apache should stop trying to force the connections at that point. If it continues then it will get errors and there is not much you can do. From the Oracle side you cannot really control how the client connects. If they cause a storm you either configure to handle the load or accept the fact the system will generate errors and not service the storm.
From the server side most of the parameters we have are to limit or control the amount of connections that are allowed to the server itself. If the client keeps trying to connect in a storm fashion there is not much you can do to prevent it on the DB you simply need to make it possible for those processes to connect.

the only real option from the server is to bump up the processes parameter to make sure you can handle the full load and have them setup a connection pool on the apache side to limit the total number of connections to the database from the web server to prevent them from hitting the processes value.

Connection pool , sqlnet, listener related issues/questions

Oracle网络TNS协议的几个基础类描述(revised)

首先是接口SQLnetDef,定义了参数

public interface SQLnetDef
{
public static final boolean DEBUG = false;
public static final boolean ASSERT = false;
public static final int NSPTCN = 1;
public static final int NSPTAC = 2;
public static final int NSPTAK = 3;
public static final int NSPTRF = 4;
public static final int NSPTRD = 5;
public static final int NSPTDA = 6;
public static final int NSPTNL = 7;
public static final int NSPTAB = 9;
public static final int NSPTRS = 11;
public static final int NSPTMK = 12;
public static final int NSPTAT = 13;
public static final int NSPTCNL = 14;
public static final int NSPTHI = 19;
public static final byte NSPHDLEN = 0;
public static final byte NSPHDPSM = 2;
public static final byte NSPHDTYP = 4;
public static final byte NSPHDFLGS = 5;
public static final byte NSPHDHSM = 6;
public static final byte NSPSIZHD = 8;
public static final byte NO_HEADER_FLAGS = 0;
public static final byte NSPCNVSN = 8;
public static final byte NSPCNLOV = 10;
public static final byte NSPCNOPT = 12;
public static final byte NSPCNSDU = 14;
public static final byte NSPCNTDU = 16;
public static final byte NSPCNNTC = 18;
public static final byte NSPCNTNA = 20;
public static final byte NSPCNONE = 22;
public static final byte NSPCNLEN = 24;
public static final byte NSPCNOFF = 26;
public static final byte NSPCNMXC = 28;
public static final byte NSPCNFL0 = 32;
public static final byte NSPCNFL1 = 33;
public static final byte NSPCNDAT = 34;
public static final int NSPMXCDATA = 230;
public static final int NSINAWANTED = 1;
public static final int NSINAINTCHG = 2;
public static final int NSINADISABLEFORCONNECTION = 4;
public static final int NSINANOSERVICES = 8;
public static final int NSINAREQUIRED = 16;
public static final int NSINAAUTHWANTED = 32;
public static final byte NSPACVSN = 8;
public static final byte NSPACOPT = 10;
public static final byte NSPACSDU = 12;
public static final byte NSPACTDU = 14;
public static final byte NSPACONE = 16;
public static final byte NSPACLEN = 18;
public static final byte NSPACOFF = 20;
public static final byte NSPACFL0 = 22;
public static final byte NSPACFL1 = 23;
public static final byte NSPRFURS = 8;
public static final byte NSPRFSRS = 9;
public static final byte NSPRFLEN = 10;
public static final byte NSPRFDAT = 12;
public static final byte NSPRDLEN = 8;
public static final byte NSPRDDAT = 10;
public static final int NSPDAFLG = 8;
public static final int NSPDADAT = 10;
public static final int NSPDAFZER = 0;
public static final int NSPDAFTKN = 1;
public static final int NSPDAFRCF = 2;
public static final int NSPDAFCFM = 4;
public static final int NSPDAFRSV = 8;
public static final int NSPDAFMOR = 32;
public static final int NSPDAFEOF = 64;
public static final int NSPDAFIMM = 128;
public static final int NSPDAFRTS = 256;
public static final int NSPDAFRNT = 512;
public static final int NSPMKTYP = 8;
public static final int NSPMKODT = 9;
public static final int NSPMKDAT = 10;
public static final int NSPMKTD0 = 0;
public static final int NSPMKTD1 = 1;
public static final byte NIQBMARK = 1;
public static final byte NIQRMARK = 2;
public static final byte NIQIMARK = 3;
public static final int NSPDFSDULN = 2048;
public static final int NSPMXSDULN = 32767;
public static final int NSPMNSDULN = 512;
public static final int NSPDFTDULN = 32767;
public static final int NSPMXTDULN = 32767;
public static final int NSPMNTDULN = 255;
public static final int NSPINSDULN = 255;
public static final String TCP_NODELAY_STR = "TCP.NODELAY";
public static final String TCP_CONNTIMEOUT_STR = "oracle.net.CONNECT_TIMEOUT";
public static final String TCP_READTIMEOUT_STR = "oracle.net.READ_TIMEOUT";
public static final int TCP_NODELAY_OFF = 0;
public static final int TCP_KEEPALIVE_OFF = 1;
public static final int TCP_CONNTIMEOUT_OFF = 2;
public static final int TCP_READTIMEOUT_OFF = 3;
public static final int ORACLE_NET_NTMINOPT = 0;
public static final int ORACLE_NET_READ_TIMEOUT = 1;
public static final int ORACLE_NET_NTMAXOPT = 10;
}

[Read more…]

共享池中的NETWORK BUFFER

中午休闲时在itpub看到一个关于network buffer占用大量内存的求助帖,帖子原文如下:

各位大侠们,请教个问题。昨天遇到一个solaris10平台下的oracle10g(10.2.0.4)数据库报共享内存不足,发现数据库的sga_target才2512M,而在v$sgastat视图中查到的
shared pool–>NETWORK BUFFER就有1848744416字节,是什么引起network buffer这么大呢,在udmp目录下1分钟产生几个跟 ORA-4031相关的文件。

==================
SQL> show parameter sga

NAME                                 TYPE        VALUE
———————————— ———– ——————————
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 2512M
sga_target                           big integer 2512M
SQL> show parameter share

NAME                                 TYPE        VALUE
———————————— ———– ——————————
hi_shared_memory_address             integer     0
max_shared_servers                   integer
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 72142028
shared_pool_size                     big integer 0
shared_server_sessions               integer
shared_servers                       inte


NETWORK BUFFER对我们来说或许有些陌生,那是因为绝大多数场合都采用dedicated server模式,共享服务器模式下NETWORK BUFFER将被大量使用。MOS文档[741523.1]叙述了NETWORK BUFFER的主要用途:

On 10.2, after upgrading from 9iR2, the following error occurs:

ORA-07445: exception encountered: core dump [] [] [] [] [] []

plus

Dispatcher Trace file contains an ORA-4031 Diagnostic trace, with:
Allocation request for: NETWORK BUFFER

…followed by…

found dead dispatcher ‘D000’, pid = (12, 1)

The amount of memory used by NETWORK BUFFERs in the shared pool has significantly grown between 9.2 and 10.2.  The side-effect is to run-out of Shared Pool memory (reporting an ORA-4031), when a large number of sessions are connecting to the server (in the order of 1000’s).

While a session is being established, we allocate 3 buffers each of 32k in size.  After the session is established, we use the 3 SDU-sized buffers, however we do not deallocate the 3x32k buffer we allocated initially.

This issue has been logged in unpublished Bug 5410481.

Additionally, there is  Bug 6907529.

NS buffers are allocated based on the SDU specified by the user. The negotiated SDU could be considerably lower. The difference between these two is wasted.

For example, the dispatcher specifies an SDU of 32k. Clients, by default, use an SDU of 8k. The remaining 24k is never used.

Issue in Bug 6907529 is fixed in 11.2.

Bug 5410481 is fixed in 10.2.0.3.

As a workaround to 5410481, the ADDRESS part of DISPATCHERS parameter can be used to specify a smaller SDU size.

For example:
DISPATCHERS=”(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp))(SDU=8192))”

To implement the change;

  1. connect to the database as SYSDBA
  2. alter system set dispatchers='(address=(protocol=tcp)(host=IP-Address)(sdu=8192))(dispatchers=DispatcherCount)’ scope=spfile;
  • re-start the database
  • 你可能会问SDU是什么?Oracle NET缓存的数据以SDU为基本单位,SDU即 session data unit,一般默认为8192 bytes。当这些数据单元被写满,或被client读取时,他们将被传递给Oracle Network层(oracle network layer)。譬如Data Guard环境中redo传输的每个Chunk往往要大于8192 bytes,那么默认的SDU就不太适用。当有大量重做数据要传输到standby库时,增大SDU buffer的大小可以改善Oracle的网络性能。你可以很方便的通过修改sqlnet.ora配置文件来修改SDU,如在该文件内加入以下条目:
    DEFAULT_SDU_SIZE=32767 /*修改全局默认SDU到32k*/
    当然你也可以在tnsnames.ora中定义服务别名时个别指定SDU,下文我们会用到。
    如上文所述在版本10.2.0.3以前当会话建立时,Oracle会以dispatchers参数定义的SDU为单位,分配3个单位的NETWORK  BUFFER,而实际上client端可能并未指定和dispatchers一致的SDU,若dispatchers中定义的SDU为32k,而client使用默认的8k SDU,则一个会话可能要浪费3*32-3*8=72k的NETWORK BUFFER。

    为什么共享服务器模式下会用到共享池中的NETWORK BUFFER,而独享服务器模式下没有呢?因为在独享服务器模式下每个会话所分配的三个SDU是从PGA中获取的;当使用共享服务器模式时会话与服务进程形成一对多的映射关系,这三个SDU 的NETWORK BUFFER同UGA一样转移到了SGA中。

    下面我们通过实践来进一步验证。

    SQL> select * from v$version;
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for Linux: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production
    /*实验服务器端采用10.2.0.4版本*/
    SQL> show parameter dispatch
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    dispatchers                          string      (address=(protocol=tcp)(host=1
    92.168.1.103)(sdu=32768))(SERV
    ICE=cXDB)(dispatchers=10)
    /*dispatchers中指定了SDU为32k*/
    C:\Windows\System32>tnsping cXDB
    TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-8月 -2010 22:51:27
    Copyright (c) 1997, 2010, Oracle.  All rights reserved.
    已使用的参数文件:
    D:\tools\adminstrator\11g\orahome\network\admin\sqlnet.ora
    已使用 TNSNAMES 适配器来解析别名
    尝试连接 (DESCRIPTION = (SDU=8192) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = cXDB)))
    OK (30 毫秒)
    /* client端采用11.2.0.1版本,定义了共享服务器模式的服务别名,显式指定SDU为8192字节*/
    

    这里我们要用到一个简单的java程序,用来模拟大量会话登录;这个程序很傻瓜,但是总比你一个个开SQLPLUS要明智的多:

    /*这是一个很简单的java程序,登录远程数据库,并尝试打开600个回话,并且都指定了SDU为8192*/
    package javaapplication2;
    import oracle.jdbc.*;
    import java.sql.*;
    public class Main
    {
    public static void main(String[] args) throws SQLException
    {
    try
    {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    }
    catch(Exception e )
    {
    }
    Connection cnn1=DriverManager.getConnection("jdbc:oracle:thin:@(DESCRIPTION = (SDU=8192) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = cXDB)))", "system", "password");
    Statement stat1=cnn1.createStatement();
    ResultSet rst1=stat1.executeQuery("select * from v$version");
    while(rst1.next())
    {
    System.out.println(rst1.getString(1));
    }
    Connection m[]=new Connection[2000];
    Statement s[]=new Statement[2000];
    ResultSet r[]=new ResultSet[2000];
    int i=0;
    while(i<600)
    {
    try
    {
    m[i]=DriverManager.getConnection("jdbc:oracle:thin:@(DESCRIPTION = (SDU=8192) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = cXDB)))", "system", "password");
    }
    catch (Exception em)
    {
    System.out.println(em.getMessage());
    }
    try
    {
    Thread.sleep(3);
    }
    catch (Exception e)
    {
    }
    s[i]=m[i].createStatement();
    m[i].setAutoCommit(false);
    i++;
    System.out.println(i+"is ok !");
    }
    System.out.println("We are waiting!");
    try
    {
    Thread.sleep(1000);
    }
    catch (Exception e)
    {
    }
    }
    }
    

    编译上面这段程序,尝试执行看看,执行的同时留意观察NETWORK BUFFER:

    SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';
    NAME                       POOL              BYTES
    -------------------------- ------------ ----------
    NETWORK BUFFER             shared pool      328080
    java -jar ora_network_buffer_test_8.jar
    /*启动编译后的测试程序*/
    SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';
    NAME                       POOL              BYTES
    -------------------------- ------------ ----------
    NETWORK BUFFER             shared pool    69608200
    SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';
    NAME                       POOL              BYTES
    -------------------------- ------------ ----------
    NETWORK BUFFER             shared pool      348960
    /*会话终止后,NETWORK BUFFER回缩*/
    修改上述程序中的SDU到32k,重新编译后再次测试
    java -jar ora_network_buffer_test_32.jar
    SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';
    NAME                       POOL              BYTES
    -------------------------- ------------ ----------
    NETWORK BUFFER             shared pool      328080
    SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';
    NAME                       POOL              BYTES
    -------------------------- ------------ ----------
    NETWORK BUFFER             shared pool    99148576
    /*可以看到同样的会话数量,client端SDU增大到32k后,NETWORK BUFFER有了大幅增长*/
    我们修改dispatchers参数中的SDU到8k看看
    SQL> alter system set dispatchers='';
    System altered.
    SQL> alter system set dispatchers='(address=(protocol=tcp)(host=192.168.1.103)(sdu=8192))(SERVICE=cXDB)(dispatchers=10)';
    System altered.
    SQL> show parameter dispatchers
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    dispatchers                          string      (address=(protocol=tcp)(host=1
    92.168.1.103)(sdu=8192))(SERVI
    CE=cXDB)(dispatchers=10)
    SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';
    NAME                       POOL              BYTES
    -------------------------- ------------ ----------
    NETWORK BUFFER             shared pool      328080
    java -jar ora_network_buffer_test_32.jar
    SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';
    NAME                       POOL              BYTES
    -------------------------- ------------ ----------
    NETWORK BUFFER             shared pool    99148552
    /*看起来dispatcher中的SDU优先级并没有client中的高*/
    我们再来看看client中SDU为8k的情况
    SQL> show parameter dispatchers
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    dispatchers                          string      (address=(protocol=tcp)(host=1
    92.168.1.103)(sdu=8192))(SERVI
    CE=cXDB)(dispatchers=10)
    SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';
    NAME                       POOL              BYTES
    -------------------------- ------------ ----------
    NETWORK BUFFER             shared pool      328080
    java -jar ora_network_buffer_test_8.jar
    SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';
    NAME                       POOL              BYTES
    -------------------------- ------------ ----------
    NETWORK BUFFER             shared pool    69608200
    /*与dispatchers中为32k,而client为8k时一样*/
    

    由以上实践可知10.2.0.4之后,NETWORK BUFFER的使用量由客户端设定的SDU和共享服务器会话数决定。我在之前的博文中曾经列出过TNS协议的几个基础类描述(见《Oracle 网络TNS协议的几个基础类描述》),其中Session包含了setSDU(int i)方法,其代码如下:

    public void setSDU(int i)
    {
    if(i <= 0) sdu = 2048;
    else if(i > 32767)
    sdu = 32767;
    else if(i < 512)
    sdu = 512;
    else
    sdu = i;
    }
    

    由以上代码可知,客户端设定的SDU时,其最大最小值分别为32k和512bytes,大于32k时被强制设为32k,而小于512bytes时被强制设为512bytes,若设定SDU<0,则被强制修正为2048 bytes,在512 bytes- 32767 bytes之间则为原值不变。

    Know about Oracle Network Security

    Good network security is accomplished by utilizing port and protocol screening with routers, firewalls,
    and Intrusion Detection Systems.Port and protocol screening with routers, firewalls,
    and Intrusion Detection Systems create a bastion against network attacks.
    A device that routes and translates information between interconnected networks is called a firewall.
    Firewalls have a different function
    Routers, not firewalls, use destination address and origin address to select the best path to route traffic.
    When installing a firewall, the first action is to stop all communication.
    After installation, the System Administrator adds rules that allow specific types of traffic to pass through the new firewall.
    After installation of a firewall, the System Administrator adds rules
    that allow specific types of traffic to pass through the new firewall
    A switch is a data link layer device that forwards traffic based on MAC addresses.
    Switching is performed in hardware instead of software, so it is significantly faster.
    Network Security Wizards Dragon 4.0 is an example of vendors that offer  Intrusion Detection Systems or IDS
    1.
    Authentication is the process of verifying the identity of a user, device, or other entity.
    Once the identity is verified, a trust relationship is established and further network interaction is possible.
    2.
    Authorization is the process of assigning various levels of access and capabilities for the authenticated user.
    In other words, authorization allows assigned levels of access in the database environment.
    3.
    Oracle 8i supports 3 models for storing Authorizations in a centralized directory service. Public Key Infrastructure,
    Microsoft Active Directory, or Distributed Computing Environment. PKI together with Oracle Internet Directory is the optimal method.
    4.
    Most issues of data security can be handled by Oracle8i authentication mechanisms.
    5.
    The init.ora file, or instance configuration file, is one of the key configuration files
    in an Oracle database environment that must be protected.
    This file contains all the initialization parameters: the configurable parameters that are applied when an instance is started up.
    6.
    A file transfer copy of the tnsnames.ora configuration file is a common way for hackers to discover whether the
    AUDIT function is enabled. If they determine that AUDIT is enabled, they can take steps to cover their activities,
    or even delete the audit trail.
    7.
    To protect the key configuration files at the operating system level,
    the system administrator should ensure that UNIX file permissions and
    the umask environment variable are set for the optimal combination of file restrictions in that environment.
    The default value of umask is 022, but the UNIX system administrator responsible for that environment may
    decide that a more restrictive value is appropriate.
    8.
    In Sun Solaris UNIX environments, a low level of security can be achieved using access control
    utilities such as GETFACL and SETFACL. These access control list utilities are specific to the Sun Solaris UNIX platform
    9.
    Controlling access by using database object privileges is called DAC, or discretionary access control.
    DAC controls access to any given object by granting specific privileges to user objects or roles.
    10.
    Giving a database user object the authority to perform INSERT or DELETE commands in a given table is an example of a privilege.
    This privilege applies to a given user object, unlike a role which applies to a group of user objects.
    11.
    Virtual Private Database technology allows security access controls to be applied directly to views or tables.
    Unlike other access control methods, defined access controls apply directly to the table or view, not the user object.
    12.
    Oracle Label Security provides fine-grained access control within the database by using access control tables and a security policy.
    Label Security augments Virtual Private Databases to provide a tighter security for data.
    13.
    The transformation of data by using cryptography to make it unintelligible is known as encryption.
    To encrypt a file is to render that file completely unreadable until it has been properly decrypted.
    14.
    DES and RC4 are examples of symmetric key encryption. 3DES, DES40 and RC2 are additional symmetrical encryption algorithms.
    15.
    Cryptography that requires key agreement, or keys on both sides of the session, is known as Diffie-Hellman cryptography.
    This allows mutual authentication with the same common key. Advanced Security Option uses Diffie-Hellman cryptography.
    16.
    Cryptography that provides for private communications within a public network without trusting anyone to keep secrets is
    called public key infrastructure, or PKI. HTTP and LDAP protocols are included within the public key infrastructure.
    17.
    The most widely used PKI application that supplies data integrity and encryption in the transport layer of the
    Open Systems Interconnection (OSI) model is the secure sockets layer, or SSL, protocol.
    SSL is typically used for authenticating servers and for the traffic encryption of credit cards and passwords.
    18.
    A data dictionary table called sys.aud$ is the database audit trail.
    The database audit trail stores records which audit database statements, schema objects, and privileges.
    19.
    An entry in the operating system audit trail is always created when instance startup or instance shutdown occurs,
    or when the sys user object logs in. The instance startup entry is necessary in order to
    maintain a complete audit trail when the data dictionary is not available.
    20.
    The type of audit trail that efficiently consolidates audit records from multiple sources
    (including Oracle databases and other applications which use the audit trail) is the operating system audit trail.
    Operating system audit trails allow all audit records to reside in one place, including database audit trails.
    21.
    You can use Oracle Reports to create customized reports of audit information when the database audit trail is in use.
    You can analyze database audit trail information and produce good reports from that analysis,
    which is an advantage over using the operating system audit trail method.
    22.
    To protect the database audit trail from unauthorized deletions,
    grant the Delete Any Table system privilege to security administrators only.
    An unauthorized user with this system privilege can severely damage a database security trail, or even delete all the data.
    Assign this privilege very carefully.
    23.
    Advanced Security Option provides a single source of integration with network encryption, single-sign-on services,
    and security protocols. ASO is the centralized source for all of these security features.
    24.
    ASO ensures that data is not disclosed or stolen during Net8 transmissions by means of RSA encryption,
    DES encryption, and Triple-DES encryption.
    25.
    The SSL feature of ASO allows you to use the SHA, or secure hash algorithm.
    The SHA is slightly slower than MD5, but it is more secure against brute-force collision and inversion attacks.
    26.
    he SSO, or single sign-on, feature of ASO allows access to multiple accounts and applications with a single password.
    SSO simplifies the management of user accounts and passwords for system administrators.
    27.
    LDAP stands for Lightweight Directory Access Protocol, which is a directory service standard based on the ISO X.500 specification.
    LDAP is a protocol defined and maintained by the same task force which defined the HTTP and TCP/IP protocols.
    28.
    OID means Oracle Internet Directory, which is the LDAP directory available from Oracle.
    OID is a directory service compliant with LDAP v. 3, and it offers scalability, security, and high availability.
    29.
    The scalability of OID allows thousands of LDAP clients to be connected together without harming performance.
    Much of this scalability is accomplished using connection pooling and multithreaded server implementations.
    30.
    The Java-based tool for administering OID is called Directory Manager.
    The Directory Manager tool provides administrative transparency for the Oracle environment,
    and is based on Oracle Enterprise Manager.
    32.
    OID security controls data access at the authentication level, by using access control lists.
    Data access is controlled with anonymous authentication methods, either password-based or certificate-based (through SSL).
    33.
    An enterprise user is defined and managed in a directory. All enterprise users have a unique identity which spans the enterprise.
    34.
    Enterprise User Security Management allows large user communities to access multiple applications with a single sign-on.
    User credentials and authorizations are stored in a directory.
    This allows single sign-ons using x.509v3 certificates over SSL.
    35.
    Groups of global roles are called enterprise roles, which are assigned to enterprise users in order to avoid
    granting roles to hundreds or thousands of individual users.
    36.
    You can remove the need to create duplicate user objects in every database by using the shared schemas feature.
    The benefit of shared schemas is fewer user accounts.
    37.
    The current user database link feature allows user objects to connect to another database instance as the procedure owner.
    A current user database link requires global users and SSL.
    38.
    The Login server provides a single, enterprisewide authentication mechanism. This authentication mechanism allows users to
    identify themselves securely to multiple applications through a single authentication step, or single sign-on (SSO).
    39.
    The single sign-on feature allows the storage of passwords in LDAP-compliant directory services such as Oracle Internet Directory.
    Storing usernames and passwords in a directory improves efficiency by centralizing this administrative duty.
    40.
    A partner application can accept authentication directly from the Login server.
    Partner applications are modified to work within the SSO framework.
    41.
    External applications are not modified to work within the SSO framework.
    The Login server does not store the username and password, but only supplies this native information from the external application.
    The benefits of LDAP directories are not available to external applications.
    42.
    During Oracle product installations, user objects are created with default passwords. SYS, SYSTEM,
    and ORACLE are the most critical to examine, but all objects that may have default passwords should be examined.
    43.
    V_$PWFILE_USERS is the view that shows which user objects have been granted SYSDBA or SYSOPER privileges.
    It is normal for INTERNAL and SYS objects to have the privileges, but suspect any other user objects that have these privileges.
    When in doubt, revoke the privilege and monitor the change.
    44.
    Users with unlimited tablespace can accidentally or intentionally use 100 percent of available tablespace.
    Review this ability by examining the DBA_TS_QUOTES view. User objects have unlimited tablespace
    if that object displays MAX_BLOCKS or MAX_BYTES columns equal to -1.
    Any user object that has this privilege should be examined closely for verification of need.
    45.
    Invoke SQL*Plus with the NOLOG switch to remove the plain-text password entry from the UNIX process table.
    Sessions started with this /nolog SQL*Plus switch cannot reveal the password
    when another session uses the Ps -ef|grep SQL*Plus command.
    46.
    The data dictionary view, DBA_ROLES, will reveal the names of all roles and their current password status.
    It is a good view for reviewing any potential security risks related to roles and their respective passwords.
    Review this view regularly to verify that these roles are not being misused,
    and that a secure password policy is in place for all roles.
    47.
    Virtual Private Databases is a good security product but requires programming to implement.
    Oracle Label Security provides similar row-level security out-of-the-box without this same need.
    Oracle Label Security provides row-level security in databases without the need for programming that VPD requires.
    48.
    The Oracle Label Security administrative tool that allows you to quickly implement a security policy on a table is named Policy Manager.
    Oracle Policy Manager allows administrators to use predefined security policies to quickly implement row-level security on any table.
    49.
    Oracle Label Security controls access to rows in database tables based on a label contained
    in the row and the label privileges given to each user session. Beyond Directory Access Controls restrictions,
    row-level security provides a finer level of security by using these two labels to implement further restrictions
    and provide ease of administration.
    50.
    The user label specifies the data that a user or stored program unit has access to.
    This is one element of security using Oracle Label Security.
    51.
    The row label specifies the sensitivity of the data placed under control. The row label has a different function than the user label.
    The row label provides security on the data, not the user session or stored program unit.
    52.
    Oracle AUDIT performs the monitoring and recording of selected user database actions.
    Oracle AUDIT is used to watch over user actions in a database instance.
    53.
    The AUDIT_TRAIL init.ora parameter is used to stop, start, and configure the AUDIT function for any given instance.
    NONE is the default value of this parameter; the OS value of this parameter
    enables all audit records to go to the operating system's audit trail,
    and the dB value of this parameter enables database auditing.
    54.
    Minimize auditing. If only user login monitoring is required, listener log monitoring is an alternative to using AUDIT.
    All sessions route through the listener, and an entry is made in the listener log for each session.
    55.
    To maintain optimal performance, you should periodically issue the SQL command truncate on the audit table. Old,
    unnecessary data should be purged regularly. The length of time between truncate command invocations
    that will maintain the optimal audit table size will vary by the volume of audit information retained.
    56.
    The most critical role to control is the DELETE_ANY_CATALOG role. Only DBAs should have this role.
    This is key to protecting the audit trail. Restricting this role will ensure that the audit trail is protected from deletion.
    Hackers will often remove or edit the audit trail to cover their activities.
    57.
    Advanced Security Option (ASO) encrypts all protocols in the database. Net8 connections to the database are encrypted,
    as are all connections to the database.
    58.
    Data integrity is provided by the checksumming algorithm. The checksumming technique detects replay attacks,
    where a valid $100.00 withdrawal is resubmitted 100 unauthorized times.
    59.
    DES is an example of native ASO cryptography. An example of an SSL cryptography that expands on DES is the 3 DES cryptography.
    Triple Data Encryption Standard (DES) makes three passes during the cryptography process, providing a higher level of security.
    60.
    A system that uses polices and procedures to establish a secure information exchange is
    called the public key infrastructure, or PKI.
    Several elements of PKI include SSL, x.509v3 certificates, and the Certificate Authority.
    61.
    Benefits of using the public key infrastructure include the ability to scale to the Internet and accommodate millions of users.
    Efficiency is paramount when millions of users are part of the community.

    Practice:Demonstrating Oracle AUDIT Concepts and Procedures

    This practice uses common UNIX and NT Oracle utilities to practice enabling AUDIT on a database.  You will:

    • See the procedure to enable and disable Oracle AUDIT on a database.
    • Understand the SQL commands used to audit a specific user schema object.
    • Investigate how to configure Audit to extend auditing into modified or new schema objects..

    ASSUMPTIONS

    • This practice will reference SQL commands that function equally on UNIX operating systems, and the NT operating system, using SQL*Plus.
    • Results may vary slightly according to your Oracle environment.
    • Utilize the RealPlayer Demonstration in conjunction with this Practice, to further illustrate and guide this activity.
    • Login to your sqlplus session using the SYSTEM user object and the current password.

    INSTRUCTIONS

    1.

    UNIX: Open a shell, login, locate and edit the initSID.ora file for your database:

    NT: Use Window’ File Manager to locate the initSID.ora file for your database.

    #audit_trail = true        # save,  original line

    audit_trail = true         #activated for demonstration, rjm

     

    Locate the line above, make a full copy of the line in the next newline, then uncomment (remove the # symbol) from the line.  Edit comments to reflect your reasons for the change.  Save the modified file, then shutdown/startup the instance.  Audit is now active on your database instance.

    2.

    UNIX: Open a shell, login, create a SQLPlus session with the SYSTEM connection.

    NT: Create an SQLPlus session with SYSTEM connection.

    sqlplus /nolog

     

    SQL> audit select any table by scott;

     

    Audit succeeded.

     

    SQL> noaudit select any table by scott;

     

    Noaudit succeeded.

     

    SQL> audit all by scott;

     

    Audit succeeded.

     

    SQL> noaudit all by scott;

     

    Noaudit succeeded.

     

    SQL>

    Now, all SELECT activity by the user Scott will be recorded in the audit trail, for our review later.

    The NOAUDIT command following disables this selective monitoring once we have accumulated sufficient data to analyze.  The next commands will begin monitoring on ALL database activity for the user Scott, and then disables that same type of monitoring.

    3.

    UNIX: Open a shell, login, create a SQLPlus session with SYSTEM connection:

    NT: Create a SQLPlus session with SYSTEM connection:

    sqlplus /nolog

     

    SQL> audit insert on default;

     

    Audit succeeded.

     

    SQL> audit delete on default;

     

    Audit succeeded.

     

    SQL> audit update on default;

     

    Audit succeeded.

     

    SQL> noaudit insert on default;

     

    Noaudit succeeded.

     

    SQL> noaudit delete on default;

     

    Noaudit succeeded.

     

    SQL> noaudit update on default;

     

    Noaudit succeeded.

     

    SQL>

    These commands will extend INSERT, DELETE, UPDATE auditing to include future new or modified schema objects.

    The second set of NOAUDIT commands disable those same audit actions.

    Practice:Demonstrating Database User Objects, Roles and Permissions

    This practice uses common UNIX and Windows NT Oracle utilities to review database user objects’ key roles and  permissions.  You will:

    1.        See default user objects and their default passwords.

    2.        Understand V$PWFILE_USER data dictionary view, and how to use this view to inspect SYSDBA or SYSOPER privileges assigned to user objects.

    3.        Examine two key data dictionary views that reveal information about an important privilege for Oracle Audit.

    4.        Investigate how to find user objects that are assigned unlimited tablespace in your active Oracle instances.

    5.        Review the secure method for invoking SQLPlus, utilizing the /nolog command line switch.

    ASSUMPTIONS

    1.        This practice references SQL commands that function equivalently on the UNIX and NT operating systems.

    2.        Results may vary slightly according to your Oracle environment.

    3.        To further illustrate and guide this activity, view  this topic’s  Demo in conjunction with this Practice.

    4.        Login to your sqlplus session using the SYSTEM user object and the current password.

    INSTRUCTIONS

    1.

    UNIX: Open a shell, login, and type the following command:

    NT: Open a DOS prompt, or invoke from the START/RUN pull down box.

     sqlplus system/<password>

                           

    SQL> connect system/manager

    Connected.

    SQL> connect sys/change_on_install

    Connected.

    SQL> connect scott/tiger

    Connected.

    SQL>

    Each connect command shows if the default password is in use with that particular default database user object.   Those objects using the default passwords should have their passwords changed.

    2.

    UNIX:  Open a shell, login, and type the following command

    NT: Open a DOS prompt, or invoke from the START/RUN pull down box.

    sqlplus system/<password> 

    SQL> select * from v$pwfile_users;

     

    USERNAME                       SYSDB SYSOP

    ------------------------------       -----       -----

    INTERNAL                       TRUE      TRUE

    SYS                            TRUE      TRUE

    ROD                            TRUE      FALSE

     

    SQL>

    It is normal for INTERNAL or SYS to have the SYSOPER and/or SYSDBA privilege.   Any other user objects are suspect and should be scrutinized.

    3.

    UNIX: Open a shell, login, and type the following command:

    NT: Open a DOS prompt, or invoke from the START/RUN pull down box.

     

    sqlplus system/<current password> 

    SQL> select role,privilege from role_sys_privs

           2  where role='DELETE_ANY_CATALOG';

    no rows selected

     

    SQL> select role,privilege from role_sys_privs

           2  where role='DELETE_ANY_CATALOG';

    no rows selected

     

    Any user objects that have the DELETE_ANY_CATALOG privilege should be scrutinized closely.  Verify the need for this object to have this privilege.  If in doubt, revoke the privilege.

    4.

    UNIX: Open a shell, login, and type this command

    NT: Open a DOS prompt, or invoke from the START/RUN pull down box.

    sqlplus system/<current password> 

    SQL> select username,max_bytes,max_blocks from dba_ts_quotas;

     

    USERNAME                        MAX_BYTES MAX_BLOCKS

    ------------------------------ ---------- ----------

    RODOEMREPOS                            -1         -1

    RODOEMREPOS                            -1         -1

     

    SQL>

     

    The numeric “-1” is the indication of unlimited tablespace assignments, whether in a MAX_BYTES or MAY_BLOCKS column.   Examine those usernames that display the “-1” value.   A username with unlimited tablespace privilege could accidentally or intentionally use 100% of that tablespace.

    5.

    UNIX: open a shell, login, and type the following command:

    NT: no practice for this concept on NT.

    sqlplus system/<current password> 

    SQL> host ps -ef|grep sqlplus

     

    The results of this UNIX command within sqlplus will display the sqlplus session with the SYSTEM user object’s password in plain text.  This is a security breach, and can be avoided with utilizing sqlplus in the NOLOG mode displayed next.

    6.

    UNIX: Open a shell, login, and type the following command:

    NT: No practice for this concept on NT.

    sqlplus system /nolog 

    SQL> connect system/manager

    Connected.

     

    SQL> host ps -ef|grep sqlplus

     

    The results of this UNIX command within sqlplus will display the sqlplus session with the SYSTEM user object’s password hidden.  The session now displays only the “/nolog” in the UNIX process table.   This keeps unauthorized users from gaining passwords by watching active sessions using the UNIX command “ps”.

    Practice:Demonstrating the Key TCP/IP Protocols

    This practice uses common UNIX and Windows NT utilities to visualize key TCP/IP protocols.  You will:

    • See IP information using the IPCONFIG utility.
    • Understand and overcome IP fault situations using the PING utility.
    • Examine the FTP (File Transfer Protocol) application, and overcome fault situations commonly seen when using FTP.
    • Create a TELNET session with a NT or UNIX server in your network, using the command line, and overcome fault situations commonly seen when invoking TELNET sessions.
    • Create an HTTP session with a browser on your computer, and overcome fault situations commonly found when starting an HTTP session.

     

    ASSUMPTIONS

    • This practice will reference commands that function equivalently on UNIX operating systems and the NT operating system.
    • Results may vary according to your IP assignments, etc.
    • Utilize this topic’s Demo in conjunction with this Practice to further illustrate and guide this activity.

    INSTRUCTIONS

    1.

    UNIX: Open a shell, login, and type the following command

    NT: Open a DOS prompt, or invoke from the START/RUN pull down box.

    ipconfig /all

    This will return some IP information about your current server on both UNIX and NT.

    2.

    UNIX: Open a shell, type the following command.

    NT: Open a DOS Prompt, type the following command

    ping <SERVER NAME>

    PING utility will return a positive response when the server indicated has responded.

    3.  File Transfer Protocol, FTP, is an utility used to transfer files between servers.  Invoke FTP using the command lines below, on both UNIX and NT servers.

    C:>ftp hostname

    Connected to hostname220 hostname FTP server (SunOS 5.6) ready.

    User (hostname:(none)): root

    331 Password required for root.

    Password:

    230 User root logged in.

    ftp> bin

    200 Type set to I.

    ftp> get

    (remote-file)

    get remote-file [ local-file ]

    ftp> bye

    221 Goodbye.

     

    C:>

    BIN  command once within FTP puts the file transfer into binary mode. GET  command once within FTP will identify the file to transfer.

    BYE  command once within FTP will close the session

    4.

    TELNET  is an utility used to gain access to servers.  Invoke TELNET using the command lines below, on both UNIX and NT servers.

    Telnet <server name>

    Login with a valid user id and password when prompted.

    This utility gives access to the designated server for administrative purposes, in a command line environment.

    5.

    HTTP is a protocol that is used commonly within browsers.  Invoke HTTP using the syntax below, in the browser of your choice, on both UNIX and NT servers.

    http://www.sun.com

    This protocol gives access to the designated site.  This syntax is seen within the browser’s “Location” box.

    沪ICP备14014813号

    沪公网安备 31010802001379号