Here are some Oracle Data Guard Interview Question And Answers, hope it will help for you.
- What is a Dataguard?
Oracle Dataguard is a disaster recovery solution from Oracle Corporation that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios.
- When did Oracle start supporting standby databases?
Oracle Database 7.3 is the first version to support standby databases.
- Was it easy to setup standby database in oracle database 7.3?
It wasn’t so easy, because all the redo log application steps had to be performed manually
- What was the improvement in Oracle database 8i?
From Oracle database 8i onwards, concept of shipping and applying redo logs, opening standby database in read-only mode on finishing log application were the enhancements introduced.
- What features of Oracle dataguard were introduced in Oracle database 9i version?
In Oracle database 9i Oracle dataguard Broker(a Command-lien interface for setting up and managing dataguard), concept of protection modes to prevent data variances between primary and secondary was introduced.
Logical standby database concept was introduced in Oracle Database 9i Release2.
- What was the dataguard enhancement made in Oracle database 10g?
Real-time apply of redo data on physical and logical standby databases was the major enhancement made in Oracle database 10g version.
- What are the enhancements made to dataguard in Oracle database 11g?
Active Data guard is the new feature introduced in Oracle database 11g.
- What are the types of Oracle Data Guard?
Oracle Data Guard classified into two types based on way of creation and method used for Redo Apply. They are as follows.
1. Physical standby (Redo Apply technology)
2. Logical standby (SQL Apply Technology)
- What are the advantages in using Oracle Data Guard?
Following are the different benefits in using Oracle Data Guard feature in your environment.
1. High Availability.
2. Data Protection.
3. Off-loading Backup operation to standby database.
4. Automatic Gap detection and Resolution in standby database.
5. Automatic Role Transition using Data Guard Broker.
- What are the different services available in Oracle Data Guard?
Following are the different Services available in Oracle Data Guard of Oracle database.
1. Redo Transport Services.
2. Log Apply Services.
3. Role Transitions.
- What are the different Protection modes available in Oracle Data Guard?
Following are the different protection modes available in Data Guard of Oracle database you can use any one based on your application requirement.
1. Maximum Protection
2. Maximum Availability
3. Maximum Performance
- How to check what protection mode of primary database in your Oracle Data Guard?
By using following query you can check protection mode of primary database in your Oracle Data Guard setup.
SELECT PROTECTION_MODE FROM V$DATABASE;
SQL> select protection_mode from v$database; PROTECTION_MODE ————————————— MAXIMUM PERFORMANCE
- How to change protection mode in Oracle Data Guard setup?
By using following query your can change the protection mode in your primary database after setting up required value in corresponding LOG_ARCHIVE_DEST_n parameter in primary database for corresponding standby database.
ALTER DATABASE SET STANDBY DATABASE TO MAXIMUM [PROTECTION|PERFORMANCE|AVAILABILITY]; Example: alter database set standby database to MAXIMUM PROTECTION;
- What are the advantages of using Physical standby database in Oracle Data Guard?
Advantages of using Physical standby database in Oracle Data Guard are as follows.
Load balancing (Backup and Reporting).
- What is physical standby database in Oracle Data Guard?
Oracle Standby database are divided into physical standby database or logical standby database based on standby database creation and redo log apply method. Physical standby database are created as exact copy i.e block by block copy of primary database. In physical standby database transactions happen in primary database are synchronized in standby database by using Redo Apply method by continuously applying redo data on standby database received from primary database. Physical standby database can offload the backup activity and reporting activity from Primary database. Physical standby database can be opened for read-only transactions but redo apply won’t happen during that time. But from 11g onwards using Active Data Guard option (extra purchase) you can simultaneously open the physical standby database for read-only access and apply redo logs received from primary database.
- What is Logical standby database in Oracle Data Guard?
Oracle Standby database are divided into physical standby database or logical standby database based on standby database creation and redo log apply method. Logical standby database can be created similar to Physical standby database and later you can alter the structure of logical standby database. Logical standby database uses SQL Apply method to synchronize logical standby database with primary database. This SQL apply technology converts the received redo logs to SQL statements and continuously apply those SQL statements on logical standby database to make standby database consistent with primary database. Main advantage of Logical standby database compare to physical standby database is you can use Logical standby database for reporting purpose during SQL apply i.e Logical standby database must be open during SQL apply. Even though Logical standby database are opened for read/write mode, tables which are in synchronize with primary database are available for read-only operations like reporting, select queries and adding index on those tables and creating materialized views on those tables. Though Logical standby database has advantage on Physical standby database it has some restriction on data-types, types of DDL, types of DML and types of tables.
- What are the advantages of Logical standby database in Oracle Data Guard?
Better usage of resource
- What is the usage of DB_FILE_NAME_CONVERT parameter in Oracle Data Guard setup?
DB_FILE_NAME_CONVERT parameter is used in Oracle Data Guard setup that to in standby databases. DB_FILE_NAME_CONVERT parameter are used to update the location of data files in standby database. These parameter are used when you are using different directory structure in standby database compare to primary database data files location.
- What is the usage of LOG_FILE_NAME_CONVERT parameter in Oracle Data Guard setup?
LOG_FILE_NAME_CONVERT parameter is used in Oracle Data Guard setup that to in standby databases. LOG_FILE_NAME_CONVERT parameter are used to update the location of redo log files in standby database. These parameter are used when you are using different directory structure in standby database compare to primary database redo log file location.
- Step for Creating Physical Standby(Below are just points which you can remember and tell an example by your own)
1. Enable forced logging
2. Create a password file
3. Configure a standby redo log
4. Enable archiving
5. Set up the primary database initialization parameters
6. Configure the listener and tnsnames to support the database on both nodes
- Tell me about parameter which is used for standby database?
The LOG_ARCHIVE_CONFIG parameter enables or disables the sending of redo streams to the standby sites.The DB_UNIQUE_NAME of the primary database is dg1 and the DB_UNIQUE_NAME of the standby database is dg2. The primary database is configured to ship redo log stream to the standby database. In this example, the standby database service is dg2. Next, STANDBY_FILE_MANAGEMENT is set to AUTO so that when Oracle files are added or dropped from the primary database, these changes are made to the standby databases automatically. The STANDBY_FILE_MANAGEMENT is only applicable to the physical standby databases. Setting the STANDBY_FILE_MANAGEMENT parameter to AUTO is is recommended when using Oracle Managed Files (OMF) on the primary database. Next, the primary database must be running in ARCHIVELOG mode.
- What is Redo Transport Services?
It control the automated transfer of redo data from the production database to one or more archival destinations. Redo transport services perform the following tasks: a) Transmit redo data from the primary system to the standby systems in the configuration.
b) Manage the process of resolving any gaps in the archived redo log files due to a network failure.
c) Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the primary database or another standby database.
- What is apply services?
Apply redo data on the standby database to maintain transactional synchronization with the primary database. Redo data can be applied either from archived redo log files, or, if real-time apply is enabled, directly from the standby redo log files as they are being filled, without requiring the redo data to be archived first at the standby database. It also allows read-only access to the data.
- What is a Dataguard?
- What is difference between physical and standby databases?
- The main difference between physical and logical standby databases is the manner in which apply services apply the archived redo data:a) For physical standby databases, Data Guard uses Redo Apply technology, which applies redo data on the standby database using standard recovery techniques of an Oracle database.
b) For logical standby databases, Data Guard uses SQL Apply technology, which first transforms the received redo data into SQL statements and then executes the generated SQL statements on the logical standby database.
- What is Data Guard Broker?
- Data guard Broker manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager. It can be used to perform:a) Create and enable Data Guard configurations, including setting up redo transport services and apply services
b) Manage an entire Data Guard configuration from any system in the configuration
c) Manage and monitor Data Guard configurations that contain Oracle RAC primary or standby databases
d) Simplify switchovers and failovers by allowing you to invoke them using either a single key click in Oracle Enterprise Manager or a single command in the DGMGRL command-line interface.
e) Enable fast-start failover to fail over automatically when the primary database becomes unavailable. When fast-start failover is enabled, the Data Guard broker determines if a failover is necessary and initiates the failover to the specified target standby database automatically, with no need for DBA intervention.
- What are the Data guard Protection modes and summarize each?
- 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.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.
Here are some additional Oracle Data Guard Interview Questions:
1. If you didn’t have access to the standby database and you wanted 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?
You can check the v$dataguard_status view. Select message from v$dataguard_status;
2. In Oracle 11g, what command in RMAN can you use to create the standby database while the target database is active?
Oracle 11g has made it extremely simple to set up a standby database environment because Recovery Manager (RMAN) now supports the ability to clone the existing primary database directly to the intended standby database site over the network via the DUPLICATE DATABASE command set while the target database is active. RMAN automatically generates a conversion script in memory on the primary site and uses that script to manage the cloning operation on the standby site with virtually no DBA intervention required. You can execute this in a run block in RMAN:
duplicate target database for standby do recover from active database;
3. What additional standby database mode does Oracle 11g offer?
Oracle 11g has introduced the Oracle Snapshot Standby Database. In Snapshot Standby Database a physical standby database can easily open in read-write mode and again you can convert it back to the physical standby database. This is suitable for test and development environments and also maintains protection by continuing to receive data from the production database and archiving it for later use.
4. In Oracle 11g how can speed up backups on the standby database?
In Oracle 11g, block change tracking is now supported in the standby database.
5. With the availability of Active Data Guard, what role does SQL Apply (logical standby) continue to play?
Use SQL Apply for the following requirements: (a) when you require read-write access to a synchronized standby database but do not modify primary data, (b) when you wish to add local tables to the standby database that can also be updated, or (c) when you wish to create additional indexes to optimize read performance. The ability to handle local writes makes SQL Apply better suited to packaged reporting applications that often require write access to local tables that exist only at the target database. SQL Apply also provides rolling upgrade capability for patchsets and major database releases. This rolling upgrade functionality can also be used by physical standby databases beginning with Oracle 11g using Transient Logical Standby.
6. Why would I use Active Data Guard and not simply use SQL Apply (logical standby) that is included with Data Guard 11g?
If read-only access satisfies the requirement – Active Data Guard is a closer fit for the requirement, and therefore is much easier to implement than any other approach. Active Data Guard supports all datatypes and is very simple to implement. An Active Data Guard replica can also easily support additional uses – offloading backups from the primary database, serve as an open read-write test system during off-peak hours (Snapshot Standby), and provide an exact copy of the production database for disaster recovery – fully utilizing standby servers, storage and software while in standby role.
7. Why do I need the Oracle 11g Active Data Guard Option?
Previous capabilities did not allow Redo Apply to be active while a physical standby database was open read-only, and did not enable RMAN block change tracking on the standby database. This resulted in (a) read-only access to data that was frozen as of the time that the standby database was opened read-only, (b) failover and switchover operations that could take longer to complete due to the backlog of redo data that would need to be applied, and (c) incremental backups that could take up to 20x longer to complete – even on a database with a moderate rate of change. Previous capabilities are still included with Oracle Data Guard 11g, no additional license is required to use previous capabilities.
8. If you wanted to upgrade your current 10g physical standby data guard configuration to 11g, can you upgrade the standby to 11g first then upgrade the primary ?
Yes, in Oracle 11g, you can temporarily convert the physical standby database to a logical standby database to perform a rolling upgrade. When you issue the convert command you need to keep the identity:
alter database recover logical standby keep identity;
9. If you have a low-bandwidth WAN network, what can you do to improve the Oracle 11g data guard configuration in a GAP detected situation?
Oracle 11g introduces the capability to compress redo log data as it transports over the network to the standby database. It can be enabled using the compression parameter. Compression becomes enabled only when a gap exists and the standby database needs to catch up to the primary database.
alter system set log_archive_dest_1=’SERVICE=DBA11GDR COMPRESSION=ENABLE’;
10. In an Oracle 11g Logical Standby Data Guard configuration, how can you tell the dbms_scheduler to only run jobs in primary database?
Oracle 11g, logical standby now provides support for DBMS_SCHEDULER. It is capable of running jobs in both primary and logical standby database. You can use the DBMS_SCHEDULER.SET_ATTRIBUTE procedure to set the database_role. You can specify that the jobs can run only when operating in that particular database role.
11. How can you control when an archive log can be deleted in the standby database in oracle 11g ?
In Oracle 11g, you can control it by using the log_auto_delete initialization parameter. The log_auto_delete parameter must be coupled with the log_auto_del_retention_target parameter to specify the number of minutes an archivelog is maintained until it is purged. Default is 24 hours. For archivelog retention to be effective, the log_auto_delete parameter must be set to true.
12. Can Oracle Data Guard be used with Standard Edition of Oracle ?
Yes and No. The automated features of Data Guard are not available in the standard edition of Oracle. You can still however, perform log shipping manually and write scripts to manually perform the steps. If you are on unix platform, you can write shell scripts that identify the logs and then use the scp or sftp command to ship it to the standby server. Then on the standby server, identify which logs have not been applied and apply/recover them maually and remove them once applied.