Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Creating New Database Instance Manually

By: FYIcenter.com

Part:   1   2  3 

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:

  1. How To Create an Oracle Database?
  2. How To Create an Oracle Database Manually?
  3. How To Select an Oracle System ID (SID)?
  4. How To Establish Administrator Authentication to the Server?
  5. How To Create an Initialization Parameter File?
  6. How To Connect the Oracle Server as SYSDBA?
  7. How To Create a Server Parameter File?
  8. How To Start an Oracle Instance?
  9. How To Start a Specific Oracle Instance?
  10. How To Start Instance with a Minimal Initialization Parameter File?
  11. How To Run CREATE DATABASE Statement?
  12. How To Do Clean Up If CREATE DATABASE Failed?
  13. How To Run CREATE DATABASE Statement Again?
  14. How To Create Additional Tablespaces for an New Database?
  15. How To Build Data Dictionary View an New Database?

Sample scripts presented in this FAQ are based Oracle 10g Express Edition (XE) for Windows. The script in Step 7 failed due some unknown reason. But it might work on Oracle 10g Standard Edition.

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: FYI.

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\initFYI_ini.ora

>edit .\database\initFYI_ini.ora
(replace XE by FYI)

In this example, only the SID is changed from XE to FYI. 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.

(Continued on next part...)

Part:   1   2  3 


Selected Developer Jobs:

More...