따봉 구걸남's 블로그

실무에서 지금까지는 대부분 엔터프라이즈 버전(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
;
 

오라클 인스턴스가 시작되면 초기화 파라미터에 명시한대로 메모리영역을 할당하고 백그라운드 프로세스를 시작한다. 

메모리영역에 저장되는 정보는 다음과 같다. 

- 프로그램코드
- DB에 연결된 세션 정보 (inactive 포함)
- 프로그램 실행 중에 필요한 정보(예: 결과 rows 을 가져오는 쿼리의 현재 상태)
- 프로세스 간에 공유되는 lock 정보 
- 캐싱된 data (data blocks, redo record)

1. 오라클 메모리 구조

 1) SGA (System Global Area)

- Shared pool : SQL statements, data dictionary, Stored procedures 등을 캐싱하여 공유

- DB buffer cache : data block 을 disk I/O 를 통해 캐싱하여 공유. 
- Redo log buffer : redo log file 을 캐싱하여 성능 향상 
- Large pool : 아래 case 에서 shared pool 을 사용할수 있는데 large pool 을 사용하는 것을 고려해볼수 있다.
    shared server (session memory)
    Parallel query (message buffers)  
    Recovery Manager(I/O buffers )

- Java pool

 2) PGA (Program Global Area)

 : Server process 가 사용하는 메모리 영역 ( oracle 에서는 작업대 정도로 비유함)
  각 server process 간에 각자의 PGA 를 사용하고 서로 공유되지 않음.
  세션 A 가 쿼리를 날렸을때 중간계산된 값이 다른 세션에 의해 사용이 되면 안될것이다. 
 

 각 서버프로세스는 각자의 PGA 를 할당받는다고 했는데, 시스템 전체의 PGA를 total instance PGA 혹은 Instance PGA 라고 부름.

SGA 는 시스템 전체에서 하나이기에 (single node 기준)  sga_max_size, sga_target 을 설정하지만 
PGA 는 서버프로세스별 할당하므로 연관된 초기화 파라미터에 전체를 의미하는 aggregate 가 붙는다.(pga_aggregate_limit,pga_aggregate_target) 

PGA 는 전체의 크기만 설정 가능하고 개별 세션의 PGA 를 조정하려면 아래와 같이 해야함.

alter session set workarea_size_policy = manual;
alter session set sort_area_size = 1024000; 

하지만 오라클에서는 memory 관리를 수동으로 조정하는 것을 권장하지 않고 있다. 

3) UGA (User Global Area) 
  The UGA is memory associated with a user session.
  
 4) Software code areas

참조문서 https://docs.oracle.com/database/121/CNCPT/memory.htm#CNCPT007

테이블스페이스 사용량 확인할때 보통 dba_data_files 와 dba_free_space 뷰를 조인하는데

특정 디비에서 쿼리 수행시간이 매우 오래 걸리는 현상 확인. 

dba_data_files 만 조회했을때는 이상이 없으나 dba_free_space 가 원인임을 파악.

MOS(Doc ID 271169.1) 를 확인해보면 10g 부터 나온 기능인 recycle bin 에서 표시되는 object 까지 dba_free_space 가 포함하는 것으로 변경이 됨.

sys 로 로긴후 아래 실행

SELECT substr(DROPTIME,1,10), count(*)
  FROM dba_recyclebin
GROUP BY substr(DROPTIME,1,10)
; 

purge dba_recyclebin;