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;
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;
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
