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
postgres 
