Tuesday, October 27, 2015

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


No comments:

Post a Comment