1

I've configured Data guard setup using data broker. When I'm inserting new data to the tables in primary database which is suppose to reflect in standby database. When I checked those data's in standby database it was not reflected there. So, I've restarted the database and opened it in read only. I've used the below command to avoid the delay:

SQL> alter database recover managed standby database nodelay disconnect from session;

But still a lot of time is taken to get reflected there in standby database.

What will be the time delay taken to get the data's from primary to standby?

Is this the standard time delay taken for a data to get reflected in standby database. Or do I have missed anything there in configuration.

Is there any way to sort it out?

  • 4
    There is no standard, it depends on your configuration. It can be anywhere between instantaneous to infinity (given you have enough resources). By default it will be as soon as log is switched on primary + (time to ship to standby + time it takes to apply the changes) – Raj Jun 22 '17 at 13:10
5

I think what you want is real time apply, but for that, you'll have to create standby redo logs on your standby database first. As a side note, to make role transitions easier, it is considered best practice to create standby redo logs on your primary database as well:

The standby redo logs should be the same size as your redo logs and you need at least 1 more group of standby logs than redo logs:

I use the following script on the primary database, which builds that create statements for the standby redo logs that I then run on the standby database:

select 'alter database add standby logfile group ' || 
to_number((select count(*) from v$log) + rownum) || '(''T:\ORACLE\STANDBYREDO\REDO0' || 
to_number((select count(*) from v$log) + rownum) || '.LOG'') SIZE ' || 
(select max( bytes/1024/1024) from v$log) || 'M;' as standbyredo
from v$log
union all
select 'alter database add standby logfile group ' || 
((select max(group#) from v$log) + (select count(*) from v$log) + 1) || '(''T:\ORACLE\STANDBYREDO\REDO0' || 
((select max(group#) from v$log) + (select count(*) from v$log) + 1) || '.LOG'') SIZE ' || 
(select max( bytes/1024/1024) from v$log) || 'M;' 
from dual;

After the standby logs are present on the standby database, to start real time apply, you need to include the using current logfile phrase in your recover command:

SQL> alter database recover managed standby database using current logfile disconnect from session;

During shipping and apply, you can check to verify that the standby logs are being used by querying v$standby_log on your standby database:

select * from v$standby_log;

I found Tim Hall's guide on Standby Databases invaluable when I was learning how to setup a physical standby.

  • Thanks Kris for your valuable answer. Now I'm able to do real time apply. But only the statements which I'm commiting is reflecting in Standby db. Do we have any way to get the values applied in standby without commit statement. For example: In the scenarios like primary db failover, the statements should get applied in standby even if it is not committed. @Kris Johnston – Joe Joy Valiyaveettil Jun 22 '17 at 14:42
  • @JoeJoyValiyaveettil No, nor would you want to. When the database goes down (shutdown abort, primary database loses power, whatever), active transactions are rolled back anyways... You wouldn't want to keep uncommitted transactions, that would lead to data inconsistency. – Kris Johnston Jun 22 '17 at 14:50
  • @JoeJoyValiyaveettil You can however configure the primary/standby as maximum availability mode which means the primary waits for confirmation that the redo was received at the standby before completing the commit on the primary. In this mode, 0 data loss is possible during role failovers, but there is a performance impact on the primary in this mode. The default mode is maximum performance (no performance impact on primary, but data loss is possible during failover). – Kris Johnston Jun 22 '17 at 14:56
  • @JoeJoyValiyaveettil If your standby database is open in read only mode and your query of the standby does not see data from statements that where executed but not commited on the primary site then that does not mean that the data wasn't already applied on the standby site but it only means that your session on the standby site cannot see it because it cannot see data of another open transaction. that is one of the main principles of database transactions. – miracle173 Jun 22 '17 at 15:31
  • @JoeJoyValiyaveettil If the gap between standby and primary is 3 seconds (there are ways to query the gap) and you executed a dml statement before 5 seconds on the primary then the modifications caused by the statements will be already aoolied on the standby. If the session executed a commit before 4 seconds than you will see it in your standby session, if the primary session executed a commit 2 seconds before or did not even execute a commit than you will not see the modified data in your standby session. – miracle173 Jun 22 '17 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.