Step 01

Decide on the location of

Three control files (minimum) on different disks/ drives/volumes

Three groups of redo log files, with each group having two members (minimum) on different disks/ drives/volumes

Create a file structure for the data files

Step 02

Creating database manually without auto undo management:

1. a. Decide on a unique instance name on NT/2000 machines up to 8.0.x cannot have a name with more than 4 characters and 8.1.x cannot have more than 8 characters 

   b. Database character set. Modifying the character set at a later date lands in many issues.

2. Set the operating system variables

               (UNIX)

                       ORACLE_HOME

                       ORACLE_SID

                       ORACLE_BASE

                       ORA_NLS33

                       PATH

               (NT)

                       ORADIM80 -NEW -SID u16

                       -INTPWD password -STARTMODE auto

                       -PFILE ORACLE_HOME\DATABASE\initu16.ora

                       You must decide the SID, Password, and

                       Create init<sid_name>.ora file

                       SET ORACLE_SID=<sid_name>

                       Make <sid_name> the current SID

        3. Prepare the parameter file

               use init.ora as a parameter file template

               db_name =  Eight characters or fewer that identify the database

               control_files = the location of three control files

               DB_BLOCK_SIZE = Determines the database block size (can not

               change after the database has been created)

        4. Create a password file

        5. Start the instance

               STARTUP NOMOUNT pfile=init.ora

        6. Create the database

               MANUALLY FROM Server Manager

                       CREATE DATABASE "<sid_name>"

                               MAXLOGFILES 6

                               MAXLOGMEMBERS 6

                               MAXDATAFILES 30

                               MAXLOGHISTORY 100

                               ARCHIVELOG

                       LOGFILE

                               GROUP 1

                                      'E:\DATA\ sid \GROUP1\log1a.rdo' SIZE 200K,

                                      'E:\DATA\ sid \GROUP1\log1b.rdo' SIZE 200K

                               GROUP 2

                                      'E:\DATA\ sid \GROUP2\log2a.rdo' SIZE 200K,

                                      'E:\DATA\ sid \GROUP2\log2b.rdo' SIZE 200K

                               GROUP 3

                                      'E:\DATA\ sid \GROUP3\log3a.rdo' SIZE 200K,

                                      'E:\DATA\ sid \GROUP3\log3b.rdo' SIZE 200K

                               DATAFILE

                                      'E:\DATA\ sid \DATAFILES\system01.dbf'

                               size 30M

                               CHARACTER SET WE8ISO8859P1;                           

                       Database

                               select name,created,log_mode from v$database;

                       Thread

                               select status, instance from v$thread;

                       Datafiles

                               select name from v$datafile;

                       Logfiles

                               select member from v$logfile;

                       Controlfiles

                               select name from v$controlfile;

                       Verify System Creation

                               select file_name from dba_data_files

                               where tablespace_name='SYSTEM';

                       Look at the database users

                               select username, created from dba_users;

        7. Run scripts to generate the data dictionary and

        accomplish postcreation steps.

                       Create the data dictionary

                               CATALOG.SQL

                       prostcreation objects

                               CATPROC.SQL

              8. to know/determine the archive mode of the database

            svrmgrl> archive log list

                  or  issue the following statement

                        select log_mode from v$database;

also issue the following statements to know whether the archive process is enabled or not

select archiver from v$instance;

The output determines whether the process is started or not.

Creating a Database using Instance Manager

        Step 1. Start Instance Manager

                       Press the New Button

                 Input a SID (4 characters) ->abcd

        Step 2. Enter the DBA Authorization password and authenication

        Step 3. Press the Advanced Button

                * The Database Name must be the same as the Database Name 

                  in the initabcd.ora file

                * Enter the max logfile value and the max group member value

                * Design the location of your datafiles, logfiles, 

                  archive files, and control files

                * Change the logfile location and name to meet your design

                * Set the location of the Parameter file

                       Defining Parameters in the Parameter File

                       db_name = {myDBName}#database name using 

                       to identify the database

                       db_files = 30          #maximum allowable 

                       number of database files

                       #control file list 

                       (Created by the Instance Manager)

                       control_files = (D:\orant\DATABASE\ctl1{SID}orcl.ora, 

                                      D:\orant\DATABASE\ctl2{SID}orcl.ora,

                                      D:\orant\DATABASE\ctl3{SID}orcl.ora)

                       #database will be compatible with 

                       software of this version

                       compatible = 7.3.0.0.0

                       #db_file_multiblock_read_count= number of database 

                       blocks to read with each I/O.         

                       #db_file_multiblock_read_count =  8 # INITIAL

                       # db_file_multiblock_read_count = 8 # SMALL

                        db_file_multiblock_read_count = 16 # MEDIUM

                       # db_file_multiblock_read_count = 32 # LARGE

                       # db_block_buffers = number of database blocks 

                       cached in memory

                       db_block_buffers tells the oracle kernel the 

                       size of the area that stores database read from the disk

                       #db_block_buffers =  200  # INITIAL

                       # db_block_buffers = 200  # SMALL

                        db_block_buffers = 550   # MEDIUM

                       # db_block_buffers = 3200  # LARGE

                       Size in bytes of the shared pool

                       #shared_pool_size =  6500000  # INITIAL

                       # shared_pool_size = 3500000  # SMALL

                        shared_pool_size = 6000000   # MEDIUM

                       # shared_pool_size = 9000000  # LARGE

                       Number of redo blocks for checkpoint 

                       threshold

                       log_checkpoint_interval = 10000

                       Maximum number of user processes

                       #processes =  50  # INITIAL

                       # processes = 50  # SMALL

                        processes = 100  # MEDIUM

                       # processes = 200  # LARGE

                       DML locks - one for each table 

                       modified in a transaction

                       #dml_locks =  100  # INITIAL

                       # dml_locks = 100  # SMALL

                        dml_locks = 200   # MEDIUM

                       # dml_locks = 500  # LARGE

                       Redo circular buffer size

                       #log_buffer =  8192      # INITIAL

                       # log_buffer = 8192      # SMALL

                         log_buffer = 32768     # MEDIUM

                       # log_buffer = 163840    # LARGE

                       Number of sequence cache entries

                       #sequence_cache_entries =  10   # INITIAL

                       # sequence_cache_entries = 10   # SMALL

                        sequence_cache_entries = 30    # MEDIUM

                       # sequence_cache_entries = 100  # LARGE

                       #sequence_cache_hash_buckets =  10     # INITIAL

                       # sequence_cache_hash_buckets = 10     # SMALL

                        sequence_cache_hash_buckets = 23      # MEDIUM

                       # sequence_cache_hash_buckets = 89     # LARGE

                       # audit_trail = true            # if you want auditing

                       # timed_statistics = true       # if you want timed statistics

                       max_dump_file_size = 10240      # limit trace file size to 5 Meg each

                       Start the Archiver Process

                       log_archive_start = true      # if you want automatic archiving

                       LOG_ARCHIVE_DEST = E:\{db_name}\ARCHIVE 

                       #location of the archive directory

                       # define directories to store trace and alert files

                       background_dump_dest=%RDBMS73%\trace

                       user_dump_dest=%RDBMS73%\trace

                       Size of database block in bytes. Db_block_size can not 

                       be changed after database creation

                       db_block_size must be a multiple of 512K

                       db_block_size = 8192

                       Number of job queue processes to start

                       snapshot_refresh_processes = 1

                       Password file usage

                       remote_login_passwordfile = shared

                       Enable text searching

                       text_enable = true

        Step 4. Create your database

               Once the instance has been started, it is an idle instance. 

               Steps to starting the database

               From a DOS Prompt

               set ORACLE_SID=abcd

               set LOCAL =2:abcd

               svrmgrl (NT)

               connect internal/password

            startup pfile=initabcd.ora

 Creating a Database with AUTO-UNDO management

Create database mydb

Controlfile reuse

Logfile group 1( ‘d:\oradata\mydb\redo\mydb_redo_01a.log’,

                    ‘e:\oradata\mydb\redo\mydb_redo_01b.log’) size 10m,

         group 2( ‘e:\oradata\mydb\redo\mydb_redo_02a.log’,

                    ‘f:\oradata\mydb\redo\mydb_redo_02b.log’) size 10m,

        group 1( ‘f:\oradata\mydb\redo\mydb_redo_03a.log’,

                    ‘d:\oradata\mydb\redo\mydb_redo_03b.log’) size 10m

maxinstances  1

maxlogfiles   5

maxloghistory 100

maxdatafiles 100

archivelog

datafile ‘d:\oradata\ mydb\system\mydb_system_01.dbf’ size 100M autoextend on next 20M maxsize unlimited,

default temporary tablespace temp

tempfile ‘f:\oradata\ mydb\temp\mydb_temp_01.dbf’ size 100M

undo tablespace undo_tbsp datafile

‘e:\oradata\ mydb\rbs\mydb_undo_01.dbf’ size 100M autoextend off;

Creating a Database with OMF (Oracle Managed File system)

Step 01

The following parameters are to be included in the init.ora parameter file of the database.

-- Defining the destination for the redo log files

db_create_online_dest_1 = d:\oradata\mydb\redo

db_create_online_dest_2 = e:\oradata\mydb\redo

db_create_online_dest_3 = f:\oradata\mydb\redo

-- Defining the destination for the data files

db_create_file_dest= d:\oradata\mydb\data

-- defining the undo auto management

undo_management=auto

undo_tablespace=undo_tbsp

Step 02

Create database mydb

Datafile size 200M

Logfile group 1 size 20M, group 2 size 20M, group 3 20M

Default temporary tablespace temp tempfile size 100M

Undo tablespace undo_tbsp datafile size 100M

Maxlogfiles=5

Maxlogmembers=5

Maxdatafiles=200

Noarchivelog;

This is not advised to be used for a production environment. Multiple datafile destinations are not supported and your ability to balance the IO is crippled.

This is recommended for the development environment to enable to developers to play with database objects easily.