Document ID: 13481.1
Subject: CREATING A SECOND DATABASE IN THE UNIX ENVIRONMENT
Last Modified: 21 September 1994
Author: Unknown
INTRODUCTION
------------
The following paper provides three methods for creating a second
database within the Unix environment and Oracle7. To help ensure
a better understanding of the corresponding created database files, it
is recommended that the following documentation is also referenced:
-Administrator's Guide
Creating A Database: Chapter 2, page 2-1
-SQL Language Reference Manual
Create Database Command: page 4-148
SETTING UNIX ENVIRONMENT
------------------------
When creating a database, Oracle looks at the environment to see which
database
to create. Therefore, it is essential that before creating a second
database your environment is set with the new database name. The following
environment variables need to be set:
ORACLE_SID - set to the database name you wish to create
ORACLE_HOME - set to full pathname of the Oracle system home directory
PATH - needs to include $ORACLE_HOME/bin
To set your Unix environment use the following commands depending on the Unix
shell you are using:
sh - ORACLE_SID XXX;export ORACLE_SID
csh - setenv ORACLE_SID XXX
Check to be sure it was changed:
echo $ORACLE_SID
<NEW_NAME>
NOTE: Not changing the ORACLE_SID environment and running the create
database will wipe out your existing database and all of its data.
CREATING THE SECOND DATABASE
----------------------------
METHOD I - CREATING THE DATABASE MANUALLY
This method involves typing the create database statement within SQL*DBA.
Using this method allows for more flexibility such as specifying
the MAXDATAFILES parameter or specifying multiple SYSTEM tablespace
database files. However by doing this manually there is also a greater
possibility of syntax errors. In addition there is no logfile automatically
created to record the options which have been specified.
Steps for Method I:
1. Set unix environment (SEE SETTING UNIX EVIRONMENT SECTION ABOVE).
2. Create a new init.ora for your new database by copying the default
one provided by Oracle:
% cp $ORACLE_HOME/dbs/init.ora $ORACLE_HOME/dbs/initNEW_NAME.ora
3. Change db_name parameter in the new initNEW_NAME.ora from DEFAULT to
the new database name.
4. Startup sql*dba in line mode:
sqldba lmode=y
5. Connect to the instance, and startup in a 'NOMOUNT' state:
SQLDBA> connect internal
Connected.
SQLDBA> startup nomount
ORACLE instance started.
SQLDBA>
6. Refer to the SQL Language Reference Guide for the 'CREATE DATABASE'
statement syntax - page 4-148.
Here is a sample create database statement:
SQLDBA> create database NEW_NAME
2> logfile group 1 ('oracle_home/dbs/log1NEW_NAME.dbf') size 500K,
3> group 2 ('oracle_home/dbs/log2NEW_NAME.dbf') size 500K
4> datafile 'oracle_home/dbs/dbsNEW_NAME.dbf' size 20M
5> maxdatafiles 50;
7. Once completed run catalog.sql located in the oracle_home/rdbms/admin
directory. This script must be run under the 'SYS' user or connected
'internal'.
NOTE: catproc.sql must also be run if you have the procedural option
installed.
SQLDBA>@oracle_home/rdbms/admin/catalog.sql
SQLDBA>@oracle_home/rdbms/admin/catproc.sql
8. After the database has been created, the SYSTEM tablespace and SYSTEM
rollback segment will exist. However, a second rollback segment in the
SYSTEM tablespace must be created and activated before any other
tablespaces can be created in the database (Refer to SQL Lanuguage
Reference Manual for full syntax).
Creating the rollback segment:
SYNTAX: CREATE ROLLBACK SEGMENT system2
TABLESPACE SYSTEM
STORAGE (...);
Activating the rollback segment:
SYNTAX: ALTER ROLLBACK SEGMENT system2 ONLINE;
9. Modify the /etc/oratab file by adding the new database name. This
is used by dbstart to startup all databases with a 'Y' entry in this
file. (See page 4-17 of Oracle for Unix technical Reference Guide).
METHOD II - USING THE CRDBXXX.SQL SCRIPT
This method assumes that you have created at least one database through the
install and therefore have a 'crdbXXX.sql' script (XXX being the created
database name). With this option you copy this sql script and make the
necessary modifications. This option allows you to make whatever changes
are desired, such as the MAXDATAFILES parameter or specifying multiple
SYSTEM tablespace database files.
Steps for Method II:
1. Set unix environment (SEE SETTING UNIX EVIRONMENT SECTION ABOVE).
2. Create a new init.ora for your new database by copying the default
one provided by Oracle:
% cp $ORACLE_HOME/dbs/init.ora $ORACLE_HOME/dbs/initNEW_NAME.ora
3. Change db_name parameter in the new initNEW_NAME.ora from DEFAULT to
the new database name.
4. Create a new crdbXXX.sql for your new database by copying the existing one
in the ORACLE_HOME/dbs directory. NOTE: If you have never created a
database through the install, you CANNOT use this method.
5. Modify the crdb<dbname>.sql and change the system datafile,
logfiles, database name, and pfile="path/initNEW_NAME.ora"
6. Run the modified create script:
$ sqldba lmode=y
SQLDBA> @crdbXXX
OPTIONAL!!! If you wish only to create the system tablespace, skip to step 9.
Only go through steps 7 and 8 if you wish to also create the standard
tablespaces the install creates.
7. Create a new crdb2<dbname>.sql by coping an existing one.
Make the nessary changes to is, as above, such as datafile names, sizes,
etc. However, this applies to the standard tablespaces which are created
by the install, including RBS, USERS, TEMP, TOOLS.
8. Run the modified script, skip to step 10:
SQLDBA> @crdb2XXX
9. Run catalog.sql found in the ORACLE_HOME/rdbms/admin directory.
This script should be run while connected internal after the
database is successfully created. NOTE: this step is only neccessary
if you skipped steps 7 and 8.
10.Run catproc.sql found in the ORACLE_HOME/rdbms/admin directory if you
have the procedural option installed. This script must be run while
connected internal or connected as the 'SYS' user.
METHOD III - USING ORAINST
--------------------------
With this method a second database can be created using the orainst.
Since this process is menu driven it is easy to use, in addition
it will run necessary scripts for any product selected. However,
this method does not have all database options available, such as
specifying a higher MAXDATAFILES value. In addition, if this method is
chosen, you must create all the standard non-system tablespaces.
Here are the steps for Method III:
1. Make a copy of your existing config.ora file in the $ORACLE_HOME/dbs
directory. THIS FILE WILL BE OVERWRITTEN WITH THIS METHOD.
% cp $ORACLE_HOME/dbs/config.ora $ORACLE_HOME/dbs/configORIG_DB.ora
2. Run orainst from the $ORACLE_HOME/install directory.
(Note: This may also be in the $ORACLE_HOME/orainst directory.)
3. When running orainst a logfile will be generated. Specify a name
for this logfile so that this file can be easily found and accessed.
4. From the Install Actions choose:
'Create New Database Objects'
5. Enter the new SID for this database.
6. From the Select Available Products choose:
'Oracle7 Server (RDBMS)'
In addition select all other appropriate products that you will
wish to use with the new database.
Once you select all the products, tab to <install> and accept.
7. Continue the installation by answering the appropriate questions.
8. Orainst will let you know what step it is processing. Once it is done,
in addition to a database, you will also have a crdb<DBNAME>.sql discussed
in Method II above. Be sure to check the log specified in step 2
to be sure no errors occurred.
9. Clear up the config.ora confusion:
a. Copy config.ora to new database name
% cp $ORACLE_HOME/dbs/config.ora $ORACLE_HOME/dbs/configNEW_DB.ora
b. Edit initORIGINAL_DB.ora change the ifile entry from config.ora to
configORIG_DB.ora
c. Edit initNEW_DB.ora change the ifile entry from config.ora to
configNEW_DB.ora
ADDITIONAL NOTES:
-----------------
In order to switch from one database to another, you need to be sure your
environment variable is pointing to the correct database name. This can be
done manually with the 'setenv' command, see 'SETTING UNIX ENVIRONMENT' above,
or by running oraenv/coraenv:
For C Shell - source coraenv
For Bourne Shell - . oraenv
see page 1-25 of Unix Technical Reference Guide.
NOTE: If you intend to export from one database and import into the second
database, you will need to precreated all the tablespaces on the 2nd database.
In addition, you need to review the new import 'DESTROY' option in the
oracle7 server utilities users guide, page 2-18.