Oracle AUD$ 테이블 감사 로그 실습

안녕하세요 Surmin 입니다
이번 내용은 AUD$ 관련 테스트 내용입니다. 작업 시 참고하시면 유용하게 활용하실 수 있을 것입니다.

데이터베이스 접속 및 기본 설정

# Oracle 사용자로 접속         
sqlplus / as sysdba 

-- 현재 인스턴스 정보 확인
SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
--------------------------------
ORA19C


#감사 설정
ALTER SYSTEM SET audit_trail = 'DB' SCOPE = SPFILE;

SHUTDOWN IMMEDIATE
STARTUP

-- SQLPlus 출력 포맷 설정
SET LINESIZE 120        
SET PAGESIZE 50         
SET COLSEP ' | '        

-- 컬럼 포맷 설정
COLUMN NAME FORMAT A50    
COLUMN VALUE FORMAT A50   
COLUMN TYPE FORMAT A20   

-- 감사 설정 상태 확인
-- 만약 SQL문도 보고 싶다면 DB, EXTENDED로 설정 
SHOW PARAMETER AUDIT_TRAIL;

NAME                                 | TYPE                 | VALUE
------------------------------------ | -------------------- | ------------------------------
audit_trail                          | string               | DB


SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE '%audit%';

NAME                                               | VALUE
-------------------------------------------------- | --------------------------------------------------
audit_sys_operations                               | TRUE
unified_audit_sga_queue_size                       | 1048576
audit_file_dest                                    | /app/oracle/admin/ORA19C/adump
audit_syslog_level                                 |
unified_audit_systemlog                            |
unified_audit_common_systemlog                     |
audit_trail                                        | DB

-- 현재 감사 로그 개수
SELECT COUNT(*) FROM SYS.AUD$;
  COUNT(*)
----------
        13

COLUMN MIN_DATE FORMAT A30    
COLUMN MAX_DATE FORMAT A30   
-- 개수 컬럼에 천단위 구분자
COLUMN TOTAL_COUNT FORMAT 999,999,999  

-- 감사 로그 날짜 범위 (데이터가 있는 경우)
SELECT 
    MIN(NTIMESTAMP#) AS MIN_DATE, 
    MAX(NTIMESTAMP#) AS MAX_DATE, 
    COUNT(*) AS TOTAL_COUNT 
FROM SYS.AUD$ 
WHERE NTIMESTAMP# IS NOT NULL;

--최소날짜                       --최대날짜                            --토탈갯수
MIN_DATE                       | MAX_DATE                       |  TOTAL_COUNT
------------------------------ | ------------------------------ | ------------
06-JUN-25 09.46.09.884418 AM   | 08-JUN-25 07.14.11.485619 AM   |           13

-- 세션 관련 감사 활성화
AUDIT SESSION;
AUDIT CREATE SESSION;
AUDIT CONNECT;

-- 모든 DDL 명령어 감사 활성화
AUDIT ALL STATEMENTS;


SET LINESIZE 150
SET PAGESIZE 30
SET COLSEP ' | '

-- DBA_STMT_AUDIT_OPTS용 컬럼 포맷 설정
COLUMN USER_NAME FORMAT A15 HEADING "USER_NAME"
COLUMN AUDIT_OPTION FORMAT A25 HEADING "AUDIT_OPTION" 
COLUMN SUCCESS FORMAT A12 HEADING "SUCCESS"
COLUMN FAILURE FORMAT A12 HEADING "FAILURE"

-- 설정 확인
SELECT USER_NAME, AUDIT_OPTION, SUCCESS, FAILURE
FROM DBA_STMT_AUDIT_OPTS
ORDER BY AUDIT_OPTION;

USER_NAME       | AUDIT_OPTION              | SUCCESS      | FAILURE
--------------- | ------------------------- | ------------ | ------------
                | ALL STATEMENTS            | BY ACCESS    | BY ACCESS
                | ALTER ANY RULE            | BY ACCESS    | BY ACCESS
                | CREATE ANY RULE           | BY ACCESS    | BY ACCESS
HR              | CREATE SESSION            | BY ACCESS    | BY ACCESS
                | CREATE SESSION            | BY ACCESS    | BY ACCESS
                | EXECUTE ANY RULE          | BY ACCESS    | BY ACCESS
HR              | NOT EXISTS                | BY ACCESS    | BY ACCESS

=> 빈 칸 (NULL USER_NAME): 시스템 전체 감사 설정 - 모든 데이터베이스 사용자에게 적용
   HR: HR 사용자에게만 특별히 적용되는 감사 설정

특이점:

CREATE SESSION이 두 번 나타나는 것은:

모든 사용자에 대한 CREATE SESSION 감사 (USER_NAME = NULL)
HR 사용자에 대한 추가적인 CREATE SESSION 감사 (USER_NAME = HR)
이는 HR 사용자의 세션 생성이 중복으로 감사되고 있다는 의미입니다.

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4007.htm#SQLRF01107

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/AUDIT-Traditional-Auditing.html

ㄴ>doc [ALL STATEMENTS경우 외 SQL문이 있음 doc참조]

특정 테이블 DML 감사 설정

• 하지만! ALL STATEMENTS가 활성화되어 있으므로 실제로는 모든 DML이 감사됩니다.

-- Object Audit용 SQLPlus 포맷 설정
SET LINESIZE 120
SET PAGESIZE 30

-- 컬럼 포맷 설정
COLUMN OWNER FORMAT A10 HEADING "OWNER"
COLUMN OBJECT_NAME FORMAT A15 HEADING "OBJECT_NAME" 
COLUMN OBJECT_TYPE FORMAT A10 HEADING "TYPE"
COLUMN INS FORMAT A8 HEADING "INSERT"
COLUMN UPD FORMAT A8 HEADING "UPDATE" 
COLUMN DEL FORMAT A8 HEADING "DELETE"
COLUMN SEL FORMAT A8 HEADING "SELECT"
-- 객체별 감사 확인
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, INS, UPD, DEL, SEL
FROM DBA_OBJ_AUDIT_OPTS
WHERE OWNER = 'HR';

OWNER      | OBJECT_NAME     | TYPE       | INSERT   | UPDATE   | DELETE   | SELECT
---------- | --------------- | ---------- | -------- | -------- | -------- | --------
HR         | EMPLOYEES       | TABLE      | A/A      | -/-      | -/-      | S/S

=> 테스트 시 Delete 감사 설정되어 있지 않지만 ALL STATEMENTS 활성화로 인하여 감사가 확인됨 , 쿼리는 DB로 설정을 하였기 때문에 조회가 안됨 

TIME_           | USERID     | DDL_TYPE             | SCHEMA_    | OBJECT_NAME     | SQL_PREVIEW
--------------- | ---------- | -------------------- | ---------- | --------------- | ---------------------------------------------
07-29 16:41:17  | HR         | SELECT               | SYS        | AUD$            |
07-29 16:40:36  | HR         | DELETE               | HR         | LOCATIONS       |

자동 로그 생성 프로시저 생성

SET SERVEROUTPUT ON SIZE 1000000

-- 대량 감사 로그 생성 프로시저
CREATE OR REPLACE PROCEDURE generate_audit_logs(p_count NUMBER DEFAULT 1000)
IS
    v_sql VARCHAR2(4000);
    v_dummy NUMBER;  -- SELECT 결과를 받을 변수 추가
BEGIN
    -- DBMS_OUTPUT 활성화
    DBMS_OUTPUT.ENABLE(1000000);
    
    FOR i IN 1..p_count LOOP
        -- DDL 작업들
        BEGIN
            -- 테이블 생성
            v_sql := 'CREATE TABLE temp_audit_' || i || ' AS SELECT * FROM DUAL';
            EXECUTE IMMEDIATE v_sql;
            
            -- INSERT 작업
            v_sql := 'INSERT INTO temp_audit_' || i || ' SELECT * FROM DUAL';
            EXECUTE IMMEDIATE v_sql;
            
            -- SELECT 작업 (결과를 변수에 저장)
            v_sql := 'SELECT COUNT(*) FROM temp_audit_' || i;
            EXECUTE IMMEDIATE v_sql INTO v_dummy;
            
            -- 테이블 삭제
            v_sql := 'DROP TABLE temp_audit_' || i;
            EXECUTE IMMEDIATE v_sql;
            
        EXCEPTION 
            WHEN OTHERS THEN
                -- 테이블이 이미 존재하는 경우 등의 오류 처리
                BEGIN
                    EXECUTE IMMEDIATE 'DROP TABLE temp_audit_' || i;
                EXCEPTION WHEN OTHERS THEN
                    NULL; -- 삭제도 실패하면 무시
                END;
        END;
        
        -- 권한 작업 (10개마다)
        IF MOD(i, 10) = 0 THEN
            BEGIN
                -- 사용자 생성
                EXECUTE IMMEDIATE 'CREATE USER temp_user_' || i || ' IDENTIFIED BY "Pass123!"';
                
                -- 권한 부여
                EXECUTE IMMEDIATE 'GRANT CONNECT TO temp_user_' || i;
                
                -- 권한 회수  
                EXECUTE IMMEDIATE 'REVOKE CONNECT FROM temp_user_' || i;
                
                -- 사용자 삭제
                EXECUTE IMMEDIATE 'DROP USER temp_user_' || i;
                
            EXCEPTION 
                WHEN OTHERS THEN
                    -- 사용자 정리 시도
                    BEGIN
                        EXECUTE IMMEDIATE 'DROP USER temp_user_' || i;
                    EXCEPTION WHEN OTHERS THEN
                        NULL;
                    END;
            END;
        END IF;
        
        -- 진행상황 표시 (100개마다)
        IF MOD(i, 100) = 0 THEN
            DBMS_OUTPUT.PUT_LINE('진행: ' || i || '/' || p_count || ' 완료');
        END IF;
        
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('완료: ' || p_count || '개의 감사 로그 생성 작업 완료');
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
        RAISE;
END generate_audit_logs;
/

#실행
EXEC generate_audit_logs(1000);

병렬 로그인/로그아웃 스크립트

# 별도 터미널에서 병렬 로그인 실행
cat > mass_login.sh << 'EOF'
#!/bin/bash
for i in {1..50}
do
    echo "로그인 테스트 $i"
    sqlplus -S system/oracle <<< "SELECT USER, SYSDATE FROM DUAL; EXIT;" > /dev/null
    sqlplus -S hr/password <<< "SELECT USER FROM DUAL; EXIT;" > /dev/null 2>&1
done
echo "대량 로그인 테스트 완료"
EOF

chmod +x mass_login.sh
./mass_login.sh

기본 감사 로그 조회

-- SQLPlus 포맷 설정 개선
SET PAGESIZE 30
SET LINESIZE 130
SET WRAP OFF
SET PAUSE OFF
SET ECHO OFF
COLUMN TIME_ FORMAT A15
COLUMN USERID FORMAT A10
COLUMN DDL_TYPE FORMAT A20
COLUMN SCHEMA_ FORMAT A10
COLUMN OBJECT_NAME FORMAT A15
COLUMN SQL_PREVIEW FORMAT A50
COLUMN RESULT_ FORMAT 99999999

-- 최근 1일 이력 상세 조회
SELECT 
    TO_CHAR(NTIMESTAMP#, 'MM-DD HH24:MI:SS') AS TIME_,
    USERID,
    CASE ACTION#
        WHEN 0   THEN 'UNKNOWN'
        WHEN 1   THEN 'CREATE TABLE'
        WHEN 2   THEN 'INSERT'
        WHEN 3   THEN 'SELECT'
        WHEN 4   THEN 'CREATE CLUSTER'
        WHEN 5   THEN 'ALTER CLUSTER'
        WHEN 6   THEN 'UPDATE'
        WHEN 7   THEN 'DELETE'
        WHEN 8   THEN 'DROP CLUSTER'
        WHEN 9   THEN 'CREATE INDEX'
        WHEN 10  THEN 'DROP INDEX'
        WHEN 11  THEN 'ALTER INDEX'
        WHEN 12  THEN 'DROP TABLE'
        WHEN 13  THEN 'CREATE SEQUENCE'
        WHEN 14  THEN 'ALTER SEQUENCE'
        WHEN 15  THEN 'ALTER TABLE'
        WHEN 16  THEN 'DROP SEQUENCE'
        WHEN 17  THEN 'GRANT OBJECT'
        WHEN 18  THEN 'REVOKE OBJECT'
        WHEN 19  THEN 'CREATE SYNONYM'
        WHEN 20  THEN 'DROP SYNONYM'
        WHEN 21  THEN 'CREATE VIEW'
        WHEN 22  THEN 'DROP VIEW'
        WHEN 23  THEN 'VALIDATE INDEX'
        WHEN 24  THEN 'CREATE PROCEDURE'
        WHEN 25  THEN 'ALTER PROCEDURE'
        WHEN 26  THEN 'LOCK'
        WHEN 27  THEN 'NO-OP'
        WHEN 28  THEN 'RENAME'
        WHEN 29  THEN 'COMMENT'
        WHEN 30  THEN 'AUDIT OBJECT'
        WHEN 31  THEN 'NOAUDIT OBJECT'
        WHEN 32  THEN 'CREATE DATABASE LINK'
        WHEN 33  THEN 'DROP DATABASE LINK'
        WHEN 34  THEN 'CREATE DATABASE'
        WHEN 35  THEN 'ALTER DATABASE'
        WHEN 36  THEN 'CREATE ROLLBACK SEG'
        WHEN 37  THEN 'ALTER ROLLBACK SEG'
        WHEN 38  THEN 'DROP ROLLBACK SEG'
        WHEN 39  THEN 'CREATE TABLESPACE'
        WHEN 40  THEN 'ALTER TABLESPACE'
        WHEN 41  THEN 'DROP TABLESPACE'
        WHEN 42  THEN 'ALTER SESSION'
        WHEN 43  THEN 'ALTER USER'
        WHEN 44  THEN 'COMMIT'
        WHEN 45  THEN 'ROLLBACK'
        WHEN 46  THEN 'SAVEPOINT'
        WHEN 47  THEN 'PL/SQL EXECUTE'
        WHEN 48  THEN 'SET TRANSACTION'
        WHEN 49  THEN 'ALTER SYSTEM'
        WHEN 50  THEN 'EXPLAIN'
        WHEN 51  THEN 'CREATE USER'
        WHEN 52  THEN 'CREATE ROLE'
        WHEN 53  THEN 'DROP USER'
        WHEN 54  THEN 'DROP ROLE'
        WHEN 55  THEN 'SET ROLE'
        WHEN 56  THEN 'CREATE SCHEMA'
        WHEN 57  THEN 'CREATE CONTROL FILE'
        WHEN 59  THEN 'CREATE TRIGGER'
        WHEN 60  THEN 'ALTER TRIGGER'
        WHEN 61  THEN 'DROP TRIGGER'
        WHEN 62  THEN 'ANALYZE TABLE'
        WHEN 63  THEN 'ANALYZE INDEX'
        WHEN 64  THEN 'ANALYZE CLUSTER'
        WHEN 65  THEN 'CREATE PROFILE'
        WHEN 66  THEN 'DROP PROFILE'
        WHEN 67  THEN 'ALTER PROFILE'
        WHEN 68  THEN 'DROP PROCEDURE'
        WHEN 70  THEN 'ALTER RESOURCE COST'
        WHEN 71  THEN 'CREATE MATERIALIZED VIEW LOG'
        WHEN 72  THEN 'ALTER MATERIALIZED VIEW LOG'
        WHEN 73  THEN 'DROP MATERIALIZED VIEW LOG'
        WHEN 74  THEN 'CREATE MATERIALIZED VIEW'
        WHEN 75  THEN 'ALTER MATERIALIZED VIEW'
        WHEN 76  THEN 'DROP MATERIALIZED VIEW'
        WHEN 77  THEN 'CREATE TYPE'
        WHEN 78  THEN 'DROP TYPE'
        WHEN 79  THEN 'ALTER ROLE'
        WHEN 80  THEN 'ALTER TYPE'
        WHEN 81  THEN 'CREATE TYPE BODY'
        WHEN 82  THEN 'ALTER TYPE BODY'
        WHEN 83  THEN 'DROP TYPE BODY'
        WHEN 84  THEN 'DROP LIBRARY'
        WHEN 85  THEN 'TRUNCATE TABLE'
        WHEN 86  THEN 'TRUNCATE CLUSTER'
        WHEN 88  THEN 'ALTER VIEW'
        WHEN 91  THEN 'CREATE FUNCTION'
        WHEN 92  THEN 'ALTER FUNCTION'
        WHEN 93  THEN 'DROP FUNCTION'
        WHEN 94  THEN 'CREATE PACKAGE'
        WHEN 95  THEN 'ALTER PACKAGE'
        WHEN 96  THEN 'DROP PACKAGE'
        WHEN 97  THEN 'CREATE PACKAGE BODY'
        WHEN 98  THEN 'ALTER PACKAGE BODY'
        WHEN 99  THEN 'DROP PACKAGE BODY'
        WHEN 100 THEN 'LOGON'
        WHEN 101 THEN 'LOGOFF'
        WHEN 102 THEN 'LOGOFF BY CLEANUP'
        WHEN 103 THEN 'SESSION REC'
        WHEN 104 THEN 'SYSTEM AUDIT'
        WHEN 105 THEN 'SYSTEM NOAUDIT'
        WHEN 106 THEN 'AUDIT DEFAULT'
        WHEN 107 THEN 'NOAUDIT DEFAULT'
        WHEN 108 THEN 'SYSTEM GRANT'
        WHEN 109 THEN 'SYSTEM REVOKE'
        WHEN 110 THEN 'CREATE PUBLIC SYNONYM'
        WHEN 111 THEN 'DROP PUBLIC SYNONYM'
        WHEN 112 THEN 'CREATE PUBLIC DATABASE LINK'
        WHEN 113 THEN 'DROP PUBLIC DATABASE LINK'
        WHEN 114 THEN 'GRANT ROLE'
        WHEN 115 THEN 'REVOKE ROLE'
        WHEN 116 THEN 'EXECUTE PROCEDURE'
        WHEN 117 THEN 'USER COMMENT'
        WHEN 118 THEN 'ENABLE TRIGGER'
        WHEN 119 THEN 'DISABLE TRIGGER'
        WHEN 120 THEN 'ENABLE ALL TRIGGERS'
        WHEN 121 THEN 'DISABLE ALL TRIGGERS'
        WHEN 122 THEN 'NETWORK ERROR'
        WHEN 123 THEN 'EXECUTE TYPE'
        WHEN 128 THEN 'FLASHBACK'
        WHEN 129 THEN 'CREATE SESSION'
        WHEN 130 THEN 'ALTER MINING MODEL'
        WHEN 131 THEN 'SELECT MINING MODEL'
        WHEN 133 THEN 'CREATE MINING MODEL'
        WHEN 134 THEN 'ALTER PUBLIC SYNONYM'
        WHEN 135 THEN 'DIRECTORY EXECUTE'
        WHEN 136 THEN 'SQL*LOADER DIRECT PATH'
        WHEN 137 THEN 'DATAPUMP DIRECT PATH'
        WHEN 157 THEN 'CREATE DIRECTORY'
        WHEN 158 THEN 'DROP DIRECTORY'
        WHEN 159 THEN 'CREATE LIBRARY'
        WHEN 160 THEN 'CREATE JAVA'
        WHEN 161 THEN 'ALTER JAVA'
        WHEN 162 THEN 'DROP JAVA'
        WHEN 163 THEN 'CREATE OPERATOR'
        WHEN 164 THEN 'CREATE INDEXTYPE'
        WHEN 165 THEN 'DROP INDEXTYPE'
        WHEN 166 THEN 'ALTER INDEXTYPE'
        WHEN 167 THEN 'DROP OPERATOR'
        WHEN 168 THEN 'ASSOCIATE STATISTICS'
        WHEN 169 THEN 'DISASSOCIATE STATISTICS'
        WHEN 170 THEN 'CALL METHOD'
        WHEN 171 THEN 'CREATE SUMMARY'
        WHEN 172 THEN 'ALTER SUMMARY'
        WHEN 173 THEN 'DROP SUMMARY'
        WHEN 174 THEN 'CREATE DIMENSION'
        WHEN 175 THEN 'ALTER DIMENSION'
        WHEN 176 THEN 'DROP DIMENSION'
        WHEN 177 THEN 'CREATE CONTEXT'
        WHEN 178 THEN 'DROP CONTEXT'
        WHEN 179 THEN 'ALTER OUTLINE'
        WHEN 180 THEN 'CREATE OUTLINE'
        WHEN 181 THEN 'DROP OUTLINE'
        WHEN 182 THEN 'UPDATE INDEXES'
        WHEN 183 THEN 'ALTER OPERATOR'
        WHEN 192 THEN 'ALTER SYNONYM'
        WHEN 197 THEN 'PURGE USER_RECYCLEBIN'
        WHEN 198 THEN 'PURGE DBA_RECYCLEBIN'
        WHEN 199 THEN 'PURGE TABLESPACE'
        WHEN 200 THEN 'PURGE TABLE'
        WHEN 201 THEN 'PURGE INDEX'
        WHEN 202 THEN 'UNDROP OBJECT'
        WHEN 204 THEN 'FLASHBACK DATABASE'
        WHEN 205 THEN 'FLASHBACK TABLE'
        WHEN 206 THEN 'CREATE RESTORE POINT'
        WHEN 207 THEN 'DROP RESTORE POINT'
        WHEN 208 THEN 'PROXY AUTHENTICATION ONLY'
        WHEN 209 THEN 'DECLARE REWRITE EQUIVALENCE'
        WHEN 210 THEN 'ALTER REWRITE EQUIVALENCE'
        WHEN 211 THEN 'DROP REWRITE EQUIVALENCE'
        WHEN 212 THEN 'CREATE EDITION'
        WHEN 213 THEN 'ALTER EDITION'
        WHEN 214 THEN 'DROP EDITION'
        WHEN 215 THEN 'DROP ASSEMBLY'
        WHEN 216 THEN 'CREATE ASSEMBLY'
        WHEN 217 THEN 'ALTER ASSEMBLY'
        WHEN 218 THEN 'CREATE FLASHBACK ARCHIVE'
        WHEN 219 THEN 'ALTER FLASHBACK ARCHIVE'
        WHEN 220 THEN 'DROP FLASHBACK ARCHIVE'
        WHEN 225 THEN 'ALTER DATABASE LINK'
        WHEN 305 THEN 'ALTER PUBLIC DATABASE LINK'
        ELSE 'OTHER'
    END AS DDL_TYPE,
    OBJ$CREATOR AS SCHEMA_,
    OBJ$NAME AS OBJECT_NAME,
    SUBSTR(SQLTEXT, 1, 50) AS SQL_PREVIEW,
    RETURNCODE AS RESULT_
FROM SYS.AUD$
WHERE ACTION# BETWEEN 0 AND 305
  AND NTIMESTAMP# > SYSDATE - 1  
ORDER BY NTIMESTAMP# DESC;

TIME_           | USERID     | DDL_TYPE             | SCHEMA_    | OBJECT_NAME     | SQL_PREVIEW
--------------- | ---------- | -------------------- | ---------- | --------------- | ---------------------------------------------
07-29 15:31:10  | SYSTEM     | SELECT               | SYS        | DUAL            |
07-29 15:31:10  | SYSTEM     | LOGON                |            |                 |
07-29 15:31:09  | HR         | LOGON                |            |                 |
07-29 15:31:07  | SYSTEM     | LOGOFF               |            |                 |
.......

Export 전 최종 확인

-- 현재 AUD$ 테이블 정확한 상태
SELECT
    COUNT(*) AS TOTAL_ROWS,
    TO_CHAR(MIN(NTIMESTAMP#), 'YYYY-MM-DD HH24:MI:SS') AS FIRST_LOG,
    TO_CHAR(MAX(NTIMESTAMP#), 'YYYY-MM-DD HH24:MI:SS') AS LAST_LOG,
    ROUND(SUM(LENGTH(NVL(SQLTEXT, 'N'))) / 1024 / 1024, 2) AS APPROX_SIZE_MB
FROM SYS.AUD$;

TOTAL_ROWS | FIRST_LOG                              | LAST_LOG                               | APPROX_SIZE_MB
---------- | -------------------------------------- | -------------------------------------- | --------------
       390 | 2025-06-06 09:46:09                    | 2025-07-29 16:48:20                    |              0


SET LINESIZE 100
SET PAGESIZE 50

COLUMN SEGMENT_NAME FORMAT A20
COLUMN SIZE_MB FORMAT 999999.99
COLUMN SIZE_GB FORMAT 9999.999


-- 한 줄 너비 늘리기 (적당히 충분히 넓게 지정)
SET LINESIZE 80
SET PAGESIZE 50

-- 컬럼별 출력 포맷 지정
COLUMN segment_name FORMAT A10       
COLUMN kilobytes FORMAT 9999999999       
COLUMN blocks FORMAT 9999999           
COLUMN extents FORMAT 999            

SELECT segment_name
     , bytes / 1024 AS kilobytes
     , blocks
     , extents
  FROM dba_segments
 WHERE owner = 'SYS'
   AND segment_name = 'AUD$';


SEGMENT_NA       BYTES BLOCKS EXTENTS
---------- ----------- ------ -------
AUD$            327680     40       5

Export 디렉토리 및 권한 설정

mkdir -p /app/oracle/admin/ora19c/dpdump

-- Export 디렉토리 생성
CREATE DIRECTORY AUDIT_EXPORT AS '/app/oracle/admin/ora19c/dpdump';
GRANT READ, WRITE ON DIRECTORY AUDIT_EXPORT TO SYSTEM;

-- 디렉토리 확인
SELECT DIRECTORY_NAME, DIRECTORY_PATH FROM DBA_DIRECTORIES
WHERE DIRECTORY_NAME = 'AUDIT_EXPORT';

실제 Export 실행

# 환경변수 설정 (db랑 맞춰주기)
export NLS_LANG=American_America.AL32UTF8

#인스턴스 확인 
SQL> SELECT parameter, value
FROM nls_database_parameters
WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
  2    3
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_CHARACTERSET
AL32UTF8

# 전통적인 exp 방식 (AUD는 export 안됨)
exp system/oracle \
file=/u01/app/oracle/admin/ORA19C/dpdump/aud_backup_$(date +%Y%m%d).dmp \
tables="sys.aud$" \
log=/u01/app/oracle/admin/ORA19C/dpdump/aud_export_$(date +%Y%m%d).log \
STATISTICS=NONE
    
 #export 로그 확인
 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to SYS
. . exporting table                           AUD$       1851 rows exported
Export terminated successfully without warnings.

Export 결과 확인

# 생성된 파일들 확인
ls -lh /app/oracle/admin/ora19c/dpdump/aud*

-rw-r--r-- 1 oracle dba 336K Jul 30 01:57 /app/oracle/admin/ora19c/dpdump/aud_backup.dmp
-rw-r--r-- 1 oracle dba  390 Jul 30 01:57 /app/oracle/admin/ora19c/dpdump/aud_export.log

Truncate 전 백업 확인

-- 현재 행 수 최종 확인
SELECT COUNT(*) AS BEFORE_TRUNCATE FROM SYS.AUD$;

BEFORE_TRUNCATE
---------------
           2022

Truncate 실행

-- AUD$ 테이블 Truncate (주의: 모든 데이터 삭제)
TRUNCATE TABLE SYS.AUD$;

-- 즉시 확인
SELECT COUNT(*) AS AFTER_TRUNCATE FROM SYS.AUD$;

-- 테이블 구조는 유지됨을 확인
DESC SYS.AUD$;

Import 실행

sqlplus / as sysdba

host imp userid=sys/oracle file=/app/oracle/admin/ora19c/dpdump/aud_backup.dmp fromuser=SYS touser=SYS tables=AUD$ ignore=Y log=/app/oracle/admin/ora19c/dpdump/aud_import_sysdba.log

IMP-00058: ORACLE error 28009 encountered
ORA-28009: connection as SYS should be as SYSDBA or SYSOPERUsername: sys as sysdba  <-입력
Password: 패스워드입력

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Export file created by EXPORT:V19.00.00 via conventional path

Warning: the objects were exported by SYSTEM, not by you

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
IMP-00403:

Warning: This import generated a separate SQL file "/app/oracle/admin/ora19c/dpdump/aud_import_sysdba_sys.sql" which contains DDL that failed due to a privilege issue.

. importing SYS's objects into SYS
. . importing table                         "AUD$"       1851 rows imported
Import terminated successfully with warnings.

데이터 복원 확인

-- 복원된 행 수 확인
SELECT COUNT(*) AS RESTORED_ROWS FROM SYS.AUD$;

-- 날짜 범위 확인
SELECT
    TO_CHAR(MIN(NTIMESTAMP#), 'YYYY-MM-DD HH24:MI:SS') AS FIRST_LOG,
    TO_CHAR(MAX(NTIMESTAMP#), 'YYYY-MM-DD HH24:MI:SS') AS LAST_LOG,
    COUNT(*) AS TOTAL_RESTORED
FROM SYS.AUD$;

스크립트로 할려면?

• DB랑 캐릭터셋 맞춰야함
• SID도 맞춰야함
  • ⇒ export ORACLE_SID=ORA19C 와 같이 스크립트에 넣어줘야함

vi exp.sh
==
exp system/oracle \
file=/u01/app/oracle/admin/ORA19C/dpdump/aud_backup_$(date +%Y%m%d).dmp \
tables="sys.aud$" \
log=/u01/app/oracle/admin/ORA19C/dpdump/aud_export_$(date +%Y%m%d).log \
STATISTICS=NONE
==

vi /etc/crontab
==
3 1 30 7 * root su - oracle -c "/home/oracle/exp.sh"
==
3: 분

1: 시(01시)

30: 일 (30일)

7: 월 (7월)

*: 요일(무시, 매년 해당 월/일에 동작)

root: 해당 루트 권한의 크론에서 실행함을 의미합니다.

su - oracle -c "...": oracle 로그로 rman.sh 실행

입력하면 "매년 7월 30일 01:03"에 해당 파일이 한 번 실행됩니다.

AUDIT 전용 TBS 방법은?

• Oracle 권장사항: 비피크 시간대(업무시간 외)에 실행할 것

대상 테이블스페이스 준비사항

• 감사 테이블을 수용할 충분한 공간 확보 필요
• 빈번한 쓰기 작업에 최적화된 테이블스페이스 구성 권장

지원되는 audit_trail_type

• AUDIT_TRAIL_AUD_STD (표준 감사)
• AUDIT_TRAIL_FGA_STD (세밀한 감사)
• AUDIT_TRAIL_DB_STD (데이터베이스 감사)

통합 감사(UNIFIED) 특별 사항

• AUDIT_TRAIL_UNIFIED 사용 시: 새로운 감사 레코드만 지정된 테이블스페이스에 저장 **(기존 감사 레코드는 이동하지 않음)** ⇒[<https://docs.oracle.com/database/121/ARPLS/d_audit_mgmt.htm#ARPLS65429>]

#먼저 어느 TBS에 있는지 확인
col table_name for a20
SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;
TABLE_NAME           TABLESPACE_NAME
-------------------- ------------------------------
AUD$                 SYSTEM
FGA_LOG$             SYSTEM


-- 감사 전용 테이블 스페이스 생성
CREATE TABLESPACE TBS_AUDIT DATAFILE '/u01/app/oracle/oradata/ORA19C/tbs_audit01.dbf' SIZE 4G;

• DBMS_AUDIT_MGMT 이용하여 옮기자
  • 11g R2 부터 가능함

-- 감사 테이블 스페이스 SYSTEM -> TBS_AUDIT 변경(audit 로그 저장 테이블 및 스키마 이동)
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'TBS_AUDIT');
END;
/

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'TBS_AUDIT');
END;
/

-- 12c only
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_location_value => 'TBS_AUDIT');
END;
/

#변경 후 확인
SET LINESIZE 100
SET PAGESIZE 50

COLUMN SEGMENT_NAME     FORMAT A15
COLUMN TABLESPACE_NAME  FORMAT A15
COLUMN BLOCKS           FORMAT 999999
COLUMN mb               FORMAT 999.999

SELECT segment_name, tablespace_name, blocks, bytes/1024/1024 AS "mb"
FROM dba_segments
WHERE segment_name IN ('AUD$','FGA_LOG$','AUD$UNIFIED');

SEGMENT_NAME    TABLESPACE_NAME  BLOCKS       mb
--------------- --------------- ------- --------
AUD$            TBS_AUDIT            80     .625
FGA_LOG$        TBS_AUDIT             8     .063
AUD$UNIFIED     SYSAUX              512    4.000

COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20
SELECT * FROM dba_audit_mgmt_config_params;

# dba_audit_mgmt_config_params 뷰를 보니 정상적으로 tablespace가 변경은 된것 같음(UNIFIED AUDIT TRAIL)
PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
DB AUDIT TABLESPACE            TBS_AUDIT            STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            TBS_AUDIT            FGA AUDIT TRAIL
DB AUDIT TABLESPACE            TBS_AUDIT            UNIFIED AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT WRITE MODE               QUEUED WRITE MODE    UNIFIED AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                UNIFIED AUDIT TRAIL
AUDIT FILE MAX AGE             5                    UNIFIED AUDIT TRAIL

14 rows selected.




1. AUD$UNIFIED 테이블의 파티션 특성

월별 자동 파티셔닝: AUD$UNIFIED 테이블은 월 단위로 자동 파티션 생성
기본 테이블스페이스: 새 설정 전까지는 SYSAUX 사용

2. 테이블스페이스 변경의 지연 효과
sql-- 이 명령을 실행해도
DBMS_AUDIT_MGMT.set_audit_trail_location(..., 'TBS_AUDIT');

즉시 적용 안됨: 기존 파티션은 SYSAUX에 그대로 유지
미래 파티션만 적용: 다음 달 새 파티션부터 TBS_AUDIT에 생성
점진적 이관: 시간이 지나면서 점차 TBS_AUDIT 사용량 증가

⇒ 라이센스별로 지원 하느것이 다름으로 확인이 필요하다

읽어주셔서 감사합니다.

참조 https://positivemh.tistory.com/496 https://jjon.tistory.com/entry/ORACLE-12c-UNIFIED-AUDIT-사용