Find us on Google+ Google+

January 20, 2019

Oracle Data Guard Interview Questions with Answers

Oracle DBA Data Guard Interview Questions [2019]

1. How to setup Data Guard?

2. What are the advantages in using Oracle Data Guard?

3. What are different types of protection modes in Data Guard and which is default?
Answer :
Maximum performance:
This is the default protection mode. It provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log.

Maximum protection:
This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions.

Maximum availability:
This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one standby database.

4. How many standby databases we can create (in 10g/11g)?
Answer :
Till Oracle 10g, 9 standby databases are supported.
From Oracle 11g R2, we can create 30 standby databases.

5. What are the parameters we have to set in primary/standby for Data Guard?

6. What is the use of fal_server and fal_client, is it mandatory to set these?

7. What are differences between physical, logical, snapshot standby and ADG (or) what are different types of standby databases?
Answer :
Physical standby – in mount state, MRP will apply archives
ADG (Active Data Guard) – in READ ONLY state, MRP will apply archives
Logical standby – in READ ONLY state, LSP will run
Snapshot standby databases – Physical standby database can be converted to snapshot standby database, which will be in READ WRITE mode, can do any kind of testing, then we can convert back snapshot standby database to physical standby database and start MRP which will apply all pending archives.

8. How to find out backlog of standby?
Answer :
select round((sysdate - a.NEXT_TIME)*24*60) as "Backlog",m.SEQUENCE#-1 "Seq Applied",m.process, m.status 
from v$archived_log a, (select process,SEQUENCE#, status from v$managed_standby where process like '%MRP%')m where a.SEQUENCE#=(m.SEQUENCE#-1);

9. If you didn't have access to the standby database and you want to find out what error has occurred in a Data Guard configuration, what view would you check in the primary database to check the error message?
Answer :
You can check the v$dataguard_status view. 
select message from v$dataguard_status;

10. How can you recover standby which far behind from primary (or) without archive logs how can we make standby sync?
Answer :
By using RMAN incremental backup.

11. What is snapshot standby (or) How can we give a physical standby to user in READ WRITE mode and let him do updates and revert back to standby?
Answer :
Till Oralce 10g, create guaranteed restore point, open in read write, let user do DMLs on snapshot standby, flashback to restore point, start MRP.
From Oracle 11g, convert physical standby to snapshot standby, let user do DMLs on snapshot standby, convert to physical standby, start MRP.

12. What are new features in 11g Data Guard?

13. What are the uses of standby redo log files?
Answer :
A standby redo log resides on the standby database site. The standby redolog file is similar to an online redo log, except that a standby redo log is used to store redo data that has been received from a another/primary database.

14. What is dg_config?

15. What is RTA (real time apply) mode MRP?  How will you identify if DB is running in real time apply mode or not?

16. What is the difference between normal MRP (managed apply) and RTA MRP (real time apply)?

17. What are various parameters in log_archive_dest and it’s use?

18. I have added a tempfile on primary database, but the tempfile is not reflecting on standby database (despite standby_file_management is set to AUTO). Why ?

19. What is the difference between SYNC/ASYNC, LGWR/ARCH, and AFFIRM/NOAFFIRM?

20. What is Data Guard broker (or) what is the use of dgmgrl?

21. What is StaticConnectIdentifier property used for?

22. What is failover/switchover (or) what is the difference between failover and switchover?
Answer :
Switchover – This is done when both primary and standby databases are available. It is pre-planned.

Failover – This is done when the primary database is NO longer available (i.e. in a Disaster). It is not pre-planned.

23. What is the usage of DB_FILE_NAME_CONVERT or LOG_FILE_NAME_CONVERT parameters in Oracle Data Guard setup?

24. What are the background processes involved in Data Guard?
Answer :

25. What are the different services available in Oracle Data Guard?

26. How to change protection mode in Oracle Data Guard setup?

27. How to check what protection mode of primary or standby database in Oracle Data Guard?

28. What is Active DataGuard (ADG). Does it need additional licensing?


  1. hi sathya
    in above some of questions don't have the answers pls give the answers

  2. excellent article

  3. Hi Satya,

    It is very helpful for fresher and thank you for sharing these information with US.