따봉 구걸남'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';
  

실무에서 지금까지는 대부분 엔터프라이즈 버전(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 유저로 접속한 것처럼 시노님을 그대로 사용할수 있게 된다. 

오라클은 8i 에서부터 백업을 위해 rman 이라는 것을 처음 소개했다. 
백업과 복구가 보다 쉬워진다는 것이 오라클이 내세운 명분이다.
하지만 새로운 것을 다시 배워야 하기에 한동안 DBA 들에겐 외면받아왔다. 
그렇지만 본격적으로 오라클이 ASM 이라는 개념을 들고왔다.
그리고 이것은 rman 을 통해서만 백업이 가능해서 이제 DBA 들은 RMAN 을 반드시 익혀야 하는 현실이다. 

각설하고..

Recovery catalog DB 란

한마디로 rman 이 사용할 metadata 들을 저장한 db스키마 라고 생각하면된다. 
RMAN 을 사용하려면 백업과 복구를 위한 정보들을 저장할 공간이 필요하다.
별도로 CATALOG DB 를 설치하지 않아도 rman 사용에는 지장이 없다.
이 경우 controlfile 에 해당 정보들이 저장된다. 


Recovery catalog 가 담는 metadata types 은 어떤 것들인가

 1) datafile, archived redo log backup sets & backup pieces
 2) datafile copies
 3) archived redo logs and their copies
 4) db structure (tbs, datafiles)
 5) stored scripts
 6) rman config


Recovery catalog DB 사용의 이점

안써도 사용가능하지만 굳이 catalog db 를 사용해서 얻는 이점은 아래와 같다.
1) redundancy of controlfile
 =>  catalog db 를 쓴다고 controlfile 에 metadata 를 저장하지 않는 것은 아니다. 정보의 중복 저장이다. 
  이것이 이점이 될수 있을까 싶지만, 원본db 의 controlfile 이 삭제되었을때도 카탈로그에는 남아있는 장점이 있다.
  
** backup, restore, and crosscheck 같은 operation 발생시 metadata 가 변경이 된다. 
이때 순서는 언제나 target db controlfile 우선 update, 카탈로그db 는 후에 sync 된다. 

2) 모든 타겟 DB metadata 중앙관리화 
 => 여러 서버를 가진 경우 한곳에서 metadata 정보 관리하기 편함. 

3) longer metadata history
 => controlfile 에 저장할때 보다 더 긴 기간 정보 저장이 가능. 

4) stored script 사용
 => catalog 에서만 가능한 것. 한번 스크립트를 생성해서 재사용 가능. 아래에서 확인해보자


 * 아카이브로그를 지우는 script 를 생성해보자. 

CREATE SCRIPT DEL_ARCH
{
 crosscheck archivelog all;
 delete noprompt expired archivelog all;
 delete noprompt archivelog all completed before 'sysdate -1';
}

* 이번에는 global keyword 로 생성해보자. 

CREATE GLOBAL SCRIPT GLB_DEL_ARCH
COMMENT 'use when deleting arc log..'
{
crosscheck archivelog all;
  delete noprompt expired archivelog all;
delete noprompt archivelog all completed before 'sysdate -1';
}
* 아래 구문으로 실행해보자. 
RUN
{
EXECUTE SCRIPT GLB_DEL_ARCH;
}

카탈로그 디비에 해당 스크립트가 저장이 되고 이는 아래 뷰에서 확인이 가능하다. 
SELECT *
  FROM RC_STORED_SCRIPT
;