Manual Oracle Database Creation

This document briefs about the step by step procedure for creating a database on unix environment.

Before starting, it is best to have the relevant environment variables set (ORACLE_SID, ORACLE_HOME, PATH, etc...). Best to have this setup in a .profile file.

Also create all the directory structures required.

Step 1: Make an init<SID>.ora in your $ORACLE_HOME/dbs directory. To simplify, you can copy init.ora to init<SID>.ora and modify the file.

            Path names, file names, and sizes will need to be modified

            Example parameter settings:

            db_block_size=8192

            db_cache_size=52428800

            background_dump_dest=/u01/32bit/app/oracle/product/9.0.1/bdump

            core_dump_dest=/u01/32bit/app/oracle/product/9.0.1/cdump

            user_dump_dest=/u01/32bit/app/oracle/product/9.0.1/udump

            timed_statistics=TRUE

            control_files=("/u01/rbdb1/control_01.ctl", "/u01/rbdb1/control_02.ctl")

            db_name=rbdb1

            shared_pool_size=52428800

            sort_area_size=524288

            undo_management=AUTO

            undo_tablespace=UNDOTBS

Step 2: Run the following sqlplus command to connect to the database:

            ]$ sqlplus '/ as sysdba'

Step 3: Startup up the database in NOMOUNT mode:

            SQL> startup nomount

Step 4: Create the Database by issuing the following in the SQL prompt:

            Path names, file names, and sizes will need to be modified

            CREATE DATABASE <db_name>

            MAXLOGFILES 255

            MAXINSTANCES 1

            MAXDATAFILES 256

            MAXLOGHISTORY 256

            DATAFILE '/u01/oracle/rbdb1/system_01.dbf' SIZE 400M REUSE

            SYSAUX DATAFILE '/u01/oracle/rbdb1/sysaux_01.dbf' SIZE 500M REUSE

UNDO TABLESPACE "UNDOTBS" DATAFILE '/u01/oracle/rbdb1/undotbs_01.dbf'

SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

            CHARACTER SET US7ASCII

            LOGFILE GROUP 1 ('/u01/oracle/rbdb1/redo1_01.dbf') SIZE 100M REUSE,

            GROUP 2 ('/u01/oracle/rbdb1/redo1_02.dbf') SIZE 100M REUSE;

Sysaux data file required for database version 10g and above.

Step 5: Create a Temporary Tablespace :

            Path names, file names, and sizes will need to be modified

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/u01/oracle/rbdb1/temp_01.dbf'

            SIZE 40M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED

            EXTENT MANAGEMENT LOCAL;

            ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";

Step 6: Run the following scripts as sys to create data dictionary views and tables.

@?/rdbms/admin/catalog.sql;

@?/rdbms/admin/catblock.sql;

@?/rdbms/admin/catproc.sql;

@?/rdbms/admin/catoctk.sql;

Step 7: Run the following scripts as system to create product user profile and command line help.

@?/sqlplus/admin/pupbld.sql;

@?/sqlplus/admin/help/hlpbld.sql helpus.sql;

Step 8: Put the database in archivelog mode.

SQL> alter database archivelog;

Step 9: Reset the sys and system password and create password file if required.

           

SQL> ALTER USER SYS IDENTIFIED BY <password>;

            SQL> ALTER USER SYSTEM IDENTIFIED BY <password>;

Step 10: Backup created database

Step 11: Create, configure and start the listener.

Step 12: Schedule the required backup and monitoring scripts in cron