Manually create an Oracle 11g database

Manually create an Oracle 11g database

Steps to manually create an Oracle 11g database on the Linux platform:

1. Set up the relevant environment variables before creating the database. The three important variables are ORACLE_SID, ORACLE_BASE, and ORACLE_HOME.

The ORACLE_SID variable is the instance identifier, which can be up to 12 characters.

On UNIX platforms, the instance identifier is case sensitive. The ORACLE_BASE parameter decides where the trace files and dump file directories will be located.

 ORACLE_HOME is the location where the Oracle 11g software is installed.

export ORACLE_SID=mydb

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

export PATH=$ORACLE_HOME/bin:$PATH

2. Create a password file using the ORAPWD utility. This allows administrative logins to the Oracle 11g database from tools such as EM Database Control.

cd $ORACLE_HOME/dbs

orapwd file=orapwmydb password=mydb entries=15

3. Create an initialization-parameter file. You can create a text-based pfile, and using SQL*Plus you can create the spfile from the pfile.

cd $ORACLE_HOME/dbs

vi initmydb.ora

mydb.__pga_aggregate_target=771751936

mydb.__sga_target=1124073472

mydb.__shared_io_pool_size=0

mydb.__shared_pool_size=335544320

mydb.__streams_pool_size=16777216

*.audit_file_dest='/u01/app/oracle/admin/mydb/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/mydb/control01.ctl','/u01/app/oracle/fast_recovery_area/mydb/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='mydb'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4227858432

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=mydbXDB)'

*.memory_target=1883242496

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

:wq!

create required directory structure for controlfiles,db_reccovery_dest,audit_file_dest.

-bash-3.2$ pwd

/u01/app/oracle/product/11.2.0/db_1/dbs

-bash-3.2$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 1 20:00:46 2015

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

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

4. Start the instance in NOMOUNT mode:
SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1887350784 bytes

Fixed Size                  2227592 bytes

Variable Size            1157628536 bytes

Database Buffers          721420288 bytes

Redo Buffers                6074368 bytes

5. Create the database using the CREATE DATABASE statement. This statement creates the database with SYSTEM, SYSAUX, TEMP, and UNDOTBS1 tablespaces. It creates control files specified in the location of CONTROL_FILES parameter and redo log files. It also sets a password for SYS and SYSTEM users.

go to location

/u01/app/oracle/oradata

create script in one specified location as 'database.sql'

vi database.sql

CREATE DATABASE "mydb"

MAXINSTANCES 8

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1024

---

CHARACTER SET UTF8

NATIONAL CHARACTER SET AL16UTF16

---

DATAFILE '/u01/app/oracle/oradata/mydb/system1a.dbf' SIZE 1000M extent management local

SYSAUX DATAFILE '/u01/app/oracle/oradata/mydb/sysaux1a.dbf' SIZE 1000M

DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/mydb/temp1a.dbf' SIZE 1000M

extent management local uniform size 1m

UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/app/oracle/oradata/mydb/undotbs1a.dbf' SIZE 1000M

---

LOGFILE

GROUP 1 ('/u01/app/oracle/oradata/mydb/log1a.dbf','/u01/app/oracle/fast_recovery_area/mydb/log1b.dbf') SIZE 100M,

GROUP 2 ('/u01/app/oracle/oradata/mydb/log2a.dbf','/u01/app/oracle/fast_recovery_area/mydb/log2b.dbf') SIZE 100M,

GROUP 3 ('/u01/app/oracle/oradata/mydb/log3a.dbf','/u01/app/oracle/fast_recovery_area/mydb/log3b.dbf') SIZE 100M

---

USER SYS IDENTIFIED BY mydb

USER SYSTEM IDENTIFIED BY mydb

/    

wq!

run this script

SQL>@/u01/app/oracle/oradata/ database.sql

Database created.

6. Create additional tablespaces if any are needed:
SQL> create tablespace "user" datafile '/u01/app/oracle/oradata/mydb/user01.dbf' size 1000m;

Tablespace created.

7. Build data dictionary views and public synonyms

(? in SQL*Plus refers to the ORACLE_HOME directory):

SQL> @?/rdbms/admin/catalog.sql

OR

SQL>@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catalog.sql

8. Build the PL/SQL packages:

SQL> @?/rdbms/admin/catproc.sql

OR

SQL>@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catproc.sql

AND

SQL> @?/sqlplus/admin/pupbld.sql

9. to compile invalid objects at db level

SQL> @?/rdbms/admin/utlrp.sql

OR

SQL>@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlrp.sql

10.some of the optional components are:

@?/rdbms/admin/catblock.sql

@?/rdbms/admin/catoctk.sql

@?/rdbms/admin/owminst.plb

@?/javavm/install/initjvm.sql

@?/xdk/admin/initxml.sql

@?/xdk/admin/xmlja.sql

@?/rdbms/admin/catjava.sql

@?/rdbms/admin/catexf.sql

@?/rdbms/admin/catclust.sql

11.set home location in /etc/oratab

cat /etc/oratab

# This file is used by ORACLE utilities.  It is created by root.sh

# and updated by the Database Configuration Assistant when creating

# a database.

# A colon, ':', is used as the field terminator.  A new line terminates

# the entry.  Lines beginning with a pound sign, '#', are comments.

#

# Entries are of the form:

#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:

#

# The first and second fields are the system identifier and home

# directory of the database respectively.  The third filed indicates

# to the dbstart utility that the database should , "Y", or should not,

# "N", be brought up at system boot time.

#

# Multiple entries with the same $ORACLE_SID are not allowed.

#

#

mydb:/u01/app/oracle/product/11.2.0/db_1:N

bash-3.2$

12. check the database has successfully created:

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

ORCL      READ WRITE

SQL> select comp_name,comp_id,status,version from dba_registry;

COMP_NAME                                COMP_ID                                  STATUS                    VERSION

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

Oracle Expression Filter                 EXF                                      VALID                     11.2.0.2.0

Oracle Workspace Manager                 OWM                                      VALID                     11.2.0.2.0

Oracle Database Catalog Views            CATALOG                                  VALID                     11.2.0.2.0

Oracle Database Packages and Types       CATPROC                                  VALID                     11.2.0.2.0

JServer JAVA Virtual Machine             JAVAVM                                   VALID                     11.2.0.2.0

Oracle XDK                               XML                                      VALID                     11.2.0.2.0

Oracle Database Java Packages            CATJAVA                                  VALID                     11.2.0.2.0

Oracle Real Application Clusters         RAC                                      INVALID                   11.2.0.2.0

8 rows selected.

******************************************************************************************************

In windows the process is same as above, but in windows you have to create oracle instance using oradim utility

this is the only differece between windows and linux.

ordim -new -sid mydb pfile='d:\initmydb.ora'  it will create a oracle service in services.msc