Creating Oracle 11g Database manually

By

Creating Database in 11g is very simple.In 10g you need to create additional
directories bdump,cdump,udump instead of diagnostic dump directory.Below are the
steps:

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

[oracle@localhost ~]$ cd /u03

[oracle@localhost u03]$ mkdir testdb

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

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

[oracle@localhost testdb]$ mkdir adump diag flash_recovery_area

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

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

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

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

[oracle@localhost dbs]$ vi init_testdb.ora

db_name=’testdb’
memory_target=1G
processes = 150
audit_file_dest=’/u03/testdb/adump’
audit_trail =’db’
db_block_size=8192
db_domain=”
db_recovery_file_dest=’/u03/testdb/flash_recovery_area’
db_recovery_file_dest_size=2G
diagnostic_dest=’/u03/testdb/diag’
dispatchers='(PROTOCOL=TCP) (SERVICE=testdb)’
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/testdb/ora_control1.ctl,/u03/testdb/ora_control2.ctl)
compatible =’11.2.0′

Step 3:Prepare Create Database script :

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

[oracle@localhost testdb]$ vi createdb_shaik.sql

CREATE DATABASE testdb
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
LOGFILE
GROUP 1 ‘/u03/testdb/redo1.log’ SIZE 10M,
GROUP 2 ‘/u03/testdb/redo2.log’ SIZE 10M,
GROUP 3 ‘/u03/testdb/redo3.log’ SIZE 10M
DATAFILE
‘/u03/testdb/system.dbf’ size 200M REUSE
sysaux datafile ‘/u03/testdb/sysaux.dbf’ size 100m
undo tablespace UNDOTBS1
datafile ‘/u03/testdb/undo1.dbf’ size 100m
DEFAULT TEMPORARY TABLESPACE temp1
TEMPFILE ‘/u03/testdb/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 testdb]# 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=testdb
     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/orapwtestdb
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_testdb.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/testdb/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

learing abbout postgresql database and oracle database and cricket Entertainment

Leave a comment