Part 2: Active-passive High Availability Solution for Oracle Database without Data Guard.

The first part.

This article is the second part for an Active-passive High Availability Solution for Oracle Database without DataGard solution.

This second part explains how to update/recover the remote Oracle database periodically and automatically.

1. Set up one scenario:

After the duplication of the Oracle database production (see part 1), it is time to update the remote Oracle database with the modification from the source/production Oracle Database.

In this scenario, I added a line to the table department on the schema HR. HR is a example schema in Oracle database if you install the examples.

Step 1: On the Source database:

$ sqlplus hr/hr
SQL>  insert into departments (DEPARTMENT_ID,DEPARTMENT_NAME) values (1199,'Test Department');

Step 2: Switch log to make a new archive log

SQL> conn / as sysdba 
SQL> alter system switch logfile;

Step 3: Check the new archive log file on source database:

$ ls /orafra/ORCL/archivelog/2014_10_15/
o1_mf_1_14_b3x19t6c_.arc

Step 4: Check the sequence number on the source database:

RMAN> list archivelog all;

Step 5: copy the file to the distination:

$ scp /orafra/ORCL/archivelog/2014_10_15/o1_mf_1_14_b3x19t6c_.arc  dbservstandby:~/

Step 6: On the remote server, RMAN can catalog by archivelog.

RMAN> catalog archivelog '/home/oracle/o1_mf_1_14_b3x19t6c_.arc';

Step7: Recover the remote Oracle database with the archive log file:

RMAN> run {
set until sequence=15;
recover database;
}

Step 8: Verify on the remote server;

Note 1: To verify the modification, you should open the Oracle database on Read Only.

$ sqlplus / as sysdba
SQL> alter database open read only;
SQL>  select * from hr.departments where DEPARTMENT_ID =1199;

Note 2: The verification is done with success.

2. Update the remote Oracle Database periodically and automatically:

Now, I want to create a script shell who do these steps (described in the above scenario) automatically and periodically.

Step 1: SSH configuration to trust machine with public-key authentication:

On the remote machine, create a public/private key with RSA with the user oracle.

$ ssh-keygen -t rsa

copy the file id_rsa.pub to the production server:.

$ scp .ssh/id_rsa.pub oracle@dbserv:~

On the remote server

Note: Now, the remote Server is a trusted machine to connect to the production server with ssh/scp without providing a password every time.

Step 2: Configure the tnsnames.ora on the remote Oracle Linux Server

ORCL-PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserv.local.domain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  ) 
ORCL-STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbservstandby.local.domain)
PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Step 3: Create a directory on the Remote Oracle database to put the archive log file and this directory will be the catalog directory;

$ mkdir Archivelog_file_from_prod_database

Step 4: The bellow script is developed by mine (Wadhah DAOUEHI), that check the list archive log on the remote server, and also on the production server and copy the all the next archive log from the production server and catalog all of them of the remote server, finally recover the remote Oracle database server.

Note: If there are any commands/instructions useful that you find comment on, and I will add to the script.

#########################################################################################
### The main objectif of this script is to update/recover a standby Oracle database ####
### Tested On Oracle Database Server 11.2.0.4
### Created/Developed by Wadhah DAOUEHI; from wadhahdaouehi.tn ####
#########################################################################################
rman target sys/oracle@orcl-prod << EOF
spool log to '/tmp/list_archivelog_prod.csv';
list archivelog all;
spool log off;
exit;
EOF
rman target sys/oracle@orcl-standby << EOF
spool log to '/tmp/list_archivelog_standby.csv';
list archivelog all;
spool log off;
exit;
EOF
standby_last_arch_seq=$(grep -B 1 'Name:' /tmp/list_archivelog_stadby.csv | tail -2 | head -1 | awk '{print $3}')
standby_last_arch_name=$(grep 'Name:' /tmp/list_archivelog_standby.csv | tail -1| awk '{print $2}' | xargs basename)
prod_last_arch_seq=$(grep -B 1 'Name:' /tmp/list_archivelog_prod.csv | tail -2 | head -1 | awk '{print $3}')

if [ "$prod_last_arch_seq" -eq "$standby_last_arch_seq" ]; then
 echo "The Remote Server is still updated";
 exit;
fi;
grep 'Name:' /tmp/list_archivelog_prod.csv > arch_full_name_prod.csv 
found_arch_name=0
catalog_next_arch_name=0
rm -rf /home/oracle/Archivelog_file_from_prod_database/*

while read full_name; do
 prod_archive_full_name=$(echo "$full_name" | awk '{print $2}' )
 echo "$prod_archive_full_name" | grep "$standby_last_arch_name" && found_arch_name=1
 if [ "$found_arch_name" -eq 1 ] && [ "$catalog_next_arch_name" -eq 1 ]; then
 scp oracle@dbserv:"$prod_archive_full_name" /home/oracle/Archivelog_file_from_prod_database/
 fi;
 if [ "$found_arch_name" -eq 1 ]; then
 catalog_next_arch_name=1
 fi;
done < arch_full_name_prod.csv

# Catalog The new archive log with RMAN on the Remote/Standby Oracle database.
until_seq=$((prod_last_arch_seq + 1))

rman target sys/oracle@orcl-standby << EOF
catalog start with '/home/oracle/Archivelog_file_from_prod_database/' NOPROMPT ;
run {
set until sequence=$until_seq;
recover database;
}
exit;
EOF

echo "The Remote Server is updated, Now";
exit

Step 5: Automate the execution of the above script with crond service with Oracle Linux Server:

Note: This script will be executed every 5 minutes, So anyone can choose the update frequency.

Conclusion:

Part one and part two explain how to set up an active-passive high availability solution for Oracle database without using Oracle data guard solution.