데이터베이스 설계와 구축(개정판) (2009년)
목록
데이터베이스 구축 0 0 44,634

by 구루비스터디 물리설계 [2009.05.18]

9.1 데이터베이스 구축을 위한 사전 준비

  • 데이터베이스 환경 정의서
  • 용량 산정서
  • 각종 오브젝트 설계서
  • 분산 환경 설계서(설치하고자 하는 데이터베이스가 분산을 고려하지 않으면 제외) |


산출물

  • ERD
  • 테이블 정의서
  • 인덱스 정의서
  • 뷰 정의서
  • 테이블스페이스 용량 산정서
  • 인덱스스페이스 용량 산정서
  • 데이터파일 용량 산정서
  • 분산 설계서
  • 데이터베이스 환경 설계서
  • 시스템파일 용량 산정서
  • 데이터베이스 환경 설계서와 시스템파일 용량 산정서는 프로젝트를 진행할 경우 공식적인 산출물이라기보다 대상이 되는 데이터베이스의 특징과 버젼에 따라 구축하면서 정리하는 경우도 많다.


9.2 데이터베이스 생성

스크립트로 생성(Manually Creating).

orcl.sh

#!/bin/sh
mkdir /backup/archive/ORCL
mkdir /oracle/admin/ORCL/bdump
mkdir /oracle/admin/ORCL/cdump
mkdir /oracle/admin/ORCL/create
mkdir /oracle/admin/ORCL/pfile
mkdir /oracle/admin/ORCL/udump
mkdir /oradata/ORCL
setenv ORACLE_SID ORCL
echo Add this entry in the oratab: ORCL:/oracle/product/9.2.0:Y
/oracle/product/9.2.0/bin/orapwd file=/oracle/product/9.2.0/dbs/orapwORCL password=change_on_install
/oracle/product/9.2.0/bin/sqlplus /nolog @/oracle/admin/ORCL/scripts/CreateDB.sql
/oracle/product/9.2.0/bin/sqlplus /nolog @/oracle/admin/ORCL/scripts/CreateDBFiles.sql
/oracle/product/9.2.0/bin/sqlplus /nolog @/oracle/admin/ORCL/scripts/CreateDBCatalog.sql
/oracle/product/9.2.0/bin/sqlplus /nolog @/oracle/admin/ORCL/scripts/postDBCreation.sql |


CreateDB.sql


connect SYS/change_on_install as SYSDBA
set echo on
spool /oracle/product/9.2.0/assistants/dbca/logs/CreateDB.log
startup nomount pfile="/oracle/admin/ORCL/scripts/init.ora";
CREATE DATABASE ORCL
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/oradata/ORCL/system01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/ORCL/temp01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/oradata/ORCL/undotbs01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET KO16KSC5601
NATIONAL CHARACTER SET UTF8
LOGFILE GROUP 1 ('/oradata/ORCL/redo01.log') SIZE 102400K,
GROUP 2 ('/oradata/ORCL/redo02.log') SIZE 102400K,
GROUP 3 ('/oradata/ORCL/redo03.log') SIZE 102400K;
spool off
exit;


CreateDBFiles.sql

connect SYS/change_on_install as SYSDBA
set echo on
spool /oracle/product/9.2.0/assistants/dbca/logs/CreateDBFiles.log
CREATE TABLESPACE "INDX" LOGGING DATAFILE '/oradata/ORCL/indx01.dbf' SIZE 50M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;
CREATE TABLESPACE "TOOLS" LOGGING DATAFILE '/oradata/ORCL/tools01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT  320K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;
CREATE TABLESPACE "USERS" LOGGING DATAFILE '/oradata/ORCL/users01.dbf' SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;
spool off
exit;


CreateDBCatalog.sql

connect SYS/change_on_install as SYSDBA
set echo on
spool /oracle/product/9.2.0/assistants/dbca/logs/CreateDBCatalog.log
@/oracle/product/9.2.0/rdbms/admin/catalog.sql;
@/oracle/product/9.2.0/rdbms/admin/catexp7.sql;
@/oracle/product/9.2.0/rdbms/admin/catblock.sql;
@/oracle/product/9.2.0/rdbms/admin/catproc.sql;
@/oracle/product/9.2.0/rdbms/admin/catoctk.sql;
@/oracle/product/9.2.0/rdbms/admin/owminst.plb;
connect SYSTEM/manager
@/oracle/product/9.2.0/sqlplus/admin/pupbld.sql;
connect SYSTEM/manager
set echo on
spool /oracle/product/9.2.0/assistants/dbca/logs/sqlPlusHelp.log
@/oracle/product/9.2.0/sqlplus/admin/help/hlpbld.sql helpus.sql;
spool off
spool off
exit;


postDBCreation.sql

connect SYS/change_on_install as SYSDBA
set echo on
spool /oracle/product/9.2.0/assistants/dbca/logs/postDBCreation.log
@/oracle/product/9.2.0/rdbms/admin/utlrp.sql;
shutdown ;
startup mount pfile="/oracle/admin/ORCL/scripts/init.ora";
alter database archivelog;
alter database open;
alter system archive log start;
shutdown ;
startup pfile="/oracle/admin/ORCL/scripts/init.ora";
exit;
  • DBCA(Database Configuration Assistants) 그래픽 툴 이용.


9.3 테이블스페이스 생성

  • 테이블 스페이스는 설계 단계에서 정의한 각종 오브젝트(테이블, 인덱스, 뷰등)가 실제 데이터파일에 저장되기 위한 공간을 정의하는 데이터베이스 오브젝트.
DDL 문장

CREATE TABLESAPCE TS001
DATAFILE 'D:\TEST\DB1\DF001.DBF' SIZE 55M
DEFAULT STORAGE ( INITIAL 1024K NEXT 512K MAXEXTENTS 128 PCTINCREASE 0);



CREATE TABLESAPCE IS001
DATAFILE 'D:\TEST\DB1\IF001.DBF' SIZE 30M
DEFAULT STORAGE ( INITIAL 512K NEXT 256K MAXEXTENTS 128 PCTINCREASE 0);


테이블 스페이스 생성 확인

SELECT TABLESPACE_NAME, FILE_NAME AS DATAFILE, (BYTES/1024) AS SZ_KB
FROM DBA_DATA_FILES;


9.4 사용자 및 역할과 권한 지정

  • 데이터베이스를 사용할 수 있도록 사용자 계정을 생성하고 역할과 권한을 지정.
DDL 문장

CREATE USER BONSA01 IDENTIFIED BY BONSA01
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;


사용자 생성 확인

SELECT USERNAME, DEFAULT_TABLESPACED, TEMPORARY_TABLESPACE, PROFILE
FROM DBA_USERS;


역할 생성 DDL 문장

CREATE ROLE BONSA_ROLE NOT IDENTIFIED;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, ALTER ROLLBACK SEGMENT TO BONSA_ROLE;


역할 생성 확인

SELECT ROLE FROM DBA_ROLES;


권한 부여 DDL 문장

GRANT BONSA_ROLE TO BONSA01;


권한 부여 확인

SELECT GRANTEE, GRANTED_ROLE
FROM DBA_ROLE_PRIVS;


9.5 오브젝트 생성

테이블 생성 DDL 문장

CREATE TABLE DEPT (
	DEPTNO			NUMBER(2) NOT NULL,
	DEPTNM			VARCHAR2(14) NOT NULL,
	LOC				VARCHAR2(14) NULL,
	CHARGE			VARCHAR2(20) NULL
) TABLESPACE TS001
STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0 );
ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY ( DEPTNO );


테이블 생성 확인

SELECT SEGMENT_NAME, ROUNT(INITIAL_EXTENT/1024, 0) INI_KB,
	ROUND(NEXT_EXTENT/1024, 0) NXT_KB, ROUND(BYTES/1024, 0) TOT_KB,
	PCT_INCREASE PCT, EXTENTS, TABLESPACE_NAME
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE';


PK 확인

SELECT TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P';


FK 확인

SELECT CONSTRAINT_NAME, R_CONSTRAINT_NAME, STATUS
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R';


인덱스 생성 DDL 문장

CREATE INDEX I_EMP01 ON EMP ( DEPTNO ASC ) TABLESPACE IS001;


인덱스 생성 확인

SELECT SEGMENT_NAME, ROUND(INITIAL_EXTENT/1024, 0) INI_KB,
	ROUND(NEXT_EXTENT/1024, 0) NXT_KB, ROUND(BYTES/1024, 0) TOT_KB,
	PCT_INCREASE PCT, EXTENTS, TABLESPACE_NAME
FROM USER_SEGMENTS
WHERE SGEMENT_TYPE = 'INDEX';

SELECT TABLE_NAME, INDEX_NAME
FROM USER_INDEXES;


뷰 생성 DDL 문장

CREATE VIEW V_EMP AS
	SELECT EMPNO, EMPNM, DEPTNO
	FROM EMP;


뷰 생성 확인

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'VIEW';


9.6 분산 환경 생성

  • 연결 대상인 데이터베이스 쪽(Remote) 서버에 리스너가 기동되어야 한다.
  • 연결하고자 하는 쪽에서는 Net8 클라이언트가 설치되어 있으면서 Tnsnames.ora 파일이 구성되어야 한다.
  • 로컬 데이터베이스와 연결 데이터베이스간 데이터베이스 링크를 설정하면 분산 환경 구성이 완료된다.
원격 데이터베이스 리스너 기동
  • lsnrctl start


로컬 서버 Tnsnames.ora 파일 구성

$ORACLE_HOME/NETWORK/ADMIN/tnsnames.ora
BONSA = 
	(DESCRIPTION =
		(ADDRESS_LIST = 
			(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
		(CONNECT_DATA = 
			(SERVICE_NAME = BONSA))


  • 데이터베이스 링크는 참조를 하려는 쪽인 로컬에서 데이터베이스 링크 생성 권한을 가진 사용자가 생성.

CREATE PUBLIC DATABASE LINK H2U1
CONNECT TO BONSA01 INDENTIFIED BY BONSA01
USING 'BONSA';


데이블 생성 DDL 문장
  • 본사 테이블 생성 DDL 문장
  • 분산 환경 설계서에서 각 테이블의 어느 위치에 생성되는지 확인
  • 테이블 설계서와 테이블 스페이스 용량 산정서를 참조하여 DDL 문장을 생성


스냅샷 테이블 생성
  • 본사 환경 설정 (스냅샷 로그 테이블 생성)

CREATE SNAPSHOT LOG ON EMP
	TABLESPACE TS001
	STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0);


지사 환경 설정 ( 스냅샷 생성 )

CREATE SNAPSHOT EMP
	TABLESPACE TS002 STORAGE(MINEXTENTS 1 PCTINCREASE 0)
	USING INDEX TABLESPACE IS002 STORAGE (MINEXTENTS 1 PCTINCREASE 0)
REFRESH FAST
START WITH SYSDATE NEXT TRUNC(SYSDATE) + 1 + (20/24) <- REFRESH 주기
AS SELECT * FROM EMP@BONSA; <-- 데이터베이스 링크명
"구루비 데이터베이스 스터디모임" 에서 2009년에 "데이터베이스 설계와 구축(개정판)" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3761

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입