안녕하세요 Surmin입니다 !
이번 포스팅에서는 오라클 데이터베이스를 생성하는 다양한 방법 중, dbca 와 같은 도구 대신 SQL의 CREATE DATABASE 문을 직접 사용하여 수동으로 데이터베이스를 생성하는 방법에 대해 다루고자 합니다. 이 내용이 오라클 데이터베이스 생성 과정을 이해하고 실무에 적용하는 데 유용한 참고가 되기를 바랍니다.
감사합니다.
Test 환경
- Red Hat Enterprise Linux release 8.4 / Oracle 19c
Test
#상황에 따라 다를 수 있음
cd $ORACLE_BASE/product/
mkdir TESTDB
#aud 위하여
mkdir -p $ORACLE_BASE/admin/TESTDB/adump
#dump지정을 위하여
mkdir -p $ORACLE_BASE/admin/TESTDB/dpdump
#데이타 파일 위하여
mkdir -p $ORACLE_BASE/oradata/TESTDB
#생성한거 확인
[oracle@ORA19C product]$ ls
19c TESTDB
#ORACLE_HOME/dbs 이동
#패스워드 파일생성
[oracle@ORA19C dbs]$ orapwd file=orapwTESTDB format=12 force=y password=oracle
#init파일 생성
#여기서는 기존 DB의 init 파일 복사 후 수정 그리고 메모리 자원에 맞게 수정을 해줍니다.
[oracle@ORA19C dbs]$ cp -av initTARGET.ora initTESTDB.ora
'initTARGET.ora' -> 'initTESTDB.ora'
==
*.audit_file_dest='/app/oracle/admin/TESTDB/adump'
*.audit_trail='NONE'
*.compatible='19.0.0'
#필수 파라미터
*.control_files='/app/oracle/oradata/TESTDB/control01.ctl','/app/oracle/oradata/TESTDB/control02.ctl'
#필수 파라미터
*.shared_pool_size=164m
#필수 파라미터
db_cache_size=50m
*.db_block_size=8192
*.db_files=1000
*.db_name='TESTDB'
*.db_securefile='PREFERRED'
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA19CXDB)'
*.local_listener='LISTENER_TESTDB'
*.log_archive_dest_1=''
*.log_archive_format='arch_%t_%s_%r.arc'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=389m
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1167m
*.undo_tablespace='UNDOTBS1'
==
export ORACLE_SID=TESTDB
sqlplus "/as sysdba"
#DB 생성은 파라미터를 읽을 수 있는 상태인 nomount 상태에서 가능하다.
startup nomount
#이걸로 교체가능 : CHARACTER SET AL32UTF8
CREATE DATABASE TESTDB
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE
GROUP 1 ('/app/oracle/oradata/TESTDB/redo01.log') SIZE 100M,
GROUP 2 ('/app/oracle/oradata/TESTDB/redo02.log') SIZE 100M,
GROUP 3 ('/app/oracle/oradata/TESTDB/redo03.log') SIZE 100M
DATAFILE '/app/oracle/oradata/TESTDB/system01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
SYSAUX DATAFILE '/app/oracle/oradata/TESTDB/sysaux01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
DEFAULT TABLESPACE USERS
DATAFILE '/app/oracle/oradata/TESTDB/users01.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
UNDO TABLESPACE UNDOTBS1
DATAFILE '/app/oracle/oradata/TESTDB/undotbs01.dbf' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '/app/oracle/oradata/TESTDB/temp01.dbf' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
CHARACTER SET KO16KSC5601
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL;
#아래의 명령어들로 DB가 잘 생성되었는지 확인 합니다.
SQL>select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TESTDB READ WRITE
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> 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
exit
cd $ORACLE_HOME/rdbms/admin/
sqlplus / as sysdba
#조회혹은 문제(인벨리드 등)가 있을경우 아래의 패키지 실행한다.
SET LINESIZE 200
SET PAGESIZE 100
COLUMN OWNER FORMAT A10
COLUMN OBJECT_NAME FORMAT A20
COLUMN OBJECT_TYPE FORMAT A15
COLUMN STATUS FORMAT A10
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
FROM DBA_OBJECTS
WHERE OWNER = 'SYS' AND OBJECT_NAME = 'STANDARD';
=====================
catalog.sql, catproc.sql, pupbld.sql 실행
데이터베이스를 수동 생성했을 때 Data Dictionary 조회나, Oracle에서 제공하는 패키지들이 실행되지 않는
문제가 발생한다. 따라서 몇가지 후속작업을 해주어야 한다. 이 후속작업들은 시간이 꽤 걸린다.
Data Dictionary를 조회할 수 있는 view를 생성해주는 파일이다. 이 파일을 실행해주어야 Data Dictionary
조회가 가능하다. 이 파일은 $ORACLE_HOME/rdbms/admin에 위치한다.
@catalog.sql
oracle이 제공하는 패키지들을 생성하기 위해 실행해주어야 하는 파일이다.
또한 위에서 catalog.sql 파일을 실행한 후에도 조회되지 않는 딕셔너리 뷰들을 생성해준다.
@catproc.sql
나중에 새로운 User를 생성하여 접속했을 때 "Error accessing PRODUCT_USER_PROFILE"이라는 경고가 발생한다.
이를 해결하기 위해 system 유저로 접속하여 pupbld.sql 파일을 실행해주어야 한다.
@pupbld.sql
#확인
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
FROM DBA_OBJECTS
WHERE OWNER = 'SYS' AND OBJECT_NAME = 'STANDARD';
#invalid 상태인 오브젝트가 있는지 확인
select object_name from dba_objects where status='INVALID' and owner='SYS';
#invalid 잡아주기
@utlrp.sql
#invalid 상태인 오브젝트가 있는지 확인
select object_name from dba_objects where status='INVALID' and owner='SYS';
-- 컬럼 형식 설정
COLUMN PARAMETER FORMAT A30
COLUMN VALUE FORMAT A40
-- 페이지 설정
SET PAGESIZE 1000
SET LINESIZE 100
#NLS확인
SELECT * FROM nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET KO16KSC5601
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
20 rows selected.
SQL> SELECT * FROM v$nls_parameters;
PARAMETER VALUE CON_ID
------------------------------ ---------------------------------------- ----------
NLS_LANGUAGE AMERICAN 0
NLS_TERRITORY AMERICA 0
NLS_CURRENCY $ 0
NLS_ISO_CURRENCY AMERICA 0
NLS_NUMERIC_CHARACTERS ., 0
NLS_CALENDAR GREGORIAN 0
NLS_DATE_FORMAT DD-MON-RR 0
NLS_DATE_LANGUAGE AMERICAN 0
NLS_CHARACTERSET KO16KSC5601 0
NLS_SORT BINARY 0
NLS_TIME_FORMAT HH.MI.SSXFF AM 0
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 0
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 0
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 0
NLS_DUAL_CURRENCY $ 0
NLS_NCHAR_CHARACTERSET AL16UTF16 0
NLS_COMP BINARY 0
NLS_LENGTH_SEMANTICS BYTE 0
NLS_NCHAR_CONV_EXCP FALSE 0
#설치된 컴포넌트 확인
SQL> COLUMN comp_name FORMAT A40
COLUMN version FORMAT A15
COLUMN status FORMAT A15
SET LINESIZE 100
SET PAGESIZE 1000
#확인 시 오라클설치에 필요한 필수 컴퍼넌트만 설치된것을 확인함
#dbca을 통하여 보면 아무것도 체크 안되어 있는것을 볼수가 있다.
SELECT comp_name, version, status FROM dba_registry;
COMP_NAME VERSION STATUS
---------------------------------------- --------------- ---------------
Oracle Database Catalog Views 19.0.0.0.0 VALID
Oracle Database Packages and Types 19.0.0.0.0 VALID
Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
Oracle XML Database 19.0.0.0.0 VALID
--
Oracle Database Catalog Views (VALID):
데이터 딕셔너리 뷰 컴포넌트가 올바르게 설치되고 작동 중
이 컴포넌트는 데이터베이스 메타데이터에 접근하는 데 필요한 핵심 뷰들을 제공함
Oracle Database Packages and Types (VALID):
내장 PL/SQL 패키지 및 타입이 올바르게 설치되고 작동 중
DBMS_* 패키지, UTL_* 패키지 등 Oracle의 기본 프로그래밍 인터페이스를 포함
Oracle Real Application Clusters (OPTION OFF):
RAC 컴포넌트는 설치되어 있지만 활성화되지 않은 상태
"OPTION OFF"는 이 기능이 라이센스 또는 구성상의 이유로 비활성화되어 있음을 의미
Oracle XML Database (VALID):
XML DB 컴포넌트가 올바르게 설치되고 작동 중
XML 데이터 저장, 검색 및 처리 기능을 제공
모든 필수 컴포넌트가 VALID 상태이므로, 데이터베이스는 정상적으로 작동할 수 있습니다. "OPTION OFF" 상태인 RAC는 선택적 기능이며, 필요한 경우 별도의 라이센스와 구성을 통해 활성화할 수 있습니다.
--
#위에 값들 맞는지 확인 후 sp파일 생성 및 교체
create spfile from pfile;
shutdown immediate;
startup;
#확인
SQL> SHOW PARAMETER spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /app/oracle/product/19c/dbs/sp
fileTESTDB.ora
#alter.log 위치확인
select value from v$diag_info where name='Diag Trace' ;
#샘플 테이블스페이스 생성 테스트 확인 시 문제없음을 확인
SQL> CREATE TABLESPACE sample_tbs
DATAFILE '/app/oracle/oradata/TESTDB/sample_tbs01.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 50M MAXSIZE 500M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
ora19c:/app/oracle/oradata/TESTDB @TESTDB] ls
control01.ctl control02.ctl redo01.log redo02.log redo03.log sample_tbs01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbforatab 설정
vi /etc/oratab
==
TESTDB:/app/oracle/product/19c:N
==- 리스너 설정(동적)
- 포트는 1522로 합니다
- 당연한 말이지만 같은 포트인 1521로하면 포트충돌 발생합니다.
- 현업에서는 hostname 다르게 설정 하지만 여기서는 테스트라서 같게 진행 합니다.
- 포트는 1522로 합니다
#listener.ora 설정
##작업전 cp 이용하여 백업 후 진행 합시다. ##
ora19c:/app/oracle/product/19c/network/admin @TESTDB] vi listener.ora
# listener.ora Network Configuration File: /app/oracle/product/19c/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
LISTENER_TESTDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
#tnsnames.ora 설정
##작업전 cp 이용하여 백업 후 진행 합시다. ##
ora19c:/app/oracle/product/19c/network/admin @TESTDB] cat tnsnames.ora
# tnsnames.ora Network Configuration File: /app/oracle/product/19c/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORA19C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = ora19c))
)
TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1522))
(CONNECT_DATA = (SERVICE_NAME = TESTDB))
)
- 기본 포트가 아닌 다른 포트를 이용한 경우는 데이터베이스 정보가 등록이 안된다.
- 기본 이름으로 쓰면서 포트를 다른 걸로 한다고하면 ALTER SYSTEM SET local_listener 작업을 해줘야만 DB에서 포트가 변경된것을 인식한다.
기본 리스너로 원복하고 싶으면?
SQL> alter system set local_listener='';
SQL> ALTER SYSTEM register;ALTER SYSTEM SET local_listener = '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1522)))'; # 이부분은 listener.ora부분임 혹은 lsnrctl status 출력에서 복붙해도 됩니다.
alter system set local_listener='LISTENER_TESTDB' scope=both;
alter system register;
show parameter local_listener#확인
ora19c:/app/oracle/product/19c/dbs @ora19c] lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 01-MAY-2025 23:24:19
Copyright (c) 1991, 2024, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 01-MAY-2025 23:17:47
Uptime 0 days 0 hr. 6 min. 32 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/oracle/product/19c/network/admin/listener.ora
Listener Log File /app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ora19c" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "ora19cXDB" has 1 instance(s).
Instance "ora19c", status READY, has 1 handler(s) for this service...
The command completed successfully
ora19c:/app/oracle/product/19c/dbs @ora19c] lsnrctl status LISTENER_TESTDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 02-MAY-2025 00:05:48
Copyright (c) 1991, 2024, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER_TESTDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 01-MAY-2025 23:23:53
Uptime 0 days 0 hr. 41 min. 54 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/oracle/product/19c/network/admin/listener.ora
Listener Log File /app/oracle/diag/tnslsnr/ora19c/listener_testdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "ORA19CXDB" has 1 instance(s).
Instance "TESTDB", status READY, has 1 handler(s) for this service...
Service "TESTDB" has 1 instance(s).
Instance "TESTDB", status READY, has 1 handler(s) for this service...
The command completed successfully- Test를 위하여 방화벽도 OPEN
sudo firewall-cmd --permanent --add-port=1522/tcp
sudo firewall-cmd --permanent --add-port=1521/tcp
sudo firewall-cmd --reload- 접속 Test
- export,import 경로설정
SET LINESIZE 150
SET PAGESIZE 50
SET COLSEP ' | '
SET UNDERLINE ON
COLUMN directory_name FORMAT A30
COLUMN directory_path FORMAT A80
#모든 디렉토리 객체 확인
SELECT directory_name, directory_path
FROM dba_directories
ORDER BY directory_name;
DIRECTORY_NAME | DIRECTORY_PATH
------------------------------ | --------------------------------------------------------------------------------
DATA_PUMP_DIR | /app/oracle/product/19c/rdbms/log/ #확인 시 다른건 괜찮은데 DATA_PUMP_DIR만 이상함 변경해 주자
DBMS_OPTIM_ADMINDIR | /app/oracle/product/19c/rdbms/admin
DBMS_OPTIM_LOGDIR | /app/oracle/product/19c/cfgtoollogs
JAVA$JOX$CUJS$DIRECTORY$ | /app/oracle/product/19c/javavm/admin/
OPATCH_INST_DIR | /app/oracle/product/19c/OPatch
OPATCH_LOG_DIR | /app/oracle/product/19c/rdbms/log
OPATCH_SCRIPT_DIR | /app/oracle/product/19c/QOpatch
ORACLE_BASE | /app/oracle
ORACLE_HOME | /app/oracle/product/19c
ORACLE_OCM_CONFIG_DIR | /app/oracle/product/19c/ccr/state
ORACLE_OCM_CONFIG_DIR2 | /app/oracle/product/19c/ccr/state
XMLDIR | /app/oracle/product/19c/rdbms/xml
XSDDIR | /app/oracle/product/19c/rdbms/xml/schemaCREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/app/oracle/admin/TESTDB/dpdump';
!expdp system/oracle dumpfile=full.dmp directory=DATA_PUMP_DIR full=y
#확인
ora19c:/app/oracle/admin/TESTDB/dpdump @TESTDB] ls
export.log full.dmp
#일반계정으로 할경우
RANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO 스키마; #스키마(사용자) - 변화량 확인
- AMM으로 인하여 초기 설정으로부터 얼마나 메모리를 조정했는지 확인 해보자.
COLUMN name FORMAT A30
COLUMN size_mb FORMAT 999,999.99
SELECT name, bytes/1024/1024 size_mb
FROM v$sgainfo
ORDER BY size_mb DESC;
NAME SIZE_MB
------------------------------ -----------
Maximum SGA Size 1,168.00
Buffer Cache Size 656.00
Shared Pool Size 448.00
Startup overhead in Shared Poo 340.77
l
Shared IO Pool Size 64.00
Streams Pool Size 32.00
Large Pool Size 16.00
Granule Size 16.00
Fixed SGA Size 8.52
NAME SIZE_MB
------------------------------ -----------
Redo Buffers 7.47
Data Transfer Cache Size .00
Free SGA Memory Available .00
In-Memory Area Size .00
Java Pool Size .00
14 rows selected.
참조