WARNING: inbound connection timed out (ORA-3136) 조치방법

#####################################################################################
##### 11gR2 (11.2.0.2 PatchSet적용)
##### TEST 환경 : 2Node RAC
#####################################################################################
11gR2 또한 inbound_connect_timeout 설정값 기본이 60초 입니다.

아래 처럼 클라이언트에서 DB 접속을 시도 중... 60초 동안 아무 입력 없이 대기를 한 상태에서
60초 후 alert log 에 WARNING: inbound connection timed out (ORA-3136) 이 출력 됩니다.

가이드를 보면 두가지를 제시 하는데..

1. 리스너 파일에 아래 처럼 수정
INBOUND_CONNECT_TIMEOUT_ = 1200
or
INBOUND_CONNECT_TIMEOUT_ = 0

2. sqlnet.ora 설정파일에 아래 처럼 추가
SQLNET.INBOUND_CONNECT_TIMEOUT=1200
or
SQLNET.INBOUND_CONNECT_TIMEOUT=0

그러나 실질적으로 뭔가 부족 했었습니다. (명확하게 뭐가 문제 인지를 통 모르겠다 이거죠..)


C:\>sqlplus /@TEST1

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Nov 30 14:54:45 2010

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

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:

--- 60초(1분) 동안 대기

########## alert.log ######################################################
-- 60초 후 에러 메세지 출력.
Tue Nov 30 14:55:43 2010
WARNING: inbound connection timed out (ORA-3136)

############################################################################


### 조치 사항

SQLNET.INBOUND_CONNECT_TIMEOUT=0

### 조치 사항에 대한 내용

위처럼 sqlplus 상으로 접소시 60초 (default 값임) 를 넘겨도 위의 ORA-3136 에러가
출력 되지 않고 프로세스도 줄어 들지 않는다.


해당 오라클 프로세스를 확인 해 보면 ps -ef | grep LOCAL=NO | wc -l
위의 방식대로 접속을 실패하더라도 프로세가 하나 늘어 나는것을 확인 할 수 있었고,
60초 후 (default) 설정시 alert.log 에 ORA-3136 메세지를 찍어 주었음. 또 한
ps -ef | grep LOCAL=NO | wc -l 확인 결과 프로세스도 줄어드는것을 확인 할 수 있었습니다.

v$process 나 v$resource_limit를 보면 프로세스가 하나 늘어나 있는 것을 볼 수 있습니다.



/oracle > telnet db-scan 1521
Trying...
Connected to db-scan.
Escape character is '^]'.

--- 60 초 후

Connection closed.
메세지 뿌려 주면서 나가게 됩니다.

그러나 alert log 에서는 메세지를 뿌려 주지 않는것을 확인 했고,
제가 테스트 한 결과 ps -ef | grep LOCAL=NO | wc -l 연결전이나 연결 시도 후 나 프로세스는 변화가 없었습니다.
v$process 나 v$resource_limit를 보면 프로세스 또한 변화 없었습니다.

######################################
### 개인 의견
######################################
위의 테스트 결과 ORA-3136 에러를 뿌려주는것은 현재 프로그램보다는 sqlplus 때문에 발생 된 것이라 할 수 있을까 하는 조심스러운
결론을 내려 봅니다.
현재 운영 상태가 아니므로 저 에러는 무시 하는것으로 처리 하겠습니다.


원문 참조 : http://cafe.naver.com/prodba/11505
WARNING: inbound connection timed out (ORA-3136) 에 관해

[AIX] Logical Volume Manager (LVM) Commands for AIX

 Logical Volume Manager (LVM) Commands for AIX

Glossary

Term	Definition
Journaled File System (JFS)	File system that uses a journaled log for faster, more reliable data recovery
Logical Partition (LP)	The LV is made up of LPs.  The LP corresponds to 1 or more (in the case of mirroring) PPs.
Logical Volume (LV)	The VG is subdivided into logical volumes and each LV can have  a file system on it.
Physical Partition (PP)	All physical volumes are subdivided into pps.  PPs are all the same size.
Physical Volume (PV)	Disk that is being managed by LVM.
Rootvg	Default volume group created during installation.  The vg holds the OS filesystems ( /,/usr, /home, /proc /opt,  /tmp,  /var and swap space )
Volume Group (VG)	Area of storage that consists of one or more PVs

Command Summary

Command	Definition
chfs -a size=<#512 byte blocks>	Increases the size of a journaled file system to the total number of  512 byte blocks specified
chfs -a size=<+512 byte blocks>	Increases the size of a journaled file system by the addional number of 512 byte blocks specified.  For example "chfs -a size=+393216 /usr"
chlv -n	Change the name of a logical volume (it must be inactive)
crfs -v jfs -m -g -a size=<# of 512 byte blocks> crfs -v jfs -m -d	This command makes a logical volume, mount point with a journaled file system: creates a jfs file system on a logical volume
df -k	Shows the disk usage of logical volumes on the server.
exportvg	removes a volume group from a machine
extendvg	Adds a new physical volume to an existing volume group
importvg -y	add a volume group to another machine
lslv [-l, m]	Lists information about the logical volumes.  The -l option lists the disks in the logical volume.
lspv [-l, M, p]	Lists the disks on the server, including the physical volume will give details about that disk.  The -l option will list the details of how the filesystems are distributed on the disk.
lsvg [-l]	Lists the volume groups on the server, including the volume group name will give details about that vg. The -l option will list the logical volumes in the volume group.
lsvpcfg	Lists each vpath and the hdisks that make up the vpath
mklv -y	Makes a logical volume in a volume group
mksysb -l -f	makes a bootable backup of rootvg
mkvg -y   . . .	Makes a volume group out of one or more physical volumes
mount   or mount   if it is already in /etc/filesystems	Mounts the file system for use.
reducevg	Removes a physical volume from a volume group
rmfs	removes a file system and it's logical volume
rmlv	Removes a logical volume (it must be inactive)
savevg -l -f	makes a backup copy of another volume group
umount  dismount the file system	Unmounts the filesystem.

Sample LVM Procedures:

Filesystem Procedures

Procedure to create a filesystem using JFS:

• See below the procedure for creating a logical volume and a filesystem using JFS:

Procedure to extend the size of filesystem using JFS:

1. "df" to see the filesystem, it's current size, % utilization and the name of it's logical volume
2. "lslv " to show information about the logical volume including it's volume group name.
3. "lsvg " to show information about the volume group, including number of free pp's and the pp size
4. If there are not enough free pp's then see below for procedure to add a disk to a volume group.
5. "chfs -a size= +4194304 " to grow the filesystem by 2 GB (4194304=2*1024*1024*1024/512)
   • NOTE:  Growing the file system will automatically grow the logical volume
6. df" shows the file system's current size is 2 GB more than before.

Troubleshooting extending the size of a filesystem using JFS:

• Error Message:  0516-787 extendlv: Maximum allocation for logical volume is 512.
  • Maximum number of LPs for the logical volume has been exceeded - must increase the allocation
  • Calculate the number of LPs needed = LV Size in MB / LP size in MB
  • chlv -x

Procedure to remove a file system

1. Unmount the filesystem
2. Remove the logical volume "rmlv "
3. Remove the filesystem information from /etc/filesystems

Procedure to reduce the size of a file system - shareold is 8mb and needs to be reduced to 4mb

1. Create the file system
   1. crfs -v jfs -m /usr/sharenew -g rootvg -a size=8192
   2. this makes a logical volume in the root volume group of 4MB that uses jfs
2. Mount the volume
   1. mount /usr/sharenew
3. Move the files from the old file system (/usr/shareold)
   1. cd /usr/shareold
   2. tar cf - | (cd /usr/sharenew; tar xvf -)
   3. cd
4. Unmount the file systems
   1. umount /usr/sharenew
   2. umount /usr/shareold
5. Remove the old file system and it's logical volume
   1. rmfs /usr/shareold
6.   
   1. chfs -m /usr/shareold /usr/sharenew
7. Mount the new filesystem
   1. mount /usr/shareold
8. Delete the temporary mount point
   1. rmdir /usr/share

Logical Volume Procedures

Procedure to create a logical volume and filesystem in a volume group using JFS:

1. lsvg to determine the size of the PP
2. lslv in similar logical volumes to determine if mirroring is in effect
3. Calculate the number of PPs needed for the logical volume
   
   1. bc
   2. scale=2
   3. /
   4. quit
4. mklv -y  "" <# of LPS>  --> creates the logical volume
5. crfs -v jfs -d -m / -A yes   --> makes the filesystem, creates the mountpoint and puts it in /etc/filesystems
6. mount /  --> mounts the new fileystem
7. df /  --> verifies the mount and the size of the new filesystem
8. Check the ownership and permissions of the new mount point
   • ls -ld
   • chown owner:group
   • chmod XXX
     
9. If mirroring is in effect, then mirror this logical volume to another disk (original and 1 mirror):
   • mklvcopy -s y 2

Check to see if  all of the logical volumes in a volume group are mirrored

• lsvg -l

Mirror a logical volume after the fact

•  mklvcopy -s y 2

Volume Group Procedures

Procedure to create a volume group:

1. lsdev -C -c disk  -> lists available disks (and the hdisk#) on the server
2. mkvg -y "" hdisk#  --> creates the volume group on the named hard disk
3. varyonvg   --> activates the volume group

Procedure to add a disk to a volume group (extend the volume group)

• extendvg
  • Verify the disk has been successfully added to the vg
• lsvg -p

Procedure to mirror the rootvg:

1. lspv  --> determine the hdisk#
2. extendvg rootvg hdisk  --> add the hdisk to the volume group
3. lspv  -->  verify that the hdisk has been successfully added to the volume group
4. chvg -Q 'n' rootvg  -->  change the quorum so that the vg will stay active if one of the mirrors fail
5. mirrorvg -S -c 2 rootvg  --> mirror all of the logical volumes in the volume group
6. lsvg -l rootvg  --> verify successful mirroring (pps will appear "stale" until synchronization is complete).
7. bosboot -a  -->  update the boot image information
8. bootlist -m normal -o hdisk0 hdisk1  --> create a new bootlist
9. bootlist -m normal -o  --> verify the bootlist is correct

Procedure to increase the number of LP's available
Assume we receive an error that the maximum number of LP's had been exceeded, and the maximum number of LP's defined was 1100:

1. "lsvg " to show the total PP's available in the volume group =1250
2. "lsvg -l " to show the total PP's used in all logical volumes in that volume group (showed sys1log, the jfs log was using 2 PP's)
3. "chlv -x 1248 " to change the maximum number of LP's from 1100 to 1248 (1250 PP's in the volume group - 2 PP's used by the jfs log  = 1248 available)

Physical Disk Procedures

Procedure to find disks/vpaths that are unallocated

• lsvpcfg
  • This will show disks/vpaths and the volume group they are allocated to
• lspv|grep None
  • This will show pvs and whether they are asssociated with a volume group
  • Note:  For vpaths, the hdisks will show as none, but they may be allocated to a vpath - you must grep each hdisk with the lsvpcfg

Procedure to make a new lun available to AIX

• Allocate the new lun on the SAN
• Run "cfgmgr"
• Verify the new vpatch/hdisk by running "lsvpcfg"
  • There should be a new vpath and it should be available with no volume group - if not, rerun cfgmgr

Procedure to list the PVs in a volume group:

• lsvg -p