수동으로 Database 생성 <Windows>

수동으로 Database 생성 <Windows XP SP3>
Oracle Version : 11.1.0.6.0
생성할 Database 이름 : mandb

Path :
 Oracle_home=c:\oracle\product\11.1.0\db_1
 Oracle_base=c:\oracle

1. 폴더 생성 ( control file, log file, database file이 저장될 폴더 )

%oracle_base%\oradata\mandb

2. 환경 변수 설정

os> set oracle_sid=mandb

3. 파라미터 파일 생성

파일명 : %oracle_home%\database\initmandb.ora

db_name  = mandb
instance_name = mandb
compatible = 11.1.0
processes = 150
undo_management = auto
undo_tablespace = undotbs01
db_cache_size = 32m
shared_pool_size= 72m
db_block_size = 4096
control_files =
( '%oracle_base%\oradata\mandb\controlmandb01.ctl',
 '%oracle_base%\oradata\mandb\controlmandb02.ctl')
remote_login_passwordfile=exclusive

4. 패스워드 파일 생성

ORAPWD is a utility used to create a password file for an Oracle Database

orapwd file=%oracle_home%\database password=oracle

5. Windows Service에 등록

ORADIM is a Windows only utility used for the creation, modification and deletion of the Windows Service

oradim -NEW -SID mandb -INTPWD password -STARTMODE auto -PFILE "%oracle_home%\database\intmandb.ora"

Oradim 참조 : http://psoug.org/reference/oradim.html

6. Oracle Nomount

os> sqlplus / as sysdba
SQL> shutdown abort
SQL> startup nomount

7. 데이터베이스 생성

SQL> ed createDB
 create database prod
 logfile
 group 1 (
  'C:\Oracle\oradata\prod\prod1a.log',
  'C:\Oracle\oradata\prod\prod1b.log') size 10m,
 group 2 (
  'C:\Oracle\oradata\prod\prod2a.log',
  'C:\Oracle\oradata\prod\prod2b.log') size 10m
 datafile 'C:\Oracle\oradata\prod\system01.dbf'
  size 100m reuse autoextend on next 10m maxsize unlimited
 sysaux datafile 'C:\Oracle\oradata\prod\sysaux01.dbf'
  size 100m reuse autoextend on next 10m maxsize unlimited
 undo tablespace undotbs01 datafile 'C:\Oracle\oradata\prod\undotbs01.dbf'
  size 100m reuse autoextend on next 10m maxsize unlimited
 default temporary tablespace temp tempfile 'C:\Oracle\oradata\prod\temp.tmp'
  size 100m reuse autoextend on next 10m maxsize unlimited
 /

SQL> @createDB

SQL> select status from v$instance;

8. Data Dictionary & Server-side PL/SQL 환경 구성

SQL> ed after_db_create.sql

 conn sys/change_on_install as sysdba
 @?/rdbms/admin/catalog
 @?/rdbms/admin/catproc
 conn system/manager
 @?/sqlplus/admin/pupbld

SQL> @after_db_create.sql

9. 리스너 설정 (생성 & 확인)
oracle_home = C:\Oracle\product\11.1.0\db_1

os> lsnrctl stop

파일 수정
# listener.ora Network Configuration
# File: C:\Oracle\product\11.1.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = admin)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (SID_NAME = ORCL)
   (ORACLE_HOME = C:\Oracle\product\11.1.0\db_1)
   (GLOBAL_DBNAME =ORCL)
  )
  (SID_DESC =
   (SID_NAME = prod)
   (ORACLE_HOME = C:\Oracle\product\11.1.0\db_1)
   (GLOBAL_DBNAME =prod)
  )
 )

os> lsnrctl start

10. iSQL*PLUS 를 위한 설정

# TNSNAMES.ORA Network Configuration
# File: C:\Oracle\product\11.1.0\db_1\Network\Admin\tnsnames.ora
# Generated by Orange Network Configuration Tool

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = admin)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SRVR = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

mandb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = admin)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SRVR = DEDICATED)
      (SERVICE_NAME = mandb)
    )
  )

11. 수동으로 EM Database control 설정
 
emca -config dbcontrol db -repos create

 ....

set oracle_sid=mandb
emctl start dbconsole

참조 : http://blog.naver.com/gseducation?Redirect=Log&logNo=20097811671