After nearly a year’s hiatus from writing for Database Journal, I’m very glad to be back, and to finally be finishing the second part of my article on my favorite new features in Oracle 11g Data Guard, their primary product for protecting databases and an integral part of many Disaster Recovery plans.
Let me start by quickly summarizing the “bottom” of my top 10 list, from 10 through 6. You can click here to read the full article.
10. DB_LOST_WRITE_PROTECT parameter
9. New settings that improve performance
(net_timeout, max_connections, redo_compression)
8. Running jobs on a logical standby database
7. LOG_AUTO_DEL_RETENTION_TARGET parameter
6. Duplicating from an active database
Now, on to my top 5 favorite new features in Oracle 11g Data Guard.
5. CONFIGURE ARCHIVELOG DELETION POLICY Changes
This configuration setting for RMAN is not in and of itself new to Oracle 11g. However, with 11gR2, Oracle has added a new setting for the archive log deletion policy. This new setting is:
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;
The previous versions only allowed for APPLIED TO rather than SHIPPED TO. The primary difference and benefit here, is that we may configure standby databases to intentionally delay applying the redo data to the standby database.
With this new setting, RMAN could remove archives once they have been successfully shipped to the standby, whether or not they’ve actually been applied. As long as the standby logs are being archived, we know the data is available to the standby database.
The advantage is space management. There are databases out there where available space is a premium, so being able to remove the archives more quickly in a Data Guard scenario is beneficial.
4. Fast Start Failover Improvements
Fast Start Failover for Data Guard was initially introduced in 10gR2, as a way to work with Data Guard Broker to automatically initiate a failover from the primary to a designated standby database.
A separate process called Observer is installed on a separate host with full connectivity to both databases, and if Observer lost contact with the primary database for more than a designated threshold time, the failover would be initiated. In order to configure Fast Start Failover, the data guard protection level had to be Maximum Availability.
With 11gR1 several new features were added to the Fast Start Failover options. First, the protection level could be either Maximum Availability or Maximum Performance.
Second, there were several additional properties that could be configured:
FastStartFailoverLagLimit – sets an acceptable time lag for the standby to fall behind the primary for applied redo. This defaults to 30 seconds.
FastStartFailoverPmyShutdown – indicates if the primary database should be shut down if the primary database has lost contact with the standby and redo generation has been stalled. By the way, this is accomplished using a shutdown abort option. This defaults to FALSE.
FastStartFailoverAutoReinstate – specifies if the old primary database should be automatically reinstated back into the configuration as a standby database if a failover is initiated. This defaults to TRUE.
ObserverConnectIdentifier – used to tell the observer how to connect to and monitor the primary and standby databases. This defaults to the regular connect identifier created for the Data Guard configuration.
Third, we can configure additional failover conditions, so that an automatic failover can be initiated for reasons other than lost communications with observer. The new conditions include:
Corrupted Controlfile (defaults to Yes)
Corrupted Dictionary (defaults to Yes)
Inaccessible Logfile (defaults to No)
Stuck Archiver (defaults to No)
Datafile Offline (defaults to Yes)
Any Oracle Server Error
These can be configured using a command similar to in the DGMGRL:
ENABLE FAST_START FAILOVER CONDITION '1578' ENABLE FAST_START FAILOVER CONDITION "Stuck Archiver"
To disable one of these settings:
DISABLE FAST_START FAILOVER CONDITION '1578'
To see your current Fast Start Failover settings, you can enter the following command:
SHOW FAST_START FAILOVER
In addition to all of these new options, we also have the ability to programmatically initiate a failover by using a new package and procedure: DBMS_DG.INITIATE_FS_FAILOVER.
3. Role Based Services
Starting with 11gR2, with the advent of the new Grid Infrastructure software, we have the ability to set up services in our databases that can now automatically be tied to the specific role that a database is playing in our Data Guard configuration. Data Guard Broker must be configured and running to use this new feature.
This can be used for both RAC and single instance databases, just so long as Grid Infrastructure had been installed. The services would have to be set up using SRVCTL rather than DBMS_SERVICE.
The great advantage to this for administrators is to simplify the management of client connections to our databases. We can now have clients connect to primary databases, logical standby databases, physical standby databases and snapshot databases. The challenge is to ensure that a client always connects to the “right” database. By connecting via services, and linking the service to only run in a database playing a specific role, we can simplify connection headaches.
The basic syntax for adding a service would be:
SRVCTL ADD SERVICE -d <db unique name> -s <service name>
-l <PRIMARY | PHYSICAL STANDBY | LOGICAL STANDBY | SNAPSHOT STANDBY -y AUTOMATIC | MANUAL
The options for -l indicate which role the database must be in for the service to be running. -y indicates whether the service should be started automatically when the database instance starts in the specified role.
However, if the database is already running, you would have to manually start the service using:
SRVCTL START SERVICE -d <db unique name> -s <service name>
2. Snapshot Standby Database
Introduced in Oracle 11gR1, a snapshot standby database is a physical standby that is temporarily “disconnected” from your data guard configuration and able to be used as a fully updatable stand alone database.
Any kind of testing or changes can be done, including features like using Database Replay. It’s a great way to maximize potential use of resources.
While the database is in the snapshot role, redo information continues to be shipped to the standby database, and archived at the standby database. However, the redo will not be applied to the standby database until after it is converted back into a regular physical standby database.
When the physical database is first converted into a snapshot standby database, a guaranteed restore point is created, and Flashback Database is enabled (if it was not previously configured).
When the database is converted back, it is flashed back to the guaranteed restore point, put back into the full data guard configuration, and the redo is applied eventually catching the standby database back up to the primary. Once this is completed, the guaranteed restore point is removed automatically.
The DGMGRL command that converts the database is:
CONVERT DATABASE <standby> TO SNAPSHOT STANDBY CONVERT DATABASE <standby> TO PHYSICAL STANDBY
Now, as much as I love this feature- it does come with some warnings…
First, if this is your only standby database, be aware that worst case scenario, if you have to failover to it, the failover is going to take longer – because the standby has to be flashed back, and caught up, before the transition can complete.
For this reason, I always recommend considering a data guard configuration that consists of two physical standby databases. That way, one is always kept current, and the other can be used as a snapshot standby.
Second, there are three restrictions where you cannot convert to a snapshot.
a) If it is the only standby of a Maximum Protection configuration
b) If it is the target of a current switchover operation
c) If it is the designated failover database for Fast Start Failover
1. Active Data Guard
And, now for my absolute favorite new feature of Oracle 11g Data Guard… Active Data Guard (introduced in 11gR1). In the data guard configuration, this displays as having Real Time Query enabled.
We now have the ability to go ahead and utilize a physical standby database for query purposes, and redo continues to be applied while queries are being run against the database.
In 11gR2, two additional features were added as part of Active Data Guard – doing incremental backups on the physical standby with block change tracking enabled and automatic corrupt block detection and recovery.
Okay, now let me get the bad news out of the way, this is a separately licensed feature of Oracle…not a freebie.
To enable Real Time Query, leave your database in recover mode, and simply alter it open for Read Only access…ALTER DATABASE OPEN READ ONLY. This has to be done from a SQL prompt as there is no command in DGMGRL to enable Real Time Query. To disable Real Time Query, shutdown your database and restart it to mounted stage.
One potential area of concern with Real Time Query is that potentially your standby database can fall behind the primary with an apply lag. If the queries you are offloading are extremely time sensitive, even a lag of a few seconds can be problematic.
Oracle has a session level parameter STANDBY_MAX_DATA_DELAY which sets a session specific tolerance level for how far behind the primary database the standby can be and still allow queries to run unaffected. If this time limit is exceeded, the user will get an oracle error rather than the query running and showing what might be “old” information. If this parameter is set to 0 (zero), then no lag can be tolerated in that session for queries. To use a setting of 0 the data guard configuration must be either maximum protection or maximum availability and real time apply must be enabled.
There is also an alter session command that can ensure that the current session gets fully synchronized with the primary before queries are executed. ALTER SESSION SYNC WITH PRIMARY will return control to the session in the standby once the synchronization is complete.
New in 11gR2 Active Data Guard is the ability to utilize the physical standby databases for offloading RMAN incremental backups. We had the ability to do full backups on a physical standby database in a Data Guard configuration, now we can do incremental backups and we can turn on block change tracking to reduce the time required for them.
In order to offload RMAN backups to a physical standby database, you must be using and connected to an RMAN Recovery Catalog and you must specifically connect to the standby database using the CONNECT TARGET command.
Also new in 11gR2 Active Data Guard is a feature called Automatic Block Repair. If a corrupt block (physical corruption only) is encountered in the primary database, it is automatically restored from the standby database and recovered. Additionally, if a corrupt block is encountered in the physical standby database, it can be automatically restored and recovered from the primary database. As long as Real Time Apply is active, this feature is enabled.
Regular RMAN block recover commands can use one of three sources now for finding the initial good copy of a block to restore, a standby database with Real Time Query enabled, flashback logs and then as the last choice, RMAN full backups are used.
So, there we have it everyone, my top 10 list for Oracle 11g Data Guard new features! An awesome tool, that continues to improve.
Until next month!
See all articles by Karen Reliford
