따봉 구걸남's 블로그

오라클에서 데이터를 이관하는 방법 중 전통적인 유틸리티인 export/ import 가 있다. 

데이터펌프가 옵션도 더 많고, 성능, 모니터링 측면에서 더 유리하지만 상황에 따라 exp/imp 방식을 써야할때도 있다.

이 때 소스쪽 환경과 타겟쪽 환경의 차이로 인해 주석이 깨져서 import 되기도 한다. 

그 원인은 data 와 주석을 export 할때 dump 파일의 주석과 데이터를 처리하는 방식의 차이 때문이다. 

- Export/Import 시 주석은 덤프 파일 내 SQL 문장으로 텍스트 형태로 기록된다. 따라서 클라이언트의 `NLS_LANG` 설정에 따라 인코딩이 결정된다. 그래서 아래와 같이 exp 유틸 사용전에 설정을 타겟과 동일하게 변경해준다. 

 => export NLS_LANG=AMERICAN_AMERICA.AL32UTF8  
      exp ...

또는 주석이 많지 않다면 데이터베이스의 DBA_COL_COMMENTS 등의 시스템 테이블에서 스크립트를 바로 추출할수도 있다. 

   -- 테이블 주석
   SELECT 'COMMENT ON TABLE ' || table_name || ' IS ''' || comments || ''';' 
     FROM all_tab_comments 
    WHERE owner='USER';

   -- 컬럼 주석
   SELECT 'COMMENT ON COLUMN ' || table_name || '.' || column_name || ' IS ''' || comments || ''';' 
     FROM all_col_comments 
    WHERE owner='USER';
   
   

AWS 환경에서 S3 통한 덤프파일 전달 받아 데이터펌프 사용하기 

# 외부 Oracle 환경에서 expdp 로 덤프 파일 생성

expdp username/password@database_link \
DIRECTORY=data_pump_dir \
DUMPFILE=export_dump.dmp \
SCHEMAS=schema_name \
LOGFILE=export_dump.log

case 1. ec2 oracle  환경에서 impdp 

1) 생성된 덤프 파일 S3 버킷에 업로드

 - AWS CLI를 사용하여 덤프 파일을 S3 버킷에 업로드합니다:

aws s3 cp export_dump.dmp s3://your-bucket-name/export_dump.dmp

2) EC2 인스턴스 접속,  S3 버킷으로부터 덤프 파일 다운로드

 - EC2 인스턴스에 접속합니다:

ssh -i "your-key.pem" ec2-user@your-ec2-instance-public-dns

 - S3 버킷에서 덤프 파일 다운로드

aws s3 cp s3://your-bucket-name/export_dump.dmp /tmp/export_dump.dmp

3) ec2 Oracle 에 impdp 로 데이터 로드

impdp username/password@database \
DIRECTORY=data_pump_dir \
DUMPFILE=export_dump.dmp \
SCHEMAS=schema_name \
LOGFILE=import_dump.log \
TABLE_EXISTS_ACTION=REPLACE

- 작업 후 파일 삭제

aws s3 rm s3://your-bucket-name/export_dump.dmp

case 2. rds oracle  환경에서 impdp 

1) 생성된 덤프 파일 S3 버킷에 업로드

aws s3 cp export_dump.dmp s3://your-bucket-name/export_dump.dmp

2) S3에서 덤프 파일을 다운로드

BEGIN
  rdsadmin.rdsadmin_s3_tasks.download_from_s3(
    p_bucket_name    => 'your-bucket-name',
    p_s3_prefix      => 'export_dump.dmp',
    p_directory_name => 'DATA_PUMP_DIR'
  );
END;
/

3) rds Oracle 에 impdp 로 데이터 로드

DECLARE
  v_hdnl NUMBER;
BEGIN
  v_hdnl := DBMS_DATAPUMP.OPEN(
    operation => 'IMPORT',
    job_mode  => 'SCHEMA',
    job_name  => 'IMPORT_JOB'
  );
  
  DBMS_DATAPUMP.ADD_FILE(
    handle    => v_hdnl,
    filename  => 'export_dump.dmp',
    directory => 'DATA_PUMP_DIR'
  );
  
  DBMS_DATAPUMP.METADATA_FILTER(
    handle => v_hdnl,
    name   => 'SCHEMA_EXPR',
    value  => 'IN (''schema_name'')'
  );
  
  DBMS_DATAPUMP.START_JOB(v_hdnl);
END;
/

# impdp 작업 모니터링

SELECT *
  FROM DBA_DATAPUMP_JOBS 
 WHERE JOB_NAME = 'IMPORT_JOB';
  

오라클에서 SQL 의 처리과정 

사용자가 SQL 을 실행하면 아래 1~ 3 까지의 파싱 단계를 거치게 된다. 

 1. syntax check : 문법적 오류 체크 (ex. select * form tab_a )

 2. semantic check : 존재하지 않는 테이블 조회 등

 3. shared pool check : 동일한 sql 이 shared pool 에 캐싱되어 있는지 확인 

이 과정을 거쳐 SQL 이 캐싱되어 있지 않는 것으로 확인되면 (library cache miss) 

4. 실행계획 생성 단계를 거쳐 

5. 최종적으로 실행을 하게 된다. 

이때 실행계획 생성과정에서 library cache, data dictionary cache 에 반복적으로 접근하고 이 과정에서 

latch 라는 lock 보다는 가벼운 잠금장치를 수없이 획득하는 과정을 거친다. 

소프트 파싱 vs 하드 파싱

소프트 파싱 (Soft Parsing) 이라는 것은 이미 만들어진 실행계획을 캐시에서 찾아 재사용하는 과정이다. 

Shared Pool의 Library Cache에 저장된 SQL 실행 계획을 공유하여 최적화 과정을 생략한다.
실행계획 생성과정이 cpu 를 많이 소모하는 과정이기 때문에 하드 파싱에 비해 더 빠르고 리소스를 절약할 수 있다.

하드 파싱 (Hard Parsing) 캐시에 SQL 이 존재하지 않아 (library cache miss) 새로운 실행 계획을 생성해야 할 때 발.

최적화 및 로우 소스 생성 단계까지 모두 거친 후에 SQL 이 실행된다. 

소프트 파싱의 이점과 한계

소프트 파싱은 실행 계획을 반복적으로 생성하는 오버헤드를 줄여 성능을 향상 시켜주어 이롭지만( 특히 OLTP 환경에서 동시 접속자가 많을 때 효과적 ) 항상 최선의 선택은 아니다.

사용자 입력값을 바인드 변수 처리하면 같은 SQL ID 로 처리되어 소프트 파싱으로 처리되는 비율이 높아지지만 

SQL 최적화 시점에 컬럼의 히스토그램을 사용하지 못해 실행 계획이 고정 되고 실제 데이터의 분포도가 플랜에 적용이 안되는 경우가 생긴다. 

바인드 Peeking의 역효과 예시

다음과 같은 테이블과 쿼리가 있다고 가정해봅시다:
sql
SELECT * FROM 직원 WHERE 부서 = :dept

이 테이블에서 '영업부'는 전체 직원의 50%를 차지하고, 나머지 부서들은 각각 5% 미만의 직원을 가지고 있습니다.
첫 실행 시 :dept에 '인사부'가 바인딩되었다면:
옵티마이저는 인덱스 스캔을 선택합니다.
이 실행 계획이 캐시에 저장됩니다.
이후 '영업부'로 쿼리를 실행하면:
캐시된 실행 계획(인덱스 스캔)을 사용합니다.
하지만 영업부는 데이터의 50%를 차지하므로, 풀 테이블 스캔이 더 효율적일 수 있습니다.
이로 인해 '영업부' 쿼리의 성능이 크게 저하될 수 있습니다1.

소프트 파싱 한계에 대한 대안

1. 힌트 사용:
특정 쿼리에 대해 옵티마이저 힌트를 사용하여 원하는 실행 계획을 강제
SELECT /*+ FULL(직원) */ * FROM 직원 WHERE 부서 = :dept

2. 아웃라인 사용:
특정 쿼리에 대한 실행 계획을 고정
CREATE OUTLINE 직원_부서_조회 FOR
SELECT * FROM 직원 WHERE 부서 = :dept;

3. 적응적 커서 공유 활용:
Oracle 11g 이상에서는 적응적 커서 공유를 통해 바인드 변수 값에 따라 다른 실행 계획을 사용할 수 있다

4. 특정 SQL의 소프트 파싱 방지
SELECT /*+ NO_SHARED_CURSOR */ * FROM 직원 WHERE 부서 = :dept

5. CURSOR_SHARING 파라미터 조정:

세션 레벨에서 CURSOR_SHARING을 EXACT로 설정, 해당 세션의 모든 쿼리에 대해 소프트 파싱을 방지
ALTER SESSION SET CURSOR_SHARING = EXACT;

실무에서 지금까지는 대부분 엔터프라이즈 버전(EE) 를 주로 사용해왔다. 

개인공부 용도로 사용하는 것도 무료이니 EE 만 써왔고, 기업에서도 메인서비스는 RAC 를 사용하기 때문에 그래왔다.

굳이 제약이 있는 Standard Edition 을 쓸 일이 없었는데..  클라우드가 대세가 되면서 이것에도 변화가 조금 생겼는데..

대부분 mysql 로 전환하지만, 기존시스템을 완전히 버리지 못해 오라클을 일부는 남기지만 EE 대신 SE 를 쓴다는 것이다. 

물론 비용적 이유인데... DBA 입장에서는 당연히 제약많은 SE 보다 EE 가 좋지만 고객사의 니즈와 의중을 안 이상 

적응해야지.. 어쩔도리가 없다. 

각설하고 오라클 SE, EE 의 큰 차이점과 그로인한 이슈를 어떻게 회피할수 있는지 알아보자

1. 파티션 테이블

월별테이블 생성 + 뷰 

2. AWR 

 statspact 사용

3. 인덱스 온라인 

 별도 테이블 생성 + 리네임 + 데이터 후보정 작업

여기까지는 그래도 대안이 있다. 반면 다음의 feature 들은 대안이 없기에 SE 로 넘어갈때 신중히 고려해야 한다. 

4. 압축
5.  SQL 병렬 작업 

https://docs.oracle.com/cd/E55822_01/DBLIC/editions.htm#DBLIC109

https://docs.aws.amazon.com/ko_kr/prescriptive-guidance/latest/evaluate-downgrading-oracle-edition/compare-features.html

# SE 버전에 따른 cpu 제한 

1) Standard Edition One (SE1):
     2 CPU까지 확장 가능한 서버에 설치 가능
2) Standard Edition (SE):
     4 CPU까지 확장 가능한 서버에 설치 가능
3) Standard Edition 2 (SE2):
    Oracle Database 12c Release 1 (12.1.0.2) 이상부터 도입됨.
    2 소켓까지 사용 가능하며, 최대 16 CPU 스레드로 제한됨.
   RAC(Real Application Cluster) 구성 시, 2개의 서버 각각 1 소켓 8 CPU 스레드만 사용 가능

1) test 유저 생성

create user test identified by "Passw0rd";
grant connect to test;

2) wallet 용 tns alias 추가 
cd $ORACLE_HOME/network/admin
vi tnsnames.ora

WAL_TEST = 
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xx.xx.xxx)(PORT=1521))
)
(CONNECT_DATE=
(SERVICE_NAME=DFS)(SERVER=DEDICATED)
)
)


3) conn script 

cd /home/oracle/

-- vi p_test.sql
conn test/"Passw0rd" @WAL_TEST
show user;
select count(*) from all_objects;
exit;

-- vi np_test.sql
conn /@WAL_TEST
show user;
select count(*) from all_objects;
exit;


4) make wallet

-- wallet 저장 디렉토리 생성
mkdir $ORACLE_HOME/network/admin/wallet

-- wallet 생성, wallet 패스워드 입력
orapki wallet create -wallet "wallet 디렉토리" -auto_login_local

-- credential 생성, alias, 계정/비번 wallet 패스워드 입력 
mkstore -wrl "wallet 디렉토리" -createCredential WAL_TEST test Passw0rd

=> wallet 비밀번호 입력 

-- sqlnet.ora 에 wallet dir 추가 

cd $ORACLE_HOME/network/admin

vi sqlnet.ora

WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY= "wallet 디렉토리")
))

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

5) 실행

cd /home/oracle/

ss
@p_test

@np_test


-- ### wallet 관리 #######

select *
  from v$encryption_wallet
;


-- credential list 확인 

mkstore -wrl "wallet 디렉토리" -listCredential


-- credential list 삭제 
mkstore -wrl "wallet 디렉토리" -deleteCredential WAL_TEST


-- credential 비번 수정 
mkstore -wrl "wallet 디렉토리" -modifyCredential WAL_TEST test pass1234

-- wallet 비번 수정 
orapki wallet change_pwd -wallet "wallet 디렉토리" -oldpwd "Passw0rd" -newpwd "oracle123"


-- wallet 삭제 
rm *wallet*

오라클 히든 파라미터 체크 

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;

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

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 파일 지우는걸 이렇게 힘들게 지워야하나..

오라클 대용량 테이블에서 사용하지 않는 컬럼 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 유저로 접속한 것처럼 시노님을 그대로 사용할수 있게 된다.