Oracle : DATABASE CREATION

DATABASE CREATION

 

Oracle Database can create by using the following 3 methods

1. Manual database creation
2. OMF method (Oracle Managed database Files)
3. DBCA method (Database Configuration Assistance)

 

Steps to create database in manual method.

 

1. Declare or specify variables or set up environmental variables:

First edit  .bash_profile and then set up the following variables.

$ vi .bash_profile

export ORACLE_SID=db

export ORACLE_HOME=/u01/app/oracle/product/12.1.0

export ORACLE_BASE=/u01/app/oracle

export PATH =$ORACLE_HOME/bin:$PATH:.

Note: db is oracle instance name

 

.  .bash_profile to execute bash profile.

Check the declared variables by using following commands.

$ echo $ORACLE_HOME

Result:  /u01/app/oracle/product/12.1.0

$ echo $ORACLE_SID

Result:  db

 

2. Making directory:

Use the following command to create the directory for storing the CRD files.

mkdir   –p /u02/demo/db

cd   /u02/demo/db

 

3. Create the initialization file.

use the following command using the following command.

  cd $ORACLE_HOME/dbs

ls –l init.ora

cp init.ora init$ORACLE_SID.ora

ls –l init$ORACLE_SID.ora (check the file information)

Open the parameter file by using the following command.

vi  init$ORACLE_SID.ora

Now set the parameter files by using following commands.

db_name=db

control_files=/u02/demo/db/control.ctl

diagnostic_dest=/u02/demo/db

 

 

4. Script for database creation:

Now write the script for database creation by using the following command.

Vi   dbcreate.sql

Now create the database script.

create database db

datafile ‘/u02/demo/db/system.dbf’ size 300m autoextend on

sysaux datafile ‘/u02/demo/db/sysaux.dbf’ size 250m autoextend on

undo tablespace undotbs datafile ‘/u02/demo/db/undotbs.dbf’ size 100m

default temporary tablespace temp tempfile ‘/u02/demo/db/temp.dbf’ size 50m

default tablespace userdata datafile ‘/u02/demo/db/userdata.dbf’ size 150m

logfile

group 1(‘/u02/demo/db/redo1a.dbf’) size 4m,

group 2(‘/u02/demo/db/redo2a.dbf’) size 4m;

 

Save the Script.

 

The SYSTEM tablespace, consisting of the operating system file ‘/u02/demo/db/system.dbf’ is created as specified by the DATAFILE clause.

A SYSAUX tablespace is created, consisting of the operating system file ‘/u02/demo/db/sysaux.dbf ‘ as specified in the SYSAUX DATAFILE.

The UNDO TABLESPACE clause creates and names an undo tablespace that is used to store undo data for this database if you have specified UNDO_MANAGEMENT=AUTO in the initialization parameter file.

The DEFAULT TEMPORARY TABLESPACE clause creates  a default temporary tablespace for this database.

The DEFAULT TABLESPACE clause creates the default permanent tablespace for this database.

Two redo log groups will create and each group will contains the single member.

 

5. Script for data dictionary views:

Now write the script for data dictionary views by using the following command.

vi  generateDDV.sql

Now create the script by using following commands:

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catblock.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

conn system/manager

@$ORACLE_HOME/sqlplus/admin/pupbld.sq

 

6. Connect and Start the database up to no mount state as a sysdba.

$ sqlplus / as sysdba

SQL> startup nomount

Note: Oracle database can be created in nomount state only.

 

7. Run the Database creation script that is created.

SQL> @dbcreate.sql

You can find the acknowledgement of database created successfully.

 

8. Now we can check the database status using the below command.

SQL> Select status from v$instance;

STATUS

OPEN

9. Now we can check the database information using the below commands.

SQL> select name from v$database.

SQL> select name from v$datafile.

SQL> select name from v$controlfile.

 

10. Now run the script that Build Data Dictionary Views.

SQL> @generateDDV.sql