2011년 8월 29일 월요일

[10g] vip check interval 변경 하기

vip check interval 변경 하기

vip속성중에 CHECK_INTERVAL 이 존재 합니다.
CRS가 VIP 의 UP/DOWN 을 CHECK 하는 시간(s) 간격 입니다.

이것이 DEFAULT 값 설정 값에 대한 정확히 체크는 안 해 봤지만..
약간 다들 차이가 나는것 같습니다. 30초 이거나 60초 이렇게 되면 한쪽 노드 다운 되고도 vip 가 failover 되는데
최고 CHECK_INTERVAL설정 값에 따라 걸릴 수도 있습니다.

그래서 이 값을 변경하고자 할때 절차를 넣어 봅니다.

### vip resource name 확인 하기

[DEVD1:/u01] crs_stat
NAME=ora.DEVD.DEVD1.inst
TYPE=application
TARGET=ONLINE
STATE=ONLINE on notedb1

NAME=ora.DEVD.DEVD2.inst
TYPE=application
TARGET=ONLINE
STATE=ONLINE on notedb2

NAME=ora.DEVD.db
TYPE=application
TARGET=ONLINE
STATE=ONLINE on notedb2

NAME=ora.notedb1.LISTENER_NOTEDB1.lsnr
TYPE=application
TARGET=ONLINE
STATE=ONLINE on notedb1

NAME=ora.notedb1.gsd
TYPE=application
TARGET=ONLINE
STATE=ONLINE on notedb1

NAME=ora.notedb1.ons
TYPE=application
TARGET=ONLINE
STATE=ONLINE on notedb1

NAME=ora.notedb1.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE on notedb1

NAME=ora.notedb2.LISTENER_NOTEDB2.lsnr
TYPE=application
TARGET=ONLINE
STATE=ONLINE on notedb2

NAME=ora.notedb2.gsd
TYPE=application
TARGET=ONLINE
STATE=ONLINE on notedb2

NAME=ora.notedb2.ons
TYPE=application
TARGET=ONLINE
STATE=ONLINE on notedb2

NAME=ora.notedb2.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE on notedb2

[DEVD1:/u01]


NAME=ora.xxxx.vip 확인을 한다. ( ora.notedb1.vip, ora.notedb2.vip)

### vip 의 상세정보 확인 하기
crs_stat -p 또는 crs_stat -p resource_name ( 예로 ora.xxxxx.vip )

그렇게 되면
CHECK_INTERVAL=xx 값을 확인 할수 있습니다.


[DEVD1:/u01] crs_stat -p ora.notedb1.vip
NAME=ora.notedb1.vip
TYPE=application
ACTION_SCRIPT=/u01/app/oracle/product/10.2/crs/bin/racgwrap
ACTIVE_PLACEMENT=1
AUTO_START=1
CHECK_INTERVAL=30
DESCRIPTION=CRS application for VIP on a node
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
HOSTING_MEMBERS=notedb1
OPTIONAL_RESOURCES=
PLACEMENT=favored
REQUIRED_RESOURCES=
RESTART_ATTEMPTS=0
SCRIPT_TIMEOUT=60
START_TIMEOUT=0
STOP_TIMEOUT=0
UPTIME_THRESHOLD=7d
USR_ORA_ALERT_NAME=
USR_ORA_CHECK_TIMEOUT=0
USR_ORA_CONNECT_STR=/ as sysdba
USR_ORA_DEBUG=0
USR_ORA_DISCONNECT=false
USR_ORA_FLAGS=
USR_ORA_IF=eth0
USR_ORA_INST_NOT_SHUTDOWN=
USR_ORA_LANG=
USR_ORA_NETMASK=255.255.255.0
USR_ORA_OPEN_MODE=
USR_ORA_OPI=false
USR_ORA_PFILE=
USR_ORA_PRECONNECT=none
USR_ORA_SRV=
USR_ORA_START_TIMEOUT=0
USR_ORA_STOP_MODE=immediate
USR_ORA_STOP_TIMEOUT=0
USR_ORA_VIP=192.168.10.110

[DEVD1:/u01]



[DEVD2:/u01] crs_stat -p ora.notedb2.vip
NAME=ora.notedb2.vip
TYPE=application
ACTION_SCRIPT=/u01/app/oracle/product/10.2/crs/bin/racgwrap
ACTIVE_PLACEMENT=1
AUTO_START=1
CHECK_INTERVAL=30
DESCRIPTION=CRS application for VIP on a node
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
HOSTING_MEMBERS=notedb2
OPTIONAL_RESOURCES=
PLACEMENT=favored
REQUIRED_RESOURCES=
RESTART_ATTEMPTS=0
SCRIPT_TIMEOUT=60
START_TIMEOUT=0
STOP_TIMEOUT=0
UPTIME_THRESHOLD=7d
USR_ORA_ALERT_NAME=
USR_ORA_CHECK_TIMEOUT=0
USR_ORA_CONNECT_STR=/ as sysdba
USR_ORA_DEBUG=0
USR_ORA_DISCONNECT=false
USR_ORA_FLAGS=
USR_ORA_IF=eth0
USR_ORA_INST_NOT_SHUTDOWN=
USR_ORA_LANG=
USR_ORA_NETMASK=255.255.255.0
USR_ORA_OPEN_MODE=
USR_ORA_OPI=false
USR_ORA_PFILE=
USR_ORA_PRECONNECT=none
USR_ORA_SRV=
USR_ORA_START_TIMEOUT=0
USR_ORA_STOP_MODE=immediate
USR_ORA_STOP_TIMEOUT=0
USR_ORA_VIP=192.168.10.111

[DEVD2:/u01]

### CHECK_INTERVAL 값 변경 하기

-- root 유저로 수행
crs_register ora.xxxx.vip -update -o ci=10 <-- 10초 간격으로 변경 하기. crs_register ora.notedb1.vip -update -o ci=60 crs_register ora.notedb1.vip -update -o st=60 -- 변경값 확인 crs_stat -p ora.xxxx.vip 수행 후 CHECK_INTERVAL 변경사항을 보면 됩니다. 적용 사례) [DEVD2:/u01] crs_stat -p ora.notedb1.vip | egrep 'CHECK_INTERVAL|SCRIPT_TIMEOUT' CHECK_INTERVAL=30 SCRIPT_TIMEOUT=60 [DEVD1:/u01] [DEVD1:/u01] crs_register ora.notedb1.vip -update -o ci=60
[DEVD1:/u01] crs_register ora.notedb1.vip -update -o st=60
[DEVD1:/u01] crs_stat -p ora.notedb1.vip | egrep 'CHECK_INTERVAL|SCRIPT_TIMEOUT'
CHECK_INTERVAL=60
SCRIPT_TIMEOUT=60
[DEVD1:/u01]

[DEVD2:/u01] crs_stat -p ora.notedb2.vip | egrep 'CHECK_INTERVAL|SCRIPT_TIMEOUT'
CHECK_INTERVAL=30
SCRIPT_TIMEOUT=60
[DEVD2:/u01]
[DEVD2:/u01] id
uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel)
[DEVD2:/u01] crs_register ora.notedb2.vip -update -o ci=60,st=60
[DEVD2:/u01] crs_stat -p ora.notedb2.vip | egrep 'CHECK_INTERVAL|SCRIPT_TIMEOUT'
CHECK_INTERVAL=60
SCRIPT_TIMEOUT=60
[DEVD2:/u01]



위의 내용중..

SCRIPT_TIMEOUT=60 설정값이 보일겁니다..

모 사이트에서 위의 설정값으로 인해 에러 로그가 생기는데..

에러 로그는 다음과 같습니다.
2011-xx-xx xx:xx:xx.xxx: [ CRSEVT][16157]32CAAMonitorHandler :: 0:Action Script /u01/app/oracle/product/10.2/crs/bin/racgwrap(check) timed
out for ora.notedb2.vip! (timeout=60)
2011-xx-xx xx:xx:xx.xx: [ CRSAPP][16157]32CheckResource error for ora.notedb2.vip error code = -2

위에서 설정정 timeout=60 은 SCRIPT_TIMEOUT 설정값에 의해 수행 되는것으로..
왜 이렇게 에러가 발생 하는것에 대해 아래와 같이 조치 및 답변 내용.

보통 I/O waiting이 발생할 경우 VIP timeout이 발생할 수 있습니다.
IBM (GPFS+Replica) 환경에서는 Begin Backup 수행시 Instance Level
Checkpoint가 발생하면서 대량의 I/O로 인해 일시적으로 I/O waiting이 발생합니다.

이때, VIP timeout이 발생하여 30 -> 60으로 증가시킨 사례가 있었다고 합니다.

timeout이 일어난다고 해서 failover 가 일어날수 있는지 여부 및 몇번 이상 되면
failover 가 나는지 여부는 관계가 없을 듯 합니다.

따라서 timeout 및 timeout 횟수에 따른 failover 가 일어난다는것은 기본적으로 배제한다.
(failover가 timeout 발생시 마다 이루어 지지 않았다면 timeout은 failover 와 관계가 없는것이 맞을 듯 함.)

그러나 timeout과 failover가 완전히 상관관계가 없지는 않을것 같은것을 고려 하여.
즉, VIP 등에 문제가 있어 timeout이 날경우 정상적인 failover도 발생할수 있으니 ...


VIP 의 안정화을 위해

cd $ORA_CRS_HOME/bin

vi racgvip 안의 파일 내용을 보시면..

# hard code default gateway here if needed
DEFAULTGW=

위의 파일 내용처럼 default gateway 설정값이 없으므로
설정을 해 주는 과정이 필요 합니다.

(설정값은 아래 참조 )
# hard code default gateway here if needed
DEFAULTGW=192.168.10.2


위의 설정을 통해 default gateway를 못찾아 timeout이 발생하는 현상을 막을수 있을듯 합니다.

그리고 각각의 노드별 CHECK_INTERVAL 값 과 SCRIPT_TIMEOUT 값을 확인 후 동일하게 맞추는 작업이 필요 합니다.

vip check interval 변경 하기


vip속성중에 CHECK_INTERVAL 이 존재 합니다.
CRS가 VIP 의 UP/DOWN 을 CHECK 하는 시간(s) 간격 입니다.

이것이 default 60 인데 이렇게 되면 한쪽 노드 다운 되고도 vip 가 failover 되는데 최고 60 sec 가까이 걸릴 수도 있습니다.

그래서 이 값을 변경하고자 할때 절차를 넣어 봅니다.

### vip resource name 확인 하기

crs_stat



NAME=ora.xxxx.vip 확인을 한다.


### vip 의 상세정보 확인 하기
crs_stat -p 또는 crs_stat -p resource_name ( 예로 ora.xxxxx.vip )

그렇게 되면

CHECK_INTERVAL=60


### CHECK_INTERVAL 값 변경 하기

-- root 유저로 수행
crs_register ora.xxxx.vip -update -o ci=10 <-- 10초 간격으로 변경 하기.

-- 변경값 확인
crs_stat -p ora.xxxx.vip 수행 후 CHECK_INTERVAL 변경사항을 보면 됩니다.


## 그리고 나머지 노드 모두 동일하게 수행 하시면 됩니다.
작성자: 시간: 댓글 없음:
라벨:

2010년 12월 29일 수요일

서브파티션 테이블에서 DBA_PART_TABLES.DEF_SUBPARTITION_COUNT 가 정상적으로 보이지 않는 경우의 해결 방법

### 서브파티션 테이블에서 DBA_PART_TABLES.DEF_SUBPARTITION_COUNT 가 정상적으로 보이지 않는 경우의 해결 방법

아래 스크립트는 저희가 보통 오렌지 등 툴로 직접 스크립트를 추출하게 되면 아래 처럼 보일겁니다.
이렇게 보이면 정상적으로 파티션에 대한 정보를 모두 보이므로 스크립트 상에 문제가 없는것을 확인 할 수 있고
아마 아래 스크립트로 생성을 하기도 할겁니다.

여기서 문제가 하나 발견 되었습니다.
DBA_PART_TABLES.DEF_SUBPARTITION_COUNT 가 정상적으로 보이지 않는 것을 확인 했습니다.



1. 테스트 스크립트 ( 오렌지등 툴로 추출한 스크립트..)

CREATE TABLE SHWOO.TKT_BKTSEAT_T
(
CO_CD VARCHAR2 (4) NOT NULL,
BKT_SALE_NO VARCHAR2 (16) NOT NULL,
SEQ NUMBER (6) NOT NULL,
REG_K_ID VARCHAR2 (14),
REG_DT DATE,
UPD_K_ID VARCHAR2 (14),
UPD_DT DATE
)
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (CO_CD, BKT_SALE_NO)
SUBPARTITION BY HASH (BKT_SALE_NO)
(
PARTITION PRH_PRM_201010 VALUES LESS THAN ('A416', '201011')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201010_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S8
TABLESPACE USERS
)
,
PARTITION PRH_PRM_201011 VALUES LESS THAN ('A416', '201012')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201011_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S8
TABLESPACE USERS
)
,
PARTITION PRH_PRM_201012 VALUES LESS THAN ('A416', '201101')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201012_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S8
TABLESPACE USERS
)
)
ENABLE ROW MOVEMENT ;
-- 생성 완료..


2. DBA_PART_TABLES 딕셔너리 확인 결과 이상 현상 발견..
( DEF_SUBPARTITION_COUNT 값이 1로 표시 )

set line 200
SELECT OWNER, TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, PARTITION_COUNT, DEF_SUBPARTITION_COUNT
FROM DBA_PART_TABLES
WHERE TABLE_NAME='TKT_BKTSEAT_T';

OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT
------------------------------ ------------------------------ --------- --------- --------------- ----------------------
SHWOO TKT_BKTSEAT_T RANGE HASH 3 1

Elapsed: 00:00:00.00
>

########################################################
#### 위의 해결 방법은 아래와 같습니다.
########################################################
1. 테이블 삭제 후 재 생성 수행 ( 주석 반드시 확인)
DROP TABLE SHWOO.TKT_BKTSEAT_T ;

CREATE TABLE SHWOO.TKT_BKTSEAT_T
(
CO_CD VARCHAR2 (4) NOT NULL,
BKT_SALE_NO VARCHAR2 (16) NOT NULL,
SEQ NUMBER (6) NOT NULL,
REG_K_ID VARCHAR2 (14),
REG_DT DATE,
UPD_K_ID VARCHAR2 (14),
UPD_DT DATE
)
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (CO_CD, BKT_SALE_NO)
SUBPARTITION BY HASH (BKT_SALE_NO)
SUBPARTITION 8 -- 반드시 서브파티션을 몇개로 나누어 졌는지 여부를 표시 해 준다. (기본은 무조건 1로 표시 합니다.)
(
PARTITION PRH_PRM_201010 VALUES LESS THAN ('A416', '201011')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201010_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S8
TABLESPACE USERS
)
,
PARTITION PRH_PRM_201011 VALUES LESS THAN ('A416', '201012')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201011_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S8
TABLESPACE USERS
)
,
PARTITION PRH_PRM_201012 VALUES LESS THAN ('A416', '201101')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201012_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S8
TABLESPACE USERS
)
)
ENABLE ROW MOVEMENT ;

2. DBA_PART_TABLES 딕셔너리 확인 결과
( DEF_SUBPARTITION_COUNT 값이 정상적으로 8 로 설정 됨)

>
SELECT OWNER, TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, PARTITION_COUNT, DEF_SUBPARTITION_COUNT
FROM DBA_PART_TABLES
WHERE TABLE_NAME='TKT_BKTSEAT_T';

OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT
------------------------------ ------------------------------ --------- --------- --------------- ----------------------
SHWOO TKT_BKTSEAT_T RANGE HASH 3 8

Elapsed: 00:00:00.01
>
>

################################################################################
######## 또는 아래 처럼 반드시 확인 후 처리 요망..
################################################################################

1. PLSQL 패키지를 이용하여 사전 DDL 문 확인 하기.

SELECT DBMS_METADATA.GET_DDL('TABLE','TKT_BKTSEAT_T','SHWOO') FROM DUAL;

2. DDL 문을 확인 후 테이블을 생성 해 주시면 됩니다.

CREATE TABLE "SHWOO"."TKT_BKTSEAT_T"
( "CO_CD" VARCHAR2(4) NOT NULL ENABLE,
"BKT_SALE_NO" VARCHAR2(16) NOT NULL ENABLE,
"SEQ" NUMBER(6,0) NOT NULL ENABLE,
"REG_K_ID" VARCHAR2(14),
"REG_DT" DATE,
"UPD_K_ID" VARCHAR2(14),
"UPD_DT" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("CO_CD","BKT_SALE_NO")
SUBPARTITION BY HASH ("BKT_SALE_NO")
SUBPARTITIONS 8 -- 이부분 값이 정상 적이지 않으면 수정 후 생성 해 주시면 됩니다.
(PARTITION "PRH_PRM_201010" VALUES LESS THAN ('A416', '201011')
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" NOCOMPRESS LOGGING
( SUBPARTITION "PRH_PRM_201010_S1"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S2"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S3"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S4"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S5"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S6"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S7"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S8"
TABLESPACE "USERS" NOCOMPRESS ) ,
PARTITION "PRH_PRM_201011" VALUES LESS THAN ('A416', '201012')
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" NOCOMPRESS LOGGING
( SUBPARTITION "PRH_PRM_201011_S1"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S2"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S3"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S4"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S5"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S6"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S7"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S8"
TABLESPACE "USERS" NOCOMPRESS ) ,
PARTITION "PRH_PRM_201012" VALUES LESS THAN ('A416', '201101')
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" NOCOMPRESS LOGGING
( SUBPARTITION "PRH_PRM_201012_S1"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S2"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S3"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S4"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S5"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S6"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S7"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S8"
TABLESPACE "USERS" NOCOMPRESS ) ) ENABLE ROW MOVEMENT
;
작성자: 시간: 댓글 없음:
라벨:

2010년 12월 1일 수요일

[11G]ADRCI PURGE , IPS 기능 활용하기

아래 내용을 보시면 11G 부터는 alert log 및 trace 관리 정책이 바뀌었다는 것을 아실 겁니다.

[11g] ADR - AlertLog와 Tracefile의 새로운 위치

아래 내용은 ADRCI PUGE 기능과 IPS 기능에 대해 테스트 한 것을 기입해 봅니다.

/oracle > adrci

ADRCI: Release 11.2.0.2.0 - Production on Tue Nov 30 10:59:54 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

ADR base = "/oracle/app/oracle"
adrci> show incident

ADR Home = /oracle/app/oracle/diag/clients/user_oracle/host_206825830_76:
*************************************************************************
0 rows fetched

ADR Home = /oracle/app/oracle/diag/clients/user_root/host_206825830_76:
*************************************************************************
0 rows fetched

ADR Home = /oracle/app/oracle/diag/rdbms/test/TEST2:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
3968573 ORA 4030 2010-07-28 16:10:37.454000 +09:00
3968533 ORA 4030 2010-07-28 16:10:37.454000 +09:00
3968557 ORA 4030 2010-07-28 16:10:37.454000 +09:00
3968589 ORA 4030 2010-07-28 16:10:37.542000 +09:00
3968541 ORA 4030 2010-07-28 16:10:37.542000 +09:00
3968733 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-28 17:26:58.531000 +09:00
3968577 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 13:16:11.467000 +09:00
3968565 ORA 4030 2010-07-29 17:46:55.237000 +09:00
3968545 ORA 4030 2010-07-29 17:46:55.239000 +09:00
3968537 ORA 4030 2010-07-29 17:46:55.249000 +09:00
3968584 ORA 4030 2010-07-29 17:46:55.257000 +09:00
3968549 ORA 4030 2010-07-29 17:46:55.306000 +09:00
3968773 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 18:09:57.796000 +09:00
3968593 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:07:05.754000 +09:00
3968639 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:09:57.510000 +09:00
3968669 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:12:40.298000 +09:00
3968594 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:14:02.333000 +09:00
3968816 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:18:16.677000 +09:00
3968357 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-30 08:25:10.893000 +09:00
3968645 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-30 11:43:34.633000 +09:00
3992524 ORA 4030 2010-08-02 20:59:03.066000 +09:00
3992525 ORA 4030 2010-08-02 20:59:09.414000 +09:00
3992526 ORA 4030 2010-08-02 20:59:16.147000 +09:00
3992527 ORA 4030 2010-08-02 20:59:23.259000 +09:00
3992528 ORA 4030 2010-08-02 20:59:26.024000 +09:00
3992588 ORA 4030 2010-08-03 09:16:49.520000 +09:00
4040570 ORA 7445 [evaopn3()+212] 2010-08-13 19:27:54.942000 +09:00

ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener:
*************************************************************************
0 rows fetched

ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener1_testdb1:
*************************************************************************
0 rows fetched

ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener1_testdb2:
*************************************************************************
0 rows fetched

ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener2_testdb1:
*************************************************************************
0 rows fetched

ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener2_testdb2:
*************************************************************************
0 rows fetched

ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener3_testdb2:
*************************************************************************
0 rows fetched

ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener_testdb1:
*************************************************************************
0 rows fetched

ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener_testdb2:
*************************************************************************
0 rows fetched

ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/sid_list_listener_testdb1:
*************************************************************************
First 0 rows fetched (*** more available ***)

adrci>
adrci> show homepath
ADR Homes:
diag/clients/user_oracle/host_206825830_76
diag/clients/user_root/host_206825830_76
diag/rdbms/test/TEST2
diag/tnslsnr/testdb2/listener
diag/tnslsnr/testdb2/listener1_testdb1
diag/tnslsnr/testdb2/listener1_testdb2
diag/tnslsnr/testdb2/listener2_testdb1
diag/tnslsnr/testdb2/listener2_testdb2
diag/tnslsnr/testdb2/listener3_testdb2
diag/tnslsnr/testdb2/listener_testdb1
diag/tnslsnr/testdb2/listener_testdb2
diag/tnslsnr/testdb2/sid_list_listener_testdb1
adrci>
adrci> PURGE
adrci> show homepath
ADR Homes:
diag/rdbms/test/TEST2
adrci>
adrci> purge -age 60 -type incident
adrci> purge -age 10 -type incident
adrci> purge -age 10 -type ALERT
adrci> purge -age 10 -type INCIDENT
adrci> purge -age 10 -type TRACE
adrci> purge -age 10 -type CDUMP
adrci> purge -age 10 -type HM
adrci> purge -age 10 -type UTSCDMP


adrci> ips generate package 2 in /oracle/DBAWORK
DIA-48448: This command does not support multiple ADR homes

adrci>


adrci> show homepath
ADR Homes:
diag/clients/user_oracle/host_206825830_76
diag/clients/user_root/host_206825830_76
diag/rdbms/test/TEST2
diag/tnslsnr/testdb2/listener
diag/tnslsnr/testdb2/listener1_testdb1
diag/tnslsnr/testdb2/listener1_testdb2
diag/tnslsnr/testdb2/listener2_testdb1
diag/tnslsnr/testdb2/listener2_testdb2
diag/tnslsnr/testdb2/listener3_testdb2
diag/tnslsnr/testdb2/listener_testdb1
diag/tnslsnr/testdb2/listener_testdb2
diag/tnslsnr/testdb2/sid_list_listener_testdb1
adrci>
adrci> set homepath diag/rdbms/test/TEST2
adrci> IPS GENERATE PACKAGE 1 IN /oracle/DBAWORK
Generated package 1 in file /oracle/DBAWORK/IPSPKG_20101130111546_COM_1.zip, mode complete
adrci>


/oracle/DBAWORK > ls -lart IPSPKG*
-rw-r--r-- 1 oracle dba 73294 Nov 30 11:20 IPSPKG_20101130111546_COM_1.zip
/oracle/DBAWORK >


###################### Note ID 738732.1 ##################################################

ADR Different Methods to Create IPS Package [ID 738732.1]
--------------------------------------------------------------------------------
Modified 26-MAY-2010 Type HOWTO Status PUBLISHED

In this Document Goal
Solution
References

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

Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1.0 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Goal
Oracle has introduced several new Diagnosability features in 11g. One of them is Incident Packaging Service (IPS).
IPS uses rules to correlate all relevant dumps and traces from ADR for a given problem and allows you to package them to ship to Oracle Support.

Refer to the note : 443529.1 for the quick steps to create package.

You can access IPS through EM or ADRCI.

This document provides instructions to create package using different methods.
Solution
Invoke adrci and set ADR_HOME.

You can create a logical package based on an incident number, a problem number, a problem key, or a time interval.
Create a logical package such that it will be most useful to diagnose the error of your concern.

I) Creating package based on incident.

Select correct incident if there are many incidents.

adrci>SHOW INCIDENT
adrci>IPS CREATE PACKAGE INCIDENT incident_number

II) Creating Empty package.

adrci>IPS CREATE PACKAGE

This creates an empty package. You must use the IPS ADD INCIDENT or IPS ADD FILE commands to
add diagnostic data to the package before generating it.


III) Creating package based on problem ID


adrci>IPS CREATE PACKAGE PROBLEM problem_ID

This creates a package and includes diagnostic information for incidents that reference the specified
problem ID. (Problem IDs are integers.) You can obtain the problem ID for an incident from the
report displayed by the SHOW INCIDENT -MODE BRIEF command. Because there can be many incidents with the same problem ID,
ADRCI adds to the package the diagnostic information for the first three incidents ("early incidents") that occurred
and last three incidents ("late incidents") that occurred with this problem ID, excluding any incidents that are older than 90 days.

IV) Creating package based on problem key

adrci>IPS CREATE PACKAGE PROBLEMKEY "problem_key"

The problem key must be enclosed in single quotes (') or double quotes (") if it contains spaces or quotes.

V)Creating package based on time interval.

This creates a package and includes diagnostic information for all incidents that occurred from sec
seconds ago until now. sec must be an integer.

adrci>IPS CREATE PACKAGE SECONDS sec adrci>IPS CREATE PACKAGE TIME 'start_time' TO 'end_time'

This creates a package and includes diagnostic information for all incidents that occurred within the
specified time range. start_time and end_time must be in the format 'YYYY-MM-DD HH24:MI:SS.FF TZR'.
This is a valid string for the NLS_TIMESTAMP_TZ_FORMAT initialization parameter.
The fraction (FF) portion of the time is optional,
and the HH24:MI:SS delimiters can be colons or periods.


Adding Incidents and files to the logical package :
You can add more files or more incidents to the package.

adrci>IPS ADD INCIDENT incident_number PACKAGE package_number
adrci>IPS ADD FILE filespec PACKAGE package_number

filespec is the complete path and name of the trace file to add .

package_number is the package id.

Generate a physical incident package :
Once you have created a logical package using one of the above methods, next step is to generate a physical package.

adrci>IPS GENERATE PACKAGE package_number IN path

This generates a complete physical package (zip file) in the designated path. For example, the
following command creates a complete physical package in the directory /home/steve/diagnostics
from logical package number 2:

adrci>IPS GENERATE PACKAGE 2 IN /home/steve/diagnostics
Upload this package to the SR for diagnosing the problem.

You can also create and generate package with one command : IPS Pack .

adrci>IPS PACK INCIDENT incident_id IN path

All the methods discussed above apply to 'IPS pack' as well.



참고 문서 :
ADRCI IPS 기능
ADRCI PURGE 기능
작성자: 시간: 댓글 없음:
라벨:

[11G] INVISIBLE INDEX 기능 활용하기

11g New Feature : Invisible Index
==================================
1. Invisible Index란

Invisible Index기능은 Optimizer가 Execution Plan을 생성 시에 Invisible Index인 Index들을 무시 하게 되는 기능입니다.
Session이나 System별로 OPTIMIZER_USE_INVISIBLE_INDEXES Parameter를 True로 설정하게 되면 Optimizer가 Invisible Index라고 할지라도
무시하지 않고 Execution Plan을 작성하게 됩니다.

Unusable Index와는 다르게 DML 작업을 하면 Invisible Index들은 계속 유지가 됩니다.
Invisible Index의 기능을 이용하면 다음과 같은 장점을 이용할 수 있습니다.

1) Index를 Drop하기 전에 Execution Plan의 변화를 미리 Test해 보실 수 있습니다.
2) 전체 Application의 영향을 주지 않고 특정 Application에서만 Temporary하게 Index를 사용하게 하실 수 있습니다.

2. Syntax

1) Index를 Invisible하게 Create

CREATE INDEX emp_deptno ON emp(deptno)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20k
PCTINCREASE 75)
INVISIBLE; <----------- Invisible Option을 사용하여 Create 한다. (DEFAULT 값이므로 옵션 제외 해도 된다.) 2) Index를 Invisible 혹은 Visible하게 만들기 ALTER INDEX index_name INVISIBLE; ALTER INDEX index_name VISIBLE; 3. Test 1) Invisible Index 생성 후에 확인 SQL> CREATE INDEX emp_deptno ON emp(deptno)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20k
PCTINCREASE 75)
INVISIBLE;
Index created.

SQL> select index_name, visibility from user_indexes;
INDEX_NAME VISIBILIT
------------ ----------
PK_EMP VISIBLE
EMP_DEPTNO INVISIBLE
PK_DEPT VISIBLE
2) PK_EMP를 Invisible로 바꾼 후의 변화
SQL> ALTER INDEX PK_EMP INVISIBLE;
Index altered.

SQL> set autotrace on
SQL> select * from scott.emp where empno=7369;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
----- ------ ---- --------- --- -----------
7369 CLERK 7902 17-DEC-80 800 20

Execution Plan
-----------------------------
Plan hash value: 3956160932
Id Operation Name Rows Bytes Cost(%CPU) Time
------------------------------------------------------------------- -------
0 SELECT STATEMENT 1 33 3 (0) 00:00:01
* 1 TABLE ACCESS FULL EMP 1 33 3 (0) 00:00:01


3) PK_EMP를 다시 Visible로 바꾼 후의 변화
SQL> ALTER INDEX PK_EMP VISIBLE;
Index altered.
SQL> set autot on
SQL> select * from scott.emp where empno=7369;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- ---- --------- --- -----------
7369 CLERK 7902 17-DEC-80 800 20

Execution Plan
----------------------------
Plan hash value: 2949544139
Id Operation Name Rows Bytes Cost(%CPU) Time
----------------------------------------------------------------------------- ----------
0 SELECT STATEMENT 1 33 1 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID EMP 1 33 1 (0) 00:00:01
* 2 INDEX UNIQUE SCAN PK_EMP 1 0 (0) 00:00:01

4) 전체 Application에 영향을 미치지 않고 특정 Query에서만 Invisible Index 사용하기

- Session A
-- * Invisible Index를 사용하도록 OPTIMIZER_USE_INVISIBLE_INDEXES Parameter를 True로 설정합니다.

SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES = true;
Session altered.

-- * Invisible Index가 있는지 확인 합니다.

SQL> select index_name, visibility from user_indexes;
INDEX_NAME VISIBILIT
--------- ----------
PK_EMP VISIBLE
EMP_DEPTNO INVISIBLE
PK_DEPT VISIBLE

-- * Index를 사용하는 부분을 확인 할 수 있습니다.
SQL> set autot on
SQL> select * from scott.emp where deptno=20;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
--------------------- --------- ---- ----- ---------
7369 CLERK 7902 17-DEC-80 800 20
7566 MANAGER 7839 02-APR-81 2975 20
7788 ANALYST 7566 19-APR-87 3000 20
7876 CLERK 7788 23-MAY-87 1100 20
7902 ANALYST 7566 03-DEC-81 3000 20

Execution Plan
----------------------------------------------------
Plan hash value: 1182541070
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------- ------------
0 SELECT STATEMENT 5 165 2 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID EMP 5 165 2 (0) 00:00:01
* 2 INDEX RANGE SCAN EMP_DEPTNO 5 1 (0) 00:00:01

- Session B
SQL> connect / as sysdba
Connected.
-- * OPTIMIZER_USE_INVISIBLE_INDEXES가 Default로 False임을 확인 합니다.
SQL> show parameter OPTIMIZER_USE_INVISIBLE_INDEXES
NAME TYPE VALUE
------------------------------ ------- ----
optimizer_use_invisible_indexes boolean FALSE

SQL> connect scott/tiger
Connected.
SQL> set autot on
-- * OPTIMIZER_USE_INVISIBLE_INDEXES가 False일 때 Invisible Index를 사용하지 않음을 확인 할 수 있습니다.
SQL> select * from scott.emp where deptno=20;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
----- ------ ------ --------- ---------------
7369 CLERK 7902 17-DEC-80 800 20
7566 MANAGER 7839 02-APR-81 2975 20
7788 ANALYST 7566 19-APR-87 3000 20
7876 CLERK 7788 23-MAY-87 1100 20
7902 ANALYST 7566 03-DEC-81 3000 20

Execution Plan
-------------------------------------------
Plan hash value: 3956160932
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------- ----------
0 SELECT STATEMENT 5 165 3 (0) 00:00:01
* 1 TABLE ACCESS FULL EMP 5 165 3 (0) 00:00:01


만약 index를 삭제 또는 변경 하는 것이 부담 스러운 경우는
OPTIMIZER_USE_INVISIBLE_INDEXES DEFAULT 값을 유지(FALSE)를 한 후
해당 인덱스만 ALTER 문을 이용하여 INVISIBLE 로 변경 처리 후 PLAN 을 확인 또는 모니터링을 한 후 정의 하시면
될듯 합니다.
개인적으로 유용한 팁이라 생각 듭니다.

Reference
Article-ID: Note 453295.1
Title: 11g New Feature : Invisible Index
본문서는 원본에서 추가 및 수정 함
작성자: 시간: 댓글 없음:
라벨:

[11gR2] Background Processes

11gR2 백그라운드 설명 입니다.
참고 하십시요.

http://download.oracle.com/docs/cd/E14072_01/server.112/e10820/bgprocesses.htm
작성자: 시간: 댓글 없음:
라벨:

2010년 11월 30일 화요일

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) 에 관해
작성자: 시간: 댓글 없음:
라벨:

2010년 11월 3일 수요일

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

 Logical Volume Manager (LVM) Commands for AIX


Glossary

TermDefinition
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.
RootvgDefault 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

CommandDefinition
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 -kShows 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.
lsvpcfgLists 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 systemUnmounts 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
작성자: 시간: 댓글 없음:
라벨:

2010년 9월 1일 수요일

[11g] SEGMENT 관리에 대해서


11gR2 의 신기능 SEGMENT 관리에 대해 알아 보겠습니다.


기존 버전에서는 TABLE 을 생성 하게 되면 DBA_SEGMENTS 딕셔너리에 등록이 됩니다.
그러나 11gR2 에서 부터는 TABLE 생성의 STORAGE 옵션 또는 파라미터설정에 따라
DBA_SEGMENTS 딕셔너리에 등록 여부를  정할 수 있습니다.
그렇다고 영원히 DBA_SEGMENTS 딕셔너리에 등록이 되지 않는것이 아니라
해당 테이블에 데이타가 INSERT 가 되면  그때 DBA_SEGMENTS 딕셔너리에 등록이 되면서 정보를 보실 수 있습니다.
그리고 그 해당 테이블을 재구성을 하지 않는 이상 DELETE , TRUNCATE 를 하더라도
DBA_SEGMENTS 딕셔너리에서 등록된 정보는  계속 남아 있습니다.

그리고 데이타가 없을 때는 세그먼트가 없으므로
exp (예전버전) 를 사용해서 테이블 Definition조차 받을 수 없고,
expdp 에서는 이 경우에도 Definition은 받을 수 있습니다.


DEFERRED_SEGMENT_CREATION 은 테이블 생성시에 세그먼트의 initial 익스텐트 때문에 테이블스페이스에서 공간을
차지하는 문제 때문에 세그먼트를 데이타가 생길 때 나중에 만들겠다는기능입니다.
즉 세그먼트가 안만들어져서 없기 때문에 당연히 뷰 에도 안보입니다.
즉, 오라클 Apps 같은 경우 사용하지 않는 테이블이 수만개가 되는데 익스텐트가 1M 단위라해도 수십GB가
낭비되는데 이런 것을 방지하기 위한 기능입니다.
즉, 일반적인 개발프로젝트에서는 데이타 없는 테이블은 안만들것이기 때문에 불필요한 기능입니다.


그러나 권고하기를 이 기능은 일반적으로 테이블이 많이 생성되지만 쓰지않은 것 또한 많은
패키지(ERP..)쪽에 권고하더군요.

일담 IN-HOUSE의 프로그램에서는 데이터가 없는 테이블은 거의 없기 때문에 공간의 절약효과는
미미 하고 , 수행중에 세그먼트가 추가되기 때문에 처음 수행지 좀 느려진다고 합니다.


첨부로 11gR2 Upgrade Workshop에서 정리된 시기능들이 있습니다.
참고하세요.
=======================================================================
DEFERRED_SEGMENT_CREATION
• Default: TRUE
• Values: TRUE
FALSE
Purpose:
Newly created table will just allocate segments when a row gets inserted
• Tablespace must be locally managed
• COMPATIBLE ≥ 11.2.0
• New behaviour - default in 11gR2
• Advantage:
• Save disk space when a high number of tables will be created but never
populated
• Application installation time is reduced
• Please note:
• Small performance penalty when the first row is inserted, because the new segment must be created at that time.


결론 그럼 난  DBA_SEGMENTS 의 정보에 무조건 남기고 싶다면..
어떻게 하느냐...

아래 두가지 설정을 선택 하시면 됩니다.

1. 파라미터 레벨 설정

deferred_segment_creation = TRUE       ### DBA_, USER_, and ALL_SEGMENTS data dictionary views 에 정보를 남기지 않는다.
deferred_segment_creation = FALSE      ### DBA_, USER_, and ALL_SEGMENTS data dictionary views 에 정보를 남긴다.

2. 테이블 생성 레벨 설정 

CREATE TABLE A(ID NUMBER);

SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME='A';
  -- deferred_segment_creation =TRUE 가 DEFAULT 값이므로 안 보입니다.



CREATE TABLE A_1(ID NUMBER) SEGMENT CREATION IMMEDIATE;
SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME='A_1';
   -- deferred_segment_creation =TRUE 가 DEFAULT 값이지만 SEGMENT CREATION IMMEDIATE 이므로 정보가 보이겠네요.
작성자: 시간: 댓글 1개:
라벨:

2010년 8월 5일 목요일

Enqueue Waits in Oracle Database 10g

In Oracle Database 10g Release 1, each enqueue type is represented by its own wait event, making it much easier to understand exactly what type of enqueue the session is waiting for. You do not need to decipher the values from the P1, P2, P3, P1RAW, P2RAW, and P3RAW columns in the V$SESSION_WAIT or the V$SESSION view.
The following table lists all the enqueue waits in Oracle Database 10g Release 1 and describes what the enqueue is for. This information is available in the X$KSQST structure. The aggregated statistics for each of these enqueue types is displayed by the view V$ENQUEUE_STAT.

Enqueue Type
Description
enq: AD - allocate AU
Synchronizes accesses to a specific OSM (Oracle Software Manager) disk AU
enq: AD - deallocate AU
Synchronizes accesses to a specific OSM disk AU
enq: AF - task serialization
Serializes access to an advisor task
enq: AG - contention
Synchronizes generation use of a particular workspace
enq: AO - contention
Synchronizes access to objects and scalar variables
enq: AS - contention
Synchronizes new service activation
enq: AT - contention
Serializes alter tablespace operations
enq: AW - AW$ table lock
Allows global access synchronization to the AW$ table (analytical workplace tables used in OLAP option)
enq: AW - AW generation lock
Gives in-use generation state for a particular workspace
enq: AW - user access for AW
Synchronizes user accesses to a particular workspace
enq: AW - AW state lock
Row lock synchronization for the AW$ table
enq: BR - file shrink
Lock held to prevent file from decreasing in physical size during RMAN backup
enq: BR - proxy-copy
Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup
enq: CF - contention
Synchronizes accesses to the controlfile
enq: CI - contention
Coordinates cross-instance function invocations
enq: CL - drop label
Synchronizes accesses to label cache when dropping a label
enq: CL - compare labels
Synchronizes accesses to label cache for label comparison
enq: CM - gate
Serializes access to instance enqueue
enq: CM - instance
Indicates OSM disk group is mounted
enq: CT - global space management
Lock held during change tracking space management operations that affect the entire change tracking file
enq: CT - state
Lock held while enabling or disabling change tracking to ensure that it is enabled or disabled by only one user at a time
enq: CT - state change gate 2
Lock held while enabling or disabling change tracking in RAC
enq: CT - reading
Lock held to ensure that change tracking data remains in existence until a reader is done with it
enq: CT - CTWR process start/stop
Lock held to ensure that only one CTWR (Change Tracking Writer, which tracks block changes and is initiated by the alter database enable block change tracking command) process is started in a single instance
enq: CT - state change gate 1
Lock held while enabling or disabling change tracking in RAC
enq: CT - change stream ownership
Lock held by one instance while change tracking is enabled to guarantee access to thread-specific resources
enq: CT - local space management
Lock held during change tracking space management operations that affect just the data for one thread
enq: CU - contention
Recovers cursors in case of death while compiling
enq: DB - contention
Synchronizes modification of database wide supplemental logging attributes
enq: DD - contention
Synchronizes local accesses to ASM (Automatic Storage Management) disk groups
enq: DF - contention
Enqueue held by foreground or DBWR when a datafile is brought online in RAC
enq: DG - contention
Synchronizes accesses to ASM disk groups
enq: DL - contention
Lock to prevent index DDL during direct load
enq: DM - contention
Enqueue held by foreground or DBWR to synchronize database mount/open with other operations
enq: DN - contention
Serializes group number generations
enq: DP - contention
Synchronizes access to LDAP parameters
enq: DR - contention
Serializes the active distributed recovery operation
enq: DS - contention
Prevents a database suspend during LMON reconfiguration
enq: DT - contention
Serializes changing the default temporary table space and user creation
enq: DV - contention
Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
enq: DX - contention
Serializes tightly coupled distributed transaction branches
enq: FA - access file
Synchronizes accesses to open ASM files
enq: FB - contention
Ensures that only one process can format data blocks in auto segment space managed tablespaces
enq: FC - open an ACD thread
LGWR opens an ACD thread
enq: FC - recover an ACD thread
SMON recovers an ACD thread
enq: FD - Marker generation
Synchronization
enq: FD - Flashback coordinator
Synchronization
enq: FD - Tablespace flashback on/off
Synchronization
enq: FD - Flashback on/off
Synchronization
Enqueue Type
Description
enq: FG - serialize ACD relocate
Only 1 process in the cluster may do ACD relocation in a disk group
enq: FG - LGWR redo generation enq race
Resolves race condition to acquire Disk Group Redo Generation Enqueue
enq: FG - FG redo generation enq race
Resolves race condition to acquire Disk Group Redo Generation Enqueue
enq: FL - Flashback database log
Synchronizes access to Flashback database log
enq: FL - Flashback db command
Synchronizes Flashback Database and deletion of flashback logs
enq: FM - contention
Synchronizes access to global file mapping state
enq: FR - contention
Begins recovery of disk group
enq: FS - contention
Synchronizes recovery and file operations or synchronizes dictionary check
enq: FT - allow LGWR writes
Allows LGWR to generate redo in this thread
enq: FT - disable LGWR writes
Prevents LGWR from generating redo in this thread
enq: FU - contention
Serializes the capture of the DB feature, usage, and high watermark statistics
enq: HD - contention
Serializes accesses to ASM SGA data structures
enq: HP - contention
Synchronizes accesses to queue pages
enq: HQ - contention
Synchronizes the creation of new queue IDs
enq: HV - contention
Lock used to broker the high watermark during parallel inserts
enq: HW - contention
Lock used to broker the high watermark during parallel inserts
enq: IA - contention
Information not available
enq: ID - contention
Lock held to prevent other processes from performing controlfile transaction while NID is running
enq: IL - contention
Synchronizes accesses to internal label data structures
Enqueue Type
Description
enq: IM - contention for blr
Serializes block recovery for IMU txn
enq: IR - contention
Synchronizes instance recovery
enq: IR - contention2
Synchronizes parallel instance recovery and shutdown immediate
enq: IS - contention
Synchronizes instance state changes
enq: IT - contention
Synchronizes accesses to a temp object’s metadata
enq: JD - contention
Synchronizes dates between job queue coordinator and slave processes
enq: JI - contention
Lock held during materialized view operations (such as refresh, alter) to prevent concurrent operations on the same materialized view
enq: JQ - contention
Lock to prevent multiple instances from running a single job
enq: JS - contention
Synchronizes accesses to the job cache
enq: JS - coord post lock
Lock for coordinator posting
enq: JS - global wdw lock
Lock acquired when doing wdw ddl
enq: JS - job chain evaluate lock
Lock when job chain evaluated for steps to create
enq: JS - q mem clnup lck
Lock obtained when cleaning up q memory
enq: JS - slave enq get lock2
Gets run info locks before slv objget
enq: JS - slave enq get lock1
Slave locks exec pre to sess strt
enq: JS - running job cnt lock3
Lock to set running job count epost
enq: JS - running job cnt lock2
Lock to set running job count epre
enq: JS - running job cnt lock
Lock to get running job count
enq: JS - coord rcv lock
Lock when coord receives msg
enq: JS - queue lock
Lock on internal scheduler queue
enq: JS - job run lock - synchronize
Lock to prevent job from running elsewhere
enq: JS - job recov lock
Lock to recover jobs running on crashed RAC inst
Enqueue Type
Description
enq: KK - context
Lock held by open redo thread, used by other instances to force a log switch
enq: KM - contention
Synchronizes various Resource Manager operations
enq: KP - contention
Synchronizes kupp process startup
enq: KT - contention
Synchronizes accesses to the current Resource Manager plan
enq: MD - contention
Lock held during materialized view log DDL statements
enq: MH - contention
Lock used for recovery when setting Mail Host for AQ e-mail notifications
enq: ML - contention
Lock used for recovery when setting Mail Port for AQ e-mail notifications
enq: MN - contention
Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
enq: MR - contention
Lock used to coordinate media recovery with other uses of datafiles
enq: MS - contention
Lock held during materialized view refresh to set up MV log
enq: MW - contention
Serializes the calibration of the manageability schedules with the Maintenance Window
enq: OC - contention
Synchronizes write accesses to the outline cache
enq: OL - contention
Synchronizes accesses to a particular outline name
enq: OQ - xsoqhiAlloc
Synchronizes access to olapi history allocation
enq: OQ - xsoqhiClose
Synchronizes access to olapi history closing
enq: OQ - xsoqhistrecb
Synchronizes access to olapi history globals
enq: OQ - xsoqhiFlush
Synchronizes access to olapi history flushing
enq: OQ - xsoq*histrecb
Synchronizes access to olapi history parameter CB
enq: PD - contention
Prevents others from updating the same property
enq: PE - contention
Synchronizes system parameter updates
Enqueue Type
Description
enq: PF - contention
Synchronizes accesses to the password file
enq: PG - contention
Synchronizes global system parameter updates
enq: PH - contention
Lock used for recovery when setting proxy for AQ HTTP notifications
enq: PI - contention
Communicates remote Parallel Execution Server Process creation status
enq: PL - contention
Coordinates plug-in operation of transportable tablespaces
enq: PR - contention
Synchronizes process startup
enq: PS - contention
Parallel Execution Server Process reservation and synchronization
enq: PT - contention
Synchronizes access to ASM PST metadata
enq: PV - syncstart
Synchronizes slave start_shutdown
enq: PV - syncshut
Synchronizes instance shutdown_slvstart
enq: PW - prewarm status in dbw0
DBWR0 holds this enqueue indicating pre-warmed buffers present in cache
enq: PW - flush prewarm buffers
Direct Load needs to flush prewarmed buffers if DBWR0 holds this enqueue
enq: RB - contention
Serializes OSM rollback recovery operations
enq: RF - synch: per-SGA Broker metadata
Ensures r/w atomicity of DG configuration metadata per unique SGA
enq: RF - synchronization: critical ai
Synchronizes critical apply instance among primary instances
enq: RF - new AI
Synchronizes selection of the new apply instance
enq: RF - synchronization: chief
Anoints 1 instance's DMON (Data Guard Broker Monitor) as chief to other instance’s DMONs
enq: RF - synchronization: HC master
Anoints 1 instance's DMON as health check master
enq: RF - synchronization: aifo master
Synchronizes critical apply instance failure detection and failover operation
enq: RF - atomicity
Ensures atomicity of log transport setup
Enqueue Type
Description
enq: RN - contention
Coordinates nab computations of online logs during recovery
enq: RO - contention
Coordinates flushing of multiple objects
enq: RO - fast object reuse
Coordinates fast object reuse
enq: RP - contention
Enqueue held when resilvering is needed or when data block is repaired from mirror
enq: RS - file delete
Lock held to prevent file from accessing during space reclamation
enq: RS - persist alert level
Lock held to make alert level persistent
enq: RS - write alert level
Lock held to write alert level
enq: RS - read alert level
Lock held to read alert level
enq: RS - prevent aging list update
Lock held to prevent aging list update
enq: RS - record reuse
Lock held to prevent file from accessing while reusing circular record
enq: RS - prevent file delete
Lock held to prevent deleting file to reclaim space
enq: RT - contention
Thread locks held by LGWR, DBW0, and RVWR (Recovery Writer, used in Flashback Database operations) to indicate mounted or open status
enq: SB - contention
Synchronizes logical standby metadata operations
enq: SF - contention
Lock held for recovery when setting sender for AQ e-mail notifications
enq: SH - contention
Enqueue always acquired in no-wait mode; should seldom see this contention
enq: SI - contention
Prevents multiple streams table instantiations
enq: SK - contention
Serialize shrink of a segment
enq: SQ - contention
Lock to ensure that only one process can replenish the sequence cache
enq: SR - contention
Coordinates replication / streams operations
enq: SS - contention
Ensures that sort segments created during parallel DML operations aren't prematurely cleaned up
Enqueue Type
Description
enq: ST - contention
Synchronizes space management activities in dictionary-managed tablespaces
enq: SU - contention
Serializes access to SaveUndo Segment
enq: SW - contention
Coordinates the ‘alter system suspend’ operation
enq: TA - contention
Serializes operations on undo segments and undo tablespaces
enq: TB - SQL Tuning Base Cache Update
Synchronizes writes to the SQL Tuning Base Existence Cache
enq: TB - SQL Tuning Base Cache Load
Synchronizes writes to the SQL Tuning Base Existence Cache
enq: TC - contention
Lock held to guarantee uniqueness of a tablespace checkpoint
enq: TC - contention2
Lock during setup of a unique tablespace checkpoint in null mode
enq: TD - KTF dump entries
KTF dumping time/scn mappings in SMON_SCN_TIME table
enq: TE - KTF broadcast
KTF broadcasting
enq: TF - contention
Serializes dropping of a temporary file
enq: TL - contention
Serializes threshold log table read and update
enq: TM - contention
Synchronizes accesses to an object
enq: TO - contention
Synchronizes DDL and DML operations on a temp object
enq: TQ - TM contention
TM access to the queue table
enq: TQ - DDL contention
DDL access to the queue table
enq: TQ - INI contention
TM access to the queue table
enq: TS - contention
Serializes accesses to temp segments
enq: TT - contention
Serializes DDL operations on tablespaces
enq: TW - contention
Lock held by one instance to wait for transactions on all instances to finish
Enqueue Type
Description
enq: TX - contention
Lock held by a transaction to allow other transactions to wait for it
enq: TX - row lock contention
Lock held on a particular row by a transaction to prevent other transactions from modifying it
enq: TX - allocate ITL entry
Allocating an ITL entry in order to begin a transaction
enq: TX - index contention
Lock held on an index during a split to prevent other operations on it
enq: UL - contention
Lock held used by user applications
enq: US - contention
Lock held to perform DDL on the undo segment
enq: WA - contention
Lock used for recovery when setting watermark for memory usage in AQ notifications
enq: WF - contention
Enqueue used to serialize the flushing of snapshots
enq: WL - contention
Coordinates access to redo log files and archive logs
enq: WP - contention
Enqueue to handle concurrency between purging and baselines
enq: XH - contention
Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
enq: XR - quiesce database
Lock held during database quiesce
enq: XR - database force logging
Lock held during database force logging mode
enq: XY - contention
Lock used by Oracle Corporation for internal testing
작성자: 시간: 댓글 없음:
라벨:

팔로어