Create a Basic Database Oracle Database Tips by Donald Burleson

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.

1.      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 as shown. 

2.      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.

3.      Choose to create a database.

4.      Select all of the nodes.

5.      The new database template is useful to build a simple test database.

6.      Name the first database created 'test?.

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

8.      Choose dedicated server mode.

9.      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.

10.  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.

11.  Verify the path to each datafile by browsing through the items.

12.  Ensure that the Create Database option is selected.

13.  Review the final parameters page.

14.  Creating a basic RAC database with minimal features requires about 30 minutes on most hardware.

15.  Two errors are expected during database creation, depending on what features are included in the database. These errors can be ignored.

16.  When the database is completed, fill change the passwords for SYS and SYSTEM.

Using SQLPLUS

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@test/password
$ 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 oracle1:
$ 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.

GV$ Views

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 synonym_name from dba_synonymswhere synonym_name like 'GV%';

Some gv$ views, such as gv$instance, gv$sessionand gv$buffer_pool_statistics are particularly useful for gathering system information across all instances. However, many gv$ views return cartesian results. For example, gv$active_instancesreturns 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 this view.

racenv

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 RAC Databases

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 once.

The following table shows the various uses for srvctl:

### Switches for srvctl:
# -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 

### Modify the configuration of an instance to a different node:
srvctl modify  instance  -d test -i test1 -n new_oracle1 

### Starting the database, all instances:
srvctl start database -d test
 

### Starting one instance:
srvctl start instance -d test -i test1

### Stopping a database, all instances:
srvctl stop database -d test

srvctl stop 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 

### Stopping one instance:
srvctl stop instance -d test -i test1 -o immediate 

### Verify the status of the database, all instances:
srvctl status database -d test 

### Verify 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 released.

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 partitions.   

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:

mkdir /dev/raw -p
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, cp, mv, and 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 pages.

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.