Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 첫번째
사진이 아닌 동영상으로: Flashback 버전 질의
전혀 아무런 설정 없이도 행의 모든 변경 내용을 즉시 식별 가능
Oracle9i Database에는 Flashback 질의 형태의 소위 “타임 머신” 기능이 이미 도입된 바 있습니다. DBA는 이 기능을 통해 실행 취소 세그먼트에 블록의 이전 이미지 복사본이 있으면 열의 값을 특정 시간으로 확인할 수 있습니다. 하지만 Flashback 질의는 두 시점 간의 변경된 데이타를 표시하는 대신 데이타의 고정된 스냅샷을 시간으로 나타내는 데 그칩니다. 외환 관리 등과 관련된 일부 애플리케이션에서는 값 데이타의 변경 내용을 두 시점에서가 아닌 일정 기간으로 확인해야 합니다. Oracle Database 10g에는 Flashback 버전 질의 기능이 있어 이러한 작업을 쉽고 편리하게 수행할 수 있습니다.
테이블 변경 내용 질의
이 예에서는 은행의 외환 관리 애플리케이션을 사용했습니다. 데이타베이스에는 특정 시간의 환율을 기록하는 RATES라는 테이블이 있습니다.
SQL> desc rates
Name Null? Type
----------------- -------- ------------
CURRENCY VARCHAR2(4)
RATE NUMBER(15,10)
이 테이블에는 CURRENCY 열에 표시된 기타 여러 통화에 대한 US 달러의 환율이 표시됩니다. 재무 서비스 업계에서는 환율을 단순히 변경될 때마다 업데이트하는 대신 지속적인 기록으로 관리합니다. 이 접근방법은 은행 거래가 “이미 지난 시간”에 이루어져 송금으로 인한 시간상의 손실을 수용할 수 있기 때문에 채택되는 것입니다. 예를 들어, 오전 10:12에 이뤄지지만 오전 9:12에 발효된 거래는 현재 시간이 아닌 오전 9:12의 환율이 적용됩니다.
지금까지는 환율 기록 테이블을 생성해 환율 변경 내용을 저장한 후 해당 테이블에 기록이 있는지 질의할 수 밖에 없었습니다. 이 밖에도 RATES 테이블 자체에 특정 환율이 적용되는 시작 시간과 종료 시간을 기록하는 방법이 있었습니다. 변경 내용이 발생하면 기존 행의 END_TIME 열이 SYSDATE로 업데이트되며 END_TIME이 NULL인 새 환율과 함께 행이 새로 삽입되는 것입니다.
하지만 Oracle Database 10g에는 Flashback 버전 질의 기능이 있어 기록 테이블을 유지하거나 시작 및 종료 시간을 저장할 필요가 없습니다. 대신 이 기능을 사용하면 추가로 설정하지 않고도 과거의 특정 시간에 해당하는 행의 값을 가져올 수 있습니다.
예를 들어, 일상 업무를 수행하는 DBA가 환율을 여러 번 업데이트하거나, 때로는 행을 삭제하고 다시 삽입하기도 한다고 가정합니다.
insert into rates values ('EURO',1.1012);
commit;
update rates set rate = 1.1014;
commit;
update rates set rate = 1.1013;
commit;
delete rates;
commit;
insert into rates values ('EURO',1.1016);
commit;
update rates set rate = 1.1011;
commit;
이러한 일련의 작업이 끝나면 DBA는 다음과 같은 RATE 열의 현재 커밋된 값을 얻게 됩니다.
SQL> select * from rates;
CURR RATE
---- ----------
EURO 1.1011
이 결과에는 행을 처음 생성했을 때부터 발생한 모든 변경 내용이 아닌 RATE의 현재 값이 표시됩니다. 따라서 Flashback 질의를 사용하면 해당 시점의 값을 검색할 수 있지만, 여기서 보다 핵심적인 의도는 단순히 특정 시점에 얻은 일련의 스냅샷이 아니라 캠코더를 통해 변경 내용을 기록하는 것 같이 변경 내용의 감사추적(Audit Trail)을 구축하려는 것입니다.
다음 질의에는 테이블의 변경 내용이 나와 있습니다.
select versions_starttime, versions_endtime, versions_xid,
versions_operation, rate
from rates versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME
/
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V RATE
---------------------- ---------------------- ---------------- - ----------
01-DEC-03 03.57.12 PM 01-DEC-03 03.57.30 PM 0002002800000C61 I 1.1012
01-DEC-03 03.57.30 PM 01-DEC-03 03.57.39 PM 000A000A00000029 U 1.1014
01-DEC-03 03.57.39 PM 01-DEC-03 03.57.55 PM 000A000B00000029 U 1.1013
01-DEC-03 03.57.55 PM 000A000C00000029 D 1.1013
01-DEC-03 03.58.07 PM 01-DEC-03 03.58.17 PM 000A000D00000029 I 1.1016
01-DEC-03 03.58.17 PM 000A000E00000029 U 1.1011
행을 삭제하고 다시 삽입했더라도 여기에 행의 모든 변경 내용이 표시됩니다. VERSION_OPERATION 열에는 행에서 수행한 작업(삽입/업데이트/삭제)이 나타나며, 이를 위한 모든 과정이 기록 테이블이나 추가 열 없이 이뤄집니다.
위의 질의에서 versions_starttime, versions_endtime, versions_xid, versions_operation 열은 ROWNUM, LEVEL 같이 친숙한 열과 유사한 의사(Pseduo) 열입니다. VERSIONS_STARTSCN 및 VERSIONS_ENDSCN 같은 다른 의사 열에는 해당 시간의 시스템 변경 번호(SCN)가 표시됩니다. 또한 versions_xid 열에는 행을 변경한 트랜잭션 식별자가 표시됩니다. 트랜잭션의 상세 내역은 FLASHBACK_TRANSACTION_QUERY 뷰에 나와 있으며, 여기서 XID 열에는 트랜잭션 ID가 표시됩니다. 예를 들어, 위에서 VERSIONS_XID 값인 000A000D00000029를 사용하면 UNDO_SQL 값에 실제 문이 표시됩니다.
SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '000A000D00000029';
UNDO_SQL
----------------------------------------------------------------------------
insert into "ANANDA"."RATES"("CURRENCY","RATE") values ('EURO','1.1013');
이 뷰에는 실제 문 외에도 커밋의 타임 스탬프와 SCN을 비롯해 질의 시작 시의 SCN과 타임 스탬프가 표시됩니다.
기간 내 변경 내용 확인
이제 이러한 정보를 효과적으로 사용하는 방법에 대해 알아보도록 하겠습니다. 오후 3:57:54의 RATE 열 값을 확인해야 한다고 가정하면 다음과 같이 실행할 수 있습니다.
select rate, versions_starttime, versions_endtime
from rates versions
between timestamp
to_date('12/1/2003 15:57:54','mm/dd/yyyy hh24:mi:ss')
and to_date('12/1/2003 16:57:55','mm/dd/yyyy hh24:mi:ss')
/
RATE VERSIONS_STARTTIME VERSIONS_ENDTIME
---------- ---------------------- ----------------------
1.1011
이 질의는 Flashback 질의와 유사합니다. 위의 예에서 시작 및 종료 시간은 NULL로 해당 시간 간격 동안 환율이 변하지 않았으며 시간 간격을 포함하고 있음을 나타냅니다. 또한 SCN을 사용하면 이전 버전 값을 확인할 수 있으며, SCN 번호는 의사 열인 VERSIONS_STARTSCN 및 VERSIONS_ENDSCN에서 가져옵니다. 다음은 이에 대한 예입니다.
select rate, versions_starttime, versions_endtime
from rates versions
between scn 1000 and 1001
/
키워드 MINVALUE 및 MAXVALUE를 사용하면 실행 취소 세그먼트의 모든 변경 내용이 표시됩니다. 특정 날짜 또는 SCN 값을 범위의 끝점 중 하나로 지정하고 다른 끝점을 리터럴 MAXVALUE 또는 MINVALUE로 지정할 수도 있습니다. 예를 들어, 다음은 전체 범위가 아닌 오후 3:57:52부터의 변경 내용을 알려주는 질의입니다.
select versions_starttime, versions_endtime, versions_xid,
versions_operation, rate
from rates versions between timestamp
to_date('12/11/2003 15:57:52', 'mm/dd/yyyy hh24:mi:ss')
and maxvalue
order by VERSIONS_STARTTIME
/
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V RATE
---------------------- ---------------------- ---------------- - ----------
01-DEC-03 03.57.55 PM 000A000C00000029 D 1.1013
01-DEC-03 03.58.07 PM 01-DEC-03 03.58.17 PM 000A000D00000029 I 1.1016
01-DEC-03 03.58.17 PM 000A000E00000029 U 1.1011
최종 분석
Flashback 버전 질의는 틀을 벗어나 테이블 변경 내용의 짧은 휘발성 값(value) 감사(Audit)를 복제합니다. 이러한 이점을 통해 DBA는 과거의 특정 값이 아니라 일정 기간의 모든 변경 내용을 가져오므로 실행 취소 세그먼트(Undo Segment)의 데이타를 최대한 활용할 수 있습니다. 따라서 최대한으로 사용할 수 있는 버전은 UNDO_RETENTION 매개변수에 달려있다고 하겠습니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 두번째
얼마나 더 걸리나요?: 롤백 모니터링
롤백 작업 시간의 정확한 예측
아직 멀었나요? 얼마나 더 걸리죠?
귀에 익은 말들입니까? 이런 질문은 아이들이 좋아하는 테마 공원에 가는 도중 갈수록 횟수를 더하며 끊임없이 뒷좌석에서 들려올 수 있습니다. 이럴 때, 앞으로 정확히 얼마나 더 걸릴지 말해주거나 적어도 그 답을 혼자만이라도 알고 있다면 낫지 않겠습니까?
긴 실행 트랜잭션을 롤백했을 때도 이와 마찬가지로 여러 사용자들이 바짝 따라다니며 같은 질문을 하는 경우가 많습니다. 롤백이 진행되면서 트랜잭션에 잠금이 발생하고 일반적인 처리 성능이 저하되므로 이러한 질문을 하는 것은 당연합니다. Oracle 9i Database 이전 버전에서는 다음 질의를 실행하면,
SELECT USED_UREC
FROM V$TRANSACTION;
현재 트랜잭션에서 사용하는 실행 취소 레코드 수를 반환하며, 반복해서 실행하면 롤백 프로세스가 진행되면서 실행 취소 레코드가 해제되므로 계속해서 줄어든 값이 표시됩니다. 그런 다음 일정 간격의 스냅샷을 얻어 비율을 계산한 후 결과를 추정하여 종료 시간을 예측할 수 있습니다.
V$TRANSACTION 뷰에 START_TIME이라는 열이 있지만, 이 열에는 전체 트랜잭션의 시작 시간만 표시됩니다(즉, 롤백 실행 전). 따라서 실제로 롤백을 실행한 시간을 알 수 있는 방법은 추정뿐입니다.
트랜잭션 롤백을 위한 통계 확장
Oracle Database 10g에서는 이 과제를 간단히 수행할 수 있습니다. 트랜잭션을 롤백하면 이벤트가 V$SESSION_LONGOPS 뷰에 기록되어 긴 실행 트랜잭션을 표시합니다. 롤백을 위한 프로세스가 6초 이상 걸리는 경우 레코드가 뷰에 나타납니다. 롤백의 실행이 끝나면 모니터 스크린을 가리고도 다음 질의를 실행할 수 있을 것입니다.
select time_remaining
from v$session_longops
where sid = ;
이제 이 V$SESSION_LONGOPS 뷰가 얼마나 중요한지 알았으니 이 뷰의 다른 기능들에 대해 살펴보도록 하겠습니다. Oracle Database 10g 이전 버전에도 이 뷰가 있지만 롤백 트랜잭션 정보는 캡처되지 않았습니다. 모든 열을 읽기 쉬운 방식으로 표시하기 위해 Tom Kyte가 AskTom.com에 설명한 PRINT_TABLE 함수를 사용하겠습니다. 이 프로시저는 열을 일반적인 행이 아닌 테이블로만 표시합니다.
SQL> set serveroutput on size 999999
SQL> exec print_table('select * from v$session_longops where sid = 9')
SID : 9
SERIAL# : 68
OPNAME : Transaction Rollback
TARGET :
TARGET_DESC : xid:0x000e.01c.00000067
SOFAR : 10234
TOTALWORK : 20554
UNITS : Blocks
START_TIME : 07-dec-2003 21:20:07
LAST_UPDATE_TIME : 07-dec-2003 21:21:24
TIME_REMAINING : 77
ELAPSED_SECONDS : 77
CONTEXT : 0
MESSAGE : Transaction Rollback: xid:0x000e.01c.00000067 :
10234 out of 20554 Blocks done
USERNAME : SYS
SQL_ADDRESS : 00000003B719ED08
SQL_HASH_VALUE : 1430203031
SQL_ID : 306w9c5amyanr
QCSID : 0
이제 이 열들을 하나하나 자세히 살펴보도록 하겠습니다. 뷰에는 이전 세션의 모든 긴 실행 작업 기록이 포함되어 있으므로 세션에는 긴 실행 작업이 하나 이상일 수 있습니다. OPNAME 열에는 이 레코드가 “트랜잭션 롤백”용이라고 명시되어 있어 올바른 작업 방향으로 이끌어줍니다. TIME_REMAINING 열은 이전에 기술한 예측 잔여 시간을 초 단위로 표시하며, ELAPSED_SECONDS 열에는 현재까지 사용된 시간이 표시됩니다.
그렇다면 이 테이블에 어떻게 예측 잔여 시간을 나타낼까요? 단서는 총 수행 작업량을 나타내는 TOTALWORK 열과 현재까지 수행한 작업량을 나타내는 SOFAR 열에 있습니다. 작업 단위는 UNITS 열에 나와 있습니다. 여기서는 블록에 들어 있으므로 현재까지 20,554개의 블록 중 총 10,234개를 롤백한 것입니다. 또한 현재까지의 작업 소요 시간은 77초이므로 잔여 블록의 롤백 시간은 다음과 같이 구합니다.
77 * ( 10234 / (20554-10234) ) ? 77 seconds
반드시 이러한 경로를 통해서만 수치를 얻어야 하는 것은 아니지만 가장 명확한 방법입니다. 마지막으로 LAST_UPDATE_TIME 열에는 뷰 내용이 통용되는 시간이 표시되어 결과를 보다 쉽게 해석할 수 있도록 해줍니다.
SQL 문
또 하나의 중요하면서도 새로운 정보는 롤백 중인 SQL 문의 식별자입니다. 이전에는 롤백 중인 SQL 문을 가져오기 위해 SQL_ADDRESS 및 SQL_HASH_VALUE를 사용했습니다. 새로운 열인 SQL_ID는 아래에서처럼 V$SQL 뷰의 SQL_ID에 해당됩니다.
SELECT SQL_TEXT
FROM V$SQL
WHERE SQL_ID = ;
이 질의를 실행하면 롤백된 문을 반환하므로 SQL 문의 주소 및 해시 값과 함께 추가 검사를 제공합니다.
병렬 인스턴스 복구
DML 작업이 병렬이었으면 QCSID 열에 병렬 질의 서버 세션의 SID가 표시됩니다. 인스턴스 복구 및 실패한 트랜잭션의 후속 복구 과정 같은 병렬 롤백을 실행하는 경우 대개 이 정보를 유용하게 사용할 수 있습니다.
예를 들어, 대규모 업데이트를 수행하는 도중 인스턴스가 비정상적으로 종료되었다고 가정합시다. 인스턴스가 나타나면 실패한 트랜잭션이 롤백됩니다. 병렬 복구를 위한 초기화 매개변수 값을 사용할 수 있는 경우 롤백이 정규 트랜잭션 롤백에서 발생하므로 직렬이 아닌 병렬로 이뤄집니다. 그런 다음 롤백 프로세스의 완료 시간을 예측합니다.
V$FAST_START_TRANSACTIONS 뷰에는 실패한 트랜잭션을 롤백하기 위해 발생한 트랜잭션이 표시됩니다. 이와 유사한 뷰인 V$FAST_START_SERVERS에는 롤백에 실행되는 병렬 질의 서버의 수가 나와 있습니다. 이전 버전에서는 이러한 두 개의 뷰가 있지만, 트랜잭션 식별자를 나타내는 새로운 XID 열이 추가되어 조인이 더 수월해졌습니다. Oracle9i Database 이전에서는 세 개 열(USN ? 실행 취소 번호, SLT ? 실행 취소 세그먼트 내의 슬롯 번호 및 SEQ ? 시퀀스 번호)의 뷰에 조인해야 했습니다. 또한 상위 집합은 PARENTUSN, PARENTSLT 및 PARENTSEQ에 표시되었습니다. 하지만 Oracle Database 10g에서는 XID 열의 뷰에만 조인하면 되고 상위 XID는 알기 쉽게 PXID로 표시됩니다.
가장 유용한 정보는 V$FAST_START_TRANSACTIONS 뷰의 RCVSERVERS 열에 나와 있습니다. 병렬 롤백을 진행하면 이 열에 병렬 질의 서버의 수가 나타나며, 이를 통해 다음과 같이 시작된 병렬 질의 프로세스의 수를 확인할 수 있습니다.
select rcvservers from v$fast_start_transactions;
출력에 1로만 나타나면 트랜잭션이 가장 비효율적인 방식인 SMON 프로세스를 통해 직렬로 롤백되고 있는 것입니다. 이 경우 초기화 매개변수 RECOVERY_PARALLELISM을 0과 1을 제외한 값으로 수정한 다음 병렬 롤백 인스턴스를 다시 시작할 수 있습니다. 그런 다음, ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH를 실행하면 CPU 수의 네 배나 되는 병렬 서버를 생성할 수 있습니다.
위의 질의 출력이 1을 제외한 임의의 값이 표시되면 병렬 롤백이 이뤄지는 것입니다. 이 경우 동일한 뷰(V$FAST_START_TRANSACTIONS)를 질의하여 상위 및 하위 트랜잭션(상위 트랜잭션 ID ? PXID 및 하위 트랜잭션 ID ? XID)을 가져올 수 있습니다. 또한 XID는 이 뷰와 V$FAST_START_SERVERS를 조인하는 데 사용하여 상세 내역을 추가로 가져올 수 있습니다.
결론
요컨대 Oracle Database 10g에서 긴 실행 트랜잭션을 롤백할 때는 병렬 인스턴스 복구 세션이나 사용자 실행 롤백 문으로 표시한 다음, V$SESSION_LONGOPS 뷰를 살펴보고 향후 소요 시간의 결과를 예측하기만 하면 됩니다. 이제 테마 공원에 도착하는 시간만 예측할 수 있으면 되겠군요!
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 세번째
손쉬운 이름 변경: 향상된 테이블스페이스 관리
Sparser인 SYSTEM, 사용자 기본 테이블스페이스 정의 지원, 새로운 SYSAUX 및 이름 바꾸기 등으로 수월해진 테이블스페이스 관리
누구나 SYSTEM 테이블스페이스에 SYS 및 SYSTEM을 제외한 세그먼트를 생성하면서 좌절감에 머리를 쥐어 뜯으며 고민한 경험이 있을 것입니다.
Oracle9i Database 이전 버전에서는 사용자를 생성할 때 DEFAULT TABLESPACE를 명시하지 않으면 기본값이 SYSTEM 테이블스페이스로 설정되었습니다. 사용자가 세그먼트를 생성하는 동안 테이블스페이스를 명시적으로 지정하지 않는 경우, 명시적으로 부여 받은 것이든 시스템 권한 UNLIMITED TABLESPACE를 통한 것이든 사용자가 테이블스페이스 할당량을 갖고 있으면 SYSTEM에 생성되었습니다. Oracle9i에서는 DBA가 명시적인 임시 테이블스페이스 절 없이 생성된 모든 사용자에 대해 기본 임시 테이블스페이스를 지정하도록 하여 이 문제를 어느 정도 해결했습니다.
Oracle Database 10g에서도 이와 유사하게 사용자에게 기본 테이블스페이스를 지정할 수 있습니다. 우선, 데이타베이스를 생성하는 과정에 CREATE DATABASE 명령은 DEFAULT TABLESPACE 절을 포함할 수 있습니다. 데이타베이스의 생성이 끝나면 다음을 실행하여 기본 테이블스페이스를 만들 수 있습니다.
ALTER DATABASE DEFAULT TABLESPACE ;
DEFAULT TABLESPACE 절 없이 생성된 모든 사용자는 기본값으로 을 갖게 됩니다. 기본 테이블스페이스는 이 ALTER 명령을 사용해 언제든 변경하여 다른 위치의 기본값으로 다른 테이블스페이스를 지정할 수 있습니다.
여기서 중요한 점은 일부 사용자에 대해 다른 어떤 항목이 명시적으로 지정되어 있어도 이전 테이블스페이스와 함께 모든 사용자의 기본 테이블스페이스가 으로 변경된다는 것입니다. 예를 들어, 사용자 생성 도중 사용자 USER1 및 USER2의 기본 테이블스페이스를 각각 TS1 및 TS2로 명시적으로 지정했다고 가정합니다. 데이타베이스의 현재 기본 테이블스페이스는 TS2지만, 나중에는 데이타베이스의 기본 테이블스페이스가 TS1으로 변경됩니다. USER2의 기본 테이블스페이스를 TS2로 명시적으로 지정했다 하더라도 TS1으로 바뀌게 되므로 이러한 부작용을 염두에 둬야 합니다!
데이타베이스 생성 과정에 기본 테이블스페이스를 지정하지 않으면 기본값이 SYSTEM으로 설정됩니다. 하지만 기존 데이타베이스의 기본 테이블스페이스는 어떻게 알 수 있을까요? 우선, 다음 질의를 실행합니다.
SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';
DATABASE_PROPERTIES 뷰에는 기본 테이블스페이스 외에도 기본 임시 테이블스페이스, 전역 데이타베이스 이름, 시간대 등과 같은 몇 가지 매우 중요한 정보가 표시됩니다.
중요하지 않은 스키마의 기본 테이블스페이스
인텔리전트 에이전트 사용자 DBSNMP 및 데이타 마이닝 사용자 ODM 같은 여러 스키마는 사용자 작업과 직접적인 관련이 없지만 데이타베이스 무결성을 위해 나름대로 중요한 역할을 합니다. 이러한 스키마의 일부는 기본 테이블스페이스가 SYSTEM인데, 이는 해당 특수 테이블스페이스 내에서 객체가 확산되는 또 다른 이유이기도 합니다.
Oracle Database 10g에는 이러한 스키마의 객체를 보유하는 SYSAUX라는 새로운 테이블스페이스가 도입되었습니다. 이 테이블스페이스는 데이타베이스 생성 도중 자동으로 생성되며 지역적으로 관리됩니다. 또한 데이타 파일 이름만 유일하게 변경할 수 있습니다.
이 접근방법은 SYSTEM이 손상되어 전체 데이타베이스를 복구해야 할 때 복구를 지원합니다. 데이타베이스는 계속 실행하면서 SYSAUX의 객체를 일반 사용자 객체로 복구할 수 있습니다.
하지만 SYSAUX에 있는 이들 스키마의 일부를 다른 테이블스페이스로 옮겨야 한다면 어떻게 할까요? 크기가 늘어나 결국에는 테이블스페이스를 꽉 채우는 일이 빈번한 LogMiner에 사용되는 객체를 예로 들어봅시다. 관리 효율을 높이기 위해 이를 저마다의 테이블스페이스로 옮기는 방법을 고려할 수도 있을 것입니다. 하지만 이것이 최선의 방법일까요?
DBA라면 이러한 특수 객체를 옮기기 위한 올바른 프로시저를 알고 있어야 합니다. 다행히도 Oracle Database 10g에는 이러한 추측 작업을 수행하는 새로운 뷰가 있습니다. 이 V$SYSAUX_OCCUPANTS 뷰에는 SYSAUX 테이블스페이스에 있는 스키마의 이름, 설명, 현재 사용 공간 그리고 이동 방법이 나와 있습니다.
여기서 LogMiner가 어떻게 분명히 7,488KB를 차지하고 있는 것으로 표시되는지에 주목합니다. LogMiner는 SYSTEM 스키마에 속해 있으며 객체를 이동하려면 패키지 프로시저 SYS.DBMS_LOGMNR_D.SET_TABLESPACE를 실행합니다. 하지만 STATSPACK 객체의 경우 뷰에 엑스포트/임포트 접근방법을 사용하는 것이 좋으며 Streams에는 이동 프로시저가 없으므로 SYSAUX 테이블스페이스에서 이를 쉽게 옮길 수 없습니다. MOVE_PROCEDURE 열에는 SYSAUX에 기본적으로 상주하는 거의 모든 툴에 대한 올바른 이동 프로시저가 표시됩니다. 이동 프로시저는 역방향으로도 사용하여 객체를 다시 SYSAUX 테이블스페이스로 가져올 수 있습니다.
테이블스페이스 이름 바꾸기
데이타 웨어하우스 환경에서는 일반적으로 데이타 마트 아키텍처가 데이타베이스 사이에서 테이블스페이스를 이동합니다. 하지만 원본 및 대상 데이타베이스는 테이블스페이스 이름이 서로 달라야 합니다. 이름이 같은 테이블스페이스가 두 개이면 대상 테이블스페이스의 세그먼트를 다른 테이블스페이스로 옮기고 테이블스페이스를 다시 생성해야 하는데 말처럼 쉽지가 않습니다.
Oracle Database 10g에는 편리한 솔루션이 있어 영구 또는 임시 여부에 관계 없이 기존 테이블스페이스(SYSTEM 및 SYSAUX 제외)의 이름을 다음 명령을 사용해 간단히 변경할 수 있습니다.
ALTER TABLESPACE RENAME TO ;
이 기능은 아카이브 프로세스에도 유용하게 사용할 수 있습니다. 매출 기록을 관리하기 위해 범위로 분할된 테이블이 있으며, 매월의 파티션은 해당 월의 이름을 따 명명된 테이블스페이스에 있습니다. 예를 들어, 1월의 파티션에는 JAN이라는 이름이 지정되며 JAN으로 명명된 테이블스페이스에 상주합니다. 보존 정책 기간은 12개월입니다. 따라서 2004년 1월에 2003년 1월의 데이타를 아카이브할 수 있게 되는 것입니다. 대략적인 작업 과정은 다음과 유사한 형태가 됩니다.
1. ALTER TABLE EXCHANGE PARTITION을 사용해 파티션 JAN에서 독립형 테이블 JAN03을 생성합니다.
2. 테이블스페이스 이름을 JAN03으로 변경합니다.
3. 테이블스페이스 JAN03에 설정된 이동 가능한 테이블스페이스를 생성합니다.
4. 테이블스페이스 JAN03의 이름을 다시 JAN으로 변경합니다.
5. 비어 있는 파티션을 다시 테이블로 교환합니다.
1, 2, 4 및 5단계는 순조롭게 진행되며 리두 및 실행 취소 공간 같은 리소스를 과도하게 소모하지 않습니다. 3단계는 단순히 파일을 복사하고 JAN03의 데이타 딕셔너리만 엑스포트하면 되므로 마찬가지로 매우 간단한 프로세스입니다. 이전에 아카이브한 파티션을 다시 유효화해야 하는 경우, 프로시저는 동일한 프로세스를 반대로 수행하는 것만큼 간단합니다.
Oracle Database 10g는 이러한 이름 바꾸기를 처리하는 방식에 있어 상당히 지능적입니다. UNDO로 사용되는 테이블스페이스 또는 기본 임시 테이블스페이스의 이름을 변경하는 경우 혼동이 발생할 수 있습니다. 하지만 데이타베이스가 필요한 레코드를 자동으로 조정하여 변경 내용을 반영합니다. 예를 들어, 기본 테이블스페이스 이름을 USERS에서 USER_DATA로 변경하면 DATABASE_PROPERTIES가 자동으로 변경됩니다. 변경에 앞서 다음 질의가
select property_value from database_properties
where property_name = 'DEFAULT_PERMANENT_TABLESPACE';
USERS를 반환합니다. 다음 문을 실행하고 나면
alter tablespace users rename to user_data;
USER_DATA에 대한 모든 참조가 USER_DATA로 변경되었므로 위의 질의가 USER_DATA를 반환합니다.
기본 임시 테이블스페이스를 변경하는 방법도 이와 동일합니다. UNDO 테이블스페이스 이름을 변경하더라도 다음과 같이 SPFILE에 변경을 트리거합니다.
SQL> select value from v$spparameter where name = 'undo_tablespace';
VALUE
--------
UNDOTBS1
SQL> alter tablespace undotbs1 rename to undotbs;
Tablespace altered.
SQL> select value from v$spparameter where name = 'undo_tablespace';
VALUE
--------
UNDOTBS
결론
객체 처리 기능은 최근의 여러 Oracle 버전을 거치면서 꾸준히 향상되었습니다. Oracle8i에는 한 테이블스페이스에서 다른 테이블스페이스로의 테이블 이동이 도입되었으며, Oracle 9i Database R2는 열 이름 변경 기능을 갖추게 되었습니다. 그리고 지금은 테이블스페이스 자체의 이름을 변경할 수 있는 수준에 이르고 있습니다. 또한 이처럼 기능이 향상되면서 데이타 웨어하우스 또는 마트 환경 등에서 DBA의 작업 부담을 크게 덜어주고 있습니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 네번째
한층 강화된 엑스포트/임포트: Oracle Data Pump
Oracle Database 10g 유틸리티로 크게 향상된 데이타 이동 기능
지금까지 엑스포트/임포트 툴세트는 열악한 속도에 대한 불만에도 불구하고 최소한의 노력으로 여러 플랫폼에 데이타를 전송하기 위해 사용해 온 유틸리티였습니다. 임포트는 단순히 엑스포트 덤프 파일에서 각 레코드를 읽고 이를 일반적인 INSERT INTO 명령을 사용해 대상 테이블에 삽입하기만 하므로 임포트 프로세스가 느린 것은 그리 놀랄만한 일이 아닙니다.
이제 프로세스 속도가 월등히 향상된 Oracle Database 10g의 보다 새롭고 빠른 엑스포트/임포트 툴킷인 Oracle Data Pump, the newer and faster sibling of the export/import toolkit in Oracle Database 10g를 사용해 보십시오.
Data Pump는 엑스포트/임포트 프로세스의 전체 구성을 나타냅니다. 일반적인 SQL 문을 사용하는 대신 독점 API로 데이타를 현저하게 빠른 속도로 로드 및 언로드합니다. 제가 테스트해본 결과, 직접 모드의 엑스포트보다 성능이 10-15배 향상되었으며, 임포트 프로세스 성능도 5배 이상 증가했습니다. 또한 엑스포트 유틸리티와 달리 프로시저 같은 특정 유형의 객체만 추출할 수 있습니다.
Data Pump Export
이 새로운 유틸리티는 원래의 엑스포트인 exp와 구분하기 위해 expdp라고 합니다. 이 예에서는 Data Pump를 사용해 약 3GB 크기의 대형 테이블인 CASES를 엑스포트합니다. Data Pump는 서버 측에서 파일 조작을 사용하여 파일을 생성하고 읽으므로 디렉토리를 위치로 사용합니다. 여기서는 filesystem /u02/dpdata1을 사용해 덤프 파일을 유지할 예정입니다.
create directory dpdata1 as '/u02/dpdata1';
grant read, write on directory dpdata1 to ananda;
그리고 다음과 같이 데이타를 엑스포트합니다.
expdp ananda/abc123 tables=CASES directory=DPDATA1
dumpfile=expCASES.dmp job_name=CASES_EXPORT
이제 이 명령의 각 부분을 분석해 보겠습니다. 사용자 ID/암호 조합, 테이블 및 덤프 파일 매개변수는 말 그대로이므로 설명이 필요 없습니다. 원래의 엑스포트와 달리 파일이 클라이언트가 아닌 서버에 생성됩니다. 위치는 디렉토리 매개변수 값 DPDATA1로 지정되며, 이는 이전에 생성된 /u02/dpdata1을 가리킵니다. 또한 프로세스를 실행하면 서버의 디렉토리 매개변수로 지정된 위치에 로그 파일이 생성됩니다. 이 프로세스에는 기본적으로 DPUMP_DIR로 명명된 디렉토리가 사용되므로 DPDATA1 대신 생성할 수 있습니다.
위의 job_name 매개변수를 보면 원래의 엑스포트에 없는 특별한 항목이 하나 있습니다. 모든 Data Pump 작업은 작업(job)을 통해 이뤄집니다. Data Pump 작업은 DBMS 작업과 달리 주 프로세스를 대신해 데이타를 처리하는 단순한 서버 프로세스입니다. 마스터 제어 프로세스라고 하는 이 주 프로세스는 Advanced Queuing을 통해 이러한 작업 노력을 조정하는데, 이는 마스터 테이블이라고 하는 런타임 시 생성된 특수 테이블을 통해 이뤄집니다. 제시한 예에서 expdp를 실행하면서 사용자 ANANDA의 스키마를 검사하면 job_name 매개변수에 해당되는 CASES_EXPORT 테이블이 있음을 알 수 있습니다. expdp가 종료되면 이 테이블은 삭제됩니다.
엑스포트 모니터링
DPE(Data Pump Export)를 실행하면서 Control-C를 누르면 화면상에 메시지 표시를 중지하지만 프로세스 자체를 엑스포트하지는 않습니다. 대신 다음과 같이 DPE 프롬프트를 표시합니다. 이제 프로세스는 소위 “대화식” 모드에 들어갑니다.
Export>
이 접근방법에서는 DPE 작업에 여러 명령을 입력할 수 있습니다. 요약을 확인하려면 다음과 같이 프롬프트에 STATUS 명령을 사용합니다.
Export> status
Job: CASES_EXPORT
Operation: EXPORT
Mode: TABLE
State: EXECUTING
Degree: 1
Job Error Count: 0
Dump file: /u02/dpdata1/expCASES.dmp
bytes written = 2048
Worker 1 Status:
State: EXECUTING
Object Schema: DWOWNER
Object Name: CASES
Object Type: TABLE_EXPORT/TBL_TABLE_DATA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 1
Completed Rows: 4687818
하지만 이것은 상태 표시일 뿐이며 엑스포트는 백그라운드에서 실행되고 있습니다. 화면의 메시지를 계속 확인하려면 Export> 프롬프트에서 CONTINUE_CLIENT 명령을 사용합니다.
병렬 작업
PARALLEL 매개변수를 통해 엑스포트시 하나 이상의 스레드를 사용하면 작업 속도를 크게 개선할 수 있습니다. 스레드마다 개별 덤프 파일을 생성하므로 매개변수 dumpfile은 병렬화 만큼 많은 여러 항목을 갖게 됩니다. 또한 하나씩 명시적으로 입력하는 대신 다음과 같이 대체 문자를 파일 이름으로 지정할 수 있습니다.
expdp ananda/abc123 tables=CASES directory=DPDATA1
dumpfile=expCASES_%U.dmp parallel=4 job_name=Cases_Export
여기서 dumpfile 매개변수에 어떻게 대체 문자 %U가 생기는지 주목합니다. 이 대체 문자는 파일이 필요에 따라 생성되고 형식은 expCASES_nn.dmp이 됨을 나타내는데, 여기서 nn은 01에서 시작하며 필요에 따라 증가하게 됩니다.
병렬 모드에서는 상태 화면에 네 개의 작업자 프로세스가 표시됩니다. (기본 모드에서는 프로세스가 한 개만 표시됩니다.) 모든 작업자 프로세스가 데이타를 동시에 추출하며 진행률을 상태 화면에 표시합니다.
데이타베이스 파일 및 덤프 파일 디렉토리 파일 시스템에 액세스하려면 I/O 채널을 반드시 구분해야 합니다. 그렇지 않으면 Data Pump 작업의 유지와 관련된 오버헤드가 병렬 스레드의 이점을 뛰어넘어 성능을 저하시킬 수 있습니다. 병렬화는 테이블 수가 병렬 값보다 크고 테이블이 대규모인 경우에만 적용됩니다.
데이타베이스 모니터링
데이타베이스 뷰에서 실행되는 Data Pump 작업에 관해서도 자세한 정보를 확인할 수 있습니다. 작업을 모니터링하는 기본 뷰는 DBA_DATAPUMP_JOBS로 작업에서 실행되는 작업자 프로세스(DEGREE 열)의 수를 알려줍니다. 그 밖의 중요한 뷰에는 DBA_DATAPUMP_SESSIONS가 있는데, 이전 뷰 및 V$SESSION과 조인하면 주 포그라운드(Foreground) 프로세스 세션의 SID를 확인할 수 있습니다.
select sid, serial# from v$session s, dba_datapump_sessions d where s.saddr = d.saddr;
이 명령에는 포그라운드 프로세스의 세션이 표시됩니다. 경고 로그에서는 보다 유용한 정보를 얻을 수 있습니다. 프로세스가 시작되면 MCP 및 작업자 프로세스가 다음과 같이 경고 로그에 나타납니다.
kupprdp: master process DM00 started with pid=23, OS id=20530 to execute -
SYS.KUPM$MCP.MAIN('CASES_EXPORT', 'ANANDA');
kupprdp: worker process DW01 started with worker id=1, pid=24, OS id=20532 to execute -
SYS.KUPW$WORKER.MAIN('CASES_EXPORT', 'ANANDA');
kupprdp: worker process DW03 started with worker id=2, pid=25, OS id=20534 to execute -
SYS.KUPW$WORKER.MAIN('CASES_EXPORT', 'ANANDA');
경고 로그에는 Data Pump 작업을 위해 시작된 세션의 PID가 표시됩니다. 실제 SID는 이 질의를 사용해 확인합니다.
select sid, program from v$session where paddr in
(select addr from v$process where pid in (23,24,25));
PROGRAM 열에는 경고 로그 파일의 이름에 해당되는 프로세스 DM(마스터 프로세스) 또는 DW(작업자 프로세스)가 표시됩니다. SID 23 같은 작업자 프로세스에서 병렬 질의를 사용하는 경우, V$PX_SESSION 뷰에서 확인할 수 있습니다. 이 뷰에는 SID 23으로 표시된 작업자 프로세스에서 실행되는 모든 병렬 질의 세션이 나타납니다.
select sid from v$px_session where qcsid = 23;
V$SESSION_LONGOPS 뷰에서는 작업 완료에 걸리는 시간을 예측하는 또 다른 유용한 정보를 얻을 수 있습니다.
select sid, serial#, sofar, totalwork from v$session_longops where opname = 'CASES_EXPORT' and sofar != totalwork;
totalwork 열에는 총 작업량이 표시되는데, 이 중 현재까지 sofar 작업량을 완료했으므로 이를 통해 얼마나 더 시간이 걸릴지 예측할 수 있습니다.
Data Pump Import
하지만 Data Pump에서 가장 눈에 잘 띄는 부분은 데이타 임포트 성능입니다. 이전에 엑스포트된 데이타를 임포트하려면 다음을 사용합니다.
impdp ananda/abc123 directory=dpdata1 dumpfile=expCASES.dmp job_name=cases_import
임포트 프로세스의 기본 작업 방식은 테이블 및 연관된 모든 객체를 생성하고 테이블이 있는 상태에서 오류를 만들어 내는 것입니다. 기존 테이블에 데이타를 추가해야 하는 경우 위의 명령행에 TABLE_EXISTS_ACTION=APPEND를 사용할 수 있습니다.
DPE와 마찬가지로 프로세스 도중 Control-C를 누르면 DPI(Date Pump Import)의 대화식 모드를 표시하며 Import>가 프롬프트됩니다.
특정 객체 작업
한 사용자에서 특정 프로시저만 엑스포트하여 다른 데이타베이스나 사용자에 다시 생성해야 했던 경험이 있습니까? 기존의 엑스포트 유틸리티와 달리 Data Pump는 특정 유형의 객체만 엑스포트할 수 있습니다. 예를 들어, 다음 명령을 실행하면 테이블, 뷰 또는 함수 등은 제외하고 오로지 프로시저만 엑스포트할 수 있습니다.
expdp ananda/iclaim directory=DPDATA1 dumpfile=expprocs.dmp include=PROCEDURE
To export only a few specific objects--say, function FUNC1 and procedure PROC1--you could use
expdp ananda/iclaim directory=DPDATA1 dumpfile=expprocs.dmp
include=PROCEDURE:\"=\'PROC1\'\",FUNCTION:\"=\'FUNC1\'\"
이 덤프 파일은 소스의 백업으로 사용됩니다. 때로는 이를 사용해 DDL 스크립트를 생성하여 나중에 사용할 수도 있습니다. DDL 스크립트 파일을 생성하려면 SQLFILE이라고 하는 특수 매개변수를 사용합니다.
impdp ananda/iclaim directory=DPDATA1 dumpfile=expprocs.dmp sqlfile=procs.sql
이 명령은 DPDATA1로 지정된 디렉토리에 procs.sql로 명명된 파일을 생성하며 엑스포트 덤프 파일 내의 객체 스크립트가 들어 있습니다. 이 접근방법을 사용하면 다른 스키마에 원본을 보다 신속하게 생성할 수 있습니다.
INCLUDE 매개변수를 사용하면 객체가 덤프 파일에서 포함 또는 제외되도록 정의할 수 있습니다. 예를 들어, INCLUDE=TABLE:"LIKE 'TAB%'" 절을 사용하면 이름이 TAB로 시작하는 테이블만 엑스포트할 수 있습니다. 마찬가지로 INCLUDE=TABLE:"NOT LIKE 'TAB%'" 구문을 사용하면 TAB으로 시작하는 모든 테이블을 제외시킬 수 있습니다. 아니면 EXCLUDE 매개변수를 사용해 특정 객체를 제외시킬 수 있습니다.
Data Pump를 사용하면 외부 테이블로 테이블스페이스를 이동할 수도 있는데, 이렇게 하면 진행 중인 병렬화를 다시 정의하고 기존 프로세스에 테이블을 추가하는 등의 작업에 매우 효과적입니다. 다음 명령을 실행하면 Data Pump 엑스포트 유틸리티에서 사용 가능한 매개변수 목록이 생성됩니다.
expdp help=y
마찬가지로 impdp help=y 명령을 실행하면 DPI의 모든 매개변수가 표시됩니다.
Data Pump 작업을 실행하는 동안 DPE 또는 DPI 프롬프트에 STOP_JOB을 실행하여 작업을 일시 중지한 다음 START_JOB으로 다시 시작할 수 있습니다. 이 기능은 공간이 부족하여 계속하기 전에 정정해야 하는 경우 유용하게 사용할 수 있습니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 다섯번째
Flashback 테이블
실수로 삭제한 테이블을 손쉽게 다시 유효화할 수 있는 Oracle Database 10g의 Flashback 테이블 기능
매우 중요한 테이블을 실수로 삭제하여 즉시 복구해야 하는 상황은 생각보다 자주 일어나는 시나리오입니다. (때로는 이처럼 불운한 사용자가 DBA일 수도 있습니다!)
Oracle9i Database에는 Flashback 질의 옵션 개념이 도입되어 데이타를 과거의 시점에서부터 검색하지만, 테이블 삭제 같은 DDL 작업을 순간적으로 되돌릴 수는 없습니다. 이 경우 유일한 수단은 다른 데이타베이스에서 테이블스페이스 적시 복구를 사용한 다음, 엑스포트/임포트 또는 기타 메서드를 사용해 현재 데이타베이스에 테이블을 다시 생성하는 것입니다. 이 프로시저를 수행하려면 복제를 위해 다른 데이타베이스를 사용하는 것은 물론, DBA의 많은 노력과 귀중한 시간이 요구됩니다.
하지만 Oracle Database 10g의 Flashback 테이블 기능으로 들어가면 몇 개의 문만 실행하여 삭제된 테이블을 간단히 검색할 수 있습니다. 그럼, 지금부터 이 기능의 작동 원리에 대해 알아보도록 하겠습니다.
자유로운 테이블 삭제
먼저, 현재 스키마의 테이블을 확인해 봅시다.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------ ------- ----------
RECYCLETEST TABLE
그런 다음, 아래와 같이 고의로 테이블을 삭제합니다.
SQL> drop table recycletest;
Table dropped.
이제 테이블의 상태를 확인합니다.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE
RECYCLETEST 테이블이 사라졌지만 새 테이블인 BIN$04LhcpndanfgMAAAAAANPw==$0이 있다는 점에 주목합니다. 좀 더 자세히 설명하면 삭제된 테이블 RECYCLETEST가 완전히 사라지는 대신 시스템 정의 이름으로 이름이 변경된 것입니다. 이 테이블은 여전히 동일한 테이블스페이스에 있으며 원래 테이블과 구조도 동일합니다. 테이블에 인덱스 또는 트리거가 정의되어 있는 경우, 마찬가지로 테이블과 동일한 명명 규칙을 사용하여 이름이 변경됩니다. 프로시저 같은 종속적인 소스는 무효화되지만, 대신 원래 테이블의 트리거 및 인덱스가 이름이 변경된 테이블인 BIN$04LhcpndanfgMAAAAAANPw==$0에 들어가 삭제된 테이블의 완전한 객체 구조를 보존합니다.
테이블 및 연관된 객체는 PC에 있는 것과 유사한 “휴지통(RecycleBin)”이라고 하는 논리적 컨테이너에 들어갑니다. 하지만 이들 객체가 이전에 있던 테이블스페이스에서 옮겨지는 것은 아니며 계속 해당 테이블스페이스에서 공간을 차지하고 있습니다. 휴지통은 단순히 삭제된 객체의 목록을 만드는 논리적 구조입니다. 휴지통의 컨텐트를 확인하려면 SQL*Plus 프롬프트에서 다음 명령을 사용합니다(SQL*Plus 10.1이 있어야 함).
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ ------------------
RECYCLETEST BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE 2004-02-16:21:13:31
이렇게 하면 테이블의 원래 이름인 RECYCLETEST는 물론, 삭제된 후 생성된 새 테이블 이름과 동일한 휴지통에서의 새 이름이 표시됩니다. (참고: 정확한 이름은 플랫폼별로 다를 수 있습니다.) 테이블을 다시 유효화하기 위해서는 FLASHBACK TABLE 명령만 사용하면 됩니다.
SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;
FLASHBACK COMPLETE.
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RECYCLETEST TABLE
자, 테이블이 정말 간단히 유효화되지 않습니까? 지금 휴지통을 확인하면 비어 있습니다.
여기서 유의할 점은 테이블을 휴지통에 넣는다고 해도 원래 테이블스페이스의 공간이 제거되는 것은 아니라는 것입니다. 공간을 제거하려면 다음을 사용해 휴지통을 지워야 합니다.
PURGE RECYCLEBIN;
하지만 Flashback 기능을 사용하지 않고 테이블을 완전히 삭제하려면 어떻게 해야 할까요? 이 경우 다음을 사용하면 테이블을 영구적으로 삭제할 수 있습니다.
DROP TABLE RECYCLETEST PURGE;
이 명령을 실행하면 테이블 이름이 휴지통 이름으로 변경되는 것이 아니라, 10g 이전 버전에서처럼 영구적으로 삭제됩니다.
휴지통 관리
이 프로세스에서 테이블을 완전히 삭제하지 않아 테이블스페이스를 해제하지 않은 상태에서 삭제된 객체가 테이블스페이스의 모든 공간을 차지하면 어떤 일이 발생할까요?
답은 간단합니다. 그 같은 상황은 결코 발생하지 않습니다. 데이타 파일에 데이타를 추가할 공간을 확보해야 할 정도로 휴지통 데이타가 테이블스페이스로 꽉 차는 상황이 발생하면 테이블스페이스는 이른바 “공간 압축” 상태에 들어갑니다. 위의 시나리오에서 객체는 선입선출 방식으로 휴지통에서 자동으로 지워지며, 종속된 객체(예: 인덱스)는 테이블보다 먼저 제거됩니다.
마찬가지로 특정 테이블스페이스에 정의된 사용자 할당량에도 공간 압축이 발생할 수 있습니다. 테이블에는 사용 가능한 공간이 충분하지만 사용자는 할당된 공간이 부족할 수 있습니다. 이러한 상황에서 Oracle은 해당 테이블스페이스의 사용자에 속한 객체를 자동으로 지웁니다.
이 외에도 여러 가지 방법으로 휴지통을 수동으로 제어할 수 있습니다. 삭제한 후 휴지통에서 TEST라고 명명된 특정 테이블을 삭제하려면 다음을 실행하거나,
PURGE TABLE TEST;
아래와 같이 해당 휴지통 이름을 사용합니다.
PURGE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0";
이 명령을 실행하면 휴지통에서 TEST 테이블과 인덱스, 제약 조건 등과 같은 모든 종속 객체가 삭제되어 일정 공간을 확보하게 됩니다. 하지만 휴지통에서 인덱스를 영구적으로 삭제하려면 다음을 사용합니다.
purge index in_test1_01;
이렇게 하면 인덱스만 제거되며 테이블의 복사본은 휴지통에 남아 있습니다.
때로는 상위 레벨에서 지우는 것이 유용할 수도 있습니다. 예를 들어, 테이블스페이스 USERS의 휴지통에 있는 모든 객체를 지워야 한다면 다음을 실행합니다.
PURGE TABLESPACE USERS;
휴지통에서 해당 테이블스페이스의 특정 사용자만 지워야 하는 경우도 있습니다. 이 접근방법은 사용자가 많은 수의 과도 상태 테이블을 생성 및 삭제하는 데이타 웨어하우스 유형의 환경에 유용합니다. 다음과 같이 위의 명령을 수정해 지우기 작업을 특정 사용자만으로 제한할 수 있습니다.
PURGE TABLESPACE USERS USER SCOTT;
사용자 SCOTT는 다음 명령으로 휴지통을 지웁니다.
PURGE RECYCLEBIN;
DBA는 다음을 사용해 테이블스페이스의 모든 객체를 지울 수 있습니다.
PURGE DBA_RECYCLEBIN;
위에서 살펴본 것처럼 휴지통은 사용자의 특정한 요구에 맞는 다양한 방식으로 관리할 수 있습니다.
테이블 버전 및 Flashback
다음과 같이 동일한 테이블을 여러 번 생성 및 삭제해야 하는 경우도 흔히 발생합니다.
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (1);
COMMIT;
DROP TABLE TEST;
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (2);
COMMIT;
DROP TABLE TEST;
CREATE TABLE TEST (COL1 NUMBER);
INSERT INTO TEST VALUES (3);
COMMIT;
DROP TABLE TEST;
여기서 TEST 테이블을 순간적으로 되돌린다면 COL1 열의 값은 어떻게 될까요? 기존의 개념에서 보면 휴지통에서 테이블의 첫 번째 버전이 검색되고 COL1 열의 값은 1이 될 것입니다. 하지만 실제로는 첫 번째가 아닌 테이블의 세 번째 버전이 검색되므로 COL1 열의 값은 1이 아닌 3이 됩니다.
이 때 삭제된 테이블의 다른 버전을 검색할 수도 있습니다. 하지만 TEST 테이블이 존재하는 이러한 작업이 불가능한데, 이 경우 다음 두 가지를 선택할 수 있습니다. 다음과 같이 이름 바꾸기 옵션을 사용합니다.
FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST2;
FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;
이렇게 하면 테이블의 첫 번째 버전은 TEST1으로, 두 번째 버전은 TEST2로 다시 유효화됩니다. 또한 TEST1 및 TEST2에서 COL1의 값은 각각 1과 2가 됩니다. 또는 복원할 테이블의 특정 휴지통 이름을 사용합니다. 이를 위해 먼저 테이블의 휴지통 이름을 식별한 후 다음을 실행합니다.
FLASHBACK TABLE "BIN$04LhcpnoanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST2;
FLASHBACK TABLE "BIN$04LhcpnqanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST1;
이렇게 하면 삭제된 테이블의 두 가지 버전이 복원됩니다.
주의 사항
삭제 취소 기능을 사용하면 테이블의 이름이 원래대로 돌아가지만 인덱스 및 트리거 같은 연관된 객체는 그렇지 않으며 계속 휴지통 이름으로 남아 있습니다. 또한 뷰 및 프로시저 같이 테이블에 정의된 소스는 재컴파일되지 않으며 무효화된 상태로 남게 됩니다. 이러한 이전 이름들은 수동으로 검색한 다음 순간적으로 되돌린 테이블에 적용해야 합니다.
이 정보는 USER_RECYCLEBIN으로 명명된 뷰에서 관리됩니다. 테이블을 순간적으로 되돌리기 전에 다음 질의를 사용해 이전 이름을 검색합니다.
SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'RECYCLETEST')
AND ORIGINAL_NAME != 'RECYCLETEST';
OBJECT_NAME ORIGINAL_N TYPE
------------------------------ ---------- --------
BIN$04LhcpnianfgMAAAAAANPw==$0 IN_RT_01 INDEX
BIN$04LhcpnganfgMAAAAAANPw==$0 TR_RT TRIGGER
테이블을 순간적으로 되돌리면 RECYCLETEST 테이블의 인덱스 및 트리거에는 OBJECT_NAME 열에 나타난 이름이 지정됩니다. 위의 질의에서는 원래 이름을 사용해 객체의 이름을 다음과 같이 변경할 수 있습니다.
ALTER INDEX "BIN$04LhcpnianfgMAAAAAANPw==$0" RENAME TO IN_RT_01;
ALTER TRIGGER "BIN$04LhcpnganfgMAAAAAANPw==$0" RENAME TO TR_RT;
한가지 유의해야 할 예외는 비트맵 인덱스입니다. 비트맵 인덱스를 삭제하면 휴지통에 들어가지 않으므로 검색할 수 없습니다. 또한 뷰에서 제약 조건 이름을 검색할 수 없습니다. 따라서 이 인덱스의 이름은 다른 소스에서 변경해야 합니다.
Flashback 테이블의 다른 용도
Flashback Drop Table에는 테이블 삭제 작업을 되돌리는 것 외에도 다른 기능이 있습니다. Flashback 질의와 마찬가지로 이를 사용해 테이블을 다른 시점으로 다시 유효화하여 전체 테이블을 “이전” 버전으로 바꿀 수 있습니다. 예를 들어, 다음 문을 사용하면 테이블을 시스템 변경 번호(SCN) 2202666520으로 다시 유효화합니다.
FLASHBACK TABLE RECYCLETEST TO SCN 2202666520;
이 기능은 Oracle Data Pump 기술로 다른 테이블을 생성하고 Flashback으로 테이블을 해당 SCN의 데이타 버전으로 채운 다음, 원래의 테이블을 새 테이블로 바꿉니다. 테이블을 어느 정도까지 순간적으로 되돌릴 수 있는지 확인하려면 Oracle Database 10g의 버전 관리 기능을 사용합니다. (자세한 내용은 이 시리즈의 1주 부분을 참조하십시오.) 또한 Flashback 절에 SCN 대신 타임 스탬프를 지정할 수도 있습니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 여섯번째
Automatic Workload Repository
AWR을 이용하여 분석과 튜닝을 위한 데이타베이스 성능 통계정보와 메트릭을 수집하고, 데이타베이스에서 사용한 정확한 시간을 확인하거나 세션 정보를 저장할 수 있습니다.
데이타베이스에 성능에 관련한 문제가 생겼을 때, 귀하가 DBA로서 가장 먼저 취하는 조치는 무엇입니까? 아마도 문제에 일정한 패턴이 존재하는지 확인하는 것이 가장 일반적인 접근방법의 하나일 것입니다. “동일한 문제가 반복되는가?”, “특정한 시간대에만 발생하는가?”, 또는 “두 가지 문제에 연관성이 있는가?” 등의 질문을 먼저 제기해 봄으로써 보다 정확한 진단을 수행할 수 있습니다.
Oracle DBA들은 데이타베이스 운영에 관련한 통계정보를 수집하거나 성능 메트릭(metric)을 추출하기 위해 써드 파티 툴, 또는 직접 개발한 툴을 사용하고 있습니다. 이렇게 수집된 정보는, 문제 발생 이전과 이후의 상태를 비교하는 데 이용됩니다. 과거에 발생했던 이벤트들을 재현해 봄으로써 현재 문제를 다양한 관점에서 분석할 수 있습니다. 이처럼 관련 통계정보들을 지속적으로 수집하는 것은 성능 분석에서 매우 중요한 작업 중의 하나입니다.
오라클은 한동안 이를 위해 Statspack이라는 이름의 빌트-인 툴을 제공하기도 했습니다. Statspack은 상황에 따라 매우 유용하긴 했지만, 성능 관련 트러블슈팅 과정에서 요구되는 안정성이 결여되었다는 문제가 있었습니다. Oracle Database 10g는 성능 통계정보의 수집과 관련하여 그 기능이 비약적으로 향상된 Automatic Workload Repository(AWR)을 제공합니다. AWR은 데이타베이스와 함께 설치되며, 기본적인 통계정보뿐 아니라 통계정보로부터 유추된 메트릭(derived metric)도 함께 수집합니다.
간단하게 테스트해 보기
AWR을 이용한 새로운 기능들은 $ORACLE_HOME/rdbms/admin 디렉토리의 awrrpt.sql 스크립트를 실행하고, 수집된 통계정보와 메트릭 정보를 바탕으로 생성된 리포트를 확인함으로써 가장 쉽게 이해할 수 있습니다. awrrpt.sql 스크립트는 Statspack과 유사한 구조로 되어있습니다. 먼저 현재 저장된 AWR 스냅샷을 모두 표시한 후, 시간 간격 설정을 위한 입력값을 요구합니다. 출력은 두 가지 형태로 제공됩니다. 텍스트 포맷 출력은 Statspack 리포트와 유사하지만, AWR 리포지토리를 기반으로 하며 (디폴트로 제공되는) HTML 포맷을 통해 section/subsection으로 구분된 하이퍼링크를 제공하는 등 사용자 편의성을 강화하였다는 점에서 차이를 갖습니다. 먼저 awrrpt.sql 스크립트를 실행하여 리포트를 확인해 보시고, AWR의 기능에 대한.특성을 이해하시기 바랍니다.
구현 원리
이제 AWR의 설계방식과 구조에 대해 알아보기로 합시다. AWR은 수집된 성능관련 통계정보가 저장되며 이를 바탕으로 성능 메트릭을 제공함으로서 잠재적인 문제의 원인 추적을 가능하게 해주는 근간을 제공해 줍니다. Statspack의 경우와 달리, Oracle10g는 AWR을 활용하여 새로운 MMON 백그라운드 프로세스와, 여러 개의 슬레이브 프로세스를 통해 자동적으로 매시간별 스냅샷 정보를 수집합니다. 공간 절약을 위해, 수집된 데이타는 7일 후 자동으로 삭제됩니다. 스냅샷 빈도와 보관 주기는 사용자에 의해 설정 가능합니다. 현재 설정값을 보기 위해서는 아래와 같이 명령을 수행하면 됩니다:
select snap_interval, retention
from dba_hist_wr_control;
SNAP_INTERVAL RETENTION
------------------- -------------------
+00000 01:00:00.0 +00007 00:00:00.0
위의 실행결과는 스냅샷이 매 시간대 별로 수집되고 있으며 수집된 통계가 7일 동안 보관되고 있음을 보여주고 있습니다. 스냅샷 주기를 20분으로, 보관 주기를 2일로 변경하기 위해서는 아래와 같이 수행하면 됩니다. (매개변수는 분 단위로 표시됩니다.)
begin
dbms_workload_repository.modify_snapshot_settings (
interval => 20,
retention => 2*24*60
);
end;
AWR은 수집된 통계를 저장하기 위해 여러 개의 테이블을 사용합니다. 이 테이블들은 모두 SYS 스키마의 SYSAUX 테이블스페이스 내에 저장되어 있으며, WRM$_* 또는 WRH$_*의 네임 포맷을 갖습니다. WRM$_* 테이블은 수집 대상 데이타베이스 및 스냅샷에 관련한 메타데이타 정보를, WRH$_* 테이블은 실제 수집된 통계 정보를 저장하는데 사용됩니다. (예측하시는 바와 같이, H는 “historical”, M은 “metadata”의 약자를 의미합니다.) 이 테이블을 기반으로 DBA_HIST_라는 prefix를 갖는 여러 가지 뷰가 제공되고 있으며, 이 뷰들을 응용하여 자신만의 성능 분석 툴을 만들 수도 있습니다. 뷰의 이름은 테이블 이름과 직접적인 연관성을 갖습니다. 예를 들어 DBA_HIST_SYSMETRIC_SUMMARY 뷰는 WRH$_SYSMETRIC_SUMMARY 테이블을 기반으로 합니다.
AWR 히스토리 테이블은 Statspack에서는 수집되지 않았던 다양한 정보(테이블스페이스 사용 통계, 파일시스템 사용 통계, 운영체제 통계 등)를 제공합니다. 테이블의 전체 리스트는 아래와 같이 데이타 딕셔너리 조회를 통해 확인할 수 있습니다:
select view_name from user_views where view_name like 'DBA\_HIST\_%' escape '\';
DBA_HIST_METRIC_NAME 뷰는 AWR에 수집되는 주요 메트릭과 메트릭이 속한 그룹, 그리고 수집 단위(unit) 등을 정의하고 있습니다. DBA_HIST_METRIC_NAME 뷰의 레코드에 대한 조회 결과의 예가 아래와 같습니다:
DBID : 4133493568
GROUP_ID : 2
GROUP_NAME : System Metrics Long Duration
METRIC_ID : 2075
METRIC_NAME : CPU Usage Per Sec
METRIC_UNIT : CentiSeconds Per Second
위에서는 "초당 CPU 사용량(CPU Usage Per Sec)" 메트릭이 “100분의 1초(CentiSeconds Per Second)” 단위로 수집되고 있으며, 이 메트릭이 "System Metrics Long Duration” 그룹에 속함을 확인할 수 있습니다. 이 레코드를 DBA_HIST_SYSMETRIC_SUMMARY와 JOIN하여 실제 통계를 확인할 수 있습니다:
select begin_time, intsize, num_interval, minval, maxval, average, standard_deviation sd
from dba_hist_sysmetric_summary where metric_id = 2075;
BEGIN INTSIZE NUM_INTERVAL MINVAL MAXVAL AVERAGE SD
----- ---------- ------------ ------- ------- -------- ----------
11:39 179916 30 0 33 3 9.81553548
11:09 180023 30 21 35 28 5.91543912
... 후략 ...
위 조회 결과를 통해 백 분의 1초 단위로 CPU 자원이 어떻게 소비되고 있는지 확인할 수 있습니다. SD(standard deviation, 표준 편차) 값을 참조하면 계산된 평균 값이 실제 부하와 비교하여 얼마나 오차를 갖는지 분석 가능합니다. 첫 번째 레코드의 경우, 초당 백 분의 3초의 CPU 시간이 소모된 것으로 계산되었지만, 표준 편차가 9.81이나 되므로 계산된 3의 평균값이 실제 부하를 정확하게 반영하지 못하는 것으로 볼 수 있습니다. 반면 28의 평균값과 5.9의 표준 편차를 갖는 두 번째 레코드가 실제 수치에 더 가깝다고 볼 수 있습니다. 이러한 트렌드 분석을 통해 성능 메트릭과 환경 변수의 상관 관계를 보다 명확하게 이해할 수 있습니다.
통계의 활용
지금까지 AWR의 수집 대상이 어떻게 정의되는지 알아보았습니다. 이번에는 수집된 데이타를 어떻게 활용할 수 있는지 설명하기로 합니다.
성능 문제는 독립적으로 존재하는 경우가 거의 없으며, 대개 다른 근본적인 문제를 암시하는 징후로서 해석되는 것이 일반적입니다. 전형적인 튜닝 과정의 예를 짚어보기로 합시다: DBA가 시스템 성능이 저하되었음을 발견하고 wait에 대한 진단을 수행합니다. 진단 결과 “buffer busy wait”이 매우 높게 나타나고 있음을 확인합니다. 그렇다면 문제의 원인은 무엇일까요? 여러 가지 가능성이 존재합니다: 인덱스의 크기가 감당할 수 없을 만큼 커지고 있을 수도 있고, 테이블의 조밀도(density)가 너무 높아 하나의 블록을 메모리에 읽어 오는 데 요구되는 시간이 제한된 때문일 수도 있고, 그 밖의 다른 이유가 있을 수 있습니다. 원인이 무엇이든, 문제가 되는 세그먼트를 먼저 확인해 보는 것이 필요합니다. 문제가 인덱스 세그먼트에서 발생했다면, 리빌드 작업을 수행하거나 reverse key index로 변경하거나 또는 Oracle Database 10g에서 새로 제공하는 hash-partitioned index로 변경해 볼 수 있을 것입니다. 문제가 테이블에서 발생했다면, 저장 관련 매개변수를 변경해서 조밀도를 낮추거나, 자동 세그먼트 공간 관리(automatic segment space management)가 설정된 테이블스페이스로 이동할 수 있을 것입니다.
DBA가 실제로 사용하는 접근법은 일반적인 방법론, DBA의 경험 및 지식 등을 그 바탕으로 합니다. 만일 똑같은 일을 별도의 엔진이, 메트릭을 수집하고 사전 정의된 로직을 바탕으로 적용 가능한 방법을 추론하는 엔진이 대신해 준다면 어떨까요? DBA의 작업이 한층 쉬워지지 않을까요?
바로 이러한 엔진이 Oracle Database 10g에 새로 추가된 Automatic Database Diagnostic Monitor (ADDM)입니다. ADDM은 AWR이 수집한 데이타를 사용하여 결론을 추론합니다. 위의 예의 경우, ADDM은 buffer busy wait이 발생하고 있음을 감지하고, 필요한 데이타를 조회하여 wait이 실제로 발생하는 세그먼트를 확인한 후, 그 구조와 분포를 평가함으로써 최종적으로 해결책을 제시합니다. AWR의 스냅샷 수집이 완료될 때마다, ADDM이 자동으로 호출되어 메트릭을 점검하고 권고사항을 제시합니다. 결국 여러분은 데이타 분석 및 권고사항 제시를 담당하는 풀 타임 DBA 로봇을 하나 두고, 보다 전략적인 업무에 집중할 수 있게 된 셈입니다.
Enterprise Manager 10g 콘솔의 “DB Home” 페이지에서 ADDM의 권고사항과 AWR 리포지토리 데이타를 확인할 수 있습니다. AWR 리포트를 보려면, Administration->Workload Repository->Snapshot의 순서로 메뉴를 따라가야 합니다. ADDM의 자세한 기능은 향후 연재에서 소개하도록 하겠습니다.
특정 조건을 기준으로 알림 메시지를 생성하도록 설정하는 것도 가능합니다. 이 기능은 Server Generated Alert라 불리며, Advanced Queue에 푸시(push) 형태로 저장되고 리스닝 중인 모든 클라이언트에 전달되는 형태로 관리됩니다. Enterprise Manager 10g 역시 Server Generated Alert의 클라이언트의 하나로서 관리됩니다.
타임 모델 (Time Model)
성능 문제가 발생했을 때, 응답시간을 줄이기 위한 방법으로 DBA의 머릿속에 가장 먼저 떠오르는 것은 무엇일까요? 말할 필요도 없이, 문제의 근본원인을 찾아내어 제거하는 것이 최우선일 것입니다. 그렇다면 얼마나 많은 시간이 (대기가 아닌) 실제 작업에 사용되었는지 어떻게 확인할 수 있을까요? Oracle Database 10g는 여러 가지 자원에 관련한 실제 사용 시간을 확인하기 위한 타임 모델(time model)을 구현하고 있습니다. 전체 시스템 관련 소요 시간 통계는 V$SYS_TIME_MODEL 뷰에 저장됩니다. V$SYS_TIME_MODEL 뷰에 대한 쿼리 결과의 예가 아래와 같습니다.
STAT_NAME VALUE
------------------------------------- --------------
DB time 58211645
DB CPU 54500000
background cpu time 254490000
sequence load elapsed time 0
parse time elapsed 1867816
hard parse elapsed time 1758922
sql execute elapsed time 57632352
connection management call elapsed time 288819
failed parse elapsed time 50794
hard parse (sharing criteria) elapsed time 220345
hard parse (bind mismatch) elapsed time 5040
PL/SQL execution elapsed time 197792
inbound PL/SQL rpc elapsed time 0
PL/SQL compilation elapsed time 593992
Java execution elapsed time 0
bind/define call elapsed time 0
위에서 DB time이라는 통계정보는 인스턴스 시작 이후 데이타베이스가 사용한 시간의 누적치를 의미합니다. 샘플 작업을 실행한 다음 다시 뷰를 조회했을 때 표시되는 DB time의 값과 이전 값의 차이가 바로 해당 작업을 위해 데이타베이스가 사용한 시간이 됩니다. 튜닝을 거친 후 DB time 값의 차이를 다시 분석하면 튜닝을 통해 얻어진 성능 효과를 확인할 수 있습니다.
이와 별도로 V$SYS_TIME_MODEL 뷰를 통해 파싱(parsing) 작업 또는 PL/SQL 컴파일 작업에 소요된 시간 등을 확인할 수 있습니다. 이 뷰를 이용하면 시스템이 사용한 시간을 확인하는 것도 가능합니다. 시스템 / 데이타베이스 레벨이 아닌 세션 레벨의 통계를 원한다면 V$SESS_TIME_MODEL 뷰를 이용할 수 있습니다. V$SESS_TIME_MODEL 뷰는 현재 연결 중인 active/inactive 세션들의 통계를 제공합니다. 세션의 SID 값을 지정해서 개별 세션의 통계를 확인할 수 있습니다.
이전 릴리즈에서는 이러한 통계가 제공되지 않았으며, 사용자들은 여러 정보 소스를 참고해서 근사치를 추측할 수 밖에 없었습니다.
Active Session History
Oracle Database 10g의 V$SESSION에도 개선이 이루어졌습니다. 가장 중요한 변화로 wait 이벤트와 그 지속시간에 대한 통계가 뷰에 추가되어, V$SESSION_WAIT를 별도로 참조할 필요가 없게 되었다는 점을 들 수 있습니다. 하지만 이 뷰가 실시간 정보를 제공하므로, 나중에 다시 조회했을 때에는 중요한 정보가 이미 사라져 버리고 없을 수 있습니다. 예를 들어 wait 상태에 있는 세션이 있음을 확인하고 이를 조회하려 하면, 이미 wait 이벤트가 종료되어 버려 아무런 정보도 얻지 못하는 경우가 있을 수 있습니다.
또 새롭게 추가된 Active Session History(ASH)는 AWR과 마찬가지로 향후 분석 작업을 위해 세션 성능 통계를 버퍼에 저장합니다. AWR과 다른 점은, 테이블 대신 메모리가 저장 매체로 이용되며 V$ACTIVE_SESSION_HISTORY 등을 통해 조회된다는 사실입니다. 데이타는 1초 단위로 수집되며, 액티브 세션만이 수집 대상이 됩니다. 버퍼는 순환적인 형태로 관리되며, 저장 메모리 용량이 가득 차는 경우 오래된 데이타부터 순서대로 삭제됩니다. 이벤트를 위해 대기 중인 세션의 수가 얼마나 되는지 확인하려면 아래와 같이 조회하면 됩니다:
select session_id||','||session_serial# SID, n.name, wait_time, time_waited from v$active_session_history a, v$event_name n where n.event# = a.event#
위 쿼리는 이벤트 별로 대기하는 데 얼마나 많은 시간이 사용되었는지를 알려 줍니다. 특정 wait 이벤트에 대한 드릴다운을 수행할 때에도 ASH 뷰를 이용할 수 있습니다. 예를 들어, 세션 중 하나가 buffer busy wait 상태에 있는 경우 정확히 어떤 세그먼트에 wait 이벤트가 발생했는지 확인하는 것이 가능합니다. 이때 ASH 뷰의 CURRENT_OBJ# 컬럼과 DBA_OBJECTS 뷰를 조인하면 문제가 되는 세그먼트를 확인할 수 있습니다.
ASH 뷰는 그 밖에도 병렬 쿼리 서버 세션에 대한 기록을 저장하고 있으므로, 병렬 쿼리의 wait 이벤트를 진단하는 데 유용하게 활용됩니다. 레코드가 병렬 쿼리의 slave process로서 활용되는 경우, coordinator server session의 SID는 QC_SESSION_ID 컬럼으로 확인할 수 있습니다. SQL_ID 컬럼은 wait 이벤트를 발생시킨 SQL 구문의 ID를 의미하며, 이 컬럼과 V$SQL 뷰를 조인하여 문제를 발생시킨 SQL 구문을 찾아낼 수 있습니다. CLIENT_ID 컬럼은 웹 애플리케이션과 같은 공유 사용자 환경에서 클라이언트를 확인하는 데 유용하며, 이 값은 DBMS_SESSION.SET_IDENTFIER를 통해 설정 가능합니다.
ASH 뷰가 제공하는 정보의 유용성을 감안하면, AWR과 마찬가지로 이 정보들을 영구적인 형태의 매체에 저장할 필요가 있을 수도 있습니다. AWR 테이블을 MMON 슬레이브를 통해 디스크로 flush 할 수 있으며, 이 경우 DBA_HIST_ACTIVE_SESS_HISTORY 뷰를 통해 저장된 결과를 확인할 수 있습니다.
수작업으로 스냅샷 생성하기
스냅샷은 자동으로 수집되도록 디폴트 설정되어 있으며, 원하는 경우 온디맨드 형태의 실행이 가능합니다. 모든 AWR 기능은 DBMS_WORKLOAD_REPOSITORY 패키지에 구현되어 있습니다. 스냅샷을 실행하려면 아래와 같은 명령을 사용하면 됩니다:
execute dbms_workload_repository.create_snapshot
위 명령은 스냅샷을 즉각적으로 실행하여 그 결과를 table WRM$_SNAPSHOT 테이블에 저장합니다. 수집되는 메트릭의 수준은 TYPICAL 레벨로 설정됩니다. 더욱 자세한 통계를 원하는 경우 FLUSH_LEVEL 매개변수를 ALL로 설정하면 됩니다. 수집된 통계는 자동으로 삭제되며, 수작업으로 삭제하려는 경우 drop_snapshot_range() 프로시저를 실행하면 됩니다.
베이스라인
성능 튜닝 작업을 수행할 때에는 먼저 일련의 메트릭에 대한 베이스라인(baseline)을 수집하고 튜닝을 위한 변경 작업을 수행한 뒤, 다시 또 다른 베이스라인 셋을 수집하는 과정을 거치는 것이 일반적입니다. 이렇게 수집된 두 가지 셋을 서로 비교하여 변경 작업의 효과를 평가할 수 있습니다. AWR에서는 기존에 수집된 스냅샷을 통해 이러한 작업이 가능합니다. 예를 들어 매우 많은 자원을 사용하는 apply_interest라는 프로세스가 오후 1시부터 3시까지 실행되었고, 이 기간 동안 스냅샷 ID 56에서 59까지가 수집되었다고 합시다. 이 스냅샷들을 위해 apply_interest_1이라는 이름의 베이스라인을 아래와 같이 정의할 수 있습니다:
exec dbms_workload_repository.create_baseline (56,59,'apply_interest_1')
위 명령은 스냅샷 56에서 59까지를 ‘apply_interest_1’이라는 이름의 베이스라인으로 표시합니다. 기존에 설정된 베이스라인은 아래와 같이 확인합니다:
select * from dba_hist_baseline;
DBID BASELINE_ID BASELINE_NAME START_SNAP_ID END_SNAP_ID
---------- ----------- -------------------- ------------- -----------
4133493568 1 apply_interest_1 56 59
튜닝 과정을 거친 후, 또 다른 이름(예: apply_interest_2)의 베이스라인을 생성하여, 이 두 가지 베이스라인에 해당하는 스냅샷의 메트릭을 비교할 수 있습니다. 이처럼 비교 대상을 한정함으로써 성능 튜닝의 효과를 한층 향상시킬 수 있습니다. 분석이 끝나면 drop_baseline(); 프로시저로 베이스라인을 삭제할 수 있습니다 (이 때 스냅샷은 그대로 보존됩니다). 또 오래된 스냅샷이 삭제되는 과정에서, 베이스라인과 연결된 스냅샷은 삭제되지 않습니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 일곱번째
SQL*Plus의 향상된 기능
Oracle Database 10g의 SQL*Plus 툴에는 프롬프트, 파일 조작 기능 등 몇 가지 눈에 띄는 기능 개선이 추가되었습니다.
DBA가 하루에 가장 많이 사용하는 툴은 무엇일까요? 아마 GUI 대신 오래된 작업 방식을 고수하고 있는 본인과 같은 DBA들이라면 SQL*Plus 커맨드 라인 툴을 그 첫 번째로 꼽을 것입니다.
Oracle Database 10g에서 Enterprise Manager 10g의 기능이 한층 강화되었음에도 불구하고, 초심자와 숙련된 DBA 모두에게 있어 SQL*Plus는 앞으로도 오랫동안 유용하게 활용될 것입니다.
이번 연재에서는 SQL*Plus 10.1.0.2에 추가된 유용한 기능을 살펴 보고자 합니다. 이 강좌를 따라 해 보려면 Oracle Database 10g에 포함된 sqlplus를 사용해야 함을 주의하시기 바랍니다 (Oracle9i Database의 sqlplus을 사용해서 10g 데이타베이스에 접속해서는 안됩니다).
프롬프트의 활용
지금 나는 어디에 있는가? 그리고 나는 누구인가? 지금 형이상학적인 질문을 던지려는 것이 아닙니다. SQL*Plus 환경의 관점에서 사용자의 위치를 묻는 질문입니다. SQL*Plus가 지금까지 천편일률적으로 제공했던 SQL> 프롬프트는 사용자가 누구이고 어디에 연결되었는지에 대한 아무런 정보도 제공하지 않습니다. 이전 릴리즈에서는 프롬프트를 바꾸려면 복잡한 코딩이 필요했습니다. SQL*Plus 10.1.0.2는 아래와 같은 간단한 명령만으로 프롬프트를 바꿀 수 있습니다:
set sqlprompt "_user _privilege> "
그러면 SQL*Plus 프롬프트는 아래와 같은 형식으로 표시됩니다:
SYS AS SYSDBA>
위의 경우는 SYS 사용자가 SYSDBA로서 접속한 상황임을 의미합니다. 위에서 _user와 _privilege라는 두 개의 변수가 활용되는 방법을 참고하십시오. _user는 현재 사용자를 _privilege는 로그인에 사용되는 privilege를 뜻합니다.
이번에는 다른 방법을 써 봅시다. 아래는 프롬프트에 오늘 날짜가 함께 표시되도록 하는 명령입니다:
SQL> set sqlprompt "_user _privilege 'on' _date >" SYS AS SYSDBA on 06-JAN-04 >
여기에 database connection identifier를 추가할 수도 있습니다. 이 방법은 사용자의 “위치”를 수시로 확인해야 하는 환경에서 대단히 유용합니다.
SQL> set sqlprompt "_user 'on' _date 'at' _connect_identifier >" ANANDA on 06-JAN-04 at SMILEY >
이번에는 오늘 날짜에 시간과 분까지 함께 표시하도록 해 보겠습니다.
ANANDA on 06-JAN-04 at SMILEY > alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
Session altered.
ANANDA on 01/06/2004 13:03:51 at SMILEY >
이처럼 몇 번의 간단한 키 입력 만으로 매우 많은 정보를 포함하는 SQL 프롬프트가 만들어졌습니다. 이 설정을 glogin.sql 파일에 저장하면 앞으로도 계속 같은 프롬프트를 사용할 수 있습니다.
불필요한 인용부호의 생략
Oracle9i이 internal login을 더 이상 지원하지 않는다고 발표되었을 때, 많은 DBA들은 불만의 함성을 터뜨렸습니다. Internal login이 없다면 커맨드 라인 상에서 SYS 패스워드를 입력할 수 없고 따라서 보안 관리가 어려워진다는 이유였습니다. 오라클은 그 해결책으로 운영체제 프롬프트 상에서 인용부호를 사용하는 방법을 제시했습니다:
sqlplus "/ as sysdba"
DBA들은 이러한 변화에 만족하지 않았지만 참고 받아들일 수 밖에 없었습니다. Oracle Database 10g에서는 이러한 요구사항이 없어졌으며, 아래와 같은 방법으로 인용부호를 사용하지 않고 SYSDBA권한으로 로그인하는 것이 가능합니다.
sqlplus / as sysdba
이러한 변화는 단순히 입력할 문자의 수가 줄었다는 것만을 의미하지 않습니다. Unix와 같은 운영체제에서 escape 문자를 사용할 필요가 없어졌다는 것도 새로운 이점의 하나입니다.
파일 조작 기능의 향상
DBA가 문제 해결 과정에서 임시로 만든 SQL 구문이 있다고 가정해 봅시다. 이 DBA는 만든 구문을 나중에 다시 재사용하기 위해 저장하고 싶어할 수도 있습니다. 이럴 때 어떻게 해야 할까요? 아마도 아래와 같은 방법으로 파일에 개별 저장하는 방법을 쓰게 될 것입니다:
select something1 ....
save 1
select something else ....
save 2
select yet another thing ....
save 3
결국 위의 구문을 모두 사용하려면 그에 해당하는 세이브 파일을 모두 불러내어야 합니다. 이 얼마나 번거로운가요! SQL*Plus 10.1.0.2는 여러 구문을 파일에 append하는 형태로 저장하는 기능을 제공합니다. 위의 경우라면 아래와 같은 명령을 사용할 수 있습니다:
select something1 ....
save myscripts
select something else ....
save myscripts append
select yet another thing ....
save myscripts append
이렇게 하면 모든 구문을 myscript.sql 파일에 append 된 형태로 저장할 수 있으므로, 여러 파일에 나누어 저장한 뒤 이를 다시 하나로 연결할 필요가 없게 됩니다.
스풀링(spooling)에서도 append 형태의 저장 방식이 사용됩니다. 이전 릴리즈에서는 SPOOL RESULT.LST 명령으로 result.lst 파일을 생성할 수 있었지만, 기존에 같은 이름의 파일이 존재하는 경우 아무런 경고도 없이 덮어쓰기가 실행된다는 문제가 있었습니다. 이 때문에 실제로 작업을 수행하는 과정에서 중요한 파일이 덮어씌워져 버리는 곤란한 상황이 발생하곤 했습니다. 10g에서는 spool 명령을 수행하면서 append 방식으로 저장하도록 설정할 수 있습니다:
spool result.lst append
덮어쓰기를 원한다면 위의 append 조건 대신 REPLACE 조건을 삽입하면 됩니다. (REPLACE는 디폴트로 적용됩니다.) 아래 명령을 사용하면 세이브하기 전에 기존 파일이 존재하는지 점검합니다:
spool result.lst create
Use another name or "SPOOL filename[.ext] REPLACE"
This approach will prevent the overwriting of the file result.lst.
Login.sql 관련 문제
login.sql과 glogin.sql이라는 파일을 기억하십니까? SQL*Plus가 실행되면 현재 디렉토리에 있는 login.sql이 자동으로 실행됩니다. 하지만 심각한 기능상의 문제가 존재했습니다. Oracle9i 및 이하 버전의 login.sql 파일에 아래와 같은 내용이 포함되어 있는 경우를 생각해 봅시다:
set sqlprompt "_connect_identifier >"
데이타베이스DB1에 접속하기 위해 SQL*Plus를 실행하면 아래와 같이 프롬프트가 표시됩니다:
DB1>
이 프롬프트 상에서 데이타베이스 DB2로의 접속을 시도해 봅시다:
DB1> connect scott/tiger@db2
Connected
DB1>
DB2로 접속한 상태임에도 프롬프트가 여전히 DB1으로 표시되고 있습니다. 이유는 간단합니다. login.sql 파일은 데이타베이스 연결 시에 실행되지 않으며 SQL*Plus 시작 시에만 실행되기 때문입니다. Oracle Database 10g에서는 이러한 문제가 해결되었습니다. login.sql은 SQL*Plus가 시작되는 시점뿐 아니라 새로운 연결이 설정되는 경우에도 자동 실행됩니다. 10g 환경에서 DB1에 접속해 있다가 다른 데이타베이스로 연결을 변경하는 경우, 아래와 같이 프롬프트도 같이 변경됩니다.
In Oracle Database 10g, this limitation is removed. The file login.sql is not only executed at SQL*Plus startup time, but at connect time as well. So in 10g, if you are currently connected to database DB1 and subsequently change connection, the prompt changes.
SCOTT at DB1> connect scott/tiger@db2
SCOTT at DB2> connect john/meow@db3
JOHN at DB3>
변화를 원치 않는다면?
어떤 이유로든, SQL*Plus의 개선된 기능을 사용하고 싶지 않은 경우도 있을 수 있습니다. 그런 경우라면, 아래처럼 ?C 옵션을 적용하면 됩니다:
sqlplus -c 9.2
위와 같이 입력하는 경우 9.2 버전의 SQL*Plus 환경이 실행됩니다.
Use DUAL Freely
아래와 같은 명령을 실제로 사용하는 개발자(또는 DBA)의 수가 얼마나 될 것이라 생각하십니까?
select USER into from DUAL
아마 거의 모든 이들이 사용하고 있을 것입니다. DUAL은 호출될 때마다 새로운 논리적 I/O(Buffer I/O)를 생성합니다. 이 기능은 매우 유용하게 활용됩니다. DUAL은 := USER와 같은 구문만큼이나 자주 사용되고 있습니다. 하지만 오라클 코드는 DUAL을 특수한 형태의 테이블로서 취급하며, 따라서 일반적인 튜닝 방법은 적용할 수 없다는 문제가 있습니다.
Oracle Database 10g에서라면 이에 관련한 걱정은 할 필요가 없습니다. DUAL이 특수한 테이블이기 때문에, 논리적 I/O를 나타내는 consistent gets의 값도 줄어들며, event 10046 trace에서 확인할 수 있는 것처럼 optimization plan도 다른 형태로 나타납니다.
Oracle9i의 경우
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 TABLE ACCESS (FULL) OF 'DUAL'
10g의 경우
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 FAST DUAL
Oracle 9i에서 사용되는 DUAL의 FULL TABLE SCAN 대신, 10g에서는 FAST DUAL optimization plan을 사용하고 있다는 점을 주목하시기 바랍니다. 이러한 기능 개선을 통해 DUAL 테이블을 자주 사용하는 애플리케이션의 연속적인 읽기 작업 성능이 대폭적으로 향상되었습니다.
참고: 엄밀하게 말하자면 DUAL 관련 기능 개선은 SQL*Plus가 아닌 SQL Optimizer의 기능이라고 보는 것이 타당할 것입니다. 여기에서는 이 기능에 접근하기 위해 주로 사용되는 툴이 SQL*Plus라는 이유로 이 기능을 소개합니다.
그 밖에 유용한 팁
그 밖에 SQL*Plus에 관련한 몇 가지 팁이 이 시리즈 전반에 걸쳐 소개되고 있습니다. 예를 들어, RECYCLEBIN 개념은 제 5주 Flashback Table 관련 연재에서 소개됩니다.
일반에 알려진 것과 달리, COPY 명령은 여전히 사용 가능합니다. 하지만 향후 릴리즈에서는 기능이 삭제될 것이라 합니다. (Oracle9i 시절부터 듣던 얘기가 아닌가요?) 이 명령이 적용된 스크립트를 사용하는 중이라면 고민할 필요가 없습니다. 사용 가능할 뿐 아니라 기술지원도 제공됩니다. 더군다나 에러 메시지 리포팅에 관련한 기능 개선도 일부 이루어졌습니다. 테이블이 LONG 칼럼을 포함하는 경우, 해당 테이블에 대한 복제본을 생성하려면 COPY 명령에 의존할 수 밖에 없습니다. CREATE TABLE AS SELECT 명령은 long 데이타 타입의 컬럼을 포함하는 테이블을 지원하지 않습니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 8번째
Automatic Storage Management
마침내, DBA들은 스토리지 디스크를 추가, 이동, 삭제하는 반복적인 일상 업무로부터 해방될 수 있게 되었습니다.
오라클 데이타베이스에 사용할 새로운 서버와 스토리지 서브시스템을 지금 막 접수한 상황이라고 가정해 봅시다. 운영체제를 구성하는 문제를 제외하고, 데이타베이스를 설치하기 전에 먼저 선행되어야 할 가장 중요한 작업이 무엇일까요? 그것은 바로 스토리지 시스템 레이아웃을 생성하는 일, 좀 더 구체적으로 말해 보호 레벨(protection level)을 정의하고 적합한 RAID(Redundant Array of Inexpensive Disks) 셋을 구성하는 일일 것입니다.
데이타베이스 설치 과정에서 스토리지를 구성하는 작업은 상당히 많은 시간을 소요합니다. 적합한 디스크 구성을 선택하기 위해서는 매우 신중한 계획과 분석이 필요하며, 무엇보다도 스토리지 테크놀로지, 볼륨 관리자, 파일 시스템 등에 대한 깊은 지식을 요구합니다. 이 과정에서 수행되는 설계 작업은 대충 아래와 같은 단계로 정리할 수 있습니다. (아래는 매우 일반적인 내용만을 포함하고 있으며, 실제 작업은 구성에 따라 달라질 수 있습니다.)
1. 스토리지가 운영체제 레벨에서 인식되는지 확인하고, 중복성(redundancy) 및 보호 레벨(protection level)을 결정합니다 (hardware RAID).
2. 논리적 볼륨 그룹을 생성하고, 필요한 경우 스트라이핑 또는 미러링 구성을 선택합니다.
3. 논리적 볼륨 관리자(logical volume manager)를 이용하여 논리적 볼륨 상에 파일 시스템을 생성합니다.
4. Oracle 프로세스가 디바이스에 대한 열기, 읽기, 쓰기를 수행할 수 있도록 접근권한 설정을 합니다.
5. 파일시스템 상에 데이타베이스를 생성하고, 가능한 경우 리두 로그, 임시 테이블스페이스, 언두 테이블스페이스 등을 별도의 non-RAID 디스크에 배치합니다.
대부분의 환경에서는, 이러한 작업의 대부분은 스토리지 시스템에 조예가 깊은 전문가에 의해 수행됩니다. 여기서 말하는 “전문가”는 DBA와 다른 별도의 인력을 의미하는 경우가 많습니다.
하지만 스트라이핑, 미러링, 논리적 파일시스템 생성과 같은 모든 작업이 단지 Oracle Database만을 위해 수행된다는 점을 감안한다면, 오라클에서 이러한 절차를 단순화할 수 있는 테크닉을 직접 제공하는 것이 순리에 맞지 않을까요?
Oracle Database 10g가 바로 이를 위한 기능을 제공합니다. Automatic Storage Management(ASM)은 위에서 언급된 작업의 상당 부분을 DBA가 오라클 프레임워크 내에서 직접 수행할 수 있도록 하는 자동화 옵션입니다. ASM을 이용하면 별도의 비용을 지불하지 않고도 Oracle Database 10g가 기본적으로 지원하는 기능만을 활용하여 높은 확장성과 성능을 갖는 파일시스템/볼륨 관리자 환경을 구현할 수 있습니다. 또 디스크, 볼륨 관리자, 파일 시스템 관리에 대한 전문적인 지식을 가지고 있을 필요도 없습니다.
이번 연재에서는, 실제 환경에서 ASM을 활용하기 위한 기본적인 방법을 설명합니다. ASM은 제한된 지면을 통해 섭렵하기에는 너무도 방대하고 강력한 기능입니다. 더 자세한 정보를 원하신다면 결론부분에서 소개하는 자료들을 참고하시기 바랍니다.
ASM이란 무엇인가?
데이타베이스에 사용할 디스크가 10개 있다고 가정해 봅시다. ASM을 이용하는 경우, OS에서는 아무 것도 수행할 필요가 없습니다. ASM은 일련의 물리적 디스크를 “디스크그룹(diskgroup)”이라는 논리적 개체로 자동으로 그룹화합니다. ASM이 사용하는 파일시스템은 사용자 파일을 저장할 수 없으며 버퍼링 기능을 제공하지 않으므로 범용 파일시스템으로 사용될 수는 없습니다. 하지만 (버퍼링 기능이 포함되지 않았기 때문에) 직접 액세스(direct access)를 통해 로우 디바이스(raw device) 수준의 성능을 내는 동시에 파일 시스템이 제공하는 편의성과 유연성을 활용할 수 있다는 장점을 제공합니다.
논리적 볼륨 관리자는 일반적으로 블록의 논리적 주소를 물리적 주소로 변환하는 함수를 제공합니다. 이 변환 작업에는 CPU 자원이 사용됩니다. 또 (RADI-5 등의) 스트라이프 구성에 새로운 디스크가 추가되는 경우, 전체 데이타 셋에 대해 비트 단위의 이동작업이 수행되어야 합니다.
반면 ASM은 파일 익스텐트(extent)를 물리적 디스크 블록으로 변환하기 위해 별도의 오라클 인스턴스를 사용합니다. 이러한 설계 덕분에 파일 익스텐트의 위치를 확인하는 작업을 보다 신속하게 수행할 수 있을 뿐 아니라, 디스크를 추가하거나 이동하는 경우에도 파일 익스텐트의 위치를 재조정할 필요가 없다는 이점을 제공합니다. ASM 인스턴스는 ASM이 실행되기 위해서 반드시 필요하며 사용자에 의해 변경될 수 없습니다. 동일 서버 내에 위치한 여러 개의 오라클 데이타베이스 인스턴스는 하나의 ASM 인스턴스를 공유합니다.
ASM 인스턴스는 “인스턴스”이긴 하지만 데이타베이스를 포함하지 않습니다. 디스크에 관련된 모든 메타데이타는 디스크그룹 자체에 저장되며, 최대한 “self-describing”한 형태로 기술됩니다.
ASM이 제공하는 이점을 간략히 정리하면 아래와 같습니다:
● 디스크의 추가 - 디스크의 추가 작업이 매우 간단합니다. 다운타임이 전혀 발생하지 않으며, 파일 익스텐트는 자동으로 재분배됩니다.
● I/O 분산 - I/O는 전체 디스크에 골고루 분산됩니다. 이에 관련하여 수작업이 전혀 불필요하며, 성능 병목이 발생할 가능성도 그만큼 줄어듭니다.
● 스트라이프 “width”의 설정 - 스트라이핑의 데이타 전송 단위는 Redo Log 파일(128K 단위 전송)처럼 작게, 또는 데이타파일(1MB 단위 전송)처럼 크게 설정할 수 있습니다.
● 버퍼링 - ASM 파일시스템에는 버퍼링 기능이 구현되어 있지 않으며, 따라서 direct I/O를 이용해 성능 향상이 가능합니다.
● Kernerlized Async I/O - kernelized asynchronous I/O를 위해 별도의 셋업 과정을 거칠 필요가 없으며, VERITAS Quick I/O와 같은 써드 파티 파일 시스템을 이용할 필요도 없습니다.
● 미러링 - 하드웨어 미러링을 적용할 수 없는 경우, 소프트웨어 미러링을 쉽게 구성할 수 있습니다.
ASM을 이용한 데이타베이스 생성
ASM을 이용해 데이타베이스를 생성하는 방법을 구체적으로 설명해 보겠습니다:
1. ASM Instance의 생성
Database Creation Assistant(DBCA)에서 아래와 같이 초기화 매개변수를 설정하여 ASM 인스턴스를 생성합니다:
INSTANCE_TYPE = ASM
ASM 인스턴스는 서버 부팅 과정에서 가장 먼저 시작되고, 서버 셧다운 과정에서 가장 마지막으로 중지되어야 합니다.
이 매개변수의 디폴트 값은 RDBMS이며, 이는 일반 데이타베이스 환경을 의미합니다.
2. Diskgroup의 셋업
ASM 인스턴스를 시작한 뒤, 구성된 디스크를 이용해 디스크그룹을 생성합니다.
CREATE DISKGROUP dskgrp1
EXTERNAL REDUNDANCY
DISK
'/dev/d1',
'/dev/d2',
'/dev/d3',
'/dev/d4',
... and so on for all the specific disks ...
;
위의 예에서는 dksgrp1이라는 이름의 디스크그룹을 생성하면서 /dev/d1, /dev/d2 등의 물리적 디스크를 개별적으로 지정했습니다. 이처럼 디스크를 일일이 지정할 수도 있지만, 아래와 같이 와일드카드를 사용하는 것도 가능합니다.
DISK '/dev/d*'
위 명령에서 EXTERNAL REDUNDANCY는 디스크에 장애가 발생하는 경우 디스크그룹이 다운됨을 의미합니다. 이러한 구성은 하드웨어 미러링 등의 구성이 별도로 지원되는 경우에 사용됩니다. 하드웨어 기반의 미러링이 사용되지 않는 경우라면, ASM에서 “failgroup”으로 지정된 디스크그룹을 생성하여 이를 대신할 수 있습니다.
CREATE DISKGROUP dskgrp1
NORMAL REDUNDANCY
FAILGROUP failgrp1 DISK
'/dev/d1',
'/dev/d2',
FAILGROUP failgrp2 DISK
'/dev/d3',
'/dev/d4';
위의 명령에서 d3와 d4가 d1과 d2의 미러(mirror)로서 1:1 대응되는 것이 아님에 주의하시기 바랍니다. ASM은 모든 디스크를 한꺼번에 활용하여 중복성(redundancy)을 구현합니다. 예를 들어, 디스크그룹의 d1에 생성된 특정 파일은 d4에 복제본을 가지고 있는 반면, d3에 생성된 다른 파일은 d2에 복제본을 가지고 있을 수 있습니다. 디스크에 장애가 발생한 경우 다른 디스크에 저장된 복제본을 이용하여 운영을 재개할 수 있습니다. d1과 d2가 연결된 컨트롤러가 다운된 경우에도, ASM은 failgroup으로 지정된 디스크를 이용하여 데이타 무결성을 보장합니다.
3. Tablespace의 생성
이제 ASM이 적용된 스토리지의 데이타파일을 사용하여 메인 데이타베이스의 테이블스페이스를 생성해 봅시다.
CREATE TABLESPACE USER_DATA DATAFILE '+dskgrp1/user_data_01'
SIZE 1024M
/
이걸로 끝입니다. 이제 셋업 과정은 모두 완료되었습니다.
디스크그룹이 가상 파일시스템(virtual file system)으로서 이용되고 있음을 참고하시기 바랍니다. 이러한 구성은 데이타파일 이외의 다른 오라클 파일을 생성하는 경우에도 유용하게 활용됩니다. 예를 들어 아래와 같이 온라인 리두 로그를 생성할 수 있습니다.
LOGFILE GROUP 1 (
'+dskgrp1/redo/group_1.258.3',
'+dskgrp2/redo/group_1.258.3'
) SIZE 50M,
...
아카이브 로그 역시 디스크그룹에 저장되도록 설정할 수 있습니다. 실제로 Oracle Database에 관련된 거의 모든 것이 ASM 기반 디스크그룹에서 생성 가능합니다. 백업 또한 ASM이 활용되는 분야의 하나입니다. 저가형 디스크를 이용해 데이타베이스 복구 영역을 생성한 다음, RMAN을 통해 데이타파일과 아카이브 로그파일 백업을 생성할 수 있습니다. (RMAN에 관련한 다음 연재에서 이 기능에 대해 자세히 설명할 예정입니다.)
ASM이 Oracle Database에 의해 생성되는 파일만을 지원한다는 사실을 주의하시기 바랍니다. ASM은 일반 파일 시스템을 대체하는 수단으로 이용될 수 없으며 바이너리 또는 플랫 파일을 저장할 수 없습니다.
유지 보수
디스크그룹의 유지보수를 위해 DBA가 일반적으로 수행하는 몇 가지 작업에 대해 설명하겠습니다. 디스크그룹 diskgrp1에 디스크를 추가하여 볼륨을 확장하고자 하는 경우, 아래와 같은 명령을 사용합니다:
alter diskgroup dskgrp1 add disk '/dev/d5';
어떤 디스크가 어떤 디스크그룹에 포함되었는지를 확인하려면 아래와 같이 입력합니다:
select * from v$asm_disk;
이 명령은 ASM 인스턴스가 관리하는 모든 데이타베이스의 디스크의 목록을 표시합니다. 특정 디스크를 제거하고자 하는 경우 아래 명령을 사용합니다:
alter diskgroup dskgrp1 drop disk diskb23;
결론
ASM을 이용하면 오라클 데이타베이스의 파일 관리 작업 환경을 한층 향상시키고, 고도의 확장성과 성능을 갖춘 스토리지 환경을 구성할 수 있습니다. ASM은 디스크의 자유로운 추가, 이동, 제거가 가능한 다이내믹 데이타베이스 환경을 구현하고 DBA를 반복적인 업무로부터 해방시키는데 필요한 툴셋을 제공합니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 9번째
RMAN
한층 강력해진 RMAN 유틸리티는 개선된 증분백업, 증분백업의 오프라인 복구, 복구 파일 미리보기, resetlog를 이용한 복구, 파일 압축 등에 관련한 다양한 신기능을 제공합니다.
RMAN이 오라클 데이타베이스 백업 툴의 실질적인 표준으로서 인정되고 있다는 사실은 대부분의 사람들이 동의할 것입니다. 하지만 RMAN의 이전 버전에 문제가 많았던 것도 사실입니다. 필자 역시 RMAN의 기능적인 한계에 불만을 가진 사용자 중 하나였습니다.
Oracle Database 10g는 이러한 문제의 많은 부분을 해결하고 RMAN을 한층 강력하고 유용한 툴로 변화시켰습니다. 그러면 한 번 살펴보기로 합시다.
증분 백업 (Incremental Backup) 기능의 개선
RMAN은 이전부터 증분 백업을 위한 옵션을 제공해 왔습니다. 하지만 이 기능을 실제로 사용하는 경우는 극히 드물었습니다.
증분 백업은 마지막으로 증분 백업이 수행된 이후 변경된 블록만을 백업하는 방식입니다. 예를 들어 Day 1에 전체 백업(level_0)이 수행되고 Day 2와 Day 3에 두 차례의 증분 백업(level_1)이 수행된 경우를 생각해 봅시다. 두 개의 증분 백업은 각각 Day 1과 Day 2, Day 2와 Day3 사이에 변경된 블록만을 포함하고 있습니다. 이와 같은 백업 정책을 사용함으로써 백업 사이즈와 백업에 필요한 디스크 공간을 절감하고, 백업 윈도우를 단축할 수 있을 뿐 아니라 네트워크를 통해 전송되는 데이타 양을 줄일 수 있습니다.
증분 백업은 데이타 웨어하우스 환경에서 특히 유용합니다. 데이타 웨어하우스 작업의 많은 부분은 NOLOGGING 모드로 수행되므로 변경 내역이 아카이브 로그 파일에 저장되지 않으며, 따라서 미디어 복구가 불가능합니다. 데이타 웨어하우스 환경의 데이타 규모와 이 데이타의 대부분이 거의 변경되지 않는다는 사실을 고려할 때, 전체 백업은 효과적이지 못하며 현실적으로 불가능할 수도 있습니다. 그 대신 RMAN을 사용해서 증분 백업을 수행하는 것이 좋은 대안이 될 수 있습니다..
그렇다면 DBA들이 증분 백업 방식을 이용하지 않는 이유는 무엇일까요? Oracle9i와 그 이전 버전의 경우, RMAN은 증분 백업을 수행하기 위해 전체 데이타 블록에 대한 스캔을 수행했습니다. 이러한 작업이 시스템에 너무 큰 부담을 주기 때문에 증분 백업이 효율적이지 않다는 평가를 받게 된 것입니다.
Oracle Database 10g의 RMAN 기능은 이러한 점에서 크게 개선되었습니다. Oracle Database 10g는 (파일시스템의 저널과 유사한 용도를 갖는) 별도의 파일을 통해 마지막 백업 이후 변경된 블록을 추적합니다. RMAN은 (전체 데이타 블록을 스캔하는 대신) 이 파일을 참조하여 어떤 블록을 백업해야 하는지 결정합니다.
아래 명령을 통해 추적 메커니즘(tracking mechanism)을 활성화시킬 수 있습니다:
SQL> alter database enable block change tracking using file '/rman_bkups/change.log';
위 명령은 /rman_bkups/change.log 바이너리 파일을 생성하고 이 파일에 블록 변경 내역을 저장합니다. 아래 명령을 사용하면 추적 메커니즘을 비활성화할 수 있습니다:
SQL> alter database disable block change tracking;
현재 블록 변경 내역의 추적이 수행되고 있는지 확인하려면 아래 쿼리를 사용합니다:
SQL> select filename, status from v$block_change_tracking;
Flash Recovery Area
Oracle9i에서 처음 소개된 Flashback 쿼리는 언두 테이블스페이스를 사용하여 이전 버전으로의 “회귀(flash-back)”을 수행하며, 그 원리상 아주 오래 전의 과거 시점으로는 되돌릴 수 없다는 한계를 갖습니다. 이러한 문제의 대안으로서 제공되는 Flash Recovery는 리두 로그와 유사한 형태의 flashback log을 생성함으로써, 원하는 특정 시점으로 데이타베이스의 상태를 되돌릴 수 있게 합니다. Flash Recovery를 사용하려면 먼저 데이타베이스에 flash recovery area를 생성하고, 그 크기를 정의한 뒤 아래와 같은 SQL 명령을 통해 데이타베이스를 flash recovery mode로 설정하면 됩니다:
alter system set db_recovery_file_dest = '/ora_flash_area';
alter system set db_recovery_file_dest_size = 2g;
alter system set db_flashback_retention_target = 1440;
alter database flashback on;
Flashback 기능을 사용하려면 데이타베이스가 아카이브 로그 모드로 설정되어 있어야 합니다. Flash Recovery가 활성화되면 디렉토리 /ora_flash_area에 최대 2GB 크기의 Oracle Managed File이 생성됩니다. 모든 데이타베이스 변경 사항은 이 파일에 기록되며, 이 파일을 사용하여 과거의 특정 시점으로 데이타베이스를 복구할 수 있습니다.
RMAN은 /ora_flash_area 디렉토리를 디폴트 백업 파일 저장위치로 사용하며, 따라서 백업 파일은 테이프가 아닌 디스크에 저장됩니다. 이 경우 백업 파일을 얼마나 오랫동안 보존할 것인지 설정할 수 있으며, 정의된 보존 기간이 지난 후 추가 공간이 필요해지면 파일은 자동으로 삭제됩니다.
Flash recovery area가 반드시 파일시스템 또는 디렉토리일 필요는 없으며, ASM(Automatic Storage Management) 디스크그룹으로 지정할 수도 있습니다. Flash recovery area를 ASM 디스크그룹으로 지정하려면 아래와 같은 명령을 사용합니다:
alter system set db_recovery_file_dest = '+dskgrp1';
ASM과 RMAN을 함께 사용하면, 별도의 추가비용 없이 Serial ATA 드라이브 또는 SCSI 드라이브 등의 저가형 디스크를 사용해서 뛰어난 확장성과 가용성을 갖춘 스토리지 환경을 구성할 수 있습니다. (ASM에 대한 자세한 설명은 이 시리즈의 제 8 주 연재를 참고하시기 바랍니다.) 이와 같이 구성함으로써 테이프 기반 솔루션만큼 저렴한 비용으로 디스크 백업 환경을 구축하는 동시에 백업 프로세스의 실행 속도를 향상시킬 수 있습니다.
이 접근법의 또 한 가지 장점으로 사용자 실수에 대한 보호 기능을 들 수 있습니다. ASM 파일은 일반적인 파일시스템 환경이 아니기 때문에, DBA와 시스템 관리자의 실수로 손상될 가능성이 매우 적습니다.
증분 병합 (Incremental Merge)
아래와 같은 백업 스케줄을 갖는 환경을 가정해 봅시다:
일요일 - Level 0 (전체 백업), tag level_0
월요일 - Level 1 (증분 백업), tag level_1_mon
Tuesday - Level 1 (incremental) with tag level_1_tue
... (이하 생략)
이와 같은 백업정책 하에서, 토요일에 데이타베이스 장애가 발생한 경우, 10g 이전의 환경에서는 tag level_0를 복구하고 나머지 6개의 증분 백업본을 모두 복구해야만 했습니다. 이러한 작업에는 매우 오랜 시간이 걸리며, 이는 실제로 DBA가 증분 백업을 즐겨 사용하지 않는 이유 중 하나이기도 합니다.
Oracle Database 10g RMAN은 이러한 작업환경을 극적으로 개선했습니다. Oracle Database 10g RMAN의 증분 백업 명령은 아래와 같은 형태로 수행됩니다:
RMAN> backup incremental level_1 for recover of copy with tag level_0 database;
위에서 우리는 RMAN이 incremental level_1 백업을 수행하고 그 결과를 level_0의 전체 백업본과 병합(merge)하도록 설정했습니다. 이 명령을 수행하면 그 날의 전체 백업 이미지를 갖는 level_0 백업본이 새로이 생성됩니다.
예를 들어, 화요일에 수행된 증분 백업(level_1)은 이전의 전체 백업(level_0)과 병합되어 화요일 버전의 새로운 전체 백업본이 생성됩니다. 마찬가지로 토요일에 수행된 증분백업 역시, 금요일의 전체백업본과 병합되어 새로운 전체백업으로 저장됩니다. 따라서 토요일에 데이타베이스가 장애가 발생한다면, level_0 백업본 하나와 아카이브 로그 몇 개만을 이용하여 데이타베이스를 복구할 수 있습니다. 이러한 방법으로 복구에 소요되는 시간을 극적으로 절감하고, 백업 속도를 향상시키는 한편, 전체 백업의 수행 횟수를 줄일 수 있습니다.
압축 파일(Compressed Files)
Flash recovery area에 디스크 백업을 보관하는 경우에도, 디스크 공간의 한계라는 문제는 여전히 남습니다. 특히 네트워크를 통해 백업을 수행하는 환경이라면, 백업본의 크기를 최대한 작게 유지하는 구성이 권장됩니다. 이를 위해 Oracle Database 10g RMAN은 백업 명령에 새로운 압축 옵션을 추가하였습니다:
RMAN> backup as compressed backupset incremental level 1 database;
COMPRESSED 키워드가 사용된 형태를 주의해 보시기 바랍니다. 이 키워드를 사용하는 경우 백업 데이타의 압축을 통해 백업 성능을 향상할 수 있으며, 복구 과정에서는 별도의 압축해제 작업 없이도 RMAN이 파일을 읽어 들일 수 있습니다. 압축을 설정한 경우, 백업 수행 과정에서 아래와 같은 메시지가 출력됩니다.
channel ORA_DISK_1: starting compressed incremental level 1 datafile backupset
또, RMAN list output 명령을 통해 백업 압축 설정 여부를 확인할 수도 있습니다.
RMAN> list output;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Incr 1 2M DISK 00:00:00 26-FEB-04
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20040226T100154
Piece Name: /ora_flash_area/SMILEY10/backupset/2004_02_26/o1_mf_ncsn1_TAG20040226T100154_03w2m3lr_.bkp
Controlfile Included: Ckp SCN: 318556 Ckp time: 26-FEB-04
SPFILE Included: Modification time: 26-FEB-04
모든 압축 알고리즘이 그러하듯, 이 방법을 쓰는 경우 CPU에 추가적인 부담을 주게 됩니다. 반면, 더 많은 RMAN 백업을 디스크에 보관할 수 있다는 장점이 있습니다. 또 Physical Standby Database에서 RMAN 백업을 실행하면 원본 서버에 부담을 주지 않고 백업을 수행할 수 있습니다.
Look Before You Leap: Recovery Preview
Oracle Database 10g의 RMAN은 한 걸음 더 나아가 복구 작업에 사용할 수 있는 백업본을 미리 확인하는 Recovery Preview 기능을 지원합니다.
RMAN> restore database preview;
위 작업의 실행 결과는 Listing 1에서 확인하실 수 있습니다. 테이블스페이스 별로 백업본을 개별적으로 확인하는 것도 가능합니다:
restore tablespace users preview;
Listing 1
Starting restore at 26-FEB-04
using channel ORA_DISK_1
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- ------------ ------------------------------------------------------------
1 1 A 26-FEB-04 318556 26-FEB-04 /ora_flash_area/SMILEY10/datafile/o1_mf_system_03w1zyhx_.dbf
2 2 A 26-FEB-04 318556 26-FEB-04 /ora_flash_area/SMILEY10/datafile/o1_mf_undotbs1_03w20qqc_.dbf
3 3 A 26-FEB-04 318556 26-FEB-04 /ora_flash_area/SMILEY10/datafile/o1_mf_sysaux_03w216xl_.dbf
4 4 A 26-FEB-04 318556 26-FEB-04 /ora_flash_area/SMILEY10/datafile/o1_mf_users_03w21p4n_.dbf
Finished restore at 26-FEB-04
Preview 기능을 활용하여 백업 인프라스트럭처가 정상적으로 운영되고 있는지 정기적으로 점검할 수 있습니다.
Resetlog와 복구 작업
커런트 온라인 리두 로그(current online redo log) 파일이 손실되어, 불완전한 데이타베이스 복구(incomplete database recovery)를 수행할 수 밖에 없는 상황을 가정해 봅시다 (이러한 경우는 흔치 않지만 실재로 존재합니다). 이 경우 가장 큰 문제는 resetlog입니다. 불완전 복구를 수행한 뒤에 resetlog 키워드를 사용하여 log thread의 시퀀스 넘버를 1로 재설정하고 데이타베이스를 오픈해야 하는데, 이렇게 하는 경우 이전에 백업된 데이타가 무용지물이 될 뿐 아니라 복구 작업 자체가 더욱 어려워지게 됩니다.
Oracle9i와 그 이전 버전에서는 데이타베이스를 resetlog 이전의 상태로 복구하려면 전혀 새로운 환경에 데이타베이스를 새로 구축할 수 밖에 없었습니다. Oracle Database 10g에서는 이러한 문제가 해결되었습니다. 컨트롤 파일에 추가된 새로운 기능 덕분에, RMAN이 resetlog 수행 이전 또는 이후의 모든 백업 이미지를 복구에 이용할 수 있게 된 것입니다. 또 백업을 수행하기 위해 데이타베이스를 셧다운할 필요도 없게 되었습니다. 이 기능을 사용하면, resetlog 작업 이후 데이타베이스를 바로 오픈하고 운영을 재개하는 것이 가능합니다.
Ready for RMAN
Oracle Database 10g RMAN은 한층 향상된 백업 관리 툴로 거듭 태어났습니다. 증분 백업에 관련한 기능 개선만으로도 RMAN은 더 이상 무시할 수 없는 툴이 되었습니다.
제공 : DB포탈사이트 DBguide.net
Oracle Database 10g: DBA를 위한 20가지 주요 기능 - 10번째
감사 기능의 확장
Oracle Database 10g 의 개선된 감사(Audit Trail) 기능을 이용해 매우 상세한 수준의 사용자 접근 기록을 관리하고, 수작업에 의존한 트리거 기반의 감사 작업을 최소화할 수 있습니다.
사용자 Joe가 아래와 같이 테이블 로우에 대한 업데이트를 수행한 경우를 가정해 봅시다.
update SCOTT.EMP set salary = 12000 where empno = 123456;
이 작업이 데이타베이스에는 어떠한 형태의 기록으로 남게 될까요? Oracle9i Database와 그 이전 버전에서는 “누가” 접근했는지에 대한 기록은 남았지만, “어떤” 작업을 했는지에 대해서는 아무런 기록도 남지 않았습니다. 예를 들어, 사용자 Joe가 SCOTT가 소유한 EMP 테이블을 업데이트했다는 사실은 알 수 있지만, 그가 사번 123456을 가진 직원의 salary 컬럼을 업데이트했다는 사실은 알 수 없습니다. 또 salary 컬럼의 변경 전 값 역시 기록되지 않습니다. 이처럼 상세한 변경 내역을 추적하려면, 트리거를 적용하거나 Log Miner를 이용해 아카이브 로그를 뒤지는 수 밖에 없었습니다.
두 가지 방법 모두 변경된 컬럼과 값을 확인할 수는 있지만 이에 수반되는 비용이 만만치 않다는 문제가 따릅니다. 감사 목적으로 적용되는 트리거는 성능적으로 심각한 부담을 줍니다. 이러한 이유 때문에 사용자 정의 트리거의 적용을 아예 금지하는 경우도 많습니다. Log Miner는 성능 자체에는 아무런 영향을 주지 않으나 변경 내역을 추적하기 위해 아카이브 로그에 의존해야 한다는 문제가 있습니다.
Oracle9i에 추가된 Fine-Grained Auditing(FGA) 기능은 로우(row) 단위 변경 내역을 SCN 넘버와 함께 저장함으로써 이전의 데이타를 재구성할 수 있게 하지만, select 구문에 대해서만 적용 가능하고 update, insert, delete 등의 DML 구문에는 적용할 수 없습니다. 이러한 이유로, Oracle Database 10g 이전 버전에서 로우 단위의 데이타 변경 내역을 추적하려면 트리거를 적용하는 것 외에 다른 방법이 없었습니다.
Oracle Database 10g에서는 감사 작업과 관련하여 두 가지 중요한 기능 개선이 이루어졌습니다. 이 문서에서는 오라클이 제공하는 두 종류의 감사 관련 기능 ? (모든 버전에서 제공되는) 표준 감사(standard audit) 기능과 (Oracle9i 이후 버전에서 제공되는) fine-grained audit 기능 ? 의 관점에서 개선된 사항을 설명하고자 합니다.
새로운 기능들
첫 번째로, FGA는 select 이외의 다른 DML 구문을 지원합니다. 변경 내역은 이전과 마찬가지로 FGA_LOG$에 기록되며, DBA_FGA_AUDIT_TRAIL 뷰를 통해 확인할 수 있습니다. 또 특정 컬럼이 액세스되는 경우, 또는 정의된 일련의 컬럼이 액세스되는 경우에만 트리거가 동작하도록 설정하는 것이 가능합니다
“AUDIT” SQL 명령으로 구현되는 표준 감사 기능은 특정 오브젝트의 감사를 위한 환경을 쉽고 빠르게 설정하려는 경우 유용합니다. 예를 들어, Scott가 소유자인 EMP 테이블에 대한 모든 업데이트를 추적하려면 아래와 같은 명령을 사용합니다:
audit UPDATE on SCOTT.EMP by access;
위 명령은 SCOTT.EMP 테이블에 업데이트가 발생할 때마다 그 내역을 AUD$ 테이블에 기록합니다. 감사 기록은 DBA_AUDIT_TRAIL 뷰를 통해 조회할 수 있습니다.
이 기능은 10g 이전의 버전에서도 제공되어 오던 것입니다. 하지만 이전 릴리즈에서는 제한적인 정보(구문을 실행한 사용자, 시간, 터미널 ID, 등)만을 추적할 수 있었으며, 바인딩 되는 변수 값과 같은 중요한 정보의 추적이 불가능했습니다. 10g에서는 이전 버전에서 제공되던 것에 추가하여 여러 가지 중요한 정보를 수집할 수 있도록 개선되었습니다. 감사 정보를 저장하는 AUD$ 테이블에도 몇 가지 새로운 컬럼이 추가되었으며, 이전과 마찬가지로 DBA_AUDIT_TRAIL을 통해 확인할 수 있습니다. AUD$ 테이블에 추가된 컬럼에 대해 좀 더 자세히 살펴보기로 합시다.
EXTENDED_TIMESTAMP. 이 컬럼은 감사 기록의 타임스탬프 값을 TIMESTAMP(6) 포맷으로 저장합니다 (TIMESTAMP(6) 포맷은 Time Zone 정보와 함께 그리니치 평균 시간(Universal Coordinated Time으로 불리기도 합니다)을 소수점 이하 9자리 초 단위로 표현합니다). 이 포맷으로 저장된 시간 정보의 예가 아래와 같습니다:
2004-3-13 18.10.13.123456000 -5:0
위 데이타는 미국 동부 표준 시간을 기준으로 한 2004년 3월 13일 시간 정보를 표시하고 있습니다 (-5.0”은 UTC 보다 5시간 이전을 기준으로 함을 의미합니다).
이와 같은 확장형 포맷을 사용함으로써 매우 세밀한 시간 간격으로 감사 기록을 관리할 수 있고, 여러 시간대에 걸쳐 사용되는 데이타베이스 환경에서 한층 유용한 정보를 얻을 수 있습니다.
GLOBAL_UID and PROXY_SESSIONID. 사용자 인증을 위해 Oracle Internet Directory와 같은 아이덴티티 관리 솔루션을 사용하는 경우, 데이타베이스가 사용자를 표시하는 방법도 조금 달라지게 됩니다. 예를 들어, 인증된 사용자가 (데이타베이스 사용자가 아닌) 엔터프라이즈 사용자인 경우를 생각해 봅시다. 이 경우 DBA_AUDIT_TRAIL 뷰의 USERNAME 컬럼에 enterprise userid가 표시되며, 따라서 해당 컬럼의 정보에 일관성이 없게 됩니다. Oracle Database 10g에서는 enterprise userid를 위한 GLOBAL_UID 컬럼을 별도로 제공하며, 이 컬럼을 이용하여 디렉토리 서버에 엔터프라이즈 사용자에 대한 정보를 질의할 수 있습니다.
멀티-티어 애플리케이션 환경에서 엔터프라이즈 사용자가 proxy user를 통해 데이타베이스에 접속하는 경우도 있습니다. 사용자에게 프록시 인증을 제공하는 명령이 아래와 같습니다:
alter user scott grant connect to appuser;
위 명령은 사용자 SCOTT가 APPUSER라는 proxy user를 통해 데이타베이스에 접속할 수 있게 합니다. 이 경우, Oracle 9i는 COMMENT_TEXT에 “PROXY”라는 값을 저장하지만 proxy user의 session id는 저장하지 않습니다. 10g에서는 PROXY_SESSIONID에 proxy session id를 저장합니다.
INSTANCE_NUMBER. Oracle Real Application Clusters (RAC) 환경에서 사용자가 실제로 어느 인스턴스에 연결되어 있는지 확인할 수 있다면 여러모로 도움이 될 것입니다. 10g에서는 INSTANCE_NUMBER 컬럼에 초기화 매개변수 파일에 지정된 해당 인스턴스의 인스턴스 번호를 저장합니다.
OS_PROCESS. Oracle9i와 그 이전 버전에서는 SID 값만을 저장하고 운영체제의 process id는 저장하지 않았습니다. 하지만 서버 프로세스의 OS process id가 Trace파일과 상호참조를 위해 필요한 경우가 있을 수 있습니다. 10g는 이를 위해 OS_PROCESS 컬럼을 제공합니다.
TRANSACTIONID. 이번에는 정말 중요한 정보를 소개할 차례입니다. 사용자가 아래와 같은 쿼리를 실행하는 경우를 가정해 봅시다:
update CLASS set size = 10 where class_id = 123;
commit;
위 명령은 트랜잭션으로서의 조건을 만족하며, 따라서 명령에 대한 감사 기록이 생성됩니다. 그렇다면, 실제로 생성된 감사 기록을 어떻게 확인할 수 있을까요? TRANSACTIONID 컬럼에 저장된 transaction id와 FLASHBACK_TRANSACTION_QUERY 뷰를 조인하면 됩니다. FLASHBACK_TRANSACTION_QUERY 뷰를 조회하는 간단한 예가 다음과 같습니다:
select start_scn, start_timestamp,
commit_scn, commit_timestamp, undo_change#, row_id, undo_sql
from flashback_transaction_query
where xid = '';
10g에서는 트랜잭션에 관련한 일반적인 정보 이외에도 undo change#와 rowid 등을 추가로 저장합니다. 트랜잭션 변경의 undo를 위한 SQL 쿼리는 UNDO_SQL 컬럼에, 변경된 로우의 rowid 값은 ROW_ID 컬럼에 저장됩니다.
System Change Number. 변경 전의 값을 저장하기 위해서 어떤 방법을 사용하고 계십니까? Oracle9i의 FGA를 이용하는 경우, flashback 쿼리를 통해 변경 전의 값을 알 수 있습니다. 이 작업을 위해서는 변경 작업에 사용된 SCN을 알고 있어야 하며, 이 정보는 감사 기록의 System Change Number 컬럼에 저장됩니다. 아래와 같은 쿼리를 사용하면 변경 전의 값을 확인할 수 있습니다.
select size from class as of SCN 123456
where where class_id = 123;
This will show what the user saw or what the value was prior to the change.
DB 감사 기능의 확장
처음에 우리가 제기했던 문제가 무엇인지 다시 상기해 보겠습니다. 표준 감사(standard auditing)을 통해 캡처되지 않던 정보들인 사용자가 실행한 SQL 구문과 바인드 변수들이 문제였습니다. Oracle Database 10g에서는 초기화 매개변수의 간단한 변경 작업만으로 이러한 목적을 달성할 수 있습니다. 매개변수 파일에 아래 항목을 삽입하면 됩니다.
audit_trail = db_extended
이 매개변수는 SQL텍스트와 컬럼에 사용된 변수 값의 기록을 활성화합니다. 이 기능은 10g 이전 버전에서는 지원되지 않았습니다.
트리거가 필요한 경우
롤백된 결과의 취소. 감사 기록은 원본 트랜잭션으로부터 파생된 autonomous transaction을 통해 생성됩니다. 그렇기 때문에 원본 트랜잭션이 롤백 되는 경우에도 감사 기록은 커밋됩니다.
간단한 예를 들어 설명해 보겠습니다. CLASS 테이블의 UPDATE 작업에 대한 감사를 수행하는 경우를 가정해 봅시다. 사용자가 SIZE 컬럼의 값을 20을 10으로 변경하는 구문을 실행한 뒤 아래와 같이 롤백했습니다:
update class set size = 10 where class_id = 123;
rollback
결국 SIZE 컬럼은 (10이 아닌) 20의 값을 갖게 됩니다. 하지만 감사 기록에는 (롤백이 수행되었음에도) 변경 작업이 수행된 것처럼 기록이 됩니다. 사용자가 롤백을 수행하는 빈도가 높은 경우, 이러한 현상으로 인해 곤란한 문제가 생길 수 있습니다. 이러한 경우라면 커밋된 변경내역만을 캡처하기 위해 트리거를 사용할 수 밖에 없습니다. 즉 사용자 정의된 audit trail에 기록하도록 CLASS테이블에 대한 트리거가 있다면, CLASS 테이블에 대한 변경 작업이 롤백 되는 경우, 설정된 트리거 역시 롤백 됩니다.
변경 전 값의 캡처. 오라클이 제공하는 감사 기록은 변경 이전/이후의 값을 제공하지 않습니다. 예를 들어, 위에서 예로 든 변경 작업에 대한 감사기록은 실행된 구문과 SCN 넘버를 저장하고 있지만 변경 전 값(20)은 저장하지 않습니다. SCN 넘버를 이용한 flashback 쿼리를 통해 이전 값을 확인할 수 있지만, 이것도 해당 정보가 undo 세그먼트에 저장되어 있는 경우에만 가능합니다. 해당 정보가 undo_retention period 설정된 보관 기간을 초과한 경우, 이전 값을 조회할 수 있는 방법은 없습니다. 트리거를 이용하면 undo_retention period의 설정값에 관계없이 변경전 값을 영구적으로 저장할 수 있습니다.
통합 감사 기록
FGA와 표준 감사(standard auditing) 기능은 서로 유사한 형태의 정보를 제공하므로, 이 두 가지를 함께 사용함으로써 매우 다양한 정보를 얻을 수 있습니다. Oracle Database 10g는 두 종류의 정보를 통합한 DBA_COMMON_AUDIT_TRAIL 뷰를 제공합니다. 이 뷰는 DBA_AUDIT_TRAIL 뷰와 DBA_FGA_AUDIT_TRAIL 뷰의 UNION ALL로 구성됩니다. (하지만 두 감사 작업이 제공하는 정보의 성격에 분명한 차이가 있는 것도 사실입니다.)
결론
10g로 오면서, 감사 기능은 단순한 “액션 레코더(action recorder)” 또는 “패스트 리코딩 메커니즘(fast-recording mechanism)”에서 매우 상세한 수준의 사용자 액티비티를 저장하는 수단으로 변화되었으며, 이 기능을 활용하여 수작업으로 수행되던 트리거 기반 감사 작업을 최소화할 수 있습니다. 또 표준 감사(standard auditing)과 FGA의 감사 결과를 통합함으로써, 데이타베이스 액세스에 대한 추적이 한층 용이해졌습니다.
제공 : DB포탈사이트 DBguide.net
출처 : Tong - redyoon님의 DB통