Creating Database Manually (Script)

Database Creation Script

dbcreate_script.sql 

CREATE DATABASE test

   USER SYS IDENTIFIED BY manager            

   USER SYSTEM IDENTIFIED BY manager

   LOGFILE GROUP 1 ('/u01/user18/test/log/redo01.log') SIZE 50M,

           GROUP 2 ('/u01/user18/test/log/redo02.log') SIZE 50M,

           GROUP 3 ('/u01/user18/test/log/redo03.006Cog') SIZE 50M

   MAXLOGFILES 5

   MAXLOGMEMBERS 5

   MAXLOGHISTORY 50

   MAXDATAFILES 100

   MAXINSTANCES 1

   DATAFILE '/u02/user18/test/data/system01.dbf' SIZE 100M autoextend on

   SYSAUX DATAFILE '/u02/user18/test/data/sysaux01.dbf' SIZE 100M   autoextend on

   DEFAULT TABLESPACE users datafile ‘/u02/user18/test/data/users01.dbf’ size 100m   autoextend on

   DEFAULT TEMPORARY TABLESPACE temp

      TEMPFILE '/u03/user18/test/data/temp01.dbf' SIZE 50m

   UNDO TABLESPACE undotbs1 

      DATAFILE '/u03/user18/test/data/undotbs01.dbf'

      SIZE 200M;

Post database creation script

Postscript.sql

@$ORACLE_HOME/rdbms/admin/catalog.sql à script to create data

                                                                                  Dictionary tables, sy…

@$ORACLE_HOME/rdbms/admin/catproc.sql à script to create procedure  

                                                                                  And packages

Connect system/manager

@$ORACLE_HOME/sqlplus/admin/pupbld.sql à script to create users

                                                                                Profile

Listed scripts are minimal scripts for creating database.

Create necessary directories……such as test

]$ mkdir test

]$ cd test

]$ mkdir adump

Note:The name of the parameter file must be the same as the name of your instance name

Location of parameter file= /u01/user18/oracle/product/10.2.0/db_1/dbs/

Inittest.ora (Parameter File)

db_name=test                                                àdefines database name

instance_name=test                                                  à defines instance name

control_files=’/u01/test/control/control01.ctl’  à name and location of controlfile

db_block_size = 8192                                   à to set db block size in bytes (8k)

undo_management=’auto’                             à undo segment management values ‘manual’ or ‘auto’

undo_tablespace=undotbs1                                      à undo tablespace name

undo_retention=900                                                à undo database retention periold

compatible=11.2.0.1.0                                   à  oracle software version

sga_max_size=1g                                          à to set upper limit of sga

sga_target=800m                                           à to set actual size of sga component (except log buffers)

log_buffer = 100m                                        à to set log buffer size

pga_aggregate_target = 200m                                    à for total pgas (soft limit)

workarea_size_policy = auto                         à auto sizing of the pga within aggregate pga

background_dump_dest=’/u01/test/bdump’            à location of alert logfile and trace

        files of background operation.

user_dump_dest=’/u01/test/udump’              à user’s related trace files location

core_dump_dest=’/u01/test/cdump’               à kernel related trace files location

Note:In 10g there were to parameter i.e sga_max_size,sga_target,pga_aggregate_target

Instead of this parameter we can use memory_target parameter. This parameter divides ur sga and pga into 60 and 40%

And instead of bdump,cdump,udump u can use diagnostic_dest=’/u01/prod’

After creating this files/script start ur database

]$export ORACLE_SID=test

]$sqlplus  / as sysdba

Sql> startup nomount

When ur database has started

Check the status of ur database

]$select status from v$instance;

It will show the status as

 Status

--------

Started

After checking it the run ur dbcreate script and post script

Sql>@dbcreate_script.sql

Sql>@postscript.sql