Physical Standby Database with Oracle Standard Edition - an alternative approach - Part 2

In Part 1, I described the background and the decision process in coming up with this solution. In this part, I will provide the implementation details.

Preliminaries

But first, a little light reading.  My assumption is that you will be familiar with Linux, Oracle Database and Grid Infrastructure. However, here is the reference for managing 3rd party applications with Oracle Clusterware.

This implementation was developed on:

• Oracle Linux 6.5 x86_64 running in VirtualBox VMs
• Oracle Grid Infrastructure 11.2.0.4
• Oracle Database Standard Edition 11.2.0.4

I wont go through all the details of getting your environment built, but basically you will need to have completed:

• Installing Oracle Linux/RHEL/CentOS - any version of Linux that will run the database and clusterware.,
• When configuring the O/S, you will need to disable SELINUX, by setting 'SELINUX=permissive' or 'SELINUX=disabled' in /etc/sysconfig/selinux,
• Install rsync, if it is not already there,
• Install lsyncd - available from the EPEL repositories,
• configure ssh user equivalence between all the nodes for the 'oracle' user. If you are using RAC, and are therefore connecting to the database with the SCAN address, add “StrictHostKeyChecking No” to ~/.ssh/config – this is because the SCAN ip can switch between hosts and consequently the host key in ~/ssh/known_hosts for the SCAN ip's can change.
• Install and configure Oracle Grid Infrastructure, either clustered or stand-alone,
• Configure ASM disk groups to hold the database data and recovery area,
• Install Oracle database software,
• Create the primary database,
• Create an entry in tnsnames.ora for the remote database on both hosts. i.e.if your database is 'ORCL' and you want to use a suffix of 'REMOTE' for the tns alias of the database on the other host, create a tnsnames.ora entry like
      ORCL_REMOTE =
       (DESCRIPTION =
         (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = remotehost.domain)(PORT = 1521))
         )
         (CONNECT_DATA =
           (SERVICE_NAME = ORCL)
           (UR = A)
         )
       )
• Create a directory on the file system to store the archived logs. This directory must be the same on all nodes. In the primary database set this directory as one of the 'log_archive_dest_n' parameters. You will also need to set one of the 'log_archive_dest_n' parameters to the value 'LOCATION=USE_DB_RECOVERY_FILE_DEST'
• Create a standby database via the usual methods i.e. RMAN from active database etc. Note that even though we are not in a Data Guard environment, you still need to set db_unique_name, but it is OK to set it to the same value in both primary and standby databases. You will need to manually register the standby database with the Oracle Grid infrastructure. When you do, include '-n <db name>' in the srvctl command

The main event

Phew.

Now that you have your environment built, we can actually start synchronising the standby database from the primary.

First, download the lsyncd.sh script.  Place it in a location on all the primary and standby nodes. For example, if you are following the Oracle Optimal Flexible Architecture, you could create a directory '/u01/app/oracle/admin/bin'. Make sure the file permissions allow the 'oracle' user to execute the script.  The only thing in the script that is site-specific is the value for STANDBY_DB_SUFFIX. This is used to reference the TNS alias for the remote database, so we can get the HOST the remote database is located on. Edit the lsyncd.sh script for your particular environment.

Next, we register the script with Oracle Grid Infrastructure. Set the environment to the Grid Infrastructure home. The easiest way to do this is:

$ ORACLE_SID=`grep '+ASM' /etc/oratab | awk -F\: '{ print $1 }'`
$ . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base has been set to /u01/app/oracle

Then run the following command:

$ crsctl add resource lsyncd.orcl -type local_resource \
-attr "ACTION_SCRIPT=/u01/app/oracle/admin/bin/lsyncd.sh, \
CHECK_INTERVAL=60,RESTART_ATTEMPTS=2, \
START_DEPENDENCIES=hard(intermediate:ora.orcl.db), \
STOP_DEPENDENCIES=hard(shutdown:ora.orcl.db)”

where

• 'lsyncd.orcl' is the name of the resource in Oracle GI – use your own naming convention, but it cannot start with 'ora.', and if you want to manage more than one database on the server, each database will have its own lsyncd resource,
• ACTION_SCRIPT is the path to the lsyncd.sh script,
• START_DEPENDANCIES and STOP_DEPENDANCIES references the resource name for the database being managed. START_DEPENDANCIES must be 'hard(intermediate:' to account for the standby database being in INTERMEDIATE state. STOP_DEPENDANCIES must be 'hard(shutdown:' so that lsyncd will keep running even if the database is stopped.

And that's it. No, really. The clusterware should start the lsyncd resource automatically. To check you can run:

$ crsctl status resource lsyncd.orcl
NAME=lsyncd.orcl
TYPE=local_resource
TARGET=ONLINE
STATE=ONLINE

If it shows 'TARGET=OFFLINE', then run

$ crsctl start resource lsyncd.orcl

To diagnose problems, there is a log created under $ORACLE_BASE/admin/<database name>/lsyncd