Database Creation Steps

This is one of the most basic tasks for Oracle DBA, it also helps us to understand how Oracle Database works.

Creating Database in 11g is very simple.In 10g you need to create additional directories bdump, cdump, udump instead of diagnostic dump directory (11G).

 Below are the steps:

Step 1:Create Directory structure with Oracle ownership and permission as below:

[oracle@localhost ~]$ cd /u03

[oracle@localhost u03]$ mkdir mydb

[oracle@localhost u03]$ chmod -R 777 /u03/mydb/*

[oracle@localhost u03]$ chown -R oracle:oinstall /u03/mydb/*

[oracle@localhost mydb]$ mkdir adump diag flash_recovery_area

[oracle@localhost u03]$ chmod -R 777 /u03/mydb/*

[oracle@localhost u03]$ chown -R oracle:oinstall /u03/mydb/*


Step 2:Create Parameter file in $ORACLE_HOME/dbs location:

[oracle@localhost mydb]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ vi init_mydb.ora

db_name='mydb'
memory_target=1G
processes = 150
audit_file_dest='/u03/mydb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u03/mydb/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u03/mydb/diag'
dispatchers='(PROTOCOL=TCP) (SERVICE=mydb)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u03/mydb/ora_control1.ctl,/u03/mydb/ora_control2.ctl)
compatible ='11.2.0'


Step 3:Prepare Create Database script :

[oracle@localhost u03]$ cd /u03/mydb/

[oracle@localhost mydb]$ vi createdb_shaik.sql

CREATE DATABASE mydb
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
LOGFILE
GROUP 1 '/u03/mydb/redo1.log' SIZE 10M,
GROUP 2 '/u03/mydb/redo2.log' SIZE 10M,
GROUP 3 '/u03/mydb/redo3.log' SIZE 10M
DATAFILE
'/u03/mydb/system.dbf' size 200M REUSE
sysaux datafile '/u03/mydb/sysaux.dbf' size 100m
undo tablespace UNDOTBS1
datafile '/u03/mydb/undo1.dbf' size 100m
DEFAULT TEMPORARY TABLESPACE temp1
TEMPFILE '/u03/mydb/temp01.dbf'
SIZE 100M REUSE
CHARACTER SET AL32UTF8
;
:wq

Step 4:Set the Oracle ENVIRONMENT and SID of Database in the Operating System:

[root@localhost mydb]# su - oracle

[oracle@localhost ~]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=orcl
export TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

:wq
[oracle@localhost ~]$ . .bash_profile
     This will set the Oracle Environment variables in Unix-based operating system.

[oracle@localhost ~]$export ORACLE_SID=mydb
     This will set the SID of the current Database in Unix-based operating system.


Step 5:Create the Password file.

[oracle@localhost ~]$orapwd file=$ORACLE_HOME/dbs/orapwmydb password=Oracle entries=10


Step 6:Create server parameter file.

[oracle@localhost dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 28 14:08:02 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>create spfile from pfile='$ORACLE_HOME/dbs/init_mydb.ora';


step 7:Start the Database in nomount State.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
SQL> select status from v$instance;

STATUS
------------
STARTED

Note:Common issue memory_target not supported,refer the below link for resolving:

http://rafioracledba.blogspot.in/2011/06/ora-00845-memorytarget-not-supported-on.html

Step 8:Execute Create Database script created in Step 3

SQL> @/u03/mydb/createdb_shaik.sql

Database created

Step 9:Execute the catalog.sql,catproc.sql and pupbld.sql scripts:
a)catalog.sql =>Creates dictionary tables and views
b)catproc.sql =>Creates PL/SQL procedures,functions and packages necessary.
c)pupbld.sql  =>Creates user profiles.


So our database is created. Now just run the catalog.sql,catproc.sql and pupbld.sql scripts.
WE will find catalog.sql and catproc.sql in $ORACLE_HOME/rdbms/admin path and pupbld.sql in $ORACLE_HOME/sqlplus/admin path.

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
conn system/manager

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

SQL> alter user system identified by manager;

User altered.

SQL> conn system
Enter password:
Connected.
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


  DATE_VALUE FROM PRODUCT_USER_PROFILE
                  *
ERROR at line 3:
ORA-00942: table or view does not exist


DROP TABLE PRODUCT_USER_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

DROP TABLE PRODUCT_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist



View created.


Grant succeeded.

DROP PUBLIC SYNONYM PRODUCT_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist



Synonym created.

DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist



Synonym created.

DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.


Step 10:Verify the Dictionary views created.


SQL> select name from v$database;


Step 11:Change the Database mode from noarchive log to archive log mode
Changing to archive log mode:

--------------------------------


SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           3
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;
Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3

This completes the manual Database creation on Unix based Operating system....

Enjoy learning Oracle DBA....

Click here to Join Our Blog and Stay Updated