Setup and maintenance of Data Guard Broker using DGMGRL [ID 201669.1]

Setup and maintenance of Data Guard Broker using DGMGRL [ID 201669.1]
	Modified09-JUL-2009TypeBULLETINStatusPUBLISHED

In this Document
Purpose
Scope and Application
Setup and maintenance of Data Guard Broker using DGMGRL
OVERVIEW OF ORACLE9i DATA GUARD BROKER
COMMAND LINE SETUP OF ORACLE DATA GUARD BROKER 9i
References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 9.2.0.8
Information in this document applies to any platform.

Purpose

Oracle9i Data Guard Broker documentation explains how to set up
a Disaster Recovery Configuration (DRC) by using Oracle Enterprise
Manager (OEM).

This paper is intended to provide the following:

1. a brief overview of Oracle9i Data Guard Broker.

2. instructions on implementing DRC by using a command line utility,
called DGMRGL.

3. an overview of common maintenance operations

4. an example on debugging a typical erroneous situation

The examples are from a Windows environment but are also applicable for UNIX.

Scope and Application

Oracle9i DBA community

Setup and maintenance of Data Guard Broker using DGMGRL

OVERVIEW OF ORACLE9i DATA GUARD BROKER

Oracle9i Data Guard Broker is composed of following components:

[a] A graphical Data Guard Manager utility that supports managing
or monitoring the databases in a DRC. This GUI is part of OEM and
requires an Intelligent Agent (IA). The GUI tool provides a wizard
that can create a standby database with minimum user interaction.

[b] A command line utility, called DGMGRL, that provides similar
functionality (except the wizard) to create standby databases.

[c] One background process, called Data Guard Broker or DMON, per
database in a DRC.

All DMON processes in a DRC communicate with each other and act as
executors of the Data Guard Manager commands. The following dynamic
init.ora parameters should be set to TRUE in order to start the
DMON process:

- DRS_START (Oracle8i, 9.0.1)
- DG_BROKER_START (Oracle 9.2)

Like other background processes, DMON process maintains a log file,
named drc$ORACLE_SID.log, in BACKGROUND_DUMP_DEST.

[d] A DRC repository used by all DMON processes. The repository
is made up of following two files:

- UNIX: $ORACLE_HOME/dbs/dr1$ORACLE_SID.dat and
$ORACLE_HOME/dbs/dr2$ORACLE_SID.dat
- Windows: %ORACLE_HOME%/DATABASE/DR1%ORACLE_SID%.DAT and
%ORACLE_HOME%/DATABASE/DR2%ORACLE_SID%.DAT

In Oracle 9.2, it is possible to reposition these files via init.ora
parameters DG_BROKER_CONFIG_FILE1 and DG_BROKER_CONFIG_FILE2.

COMMAND LINE SETUP OF ORACLE DATA GUARD BROKER 9i

2.1. Pre-requisites

In order to use DGMGRL, the following should be done manually:

[a] Create standby databases manually as perNote 180031.1
Creating a Data Guard Configuration.

[b] Use SPFILE instead of PFILE for all databases. This is required
since all necessary init.ora parameters are dynamically changed via
command 'ALTER SYSTEM SET'.

[c] Use remote password files. This is required since DGMGRL needs
to connect remotely as SYSDBA in order to perform certain actions.

[d] Add standby redo logs in case you want to put the DRC in
MaxProtection or MaxAvailability mode.

Create at least one standby redo log on the standby database with
the same size as on the primary database by using the following
command:

ALTER DATABASE ADD STANDBY LOGFILE
'<Full path of standby redo log file>' SIZE <size>;

[e] Start all databases in a DRC in nomount state. Also, start the
listeners needed for the log archive transport.

[f] Start the DMON process on each database by setting dynamic
init.ora DRS_START (Oracle8i, 9.0.1) or DG_BROKER_START (Oracle9.2)
to TRUE.

2.2. Setup of the DRC configuration

=> Set ORACLE_SID to the primary database and connect via DGMGRL

C:/>dgmgrl
DGMGRL for 32-bit Windows: Version 9.2.0.1.0 - Production.

(c) Copyright 2002 Oracle Corporation. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.

=> Create a new DRC and define a primary site

DGMGRL> create configuration 'V920_DRC' as
> Primary site is 'prim_site'
> Resource is 'prim_db'
> Hostname is 'HOST1'
> Instance name is 'V920'
> Service name is 'R920'
> Site is maintained as physical;
Configuration "V920_DRC" added with primary site "prim_site"
Database resource "prim_db" added.

=> Define one or more standby sites

DGMGRL> create site 'standby_site'
> Resource is 'standby_db'
> Hostname is 'HOST2'
> Instance name is 'V920STBY'
> Service name is 'R920STBY'
> Site is maintained as PHYSICAL;
Site "standby_site" added to configuration.
Database resource "standby_db" added.

NOTE: The following must be observed when setting up the
configuration:

* The Hostname and Instance name values must be the
same as values displayed in v$instance on the respective
primary and standby instances.

* The Service name should be the Net alias defined in the
tnsnames.ora. This will then be used to configure the
fal_server, fal_client, and log_archive_dest_n parameters
and provides the communication path between the primary
broker and standby broker processes (DMON). This alias
should exist on both standby and primary tnsnames.ora files.

=> View DRC

DGMGRL> show configuration
Configuration 'V920_DRC' is
Primary Site is 'prim_site'
Standby Site is 'standby_site'
Current status for "V920_DRC":
DISABLED

=> Enable DRC. Review alert.log files of all databases as well as
the log file for DMON processes. The databases are opened or mounted
as needed.

DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration
Configuration 'V920_DRC' is
Primary Site is 'prim_site'
Standby Site is 'standby_site'
Current status for "V920_DRC":
SUCCESS

=> View information about all sites in DRC

DGMGRL> show site verbose 'prim_site';
Site
Name: 'prim_site'
Hostname: 'HOST1'
Instance name: 'V920'
Service Name: 'R920'
Standby Type: 'physical'
Number Built-in Processes: '2'
Number Generic Processes: '0'
Enabled: 'yes'
Required: 'yes'
Default state: 'PRIMARY'
Intended state: 'PRIMARY'
PFILE: ''
Number of resources: 1
Resources:
Name: prim_db (default) (verbose name='prim_db')
Current status for "prim_site":
SUCCESS

=> View information about all resources in DRC

DGMGRL> show resource verbose 'prim_db';
Resource
Name: prim_db
Manager Type: internal
Standby Type: PHYSICAL
Online States:
ONLINE
PHYSICAL-APPLY-READY
PHYSICAL-APPLY-ON
READ-ONLY
LOGICAL-APPLY-READY
LOGICAL-APPLY-ON
READ-WRITE
READ-WRITE-XPTON
Properties:
INTENDED_STATE = 'READ-WRITE-XPTON'
ENABLED = 'yes'
IGNORE_STATUS = 'no'
LogXptMode = 'ARCH'
Dependency = ''
Alternate = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
ReopenSecs = '300'
AsyncBlocks = '2048'
LogShipping = 'ON'
ApplyNext = '0'
ApplyNoDelay = 'NO'
ApplyParallel = '1'
StandbyArchiveDest = 'd:/oracle/oradata/V920/standby'
LogArchiveTrace = '0'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'stby , v920 '
LogFileNameConvert = 'stby , v920 '
LogArchiveFormat = 'ARC%S.%T'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
SbyLogQueue = '(monitor)'
Properties for 'PRIMARY' state:
DEFAULT_STATE = 'READ-WRITE-XPTON'
EXPLICIT_DISABLE = 'no'
REQUIRED = 'yes'
Properties for 'STANDBY' state:
DEFAULT_STATE = 'PHYSICAL-APPLY-ON'
EXPLICIT_DISABLE = 'no'
REQUIRED = 'yes'
Current status for "prim_db":
SUCCESS

=> When you remove a standby site, management of the database resource object
associated with that standby site is removed. However, this command does not
remove or affect the actual standby site and database. You cannot remove the
primary site with this command.

DGMGRL> REMOVE SITE 'standby_site2';
Removed site "standby_site2" from configuration.


3. MAINTENANCE OPERATIONS IN A DRC

=> Switch the standby database between read-only and recovery mode:

DGMGRL> alter resource 'standby_db' on site 'standby_site'
set state = 'READ-ONLY';
Succeeded.

DGMGRL> alter resource 'standby_db' on site 'standby_site'
set state = 'PHYSICAL-APPLY-ON';
Succeeded.

=> Put the DRC in MaxProtection mode (Oracle9.2 onwards)

DGMGRL> alter resource 'prim_db' set property LogXptMode = 'SYNC';
Property "logxptmode" updated.

DGMGRL> alter resource 'standby_db' set property LogXptMode = 'SYNC';
Property "logxptmode" updated.

DGMGRL> alter configuration set protection mode as 'MaxProtection';
Operation requires restart of site "prim_site"
Shutting down site prim_site...
Database closed.
Database dismounted.
ORACLE instance shut down.
Restarting site prim_site...
Started "prim_site" as new primary

=> Graceful switchover from primary to standby (Oracle 9.2 onwards)
(takes around 3 minutes on a laptop)

DGMGRL> switchover to 'standby_site';
Performing switchover NOW. Please wait...
Operation requires restart of site "prim_site"
Operation requires restart of site "standby_site"
Shutting down site prim_site...
database not mounted
ORACLE instance shut down.
Shutting down site standby_site...
database not mounted
ORACLE instance shut down.
Restarting site prim_site...
Restarting site standby_site...
Started "prim_site" as standby
Started "standby_site" as new primary
Switchover succeeded. New primary is "standby_site"


4. DEBUG AN ERRONEOUS SITUATION IN A DRC

=> View configuration status

DGMGRL> show configuration;
Configuration 'V920_DRC' is
Primary Site is 'prim_site'
Standby Site is 'standby_site'
Current status for "V920_DRC":
Warning: ORA-16608: one or more sites have warnings

=> ORA-16608 indicates that one or more sites have warnings.
Find out which site has a warning.

DGMGRL> show site 'prim_site';
Site 'prim_site' is
Hostname is 'BEL716'
Instance name is 'V920'
Service name is 'R920'
Standby is maintained as 'physical'
Site has 1 resource
Resource is 'prim_db'
Current status for "prim_site":
Warning: ORA-16609: one or more resources have failed

=> Primary site has a problem because its only resource 'prim_db'
is not working (as indicated by ORA-16609)

DGMGRL> show resource 'prim_db';
Resource 'prim_db' on site 'prim_site'
depends on 'prim_site'
Current status for "prim_db":
Error: ORA-16778: The log transport service for some site has an error

=> Resource 'prim_db' is not working because the redo/archive logs
cannot be transported to the standby site

DGMGRL> show site 'standby_site';
Site 'standby_site' is
Hostname is 'BEL716'
Instance name is 'V920STBY'
Service name is 'R920STBY'
Standby is maintained as 'physical'
Site has 1 resource
Resource is 'standby_db'
Current status for "standby_site":
Warning: ORA-12541: TNS:no listener

=> We can not transport the logs because the listener on the standby
site is down.

=> ACTION: Start the listener on the standby database

DGMGRL> show site 'standby_site';
Site 'standby_site' is
Hostname is 'BEL716'
Instance name is 'V920STBY'
Service name is 'R920STBY'
Standby is maintained as 'physical'
Site has 1 resource
Resource is 'standby_db'
Current status for "standby_site":
Warning: ORA-01034: ORACLE not available

=> We can not connect to the DMON process of the standby database
because the database is down.

=> ACTION: Start the standby database in nomount state

DGMGRL> show configuration;
Configuration 'V920_DRC' is
Primary Site is 'prim_site'
Standby Site is 'standby_site'
Current status for "V920_DRC":
SUCCESS

=> The standby database has been successfully mounted in recovery
mode by the Data Guard Broker.

References

NOTE:180031.1- Creating a Data Guard Configuration
NOTE:260112.1- 10g DGMGRL CLI Configuration

---

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