Emre Baransel - Oracle Blog

I wanted to share my notes on "Oracle Data GuardConcepts and Administration 11g Release 2" document.

11gR1 New Features

· Compression of redo traffic over the network in a Data Guard configuration : This feature improves redo transport performance when resolving redo gaps by compressing redo before it is transmitted over the network.
· You can now find the DB_UNIQUE_NAME of the primary database from the standby database by querying the new PRIMARY_DB_UNIQUE_NAME column in the V$DATABASE view. Also, Oracle Data Guard release 11g ensures each database's DB_UNIQUE_NAME is different. After upgrading to 11g, any databases with the same DB_UNIQUE_NAME will not be able to communicate with each other.
· Heterogeneous Data Guard Configuration: This feature allows a mix of Linux and Windows primary and standby databases in the same Data Guard configuration.

Specific to Redo Apply (Physical Standby Database);
· Real-time query capability of physical standby
· Snapshot standby
· Lost-write detection using a physical standby : A "lost write" is a serious form of data corruption that can adversely impact a database. It occurs when an I/O subsystem acknowledges the completion of a block write in the database, while in fact the write did not occur in the persistent storage. This feature allows a physical standby database to detect lost writes to a primary or physical standby database.
· A number of enhancements in RMAN help to simplify backup and recovery operations across all primary and physical standby databases, when using a catalog. Also, you can use the RMAN DUPLICATE command to create a physical standby database over the network without a need for pre-existing database backups.

11gR2 New Features

· The new ALTER SYSTEM FLUSH REDO SQL statement can be used at failover time to flush unsent redo from a mounted primary database to a standby database, thereby allowing a zero data loss failover to be peformed even if the primary database is not running in a zero data loss data protection mode.
· The FAL_CLIENT database initialization parameter is no longer required.

Specific to Redo Apply (Physical Standby Database);
· A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode. A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database.



Some Keynotes on Prerequisites

· The COMPATIBLE database initialization parameter must be set to the same value on all databases in a Data Guard configuration, except when using a logical standby database, which can have a higher COMPATIBLE setting than the primary database.
· To protect against unlogged direct writes in the primary database that cannot be propagated to the standby database, turn on FORCE LOGGING at the primary database before performing datafile backups for standby creation. Keep the database in FORCE LOGGING mode as long as the standby database is required.

Important Notes on Creating a Physical Standby

· If the standby database will be hosted on a Windows system, use the ORADIM utility to create a Windows service. For example:

WINNT> oradim –NEW –SID boston –STARTMODE manual

· If the primary database has a database encryption wallet, copy it to the standby database system and configure the standby database to use this wallet.(The database encryption wallet must be copied from the primary database system to each standby database system whenever the master encryption key is updated.)

· Post-Creation :
o Upgrade the data protection mode
o Enable Flashback Database : Flashback Database removes the need to re-create the primary database after a failover.

Important Notes on Creating a Logical Standby

· Note that a logical standby database uses standby redo logs (SRLs) for redo received from the primary database, and also writes to online redo logs (ORLs) as it applies changes to the standby database. Thus, logical standby databases often require additional ARCn processes to simultaneously archive SRLs and ORLs. Additionally, because archiving of ORLs takes precedence over archiving of SRLs, a greater number of SRLs may be needed on a logical standby during periods of very high workload.
· Determine Support for Data Types and Storage Attributes for Tables
· Ensure Table Rows in the Primary Database Can Be Uniquely Identified (Look doc. for details)
· Step-by-Step Instructions for Creating a Logical Standby Database:
    o Create a Physical Standby Database
    o Stop Redo Apply on the Physical Standby Database
    o Prepare the Primary Database to Support a Logical Standby Database
          § LOG_ARCHIVE_DEST_3= 'LOCATION=/arch2/chicago/VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=chicago'
          § EXECUTE DBMS_LOGSTDBY.BUILD;
    o Transition to a Logical Standby Database
          § ALTER DATABASE RECOVER TO LOGICAL STANDBY db_name;
          § In releases prior to Oracle Database 11g, you needed to create a new password file before you
             opened the logical standby database. This is no longer needed.
          § SHUTDOWN; / STARTUP MOUNT;
          § LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/boston/ VALID_FOR=
             (ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston'
             LOG_ARCHIVE_DEST_2= 'SERVICE=chicago ASYNC VALID_FOR=           
             (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago'
             LOG_ARCHIVE_DEST_3= 'LOCATION=/arch2/boston/ VALID_FOR=
             (STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=boston'
    o Open the Logical Standby Database
          § ALTER DATABASE OPEN RESETLOGS;
          § ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Protection Modes:
· Execute the following SQL statement on the primary database in order to set the data protection mode.

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};

· Perform the following query on the primary db to confirm that it is operating in the new protection mode:

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

Redo Transport Services:
o Redo Transport Authentication Using SSL
o Redo Transport Authentication Using a Password File: By default, the password of the SYS user is used to authenticate redo transport sessions when a password file is used. The REDO_TRANSPORT_USER database initialization parameter can be used to select a different user password for redo transport authentication by setting this parameter to the name of any user who has been granted the SYSOPER privilege. For administrative ease, Oracle recommends that the REDO_TRANSPORT_USER parameter be set to the same value on the redo source database and at each redo transport destination.

· LOG_ARCHIVE_DEST_STATE_n Initialization Parameter Values: ENABLE, DEFER, ALTERNATE
· LOG_ARCHIVE_DEST_n parameter attributes :
    o AFFIRM and NOAFFIRM
    o ALTERNATE (not supported for LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31)
    o COMPRESSION (licensed)
    o DB_UNIQUE_NAME
    o DELAY
    o LOCATION and SERVICE (LOCATION is not supported for LOG_ARCHIVE_DEST_11 thr 31)
    o MANDATORY (not supported for LOG_ARCHIVE_DEST_11 through 31)
    o MAX_CONNECTIONS
    o MAX_FAILURE
    o NET_TIMEOUT
    o NOREGISTER
    o REOPEN
    o SYNC and ASYNC (SYNC is not supported for LOG_ARCHIVE_DEST_11 through 31)
    o SERVICE (mandatory attribute for a redo transport destination)
    o TEMPLATE
    o VALID_FOR

· Redo received by a standby database is written directly to an archived redo log file if a standby redo log group is not available or if the redo was sent to resolve a redo gap. When this occurs, redo is written to the location specified by the LOCATION attribute of one LOG_ARCHIVE_DEST_n parameter that is valid for archiving redo received from another database.

· If the redo source database is an Oracle Real Applications Cluster (Oracle RAC) or Oracle One Node database, query the V$LOG view at the redo source database to determine how many redo threads exist and specify the corresponding thread numbers when adding redo log groups to the standby redo log.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;

Apply Services
· Canceling a Time Delay

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NODELAY;

· If you define a delay for a destination that has real-time apply enabled, the delay is ignored.
· As an alternative to setting an apply delay, you can use Flashback Database to recover from the application of corrupted or erroneous data to the standby database. Flashback Database can quickly and easily flash back a standby database to an arbitrary point in time.

· Applying Redo Data to Logical Standby Databases

o Starting SQL Apply:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY [IMMEDIATTE];

o Stopping SQL Apply:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Role Transitions

Switchover Notes
· Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database.
· Before performing a switchover from an Oracle RAC primary database to a physical standby database, shut down all but one primary database instance.
· Before performing a switchover to a physical standby database that is in real-time query mode, consider bringing all instances of that standby database to the mounted but not open state for the fastest possible role transition.

SQL> SELECT NAME, VALUE, TIME_COMPUTED FROM V$DATAGUARD_STATS;

NAME VALUE
------------------------ ---------------- --
apply finish time +00 03:09:38.7
apply lag +00 02:09:53
estimated startup time 48
standby has been open N
transport lag +00 00:11:12

Failover Notes
· If a standby database currently running in maximum protection mode will be involved in the failover, first place it in maximum performance mode.
· If possible, copy the most recently archived redo log file for each primary database redo thread to the standby database if it does not exist there, and register it.
· Flush any unsent redo from the primary database to the target standby database. If the primary database can be mounted, it may be possible to flush any unsent archived and current redo from the primary database to the standby database

SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;

· Stop Redo Apply

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

· Finish applying all received redo data

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

· SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
· SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
· SQL> ALTER DATABASE OPEN;

· Using Flashback Database After a Switchover:

If the switchover involved a logical standby database, flashing back changes the role of the standby database to what it was at the target SCN or time to which you flashed back the database.

· Using Flashback Database After a Failover
You can use Flashback Database to convert the failed primary database to a point in time before the failover occurred and then convert it into a standby database. (Converting a Failed Primary Into a Standby Database Using Flashback Database)

Real-time query
· A physical standby database instance cannot be opened if Redo Apply is active on a mounted instance of that database. Use the following SQL statements to stop Redo Apply, open a standby instance read-only, and restart Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
SQL> SELECT open_mode FROM V$DATABASE;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Apply Lag

· To obtain the apply lag, query the V$DATAGUARD_STATS view. For example:

SQL> SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like 'apply lag';

NAME VALUE DATUM_TIME TIME_COMPUTED
--------- ------------- ------------------- ------------------
apply lag +00 00:00:00 05/27/2009 08:54:16 05/27/2009 08:54:17

The apply lag metric is computed using data that is periodically received from the primary database. The DATUM_TIME column contains a timestamp of when this data was last received by the standby database. The TIME_COMPUTED column contains a timestamp taken when the apply lag metric was calculated. The difference between the values in these columns should be less than 30 seconds. If the difference is larger
than this, the apply lag metric may not be accurate.

STANDBY_MAX_DATA_DELAY

· If STANDBY_MAX_DATA_DELAY is set to 0, a query issued to a physical standby database is guaranteed to return the exact same result as if the query were issued on the primary database, unless the standby database is lagging behind the primary database, in which case an ORA-3172 error is returned.
· The following additional restrictions apply if STANDBY_MAX_DATA_DELAY is set to 0 or if the ALTER SESSION SYNC WITH PRIMARY SQL statement is used:
    * The standby database must receive redo data via the SYNC transport.
    * The redo transport status at the standby database must be SYNCHRONIZED and the primary database must be running in either maximum protection mode or maximum availability mode.
    * Real-time apply must be enabled.

Automatic Repair of Corrupt Data Blocks

· A physical standby database operating in real-time query mode can also be used to repair corrupt data blocks in a primary database. If a corrupt data block is discovered on a physical standby database, the server attempts to automatically repair the corruption by obtaining a copy of the block from the primary database. No additional configuration is needed. If automatic repair is not possible, an ORA-1578 error is returned.

· The RMAN RECOVER BLOCK command is used to manually repair a corrupted data block. This command searches several locations for an uncorrupted copy of the data block.

Tuning Queries on a Physical Standby Database

· Appendix D of the Active Data Guard 11g Best Practices white paper describes how to tune queries for optimal performance on a physical standby database.


Using RMAN

Interchangeability of Backups in a Data Guard Environment

· RMAN commands use the recovery catalog metadata to behave transparently across different physical databases in the Data Guard environment. For example, you can back up a tablespace on a physical standby database and restore and recover it on the primary database. Similarly, you can back up a tablespace on a primary database and restore and recover it on a physical standby database.
Note: Backups of logical standby databases are not usable at the primary database.

· Backups of standby control files and nonstandby control files are interchangeable. For example, you can restore a standby control file on a primary database and a primary control file on a physical standby database. This interchangeability means that you can offload control file backups to one database in a Data Guard environment.

Association of Backups in a Data Guard Environment

· The recovery catalog tracks the files in the Data Guard environment by associating every database file or backup file with a DB_UNIQUE_NAME. The database that creates a file is associated with the file. For example, if RMAN backs up the database with the unique name of standby1, then standby1 is associated with this backup. A backup remains associated with the database that created it unless you use the CHANGE ... RESET DB_UNIQUE_NAME to associate the backup with a different database.

Accessibility of Backups in a Data Guard Environment

· In a Data Guard environment, the recovery catalog considers disk backups as accessible only to the database with which it is associated, whereas tape backups created on one database are accessible to all databases.
Note: You can FTP a backup from a standby host to a primary host or vice versa, connect as TARGET to the database on this host, and then CATALOG the backup. After a file is cataloged by the target database,
the file is associated with the target database.

RMAN Configurations

· For primary ;
Specify when archived logs can be deleted with the CONFIGURE ARCHIVELOG DELETION POLICY command. For example, if you want to delete logs after ensuring that they shipped to all destinations, use the following configuration:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;

If you want to delete logs after ensuring that they were applied on all standby destinations, use the following configuration: 

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

For standby ;
Enable automatic deletion of archived logs once they are applied at the standby database:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

· Configure the connect string for the primary database and all standby databases, so that RMAN can connect remotely and perform resynchronization when the RESYNC CATALOG FROM DB_UNIQUE_NAME command is used.

RMAN> CONFIGURE DB_UNIQUE_NAME BOSTON CONNECT IDENTIFIER 'boston_conn_str';

· Commands for Daily Tape Backups Using Disk as Cache

The script to perform daily backups looks as follows:

RESYNC CATALOG FROM DB_UNIQUE_NAME ALL; -- Resynchronizes the information from all other database sites (primary and other standby databases)
RECOVER COPY OF DATABASE WITH TAG 'OSS'; -- Rolls forward level 0 copy of the database by applying the level 1 incremental backup taken the day before. On the first day this command is run there will be no roll forward because there is no incremental level 1 yet. A level 0 incremental will be created by the BACKUP DEVICE TYPE DISK ... DATABASE command. Again on the second day there is no roll forward because there is only a level 0 incremental.
BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'OSS' DATABASE; -- Create a new level 1 incremental backup. On the first day this command is run, this will be a level 0 incremental. On the second and following days, this will be a level 1 incremental.
BACKUP DEVICE TYPE SBT ARCHIVELOG ALL; -- Backs up archived logs to tape according to the deletion policy in place.
BACKUP BACKUPSET ALL; -- Backs up any backup sets created as a result of incremental backup creation.
DELETE ARCHIVELOG ALL; -- Deletes archived logs according to the log deletion policy set by the CONFIGURE ARCHIVELOG DELETION POLICY command. If the archived logs are in a fast recovery area, then they are automatically deleted when more open disk space is required.

Registering and Unregistering Databases in a Data Guard Environment

· Only the primary database must be explicitly registered using the REGISTER DATABASE command. You do this after connecting RMAN to the recovery catalog and primary database as TARGET.
· A new standby is automatically registered in the recovery catalog when you connect to a standby database or when the CONFIGURE DB_UNIQUE_NAME command is used to configure the connect identifier.
· To unregister information about a specific standby database, you can use the UNREGISTER DB_UNIQUE_NAME command.

Reporting in a Data Guard Environment

· Use the RMAN LIST, REPORT, and SHOW commands with the FOR DB_UNIQUE_NAME clause to view information about a specific database.

RMAN> LIST DB_UNIQUE_NAME OF DATABASE;
RMAN> LIST ARCHIVELOG ALL FOR DB_UNIQUE_NAME BOSTON;
RMAN> REPORT SCHEMA FOR DB_UNIQUE_NAME BOSTON;
RMAN> SHOW ALL FOR DB_UNIQUE_NAME BOSTON;