|
TECHNOLOGY: DBA
단서의 마이닝
저자 - Arup Nanda
Oracle LogMiner를 이용하여 트랜잭션을 재생하고 피해를 복구할 수 있습니다.
폴이 무척 흥분된 목소리로 전화를 걸어 왔습니다. 폴은 지난 20년 동안 엘렌 스미스라는 고객의 어카운트 매니저로 일해 왔습니다. 그런데 오늘 은행을 찾아온 엘렌이 시스템을 조회한 결과 폴이 더 이상 어카운트 매니저가 아닌 것으로 확인되었다는 것입니다! 무척 화가 난 폴은 해명을 요구하고 있습니다.
이 은행의 수석 DBA인 존은 문제를 최대한 빨리 해결해야 합니다. 존이 ACCOUNTS 테이블을 조회한 결과, 계좌 번호 4567(엘렌의 계좌)의 ACC_MGR_ID 컬럼이 1로 설정되어 있었습니다. 폴의 ID는 5입니다. 이 컬럼의 값이 과거에는 5로 설정되었던 것은 분명해 보입니다. 도대체 누가, 왜 이 기록을 변경한 것일까요? 또 다른 쿼리를 던져 보니 폴은 자신이 전혀 알지 못하는 고객(계좌 번호 9876)에 할당되어 있었습니다. 왜 이런 상황이 발생한 것일까요? 그리고 계좌 번호 9876의 실제 어카운트 매니저는 누구일까요?
아카이브의 분석
이 상황을 해결하기 위해 존이 할 수 있는 일은 무엇일까요?
먼저 오라클의 언두(undo)/리두(redo) 로드를 분석해 보아야 할 것입니다. 오라클 데이터베이스는 변경 사항이 발생할 때마다undo, redo 엔트리를 기록해 둡니다. 간단히 설명하자면, 언두 데이터는 변경 이전의 데이터 값을, 리두 데이터는 변경 이후의 데이터 값을 제공합니다. 리두 데이터는 변경 사항이 발생할 때마다 시스템 글로벌 영역(SGA)의 로그 버퍼에 기록됩니다. 그런 다음, 로그 라이터(LGWR) 프로세스는 리두 데이터를 온라인 리두 로그에 기록합니다. 또 데이터베이스가 ARCHIVELOG 모드로 운영 중인 경우, 아카이브 프로세스(ARC0)가 다시 리두 데이터를 아카이브 로그에 기록합니다. 리두 데이터는 데이터베이스에 재해 상황이 발생했을 때 데이터베이스 실행 기록을 재생(replay)할 수 있도록 보장하는 것을 목적으로 합니다. 데이터가 완전 소실된 경우, DBA는 데이터베이스의 백업본을 복구한 뒤 백업 이후 생성된 전체 아카이브 로그를 적용하여 장애 시점까지 롤 포워드(roll forward) 처리할 수 있습니다.
그렇다면 언두 데이터는 어떤 역할을 담당할까요? 오라클의 읽기 일관성 모델(read consistency model)에 의하면, 사용자는 변경 사항이 커밋(commit) 된 이후에만 변경된 데이터 값을 조회할 수 있습니다. 변경 사항이 커밋 되지 않으면 데이터베이스는 기존의(변경되지 않은) 값을 복구합니다. 변경 이전의 값은 언두 세그먼트로부터 가져옵니다. 또, 재해 상황이 발생한 후 데이터를 복구하는 동안, 오라클 데이터베이스는 일관성과 데이터 무결성의 보장을 위해 커밋되지 않은 모든 변경 내역을 롤백 처리해야 합니다. 롤백 작업을 위한 정보 역시 언두 세그먼트로부터 가져옵니다. 언두 세그먼트는 데이터베이스의 변경 내역을 모두 저장하고 있으며, 따라서 리두 데이터 또한 언두 세그먼트로부터 생성되어 온라인 리두 로그 및 아카이브 로그로 전달됩니다.
결과적으로, 온라인/아카이브 로그는 언두/리두 정보를 모두 포함하게 됩니다. DBA는 두 가지 로그를 통해 기존의 값과 새로운 데이터 값을 모두 확인할 수 있습니다. 하지만 온라인/아카이브 로그는 일반적인 쿼리로는 조회가 불가능하며 사람이 읽을 수 있는 포맷으로 저장되어 있지 않습니다. 로그의 데이터를 읽으려면 Oracle LogMiner라는 툴이 필요합니다. Oracle LogMiner는 Oracle8i에서 처음 소개된 DBMS_LOGMNR 패키지를 통해 구현됩니다. 본 문서는 Oracle LogMiner를 이용하여 오라클 데이터베이스의 변경 작업에 관련한 일반적인, 또는 그리 일반적이지 않은 문제들을 해결하는 방법을 설명하고 있습니다.
마이닝 시작하기
다시 스미스 씨의 계정 문제로 돌아가 보기로 합시다. 존은 Flashback Query를 이용하여 테이블 레코드의 이전 값을 조회함으로써 어카운트 매니저가 언제, 어떻게 변경되었는지 확인할 수 있습니다. Flashback Query는 과거 특정 시점의 컬럼 값을 확인할 수 있게 하는 오라클 데이터베이스의 기능입니다.
하지만 Flashback Query 기능은 변경 작업이 UNDO_RETENTION 매개변수에 정의된 기간 안에 수행되었을 때에만 효과가 있습니다. OLTP 데이터베이스에서 이 매개변수는 짧게는 30분 정도로 설정되는 경우가 많습니다. 그러므로 Flashback Query를 통해 변경 정보를 확인하지 못할 가능성이 큽니다.
존은 LogMiner를 이용하여 온라인 리두 로그 파일을 직접 조회하는 방법으로 문제를 해결하기로 합니다. LogMiner를 설정하고 사용하는 방법이 아래와 같습니다.
- DBMS_LOGMNR 패키지 설치. 존은 SQL*Plus에 SYS 계정으로 로그인하고 $ORACLE_HOME/rdbms/admin/dbmslm.sql 파일을 실행하여 패키지를 설치합니다.
- 권한 할당. 존은 마이닝 작업을 수행할 계정(여기에서는 본인 자신의 계정)에 패키지 실행 권한을 할당합니다.
GRANT EXECUTE_CATALOG_ROLE TO JOHN;
- 동의어(synonym) 생성. 존이 아래와 같이 public synonym을 생성합니다.
CREATE PUBLIC SYNONYM DBMS_LOGMNR FOR SYS.DBMS_LOGMNR;
위의 세 단계는 단 한 차례만 실행됩니다.
- 마이닝의 범위 설정. 문제가 최근에 발생했다면, 변경 내역이 여전히 온라인 리두 로그 파일에 저장되어 있을 가능성이 큽니다. 따라서 존은 1차적으로 온라인 리두 로그 파일만을 마이닝하기로 결정합니다. 존은 아래와 같은 쿼리를 통해 파일을 지정합니다.
SELECT distinct member LOGFILENAME FROM V$LOGFILE;
LOGFILENAME
________________________
/dev/vgredo01/rlog1a
/dev/vgredo01/rlog1b
데이터베이스에는 2개의 리두 로그 파일이 사용되고 있습니다. 존은 아래와 같이 실행하여 이 2개의 파일로 마이닝 범위를 제한합니다.
BEGIN
DBMS_LOGMNR.ADD_LOGFILE
('/dev/vgredo01/rlog1a');
DBMS_LOGMNR.ADD_LOGFILE
('/dev/vgredo01/rlog1b');
END;
- LogMiner 세션을 시작하고 딕셔너리를 설정. 존은 아래 SQL 구문을 실행하여 LogMiner 세션을 시작합니다.
BEGIN
DBMS_LOGMNR.START_LOGMNR
(options =>
dbms_logmnr.dict_from_online_catalog);
END;
또 OPTIONS 매개변수를 사용해서 오라클 데이터베이스가 LogMiner를 시작하는 과정에서 딕셔너리 정보를 읽어온 후 온라인 카탈로그의 오브젝트 네임을 변환하도록 설정합니다.
앞에서 언급한 것처럼 리두 로그 정보는 일반 텍스트 형식으로 기록되어 있지 않습니다. LogMiner는 리두 로그 파일로부터 텍스트 기반 리포트를 추출해냅니다. 하지만 리포트를 통해 제시되는 값이 기존 오브젝트의 값과 다르게 표시될 수 있습니다. 예를 들어 온라인 리두 로그의 소유자, 테이블, 컬럼 네임 정보는 사용자가 처음에 입력한 포맷이 아닌 16진수 값으로 변환되어 있습니다. 예를 들어, ACCOUNTS 테이블은 OBJ#45C1로 표시됩니다. 리두 로그 데이터의 가독성을 개선하기 위해, 존은 LogMiner가 딕셔너리를 이용하여 이러한 값을 보다 읽기 쉬운 포맷으로 변환하도록 설정할 수 있습니다.
- 컨텐트의 확인. LogMiner 세션을 시작하면 V$LOGMNR_CONTENTS라는 이름의 뷰에 데이터가 입력됩니다. 존은 이 뷰를 조회하여 엘렌 스미스의 계좌에 무슨 일이 일어났는지 확인할 수 있습니다. 존은 ACCOUNTS 테이블에 대한 업데이트 작업을 수행한 사용자와 실행 시간을 조회합니다. V$LOGMNR_CONTENTS 뷰에 대한 쿼리와 그 결과를 Listing 1에서 확인할 수 있습니다. (이 쿼리가 LogMiner 세션을 시작한 세션 내에서 실행되어야 함에 주의하시기 바랍니다.) Listing 1의 6번째 라인에서, 존은 업데이트 작업된 관련된 데이터만을 조회하도록 제한하고 있습니다.
Listing 1의 실행 결과를 통해, 존은 조(Joe)라는 이름의 사용자가 오후 2시 16분에 테이블을 업데이트했음을 확인합니다. SID, SERIAL# 컬럼을 통해 조가 로그인한 세션에 관련된 정보를 확인할 수 있습니다.
Listing 1: V$LOGMNR_CONTENTS 뷰의 조회
SQL> select username, to_char(timestamp,'mm/dd/yy hh24:mi:ss') timestamp,
2 seg_type_name, seg_name, table_space, session# SID, serial#
3 from v$logmnr_contents
4 where table_name = 'ACCOUNTS'
5 and seg_owner = 'ARUP'
6 and operation = 'UPDATE';
USERNAME TIMESTAMP SEG_TYPE_N SEG_NAME TABLE_SPAC SID SERIAL#
___________ __________________ _____________ ______________ ___________ ____ _________
JOE 01/26/05 14:16:41 TABPART ACCOUNTS,P1 ACC_D1 532 62
JOE 01/26/05 14:16:41 TABPART ACCOUNTS,P2 ACC_D2 532 62
JOE 01/26/05 14:16:41 TABPART ACCOUNTS,P3 ACC_D3 532 62
...
또 다른 컬럼 SESSION_INFO를 통해서도 조의 세션에 관련된 추가적인 정보를 확인할 수 있습니다. 전체 로우에 대한 SESSION_INFO 값이 아래와 같습니다.
login_username=JOE
client_info=
OS_username=jsmoe
Machine_name=ACMENTNY\JSMOE
OS_terminal= OS_process_id=4080:3096 OS_program name=sqlplus.exe
이 정보를 바탕으로, 존은 조가 ACMENTNY 네트워크에 연결된 JSMOE라는 이름의 클라이언트 머신으로부터 ACCOUNTS 테이블을 업데이트하였음을 확인합니다. 마지막 정보, "OS_program name=sqlplus .exe"는 또 다른 중요한 단서를 제공합니다. 조가 SQL*Plus를 사용 중이었다는 사실로부터, 조가 임의로 업데이트 구문을 실행했을 것이라 추측했을 것입니다. 조는 개발자이며, 따라서 긴급 상황이 아닌 이상 운영 데이터베이스에 업데이트 작업을 수행할 수 없습니다. 도대체 조가 이 작업을 실행한 이유가 무엇일까요?
그 이유가 무엇이든, 존은 조가 어떤 정보들을 업데이트하였는지, 그리고 업데이트 이전의 값이 무엇인지 알아 내야 합니다. 이 정보를 확인하기 위해, 존은 Listing 1의 쿼리를 수정하여 V$LOGMNR_CONTENTS 뷰에 포함된 두 가지 매우 중요한 컬럼을 조회합니다. 그 중 하나인 SQL_REDO 컬럼은 사용자가 발생시킨 변경 작업과 동일한 효과를 갖는 구문을 저장하고 있으며, SQL_UNDO는 변경 작업을 무효화시키기 위해 실행될 수 있는 구문을 저장하고 있습니다. 수정된 쿼리가 Listing 2와 같습니다.
Listing 2: SQL_UNDO, SQL_REDO 컬럼의 조회
SQL> select sql_undo, sql_redo
2 from v$logmnr_contents
3 where table_name = 'ACCOUNTS'
4 and seg_owner = 'ARUP'
5 and operation = 'UPDATE';
SQL_UNDO SQL_REDO
____________________________-_______________ ___________ ________________________________
update "ARUP"."ACCOUNTS" set "ACC_M update "ARUP"."ACCOUNTS" set "ACC_M
GR_ID" = '2' where "ACC_MGR_ID" = ' GR_ID" = '6' where "ACC_MGR_ID" = '
6' and ROWID = 'AAOKdAALAAAGcEAB' 2' and ROWID = 'AAOKdAALAAAGcEAB'
update "ARUP"."ACCOUNTS" set "ACC_M update "ARUP"."ACCOUNTS" set "ACC_M
GR_ID" = '1' where "ACC_MGR_ID" = ' GR_ID" = '6' where "ACC_MGR_ID" = '
6' and ROWID = 'AAAOKdAALAAAGcEAB0' 1' and ROWID = 'AAAOKdAALAAAGcEAB0'
update "ARUP"."ACCOUNTS" set "ACC_M update "ARUP"."ACCOUNTS" set "ACC_M
GR_ID" = '5' where "ACC_MGR_ID" = ' GR_ID" = '6' where "ACC_MGR_ID" = '
6' and ROWID = 'AAAOKdAALAAAGcEAB5' 5' and ROWID = 'AAAOKdAALAAAGcEAB5'
...
존의 연락을 받은 조는 자신이 변경 작업을 실행했음을 인정합니다. 하지만 조는 나름의 중대한 이유가 있었다고 항변하고 있습니다. 은행의 중요 고객 중 하나인 엘렌 스미스가, 자신에게 할당된 어카운트 매니저로부터 매우 불쾌한 경험을 한 뒤 담당자를 바꾸어 달라고 요구했다는 것입니다. 물론 이런 변경 작업은 관리자에 의해 수행되는 것이 올바른 절차이지만, 담당 관리자가 자리를 비운 상태였으며 새로운 어카운트 매니저가 조에게 연락해서 어떤 방법으로든 조치를 취해달라고 부탁했다고 합니다. 부탁을 받은 조는 아래와 같은 구문을 실행하였습니다.
UPDATE ACCOUNTS
SET acc_mgr_id = 6
WHERE first_name = 'Ellen'
AND last_name = 'Smith';
하지만 조는 위의 구문으로 인해 "엘렌 스미스"라는 이름을 갖는 340 명의 동명이인의 레코드들이 모두 업데이트 되었음을 알아차리지 못하고 있었습니다. 조는 단 한 명의 고객 기록만을 업데이트하기 원했지만, 결과적으로 340개의 레코드가 업데이트되어 버린 것입니다. 그 중 하나가 폴의 고객인 또 다른 "엘렌 스미스"였습니다.
위에서는 존이 온라인 리두 로그에 대해서만 마이닝을 수행했음을 상기하시기 바랍니다. 하지만 문제가 오래 전에 발생한 것이라면 이미 온라인 리두 로그의 컨텐트가 아카이브 로그 파일로 이전되었을 가능성이 높습니다. 이러한 경우라면 아카이브 로그 역시 마이닝 대상에 포함시켜야 할 것입니다. LogMiner에서 아카이브 로그를 분석하려면, DBMS_LOGMNR.ADD_LOGFILE 프로시저를 호출하면서 아카이브 로그 파일의 이름을 명시하기만 하면 됩니다. 나머지 작업은 동일한 방법으로 수행됩니다. 아카이브 로그 파일의 이름은 V$ARCHIVED_LOG 데이터 딕셔너리 뷰에서 확인할 수 있습니다.
피해의 복구
이제 존은 나머지 339개의 레코드를 기존의 값으로 신속하게 변경해야 합니다. Listing 2의 실행 결과에서, SQL_REDO는 기존에 발생한 변경 구문을, SQL_UNDO는 변경 작업을 무효화시키기 위한 구문을 보여 주고 있음을 설명한 바 있습니다. 조는 단 하나의 구문을 사용했지만 LogMiner는 각 레코드별로 하나씩 새로운 구문을 재구성하여 제시합니다. 존은 SQL_UNDO 컬럼의 각 구문을 실행함으로써 기존의 어카운트 매니저 ID를 복구하고, 조가 실행한 변경 작업을 무효화시킬 수 있습니다.
출력 결과에서 참고할만한 정보
Listing 2에서 실행 결과로 출력된 SQL_UNDO, SQL_REDO 컬럼 값은 하나의 라인으로 작성되어 읽기가 쉽지 않습니다. 존은 이 문제를 해결하기 위해 print_pretty_sql 옵션을 사용할 수 있습니다. 존은 또 다이내믹 SQL 구문을 이용하여 PL/SQL 루프에서 SQL 구문을 실행하기로 합니다. 이 방법을 사용하려면 각 구문 뒤에 붙은 세미콜론을 제거해야 합니다. 이 작업을 위해 no_sql_delimiter 옵션을 사용할 수 있습니다.
존는 아래와 같은 옵션을 사용하여 새로운 LogMiner 세션을 시작합니다.
begin
dbms_logmnr.start_logmnr(
options =>
dbms_logmnr.
dict_from_online_catalog +
dbms_logmnr.print_pretty_sql +
dbms_logmnr.no_sql_delimiter
);
end;
이 명령을 실행한 뒤, V$LOGMNR_CONTENTS 뷰에 대해 Listing 2의 쿼리를 실행한 결과로 출력된 SQL_UNDO 뷰의 값이 아래와 같습니다.
SQL_UNDO
______________________________
update "ARUP"."ACCOUNTS"
set
"ACC_MGR_ID" = 2
where
"ACC_MGR_ID" = 6 and
ROWID = 'AAOKdAALAAAGcEAB'
...
선택적 복구
앞에서는 ACCOUNTS 테이블에 적용된 모든 업데이트 작업을 "undo" 처리하기 위한 SQL 구문을 얻어내는 방법에 대해 설명했습니다. 물론 존 이외의 다른 사용자들도 이 테이블에 업데이트 작업을 했을 수 있으며, 이러한 사용자들의 변경 작업 내역은 그대로 유지되어야 할 것입니다. 존은 V$LOGMNR_CONTENTS 뷰의 컨텐트에 대한 필터링을 통해 관련된 정보만을 추출하기로 합니다. 그 방법이 Listing 3과 같습니다.
Listing 3: V$LOGMNR_CONTENTS 뷰의 선택적 조회
select
acc_type, first_name, last_name,
dbms_logmnr.mine_value(redo_value,'ARUP.ACCOUNTS.ACC_MGR_ID') new_value,
dbms_logmnr.mine_value(undo_value,'ARUP.ACCOUNTS.ACC_MGR_ID') old_value
from v$logmnr_contents l, accounts a
where table_name = 'ACCOUNTS'
and operation = 'UPDATE'
and dbms_logmnr.column_present(undo_value,'ARUP.ACCOUNTS.ACC_MGR_ID') =1
and dbms_logmnr.column_present(redo_value,'ARUP.ACCOUNTS.ACC_MGR_ID') =1
and a.rowid = l.row_id;
A FIRST_NAME LAST_NAME NEW_VALUE OLD_VALUE
________________ _____________ _____________ ____________
D Ellen Smith 5 6
M Ellen Smith 5 10
D Ellen Smith 5 7
...
이 SQL 구문을 주의 깊게 살펴 보시기 바랍니다. DBMS_LOGMNR 패키지가 제공하는 함수들이 추가로 사용되고 있습니다. 첫 번째로 MINE_VALUE 함수는 지정된 컬럼의 변경 이전 또는 이후의 값을 조회합니다. 예를 들어, "DBMS_LOGMNR.MINE_VALUE (UNDO_VALUE, 'ARUP.ACCOUNTS.ACC_MGR_ID')" 구문을 실행하면 변경 이전의 ACC_MGR_ID 컬럼 값을 확인할 수 있습니다. 여기서 UNDO_VALUE 매개변수를 REDO_VALUE로 변경하면 변경 이후의 값을 확인할 수 있습니다.
COLUMN_PRESENT 함수는 컬럼이 존재하는 경우 1을, 그렇지 않은 경우 0을 반환합니다. ACCOUNTS 테이블과 조인하는 경우, 이 함수를 통해 account에 관련된 상세 정보를 확인할 수 있습니다.
Listing 3의 출력 결과에서 ACC_MGR_ID, ACC_TYPE, 그리고 이름(FIRST_NAME, LAST_NAME)의 변경 이전/이후 값을 확인할 수 있습니다. 존은 이 출력 결과를 통해 전반적인 피해 상황을 한 눈에 확인하고 해결책을 마련할 수 있습니다. Listing 3의 SQL 구문을 이용하여, 존은 ACC_MGR_ID 값을 기존의 값으로 선택적으로 복구하기 위한 간단한 PL/SQL 코드를 작성합니다. 또 그 전에 ACC_TYPE, FIRST_NAME과 같은 컬럼을 확인하는 절차가 필요합니다. 작성된 코드가 Listing 4와 같습니다.
Listing 4: SQL_UNDO 실행을 위한 PL/SQL 코드
SQL> begin
2 for undo_rec in (
3 select sql_undo, row_id
4 from v$logmnr_contents
5 where table_name='ACCOUNTS' and seg_owner='ARUP' and operation='UPDATE'
6 and dbms_logmnr.column_present(undo_value,'ARUP.ACCOUNTS.ACC_MGR_ID')=1
7 and dbms_logmnr.column_present(redo_value,'ARUP.ACCOUNTS.ACC_MGR_ID')=1
8 )
9 loop
10 begin
11 execute immediate undo_rec.sql_undo;
12 exception
13 when no_data_found then
14 dbms_output.put_line('RowId '||undo_rec.row_id||' Failed.');
15 end;
16 end loop;
17 end;
원격 마이닝
지금까지 운영 환경의 소스 데이터베이스 상에서 LogMiner를 이용하는 방법에 대해 논의하였습니다. 존은 보안 환경의 개선을 위해 아카이브 로그를 다른 데이터베이스로 이전하고 그곳에서 마이닝을 수행하고 합니다. 이러한 방법은 (원격 데이터베이스는 소스 데이터베이스의 DBA 계정으로 접근할 수 없으므로) 악의를 가진 데이터베이스 관리자가 자신의 커맨드 실행 기록을 함부로 삭제하기 어렵다는 장점이 있습니다. 원격 마이닝은, 마이닝 활동을 별도의 데이터베이스에서 오프로드 처리함으로써 소스 데이터베이스에 미치는 성능 영향 요소를 제거할 수 있다는 또 다른 이점을 제공하기도 합니다.
하지만 현재의 설정에서 먼저 해결하고 넘어가야 할 한 가지 작은 문제가 있습니다. 위의 시나리오에서, 존은 리두 로그 오브젝트의 이름을 16진수 값에서 읽고 이해할 수 있는 이름으로 변환하기 위해 온라인 데이터 딕셔너리를 이용했습니다. 하지만 원격 데이터베이스에서 마이닝을 수행하는 동안에는 소스 데이터베이스의 온라인 딕셔너리를 이용할 수 없다는 것이 문제입니다.
존이 선택할 수 있는 해결책은 두 가지가 있습니다.
- LogMiner가 리두 로그 파일에 온라인 데이터 딕셔너리 정보를 기록하도록 함으로써, 결과적으로 딕셔너리 정보를 아카이브 로그에 저장하고 원격 서버에서 활용할 수 있습니다.
- 소스 데이터베이스의 데이터 딕셔너리를 플랫 파일로 다운로드한 후 원격 서버에서 활용할 수 있습니다.
두 가지 방법 모두 온라인 데이터 딕셔너리를 (리두 로그의 형태든 플랫 파일로든) 다운로드해야 한다는 공통점을 갖습니다. 이 작업은 DBMS_LOGMNR_D 패키지를 이용하여 수행 가능합니다. 이 패키지가 현재 환경에 존재하지 않는 경우, $ORACLE_HOME/rdbms/admin 디렉토리의 dbmslmd.sql을 실행하면 패키지가 생성됩니다.
존이 첫 번째 대안(소스 데이터 딕셔너리를 리두 로그에 저장)을 선택한 경우 실행해야 하는 커맨드가 아래와 같습니다.
begin
dbms_logmnr_d.build (
options => dbms_logmnr_d.store_in_redo_logs
);
end;
이 옵션을 활성화하려면 데이터베이스가 아카이브로그 모드로 설정되어 있어야 합니다. 아카이브로그 모드는 딕셔너리 정보를 온라인 리두 로그에 추가하며, 결과적으로 딕셔너리 정보는 아카이브 로그에 저장됩니다. 존은 FTP와 같은 파일 전송 유틸리티를 사용하여 아카이브 로그 파일을 원격 서버로 이동시킨 뒤, dbms_logmnr 패키지의 add_logfile 프로시저를 실행하는 과정에서 이 파일을 포함시킵니다.
그런 다음, 존은 조금 다른 매개변수를 사용하여 LogMiner 세션을 시작합니다.
dbms_logmnr.start_logmnr( options =>
dbms_logmnr.dict_from_redo_logs
);
존이 두 번째 대안(플랫 파일에 데이터 딕셔너리를 저장)을 선택한 경우라면, 새로운 딕셔너리를 생성할 파일을 지정해야 합니다. 오라클 데이터베이스에서 UTL_FILE 패키지를 이용하여 파일을 디렉토리에 저장하기 위해서는 UTL_FILE_DIR 초기화 매개변수를 통해 파일의 위치가 지정되어야 합니다. 이 값이 설정되어 있지 않다면, /tmp와 같은 디렉토리를 선택하여 아래와 같이 지정할 수 있습니다.
utl_file_dir = '/tmp'
그런 다음, 존은 소스 데이터베이스 인스턴스를 재시작하여 매개변수를 반영시킨 뒤, 아래와 같이 실행하여 새로운 딕셔너리를 생성합니다.
begin
dbms_logmnr_d.build (
dictionary_filename => 'ora_dict.txt',
dictionary_location => '/tmp'
);
end;
이 명령은 /tmp 디렉토리에 ora_dict.txt라는 이름의 파일을 생성합니다. 존은 소스 데이터베이스 서버의 /tmp/ora_dict.txt 파일을 원격 데이터베이스 서버의 /tmp 디렉토리로 전송한 뒤 아래와 같이 실행하여 LogMiner 세션을 시작합니다.
dbms_logmnr.start_logmnr(dictfilename => '/tmp/ora_dict.txt');
새로운 dictfilename 매개변수가 앞에서 생성된 딕셔너리 파일을 가리키고 있음을 주목하시기 바랍니다. 이 경우 온라인 딕셔너리 또는 리두 로그로부터 정보를 읽어 오지 않으므로 OPTIONS 매개변수를 사용할 필요가 없습니다. 단 SQL의 포맷팅, 세미콜론의 제거 등과 같은 작업을 위해 OPTIONS 매개변수를 사용할 수는 있습니다.
어떤 방법이 더 효과적일까요? 첫 번째 방법에서는 리두 스트림에 딕셔너리가 포함되어 있으므로 마이닝 과정에서 리두 로그에 접근할 수 있어야만 합니다. 작은 크기의 로그 파일을 마이닝하는 경우라면 리두 로그를 마이닝 대상에 포함시키는 것이 번거로울 수 있습니다. 두 번째 방법에서는 마이닝 대상 리두 로그 파일의 수에 관계없이 플랫 파일에 대한 접근이 가능해야만 합니다. 하지만 UTL_FILE_DIR 초기화 매개변수가 설정되어 있지 않다면 플랫 파일을 이용할 수 있는 방법이 없으며, 이런 경우라면 리두 로그를 사용할 수 밖에 없습니다.
Supplemental Logging
이번에는 앞에서 설명한 것과 조금 다른 시나리오를 살펴 보기로 하겠습니다. ACCOUNTS 테이블이 ACC_MGR_ID 컬럼을 기준으로 영역 파티셔닝(range partitioning) 되어 있고, 각각의 ACC_MGR_ID 컬럼 값에 별도의 파티션이 생성되었다고 가정해 봅시다. 이런 환경에서 조가 ACC_MGR_ID 컬럼을 업데이트한다면 여러 파티션이 동시에 변경될 것입니다. 예를 들어, ACC_MGR_ID = 6인 로우는 6번째 파티션으로, 컬럼 값이 3인 로우는 3번째 파티션으로 업데이트됩니다. ROWID는 로우의 물리적 위치를 기준으로 하므로, 파티션 환경에서는 로우의 ROWID가 업데이트 과정에서 변경됩니다.
이런 경우라면 어떻게 해야 할까요?
Listing 2에서 LogMiner 세션을 통해 추출한 undo 구문을 실행하면, —
update "ARUP"."ACCOUNTS" set "ACC_MGR_ID" = '6' where "ACC_MGR_ID" = '3' and ROWID = 'AAAOKeAAcAAAH8MAA7';
—아무 것도 변경되지 않을 것입니다. 위에서 얻어진 ROWID가 현재의 ROWID 값과 일치하지 않기 때문입니다. 설상가상으로, ROWID는 일치하지만 실제로는 전혀 관계 없는 레코드가 업데이트되어 버릴 수도 있습니다. 따라서 파티셔닝된 환경에서 로우를 변경할 때 ROWID를 사용해서는 안됩니다.
로그 파일에 프라이머리 키 정보를 기록하기 위해서는 supplemental logging을 활성화해야 합니다.
Supplemental logging은 리두 로그에 추가적인 컬럼 값을 기록하기 위한 옵션입니다. Supplemental logging을 활성화하려면 아래와 같이 실행합니다.
alter database add supplemental log data (primary key) columns;
이 구문은 리두 로그에 프라이머리 키 값이 함께 기록되도록 설정합니다. 하지만 위 구문은 구문이 실행된 이후에 발생한 변경 작업에만 유효하며 소급 적용은 불가능함을 참고하시기 바랍니다. 변경 작업이 수행되기 전에 위의 구문을 실행해 두었다면, Listing 2에서 출력된 SQL_UNDO 컬럼 값을 통해 다음과 같은 정보를 확인할 수 있을 것입니다.
update "ARUP"."ACCOUNTS" set "ACC_MGR_ID" = '6' where "ACC_NO" = '5720' and "ACC_MGR_ID" = '3' and ROWID = 'AAAOKeAAcAAAH8MAA7';
이제 SQL_UNDO 컬럼에 프라이머리 키, ACC_NO가 ROWID와 함께 표시되고 있으므로 정확하게 로우를 참조하는 것이 가능합니다.
존은 supplemental logging을 이용해서 LogMiner로부터 프라이머리 키 값을 얻어 내는데 성공했습니다. 테이블에 프라이머리 키가 존재하지 않는다면, 전체 컬럼에 supplemental logging을 적용함으로써 각 로우에 대해 유니크한 값을 얻어낼 수 있습니다. 단, 추가적인 정보를 기록함으로써 데이터베이스 서버에 성능 부담이 발생할 수 있으므로, 적용 여부를 신중하게 고려해야 합니다.
결론
오라클 리두 로그는 데이터베이스 내부에서 발생된 모든 변경 작업을 소상히 기록하고 있습니다. LogMiner는 저장된 로그 파일로부터 정보를 추출하고, 사용자 트랜잭션을 확인하거나 취소 처리할 수 있는 유용한 툴입니다.
Arup Nanda (arup@proligence.com)는 뉴욕 와이트 플레인에 위치한Starwood Hotels and Resorts의 데이터베이스 엔지니어링 및 운영 담당 디렉터입니다. 오라클 매거진에 의해 "2003년 올해의 DBA"로 선정된 그는
의 공저자이기도 합니다. |
Bookmark
