오라클 데이터베이스 생성과 프로세스

오라클 데이터베이스 생성과 프로세스

1. 오라클 데이터베이스 개발
오라클상의 데이터베이스 생성이란 물리적으로 논리적인 데이터베이스를 위한 틀을 갖춘다는 의미이다.

데이터베이스 생성이란 개념적으로 운영체제상에서 그 크기가 고정된 디렉토리를 만드는 것과 같다고 볼 수
있다. 생성한 디렉토리에 파일을 만드는 것이 오라클에서 테이블 생성 그리고 그 파일에 데이터를 입력하는
것이 데이터 저장이라고 볼 수 있다.

오라클 데이터베이스 생성단계
1, 테이블스페이스를 통해 데이터베이스의 내용을 구성한다.
2, Contention과 Fragmentation을 감소시키기 위해 데이터베이스의 구조를 설계한다.
3, 데이터베이스 생성을 위한 운영체제의 환경을 준비한다.
4, 파라미터파일을 수정한다.
5, 오라클 인스턴스를 가동시킨다.
6, CREATE DATABASE SQL 명령을 실행한다.
7, 여러 개의 리두 로그 파일과 컨트롤 파일을 만들어 데이터베이스의 안전성을 확보한다.
8, 데이터베이스를 모니터하기 위해 Data Dictionary와 Data Dictionary View를 정의한다.
9, 읽기 연속성을 보장하기 위해 롤백 세그먼트를 정의하고 생성한다.

오라클 Tablespace의 결정
- 각각의 Tablespace는 하나 또는 그 이상의 물리적 Data file을 포함한다.
- Tablespace는 데이터베이스가 가동하는 동안에 Online 또는 Offline으로 그 상태를 변경할 수 있다.
예외) SYSTEM Tablespace는 오라클 데이터베이스 가동중에 Offline으로 바꿀 수 없다.
- 각각의 객체는 반지 한 개의 Tablespace로 운영된다.
주의)버전 8부터는 한 객체가 두 개이상의 Tablespace를 가질 수 있다.

오라클이 제안하는 Tablespace (참고 page. 100)
- Sorting과 같은 임시 데이터를 저장하기 위한 TEMP Tablespace.
- 리두 로그 파일을 저장하기 위한 RBS Tablespace.(RollBack Segment Tablespace)
- 오라클 서버 툴이 필요로 하는 테이블들을 저장하기 위한 TOOLS Tablespace.
- 일반 Tablespace를 저장하기 위한 APPL_DATA Tablespace.
- 인덱스를 저장하기 위한 APPL_INDEX Tablespace.

Tablespace 설계시 고려사항
- Fragmentation을 최소화할 수 있도록.
- Disk Contention을 최소화할 수 있도록.
①다른 Segment로부터 디렉토리 Segment를 분리.
②다른 Segment로부터 Rollback Segment를 분리.
③Index Segment와 대응되는 Data Segment를 분리.
- 각각의 Segment를 사용용도와 종류별로 나누어 분리.
①백업의 필요 여부에 따라 Segment 구분
②사용용도에 따라 Segment 구분
③서로 다른 수명을 갖는 Segment 구분

오라클 데이터베이스 파일 관리
- 적어도 두 개 이상의 서로 다른 물리적인 디스크에 두 개 이상의 컨트롤 파일을 복사해 둔다.
- 적어도 두 개 이상의 리두 로그 그룹을 만들고 각 그룹에 두 개 이상의 리두 로그 파일을 만든다.
또한 서로 다른 그룹은 물리적으로 각각 다른 디스크에 둔다.
- 서로 다른 물리적인 디스크 자원을 교차하여 데이터가 디스크 자원의 Contention이 발생할 소지가 있으면
Tablespace를 분리한다.

2, 데이터베이스 생성

1단계 : 기존 데이터베이스의 백업 및 데이터베이스 Spec 결정
Data File, Control File, Parameter File, Redo Log File을 Backup 해야한다.
왜냐하면 새로운 데이터베이스 생성이 이러한 File에 영향을 줄 수 있기 때문이다.

새로 생성할 데이터베이스의 이름과 유일한 인스턴스 이름, 데이터 블록의 크기와 데이터베이스 언어 설정 및 오라클 DBMS에 의해 생성될 수 있는 데이터파일과 Lock File의 최대 수를 결정하는 일이다.

2단계 : 새로운 환경설정 파일의 생성
오라클 설치시의 SID로 File명이 결정된다.
ex) personal : C:\ORAWIN95\Database\initorcl.ora
인스턴스를 시작할 때, 이 파라미터 파일에서 오라클 데이터베이스 환경과 관련된 사항들을 참조하여 인스턴스를 가동시키게 된다.

주의: 기존의 init.ora file을 복사 후 다른 이름으로 저장하여 내용을 수정.

3단계 : 생성한 파라미터 파일의 수정

참고] page. 168 퍼스널 오라클의 init.ora 파일

파라미터들과 파라미터의 의미

CHECKPOINT_PROCESS
LGWR 프로세스에 의해 동작한다.
Update 될 데이터가 많을 때 LGWR의 부하를 줄여 준다.
LGWR의 성능이 효율적이지 못할 때, 일정한 규칙에 따라 Checkpoint process를 인위적으로 가동시키려 한다면
TRUE로 설정한다.

COMPATIBLE
호환성을 유지해야 하는 버전을 명시.

CURSOR_SPACE_FOR_TIME
커서를 사용하는 프로그램의 성능을 향상.
메모리 영역의 SQL 영역에 영향을 주는 것으로 TRUE로 설정되어 있을 경우 보다 많은 메모리 영역을 SQL 영역에 할당하게 된다.
- CURSOR : 모든 SQL의 실행시에 문맥을 가리키는 pointer 역할.

DB_BLOCK_BUFFERS
SGA내의 데이터베이스 버퍼 캐시의 개수를 결정하는 파라미터.
주의할 점은 설치할 시스템이 그러한 메모리를 가지고 있는지 확인해야 한다.
(DB Buffer Cache(400K) = DB_BLOCK_SIZE(2K) * DB_BLOCK_BUFFERS(200))

또한 값을 높게 설정하면 성능 향상에 도움이 되지만 그만큼 많은 메모리를 사용하게 되며 과다한 값의 설정은 많은 Swaping을 유발할 수 있기 때문에 성능의 저하를 가져온다.

DB_BLOCK_SIZE
오라클 데이터베이스 블록은 오라클 스토리지 구조의 가장 기본이 되는 단위이며, 데이터베이스를 생성하는데 있어 가장 중요한 파라미터중 하나이다.

DB_FILES
데이터베이스에서 사용 가능한 데이터 파일의 개수를 지정하는 파라미터.
CREATE DATABASE SQL문에서 설정하는 MAXDATAFILES의 값보다 크게 설정되어 있어야 한다.

DB_FILE_MULTIBLOCK_READ_COUNT
데이터베이스가 많은 데이터를 연속해서 읽어 들일 때 한번의 I/O 작업에 의해서 읽어지는 Block의 최대 크기를 결정.
운영체제의 최대 I/O 크기보다 작아야 한다.

DB_NAME
데이터베이스의 이름을 정해주는 파라미터.
CREATE DATABASE SQL 문에서 사용하는 데이터베이스명과 일치해야 한다.
_ , # , $와 같은 특수문자는 허용 나머지는 허용하지 않는다.

LOG_BUFFER
SGA 영역의 리두 로그 버퍼의 크기를 결정하는 파라미터.
이 크기가 크면 리두 로그를 위한 I/O작업이 줄어들게 된다.
초기값은 데이터베이스 블록 크기의 4배정도 된다.

LOG_CHECKPOINT_INTERVAL
로그 체크 포인트의 간격을 결정.
로그 체크포인트가 발생할 데이터 블록의 수를 정해 주는 것으로 정해진 값 이상으로 채워지면 체크 포인트가 발생한다.
간격을 크게 하면 체크포인트가 적게 일어나 성능을 향상시킨다.

LOG_CHECKPOINT_TIMEOUT
로그 체크포인트의 시간적 간격을 초단위로 정한다.

NLS (National Language Support)
한글 문자 - KO16KSC5601

OPEN_CURSORS
하나의 오라클 Session에서 사용할 수 있는 최대 CURSOR의 수를 제한.
CURSOR의 수가 정의한 수보다 크면 open할 수 없다는 메시지와 함께 프로그램이 제대로 실행되지 않는다.

PRE_PAGE_SGA
SGA 영역을 메모리로 설정할 것인지 결정.
YES로 설정하면 모든 SGA 영역이 메모리에 위치하게 되어 성능의 향상을 가져오나 다른 프로그램의 실행에 영향을 준다.
하지만 속도의 향상을 위해 설정을 권장.

ROLLBACK_SEGMENTS
오라클에서 사용되는 ROLLBACK SEGMENT의 목록
예) ROLLBACK_SEGMENTS= (r1, r2, r3, r4)

SHARED_POOL_SIZE
SGA의 SHARED POOL 버퍼의 size를 결정. Default는 3.5Mbytes

SORT_SIZE_AREA
PGA 부분에서 정렬시에 사용되는 메모리인 SORT AREA의 크기를 결정.

BACKGROUND_DUMP_DEST
백그라운드 프로세스에 의해서 발견된 에러 로그를 기록하는 Trace File의 위치를 지정.

USER_DUMP_DEST
서버 프로세스에 의해 발견된 에러를 기록하는 Trace File의 위치를 지정.

CONTROL_FILE
control file의 위치와 이름을 명시.

AUDIT_TRAIL
데이터베이스의 감사 추적을 활성화 비활성화.

LOG_ARCHIVE_START
데이터베이스가 ARCHIVELOG 모드에 있을 때 ARCH 프로세스를 활성화/비활성화.

LOG_ARCHIVE_DEST
ARCHIVE에 의해 생성되는 리두 로그 파일의 위치지정.

IFILE
만약 두 개 이상의 파라미터 파일이 존재한다면 데이터베이스 기동시에 참조할 다른 파라미터 파일의 이름을 명시.

4단계 : CREATE DATABASE SQL문 실행

CREATE DATABASE 명령
























database name
생성할 데이터베이스의 이름은 오라클 파라미터 파일에 명시한 데이터베이스 이름과 동일해야만 한다.

File Spac : 데이터 파일 또는 로그 파일의 설정.
[SIZE] - 숫자+바이트 단위
[REUSE] - 사용할 파일이 꼭 존재해야 한다.

CONTROLFILE REUSE :
contrilfile의 재사용할 것을 지정

LOGFILE GROUP :
사용할 로그 파일과 각 로그 파일의 그룹을 지정

MAXLOGFILES : 로그 파일의 최대수

MAXLOGMEMBERS :
한 로그 파일 그룹이 가질 수 있는 로그 파일의 최대 수

DATAFILE : 사용할 데이터파일을 지정

MAXDATAFILE : 생성하는 데이터파일의 최대 수

ARCHIVELOG/NOARCHIVELOG :
ARCHIVELOG는 항상 리두로그가 재사용되기 전에 먼저 Archive 될 것을 지정하고, NOARCHIVELOG는
Archiving 없이 리두 로그를 재사용 할 수 있음을 지정.
기본값은 NOARCHIVELOG

EXCLUSIVE :
EXCLUSIVE 모드로 데이터베이스를 마운트 할 것을 지정

CHARACTER SET : 글자 셋을 설정, 기본값은 'US7ASCII' 이다.

3, 데이터베이스 시작과 종료

























- NOMOUNT 단계 : 오라클 인스턴스를 시작.
오라클 데이터베이스는 아직 SGA영역에 올려지지 않은 상태이며, 데이터베이스를 생성하려면 NOMOUNT 단계까지 시작하고 생성해야 한다.

- MOUNT 단계 : 데이터베이스를 UPLOAD 하는 단계.
RECOVERY가 필요시는 MOUNT 단계에서 수행.

- OPEN 단계 : 모든 데이터베이스 파일을 열고 모든 사용자가 접근할 수 있도록 만든다.

NOMOUNT와 MOUNT 단계는 SYS 와 SYSTEM 사용자만이 접근.

"C:\Orawin95\Bin "의 svrmgr30.exe로 실행

오라클 데이터베이스 시작(Database Startup)

- 서버관리자(svrmgr30.exe)를 실행시킨다.
- 데이터베이스 내부에 접속한다.
SVRMGR>connect internal
Connected.
SYSTEM 권한을 가진 사용자로 로그인.
internal은 SYS와 SYSTEM이 가진 모든 권한을 가진다.
- 데이터베이스를 가동시킨다.(startup)
정상적이라면 인스턴스가 시작되고, 데이터베이스가 MOUNT되고, 데이터베이스가 OPEN되었음을 알리는 메시지가 출력된다.
(SGA에 할당된 메모리의 크기와 연산자 크기, 데이터베이스 버퍼, 리두로그 버퍼를 위해 할당된 메모리의 크기가 출력된다.)

오라클 데이터베이스의 시작 옵션

PFILE
시작하기 위해 사용할 파라미터 파일을 결정.
SVRMGR>STARTUP PFILE = initorcl.ora

데이터베이스를 생성하기 위해 새로운 파라미터 파일을 생성하였을 경우 새로운 파라미터 파일을 지정하기 위해 사용, 다른 데이터베이스 옵션 세팅이 필요할 때, 파일을 수정하고 새로운 이름으로 저장하였을 경우.

RESTRICT
사용자를 제한.
RESTRICTED SESSION 권한을 가진 사용자만 access.
DBA가 특별한 작업을 할 때 사용.

FORCE
현재 가동중인 오라클 인스턴스를 중지시키고, 오라클 데이터베이스를 다시 시작.
오류가 생겼거나 가동중인 인스턴스를 강제로 종료하고 다시 시작할 때 사용.

예) SVRMGR>STARTUP FORCE RESTRICT
PFILE=initorcl.ora

오라클 데이터베이스 시작 모드

NOMOUNT
오라클 인스턴스를 생성.
SGA 메모리 영역을 생성하고 오라클 백그라운드 프로세스를 시작시키는 작업.

MOUNT
오라클 인스턴스의 복구나 리두 로그 파일 관리 등의 작업을 수행할 때 사용.

- PARALLEL
병렬 서버와 같이 두 개 이상의 오라클 인스턴스를
기동할 때.
- EXCLUSIVE
오직 하나의 인스턴스를 기동 할 때
Default 는 EXCLUSIVE.

오라클 인스턴스가 이미 시작되어 있으면
ALTER DATABASE SQL 문을 사용하여 MOUNT나 OPEN 시킬수 있다.
SVRMGR>ALTER DATABASE MOUNT;

OPEN

이미 MOUNT 되어 있는 데이터베이스를 개방.
ALTER DATABASE SQL 문을 사용하여 OPEN 시킬수 있다.
SVRMGR>ALTER DATABASE OPEN;
오라클 데이터베이스의 종료

일반적으로 STARTUP과 반대의 작용.
SHUTDOWN시는 STARTUP보다 시간이 오래 걸린다.
이유는, 메모리상의 DATA를 실제 하드디스크상의 데이터 파일 및 로그 파일에 저장하는 작업을 하기 때문.

NORMAL : 정상 종료
SHUTDOWN 명령을 실행하면 사용자의 접근을 막고, 사용중인 사용자가 있으면 접속을 해지 할 때까지 기다린다.
IMMEDIATE : 강제 종료
현재 접속한 사용자의 접속 해지를 기다리지 않고 종료.
COMMIT 되지 않은 데이터는 모두 ROLLBACK 된다.
ABORT : 중단
현재 접속중인 사용자를 기다리지 않고, 데이터베이스를 CLOSE 나 DISMOUNT 하지 않는다.
단지 인스턴스만 종료한다.
종료한 오라클 데이터베이스를 다시 시작할 때에는 인스턴스 복구를 해주어야 한다.(자동으로 복구한다.)

4, 오라클 데이터베이스 생성 실제

- 파라미터 파일의 수정 (initorcl.ora)
DB_NAME
CONTROL_FILE
BACKGROUND_DUMP_DEST
USER_DUMP_DEST
DB_BLOCK_SIZE
수정 후 다른 이름으로 저장.
저장할 이름은 수정한 DB_NAME과 같아야 한다.

데이터베이스의 생성

데이터베이스를 NOMOUNT로 시작.
PFILE로 참조할 file을 지정한다.
SVRMGR>STARTUP NOMOUNT
PFILE=E:\XXX\initXXX.ora

데이터베이스를 생성
SVRMGR>CREATE DATABASE XXX
DATAFILE 'E:\XXX\system_01.dbf' SIZE 20M
LOGFILE GROUP 1 'E:\XXX\log1a.rdo' SIZE 200K
GROUP 2 'E:\XXX\log2a.rdo' SIZE 200K;
그러면 E:\XXX 디렉토리에 File이 생성된다.

컨트롤 파일의 추가

새로 생성한 데이터베이스에 컨트롤 파일을 복사, 추가하여 원본 컨트롤파일이 손상될 경우를 대비하여 복수 개의 컨트롤 파일을 사용.
XXX 데이터베이스를 종료하고 C:\Xbackup 디렉토리를 생성, 여기에 기존의 컨트롤 파일을 복사하고 이름을 바꾼다.(Ctl2XXX.ora)
그리고 initXXX.ora file에 추가한다.
control_files = (E:\XXX\ctl1XXX.ora, C:\Xbackup\ctl2XXX.ora)
저장하고 다시 구동한다.
SVRMGR>STARTUP PFILE=E:\XXX\initXXX.ora

로그파일의 추가

V$LOG Data Dictionary View를 참조하여 현재 로그 파일의 상태를 확인 한다.
SVRMGR>SELECT * FROM V$LOG;

log1b.rdo 와 log2b.rdo를 추가한다.
(주의:각 로그 그룹은 모두 동일한 수의 멤버 파일을 가져야 한다)

SVRMGR>ALTER DATABASE ADD LOGFILE MEMBER 'C:\Xbackup\log1b.rdo' TO GROUP 1;
SVRMGR>ALTER DATABASE ADD LOGFILE MEMBER 'C:\Xbackup\log2b.rdo' TO GROUP 2;
SVRMGR>SELECT * FROM V$LOGFILE;

로그파일의 삭제
SVGMGR>ALTER DATABASE XXX
DROP LOGFILE MEMBER 'C:\Xbackup\log2b.rdo';

* 로그 파일의 삭제는 실제 운영체제상의 물리적인 파일을 삭제하는 것이 아니라, 오라클 내부에서 삭제하려는 로그 파일에 대한 포인터와 파일 정의 등을 삭제하는 것이다. 따라서 물리적인 실제 로그 파일의 삭제는 운영체제상에서 이루어져야 한다.

로그 파일 이름 바꾸기
SVRMGR>ALTER DATABASE XXX RENAME FILE 'C:\log3.log' TO 'E:\log4.log';
운영체제상에서 먼저 이름을 바꾸려는 로그 파일을 복사하여 원하는 위치에 두고 원하는 이름으로 바꾼 뒤
ALTER DATABASE의 RENAME FILE 절을 사용한다.

로그 그룹 전체의 삭제
SVRMGR>ALTER DATABASE XXX DROP LOGFILE GROUP 2;

데이터 사전 뷰

Data Dictionary는 읽기 전용 참조 테이블 및 뷰들의 집합으로 데이터베이스에 대한 전반적인 정보를 제공한다.
- 오라클 서버의 각 사용자 이름
- 각 사용자에게 허가된 오라클 권한과 역할
- 오라클 데이터베이스 스키마 객체들의 이름과 정의
- 각 데이터베이스 객체에 관한 스페이스 사용
- 데이터베이스 구조
- 무결성 제약
- 데이터베이스 감사(Auditing)정보
- 오라클 데이터베이스 저장 함수 및 트리거(Triggers)

Data Dictionary Table의 생성

DBA의 권한을 가진 사용자로 로그인.
"sql.bsq" SQL 스크립트 파일의 실행
SVRMGR>@C:\Orawin95\Rdbms80\Adimn\sql.bsq
'@'는 이 파일을 실행하라는 서버관리자 명령어이다.

Data Dictionary View의 생성

자주 사용하는 뷰를 생성하기 위해
SVRMGR>@C:\Orawin95\Rdbms80\Adimn\catalog.sql
SVRMGR>@C:\Orawin95\Rdbms80\Adimn\catproc.sql

[표. 데이블 사전 뷰 및 테이블 관련 스크립트]

스크립트	설명
sql.bsq	베이스 데이터 사전 테이블 생성
catalog.sql	자주 사용되는 데이터 사전 뷰 생성
expvew.sql	Export/Import의 실행을 위한 데이터 사전 뷰 생성
audit.sql	Audit trail 과 이에 관계한 뷰 생성
blocking.sql	Locking 정보를 구하는 뷰 생성

데이터 사전 뷰의 확인

모든 데이터 사전 뷰는 SYS 사용자에게 종속되어 있으며 USER_XXX, DBA_XXX, ALL_XXX의 형태를 갖는다.

USER_XXX 뷰 : 한 특정 사용자에게 종속되어 있고 그 사용자가 조회 가능한 데이터 사전 뷰들을 나타낸다.
ALL_XXX 뷰 : 한 특정 사용자가 조회 가능한 모든 데이터 사전 뷰를 의미한다.(접근 권한이 있어야 한다.)
DBA_XXX 뷰 : DBA 권한을 가진 사용자만이 조회할 수 있는 데이터 사전 뷰로서 모든 오라클 데이터베이스 객체에 대한 정보를 볼수 있다.

사용자 확인 (DBA_USERS)
SQL>SELECT USERNAME, USER_ID FROM DBA_USERS;

사용자의 객체의 조회 (USER_OBJECTS)
SQL>SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, STATUS
FROM USER_OBJECTS;

모든 객체의 조회 (DBA_OBJECTS)
SQL>SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, STATUS
FROM USER_OBJECTS
WHERE OBJECT_NAME LIKE '%ROLLBACK%';

사용자 시노님의 조회 (USER_SYNONYMS)
SQL>SELECT * FROM USER_SYNONYMS;

여유공간의 조회 (DBA_FREE_SPACE)
SQL>SELECT * FROM DBA_FREE_SPACE;

데이터 파일 조회 (DBA_DATA_FILES)
SQL>SELECT FILE_NAME, BYTES, STATUS FROM DBA_DATA_FILES;

테이블스페이스 정보 조회 (DBA_TABLESPACES)
SQL>SELECT TABLESPACE_NAME, STATUS, CONTENTS
FROM DBA_TABLESPACES;

롤백 세그먼트 정보 조회 (DBA_ROLLBACK_SEGS)
리두 로그 버퍼의 데이터를 디스크 파일로 저장할 铁에 사용되는 데이터 파일의 세그먼트이다.
SQL>SELECT * SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS;