Monday, February 6, 2012

Creating a new Oracle 11g R2 Database on CentOS 6.2

Once you installed an Oracle 11g R2 Database Server on CentOS 6.2 the next step is to create a new empty and fresh database for your own use. You must have a running listener before creating a database.

I assume you are using a Linux client (CentOS, Ubuntu, ...) with a desktop environment installed  (Gnome, KDE, ...).

The steps are straightforward and I gonna choose a simple deployment configuration.

  1. Login on the server with oracle user with SSH X11 Forwarding:
  2. $ ssh -Y oracle@SERVER
    
  3. You already had the $ORACLE_HOME/bin on the PATH environment variable (see ~/.bash_profile here) so you can issue:
  4. $ dbca
    
  5. An X11 windows should be launched on your Linux desktop, on the Operations panel choose "Create a database":
  6. Select "General Purpose or transaction Processing" on the Database Templates panel:
  7. On Database Identification Panel put a Global Database Name "demo.home.dev" and Oracle System Identifier (SID) "demo". NOTE: on this example, I used "demo.home.dev" as the global database name and "demo" for the SID, but I recommend to let it as simple as possible and choose "demo" for both:
  8. On Management Options panel check "Configure Enterprise Manager" and "Configure Database Control for local management"
  9. On Database Credentials panel I choose "Use the Same Administrative Password for All Accounts" but you can make a different choice and set each password individually:
  10. On the Storage Options panel I choose "File System":
  11. I let the Database File Locations with the defaults:
  12. Also the default settings on Recovery Configuration:
  13.  On database content I didn't touch anything:
  14. You can tweak the database Initialization Parameters according to your system configuration and usage scenario:
  15. On Security Settings panel I recommend to use the enhanced 11g default security as well:
  16. I also enabled the Automatic Maintenance Tasks at certain schedule:
  17. I used defaults Database Storage options:
  18. On Creation Options panel don't forget to check the Create Database and optionally check Save as Database Template if you want to reuse this settings further:
  19. You should be able to see an install progress like this:
  20. An finally a summary screen like this:
  21. Once finished you should had a fresh empty database.

  22. Finnally add the ORACLE_SID environment variable to oracle's ~/.bash_profile. Login to the server as oracle user and edit:
  23. $ nano ~/.bash_profile
    
    
    then append:
    export ORACLE_SID=demo
    
    
    at the end the ~/.bash_profile archive should look like:
    # .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
    
    umask 022
    
    export TMPDIR=$TMP
    export ORACLE_BASE=/opt/app/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
    export PATH=$ORACLE_HOME/bin:$PATH
    
    export ORACLE_SID=demo
    export ORACLE_HOME_LISTNER=LISTENER
    
    
  24. DON'T FORGET to set the recently created database in autostart mode at /etc/oratab. Edit with oracle user:
  25. $ nano /etc/oratab
    
    and it should look like. Notice the red Y:
    # This file is used by ORACLE utilities.  It is created by root.sh
    # and updated by the Database Configuration Assistant when creating
    # a database.
    
    # A colon, ':', is used as the field terminator.  A new line terminates
    # the entry.  Lines beginning with a pound sign, '#', are comments.
    #
    # Entries are of the form:
    #   $ORACLE_SID:$ORACLE_HOME::
    #
    # The first and second fields are the system identifier and home
    # directory of the database respectively.  The third filed indicates
    # to the dbstart utility that the database should , "Y", or should not,
    # "N", be brought up at system boot time.
    #
    # Multiple entries with the same $ORACLE_SID are not allowed.
    #
    #
    fresh:/opt/app/oracle/product/11.2.0/db_1:Y
    
  26. One final thing to do is to test the 3 main components installed: listener, database and enterprise manager. Login as oracle user and issue:
  27. $ lsnrctl start 
    
    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-FEB-2012 16:44:03
    
    ... 
    The command completed successfully
    
    
    then run. (NOTE: you must have the autostart mode for the database. See previous step):
    $ dbstart $ORACLE_HOME
    Processing Database instance "fresh": log file /opt/app/oracle/product/11.2.0/db_1/startup.log
    ...
    
    and finally the Enterprise Manager:
    $ emctl start dbconsole
    Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0 
    Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
    https://SERVERNAME:1158/em/console/aboutApplication
    Starting Oracle Enterprise Manager 11g Database Control ......... started. 
    ...
    
    
  28. To verify if you can connect to the database, on the server issue the following command and enter the password supplied on the Database Credentials panel:

  29. $ sqlplus sys@demo AS SYSDBA
    SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 6 17:36:28 2012
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    Enter password: 
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> 
    
    then issue the following query:
    SQL> select * from dual;  
    
    D
    -
    X
    
    
  30. To check if the listener, database and enterprise manager are running, open a browser at the URL: https://SERVERNAME:1158/em/console/aboutApplication. NOTE: Remember to open this port on iptables if you want remote database access, otherwise you won't be able to use it.

What to do next?

7 comments:

  1. Perfect!!!

    Thanks for this little 'How to' of configuration of Oracle DB 11

    ReplyDelete
  2. hi ed's
    your post is good ..
    by the way do you have a way how to create new oracle by console, not GUI
    for example the oracle server is located at A city, then I remote the server by SSH, and then I create new oracle by remote. any suggest?

    thank you

    ReplyDelete
    Replies
    1. Hi:

      Even on top of SSH you can use a GUI installer by tunneling X11. I think you should read first my post http://eduardo-lago.blogspot.com/2012/01/step-by-step-installing-oracle-ready.html, in the step 14 "Prepare the CentOS server for installing Oracle products ..." I prepare SSH X11 Forwarding to enjoy a GUI wizard on top of SSH w/o the need of a full graphical environment like Gnome/KDE on the server side. Of course you will need a X11 server on your PC, Gnome/KDE Desktop both have a built-in X11 server. On windows there many X11 server implementations.

      My advise WRT to Oracle DB installer is to use the GUI on top of SSH X11 Forwarding, cuz the Wizard is very rich and its difficult to replace using only a terminal.

      Enjoy it!

      Delete
  3. Hi Ed,

    Very good post, straight and to the point.

    ReplyDelete
  4. Just a quick note to state that I followed these instructions to install Oracle 11.2.0.3.0 on 64-bit CentOS 6.3 and only one error popped up - namely, when trying to run 'emctl start dbconsole' I got an error that ORACLE_UNQNAME needs to be set. Googling revealed:
    http://blog.mclaughlinsoftware.com/2012/08/23/whats-oracle_unqname/
    where the solution is detailed.

    Cheers,
    ak.

    ReplyDelete
  5. Thank you so much took the long guess work out of the equation
    Great article

    ReplyDelete
  6. This post is very simple to read and appreciate without leaving any details out. Great work! new company name suggestions

    ReplyDelete