Build Your Own Oracle RAC Cluster on Oracle Enterprise Linux and iSCSI (Continued)

For development and testing only; production deployments will not be supported!

Perform the following installation procedures from only one of the Oracle RAC nodes in the cluster (linux1)! The Oracle Clusterware software will be installed to both of Oracle RAC nodes in the cluster by the Oracle Universal Installer.

You are now ready to install the "cluster" part of the environment - the Oracle Clusterware. In a previous section, you downloaded and extracted the install files for Oracle Clusterware to linux1 in the directory ~oracle/orainstall/clusterware. This is the only node from which you need to perform the install.

During the installation of Oracle Clusterware, you will be asked for the nodes involved and to configure in the RAC cluster. Once the actual installation starts, it will copy the required software to all nodes using the remote access we configured in the section Section 14 ("Configure RAC Nodes for Remote Access").

So, what exactly is the Oracle Clusterware responsible for? It contains all of the cluster and database configuration metadata along with several system management features for RAC. It allows the DBA to register and invite an Oracle instance (or instances) to the cluster. During normal operation, Oracle Clusterware will send messages (via a special ping operation) to all nodes configured in the cluster, often called the "heartbeat." If the heartbeat fails for any of the nodes, it checks with the Oracle Clusterware configuration files (on the shared disk) to distinguish between a real node failure and a network failure.

After installing Oracle Clusterware, the Oracle Universal Installer (OUI) used to install the Oracle 10g database software (next section) will automatically recognize these nodes. Like the Oracle Clusterware install you will be performing in this section, the Oracle Database 10g software only needs to be run from one node. The OUI will copy the software packages to all nodes configured in the RAC cluster.

Oracle Clusterware Shared Files

The two shared files (actually file groups) used by Oracle Clusterware will be stored on the Oracle Cluster File System, Release 2 (OFCS2) we created earlier. The two shared Oracle Clusterware file groups are:

• Oracle Cluster Registry (OCR)
  • File 1 : /u02/oradata/orcl/OCRFile
  • File 2 : /u02/oradata/orcl/OCRFile_mirror
  • Size : (2 * 100MB) = 200M

• CRS Voting Disk
  • File 1 : /u02/oradata/orcl/CSSFile
  • File 2 : /u02/oradata/orcl/CSSFile_mirror1
  • File 3 : /u02/oradata/orcl/CSSFile_mirror2
  • Size : (3 * 20MB) = 60MB

Note: It is not possible to use Automatic Storage Management (ASM) for the two shared Oracle Clusterware files: Oracle Cluster Registry (OCR) or the CRS Voting Disk files. The problem is that these files need to be in place and accessible BEFORE any Oracle instances can be started. For ASM to be available, the ASM instance would need to be run first.

Also note that the two shared files could be stored on the OCFS2, shared RAW devices, or another vendor's clustered file system.

Verifying Terminal Shell Environment

Verify Server and Enable X Server Access

# hostname
linux1
# xhost +
access control disabled, clients can connect from any host

Login as the oracle User Account and Set DISPLAY (if necessary)

# su - oracle
$ # IF YOU ARE USING A REMOTE CLIENT TO CONNECT TO THE
$ # NODE PERFORMING THE INSTALL
$ DISPLAY=<your local workstation>:0.0
$ export DISPLAY

Verify Remote Access / User Equivalence

Verify you are able to run the Secure Shell commands (ssh or scp) or the Remote Shell commands (rsh and rcp) on the Linux server you will be running the Oracle Universal Installer from against all other Linux servers in the cluster without being prompted for a password.

When using the secure shell method, user equivalence will need to be enabled on any new terminal shell session before attempting to run the OUI. To enable user equivalence for the current terminal shell session, perform the following steps remembering to enter the pass phrase for each key that you generated when prompted:

$ exec /usr/bin/ssh-agent $SHELL
$ /usr/bin/ssh-add
Enter passphrase for /home/oracle/.ssh/id_rsa: xxxxx
Identity added: /home/oracle/.ssh/id_rsa (/home/oracle/.ssh/id_rsa)
Identity added: /home/oracle/.ssh/id_dsa (/home/oracle/.ssh/id_dsa)

$ ssh linux1 "date;hostname"
Thu Jun 28 03:06:34 EDT 2007
linux1

$ ssh linux2 "date;hostname"
Thu Jun 28 03:07:08 EDT 2007
linux2

When using the remote shell method, user equivalence is generally defined in the /etc/hosts.equiv file for the oracle user account and is enabled on all new terminal shell sessions:

$ rsh linux1 "date;hostname"
Thu Jun 28 03:04:25 EDT 2007
linux1

$ rsh linux2 "date;hostname"
Thu Jun 28 03:04:59 EDT 2007
linux2

Installing Cluster Ready Services

Perform the following tasks to install the Oracle Clusterware:

$ cd ~oracle
$ ~oracle/orainstall/clusterware/runInstaller -ignoreSysPrereqs
      

# chmod 644 /etc/oraInst.loc
# ls -l /etc/oraInst.loc
-rw-r--r--  1 root root 63 Sep  3 11:06 /etc/oraInst.loc

Verify Oracle Clusterware Installation

After the installation of Oracle Clusterware, we can run through several tests to verify the install was successful. Run the following commands on both nodes in the RAC cluster.

Check cluster nodes

$ /u01/app/crs/bin/olsnodes -n
linux1 1
linux2 2

$ ls -l /etc/init.d/init.*
-r-xr-xr-x  1 root root  1951 Jun 28 08:57 /etc/init.d/init.crs
-r-xr-xr-x  1 root root  4714 Jun 28 08:57 /etc/init.d/init.crsd
-r-xr-xr-x  1 root root 35394 Jun 28 08:57 /etc/init.d/init.cssd
-r-xr-xr-x  1 root root  3190 Jun 28 08:57 /etc/init.d/init.evmd

Perform the following installation procedures from only one of the Oracle RAC nodes in the cluster (linux1)! The Oracle Database software will be installed to both of Oracle RAC nodes in the cluster by the Oracle Universal Installer.

After successfully installing the Oracle Clusterware software, the next step is to install the Oracle Database 10g Release 2 (10.2.0.1.0) with RAC.

For the purpose of this example, you will forgo the "Create Database" option when installing the software. You will, instead, create the database using the Database Configuration Assistant (DBCA) after the install.

Like the Oracle Clusterware install (previous section), the Oracle 10g database software only needs to be run from one node. The OUI will copy the software packages to all nodes configured in the RAC cluster.

Verifying Terminal Shell Environment

Login as the oracle User Account and Set DISPLAY (if necessary)

# su - oracle
$ # IF YOU ARE USING A REMOTE CLIENT TO CONNECT TO THE
$ # NODE PERFORMING THE INSTALL
$ DISPLAY=<your local workstation>:0.0
$ export DISPLAY

Verify Remote Access / User Equivalence

Verify you are able to run the Secure Shell commands (ssh or scp) or the Remote Shell commands (rsh and rcp) on the Linux server you will be running the Oracle Universal Installer from against all other Linux servers in the cluster without being prompted for a password.

When using the secure shell method, user equivalence will need to be enabled on any new terminal shell session before attempting to run the OUI. To enable user equivalence for the current terminal shell session, perform the following steps remembering to enter the pass phrase for each key that you generated when prompted:

$ exec /usr/bin/ssh-agent $SHELL

$ /usr/bin/ssh-add
Enter passphrase for /home/oracle/.ssh/id_rsa: xxxxx
Identity added: /home/oracle/.ssh/id_rsa (/home/oracle/.ssh/id_rsa)
Identity added: /home/oracle/.ssh/id_dsa (/home/oracle/.ssh/id_dsa)

$ ssh linux1 "date;hostname"
Thu Jun 28 03:06:34 EDT 2007
linux1
$ ssh linux2 "date;hostname"
Thu Jun 28 03:07:08 EDT 2007
linux2

When using the remote shell method, user equivalence is generally defined in the /etc/hosts.equiv file for the oracle user account and is enabled on all new terminal shell sessions:

$ rsh linux1 "date;hostname"
Thu Jun 28 03:04:25 EDT 2007
linux1

$ rsh linux2 "date;hostname"
Thu Jun 28 03:04:59 EDT 2007
linux2

Run the Oracle Cluster Verification Utility

Before installing the Oracle Database Software, we should run the following database pre-installation check using the Cluster Verification Utility (CVU).

Note: Instructions for configuring CVU can be found in the section "Prerequisites for Using Cluster Verification Utility discussed earlier in this article.

$ cd ~oracle/orainstall/clusterware/cluvfy
$ ./runcluvfy.sh stage -pre dbinst -n linux1,linux2 -r 10gR2 -verbose

Review the CVU report. Note that this report will contain the same errors we received when checking pre-installation tasks for CRS — failure to find a suitable set of interfaces for VIPs and the failure to find specific RPM packages that do not exist in Enterprise Linux 4 Update 5. These two errors can be safely ignored.

Install Oracle Database 10g Release 2 Software

Install the Oracle Database 10g Release 2 software with the following:

$ cd ~oracle
$ ~oracle/orainstall/database/runInstaller -ignoreSysPrereqs
      

Checking available swap space requirements...
Expected result: 3036MB
Actual Result: 1983MB

Perform the following installation procedures from only one of the Oracle RAC nodes in the cluster (linux1)! The Oracle10g Companion CD software will be installed to both of Oracle RAC nodes in the cluster by the Oracle Universal Installer.

After successfully installing the Oracle Database software, the next step is to install the Oracle 10g Release 2 Companion CD software (10.2.0.1.0).

Please keep in mind that this is an optional step. For the purpose of this guide, my testing database will often make use of the Java Virtual Machine (Java VM) and Oracle interMedia and therefore will require the installation of the Oracle Database 10g Companion CD. The type of installation to perform will be the Oracle Database 10g Products installation type.

This installation type includes the Natively Compiled Java Libraries (NCOMP) files to improve Java performance. If you do not install the NCOMP files, the ORA-29558:JAccelerator (NCOMP) not installed error occurs when a database that uses Java VM is upgraded to the patch release.

Verifying Terminal Shell Environment

Login as the oracle User Account and Set DISPLAY (if necessary)

# su - oracle
$ # IF YOU ARE USING A REMOTE CLIENT TO CONNECT TO THE
$ # NODE PERFORMING THE INSTALL
$ DISPLAY=<your local workstation>:0.0
$ export DISPLAY

Verify Remote Access / User Equivalence

Verify you are able to run the Secure Shell commands (ssh or scp) or the Remote Shell commands (rsh and rcp) on the Linux server you will be running the Oracle Universal Installer from against all other Linux servers in the cluster without being prompted for a password.

When using the secure shell method, user equivalence will need to be enabled on any new terminal shell session before attempting to run the OUI. To enable user equivalence for the current terminal shell session, perform the following steps remembering to enter the pass phrase for each key that you generated when prompted:

$ exec /usr/bin/ssh-agent $SHELL
$ /usr/bin/ssh-add
Enter passphrase for /home/oracle/.ssh/id_rsa: xxxxx
Identity added: /home/oracle/.ssh/id_rsa (/home/oracle/.ssh/id_rsa)
Identity added: /home/oracle/.ssh/id_dsa (/home/oracle/.ssh/id_dsa)

$ ssh linux1 "date;hostname"
Thu Jun 28 03:06:34 EDT 2007
linux1

$ ssh linux2 "date;hostname"
Thu Jun 28 03:07:08 EDT 2007
linux2

When using the remote shell method, user equivalence is generally defined in the /etc/hosts.equiv file for the oracle user account and is enabled on all new terminal shell sessions:

$ rsh linux1 "date;hostname"
Thu Jun 28 03:04:25 EDT 2007
linux1

$ rsh linux2 "date;hostname"
Thu Jun 28 03:04:59 EDT 2007
linux2

Install Oracle Database 10g Companion CD Software

Install the Oracle Database 10g Companion CD software with the following:

$ cd ~oracle
$ ~oracle/orainstall/companion/runInstaller -ignoreSysPrereqs
      

Perform the following configuration procedures from only one of the Oracle RAC nodes in the cluster (linux1)! The Network Configuration Assistant (NETCA) will setup the TNS listener in a clustered configuration on both of Oracle RAC nodes in the cluster..

The DBCA requires the Oracle TNS Listener process to be configured and running on all nodes in the RAC cluster before it can create the clustered database.

The process of creating the TNS listener only needs to be performed on one node in the cluster. All changes will be made and replicated to all nodes in the cluster. On one of the nodes (I will be using linux1) bring up the NETCA and run through the process of creating a new TNS listener process and also configure the node for local access.

Verifying Terminal Shell Environment

Login as the oracle User Account and Set DISPLAY (if necessary)

# su - oracle
$ # IF YOU ARE USING A REMOTE CLIENT TO CONNECT TO THE
$ # NODE PERFORMING THE INSTALL
$ DISPLAY=<your local workstation>:0.0
$ export DISPLAY

Verify Remote Access / User Equivalence

Verify you are able to run the Secure Shell commands (ssh or scp) or the Remote Shell commands (rsh and rcp) on the Linux server you will be running the Oracle Universal Installer from against all other Linux servers in the cluster without being prompted for a password.

When using the secure shell method, user equivalence will need to be enabled on any new terminal shell session before attempting to run the OUI. To enable user equivalence for the current terminal shell session, perform the following steps remembering to enter the pass phrase for each key that you generated when prompted:

$ exec /usr/bin/ssh-agent $SHELL
$ /usr/bin/ssh-add
Enter passphrase for /home/oracle/.ssh/id_rsa: xxxxx
Identity added: /home/oracle/.ssh/id_rsa (/home/oracle/.ssh/id_rsa)
Identity added: /home/oracle/.ssh/id_dsa (/home/oracle/.ssh/id_dsa)

$ ssh linux1 "date;hostname"
Thu Jun 28 03:06:34 EDT 2007
linux1

$ ssh linux2 "date;hostname"
Thu Jun 28 03:07:08 EDT 2007
linux2

When using the remote shell method, user equivalence is generally defined in the /etc/hosts.equiv file for the oracle user account and is enabled on all new terminal shell sessions:

$ rsh linux1 "date;hostname"
Thu Jun 28 03:04:25 EDT 2007
linux1

$ rsh linux2 "date;hostname"
Thu Jun 28 03:04:59 EDT 2007
linux2

Run the Network Configuration Assistant

To start the NETCA, run the following:

$ netca &
      

Screen Name	Response
Select the Type of Oracle Net Services Configuration	Select Cluster Configuration
Select the nodes to configure	Select all of the nodes: linux1 and linux2.
Type of Configuration	Select Listener configuration.
Listener Configuration - Next 6 Screens	The following screens are now like any other normal listener configuration. You can simply accept the default parameters for the next six screens:    What do you want to do: Add    Listener name: LISTENER    Selected protocols: TCP    Port number: 1521    Configure another listener: No    Listener configuration complete! [ Next ] You will be returned to this Welcome (Type of Configuration) Screen.
Type of Configuration	Select Naming Methods configuration.
Naming Methods Configuration	The following screens are:    Selected Naming Methods: Local Naming    Naming Methods configuration complete! [ Next ] You will be returned to this Welcome (Type of Configuration) Screen.
Type of Configuration	Click Finish to exit the NETCA.

The Oracle TNS listener process should now be running on all nodes in the RAC cluster:

$ hostname
linux1

$ ps -ef | grep lsnr | grep -v 'grep' | grep -v 'ocfs' | awk '{print $9}'
LISTENER_LINUX1

=====================

$ hostname
linux2

$ ps -ef | grep lsnr | grep -v 'grep' | grep -v 'ocfs' | awk '{print $9}'
LISTENER_LINUX2

The database creation process should only be performed from one of the Oracle RAC nodes in the cluster (linux1)!

We will use the DBCA to create the clustered database.

Before executing the DBCA, make sure that $ORACLE_HOME and $PATH are set appropriately for the $ORACLE_BASE/product/10.2.0/db_1 environment.

You should also verify that all services we have installed up to this point (Oracle TNS listener, Oracle Clusterware processes, etc.) are running before attempting to start the clustered database creation process.

Verifying Terminal Shell Environment

Login as the oracle User Account and Set DISPLAY (if necessary)

# su - oracle
$ # IF YOU ARE USING A REMOTE CLIENT TO CONNECT TO THE
$ # NODE PERFORMING THE INSTALL
$ DISPLAY=<your local workstation>:0.0
$ export DISPLAY

Verify Remote Access / User Equivalence

Verify you are able to run the Secure Shell commands (ssh or scp) or the Remote Shell commands (rsh and rcp) on the Linux server you will be running the Oracle Universal Installer from against all other Linux servers in the cluster without being prompted for a password.

When using the secure shell method, user equivalence will need to be enabled on any new terminal shell session before attempting to run the OUI. To enable user equivalence for the current terminal shell session, perform the following steps remembering to enter the pass phrase for each key that you generated when prompted:

$ exec /usr/bin/ssh-agent $SHELL
$ /usr/bin/ssh-add
Enter passphrase for /home/oracle/.ssh/id_rsa: xxxxx
Identity added: /home/oracle/.ssh/id_rsa (/home/oracle/.ssh/id_rsa)
Identity added: /home/oracle/.ssh/id_dsa (/home/oracle/.ssh/id_dsa)

$ ssh linux1 "date;hostname"
Thu Jun 28 03:06:34 EDT 2007
linux1

$ ssh linux2 "date;hostname"
Thu Jun 28 03:07:08 EDT 2007
linux2

When using the remote shell method, user equivalence is generally defined in the /etc/hosts.equiv file for the oracle user account and is enabled on all new terminal shell sessions:

$ rsh linux1 "date;hostname"
Thu Jun 28 03:04:25 EDT 2007
linux1

$ rsh linux2 "date;hostname"
Thu Jun 28 03:04:59 EDT 2007
linux2

Run the Oracle Cluster Verification Utility

Before creating the Oracle clustered database, we should run the following database configuration check using the Cluster Verification Utility (CVU).

Note: Instructions for configuring CVU can be found in the section "Prerequisites for Using Cluster Verification Utility discussed earlier in this article.

$ cd ~oracle/orainstall/clusterware/cluvfy
$ ./runcluvfy.sh stage -pre dbcfg -n linux1,linux2 -d ${ORACLE_HOME} -verbose

Review the CVU report. Note that this report will contain the same error we received when checking pre-installation tasks for CRS — failure to find a suitable set of interfaces for VIPs. This error can be safely ignored.

Create the Clustered Database

To start the database creation process, run the following:

$ dbca &
      

When the DBCA has completed, you will have a fully functional Oracle RAC cluster running!

Create the orcl_taf Service

During the creation of the Oracle clustered database, you added a service named orcl_taf that will be used to connect to the database with TAF enabled. During several of my installs, the service was added to the tnsnames.ora, but was never updated as a service for each Oracle instance.

Use the following to verify the orcl_taf service was successfully added:

SQL> show parameter service

NAME                 TYPE        VALUE
-------------------- ----------- --------------------------------
service_names        string      orcl.idevelopment.info, orcl_taf

SQL> show parameter service

NAME                 TYPE        VALUE
-------------------- ----------- --------------------------
service_names        string      orcl.idevelopment.info

SQL> alter system set service_names = 
  2  'orcl.idevelopment.info, orcl_taf.idevelopment.info' scope=both;

This chapter describes several optional tasks that can be applied to your new Oracle 10g environment in order to enhance availability as well as database management.

Enabling Archive Logs in a RAC Environment

Whether a single instance or clustered database, Oracle tracks and logs all changes to database blocks in online redolog files. In an Oracle RAC environment, each instance will have its own set of online redolog files known as a thread. Each Oracle instance will use its group of online redologs in a circular manner. Once an online redolog fills, Oracle moves to the next one. If the database is in "Archive Log Mode", Oracle will make a copy of the online redo log before it gets reused. A thread must contain at least two online redologs (or online redolog groups). The same holds true for a single instance configuration. The single instance must contain at least two online redologs (or online redolog groups).

The size of an online redolog file is completely independent of another intances' redolog size. Although in most configurations the size is the same, it may be different depending on the workload and backup / recovery considerations for each node. It is also worth mentioning that each instance has exclusive write access to its own online redolog files. In a correctly configured RAC environment, however, each instance can read another instance's current online redolog file to perform instance recovery if that instance was terminated abnormally. It is therefore a requirement that online redo logs be located on a shared storage device (just like the database files).

As already mentioned, Oracle writes to its online redolog files in a circular manner. When the current online redolog fills, Oracle will switch to the next one. To facilitate media recovery, Oracle allows the DBA to put the database into "Archive Log Mode" which makes a copy of the online redolog after it fills (and before it gets reused). This is a process known as archiving.

The Database Configuration Assistant (DBCA) allows users to configure a new database to be in archive log mode, however most DBA's opt to bypass this option during initial database creation. In cases like this where the database is in no archive log mode, it is a simple task to put the database into archive log mode. Note however that this will require a short database outage. From one of the nodes in the Oracle RAC configuration, use the following tasks to put a RAC enabled database into archive log mode. For the purpose of this article, I will use the node linux1 which runs the orcl1 instance:

1. Login to one of the nodes (i.e. linux1) and disable the cluster instance parameter by setting cluster_database to FALSE from the current instance:

   $ sqlplus "/ as sysdba"
   SQL> alter system set cluster_database=false scope=spfile sid='orcl1';

2. Shutdown all instances accessing the clustered database:

   $ srvctl stop database -d orcl

3. Using the local instance, MOUNT the database:

   $ sqlplus "/ as sysdba"
   SQL> startup mount

4. Enable archiving:

   SQL> alter database archivelog;

5. Re-enable support for clustering by modifying the instance parameter cluster_database to TRUE from the current instance:

   SQL> alter system set cluster_database=true scope=spfile sid='orcl1';

6. Shutdown the local instance:

   SQL> shutdown immediate

7. Bring all instance back up using srvctl:

   $ srvctl start database -d orcl

8. (Optional) Bring any services (i.e. TAF) back up using srvctl:

   $ srvctl start service -d orcl

9. Login to the local instance and verify Archive Log Mode is enabled:

   $ sqlplus "/ as sysdba"
   SQL> archive log list
   Database log mode              Archive Mode
   Automatic archival             Enabled
   Archive destination            USE_DB_RECOVERY_FILE_DEST
   Oldest online log sequence     83
   Next log sequence to archive   84
   Current log sequence           84

After enabling Archive Log Mode, each instance in the RAC configuration can automatically archive redologs!

Download and Install Custom Oracle Database Scripts

DBA's rely on Oracle's data dictionary views and dynamic performance views in order to support and better manage their databases. Although these views provide a simple and easy mechanism to query critical information regarding the database, it helps to have a collection of accurate and readily available SQL scripts to query these views.

In this section you will download and install a collection of Oracle DBA scripts that can be used to manage many aspects of your database including space management, performance, backups, security, and session management. The Oracle DBA scripts archive can be downloaded using the following link http://www.idevelopment.info/data/Oracle/DBA_scripts/common.zip. As the oracle user account, download the common.zip archive to the $ORACLE_BASE directory of each node in the cluster. For the purpose of this example, the common.zip archive will be copied to /u01/app/oracle. Next, unzip the archive file to the $ORACLE_BASE directory.

For example, perform the following on both nodes in the Oracle RAC cluster as the oracle user account:

$ mv common.zip /u01/app/oracle
$ cd /u01/app/oracle
$ unzip common.zip

The final step is to verify (or set) the appropriate environment variable for the current UNIX shell to ensure the Oracle SQL scripts can be run from SQL*Plus while in any directory. For UNIX verify the following environment variable is set and included in your login shell script:

ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export ORACLE_PATH

Note that the ORACLE_PATH environment variable should already be set in the .bash_profile login script that was created in the section Create Login Script for oracle User Account.

Now that the Oracle DBA scripts have been unzipped and the UNIX environment variable ($ORACLE_PATH) has been set to the appropriate directory, you should now be able to run any of the SQL scripts in your $ORACLE_BASE/common/oracle/sql while logged into SQL*Plus. For example, to query tablespace information while logged into the Oracle database as a DBA user:

SQL> @dba_tablespaces

Status    Tablespace Name TS Type      Ext. Mgt.  Seg. Mgt.    Tablespace Size    Used (in bytes) Pct. Used
--------- --------------- ------------ ---------- --------- ------------------ ------------------ ---------
ONLINE    UNDOTBS1        UNDO         LOCAL      MANUAL         1,283,457,024          9,043,968         1
ONLINE    SYSAUX          PERMANENT    LOCAL      AUTO             524,288,000        378,732,544        72
ONLINE    USERS           PERMANENT    LOCAL      AUTO           2,147,483,648        321,257,472        15
ONLINE    SYSTEM          PERMANENT    LOCAL      MANUAL           838,860,800        505,544,704        60
ONLINE    INDX            PERMANENT    LOCAL      AUTO           1,073,741,824             65,536         0
ONLINE    UNDOTBS2        UNDO         LOCAL      MANUAL         1,283,457,024         22,282,240         2
ONLINE    TEMP            TEMPORARY    LOCAL      MANUAL         1,073,741,824         92,274,688         9
                                                            ------------------ ------------------ ---------
avg                                                                                                      23
sum                                                              8,225,030,144      1,329,201,152

7 rows selected.

To obtain a list of all available Oracle DBA scripts while logged into SQL*Plus, run the help.sql script:

SQL> @help.sql

========================================
Automatic Shared Memory Management
========================================
asmm_components.sql

========================================
Automatic Storage Management
========================================
asm_alias.sql
asm_clients.sql
asm_diskgroups.sql
asm_disks.sql
asm_disks_perf.sql
asm_drop_files.sql
asm_files.sql
asm_files2.sql
asm_templates.sql

< --- SNIP --- >

perf_top_sql_by_buffer_gets.sql
perf_top_sql_by_disk_reads.sql

========================================
Workspace Manager
========================================
wm_create_workspace.sql
wm_disable_versioning.sql
wm_enable_versioning.sql
wm_freeze_workspace.sql
wm_get_workspace.sql
wm_goto_workspace.sql
wm_merge_workspace.sql
wm_refresh_workspace.sql
wm_remove_workspace.sql
wm_unfreeze_workspace.sql
wm_workspaces.sql

Create Shared Oracle Password Files

In this section, I present the steps required to configure a shared Oracle password file between all instances in the Oracle clustered database. The password file for the database in UNIX is located at $ORACLE_HOME/dbs/orapw<ORACLE_SID> for each instance and contains a list of all database users that have SYSDBA privileges. When a database user is granted the SYSDBA role, the instance records this in the database password file for the instance you are logged into. But what about the other instances in the cluster? The database password file on other instances do not get updated and will not contain the user who was just granted the SYSDBA role. Therefore a program (like RMAN) that tries to login as this new user with SYSDBA privileges will fail if it tries to use an instance with a password file that does not contain his or her name.

To resolve this problem, a common solution is to place a single database password file on a shared / clustered file system and then create symbolic links from each of the instances to this single version of the database password file. Since the environment described in this article makes use of the Oracle Clustered File System (OCFS2), we will use it to store the single version of the database password file.

In this section, we will also be including the Oracle password file for the ASM instance.

1. Create the database password directory on the clustered file system mounted on /u02/oradata/orcl. Perform the following from only one node in the cluster as the oracle user account - (linux1):

   $ mkdir -p /u02/oradata/orcl/dbs

2. From one node in the cluster (linux1), move the database password files to the database password directory on the clustered file system. Chose a node that contains a database password file that has the most recent SYSDBA additions. In most cases, this will not matter since any missing entries can be easily added by granting them the SYSDBA role - (plus the fact that this is a fresh install and unlikely you created any SYSDBA users at this point!). Note that the database server does not need to be shutdown while performing the following actions. From linux1 as the oracle user account:

   $ mv $ORACLE_HOME/dbs/orapw+ASM1 /u02/oradata/orcl/dbs/orapw+ASM
   $ mv $ORACLE_HOME/dbs/orapworcl1 /u02/oradata/orcl/dbs/orapworcl
   
   $ ln -s /u02/oradata/orcl/dbs/orapw+ASM $ORACLE_HOME/dbs/orapw+ASM1
   $ ln -s /u02/oradata/orcl/dbs/orapworcl $ORACLE_HOME/dbs/orapworcl1

3. From the second node in the cluster (linux2):

   $ rm $ORACLE_HOME/dbs/orapw+ASM2
   $ rm $ORACLE_HOME/dbs/orapworcl2
   
   $ ln -s /u02/oradata/orcl/dbs/orapw+ASM $ORACLE_HOME/dbs/orapw+ASM2
   $ ln -s /u02/oradata/orcl/dbs/orapworcl $ORACLE_HOME/dbs/orapworcl2

Now, when a user is granted the SYSDBA role, all instances will have access to the same password file:

SQL> GRANT sysdba TO scott;

Ensure that the TNS networking files are configured on both Oracle RAC nodes in the cluster!

listener.ora

We already covered how to create a TNS listener configuration file (listener.ora) for a clustered environment in Section 23. The listener.ora file should be properly configured and no modifications should be needed.

For clarity, I have included a copy of the listener.ora file from my node linux1 in this guide's support files. I've also included a copy of my tnsnames.ora file that was configured by Oracle and can be used for testing the Transparent Application Failover (TAF). This file should already be configured on both Oracle RAC nodes in the cluster.

You can include any of these entries on other client machines that need access to the clustered database.

Connecting to Clustered Database From an External Client

This is an optional step, but I like to perform it in order to verify my TNS files are configured correctly. Use another machine (i.e. a Windows machine connected to the network) that has Oracle installed and add the TNS entries (in the tnsnames.ora) from either of the nodes in the cluster that were created for the clustered database.

Note: Verify that the machine you are connecting from can resolve all host names exactly how they appear in the listener.ora and tnsnames.ora files. For the purpose of this document, the machine you are connecting from should be able to resolve the following host names in the local hosts file or through DNS:

192.168.1.100    linux1
192.168.1.101    linux2
192.168.1.200    linux1-vip
192.168.1.201    linux2-vip

Try to connect to the clustered database using all available service names defined in the tnsnames.ora file:

C:\> sqlplus system/manager@orcl2
C:\> sqlplus system/manager@orcl1
C:\> sqlplus system/manager@orcl_taf
C:\> sqlplus system/manager@orcl

When creating the clustered database, we left all tablespaces set to their default size. If you are using a large drive for the shared storage, you may want to make a sizable testing database.

Note: Please keep in mind that the database file names (OMF files) being listed in these examples may differ from what the Oracle Database Configuration Assistant (DBCA) creates for your environment. When working through this section, substitute the data file names that were created in your environment where appropriate. The following query can be used to determine the file names for your environment:

SQL> select tablespace_name, file_name
  2  from dba_data_files
  3  union
  4  select tablespace_name, file_name
  5  from dba_temp_files;

TABLESPACE_NAME     FILE_NAME
--------------- --------------------------------------------------
EXAMPLE         +ORCL_DATA1/orcl/datafile/example.257.570913311
INDX            +ORCL_DATA1/orcl/datafile/indx.270.570920045
SYSAUX          +ORCL_DATA1/orcl/datafile/sysaux.260.570913287
SYSTEM          +ORCL_DATA1/orcl/datafile/system.262.570913215
TEMP            +ORCL_DATA1/orcl/tempfile/temp.258.570913303
UNDOTBS1        +ORCL_DATA1/orcl/datafile/undotbs1.261.570913263
UNDOTBS2        +ORCL_DATA1/orcl/datafile/undotbs2.265.570913331
USERS           +ORCL_DATA1/orcl/datafile/users.264.570913355

$ sqlplus "/ as sysdba"

SQL> create user scott identified by tiger default tablespace users;
SQL> grant dba, resource, connect to scott;

SQL> alter database datafile '+ORCL_DATA1/orcl/datafile/users.264.570913355' resize 1024m;
SQL> alter tablespace users add datafile '+ORCL_DATA1' size 1024m autoextend off;

SQL> create tablespace indx datafile '+ORCL_DATA1' size 1024m
  2  autoextend on next 50m maxsize unlimited
  3  extent management local autoallocate
  4  segment space management auto;

SQL> alter database datafile '+ORCL_DATA1/orcl/datafile/system.262.570913215' resize 800m;

SQL> alter database datafile '+ORCL_DATA1/orcl/datafile/sysaux.260.570913287' resize 500m;

SQL> alter tablespace undotbs1 add datafile '+ORCL_DATA1' size 1024m
  2  autoextend on next 50m maxsize 2048m;

SQL> alter tablespace undotbs2 add datafile '+ORCL_DATA1' size 1024m
  2  autoextend on next 50m maxsize 2048m;

SQL> alter database tempfile '+ORCL_DATA1/orcl/tempfile/temp.258.570913303' resize 1024m;

Status    Tablespace Name TS Type      Ext. Mgt.  Seg. Mgt.    Tablespace Size    Used (in bytes) Pct. Used
--------- --------------- ------------ ---------- --------- ------------------ ------------------ ---------
ONLINE    UNDOTBS1        UNDO         LOCAL      MANUAL         1,283,457,024         85,065,728         7
ONLINE    SYSAUX          PERMANENT    LOCAL      AUTO             524,288,000        275,906,560        53
ONLINE    USERS           PERMANENT    LOCAL      AUTO           2,147,483,648            131,072         0
ONLINE    SYSTEM          PERMANENT    LOCAL      MANUAL           838,860,800        500,301,824        60
ONLINE    EXAMPLE         PERMANENT    LOCAL      AUTO             157,286,400         83,820,544        53
ONLINE    INDX            PERMANENT    LOCAL      AUTO           1,073,741,824             65,536         0
ONLINE    UNDOTBS2        UNDO         LOCAL      MANUAL         1,283,457,024          3,801,088         0
ONLINE    TEMP            TEMPORARY    LOCAL      MANUAL         1,073,741,824         27,262,976         3
                                                            ------------------ ------------------ ---------
avg                                                                                                      22
sum                                                              8,382,316,544        976,355,328

8 rows selected.

The following RAC verification checks should be performed on both Oracle RAC nodes in the cluster! For this article, however, I will only be performing checks from linux1.

This section provides several srvctl commands and SQL queries you can use to validate your Oracle RAC 10g configuration.

There are five node-level tasks defined for SRVCTL:

• Adding and deleting node-level applications
• Setting and unsetting the environment for node-level applications
• Administering node applications
• Administering ASM instances
• Starting and stopping a group of programs that includes virtual IP addresses, listeners, Oracle Notification Services, and Oracle Enterprise Manager agents (for maintenance purposes).

Status of all instances and services

$ srvctl status database -d orcl
Instance orcl1 is running on node linux1
Instance orcl2 is running on node linux2

Status of a single instance

$ srvctl status instance -d orcl -i orcl2
Instance orcl2 is running on node linux2

Status of a named service globally across the database

$ srvctl status service -d orcl -s orcl_taf
Service orcl_taf is running on instance(s) orcl2, orcl1

Status of node applications on a particular node

$ srvctl status nodeapps -n linux1
VIP is running on node: linux1
GSD is running on node: linux1
Listener is running on node: linux1
ONS daemon is running on node: linux1

Status of an ASM instance

$ srvctl status asm -n linux1
ASM instance +ASM1 is running on node linux1.

List all configured databases

$ srvctl config database
orcl

Display configuration for our RAC database

$ srvctl config database -d orcl
linux1 orcl1 /u01/app/oracle/product/10.2.0/db_1
linux2 orcl2 /u01/app/oracle/product/10.2.0/db_1

Display all services for the specified cluster database

$ srvctl config service -d orcl
orcl_taf PREF: orcl2 orcl1 AVAIL:

Display the configuration for node applications - (VIP, GSD, ONS, Listener)

$ srvctl config nodeapps -n linux1 -a -g -s -l
VIP exists.: /linux1-vip/192.168.1.200/255.255.255.0/eth0:eth1
GSD exists.
ONS daemon exists.
Listener exists.

Display the configuration for the ASM instance(s)

$ srvctl config asm -n linux1
+ASM1 /u01/app/oracle/product/10.2.0/db_1

All running instances in the cluster

SELECT
    inst_id
  , instance_number inst_no
  , instance_name inst_name
  , parallel
  , status
  , database_status db_status
  , active_state state
  , host_name host
FROM gv$instance
ORDER BY inst_id;

 INST_ID  INST_NO INST_NAME  PAR STATUS  DB_STATUS    STATE     HOST
-------- -------- ---------- --- ------- ------------ --------- -------
       1        1 orcl1      YES OPEN    ACTIVE       NORMAL    linux1
       2        2 orcl2      YES OPEN    ACTIVE       NORMAL    linux2

All data files which are in the disk group

select name from v$datafile
union
select member from v$logfile
union
select name from v$controlfile
union
select name from v$tempfile;

NAME
-------------------------------------------
+FLASH_RECOVERY_AREA/orcl/controlfile/current.258.570913191
+FLASH_RECOVERY_AREA/orcl/onlinelog/group_1.257.570913201
+FLASH_RECOVERY_AREA/orcl/onlinelog/group_2.256.570913211
+FLASH_RECOVERY_AREA/orcl/onlinelog/group_3.259.570918285
+FLASH_RECOVERY_AREA/orcl/onlinelog/group_4.260.570918295
+ORCL_DATA1/orcl/controlfile/current.259.570913189
+ORCL_DATA1/orcl/datafile/example.257.570913311
+ORCL_DATA1/orcl/datafile/indx.270.570920045
+ORCL_DATA1/orcl/datafile/sysaux.260.570913287
+ORCL_DATA1/orcl/datafile/system.262.570913215
+ORCL_DATA1/orcl/datafile/undotbs1.261.570913263
+ORCL_DATA1/orcl/datafile/undotbs1.271.570920865
+ORCL_DATA1/orcl/datafile/undotbs2.265.570913331
+ORCL_DATA1/orcl/datafile/undotbs2.272.570921065
+ORCL_DATA1/orcl/datafile/users.264.570913355
+ORCL_DATA1/orcl/datafile/users.269.570919829
+ORC