Wednesday , July 26 2017
Home / Tutorial for Creating New Database Instance Manually

Tutorial for Creating New Database Instance Manually

This is a collection of 15 FAQs for Oracle DBA on creating Oracle database instances manually using CREATE DATABASE statement. Items in this FAQ collection are organized together to form a complete tutorial guide on creating a new database instance manually. Topics included in this FAQ are:

How To Create an Oracle Database?

There are two ways to create a new database:

  • Use the Database Configuration Assistant (DBCA) to create a database interactively.
  • Use the CREATE DATABASE statement to create a database manually.

How To Create an Oracle Database Manually?

Based on Oracle’s Administrator Guide, there are 11 steps to create a database with the CREATE DATABASE statement:

  • Step 1: Decide on Your Instance Identifier (SID)
  • Step 2: Establish the Database Administrator Authentication Method
  • Step 3: Create the Initialization Parameter File
  • Step 4: Connect to the Instance
  • Step 5: Create a Server Parameter File (Recommended)
  • Step 6: Start the Instance
  • Step 7: Issue the CREATE DATABASE Statement
  • Step 8: Create Additional Tablespaces
  • Step 9: Run Scripts to Build Data Dictionary Views
  • Step 10: Run Scripts to Install Additional Options (Optional)
  • Step 11: Back Up the Database.

Other items in this FAQ collection will follow those steps to help you creating a new database manually from beginning to end.

How To Select an Oracle System ID (SID)?

This is Step 1. If you are planning to create a new database, you need to select an Oracle System ID (SID). This ID will be used to identify the new Oracle database and its Oracle instance. SID must be unique if you want to run multiple databases on a single server.

Let’s set SID for the new database to be: DBA.

How To Establish Administrator Authentication to the Server?

This is Step 2. There are two ways to establish administrator authentication to a new database.

  • Use a password file.
  • Use operating system (OS) authentication.

Using OS authentication is easier on Windows system. If you used your own Windows user account to install Oracle server, it will put your Windows user account into a special Window’s user group called SYSDBA. This Window’s user group will be fully trusted by Oracle server with SYSDBA privilege.

To continue with other steps, make sure you logged into the Windows system with a user account in the SYSDBA group.

How To Create an Initialization Parameter File?

This is Step 3. To run an Oracle database as an Oracle instance, you need to create an initialization parameter file, which contains a set of initialization parameters.

The easiest way to create an initialization parameter file to copy from the sample file provided by Oracle. You can do this in a command window as shown below:

>cd $ORACLE_HOME

>copy .\config\scripts\init.ora .\database\initDBA_ini.ora

>edit .\database\initDBA_ini.ora
(replace XE by DBA)

In this example, only the SID is changed from XE to DBA. All other parameters are maintained as is.

How To Connect the Oracle Server as SYSDBA?

This is Step 4. The best way to connect to the Oracle server as an administrator is to use SQL*Plus. You need to run SQL*Plus with /nolog option and use the CONNECT with blank user name, blank password and AS SYSDBA option. Here is a sample session:

>cd $ORACLE_HOME
>.\bin\sqlplus /nolog

SQL> CONNECT / AS SYSDBA
Connected.

How To Create a Server Parameter File?

This is Step 5. The initialization parameter file is good to get an Oracle database instance started. But it is not ideal run an instance as production. You need to convert the initialization parameter file into a Server Parameter File (SPFile) using the CREATE SPFILE statement. The script below shows you how do this:

SQL> CREATE SPFILE=$ORACLE_HOME/dbs/SPFILEDBA.ora
  2  FROM PFILE=$ORACLE_HOME/database/initDBA_ini.ora;
File created.

Note that $ORACLE_HOME should be replaced by the real path name where your Oracle server is intalled.

The SPFile should be located in the expected directory and named as SPFILE($SID).ora.

How To Start an Oracle Instance?

This is Step 6. Now you are ready to start the new Oracle Instance without any database. This instance will be used to create a database. Starting an instance without database can be done by using STARTUP NOMOUNT statement as shown below:

>.\bin\sqlplus /nolog

SQL> CONNECT / AS SYSDBA
Connected.

SQL> SHUTDOWN
ORACLE instance shut down.

SQL> STARTUP NOMOUNT
ORA-00821: Specified value of sga_target 16M is too small, 
needs to be at least 20M

The SHUTDOWN command is need to bring the default instance XE down.

The STARTUP NOMOUNT command failed because it tried to start the default instance XE, and there is a bad parameter in the XE instance SPFile.

See the next FAQ question to find another way to start the new instance DBA.

How To Start a Specific Oracle Instance?

A simple way to start a specific Oracle instance is to start the instance with the PFILE option as shown in the following example:

>.\bin\sqlplus /nolog

SQL> CONNECT / AS SYSDBA
Connected.

SQL> STARTUP NOMOUNT
   PFILE=$ORACLE_HOME/database/initDBA_ini.ora

ORA-02778: Name given for the log directory is invalid

The PFILE option allows you to specify the initialization parameter file of a specific Oracle instance. But the initialization parameter file created in Step 3 has some problem with the log directory.

How To Start Instance with a Minimal Initialization Parameter File?

The sample initialization parameter file provided by Oracle seems to be not working. But we can try to start the new instance with a minimal initialization parameter file (PFile). First you can create another PFile, $ORACLE_HOME/database/initDBA_ini_min.ora, as shown below:

db_name=DBA
control_files=("\oraclexe\oradata\DBA\control.dbf")
undo_management=AUTO

Then start the DBA instance again:

SQL> CONNECT / AS SYSDBA
Connected.

SQL> STARTUP NOMOUNT
   PFILE=$ORACLE_HOME/database/initDBA_ini_min.ora

ORACLE instance started.

Total System Global Area  113246208 bytes
Fixed Size                  1286028 bytes
Variable Size              58720372 bytes
Database Buffers           50331648 bytes
Redo Buffers                2908160 bytes

How To Run CREATE DATABASE Statement?

This is Step 7. Oracle Administrator Guide provided a sample CREATE DATABASE statement. But it is a long statement. You can modify and same it in a file, $ORACLE_HOME/configscripts/create_database_dba.sql, and run the file within SQL*Plus. Here is a copy of the modified CREATE DATABASE statement:

CREATE DATABASE DBA
USER SYS IDENTIFIED BY dbacentre
USER SYSTEM IDENTIFIED BY dbacentre
LOGFILE GROUP 1 ('/oraclexe/oradata/DBA/redo01.log') SIZE 10M,
  GROUP 2 ('/oraclexe/oradata/DBA/redo02.log') SIZE 10M,
  GROUP 3 ('/oraclexe/oradata/DBA/redo03.log') SIZE 10M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/oraclexe/oradata/DBA/system01.dbf' SIZE 32M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oraclexe/oradata/DBA/sysaux01.dbf' SIZE 32M REUSE
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE tempts1
  TEMPFILE '/oraclexe/oradata/DBA/temp01.dbf'SIZE 20M REUSE
UNDO TABLESPACE undotbs
  DATAFILE '/oraclexe/oradata/DBA/undotbs01.dbf'
  SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Here is how to run this long CREATE DATABASE statement:

SQL> @$ORACLE_HOME\config\scripts\create_database_dba.sql;
CREATE DATABASE DBA
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: control file could not be created
ORA-00202: control file: 'C:\ORACLEXE\ORADATA\DBA\CONTROL.DBF'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

Don’t worry about the error messages. They are caused by the missing directory for data files in file system.

How To Do Clean Up If CREATE DATABASE Failed?

To better organize data files, you should create a dedicated directory for each Oracle database. This can be done by using Windows file explorer to create the \oraclexe\oradata\dba\ directory. Try the CREATE DATABASE statement again, when you have the directory ready.

If your CREATE DATABASE statement failed half way again, you may have to clean up the partial result of the CREATE DATABASE statement. Here is a list of suggestions for you:

  • Run SHUTDOWN command to stop the partial started database instance.
  • Remove all files in DBA directory: \oraclexe\oradata\dba\
  • Run STARTUP NOMOUNT PFILE command to start the empty instance again to be ready for CREATE DATABASE statement.

How To Run CREATE DATABASE Statement Again?

After cleaning up the results of a previously failed CREATE DATABASE statement, you can run the CREATE DATABASE statement again as shown below:

SQL> @$ORACLE_HOME\config\scripts\create_database_dba.sql;
CREATE DATABASE DBA
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

Something went wrong again. This time it might related the limitation that Oracle 10g XE only supports one database instance. With the default instance called “XE” already defined, creating another database instance might be not allowed.

How To Create Additional Tablespaces for an New Database?

This is Step 8. Creating additional tablespaces can be done by using the CREATE TABLESPACE statement as shown in the following sample script:

SQL> CREATE TABLESPACE users
  2  DATAFILE '/oraclexe/oradata/DBA/users01.dbf' SIZE 10M;

SQL> CREATE TABLESPACE indx
  2  DATAFILE '/oraclexe/oradata/DBA/indx01.dbf' SIZE 10M;

How To Build Data Dictionary View of an New Database?

This is Step 9. The Oracle Administrator Guide suggests to run two SQL scripts provided by Oracle as shown bellow:

SQL> @/u01/oracle/rdbms/admin/catalog.sql

SQL> @/u01/oracle/rdbms/admin/catproc.sql