'Oracle/Admin' 카테고리의 글 목록

Reorg의 목적

Oracle/Admin

Reorganization의 목적 (REORG)

1. 단편화 해소를 통한 성능 향상

- 인덱스 rebuild, 테이블 move를 통하여 단편화 해소

2. 단편화 해소 및 저장위치 변경을 통항 성능 향상

- 오브젝트의 테이블스페이스를 변경하여 단편화를 해소하고 테이블스페이스 내에 데이터를 재배치하여 디스크 공간 확보

3. 스키마 오브젝트의 물리적 재구성을 통한 성능 향상

- 대용량 테이블을 파티셔닝 (테이블1 -> 테이블n)을 통해 물리적으로 분리

==> REORG는 DB의 최적화로 성능향상을 위한 것.

※ 인덱스 단편화는 왜 생기는 걸까?

인덱스는 항상 sorting된 상태를 유지하기 때문에 새로 추가된 자리를 마련하기 위해 공간을 만들어 넣기 때문이다.

인덱스 단편화는 Insert 된 데이터에 의해 발생되는 것이 대부분이다. 일부는 update된 데이터에 의해 발생하지만,

인덱스에 해당하지 않는 컬럼인 경우에는 단편화는 발생하지 않는다.

테이블 reorg : alter table scott.tab_01 move tablespace ts_d_work1;

인덱스 rebuild : alter index scott.ix_tab_01 rebuild tablespace ts_i_work1;

(보통 인덱스 리빌드시 nollogging으로 처리 후 logging모드로 전환.

alter index scott.ix_tab_01 rebuild partition p04 parallel 5 nologging;

alter index scott.ix_tab_01 logging; )

인벨리드 오브젝트 컴파일 : alter function scott.fn_tab_01 compile; --> function/trigger/procedure/view

출처 : http://blog.naver.com/lemonyja/10153976212

저작자표시 비영리 동일조건

'Oracle > Admin' 카테고리의 다른 글

[Oracle Admin] Chap 16. Control File 장애 복구 (0)	2013.09.29
[Oracle Admin] Chap 15. Parameter File 장애 복구 (0)	2013.09.26
[Oracle Admin] Chap 14. Recovery 원리 (0)	2013.09.23
[Oracle Admin] Chap 13. Oracle Backup (0)	2013.09.22
[Oracle Admin] Chap 12. Archive/No Archive log mode (0)	2013.09.22

by 짱구를꼭말려 2013. 10. 24. 00:03

[Oracle Admin] Chap 16. Control File 장애 복구

Oracle/Admin

Chap 16. Control File 장애 복구

1. Control File 관련 장애 해결하기

1) Parameter file 의 경로와 실제 파일 경로가 다른 경우

- 우선 장애 상황을 만들기 위해 DB를 종료한 후 parameter file 에 적혀 있는 3 개의 control file 중에 하나를 삭제해 보자.

SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> !
[oracle@chan ~]$ cat $ORACLE_HOME/dbs/inittestdb.ora | grep control_files
*.control_files='/home/oracle/disk4/control01.ctl','/home/oracle/disk5/control02.ctl','/home/oracle/disk6/control03.ctl'
[oracle@chan ~]$
[oracle@chan ~]$ rm -f /home/oracle/disk6/control03.ctl
[oracle@chan ~]$

-- 다시 DB open
[oracle@chan ~]$ exit
exit

SYS> startup
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size          1344616 bytes
Variable Size         260049816 bytes
Database Buffers      155189248 bytes
Redo Buffers            6086656 bytes
ORA-00205: error in identifying control file, check alert log for more info   -- 장애 발생

SYS>

- alert_testdb.log 파일을 확인해서 장애상황 해결

SYS> !tail $ORACLE_BASE/diag/rdbms/testdb/testdb/trace/alert_testdb.log
Mon Aug 19 22:58:39 2013
ALTER DATABASE   MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/home/oracle/disk6/control03.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE   MOUNT...
Mon Aug 19 22:58:39 2013
Checker run found 1 new persistent data failures

-- 위의 메세지 중 ORA-00202: control file: '/home/oracle/disk6/control03.ctl' 이런 부분이 보인다.
-- 해당 파일을 삭제했기 때문에 나오는 에러이다.
-- 해당 파일만 복사해 주고 다시 DB open 하면 끝!
SYS> select status from v$instance;

STATUS
------------------------
STARTED

SYS> !cp /home/oracle/disk4/control01.ctl /home/oracle/disk6/control03.ctl

SYS> alter database mount;

Database altered.

SYS> alter database open;

Database altered.

2) Control file 끼리 내용이 다른경우

- 다중화된 모든 control file 끼리는 정보가 동일해야 한다. 만약 정보가 달라서 발생하는 version error 가 생길때는 어떻게 해결해야 하는지 살펴보자.

- 우선 하나의 control file 의 정보를 다르게 만들어 보자

SYS> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/disk4/control01.ctl
/home/oracle/disk5/control02.ctl
/home/oracle/disk6/control03.ctl

SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>
SYS> !vi $ORACLE_HOME/dbs/inittestdb.ora

-- 아래 처럼 마지막 control file 을 주석처리 한 후 저장
*.control_files='/home/oracle/disk4/control01.ctl','/home/oracle/disk5/control02.ctl'##,'/home/oracle/disk6/control03.ctl'

SYS> startup
ORACLE instance started.

NAME
-------------------------------------------------------------------------------- /home/oracle/disk4/control01.ctl
/home/oracle/disk5/control02.ctl

-- control file 의 정보가 변경되게 checkpoint 를 여러번 일으켜보자.
SYS> alter system checkpoint;

System altered.

SYS> /

System altered.

SYS> /

System altered.

SYS> /

System altered.

SYS> /

System altered.

-- 주석처리 했던 control file 을 다시 사용하게 끔 변경
SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>
SYS> !vi $ORACLE_HOME/dbs/inittestdb.ora
*.control_files='/home/oracle/disk4/control01.ctl','/home/oracle/disk5/control02.ctl' ,'/home/oracle/disk6/control03.ctl'

SYS> startup
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size          1344616 bytes
Variable Size         260049816 bytes
Database Buffers      155189248 bytes
Redo Buffers            6086656 bytes
ORA-00214: control file '/home/oracle/disk4/control01.ctl' version 865
inconsistent with file '/home/oracle/disk6/control03.ctl' version 846

-- 위의 에러를 보면, control01 의 버전은 865 인데, control03 의 버전은 846 이다.
-- 이럴 경우, 보통 번호가 큰 쪽이 최신 control file 이기 때문에 번호가 큰 파일로 덮어쓰면 된다.
SYS> !cp /home/oracle/disk4/control01.ctl /home/oracle/disk6/control03.ctl

SYS> alter database mount;

Database altered.

SYS> alter database open;

Database altered.

SYS> select name from v$controlfile;

NAME
-------------------------------------------------------------------------------- /home/oracle/disk4/control01.ctl
/home/oracle/disk5/control02.ctl
/home/oracle/disk6/control03.ctl

3) old control file / control file 삭제 된 경우 / incarnation 에러

- 편의를 위해 log와 data 파일을 조회하기 위한 sql 문을 미리 만들어 두자.

SYS> !vi data.sql

conn / as sysdba

set line 200
col tablespace_name for a10
col file_name for a50
col mb for 9999

select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

SYS> @data
Connected.

TABLESPACE    MB FILE_NAME
---------- ----- --------------------------------------------------
USERS          5 /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1      90 /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX       510 /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM       700 /app/oracle/oradata/testdb/system01.dbf
EXAMPLE      346 /app/oracle/oradata/testdb/example01.dbf

SYS> !vi log.sql

set line 200
col group# for 999
col mb for 9999
col member for a45
col seq# for 999
col status for a8
col arc for a5

select a.group#, a.member, b.bytes/1024/1024 MB, b.sequence# "SEQ#", b.status, b.archived "ARC", b.first_change# from v$logfile a, v$log b where a.group#=b.group# order by 1,2;

SYS> @log

GROUP# MEMBER                       MB SEQ# STATUS   ARC   FIRST_CHANGE#
------ --------------------------------------------- ----- ---- -------- ----- -------------
   1 /app/oracle/oradata/testdb/redo01.log        50    4 INACTIVE NO       823608
   2 /app/oracle/oradata/testdb/redo02.log        50    5 INACTIVE NO       841406
   3 /app/oracle/oradata/testdb/redo03.log        50    6 CURRENT NO       859461

- 장애 상황 만들기 ▼

SYS> select name from v$controlfile;

NAME
------------------------------------
/home/oracle/disk4/control01.ctl
/home/oracle/disk5/control02.ctl
/home/oracle/disk6/control03.ctl

SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>
SYS> !vi $ORACLE_HOME/dbs/inittestdb.ora

*.control_files='/home/oracle/disk4/control01.ctl','/home/oracle/disk5/control02.ctl' ##,'/home/oracle/disk6/control03.ctl'

SYS> startup
ORACLE instance started.

NAME
---------------------------------------------
/home/oracle/disk4/control01.ctl
/home/oracle/disk5/control02.ctl

-- 우선 다른 version 의 control file 을 만들기 위해 checkpoint
SYS> alter system checkpoint;

System altered.

SYS> /

System altered.

SYS> /

System altered.

SYS> /

System altered.

SYS> /

System altered.

SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>
SYS> !vi $ORACLE_HOME/dbs/inittestdb.ora

*.control_files='/home/oracle/disk4/control01.ctl','/home/oracle/disk5/control02.ctl' ,'/home/oracle/disk6/control03.ctl'

SYS> startup
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size          1344616 bytes
Variable Size         260049816 bytes
Database Buffers      155189248 bytes
Redo Buffers            6086656 bytes
ORA-00214: control file '/home/oracle/disk4/control01.ctl' version 898 inconsistent with file '/home/oracle/disk6/control03.ctl' version 879

-- 위의 에러를 보면 control01 과 control 03 의 version 이 다르다.
-- 이럴 때 위의 2) Control file 끼리 내용이 다른경우 처럼 해결을 할 때,
-- version 이 높은애를 복사하지 말고 낮은 애를 복사할 경우,
-- 어떤 에러가 나고 어떻게 처리하는지 살펴보자.

SYS> !cp /home/oracle/disk6/control03.ctl /home/oracle/disk4/control01.ctl

SYS> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00214: control file '/home/oracle/disk5/control02.ctl' version 898 inconsistent with file '/home/oracle/disk4/control01.ctl' version 879

-- 이 상황에서 다시 control01 (낮은 버전) 을 control02(높은버전) 로 복사
SYS> !cp /home/oracle/disk4/control01.ctl /home/oracle/disk5/control02.ctl

SYS> alter database mount;

Database altered.
-- 모든 control file 의 version 이 낮은 버전으로 다 동일하게 되어서 mount 는 되었다.
-- 이제 open 시켜보자.

SYS> alter database mount;

Database altered.

SYS> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/app/oracle/oradata/testdb/system01.dbf'
ORA-01207: file is more recent than control file - old control file

-- old control file error 가 나왔다.
- 이 에러의 원인은 control file 의 checkpoint 가 data file 의 checkpoint 정보보다
- 더 예전 내용이기 때문이다.

- 보통 위의 장애 상황을 해결하기 위해서는 3가지 방법이 있을 수 있다.

[1] 현재 data file, redo log file 에 이상이 없을 경우 : 재생성, no resetlogs 로 복구

[2] 현재 data file, redo log file 에 이상이 있고, data backup 이 있을 경우 : using backup control 로 복구 (드문 경우임)

[3] 현재 data file, redo log file 에 이상이 있거나, data backup 이 없을 경우 : 재생성, resetlogs 로 복구

- 해결 방안 [1]: 현재 data file, redo log file에 이상이 없을 경우 (control file 재생성 + no resetlogs)

-- control file 을 재생성 하려면 no mount 상태에서 DB 를 재생성하는 명령어를 입력해야 하는데,
-- 그 명령어가 너무 길기 때문에 script 로 만들어서 생성하는 방법을 이용하자.
-- control file 재성성 script 를 만들기 위해서는 DB 가 mount 상태 이어야 한다.

SYS> select status from v$instance;

STATUS
--------
MOUNTED

SYS> alter database backup controlfile to trace as '/home/oracle/re.sql';

Database altered.

SYS> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SYS> !
[oracle@chan ~]$

-- /home/oracle/re.sql 파일을 열면 Set #1. NORESETLOGS case 과
-- Set #2. RESETLOGS case 부분으로 script 가 2 부분으로 나눠져 있다.
-- 여기서는 no resetlogs 를 사용할 것이므로, set #2 부분은 다 지우도록 하자.
-- 즉, 아래의 내용만 있으면 된다.
-- 주의 할 점은 LOGFILE 부분과 DATAFILE 부분은 정확해야 한다.

[oracle@chan ~]$ vi /home/oracle/re.sql

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS NOARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 100
      MAXINSTANCES 8
     MAXLOGHISTORY 292
LOGFILE
   GROUP 1 '/app/oracle/oradata/testdb/redo01.log' SIZE 50M BLOCKSIZE 512,
   GROUP 2 '/app/oracle/oradata/testdb/redo02.log' SIZE 50M BLOCKSIZE 512,
   GROUP 3 '/app/oracle/oradata/testdb/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
   '/app/oracle/oradata/testdb/system01.dbf',
   '/app/oracle/oradata/testdb/sysaux01.dbf',
   '/app/oracle/oradata/testdb/undotbs01.dbf',
   '/app/oracle/oradata/testdb/users01.dbf',
   '/app/oracle/oradata/testdb/example01.dbf'
CHARACTER SET KO16MSWIN949
;

[oracle@chan ~]$ exit
exit

SYS> @/home/oracle/re.sql
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size          1344616 bytes
Variable Size         260049816 bytes
Database Buffers      155189248 bytes
Redo Buffers            6086656 bytes
Control file created.

SYS> alter database open;

Database altered.

SYS> select name from v$controlfile;

NAME
--------------------------------------
/home/oracle/disk4/control01.ctl
/home/oracle/disk5/control02.ctl
/home/oracle/disk6/control03.ctl

- 해결 방안 [2]: 현재 data file, redo log file 에 이상이 있고, data backup 이 있을 경우 (using backup control 로 복구)

-- 현재 상태를 전체 백업 수행

SYS> select name from v$datafile;

NAME
---------------------------------------------
/app/oracle/oradata/testdb/system01.dbf
/app/oracle/oradata/testdb/sysaux01.dbf
/app/oracle/oradata/testdb/undotbs01.dbf
/app/oracle/oradata/testdb/users01.dbf
/app/oracle/oradata/testdb/example01.dbf

SYS> select name from v$controlfile;

NAME
---------------------------------------------
/home/oracle/disk4/control01.ctl
/home/oracle/disk5/control02.ctl
/home/oracle/disk6/control03.ctl

SYS> select member from v$logfile;

MEMBER
---------------------------------------------
/app/oracle/oradata/testdb/redo03.log
/app/oracle/oradata/testdb/redo02.log
/app/oracle/oradata/testdb/redo01.log

SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>
SYS> !

-- 미리 만들어 둔 /data/backup/close 폴더에 위의 파일들을 복사한다.
[oracle@chan ~]$ cp /app/oracle/oradata/testdb/*.dbf /data/backup/close/
[oracle@chan ~]$ cp /app/oracle/oradata/testdb/*.log /data/backup/close/
[oracle@chan ~]$ cp /home/oracle/disk4/*.ctl /data/backup/close/
[oracle@chan ~]$ cp /home/oracle/disk5/*.ctl /data/backup/close/
[oracle@chan ~]$ cp /home/oracle/disk6/*.ctl /data/backup/close/

-- 위의 장애 상황 만들기 를 참고해서 다시 장애 상황을 만든다.
[oracle@chan ~]$ vi $ORACLE_HOME/dbs/inittestdb.ora

*.control_files='/home/oracle/disk4/control01.ctl','/home/oracle/disk5/control02.ctl' ##,'/home/oracle/disk6/control03.ctl'

[oracle@chan ~]$ exit
exit

SYS> startup
ORACLE instance started.

System altered.

SYS> /

System altered.

SYS> /

System altered.

SYS> /

System altered.

SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>
SYS> !
[oracle@chan ~]$ vi $ORACLE_HOME/dbs/inittestdb.ora

*.control_files='/home/oracle/disk4/control01.ctl','/home/oracle/disk5/control02.ctl' ,'/home/oracle/disk6/control03.ctl'

[oracle@chan ~]$ exit
exit

SYS> startup
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size          1344616 bytes
Variable Size         260049816 bytes
Database Buffers      155189248 bytes
Redo Buffers            6086656 bytes
ORA-00214: control file '/home/oracle/disk4/control01.ctl' version 939
inconsistent with file '/home/oracle/disk6/control03.ctl' version 922

SYS> !cp /home/oracle/disk6/control03.ctl /home/oracle/disk4/control01.ctl

SYS> !cp /home/oracle/disk4/control01.ctl /home/oracle/disk5/control02.ctl

SYS> alter database mount;

Database altered.

-- 다시 old control file 장애가 발생했다. 이제 백업 데이터를 가지고 복구해 보자.
SYS> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SYS>
SYS> !cp /data/backup/close/*.dbf /app/oracle/oradata/testdb/ -- 데이터 파일만 복원

SYS> startup mount
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size          1344616 bytes
Variable Size         260049816 bytes
Database Buffers      155189248 bytes
Redo Buffers            6086656 bytes
Database mounted.
SYS>
SYS> recover database until cancel using backup controlfile;
ORA-00279: change 866181 generated at 08/20/2013 00:39:18 needed for thread 1
ORA-00289: suggestion :
/app/oracle/fast_recovery_area/TESTDB/archivelog/2013_08_22/o1_mf_1_7_%u_.arc
ORA-00280: change 866181 for thread 1 is in sequence #7

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/app/oracle/fast_recovery_area/TESTDB/archivelog/2013_08_22/o1_mf_1_7_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log
'/app/oracle/fast_recovery_area/TESTDB/archivelog/2013_08_22/o1_mf_1_7_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SYS> alter database open resetlogs;

Database altered.

SYS> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/disk4/control01.ctl
/home/oracle/disk5/control02.ctl
/home/oracle/disk6/control03.ctl

SYS> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/testdb/system01.dbf
/app/oracle/oradata/testdb/sysaux01.dbf
/app/oracle/oradata/testdb/undotbs01.dbf
/app/oracle/oradata/testdb/users01.dbf
/app/oracle/oradata/testdb/example01.dbf

SYS> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/app/oracle/oradata/testdb/redo03.log
/app/oracle/oradata/testdb/redo02.log
/app/oracle/oradata/testdb/redo01.log

- 해결 방안 [3]: 현재 data file, redo log file에 이상이 있고 백업 파일이 없을 경우 (control file 재생성 + resetlogs)

- 사용중이다가 비정상 종료된 경우 checkpoint 가 다른 data file 을 사용해서 긴급 복구 하는 경우를 살펴보자

-- 현재 상태 확인
SYS> @data
Connected.

TABLESPACE    MB FILE_NAME
---------- ----- --------------------------------------------------
EXAMPLE      346 /app/oracle/oradata/testdb/example01.dbf
USERS          5 /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1      90 /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX       510 /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM       700 /app/oracle/oradata/testdb/system01.dbf

SYS> @log

GROUP# MEMBER                       MB SEQ# STATUS   ARC   FIRST_CHANGE#
------ --------------------------------------------- ----- ---- -------- ----- -------------
      1 /app/oracle/oradata/testdb/redo01.log        50    4 CURRENT NO       866631
      2 /app/oracle/oradata/testdb/redo02.log        50    2 INACTIVE NO       866625
      3 /app/oracle/oradata/testdb/redo03.log        50    3 INACTIVE NO       866628

-- 장애 상황 만들기
SYS> create table scott.test01 (no number);

Table created.

SYS> insert into scott.test01 values (1);

1 row created.

SYS> insert into scott.test01 values (2);

1 row created.

SYS> commit;

Commit complete.

SYS> shutdown abort;
ORACLE instance shut down.
SYS>
SYS> !rm -f /app/oracle/oradata/testdb/*.log

SYS> !rm -f /home/oracle/disk4/*.ctl

SYS> !rm -f /home/oracle/disk5/*.ctl

SYS> !rm -f /home/oracle/disk6/*.ctl

SYS> startup
ORACLE instance started.

-- 위의 [2] 번 case 에서 백업해 둔 (오래된) control file 복원
SYS> !cp /data/backup/close/control01.ctl /home/oracle/disk4/.

SYS> !cp /data/backup/close/control02.ctl /home/oracle/disk5/.

SYS> !cp /data/backup/close/control03.ctl /home/oracle/disk6/.

SYS> startup
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size          1344616 bytes
Variable Size         260049816 bytes
Database Buffers      155189248 bytes
Redo Buffers            6086656 bytes
Database mounted.
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/app/oracle/oradata/testdb/system01.dbf'      -- 장애 발생

-- control file 재생성 script. (resetlogs)
SYS> alter database backup controlfile to trace as '/home/oracle/re2.sql';

Database altered.

SYS> shutdown abort;
ORACLE instance shut down.

SYS> !vi /home/oracle/re2.sql
-- 아래 부분만 남기자

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS NOARCHIVELOG
     MAXLOGFILES 16
     MAXLOGMEMBERS 3
     MAXDATAFILES 100
     MAXINSTANCES 8
     MAXLOGHISTORY 292
LOGFILE
   GROUP 1 '/app/oracle/oradata/testdb/redo01.log' SIZE 50M BLOCKSIZE 512,
   GROUP 2 '/app/oracle/oradata/testdb/redo02.log' SIZE 50M BLOCKSIZE 512,
   GROUP 3 '/app/oracle/oradata/testdb/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
   '/app/oracle/oradata/testdb/system01.dbf',
   '/app/oracle/oradata/testdb/sysaux01.dbf',
   '/app/oracle/oradata/testdb/undotbs01.dbf',
   '/app/oracle/oradata/testdb/users01.dbf',
   '/app/oracle/oradata/testdb/example01.dbf'
CHARACTER SET KO16MSWIN949
;

SYS> @/home/oracle/re2.sql
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size          1344616 bytes
Variable Size         260049816 bytes
Database Buffers      155189248 bytes
Redo Buffers            6086656 bytes

Control file created.

SYS> alter database open resetlogs;
alter database open resetlogs
* ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/app/oracle/oradata/testdb/system01.dbf'

-- 위와 같은 경우의 에러는, DB가 종료 될 때 shutdown abort 로 비정상 종료되어
-- 데이터 파일끼리 checkpoint 정보가 동기화되지 않아서 resetlogs 로 open 할 수 없으니
-- 복구하라는 내용이다. 하지만 현재 모든 redo log 역시 삭제되어 복구할 수 없다.
-- 이럴 경우 강제로 data file 끼리 checkpoint 정보를 동기화시켜서 open 시키는
-- hidden parameter 를 사용해야 한다. 단, 이 parameter 는 위험하기 때문에 실무에선
-- 권장하지 않는다.

SYS> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SYS>
SYS> !vi $ORACLE_HOME/dbs/inittestdb.ora

-- 아래 부분 추가 후 저장
_allow_resetlogs_corruption=true

SYS> startup mount;
ORACLE instance started.

Database altered.

SYS> select * from scott.test01;
select * from scott.test01
*
ERROR at line 1:
ORA-00942: table or view does not exist

-- DB 는 open 되었지만, 데이터는 복구되지 못했다.

2. Control file 를 재생성 해야 하는 대표적인 경우

1) control file 이 전부 사라졌을 경우

2) old control file 에러 발생 시

3) DB Name 을 변경하고 싶을 때

4) 최대 데이터 파일 갯수와 redo log 파일 갯수를 변경하고 싶을 때

저작자표시 비영리 동일조건

'Oracle > Admin' 카테고리의 다른 글

Reorg의 목적 (0)	2013.10.24
[Oracle Admin] Chap 15. Parameter File 장애 복구 (0)	2013.09.26
[Oracle Admin] Chap 14. Recovery 원리 (0)	2013.09.23
[Oracle Admin] Chap 13. Oracle Backup (0)	2013.09.22
[Oracle Admin] Chap 12. Archive/No Archive log mode (0)	2013.09.22

by 짱구를꼭말려 2013. 9. 29. 00:03

[Oracle Admin] Chap 15. Parameter File 장애 복구

Oracle/Admin

Chap 15. Parameter File 장애 복구

Parameter file 은 DB 시작 단계 중 startup 에서 no mount 단계로 가기 위해 꼭 필요한 설계도 같은 파일이다.

pfile 과 spfile 두 가지 종류의 parameter file 이 있는데, pfile 은 initSID.ora 라는 이름의 수정가능한 일반 텍스트 파일이고, spfile 은 spfileSID.ora 란 이름의 바이너리(binary) 파일이라 사람이 직접 수정할 수 없다. spfile 은 DBA가 server process 에게 적절한 SQL을 수행히여 변경시켜야 한다.

Parameter file 의 경로는 $ORACLE_HOME/dbs 에 존재하는데 만약 spfile 과 pfile 이 동시에 존재할 경우 spfile 만 사용해서 oracle 을 구동한다.

1. Parameter file 이 없는 상태에서 pfile 생성하기

모든 parameter file이 삭제되었을 경우, 최초 설치시 생성되었던 parameter file 을 활용해서 pfile 을 생성해 보자.

-- 현재 parameter file 상태 (spfile 을 사용중이다.)
SYS> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /app/oracle/product/11g/dbs/sp filetestdb.ora

-- 장애 상황을 만들기 위해 DB 을 종료 한 후 모든 parameter file 을 삭제.
SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> !
[oracle@chan ~]$ cd $ORACLE_HOME/dbs
[oracle@chan dbs]$ ls -l
total 24
-rw-rw---- 1 oracle oinstall 1544 Aug 19 19:47 hc_DBUA0.dat
-rw-rw---- 1 oracle oinstall 1544 Aug 19 20:02 hc_testdb.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 24 Aug 19 19:51 lkTESTDB
-rw-r----- 1 oracle oinstall 1536 Aug 19 19:54 orapwtestdb
-rw-r----- 1 oracle oinstall 2560 Aug 19 19:54 spfiletestdb.ora

-- spfile 삭제
[oracle@chan dbs]$ rm -f spfiletestdb.ora

-- 최초 설치시 만들어진 원본 pfile 복사
[oracle@chan dbs]$ ls -l $ORACLE_BASE/admin/testdb/pfile
total 4
-rw-r----- 1 oracle oinstall 1777 Aug 19 19:50 init.ora.7192013195444
[oracle@chan dbs]$ cp $ORACLE_BASE/admin/testdb/pfile/init.ora.7192013195444 inittestdb.ora
[oracle@chan dbs]$ ls -l
total 24
-rw-rw---- 1 oracle oinstall 1544 Aug 19 19:47 hc_DBUA0.dat
-rw-rw---- 1 oracle oinstall 1544 Aug 19 20:02 hc_testdb.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 1777 Aug 19 20:04 inittestdb.ora
-rw-r----- 1 oracle oinstall 24 Aug 19 19:51 lkTESTDB
-rw-r----- 1 oracle oinstall 1536 Aug 19 19:54 orapwtestdb
[oracle@chan dbs]$ exit
exit

SYS> startup
ORACLE instance started.

NAME TYPE VALUE
------------------------ ------- ------------------------------
spfile string

2. pfile, spfile 만들기

1) pfile로 부터 spfile 만들기

SYS> !ls -l /app/oracle/product/11g/dbs/*.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 /app/oracle/product/11g/dbs/init.ora
-rw-r----- 1 oracle oinstall 1777 Aug 19 20:04 /app/oracle/product/11g/dbs/inittestdb.ora

SYS> create spfile from pfile;

File created.

2) spfile로 부터 pfile 만들기

-- pfile 삭제
SYS> !rm -f /app/oracle/product/11g/dbs/inittestdb.ora

SYS> !ls -l /app/oracle/product/11g/dbs/*.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 /app/oracle/product/11g/dbs/init.ora
-rw-r----- 1 oracle oinstall 2560 Aug 19 20:10 /app/oracle/product/11g/dbs/spfiletestdb.ora

-- 다시 pfile 생성

SYS> create pfile from spfile;

File created.

SYS> !ls -l /app/oracle/product/11g/dbs/*.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 /app/oracle/product/11g/dbs/init.ora
-rw-r--r-- 1 oracle oinstall 905 Aug 19 20:18 /app/oracle/product/11g/dbs/inittestdb.ora
-rw-r----- 1 oracle oinstall 2560 Aug 19 20:18 /app/oracle/product/11g/dbs/spfiletestdb.ora

SYS> !rm -f /app/oracle/product/11g/dbs/spfiletestdb.ora

저작자표시 비영리 동일조건

'Oracle > Admin' 카테고리의 다른 글

Reorg의 목적 (0)	2013.10.24
[Oracle Admin] Chap 16. Control File 장애 복구 (0)	2013.09.29
[Oracle Admin] Chap 14. Recovery 원리 (0)	2013.09.23
[Oracle Admin] Chap 13. Oracle Backup (0)	2013.09.22
[Oracle Admin] Chap 12. Archive/No Archive log mode (0)	2013.09.22

by 짱구를꼭말려 2013. 9. 26. 21:35

[Oracle Admin] Chap 14. Recovery 원리

Oracle/Admin

Chap 14. Recovery 원리

1. Recovery 단계

- DB 에서 startup 이나 recover 명령어를 입력했을 때 서버 프로세스는 먼저 control file 을 읽어서 문제가 있는지 없는지 확인한다. 아래는 Oracle 이 startup 되는 단계이다.

1) nomount

서버 프로세스가 parameter file (pfile, / spfile) 을 PGA 로 읽어와서 해당 parameter file 에 지정한 대로 instance 를 생성하는 단계이다.

2) mount

여기서는 parameter file 에 기록되어 있는 위치에서 control file 을 읽어서 장애를 복구하거나, 유지 관리 작업 (rename file, drop file 등등) 들을 하는 단계이다. 자세한 순서는 아래와 같다.

* CKPT 프로세스가 모든 control file 을 읽고 일시적으로 lock 설정을 한다.

* control file header 정보를 확인해서 이상이 없는지 체크한다. 그리고 mountID 를 계산해서 control file 에 저장한다.

* Parameter file 의 database name 과 control file 의 DB name 이 동일한지 체크한다.

* 위 과정까지 이상이 없다면 alert log file 에 "Successful mount of redo Thread" 라는 메세지를 기록한 후 "Database mounted~~" 라는 메세지를 기록한다.

3) open

Data file header 부분의 정보와 control file 의 정보를 서로 비교해서 장애 유무를 판단한다. 이때 사용되는 것이 checkpoint SCN 정보이다. 이 SCN 이 이상이 없을 경우 DB는 문제가 없다고 판단하고 정상 open 된다.

2. Recovery 원리

1) Control file 내의 Data file records 부분의 checkpoint cnt 부분의 숫자를 가지고 data file의 checkpoint cnt 번호와 동일한지 비교한다. 만약 이 부분이 문제가 없다면 다음 단계를 진행한다.

2) 각 data file 의 header 에 있는 checkpoint SCN 이 control file 안에 있는 stop scn 번호와 동일한지 비교 후. 모두 일치 하면 복구할 필요가 없지만 불일치 하면 복구를 시도한다.

3) 복구가 필요할 경우, data file 에 부족한 scn 중에서 가장 낮은 scn 이 몇 번인지 확인후 control file 안에 있는 log file records 를 찾아간다. data file 과 control file 에서 차이나는 scn 부분을 redo log file 을 뒤져서 low scn 부분을 확인, 필요한 내용이 들어있는 파일을 찾아낸다. 그리고 그 경로로 찾아가서 복구해야 하는 내용을 읽어서 필요한 로그 파일을 순차적으로 적용시킨다.

4) 적당한 redo log file 을 찾으면 가장 낮은 scn 번호부터 roll forward 한다.

5) roll forward 가 끝나면 commit 이 수행되지 않은 transaction 을 찾기위해 undo$ 딕셔너리를 찾아본다. 거기서 active 한 undo segment 를 찾고 commit 안된 데이터를 roll backward 한다.

3. Recovery 종류

1) Crash Recovery (Instance recovery)

- 운영 중이던 DB 가 비 정상적으로 종료된 것을 startup 시에 automatically 하게 recovery 하는 것을 의미한다.

- 이 때는 online redo log file 만 사용된다.

2) Media Recovery

- 위의 Instance recovery 시에 file 삭제나 disk fail 등의 이유로 Instance recovery 가 실패할 경우 DBA 가 수동으로 백업 파일 등을 복원한 후 online redo log file 과 archive redo log fie 등을 사용하여 recovery 해야 하는데 이것을 Media Recovery 라고 한다.

저작자표시 비영리 동일조건

'Oracle > Admin' 카테고리의 다른 글

[Oracle Admin] Chap 16. Control File 장애 복구 (0)	2013.09.29
[Oracle Admin] Chap 15. Parameter File 장애 복구 (0)	2013.09.26
[Oracle Admin] Chap 13. Oracle Backup (0)	2013.09.22
[Oracle Admin] Chap 12. Archive/No Archive log mode (0)	2013.09.22
[Oracle Admin] Chap 11. DBMS_JOB & DBMS_SCHEDULER (0)	2013.09.18

by 짱구를꼭말려 2013. 9. 23. 00:42

[Oracle Admin] Chap 13. Oracle Backup

Oracle/Admin

Chap 13. Oracle Backup

1. 백업 대상

- 필수 백업 대상 : Data file, Control file, Redo log file

- 선택 백업 대상 : parameter file, password file

1) Data file

- 실제 데이터가 저장되어 있는 파일이다. 아래와 같이 조회가능.

SQL> select name, status from v$datafile;

NAME STATUS

-------------------------------------------------- -------

/app/oracle/oradata/testdb/system01.dbf SYSTEM

/app/oracle/oradata/testdb/sysaux01.dbf ONLINE

/app/oracle/oradata/testdb/undotbs01.dbf ONLINE

/app/oracle/oradata/testdb/users01.dbf ONLINE

/app/oracle/oradata/testdb/example01.dbf ONLINE

/app/oracle/oradata/testdb/ts_webhard.dbf ONLINE

/app/oracle/oradata/testdb/ts_web_idx.dbf ONLINE

2) Control file

- DB를 운영하는데 있어 중요한 설정 파일들이다. 현재 사용중인 파일만 유효하고 과거에 썼던 파일들은 백업 받아도 사용할 수 없다.

SQL> select name from v$controlfile;
NAME
-------------------------------------------------------
/app/oracle/oradata/testdb/control01.ctl
/app/oracle/fast_recovery_area/testdb/control02.ctl

3) Redo log file

- 데이터에 변경이 일어난 내용을 복구에 사용하기 위해 저장하고 있는 파일

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status from v$logfile a, v$log b where a.group#=b.group# order by 1,2;

GROUP# MEMBER MB ARC STATUS

---------- ---------------------------------------- ---------- --- ----------------
        1      /app/oracle/oradata/testdb/redo01.log        50         YES INACTIVE
        2       /app/oracle/oradata/testdb/redo02.log        50         YES INACTIVE
        3      /app/oracle/oradata/testdb/redo03.log        50         NO CURRENT

4) Parameter file / Password file

- Parameter file: Oracle 서버를 운영하는데 있어 필요한 각종 설정 정보를 저장하고 있는 파일. 중요하다!!

- Password file: sysdba 권한의 암호를 저장하는 파일로 일반적으로 사용자의 암호는 dictionary 에 저장되어서 DB가 open 후에 조회 가능하다. (db_users). 하지만 db가 shutdown 상태에서도 조회가 가능해야 하기 때문에 sysdba의 암호를 일반 파일에 저장해 둔다.

- sys 계정 암호를 항상 물어보도록 설정하기

처음 Oracle 을 설치하면 sys 계정의 암호를 묻지 않는데, 그 이유는 $ORACLE_HOME/network/admin 안에 sqlnet.ora 라는 파일이 없기 때문이다. (정확히는 이 파일안의 sqlnet.authentication_services 설정)

우선 $ORACLE_HOME/network/admin/sqlnet.ora 파일이 없다면 netca 로 파일 생성해 준다.

[oracle@chan ~]$ netca

====> 이 부분은 그래픽 모드, x-window 환경이 필요하므로, VM 안의 oracle 서버에 oracle 계정으로 접속해서 터미널에서 작업하던지, 아니면 xmanger 같은 프로그램을 사용한다. (본인은 xmanager 를 사용했다.)

완료후에, 해당 파일이 생성되었는지 확인한다.

[oracle@chan ~]$ ls -l $ORACLE_HOME/network/admin/sqlnet.ora

-rw-r--r-- 1 oracle oinstall 237 Jul 10 15:33 /app/oracle/product/11g/network/admin/sqlnet.ora

파일이 잘 생성되었으면, 파일을 vi로 열어서 아래 부분을 추가하고 저장한다.

sqlnet.authentication_services=(none)

그리고 나서 sqlplus / as sysdba 명령으로 (암호 없이) 로그인을 시도하면 암호를 물어보는 프롬프트가 뜰 것이다.

[oracle@chan ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jul 10 15:59:20 2013

ERROR:

ORA-01031: insufficient privileges

Enter user-name:

이럴 경우 암호를 입력해서 접속하면 된다.

Enter user-name: sys/암호 as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning option

SYS>

2. 백업의 종류

1) 닫힌 백업 (cold backup / closed backup)

- 백업 대상 확인하기

SYS> select name, status from v$datafile;

NAME                            STATUS
------------------------------------------------------- -------
/app/oracle/oradata/testdb/system01.dbf         SYSTEM
/app/oracle/oradata/testdb/sysaux01.dbf         ONLINE
/app/oracle/oradata/testdb/undotbs01.dbf        ONLINE
/app/oracle/oradata/testdb/users01.dbf          ONLINE
/app/oracle/oradata/testdb/example01.dbf        ONLINE
/app/oracle/oradata/testdb/ts_webhard.dbf       ONLINE
/app/oracle/oradata/testdb/ts_web_idx.dbf       ONLINE

SYS> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.sequence#, b.status from v$logfile a, v$log b where a.group#=b.group#;

GROUP#       MEMBER                                              MB ARC SEQUENCE# STATUS
---------- ----------------------------------------- --- --- -------------- ----------------
        1 /app/oracle/oradata/testdb/redo01.log              50 YES         4            INACTIVE
        2 /app/oracle/oradata/testdb/redo02.log              50 YES          5           INACTIVE
        3 /app/oracle/oradata/testdb/redo03.log              50 NO            6          CURRENT

SYS> select name from v$controlfile;

NAME

-------------------------------------------------------

/app/oracle/oradata/testdb/control01.ctl
/app/oracle/fast_recovery_area/testdb/control02.ctl

/* =====> 테스트 편의성을 위해서 control file 을 control01.ctl 하나만 쓰도록 설정하겠다. */

SYS> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SYS>

SYS> !vi $ORACLE_HOME/dbs/inittestdb.ora

/* ~~~~ 아래 부분처럼 수정 후 저장 */

*.control_files='/app/oracle/oradata/testdb/control01.ctl'

SYS> startup mount

ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size          1344616 bytes
Variable Size         289409944 bytes
Database Buffers      125829120 bytes
Redo Buffers            6086656 bytes
Database mounted.

SYS>

SYS> select name from v$controlfile;

NAME
-------------------------------------------------------
/app/oracle/oradata/testdb/control01.ctl

- DB 종료하기 (정상 종료)

SYS> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

- /data/backup/close 의 경로로 파일 copy

[oracle@chan ~]$ mkdir -p /data/backup/close

[oracle@chan ~]$ cp -av /app/oracle/oradata/testdb/*.dbf /data/backup/close/

`/app/oracle/oradata/testdb/example01.dbf' -> `/data/backup/close/example01.dbf'
`/app/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/close/sysaux01.dbf'
`/app/oracle/oradata/testdb/system01.dbf' -> `/data/backup/close/system01.dbf'
`/app/oracle/oradata/testdb/temp01.dbf' -> `/data/backup/close/temp01.dbf'
`/app/oracle/oradata/testdb/temp_web.dbf' -> `/data/backup/close/temp_web.dbf'
`/app/oracle/oradata/testdb/ts_webhard.dbf' -> `/data/backup/close/ts_webhard.dbf'
`/app/oracle/oradata/testdb/ts_web_idx.dbf' -> `/data/backup/close/ts_web_idx.dbf'
`/app/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/close/undotbs01.dbf'
`/app/oracle/oradata/testdb/users01.dbf' -> `/data/backup/close/users01.dbf'

[oracle@chan ~]$ cp -av /app/oracle/oradata/testdb/*.log /data/backup/close/
`/app/oracle/oradata/testdb/redo01.log' -> `/data/backup/close/redo01.log'
`/app/oracle/oradata/testdb/redo02.log' -> `/data/backup/close/redo02.log' `/app/oracle/oradata/testdb/redo03.log' -> `/data/backup/close/redo03.log'

[oracle@chan ~]$ cp -av /app/oracle/oradata/testdb/*.ctl /data/backup/close/
`/app/oracle/oradata/testdb/control01.ctl' -> `/data/backup/close/control01.ctl'

[oracle@chan ~]$ cp -av /app/oracle/product/11g/dbs/inittestdb.ora /data/backup/close/
`/app/oracle/product/11g/dbs/inittestdb.ora' -> `/data/backup/close/inittestdb.ora'

[oracle@chan ~]$ cp -av /app/oracle/product/11g/dbs/orapwtestdb /data/backup/close/
`/app/oracle/product/11g/dbs/orapwtestdb' -> `/data/backup/close/orapwtestdb'

- DB open

[oracle@chan ~]$ exit

exit

SYS> startup

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 260049816 bytes

Database Buffers 155189248 bytes

Redo Buffers 6086656 bytes

Database mounted.

Database opened.

2) 열린 백업 (hot backup / open backup / begin backup)

- 닫힌 백업 같은 경우는 DB 를 종료해야만 가능하다는 치명적인 단점이 있다. 하지만 서비스 중인 DB 를 내리지 않고 백업을 받아야 할 경우 열린 백업 (hot backup) 을 이용할 수 있다.

- 열린 백업 같은 경우는 tablespace 단위로 백업을 수행한다. 11g 부터는 database 전체를 한번에 백업 모드로 설정할 수 있는 옵션이 있지만 redo log 량이 많이 생기기 때문에 권장하지는 않는다.

- 열린 백업을 받기 위해서는 반드시 DB 가 archive log mode 여만 한다.

- 열린 백업은 Data file 과 Control file 은 백업이 가능하지만, online redo log file 은 불가능 하다.

- users tablespace 를 열린 백업으로 받기

SYS> archive log list;
Database log mode          Archive Mode
Automatic archival         Enabled
Archive destination        /data/arc2
Oldest online log sequence     108
Next log sequence to archive   110
Current log sequence           110

SYS> alter tablespace users begin backup;

Tablespace altered.

SYS> !mkdir -p /data/backup/open

SYS> !cp /app/oracle/oradata/testdb/users01.dbf /data/backup/open/

SYS> !ls -l /data/backup/open

total 7700

-rw-r----- 1 oracle oinstall 7872512 Jul 10 16:44 users01.dbf

SYS> alter tablespace users end backup;

Tablespace altered.

- end backup 이 수행되었는지 여부 확인.

SYS> select a.file#, a.name, b.status, to_char(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time from v$datafile a, v$backup b where a.file#=b.file#

FILE# NAME STATUS TIME

---------- -------------------------------------------------- --------------- -------------------
        1 /app/oracle/oradata/testdb/system01.dbf        NOT ACTIVE
        2 /app/oracle/oradata/testdb/sysaux01.dbf        NOT ACTIVE
        3 /app/oracle/oradata/testdb/undotbs01.dbf       NOT ACTIVE
        4 /app/oracle/oradata/testdb/users01.dbf         NOT ACTIVE      2013-07-15:15:03:30
        5 /app/oracle/oradata/testdb/example01.dbf       NOT ACTIVE
        6 /app/oracle/oradata/testdb/ts_webhard.dbf          NOT ACTIVE
        7 /app/oracle/oradata/testdb/ts_web_idx.dbf          NOT ACTIVE

8 rows selected.

- hot backup 수행 테스트 (script 이용)

SYS> !vi /app/oracle/open_backup.sql

/* ### 아래 내용 입력 후 저장 */

conn / as sysdba;
alter tablespace system begin backup;
!cp -av /app/oracle/oradata/testdb/system01.dbf /data/backup/open/
alter tablespace system end backup;

alter tablespace sysaux begin backup;
!cp -av /app/oracle/oradata/testdb/sysaux01.dbf /data/backup/open/
alter tablespace sysaux end backup;

alter tablespace undotbs1 begin backup;
!cp -av /app/oracle/oradata/testdb/undotbs01.dbf /data/backup/open/
alter tablespace undotbs1 end backup;

alter tablespace users begin backup;
!cp -av /app/oracle/oradata/testdb/users01.dbf /data/backup/open/
alter tablespace users end backup;

alter tablespace example begin backup;
!cp -av /app/oracle/oradata/testdb/example01.dbf /data/backup/open/
alter tablespace example end backup;

alter tablespace ts_webhard begin backup;
!cp -av /app/oracle/oradata/testdb/ts_webhard.dbf /data/backup/open/
alter tablespace ts_webhard end backup;

alter tablespace ts_web_idx begin backup;
!cp -av /app/oracle/oradata/testdb/ts_web_idx.dbf /data/backup/open/
alter tablespace ts_web_idx end backup;

alter database backup controlfile to '/data/backup/open/control01.ctl';
:wq!

SYS> @/app/oracle/open_backup.sql
Connected.
Tablespace altered.
`/app/oracle/oradata/testdb/system01.dbf' -> `/data/backup/open/system01.dbf'
Tablespace altered.
Tablespace altered.
`/app/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/open/sysaux01.dbf'
Tablespace altered.
Tablespace altered.
`/app/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/open/undotbs01.dbf'
Tablespace altered.
Tablespace altered.
`/app/oracle/oradata/testdb/users01.dbf' -> `/data/backup/open/users01.dbf'
Tablespace altered.
Tablespace altered.
`/app/oracle/oradata/testdb/example01.dbf' -> `/data/backup/open/example01.dbf'
Tablespace altered.
Tablespace altered.
`/app/oracle/oradata/testdb/ts_webhard.dbf' -> `/data/backup/open/ts_webhard.dbf'
Tablespace altered.
Tablespace altered.
`/app/oracle/oradata/testdb/ts_web_idx.dbf' -> `/data/backup/open/ts_web_idx.dbf'
Tablespace altered.
Database altered.

SYS> alter system checkpoint;
System altered.

- 일자별로 자동 백업 디렉토리를 생성해서 begin backup 하는 테스트

위의 hot backup script 는 tablespace 직접 다 써주어야 하기 때문에 불편하다. 다음을 참고해서 5개의 script 로 편리하게 backup 받자!

* 일자별로 자동 백업 디렉토리를 생성해서 begin backup 하는 테스트

(참고: 오라클 백업과 복구의 정석1 - 서진수 저자)

1. main_backup.sh

- main script 로 다른 script 들을 호출

#!/bin/bash

export LANG=C
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=/app/oracle/product/11g
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=testdb

touch $ORACLE_BASE/total.log
echo ""
echo "==== set begin backup mode ===="
time sh $ORACLE_BASE/begin_backup.sh >> $ORACLE_BASE/total.log
echo ""
echo "==== end backup mode ===="
echo ""
echo "==== start file copy ===="
time sh $ORACLE_BASE/copy_backup.sh >> $ORACLE_BASE/total.log
echo "==== end file copy ===="
echo ""
echo "==== set end backup mode ===="
time sh $ORACLE_BASE/end_backup.sh >> $ORACLE_BASE/total.log
echo "==== complete hot backup ===="

2. begin_backup.sh

- tablespace 를 begin backup 상태로 만들기

#!/bin/bash

export LANG=C
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=/app/oracle/product/11g
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=testdb

sqlplus /nolog<<EOF1
conn / as sysdba

set head off
set feedback off
set time off
set timing off
set echo off
spool /tmp/online.tmp

select 'alter tablespace '|| tablespace_name ||' begin backup;' \
from dba_tablespaces \
where status='ONLINE' \
and contents != 'TEMPORARY';

spool off

!cat /tmp/online.tmp | egrep -v spool | egrep -v SQL | egrep -v [2-4] > $ORACLE_BASE/begin.sh
@/app/oracle/begin.sh
!sh $ORACLE_BASE/status.sh
exit
EOF1

3. copy_backup.sh

- data file 과 control file 을 날짜 이름으로 디렉토리를 만들어서 복사

#!/bin/bash

export LANG=C
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=/app/oracle/product/11g
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=testdb

sqlplus /nolog <<EOF3
conn / as sysdba

set head off
set time off
set timing off
set feedback off
set echo off
set line 200
col name for a100
spool /app/oracle/cp.tmp

select 'mkdir /data/backup/open' || to_char(sysdate, 'YYYY-MM-DD-HH24-MI-SS') from dual;
select 'cp -av '||name||' /data/backup/open/'||to_char(sysdate, 'YYYY-MM-DD-HH24-MI-SS')
"name" from v\$datafile;
spool off

spool /app/oracle/control.tmp
alter session set nls_date_format='YYYY-MM-DD-HH24-MI-SS';
select 'alter database backup controlfile to '' /data/backup/open/'||sysdate||\
'/'||sysdate||'.ctl''; ' form dual;
spool off;

!cat /app/oracle/cp.tmp | egrep -v SQL > /app/oracle/cp.sh
!cat /app/oracle/control.tmp | egrep -v SQL > /app/oracle/control.sql
!sh /app/oracle/cp.sh
@/app/oracle/control.sql
exit
EOF3

4. end_backup.sh

- 위의 복사가 끝나면 end backup 상태로 변경

#!/bin/bash

export LANG=C
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=/app/oracle/product/11g
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=testdb

sqlplus /nolog <<EOF4
conn / as sysdba

set head off
set feedbackup off
set time off
set timing off
set echo off
spool /tmp/online.tmp

select 'alter tablespace '|| tablespace_name ||' end backup;' \
from dba_tablespaces \
where status='ONLINE' \
and contents != 'TEMPORARY';
spool off

!cat /tmp/online.tmp | egrep -v spool | egrep -v SQL | egrep -v [2-4] > /app/oracle/end.sh
@/app/oracle/end.sh
!sh /app/oracle/status.sh
exit
EOF4

5. status.sh

- begin backup 모드 상태를 확인하는 script

#!/bin/bash

export LANG=C
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=/app/oracle/product/11g
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=testdb

sqlplus /nolog <<EOF2
conn / as sysdba

set head on
set echo off
set feedback off
spool /tmp/status.tmp
set line 200
col name for a50
col status for a15

select a.file#, a.name, b.status, to_char(b.time, 'YYYY-MM-DD:HH24:MI:SS') "Time" \
from v\$datafile a, v\$backup b \
where a.file#=b.file#;
spool off
exit
EOF2

▼ 해당 script 들에 실행 권한을 주고 (chmod 755 *.sh) main_backup.sh 을 실행해 본 결과이다.

[oracle@chan ~]$ cd /app/oracle
[oracle@chan oracle]$ ls -l *.sh
-rw-r--r-- 1 oracle oinstall 602 Jul 15 19:49 begin_backup.sh
-rw-r--r-- 1 oracle oinstall 945 Jul 15 19:57 copy_backup.sh
-rw-r--r-- 1 oracle oinstall 595 Jul 15 20:00 end_backup.sh
-rw-r--r-- 1 oracle oinstall 619 Jul 15 19:44 main_backup.sh
-rw-r--r-- 1 oracle oinstall 476 Jul 15 20:03 status.sh

[oracle@chan oracle]$
[oracle@chan oracle]$ chmod 755 *.sh
[oracle@chan oracle]$
[oracle@chan oracle]$ ./main_backup.sh

==== set begin backup mode ====

real    0m0.157s
user    0m0.017s
sys 0m0.016s

==== end backup mode ====

==== start file copy ====
real    0m12.046s
user    0m0.012s
sys 0m0.007s
==== end file copy ====

==== set end backup mode ====
real    0m0.249s
user    0m0.009s
sys 0m0.023s
==== complete hot backup ====

저작자표시 비영리 동일조건

'Oracle > Admin' 카테고리의 다른 글

[Oracle Admin] Chap 15. Parameter File 장애 복구 (0)	2013.09.26
[Oracle Admin] Chap 14. Recovery 원리 (0)	2013.09.23
[Oracle Admin] Chap 12. Archive/No Archive log mode (0)	2013.09.22
[Oracle Admin] Chap 11. DBMS_JOB & DBMS_SCHEDULER (0)	2013.09.18
[Oracle Admin] Chap 10. Oracle 사용자 관리 (0)	2013.09.17

by 짱구를꼭말려 2013. 9. 22. 23:49

[Oracle Admin] Chap 12. Archive/No Archive log mode

Oracle/Admin

Chap 12. Archive/No Archive log mode

1. 데이터 베이스 복구 원리 설명 (Archive/No Archive log mode)

데이터의 복구 원리를 설명하기 전에 우선, 데이터가 저장되는 원리를 먼저 보자. 사용자가 A라는 데이터를 테이블에 입력한다고 하면,

- Redo log buffer 에 A 에 먼저 들어가고,

- 사용자가 commit 을 하면 Redo log buffer 에 A 가 Redo log file 에 SCN 과 함께 저장이 되고 (SCN 을 1이라고 가정한다. ) Redo log buffer 에서는 A 가 지워질 것이다.

- 이 때, log switch 같은 것이 발생해서 checkpoint 가 일어나면 DB buffer cache 에 있던 A 가 Data file 로 내려써 지게 되면서 CKPT (check point) 프로세스가 Data file 헤더와 Control file 헤더에 각각 동일한 SCN (여기서는 1) 을 기록하게 된다.

- 이 상태에서 DB backup 을 받고 (Data 파일에 SCN 이 1인 상태), 그 이후로 데이터 B, C 가 들어와서 SCN 이 2~3 으로 증가 후 Data/control file의 헤더에 이제 SCN 이 3인 상태가 되었다고 가정하자.

- 여기서 새로운 데이터 D와 E 가 들어와서 commit 이 발생, 그리고 LGWR 이 해당 데이터들을 Redo log file 내려써야 하는데, 빈 공간이 없다면 기존의 A, B 가 들어가 있는 공간 (inactive redo log file) 에 덮어 쓸 것이다. 그러면서 또 다시 checkpoint 가 일어나서 data/control header 에도 SCN 이 4->5 로 증가했다고 가정하자.

=====================================================================================================

만약, 이 상황에서 예기치 않게 DB 서버에 장애가 생겨서 데이터 파일이 날아갔다고 하면, 기존에 backup 해 둔 데이터를 복사해 와서 (Restore) DB 를 Recover 해야한다.

하지만 위의 상황에서 backup data 에는 A 가 입력 되었을 때의 데이터, 즉, Data file 의 SCN 이 1 일때의 상태이고, 현재 Control file 의 SCN 이 5 번이다.

SCN 이 서로 다르므로 Recover 를 할 때는 2~5 번까지의 작업을 Redo log file 이나 Archive log file 을 뒤져서 Recover 를 해야하는데 (2~5 번 순서대로 하고, 만약 2번이 없으면 더 이상 진행되지 않는다.) Redo log 에서 SCN 2번이 저장되어 있는 부분을 (즉, 데이터 B 를 입력했던 부분) 데이터 D 가 덮어써 버렸으니, SCN 2번 부분을 복구 할 수 없게 된다.

이런 경우는 no archive log mode 라고 하고 Oracle 을 처음 설치하면 기본 모드이다.

이런 문제를 막기 위해서 Redo log file 에 덮어쓰기 전에 다른 곳으로 복사해 두고 덮어쓰는 방법을 쓸 수 있는데 이런 방법을 archive log mode 라고 한다.

2, Archive redo log mode로 변경하는 테스트

- pfile 을 사용한다고 전제하에, 처음 오라클 설치 후 archive mode 로 변경하는 순서는 아래와 같다.

DB 종료 -> Parameter file (pfile) 변경 -> DB Mount 로 시작 -> mode 변경 -> DB open

-- 현재 log mode 조회
SQL> archive log list;
Database log mode          No Archive Mode
Automatic archival         Disabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     32
Current log sequence           34

-- DB 종료
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

-- Parameter file 변경
SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora

-- 아래 내용 추가

log_archive_dest_1='location=/data/arc1'
log_archive_dest_2='location=/data/arc2'
log_archive_format=%s_%t_%r.arc

-- 해당 폴더 생성
SQL> !mkdir /data/arc1 /data/arc2

-- mount 상태로 startup
SQL> startup mount
ORACLE instance started.

-- archive log mode 경로 변경 내용 확인
SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /data/arc2

Oldest online log sequence 32

Current log sequence 34

-- archive mode 로 변경하기

SQL> alter database archivelog;

Database altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /data/arc2

Oldest online log sequence 32

Next log sequence to archive 34

Current log sequence 34

-- DB open

SQL> alter database open;

Database altered.

3. Archive Hang 발생 시 해결 방법

- archive log 가 저장되는 폴더에 권한을 oracle 에서 root 로 변경해서 archive hang 을 만든 후 복구해 보자.

-- 현재 archive 확인

SYS> archive log list;
Database log mode          Archive Mode
Automatic archival         Enabled
Archive destination        /data/arc2
Oldest online log sequence     101
Next log sequence to archive   103
Current log sequence           103

-- /data/arc1 과 /data/arc2 의 권한을 root 로 변경해 보자
[oracle@chacha ~]$ su -
[root@chacha ~]# chown -R root:root /data/arc1 /data/arc2
[root@chacha ~]# ls -l /data/arc*
/data/arc1:
total 76
-rw-r----- 1 root root 72192 Jul 10 14:34 104_1_819630962.arc

/data/arc2:
total 76
-rw-r----- 1 root root 72192 Jul 10 14:34 104_1_819630962.arc

-- Hang 을 유도하게 log switch

SYS> alter system switch logfile;

-- 더 이상 프롬프트가 떨어지지 않고 아무 메세지도 없고, 멈춘 상태로 될 것이다.
-- alert log 를 확인

[oracle@chacha ~]$ vi /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log

-- 아래 부분에 보면, 아래와 같은 메세지가 있다. (ORA-19504: failed to create file "")
-- 디렉토리가 root 로 되어 있었어서 archive log 파일을 만들지 못했다.

ARC3: Error 19504 Creating archive log file to '/data/arc2/105_1_819630962.arc'
ARC3: Error 19504 Creating archive log file to '/data/arc1/105_1_819630962.arc'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance testdb - Archival Error
ORA-16038: log 1 sequence# 105 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 1 thread 1: '/app/oracle/disk4/redo01_a.log'
ORA-00312: online log 1 thread 1: '/app/oracle/disk5/redo01_b.log'

-- 이제 /data/arc1 과 /data/arc2 의 권한을 다시 oracle 로 변경
[root@chacha ~]# chown -R oracle:oinstall /data/arc1 /data/arc2
[root@chacha ~]# ls -l /data/arc*
/data/arc1:
total 76
-rw-r----- 1 oracle oinstall 72192 Jul 10 14:34 104_1_819630962.arc

/data/arc2:
total 76
-rw-r----- 1 oracle oinstall 72192 Jul 10 14:34 104_1_819630962.arc

-- 파라미터 변경 후 archive 재시작
SYS> alter system set log_archive_dest_state_1=defer;
System altered.

SYS> alter system set log_archive_dest_state_1=enable;
System altered.

SYS> alter system set log_archive_dest_state_2=defer;
System altered.

SYS> alter system set log_archive_dest_state_2=enable;
System altered.

SYS> alter system archive log stop;
System altered.

SYS> alter system archive log start;
System altered.

SYS> alter system switch logfile;
System altered.

저작자표시 비영리 동일조건

'Oracle > Admin' 카테고리의 다른 글

[Oracle Admin] Chap 14. Recovery 원리 (0)	2013.09.23
[Oracle Admin] Chap 13. Oracle Backup (0)	2013.09.22
[Oracle Admin] Chap 11. DBMS_JOB & DBMS_SCHEDULER (0)	2013.09.18
[Oracle Admin] Chap 10. Oracle 사용자 관리 (0)	2013.09.17
[Oracle Admin] Chap 9. Oracle 메모리 관리 기법들 (0)	2013.09.17

by 짱구를꼭말려 2013. 9. 22. 23:49

[Oracle Admin] Chap 11. DBMS_JOB & DBMS_SCHEDULER

Oracle/Admin

Chap 11. DBMS_JOB & DBMS_SCHEDULER

이 두가지는 반복되는 작업을 수행해야 할 경우 아주 유용하다.

1. DBMS_JOB

1) dbms_job 패키지

- 이 패키지를 설정하게 되면 SNP 백그라운드 프로세스가 이 패키지에 설정되어 있는 작업을 수행하게 된다. SNP 백그라운드 프로세스는 선택적인 프로세스로 초기화 파라미터 파일에 아래의 항목을 설정 한 후 재 시작을 하면 실행된다.

* job_queue_process = 1 : SNP 프로세스의 개수를 지정

* job_queue_interval = 60 : SNP 프로세스가 sleep 상태에서 꺠어나는 간격을 초로 지정.

- dbms_job 패키지는 아래와 같은 프로시저들을 가지고 있다.

* submit : 새로운 작업을 job queue 목록에 등록한다.

* remove : job queue 에 등록된 job 을 제거한다.

* change : job queue 에 등록된 job 을 변경한다.

* next_date : job queue 에 등록된 job 의 작동 시간을 변경한다.

* interval : job queue 에 등록된 job 의 수행 주기를 변경한다.

* what : 수행할 procedure or package 를 변경한다.

* run : 등록되어 있는 job 을 수동으로 수행한다.

2) job 관리하기

- 기본 문법

dbms_job.submit(

job out binary_integer, ### job number

what in varchar2, ### 수행할 pl/sql, procedure, package 이름 이나 직접 SQL 문장을 써도 된다.

next_date in date default sysdate, ### 다음에 수행된 시간 지정

interval in varchar2 default 'null', ### 수행되는 주기를 지정하며 초 단위까지 지정가능

no_parse in boolean default false ### parse 여부를 지정한다.

)

- 새로운 job 등록 테스트 하기

## STEP1 - table, sequence, procedure 생성

SCOTT> create sequence seq_job_seq1;

Sequence created.

SCOTT> create table job_test01 (no number, name varchar2(5) );

Table created.

SCOTT> create or replace procedure insert_job_test01 is

2 begin

3 insert into scott.job_test01 values(seq_job_seq1.nextval, dbms_random.string('a',3));

4 end;

5 /

Procedure created.

## STEP2 - job 을 등록할 procedure 생성

SCOTT> !vi job1.sql

begin

dbms_job.submit(:jno,

'scott.insert_job_test01;',

sysdate,

'sysdate + 1/24/60',

false);

end;

:wq!

==> 여기서 :jno 의 의미는 job number 를 oracle 이 알아서 할당하게 하라는 의미이다.

## STEP3 - job 을 등록

SCOTT> variable jno number; ==> jno 에 number 할당

SCOTT> @job1.sql

PL/SQL procedure successfully completed.

SCOTT> print jno;

JNO

----------

SCOTT> commit; ==> 이 시간 부터 job 이 실행된다. commit 을 안하면 수행 안 됨.

Commit complete.

## STEP4 - 수행되고 있는 job 내역 확인

SCOTT> select what, job, next_date, next_sec, failures, broken from user_jobs where what='scott.insert_job_test01;';

WHAT JOB NEXT_DATE NEXT_SEC FAILURES B

-------------------------------------------------- ---------- ------------ ---------------- ---------- -

scott.insert_job_test01; 5 10-JUL-13 12:21:49 N

SCOTT> select * from job_test01 order by no;

NO NAME

---------- -----

1 PbD

2 TWN

3 mqi

4 Izh

5 yer

6 Kji

6 rows selected.

- 등록되어 있는 job 삭제하기

SCOTT> print jno;

JNO

----------

SCOTT>

SCOTT> exec dbms_job.remove(5);

PL/SQL procedure successfully completed.

SCOTT> select what, job, next_date, next_sec, failures, broken from user_jobs where job=5;

no rows selected

- 등록되어 있는 job 수정하기

## test 위해 다시 job 등록

SCOTT> variable jno number;

SCOTT> @job1.sql

PL/SQL procedure successfully completed.

SCOTT> print jno;

JNO

----------

SCOTT> commit;

Commit complete.

SCOTT> select what, job, next_date, next_sec, interval from user_jobs where job=6;

WHAT JOB NEXT_DATE NEXT_SEC INTERVAL

-------------------------------------------------- ---------- ------------ ---------------- --------------------------------------------------

scott.insert_job_test01; 6 10-JUL-13 12:30:10 sysdate + 1/24/60

## 수행 시간을 1분마다 -> 5분 마다로 수행으로 변경

SCOTT> exec dbms_job.change(:jno, 'scott.insert_job_test01;', sysdate, 'sysdate + 5/24/60');

PL/SQL procedure successfully completed.

SCOTT> select what, job, next_date, next_sec, interval from user_jobs where job=6;

WHAT JOB NEXT_DATE NEXT_SEC INTERVAL

-------------------------------------------------- ---------- ------------ ---------------- ------------------------------

scott.insert_job_test01; 6 10-JUL-13 12:32:36 sysdate + 5/24/60

2. DBMS_SCHEDULER

1) 주요 특징 및 구성

- dbms_job 이 할 수 없는 일들을 하기 위해 생성.

* oracle 에서 생성한 procedure 나 function 이외에 OS 에서 생성된 각종 유틸이나 프로그램도 실행 가능

* 데이터 베이스에서 내부 이벤트까지 추적가능하기 때문에 OS 나 dbms_job 보다 훨씬 다양하게 작업을 체크/수행가능

2) DBMS_SCHEDULER 사용하기

- 신규 job 생성

일반적으로 dbms_scheduler.create_job 이나 dbms_scheduler.create_program 을 이용할 수 있으나 전자를 더 많이 쓴다.

## job_test1 이라는 table 에 3초에 한번씩 데이터를 insert 해보자. 이 작업을 하기 위해서는 해당 계정이 create any job 의 권한을 가지고 있어야 한다.

SYS> grant create any job to scott;

Grant succeeded.

SYS> conn scott/tiger

Connected.

## test 할 table, sequence, procedure 생성

SCOTT> create table job_test1 (no number, name varchar2(5), rdate date default sysdate);

Table created.

SCOTT> create sequence seq_job_test1_no;

Sequence created.

SCOTT> create or replace procedure insert_job_test

2 is

3 begin

4 insert into scott.job_test1 (no, name) values (seq_job_test1_no.nextval, dbms_random.string('a',2));

5 commit;

6 end;

7 /

Procedure created.

## 신규 job 생성

SCOTT> begin

2 dbms_scheduler.create_job(

3 job_name => 'insert_job_test1',

4 job_type => 'PLSQL_BLOCK',

5 job_action => 'BEGIN insert_job_test; END;',

6 start_date => systimestamp,

7 repeat_interval => 'freq=secondly; interval=3');

8 end;

9 /

PL/SQL procedure successfully completed.

======================================================================================

각 라인 설명 :

2 > 신규 job 을 생성하라는 의미

3 > dbms_scheduler 내에서 사용 될 job 의 이름. 중복되면 안됨.

4 > 5번 줄에 적히는 항목에 따라 값이 달라진다. 이 곳은 5번줄에 적힌 프로그램 타입을 적는 곳이다. 현재는 5번줄에 begin~end 형태의 pl/sql 형태로 적었기 때문에 PLSQL_BLOCK 가 된 것이고, 만약 프로시저 이름이라면, STORED_PROCEDURE 라고 적어야 한다. 또, 실행 프로그램이라면 EXECUTABLE 라고 적어주면 된다.

5 > 실제 실행 될 프로그램을 적는 부분. 이 곳에서는 실제 pl/sql 블록을 적을 수도 있고, 프로시저 이름을 적을 수도 있고, os에 있는 실행파일 이름을 지정할 수도 있으며, program_name 으로 미리 생성해 둔 프로그램 이름을 지정할 수도 있고, chained 값으로 생성된 체인을 활용할 수도 있다.

6 > 해당 job 이 처음 시작될 시간을 지정하는 곳이다. systimestamp 는 지금 즉시 시작하라는 의미이며, 특정 시간에 시작하도록 지정하고 싶으면, start_date => to_timestamp_tz('2013-07-01 02:00 ROK', 'YYYY-DD-DD HH24:MI:SS TZR) 이런 식으로 설정하면 된다. 또한 종료 시간을 설정하고 싶으면 end_date => systimestamp + interval '30' day 이런식으로 줄 수 있다.

7 > 반복할 주기를 지정하는 곳이다. 반복 주기를 지정하는 방법은 아래와 같이 2가지가 있다.

* 얼마마다 한번씩 수행한다는 형식 (CALENDARING Expression)

repeat_interval => 'freq=hourly; interval=1' ----- 1시간 간격으로 수행하도록 설정

repeat_interval => 'freq=minutely; interval=30' ----- 30분 간격으로 수행하도록 설정

repeat_interval => 'freq=secondly; interval=5' ----- 5초 간격으로 수행하도록 설정

repeat_interval => 'freq=weekly; interval=2' ----- 2주 간격으로 수행하도록 설정

repeat_interval => 'freq=monthly' ----- 매달 수행하도록 설정

위의 방법에 by 를 사용해서 보다 구체적으로 시간을 지정할 수 있다. 예를 들어, 매주 일요일 밤 20 시 30분에 수해하고 싶다면,

repeat_interval => 'freq=weekly; byday=sun; byhour=20; byminute=30;'

또 다른 옵션으로는 다른 일정을 현재 일정에 포함할 수 있는 include 가 있다.

BEGIN

dbms_scheduler.create_schedule (

schedule_name => 'sub_sched_1',

repeat_interval => 'freq=yearly; bydate=0101,0201,0301');

dbms_scheduler.create_schedule (

schedule_name => 'main_sched',

repeat_interval => 'freq=monthly;interval=2; bymonthday=15; byhour=9,17; include=sub_sched_1');

END;

* 다음 직접 작업 시간까지의 간격을 지정하기

아래와 같이 설정 할 수 있다.

repeat_interval => 'SYSDATE + 1' ----- 하루 뒤에 수행

repeat_interval => 'SYSDATE + 30/1440' --- 1440 은 하루를 분으로 환산한 것이니 30분 마다 수행하라는 의미

======================================================================================

SCOTT> exec dbms_scheduler.enable('insert_job_test1');

PL/SQL procedure successfully completed.

SCOTT> exec dbms_scheduler.run_job('insert_job_test1');

PL/SQL procedure successfully completed.

SCOTT> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS';

Session altered.

SCOTT> select * from job_test1 order by 3;

NO NAME RDATE

---------- ----- -------------------

1 Eo 2013-07-10:12:50:14

2 OY 2013-07-10:12:50:17

3 XM 2013-07-10:12:50:20

4 Hu 2013-07-10:12:50:23

5 il 2013-07-10:12:50:26

6 Da 2013-07-10:12:50:29

7 xl 2013-07-10:12:50:29

8 AR 2013-07-10:12:50:32

9 kx 2013-07-10:12:50:35

10 Xc 2013-07-10:12:50:38

11 Qv 2013-07-10:12:50:41

12 qF 2013-07-10:12:50:44

13 EG 2013-07-10:12:50:47

14 lN 2013-07-10:12:50:50

15 jA 2013-07-10:12:50:53

16 zB 2013-07-10:12:50:56

17 he 2013-07-10:12:50:59

18 BM 2013-07-10:12:51:02

19 YF 2013-07-10:12:51:05

19 rows selected.

- 현재 작동중인 job 확인하기

SYS> select p.job_name, p.job_type, o.object_id, p.enabled, o.last_ddl_time, o.created, cast(p.next_run_date as date) next_run_date, p.state, p.job_class, o.owner, schedule_type

2 from dba_objects o, dba_scheduler_jobs p

3 where o.owner=p.owner and o.object_type='JOB' and o.owner='SCOTT';

JOB_NAME JOB_TYPE OBJECT_ID ENABL LAST_DDL_TIM CREATED NEXT_RUN_DAT STATE JOB_CLASS OWNER SCHEDULE_TYP

------------------------------ ---------------- ---------- ----- ------------ ------------ ------------ --------------- ------------------------------ ------------------------------ ------------

INSERT_JOB_TEST1 PLSQL_BLOCK 75689 TRUE 15-JUL-13 15-JUL-13 15-JUL-13 SCHEDULED DEFAULT_JOB_CLASS SCOTT CALENDAR

- 특정 job 을 일시 중지 또는 재 시작 하기

SCOTT> exec dbms_scheduler.disable ('insert_job_test1');

PL/SQL procedure successfully completed.

SCOTT> exec dbms_scheduler.enable ('insert_job_test1');

PL/SQL procedure successfully completed.

- 특정 job 을 삭제하기

SCOTT> begin

2 dbms_scheduler.drop_job('insert_job_test1');

3 end;

4 /

PL/SQL procedure successfully completed.

- Job 의 속성 변경하기

속성변경 테스트를 위해서 job 을 하나 더 생성해서 테스트 해보겠다.

## 새 작업 만들기

SCOTT> begin

2 dbms_scheduler.create_job (

3 job_name => 'insert_job_test2',

4 job_type => 'PLSQL_BLOCK',

5 job_action => 'BEGIN insert_job_test; END;',

6 start_date => systimestamp,

7 repeat_interval => 'freq=secondly; interval=5' );

8 end;

9 /

PL/SQL procedure successfully completed.

==> 7 라인에 보면 5초마다 한번씩 수행되는 것으로 생성이 되어 있다. 이 부분을 10초에 한번씩으로 바꾸어 보겠다.

SCOTT> exec dbms_scheduler.set_attribute(name => 'insert_job_test2', attribute => 'repeat_interval', value => 'freq=secondly;interval=10' );

PL/SQL procedure successfully completed.

### sys 계정으로 바꼈는지 조회

SYS> select owner, job_name, start_date, repeat_interval from dba_scheduler_jobs where owner='SCOTT';

OWNER JOB_NAME START_DATE REPEAT_INTERVAL

-------------------- ------------------------------ --------------------------------------------------------------------------- ----------------------------------------

SCOTT INSERT_JOB_TEST2 15-JUL-13 01.55.33.357796 PM +09:00 freq=secondly;interval=10

3) DBMS_SCHEDULER 관리하기

- 생성된 job을 즉시 실행하기

SCOTT > exec dbms_scheduler.run_job('insert_job_test1');

- job 을 중단하기

SCOTT > exec dbms_scheduler.stop_job(job_name => 'insert_job_test1', force => true);

- job 삭제하기

SCOTT > exec dbms_scheduler.drop_job(job_name => 'insert_job_test1', force => true);

저작자표시 비영리 동일조건

'Oracle > Admin' 카테고리의 다른 글

[Oracle Admin] Chap 13. Oracle Backup (0)	2013.09.22
[Oracle Admin] Chap 12. Archive/No Archive log mode (0)	2013.09.22
[Oracle Admin] Chap 10. Oracle 사용자 관리 (0)	2013.09.17
[Oracle Admin] Chap 9. Oracle 메모리 관리 기법들 (0)	2013.09.17
[Oracle Admin] Chap 8. Oracle 저장구조 (0)	2013.09.14

by 짱구를꼭말려 2013. 9. 18. 01:14

[Oracle Admin] Chap 10. Oracle 사용자 관리

Oracle/Admin

Chap 10. Oracle 사용자 관리

1. Schema 와 User

- User: 말 그대로 사용자 (ex, scott 같은)

- Schema: 특정 사용자(user) 가 만들어 놓은 모든 Object 의 집합 (table, index, vie constraint,m trigger, dblink, synonym, sequence 등등 모든 것)

1) User 생성하기

- User 생성 순서

* 생성할 사용자의 Default tablespace 결정 후 해당 tablespace 생성

* 생성할 사용자가 사용하는 temporary tablespace 생성

* 사용자 생성

* 적절한 profile 과 privilege (권한), role 등을 생성 후 할당.

- User 생성하기

########## 조건

User 명은 webuser, 패스워드는 webpwd, tablespace 는 TS_WEBHARD 라고 생성해서 모든 table은 여기에 저장되도록 하고, index 는 TS_WEB_IDX 라는 tablespace 라는 곳에다가 저장하기로 한다.

그리고 temporary tablespace 는 temp_web 이라고 만들기로 한다.

##########

## Default tablespace 생성

- 현재 tablespace 및 temp tablespace 조회

SYS> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE MB FILE_NAME

---------- ---------- ---------------------------------------------

USERS 5 /app/oracle/oradata/testdb/users01.dbf

UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf

SYSAUX 510 /app/oracle/oradata/testdb/sysaux01.dbf

SYSTEM 720 /app/oracle/oradata/testdb/system01.dbf

EXAMPLE 313.125 /app/oracle/oradata/testdb/example01.dbf

SYS> select tablespace_name, bytes/1024/1024 MB , file_name from dba_temp_files;

TABLESPACE_NAME MB FILE_NAME

------------------------------ ---------- ---------------------------------------------

TEMP 29 /app/oracle/oradata/testdb/temp01.dbf

- 새로운 tablespace 생성

SYS> create tablespace ts_webhard datafile '/app/oracle/oradata/testdb/ts_webhard.dbf' size 100M;

Tablespace created.

SYS> create tablespace ts_web_idx datafile '/app/oracle/oradata/testdb/ts_web_idx.dbf' size 10M;

Tablespace created.

- 다시 조회

SYS> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE MB FILE_NAME

---------- ---------- ---------------------------------------------

USERS 5 /app/oracle/oradata/testdb/users01.dbf

UNDOTBS1 90 /app/oracle/oradata/testdb/undotbs01.dbf

SYSAUX 510 /app/oracle/oradata/testdb/sysaux01.dbf

SYSTEM 720 /app/oracle/oradata/testdb/system01.dbf

EXAMPLE 313.125 /app/oracle/oradata/testdb/example01.dbf

TS_WEBHARD 100 /app/oracle/oradata/testdb/ts_webhard.dbf

TS_WEB_IDX 10 /app/oracle/oradata/testdb/ts_web_idx.dbf

## temporary tablespace 생성 및 조회

SYS> create temporary tablespace temp_web tempfile '/app/oracle/oradata/testdb/temp_web.dbf' size 10M;

Tablespace created.

SYS> select tablespace_name, bytes/1024/1024 MB, file_name from dba_temp_files;

TABLESPACE_NAME MB FILE_NAME

------------------------------ ---------- ---------------------------------------------

TEMP 29 /app/oracle/oradata/testdb/temp01.dbf

TEMP_WEB 10 /app/oracle/oradata/testdb/temp_web.dbf

## 사용자 생성

SYS> create user webuser identified by webpwd default tablespace ts_webhard temporary tablespace temp_web quota unlimited on ts_webhard quota 0m on system;

User created.

===> identified by webpwd : 암호를 지정

===> default tablespace ts_webhard : default tablespace 지정

===> temporary tablespace temp_web : temporary tablespace 지정

===> quota unlimited on ts_webhard : ts_webhard tablespace 에 할당량을 무제한으로 설정

===> quota 0m on system : system tablespace 를 사용하지 못하게 설정

## 권한 설정

SYS> grant resource, connect to webuser;

Grant succeeded.

## 접속 확인

SYS> conn webuser/webpwd

Connected.

WEBUSER>

2) 사용자 정보 확인 및 수정하기

## 사용자 정보 확인

SYS> select username, default_tablespace, temporary_tablespace from dba_users where username='WEBUSER';

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

------------------------------ ------------------------------ ------------------------------

WEBUSER TS_WEBHARD TEMP_WEB

## 사용자 정보 수정 (password 변경하기)

SYS> alter user webuser identified by webpwd2;

User altered.

3) Profile 관리하기

- password profile 관련 parameter

Parameter	의미
failed_login_attempts	login 시도를 여기서 설정한 횟수만큼 실패할 경우 계정 잠금. 즉, 이 값을 5로 설정했는데 5번 틀렸을 경우 6번째부터는 로그인이 안된다.
password_lock_time	계정이 잠기면 몇일 동안 잠글 것인지를 설정할 수 있다. 단위는 일 수이다. DBA가 이 값과 상관없이 unlock 가능하다.
password_life_time	동일한 암호를 몇 일동안 사용할 수 있는지 설정한다 (단위는 일 수)
password_grace_time	password_life_time 이 만료 되어도 이 parameter 에서 지정된 값 만큼 더 암호를 변경할 기간을 허용한다.
password_reuse_time	동일한 암호를 다시 사용할 수 없도록 설정하는 기간. 즉, 암호를 변경하라고 했는데 사용자가 동일한 암호를 다시 사용하려고 할 수 있기에 같은 암호를 다시 쓸 수 없게 만들어야 할 때 사용하는 파라미터.
password_reuse_max	password_reuse_time 설정을 피해서 동일한 암호를 재사용을 할 경우 최대 사용 가능한 횟수를 지정하는 파라미터이다.
password_verify_function	사용자가 입력한 암호가 정말 암호로 적합한지를 점검한다.

- profile 생성하기 실습

### 로그인 시도 5회 실패 시 계정을 5 일 동안 사용 못하게 할 것.

### 계정의 암호는 10일에 한번씩 변경하게 할 것

### 동일한 암호는 10일 동안 사용 못하게 할 것

SYS> create profile sample_prof limit failed_login_attempts 5 password_lock_time 5 password_life_time 10 password_reuse_time 10;

Profile created.

- resource profile 관련 parameter

* 이 parameter 를 사용하려면 resource_limit = true 라는 설정이 되어 있어야 한다.

* startup 할 때 사용되는 parameter file 위에 위 문장을 적어 놓거나 9i 버전 이상에 alter system set resource_limit=true; 를 실행하면 바로 적용이 된다.

* 아래는 resource profile 관련 설정들이다.

* CPU_PSER_SESSION : SESSION 이란 1명의 접속을 의미한다. 하나의 SESSION이 CPU 를 연속적으로 사용할 수 있는 최대 시간을 설정한다. 1/100초 단위이다.

* SESSIONS_PER_USER : 하나의 사용자 계정으로 몇 명의 사용자가 동시에 접속 할 수 있는지 설정하는 파라미터이다.

* CONNECT_TIME : 하루 동안 DB server 에 접속 할 수 있는 총 시간을 설정한다.

* IDLE_TIME : 연속적으로 휴면 시간이 여기 값을 넘으면 접속을 해제한다. 이렇게 하는 이유는 보안적인 문제도 있고, 메모리 사용량 때문이기도 하다. 예를 들어, 사용자가 서버에 접속을 하면 server process 가 생성되고 PGA 가 할당이 된다. PGA 를 1M 로 줄 경우 1000 명의 사용자가 서버에 접속해서 아무 일도 안해도 PGA 가 1000M 를 사용하게 되는 것이다. 이런 일을 막기 위해 설정한다.

* LOGICAL_READS_PER_SESSION : 한 session 에서 사용 가능한 최대 block 수를 설정한다.

* PRIVATE_SGA : MTS / shared server 일 경우 해당 session 의 SGA 사용량을 bytes 단위로 설정한다.

* CPU_PER_CALL : 하나의 call 당 cpu 를 점유할 수 있는 시간이며 1/100초 단위이다.

* LOGICAL_READS_PER_CALL : 하나의 call 당 읽을 수 있는 block 의 개수를 의미.

- resource 관련 profile 만들고 사용자에게 할당하기

## 현재 resource_limit 설정 값 확인

SYS> show parameter resource_limit;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

resource_limit boolean FALSE

## resource_limit 값 true 로 변경

SYS> alter system set resource_limit=true;

System altered.

SYS> show parameter resource_limit;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

resource_limit boolean TRUE

## 아래 조건으로 resource profile 을 생성

## - 1명당 연속적으로 cpu 를 사용할 수 있는 시간을 10초로 제한할 것

## - 하루 중 8시간만 DB 에 접속 가능하게 할 것

## - 10분 동안 사용하지 않으면 강제로 접속을 끊을 것

SYS> create profile re_sample_prof limit

2 cpu_per_session 1000

3 connect_time 480

4 idle_time 10;

Profile created.

## webuser 사용자의 현재 profile 조회

SYS> select username, profile from dba_users where username='WEBUSER';

USERNAME PROFILE

------------------------------ ------------------------------

WEBUSER DEFAULT

## 처음 생성했던 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 5

SAMPLE_PROF PASSWORD_LIFE_TIME PASSWORD 10

PROFILE RESOURCE_NAME RESOURCE LIMIT

------------- ------------------------------ -------- ----------

SAMPLE_PROF PASSWORD_REUSE_TIME PASSWORD 10

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

PROFILE RESOURCE_NAME RESOURCE LIMIT

--------------- ------------------------------ -------- ----------

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.

## webuser 사용자에게 profile 적용시키고 확인하기

SYS> alter user webuser profile sample_prof;

User altered.

SYS> alter user webuser profile re_sample_prof;

User altered.

SYS> select username, profile from dba_users where username='WEBUSER';

USERNAME PROFILE

------------------------------ ---------------

WEBUSER RE_SAMPLE_PROF

- 사용 안하는 profile 삭제하기

## profile 은 현재 사용자에게 할당되어 있는 profile 은 기본적으로 삭제가 안되고 cascade 옵션으로 삭제하면 가능하다. 이렇게 삭제하면 해당 사용자는 default profile 을 사용하게 된다.

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

SYS> drop profile re_sample_prof cascade;

Profile dropped.

SYS> select username, profile from dba_users where username='WEBUSER';

USERNAME PROFILE

------------------------------ ---------------

WEBUSER DEFAULT

4) privilege (권한) 관리하기

- profile 이 사용자에게 어떤 것들을 못하게 제한하는 것이라면, privilege 는 사용자에게 어떤 것들을 하게 허락해 주는 것이 목적이다. privilege 는 크게 system 과 object 관련 privilege 가 있다.

- System privilege

대 분 류	privilege	설 명
index	create any index	소유자에 상관없이 모든 테이블에 index 를 생성할 수 있는 권한
	drop any index	소유자에 상관없이 모든 테이블에 index 를 삭제할 수 있는 권한
	alter any index	소유자에 상관없이 모든 테이블에 index 를 수정할 수 있는 권한
table	create table	본인 계정에 테이블을 생성할 수 있는 권한
	create any table	소유자에 상관없이 다른 user 이름으로 테이블을 생성할 수 있는 권한
	alter any table	소유자에 상관없이 모든 테이블의 구조를 수정할 수 있는 권한
	drop any table	소유자에 상관없이 모든 사용자의 테이블을 삭제할 수 있는 권한
	update any table	소유자에 상관없이 모든 사용자의 테이블을 업데이트 할 수 있는 권한
	delete any table	소유자에 상관없이 모든 사용자의 테이블의 데이터를 삭제할 수 있는 권한
	insert any table	소유자에 상관없이 모든 사용자의 테이블에 데이터를 insert 할 수 있는 권한
session	create session	서버에 접속할 수 있는 권한
	alter session	접속 상태에서 환경값을 변경할 수 있는 권한
	restricted session	Restricted 모드로 open 된 DB 에 접속 할 수 있는 권한
tablespace	create tablespace	tablespace 를 만들 수 있는 권한
	alter tablespace	tablepsace 를 수정 할 수 있는 권한
	drop tablespace	tablespace 를 삭제할 수 있는 권한
	unlimited tablespace	tablespace 사용용량을 무제한으로 허용 하는 권한, 즉, quota 옵션 적용을 받지 않게 됨.

- Object 관련 privilege 권한 할당 / 해제하기

Object 관련 privilege 는 주로 DML (select, insert, update, delete) 등을 할 수 있는 권한을 말한다.

## webuser 사용자에게 scott 의 emp 테이블을 조회할 수 있도록 권한 설정

SYS> grant select on scott.emp to webuser;

Grant succeeded.

[oracle@chacha ~]$ sqlplus webuser/webpwd2

WEBUSER> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

## webuser 사용자에게 scott 의 emp 테이블에 update 할 수 있는 권한을 생성하되, webuser 가 다른 사용자에게도 이 권한을 줄 수 있도록 설정

SYS> grant update on scott.emp to webuser with grant option;

Grant succeeded.

## webuser 가 가진 scott 관련 권한 해제

SYS> revoke select on scott.emp from webuser;

Revoke succeeded.

- SYSOPER / SYSDBA privilege

Privilege	할 수 있는 일
SYSOPER	Startup / Shutdown
	Alter database mount / open
	Alter database backup control file to ..
	Recover database
	Alter database archivelog
	Restricted session
SYSDBA (모든 것 가능)	SYSOPER privilege with admin option
	create database
	Alter tablespace .. begin backup / end backup
	Recover database until

- SYSTEM 관련 권한 할당 / 조회 / 해제하기

### webuser 사용자에게 create table, create session 권한을 할당 / 조회 / 해제

SYS> grant create table, create session to webuser;

Grant succeeded.

SYS> select * from dba_sys_privs where grantee='WEBUSER';

GRANTEE PRIVILEGE ADM

------------------------------ ---------------------------------------- ---

WEBUSER UNLIMITED TABLESPACE NO

WEBUSER CREATE TABLE NO

WEBUSER CREATE SESSION NO

SYS> revoke create table from webuser;

Revoke succeeded.

SYS> select * from dba_sys_privs where grantee='WEBUSER';

GRANTEE PRIVILEGE ADM

------------------------------ ---------------------------------------- ---

WEBUSER UNLIMITED TABLESPACE NO

WEBUSER CREATE SESSION NO

============> 위의 adm 컬럼은 with admin option 여부를 나타낸다. with admin option 이란 권한 위임하는 기능이다. 예를 들어, DBA 가 A 사용자에게 create table 이란 권한을 줄 때 그냥 주면 A 사용자는 create table 을 수행하는 권한만 받게 되지만, grant create table to A with admin option 이렇게 주면 A 사용자는 또 다른 사용자에게 with admin option 과 함께 받은 create table 이라는 권한을 마치 DBA 처럼 할당 / 회수 할 수 있다.

- with grant option / with admin option

이 둘의 차이점은 DBA 가 A 사용자에게 with admin option 을 사용하여 권한을 주고 A 사용자가 다시 B 사용자에게 권한을 주었을 때, DBA 가 A 사용자에게서 권한을 해제해도 B 사용자의 권한은 해제가 안되지만, with grant option 은 A 사용자의 권한을 해제하면 자동으로 A 사용자에게서 받은 B 사용자의 권한까지 다 해제된다.

- SYSDBA 권한을 가지고 있는 계정 조회하기

SYS> grant sysdba to scott;

Grant succeeded.

SYS> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS

------------------------------ ----- ----- -----

SYS TRUE TRUE FALSE

SCOTT TRUE FALSE FALSE

5) Role 관리하기

Role 이란 권한들의 그룹이라고 생각하면 된다. 일일히 필요한 권한들을 각 사용자에게 각각 할당하기 힘드니 여러가지 권한을들 묶어 놓고 role 하나를 주면 편하다.

- Role 생성하기

SYS> create role trole;

Role created.

- Role 에 create session, create table 권한 할당하기

SYS> grant create session, create table to trole;

Grant succeeded.

- webuser 사용자에게 trole 할당하기

SYS> grant trole to webuser;

Grant succeeded.

- 어떤 사용자가 어떤 role 을 사용하는지 확인하기

SYS> select * from dba_role_privs where grantee='WEBUSER';

GRANTEE GRANTED_ROLE ADM DEF

------------------------------ ------------------------------ --- ---

WEBUSER RESOURCE NO YES

WEBUSER TROLE NO YES

WEBUSER CONNECT NO YES

- 어떤 Role 에 어떤 권한이 있는지 확인하기

SYS> select * from dba_sys_privs where grantee='CONNECT';

GRANTEE PRIVILEGE ADM

------------------------------ ---------------------------------------- ---

CONNECT CREATE SESSION NO

SYS> select * from dba_sys_privs where grantee='RESOURCE';

GRANTEE PRIVILEGE ADM

------------------------------ ---------------------------------------- ---

RESOURCE CREATE TRIGGER NO

RESOURCE CREATE SEQUENCE NO

RESOURCE CREATE TYPE NO

RESOURCE CREATE PROCEDURE NO

RESOURCE CREATE CLUSTER NO

RESOURCE CREATE OPERATOR NO

RESOURCE CREATE INDEXTYPE NO

RESOURCE CREATE TABLE NO

8 rows selected.

저작자표시 비영리 동일조건

'Oracle > Admin' 카테고리의 다른 글

[Oracle Admin] Chap 12. Archive/No Archive log mode (0)	2013.09.22
[Oracle Admin] Chap 11. DBMS_JOB & DBMS_SCHEDULER (0)	2013.09.18
[Oracle Admin] Chap 9. Oracle 메모리 관리 기법들 (0)	2013.09.17
[Oracle Admin] Chap 8. Oracle 저장구조 (0)	2013.09.14
[Oracle Admin] Chap 7. Tablespace 와 Data file 관리하기 (0)	2013.09.11

by 짱구를꼭말려 2013. 9. 17. 23:33

[Oracle Admin] Chap 9. Oracle 메모리 관리 기법들

Oracle/Admin

Chap 9. Oracle 메모리 관리 기법들

1. 9i 버전에서의 메모리 관리 기법

1) Dynamic SGA 및 그래뉼 (Granule)

- 9.0.1 버전에서는 Default buffer cache 의 값을 Dynamic 하게 변경할 수 있다.

- 9.0.2 버전부터는 Shared poll, Default buffer cache, Large pool, java pool 의 값을 변경할 수 있다.

- 주의 사항은 SGA 의 전체 크기 (위 항목들을 모두 합친 값이) 가 SGA_MAX_SIZE 에 설정 된 값 보다 클 수 없다는 것다.

- Oracle 이 동적으로 각종 메모리를 할당할 때 쓰는 단위로 그래뉼 (Granule) 를 사용한다. 1 그래뉼의 단위는,

* 9i 의 경우 > SGA 의 전체 크기가 128M 이하이면 1그래뉼이 4MB / 128M 이상이면 16MB

* 10g 의 경우 > SGA 의 전체 크기가 1G 이하이면 1그래뉼이 4MB / 1G 이상이면 16MB

2) SGA 총 크기와 그래뉼 사이즈 조회

SQL> select name, bytes/1024/1024 MB from v$sgainfo where name='Maximum SGA Size';

NAME MB

-------------------------------- ----------

Maximum SGA Size 403.089844

SQL> select name, bytes/1024/1024 MB from v$sgainfo where name='Granule Size';

NAME MB

-------------------------------- ----------

Granule Size 4

- 위에서 조회한 그래뉼 사이즈로 각 백그라운드 프로세스들은 메모리를 할당 받아서 사용하게 된다. 아래 결과를 보면 메모리가 4MB 씩 매핑되어 사용되는 것을 알 수 있다.

[oracle@chacha ~]$ pmap `pgrep -f pmon` | grep /dev/shm/ora_testdb

~~~~~~~ 중간 생략

20001000 4092K rwxs- /dev/shm/ora_testdb_6389777_0

20400000 4096K rwxs- /dev/shm/ora_testdb_6389777_1

20800000 4096K rwxs- /dev/shm/ora_testdb_6389777_2

20c00000 4096K rwxs- /dev/shm/ora_testdb_6389777_3

~~~~~~~ 중간 생략

3) PGA 관리

- 9i 부터 WORKAREA_SIZE_POLICY 파라미터를 AUTO 로 설정해두면 PGA 전체 크기 안에서 PGA 값을 Oracle 이 자동으로 관리를 하게 된다.

- PGA 전체 크기는 PGA_AGGREGATE_TARGET 파라미커로 정할 수 있다. 단, 실제 서버 프로세스가 사용할 수 있는 최대 PGA 크기는 히든 파라미터인 _PGA_MAX_SIZE 로 지정된다. 아래는 PGA 주요 항목들이다.

SQL> select a.ksppinm, b.ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and a.ksppinm in ('_pga_max_size', '_smm_max_size', '_smm_px_max_size');

KSPPINM KSPPSTVL

---------------------------------------- ----------------------------------------

_pga_max_size 209715200

_smm_max_size 33587

_smm_px_max_size 83968

* _pga_max_size : 1개의 프로세스가 사용할 수 있는 최대 PGA의 크기를 bytes 단위로 나타낸다. 직렬/병렬 처리시 사용 될 값을 모두 합친 값이다.

* _smm_max_size : 1개의 프로세스가 직렬처리 시 사용할 수 있는 최대 PGA 값을 kb 단위로 나타낸다.

* _smm_px_max_size : 1개의 프로세스가 병렬처리 시 사용할 수 있는 최대 PGA의 값을 kb 단위로 나타낸다.

- 9i 부터 PGA 가 자동으로 관리가 되서 편리하긴 하지만 단점으로는, 하나의 세션이 사용할 수 있는 PGA 의 용량이 정해져 있기 때문에 대량의 데이터를 처리해야 하는 배치성 작업의 경우에는 문제가 될수 있다. 이럴 경우 (대량의 sort 나 hash 가 발생하는 경우), WORKAREA_SIZE_POLICY 값을 manual 로 설정한 후에 SORT_AREA_SIZE 나 HASH_AREA_SIZE 값을 수동으로 정해주면 성능향상에 도움이 된다.

- PGA 에서 자동으로 관리할 경우 1 세션당 사용할 수 있는 PGA 용량은 아래와 같이 결정된다.

* 직렬 작업일 경우 : 5% PGA_AGGREGATE_TARGET 또는 100MB (둘중 작은 값)

* 병렬 작업일 경우 : 30% PGA_AGGREGATE_TARGET/DOP 또는 _pga_max_size (둘중 작은 값)

- PGA 용량이 부족할 경우 ORA-04030 에러가 발생한다.

- 현재 작동중인 Parallel Query 의 busy, idle 상태 및 server start, shutdown 된 상태를 조회하려면 아래와 같이 하면 된다.

SQL> select * from v$pq_sysstat where statistic='Severs Busy';

2. 10g 버전에서의 메모리 관리기법

1) ASMM / MMAN

- 10g 부터는 공유 메모리 (SGA) 의 주요 구성 요소들의 크기를 오라클이 스스로 변경할 수 있게 하는 기능인 Automatic Shared Memory Management (ASMM) 이 등장한다.

- ASMM 로 변경 가능한 구성요소는 Shared Pool, Default Database Buffer Cache, Large Pool, java Pool, Streams Pool 이다. (Redo log buffer 는 불가능)

- ASMM 을 수행하기 위한 백그라운드 프로세스는 MMAN 이다.

- MMAN 이 SGA 의 양을 자동으로 할당하고 관리 할 때 기준으로 사용하는 파라미터가 SGA_TARGET 이다. 이 파라미터에 설정된 용량의 범위 안에서 메모리를 할당하고 관리를 하게 된다.

- SGA_TARGET 파라미터는 SGA_MAX_SIZE 의 값을 초과할 수 없다.

- ASMM 기능을 사용하기 위한 조건 :

* STATISTICS_LEVEL 을 TYPICAL 또는 ALL 로 설정

* SGA_TARGET 파라미터의 값을 0 보다 큰 값으로 설정

* 다른 파라미터의 값들을 0 으로 설정

=> 만약, 다른 파라미터의 값들 또한 0 보다 큰 값으로 설정을 하면, 해당 파라미터의 값이 최소값으로 인식을 하게 된다. 예를 들어, SGA_TARGET=2G 로 했는데, Shared_pool_size=100m 로 설정했다면, Shared_pool_size 의 최소값은 100m 가 된다.

- SGA_TARGE 값으로 자동 튜닝이 되는 파라미터 (Auto-Tuned dynamic parameter) :

DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, STREAMS_POOL_SIZE

- SGA_TARGE 값으로 사용자가 바꿔주는 수동 파라미터 (Manual-Tuned parameter) :

DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, DB_nK_CACHE_SIZE, SGA_TARGET

2) SGA_TARGET 관련 테스트

- SGA_TARGET 값 조회

SQL> show parameter sga_target;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

sga_target big integer 0

==> 이럴 경우는 SGA 관련 파라미터들을 전부 수동으로 설정해 주어야 한다.

- SGA_TARGET 값 설정을 한 후 자동으로 할당 된 내역들을 살펴보자. 우선 SGA_TARGET 값은 SGA_MAX_SIZE 에서 Log_buffer 값과 예약공간 값을 제외한 크기를 초과할 수 없다. 현재 남아 있는 용량을 살펴 보려면 v$sga_dynamic_free_memory 를 조회해 보면 된다.

## 현재 sga_max_size 조회

SQL> show parameter sga_max_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

sga_max_size big integer 404M

## 현재 남은 용량 조회

SQL> select current_size/1024/1024 MB from v$sga_dynamic_free_memory;

----------

164

================> 위에서 적었듯이, sga_target 값은 sga_max_size 에서 log_buffer 와 예약공간 값을 제외한 크기를 초과할 수 없다. 그래서 sga_target 값을 적절히 주지 않으면 아래와 같은 에러가 발생한다.

SQL> alter system set sga_target=400M;

alter system set sga_target=400M

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00839: SGA_TARGET cannot be modified to the specified value

==> 다시 적당한 값으로 바꿔보자.

SQL> alter system set sga_target=100M;

System altered.

SQL> show parameter sga_target;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

sga_target big integer 100M

SQL> select component, current_size/1024/1024 curr_MB, min_size/1024/1024 min_MB, user_specified_size/1024/1024 user_MB from v$sga_dynamic_components;

COMPONENT CURR_MB MIN_MB USER_MB

---------------------------------------------------------------- ---------- ---------- ----------

shared pool 96 76 0

large pool 4 4 0

java pool 4 4 0

streams pool 0 0 0

DEFAULT buffer cache 128 128 0

KEEP buffer cache 0 0 0

RECYCLE buffer cache 0 0 0

DEFAULT 2K buffer cache 0 0 0

DEFAULT 4K buffer cache 0 0 0

DEFAULT 8K buffer cache 0 0 0

DEFAULT 16K buffer cache 0 0 0

DEFAULT 32K buffer cache 0 0 0

Shared IO Pool 0 0 0

ASM Buffer Cache 0 0 0

3. 11g 버전에서의 메모리 관리기법

- 메모리를 관리하고 튜닝하는 기술이 9i 는 Dynamic SGA , 10g 는 ASMM 로 SGA 크기를 스스로 튜닝하는 기술로 발전했으며, 11g 에서는 SGA 뿐만 아니라 PGA 까지 한꺼번에 자동으로 관리하는 기술인 Automatic Memory Management (AMM) 으로 발전하게 된다.

- AMM 에서 새로 생긴 2개의 파라미터는 아래와 같다.

* MEMORY_TARGET :

이 파라미터로 AMM 기능으로 관리할 메모리의 총 량을 지정할 수 있다. 이 파라미터의 최대 설정값은 MEMORY_MAX_SIZE 파라미터 값 까지 지정할 수 있다. 기본값은 0 이며, 이 뜻은 이 기능을 사용하지 않는다는 의미이다.

* MEMORY_MAX_TARGET :

이 파라미터는 MEMORY_TARGET 값이 최대로 증가될 값을 지정하는 용도로 사용된다.

- AMM 기능을 사용하게 된다면, SGA_TARGET 이나 PGA_AGGREFATE_TARGET 파라미터의 값을 0으로 셋팅하는 것이 좋다. 민약 이 값들을 지정하게 된다면, 오라클은 그 값을 최소값으로 인식하게 된다.

- AMM 기능을 사용하기 위해서는 물리적인 메모리가 충분한 여유공간이 있어야 하며, 만약 메모리 공간이 부족할 경우 에러가 발생한다. AMM 은 /dev/shm 공간을 공유 메모리 공간과 매핑시켜서 메모리를 디스크 처럼 사용하게 되는데, 기본 크기는 OS 메모리의 50% 로 설정이 된다.

- 만약 물리 메모리 양이 부족할 경우 oracle startup 할 시에 ORA-00845 같은 에러를 발생한다.

저작자표시 비영리 동일조건

'Oracle > Admin' 카테고리의 다른 글

[Oracle Admin] Chap 11. DBMS_JOB & DBMS_SCHEDULER (0)	2013.09.18
[Oracle Admin] Chap 10. Oracle 사용자 관리 (0)	2013.09.17
[Oracle Admin] Chap 8. Oracle 저장구조 (0)	2013.09.14
[Oracle Admin] Chap 7. Tablespace 와 Data file 관리하기 (0)	2013.09.11
[Oracle Admin] Chap 6. Redo Log 관리하기 (0)	2013.09.10

by 짱구를꼭말려 2013. 9. 17. 23:18

[Oracle Admin] Chap 8. Oracle 저장구조

Oracle/Admin

Chap 8. Oracle 저장구조

* data 의 작업 단위

Block < Extent < Segment < Table space < Data base

1. Oracle Block

- Data 의 가장 최소 작업 단위이다.

- OS Block 을 한 개 이상 합쳐서 생성되며, DB_BLOCK_SIZE 로 지정된다.

- DB_BLOCK_SIZE 파라미터는 Create Database 할 때 한 번 지정이 되면 그 값은 DB를 재 생성 하기 전에는 변경 할 수 없으며 이 값을 Standard Block Size 라고 한다.

- Block 의 크기는 최소 2KB, 4KB, 8KB, 16KB, 32KB 가 제공된다. (짝수로 하는 것을 권장) 이 중, DB_BLOCK_SIZE 값에 설정 된 크기를 Standard Block Size 라고 하며, 다른 크기들은 Non-Standard Block size 라고 한다.

- 이런 Block size 들은 tablespace 를 생성할 때 이용할 수 있지만, Standard Block Size 외의 크기를 가지고 tablespace 를 생성하려면 DB Buffer cache 에도 해당 block size 만큼의 공간이 할당 되어 있어야 한다. 즉, 아래 내용을 보면,

## 우선 아래처럼 설정되어 있는 db_block_size 을 확인, (8K 이다.)

SYS> show parameter db_block_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_size integer 8192

## 아래처럼 4K 크기로 tablespace 를 만들면 standard block size 가 아니므로 에러가 난다.

SYS> create tablespace test_4k datafile '/app/oracle/oradata/testdb/test01.dbf' size 5M blocksize 4k;

create tablespace test_4k datafile '/app/oracle/oradata/testdb/test01.dbf' size 5M blocksize 4k

ERROR at line 1:

ORA-29339: tablespace block size 4096 does not match configured block sizes

## 이럴 경우, DB Cache 에 미리 공간을 할당하고,

SYS> alter system set db_4k_cache_size=10M;

System altered.

## 다시 생성,

SYS> create tablespace test_4k datafile '/app/oracle/oradata/testdb/test01.dbf' size 5M blocksize 4K;

Tablespace created.

2. Oracle Data Block 의 상세구조

- 아래는 Data Block 의 상세 구조이다.

Data Block Layout

Cache layer (common)	Block header
Transaction layer : fixed
Transaction layer : variable

Table directory	Data layer
Row directory
Free space
Row data

- 각 크기 조회하는 방법

SYS> select component, type, description, type_size from v$type_size where component in ('KCB', 'KTB');

COMPONEN TYPE DESCRIPTION TYPE_SIZE

-------- -------- -------------------------------- ----------

KCB KCBH BLOCK COMMON HEADER 20

KTB KTBIT TRANSACTION VARIABLE HEADER 24

KTB KTBBH TRANSACTION FIXED HEADER 48

KTB KTBBH_BS TRANSACTION BLOCK BITMAP SEGMENT 8

- 위의 정보를 보면, Block header 의 cache layer 는 20 bytes 이고 (KCBH 로 조회), 아래와 같은 정보가 들어있다.

## Cache layer 에 포함되어 있는 정보

* Data Block Address (DBA)

* Block Type (ex, Table, Index, Undo..)

* Block Format

* System Chane Number (SCN)

- 위의 정보에서, Transaction fixed 는 48 bytes 로 지정이 되어 있고, 이 곳에는 Block type, 마지막으로 수행된 Block Cleanout 시간, ITL Entries 정보, Free List Link 정보, Free space Lock 정보 등이 저장되어 있다.

- 그리고 Transaction Variable 영역은 KTBIT 부분에 해당하며 24 bytes 의 크기를 가지고 있다. 이 부분에는 해당 Block 에 있는 row 를 변경하기를 원하는 실제 ITL 관련 정보가 들어간다. 사용자가 많으면 이 부분의 내용이 많아지고 사용자가 적으면 내용이 적어진다.

- Data layer: 실제 데이터가 저장되는 곳이므로 Free space 관리가 중요하다. 그래서 늘 비어있는 free space 정보를 관리하고 있는데 row index 에서 첫 번째 빈 공간의 포인터 주소와 빈 공간의 시작과 끝나느 offset 주소, 사용가능한 빈 공간의 양을 가지고 있다.

3. PCTFREE 와 PCTUSED

1) PCTFREE

- 해당 Block 에 입력되어 있는 데이터들이 update 될 경우를 대비해서 예약을 해두는 공간이다.

- 원본 데이터보다 더 큰 크기의 데이터가 update 될 경우, 해당 block 에 여유 공간이 없어서 다른 block 으로 migration (row migration) 을 해야할 경우 성능 저하가 올 수 있다.

- 이 값은 해당 block 크기의 %로 지정한다. 즉, 해당 Block 이 8KB 라고 하고, PCTFREE=20 이라고 지정했다면, 8KB의 20%만큼 update 만을 위한 공간으로 예약이 되어 해당 공간에는 insert 는 안되고 update만 된다. 그리고 이 공간만큼을 뺀 나머지 공간이 다 찼을 경우는 해당 block 의 더 이상 빈 공간이 없다고 판단(dirty block) 하게 된다.

2) PCTUSED

- Block 의 재사용 여부를 결정하는 요소로, 현재 데이터의 사용량이 이 설정값 이하로 남아 있을 경우에 해당 Block 의 상태를 dirty 에서 free 로 반환할 수 있다.

- 즉, 해당 Block 에 PCTUSED 가 40% 로 설정이 되어 있다면, 해당 block 에 데이터 적재량이 그 block 의 40% 이하가 되어야 이 block 으로 데이터가 다시 insert 될 수 있다.

4. Row Chaining & Row Migration

- 데이터가 Block 에 저장이 될 때는 보통 row 단위로 입력이 되는데 row overhead 와 column data 부분으로 나눌 수 있다. row overhead 에는 lock 정보 같은 것이 들어가 있고, column data 부분이 실제 데이터가 들어가는 부분이다.

- Row Chaining

만약 입력 되는 데이터가 너무 길어서 하나의 block column data 부분에 들어가지를 못한다면 해당 row 를 다른 block 에 연결해서 저장하게 된다. 즉, A 라는 Block 에 사용가능한 공간이 1KB 정도 밖에 없는데 데이터가 2KB 가 들어온다면 A block 에 1KB 를 저장하고 나머지 1KB 는 다른 block 에 이어서 저장한다. 이런 경우를 Row Chainin 라고 한다.

- Row Migration

특정 Block 에 위치하던 row 가 더 큰 크기로 update 가 될 때 해당 block 의 크기가 너무 작아서 다른 block 으로 이사를 가는 것을 말한다. 이럴 경우 이전 block 으로 찾아오는 액서스들을 위해 원래 block 의 이사가는 신규 block 의 주소를 남겨 두게 되는데 이런 이유로 I/O 가 더 많이 일어나게 된다. 즉, 성능에 좋지 않다.

5. Extent 와 Segment

1) Extent

- Block 을 여러 개 묶어 놓은 단위

- 테이블을 생성하면 Block 을 묶어서 Extent 를 생성하게 되고, 데이터가 계속 추가되어서 최초의 Extent 를 다 사용하게 되면, 자동으로 Extent 를 추가 생성하게 된다.

- Extend 의 기본 단위는 64K 이고 Extend 에 데이터를 어디까지 사용했는지를 표시하기 위한 책갈피 역활을 하는 하는 애가 High Water Mark 라고 한다.

- Extent 를 미리 확보하기 위해 수동으로 할당 할 수도 있는데, 이럴 경우 (미리 확보해 둘 경우) :

* 단점 : 데이터가 할당해 둔 것보다 훨씬 작게 들어온다면 비어 있는 공간이 많이 생겨 공간 낭비가 심할 수 있다.

* 장점 : 하나의 데이터파일에 여러 테이블이 저장 될 수 있는데, 미리 Extent 를 확보해 두면 각 테이블에 데이터의 분산을 막을 수 있다.

2) Segment

- 쉽게 말해 데이터를 담는 통이다. Data segment 는 우리가 알고 있는 table 이고, Index segment 는 index 이다.

- Undo, Temp, LOB 등이 대표적인 segment 의 종류 들이다.

6. Extent 관리 기법

1) Dictionary Management Tablespace (DMT)

- Oracle 8 버전까지 사용했던 방식으로 모든 데이터 파일을 Dictionary 에서 Free/used block 을 중앙 집중적으로 관리했던 방식이다.

- Free Block 은 FET$ (Free Extent Table) 에서, Dirty Block 은 UET$ (Used Extent Table) 에서 관리했다.

- 만약 Drop Tablespace 같은 이유로 Dirty extent -> Free extent 가 된다면, 위의 테이블들을 업데이트 해야만 했다.

- 단점 : 데이터가 한꺼번에 몰려서 Free Extent 를 요청하거나, Drop 명령등의 수행으로 FET$ 를 업데이트 해야 할 경우 심각한 병목현상이 발생해 성능 저하가 올 수 있다.

2) Locally Managed Tablespace (LMT)

- Oracle 8 버전의 단점으로 인해 9i 부터는 DMT 대신 LMT 방식을 도입하게 되었는데, 아래의 2가지 방식으로 관리가 된다.

Free List Management (FLM)

- 9i 버전에서 쓰는 Extent 관리기법

- Free Extents 를 관리하기 위해서는 Free List 와 Free Group 이라는 것을 사용한다.

- Free List 는 Segment 단위로 작성이 되는데, 즉, Table 단위로 생성이 된다. 아래와 같이 3가지 종류가 있다.

* Master Free List (Segment Free List)

해당 Segment 에 새롭게 할당된 Free Block 이나 트랜잭션이 종료되어 Dirty -> Free 로 반환된 Block 들의 목록이 저장되어 있다. 테이블 생성시 Storage 설정으로 Process Free List 를 지정하지 않으면 기본적으로 이 Free List 만 만들어 지고 전체 Block 를 관리하게 된다.

* Process Free List

트랜잭션이 종료되어 Dirty -> Free 로 반환된 Block 들의 목록이 저장되어 있다. Master Free List 의 경합을 막기 위해서 사용된다.

* Transaction Free List

현재 세션이 Transaction 을 수행하는 도중 Free Block 으로 변한 Block 들의 목록을 관리한다. 예를 들어, 특정 row 가 update 되거나 delete 되어서 해당 row 가 들어 있던 block 이 PCTUSED 파라미터 값에 도달하게 되서 Free Block 으로 변하게 되면 이 block 을 바로 Master Free List 로 반환하지 않고 Transaction 이 수행되고 있는 기간동안 이 List 에 등록해 두고 Free Block 이 필요할 경우 우선적으로 사용하게 된다.

- FLM 의 단점은 현재 사용량을 알수가 없다.

Automatic Segment Space Management (ASSM)

- 10g 버전부터의 Extent 관리기법이다.

- FLM 이 Free List 의 관리에 따라 성능이 결정이 되는 수동 모드의 관리 방법이라면, ASSM 은 Oracle 이 자동으로 Extent 를 관리하는 기법이다.

- 기존에 사용하던 Free List 대신에 아래처럼 Bitmap 으로 사용 정도를 관리하게 된다.

* 0000 : 포멧이 안된 상태

* 0001 : 100% 사용중인 상태

* 0010 : 0~25% Free space 가 있는 상태 (FS1)

* 0011 : 25~50% Free space 가 있는 상태 (FS2)

* 0100 : 50~75% Free space 가 있는 상태 (FS3)

* 0101 : 75~100% Free space 가 있는 상태 (FS4)

저작자표시 비영리 동일조건

'Oracle > Admin' 카테고리의 다른 글

[Oracle Admin] Chap 10. Oracle 사용자 관리 (0)	2013.09.17
[Oracle Admin] Chap 9. Oracle 메모리 관리 기법들 (0)	2013.09.17
[Oracle Admin] Chap 7. Tablespace 와 Data file 관리하기 (0)	2013.09.11
[Oracle Admin] Chap 6. Redo Log 관리하기 (0)	2013.09.10
[Oracle Admin] Chap 5. Control File 관리하기 (0)	2013.09.09

by 짱구를꼭말려 2013. 9. 14. 00:14

PREV | 1 2 3 | NEXT

글

설정

'Oracle > Admin' 카테고리의 다른 글

글

설정

'Oracle > Admin' 카테고리의 다른 글

글

설정

'Oracle > Admin' 카테고리의 다른 글

글

설정

'Oracle > Admin' 카테고리의 다른 글

글

설정

'Oracle > Admin' 카테고리의 다른 글

글

설정

'Oracle > Admin' 카테고리의 다른 글

글

설정

'Oracle > Admin' 카테고리의 다른 글

글

설정

'Oracle > Admin' 카테고리의 다른 글

글

설정

'Oracle > Admin' 카테고리의 다른 글

글

설정

'Oracle > Admin' 카테고리의 다른 글

사이드 메뉴

CATEGORY

RECENT POSTS

CALENDAR

COUNTER

MY LINK

티스토리툴바