Oracle Database 생성 - Oracle Database saengseong

Database/Oracle

오라클 데이터베이스 생성

Youngs_ 2021. 11. 25. 09:01

오라클은 데이터베이스를 생성하기 위해서는 사용자를 만들고 생성한 사용자에 권한을 부여해야한다.

alter session set "_ORACLE_SCRIPT"=true;

오라클 12 이상 버전을 사용한다면 위 쿼리를 실행 한 후에 아래 쿼리를 실행하여야한다.

오라클 12 이상 버전은 유저를 생성할때 앞에 C##을 붙여야 하는데 위 쿼리는 앞에 C##을 붙이지 않아도 되게 해준다.

위 쿼리를 실행하지 않으면 'ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다.' 에러가 나올것이다.

위 쿼리를 실행하고싶지 않다면 CREATE USER "C##ADMIN" 과 같은 형식으로 사용자를 추가하면 된다.

1. 아래 코드를 사용해 ADMIN 사용자를 만들고 기본테이블 스페이스, 임시 테이블 스페이스를 지정해준다.

지정하지 않으면 system 계정과 이를 공유하게 되는데 이는 데이터 분리에 좋지않다고 한다.

사용자를 생성했으니 권한을 부여하여야 한다. 아직은 권한을 부여하지 않았기때문에 계정을 제대로 쓸수없다.

CREATE USER ADMIN
IDENTIFIED BY test
DEFAULT TABLESPACE USERS 
TEMPORARY TABLESPACE TEMP;

-- 해당 쿼리에서 아이디는 ADMIN, 비밀번호는 test이다.
-- CREATE USER 사용자명
-- IDENTIFIED BY 비밀번호
-- DEFAULT TABLESPACE USERS 기본 테이블 스페이스
-- TEMPORARY TABLESPACE 임시테이블 스페이스;

2. 아래 쿼리를 통해 ADMIN 계정에 권한을 부여한다.

GRANT CONNECT TO ADMIN;
GRANT RESOURCE TO ADMIN;
GRANT DBA TO ADMIN;

더보기

권한을 뺏을때는 아래 코드를 쓰면된다.

REVOKE CONNECT TO ADMIN;
REVOKE RESOURCE TO ADMIN;
REVOKE DBA TO ADMIN;

새 데이터베이스 접속을 만든다.

사용자이름에는 1번에서 만든 계정의 아이디, 비밀번호에는 1번에서 만든 계정의 비밀번호를 입력한다

320x100

Oracle Database에서 DBCA를 이용하지 않고 수동으로 CREATE DATABASE 명령어를 사용해서 데이터베이스를 생성하는 실습을 하려고합니다. 새로운 데이터베이스를 생성하면 새로운 SID를 지정하여 업무를 나눠 관리할 수 있습니다.

1. 환경변수 수정 oraenv 사용

Oracle 설치하는 과정에서 ORACLE_SID, ORACLE_HOME 환경변수를 설정하는 작업을 진행했을 것 입니다. Oracle Instance를 기동할때 어떤 데이터베이스로 연결해야하는지 알려주는 변수값이기 때문에 데이터베이스를 하나 더 생성한다면 간단하게 이 두가지 환경변수를 수정하고 다시 Instance를 기동한다면 변경된 데이터베이스의 정보로 접속이 가능합니다.

하지만 번갈아가면 여러 데이터베이스에 접속을 바꿔가면서 해야할때 환경변수를 재정의해주는 작업이 번거롭다. 이 문제를 해결하기 위해 Oracle에서 제공해주는 oraenv를 사용하면 좀 더 편한 방법으로 환경변수의 변경작업을 할 수 있습니다.

[oracle@host ~]$ . oraenv
ORACLE_SID = [orcl] ? prod
ORACLE_HOME = [/opt/oracle] ? /opt/oracle/app/product/11.2.0/dbhome_1
The Oracle base for ORACLE_HOME=/opt/oracle/app/product/11.2.0/dbhome_1 is /opt/oracle/app
[oracle@6host ~]$ echo $ORACLE_SID
prod
[oracle@host ~]$ echo $ORACLE_HOME
/opt/oracle/app/product/11.2.0/dbhome_1

oratab 파일 수정

oraenv를 사용해서 환경변수 변경작업이 가능하지만, ORACLE_HOME 경로같은 경우는 오타로 인해 잘못 타이필할 수 있을 뿐더러 이것도 귀찮은 작업이다. 그래서 /etc/oratab 파일을 열어 변수를 정의하듯 미리 변경할 정보를 저장해두면 더 간편하게 환경변수 수정이 가능합니다.

[oracle@host ~]$ vi /etc/oratab

# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
orcl:/opt/oracle/app/product/11.2.0/dbhome_1:N

orcl이라는 SID를 사용하는 데이터베이스는 사전에 dbca를 이용해서 생성했습니다. ORACLE_SID=prod로 추가 생성할 데이터베이스의 정보를 위와 같이 형식에 맞게 추가하겠습니다.

orcl:/opt/oracle/app/product/11.2.0/dbhome_1:N
prod:/opt/oracle/app/product/11.2.0/dbhome_1:N

oratab 파일을 수정하고 다시 oraenv 명령을 사용해서 prod로 변경해보겠습니다.

[oracle@host ~]$ . oraenv 
ORACLE_SID = [prod] ? prod
The Oracle base for ORACLE_HOME=/opt/oracle/app/product/11.2.0/dbhome_1 is /opt/oracle/app

ORACLE_SID 값만 입력했는데 ORACLE_HOME에 대한 정보를 자동으로 인식하여 변경 완료되었습니다.

2. pfile 생성

[oracle@host ~]$ cd $ORACLE_HOME/dbs
[oracle@host dbs]$ ls
hc_DBUA0.dat  hc_orcl.dat  init.ora  initorcl.ora  lkORCL  orapworcl  spfileorcl.ora
[oracle@host dbs]$ vi initprod.ora

db_name=prod
control_files='/opt/oracle/app/oradata/prod/control01.ctl'
sga_target=400M
remote_login_passwordfile='EXCLUSIVE'

Database Create는 nomount 상태에서 가능합니다. 파라미터 파일을 읽을 수 있는 상태까지 Database를 올리는 작업이 필요하여 pfile을 직접 생성하여 최소로 필요한 내용을 작성합니다. pfile은 $ORACLE_HOME/dbs 경로에 init<ORACLE_SID>.ora 형태로 파일이 존재해야합니다.

기본적으로 Parameter file을 열기 위해서는 db_name, control_files, shared_pool_size 그리고 db_cache_size 이 4개지 파라미터가 정의되어 있어야합니다. 하지만 sga_target 파라미터를 정의한다면 sga 메모리를 자동으로 할당하기 때문에 shared_pool_size와 db_cache_size의 정보를 제외할 수 있습니다.

remote_login_passwordfile은 필수적인 요소는 아니지만 Password fie이 하나의 데이터베이스에서 사용할 수 있도록 설정.

3. spfile 생성

[oracle@host ~]$ sqlplus / as sysdba

SQL> create spfile from pfile;

File created.

SQL> !ls $ORACLE_HOME/dbs
hc_DBUA0.dat  hc_orcl.dat  init.ora  initorcl.ora  initprod.ora  lkORCL  orapworcl  spfileorcl.ora  spfileprod.ora

pfile을 사용해서 실행할 수 있지만, 편의상 작성한 pfile로 spfile을 생성하고 Database를 실행할 계획입니다. spfile 생성 작업은 DB가 내려가있는 상태에서 만들 수 있고 생성 이후 spfile<ORACLE_SID>.ora 형태의 파일이 만들어진 것을 확인할 수 있습니다.

DB nomount 실행

SQL> startup nomount
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size		    2213936 bytes
Variable Size		  134219728 bytes
Database Buffers	  272629760 bytes
Redo Buffers		    8482816 bytes

Database 생성을 위해 nomount 상태로 DB를 실행합니다. 정상적으로 올라왔다면 생성한 pfile에 문제가 없다는 뜻입니다.

4. DB 생성

[oracle@host ~]$ vi /etc/oratab

DB 생성하면서 만들어질 datafile, redologfile들의 경로를 만들어줍니다.

[oracle@host ~]$ sqlplus / as sysdba

[oracle@host ~]$ vi /etc/oratab

USER SYS IDENTIFIED ... : SYS 유저 비밀번호 지정

USER SYSTEM IDENTIFIED ... : SYSTEM  유저 비밀번호 지정

CONTROLFILE :

REUSE 명령을 사용하기 위해 명시. LOGFILE GROUP 이나 DATAFILE들에서도 REUSE 옵션이 붙어 있습니다. 이 옵션은 만약 해당 파일이 이미 존재한다면 해당 파일 이름 그대로 사용하여 덮어써서 DB를 생성합니다.

LOGFILE GROUP : 로그 파일은 최소 2개의 그룹으로 지정해야합니다.

EXTENET MANAGEMENT LOCAL :

LOCAL 방식은 Tablespace에 대한 free space 공간을 datafile 자체에서 bitmap을 이용해서 관리하는 방법입니다. 다른방법으로 Tablespace의 free space 공간에 대한 정보를 data dictionary에서 관리하는 DICTIONARY 방식이 있지만 data dictionary에 대한 경합이 심해지고 성능저하의 원인으로 Oracle 8i부터 LOCAL 방식이 처음 소개되면서 이 방식으로 관리하도록 권장합니다.

DATAFILE :

        지정 없음 : SYSTEM Tablespace

        SYSAUX : SYSTEM Auxilary(보조)

        UNDO : 언두 Tablespace 지정

TEMPFILE : TEMPORARY Tablespace는 DEFAULT TEMPORARY TABLESPACE를 꼭 지정해줘야합니다.

CHARACTER SET, NATIONAL CHARACTER SET : 문자 인코딩 지정

DB Open 확인

[oracle@host ~]$ vi /etc/oratab

Instance가 Open 상태까지 올라왔는지 확인합니다.

여러 SID 사용 확인

환경변수 변경

[oracle@host ~]$ vi /etc/oratab

prod에 붙어서 작업하다가 orcl로 변경해서 작업하기 위해서 oraenv로 환경변수의 정보를 변경하고 로그인해줍니다.

orcl instance startup

[oracle@host ~]$ vi /etc/oratab

orcl instance를 기동해줍니다.

[oracle@host ~]$ vi /etc/oratab

orcl에서도 마찬가지로 instance의 open 상태를 확인합니다.

[oracle@host ~]$ vi /etc/oratab

주요 프로세스중 하나인 smon의 프로세스가 기동중인지 확인합니다. prod와 orcl모두 올라와 있다면 각각 데이터베이스에 연결된 인스턴스를 올려준 것을 확인할 수 있습니다.

후속작업

데이터베이스의 생성은 완료되었고 이상없이 돌아갑니다. 하지만 수동 생성했을 때 Data Dictionary 조회작업이나, Oracle에서 제공하는 패키지들이 실행되지 않습니다. 이런 문제들을 해결하기 위해 몇 가지 후속작업을 해줘야합니다. 후속작업에 대한 내용은 아래 링크를 통해 진행할 수 있습니다.

[Oracle] 데이터베이스 생성 후속 작업, Data Dictionary 조회 안되는 것 문제 해결

데이터베이스 생성 후속 작업 이전 글에서 데이터베이스 수동생성하는 작업을 진행했습니다. 수동으로 작업을 진행 했을 때 문제없이 데이터베이스를 사용할 수 있지만, 몇가지 안되는 기능들�