5
Customizing Oracle Real Application Clusters Guard

This chapter describes the scripts that should be customized for each Oracle Real Application Clusters Guard environment. It includes the following sections:

• Overview of Customizing Oracle Real Application Clusters Guard
• Setting Up the Call-Home Feature
• Setting Up the Customer Query
• Setting Up Role Change Notification
• Setting Up Oracle Enterprise Manager for Oracle Real Application Clusters Guard
• Warming the Library Cache on the Secondary Instance

Overview of Customizing Oracle Real Application Clusters Guard

You can customize your Oracle Real Application Clusters Guard installation. Table 5-1 shows the features that can be customized and the important files, scripts, or packages associated with each feature.

Table 5-1 Oracle Real Application Clusters Guard Features That Can Be Customized

Feature	File, Scripts, or Packages
Call-home	$PFS_HOME/user/pfs_$ORACLE_SERVICE_callhome.sh script
Customer query	$ORACLE_HOME/pfs/admin/catpfs.sql script
Role change notification	$PFS_HOME/user/pfs_$ORACLE_SERVICE_notifyrole.sh script
Oracle Enterprise Manager	ORATAB file
Warming the library cache on the secondary instance	DBMS_LIBCACHE package and pfs_$ORACLE_SERVICE_warmcache.sh script

Setting Up the Call-Home Feature

For failures in Oracle Real Application Clusters Guard that result in the primary instance or primary role becoming unavailable, Oracle Real Application Clusters Guard automatically executes specific failover or recovery actions. Although Oracle Real Application Clusters Guard can take certain automated actions, it requires manual intervention to repair some types of failures. It is important for those failures to be examined and quickly repaired so that a secondary outage does not occur. For example, a secondary instance may shut down due to a failure of the secondary node. Oracle Real Application Clusters Guard cannot restart the failed node because it requires manual examination of why the node failed, some repair action, and then restarting the failed node. The failure of the secondary node does not cause any downtime because the primary instance and service are still available, but Oracle Real Application Clusters Guard sends a call-home message to the Oracle Real Application Clusters Guard log immediately so that normal operation can be restored quickly.

Oracle Real Application Clusters Guard sends a call-home message to the Oracle Real Application Clusters Guard log for every failure that occurs in Oracle Real Application Clusters Guard, regardless of the type of the failure. Oracle Real Application Clusters Guard also executes a callout to the call-home script. The Oracle Real Application Clusters Guard Setup Utility (PFSSETUP) generates the script, which is located in $PFS_HOME/user/pfs_$ORACLE_SERVICE_callhome.sh.

This script is not executable until you customize it to suit the your environment. For example, you can customize the script so that an e-mail notification results in sending a page to the appropriate administrator.

The following call-home template is part of the call-home script:

# Call_Home Template
#
CALLHOME_MESSAGE=$1
#
# Example:
#
# mail <userid> << EOF
# !!! Alert: $CALLHOME_MESSAGE at `date` !!!
# EOF

When there is a failure, this part of the call-home script logs a message in pfs_$ORACLE_SERVICE_host.log similar to the following:

Fri Jul 27 12:48:08 2001 PFS-2019: Info: Real Application Clusters Guard 
callhome with OracleUp_for_SALES_error_Will_failover now
Fri Jul 27 12:48:09 2001 PFS-2019: Info: Real Application Clusters Guard 
callhome with Failing_over_service_SALES now

It also e-mails the messages to the user ID that you specify when you customize the template.

Setting Up the Customer Query

The heartbeat monitor checks not only whether the primary instance has a heartbeat but also whether it is capable of work. It monitors the work capability by repeatedly running a customized PL/SQL procedure containing a query that should represent the actual work that needs to be done in the instance. The default catpfs.sql script, which you run when you create the Oracle Real Application Clusters Guard database, prompts the user for a table owner and a table name upon which to run a basic SELECT statement. Modify the customer_query package in the catpfs.sql script to reflect your business needs before running the script.

The customer query, customer_query.test, is contained in catpfs.sql and is located in the $ORACLE_HOME/pfs/admin directory. The contents of the customer_query.test template are as follows:

REM customer_query package executed by ORACLE_PING
create or replace package customer_query as
 procedure test (total_rows out number);
 end customer_query;
/

create or replace package body customer_query as
  procedure test (total_rows out number) is
  begin
          -- Example customer queries:
          -- select count(*) into total_rows from &&syn_name;
          -- select max(emp_id) into total_rows from &&syn_name;
          -- select sum(salary) into total_rows from &&syn_name;
  end;
end customer_query;
/ 

/*  from ORACLE_PING */
EXEC SQL EXECUTE
      BEGIN
         customer_query.test(:number_of_rows);
      END ;
END-EXEC ; 

The customer query can be modified to select any single specific value such as a specific row, a row count, or a maximum value. When you write the customer query, consider the following recommendations:

• Access a table that is representative of the application.
• Exercise as much of the database as possible. Do not reissue the same query each time the heartbeat monitor runs. Consider selecting a random row each time.
• Choose queries that can be completed within the desired timeout interval. For example, do not use queries that require the entire table to be scanned. Although the parameter values can be increased, increases contribute to the length of time it takes to detect a failure due to a database hang.

Test the customer query when the system is under heavy load to ensure that it can be completed within the interval defined by the USER_TIMEOUT value in the ORAPING_CONFIG table.

Setting Up Role Change Notification

When an instance or pack starts up, shuts down or changes its role from secondary to primary, Oracle Real Application Clusters Guard sends a message to the Oracle Real Application Clusters Guard log. Automatic role change notification occurs when Oracle Real Application Clusters Guard takes automatic actions. Manual role change notification occurs only when PFSCTL commands are executed.

Table 5-2 shows notifications of role changes.

Table 5-2 Role Change Notifications

Notification	Type	Description
UP	Automatic	Called after the instance is started or after its role changes from secondary to primary
DOWN	Automatic	Called before the instance is shut down
CLEANUP	Manual	Called after the instance is shut down
PLANNED_UP	Manual	Called from PFSCTL before the instance is started
PLANNED_DOWN	Manual	Called from PFSCTL before the instance is shut down

The notification is implemented by a script called pfs_$ORACLE_SERVICE_notifyrole.sh. The PFSSETUP utility generates the script, which resides under the $PFS_HOME/user directory. The script is not executable until you modify it. You can modify it to perform certain actions before an instance shuts down, for example. The following is the role notification script template:

# Role Notification Template
#
NOTIFY_ORACLE_SERVICE=$1
NOTIFY_NODE=$2
NOTIFY_ROLE=$3
NOTIFY_STATUS=$4
NOTIFY_LOGGING_DATE=$5
#
# --- DO NOT MODIFY ABOVE THIS LINE ---
#
# Example
#
case $NOTIFY_STATUS in
  "planned_up")
#  Run scripts that you want executed before the instance starts.
  ;;

  "up")
#  Run scripts that you want executed after the instance starts.     
  ;;

  "planned_down" | "down")
#  Run scripts that you want executed before the instance is shut down.
#  WARNING: Scripts executed at this step will affect failover time.
  ;;

  "cleanup")
#  Run scripts that you want executed after the instance is shut down.
  ;;

esac 

Examples of Role Change Notification Messages

The following examples show the messages that occur on each host:

• Role Change Notification: pfsboot Command
• Role Change Notification: pfshalt Command
• Role Change Notification: move_primary Command
• Role Change Notification: restore Command
• Role Change Notification: Automatic Failover

Role Change Notification: pfsboot Command

When the pfsboot command is entered on HOSTA, the following messages appear in the Oracle Real Application Clusters Guard log on HOSTA:

Fri Dec 22 16:31:27 2000 PFS-5555: sales hostA unknown planned_up 2000/12/22-16:31:26
Fri Dec 22 16:32:13 2000 PFS-5555: sales hostA primary up 2000/12/22-16:32:12

The following messages appear in the Oracle Real Application Clusters Guard log on HOSTB:

Fri Dec 22 16:32:25 2000 PFS-5555: sales hostB unknown planned_up 2000/12/22-16:32:23
Fri Dec 22 16:33:18 2000 PFS-5555: sales hostB secondary up 2000/12/22-16:33:17

Role Change Notification: pfshalt Command

When the pfshalt command is entered on HOSTA, the following messages appear in the Oracle Real Application Clusters Guard log on HOSTA:

Fri Dec 22 16:38:41 2000 PFS-5555: sales hostA primary planned_down 2000/12/22-16:38:40
Fri Dec 22 16:38:50 2000 PFS-5555: sales hostA primary down 2000/12/22-16:38:49
Fri Dec 22 16:40:09 2000 PFS-5555: sales hostA primary cleanup 2000/12/22-16:40:08

The following messages appear in the Oracle Real Application Clusters Guard log on HOSTB:

Fri Dec 22 16:37:56 2000 PFS-5555: sales hostB secondary planned_down 2000/12/22-16:37:54
Fri Dec 22 16:38:04 2000 PFS-5555: sales hostB secondary down 2000/12/22-16:38:04
Fri Dec 22 16:38:27 2000 PFS-5555: sales hostB secondary cleanup 2000/12/22-16:38:26

Role Change Notification: move_primary Command

When the move_primary command is entered on HOSTA, the following messages appear in the Oracle Real Application Clusters Guard log on HOSTA:

Fri Dec 22 11:21:35 2000 PFS-5555: sales hostA primary planned_down 2000/12/22-11:21:35
Fri Dec 22 11:22:15 2000 PFS-5555: sales hostA primary down 2000/12/22-11:22:15
Fri Dec 22 11:22:25 2000 PFS-5555: sales hostA primary cleanup 2000/12/22-11:22:24

The following message appears in the Oracle Real Application Clusters Guard log on HOSTB:

Fri Dec 22 11:22:10 2000 PFS-5555: sales hostB primary up 2000/12/22-11:22:09

Role Change Notification: restore Command

When the restore command is entered on HOSTA, the following messages appear on the Oracle Real Application Clusters Guard log on HOSTA:

Fri Dec 22 12:20:24 2000 PFS-5555: sales hostA unknown planned_up 2000/12/22-12:20:23
Fri Dec 22 12:21:24 2000 PFS-5555: sales hostA secondary up 2000/12/22-12:21:23

No messages appear in the Oracle Real Application Clusters Guard log on HOSTB.

Role Change Notification: Automatic Failover

When automatic failover occurs, the following messages appear in the Oracle Real Application Clusters Guard log on HOSTA:

Fri Dec 22 15:03:42 2000 PFS-5555: sales hostA primary down 2000/12/22-15:03:41
Fri Dec 22 15:04:51 2000 PFS-5555: sales hostA primary cleanup 2000/12/22-15:04:50

The following message appears in the Oracle Real Application Clusters Guard log on HOSTB:

Fri Dec 22 15:03:38 2000 PFS-5555: sales hostB primary up 2000/12/22-15:03:37

Setting Up Oracle Enterprise Manager for Oracle Real Application Clusters Guard

You can use Oracle Enterprise Manager to administer the Oracle Real Application Clusters Guard database. The following requirements must be met in order to use Oracle Enterprise Manager:

• The Oracle System Identifier (SID) for each instance in the Oracle9i Real Application Clusters must be unique.
• The ORATAB file must be configured for SRVCTL.

  Note: Follow these guidelines rather than the Oracle Enterprise Manager instructions other Oracle9i Real Application Clusters documentation.

Modifying the ORATAB File for Oracle Real Application Clusters Guard

Create an entry for the Oracle9i Real Application Clusters database in the ORATAB file. Oracle Enterprise Manager uses the ORATAB file during service discovery to determine:

• Whether the database is an Oracle9i Real Application Clusters database
• The database name

Use the following syntax for the Oracle9i Real Application Clusters entry:

db_name:$ORACLE_HOME:N

DB_NAME is the database name given to the Oracle9i Real Application Clusters database. $ORACLE_HOME is the directory path to the database. N indicates that the database should not be started at reboot time.

The following example is for a database named SALES:

SALES:/u01/oracle/901:N

Warming the Library Cache on the Secondary Instance

This section contains the following topics:

• Overview of Warming the Library Cache
• Setting Up DBMS_LIBCACHE
• Using DBMS_LIBCACHE

Overview of Warming the Library Cache

The library cache includes the shared SQL areas, private SQL areas, PL/SQL procedures and packages, and control structures such as locks and library cache handles. A shared SQL area contains the parse tree and execution plan for a single SQL statement or for similar SQL statements. Oracle saves memory by using one shared SQL area for multiple similar DML statements, particularly when many users execute the same application. A private SQL area contains data such as bind information and runtime buffers. Oracle processes PL/SQL program units (procedures, functions, packages, anonymous blocks, and database triggers) much the same way it processes individual SQL statements. Oracle allocates a shared area to hold the parsed, compiled form of a program unit. Oracle allocates a private area to hold values specific to the session that executes the program unit, including local, global, and package variables and buffers for executing SQL.

Maintaining information about frequently executed SQL and PL/SQL statements in the library cache improves the performance of the Oracle database server. In an Oracle9i Real Application Clusters primary/secondary configuration, the library cache associated with the primary instance contains up-to-date information. If failover occurs, then the benefit of that information is lost unless the library cache on the secondary instance is populated before failover.

You can use the DBMS_LIBCACHE package to transfer the information in the library cache of the primary instance to the library cache of the secondary instance. This process is called warming the library cache. It improves performance immediately after failover because the new primary library cache does not need to be populated with parsed SQL statements and compiled PL/SQL units.

Figure 5-1 shows the library cache being compiled at the secondary instance, by using SQL statements and PL/SQL units extracted from the primary instance.

Figure 5-1 Warming the Library Cache of the Secondary Instance

Text description of the illustration pfsar001.gif

DBMS_LIBCACHE captures and compiles the part of selected cursors that can be shared. It selects cursors based on the amount of shared memory that is used and the frequency with which the associated SQL statements are used. It then populates the library cache on the secondary instance with the compiled cursors.

Execute the DBMS_LIBCACHE package on the secondary instance:

• As a regularly scheduled job
• Before executing a manual failover or switchover

  See Also: Oracle9i Database Concepts "Using DBMS_LIBCACHE"

Setting Up DBMS_LIBCACHE

Installing the DBMS_LIBCACHE procedure causes the following actions to occur:

1. Creates a user named PARSER.
2. Asks for a password for PARSER.
3. Asks for default and temporary tablespace for PARSER.
4. Creates a database link named LIBC_LINK that is owned by PARSER.
5. Asks for an appropriate connect string to use when the database link connects to other instances of the Oracle9i Real Application Clusters configuration. This connect string collects the library cache information.

   Note: Oracle Net configuration of the listener.ora and tnsnames.ora files for this connect string must already be completed on all the nodes of the Oracle9i Real Application Clusters configuration.

The DBMS_LIBCACHE package can be installed at any time, either during installation of Oracle9i Real Application Clusters or later, during normal operation.

Perform the following steps to set up DBMS_LIBCACHE:

1. Connect as the user SYS. Enter the following commands:

   $ cd $ORACLE_HOME/pfs/admin
   $ sqlplus "/ as sysdba"
   
   

   You should see output similar to the following:

   SQL*Plus: Release 9.0.1.0_0 - Production on Fri Feb 9 15:45:54 2001 
   
   (c) Copyright 2001 Oracle Corporation.  All rights reserved. 
   
   Connected to: 
   Oracle9i Enterprise Edition Release 9.0.1.0_0 - Production 
   With the Partitioning and Real Application Clusters options 
   JServer Release 9.0.1.0_0 - Production 
   
   SQL> 
   
   

2. Create the header definition package for DBMS_LIBCACHE by running catlibc.sql. The catlibc.sql procedure is found in the $ORACLE_HOME/pfs/admin directory, which you entered in Step 1. Enter the following command:

   SQL> @catlibc.sql
   
   

   You should see output similar to the following, including the errors:

   Package created.
   
   drop public synonym dbms_libcache$def
                       *
   ERROR at line 1:
   ORA-01432: public synonym to be dropped does not exist 
   
   Synonym created.
   
   Grant succeeded.
   
   View created.
   
   Grant succeeded.
   
   drop public synonym v$sql2 
                       *
   ERROR at line 1:
   ORA-01432: public synonym to be dropped does not exist 
   
   Synonym created.
   
   ... Creating the parsing user and database link.
   
   Below are the list of online tablespaces in this database.
   Decide which tablespace you wish to use for the PARSER user.
   
   TABLESPACE_NAME 
   ------------------------------ 
   RBS 
   TEMP 
   USERS 
   Please enter the parsing users password and tablespaces.
   
   Enter value for parser_password:
   
   

3. The package has created a user called PARSER. Enter the password for PARSER. For example, the password may be parser:

   parser
   
   

   You should see output similar to the following:

   Enter value for default_tablespace:
   
   

4. Enter the default tablespace for PARSER. For example:

   USERS
   
   

   You should see output similar to the following:

   Enter value for temporary_tablespace:
   
   

5. Enter the temporary tablespace for PARSER. For example:

   TEMP
   
   

   You should see output similar to the following:

   User created.
   
   Grant succeeded.
   
   Enter value for parser_password:
   
   

6. Enter the password that you specified in step 3. For example:

   parser
   
   

   You should see output similar to the following, including the error:

   Connected.
   drop public database link libc_link 
                             *
   ERROR at line 1:
   ORA-02024: database link not found 
   
   Please enter the parsing users TNS connect string.
   
   Enter value for connect_string:
   
   

7. Enter the TNS connect string that is used to connect to the other instance of the Oracle9i Real Application Clusters configuration. PARSER uses this database link to connect to the library cache on the other instance. For example:

   parser_service
   
   

   You should see output similar to the following:

   Database link created.
   
   

8. Create the DBMS_LIBCACHE package. Connect as user SYS and run dbmslibc.sql, which is found in the $ORACLE_HOME/pfs/admin directory. Enter the following commands:

   $ cd $ORACLE_HOME/pfs/admin
   $ sqlplus "/ as sysdba"
   
   SQL> @dbmslibc
   
   

   You should see output similar to the following, including the error:

   Package created.
   
   drop public synonym dbms_libcache
                       *
   ERROR at line 1:
   ORA-01432: public synonym to be dropped does not exist 
   
   Synonym created.
   
   Grant succeeded.
   
   Grant succeeded.
   
   Package body created.
   

Using DBMS_LIBCACHE

The main public interface of the DBMS_LIBCACHE package is the COMPILE_FROM_REMOTE procedure. Invoke it from the instance on which the library cache needs to be populated, the secondary instance in the context of Oracle Real Application Clusters Guard. While the DBMS_LIBCACHE package has been designed for an Oracle9i Real Application Clusters configuration, you can use it in other Oracle environments that would benefit from warming the library cache.

Oracle Corporation recommends that you invoke the procedure after the contents of the library cache on the primary instance have stabilized. You can use the COMPILE_FROM_REMOTE procedure in the following ways:

• As a regularly scheduled job
• Before executing a manual failover or switchover

On the secondary instance, perform the following steps:

1. Connect as the user PARSER, by using SQL*Plus:

   $ sqlplus parser/parser
   
   

2. Turn on server output:

   SQL> set serveroutput on;
   
   

3. Execute the COMPILE_FROM_REMOTE procedure. The SQL statement should have the following format:

   EXECUTE sys.dbms_libcache.compile_from_remote(db_link,username,threshold_
   executions,threshold_sharable_mem);
   
   

Table 5-3 describes the arguments for executing the COMPILE_FROM_REMOTE procedure.

Table 5-3 Arguments for the COMPILE_FROM_REMOTE Procedure  

Name	Description	Mandatory	Default Value
DB_LINK	The database link that points to the primary instance	Yes	LIBC_LINK, which is the database link that is created during installation. Can be changed by the user.
USERNAME	The user whose information is extracted from the primary instance to be parsed on the secondary instance	No	All users
THRESHOLD_EXECUTIONS	Minimum number of executions of a SQL statement that must have occurred before the SQL statement is considered for extraction	No	3 Check the EXECUTIONS column in the V$SQL view for the SQL statements that are considered. Increasing the value causes the secondary instance to extract only the most frequently executed SQL statements. Decreasing the value causes the secondary instance to extract more SQL statements, which then populates the cache with more information. If some SQL statements are not used often, then the additional information has limited usefulness.
THRESHOLD_SHARABLE_MEM	Minimum size of cursors that is considered for extraction	No	1000 Check the SHARABLE_MEM column of the V$SQL view for the statements that are considered. Increasing the value causes the secondary instance to extract and create only the largest cursors. Generally, the largest cursors take most of the time needed for warming the library cache. Decreasing the value causes the secondary instance to extract smaller cursors that may not be as useful.

Only DB_LINK is mandatory. The EXECUTE statement must contain a value for DB_LINK. Specify values for the other arguments only if their values are different from the default values.For example, suppose you want to specify the parameter values shown in the following table.

Parameter	Value
DB_LINK	LIBC_LINK
USERNAME	APPS
THRESHOLD_EXECUTION	5
THRESHOLD_SHARABLE_MEM	800

Then execute the COMPILE_FROM_REMOTE procedure with the following SQL statement:

SQL> EXECUTE sys.dbms_libcache.compile_from_remote('LIBC_LINK','APPS',5,800); 

Example: Compiling All Cursors for All Users with Default Threshold Values

Enter the following SQL statement:

SQL> EXECUTE sys.dbms_libcache.compile_from_remote('LIBC_LINK');

You should see output similar to the following:

Total SQL statements to compile=14
Total SQL statements compiled=14

Example: Compiling All Cursors for the Oracle GL Application with Default Threshold Values

The COMPILE_FROM_REMOTE procedure is invoked twice because the cursors may be owned by the APPS user or the GL user:

SQL> EXECUTE sys.dbms_libcache.compile_from_remote('LIBC_LINK', 'APPS');
SQL> EXECUTE sys.dbms_libcache.compile_from_remote('LIBC_LINK', 'GL');