Create a database in Oracle11g on Linux (RHEL6)


In the previous tutorial I explained how to Install Oracle 11g Database on a Linux machine. In this tutorial, I will explain how to create a new database. Last time we had created & configured a default database (orcl) that comes with Oracle. This time we will create our own database & then we can create tables there. Let’s start with this tutorial 🙂 Make sure you have at least 2GB of free space where your Oracle is installed.

Declare new shell variable. This will be the name of your new database. In this tutorial, I chose the name db_new as I had run out of nice names 😉 Please note that Oracle accepts database names of maximum 8 characters. So don’t go for longer names. Open your bash shell or terminal & issue : – ORACLE_SID=db_new (make sure you use your own name that you have chosen for your database). Then issue : – export ORACLE_SID. This will export ORACLE_SID variable to your profile. It also means, your Oracle will now run targeting this database.

Create a directory for your database. Issue this command: – mkdir -p $ORACLE_BASE/oradata/db_new. This is where control & data-files for your database will be kept. Refer to previous post to know about ORACLE_BASE & HOME.

Create an Initialisation Parameter file. This parameter file or pfile determines the database name, location of control files & a variety of parameters. Its a text file, so easiest way to create it is to copy it from init.ora file found in $ORACLE_HOME/dbs location. Copy it to the same location & give it the name initdb_new.ora (name of your database, just make sure you prefix init as it’s an initialisation file). Now edit with the contents shown below. Make sure to comment the lines mentioned in screenshot (open the image in new tab if you find text too small) because we are preparing a minimal file. Anything extra will result into failure. Be extra cautions about the control files location. Control files are the files that determine the number & location of database data files. Data files are actual files what store the actual data.

pflie contents.

pflie contents.

Create Server Parameter File. Server parameter file or spfile is a binary file based on our initialisation parameter file(pfile) & can’t be read using a text editor. This is what Oracle reads before mounting the database. So, start Oracle by issuing sqlplus /nolog command. And then connect using your credentials as sysdba (for eg. sqlplus / as sysdba). This will connect you to an idle instance. From this point, we can create our spfile or server parameter file. Issue command : – create spfile from pfile. This will create spfile. Now armed with spfile, database can now be started with nomount stage. Nomount stage is where Oracle reads the spfile & starts the Oracle instance. Issue startup nomount command to do this.

spfile created & database mounted to nomount stage.

spfile created & database mounted to nomount stage.

Issue Create Database script. Below is the database creation script. I am not posting the text here as I want you to this by hand to know what you are actually doing to create database. This is the most important step, so be extra cautious here. This is the step that got me exhausted the most 😦 I failed almost 8-10 times going past this step. Potential pitfalls are : – lack of adequate storage space for data-files, wrong tablespace names in pfile, specifying inadequate size to various parameters, etc. I suggest following the values strictly that are mentioned in screenshot, if you are a novice in Oracle.

Database creation script executed.

Database creation script executed.

Mount database. Now issue alter database mount command. Mount is the stage where Oracle read the contents on control-files to know about size, number & location of data-files. Your database is now mounted.

Open database. Now issue alter database open command to open the database. This is the stage where Oracles reads from & writes to actual database files & database becomes ready for transactions.

Run Oracle scripts to build database dictionary views. This is a time-taking step (script #2), so its better to execute these scripts & then go for a cup of tea or coffee or some snacks 😉

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql

scripts executed1 scripts executed

Test database. Now that database has been created, we can create our tables here. You can also test it by querying database dictionary views. Below are two such queries.

querying database dictionary view

querying database dictionary view

So, we have successfully created our new database to play with. In next tutorial, I will explain you how to back-up & restore a database 🙂 See you later 😉

Advertisements

One thought on “Create a database in Oracle11g on Linux (RHEL6)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s