Tuesday 5 June 2012

Enabling Real Time Apply on open standby (with VPD)

Actually nothing special. One needs to stop the recovery, open standby in READ-ONLY mode and one again start the recovery.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
There is however one trick - one needs to do this rather quick. It seems (at least for me) that even one archivelog behind the most actual means the managed replication will not start, waiting apparently on some flag indicating the end of previously written log. Thus copying to standby needed logs does not mean, the standby will apply them. It seems a condition is here that archivelog on standby should be "in transition" and the same log being current on primary.

UPDATE from 2013-06-20
It seems I missed some apparent (and specific for this configuration) facts. First of all the database in question despite of having its standby as Active Data Guard has also implemented some VPD policy, which corresponds to a trigger triggered on logon.

The starting position is the following:
  • primary database up and running, log_archive_dest_state_2 equals to defer
  • standby database shut down, few archivelogs behind the primary, FAL server indicated

When the standby is mounted or started it tries to connect to FAL server. In my case this connection fails due to the trigger mentioned above as it throws an exception for some reason. If the standby is mounted then enabling log_archive_dest_2 (ie setting log_archive_dest_state_2 do enable) on the primary makes the primary to connect to the standby. Those connections succeed. Now no matter what I will do (open the standby in READ ONLY mode and then start the Real Time Apply or start it on the mounted standby) the managed recovery will succeed - after recovery start the ARCH processes provide not yet transferred archivelogs, the recovery process (MRP0) applies them, and then (assuming USING CURRENT LOGFILE clause) switches to communication with a primary LNS process in order to apply directly from memory not waiting for the logfile switch.
But if I would open the standby directly or do not enable log_archive_dest_2 when the standby stays in MOUNTED mode then all the connections from the primary would fail as the FAL connection from the standby to the primary. In such case there is impossible to open the communication and enable successfully the replication - the MRP0 process will start without problems, yet the archivelogs will not be transferred to the standby.
Then the only solution I've found is to shut the standby down and then mount it, ensure the communication with the primary is enabled (simple switching log_archive_dest_state_2 to defer, then to enabled again resets the connections and the messages in the standby alert log will tell if it works (no messages or error messages signal the problem)) and now (optionally) open, then start the managed recovery.

All my previous hypotheses are apparently wrong. There is no need to be at the same sequence on the primary and the standby, so the time after which the recovery is started is irrelevant in this context. The only thing important is to make connections during mount phase (as they are impossible to acquire when any database is in open mode).

No comments: