Difference Between Dataguard and Standby
There is common confusion about oracle standby database and dataguard. I found many people who consider dataguard and standby are same . Here are the difference between Dataguard and standby :
Dataguard is mechanism/tool to maintain standby database.
"Oracle Data Guard provides the management, monitoring, and automation software to create and maintain one or more standby databases..."
http://www.oracle.com/technology/deploy/availability/htdocs/DataGuardOverview.html
Dataguard : Dataguard is mechanism/tool to maintain standby database. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage.
The dataguard is set up between primary and standby instance .DataGuard can manage both physical and logical standby. DataGuard requires the enterprise edition, while we could write our own standby scripts using the standard edition. we have got support and testing issues-- if something goes wrong, we are on our own to debug our scripts. If we are using DataGuard, there is a variety of documentation available and a variety of options for support. Oracle has also invested quite a bit of time testing DataGuard with various failure scenarios-- it's likely that we wouldn't have that much time to test our own scripts. On a day-to-day basis, DataGuard provides integration with various monitoring utilities (i.e. Enterprise Manager) and provides a rather nice set of tables to view information about what's going on. DataGuard also provides functionality like the ability to automatically detect and resolve gaps in the archived log files that your scripts would not likely be written to handle. And, of course, our scripts could only manage a physical standby database.
Data Guard was originally a set of scripts, but now is the entire environment including a set of processes that control the extraction of redo (directly from log bugger, from redo logs or archive redo logs) from the primary, shipping to the standby, ensuring that the logs are applied. Data Guard processes also include the mechanics needed to make the standby database active automatically (failover) or manually (switchover) and also to re-sync and make the original database active again (switchback).
All that said, Data Guard is only available on Enterprise Edition. Standby capability is available on Standard Edition. And there are commercial products around that provide capability similar to Data Guard for Standard Edition. For more detail click here
Standby Database : 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, though Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database. A physical standby database can be used for business purposes other than disaster recovery on a limited basis.
Enjoy :-)
Oracle Data Guard Interview Questions
1. Can Oracle's Data Guard be used on Standard Edition, and if so how? How can you test that the standby database is in sync?
Oracle's Data Guard technology is a layer of software and automation built on top of the standby database facility. In Oracle Standard Edition it is possible to be a standby database, and update it *manually*. Roughly, put your production database in archivelog mode. Create a hotbackup of the database and move it to the standby machine. Then create a standby controlfile on the production machine, and ship that file, along with all the archived redolog files to the standby server. Once you have all these files assembled, place them in their proper locations, recover the standby database, and you're ready to roll. From this point on, you must manually ship, and manually apply those archived redologs to stay in sync with production.
To test your standby database, make a change to a table on the production server, and commit the change. Then manually switch a logfile so those changes are archived. Manually ship the newest archived redolog file, and manually apply it on the standby database. Then open your standby database in read-only mode, and select from your changed table to verify those changes are available. Once you're done, shutdown your standby and startup again in standby mode.
2. What is the difference between Active Dataguard, and the Logical Standby implementation of 10g dataguard?
Active dataguard is mostly about the physical standby.
Use physical standby for testing without compromising protection of the production system. You can open the physical standby read/write - do some destructive things in it (drop tables, change data, whatever - run a test - perhaps with real application testing). While this is happening, redo is still streaming from production, if production fails - you are covered. Use physical standby for reporting while in managed recovery mode. Since physical standby supports all of the datatypes - and logical standby does not (11g added broader support, but not 100%) - there are times when logical standby isn’t sufficient. It also permits fast incremental backups when offloading backups to a physical standby database.
3. 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.
4. What are the uses of Oracle Data Guard?
a) Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data.
b) Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions.
c) With Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.
5. 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.
6. 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.
7. 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.
8. 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.
9. 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.
10. 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;
11. 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 dorecover from active database;
12. 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.
13. 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.
14. 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.
15. 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.
16. 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.
17. 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;
18. 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';
19. 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.
20. 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.
21. 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.
The Oracle Data Guard broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations. The following describes some of the operations the broker automates and simplifies :
I.) Adding additional new or existing (physical, snapshot, logical, RAC or non-RAC) standby databases to an existing Data Guard configuration, for a total of one primary database, and from 1 to 9 standby databases in the same configuration.
III.) Managing the protection mode for the broker configuration.
Oracle Data Guard Broker Diagram : The below diagram will help us to understand Data Guard Broker.
Benefits of Data Guard Broker :
Relationship of Objects Managed by the Data Guard Broker :
Primary Databse = Noida
Standby Database = Delhi
Step 1 : Check the Data Guard Broker process
SQL> sho parameter dg_broker
NAME TYPE VALUE
----------------- ---------- ----------
dg_broker_start boolean FALSE
Step 2 : Start the Data Guard Broker Process
SQL>alter system set dg_broker_start=true scope=both;
System altered.
Step 3 : Check DG_BROKER on standby database and start it
SQL> sho parameter dg_broker
NAME TYPE VALUE
----------------- ---------- ----------
dg_broker_start boolean FALSE
SQL>alter system set dg_broker_start=true scope=both ;
System altered.
Step 4 : Edit the listener.ora file
SQL> show parameter db_domain
NAME TYPE VALUE
-------------- ----------- --------------
db_domain string
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = noida_DGMGRL)
(ORACLE_HOME = C:\app\neerajs\product\11.2.0\dbhome_1)
(SID_NAME = noida)
)
)
Similarly, edit the listener.ora file on standby database .
Step 5 : Now create the Data Guard Configuration
C:\>dgmgrl
DGMGRL for 32-bit Windows: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/xxxx@noida
Connected.
DGMGRL> create configuration 'dgnoida'
> as primary database is 'noida'
> connect identifier is noida ;
Configuration "dgnoida" created with primary database "noida" .
Once the configuration is created then check the status of configuration .
DGMGRL> show configuration
Configuration - dgnoida
Protection Mode : MaxPerformance
Databases : noida - Primary database
Fast-Start Failover : DISABLED
Configuration Status : DISABLED
Step 6 : Add standby database to the data broker configuration
DGMGRL> add database 'delhi' as
> connect identifier is delhi
> maintained as physical;
Database "delhi" added
DGMGRL> show configuration
Configuration - dgnoida
Protection Mode : MaxPerformance
Databases : noida - Primary database
: delhi - Physical standby database
Fast-Start Failover : DISABLED
Configuration Status : DISABLED
Step 7 : Enable the configuration
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration - dgnoida
Protection Mode : MaxPerformance
Databases : noida - Primary database
: delhi - Physical standby database
Fast-Start Failover : DISABLED
Configuration Status : SUCCESS
Step 8 : View the Primary and Standby database properties
DGMGRL> show database verbose noida
Database - noida
Role: PRIMARY
Intended State : TRANSPORT-ON
Instance(s) : noida
Properties:
DGConnectIdentifier = 'noida'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'TECH-199'
SidName = 'noida'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TECH-199)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=noida_DGMGRL)(INSTANCE_NAME=noida)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'D:\archive\'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ARC%S_%R.%T'
TopWaitEvents = '(monitor)'
Database Status : SUCCESS
DGMGRL> show database verbose delhi
Database - delhi
Role: PHYSICAL STANDBY
Intended State : APPLY-ON
Transport Lag : 0 seconds
Apply Lag : 0 seconds
Real Time Query : ON
Instance(s) : delhi
Properties:
DGConnectIdentifier = 'delhi'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'C:\app\neerajs\oradata\noida\, D:\app\stand\oradata\, E:\oracle\, D:\app\stand\oradata\'
LogFileNameConvert = 'C:\app\neerajs\oradata\noida\, D:\app\stand\oradata\'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'TECH-284'
SidName = 'delhi'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TECH-284)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=delhi_DGMGRL)(INSTANCE_NAME=delhi)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'D:\app\stand\archive\'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ARC%S_%R.%T'
TopWaitEvents = '(monitor)'
Database Status : SUCCESS
DGMGRL>
Oracle Data Guard Interview Questions
1. Can Oracle's Data Guard be used on Standard Edition, and if so how? How can you test that the standby database is in sync?
Oracle's Data Guard technology is a layer of software and automation built on top of the standby database facility. In Oracle Standard Edition it is possible to be a standby database, and update it *manually*. Roughly, put your production database in archivelog mode. Create a hotbackup of the database and move it to the standby machine. Then create a standby controlfile on the production machine, and ship that file, along with all the archived redolog files to the standby server. Once you have all these files assembled, place them in their proper locations, recover the standby database, and you're ready to roll. From this point on, you must manually ship, and manually apply those archived redologs to stay in sync with production.
To test your standby database, make a change to a table on the production server, and commit the change. Then manually switch a logfile so those changes are archived. Manually ship the newest archived redolog file, and manually apply it on the standby database. Then open your standby database in read-only mode, and select from your changed table to verify those changes are available. Once you're done, shutdown your standby and startup again in standby mode.
2. What is the difference between Active Dataguard, and the Logical Standby implementation of 10g dataguard?
Active dataguard is mostly about the physical standby.
Use physical standby for testing without compromising protection of the production system. You can open the physical standby read/write - do some destructive things in it (drop tables, change data, whatever - run a test - perhaps with real application testing). While this is happening, redo is still streaming from production, if production fails - you are covered. Use physical standby for reporting while in managed recovery mode. Since physical standby supports all of the datatypes - and logical standby does not (11g added broader support, but not 100%) - there are times when logical standby isn’t sufficient. It also permits fast incremental backups when offloading backups to a physical standby database.
3. 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.
4. What are the uses of Oracle Data Guard?
a) Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data.
b) Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions.
c) With Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.
5. 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.
6. 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.
7. 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.
8. 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.
9. 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.
10. 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;
11. 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 dorecover from active database;
12. 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.
13. 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.
14. 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.
15. 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.
16. 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.
17. 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;
18. 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';
19. 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.
20. 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.
21. 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.
DataGuard Broker And its Benefits
I.) Adding additional new or existing (physical, snapshot, logical, RAC or non-RAC) standby databases to an existing Data Guard configuration, for a total of one primary database, and from 1 to 9 standby databases in the same configuration.
II.) Managing an entire Data Guard configuration, including all databases, redo transport services, and log apply services, through a client connection to any database in the configuration.
III.) Managing the protection mode for the broker configuration.
IV.) Invoking switchover or failover with a single command to initiate and control complex role changes across all databases in the configuration.
V.) Configuring failover to occur automatically upon loss of the primary database, increasing availability without manual intervention.
VI.) Monitoring the status of the entire configuration, capturing diagnostic information, reporting statistics such as the redo apply rate and the redo generation rate, and detecting problems quickly with centralized monitoring, testing, and performance tools.
Oracle Data Guard Broker Diagram : The below diagram will help us to understand Data Guard Broker.
We can perform all management operations locally or remotely through the broker's easy-to-use interfaces: the Data Guard management pages in Oracle Enterprise Manager, which is the broker's graphical user interface (GUI), and the Data Guard command-line interface called DGMGRL.
Benefits of Data Guard Broker :
The broker's interfaces improve usability and centralize management and monitoring of the Data Guard configuration.The following benefits are :
1.) Disaster protection: By automating many of the manual tasks required to configure and monitor a Data Guard configuration, the broker enhances the high availability, data protection, and disaster protection capabilities that are inherent in Oracle Data Guard. Access is possible through a client to any system in the Data Guard configuration, eliminating any single point of failure. If the primary database fails, the broker automates the process for any one of the standby databases to replace the primary database and take over production processing. The database availability that Data Guard provides makes it easier to protect our data.
2.) Higher availability and scalability : While Oracle Data Guard broker enhances disaster protection by maintaining transitionally consistent copies of the primary database, Data Guard, configured with Oracle high availability solutions such as Oracle Real Application Clusters (RAC) databases.
3.) Automated creation of a Data Guard configuration: The broker helps you to logically define and create a Data Guard configuration consisting of a primary database and (physical or logical, snapshot, RAC or non-RAC) standby databases. The broker automatically communicates between the databases in a Data Guard configuration using Oracle Net Services. The databases can be local or remote, connected by a LAN or geographically dispersed over a WAN.
4.) Easy configuration of additional standby databases : After we create a Data Guard configuration consisting of a primary and a standby database, we can add up to eight new or existing, physical, snapshot, or logical standby databases to each Data Guard configuration. Oracle Enterprise Manager provides an Add Standby Database wizard to guide us through the process of adding more databases.
5.) Simplified, centralized, and extended management: We can issue commands to manage many aspects of the broker configuration. These include:
I.> Simplify the management of all components of the configuration, including the primary and standby databases, redo transport services, and log apply services.
II.> Coordinate database state transitions and update database properties dynamically with the broker recording the changes in a broker configuration file that includes profiles of all the databases in the configuration. The broker propagates the changes to all databases in the configuration and their server parameter files.
6.) Simplified switchover and failover operations : The broker simplifies switchovers and failovers by allowing us to invoke them using a single key click in Oracle Enterprise Manager or a single command at the DGMGRL command-line interface. Fast-start failover can be configured to occur with no data loss or with a configurable amount of data loss.
7.) Built-in monitoring and alert and control mechanisms: The broker provides built-in validation that monitors the health of all of the databases in the configuration. From any system in the configuration connected to any database, we can capture diagnostic information and detect obvious and subtle problems quickly with centralized monitoring, testing, and performance tools.
8.) Transparent to application : Use of the broker is possible for any database because the broker works transparently with applications; no application code changes are required to accommodate a configuration that you manage with the broker.
Relationship of Objects Managed by the Data Guard Broker :
Step-By-Step Configuration Of Data Guard Broker in Oracle 11g
As i have already discuss about the Data Guard Broker and its benefits in my earlier post . Here we will configure the data Guard Broker . Here are the steps :
Standby Database = Delhi
Step 1 : Check the Data Guard Broker process
SQL> sho parameter dg_broker
NAME TYPE VALUE
----------------- ---------- ----------
dg_broker_start boolean FALSE
Step 2 : Start the Data Guard Broker Process
SQL>alter system set dg_broker_start=true scope=both;
System altered.
Step 3 : Check DG_BROKER on standby database and start it
SQL> sho parameter dg_broker
NAME TYPE VALUE
----------------- ---------- ----------
dg_broker_start boolean FALSE
SQL>alter system set dg_broker_start=true scope=both ;
System altered.
Step 4 : Edit the listener.ora file
Edit the listener.ora file which includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME in both primary and standby database . To set the value, lets check the db_domain value .
SQL> show parameter db_domain
NAME TYPE VALUE
-------------- ----------- --------------
db_domain string
Since the value of db_domain is null so the the value of GLOBAL_DBNAME = NOIDA_DGMGRL for primary database and for standby GLOBAL_DBNAME = DELHI_DGMGRL. The primary listener.ora file is as
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = noida_DGMGRL)
(ORACLE_HOME = C:\app\neerajs\product\11.2.0\dbhome_1)
(SID_NAME = noida)
)
)
Similarly, edit the listener.ora file on standby database .
Step 5 : Now create the Data Guard Configuration
C:\>dgmgrl
DGMGRL for 32-bit Windows: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/xxxx@noida
Connected.
DGMGRL> create configuration 'dgnoida'
> as primary database is 'noida'
> connect identifier is noida ;
Configuration "dgnoida" created with primary database "noida" .
Once the configuration is created then check the status of configuration .
DGMGRL> show configuration
Configuration - dgnoida
Protection Mode : MaxPerformance
Databases : noida - Primary database
Fast-Start Failover : DISABLED
Configuration Status : DISABLED
Step 6 : Add standby database to the data broker configuration
DGMGRL> add database 'delhi' as
> connect identifier is delhi
> maintained as physical;
Database "delhi" added
DGMGRL> show configuration
Configuration - dgnoida
Protection Mode : MaxPerformance
Databases : noida - Primary database
: delhi - Physical standby database
Fast-Start Failover : DISABLED
Configuration Status : DISABLED
Step 7 : Enable the configuration
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration - dgnoida
Protection Mode : MaxPerformance
Databases : noida - Primary database
: delhi - Physical standby database
Fast-Start Failover : DISABLED
Configuration Status : SUCCESS
Step 8 : View the Primary and Standby database properties
DGMGRL> show database verbose noida
Database - noida
Role: PRIMARY
Intended State : TRANSPORT-ON
Instance(s) : noida
Properties:
DGConnectIdentifier = 'noida'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'TECH-199'
SidName = 'noida'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TECH-199)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=noida_DGMGRL)(INSTANCE_NAME=noida)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'D:\archive\'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ARC%S_%R.%T'
TopWaitEvents = '(monitor)'
Database Status : SUCCESS
DGMGRL> show database verbose delhi
Database - delhi
Role: PHYSICAL STANDBY
Intended State : APPLY-ON
Transport Lag : 0 seconds
Apply Lag : 0 seconds
Real Time Query : ON
Instance(s) : delhi
Properties:
DGConnectIdentifier = 'delhi'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'C:\app\neerajs\oradata\noida\, D:\app\stand\oradata\, E:\oracle\, D:\app\stand\oradata\'
LogFileNameConvert = 'C:\app\neerajs\oradata\noida\, D:\app\stand\oradata\'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'TECH-284'
SidName = 'delhi'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TECH-284)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=delhi_DGMGRL)(INSTANCE_NAME=delhi)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'D:\app\stand\archive\'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ARC%S_%R.%T'
TopWaitEvents = '(monitor)'
Database Status : SUCCESS
DGMGRL>
