Go to main content

Oracle® Solaris Cluster Data Service for Oracle External Proxy Guide

Exit Print View

Updated: May 2019
 
 

Planning the Installation and Configuration

To register and configure HA for Oracle External Proxy, you must consider or provide information on the following points.

Configuration Requirements

The following sections describe the configuration requirements for Oracle External Proxy.

Database User

On the Oracle Database or the Oracle RAC database, create a user that will be used by the Oracle External Proxy resource.

Example 1   Creating a User hauser Using the Helper Script

This example shows how to create a user hauser with a password hauser by using the helper script. You can choose a different username and password. The username and password that you choose will be used later by the Oracle Solaris Cluster resource.

As the Oracle database user, copy /opt/ORCLscoep/util/grant_privileges from an Oracle Solaris Cluster node to a working directory, for example, /var/tmp, on the Oracle Database node.

-bash-4.1$ export ORACLE_HOME=/u01/app/oracle/VIS/12.1.0
-bash-4.1$ export ORACLE_SID=VIS1
-bash-4.1$ cd /var/tmp
-bash-4.1$ (echo hauser; echo hauser) | ./grant_privileges
Example 2  Creating a User hauser Using the SQL Commands

This example shows how to create a user hauser with a password hauser by using the SQL commands. You can choose a different username and password. The username and password that you choose will be used later by the Oracle Solaris Cluster resource.

-bash-3.00$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 4 05:23:31 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create user hauser identified by hauser;

User created.

SQL> grant create session to hauser;

Grant succeeded.

SQL> grant execute on dbms_lock to hauser;

Grant succeeded.

SQL> grant select on v_$instance to hauser;

Grant succeeded.

SQL> grant select on v_$database to hauser;

Grant succeeded.

SQL> create profile hauser limit PASSWORD_LIFE_TIME UNLIMITED;
 
Profile created.

SQL> alter user hauser identified by hauser profile hauser;

User altered.

SQL>

If the Oracle database or the Oracle RAC database is being used as a standby database, then include the following command to Example 2, Creating a User hauser Using the SQL Commands.

SQL> grant sysdba to hauser;

Secure Database Password

The ORCL.oracle_external_proxy resource type verifies a connection to the Oracle database. There is no requirement for any Oracle Client software on the nodes where the ORCL.oracle_external_proxy resource type will execute. You will need to create a secure password to connect to a database. Example 3, Encrypting the Database User Password shows how to encrypt the database user password. The example uses hauser as the password. However, this password must correspond to the password you used when creating the database user in Database User.

Example 3  Encrypting the Database User Password

Assuming a root role on all Oracle Solaris Cluster nodes, do the following:

# clpstring create -b "resource" "resource"-pw
Enter string value: ********
Enter string value again: ********
#

Replace "resource" with the Oracle Solaris Cluster resource name you will use for the ORCL. oracle_external_proxy resource. For example, if the resource name is oep-rs issue the following command.

# clpstring create -b oep-rs oep-rs-pw
Enter string value: ********
Enter string value again: ********
#

tnsnames.ora File

A valid tnsnames entry for the Oracle Database or Oracle RAC service is required within a tnsnames.ora file. By default, the Oracle External Proxy resource uses the /var/opt/oracle/tnsnames.ora file to determine the tnsnames entry. However, if the default value is not acceptable, then specify the Tns_admin extension property as follows:

-p TNS_ADMIN=${ORACLE_HOME}/network/admin -p TNS_ADMIN=your path to tnsnames.ora

The Oracle External Proxy resource can query a remote Oracle data base or a local Oracle database and has the following requirements:

  • Querying a remote Oracle database service - If the Oracle database is a remote Oracle database, then the ADDRESS and the HOST entry in the tnsnames.ora file must specify the SCAN name for either Oracle Database 11g Release 2 or Oracle Database 12c. For Oracle Database 10g Release 2 and Oracle Database 11g Release 1, specify the appropriate listener address entries for each node that runs an instance of the database.

  • Querying a local Oracle database service - If the Oracle database is a local Oracle database, then the HOST entry can be omitted. Instead, the PROTOCOL and KEY entries are required within the ADDRESS statement.

  • The tnsnames entry and SERVICE_NAME entry must be the same.

The following examples show how to use Oracle External Proxy to query a remote Oracle database and a local Oracle database.

When querying a local Oracle Database service, you must first create a non-default service name for that database.

Example 4  Querying a Remote Oracle Database Service

In this example, the /var/opt/oracle/tnsnames.ora file is used.

In this example, the non-default service sales_svc is created for the sales database.

preferred_list is a list of preferred instances on which the service runs when the database is administrator managed.

bash-4.3$ srvctl add service -d sales -s sales_svc -r "preferred_list"
bash-4.3$ srvctl start service -d sales -s sales_svc
bash-4.3$
bash-4.3$ cat /var/opt/oracle/tnsnames.ora  
SALES_SVC =
 (DESCRIPTION = 
   (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
              (CONNECT_DATA =
                (SERVER = DEDICATED)
                   (SERVICE_NAME = SALES_SVC)
  )
) 
bash-4.3$

The hostname dbhost-scan-lh must be resolvable and within the /etc/hosts file on each node.

bash-4.3$ grep dbhost-scan-lh /etc/hosts  
10.134.84.58 dbhost-scan-lh.us.oracle.com dbhost-scan-lh  
bash-3.00#
Example 5  Querying a Local Oracle Database Service

In this example, the /var/opt/oracle/tnsnames.ora file is used.

bash-3.00# cat /var/opt/oracle/tnsnames.ora  
SALES =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
              (CONNECT_DATA =
        (SERVER = DEDICATED)
          (SERVICE_NAME = SALES) 
  )
) 
bash-3.00#

Remote Oracle Notification Service

Running Oracle Notification Service on every database node reduces the time it takes for the ORCL.oracle_external_proxy resource type to connect to the database and to determine the state of the database. To verify that Oracle Notification Service is running on the database nodes, run the following command.

bash-3.00# su - oragrid
Oracle Corporation  5.11 11.0 November 2011
-bash-3.00$ crsctl stat res ora.ons -t
---------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS Local Resources
---------------------------------------------------------
ora.ons
ONLINE ONLINE dbhost1
ONLINE ONLINE dbhost2
bash-3.00$

If Oracle Notification Service fails or stops running on a database node, the ORCL.oracle_external_proxy resource will still continue to monitor the remote database. However, it will take longer to connect to the database and determine the state of the database.