Oracle RAC

With Oracle Real Application Clusters (RAC) it is possible to use two (or more) database instances at the same time, when extracting data from the database.

The settings assume that an existing Oracle Instance is ready for MediationZone use.

1. Install Oracle RAC, create a database with shared storage on the cluster nodes and listeners according to normal Oracle RAC procedures.

2. Modify the install.xml file to include the SID of one of the instances or Service Name of the RAC cluster and modify other obvious information (such as the location of the shared storage place of the database files and the Oracle installation).

3. Modify and run MediationZone Oracle scripts.

   1. Modify the oracle_create_instance.sh file.
      The following passage in the oracle_create_instance.sh file should be commented out. It is, however, important to make sure that listeners are running correctly for RAC instances/services since modification of the script causes it to not check for the presence of running listeners.

      # Make sure that the listener is up for our instance. # It is not really needed in this script but it will be # necessary in the installer. # lsnrctl status | grep "Instance \"${ORACLE_SID}\"" > /dev/null # # if [ $? -ne 0 ] ; then # echo "The listener for $ORACLE_SID is not running, abort!" 1>&2 # exit 1 # fi

      Ensure that each listener and instance running get a row each in the init<instance>.ora files located in $ORACLE_HOME/dbs for each of the nodes.

   2. Modify oracle_create.sql.
      The following passage should be commented out. This makes the script create tables and users in the RAC instances, rather than create a new database/instance.

      -- Create the database -- prompt Creating database -- CREATE DATABASE orac1 -- DATAFILE '/opt/oradata/orac/mz/ts_orac1_system.dbf' -- SIZE 250M REUSE AUTOEXTEND OFF -- sysaux datafile '/opt/oradata/orac/mz/ts_orac1_sysaux.dbf' -- size 100M autoextend off -- UNDO TABLESPACE MZ_UNDO_TS_01 -- DATAFILE '/opt/oradata/orac/mz/ts_orac1_undo01.dbf' -- SIZE 125m REUSE AUTOEXTEND OFF -- LOGFILE -- group 1 ('/opt/oradata/orac/mz/ts_orac1_log01.log') size 15M, -- group 2 ('/opt/oradata/orac/mz/ts_orac1_log02.log') size 15M, -- group 3 ('/opt/oradata/orac/mz/ts_orac1_log03.log') size 15M, -- group 4 ('/opt/oradata/orac/mz/ts_orac1_log04.log') size 15M;

      When running the MediationZone *.sql scripts, the original file $ORACLE_HOME/dbs/init*.ora (* = instance name) is replaced with a new .ora file. The original file has to be restored, hence the file created by MediationZone should not be used. The easiest way to restore the init<instance>.ora file is to copy it from the second node, since they should be identical on both hosts. An example of an init<instance>.ora is shown next:

      oracga1.local_listener="(address=(protocol=tcp) (host=10.0.0.111)(port=1521))" oracga2.local_listener="(address=(protocol=tcp) (host=10.0.0.112)(port=1521))" SPFILE='+ORAC50/oracga/spfileoracga.ora'

      The three occurrences of create tablespace should also be changed. Remove the path after DATAFILE and remove REUSE before AUTOEXTEND, so it looks like this:

      create tablespace ts_mz_tab datafile size 200m autoextend off extent management local uniform size 250k SEGMENT SPACE MANAGEMENT AUTO;

   3. Run oracle_create_instance.sh
      If the script fails, you must manually drop all objects in the mzadmin and mzowner schemas before you run it again.   

Oracle RAC for High Availability with Failover

If you want to set up an Oracle RAC for High Availability (HA) using Failover, you should use an active / passive / passive pattern where:

• 1 active instance runs the service and handles traffic.

• 2 passive instances remain available to take over if there is a failure.

It works like this:

• In Oracle RAC, a service is assigned to specific instances as preferred (active) or available (passive).

• If the active instance, or its server, becomes unavailable, the service fails over to one of the passive instances.

• Clients connect using TNS descriptors or JDBC URLs that reference the Oracle RAC service and not a specific instance.

Recommended service layout (active/passive/passive)

To create the recommended service layout:

1. Setup an Oracle RAC database with three nodes, using the unique database name MZPRD and the instance names MZPRD1, MZPRD2, MZPRD3.

2. Create and start a dedicated service for MediationZone, for example MZSVC:

Flags:

• -r = preferred instances (active).

• -a = available instances (passive/failover targets). 

If MZPRD1 fails, the Oracle Clusterware automatically relocates the service to MZPRD2 or MZPRD3.

Client Connectivity Patterns

Best practice for client connectivity patterns is to use SCAN. 

If your cluster has a SCAN name (for example, mz-scan.mycorp.local) that resolves to three SCAN VIPs, the clients will discover the cluster automatically.

If you must list three nodes explicitly, use the node VIPs in the connect descriptor. Configure with LOAD_BALANCE=OFF and FAILOVER=ON to align with active/passive/passive.

Below follows examples for two different options using three nodes and tnsnames.ora (OCI/SQL*Plus/ODP.NET).

 Below follows examples for two different options using JDBC Thin URS (for Java clients).