Friday, June 3, 2011

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

No comments:

Post a Comment

I don't know what you think about my creation, but every words of you help me grow better and stronger!!