251211 TIL
2025. 12. 11. 17:32ㆍCourses/아이티윌 오라클 DBA 과정
Audit(Cont.)
Audit 비활성화
SYS@ora19c> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB, EXTENDED
# audit 비활성화
SYS@ora19c> alter system set audit_trail = none scope = spfile;
System altered.
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 553648128 bytes
Database Buffers 260046848 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SYS@ora19c> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SYS@ora19c> truncate table aud$;
Table truncated.Fine Grained Auditing
- 컨텐츠(특정 조건, 특정 컬럼)를 기준으로 데이터 액세스 모니터
- select, insert, update, delete, merge 감사
- 테이블, 뷰에 있는 하나 이상의 열에 감사 수행 가능
- 프로시저 실행 가능
- dbms_fga 패키지 사용
SYS@ora19c> desc dbms_fga
PROCEDURE ADD_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
AUDIT_CONDITION VARCHAR2 IN DEFAULT
AUDIT_COLUMN VARCHAR2 IN DEFAULT
HANDLER_SCHEMA VARCHAR2 IN DEFAULT
HANDLER_MODULE VARCHAR2 IN DEFAULT
ENABLE BOOLEAN IN DEFAULT
STATEMENT_TYPES VARCHAR2 IN DEFAULT
AUDIT_TRAIL BINARY_INTEGER IN DEFAULT
AUDIT_COLUMN_OPTS BINARY_INTEGER IN DEFAULT
POLICY_OWNER VARCHAR2 IN DEFAULT
PROCEDURE DISABLE_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE DROP_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE ENABLE_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
ENABLE BOOLEAN IN DEFAULTFGA 생성
-- audit select on hr.emp; 과 동일
begin
dbms_fga.add_policy(
object_schema => 'hr',
object_name => 'emp',
policy_name => 'emp_fga'
);
end;
/begin
dbms_fga.add_policy(
object_schema => 'hr',
object_name => 'emp',
policy_name => 'emp_fga',
audit_condition => 'dept_id = 80',
audit_column => 'sal, comm',
audit_column_opts => dbms_fga.all_columns,
statement_types => 'select, insert, update, delete',
enable => true
);
end;
/audit_column_opts => dbms_fga.any_columns(기본값) : audit_column 변수에 나열한 각각의 컬럼들을 참조할 때 또는 모두 참조할 때 수행audit_column_opts => dbms_fga.all_columns: audit_column 변수에 나열한 컬럼들을 모두 참조할 때 수행statement_types => 'select'(기본값)
-- audit 조회
select * from dba_audit_policies;
-- fga 딕셔너리 테이블
select * from fga_log$;
-- fga 뷰
select * from dba_fga_audit_trail;-- 감사 X
select name from hr.emp where dept_id = 80;
select sal from hr.emp where dept_id = 80;
select comm from hr.emp where dept_id = 80;
-- 감사
select * from hr.emp;
select * from hr.emp where dept_id = 80;
select * from hr.emp where id = 145;
select sal, comm from hr.emp where dept_id = 80;
select sal from hr.emp where dept_id = 80 and comm = 0.3;
select * from hr.emp where dept_id = 50;
-- 바인드 변수 값에 따라 감사
select * from hr.emp where dept_id = :b_dept_id;
select * from hr.emp where id = :b_id;
-- 감사
insert into hr.emp(id, name, sal, comm, dept_id) values(300, 'zoey', 1000, 0.1, 80);
-- 감사 X
insert into hr.emp(id, name, sal, comm, dept_id) values(400, 'lucy', 1000, 0.1, 20);
commit;
-- 바인드 변수 값에 따라 감사
insert into hr.emp(id, name, sal, comm, dept_id) values(:b_id, :b_name, :b_sal, :b_comm, :b_dept_id);
commit;
desc hr.emp
-- 감사 X
insert into hr.emp(id, name, dept_id) values(:b_id, :b_name, :b_dept_id);
commit;
-- 감사 X
update hr.emp
set sal = sal * 1.1
where id = 145;
rollback;
-- 감사
update hr.emp
set sal = sal * 1.1, comm = 0.2
where id = 145;
rollback;
-- 감사
delete from hr.emp where id = 145;
-- 감사 X
delete from hr.emp where id = 100;
rollback;
FGA 삭제
-- FGA 삭제
exec dbms_fga.drop_policy(
object_schema => 'hr',
object_name => 'emp',
policy_name => 'emp_fga'
);
-- fga_log$ 딕셔너리 테이블 truncate
truncate table fga_log$;
select * from fga_log$;handler
-- 테이블 생성
create table hr.fga_emp_log(
object_schema varchar2(30),
object_name varchar2(30),
policy_name varchar2(30),
user_name varchar2(30),
sql_text varchar2(4000),
sql_bind varchar2(100),
day timestamp
);-- 프로시저 생성 : 감사가 생길 때 fga_emp_log에도 감사 정보 insert
create or replace procedure hr.fga_trail_proc(
object_schema in varchar2,
object_name in varchar2,
policy_name in varchar2)
is
pragma autonomous_transaction;
begin
insert into hr.fga_emp_log(object_schema, object_name, policy_name, user_name, sql_text, sql_bind, day)
values(object_schema, object_name, policy_name, sys_context('userenv', 'session_user'), sys_context('userenv', 'current_sql'), sys_context('userenv', 'current_bind'), localtimestamp);
commit;
end fga_trail_proc;
/-- fga 생성
begin
dbms_fga.add_policy(
object_schema => 'hr',
object_name => 'emp',
policy_name => 'emp_fga',
statement_types => 'select, insert, update, delete',
handler_schema => 'hr',
handler_module => 'fga_trail_proc',
enable => true
);
end;
/
select * from dba_audit_policies;
- hr.emp에 대해 select, insert, update, delete 수행 후
select * from fga_log$;
select * from dba_fga_audit_trail;
select * from fga_emp_log;
- 트리거는 DML, DDL 발생 시에만 사용 가능
- select 시 특정 작업을 수행하려면 fine grained audit 사용
정책 활성화
-- 정책 비활성화
exec dbms_fga.disable_policy(object_schema => 'hr', object_name => 'emp', policy_name => 'emp_fga');
select * from dba_audit_policies;
정책 비활성화
-- 정책 비활성화
exec dbms_fga.enable_policy(object_schema => 'hr', object_name => 'emp', policy_name => 'emp_fga');
select * from dba_audit_policies;
감사 딕셔너리 테이블을 다른 테이블스페이스로 이관
select table_name, tablespace_name
from dba_tables
where table_name in ('AUD$', 'FGA_LOG$');
- 성능 상 감사 테이블의 테이블스페이스를 system 테이블스페이스에서 분리해서 관리하는 것을 권고
-- 테이블스페이스 생성
create tablespace audit_aux
datafile '/u01/app/oracle/oradata/ORA19C/audit_aux01.dbf' size 10m autoextend on
extent management local uniform size 1m
segment space management auto;
SYS@ora19c> desc dbms_audit_mgmt
...
# 감사 딕셔너리 테이블의 테이블스페이스를 이관하는 프로시저
PROCEDURE SET_AUDIT_TRAIL_LOCATION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
AUDIT_TRAIL_TYPE BINARY_INTEGER IN
AUDIT_TRAIL_LOCATION_VALUE VARCHAR2 IN-- aud$ 딕셔너리 테이블을 audit_aux 테이블스페이스로 이관
begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'audit_aux'
);
end;
/
select table_name, tablespace_name
from dba_tables
where table_name in('AUD$', 'FGA_LOG$');
-- fga_log$ 딕셔너리 테이블을 audit_aux 테이블스페이스로 이관
begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std,
audit_trail_location_value => 'audit_aux'
);
end;
/
select table_name, tablespace_name
from dba_tables
where table_name in('AUD$', 'FGA_LOG$');
-- fga_log$ 딕셔너리 테이블을 system 테이블스페이스로 이관
begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std,
audit_trail_location_value => 'system'
);
end;
/
-- aud$ 딕셔너리 테이블을 system 테이블스페이스로 이관
begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'system'
);
end;
/
select table_name, tablespace_name
from dba_tables
where table_name in('AUD$', 'FGA_LOG$');
SYS User Audit
SYS@ora19c> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
SYS@ora19c> show parameter audit_sys_operations
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean TRUE
SYS@ora19c> show parameter audit_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/ora19c/a
dumpaudit_trail: audit 활성화 설정(none이면 감사 수행 X, db로 설정되어야 함)audit_sys_operations: sys 유저가 수행하는 모든 작업에 대한 감사 설정audit_file_dest: audit file 위치
# audit file 위치로 이동
[oracle@ora19c ~]$ cd /u01/app/oracle/admin/ora19c/adump
[oracle@ora19c adump]$ pwd
/u01/app/oracle/admin/ora19c/adump
# sys 세션이 열릴 때마다 .aud 파일 생성
[oracle@ora19c adump]$ ls -al
total 52
drwxr-x---. 2 oracle oinstall 4096 Dec 11 15:07 .
drwxr-x---. 6 oracle oinstall 64 Nov 25 14:07 ..
-rw-r-----. 1 oracle oinstall 883 Dec 11 14:51 ora19c_ora_10872_20251211145152513403633260.aud
-rw-r-----. 1 oracle oinstall 1187 Dec 11 14:58 ora19c_ora_11223_20251211145759225520067203.aud
-rw-r-----. 1 oracle oinstall 847 Dec 11 14:58 ora19c_ora_11258_20251211145839637112843093.aud
-rw-r-----. 1 oracle oinstall 872 Dec 11 14:59 ora19c_ora_11258_20251211145908333231258282.aud
-rw-r-----. 1 oracle oinstall 1763 Dec 11 14:59 ora19c_ora_11366_20251211145908373817128220.aud
-rw-r-----. 1 oracle oinstall 3075 Dec 11 15:02 ora19c_ora_11376_20251211145912644507208927.aud
-rw-r-----. 1 oracle oinstall 19665 Dec 11 15:04 ora19c_ora_11915_20251211150353952431062775.aud
-rw-r-----. 1 oracle oinstall 1485 Dec 11 15:07 ora19c_ora_12140_20251211150743339353587798.aud
# sys 세션에서 수행하는 모든 명령 감사 가능
[oracle@ora19c adump]$ tail -F ora19c_ora_12140_20251211150743339353587798.aud
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '1271306228'
SESSIONID:[10] '4294967295'
USERHOST:[6] 'ora19c'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[2] '44'
Thu Dec 11 15:08:03 2025 +09:00
LENGTH : '276'
ACTION :[28] 'select * from dba_data_files'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '1271306228'
SESSIONID:[10] '4294967295'
USERHOST:[6] 'ora19c'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[1] '3'Oracel Net 서비스
- 클라이언트 또는 middle-tier 응용프로그램에서 오라클 서버의 네트워크 연결을 활성화
Listener
- 클라이언트 연결 요청을 수신하고 서버에 대한 트래픽을 관리하는 서버에 상주하는 프로세스
- 로컬이 아닌 모든 유저 연결을 위한 oracle instance gateway
# 리스너 중지
[oracle@ora19c adump]$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 11-DEC-2025 15:21:05
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
The command completed successfully
# 리스너 시작
[oracle@ora19c adump]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 11-DEC-2025 15:21:13
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 11-DEC-2025 15:21:13
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully# 리스너 정보 빠르게 등록
SYS@ora19c> alter system register;
System altered.
# 리스너 정보 조회
SYS@ora19c> ! lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 11-DEC-2025 15:21:35
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 11-DEC-2025 15:21:13
Uptime 0 days 0 hr. 0 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora19c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora19c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ora19c" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "ora19cXDB" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
The command completed successfullyLREG
- 12c 버전 이후로 listener에게 데이터베이스 정보를 등록하는 백그라운드 프로세스
- 11g 버전까지는 PMON이 수행
LISTENER 정보
[oracle@ora19c adump]$ cd $ORACLE_HOME/network/admin
[oracle@ora19c admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@ora19c admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)$ORACLE_HOME/network/admin/listener.ora- port : 1521(오라클 기본 포트)
- protocol : TCP/IP
- host : 컴퓨터 이름, IP
- SID(instance name)
# 호스트명
[oracle@ora19c adump]$ hostname
ora19c
# ip
[oracle@ora19c adump]$ hostname -i
192.168.56.150
[oracle@ora19c adump]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.56.150 ora19ceasy connect
- 기본적으로 활성화되어 있음
- 클라이언트 측 네트워크 구성 필요 없음
- TCP/IP 프로토콜만 지원
- 고급 연결 옵션 지원 X (RAC)
- connect time failover
- 소스 경로 지정
- 로드 밸런싱
username/password@hostname(ip):port/instance_name(sid)username/password@hostname(ip):port/service_name(db_name.db_domain)
# IP로 연결
C:\Users\itwill>sqlplus hr/hr@192.168.56.150:1521/ora19c
SQL*Plus: Release 11.2.0.2.0 Production on 목 12월 11 15:38:42 2025
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL>
# 호스트명으로 연결
C:\Users\itwill>sqlplus hr/hr@192.168.56.150:1521/ora19c
SQL*Plus: Release 11.2.0.2.0 Production on 목 12월 11 15:38:42 2025
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL># 인스턴스명 조회
SYS@ora19c> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string ora19c
# 서비스명 조회
# service_names : global database name(db_name.db_domain)
SYS@ora19c> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string ora19c
# db명 조회
SYS@ora19c> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ora19c
# db 도메인 조회
SYS@ora19c> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string
# 인스턴스명 조회
SQL> select instance_name from v$instance;
INSTANCE_NAME
--------------------------------
ora19c
# DB명 조회
SQL> select name from v$database;
NAME
------------------
ORA19CLocal naming(로컬 이름 지정)
- 클라이언트 측 이름 분석 파일 필요(네트워크 구성 프로그램)
- 모든 oracle net 프로토콜 지원
- 고급 연결 옵션 지원(RAC)
- connect time failover
- 소스 경로 지정
- 로드 밸런싱
클라이언트 이름 분석 파일 수정
- C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora
# 아래 내용 새로 추가
ora19c =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora19c)
)
)- tns명 : ora19c
- protocol : tcp
- host : 192.168.56.150
- port : 1521
- service_name : ora19c
로컬 이름으로 접속(CMD)
# tnsping - 접속 가능 여부 확인
C:\Users\itwill>tnsping ora19c
TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 11-12월-2025 16:23:55
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.150)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora19c)))
OK (20 msec)
# 로컬 이름으로 접속
C:\Users\itwill>sqlplus sys/oracle@ora19c as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on 목 12월 11 16:24:52 2025
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> select instance_name from v$instance;
INSTANCE_NAME
--------------------------------
ora19c로컬 이름으로 접속(SQL Developer)
'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글
| 251210 TIL (0) | 2025.12.11 |
|---|---|
| 251209 TIL (0) | 2025.12.09 |
| 251208 TIL (0) | 2025.12.08 |
| 251205 TIL (1) | 2025.12.05 |
| 251203 TIL (0) | 2025.12.05 |