Membuat Database Oracle 11g secara Manual di Windows

Berikut cara membuat database baru di Oracle 11g R2 secara manual tanpa DBCA, ikuti tahapannya :

Tahap 1 SIAPKAN ENVIRONMENT VARIABLE

C:\>set ORACLE_HOME=C:\app\Administrator\product\11.1.0\db_sisindotek

C:\>set PATH=%ORACLE_HOME%\bin;%PATH%

C:\>set ORACLE_SID=MYORA



Tahap 2 BUAT DIREKTORI YANG DIBUTUHKAN


C:\>mkdir C:\app\Administrator\db\myora\admin\adump

C:\>mkdir C:\app\Administrator\db\myora\admin\dpdump

C:\>mkdir C:\app\Administrator\db\myora\admin\pfile

C:\>mkdir C:\app\Administrator\db\myora\diag

C:\>mkdir C:\app\Administrator\db\myora\flash_recovery_area

C:\app\Administrator\db\myora\oradata



Tahap 3 BUAT PARAMETER FILE


berikan nama initmyora.ora dan simpan di folder {ORACLE_HOME}\database\

db_name='MYORA'
db_block_size=8192
memory_target=500m
processes=100
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
compatible ='11.1.0'
audit_trail ='db'
db_recovery_file_dest_size=5g
db_recovery_file_dest='C:\app\Administrator\db\myora\flash_recovery_area'
audit_file_dest='C:\app\Administrator\db\myora\admin\adump'
diagnostic_dest='C:\app\Administrator\db\myora\diag'
control_files = ('C:\app\Administrator\db\myora\oradata\control1.ctl', 'C:\app\Administrator\db\myora

\oradata\control2.ctl', 'C:\app\Administrator\db\myora\oradata\control3.ct






Tahap 4 BUAT WINDOW SERVICE



C:\>oradim -NEW -SID myora -STARTMODE manual

Instance created.

 

C:\>sc query oracleservicemyora

 

SERVICE_NAME: oracleservicemyora

        TYPE               : 10  WIN32_OWN_PROCESS

        STATE              : 4  RUNNING

                                (STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)

        WIN32_EXIT_CODE    : 0  (0x0)

        SERVICE_EXIT_CODE  : 0  (0x0)

        CHECKPOINT         : 0x0

        WAIT_HINT          : 0x0




Tahap 5 Konek ke Instance dan buat spfile

C:\>sqlplus

 

SQL*Plus: Release 11.1.0.1.0 Production on Wed May 23 07:39:54 2012

 

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

 

Enter user-name: /as sysdba

Connected to an idle instance.

 

SQL> create spfile from pfile;

 

File created.

Tahap 6 - START INSTANCE DALAM MODE NOMOUNT

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  523108352 bytes

Fixed Size                  1375704 bytes

Variable Size             314573352 bytes

Database Buffers          201326592 bytes

Redo Buffers                5832704 bytes





Tahap 7 - BUAT DATABASE


gunakan script berikut



CREATE DATABASE MYORA

    USER sys IDENTIFIED BY sys

    USER system IDENTIFIED BY system

    MAXLOGFILES 5

    MAXLOGMEMBERS 3

    MAXDATAFILES 200

    MAXINSTANCES 1

    MAXLOGHISTORY 500

LOGFILE

GROUP 1 (

    'C:\app\Administrator\db\myora\oradata\redo01a.rdo',

    'C:\app\Administrator\db\myora\oradata\redo01b.rdo'

    ) SIZE 50M,

GROUP 2 (

    'C:\app\Administrator\db\myora\oradata\redo02a.rdo',

    'C:\app\Administrator\db\myora\oradata\redo02b.rdo'

    ) SIZE 50M,

GROUP 3 (

    'C:\app\Administrator\db\myora\oradata\redo03a.rdo',

    'C:\app\Administrator\db\myora\oradata\redo03b.rdo'

    ) SIZE 50M

DATAFILE 'C:\app\Administrator\db\myora\oradata\system01.dbf' SIZE 100M AUTOEXTEND ON
NEXT 50K
MAXSIZE 100M
EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE 'C:\app\Administrator\db\myora\oradata\sysaux01.dbf' SIZE 200M

UNDO TABLESPACE UNDOTBS1 DATAFILE 'C:\app\Administrator\db\myora\oradata\undotbs01.dbf' SIZE 300M AUTOEXTEND OFF

DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'C:\app\Administrator\db\myora\oradata\temp01.dbf' SIZE 200M REUSE AUTOEXTEND OFF

CHARACTER SET WE8ISO8859P1

NATIONAL CHARACTER SET UTF8;






Tahap 8 Buat data dictionary object


Jasalankan catalog.sql dan catproc.sql yang ada di folder RDBMS oracle home andag

SQL> @%ORACLE_HOME%\rdbms\admin\catalog.sql

SQL> @%ORACLE_HOME%\rdbms\admin\catproc.sql

SQL> connect system/system

SQL> @%ORACLE_HOME%\sqlplus\admin\pupbld.sql

Tahap 10 enabled archive

SQL> connect /as sysdba

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  523108352 bytes

Fixed Size                  1375704 bytes

Variable Size             314573352 bytes

Database Buffers          201326592 bytes

Redo Buffers                5832704 bytes

Database mounted.

SQL> alter database archivelog;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     12

Next log sequence to archive   13

Current log sequence           13

Oleh :

Hery Purnama

Freelance IT Trainer

http://freelance-it-trainer.blogspot.com

http://www.inhousetrainer.net

Call/SMS/WA : 081223344506

PinBB : 7DC633AA

email : inhousetrainer@yahoo.com