Q 1. What Is Data Guard?
Data Guard offers services to create and manage standby databases, helping Oracle databases recover from disasters and data loss. It keeps these standby databases as copies of the main production database and can work with standard backup and cluster methods to ensure data protection and availability.
Q 2. What Is Dg Broker?
DG Broker is a management and monitoring tool for Oracle Data Guard. It automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations. You can perform management tasks using Oracle Enterprise Manager (OEM) or the command-line tool “DGMGRL.”
Q 3. What Is The Difference Between Data Guard And Standby?
DataGuard: DataGuard is a tool for managing standby databases. It offers services to create, maintain, and monitor standby databases, helping production Oracle databases withstand disasters and data corruption. It keeps standby databases as backups of the production database. If the production database goes down due to an outage, DataGuard can quickly switch a standby database to act as the production database, reducing downtime.
Standby Database: A physical standby database is an exact copy of the primary database, with the same structure and schema, including indexes. It stays synchronized with the primary database through Redo Apply, which applies redo data from the primary. This type of database can also be used for other business needs, not just for disaster recovery, although to a limited extent.
Q 4. What Are The Differences Between Physical/logical Standby Databases? How Would You Decide Which One Is Best Suited For Your Environment?
Physical standby DB: It is a physical copy of the primary database, including datafiles and schema. It stays synchronized with the primary database by applying redo data to the standby DB.
Logical Standby DB: A logical standby database holds the same information as the production database, but its physical structure may differ. It syncs with the primary database using SQL Apply, converting redo data from the primary into SQL statements and executing them on the standby database.
We can open the physical standby database in “read-only” mode for application users, allowing only select queries. During this time, we cannot apply redo logs from the primary database. However, we don’t encounter these issues with the logical standby database; we can open it normally and allow user access while applying archived logs from the primary database. For large OLTP transactions, using a logical standby database is preferable.
Q 5. Explain Active Data Guard?
Oracle Active Data Guard allows read-only access to a standby database for tasks like querying and reporting while it keeps up with changes from the production database. It also supports quick incremental backups to the standby database and offers increased high availability and disaster protection against outages.
Q 6. What Are Differences Between Physical, Logical, Snapshot Standby And Active dataguard (or) What Are Different Types Of Standby Databases?
Physical standby – When mounted, MRP will use archive logs.
Active DG – In READ ONLY mode, MRP will use archive logs.
Logical standby – In READ ONLY mode, LSP will operate.
Snapshot standby databases – A physical standby database can change to a snapshot standby database, which allows changes and testing. After testing, it can switch back to a physical standby database, and MRP will apply any pending archive logs.
Q 7. How To Find Out Backlog Of Standby?
SELECT round((sysdate – a.NEXT_TIME)2460) 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);
Q 8. 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;
Q 9. How Can U Recover Standby Which Far Behind From Primary (or) Without Archive Logs How Can We Make Standby Sync?
By using RMAN incremental backup.
Q 10. 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?
Create a guaranteed restore point in Oracle 10g, open it for updates, then flashback to restore it and start MRP. In Oracle 11g, convert a physical standby to snapshot standby for updates, then convert it back to physical standby and start MRP.
Q 11. What Are New Features In 11g Data Guard?
Here is some data guard category and their enhancements:
– Data Protection: Advanced Compression, Lost-write protection, Fast-Start Failover
– Increase ROI: Active Data Guard, Snapshot Standby
– High Availability: Faster Redo Apply, Faster failover & switchover, Automatic Failover using ASYNC
– Manageability: Mixed Windows/Linux
Q 12. What Are The Uses Of Standby Redo Log Files?
A standby redo log is necessary for maximum protection and availability modes, and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files. When the real-time apply feature is on, log apply services can apply redo data immediately without waiting for the current standby redo log file to be archived. This leads to quicker switchover and failover times because the standby redo log files are applied to the standby database before the failover or switchover starts.
Q 13. What Is Dg_config ?
Specify the DG_CONFIG attribute to identify the DB_UNIQUE_NAME for the primary and standby databases in the Data Guard setup. By default, this parameter allows the primary database to send redo data to remote locations and enables standby databases to receive it.
Q 14. What Is Rta (Real-Time Apply) Mode MRP?
Real-time apply allows the LGWR process to write to a standby log file simultaneously with the online redo log file before log shipping. This ensures that all committed transactions are processed without loss. In this setup, once a transaction is committed on the Primary, the changes are immediately available on the Standby. The MRP, or Managed Recovery Process, applies archived redo logs to the standby database in Data Guard.
Q 15. What Is The Difference Between Sync/async, Lgwr/arch, And Affirm/noaffirm?
Sync means data is transferred right away, while async allows for delays. Lgwr (Log Writer) saves redo entries to disk, and arch (Archiver) stores backup logs. Affirm makes sure data is fully written before confirming it’s done, while noaffirm doesn’t guarantee this.
This includes whether network input/output (I/O) is done right away (SYNC) or with delays (ASYNC) during archiving with the log writer process (LGWR). It also indicates if redo transport uses archiver processes (ARCn) or the log writer process (LGWR) to collect and send transaction data to standby locations. If neither ARCH nor LGWR is chosen, the default is ARCH. It controls whether redo transport uses immediate or delayed I/O to write redo data to disk.
AFFIRM means that all disk I/O for archived redo logs and standby redo logs is done immediately and must succeed before the log writer continues. NOAFFIRM means that all disk I/O for these logs can happen with delays; the log writer does not wait for the I/O to finish before moving on.
Q 16. What Is Staticconnectidentifier Property Used For?
The new database property, StaticConnectIdentifier, lets users set a static connect identifier for starting database instances with the DGMGRL client.
Q 17. What Is Failover/switchover (or) What Is The Difference Between Failover And Switchover?
Switchover occurs when both primary and standby databases are available and is planned ahead. Failover happens when the primary database is unavailable (e.g., during a disaster) and is unplanned.
Q 18. What Are The Background Processes Involved In Data Guard?
MRP, LSP
Q 19. What Are Different Types Of Modes In Data Guard And Which Is Default?
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.
Q 20. How Many Standby Databases We Can Create (in 10g/11g)?
Till Oracle 10g, 9 standby databases are supported. From Oracle 11g R2, we can create 30 standby databases..
Q 21. What Are The Parameters We’ve To Set In Primary/standby For Data Guard ?
DB_UNIQUE_NAME, LOG_ARCHIVE_CONFIG, LOG_ARCHIVE_MAX_PROCESSES, DB_CREATE_FILE_DEST, DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT, LOG_ARCHIVE_DEST_n, LOGARCHIVE_DEST_STATE_n, FAL_SERVER, FAL_CLIENT,
STANDBY_FILE_MANAGEMENT.
Q 22. What are Fal_server and Fal_client used for, and are they necessary to set?
FAL_SERVER identifies the FAL (fetch archive log) server for a standby database and must be properly configured on the standby system to connect to the FAL server. FAL_CLIENT designates the FAL client name used by the FAL service, which also needs to be configured correctly to link back to the FAL client (the standby database).
Q 23. What Is A Snapshot Standby Database?
11g Snapshot Standby Database: Oracle 11g introduces the Snapshot Standby database, which is an updateable standby created from a physical standby database. We can change a physical standby database into a snapshot standby, perform testing on this read-write copy of the primary or production database, and then revert it back to its earlier state as a physical standby database. While the snapshot standby is open in read-write mode, redo data from the primary database is received but not applied. When we convert it back to a physical standby, it is resynchronized by applying the accumulated redo data that was previously shipped but not applied. By using a snapshot standby, we can conduct real-time application testing with near real-time production data. This approach allows us to meet testing needs without the effort, time, resources, and disk space required for production clones.
Q 24. Snapshot Standby Database (updatable Snapshot For Testing)?
A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database.
Like a physical or logical standby database, a snapshot standby database receives and archives redo data from a primary database. Unlike a physical or logical standby database, a snapshot standby database does not apply the redo data that it receives. The redo data received by a snapshot standby database is not applied until the snapshot standby is converted back into a physical standby database, after first discarding any local updates made to the snapshot standby database.
Q 25. Data Guard Architecture?
Data Guard Configurations: A Data Guard configuration consists of one production database and one or more standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided they can communicate with each other. Dataguard Architecture The Oracle 9i Data Guard architecture incorporates the following items:
o Primary Database – A production database that is used to create standby databases. The archive logs from the primary database are transfered and applied to standby databases. Each standby can only be associated with a single primary database, but a single primary database can be associated with multiple standby databases.
o Standby Database – A replica of the primary database.
o Log Transport Services – Control the automatic transfer of archive redo log files from the primary database to one or more standby destinations.
o Network Configuration – The primary database is connected to one or more standby databases using Oracle Net.
o Log Apply Services – Apply the archived redo logs to the standby database. The Managed Recovery Process (MRP) actually does the work of maintaining and applying the archived redo logs.
o Role Management Services – Control the changing of database roles from primary to standby. The services include switchover, switchback and failover.
o Data Guard Broker – Controls the creation and monitoring of Data Guard. It comes with a GUI and command line interface. Primary Database: A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role. This is the database that is accessed by most of your applications. Standby Database:
A standby database is a transactionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to nine standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database. The types of standby databases are as follows: Physical standby database: Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, through Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database. Logical standby database: Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database through SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executes the SQL statements on the standby database.
Q 26. What Are The Services Required On The Primary And Standby Database ?
The services required on the primary database are:
o Log Writer Process (LGWR) – Collects redo information and updates the online redo logs. It can also create local archived redo logs and transmit online redo to standby databases.
o Archiver Process (ARCn) – One or more archiver processes make copies of online redo logs either locally or remotely for standby databases.
o Fetch Archive Log (FAL) Server – Services requests for archive redo logs from FAL clients running on multiple standby databases. Multiple FAL servers can be run on a primary database, one for each FAL request. .
o The services required on the standby database are:
o Fetch Archive Log (FAL) Client – Pulls archived redo log files from the primary site. Initiates transfer of archived redo logs when it detects a gap sequence.
o Remote File Server (RFS) – Receives archived and/or standby redo logs from the primary database.
o Archiver (ARCn) Processes – Archives the standby redo logs applied by the managed recovery process (MRP).
o Managed Recovery Process (MRP) – Applies archive redo log information to the standby database
Q 27. What Is Rts (redo Transport Services) In Data Guard?
It controls the automated transfer of redo data from the production database to one or more archival destinations. The redo transport services perform the following tasks:
o Transmit redo data from the primary system to the standby systems in the configuration.
o Manage the process of resolving any gaps in the archived redo log files due to a network failure.
o 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.
Q 28. What Are The Protection Modes In Dataguard?
Data Guard Protection Modes This section describes the Data Guard protection modes. In these descriptions, a synchronized standby database is meant to be one that meets the minimum requirements of the configured data protection mode and that does not have a redo gap. Redo gaps are discussed in Section 6.3.3. Maximum Availability This protectionmode 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 synchronized standby database. If the primary database cannot write its redo stream to at least one synchronized standby database, it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database. This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database. Maximum Performance This protectionmode 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. Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction commitment, so
primary database performance is unaffected by delays in writing redo data to the standby database(s). This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance. This is the default protection mode. Maximum Protection This protection mode ensures that zero data loss occurs if a 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 synchronized standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions, if it cannot write its redo stream to at least one synchronized standby database. Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.
Q 29. How To Delay The Application Of Logs To A Physical Standby?
A standby database applies redo logs from the primary database automatically. However, sometimes we need a delay between when a redo log is archived at the primary site and when it is applied at the standby site. To set this up, modify the LOG_ARCHIVE_DEST_n initialization parameter on the primary database. For example, to create a 60-minute delay, use: ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=stdby_srvc DELAY=60′; The DELAY attribute is in minutes. The redo logs are still copied to the standby site, but they are not applied until the specified delay is over.
Q 30. Steps To Create Physical Standby Database?
o Take a full hot backup of Primary database
o Create standby control file
o Transfer full backup, init.ora, standby control file to standby node.
o Modify init.ora file on standby node.
o Restore database
o Recover Standby database(Alternatively, RMAN DUPLICATE DATABASE FOR STANDBY DO RECOVERY can be also used)
o Setup FAL_CLIENT and FAL_SERVER parameters on both sides
o Put Standby database in Managed Recover mode
Q 31. What Are The Data Guard Parameters In Oracle?
Set Primary Database Initialization Parameters — On the primary database, set parameters that manage redo transport services while it acts as the primary. Additional parameters are needed for receiving redo data and log apply services when switching to standby. Example parameters include: DB_NAME=chicago, DB_UNIQUE_NAME=chicago, CONTROL_FILES=’/arch1/chicago/control1.ctl’, ‘/arch2/chicago/control2.ctl’, and LOG_ARCHIVE_DEST_1= ‘LOCATION=/arch1/chicago/’. Transitioning to standby involves creating a text initialization parameter file (PFILE) based on the primary’s server parameter file (SPFILE), for example: CREATE PFILE=’/tmp/initboston.ora’ FROM SPFILE; Modifications for the standby database include changing DB_NAME to boston, updating LOG_ARCHIVE_FORMAT to log%t_%s_%r.arc, and other necessary settings like FAL_SERVER and FAL_CLIENT to manage log file conversions and destinations.
Q 32. What Are The Types Of Oracle Data Guard?
Oracle Data Guard has two types based on how they are created and the methods used for Redo Apply:
o Physical standby (Redo Apply technology)
o Logical standby (SQL Apply technology)
Q 33. What Are The Advantages In Using Oracle Data Guard?
Here are the benefits of using the Oracle Data Guard feature:
o High Availability.
o Data Protection.
o Offloading backups to a standby database.
o Automatic gap detection and resolution in the standby database.
o Automatic role transition with Data Guard Broker.
Q 34. What Are The Different Services Available In Oracle Data Guard?
The following Services are available in Oracle Data Guard:
– Redo Transport Services.
– Log Apply Services.
– Role Transitions.
Q 35. What Are The Different Protection Modes Available In Oracle Data Guard?
Data Guard in Oracle database offers three protection modes to choose from based on your application needs:
o Maximum Protection
o Maximum Availability
o Maximum Performance.
Q 36. How To Check What Protection Mode Of Primary Database In Your Oracle Data Guard?
To check the protection mode of the primary database in your Oracle Data Guard setup, use the following query:
SELECT PROTECTION_MODE FROM V$DATABASE;
For example: SQL> SELECT PROTECTION_MODE FROM V$DATABASE; The result will show PROTECTION_MODE as MAXIMUM PERFORMANCE.
Q 37. How To Change Protection Mode In Oracle Data Guard Setup?
To change the protection mode in your primary database, use the following query after setting the LOG_ARCHIVE_DEST_n parameter for the standby database: ALTER DATABASE SET STANDBY DATABASE TO MAXIMUM [PROTECTION|PERFORMANCE|AVAILABILITY]; For example: ALTER DATABASE SET STANDBY DATABASE TO MAXIMUM PROTECTION;
Q 38. What Are The Advantages Of Using Physical Standby Database In Oracle Data Guard?
Advantages of using a physical standby database in Oracle Data Guard include:
o High Availability.
o Load balancing for backup and reporting.
o Data Protection.
o Disaster Recovery.
Q 39. What Is Physical Standby Database In Oracle Data Guard?
Oracle Standby databases are classified as either physical or logical standby databases, depending on how they are created and how redo logs are applied. A physical standby database is an exact block-by-block copy of the primary database. In this setup, transactions from the primary database are synchronized with the standby database using the Redo Apply method, which continuously applies redo data sent from the primary. Physical standby databases can take on backup and reporting duties, relieving the primary database. They can be opened for read-only transactions, but redo apply will pause during this time. However, starting from 11g, with the Active Data Guard option (available for an extra cost), you can open the physical standby database for read-only access while still applying redo logs from the primary database.
Q 40. 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.
Q 41. What Are The Advantages Of Logical Standby Database In Oracle Data Guard?
o Better usage of resource
o Data Protection
o High Availability
o Disaster Recovery
Q 42. 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.
Q 43. 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 Physical Standby These are the steps to follow:
o Enable forced logging
o Create a password file
o Configure a standby redo log
o Enable archiving
o Set up the primary database initialization parameters
o Configure the listener and tnsnames to support the database on both nodes
o col name format a20
o col thread# format 999
o col sequence# format 999
o col first_change# format 999999
o col next_change# format 999999 SELECT thread#, sequence# AS “SEQ#”, name, first_change# AS “FIRSTSCN”,
next_change# AS “NEXTSCN”,archived, deleted,completion_time AS “TIME” FROM v$archived_log V$ log_history
Q 44. Tell Me About Parameter Which Is Used For Standby Database?
o Log_Archive_Dest_n
o Log_Archive_Dest_State_n
o Log_Archive_Config
o Log_File_Name_Convert
o Standby_File_Managment
o DB_File_Name_Convert
o DB_Unique_Name
o Control_Files
o Fat_Client
o Fat_Server 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.