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:

*.timed_statistics=TRUE
*.undo_management=AUTO
*.undo_tablespace=UNDOTBS
*.audit_file_dest='/oracle/U04/app/admin/rbdb1/adump'
*.audit_trail=db,extended
*.compatible='11.2.0.4.0' -- # set compatible value as required
*.control_files='/oracle/U04/app/oradata/rbdb1/control01.ctl','/oracle/U04/app/fast_recovery_area/rbdb1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='rbdb1'
*.db_recovery_file_dest='/oracle/U04/app/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/oracle/U04/app'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=5047844864
*.sga_max_size=5047844864

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:

SQL> CREATE DATABASE db_name -- replace with your desired DB name
-- orabliss.blogspot.com
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

SQL> CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/u01/oracle/rbdb1/temp_01.dbf'
-- change path, file name and size as per your specifications
SIZE 40M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";

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

SQL> @?/rdbms/admin/catalog.sql;
SQL> @?/rdbms/admin/catblock.sql;
SQL> @?/rdbms/admin/catproc.sql;
SQL> @?/rdbms/admin/catoctk.sql;

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

SQL> conn system/password -- pass SYSTEM user password
SQL> @?/sqlplus/admin/pupbld.sql;
SQL> @?/sqlplus/admin/help/hlpbld.sql helpus.sql;

Step 8: Place 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; -- pass desired password
SQL> ALTER USER SYSTEM IDENTIFIED BY password; -- pass desired 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