HOW TO REPLICATE ROWS FROM ORACLE DATABASE TO IQ SYBASE DATABASE - Part 1

Every now and again It is necessary to work with lots of heterogeneous systems and what’s more being able to replicate rows among them.

Today I’m going to look at how oracle database rows  can be replicated on IQ SyBASE database.

First of all it is necessary to recap all the components. Let’s get started, shall we ?

1)    Oracle Database
2)    SYBASE RAO Agent
3)    SYBASE RTL Server
4)    SYBASE IQ database.

Everyone knows Oracle database, so I’m going to look at the second point, the  SYBASE RAO Agent.

The Replication Agent (RAO Agent) is a Java-based application that requires a Java Runtime Environment (JRE), JDBC driver for Oracle database and it is necessary to install it on the Oracle Database Server Machine too.

Each Replication Agent Java program uses an embedded Replication Agent System Database (RASD) to manage its stuff, I’ve been writing on it lately and you can find some handy information about it in my previous blog. 

Let’s see how it works, shall we ?

Using a Log Transfer Language (LTL), RAO Agent reads the primary Oracle database transaction log (archived logs),  and  transfers log records for replicated table to the Replication Server (RTL) , then it converts  and forwards LTL log records to the remote site (SYBASE IQ Database). 

To be continued....

SWITCH DATABASE TO COPY

In the previous post we  looked at the incremental image copy backup, today it’ time to try the recovery process out.    

There are two different ways to do so, let’s see how, shall we ?

1)Using the conventional restore and recovery process, where you have to restore the datafiles copy to the original path and what’s more recover them using the level 1 image copy backup and archived logs too.

2) Using the  ‘switch database to copy’ command where you don’t need to restore any datafiles because, they are already available in the backup directory.

It is only required to apply on them the level 1 image copy and all the archived logs available.

If it is necessary to recover the database immediately, it is the best way to do so.

Let’s get started, shall we ?

1)    Startup the database in a MOUNT state :

SQL > startup mount

2)    Execute the following command :

RMAN> switch database to copy;

Short log file:

Using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "/backup/datafile/o1_mf_system.dbf"
datafile 2 switched to datafile copy "/backup/datafile/o1_mf_undotbs1.dbf"
ecc
 
As you can verify on Oracle view, now the datafiles path are different, indeed our instance has started to work with the image copy datafiles backup.

SQL> select NAME from v$datafile;
NAME
--------------------------------------------------------------------------------
/backup/datafile/o1_mf_system_.dbf
/backup/datafile/o1_mf_undotbs1..dbf
Ecc

Also the Alert log files can confirm it as you can see below :

WARNING: switching recovery area datafile copy
/backup/datafile/ o1_mf_system.dbf" as database area datafile 

3)    Now it is time to recover those image copy datafiles :

RMAN > recover database;

Log :

Starting recover at 13-OCT-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=236 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /backup/datafile/o1_mf_system_.dbf

As I mentioned  before RMAN is going to apply the last level 1 image copy backup and archived logs too (if available).

4)    Open the database and keep your fingers crossed !!!

SQL> alter database open;

All those steps end up starting the database infact we ‘ve been able to figure the problem out and what’s more in a short time.

Well done ! 

Remember to try it out on a test environment and please redo a complete database backup , since the previous one is now online and no longer available to restore it.

Byeeeee

INCREMENTAL IMAGE COPY BACKUP

Every now and again it is necessary to speed up the recovery phase of a Oracle Database and what’s more to reduce the time of a backup, for example when  it can’t be completed during the night because it’s too big.

To achieve the first request you can execute a RMAN image copy backup and an incrementally updated image copy backup to achieve the second request too.

Let’s see how, shall we ?

• Execute the RMAN command and connect it to the database as sysdba :

RMAN > connect target /

• Execute the following command :

RMAN > RUN {

BACKUP device type disk INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'test_incr_backup' DATABASE;

RECOVER COPY OF DATABASE WITH TAG 'test_incr_backup';

}

If the level 0 image copy doesn’t exist,RMAN creates it, following you’ll find a short log file:

Starting backup at 08-OCT-15

using channel ORA_DISK_1

no parent backup or copy of datafile 2 found

no parent backup or copy of datafile 100 found

no parent backup or copy of datafile 98 found

input datafile fno=00002 name=/u01/undotbs01.dbf

ecc

Since the first image copy doesn’t exist, RMAN can’t produce a level 1 backup set and what’s more the RECOVER phase doesn’t start too.

NB : The RECOVER phase can only get started  if a level 1 incremental image copy backup is found.

NB : The backup of the first IMAGE COPY can take a very long time especially if your 

       database is really huge, so be careful !!

•  Execute again the previous command :

RMAN > RUN {

BACKUP device type disk INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'test_incr_backup' DATABASE;

 RECOVER COPY OF DATABASE WITH TAG 'test_incr_backup';

}

Since we’ve already got a level 0 image copy, the second execution of the backup command is going to produce our first level 1 incremental image copy.

Following you’ll find a short log file :

Starting backup 08-OCT-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00100 name=data.dbf

channel ORA_DISK_1: starting piece 1 at 08-OCT-15 channel ORA_DISK_1: finished piece..

Since our first level 1 incremental image copy is now ready, the RECOVER command can now apply that on the first level 0 image copy and forward it up to now.

Following you’ll find a short log file :

Starting recover at 09-OCT-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile copies to recover

recovering datafile copy fno=00002 name=/undotbs1_.dbf

Be careful : Performance issues  come up if the block change tracking isn’t available during the incremental image backup.  So it’s necessary to enable it, executing the following command :

If OMF is enabled :

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Otherwise :

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/PATH/FILE.TRACK.F’;

Those commands end up enabling the block change tracking and what’s more tracking changed block.

You’ll try, won’t you ?

Remember to try it out on a test environment and good luck ! 

Byeeeeeeeee

SyBASE Replication Agent System Database (RASD) got corrupted

Every now and again problems crop up leaving us with few possibilities to figure it out, this can happen also at work, where you have to come up with a solution immediately.

Let me explain how, shall we ?

Due to system hung we had to reboot a production database server, which was made up of an Oracle Database and what’s more a Sybase Replication Agent (RAO) which replicates  Oracle data rows through a Real-Time loading Server to a Sybase IQ database.

The Oracle database started up without any problems instead when I was trying to start the RAO Agent which is made up of a java process and also Replication Agent System Database (RASD)  process I got the following error :

 E. 09/11 20:58:17. *** ERROR *** Assertion failed: 201129 (11.0.1.2044)
File is shorter than expected

The Replication Agent system Database  got corrupted !!

This was a fairly serious issue !!

It was necessary to contact the Sybase support  and following you’ll find the procedure to figure out the corruption and restart the RAO agent :
export  LD_LIBRARY_PATH=/directorty/RAX-XX/RAO/lib

cd $RAO_HOME/repository

Make sure the RAO.log is not there

Make sure the RAO.db file is there.

Execute the following command : dbeng12 –f RAO.db
NB : this command tells the database to come up wihout a log file. 

Execute the following command :dblog –t RAO.log  RAO.db
NB: this command tells the database to assign a new log file to the database.


Restart the RAO using normal procedures.
 

The procedure ended up bringing the RAO again online and what’s more in ready to replicate data.

See you in my next blog.

Byeeeee