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

Monday, 4 June 2012

Error ORA-01031/ORA-00604 on communication with standby

When I have hit the ORA-01031 error on version 10g, the cause was usually boring - badly configured connection primary<->standby, lack of password file, improper permissions on such file, etc. With 11g version the trivial error become more enigmatic. I checked connections, copied the password file from the primary and still got ORA-01031 on connection from the primary to the standby (and ORA-00604 on the connection from the standby to the primary). Finally under the link https://forums.oracle.com/forums/thread.jspa?messageID=10350931 I have found quite good explanation to my problem.
With the advent of 11g version DEFAULT profile has got some limitations - among others expired passwords. Due to this passwords go through grace time period, when "normal" user sees a message about grace time period. But automatically controlled connection to/from a standby gets lost with this behavior, while returned errors do not indicate the right solution, which is simple of course. One may
  • change a password (to solve temporarily)
  • or change the DEFAULT profile
  • or change SYS profile to less restrictive
One must remember that after any of those operations the current logfile needs to be applied on a standby in order to see a positive change. After that automatic redo shipping started to work properly.

UPDATE on 2013-06-20
It seems that despite possible problems with passwords (which may arise with similar symptoms) in my particular case the true reason behind the ORA-1031/ORA-604 errors is the local coniguration - on the database is enabled VPD technology and an ON LOGON trigger to enforce the required context settings. The connections are successful only if done from a primary to a mounted standby. More on the subject in another article.