Manual Database Creation

The specific methods for creating a database are:

• With Database Configuration Assistant (DBCA), a graphical tool.
• With the CREATE DATABASE SQL statement.

1. Specify an Instance Identifier (SID)

The ORACLE_SID environment variable is used to distinguish this instance from other Oracle Database instances on the same host computer.

2. Ensure That the Required Environment Variables Are Set

3. Choose a Database Administrator Authentication Method

• Create a password file

Usage:

 orapwd file=[fname] password=[sys password] entries=[users] force=[y/n] ignorecase=[y/n] nosysdba=[y/n]

• file – name of password file (required),
• password – password for SYS will be prompted if not specified at command line,
• entries – maximum number of distinct DBA (optional),
• force – whether to overwrite existing file (optional),
• ignorecase – passwords are case-insensitive (optional),
• nosysdba – whether to shut out the SYSDBA logon (optional Database Vault only, deprecated in 11.2),

4. Create the Initialization Parameter File

When an Oracle instance starts, it reads an initialization parameter file. This file can be a text file, which can be created and modified with a text editor, or a binary file, which is created and dynamically modified by the database. The binary file, which is preferred, is called a server parameter file. In this step, you create a text initialization parameter file. In a later step, you create a server parameter file from the text file.

• Create the initialization parameter file.

Table 2-2 Recommended Minimum Initialization Parameters

5. Connect to the Instance

Start SQL*Plus and connect to your Oracle Database instance with the SYSDBA administrative privilege.

• To authenticate with a password file, enter the following commands, and then enter the SYS password when prompted:

6. Create a Server Parameter File

The server parameter file enables you to change initialization parameters with the ALTER SYSTEM command and persist the changes across a database shutdown and startup.

You create the server parameter file from your edited text initialization file.

7. Start the Instance

Start an instance without mounting a database.

• Run the STARTUP command with the NOMOUNT clause.

At this point, the instance memory is allocated and its processes are started. The database itself does not yet exist.

8. Issue the CREATE DATABASE Statement

To create the new database, use the CREATE DATABASE statement.

• Run the CREATE DATABASE statement.
• The initialization parameter file specifies the number and location of control files with the CONTROL_FILES parameter.
• Create Necessary directories from parameter file.

CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512,
                   GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512,
                   GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M BLOCKSIZE 512
   MAXLOGHISTORY 1
   MAXLOGFILES 16
   MAXLOGMEMBERS 3
   MAXDATAFILES 1024
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf'
     SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
   SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf'
     SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'
      SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
   UNDO TABLESPACE undotbs1
      DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
   USER_DATA TABLESPACE usertbs
      DATAFILE '/u01/app/oracle/oradata/mynewdb/usertbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

• MAXLOGHISTORY define limits for the redo log
• MAXDATAFILES specifies the maximum number of data files that can be open in the database

• The AL32UTF8 character set is used to store data in this database.
• The AL16UTF16 character set is specified as the NATIONAL CHARACTER SET used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2
• The SYSTEM tablespace is created as a locally managed tablespace

9. Run Scripts to Build Data Dictionary Views

Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus

1. In SQL*Plus, connect to your Oracle Database instance with the SYSDBA administrative privilege:
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql

2. In SQL*Plus, connect to your Oracle Database instance as SYSTEM user:
@?/sqlplus/admin/pupbld.sql

The at-sign (@) is shorthand for the command that runs a SQL*Plus script.

The question mark (?) is a SQL*Plus variable indicating the Oracle home directory.