Logical Standby Out of Sync — again!

I'm not a big fan of data guard's logical standby.  In my experience we have way too many issues that crop up and I spend way too much time having to manage the standby.  In my opinion this valuable time that could be spent on other areas that are more pressing.  The logical standby is used for reporting so when it's out of sync my attention is diverted to it and that happens way too often.  But it is what it is at this time.  Hopefully the future will bring some change that will eliminate this requirement.

In the meantime, one of the most problematic areas is when the logical standby is missing archive logs.  We have had many different scenarios where archive logs end up being deleted before being applied.  Why that happens is another conversation although it's mostly human error.

The problem is compounded because the primary utilize ASM and RAC, and the logical is filesystem single instance.  When we need to move archivelogs from primary to logical we follow these general steps:

1.  On the primary host. Copy the archivelog from ASM to filesystem.  This can be done via a RMAN, ASMCMD or DBMS_TRANSFER (DBMS_TRANSFER is the only way for 10g).  I prefer the ASMCMD cp simply because it's an easy shell script. Ensure you are logged in as a user with the correct credentials to execute asmcmd:

for i in $(asmcmd ls +FRA/ORCL/ARCHIVELOG/2013_04_19/*);
asmcmd cp +FRA/ORCL/ARCHIVELOG/2013_04_19/$i /u01/temp

Substitute the * for the characters that you are interested in copying. Usually I'm looking for specific sequence numbers and possible the thread number.

2. Move the files from the primary host to the logical standby host. I prefer scp but there are other methods that could be used as well.

scp thread_1_seq_* user@host:/path

substitute the correct filename with wildcard, as well as the user, host and path for the remote destination.

Now that the files are on the logical standby host there is a good chance that the names need to be changed. Since the primary archive format is thread ... but the logical just starts with the thread number. Not a big thing if only dealing with a few files, but with a hundred or more a shell script can ease the pain.

3. On the logical standby host. Execute a shell script that performs a mass rename of files. Yes there are many ways that this could probably be written, but I believe in keeping it simple.

for file in $(ls /path/filename*)
substr=`echo $file | cut -d '_' -f 4 | cut -d '.' -f 1`
mv ${file} 1_${substr}_69837890223.arc

In the above code the substitute the correct path for the path. And utilize wildcards and the actual filename to grab all the files that you would like to rename. This will be specific to your needs.

The substr utilizes the cut to remove everything from the last underscore (_) and the second one removes everything from the first period (.). So my file format for achive logs is thread_1_seq_1111111.22222.dbid. This means the sequence number that I must maintain begins right after the second underscore (_) or is the 4th group and before the first period. The first cut removes the first three groups plus the underscores (_) leaving the sequence number as the first group. The second cut then removes everything after the first group. I'm left with just the sequence number which I need in the filename for the logical standby.

The mv takes the $file and renames it to the correct format, thread_seq_dbid.arc.

A review of the alert.log on the standby shows that the apply process picks up the correctly named archive logs and begins to apply them.

Avoiding the whole rebuild process. Depending on how far behind will depend on how long it takes. For us we avoid impacting production by avoiding the replication process and are able to catchup 24 hours in about 2 hours without any gotchas.


Tagged: ,

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s