Manual Oracle database creation Script

Today i will show how create a database Oracle syntax manually . We know that during installation oracle database is created Auto. Also we can create it by DBCA command auto however its good to know creating by your hand J


Before you create the database make sure you have done the planning about the size of the database, number of tablespaces and redo log files you want in the database.

Select the standard database block size.

Lets Begin...
First Change Instance name Export Oracle SID
export ORACLE_HOME=/path/to/oracle/home
oracle@... > export ORACLE_SID=test_orcl
Check it
oracle@...> echo $ORACLE_HOME

Create a init.ora

# $ORACLE_HOME/dbs/init<sid>.ora

control_files = (/path/to/control1.ctl,/path/to/control2.ctl,/path/to/control3.ctl)

undo_management = AUTO

undo_tablespace = UNDOTBS1

db_name = test

db_block_size = 8192

sga_max_size = 1073741824 #one gig

sga_target = 1073741824 #one gig

u01/app/oracle/product/11.2.0/dbhome_1/dbs/init_test.ora
and set init.ora make sure that
DB_NAME=test_orcl

(remember the parameter file name should of the format init<sid>.ora and it should be in ORACLE_HOME/dbs  directory)

3.  Create a password file

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwd<sid>.ora password=oracle entries=5

Start the instance

sqlplus / as sysdba

startup nomount

 Create the database

 SQL> create database Test_orcl

logfile group 1 ('/u01/app/oracle/oradata/redo1.log') size 50M,

            group 2 ('/u01/app/oracle/oradata/redo2.log') size 50M,

            group 3 ('/u01/app/oracle/oradata /redo3.log') size 50M

character set WE8ISO8859P1

national character set utf8

datafile '/u01/ app/oracle/oradata/system.dbf' size 500M

autoextend on next 10M maxsize unlimited extent management local

sysaux datafile '/u01/app/oracle/oradata/sysaux.dbf' size 100M autoextend on

next 10M maxsize unlimited

undo tablespace undotbs1 datafile '/u01/app/oracle/oradata /undotbs1.dbf' size 100M

default temporary tablespace temp tempfile '/u01/app/oracle/oradata /temp01.dbf' size 100M;
SQL> CREATE TABLESPACE users DATAFILE ‘/u01/app/oracle/oradata/deneme/users01.dbf’ SIZE 25M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

Run catalog and catproc 

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql
 

Change passwords

alter user sys identified by sys_password;

alter user system identified by sys_password;

SQL> alter database mount;
SQL> alter database open;
SQL> select instance_name,version,status,database_status from v$instance;
INSTANCE_NAME   VERSION       STATUS         DATABASE_STATUS
—————-            —————–    ————           —————–
test_orcl                    11.2.0.1.0          STARTED          ACTIVE