Audit Trail Purge 2편 (자동 Purge)

안녕하세요 Thinking Man 입니다.

이번 글에서는 Audit Trail 데이터를 삭제하는 방법 중 자동으로 삭제하는 방법에 대해 알아보도록 하겠습니다.

이번 글을 읽게 되면 Audit Trail 을 자동 Purge 할 수 있게 스케쥴링하는 방법과 타임스탬프 설정 방법에 대해서 알 수 있습니다.

이 글은 Oracle 11gR2 공식 문서를 참고하여 작성하였습니다.

Audit Trail Purge

이번 글에서는 앞의 글 “수동 Purge” 에 이어서 “Audit Trail 자동 Purge” 방안에 대해서 작성해보도록 하겠습니다.

Audit Trail 자동 Purge

Audit Trail 데이터를 자동으로 특정 기간이면 지우도록 스케쥴링 할 수 있습니다. 이때 타임스탬프 이전에 생성된 데이터를 삭제하거나 전체 데이터를 삭제하거나 선택 할 수 있습니다.

이 Audit Trail Purge 작업은 작업량이 많은 경우에는 오랜시간 걸릴 수 있기 때문에 DB의 부하가 적은 업무 외 시간에 작업하는 것을 권고합니다.

Audit Trail 유형이 서로 충돌하지 않는다면 다른 유형의 Audit Trail Purge 작업을 여러 개 생성 할 수도 있습니다. 표준 감사 테이블과 상세 감사 테이블 Purge 작업을 각 각 생성하고 예약할 수 있습니다. DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD 또는 DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL 속성을 사용하여 표준 및 상세 감사 Trail 모두 삭제하는 작업은 생성 할 수 없습니다.

자동 Purge 작업을 생성하고 예약하려면 아래의 단계를 거쳐야 합니다.

단계 1 : 필요한 경우 Redo Logfile, Archive Log 사이즈 조정

Audit Trail 데이터 Purge 작업은 Redo 데이터를 생성하기 때문에 작업 수행 전에 추가로 생성될 Redo 데이터를 감안하여 수용할 만큼의 Redo Log 및 Archive 사이즈를 조정해야 할 수 있습니다. Purge 작업은 실제로 Audit 테이블의 데이터를 Delete 하는 작업을 수행하기 때문에 Redo 데이터가 생성됩니다.

Redo Log 사이즈 조정에 대한 정보는 Redo Log 관리와 Configuring a Database for Performance 의 “4.1.3 Sizing Redo Log Files” 부분을 참고하세요.

단계 2 : 아카이브 전략과 타임스탬프 계획 수립

Audit 레코드를 삭제하기 전에 DB 및 OS Audit 레코드의 타임스탬프를 지정해야 합니다. DBA_AUDIT_MGMT_LAST_ARCH_TS 뷰를 조회하면 타임스탬프 날짜 확인이 가능합니다. 타임스탬프를 지정하는 이유는 원하는 날짜 이전에 Audit 데이터를 삭제하고자 할때 이 타임스탬프로 지정된 날짜보다 이전에 생성된 Audit Trail 레코드를 삭제하기 때문입니다.

타임스탬프 이전에 데이터를 삭제하는 방법은 단계 4를 참고하세요.

타임스탬프 지정 이후 Audit 레코드를 Archiving 하면 됩니다. Archiving 방법은 아래 링크를 참조하세요.

– Audit Trail 관리 1편의 DB Audit Trail Archiving 챕터 참고

– Audit Trail 관리 2편의 OS Audit Trail Archiving 챕터 참고

단계 3: Audit Trail Purge 작업 초기화

Audit Trail Purge 작업을 수행 전에 초기화 작업을 먼저 해야 됩니다.

DB Audit Trail 의 경우에는 SYSTEM 테이블스페이스에 저장된 Audit 테이블(SYS.AUD$, SYS.FGA_LOG$)을 다른 테이블스페이스로 이동하지 않았다면 테이블스페이스를 먼저 이동해야 합니다. DB Audit Trail 테이블의 테이블스페이스 이동은 여기를 참고하세요. TBS 이동 시 Audit 테이블의 사이즈가 크다면 오랜 시간이 걸릴 수 있으니 업무 외 시간에 작업하는 것을 권고합니다.

Audit Trail 초기화 작업은 아래 순서대로 수행합니다.

DBMS_AUDIT_MGMT 패키지에 EXECUTE 권한이 있는 관리자 사용자로 SQL*Plus에 로그인합니다.
INIT_CLEANUP 프로시저를 실행하여 Audit Trail Purge 작업을 초기화합니다. (이 단계는 한 번만 수행해야 합니다.)

초기화 작업이 되었는지 확인하려면 DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED 프로시저를 수행해서 확인 가능합니다. 초기화 확인 예제는 다음 글을 참고하세요.

예제)

BEGIN DBMS_AUDIT_MGMT.INIT_CLEANUP(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  DEFAULT_CLEANUP_INTERVAL    => 24 );
END;
/

예제 속성 설명

AUDIT_TRAIL_TYPE : 다음 값 중 하나를 입력합니다.

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD : 표준 감사 테이블 AUD$를 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD : 상세 감사 테이블 FGA_LOG$를 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD : 표준 및 상세 감사 테이블을 모두 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS : .aud 확장자를 가진 OS Audit 파일 지정 (이 설정은 Windows 이벤트 로그 항목에는 적용되지 않습니다.)

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML : XML 형식의 OS Audit 파일 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES : OS 및 XML Audit 파일 모두 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL : DB Audit Trail 및 OS Audit Trail 유형의 모든 Audit Trail 지정

DEFAULT_CLEANUP_INTERVAL : 단위는 시간이며 원하는 정리 시간 간격을 지정 (예 : 24시간마다 정리를 원한다면 24로 설정). DBMS_AUDIT_MGMT 프로시저에서 이 값을 확인하여 감사 레코드 삭제 방법을 결정

시간 간격이 정해지는 타이밍은 DBMS_AUDIT_MGMT.INIT_CLEANUP 프로시저를 수행한 시점으로부터 설정됩니다.

DEFAULT_CLEANUP_INTERVAL에 설정된 값에 따라 DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL 프로시저 호출 빈도가 지정되는 것입니다.

초기화 이후에 정리 시간 간격을 변경하고자 한다면 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY 프로시저의 DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL 속성을 사용하여 변경 가능합니다.

주의해야 할 점은 삭제 작업을 수행하는 시간 간격과 그 시간 간격 동안 쌓일 것으로 예상되는 Audit 레코드를 고려해서 정리 시간 간격을 지정해야 합니다.

단계 4 : 필요한 경우 아카이브 타임스탬프 설정

Audit Trail의 모든 데이터를 삭제하고자 한다면 이 단계는 건너뛰어도 괜찮습니다.

마지막 감사 레코드가 아카이브된 시간을 설정할 수 있습니다. 아카이브 타임스탬프를 설정하면 정리 작업이 6시간마다 실행될 것임을 정리 인프라에 힌트를 제공합니다.

DB Audit Trail의 경우는 Audit Trail Purge 작업 초기화한 이후에 타임스탬프를 설정해야 합니다. 타임스탬프 설정 후에 DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL 패키지를 수행하면 설정한 타임스탬프 이전 시간의 Audit 데이터들이 삭제됩니다. 설정된 마지막 아카이브 타임스탬프를 찾으려면 DBA_AUDIT_MGMT_LAST_ARCH_TS 뷰를 조회하여 확인 할 수 있습니다. 설정된 아카이브 타임스탬프를 삭제하는 방법도 존재하며 이후 글에서 다룰 예정입니다.

OS Audit Trail의 경우 OS Audit 파일(XML 포함)의 개별 레코드를 삭제할 수 없습니다. OS Audit은 파일에 여러 Audit 데이터가 기록되기 때문에 파일 자체가 삭제됩니다. 타임스탬프 설정 시 해당 타임스탬프 데이터를 포함하는 파일이 삭제됩니다.

Oracle RAC의 경우에는 인스턴스 간에 서버 시간을 동기화하기 위해 NTP(Network Time Protocol)를 사용할 수 있습니다. RAC 2Node 환경에서 1번 Node의 시간이 12:00:00 이고 2번 Node 시간이 12:10:00 이라면 두 Node는 일치 하지 않는 시간을 갖게되어 시스템에 많은 영향을 초래할 수 있습니다. 이러한 이유로 인스턴스간 시간 동기화를 위해 NTP를 사용합니다.

타임스탬프를 설정하기 위해서는 DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP 프로시저를 사용하며 아래 예제를 통해 설명하겠습니다.

예제)

BEGIN  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   AUDIT_TRAIL_TYPE     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   LAST_ARCHIVE_TIME    =>  '2024-07-30 09:00:00.00',
   RAC_INSTANCE_NUMBER  =>  0 );
END;
/

예제 속성

AUDIT_TRAIL_TYPE : 다음 값 중 하나를 입력합니다

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD : 표준 감사 테이블 AUD$를 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD : 상세 감사 테이블 FGA_LOG$를 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS : .aud 확장자를 가진 OS Audit 파일 지정 (이 설정은 Windows 이벤트 로그 항목에는 적용되지 않습니다.)

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML : XML 형식의 OS Audit 파일 지정

LAST_ARCHIVE_TIME : 표준 감사 테이블 및 상세 감사 테이블의 경우 YYYY-MM-DD HH:MI:SS.FF UTC(협정 세계시간) 형식으로 타임스탬프를 입력하고 OS 및 XML의 경우 로컬 시간대로 입력

RAC_INSTANCE_NUMBER : Oracle RAC 설치에 대한 인스턴스 번호를 지정. 표준 감사 또는 상세 감사의 경우 RAC_INSTANCE_NUMBER 인수를 생략할 수 있음. RAC, 싱글 인스턴스와 상관없이 AUD$, FGA_LOG$ 테이블은 하나만 존재하기 때문. Default 값은 싱글 인스턴스 DB에서 사용되는 0.

타임스탬프 설정 후 타임스탬프 날짜 이전 데이터를 아래 5단계 6단계 없이 바로 삭제하려면 DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL 패키지를 사용하여 삭제합니다.

단계 5: Purge 작업 생성 및 스케줄링

DBMS_AUDIT_MGMT.CREATE_PURGE_JOB 프로시저를 실행하여 Purge 작업을 생성하고 스케줄링합니다.

Purge 작업을 생성하고 스케줄링하려면 DBMS_AUDIT_MGMT.CREATE_PURGE_JOB 프로시저를 사용합니다. 아래 예제를 참고하세요.

예제)

BEGIN  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
   AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   AUDIT_TRAIL_PURGE_INTERVAL  => 24,
   AUDIT_TRAIL_PURGE_NAME      => 'Standard_Audit_Trail_TK',
   USE_LAST_ARCH_TIMESTAMP     => TRUE );
END;
/

예제 속성

AUDIT_TRAIL_TYPE : 다음 값 중 하나를 입력합니다

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD : 표준 감사 테이블 AUD$를 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD : 상세 감사 테이블 FGA_LOG$를 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD : 표준 및 상세 감사 테이블을 모두 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS : .aud 확장자를 가진 OS Audit 파일 지정 (이 설정은 Windows 이벤트 로그 항목에는 적용되지 않습니다.)

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML : XML 형식의 OS Audit 파일 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES : OS 및 XML Audit 파일 모두 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL : DB Audit Trail 및 OS Audit Trail 유형의 모든 Audit Trail 지정

AUDIT_TRAIL_PURGE_INTERVAL : Purge 작업의 수행 간격을 지정. CREATE_PURGE_JOB 프로시저를 수행한 시점부터 시간이 계산됨. 위 예제는 24시간 이후 수행. 추후에 이 시간 값을 수정하려면 DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL 프로시저를 수행하여 수정할 수 있습니다.

USE_LAST_ARCH_TIMESTAMP : 삭제할 타임스탬프 기준을 선택. TRUE, FALES 중 하나를 입력

– TRUE : 최종 아카이브 타임스탬프 이전에 생성된 Audit 데이터를 삭제합니다. Default 값은 TRUE 이며 Oracle 에서는 USE_LAST_ARCH_TIMESTAMP를 TRUE로 설정하는 것을 권고합니다.

– FALSE : 최종 아카이브 타임스탬프를 고려하지 않고 모든 Audit 데이터를 삭제합니다. 삭제되면 안되는 Audit 데이터를 실수로 삭제할 수 있으니 주의해서 사용하시기 바랍니다.

단계 6: Audit Trail 을 일괄 삭제도 가능 (선택적 사용)

Purge 작업을 수행할 때 전체 데이터를 모두 한번에 삭제할 수 있지만 Audit Trail 데이터가 매우 큰 경우 일괄 삭제 처리 단위를 지정할 수 있습니다. 삭제 처리 단위를 지정하면 매우 커진 Audit Trail 데이터를 삭제할 때 용이할 수 있습니다.

DBMS_AUDIT_MGMT 패키지는 DB 및 OS Audit Trail 레코드를 Default 로 1만건 DB 레코드, 1000개의 OS 감사 파일 단위로 일괄 삭제를 합니다. 이 Default 값을 확인하려면 DBA_AUDIT_MGMT_CONIFG_PARAMS 뷰의 ‘DB AUDIT CLEAN BATCH SIZE’, ‘OS FILE CLEAN BATCH SIZE’ 값을 조회하면 됩니다.

삭제 단위를 변경할 수 있으며 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY 프로시저를 사용해서 변경 가능합니다. 변경한 삭제 단위 설정을 지울 수도 있으며 이는 다음글에서 다룰 예정입니다.

삭제 단위 변경 예제) 표준 감사 테이블 AUD$의 일괄 삭제 단위를 20만건으로 설정

BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  AUDIT_TRAIL_PROPERTY        => DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
  AUDIT_TRAIL_PROPERTY_VALUE  => 200000);
END;
/

이 예에서:

AUDIT_TRAIL_TYPE : 다음 값 중 하나를 입력합니다

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD : 표준 감사 테이블 AUD$를 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD : 상세 감사 테이블 FGA_LOG$를 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS : .aud 확장자를 가진 OS Audit 파일 지정

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML : XML 형식의 OS Audit 파일 지정

AUDIT_TRAIL_PROPERTY : Audit Trail 일괄 삭제 사이즈를 변경할 속성을 지정. DB Audit Trail 크기 설정은 DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE 을 지정. OS Audit Trail 크기 설정은 DBMS_AUDIT_MGMT.FILE_DELETE_BATCH_SIZE 을 지정

AUDIT_TRAIL_PROPERTY_VALUE : 일괄 삭제 처리할 단위를 입력. 100 ~ 1,000,000 사이의 값 입력 가능. 삭제 될 총 레코드, 파일 개수와 Purge 작업이 수행되는 시간 간격을 고려해서 설정 필요. Default 값은 DB Audit Trail 1만건, OS Audit Trail 1,000건