Ran into an interesting situation recently with a managed services client running Oracle 11.1.0.7.0 Enterprise Edition on Linux in a two node RAC environment with Dataguard.
The client asked to have the dataguard instance changed over to read/write to do some testing. This is a common request and something that I had done for them in the past. I ran into some issues during the switchover and had to restore the dataguard instance. The restore completed successfully and everything was looking good. I started the redo apply on the standby database using the command alter database recover managed standby database disconnect. The command completed successfully and I then checked to make sure the logs were being applied by querying the v$managed_standby view. Everything looked good and the logs were being shipped over and applied. I then canceled the redo issuing the command alter database recover managed standby database cancel and proceeded to create the standby logs on the physical standby. The logs created successfully and I then started the redo apply by issuing the command alter database recover managed standby database using current logfile disconnect. The command completed successfully and I again queried the v$managed_standby view to ensure the logs were applying correctly. But this time it was not applying the logs and in fact it was looking for sequence# 1 and waiting for gap. This had me totally baffled on how it went from applying log sequence# 24532 to now looking for sequence# 1. Especially since the control file being used was a standby control file created from the primary database. No matter what I did the instance was still looking for sequence# 1.
After talking things over with our senior dba the decision was made to restore the database again. The restore was successful and everything was looking good again. I started the redo apply by issuing the alter database command and again it went back to looking for sequence# 1.
I started thinking about an issue I had encountered on a different client that had a similar environment. I would make changes to the init.ora file and restart the database and check the altered parameters and I would find they had been changed back to the original value. I found out that if you have a dataguard environment and if the parameters in the init.ora file do not match what is in the dataguard configuration they will be overwritten and changed to match. Knowing this I logged into dgmgrl and dropped the current configuration and recreated everything from the beginning. After I did that I shut down the physical standby, brought it back up to the mount mode and started the redo apply again. When I checked the v$managed_standby view the logs were being applied correctly and looking for the current sequence#.
Good morning . I have a redo apply issue with
dataguard configuration on windows. The standby
database is created but i used the code to apply the
logs on the standby , the standby does not
synchronize with the primary. Sometimes it works but other time it doesn’t.
I don’t know where is exactly the matter for the lack of redo apply and synchronization .
I need someone help.
Joao,
Run the following sql query to see what the status of the MRP process is and if the logs are being shipped across. When you run the query look at the STATUS for the MRP0 process. It should show a status of APPLING_LOG. If it shows WAIT_FOR_LOG or WAIT_FOR_GAP then could have an issue. WAIT_FOR_LOG usually means it is just waiting for the log to complete on the primary. If it shows WAIT_FOR_GAP then the archivelog could have been backed up and deleted on the primary. You would then need to check the status of the archivelog on the primary that it is waiting for.
SET LINESIZE 150
SET PAGESIZE 9999
SET TERMOUT OFF
ALTER session
SET nls_date_format = ‘yyyy-mon-dd hh24:mi:ss’;
SET TERMOUT ON
PROMPT –PROCESS STATUS:
SELECT process, status, thread#, sequence#, block#, blocks, sysdate
FROM v$managed_standby
ORDER BY process, thread#, sequence#;
Hello Joao,
There can be a number of potential causes for your issue. The only way we can help would be to alayze your configurations and error logs.
If you would like consultanting services from a Sr. Oracle DBA resource please fill out this form and we can see what help you require.
https://www.xtivia.com/contact/
Thank you,