Create Database 커맨드로 새로운 데이터베이스 생성하기

과제: SID가 PROD3인 데이터베이스를 생성할 것이다.

 

환경: Oracle Linux 7.9, Oracle Database 19c

 

 

 

1.  bash_profile의 ORACLE_SID를 수정하고, pfile (init<SID>.ora)를 생성한다.

[oracle@srv1 ~]$ export ORACLE_SID=PROD3
[oracle@srv1 ~]$
[oracle@srv1 ~]$ cd $ORACLE_HOME/dbs
[oracle@srv1 dbs]$
[oracle@srv1 dbs]$ cat initPROD3.ora
db_name='PROD3'
control_files=('/u01/app/oracle/oradata/PROD3/control01.ctl', '/u01/app/oracle/oradata/PROD3/control2.ctl')
[oracle@srv1 dbs]$
[oracle@srv1 dbs]$

 

 

2. 데이터베이스를 열고, pfile을 spfile로 바꾼다.

[oracle@srv1 dbs]$ mkdir -p /u01/app/oracle/oradata/PROD3
[oracle@srv1 dbs]$
[oracle@srv1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on ﾈｭ 9ｿ・12 11:50:21 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


ｴﾙﾀｽｿ｡ ﾁ｢ｼﾓｵﾊ:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create spfile from pfile;

 

 

3. 오라클 Docs을 참고하며, CREATE DATABASE문을 작성한다.

CREATE DATABASE PROD3
	USER SYS IDENTIFIED BY oracle
	USER SYSTEM IDENTIFIED BY oracle
	LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD3/redo01.log') SIZE 100M,
		GROUP 2 ('/u01/app/oracle/oradata/PROD3/redo02.log') SIZE 100M
	MAXLOGFILES 5
	MAXLOGMEMBERS 5
	MAXDATAFILES 100
	MAXINSTANCES 1
	CHARACTER SET AL32UTF8
	DATAFILE '/u01/app/oracle/oradata/PROD3/system01.dbf' SIZE 300M
	EXTENT MANAGEMENT LOCAL
	SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD3/sysaux01.dbf' SIZE 200M
	DEFAULT TEMPORARY TABLESPACE temp
		TEMPFILE '/u01/app/oracle/oradata/PROD3/temp01.dbf' SIZE 20M
	UNDO TABLESPACE undotbs01
		DATAFILE '/u01/app/oracle/oradata/PROD3/undotbs01.dbf' SIZE 50M;

 

 

 

4. 생성 완료 확인 후, 실제 데이터베이스가 생성된 게 맞는지 확인한다.

SQL> SHOW PARAMETER INSTANCE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
active_instance_count                integer
cluster_database_instances           integer     1
instance_abort_delay_time            integer     0
instance_groups                      string
instance_mode                        string      READ-WRITE
instance_name                        string      PROD3
instance_number                      integer     0
instance_type                        string      RDBMS
open_links_per_instance              integer     4
parallel_instance_group              string
SQL>
SQL> SELECT OPEN_MODE FROM V$DATABASE;

OPEN_MODE
--------------------
READ WRITE

SQL>

 

 

 

 

 

 

 

 

 

이미지 정보 보기