Oracle Real Application Clusters Guard Administration and Reference Guide
Release 9.0.1

Part Number A88810-02
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

3
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

You can customize your Oracle Real Application Clusters Guard installation. The following table shows the features that can be customized and the important files, scripts, or packages associated with each feature:

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, 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:

Tue Dec  5 13:23:51 2000 PFS-2019: Info: PFS callhome with OracleUp_for_SALES_error_Will_
failover now.
Tue Dec  5 13:23:53 2000 PFS-2019: Info: PFS callhome with Failing_over_service_SALES now.

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


Note:

Oracle Real Application Clusters Guard does not provide an external call-home mechanism. It relies on the operating system or third-party tools installed on your site for sending notification to an administrator. 


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:

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.

See Also:

"Making Online Changes to the ORAPING_CONFIG Table" to find out how to check the value of USER_TIMEOUT 

Setting Up Role Change Notification

When an instance or pack comes up, goes down or changes 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.

The notification of role changes are:

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 

See Also:

Your platform-specific Oracle Real Application Clusters Guard installation guide for more information about the PFS Installer 

Examples of Role Change Notification Messages

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

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 messages appear 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 ORATAB File

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:

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

db_name:$ORACLE_HOME:N

where 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

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.

See Also:

Oracle9i Database Concepts 

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 3-1 shows the library cache being compiled at the secondary instance, using SQL statements and PL/SQL units extracted from the primary instance.

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


Text description of pfsar001.gif follows
Text description of the illustration pfsar001.gif

DBMS_LIBCACHE captures and compiles the sharable part of selected cursors. It selects cursors based on the amount of sharable memory 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:

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 to collect 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:

    $ sqlplus "/ as sysdba"
    
    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:

    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:

    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:

    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 will be 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:

    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:

On the secondary instance, perform the following steps:

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

    $ sqlplus parser/password
    
    
  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 3-1 describes the arguments for executing the COMPILE_FROM_REMOTE procedure.

Table 3-1 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 will be considered for extraction 

No 

3

Check the EXECUTIONS column in the V$SQL view for the SQL statements that will be considered.

Increasing the value causes the secondary instance to extract only the most frequently executed SQL statements. Decreasing the value cause the secondary instance to extract more SQL statements, which will populate 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 will be 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, if you want to specify the following values:

DB_LINK

LIBC_LINK

USERNAME

APPS

THRESHOLD_EXECUTION

5

THRESHOLD_SHARABLE_MEM

800

DB_

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');


Go to previous page Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback