7 Steps to Create New Oracle Database from Command Line
7 Steps to Create New Oracle Database from Command Line
Even if you are a Linux sysadmin or developer, sometimes you might end-up managing Oracle database that is running in your environment.
In that case, it is essential to understand certain basic Oracle DBA activities. In this tutorial, we’ll explain how to create an Oracle database from command line.
When you install Oracle software, it will give you an option to create a new database from the UI.
At that time, if you decide not to create a new database, and install only the Oracle software, later you can create the database separately.
To create an Oracle database, you have two options:
- Use Database Configuration Assistant (DBCA) and create new database by using the GUI. This is fairly straight forward.
- Use the “Create Database” command to create a brand new oracle database from the command line. This method is helpful when you don’t have console access to the server to launch the DBCA. Or, when your server doesn’t have Xterm setup properly, use this method.
1. Setup Appropriate Oracle Environment Variables
First, you should setup appropriate oracle environment variable on your server.
In this example, it assumes that oracle is installed under /u01/app/oracle/product directory. Change this value according to your environment.
export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.2.0
The most important variable is ORACLE_SID, which will have the name of the new oracle database that you like to create. In this example, the name of the new database is set to “dev” as shown below.
export ORACLE_SID=dev
2. Create an Ini File – initdev.ora
Next, create a ora.ini file for your new database. This is the initialization file for the new database.
Depending on the version of your Oracle, you might see a sample init.ora file under $ORACLE_HOME. If you have one, use that as a baseline and edit the values accordingly.
cd $ORACLE_HOME/dbs cp init.ora initdev.ora
Note: As shown above, the init file for this new database should be of this format: init{ORACLE_SID}.ora — So, in this case, the filename will be: initdev.ora
If you don’t see a default init.ora template in your $ORACLE_HOME/dbs, use the following sample.
*.db_name='dev' *.db_domain='' *.audit_file_dest='/u01/app/oracle/admin/dev/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.memory_target=1G *.control_files='/u01/app/oracle/oradata/dev/control01.ctl','/home/oracle/u02/oradata/dev/control02.ctl' *.db_block_size=8192 *.diagnostic_dest='/u01/app/oracle/admin/dev' *.open_cursors=250 *.processes=100 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTS'
Few points to consider in the above file:
- In the above file, make sure you set the db_name to the ORACLE_SID name that you set in the previous step
- Whatever the name of the undo_tablespace that we specify here, we have to use the exact name during the CREATE DATABASE command.
- Change the directory location appropriately based on your system. Don’t forget to change “dev” in the above directory location to your ORACLE_SID name.
3. Create Serve Parameter file (spfile)
SP file stands for Server Parameter file. Unlike the init file sp file is a binary file, and you can’t edit the spfile manually.
SP file is created from the text based ini file. The advantage of SP file is that, you can change the initialization parameter values after you start the database using the ALTER SYSTEM command.
In other words, when you use “ALTER SYSTEM” command to change any of the parameter’s value, it stores them in the SP file.
Then, when the oracle database is started, it first looks for the SP file for the parameter value. If it can’t find any SP file, then it will use the text based Ini file.
To create a SP file for our new database, use the following command.
First, use sqlplus command and get the oracle sysdba prompt, from where we’ll create a new database.
$ sqlplus / as sysdba Connected to an idle instance. SQL>
If you notice in the above output, it says “Connected to an idle instance.”. This is because our current ORACLE_SID is set to dev, which is a new database, which we have not yet created.
So, the first step is to create a new SP file based on our Ini file. PFILE stands for Ini file. The following command will create a new SPFILE.
SQL> CREATE SPFILE FROM PFILE; File created.
As you see below, the above command has created the spfiledev.ora automatically.
$ ls -1 $ORACLE_HOME/dbs/ initdev.ora spfiledev.ora
4. Start the Idle Instance
Before we create the database, we should start the instance for “dev” database using STARTUP NOMOUNT command. As you might’ve guessed, this command will not MOUNT any database. This is simply starting the empty new idle instance with the ORACLE_SID name “dev”.
SQL> STARTUP NOMOUNT; ORACLE instance started. Total System Global Area 1258291200 bytes Fixed Size 1261564 bytes Variable Size 520093700 bytes Database Buffers 721420288 bytes Redo Buffers 15515648 bytes
During the above command, it will read the default spfile with the name spfile{ORACLE_SID}.ora from the default spfile location $ORACLE_HOME/dbs. If the spfile is not there, it will use the default init file init{ORACLE_SID}.ora
For some reason, if you want to specify the location of the pfile yourself, you can do that by passing PFILE parameter as shown below.
SQL> STARTUP NOMOUNT PFILE=/tmp/initdev.ora
Also, you might get the following ORA-01078 and LRM-00109, if the spfile, or the init file is missing in the default location.
SQL> STARTUP NOMOUNT ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbs/initdev.ora'
5. Create New Oracle Database
Use the following CREATE DATABASE command to create an empty database.
SQL> CREATE DATABASE dev
USER SYS IDENTIFIED BY DevSysPass
USER SYSTEM IDENTIFIED BY DevSystemPass
LOGFILE GROUP 1 ('/home/oracle/u02/oradata/dev/redomed_01.log') SIZE 50M,
GROUP 2 ('/home/oracle/u02/oradata/dev/redomed_02.log') SIZE 50M,
MAXLOGFILES 5
MAXLOGHISTORY 10
MAXDATAFILES 50
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/home/oracle/u02/oradata/dev/system01.dbf' SIZE 100M REUSE
SYSAUX DATAFILE '/home/oracle/u02/oradata/dev/sysaux01.dbf' SIZE 100M REUSE
DEFAULT TABLESPACE USERS
DATAFILE '/home/oracle/u02/oradata/dev/users01.dbf'
SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMPTS
TEMPFILE '/home/oracle/u02/oradata/dev/tempts01.dbf'
SIZE 30M REUSE
UNDO TABLESPACE UNDOTS
DATAFILE '/home/oracle/u02/oradata/dev/undots01.dbf'
SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
In the above command:
- Create database command will create an oracle database with name “dev”
- The password specified in the 2nd line will be assigned to SYS user
- The password specified in the 3rd line will be assigned to SYSTEM user
- We are creating two redo logfiles with size 100MB each.
- MAXLOGFILES – The maximum number of redo log files is set to 5
- MAXDATAFILES – This indicates that maximum number of oracle data files that can be created for this database.
- DATAFILE – This specify the datafile that will be used by the SYSTEM tablespace
- SYSAUX DATAFILE – This indicates the datafile that will be used by the SYSAUX tablespace
- The default tablespace for this database is set to USERS
- The default temporary tablespace is set to TEMPTS
- The undo tablespace is set to UNDOTS
If you want to create additional tablespace after the DB is created, use this: 15 Oracle Tablespace and Datafile Command Examples
Note: If you specify DB_CREATE_FILE_DEST in your init file to a directory location, then you don’t need to specify the exact location and filenames for all the datafiles, oracle will take care of those for you.
For example, if you’ve specified this in your initdev.ora file.
# vi initdev.ora DB_CREATE_FILE_DEST='/home/oracle/u02/oradata/dev'
In this case, you can simplify your CREATE DATBASE command as shown below.
SQL> CREATE DATABASE dev
USER SYS IDENTIFIED BY DevSysPass
USER SYSTEM IDENTIFIED BY DevSystemPass
MAXLOGFILES 5
MAXLOGHISTORY 10
MAXDATAFILES 50
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DEFAULT TABLESPACE USERS
DEFAULT TEMPORARY TABLESPACE TEMPTS
UNDO TABLESPACE UNDOTS
The above will create the appropriate datafiles required for all the tablespaces (undo, temporary, etc) under the location specified in the DB_CREATE_FILE_DEST directory.
To understand more about UNDO tablespace, this should help: 10 Oracle UNDO Tablespace Management Examples
6. Build Data Dictionary Views
As a last step, execute the catalog.sql and catproc.sql. catalog script will create all the dictionary tables, performance related views, required public synonyms. This will also grant appropriate access to all the synonyms that was created. catproc script executes all the scripts that are required for PL/SQL functionalities.
SQL> @?/rdbms/admin/catalog.sql; SQL> @?/rdbms/admin/catproc.sql
Partial output of the above commands.
SQL> @?/rdbms/admin/catalog.sql; DOC>###################################################################### DOC>###################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error and terminate the SQLPLUS session if the user is not SYS. DOC> Disconnect and reconnect with AS SYSDBA. DOC>###################################################################### DOC>###################################################################### DOC># no rows selected Package created. Package body created. .. .. Synonym created. Grant succeeded. PL/SQL procedure successfully completed. SQL> @?/rdbms/admin/catproc.sql .. .. Comment created. Synonym created. Grant succeeded. PL/SQL procedure successfully completed.
If you are curious, you can look at the catalog and catproc script to understand what exactly it does.
vi $ORACLE_HOME/rdbms/admin/catalog.sql; vi $ORACLE_HOME/rdbms/admin/catproc.sql
7. Verify – Shutdown and Startup
Finally, perform a regular shutdown and startup to make sure everything works as expected on this new database.
$ sqlplus / as sysdba SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP; ORACLE instance started. Total System Global Area 1258291200 bytes Fixed Size 1261564 bytes Variable Size 520093700 bytes Database Buffers 721420288 bytes Redo Buffers 15515648 bytes Database mounted.
