HOW TO CREATE MANUALLY DATABASE IN ORACLE 11G

Hi Guys,


This article is going to explain how to create manually database .  Sometimes for testing purpose we may be creating new database or as per user requirement we will create new database  on existing home.  Its very easy to create.


Step1:    Login as oracle user
#su - oracle
$cd /mnt
$mkdir scripts


Here we need to create 8 different files.  Once all file creation is over then run one script , your database will create automatically.


Note:  I am going to create a database name called MK11G .  Just check your path , change accordingly.


File 1:  create a file name called <DBNAME>.sh
ex: $touch MK11G.sh


Open this file add below contents as similar as and save it.


#!/bin/sh
OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/app/oracle/admin/MK11G/adump
mkdir -p /u01/app/oracle/admin/MK11G/dpdump
mkdir -p /u01/app/oracle/admin/MK11G/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/MK11G
mkdir -p /u01/app/oracle/flash_recovery_area
mkdir -p /u01/app/oracle/flash_recovery_area/MK11G
mkdir -p /u01/app/oracle/oradata/MK11G
mkdir -p /u01/app/oracle/product/11.2.0.1/server/dbs
mkdir -p /u01/app/oracle/admin/MK11G/scripts
cp /mnt/scripts/* /u01/app/oracle/admin/MK11G/scripts
umask ${OLD_UMASK}
ORACLE_SID=MK11G; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab: MK11G:/u01/app/oracle/product/11.2.0.1/server:Y
/u01/app/oracle/product/11.2.0.1/server/bin/sqlplus /nolog @/u01/app/oracle/admin/MK11G/scripts/MK11G.sql


*************************************************************************************
This note applicable to all files and all steps. You must change the MK11G name based on your requirement.
Note :  Wherever MK11G is there means thats database name. Suppose your database name is AMK that means you need replace AMK instead of MK11G.
*************************************************************************************


File 2: File name is MK11G.sql,content will be

set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
host /u01/app/oracle/product/11.2.0.1/server/bin/orapwd file=/u01/app/oracle/product/11.2.0.1/server/dbs/orapwMK11G force=y
@/u01/app/oracle/admin/MK11G/scripts/CreateDB.sql
@/u01/app/oracle/admin/MK11G/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/MK11G/scripts/CreateDBCatalog.sql
@/u01/app/oracle/admin/MK11G/scripts/lockAccount.sql
@/u01/app/oracle/admin/MK11G/scripts/postDBCreation.sql


File 3:  File name is lockAccount.sql, content should be


SET VERIFY OFF
set echo on
spool /u01/app/oracle/admin/MK11G/scripts/lockAccount.log append
BEGIN
 FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
'SYS','SYSTEM') )
 LOOP
  dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
  execute immediate 'alter user ' ||
   sys.dbms_assert.enquote_name(
   sys.dbms_assert.schema_name(
   item.USERNAME),false) || ' password expire account lock' ;
 END LOOP;
END;
/
spool off


File 4: File name is CreateDBFiles.sql, content will be


SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/MK11G/scripts/CreateDBFiles.log append
CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/u01/app/oracle/oradata/MK11G/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;
ALTER DATABASE DEFAULT TABLESPACE "USERS";
spool off

File 5: File name is CreateDBCatalog.sql, content will be


SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/MK11G/scripts/CreateDBCatalog.log append
@/u01/app/oracle/product/11.2.0.1/server/rdbms/admin/catalog.sql;
@/u01/app/oracle/product/11.2.0.1/server/rdbms/admin/catblock.sql;
@/u01/app/oracle/product/11.2.0.1/server/rdbms/admin/catproc.sql;
@/u01/app/oracle/product/11.2.0.1/server/rdbms/admin/catoctk.sql;
@/u01/app/oracle/product/11.2.0.1/server/rdbms/admin/owminst.plb;
connect "SYSTEM"/"&&systemPassword"
@/u01/app/oracle/product/11.2.0.1/server/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /u01/app/oracle/admin/MK11G/scripts/sqlPlusHelp.log append
@/u01/app/oracle/product/11.2.0.1/server/sqlplus/admin/help/hlpbld.sql helpus.sql;
spool off
spool off


File 6:  File name is CreateDB.sql


SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/MK11G/scripts/CreateDB.log append
startup nomount pfile="/u01/app/oracle/admin/MK11G/scripts/init.ora";
CREATE DATABASE "MK11G"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/u01/app/oracle/oradata/MK11G/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/MK11G/sysaux01.dbf' SIZE 600M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/MK11G/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/app/oracle/oradata/MK11G/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/MK11G/redo01.log') SIZE 51200K,
GROUP 2 ('/u01/app/oracle/oradata/MK11G/redo02.log') SIZE 51200K,
GROUP 3 ('/u01/app/oracle/oradata/MK11G/redo03.log') SIZE 51200K
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
spool off


File 7:  File name is postDBCreation.sql


SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/MK11G/scripts/postDBCreation.log append
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
create spfile='/u01/app/oracle/product/11.2.0.1/server/dbs/spfileMK11G.ora' FROM pfile='/u01/app/oracle/admin/MK11G/scripts/init.ora';
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup ;
spool off






File 8: File name is init.ora
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################


###########################################
# Cache and I/O
###########################################
db_block_size=8192


###########################################
# Cursors and Library Cache
###########################################
open_cursors=300


###########################################
# Database Identification
###########################################
db_domain=""
db_name=MK11G


###########################################
# File Configuration
###########################################
control_files=("/u01/app/oracle/oradata/MK11G/control01.ctl", "/u01/app/oracle/flash_recovery_area/MK11G/control02.ctl")
db_recovery_file_dest=/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size=5218762752


###########################################
# Miscellaneous
###########################################
compatible=11.2.0.0.0
diagnostic_dest=/u01/app/oracle
memory_target=526385152


###########################################
# Network Registration
###########################################
#local_listener=LISTENER_MK11G


###########################################
# Processes and Sessions
###########################################
processes=150


###########################################
# Security and Auditing
###########################################
audit_file_dest=/u01/app/oracle/admin/MK11G/adump
audit_trail=db
remote_login_passwordfile=EXCLUSIVE


###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1


Step 2:  Give permission to script folder
#su - oracle
$chmod -R 775 /mnt/scripts
$cd /mnt/scripts
$./MK11G.sh


Step3:  Enter the ORACLE SID entry in /etc/oratab


#su - oracle
$vi /etc/oratab
add below similar line
MK11G:/u01/app/oracle/product/11.2.0.1/server:N


step4:  Add entries for listeners


#su - oracle
$ cd /u01/app/oracle/product/11.2.0.1/server/network/admin
$vi listener.ora
add as similar as below ( Here my listener name is LISTENER_MK11G_1570 and port is 1570 and host is inatechsrv.blr.com)
LISTENER_MK11G_1570 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1570))
    )
  )


ADR_BASE_LISTENER_MK11G_1570 = /u01/app/oracle


save it


$vi tnsnames.ora


MK11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1570))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = MK11G.blr.com)
    )
  )


LISTENER_MK11G_1570 = (ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1570))


save it.


Step5:  Register the listener in the database
#su - oracle
$export ORACLE_SID=MK11G
$sqlplus '/as sysdba'
SQL> alter system set local_listener=LISTENER_MK11G_1570 scope=spfile;
system is altered.
SQL>shut immediate
SQL>startup.


Now check the listener status after one minute.


Step6 : Now check the connection establishment, listener services and alert logs and listener logs.


Thats all Your database is fine with 100%.