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.
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 an 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.
Issue Create Database script.
Now we can execute a script that will create our 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. 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. Grab the script from my GitHub gist or type it as shown below.
Mount the database.
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 the database.
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
Test the 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.
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 😉