따봉 구걸남's 블로그

오라클 히든 파라미터 체크 

SELECT x.ksppinm "Parameter",
              Y.ksppstvl "Session Value",
              Z.ksppstvl "Instance Value"
  FROM x$ksppi X,
             x$ksppcv Y,
             x$ksppsv Z
WHERE x.indx = Y.indx
      AND x.indx = z.indx
      AND x.ksppinm LIKE '/_%' escape '/'
order by x.ksppinm;

위 쿼리로 현재 설정된 히든 파라미터 정보를 확인해보자 

ORA 02020 too many database link 오류

원인 : 동시에 연결된 db link 의 갯수가 parameter 에 설정된 값보다 많은 경우 발생하는 에러

해결방안 : 

     1. 링크를 종료하는 습관 

         rollback; 혹은 commit;

    2. 설정값 늘리기 

      select name, value from v$parameter where name='open_links';

      alter system set open_links=100 scope=spfile;

테이블 통계정보가 부정확 할 경우 실행계획이 잘못 생성되어 성능에 이슈가 발생할 수 있다. 

그래서 보통 dbms_stats 유틸로 gather_table_stat 패키지를 실행해서 통계정보를 수집하는데 

바뀐 통계정보가 바로 반영되어 새로운 실행계획이 바로 적용되지 않을 수 있는데 

이 경우 no_invalidate=> false 옵션을 사용하면 즉시 cursor 가 무효화 되어 새로운 실행계획을 바로 적용할수 있다. 

* no_invalidate=> true 는 커서를 무효화 하지 않겠단 뜻으로 cursor 가 shared pool 에서 flush 된 후 reload 될때까지 변경되지 않는다. 

다만 rac 환경에서는 롤링이 되지않아 특정 노드에서만 쿼리가 느린 현상이 발견 됨.

"_optimizer_invalidation_period" 에 적용된 시간이 지나서야 모든 노드의 cursor 가 무효화가 된다. 

운영 상태에서 인덱스를 생성, 변경시 online 옵션은 매우 유용하지만 가끔 의도치 않는 상황을 만들기도 한다. 

의도치 않거나 혹은 의도해서 해당 프로세스를 kill 했을때 uncommit 된 트랜잭션이 정리가 되지 않는 상태로 오래 지속될수가 있다. 

08104, 00000, "this index object %s is being online built or rebuilt"
// *Cause:  the index is being created or rebuild or waited for recovering
//          from the online (re)build
// *Action: wait the online index build or recovery to complete

CREATE TABLE TEST_TAB(A INT, B VARCHAR2(10));
insert into TEST_TAB 
select dbms_random.value(1,1000000), 'A'
  from dual
 connect by level < 1000000;
 
CREATE INDEX IDX_TEST ON TEST_TAB(A)online;
 => cancel 

DROP INDEX IDX_TEST;

 => ORA-08104

08104, 00000, "this index object %s is being online built or rebuilt"
// *Cause:  the index is being created or rebuild or waited for recovering
//          from the online (re)build
// *Action: wait the online index build or recovery to complete

아래처럼 수동으로 상태를 정리해주자.
 
declare
v_result boolean;
begin
v_result := DBMS_REPAIR.ONLINE_INDEX_CLEAN(73252);  -- 인덱스의 object_id
end;
/

oracle audit 파일 삭제

show parameter audit 으로 audit 파일 destination 확인 

1. os 명령어로 삭제하기 

find . -mtime +20 -type f -name "*.aud" -mtime +1  -exec rm -f {} \;

=> 수정한지 20 일 이상된 .aud 파일을 삭제 

2. 오라클 제공 util (DBMS_AUDIT_MGMT) 로 삭제 

-- INIT_CLEANUP ( PURGE JOB 생성 위해 필요)
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => dbms_audit_mgmt.AUDIT_TRAIL_OS,
default_cleanup_interval => 24*7);  -- 1주일에 1번 실행
END;

-- 주기적으로 삭제하는 JOB 생성 
BEGIN
   DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
   AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   AUDIT_TRAIL_PURGE_INTERVAL => 24*7,
   AUDIT_TRAIL_PURGE_NAME => 'STANDARD_OS_AUDIT_TRAIL_PURGE',
   USE_LAST_ARCH_TIMESTAMP => FALSE );
END;

-- 등록된 PURGE JOB 은 DBA_AUDIT_MGMT_CLEANUP_JOBS 뷰에서 확인가능

-- 기타 AUDIT UTIL 관련 뷰
select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;
select * from DBA_AUDIT_MGMT_CLEAN_EVENTS;

-- 등록된 PURGE JOB 삭제하기 

BEGIN DBMS_AUDIT_MGMT.DROP_PURGE_JOB('STANDARD_OS_AUDIT_TRAIL_PURGE'); END;

-- INIT CLEANUP 삭제하기 

BEGIN
  DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
  AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS);
END;

-- 1회성 AUDIT 삭제 

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   AUDIT_TRAIL_TYPE         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp  =>  FALSE);
END;
 

audit 파일 지우는걸 이렇게 힘들게 지워야하나..

- core 수 확인

cat /proc /cpuinfo | grep processor

- du depth

du -h --max_depth=1

- ftp전송후  command not found

$'\r': command not found 

 sed -i -e 's/\r$//' 대상파일

- file 첫줄 편집 

sed -i "1i\spool test.log" test.sql
echo -e "spool off\next; \n" >> text.sql

-- 현재 AWR 설정 확인 (주기, TOPn )

SELECT *
  FROM DBA_HIST_WR_CONTROL
;

-- 각 interval 당 snapshot 확인 
SELECT *
  FROM DBA_HIST_ASH_SNAPSHOT
ORDER BY SNAP_ID DESC
;

-- snapshot 의 sql_id 별 stat 확인 
SELECT *
  FROM DBA_HIST_ACTIVE_SESS_HISTTORY
;
 

 ## 스냅샷 생성 및 삭제 

1. 스냅샷 생성

BEGIN DBNS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT
( [FLUSH_LEVEL => 'FLUSH_LEVEL']);
END;
/


2. 스냅샷 삭제 

BEGIN DBNS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE
( LOW_SNAP_ID => snap_id, HIGH_SNAP_ID => snap_id [DBID => dbid]);
END;
/

3. 스냅샷 설정 변경 

BEGIN DBNS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
( [RETENTION => retention_time][, INTERVAL => interval_time]
 [, TOPNSQL => topn_sql_number]);
END;
/


4. 베이스라인 설정 

BEGIN DBNS_WORKLOAD_REPOSITORY.CREATE_BASELINE
( START_SNAP_ID => snap_id, END_SNAPID => snap_id,
BASELINE_NAME => 'baseline_name' [,DBID => dbid]
);
END;
/

 
5. 베이스라인 삭제  

BEGIN DBNS_WORKLOAD_REPOSITORY.DROP_BASELINE
( BASELINE_NAME => 'baseline_name' [,CASCADE => true|false][,DBID => dbid]
);
END;
/

오라클 대용량 테이블에서 사용하지 않는 컬럼 DROP 하기 

1. SET UNUSED 구문으로 사용하지 않음으로 MARK 하기

2. DROP UNUSED  구문으로 삭제하기

예제 

UNUSED 가 잘되어있는지 확인해보자

UNUSED 로 MARK 된 컬럼은 테이블을 조회해도 나오지 않고, 딕셔너리에서도 제외된다. 

해당컬럼을 DROP 시 많은양의 UNDO 가 발생되는것을 막기 위해 CHECKPOINT 구문으로 DROP하자.

-- script 

DROP TABLE UNUSED_TEST PURGE;

CREATE TABLE UNUSED_TEST (A INT, B CHAR(1));

INSERT INTO UNUSED_TEST(A,B) VALUES(1,'A');
COMMIT;

ALTER TABLE  UNUSED_TEST SET UNUSED COLUMN B;

SELECT * FROM DBA_UNUSED_COL_TABS;
 
SELECT * FROM UNUSED_TEST; 

SELECT OWNER, TABLE_NAME, COLUMN_NAME 
  FROM DBA_TAB_COLUMNS
 WHERE TABLE_NAME='UNUSED_TEST' AND OWNER='ORAUSER';

ALTER TABLE UNUSED_TEST DROP UNUSED COLUMN CHECKPOINT 100;
 

1. Hugepage 정의, 이점

엔터프라이즈 급의 리눅스 환경에서 hugepage를 사용하는 것은 운영상의 이점이 있기에 거의 필수적으로 사용하고있다. 

hugepage 란 커널의 메모리 사용 기법인데, 보통의 경우 regular size page 가 4k로 쪼개져서 메모리를 관리하게 된다. 

잘게 쪼개진 page 로 인해 커널이 관리할 메타데이터의 양이 많아지는 부담과 메모리 스왑 현상이 나타나는 단점이 있다.

# 메모리 스왑 : 물리적 메모리 한계를 극복하기 위해 사용빈도가 적은 메모리의 영역을 디스크에 내려서 사용 가능 메모리 영역을 더 확보하려는 현상.

반면 hugepage를 사용하면, 4k 로 쪼개진 page 가 2M 단위로 관리되어 훨씬 적은 양의 메타데이터를 커널이 관리한다. 

ex) 100GB SGA 를 사용할 경우 

   - 4k regular size page : 26,214,400 개 

   - 2m hugepage : 51200 개

 2M hugepage 를 사용할 경우 커널이 관리할 메타데이터가 512 배가 절약 된다. 

또한 hugepage 는 메모리에 상주하여 swap out 되지 않는다. 

2. 운영중인 DB 의 SGA 에 적절한 hugepage 숫자를 계산하기   

my oracle support 에 적절한 갯수 hugepage 를 계산하는 shell script (hugepages_settings.sh)가 있다. 

 MOS (Doc ID 401749.1) 

오라클 인스턴스가 떠있는 상태에서 해당 쉘을 실행하자.

나의 경우엔 706 개 hugepage 가 적절하다고 알려준다. 

나의 테스트 환경은 메모리가 넉넉하지 않아 instance 가 떠있는 상태에선 적절한 숫자의 hugepage 를 만들지 못한다.

이때는 instance 를 내리고 다시 명령어를 적용한다. 

cat /proc/meminfo | grep Huge

sysctl -w vm.nr_hugepages=706

# 서버재기동시에도 적용위해
vi /etc/sysctl.conf
vm.nr_hugepages=706

hugepage 를 사용하여 SGA 가 기동되었다.

3. SGA 에서 hugepage 파라미터 설정

alter system set use_large_pages={ TRUE | FALSE | AUTO | ONLY | AUTO_ONLY } scope=spfile;

* USE_LARGE_PAGES 파라미터 값

 - TRUE : hugepage 를 사용하지만 부족하면 일반 4k page 를 사용. 

 - FALSE : hugepage 를 사용하지 않고 일반 page 사용

 - ONLY : hugepage 만으로 SGA 를 사용. free hugepage 가 부족시 instance 시작시 에러 발생

# USE_LARGE_PAGES 가 리눅스에만 해당하는 파라미터지만  FALSE 로 적용시 성능저하 가능성 있음.

 AMM 을 쓰는 경우의 USE_LARGE_PAGES 를 FALSE 로 적용하는 것은 해당하지 않음.

#  호환성

1) incompatible

- AMM 을 사용할 경우에는 사용 불가 

2) compatible

-  AUTO PGA MANAGEMENT 

WORKAREA_SIZE_POLICY = AUTO
PGA_AGGREGATE_TARGET = XXX

- Automatic Shared Memory Management

ALTER SYSTEM SET memory_target=0 SCOPE=BOTH;
ALTER SYSTEM SET SGA_TARGET=500M SCOPE=BOTH;

보통 개발자들은 SQL을 작성할때 오너를 명시하지 않는 경우가 대부분이다. 

만약 퍼블릭 시노님을 사용하지 않는 환경에서  'A' 라는 유저로 스크립트를 수행해야하는데 해당 유저의 비밀번호를 모른다면 어떻게 할까?

 유저 A 의 비밀번호를 재설정할수도 있지만 서비스 계정일 경우 함부로 바꿀수도 없다. 

ALTER SESSION SET CURRENT_SCHEMA=A

위 구문을 실행하면 A 유저로 접속한 것처럼 시노님을 그대로 사용할수 있게 된다.