Sunday, August 26, 2012

Creating Oracle 11g Database manually in 11 steps in Unix-based Operating system

Hi,
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.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

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

Enjoy learning Oracle DBA....


Best regards,

Rafi.




































32 comments:

  1. Thanks ! This post is amazing. I am new to this technology. I am a sybase guy. your post helped me a lot Boss. Thanks.
    --between in step 9, you said : conn system/manager. But when you execute rest of the steps with this, it fails. So only for that pubbld.sql you need to connect as system. right? rest is smooth like butter

    ReplyDelete
  2. Very nice Post. Please see mine as well.

    http://www.dbas-oracle.com/2013/07/Manual-Database-Creation-Script.html

    ReplyDelete
  3. Thank you Very Much Rafi.............

    ReplyDelete
  4. Its really great information..Thanks for sharing this informative blog..hadoop training chennai

    ReplyDelete
  5. Excellent information.After reading iam impressed alot.Thank you so much.
    We provide Oracle 11g DBA Online training.ORACLE 11g DBA online training

    ReplyDelete
  6. I really enjoy the blog.Much thanks again. Really Great.
    Very informative article post.Really looking forward to read more. Will read on…

    oracle online training
    sap sd online training
    sap-crm-online-training
    hadoop online training

    ReplyDelete
  7. I was reading your blog this morning and noticed that you have a awesome
    resource page. I actually have a similar blog that might be helpful or useful
    to your audience.

    Regards
    sap sd and crm online training
    sap online tutorials
    sap sd tutorial
    sap sd training in ameerpet

    ReplyDelete
  8. This is one awesome blog article. Much thanks again.
    I really enjoy the blog.Much thanks again. Really Great.

    sap online training

    ReplyDelete
  9. Nice Post & a good info. Persuing it is very beneficial CAPM Certification Training in USA This also increases the market value.

    ReplyDelete
  10. Thanks for sharing this informative content that guided me to know the details about the training offered in different technology.
    hadoop training in chennai

    ReplyDelete
  11. Excellent ! I am truly impressed that there is so much about this subject that has been revealed and you did it so nicely.
    UNIX Training in Chennai

    ReplyDelete
  12. You post is useful for me. Thanks to shared this informative and valuable news.
    Oracle courses | DBA course

    ReplyDelete
  13. I read this blog i didn't have any knowledge about this but now i got some knowledge so keep on sharing..

    Oracle Weblogic Server Training

    ReplyDelete
  14. Thanks@Rafi your Are Awe! Providing Very Valuable Information.
    Coming To Our self We Provide Restaurant Service Parts In Us.Thanks For Providing Such A Great And Valuable Information.Have A Nice Day.

    ReplyDelete
  15. This Blog Provides Very Useful and Important Information. I just Want to share this blog with my friends and family members. United States Medical Licensing Examination

    ReplyDelete
  16. This Blog Provides Very Useful and great Information. United States Medical Licensing Examination Thanks for sharing.

    ReplyDelete
  17. I like your post very much. It is very much useful for my research. I hope you to share more info about this. Keep posting!web design company in velachery

    ReplyDelete