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.

 Table of Contents:

– Set up one scenario

–  Update  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');

insert_departments

 

Step 2: Switch log to make a new archive log

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

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

ls_archive

 

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

RMAN> list archivelog all;

list_archivelog_src

 

Step 5: copy the file to the distination:

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

scp_archivelog

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

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

catalog_archivelog_dest

 

 

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

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

recover_database_dest

 

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;

select hr.departments;

 

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

ssh_key_gen_remote

 

 

 

 

 

 

 

  • copy the file id_rsa.pub to the production server:.
$ scp .ssh/id_rsa.pub oracle@dbserv:~

scp_id_rsa.pub

 

 

 

 

  • On the remote server

ssh_cat_authorized

 

 

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

ssh_trust

 

 

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)
    )
  )

cat_tnsnames.ora

 

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

pwd

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

script_1

 

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

crontab

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.

 

 

Bookmark the permalink.
Loading Facebook Comments ...

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

  1. Pingback: Wadhah DAOUEHI - Part 1: Active-passive High Availability Solution for Oracle Database without Data Guard.

Leave a Reply