---

Overview : In this practice you will create database step-by-step manually.

Using the CREATE DATBASE SQL statement is a more manual approach to creating a database. If you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running prepared scripts.

Specify an Instance Identifier (SID) & Environment Variables ORACLE_SID, unique Oracle system identifier (SID) is used to distinguish this instance from other Oracle Database instances that you may create later and run concurrently on the same host computer. The maximum number of characters for ORACLE_SID is 12, and only letters and numeric digits are permitted.

In our case ORACLE_HOME is on /u01/app/oracle/product/12.1.0.2/dbhome_1 path where we have Oracle software Installed.

$ cd

$ vi .bash_profile

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

 

# User specific environment and startup programs

 

export ORACLE_SID=orcl

export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1

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

$ . .bash_profile

Ensure That the Required Environment Variables Are Set

$ echo $ORACLE_SID

$ echo $ORACLE_HOME

$ echo $PATH

Create the Initialization Parameter File

$ cd $ORACLE_HOME/dbs

$ cp init.ora initorcl.ora--(orcl is SID here )

Open your pfile and edit with the following parameters

$ vi initorcl.ora

db_block_size=8192

db_name='orcl'

open_cursors=300

processes=150

shared_pool_size=150m

sga_target=400m

undo_tablespace='UNDOTBS1'

control_files='/disk1/oradata/orcl/control01.ctl'

db_create_file_dest='/disk1/oradata/orcl'

diagnostic_dest='/disk3/oradata/orcl'

compatible='12.1.0.2'

Create directory structure

$ mkdir -p /disk1/oradata/orcl

$ mkdir -p /disk2/oradata/orcl

$ mkdir -p /disk3/oradata/orcl

Connect and startup your instance in nomount mode

$ sqlplus / as SYSDBA

SQL> startup NOMOUNT

SQL> EXIT

Issue the CREATE DATABASE Statement

$ vi $HOME/cr8db.sql

CREATE DATABASE orcl

LOGFILE GROUP 1 ('/disk1/oradata/orcl/redo01a.log') SIZE 5M,

GROUP 2 ('/disk1/oradata/orcl/redo02a.log') SIZE 5M

DATAFILE '/disk1/oradata/orcl/system01.dbf' SIZE 200M REUSE autoextend on next 10m

SYSAUX DATAFILE '/disk1/oradata/orcl/sysaux01.dbf' SIZE 200M REUSE autoextend on

Default TEMPORARY TABLESPACE TEMPTS

TEMPFILE '/disk1/oradata/orcl/tempts01.dbf' SIZE 20M REUSE

UNDO TABLESPACE UNDOTBS1

DATAFILE '/disk1/oradata/orcl/undotbs01.dbf' SIZE 25M REUSE;

$ sqlplus / as sysdba

SQL> @$HOME/cr8db.sql

Database created

Build Data Dictionary Views

Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus:

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql

SQL> shut Immediate

SQL> startup upgrade

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

SQL> shut Immediate

SQL> startup

SQL> connect system/manager

SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

SAMPLE QUERIES:

SQL> select name,value from v$sga;

SQL> select instance_name, version, startup_time,status, database_status from v$instance;

SQL> select name,created,open_mode,current_scn from v$database;

SQL> select banner from v$version;

 

Data Dictionary VIEWS to Query

·      V$SGA ·      V$INSTANCE ·      V$DATABASE ·      V$PROCESS ·      V$SYSAUX_OCCUPANTS

 

---