Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Data Guard Broker Configuration on an existing DataGuard Env

Posted by Hendry chinnapparaj on May 17, 2010

 

Data Guard Broker Configuration on an existing DataGuard Env

 

1        Introduction

 

1.1     Purpose & Scope

This document is intended as a step by step Dataguard Configuration & Operation Guide between CPSTEST Prod Primary Servers & CPSTEST Prod Standby Servers.

1.2     Environment Details

·       OS:  IBM AIX 5.3 TL05

·       Hardware:  IBM P-Series 595 Model

·       Oracle Software: Oracle 10gR2 Enterprise Edition -10204 (64bit)

·       ASM Oracle 11Gr2 Instance

·       Primary Site: 1 Node (DEVU009N3)

·       Standby Site:  1 Node (DEVU009N4)

·       Storage: Veritas Cluster Services

·       Database Name: CPSTEST

·       Instance Name: CPSTEST

·       Primary DB_UNIQUE_NAME: CPSTEST_J

·       Standby DB_UNIQUE_NAME: CPSTEST_F

·       Standby Type: Physical Standby

 

  •  

 

 

1.3     Definitions and Acronyms

Acronym  Definitions 
ASM Automatic Storage Management
RAC Oracle Real Application Cluster
OCR Oracle Cluster Registry
VIP Virtual IP Address

 

1.4     Audience

It is assumed that the intended audience has a general knowledge of database technology.

  • Database Administrators


2        Data Guard Configuration Steps

 

  • Pre-requisite for using Data Guard Broker
  • Creating Configuration
  • Post Configuration Steps
  • Performing a Switchover 

 

3        Pre-requisite for using Data Guard Broker

The following conditions must be true before you can use the broker:

3.1     Pre-create the standby database manually

Refer to oracle10gR2 DataGuard configuration and setup post on this blog

3.2      Define Broker Configuration Files

Two copies of the configuration file are maintained for each database so as to always have a record of the last known valid state of the configuration.

DEVU009N3

SQL>  alter system set dg_broker_config_file1=’/u01/oracle/product/10.2.0/db_1/dbs/dr1cpstest_j.dat’;

System altered.

SQL> alter system set dg_broker_config_file2=’/u01/oracle/product/10.2.0/db_1/dbs/dr2cpstest_j.dat’;

System altered.

DEVU009N4

SQL> alter system set dg_broker_config_file1=’/u01/oracle/product/10.2.0/db_1/dbs/dr1cpstest_f.dat’;

System altered.

SQL> alter system set dg_broker_config_file2=’/u01/oracle/product/10.2.0/db_1/dbs/dr2cpstest_f.dat’;

System altered.

3.3     Starting the Data Guard Broker

After setting up the configuration files, the DG_BROKER_START initialization parameter must be set to TRUE for each database to start the DMON processes.

DEVU009N3

SQL> alter system set DG_BROKER_START=TRUE;

System altered.

DEVU009N4

SQL> alter system set DG_BROKER_START=TRUE;

System altered.

3.4     Configure Network Aliases

Oracle Net Services network files must be set up on the primary database and on the standby database if you configure an existing standby database into the broker configuration.

Add the following entries in TNSNAMES.ORA of Primary as well as standby database.

DEVU009N3 & DEVU009N4

cpstest_j =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = cpstest_j)

    )

  )

cpstest_f =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = cpstest_f)

    )

  )

3.5     Static registration with Local Listener

To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the local listener of each instance. The value for the GLOBAL_DBNAME attribute must be set to a concatenation of db_unique_name_DGMGRL.db_domain.

Add the following entries in LISTENER.ORA file.

DEVU009N3

SID_LIST_LISTENER =

    (SID_LIST =

      (SID_DESC =

        (GLOBAL_DBNAME = cpstest_j_dgmgrl)

        (ORACLE_HOME = /u01/oracle/product/11.2.0/grid)

        (SID_NAME = cpstest)

      )

    )

 

 

DEVU009N4

SID_LIST_LISTENER =

    (SID_LIST =

      (SID_DESC =

        (GLOBAL_DBNAME = cpstest_f_dgmgrl)

        (ORACLE_HOME = /u01/oracle/product/11.2.0/grid)

        (SID_NAME = cpstest)

      )

    )

3.6     Creating Data Guard Broker Configuration

 

The following steps show how to create a configuration and add one physical standby database. We can create and manage the configuration from any of the node, Lets do it from DEVU009N3.

Step 1 Invoke DGMGRL.

devu009n3 $ pwd

/u01/oracle

devu009n3 $ dgmgrl

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

Step 2 Connect to the primary database

DGMGRL> connect sys/cpstest

Connected.

 Note:- connecting as sys is important as it needs to communicate to the other server

Step 3 Create the broker configuration (Note: content in quotes is case sensitive)

 

DGMGRL> CREATE CONFIGURATION ‘cpstest_dgb’ AS PRIMARY DATABASE IS ‘cpstest_j’  CONNECT IDENTIFIER IS ‘cpstest_j’;

Configuration “CPSTEST_DGB” created with primary database “CPSTEST_J”

Step 4 Add a standby database to the configuration

 

ADD DATABASE ‘cpstest_f’ AS CONNECT IDENTIFIER IS ‘cpstest_f’ MAINTAINED AS PHYSICAL;

DGMGRL> ADD DATABASE ‘cpstest_f’ AS CONNECT IDENTIFIER IS ‘cpstest_f’ MAINTAINED AS PHYSICAL;

Database “cpstest_f” added

Step 5 Verify the Configuration

DGMGRL> show configuration;

Configuration

  Name:                cpstest_dgb

  Enabled:             NO

  Protection Mode:     MaxAvailability

  Fast-Start Failover: DISABLED

  Databases:

    cpstest_j – Primary database

    cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

DISABLED

Step 6 Enable Configuration

 

DGMGRL> enable configuration

Enabled.

 

Step 7 Verify Configuration

 

DGMGRL> show configuration;

Configuration

  Name:                cpstest_dgb

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: DISABLED

  Databases:

    cpstest_j – Primary database

    cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS


4        Post Configuration steps

4.1     Enable Force Logging

Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement on DEVU009N3.

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

4.2     Creating Standby redo log files

Add the Standby redo log files to Primary (DEVU009N3) as well as Standby (DEVU009N4).

SQL> SELECT GROUP#, THREAD#, MEMBERS FROM V$LOG;

    GROUP#    THREAD#    MEMBERS

———- ———- ———-

         1          1          2

         2          1          2

         3          2          2

         4          2          2

         5          1          2

         6          1          2

         7          2          2

         8          2          2

8 rows selected.

SQL>

This will create logfiles in both the +FRA and +DATA diskgroups. Only one is required in +FRA so the +DATA logfiles can be removed. The below SQL will provide the syntax required for this:

SQL> select ‘alter database drop standby logfile member ”’ || member || ”’;’ from v$logfile

where type = ‘STANDBY’

and member like ‘+DATA%’;

4.3     Upgrade Protection Mode if already not done

DEVU009N3

DGMGRL> EDIT DATABASE ‘cpstest_j’ SET PROPERTY ‘StandbyFileManagement’=’AUTO’;

Property “StandbyFileManagement” updated.

DGMGRL> EDIT DATABASE ‘cpstest_j’ SET PROPERTY ‘LogXptMode’=’SYNC’;

Property “LogXptMode” updated

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

DEVU009N4

DGMGRL> EDIT DATABASE ‘cpstest_f’ SET PROPERTY ‘StandbyFileManagement’=’AUTO’;

Property “StandbyFileManagement” updated.

DGMGRL> EDIT DATABASE ‘cpstest_f’ PROPERTY ‘LogXptMode’=’SYNC’;

Property “LogXptMode” updated

4.4     Set Desired RMAN Archivelog Retention Policy

 

DEVU009N4

 

Oracle 10g can manage the deletion of archivelogs on the Standby side of a DataGuard setup. This is done by setting the below policy in a RMAN session into the standby database:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

Please note that whenever switchover or failover is carried out unless only for a short time this setting should be toggled between “NONE” and “APPLIED ON STANDBY” on the primary and standby sides respectively.


5        Performing a Switchover

5.1     Pre-requisites for Switchover

 

  • Check DGMGRL for indication of a healthy DataGuard setup again – i.e. “SUCCESS” – this may take a few minutes for this to be returned.

 

DGMGRL> show configuration

Configuration

  Name:                cpsetst_dgb

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: DISABLED

  Databases:

    CPSTEST_J – Primary database

    CPSTEST_F – Physical standby database

Current status for “CPSTEST_dgb”:

SUCCESS

5.2     Initiate Switchover

 

On either the primary or the standby side start a DGMGRL session and issue the following:

devu009n4 $ dgmgrl

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

  Name:                cpstest_dgb

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: DISABLED

  Databases:

    cpstest_j – Physical standby database

    cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL> switchover to cpstest_j;

Performing switchover NOW, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_f”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance “cpstest” on database “cpstest_j”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “cpstest” on database “cpstest_j”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_j”

DGMGRL> show configuration;

Configuration

  Name:                cpstest_dgb

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: DISABLED

  Databases:

    cpstest_j – Primary database

    cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

devu009n4 $

on new Primary now, query

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

             LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

             LOG_ARCHIVED – LOG_APPLIED LOG_GAP

        FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

                  FROM GV$INSTANCE

              ORDER BY INST_ID) NAME,

             (SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

                  FROM GV$ARCHIVE_DEST_STATUS

                 WHERE DEST_ID = 2

              ORDER BY INST_ID) STAT,

             (SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

                  FROM V$ARCHIVED_LOG

                 WHERE DEST_ID = 1

                   AND ARCHIVED = ‘YES’

                   AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

                                         FROM V$ARCHIVED_LOG

                                        WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

              GROUP BY THREAD#

              ORDER BY THREAD#) ARCH,

             (SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

                       TO_CHAR (MAX (COMPLETION_TIME),

                                ‘DD/MM HH24:MI:SS’

                               ) TIME_APPLIED

                  FROM V$ARCHIVED_LOG

                 WHERE DEST_ID = 2

                   AND APPLIED = ‘YES’

                   AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

                                         FROM V$ARCHIVED_LOG

                                        WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

              GROUP BY THREAD#

              ORDER BY THREAD#) APPL

       WHERE NAME.INST_ID = STAT.INST_ID

         AND NAME.INST_ID = ARCH.THREAD#

         AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

—————————————————————-

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

——————– ———————- ———— ———–

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n3

MAXIMUM AVAILABILITY OK                              105         105

14/05 11:34:24          0

Query to ensure that the datafiles are online

SQL> select name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     98

Next log sequence to archive   101

Current log sequence           101

Query the standby

select * from v$dataguard_status

FACILITY                 SEVERITY         DEST_ID MESSAGE_NUM ERROR_CODE CAL

———————— ————- ———- ———– ———- —

TIMESTAMP

———

MESSAGE

——————————————————————————–

14-MAY-10

Standby controlfile consistent with primary

Remote File Server       Warning                0          49          0 NO

14-MAY-10

RFS[1]: Successfully opened standby log 6: ‘+DATA/cpstest_j/onlinelog/group_6.15

91.718685471’

FACILITY                 SEVERITY         DEST_ID MESSAGE_NUM ERROR_CODE CAL

———————— ————- ———- ———– ———- —

TIMESTAMP

———

MESSAGE

——————————————————————————–

Log Apply Services       Warning                0          50          0 NO

14-MAY-10

Media Recovery Waiting for thread 1 sequence 101 (in transit)

50 rows selected.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     99

Next log sequence to archive   0

Current log sequence           101

5.3     Post Switchover

 

If switchover or failover is carried out and not intended to be reverted within a few hours the RMAN archivelog deletion policy setting should be changed on both databases. The following indicates which setting is correct for each side:

Primary Side:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

Standby Side:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;


6        Opening a Standby Database for Read only

 

You might want to use your physical standby database temporarily for reporting applications. To change the state of the standby database to read-only.

DGMGRL> EDIT DATABASE ‘cpstest_f’ SET STATE=’READ-ONLY’;

Succeeded.

Note:- Opening the standby database in readonly may not be possible if you have turned on auditing which is seen from audit_trail=db init.ora parameter. You have to change the value to open in readonly.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: