The following sections describe the configuration requirements for Oracle External Proxy.
On the Oracle Database or the Oracle RAC database, create a user that will be used by the Oracle External Proxy resource.
Example 1-1 Creating a User hauser Using the SQL CommandsThis 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> 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 1–1.
SQL> grant sysdba to hauser;
The ORCL.oracle_external_proxy resource type verifies a connection to the remote 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 remote database. Example 1–2 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 Remote Database User.
If you have recreated /var/cluster/scoep_key, you will need to recreate the encrypted password again, and then disable and enable the ORCL.oracle_external_proxy resource type for the change to be effective.
Assuming a root role on all Oracle Solaris Cluster nodes, do the following:
bash-3.00# dd if=/dev/urandom of=/var/cluster/scoep_key bs=8 count=1 1+0 records in 1+0 records out bash-3.00# bash-3.00# echo hauser | /usr/sfw/bin/openssl enc -aes128 -e -pass \ file:/var/cluster/scoep_key -out /opt/ORCLscoep/.oep-rs_passwd bash-3.00#
oep-rs in oep-rs_passwd represents the resource name that you will create later. However, you can choose a different resource name. Now verify that the password can be decrypted.
bash-3.00# /usr/sfw/bin/openssl enc -aes128 -d -pass \ file:/var/cluster/scoep_key -in /opt/ORCLscoep/.oep-rs_passwd hauser bash-3.00# chmod 400 /var/cluster/scoep_key bash-3.00# chmod 400 /opt/ORCLscoep/.oep-rs_passwd
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 - 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 - 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.
Example 1-3 Querying a Remote Oracle DatabaseIn this example, the /var/opt/oracle/tnsnames.ora file is used.
bash-3.00# cat /var/opt/oracle/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost-scan-lh)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
bash-3.00#
The hostname dbhost-scan-lh must be resolvable and within the /etc/hosts file on each node.
bash-3.00# grep dbhost-scan-lh /etc/hosts 10.134.84.58 dbhost-scan-lh.us.oracle.com dbhost-scan-lh bash-3.00#Example 1-4 Querying a Local Oracle Database
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#
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.