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:

$ ssh -Y oracle@SERVER

2. You already had the $ORACLE_HOME/bin on the PATH environment variable (see ~/.bash_profile here) so you can issue:

$ dbca

3. An X11 windows should be launched on your Linux desktop, on the Operations panel choose "Create a database":

4. Select "General Purpose or transaction Processing" on the Database Templates panel:

5. 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:

6. On Management Options panel check "Configure Enterprise Manager" and "Configure Database Control for local management"

7. 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:

8. On the Storage Options panel I choose "File System":

9. I let the Database File Locations with the defaults:

10. Also the default settings on Recovery Configuration:

11.  On database content I didn't touch anything:

12. You can tweak the database Initialization Parameters according to your system configuration and usage scenario:

13. On Security Settings panel I recommend to use the enhanced 11g default security as well:

14. I also enabled the Automatic Maintenance Tasks at certain schedule:

15. I used defaults Database Storage options:

16. 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:

17. You should be able to see an install progress like this:

18. An finally a summary screen like this:

Once finished you should had a fresh empty database.


19. Finnally add the ORACLE_SID environment variable to oracle's ~/.bash_profile. Login to the server as oracle user and edit:

$ 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

20. DON'T FORGET to set the recently created database in autostart mode at /etc/oratab. Edit with oracle user:

$ 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

21. One final thing to do is to test the 3 main components installed: listener, database and enterprise manager. Login as oracle user and issue:

$ 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. 
...

22. 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:



$ 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

23. 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?

• Create a init script to make Oracle database start automatically on system bootin