Manual Database Creation Oracle 11g

Manually Database Creation is one of the most important works of aDBA.

The procedure is same as previous which we are used for 9i, 10g.

In this database creation I used below features

1. OMF (Oracle Managed File) for datafiles, redolog files & controlfiles
2. FRA (Flash Recovery Area) for Archivelog or backup files

1. Create Required Directories

Microsoft Windows XP [Version 5.1.2600]

(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\UserXP>mkdir D:\app\UserXP\admin\mandb11g

2. Set Enviourment Variable (ORACLE_HOME,ORACLE_SID,PATH)

C:\Documents and Settings\UserXP>set ORACLE_HOME=D:\app\UserXP\product\11.1.0\db
_1

C:\Documents and Settings\UserXP>set PATH=D:\app\UserXP\product\11.1.0\db_1\BIN

C:\Documents and Settings\UserXP>set ORACLE_SID=mandb11g

3. Creating INIT.ORA parameter file 

Set minimum required parameter in INIT.ora file.

Open NOTEPAD file and set below parameters

db_name = 'MANDB11G'
#Database name.

db_create_file_dest='D:\app\UserXP\oradata\mandb11g\data'
#OMF configuration for Datafile,controlfile
db_create_online_log_dest_1='D:\app\UserXP\oradata\mandb11g\redo'
#OMF configuration for redolog file

db_recovery_file_dest='D:\app\UserXP\oradata\mandb11g\flash_recovery'
db_recovery_file_dest_size=4G
#FRA (FLASH RECOVERY AREA configuration)

diagnostic_dest='D:\app\UserXP\admin\mandb11g'
#It is new feature with 11g for trace files (bdump,udump,cdump or many others
#folder created in "DIAG" folder inside "D:\app\UserXP\admin\mandb11g" folder.

Save it in Temporary folder.

4. Create an Instance

C:\Documents and Settings\UserXP>oradim -new -sid mandb11g -startmode auto

Instance created.

5. Connect to an Instance

C:\Documents and Settings\UserXP>sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Aug 9 05:49:42 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

SQL> conn / as sysdba

Connected to an idle instance.

6. Create Server Parameter file (SPFILE)

 

SQL> create spfile from pfile='D:\init.ora';

File created.

7. Start the Instance

SQL> startup nomount

ORACLE instance started.

Total System Global Area  150667264 bytes

Fixed Size                  1345868 bytes

Variable Size              92276404 bytes

Database Buffers           50331648 bytes

Redo Buffers                6713344 bytes

7. Start the Instance

 

SQL> create database mandb11g;

Database created.

9. Create Temporary and Additional Tablespace

SQL> create temporary tablespace temp tempfile size 5m;

Tablespace created.

SQL> create tablespace userdata datafile size 10m autoextend on;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> alter database default tablespace userdata;

Database altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  150667264 bytes

Fixed Size                  1345868 bytes

Variable Size              92276404 bytes

Database Buffers           50331648 bytes

Redo Buffers                6713344 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

10. Run Scripts to build data dictionary views

@%ORACLE_HOME%/rdbms/admin/catalog.sql
@%ORACLE_HOME%/rdbms/admin/catproc.sql
@%ORACLE_HOME%/sqlplus/admin/pupbld.sql

11. Change Database Mode from NO-ARCHIVELOG to ARCHIVELOG.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1331740 bytes
Variable Size 92278244 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes
Database mounted.
SQL> alter database ARCHIVELOG;
Database altered.
SQL> alter database OPEN;
Database altered.

NOTE: It is recommended to run database in ARCHIVELOG MODE

12. Check all database files

SQL> select NAME from v$datafile

  2  union all

  3  select NAME from v$controlfile

  4  union all

  5  select MEMBER from v$logfile;

NAME

--------------------------------------------------------------------------------

D:\APP\USERXP\ORADATA\MANDB11G\DATA\MANDB11G\DATAFILE\O1_MF_SYSTEM_741CP7KL_.DBF

D:\APP\USERXP\ORADATA\MANDB11G\DATA\MANDB11G\DATAFILE\O1_MF_SYSAUX_741CPC5V_.DBF

D:\APP\USERXP\ORADATA\MANDB11G\DATA\MANDB11G\DATAFILE\O1_MF_SYS_UNDO_741CPFMK_.D

BF

D:\APP\USERXP\ORADATA\MANDB11G\DATA\MANDB11G\DATAFILE\O1_MF_USERDATA_741CY41G_.D

BF

D:\APP\USERXP\ORADATA\MANDB11G\REDO\MANDB11G\CONTROLFILE\O1_MF_741CP37T_.CTL

D:\APP\USERXP\ORADATA\MANDB11G\REDO\MANDB11G\ONLINELOG\O1_MF_1_741CP3PG_.LOG

D:\APP\USERXP\ORADATA\MANDB11G\REDO\MANDB11G\ONLINELOG\O1_MF_2_741CP5D6_.LOG

7 rows selected.

SQL>