Database creation(manual)

 

                                             Database creation(manual)

Database can be created using any of the following methods.

1.At the time of installation of oracle software.

2.Using dbca.

3.Manually.

env |grep ORA(to know the environment variable)

. .bash_profile 

1.Create directory structure

cd /oradata02

mkdir dev & cd dev

mkdir control data log arch adump

2.Make the parameter file

oracle$cd $ORACLE_HOME/dbs

oracle$vi initdev.ora

db_name=dev

control_files = '/oradata02/dev/control/control1.ctl','/oradata02/dev/control/control2.ctl'

undo_management = Auto

undo_tablespace = UNDOTBS1

db_block_size = 8192

compatible = 11.2.0

memory_max_target = 500m

memory_target = 500m

pga_aggregate_target= 100m

log_archive_dest_1='LOCATION=/oradata02/dev/arch'

:wq!

cd  /oradata02/dev

oracle$vi dbcreation.sql

CREATE DATABASE dev

SET TIME_ZONE='Asia/Calcutta'

   USER SYS IDENTIFIED BY Oracle123

   USER SYSTEM IDENTIFIED BY Oracle123

   LOGFILE GROUP 1 ('/oradata02/dev/log/redo01a.log','/oradata02/dev/log/redo01b.log') SIZE 50M BLOCKSIZE 512,

           GROUP 2 ('/oradata02/dev/log/redo02a.log','/oradata02/dev/log/redo02b.log') SIZE 50M BLOCKSIZE 512,

           GROUP 3 ('/oradata02/dev/log/redo03a.log','/oradata02/dev/log/redo03b.log') SIZE 50M BLOCKSIZE 512

   MAXLOGFILES 5

   MAXLOGMEMBERS 5

   MAXLOGHISTORY 1

   MAXDATAFILES 100

   CHARACTER SET US7ASCII

   NATIONAL CHARACTER SET AL16UTF16

   EXTENT MANAGEMENT LOCAL

   DATAFILE '/oradata02/dev/data/system01.dbf' SIZE 325M REUSE

   SYSAUX DATAFILE '/oradata02/dev/data/sysaux01.dbf' SIZE 325M REUSE

   DEFAULT TABLESPACE users

      DATAFILE '/oradata02/dev/data/users01.dbf'

      SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

   DEFAULT TEMPORARY TABLESPACE tempts1

      TEMPFILE '/oradata02/dev/data/temp01.dbf'

      SIZE 20M REUSE

   UNDO TABLESPACE undotbs1

      DATAFILE '/oradata02/dev/data/undotbs01.dbf'

      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

:wq!

export ORACLE_SID=dev

sqlplus / as sysdba

sql>create spfile from pfile;

sql>startup nomount

sql>@dbcreation.sql

you must see database creation statement here.

Once you see database created message, run the following scripts to create the data dictionary.

sql> @?/rdbms/admin/catalog.sql

Then run cataproc.sql and pupbld.sql

sql> @?/rdbms/admin/catproc.sql

sql> conn system/Oracle123

sql> @?/sqlplus/admin/pupbld.sql