1. 사전 작업
spfile 로 운영중인지 pfile 로 운영중인지 확인
SQL> show parameter spfile;
- pfile 로 운영중
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
spfile string
- spfile 로 운영중
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string ?/dbs/spfile@.ora
오라클이 spfile 로 운영되는 경우에는 pfile로 운영되도록 변경한다.
SQL> create pfile from spfile;
위 명령을 실행하면 pfile이 자동 생성(/opt/oracle/product/10.2.0/dbs/initSID.ora 파일 생성)
2. db 이름(SID) 결정(1-8자) : NEW_DB
3. 환경변수 ORACLE_SID 변경
$ export ORACLE_SID=NEW_DB
4. /opt/oracle/oradata/NEW_DB 디렉토리 생성
$ mkdir /opt/oracle/oradata/NEW_DB
$ mkdir /opt/oracle/oradata/NEW_DB/adump
$ mkdir /opt/oracle/oradata/NEW_DB/bdump
$ mkdir /opt/oracle/oradata/NEW_DB/cdump
$ mkdir /opt/oracle/oradata/NEW_DB/dpdump
$ mkdir /opt/oracle/oradata/NEW_DB/udump
5. pfile 생성
$ cp /opt/oracle/admin/orcl/pfile/init.ora.xxxxxxxx /opt/oracle/product/10.2.0/dbs/
$ cd /opt/oracle/product/10.2.0/dbs/
$ mv init.ora.xxxxxxxx initNEW_DB.ora (pfile명 : "init" + SID명 + ".ora" = initNEW_DB.ora)
6. pfile 파라미터 수정
$ vi /opt/oracle/product/10.2.0/dbs/initNEW_DB.ora
db_name=NEW_DB
audit_file_dest=/opt/oracle/oradata/NEW_DB/adump
background_dump_dest=/opt/oracle/oradata/NEW_DB/bdump
core_dump_dest=/opt/oracle/oradata/NEW_DB/cdump
user_dump_dest=/opt/oracle/oradata/NEW_DB/udump
control_files=(
"/opt/oracle/oradata/NEW_DB/control01.ctl",
"/opt/oracle/oradata/NEW_DB/control02.ctl",
"/opt/oracle/oradata/NEW_DB/control03.ctl"
)
7. database 생성
- nomount 단계
$ sqlplus "/as sysdba"
SQL> startup nomount;
SQL> show parameter db_name; (NEW_DB가 나오면 정상)
- database 생성
create database JDSINFO datafile '/opt/oracle/oradata/jdsinfo/SYSTEM01.dbf' size 200m
logfile '/opt/oracle/oradata/jdsinfo/REDO01.LOG' size 10m,
'/opt/oracle/oradata/jdsinfo/REDO2.LOG' size 10m
character set ko16ksc5601
SYSAUX datafile '/opt/oracle/oradata/jdsinfo/SYSAUX01.dbf' size 200m
undo tablespace UNDOTBS1 datafile '/opt/oracle/oradata/jdsinfo/UNDO01.dbf' size 100m
default temporary tablespace TEMP tempfile '/opt/oracle/oradata/jdsinfo/TEMP01.dbf' size 100m;
- USERS 테이블 스페이스 생성
create tablespace USERS
datafile '/opt/oracle/oradata/jdsinfo/USERS01.dbf' size 100m
- user 생성
create user jdsinfo identified by infojds
default tablespace USERS;
- user 권한
grant dba to jdsinfo;
SQL> shutdown (마운트 해제)
8. data dictionary 생성
SQL> startup
SQL> @/opt/oracle/product/10.2.0/rdbms/admin/catalog.sql;
SQL> @/opt/oracle/product/10.2.0/rdbms/admin/catproc.sql;
9. /etc/oratab SID추가(자동실행)
NEW_DB:/opt/oracle/product/10.2.0:Y
10. 리스너 추가
$ vi /opt/oracle/product/10.2.0/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /opt/oracle/product/10.2.0)
(PROGRAM = extproc)
)
# 추가
(SID_DESC =
(SID_NAME = NEW_DB)
(ORACLE_HOME = /opt/oracle/product/10.2.0)
)
# 추가 끝
)
[출처] 리눅스 오라클10g database 추가|작성자 귀차니즘