Oracle Fundamental I - Chapter 4: Creating a Database

In last chapter, we taked about how to manager an Oracle instance. As we discussed, the oracle DBMS is composed by two major parts, the Oracle instance and Oracle database. In this chapter, we will discuss how to manager the Oracle database.

Creation Prerequisites
To create a new database, you must have the following:

• Aprivileged account authenticated by one of the following:
• Operating system
• Password file
• Sufficient memory to start the instance
• Sufficient disk space for the planned database

Using Password File Authentication

• Create the password file using the password utility:

$ orapwd file=$ORACLE_HOME/dbs/orapwU15 password=admin entries=5

• Set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE in initialization parameter file
• Add users to the password file
• Assign appropriate privileges to each user

GRANT SYSDBA TO HR;

An Oracle database can be created by:

• Oracle Universal Installer
• Oracle Database Configuration Assistant
• Graphical user interface
• Java-based
• Launched by the Oracle Universal Installer
• Can be used as a stand-alone application
• The CREATE DATABASE command

In this chapter, we will discuss the procedure based on CREATE DATABASE command.

Before creating a database, some operating system environment must be properly configured:
ex: on the values
ORACLE_BASE: /opt/oracle
ORACLE_HOME: /opt/oracle/product/11.2
ORACLE_SID: DB1
ORA_NLS33: /$ORACLE_HOME/ocommon/nls/admin/data
PATH: /$ORACLE_HOME/bin
LD_LIBRARY_PATH: $ORACLE_HOME/bin

Creating a database manually:

• choose a unique instance and database name
• choose a database character set
• Set operating system variables
• Create the initialization parameter set
• Start the instance in NOMOUNT stage
• Create and execute the CREATE DATABASE command
• Run scripts to generate the data dictionary and accomplish post-creation steps
• catalog.sql: creates the views on the base tables and on the dynamic performance views, and their synonyms.
• catproc.sql: creates the packages and procedures required to use PL/SQL.
• pupbld.sql: creates the Product User Profile table and realted procedure, this scrip must be run as system.
• Create additional tablespaces as needed

Oracle has a feature called Oracle Managed Files (OMF) to help you easily managing the location to data files.

• OMF are established by setting two parameters:
• DB_CREATE_FILE_DEST: Set to give the default locatio for data files
• DB_CREATE_ONLINE_LOG_DEST_n: Set to give the default locations for online redo log fiels and control files
• Maximum of five locations
• Once specifying those two parameter, the create database command can be as simple as:

SQL> CREATE DATABASE dba01;