Data Guard broker config

REFERENCE:
-----------

DATGUARD CREATION:
-------------------
http://www.oracledba.org/10g/dr/10gR2_dataguard_on_Linux.html

DATAGUARD BROKER CREATION
--------------------------
http://www.orafaq.com/wiki/Data_Guard_implementation_for_Oracle_10gR2
http://www.oracle.com/technology/pub/articles/smiley-fsfo.html
http://www.oraclecommunity.net/profiles/blogs/configuration-of-10g-data
http://www.ritzyblogs.com/OraTalk/EntryId/122/How-to-setup-DGMGRL-broker-with-example


********************************************************************************************************************************

PREREQUESTS:
-----------------

1.database should be in force logging mode.
2.database should be in archive log mode
3.init parameter dg_broker_start=true
4. _DGMGRL in listener file
5.standby logfiles on both the sides.
6.init parameter Log_archive_config='DG_CONFIG=(primary,standby)'
7.Verify ur dataguard setup before dg-broker. If any errors solve it.


Now on the PRIMARY site check the status of Standby Archiving destination.

SQL> Select Status, Error from v$Archive_dest where dest_id=2;

The STATUS should return – VALID. If it returns Error, then check the connectivity between the Primary and Standby machines.


On the PRIMARY Database: (Open stage)

Query V$DATABASE to check the role of Primary Database.

SQL> Select Database_role from v$Database;
It will return "PRIMARY",

Now check the Switchover Status of the Primary Database.

SQL> Select switchover_status from v$Database;
It will return "SESSIONS ACTIVE".

On the PHYSICAL STANDBY Database: (Mount stage) Query V$DATABASE to check the role of Standby Database.

SQL> Select Database_role from v$Database;
It will return "PHYSICAL STANDBY",

Now check the Switchover Status of the Standby Database.

SQL> Select switchover_status from v$Database;
It will return "SESSIONS ACTIVE".


********************************************************************************************************************************


IF U USE FSFO(FAST START FAILOVER) only
---------------------------------------

1.flashback Database should be enabled.
2.fast start failover should be eanabled in DGMGRL
3.start the observer in third computer.
4.oracle version should be 10.2 or higher


********************************************************************************************************************************

1. Set up init parameters on primary to enable broker
--------------------------------------------------------

NOTE :(IF u planning to use Automatic failover(FSFO)please refer prerequets of FSFO before configuring the follows)

SQL> alter system set dg_broker_start=false sid='*';
System altered.

SQL>Alter database add standby logfile
('C:\oracle\product\10.2.0\oradata\ORCL\StandbyRedo.log') size 50m;(It Should be same size of online redo log files)

SQL> alter system set dg_broker_config_file1='/n01/dg_broker_config_files/dr1PRIMARY.dat' sid='*';
System altered.

SQL> alter system set dg_broker_config_file2='/n01/dg_broker_config_files/dr2PRIMARY.dat' sid='*';
System altered.

SQL> alter system set dg_broker_start=true sid='*';
System altered.




2. Verify if DMON process has started on all the instances of primary.
-----------------------------------------------------------------------
Example:

$ ps -ef|grep dmon|grep -v grep
oracle 16190 1 0 08:53 ? 00:00:00 ora_dmon_PRIMARY

$ ps -ef|grep dmon|grep -v grep
oracle 29723 1 0 08:53 ? 00:00:00 ora_dmon_PRIMARYR2



3. Set up init parameters on standby
-------------------------------------

SQL> alter system set dg_broker_start=false sid='*';
System altered.

SQL> alter system set dg_broker_config_file1='/export/crawlspace/dg_broker_config_files/dr1STANDBY.dat' sid='*';
System altered.

SQL> alter system set dg_broker_config_file2='/export/crawlspace/dg_broker_config_files/dr2STANDBY.dat' sid='*';
System altered.

SQL> alter system set dg_broker_start=true sid='*';
System altered.



4. Setting up the listener
-----------------------------

GLOBAL_DBNAME should be set to <>_DGMGRL.<> in listener.ora on all instances of both primary and standby.
This is important otherwise you'll have TNS-12154 error during switchover operation.

Example:

SID_LIST_LISTENER_PRIMARY =
(SID_LIST =
(SID_DESC =
(SID_NAME = PRIMARY)
(GLOBAL_DBNAME = PRIMARY_DGMGRL)
(ORACLE_HOME = /apps/oracle/product/10g/db)
)
(SID_DESC =
(SID_NAME = STANDBY)
(GLOBAL_DBNAME = STANDBY_DGMGRL)
(ORACLE_HOME = /apps/oracle/product/10g/db)
)
)




5. DGMGRL Configuration
------------------------

5.1Connect
-------------

DGMGRL> CONNECT sys/sys@primary
Connected.


5.2Create Configuration
------------------------

DGMGRL> CREATE CONFIGURATION 'DG_TEST' AS PRIMARY DATABASE IS 'PRIMARY' CONNECT IDENTIFIER IS PRIMARY;
Configuration "DG_TEST" created with primary database "PRIMARY".


DG_TEST --> any name.


5.3 Add standby database to the configuration
-----------------------------------------------

DGMGRL> ADD DATABASE 'STANDBY' AS CONNECT IDENTIFIER IS STANDBY MAINTAINED AS PHYSICAL;
Database "STANDBY" added.


5.4Verifying everything
---------------------------
DGMGRL> SHOW CONFIGURATION;
Configuration
Name: DG_TEST
Enabled: YES
Protection Mode: MaxPerformance
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database

Current status for "DG_TEST":
SUCCESS

Note:If u doesn't get "SUCCESS" & if any issue kindly refer TROUBLESHOOT chapter --> NO :6
-----

DGMGRL>SHOW DATABASE VERBOSE 'prod' ;

DGMGRL>SHOW DATABASE VERBOSE 'stand' ;


Note : It should give result as SUCCESS.And check for logxptmode,standbyarchivelocation,nettimeout
------

DGMGRL>edit database prod set property 'logxptmode'='SYNC';

DGMGRL>edit database stand set property 'logxptmode'='SYNC';




DGMGRL>edit database prod set property 'standbyarchivelocation'='C:\oracle\product\oradata\arch_stand';

DGMGRL>edit database stand set property 'standbyarchivelocation'='C:\oracle\product\10.2.0\oradata\arch_prod';



DGMGRL>edit database prod set property 'nettimeout'='10';

DGMGRL>edit database stand set property 'nettimeout'='10';



5.5Verify database; if RAC verify if all instances are validated
-----------------------------------------------------------------

DGMGRL> show database 'PRIMARY';
Database
Name: PRIMARY
Role: PRIMARY
Enabled: NO
Intended State: ONLINE
Instance(s):
PRIMARY
PRIMARYR2

Current status for "PRIMARY":
DISABLED



5.6 Enable the broker
----------------------
DGMGRL> ENABLE CONFIGURATION;
Enabled.


********************************************************************************************************************************

Switchover:
--------------

Now we are ready to switch over the PRIMARY database Role to STANDBY database Role.

DGMGRL> switchover to 'Standby';
…..
…..
Primary Database Successfully converted to Physical Standby. You can again switch over the Standby Database to Primary by executing following command.

DGMGRL> switchover to 'Primary';
…..
…..
Standby Database is successfully converted to Primary Database.


Failover: (Manual)
------------------

Failover can be done through the same configuration without any alteration. You simply need to execute following command:

DGMGRL> failover to 'Standby';
….
Failover to standby succeeded.
And also…

DGMGRL> failover to 'Primary';
….
Failover to primary succeeded.



********************************************************************************************************************************



--------------------------
AUTOMATIC FAILOVER(FSFO)
--------------------------


IF U USE FSFO(FAST START FAILOVER) only
---------------------------------------

1.flashback Database should be enabled.
2.fast start failover should be eanabled in DGMGRL
3.start the observer in third computer.
4.oracle version should be 10.2 or higher

DATAGUARD OBSERVER :
-----------------------

Observer is a utility that is available with the dataguard. Its basic purpose is to keep the database available to the
clients all the time. It is started on a separate location other than Primary and Standby locations. After starting the observer,
it starts watching continuously both the sites. Whenever the PRIMARY database fails due to any reason and the connection between
the PRIMARY site and the observer breaks, the observer waits for a certain time which is set up during setting the broker configuration.
When this time passes, the observer automatically starts the failover process. As a result, the Physical Standby database is converted
to PRIMARY database. After this failover, the users again connect to the database. This connection redirects them automatically to the
new PRIMARY database.


OBSERVER CONFIGURATION:
-------------------------

There are some important configuration settings that are needed to be set before starting the Dataguard (DG) Observer.
Main thing is to set the STANDBY database to Maximum Availability protection mode. DG broker configuration
must also be set to "MaxAvailability" protection mode. After this we need to enable "fast_start failover" parameter of the broker configuration.
Without setting these options, broker will not allow to proceed for observer to work for remote failover. Following are the steps to enable the observer.


OBSERVER SITE: (Information)
------------------------------------

OBSERVER Machine IP: 192.168.1.65
PRIMARY Machine IP: 192.168.1.46
STANDBY Machine IP: 192.168.1.96

TNS Service Name for PRIMARY site: to_primary (Through this service, the observer machine will be connected to PRIMARY machine)

TNS Service Name for STANDBY site: to_standby (Through this service, the observer machine will be connected to STANDBY machine)

(NOTE: Fresh DG broker configuration will be created for implementation of Observer. Therefore, remove any previously created configuration.)




Step 1:
--------

Create TNS services on the observer machine for PRIMARY site and STANDBY site through which the observer machine
will be connected to the primary and standby databases.

TNS service name for PRIMARY site is 'primary'. TNS service name for STANDBY site is 'to_standby'.

Step 2:
---------
Before starting the broker CLI (DGMGRL) we have to enable FLASHBACK on both the Primary and Standby databases.

At mount stage execute:

SQL> Alter database Flashback on;
Database altered.

Step 3:
--------
On the STANDBY site, edit Log_Archive_Dest_2 parameter and add three properties i.e. LGWR AFFIRM SYNC. These properties are needed
to bring the broker protection mode to MaxAvailability.

SQL> Alter System set Log_archive_dest_2='Service=primary
LGWR AFFIRM SYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
db_unique_name=primary';
System altered.

Step 4:
---------
Now execute the following command on the primary database to change the protection mode from MAXIMUM PERFORMANCE to MAXIMUM AVAILABILITY.

SQL> Alter database set primary database to maximize availability;
Shut down the PRIMARY database and then restart it. Check the protection mode on both PRIMARY and STANDBY databases.

SQL> select protection_mode from v$database.
It should return MAXIMUM AVAILABILITY.

Step 5:
--------
Now start the DG CLI (DGMGRL) to start the broker.

C:\> dgmgrl
DGMGRL for 32-bit Windows: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information. DGMGRL>

DGMGRL> connect sys/oracle@primary
Connected.
Check any previously created configuration and remove it.

DGMGRL> show configuration;
If a configuration is displayed then remove it.

DGMGRL> remove configuration;
Removed.
Now create a new configuration.

DGMGRL> create configuration 'broker1' as
primary database is 'primary'
connect identifier is primary;
Configuration "broker1" created with primary database "primary"



DGMGRL> add database 'standby' as
connect identifier is STANDBY
maintained as physical;
Database "broker1" added



DGMGRL> show configuration;
Configuration
Name: broker1
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLE
Databases:
primary - Physical standby database
standby - Primary database
Current status for "broker1":
DISABLE



DGMGRL> enable configuration;
Enabled.



DGMGRL> show configuration;
Configuration
Name: broker1
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLE
Databases:
primary - Physical standby database
standby - Primary database
Current status for "broker1":
SUCCESS

DGMGRL> edit database PRIMARY set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database STANDBY set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit configuration set protection mode as MaxAvailability;
Succeeded.
DGMGRL>edit database prod set property 'standbyarchivelocation'='C:\oracle\product\oradata\arch_stand';

DGMGRL>edit database stand set property 'standbyarchivelocation'='C:\oracle\product\10.2.0\oradata\arch_prod';



DGMGRL>edit database prod set property 'nettimeout'='10';

DGMGRL>edit database stand set property 'nettimeout'='10';


DGMGRL>EDIT DATABASE prod SET PROPERTY FastStartFailoverTarget='stand';

DGMGRL>EDIT DATABASE stand SET PROPERTY FastStartFailoverTarget='prod';



DGMGRL> enable fast_start failover;
Enabled.
DGMGRL> show configuration;
Configuration
Name: dg1
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
primary - Physical standby database
- Fast-Start Failover target
standby - Primary database
Current status for "dg1": Warning: ORA-16608: one or more databases have warnings

If you check this error by querying StatusReport property of the PRIMARY database,
you will find that Fast Start Failover OBSERVER is not started. Simply start the observer.

DGMGRL> show database primary StatusReport
Now start the observer.

DGMGRL> start observer (In a third machine apart from primary & standby)
Observer Started.

It seems to be hanged.but no issue

The Observer has started its working. The current session of 'cmd' will not be returned to you because it is a foreground process.
It will continuously observe PRIMARY site. To check the configuration, open another 'cmd' window and check the configuration.

DGMGRL> show configuration;
Configuration
Name: broker1 Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: Enabled
Databases:
primary -- Primary database
standby -Physical standby database - Fast – Start Failover target


Fast-Start Failover
Threshold: 30 seconds Observer: orat

Current status for "broker1":
SUCCESS
___________________________________________

At this stage every thing is going on normally. An identical copy of PRIMARY database is being made at the STANBY site.
If due to any reason, PRIMARY database crashes, the observer will start its working.

DGMGRL> start observer
Observer started.
15:21:21.69 Wednesday, March 07, 2007
Initiating fast-start failover to database "standby"…
Performing failover NOW, please wait…
Operation requires shutdown of instance "orcl" on database "standby"
Shutting down instance "orcl"…
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "standby" Starting instance "orcl"…

********************************************************************************************************************************


6. Troubleshooting
-------------------

REF : http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10822/cli.htm

Let us see some sample issues and their fix Issue

DGMGRL> CONNECT sys/sys
ORA-16525: the Data Guard broker is not yet available

Fix
Set dg_broker_start=true

Issue
After enabling the configuration, on issuing SHOW CONFIGURATION, this error comes
Warning: ORA-16608: one or more sites have warnings

Fix
To know details of the error, you may check log which will be generated at bdump with naming as drc{DB_NAME}.log or there are various monitorable properties that can be used to query the database status and assist in further troubleshooting.

Few Monitorable properties to troubleshoot
DGMGRL> SHOW DATABASE 'PRIMARY' 'StatusReport';
DGMGRL> SHOW DATABASE 'PRIMARY' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'PRIMARY' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'PRIMARY' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'STANDBY' 'StatusReport';
DGMGRL> SHOW DATABASE 'STANDBY' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'STANDBY' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'STANDBY' 'InconsistentLogXptProps';

Issue
DGMGRL> SHOW DATABASE 'PRIMARY' 'StatusReport';
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
PRIMARYR2 WARNING ORA-16714: The value of property ArchiveLagTarget is inconsistent with the database setting.
PRIMARYR2 WARNING ORA-16714: The value of property LogArchiveMaxProcesses is inconsistent with the database setting.

Issue
DGMGRL> SHOW DATABASE 'PRIMARY' 'InconsistentProperties';
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
PRIMARYR2 ArchiveLagTarget 0 0
PRIMARYR2 LogArchiveMaxProcesses 4 2 4

Example
DGMGRL> SHOW DATABASE 'PRIMARY' 'LogArchiveMaxProcesses';
LogArchiveMaxProcesses = '4'

Fix
DGMGRL> EDIT DATABASE 'PRIMARY' SET PROPERTY 'LogArchiveMaxProcesses'=2;

or

SQL> alter system set log_archive_max_processes=4 scope=spfile sid='*';
System altered.

DGMGRL> SHOW DATABASE 'PRIMARY' 'LogArchiveMaxProcesses';
LogArchiveMaxProcesses = '4'

More commands
DGMGRL> SHOW DATABASE VERBOSE 'dbname';
This will show all property values in detail

DGMGRL> HELP;
List of all broker commands with usage help

Equivalent Broker Commands to 'ALTER SYSTEM'
SQL> alter database recover managed standby database cancel;
DGMGRL> edit database 'stby_dbname' set state='LOG-APPLY-OFF';

SQL> alter database recover managed standby database disconnect;
DGMGRL> edit database 'stby_dbname' set state='ONLINE';

SQL> alter system set log_archive_max_processes=4;
DGMGRL> edit database 'dbname' set property 'LogArchiveMaxProcesses'=4;

SQL> alter system set log_archive_dest_state_2='enable' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='ON';

SQL> alter system set log_archive_dest_state_2='defer' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='OFF';

DGMGRL> edit database 'pri_dbname' set state='LOG-TRANSPORT-OFF';
This will defer all standby databases


That's it my friends. You are all set to use broker now for managing your dataguard configuration.