9장. 데이터베이스 구축

이 문서는 구루비에서 작성하였습니다.
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=1966791&
구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

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))

데이터베이스 링크 생성(DATABASE LINK)

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

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; <-- 데이터베이스 링크명

문서정보

이 문서는 구루비에서 작성하였습니다.
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=1966791&
구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

9장. 데이터베이스 구축

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

산출물

9.2 데이터베이스 생성

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

orcl.sh

CreateDB.sql

CreateDBFiles.sql

CreateDBCatalog.sql

postDBCreation.sql

9.3 테이블스페이스 생성

DDL 문장

테이블 스페이스 생성 확인

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

DDL 문장

사용자 생성 확인

역할 생성 DDL 문장

역할 생성 확인

권한 부여 DDL 문장

권한 부여 확인

9.5 오브젝트 생성

테이블 생성 DDL 문장

테이블 생성 확인

PK 확인

FK 확인

인덱스 생성 DDL 문장

인덱스 생성 확인

뷰 생성 DDL 문장

뷰 생성 확인

9.6 분산 환경 생성

원격 데이터베이스 리스너 기동

로컬 서버 Tnsnames.ora 파일 구성

데이터베이스 링크 생성(DATABASE LINK)

데이블 생성 DDL 문장

스냅샷 테이블 생성

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

문서정보

Labels