DBMS 1
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
과거에 실행된 트랜잭션과 관련된 종속 트랜잭션을 확인하고 롤백 처리하는 방법을 배워 보십시오. LogMiner는 (종종 간과되곤 하지만) 오라클 데이터베이스가 제공하는 매우 강력한 도구로서 활용됩니다. LogMiner를 이용하면 리두 로그 파일에서 DML 구문(트랜잭션을 발생시킨 SQL 구문 또는 트랜잭션을 취소(undo) 처리한 SQL 구문)을 추출할 수 있습니다. (LogMiner의 기본적인 기능과 동작 원리에 대한 설명은 필자가 오라클 매거진에 기고한 "단서의 마이닝(한글)") 아티클을 참고하시기 바랍니다. 하지만 최근까지 LogMiner는 그 인터페이스가 너무 복잡하다는 이유 때문에 자주 활용되지 못했습니다. 하지만 Oracle Database 11g의 Oracle Enterprise Manager에 LogMiner를 이용하여 리두 로그로부터 트랜잭션을 추출하기 위한 그래픽 인터페이스가 새로이 구현되었습니다. 이 인터페이스를 이용하면 LogMiner를 이용한 트랜잭션 추출 및 롤백 작업을 매우 쉽게 처리할 수 있습니다. (참고: 이전 버전에서 제공되던 DBMS_LOGMNR 패키지 기반의 커맨드 라인 인터페이스도 여전히 사용 가능합니다.) 이제 그 활용 방법을 살펴 보기로 합시다. 로그 마이닝을 활성화하려면, 먼저 데이터베이스 또는 (최소한) 테이블에 "supplemental log"의 활성화 과정을 거쳐야 합니다. 또 Flashback Transaction은 primary key log를 요구합니다. 전체 데이터베이스에 로깅을 활성화하려면 아래 명령을 실행합니다: 이제 애플리케이션에서 아래와 같은 구문이 실행되었다고 가정해 봅시다: 위 구문을 주의 깊게 살펴 보시기 바랍니다: 각각의 구문 뒤에 commit 구문이 뒤따르고 있습니다. 따라서 하나 하나의 구문이 별도의 트랜잭션을 구성합니다. 이제 Oracle Database 11g Database Control의 LogMiner에서 트랜잭션을 조회하는 방법을 설명하겠습니다. Enterprise Manager의 Database Homepage에서Availability탭을 클릭합니다. Manage하단의View and Manage Transactions를 클릭합니다. 그 결과로 아래와 같이 LogMiner 인터페이스가 표시됩니다: 여기서 시간 또는 SCN을 기준으로 검색 범위를 지정할 수 있습니다. 위의 그림에서, 필자는 Query Time Range메뉴를 통해 검색 대상 시간을 설정하였습니다. 또 Query Filter에서 SCOTT이 실행한 트랜잭션만을 조회하도록 설정하였습니다. 필요한 경우, Advanced Query 섹션에서 추가로 필터를 설정하는 것이 가능합니다. 모든 필드를 입력하였다면 Continue를 클릭합니다. 이제 Log Mining 프로세스가 실행되어 리두 로그에 대한 검색을 통해 SCOTT 사용자가 실행한 트랜잭션이 확인됩니다(필요한 경우 온라인 리두 로그 뿐 아니라 아카이브 로그도 함께 검색할 수 있습니다). 프로세스가 완료되면 그 결과가 화면으로 표시됩니다. 아래 그림은 결과로 표시된 화면의 상단 부분을 보여 주고 있습니다: 결과 화면을 통해, SCOTT에 의해 2개의 트랜잭션이 실행되었으며 2개의 레코드가 그 영향을 받았음을 확인할 수 있습니다. 스크린의 하단에는 트랜잭션의 상세 정보가 표시됩니다. 그 화면의 일부가 아래와 같습니다. 트랜잭션에 1 ins로 표시된 것은 "1 insert statement"를 의미합니다. 가장 왼쪽의 컬럼인 트랜잭션 ID(XID)는 트랜잭션의 식별자로 사용되는 숫자입니다. 트랜잭션 ID를 클릭하면 아래와 같이 트랜잭션의 상세 정보를 확인할 수 있습니다. 지금까지 Database Control을 이용하여 트랜잭션을 검색하고 조회하는 방법에 대해 알아 보았습니다.Previous Transaction버튼과Next Transaction버튼을 이용하면 스크롤을 통해 검색 결과로 반환된 트랜잭션들을 전체적으로 확인할 수 있습니다. 그렇다면 이 기능을 어떤 용도로 활용할 수 있을까요 물론 여러 가지 방법이 있습니다. 가장 중요한 활용 사례로 "누가", "무엇"을 실행했는지 확인해야 하는 경우를 들 수 있습니다. 성능 상의 문제를 고려하여 감사 기능을 활성화하지 않았거나 감사 기록이 제대로 보존되지 않았다면, LogMiner 인터페이스를 통해 리두 로그(온라인/아카이브)를 검색하는 것 이외에는 다른 방법을 찾을 수 없을 것입니다. 검색 스크린의Query Filter하단에 위치한Advanced Query에 추가적인 필터링 조건을 입력할 수 있습니다. RES_ID = 100002인 레코드가 삽입, 삭제 또는 업데이트된 트랜잭션을 검색해야 하는 경우를 가정해 봅시다. dbms_logmnr 패키지의 column_present 함수를 이용하면 특정 값을 기준으로 리두 스트림을 검색할 수 있습니다: 이 함수는 SCOTT 스키마의 RES 테이블에 포함된 RES_ID 컬럼 100002와 관련된 모든 트랜잭션을 추출해 냅니다. 또 이 기능을 이용하여 데이터베이스에 대해 실행된 DDL 커맨드를 추출해 낼 수도 있습니다. 이와 같이 하려면Query Filter섹션에서View DDL Only라디오 버튼을 선택합니다. 트랜잭션을 확인한 뒤에는 어떤 작업이 필요할까요 아마도 여러분이 찾고 있는 트랜잭션이 사용자에 의해 실수로 실행된 것일 수도 있을 것입니다. 그런 경우라면 트랜잭션을 롤백 처리해야 할 것입니다. 트랜잭션을 롤백 처리하는 방법은 간단합니다. 트랜잭션이 INSERT 작업이었다면 DELETE 작업을 수행하고, UPDATE 작업이었다면 기존의 값으로 다시 업데이트하면 됩니다. 하지만 먼저, 예제에서 사용된 트랜잭션을 주의 깊게 살펴 보시기 바랍니다. 첫 번째 트랜잭션은 로우를 INSERT 하고 있습니다. 두 번째 트랜잭션은 방금 INSERT 된 로우를 UPDATE하고 있으며, 세 번째 트랜잭션은 해당 로우를 DELETE 하였습니다. 여기서 첫 번째 트랜잭션을 롤백 처리하려 합니다. 하지만 한 가지 문제가 있습니다. 로우가 이미 후속 트랜잭션에 의해 DELETE 처리된 것입니다. 그렇다면 이러한 경우 트랜잭션의 언두 작업은 어떻게 진행되어야 할까요 바로 이런 경우에 Oracle Database 11g의 Dependent Transaction 조회 기능이 유용하게 활용됩니다. 먼저Flashback Transaction을 클릭합니다. 검색이 완료된 후 표시되는 화면이 아래와 같습니다: 이 스크린을 통해 종속 트랜잭션 및 업데이트, 삭제 작업을 확인할 수 있습니다. 이제 트랜잭션을 롤백하면서 종속 트랜잭션도 함께 롤백 처리할 수 있습니다. 이를 위해, 아래 목록에서Cascade라디오 버튼을 선택하고OK를 클릭합니다. 롤백 처리할 여러 개의 트랜잭션이 표시됩니다. Transaction ID를 클릭하면 오라클이 해당 트랜잭션을 롤백하기 위해 사용할 SQL 구문을 확인할 수 있습니다. 한 예로, INSERT 작업을 롤백 처리하려면 위와 같이 DELETE를 수행하면 됩니다. (바로 아래에 위치한) 다음 트랜잭션을 클릭하면 해당 트랜잭션을 롤백 처리하기 위해 필요한 작업이 무엇인지 확인할 수 있습니다. 이제 개념이 잡히셨을 것입니다. Submit을 클릭하면 모든 트랜잭션이 한 번에 롤백 처리됩니다. 이는 트랜잭션과 종속된 작업들을 한꺼번에 언두 처리하는 가장 깔끔한 방법입니다. 이번에는 Enterprise Manager에 접근할 수 없는 경우, 또는 스크립트를 이용하는 경우를 생각해 봅시다. Oracle Database 10g에서도 제공되었던 DBMS_FLASHBACK 패키지에, TRANSACTION_BACKOUT이라는 새로운 프로시저가 추가되었습니다. 이 프로시저를 실행하려면 아래와 같이 네임드 매개변수에 값을 전달해 주어야 합니다. xid_array 타입 또한 Oracle Database 11g에 새로 추가된 것입니다. 이 어레이 타입은 일련의 트랜잭션 ID를 프로시저에 전달하기 위해 활용됩니다. XMLType을 데이터 타입으로 사용해 온 사용자라면 Oracle Database 11g로 업그레이드 해야 할 이유가 한 가지 더 있습니다. 이제 LogMiner에서 XML 데이터를 마이닝할 수 있게 되었기 때문입니다. 결과는 SQL_REDO, SQL_UNDO 컬럼에 모두 표시됩니다. LogMiner에서 SKIP_CORRUPTION 옵션을 설정하면 리두 로그에서 손상된 블록을 생략(skip)하고 넘어 갈 수 있습니다. 따라서 부분적으로 손상된 리두 로그에서도 데이터를 추출해 내는 것이 가능합니다. 개선된 신택스의 활용 방법이 아래와 같습니다: 오라클은 Oracle9i Database Release 2를 통해 Flashback Query라는 일종의 "타임 머신" 기능을 처음으로 소개한 바 있습니다. Flashback Query는 데이터가 변경되기 이전 버전을 쉽게 확인할 수 있는 메커니즘을 제공합니다. 한 예로, 값을 100에서 200으로 변경한 후 커밋 처리했다고 가정해 봅시다. 사용자는 커밋이 완료된 이후에도 여전히 2분 전의 데이터를 조회할 수 있습니다. 이 기능은 언두 세그먼트에서 변경 이전의 데이터를 추출해 내는 방법으로 동작합니다. Oracle Database 10g에서는 Flashback Versions Query라는 좀 더 개선된 기능이 제공되기 시작했습니다. Flashback Versions Query에서는 변경 기록이 언두 세그먼트에 여전히 존재하는 한 로우에 대한 변경 내역을 추적하는 것이 가능합니다. 하지만 한 가지 문제가 있습니다. 데이터베이스가 리사이클 처리되면 언두 데이터는 삭제되며 따라서 변경 이전의 기록이 지워집니다. 데이터베이스가 리사이클 되지 않는다 해도, 데이터의 보존 기간이 오래 지나면 언두 세그먼트에서 공간 확보를 위해 기록을 삭제할 수 있습니다. 11g 이전 버전의 플래시백 작업은 전적으로 언두 데이터에 의존하고 있었으며, 따라서 장시간에 걸친 활용이 어려울 뿐 아니라 감사와 같은 목적으로는 아예 사용이 불가능하다는 문제가 있었습니다. 이러한 문제에 대한 우회책으로, DBA들은 트리거를 사용하여 변경 내역을 영구적으로 데이터베이스에 저장하는 방법을 사용하곤 했습니다. 벌써부터 실망하실 필요는 없습니다. Oracle Database 11g는 이 두 가지 접근법의 장점을 절묘하게 조합한 Flashback Data Archive 기능을 새로이 제공하고 있습니다. Flashback Data Archive는 플래시백 쿼리의 단순성, 강력한 기능을 제공하는 동시에 리두 로그와 같은 비영구적인 저장 공간의 문제를 해결하고 있습니다. Flashback Data Archive는 영구적인 저장 공간인 Flashback Recovery Area에 변경 내역을 저장합니다. 예를 들어 설명해 보기로 하겠습니다. (참고: Flashback Data Archive를 이용하려면 Automatic Undo Management 기능이 활성화되어 있어야 합니다.) 먼저 아래와 같은 방법으로 Flashback Data Archive를 생성합니다: 여기서 "retention"이 갖는 의미는 나중에 설명하도록 하겠습니다. 아카이브는 far_near_term 테이블스페이스에 생성됩니다. 이제 TRANS 테이블에 발생한 변경 사항을 기록해야 하는 경우를 가정해 보겠습니다. 관리자는 이 테이블에 Flashback Data Archive를 활성화하기만 하면 됩니다. 이제 TRANS 테이블은 Flashback Data Archive 모드로 전환됩니다. 테이블의 로우에 대한 모든 변경 내역은 영구적으로 추적됩니다. 간단한 예를 확인해 봅시다. 먼저 테이블의 특정 로우를 선택합니다. 이제 이 로우의 컬럼을 조회하면 2000이라는 값이 확인될 것입니다. 과거 특정 시점의 값을 확인하려면 아래와 같이 Flashback 쿼리를 실행하면 됩니다. 시간이 어느 정도 흘러 언두 세그먼트에 기록된 언두 데이터가 삭제되고 난 다음, 다시 플래시백 쿼리를 실행해 봅니다: 확인된 결과는 19325.67입니다. 언두 데이터가 삭제되었는데 어디서 이 데이터를 얻은 것일까요 오라클에 직접 물어보기로 합시다. autotrace 기능을 이용하여 실행 계획을 조회한 결과가 아래와 같습니다: 위 결과를 통해 수수께끼가 풀렸습니다. 앞에서 정의한 Flashback Archive에 포함된 SYS_FBA_HIST_68909 테이블에서 데이터가 전달된 것입니다. 테이블을 직접 확인할 수도 있겠지만, 오라클은 Flashback Archive 데이터의 직접적인 조회 기능을 제공하고 있지 않습니다. 그리고 굳이 그래야 할 이유도 없습니다. 아카이브에 저장된 데이터는 얼마나 오랫동안 보존되는 것일까요 "retention" 옵션은 바로 이러한 목적을 위해 사용됩니다. 데이터는 "retention" 옵션에 설정된 기간 동안 보존됩니다. 보존 기간이 만료되고 새로운 데이터가 입력되면 오래된 데이터는 자동으로 삭제됩니다. 또 아래와 같은 명령으로 직접 데이터를 삭제할 수도 있습니다: 아카이브에 두 개 이상의 테이블스페이스를 추가할 수도 있습니다. 또는 역으로, 아카이브에서 특정 테이블스페이스를 제거하는 것도 가능합니다. 다른 사용자 데이터를 함께 포함하고 있는 테이블스페이스를 사용하는 경우, 테이블스페이스에 Flashback Data Archive 데이터가 가득 차, 사용자 데이터를 위한 공간이 남지 않는 문제가 발생할 수 있습니다. 이러한 위험을 줄이려면, 아카이브가 테이블스페이스에서 사용하는 공간의 쿼타를 설정해야 합니다. 쿼타 설정 방법이 다음과 같습니다: 딕셔너리 뷰를 조회하면 어떤 테이블에 Flashback Data Archive가 활성화되었는지 확인할 수 있습니다: 여러 개의 아카이브를 동시에 생성한 경우, 각각의 아카이브를 상황에 맞는 용도로 효과적으로 활용할 수 있다는 장점이 있습니다. 예를 들어, 호텔 기업의 데이터베이스에서 예약 기록은 1년 동안, 결제 기록은 3년간 보존해야 할 수 있습니다. 이런 경우 여러 개의 아카이브에 각각 다른 보존 기간을 설정하고 별도의 테이블에 할당할 수 있습니다. 또는 보존 기간이 일정하다면 하나의 아카이브만 만들고 디폴트 설정으로 활용할 수 있을 것입니다. 테이블의 아카이브 설정을 해제하려면 아래와 같이 실행합니다: 지금까지 확인한 것처럼, Flashback Data Archive를 이용하면 단 한 줄의 코드도 작성하지 않고 강력한 변경 기록 시스템을 구현할 수 있습니다. Flashback Data Archive가 일반적인 감사(auditing) 기능과는 어떻게 다른 것일까요 먼저, 감사 기능은 audit_trail 매개변수를 DB 또는 DB_EXTENDED로 설정해야 하며, 감사 결과가 SYSTEM 테이블스페이스의 AUD$ 테이블에 기록된다는 차이가 있습니다. Flashback Data Archives는 어떤 테이블스페이스에든 설정이 가능하고, 사용되는 테이블스페이스의 수에도 제한이 없으며, 따라서 저가형 스토리지에도 구 기능은 "autonomous" 트랜잭션을 기반으로 수행되며 따라서 어느 정도의 성능 오버헤드가 필연적으로 수반됩니다. Flashback Data Archives는 FBDA라는 별도의 백그라운드 프로세스에 실행되며, 따라서 성능 부담이 더 적습니다. 마지막으로, Flashback Data Archives는 일정 주기로 자동 삭제가 가능합니다. 반면, 감사 기록은 수작업으로 관리되어야만 합니다. Flashback Data Archive는 여러 가지 목적으로 활용이 가능합니다. 그 몇 가지 예가 아래와 같습니다: 사용자에 의한 실수는 언제든 발생할 수 있습니다. 관리자는 트랜잭션 롤백 기능을 이용하여 사용자가 저지른 실수를 되돌릴 수 있습니다. Oracle Database 11g는 아카이브/온라인 리두 로그의 비영구성이라는 문제를 해결하고 Flashback Archive에 변경 내역을 영구적으로 저장할 수 있는 기능을 추가로 제공하고 있습니다. 이제 Flashback Recovery Area 기능이 제공하는 간단한 몇 가지 명령만으로 전체 테이블의 변경 내역을 관리하고 복구할 수 있게 되었습니다.LogMiner와 Flashback Data Archive를 이용한 트랜잭션 관리
LogMiner와 Flashback Data Archive를 이용한 트랜잭션 관리
간략한 개요
Oracle Enterprise Manager의 LogMiner 인터페이스
SQL> alter database add supplemental log data;
Database altered.
SQL> alter database add supplemental log data (primary key) columns;
Database altered.
SQL> insert into res values (100002,sysdate,12,1);
1 row created.
SQL> commit;
Commit complete.
SQL> update res set hotel_id = 13 where res_id = 100002;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete res where res_id = 100002;
1 row deleted.
SQL> commit;
Commit complete.
활용 사례
선택된 트랜잭션의 롤백 처리
커맨드 라인 인터페이스
declare
trans_arr xid_array;
begin
trans_arr := xid_array('030003000D040000','F30003000D04010');
dbms_flashback.transaction_backout (
numtxns => 1,
xids => trans_arr,
options => dbms_flashback.cascade
);
end;
다른 LogMiner 개선 사항
begin
dbms_logmnr.start_logmnr(
options => dbms_logmnr.skip_corruption
) ;
end;
Flashback Data Archive
SQL> create flashback archive near_term
2 tablespace far_near_term
3 retention 1 month
4 /Flashback archive created.
SQL> alter table trans flashback archive near_term;Table altered.SQL> select txn_amt from trans where trans_id = 2;TXN_AMT
----------
19325.67SQL> update trans set txn_amt = 2000 where trans_id = 2;
1 row updated.
SQL> commit;
Commit complete.
elect txn_amt
from trans
as of timestamp to_timestamp ('07/18/2007 12:39:00','mm/dd/yyyy hh24:mi:ss')
where trans_id = 2;TXN_AMT
----------
19325.67
select txn_amt
from trans
as of timestamp to_timestamp ('07/18/2007 12:39:00','mm/dd/yyyy hh24:mi:ss')
where trans_id = 2;
alter flashback archive near_term purge before scn 1234567;Flashback Archive의 관리
alter flashback archive near_term modify tablespace far_near_term quota 10M;
alter flashback archive near_term set default;alter table trans no flashback archive;일반 감사 기능과의 차이점
활용 사례
결론