rmougdg03

8/6/2019 rmougDG03

1/10

S TAND BY YOUR DATA GUARDDarl Kuhn, Sun Microsystems

IntroductionTodays business environment often requires that data be highly available. Oracle Data Guard is a built-in component of Oracle9 i that protects your database from disasters, corruption, and user errors. In addition, it also reduces planned downtime.

Previously known as the standby option, this feature has long been a dependable and efficent method for achieving highavailability and disaster recovery protection. The reason for its reliability and economic appeal is that standby is underpinned

by a simple and yet powerful architecture that:

1. Takes a backup of your primary database and moves it to a standby location2. Periodically copies the primary archived redo logs and applies them to the standby

With Oracle8 i, standby was improved with automated log copy and apply services and the ability to open a standby databasein read-only mode for reporting. Additionally, in release 8.1.7, standby database technology was introduced as Oracle DataGuard.

Starting with Oracle9i, Oracle Data Guard adds many new features such as:

Data Guard SQL Apply (version 9.2 only) Guaranteed no-data-loss Archive gap management Propagation of datafile operations User error protection Database switchover Data Guard Broker

Each of these improvements adds substantial functionality over what was available prior to Oracle9 i. These new features giveyou advanced levels of high availability and robust methods for managing the creation, maintenance, switchover, and failover operations of your databases.

Data Guard SQL ApplyBefore Oracle9 i release 2, the only type of standby database was a physical standby. One feature of a physical standby is thatit can be placed in either recovery mode or read-only mode. The recovery mode and read-only modes are mutually exclusive.

New with the Data Guard SQL Apply feature of Oracle9 i release 2, you can now implement a logical standby database. Alogical standby database is continuously available for reporting while simultaneously applying transactions from the primary.A logical standby database would be appropriate if you have business needs of a near real-time copy of your productiondatabase that is also 24x7 available for querying.

A logical standby database differs architecturally from a physical standby. A physical standby database is identical to the primary database down to the block level. This is because the Oracle uses redo information received from the primary toapply those redo changes to the data blocks on disk.

www.rmoug.org RMOUG Training Days 2003
http://www.odtug.com/http://www.odtug.com/

8/6/2019 rmougDG03

2/10

Stand By Your Data Guard Kuhn

In contrast with a physical standby, a logical standby uses LogMiner technology to transform the redo data received from the primary into SQL statements and then applies those SQL statements to the logical standby. Therefore, it is logically identicalto the primary, meaning it has the same objects, but it is not physically identical at the disk level.

Just like a physical standby, a logical standby database can take the role of the primary if there is some sort of disaster. You

can configure your standby environment to use both logical and physical standby databases. If you use your logical standbyfor querying, you can further enhance it by creating indexes and materialized views independent of the primary.

Note: The following datatypes are not supported in a logical standby database: long, long raw, nclob, bfile, rowid, andurowid. You can query DBA_LOGSTDBY_UNSUPPORTED in your primary database to determine if you haveunsupported data types.

Setting up a logical standby involves dozens of steps. The best places for setup directions are the version 2 Oracle DataGuard Concepts and Administration manual and Oracle MetaLink note 186150.1.

Guaranteed No-Data-LossPrior to Oracle9 i, the potential existed with a physical standby that you could lose transactions in the primary database redologs before they had a chance to be transported and applied to the standby.

With Oracle9 i, you get a wide variety of data protection levels when configuring archive destinations and transactioncompletion requirements. For example, the Maximum Protection mode guarantees that transactions are not committed on the

primary until the redo needed to recover those transactions has been written to disk on at least one standby database. Thisensures zero data loss for the Data Guard configuration.

To change your physical standby to the Maximum Protection mode that guarantees zero data loss, do the following:

Enable primary LGWR to write transactions synchronously Create standby redo logs on your physical standby databases Set standby log archive destination Alter primary database into standby database protected mode

The Maximum Protection mode has several key architectural implications. One noteworthy feature is that the log writer (LGWR) becomes the process for propagating transactions to the standby, not the archiver (ARC0) process. In MaximumProtection mode, LGWR must be enabled with a log transport setting of SYNC and the disk write setting of AFFIRM.

On your primary database you enable the feature as follows:

SQL> alter system set log_archive_dest_2='SERVICE=standby1 LGWR SYNC AFFIRM';

Once enabled, LGWR must be able to successfully write transactions synchronously to both the primary and the standby.Enabling Maximum Protection mode should be a well-planned activity. When enabled, if the standby is unavailable for anyreason, the primary will also become unavailable. Additionally, since LGWR is synchronously shipping redo information tothe standby, this could potentially impact your primary database performance.


8/6/2019 rmougDG03

3/10


Note: The impact on performance can be minimized by configuring a network with sufficient bandwidth for peak transactionload and with low round trip latency.

Another important architectural aspect of the Maximum Protection mode is that the standby must use standby redo logs . Newwith Oracle9 i, standby redo logs are special redo logs that are required when you enable higher levels of data protection.

When you use standby redo logs, you get some interesting side benefits: Standby redo logs can setup with multiple members, offering more reliability These files can exist on raw devices, offering possible performance benefits

The standby redo logs must be of the same size as the primary online redo logs, and must have at least two groups. In your standby environment, you create the logs as follows:

SQL> alter database mount standby database;

SQL> alter database add standby logfile ('/ora1/oradata/BRDSTN/sb_redo_01.log') size 20M;

SQL> alter database add standby logfile ('/ora1/oradata/BRDSTN/sb_redo_02.log') size 20M;

On the standby, you must set your log_archive_dest_1 parameter to point to the directory where the standby redo logs arearchived and then put the standby back in managed recovery mode:

SQL> alter system set log_archive_dest_1='LOCATION=/ora1/oradata/BRDSTN/sb_redo';

SQL> recover managed standby database disconnect;

The last step involved in deploying Maximum Protection mode protection is to alter your primary database into protectedmode:

SQL> startup mount;

SQL> alter database set standby database protected;

SQL> alter database open;

To give you that fuzzy and warm feeling that you've set things up correctly, on your primary query the v$archive_dest view:

SQL> select target, status, process, transmit_mode, affirm from v$archive_dest where target='STANDBY' andstatus='VALID';

TARGET STATUS PROCESS TRANSMIT_MOD AFF

------------- ---------- ------------- ------------------------ -----

STANDBY VALID LGWR SYNCHRONOUS YES

To verify the role and protection mode, on your primary select the new Oracle9 i database role and mode columns nowavailable in the v$database view:


8/6/2019 rmougDG03

4/10


SQL> select database_role, standby_mode from v$database;

DATABASE_ROLE STANDBY_MODE

------------------------ -----------------------PRIMARY PROTECTED

From this point on you are guaranteed that transactions will be committed simultaneously in both your primary and thestandby database.

Archive Gap ManagementIn a managed recovery configuration, if the primary log transport mechanism is not able to ship the redo data from the

primary database to the physical standby database, then you have a gap between what archive logs have been generated bythe primary and what have been applied to the physical standby. This situation will likely arise if there is a network problemor if the standby database is down for any reason.

Discovering and resolving archive log gaps is critical to enabling high availability. An untimely gap resolution mechanismcompromises the availability of data in the standby environment. For example, if you have an undetected gap, and thendisaster strikes, this will most likely result in a loss of data after you failover to your standby database.

In Oracle9 i, you get nice improvements in the method that archive gaps are discovered and resolved. Previously in Oracle8 i,you had the responsibility of monitoring for gaps, and once discovered, manually perform the following tasks on your standby:

Determine which archived redo logs are part of the gap Manually copy the archived redo gap logs to standby Take the standby database out of managed recovery mode Issue the RECOVER STANDBY DATABASE command Alter the standby database back into managed recovery mode

In Oracle9 i, Data Guard has two methods for automatically detecting and resolving archive gaps. The first method for gapresolution is via a periodic background communication between the primary database and all of its standby databases. DataGuard compares log files created on the primary with log files received on the standby databases to detect the gaps. Once agap is noticed, the missing archive log files are sent to the appropriate standbys. This method is automatic and does not needany special configuration.

Using two new Oracle9i initialization parameters on your standby database enables the second method for automatic archive

gap resolution:

SQL> alter system set FAL_SERVER = primary1;

SQL> alter system set FAL_CLIENT = standby1;


8/6/2019 rmougDG03

5/10


Once these initialization parameters are enabled on the standby database, the managed recovery process on the physicalstandby will automatically check and resolve gaps at the time archive redo is applied. The new Fetch Archive Log (FAL)

processes manage this automated gap detection and resolution.

The FAL client process runs in the physical standby environment and is responsible for requesting primary archived redo

logs when it detects a gap on the standby. The FAL server process runs on the primary database server and services requestsfrom the FAL client.

In the next example, standby1 is the Oracle Net name of the standby database and primary1 is the Oracle Net name of the primary database. The FAL processes use these parameters to determine the location of the physical standby and primarydatabases. In this scenario, the network was temporarily down, and during the down time the primary generated two archivedredo logs numbered 153 and 154. After the network came back up, Data Guard automatically determined there was a gap andtransmitted and applied the appropriate archived redo logs. The primary database alert.log file shows that a gap has beendiscovered and the action taken to synchronize the primary and standby databases:

ARC0: Begin FAL archive (thread 1 sequence 153 dest standby1)

ARC0: Complete FAL archive (thread 1 sequence 153 dest standby1)ARC0: Begin FAL archive (thread 1 sequence 154 dest standby1)

ARC0: Complete FAL archive (thread 1 sequence 154 dest standby1)

In the standby alert.log file, we see that the two archive redo logs that comprise the gap have been applied and now themanaged recovery process is waiting for the next archived redo log file to arrive:

Media Recovery Log /ora1/oradata/BRDSTN/ar/arch_0001_000153.arc

Media Recovery Log /ora1/oradata/BRDSTN/ar/arch_0001_000154.arc

Media Recovery Waiting for thread 1 seq# 155

Note: The FAL parameters are also used by the managed recovery process to re-ship an archive redo log if corruption isdetected or if the log file has been removed after previously being shipped.

Again, timely gap resolution is critical to maintaining a high availability. With automatic gap detection and resolution, DataGuard now automates a previously manual task.

Propagation of Datafile OperationsOne advantage that the physical standby database feature has always had over other high availability paradigms is that all

datatypes and DML operations are supported. Another advantage is that most DDL statements are supported. For example,any CREATE USER, CREATE TABLE, CREATE PACKAGE, etc., statements are automatically applied to the standbywithout DBA intervention.

However, one shortcoming of Oracle8 i is that tablespace and datafile add/drop operations are not propagated in an automatedfashion. For example, in Oralce8 i, you may add a datafile to your primary database:

SQL> alter tablespace GOBR add datafile '/ora1/oradata/BRDSTN8/gobr02.dbf' size 5M;


8/6/2019 rmougDG03

6/10


If you don't manually intervene before that statement is propagated to the standby, your standby database recovery sessionwill automatically be canceled and remain in limbo until you manually add the datafile to the standby as follows:

SQL> alter database create datafile '/ora1/oradata/BRDSTN8/gobr02.dbf' as '/ora1/oradata/BRDSTN8/gobr02.dbf';

While this isn't a huge inconvenience, it is something you must be aware of and have manual procedures in place to ensurethat tablespace and datafile add/drop operations do not terminate your standby recovery process.

New with Oracle9 i, tablespace and datafile add/drop operations can be setup to be automatically propagated to the standbydatabase. All you have to do is set the STANDBY_FILE_MANAGEMENT initialization parameter to AUTO. In your standby database issue the following command:

SQL> alter system set standby_file_management = auto;

From that point forward, tablespace and datafile add/drop commands are automatically propagated to the standbyenvironment. For example, if you issue the following command on your primary:

SQL> drop tablespace SHOLAY including contents and datafiles;

When that command is propagated to the standby, the SHOLAY tablespace will be dropped and the datafiles will be removedfrom disk. This relieves you from having to manually intervene with these critical physical DBA tasks.

Note: If you rename a datafile in Oracle9 i, you still must manually intervene. The STANDBY_FILE_MANAGEMENTinitialization parameter has no effect on datafile rename commands.

User Error ProtectionMany businesses use Oracle Data Guard to provide protection from user errors such as accidentally dropped tables anderroneous update/delete/insert statements. Protection from user errors is achieved by introducing a delay between the time a

primary archive redo log is generated and the time that it is applied to the physical standby. The idea being that with a delay,if somebody accidentally drops a table you will notice the problem before it has had time to make it to the physical standbydatabase.

In Oracle8 i you were required to manually enable this delay by writing an OS script that would manage the transport andapply of archived redo logs. The logic in the script would check the date stamp on the archive redo log file and would notapply any files that weren't older than the specified time criterion.

With Oracle9 i, you now get many robust options for configuring your managed recovery environment. One such optionautomates the delay in the application of archived redo logs to the physical standby. For example, when enabling managedrecovery mode, the following command instructs the managed recovery process to wait 60 minutes from the time the archiveredo log was generated to the time it is applied to the physical standby.


8/6/2019 rmougDG03

7/10


SQL> recover managed standby database delay 60 disconnect;

In this scenario the archive redo logs are immediately copied to the standby location as they become available, but are notapplied until 60 minutes later. This relieves you from having to script, monitor, and maintain a manual delay process.

Database Switchover In Data Guard, a database either has the role of a primary database or a standby database. These roles are mutually exclusive.To view the current role of your database:

SQL> select database_role from v$database;

DATABASE_ROLE

------------------------

PRIMARY

A database switchover is a Data Guard feature that allows you to switch the role of a database. You can now take a primarydatabase and make it a standby or take a standby database and transition its role to primary.

The Oracle9 i switchover feature is useful when you need to take your primary database out of service, say for hardwaremaintenance. In a scenario like this, you would switchover the primary to a standby role and switch the standby to the

primary role. After the hardware maintenance is finished, reverse (sometimes called a switchback ) the database roles back tothe original configuration.

Note: Switchover should not be confused with database failover. A failover is taking a standby database, shutting it down,mounting it, and then altering it to activate it as the primary. You would only initiate a failover if your primary database waslost beyond hope, and thus the only way to put it back would be from a backup.

Ensure that you refer to the Oracle Data Guard Concepts and Administration documentation for full details when performinga switchover. Performing a switchover should be a well-planned and practiced activity. The last thing you want to see whenyou issue the switchover command is:

ERROR at line 1:

ORA-16139: media recovery required

:-(

I recommend having two versions of spfiles (or init.ora) for each database, one for when it's in a primary role and one for when it's in a standby role. This will make it easier to ensure that parameters like CONTROL_FILES andDB_FILE_NAME_CONVERT are used correctly depending on the database role.

Data Guard Broker I'm old school and believe that when it comes to database availability you should know how to manually implement andmaintain critical data protection features. If problems arise (and they will) you'll need to know some of the inner workings of


8/6/2019 rmougDG03

8/10


your Data Guard environment. Thus, I recommend becoming familiar with manually implementing and maintaining anenvironment before using automated tools. Having said that, Data Guard Broker is a comprehensive set of tools that you canuse to create, manage, maintain, monitor, and automate tasks in your greater Data Guard environment.

The most visible component of the broker is Data Guard Manager . Data Guard Manager is a screen-based, wizard driven

tool invoked from Oracle Enterprise Manager (OEM). You can use Data Guard Manager to automate many tasks in your Data Guard environment. It is available in both Oracle9 i and Oracle9 i version 2.

The OEM Oracle9 i version 2 edition of Data Guard Manager is particularly feature-rich, allowing you to perform tasks suchas:

Create physical and logical standby databases Perform switchovers and failovers Toggle physical standby from recovery mode to read-only mode Change the protection mode for a primary/standby configuration Monitor log transport and log apply services

Configure event driven reporting/e-mailing/paging

You can literally point-and-click your way through many potentially complex manual tasks. If you are familiar with OEMtools, Data Guard Manager will be easy for you to use. The advantage of using this tool is that from one central console youcan automate complex tasks throughout a distributed Data Guard environment that would otherwise have to be donemanually. Refer to the Oracle Data Guard Broker documentation for full implementation details.

Getting StartedIf you're just getting started with Oracle Data Guard, here are three practical tips from the trenches that will help you moveforward with this data protection technology:

Start Out SimpleDon't start out trying to implement the most complex architecture. When getting started with Oracle Data Guard, it's easier toimplement if you:

Set up Oracle Data Guard with two different servers, one for the primary and one for the standby Ensure that the mount points are named the same between the two servers Make the database name the same for the primary and standby

This way all you have to do is take a backup of your primary, lay it onto your standby server without having to modify parameters in your spfile (or init.ora) that handle file name conversions.

Use Alert LogsAs you deploy your Oracle Data Guard environment and perform maintenance operations, pertinent messages are oftenwritten to the primary and standby alert.log files. I've found that concepts jell more quickly and issues are resolved morerapidly when I simultaneously monitor the contents of both the standby and primary alert.log files. In a Unix environment thetail command is particularly helpful in this regard:


8/6/2019 rmougDG03

9/10


$ tail -f alert_BRDSTN.log

List of Often Used CommandsIt is also handy is to keep a list of commonly used SQL commands in an Oracle Data Guard environment. Pin these up onyour cube wall so that you can concentrate on concepts rather than digging through manuals looking for syntax. This list isnot comprehensive, nor does it show all options, but I've found it makes a good quick reference:

Creating standby controlfilealter database create standby controlfile as '/';

Starting standbystartup nomount;

alter database mount standby database;

Recovering standbyMANUALrecover standby database;

MANAGEDrecover managed standby database [disconnect|cancel];

Placing physical standby in read-onlyalter database recover managed standby database cancel;

alter database open read only;

Read-only to managed recovery modealter database close;

recover managed standby database;

Failover alter database mount standby database;

alter database activate standby database;

Viewing database role and modeselect database_role, standby_mode from v$database;

Viewing transaction protection levelselect target,status, archiver, process,transmit_mode,async_blocks,affirm,type from v$archive_dest;


8/6/2019 rmougDG03

10/10


ConclusionOracle Data Guard is a fully integrated feature of Oralce9 i that enables you to build highly available database solutions.Oracle Data Guard adds an impressive set of disaster recovery features that build upon the already tried-and-true standbytechnology. This gives you a comprehensive disaster recovery tool suite that allows you to implement the appropriate level of data protection needed by your business.


rmougdg03

Documents