Friday, December 17, 2010

Yet another blog post to create database manually (Oracle version 10.2)


1. Environment variables
$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_SID=testdb

2. Required directories
$ mkdir -p /dbdir/testdb/{control,data,redo,arch}
$ mkdir -p /dbdir/testdb/admin/{a,b,c,u}dump

3. Entries in pfile $ORACLE_HOME/dbs/inittestdb.ora
db_name                    = testdb

control_files              = (/dbdir/testdb/control/control.dbf)

log_archive_dest_1         = "LOCATION=/dbdir/testdb/arch"
log_archive_dest_state_1   = enable

sga_target                 = 500M
pga_aggregate_target       = 100M

undo_management            = AUTO
undo_tablespace            = undotbs

audit_file_dest            = /dbdir/testdb/admin/adump
background_dump_dest       = /dbdir/testdb/admin/bdump
core_dump_dest             = /dbdir/testdb/admin/cdump
user_dump_dest             = /dbdir/testdb/admin/udump

4. Creating database
$ sqlplus / as sysdba

SQL> create spfile from pfile;

SQL> startup nomount;

SQL> CREATE DATABASE testdb
   USER SYS IDENTIFIED BY secret1
   USER SYSTEM IDENTIFIED BY secret2
   DATAFILE '/dbdir/testdb/data/system1.dbf' SIZE 200M
      EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE '/dbdir/testdb/data/sysaux1.dbf' SIZE 50M
   DEFAULT TABLESPACE worktbs
      DATAFILE '/dbdir/testdb/data/work1.dbf' SIZE 50M
   DEFAULT TEMPORARY TABLESPACE temptbs
      TEMPFILE '/dbdir/testdb/data/temptbs1.dbf' SIZE 50M
   UNDO TABLESPACE undotbs
      DATAFILE '/dbdir/testdb/data/undotbs1.dbf' SIZE 50M
   LOGFILE GROUP 1 ('/dbdir/testdb/redo/redo1.log') SIZE 50M,
           GROUP 2 ('/dbdir/testdb/redo/redo2.log') SIZE 50M
   ARCHIVELOG
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
;

5. Post database creation steps
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> connect system/secret2
SQL> @?/sqlplus/admin/pupbld.sql
SQL> exit

$ echo "$ORACLE_SID:$ORACLE_HOME:N" >> /etc/oratab


No comments:

Post a Comment