 |
|
Oracle Tips by Burleson |
Managing Oracle Instance
Creation
As a review from Tutorial one, we took a quick
look at the “create database” command and reviewed how it was used to
initially configure the Oracle database. Here is a sample of a Oracle
database create script:
create
database
"diogenes"
maxinstances 1
maxlogfiles 16
maxloghistory 226
maxlogmembers 2
maxdatafiles 30
noarchivelog
character set "US7ASCII"
SET TIME_ZONE = 'PST';
datafile
'c:\oracle\oradata\system01.dbf' size 246M
logfile
group 1 'c:\oracle\oradata\log01.dbf' size 50K,
group 2 'c:\oracle\oradata\log02.dbf' size 50K,
group 3 'c:\oracle\oradata\log03.dbf' size 50K
;
We are now ready to look deeper into the
database creation phase. When a database is created, Oracle will look
for the parameter file to see other important metrics. This file is
commonly called the init.ora file, and Oracle will always look for the
parameter files in the following locations:
* ORACLE_HOME/database – Oracle 10g
* ORACLE_HOME/dbs – Oracle 10g and previous
Note that ORACLE_HOME system variable is a
symbolic variable that points to the location of the Oracle software.
It is set in the computer at installation time, and can be found on a
PC inside the MS-Windows Registry, and in UNIX by displaying the value
of $ORACLE_HOME.
The initialization file is always in the form
init$ORACLE_SID.ora, where $ORACLE_SID is the name of your Oracle
database. In the example above, the Oracle database name is “diogenes”,
so we would look for a file named initdiogenes.ora in our ORACLE_HOME/database
directory.
On your PC, the ORACLE_HOME should be
c:\oracle\ora90
Take a minute to find the init.ora file on
your Oracle database. (Figure 1)
Let’s take a look inside a typical init.ora
file.
IFILE='C:\oracle\admin\diogenes\pfile\init.ora'
So, where are the parameters? In a default
Oracle 10g install, the init.ora file contains a “pointer” to yet
another file location. This is part of Oracle “Optimal Flexible
Architecture” or OFA. The OFA standard says that the init.ora file
will be located in DBA/ORACLE_SID/pfile.
Lets go to this directory and see if we can
find our init.ora file:
Now that we have located the file, let’s
review the contents.
#######################################################################
# Copyright (c) 1991, 2001 by Oracle Corporation
#######################################################################
###########################################
# Cache and I/O
###########################################
db_block_size=4096
db_cache_size=33554432
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=C:\oracle\admin\diogenes\bdump
core_dump_dest=C:\oracle\admin\diogenes\cdump
timed_statistics=TRUE
user_dump_dest=C:\oracle\admin\diogenes\udump
###########################################
# Distributed, Replication and Snapshot
###########################################
db_domain=""
remote_login_passwordfile=EXCLUSIVE
##########################################
# File Configuration
###########################################
control_files=("C:\oracle\oradata\diogenes\CONTROL01.CTL",
"C:\oracle\oradata\diogenes\CONTROL03.CTL")
###########################################
# MTS
###########################################
dispatchers="(PROTOCOL=TCP)(SER=MODOSE)",
"(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer)",
"(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
###########################################
# Miscellaneous
###########################################
compatible=9.0.0
db_name=diogenes
###########################################
# Network Registration
###########################################
instance_name=diogenes
###########################################
# Pools
###########################################
java_pool_size=33554432
large_pool_size=1048576
shared_pool_size=33554432
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
sort_area_size=524288
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDOTBS
These are the base parameters that were placed
by the Oracle 10g installer when you loaded the software. Now, while
Oracle 10g has “wizards” to automate the creation of a new Oracle
database, the savvy database professional is often called upon to
create a custom Oracle instance.
Here are the steps to manually create an
Oracle database:
1 - Create the OFA directory structure
2 - Add links from ORACLE_HOME/database
3 – Create the init.ora file
4 – Update the listener file
5 – Set the environment
6 – Create the Oracle database
However, one the Oracle database is created,
there is still more work before you can start allocating tablespaces.
These post-creation steps set-up the undo logs and populate the Oracle
data dictionary with basic system information. These post-install
steps include:
1. Install data dictionary views with
catalog.sql
2. Create an additional undo log in the SYSTEM
tablespace
3. Create undo log tablespace
4. Create a TEMP tablespace
5. Create the undo logs
6. Run catproc.sql to populate the dictionary
7. Run the dbmsutil.sql script to create DBMS
utilities
So, let’s review the basic instance creation
process.
1 - Create the directory structure
As we have noted, this init.ora file must
exist before you can create a database. However, you must also create
the OFA directory structure before you can create a Oracle database.
On windows, this involves creating these directories. In this
example, let’s assume we are creating a database called “fred”.
c:\oracle\admin\fred
c:\oracle\admin\fred\pfile – This contains the init.ora file.
c:\oracle\admin\fred\cdump – This directory is for core dumps
c:\oracle\admin\fred\bdump – This directory is for background dumps
and the alert log
c:\oracle\admin\fred\udump – This is the user dump destination
c:\oracle\oradata\fred
– This will contain the data files for the Oracle database
2 - Add links from ORACLE_HOME/database
You must also create a file called
initfred.ora with the following contents:
IFILE='C:\oracle\admin\fred\pfile\init.ora'
3 – Create the init.ora file
Your next task is to place an init.ora file at
the located specified in step 2. Remember, Oracle can accept a host
of default values, and we can create a Oracle database with a minimum
of parameters.
###########################################
# SGA parms
###########################################
db_block_size=8192
db_cache_size=33554432
sort_area_size=524288
java_pool_size=33554432
large_pool_size=1048576
shared_pool_size=33554432
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=C:\oracle\admin\fred\bdump
core_dump_dest=C:\oracle\admin\fred\cdump
timed_statistics=TRUE
user_dump_dest=C:\oracle\admin\fred\udump
###########################################
# File Configuration
###########################################
control_files=("C:\oracle\oradata\fred\CONTROL01.CTL",
"C:\oracle\oradata\fred\CONTROL02.CTL",
"C:\oracle\oradata\fred\CONTROL03.CTL")
###########################################
# Miscellaneous
###########################################
compatible=9.0.0
db_name=fred
###########################################
# Network Registration
###########################################
instance_name=fred
###########################################
# Processes and Sessions
###########################################
processes=150
4 – Update the listener file
The Oracle listener file must be updated to
add the name of the new Oracle database. The listener file is always
located in your ORACLE_HOME/network/admin directory as listener.ora.
On windows, this file is located at c:\oracle\ora90\network\admin.
Here is a sample listener.ora with the new entry for our new “fred”
database:
#
LISTENER.ORA Network Configuration File:
C:\oracle\ora90\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL
= TCP)(HOST = Bask)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\ora90)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = diogenes)
(ORACLE_HOME = C:\oracle\ora90)
(SID_NAME = diogenes)
)
(SID_DESC =
(GLOBAL_DBNAME = fred)
(ORACLE_HOME = C:\oracle\ora90)
(SID_NAME = fred)
)
)
5 – Set the environment
The final step is to enter SQL*Plus as sysdba
and issue the “create database” command.
SQL >
sqlplus /nologin
SQL> connect
internal@fred as sysdba
Connected to an idle instance.
6 – Create the Oracle database
The final step is to execute your “create
database” statement:
startup
nomount pfile=c:\oracle\ora90\database\initfred.ora
create
database
"diogenes"
maxinstances 1
maxlogfiles 16
maxloghistory 226
maxlogmembers 2
maxdatafiles 30
noarchivelog
character set "US7ASCII"
SET TIME_ZONE = 'PST';
datafile
'c:\oracle\oradata\system01.dbf' size 246M
logfile
group 1 'c:\oracle\oradata\log01.dbf' size 50K,
group 2 'c:\oracle\oradata\log02.dbf' size 50K,
group 3 'c:\oracle\oradata\log03.dbf' size 50K
;
Database created.
You are now ready to start the allocation of
undo logs and the population of the Oracle data dictionary. As we can
see, the creation of a new Oracle instance can be quite challenging,
and even with the help of the Oracle wizard, there are hundreds of
considerations. It is the flexibility (and resulting flexibility)
that makes Oracle the world’s most popular database.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
