Manual Database creation 11g version

MANUAL DATABASE CREATION – 11g version



Lets say we will create a database named ACTIONAL

1. Create the PATHS for database files to be stored.

Control Files 
Archive Location
Database Files
Redolog Files Location


2.Now create the init.ora file( in $ORACLE_HOME/dbs location)

*.bitmap_merge_area_size=10485760
*.compatible='11.2.0.0.0'
*.control_files='/u31_Actional/actional/oradata/control01.ctl','/u31_Actional/actional/oradata/control02.ctl','/u31_Actional/actional/oradata/control03.ctl'
*.control_management_pack_access='NONE'
*.cursor_sharing='SIMILAR'
*.db_block_size=8192
*.db_cache_advice='ON'
*.db_domain=''
*.db_file_multiblock_read_count=128
spfile.db_files=500
*.db_files=500
*.db_name='actional'   =======> Database Name
*.diagnostic_dest='/u01'
*.dml_locks=5000
*.fast_start_mttr_target=0
*.job_queue_processes=4
*.log_archive_dest_1='LOCATION=/u31_Actional/archive'     #### Archivelog Location
*.log_archive_dest_state_1='ENABLE'
*.log_checkpoints_to_alert=FALSE
*.open_cursors=300
*.open_links=10
*.open_links_per_instance=10
*.optimizer_dynamic_sampling=0
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=3092M
*.processes=500
*.query_rewrite_enabled='TRUE'
*.query_rewrite_integrity='TRUSTED'
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sessions=672
*.sga_target=9280M
*.star_transformation_enabled='FALSE'
*.statistics_level='TYPICAL'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=1800
*.undo_tablespace='UNDOTBS1'
*.workarea_size_policy='AUTO'



3.Set environment variables for the new database (actional).
$export ORACLE_HOME=/u01/product/11.2.0/db_1
$export PATH=$ORACLE_HOME/bin:$PATH
$export ORACLE_SID=ACTIONAL

4.Start the database in nomount stage:

$ sqlplus “/ as sysdba”

SQL> startup nomount pfile=’initactiona.ora’;

5. Below the create database script.

CREATE CONTROLFILE “Actional” DATABASE "MANDATO" NORESETLOGS  NOARCHIVELOG
LOGFILE
  GROUP 1 '/u31_Actional/oradata/logfiles/redo1.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/u31_Actional/oradata/logfiles/redo2.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 3 '/u31_Actional/oradata/logfiles/redo3.log'  SIZE 100M BLOCKSIZE 512
DATAFILE
'/u31_Actional/oradata/datafiles/system1.dbf' size 1G,
SYSAUX datafile ‘/u31_Actional/oradata/datafiles/sysaux01.dbf’ size 1G,
default temporary tablespace temp tempfile ‘/u31_Actional/oradata/datafiles/temp01.dbf’ size 1G,
undo tablespace UNDOTBS1 datafile ‘/u31_Actional/oradata/datafiles/undo01.dbf’ size 1G
character set WE8MSWIN1252;

6. Now create the data dictionary views(CATALOG and CATPROC component)

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/catblock.sql
SQL> @?/rdbms/admin/dbmspool.sql
7. Create the product user profile table ( by connecting to system user)

SQL> conn system/manager
SQL> @?/sqlplus/admin/pupbld.sql

8. Create a listener file and start the listener.

LISTENER_actional= (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.46)(PORT=1571)))
SID_LIST_LISTENER_actional=(SID_LIST=(SID_DESC=(SID_NAME=actional)(ORACLE_HOME=/u01/product/11.2.0/db_1)))