Create a Basic
Oracle Database Tips by Burleson Consulting
This chapter will cover creating a basic RAC
databaseby using the
dbcaprogram. To save time, the author suggested that
the first database created not include any optional features. This
will cut database creation time down from hours to minutes. If a basic
database can be created with the current configuration, then a
database with more features can also be created.
The following documents the steps taken to build
an Oracle 9i database.
Create all databases from the oracle1 node. To launch the
Database Configuration Assistant(dbca),
first set the $PATHenvironmental variable with .
oraenv. Respond to the
prompt with *. As the image suggests, there is a help file available
with dbca that can be
accessed with the command dbca ‑help. It is wise to be familiar with
all of the switches available when launching dbca. In this case, run
the database configuration assistant with the command dbca ‑datafileDestination /u02/oradata
If the option to create a clustered database is not available,
Quit the dbca program then
check that cluster manager and the global services daemon are running
on each node.
Choose to create a database.
Select all of the nodes.
The new database template is useful to build a simple test
Name the first database created 'test?.
Database creation can take a very long time. If a simple
database can be created with the current configuration, then any
database can be created. To speed up the database creation, deselect
all of the database options for the first database. When the dbca
prompts to delete a tablespace, click ?Yes?. Click the 'standard
Database Features? button to remove those features as well.
Choose dedicated server mode.
Any and all of these parameters can be changed after the
database is created. Choose any configuration preferred, or simply use
50% of physical memory for Oracle.
The location of the spfile is indicated on the File Locations
tab. It should have a path and file name as shown without having to
edit it. If not, the ‑datafileDestination switch used to launch
dbca was not correct. Upon
clicking next, the dbca
program will produce an error if the path for the
spfile is not accessible by
each node in the cluster.
Verify the path to each datafile by browsing through the items.
Ensure that the Create Database option is selected.
Review the final parameters page.
Creating a basic RAC database with minimal features requires
about 30 minutes on most hardware.
Two errors are expected during database creation, depending on
what features are included in the database. These errors can be
When the database is completed, fill change the passwords for
SYS and SYSTEM.
For many administrators,
sqlpluswill be the preferred tool to use to verify a new
database's existence. There are two methods for connecting with
sqlplus. The first method is
to connect via the listener. The available syntaxes for a database
named 'test? would be:
$ sqlplus system@test1/password
$ sqlplus system@test2/password
Using @test as the connect identifier, the listener will
determine which instance to connect to. Using @test1, the listener
will connect the user to the first instance, and using @test2 the user
will be connected to the second instance.
The second method for connecting to the database
is by exporting the variable $ORACLE_SID so that it names the SID that
is hosted on a given node. This method bypasses the listener. The
syntax would be:
$ ### from
$ export ORACLE_SID=test1
$ sqlplus system/password
$ ### from oracle2:
$ export ORACLE_SID=test2
$ sqlplus system/password
Figure 7.1 demonstrates a simple query on
v$active_instancesthat tells us that two instances are active at this time.
Most database administrators are familiar with
v$ views or dynamic
performance views. A set of gv$ views,
or global dynamic performance views, has been created to support RAC
databases. These views return information for all instances in a
cluster. For example, v$instancewill always return one row with information about the
instance that is supporting the connection. On the other hand,
gv$instancewill return one row for each open instance in the cluster.
For a list of these views, spool the query select
Some gv$ views, such as
gv$buffer_pool_statistics are particularly useful for gathering
system information across all instances. However, many
gv$ views return cartesian
results. For example,
one row for each instance detected by each instance. So, if there are
3 instances in a cluster, you can expect 9 rows to be returned from
The oraenv script is excellent for setting
environmental variables, but it does not set the variable $ORACLE_SID
with the additional numeric digit at the end. One option is to export
the value for $ORACLE_SID by manually typing out the command. The code
depot for this book includes the script racenv which can be used in
the same way as the oraenv
script but includes the numeric digit. Refer to Appendix B for
installation and usage details.
Fixing Sqlplus and
Other Command Line Tools
If you have ever used
sqlplus on a Microsoft
Windows machine, you are aware that pressing the up arrow returns a
history of the commands that were previously issued. This
functionality was not included in the
sqlplus for Linux, nor was
it included in rman, lsnrctlor any of the other Oracle command line tools. To
fix this, install the rlwrapprogram as root, then edit the file ~oracle/.bashrc
to include the aliases shown in figure 7.2. To make the aliases
effective, simply open a new gnome-terminal.
Using srvctl to Manage
A RAC database configuration requires extra tools
to manage the software and instances. One such tool is
srvctl, used to startup,
shutdown and check the status a RAC database. A member of the Linux
dba group can use this tool.
Sqlplus can be used to start and stop an individual instance of
a clustered database. Srvctl,
on the other hand, can be used start or stop all the instances at
The following table shows the various uses for
# -d database_name -i instance_name -n node_name
# -o ORACLE_HOME (for add command)
# -o options (for startup and shutdown commands)
### Add a
database or instance to srvctl repository:
srvctl add database -d test -o $ORACLE_HOME
srvctl add instance -d test -i test1 -n oracle1
### Remove a
database or instance from srvctl repository:
srvctl remove instance -i test1
srvctl remove database -d test
### Print (to
screen) the configuration of a database or instance:
srvctl config database -d test
srvctl config instance -i test1
the configuration of an instance to a different node:
srvctl modify instance -d test -i test1 -n new_oracle1
the database, all instances:
srvctl start database -d test
srvctl start instance -d test -i test1
a database, all instances:
srvctl stop database -d test
database -d test -o normal
srvctl stop database -d test -o transactional
srvctl stop database -d test -o immediate
srvctl stop database -d test -o abort
srvctl stop instance -d test -i test1 -o immediate
the status of the database, all instances:
srvctl status database -d test
the status of instance(s):
srvctl status instance -d test -i test1
srvctl status instance -d test -i test1,test2
Adding a Tablespace on
a RAW partition
A raw partition (also known as a raw device) is a
partition that has no file system and is not mounted, but it is still
used to read and write data. Raw partitions were the first method for
creating data files for RAC databases. They were used before OCFS was
Raw partitions are still in use and may be
preferred by some administrators. Raw partitions are also a pre-cursor
to Automatic Storage Management(ASM),
which is Oracle's storage solution for 10g databases. It is possible
to create an entire RAC database using raw partitions. For these
reasons, it is important to understand the basics of raw
Even though the data files for the newly created
database are stored on OCFS, it is a simple matter to add more
tablespaces and store their associated data files on raw partitions.
To begin, verify that the raw devices
/dev/raw/raw1 and /dev/raw/raw2 exist using the ls command to list
them. Just as /dev/hda represents a hard disk, /dev/raw/raw1 is a
device notation used to represent a raw device.
If the raw devices do not exist, issue the
following commands as root to create them:
mknod ?m 660 /dev/raw/raw1 c 162 1
mknod ?m 660 /dev/raw/raw2 c 162 2
Read the man page for
mknodfor more details on creating the block and character files
used to control devices.
To associate a device to an existing partition,
switch to the root account and issue the commands shown in figure 7.3.
The raw command is used to inform the operating
system that it must associate the raw device /dev/raw/raw1 to the
partition /dev/sda9. This action is called binding a raw device to a
partition. The chowncommand is used to change the ownership of the raw
device to oracle:oinstall.
Commands used to manage files on a file system,
such as ls,
rm cannot be used to manage
a raw partition. The only operating system command that can be used to
transfer data to and from a raw device is the dd command. It is
possible to use the dd
command to take a cold-backup of a raw partition database file, but it
is more practical to use RMAN.
A raw partition can house only one contiguous
chunk of data or ?file.? There are other limitations as well. For
example, using the Logical Volume Manager,
a limit of 255 partitions can be created and slated for raw ?files? on
a single physical disk. As an alternative to using a Logical Volume
Manager, logical partitions can be used, as is demonstrated in these
Once a raw device is bound to its associated
partition, Oracle database software can read and write to the binary
?file? /dev/raw/raw1. Binding does not happen automatically. The
operating system must be told to make the association. Linux uses the
text file /etc/sysconfig/rawdevices to bind raw devices to their
associated partitions on boot. Open the rawdevices file in a text
editor and add the entries to bind the raw devices to their partitions
as shown in Figure 7.4.
With the raw device bound to a partition, Oracle
can use it for a tablespace. Create a tablespace with the command
shown in Figure 7.5, and then create an additional tablespace using
/dev/raw/raw2. It is important to use the REUSE keyword
as Oracle will detect the raw partition as an existing file.
An example of creating a table with 10,000 rows of
data can be found in Figure 7.6. Use this example to create a few
sample tables on the tablespaces you create. Having sample tables that
you have created will enable you to validate upgrades as you continue
with this project.
If you want to learn RAC at home, get the bestselling book "Personal
Oracle Real Application Clusters" by Edward Stoever.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.