How to verify real-time redo streaming in Data Guard 9i

[BJE_DBA]

I wanted a 100% verifiable way to prove they were indeed streaming and not just accumulating on the primary.

DUMP LOGFILE 'xxx' did not seem to bear the fruit I wanted and it turns my 100MB log into a 170MB file.

Its difficult to verify because the standby database is never open so you cannot simply query the tables unless you cancel recovery and open read-only.

After mere seconds I checked my standby log for a very distinctive record and its there....

ÜÔü$  ä@KL ***595959<<<<>>>>595959*** ÜÔü$  äÜÔü$  ä

..amidst the binary control info is

***595959<<<<>>>>595959*** my inserted record on the standby

This was what I have been looking for and I did not need to stop nor open up the standby.

I will take one more look at dump logfile but I think my 'os level copy of current standby redo log' followed by a search for distinctive row data is simple, absolute and allows the standby to remain in recovery mode.

I guess its prudent to scan the lqwr trace files for errors but not as clear cut an answer if you ask me, plus I may not have tracing on in production.

I think logminor is worth a look, perhaps it can perform the test quickly and save me redo log copy.

Anybody know the best way? One that can be automated from a web servlet call?

off to learn about log minor.....

...back from log minor already - yeach, looks like you have to open the database and run sql pl/sql. I thought it was an external utility that read the log file.

Sticking with 'cp log log2' grep for distinctive record - call it good.

[stmontgo]

what are you talking about?

running data guard in protected mode with zero data loss?

are you wanting to verify that in this manner all committed transactions on the primary are availale on the standby?

[BJE_DBA]

Even in max performance mode, if you use standby redo logs (SLRs) then the transactions are streamed over to the standby immediately.

I have looked, they are there as quick as I can copy the standby redo log which is active and search the file.

Thats not all - the data is over there even before I commit.

I thought that without SLRs the data would accumulate on the primary unilt a log switch but they actually stream over but are not written to SLRs they are written to archive logs.

The problem with that is the partially filled archive log is useless in a failover, it cannot be used unless it is completely filled.

So its important to both 1) have SLRs 2) and to know they are working. Let me know if you think copying the online redo log could ever cause it to become corrupted as I did experience that in my lab, dont know if its related, I had copied it successfully dozens of times prior to that - it should be benign as it simply 'reads' it.

Here is the Oracle excerpts that backup what I am saying about data loss.

In Oracle 9i you have to opportunity to add Standby Rodo Log groups to your Online Redo Log groups. These Standby Redo Logs then store the information received from the Primary Database. In case of a Failover situation, you will have less data loss than without Standby Redo Logs.

The great advantage of Standby Redo Logs is that every entry written into the Online RedoLogs of the Primary Database is transfered to the Standby Site and written into the Standby Redo Logs at the same time; threfore, you reduce the probability of data loss on the Standby Database.

In case you do not have Standby Redo Logs, an Archived Redo Log is created by the RFS process and when it has completed, this Archived Redo Log is applied to the Standby Database by the MRP (Managed Recovery Process). An open (not fully written) Archived Redo Log file cannot be applied on the Standby Database and will not be used in a Failover situation. This causes a certain data loss.

If you have Standby Redo Logs, the RFS process will write into the Standby Redo Log as mentioned above and when a log switch occurs, the Archiver Process of the Standby Database will archive this Standby Redo Log to an Archived Redo Log, while the MRP process applies the information to the Standby Database. In a Failover situation, you will also have access to the information already written in the Standby Redo Logs, so the information will not be lost.