오라클 프로파일
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 프로파일[편집]
| Resource | Description |
| CPU_PER_SESSION | 1/100 초 단위로 측정한 총 CPU이용시간 |
| CONNECT_TIME | 분 단위로 측정한, 경과된 접속시간 |
| IDLE_TIME | 분 단위로 측정한 비활동 시간 : 서버 프로세스에 대해서만 IDLE_TIME 계산.
응용프로그램 작업은 포함되지 않음.
IDLE_TIME은 오랜시간 수행되는 질의나 다른 작업들은 포함하지 않음.
|
| LOGICAL_READS_PER_SESSION | 데이터 블록 수 (물리적:디스크로부터,논리적:메모리로부터 읽은)
: LOGICAL_READS_PER_SESSION은 메모리와 디스크 모두 에게서 읽는 총 횟수를 제한. |
| PRIVATE_SGA | 바이트 단위로 측정한 SGA 내의 전용(private) 공간(MTS만) : PRIVATE_SGA는 다중 스레드 서버(MTS) 구성 일 때만 적용 .
|
1.1 PROFILE 조회[편집]
SELECT *
FRO DBA_PROFILES;1.2 PROFILE 생성[편집]
PROFILE 은 사용자 계정의 행동에 제약사항을 두기 위해서 사용하는 경우가 대부분입니다. 예를 들어 5분 이상 활동이 없으면 접속을 강제로 종료시킨다든지, 1개월마다 암호를 다른 것으로 바꾸게 강제로 적용한다든지 하는 것들이 대표적인 예입니다. PROFILE 은 세부 파라미터가 어떤 의미가 있는지 와 어떻게 만들어서 사용하는지를 배우도록 하겠습니다.
1) PASSWORD PROFILE 관련 파라미터
(1) FAILED_LOGIN_ATTEMPTS :
login 시도를 실패할 경우 계정을 잠그는데 여기 설정된 횟수만큼 시도한 후 계정을 잠그게 됩니다. 예를 들어 failed_login_attemps 3 이렇게 하면 3번 실패하면 4번째부턴 로그인 못한다는 의미입니다.
(2) PASSWORD_LOCK_TIME : 위 1번 에서 계정이 잠기면 여기서 며칠 동안 잠글 것 인지 기간을 정하는 파라미터 입니다. 단위는 일 이고 여기 일수와 무관하게 DBA가 unlock 해서 사용하게 할 수 있습니다. (3) PASSWORD_LIFE_TIME : 동일한 암호를 며칠간 사용하게 할 것인지 설정하는 파라미터입니다. 단위는 일 이며 이 기간이 지나도 안 바꾸면 다음 로그인할 때 강제로 바꾸게 프롬프트를 보여줍니다. (4) PASSWORD_GRACE_TIME : 위 3번 항목에서 만료되어도 이 파라미터에 지정된 값 만큼 더 암호를 변경할 기간을 허용하게 됩니다. (5) PASSWORD_REUSE_TIME : 동일한 암호를 다시 사용할 수 없도록 설정하는 기간. 즉 암호를 변경하라고 했는데 사용자가 동일한 암호를 다시 사용하려고 할 수 있기에 같은 암호를 다시 쓸 수 없게 만들어야 할 때 사용하는 파라미터 입니다.
(6) PASSWORD_REUSE_MAX : 동일한 암호를 위 5번 설정을 피해 재사용을 할 경우 최대 사용 가능한 횟수를 지정하는 파라미터입니다. (7) PASSWORD_VERIFY_FUNCTION : 암호를 보다 복잡하게 만들기 위해서 특정 함수를 적용시켜 사용자의 암호를 점검합니다. 이 함수는 오라클에서 만들어 둔 기본 함수인 verify_function 을 사용할 수 있고 사용자가 별도의 함수를 만들어서 적용할 수도 있습니다. 여기서는 verify_function 함수를 이용할 경우 어떤 조건을 점검하는 지 보겠습니다.
- 암호는 최소한 4글자 이상 되어야 합니다.
- 암호는 사용자 계정과 달라야 합니다.
- 암호는 하나의 특수문자나, 알파벳 , 숫자가 포함되어야 합니다.
- 암호는 이전 암호와 3글자 이상 달라야 합니다.
- 예제 1. Password 관련 PROFILE 생성하기
- 조건 1: 로그인 시도 3회 실패 시 계정을 5일 동안 사용 못하게 할 것 - 조건 2: 계정의 암호는 15일에 한번씩 변경하게 할 것 - 조건 3: 동일한 암호는 15일 동안 사용 못하게 할 것
-- 프로파일 생성
CREATE PROFILE RTIS_DEV_PROF;
-- 프로파일 수정
ALTER PROFILE RTIS_DEV_PROF LIMIT
IDLE_TIME 10
FAILED_LOGIN_ATTEMPTS 6
PASSWORD_LIFE_TIME 90
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX 4
PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
;
2) RESOURCE PROFILE 관련 파라미터
RESOURCE란 자원이란 의미가 있듯이 컴퓨터의 자원인 CPU 와 메모리 관련된 부분을 제어하는 파라미터입니다.
이 PROFILE 을 사용하려면 RESOURCE_LIMIT = true 라는 설정이 되어 있어야 합니다.
ORACLE이 시작될 때 사용되는 parameter file(Pfile / SPfile) 에 위 문장을 적어 놓거나 또는 지금 즉시 적용시키고 싶으면 9i 이상 버전일 경우엔
ALTER SYSTEM SET RESOURCE_LIMIT = true;를 실행하시면 됩니다
(1) CPU_PER_SESSION : 하나의 세션이 CPU를 연속적으로 사용할 수 있는 최대 시간을 설정합니다. 무한루프 같은 쿼리가 작동되면 혼자서 CPU를 연속적으로 점유하기 때문에 이 설정을 사용해서 그런 일을 막고자 하는 것입니다. 1/100초 단위입니다.
(2) SESSIONS_PER_USER : 하나의 사용자 계정으로 몇 명의 사용자가 동시에 접속 할 수 있는지를 설정하는 파라미터 입니다. (3) CONNECT_TIME : 하루 동안 DB Server에 접속을 허락하는 총 시간을 설정합니다.(단위는 분입니다) (4) IDLE_TIME : 연속적으로 휴면 시간이 여기 값을 넘으면 접속을 해제합니다. 예를 들어 IDLE_TIME 5 이렇게 하면 5분 동안 활동이 없는 세션은 강제로 접속이 끊어지게 됩니다. (5) LOGICAL_READS_PER_SESSION : 한 session 에서 사용 가능한 최대 block 수를 지정합니다. (6) PRIVATE_SGA :MTS / SHARED SERVER 일 경우 해당 session의 SGA 사용량을 bytes 단위로 설정합니다. (7) CPU_PER_CALL : 하나의 call 당 cpu를 점유할 수 있는 시간이며 1/100초 단위입니다. (8) LOGICAL_READS_PER_CALL : 하나의 call 당 읽을 수 있는 block의 개수를 지정합니다.
예제 2: RESOURCE 관련 PROFILE 만들기
SYS>ALTER SYSTEM SET RESOURCE_LIMIT=true ;- 조건 1: 1명당 연속적으로 CPU를 사용할 수 있는 시간을 10초로 제한할 것. - 조건 2: 하루 중 8시간만 DB에 접속 가능하게 할 것. - 조건 3: 10분 동안 사용하지 않으면 강제로 접속을 끊을 것.
SYS>CREATE PROFILE RE_SAMPLE_PROF LIMIT
CPU_PER_SESSION 1000
CONNECT_TIME 480
IDLE_TIME 10 ;3) 사용자에게 PROFILE 할당하기 여기에서는 위의 예제 1번과 2번에서 만든 PROFILE 을 scott 에게 설정하겠습니다. (1) 현재 모든 사용자가 적용 받고 있는 PROFILE 확인하기
SYS>SELECT username "사용자명" , profile "적용 프로파일"
FROM dba_users
WHERE username=’SCOTT’ ;
사용자명 적용 프로파일
-----------------------------
SCOTT DEFAULT
(2) 해당 PROFILE 에 어떤 내용이 있는지 확인하기 - 위에서 만든 sample_prof 의 내용을 조회하겠습니다.
SYS>SELECT * FROM dba_profiles
WHERE PROFILE='SAMPLE_PROF' ;PROFILE RESOURCE_NAME RESOURCE LIMIT
-------------------------------------- ---------------- --------------
SAMPLE_PROF COMPOSITE_LIMIT KERNEL DEFAULT SAMPLE_PROF SESSIONS_PER_USER KERNEL DEFAULT SAMPLE_PROF CPU_PER_SESSION KERNEL DEFAULT SAMPLE_PROF CPU_PER_CALL KERNEL DEFAULT SAMPLE_PROF LOGICAL_READS_PER_SESSION KERNEL DEFAULT SAMPLE_PROF LOGICAL_READS_PER_CALL KERNEL DEFAULT SAMPLE_PROF IDLE_TIME KERNEL DEFAULT SAMPLE_PROF CONNECT_TIME KERNEL DEFAULT SAMPLE_PROF PRIVATE_SGA KERNEL DEFAULT SAMPLE_PROF FAILED_LOGIN_ATTEMPTS PASSWORD 3 SAMPLE_PROF PASSWORD_LIFE_TIME PASSWORD 15 SAMPLE_PROF PASSWORD_REUSE_TIME PASSWORD 15 SAMPLE_PROF PASSWORD_REUSE_MAX PASSWORD DEFAULT SAMPLE_PROF PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT SAMPLE_PROF PASSWORD_LOCK_TIME PASSWORD 5 SAMPLE_PROF PASSWORD_GRACE_TIME PASSWORD DEFAULT 16 rows selected. - 위에서 만든 re_sample_prof 의 내용을 조회하겠습니다.
SYS> SELECT * FROM dba_profiles
WHERE profile='RE_SAMPLE_PROF' ;PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------------- --------------- ------------
RE_SAMPLE_PROF COMPOSITE_LIMIT KERNEL DEFAULT RE_SAMPLE_PROF SESSIONS_PER_USER KERNEL DEFAULT RE_SAMPLE_PROF CPU_PER_SESSION KERNEL 1000 RE_SAMPLE_PROF CPU_PER_CALL KERNEL DEFAULT RE_SAMPLE_PROF LOGICAL_READS_PER_SESSION KERNEL DEFAULT RE_SAMPLE_PROF LOGICAL_READS_PER_CALL KERNEL DEFAULT RE_SAMPLE_PROF IDLE_TIME KERNEL 10 RE_SAMPLE_PROF CONNECT_TIME KERNEL 480 RE_SAMPLE_PROF PRIVATE_SGA KERNEL DEFAULT RE_SAMPLE_PROF FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT RE_SAMPLE_PROF PASSWORD_LIFE_TIME PASSWORD DEFAULT RE_SAMPLE_PROF PASSWORD_REUSE_TIME PASSWORD DEFAULT RE_SAMPLE_PROF PASSWORD_REUSE_MAX PASSWORD DEFAULT RE_SAMPLE_PROF PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT RE_SAMPLE_PROF PASSWORD_LOCK_TIME PASSWORD DEFAULT RE_SAMPLE_PROF PASSWORD_GRACE_TIME PASSWORD DEFAULT 16 rows selected.
(3) 사용자에게 PROFILE 적용시키고 확인하기
SYS>ALTER USER scott PROFILE sample_prof;
SYS>ALTER USER scott PROFILE re_sample_prof;
SYS>SELECT username, profile
FROM dba_users
WHERE username='SCOTT';
USERNAME PROFILE
----------------------
SCOTT RE_SAMPLE_PROF 위 예에서 알 수 있듯이 여러 개의 프로파일을 적용시킬 수 없습니다. 그래서 처음부터 프로파일을 만들 때 원하는 파라미터를 전부 넣고 한꺼번에 만드신 후 그것을 적용시키셔야 합니다.
(4) 사용 안 하는 PROFILE 삭제하기 - 현재 사용 중인 re_sample_prof 프로파일을 삭제하겠습니다.
SYS>DROP PROFILE re_sample_prof;
drop PROFILE re_sample_prof
*
ERROR at line 1:
ORA-02382: PROFILE RE_SAMPLE_PROF has users assigned, cannot drop without
CASCADE위에서 보듯이 현재 사용자에게 할당이 되어 있는 PROFILE 은 기본적으로 삭제가 안됩 니다. 그러나 CASCADE 옵션으로 삭제하면 삭제 할 수 있습니다. 그렇게 삭제하게 되면 해당 프로파일을 사용하던 사용자는 DEFAULT PROFILE 을 사용하게 됩니다.
SYS>DROP PROFILE re_sample_prof CASCADE;
SYS>SELECT username, PROFILE
FROM dba_users
WHERE username='SCOTT';USERNAME PROFILE
---------------
SCOTT DEFAULT
CREATE PROFILE scott_프로파일명 LIMIT
SESSIONS_PER_USER 2 -- 동시 세션 2명
CPU_PER_SESSION 10000
IDLE_TIME 60 -- 60분 대기 시간 후 세션종료
CONNECT_TIME 480; -- 480분 동안만 작업 가능1.3 프로파일 수정[편집]
ALTER PROFILE RTIS_DEV LIMIT SESSIONS_PER_USER UNLIMITED; -- 세션 제한 UNLIMITED1.4 사용자에게 적용[편집]
ALTER USER scott PROFILE scott_프로파일명;