Tuesday, December 4, 2012

1 Oracle Database creation (1) with OMF

Unlike other DBMS vendor, such as Sybase, DB2 and MSSQL, We can only have one online database in given Oracle instance. It is because the parameter file ( pfile/spifle ) has the parameter ‘DB_NAME’ specify for the database name for that given instance.

It is possible , that oracle instance has more then 1 database. but only one database can be mount in any given time. Also, under the RAC, multiple oracle instance can connect to the same database.

How to create the database ( short version)

  1. Defined the ORACLE_SID environment variable: The Oracle System ID (SID) is unique to identify the database instance. It is not the database name but the instance.
  2. Prepare the parameter file ( init.ora)
  3. Startup the oracle instance with nomount
  4. Create the database


after we start the oracle, we can see the process name has been append with SID.


Prepare the parameter file ( init.ora )

There are many options we can put in the init.ora but for now, I only have the most basic ones.

  • DB_NAME –> This is the actual database name.
  • DB_CREATE_FILE_DEST –> When setup this parameter, it will use OMF, this save us a lot of parameter, such as the default location of the database files…
db_name = mydb
db_block_size = 8192
undo_management = AUTO
sga_target = 500M
db_create_file_dest = /home/oracle/app/oracle/oradata/orcl
Startup the oracle instance with nomount
startup nomount pfile='/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initmydb.ora';
Create the database
create database mydb;


I am also checking the control file location here. Because we didn’t specify the control file location, therefore , it is been created in the OMF location. However, it is still not defined in the init.ora file, so when we shutdown the instance and the database will fail to open.

The ORA-00205  means the Oracle instance fail to open the Oracle


lets add the control_files in the parameter file and restart the oracle

db_name = mydb
db_block_size = 8192
undo_management = AUTO
sga_target = 500M
db_create_file_dest = /home/oracle/app/oracle/oradata/orcl

Can I have 2 SID with their own databases?

Yes, of course you can have as many of the Oracle instance running. But the question would be why you need that? Each Oracle instance require certain amount of resources. It is not recommend to have multiple instance in the production environment.

Here is the out put show 2 oracle instances MYDB and mydb2. 


Overview for the new database

  • v$instance
  • v$database
  • v$datafile /  dba_data_files
  • v$tempfile / dba_temp_files

Since we don’t specify the temporary file in the create database syntax. There is no entry in this view. The database use SYSTEM tablespace as temporary tablespace. SYSTEM tablespace is being create as dictionary management by default. The local management SYSTEM tablespace can not be used as temporary tablespace.

  • v$controlfile
  • v$logfile
  • v$tablespace / dba_tablespaces
  • check the users / dba_users
select * from sys.user$ where type#=1;
  • check the roles
select * from sys.user$ where type#=0;


  1. Hi,
    Very useful and informative article you have been provided to us on oracle database.Thanks for sharing such a article and sharing your knowledge with your users.
    Thank you,
    Oracle EBS training



SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates