0216 ADMIN

현스·2024년 2월 16일

ADMIN

목록 보기

8/18

■ 오라클 관리 수업 복습

1장: 오라클 아키텍쳐 (5문제) --------> 오라클 구조 암기(A4지 3~4장)
2장: 오라클 소프트웨어 설치 (1문제) ----> 기본기
3장: dbca 를 이용해서 db 생성 (1문제) ----> 기본기
4장: 오라클 인스턴스 관리 (파라미터 파일) (3문제) -->오라클 설정 변경할 일
5장: ASM 인스턴스 관리 (3문제)
6장: 오라클 네트워크 관리 ---> 일하러 나가서 첫날 셋팅해야할 일
7장: 테이블 스페이스 관리 (3문제) ---> DBA 가 일상적으로 하는일
8장: 유져 보안 관리 (2문제) ---> si형 DBA 가 일상적으로 하는일
9장: 데이터 동시성 관리 (lock) ---> si형, sm형 DBA 가 일상적으로 하는일
10장: 언두 데이터 관리 -----> si형, sm형 DBA 가 일상적으로 하는일

db엔지니어 --- > sm형 dba ----> si 형 dba ---> sm 형 dba

undo_management 는 무조건 auto 로 설정합니다
undo_retention 은 우리 회사에서 가장 길게 도는 쿼리문의 시간을 기준으로 설정
undo tablespace 테이블 스페이스명 < --- 사이즈를 넉넉히 설정
< --- undo retention 을 guarantee 할지 말지

11장 : 감사 기능 ---- > 개인 정보를 보호하기 위해서 사용하는 오라클의 기본 기능

실습2. 감사 옵션 중 audit select on scott.emp; 테스트 하기

sys 유져에서 scott 의 emp 테이블을 누군가 조회하면 알 수 있게 설정합니다.

audit select on scott.emp;

scott 유져로 접속해서 emp 테이블을 조회합니다.

select ename, sal
from emp
where ename='KING';

sys 유져에서 누가 scott 의 emp 테이블을 조회했는지 확인합니다. (dbever 에서 수행)

select *
from dba_audit_trail
where username='SCOTT' and action_name;

문제1. SCOTT 유져가 가지고 있는 모든 테이블들을 다 감사를 걸고
누군가 SCOTT 유져의 테이블을 SELECT 하면 감사 되게 하시오

audit select on scott.모든테이블

PROD(SCOTT) > select table_name
2 from user_tables;

PROD(SCOTT) > connect / as sysdba
Connected.
PROD(SYS) > select table_name
2 from dba_tables
3 where owner='SCOTT';

select 'audit select on scott.' || table_name || ';'
from dba_tables
where owner='SCOTT' and table_name not in ('CHAINED_ROWS','TAB1');

▣ 예제 86. 의심스러운 작업에 대한 구체적인 SQL 을 보고 싶다면 상세 감사 설정을 알아야해요

행안부 공공 기관의 DB 감사 설정의 예

sys 유져에서 scott 의 emp 테이블에서 월급이 3000 이상인 사원들의 data 를 조회하는 sql 을
누군가 수행하면 무조건 감사 되게 설정 하시오



begin
    dbms_fga.add_policy(object_schema=>'SCOTT',
                       object_name=>'EMP',
                       policy_name=>'POL7',
                       audit_condition=>'SAL >= 3000',
                       enable=>TRUE,
                       statement_types=>'INSERT,SELECT,UPDATE',
  audit_trail=>DBMS_FGA.DB_EXTENDED);
END;
/

scott 유져에서 월급이 5000 인 사원의 이름과 월급을 조회합니다.

select ename, sal
from emp
where sal = 5000;

scott 유져에서 ALLEN 의 월급을 6000 으로 수정합니다.

update emp
set sal = 6000
where ename='ALLEN';

sys 유져에서 누군가 scott 유져에서 수행한 sql 을 확인합니다.

col timeinfo  for  a15
col  object_name  for  a10
col sql_text for  a40
col sql_bind  for  a20
 

select to_char(TIMESTAMP,'yyyy/mm/dd HH24:MI:SS')  timeinfo ,
        userhost, os_user, OBJECT_NAME,SCN,
  SQL_TEXT,SQL_BIND
  from dba_fga_audit_trail;

문제1. sys 유져에서 상사 감사를 설정하는데 salgrade 테이블의 losal 을 4000 보다
큰값으로 insert 하거나 update 를 하면 감사가 되겠금 설정하고 테스트하시오 !


begin
    dbms_fga.add_policy(object_schema=>'SCOTT',
                       object_name=>'SALGRADE',
                       policy_name=>'POL9',
                       audit_condition=>'LOSAL > 4000',
                       enable=>TRUE,
                       statement_types=>'INSERT,UPDATE',
  audit_trail=>DBMS_FGA.DB_EXTENDED);
END;
/

update salgrade
set losal = 6000;

select to_char(TIMESTAMP,'yyyy/mm/dd HH24:MI:SS')  timeinfo ,
        userhost, os_user, OBJECT_NAME,SCN,
  SQL_TEXT,SQL_BIND
  from dba_fga_audit_trail;

문제2. 방금 생성한 상세 감사 정책을 drop 하시오 !

begin
dbms_fga.drop_policy(object_schema=>'SCOTT',
                       object_name=>'SALGRADE',
                       policy_name=>'POL9'
      );
END;
/

begin
    dbms_fga.drop_policy(object_schema=>'SCOTT',
                       object_name=>'EMP',
                       policy_name=>'POL7'
     );
END;
/

※ 상세감사 설정시 주의사항 !

system tablespace 가 full 이 날 수 있으니 주의해야합니다 !

문제4. system tablespace 의 사용량을 확인하시오 ! dba.sh 쉘 스크립트를 수행합니다.

sh dba.sh

문제5. system tablespace 의 공간을 늘리시오 !

 alter   tablespace   system
  add  datafile  '/u01/app/oracle/oradata/PROD/disk1/system02.dbf'  size 100m;

문제6. sysaux tablespace 의 공간도 늘리시오 !

 alter  tablespace  sysaux
   add  datafile  '/u01/app/oracle/oradata/PROD/disk1/sysaux02.dbf'  size  100m;

※ audit_trail 이 db 로 되어있으면 모든 감사 로그는 system 테이블 스페이스에 저장됩니다.
그래서 system tablespace 가 full 이 나지 않도록 db 말고 os 라고 지정하면
감사 로그가 os 에 저장이 됩니다.
os 에 어디에 저장되냐면 audit_file_dest 파라미터에 지정된 위치에 감사로그가 저장됩니다

문제7. 오라클 파라미터 audit_trail 을 db 에서 os 로 변경하시오 !

PROD(SYS) > show parameter audit_trail

PROD(SYS) > alter system set audit_trail='os' scope=spfile;

PROD(SYS) > shutdown immediate

PROD(SYS) > startup

PROD(SYS) > show parameter audit_trail

문제8. 감사 로그가 os 에 /home/oracle 밑에 생성될 수 있도록 audit_file_dest 파라미터를
/home/oracle 로 지정되게 하시오 !

PROD(SYS) > alter system set audit_file_dest = '/home/oracle' scope=spfile;
PROD(SYS) > shutdown immediate
PROD(SYS) > startup

문제 9. 우리가 지금까지 설정한 감사 기능을 끄시오

noaudit table;

noaudit select any table;

▣ 예제87. dba 작업도 감사 할 수 있습니다.

설명 요약

만약 sysdba 권한을 가진 유져를 감사하려면 3가지 설정을 합니다.

audit_trail=os
audit_file_dest = /home/oracle
audit_sys_operations = true

■ 실습

아래의 3개의 파라미터의 값을 확인합니다.

show parameter audit_trail
show parameter audit_file_dest
show parameter audit_sys_operations

위의 3개의 파라미터를 설정합니다

show parameter audit_trail - os 로
show parameter audit_file_dest - 특정 os 위치로
show parameter audit_sys_operations - true 로 세팅

alter system set audit_sys_operations=true scope=spfile;

db를 내렸다 올립니다.
잘 설정되었는지 다시 확인 합니다

show parameter audit_sys_operations

sysdba 권한으로 접속을 해봅니다
/home/oracle 밑에 sysdba 감사로그를 열어봅니다.

$ cd
$ ls -lrt

-rw-r----- 1 oracle dba 747 2월 16 11:35 prod_ora_15528_1.aud
-rw-r----- 1 oracle dba 741 2월 16 11:35 prod_ora_15492_2.aud
-rw-r----- 1 oracle dba 946 2월 16 11:58 prod_ora_15533_1.aud
-rw-r----- 1 oracle dba 741 2월 16 11:59 prod_ora_15766_2.aud

-rw-r----- 1 oracle dba 1440 2월 16 11:59 prod_ora_15800_1.aud

-rw-r----- 1 oracle dba 1456 2월 16 11:59 prod_ora_15805_1.aud

확장자가 aud 로 끝나는 파일을 열어보는데 맨 마지막에 생성된 파일을 vi 로 엽니다.

dba 작업도 감사 할 수 있습니다.

문제1. 다시 sysdba 감사 기능을 끄시오

audit_sys_operations 를 false 로 변경

alter system set audit_sys_operations=false scope=spfile;

점심시간 문제

PROD(SYS) > show parameter audit_trail

PROD(SYS) > alter system set audit_trail='db' scope=spfile;

PROD(SYS) > shutdown immediate

PROD(SYS) > startup

PROD(SYS) > show parameter audit_trail

▣ 예제 88. oracle audit valut 기능을 이용해서 데이터 보안을 강화 하세요 !

SQL> select ename, sal
from emp;

14명이 전부 다 조회가 되어야 하는데 vault 기능을 이용하면 특정 행을 감추고 13명만 조회되게 한다든지
월급이 3000 이상인 사원들은 아예 조회가 안되게 할 수 있습니다.
실제로는 테이블에 데이터가 있지만 조회가 안되게 하는 것 입니다.

oracle audit valut (유료기능) 사용되는 순간

허락(구매) 안맡고 사용하는 순간 oracle 에서 감사를 나온다

비용 청구함 (이 기능이 우리회사에서 구매 했는지 안했는지 알아보고 사용해야함 !!!!!!!!! )

.
.

12장 . 데이터베이스 유지 관리

▣ 예제 89. 오라클은 스스로 성능 정보를 수집하고 있어요

그림 해석

오라클은 스스로 자기의 문제를 진단하고 그 문제점에 대한 해결 방법 스크립트로 생성해서 가지고 있습니다.

스스로 진단을 하기 위한 성능 데이터는 AWR 에 저장됩니다.

■ 실습

1시간에 1번씩 자동으로 성능 정보를 수집하고 있는지 확인합니다.

@?/rdbms/admin/awrrpt.sql

수동으로 성능정보를 수집해봅니다.

PROD(SYS) > exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

PROD(SYS) > @?/rdbms/admin/awrrpt.sql

▣ 예제 90. 오라클에 문제가 생겼을 때 그 문제를 확인하려면 일단 alert log 파일을 보세요

alert log file 에 큰 이슈들이 저장되어 있습니다.

■ 실습

alert log file 의 위치를 확인합니다.

PROD(SYS) > show parameter background

alert log file 의 위치로 가서 alert log file 을 열어봅니다.

[PROD:~]$ cd /u01/app/oracle/diag/rdbms/prod/PROD/trace
[PROD:trace]$ ls -l alert*
-rw-r----- 1 oracle dba 199075 2월 16 12:14 alert_PROD.log
[PROD:trace]$ vi alert_PROD.log

alert log file 에 ORA- 로 시작하는 에러 번호들이 몇개가 있는지 조회합니다.

alert log file 에 ORA- 로 시작하는 에러 번호들은 디비에이가 반드시 해결해줘야함 !!!!

문제1. alert log file 을 열어서 언제 deadlock 이 발생했는지 알아내시오 !@

[PROD:trace]$ grep -i 'dead' alert_PROD.log
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_6141.trc.
[PROD:trace]$ grep -i -A 1 -B 1 'dead' alert_PROD.log
Thu Feb 15 10:21:00 2024
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_6141.trc.
Thu Feb 15 11:17:50 2024
[PROD:trace]$

-A 1 그 다음 1 행 더 출력

-B 1 그 전 행 1행 더 출력

-i 대소문자 구분하지 않겠다.

문제 2. alert log file 을 열어서 'ORA-01555' 언제 발생했는지 알아내시오 !

[PROD:trace]$ grep -i -B 1 'ORA-01555' alert_PROD.log
Thu Feb 15 14:52:49 2024
ORA-01555 caused by SQL statement below (SQL ID: d3hz1avmu1sgq, Query Duration=42 sec, SCN: 0x0000.00067a24):

문제3. 위의 SQL ID 를 가지고 관련 SQL 이 뭔지 찾아내시오 !

(SQL ID: d3hz1avmu1sgq, Query Duration=42 sec, SCN: 0x0000.00067a24):

현재 메모리에 있는 SQL 을 찾는 명령어

SELECT sql_text
from v$sql
where sql_id='d3hz1avmu1sgq';

과거의 상황이기 때문에 과거의 1시간에 한번씩 저장되는 awr 정보를 뒤져서 알아내면 됩니다.

Thu Feb 15 14:52:49 2024

SQL> @?/rdbms/admin/awrrpt.sql

report_type : html

Snap Id Snap Started Level

42 15 Feb 2024 14:00 1
43 15 Feb 2024 15:00 1

.
.
.
End of Report

Report written to report01.html

모바텀을 이용해 report01.html 파일을 바탕화면으로 다운받는다

문제3.

$ cd /u01/app/oracle/diag/rdbms/prod/PROD/trace
$ vi alert*

열고나서 / 를 하고 찾을 검색어를 쓴다

/ORA-01555

문제4. 지금 현재 DB 의 이슈를 확인하시오 !

select reason
from dba_outstanding_alerts;

PROD(SYS) > select reason
from dba_outstanding_alerts;

no rows selected

▣ 예제 91. 오라클 자동화 기능에 관련한 몇가지 용어를 암기하고 있어야 해요

awr : 성능 데이터 수집 수집된것을 가지고 분석 하거나 오라클이 권장하는 해결방법도 볼 수 있다.
이게 모냐 - 성능 데이터가 자동으로 수집되는 레파지토리 repository (저장소)

awr baseline : 우리회사에 가장 이상적인 데이터베이스 상태 시간과 악성 sql 이 돌았을때 를 비교해서
어떤지 알아보려고 베이스라인을 만듦. 그 중 이상적인 데이터베이스 상태와 시간이 베이스라인임

메트릭 : 누적 통계 변화율 / 어떤 악성 sql 을 수행하면 로지컬리드 - 메모리 리드는 계속해서 증가한다

현재 데이터베이스에서 발생하고 있는 메모리 read 의 누적값

select name, value
from v$sysstat
where name = 'session logical reads';

통계 1. 옵티마이져 통계 정보 : 테이블에 대한 통계 정보

시스템 통계 정보 : 시스템 상태에 대한 통계 정보

■ 실습 1.

scott 으로 접속해서 emp 테이블에 대해서 통계정보가 수집되었는지 확인합니다.

select table_name, last_analyzed
from user_tables
where table_name='EMP';

PROD(SYS) > connect scott/tiger
Connected.
PROD(SCOTT) > select table_name, last_analyzed
from user_tables
where table_name='EMP'; 2 3

이렇게 수집이 안되었으면 옵티마이져가 emp 테이블과 관련된 sql 에 대해서 좋은 실행계획을 만들어내기가 어렵다.

만약 수집이 안되었거나 오래되었다면 새로 수집을 합니다.

exec dbms_stats.gather_table_stats('scott','emp');

emp 테이블의 통계정보가 잘 수집되었는지 확인합니다.

.

문제1. scott 이 가지고 있는 모든 테이블에 대해서 테이블 통계 정보를 수집하시오

select table_name, last_analyzed
from user_tables;

exec dbms_stats.gather_schema_stats('scott');

문제2. 테이블 통계정보는 밤 10시에 자동으로 수집됩니다. 자동으로 수집되고 있는지 확인하시오

 select w.window_name, w.repeat_interval, w.duration
     , cast(w.last_start_date as timestamp with local time zone) last_start_date
     , cast(w.next_start_date as timestamp with local time zone) next_start_date
 from   dba_scheduler_wingroup_members m
     , dba_scheduler_windows w
where  m.window_group_name = 'MAINTENANCE_WINDOW_GROUP'
and    w.window_name = m.window_name;

문제3. 오라클에 어떤 기능이 자동으로 매일 수행되는지 확인하시오

select client_name, status
from dba_autotask_client;

auto space advisor : 공간정리 방정리

sql tuning advisor : 자동 sql 튜닝

문제 4. 위의 3개의 자동화 기능을 모두 끄시오

begin 
	dbms_auto_task_admin.disable(
	client_name=> 'auto optimizer stats collection',
	operation => null,
	window_name=> null);
end;
/

begin 
	dbms_auto_task_admin.disable(
	client_name=> 'auto space advisor',
	operation => null,
	window_name=> null);
end;
/


begin 
	dbms_auto_task_admin.disable(
	client_name=> 'sql tuning advisor',
	operation => null,
	window_name=> null);
end;
/

NL 조인
Hash 조인
sort merge 조인

full table 스캔 / index 스캔 등등

▣ 예제 92. 자주 변경되는 테이블은 옵티마이져 통계정보를 자주 수집해줘야해요

emp 테이블의 건수를 확인하시오

select count(*) from emp;

alter session set nls_date_format='RR/MM/DD:HH24:MI:SS';

select table_name, num_rows , last_analyzed
from user_tables
where table_name='EMP';

PROD(SCOTT) > select table_name, num_rows , last_analyzed
from user_tables
where table_name='EMP'; 2 3

EMP 테이블의 데이터를 아래와 같이 입력하고 다시 건수를 확인하세요

INSERT into emp
select * from emp;

select count(*) from emp;

select table_name, num_rows, last_analyzed
from user_tables
where table_name='EMP';

왜 다른가 ? 통계정보가 수집되지 않았기 때문 !
최근 정보로 맞춰주려면 다시 통계정보를 수집해줘야 합니다.

다시 emp 테이블의 통계정보를 수집하고 건수가 서로 같은지를 확인하시오

exec dbms_stats.gather_table_stats('scott','emp');

select count(*) from emp;

select table_name, num_rows, last_analyzed
from user_tables
where table_name='EMP';

문제1. dept 테이블을 아래와 같이 데이터를 늘리고서 dept 테이블을 직접 count 했을때와 user_tables 에서 건수를 조회했을 때 결과가 똑같게 하시오

insert into dept
select * from dept;

exec dbms_stats.gather_table_stats('scott','dept');

select count(*) from dept;

select table_name, num_rows, last_analyzed
from user_tables
where table_name='DEPT';

문제2. (dba 에게 유용한 tip) 테이블명을 입력하면 자동으로 통계정보가 수집되게 스크립트를 생성하시오

SQL> ed gt.sql

set verify off
accept p_table prompt '테이블명을 입력하세요 ~ '
exec dbms_stats.gather_table_stats('scott', '&p_table');

alter session set nls_date_format='RR/MM/DD:HH24:MI:SS';
select table_name, num_rows , last_analyzed
from user_tables
where table_name= upper('&p_table');

@gt

실행

자주 변경되는 테이블은 통계 정보를 자주 수집해주어야 한다.

▣ 예제 93. 밤 10시에 자동으로 통계 정보가 수집되는 테이블이 무엇인지 알아야 해요

옵티마이져가 통계 정보를 수집하는데

매일 밤 오라클이 10시에 데이터 베이스에 있는 모든 테이블들에 대해서
자동으로 통계 정보를 수집하는데 모든 테이블들에 대해서 다 수집하는 것은 아니고
데이터가 하루에 10% 이상 변경된 테이블에 대해서만 수집을 한다.

테이블마다 변경 % 를 변경할 수 있다.

■ 실습

scott 의 emp 테이블은 10% 가 아니라 20% 변경되었을 때 자동으로 수집되게 설정 하세요

PROD(SCOTT) > exec dbms_stats.set_table_prefs('scott', 'emp', 'stale_percent', 20);

PL/SQL procedure successfully completed.

변경된 내용을 확인합니다.

select table_name, preference_name, preference_value
from user_tab_stat_prefs
where table_name='EMP';

문제1. dept 테이블에 대해서 자동으로 통계정보를 수집하는 변경 데이터의 양이 10% 가 아니라 30% 변경되었을 때로
수집되게 설정하시오

exec dbms_stats.set_table_prefs('scott','dept', 'stale_percent', 30);

select table_name, preference_name, preference_value
from user_tab_stat_prefs
where table_name='DEPT';

통계정보 수집이 실제로는 굉장히 오래걸리고 db 의 cpu 와 메모리를 많이 사용합니다.

그런데 밤에 통계정보만 수집하는게 아니라 다른 작업들도 많이 돌고 있습니다

다른작업 예를 들면 배치작업입니다 이 배치작업이 통계정보 수집작업 때문에 cpu 를 많이 사용하지 못하고 느려질 수 있다

따라서 모든 테이블의 통계정보를 수집하도록 하면 안된다
중요하고 특별한 테이블에 대해서만 통계 정보가 수집되게 해줘야 한다. (자동으로 수집하는 것 만 )

오늘의 마지막 문제. orcl db 로 접속해서 hr 계정과 sh 계정의 모든 테이블 통계정보
를 수집하고 수집한 결과를 출력하시오 !

여태것 배운것을 다 동원해야합니다.

hr 과 sh 계정 잠겨있을테니까 잠김을 풀어줘야합니다.(배웠던거 찾아서 수행합니다.)
수집되는 시간이 좀 걸립니다.

hr 계정과 sh 계정 의 모든 테이블 통계정보 수집하고 수집한 결과 출력하시오

잠겨있는걸 풀어야된다고...

CONNECT sys/password AS SYSDBA;

비밀번호 바꾸기

alter user hr
identified by tiger;

ALTER USER hr ACCOUNT UNLOCK;

alter user sh
identified by tiger;

ALTER USER sh ACCOUNT UNLOCK;

-- HR 계정에 로그인
CONNECT hr/password;

-- 모든 테이블 통계 정보 수집
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'HR',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
CASCADE => TRUE
);
END;
/

수집한 결과 출력

select table_name, last_analyzed
from user_tables;

exec dbms_stats.gather_schema_stats('hr');

sh 계정

-- sh 계정에 로그인
CONNECT hr/password;

수집한 결과 출력

BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'SH',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
CASCADE => TRUE
);
END;
/

select table_name, last_analyzed
from user_tables;

exec dbms_stats.gather_schema_stats('sh');