Friday 3 October 2014

Mastering 12C Data Guard BROKER


In this article we will see how to master 12C Oracle Data Guard Broker, we will see how to setup a broker, troubleshoot the broker, problems faced with 12C Broker, Switchover and failover with 12C Broker and also Fast Start failover with 12C Broker.

In this article we have a data guard setup with primary as PRD and standby as DRS.

[oracle@node4 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /   
Connected as SYSDG.

DGMGRL> create configuration myconfig1 as primary database is 'prd'  connect identifier is prd ;
Error: 
ORA-16525: the Data Guard broker is not yet available

## So in this case we will enable the initialization parameter dg_broker_start

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PRD       READ WRITE

SQL> alter system set dg_broker_start=true;

System altered.

## Lets go to standby database

[oracle@node4 ~]$ sqlplus sys@drs as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 8 15:09:22 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL>  alter system set dg_broker_start=true;

System altered.

[oracle@node4 ~]$ dgmgrl

DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@prd
Password:
Connected as SYSDBA.

DGMGRL>  create configuration myconfig1 as primary database is 'prd'  connect identifier is prd ;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Failed.

## In this case we should disable the following initialization parameter

SQL>   ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=" ";

System altered.


DGMGRL>  create configuration myconfig1 as primary database is 'prd'  connect identifier is prd ;

Configuration "myconfig1" created with primary database "prd"

DGMGRL> add database drs as  connect identifier is drs ;

Database "drs" added
DGMGRL> 
DGMGRL> 

DGMGRL> enable configuration ;
Enabled.

DGMGRL> show configuration ;

Configuration - myconfig1

  Protection Mode: MaxPerformance
  Databases:
  prd - Primary database
    Warning: ORA-16789: standby redo logs not configured

    drs - Physical standby database 
      Error: ORA-16525: the Data Guard broker is not yet available

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

## So we will configure standby redo logs now for primary

SQL> alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m;
Enter value for a: 4
Enter value for a: red04.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m
new   1: alter database add standby logfile group 4 ('/u01/app/oracle/oradata/prd/red04.log') size 50m

Database altered.

SQL> /
Enter value for a: 5
Enter value for a: red05.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m
new   1: alter database add standby logfile group 5 ('/u01/app/oracle/oradata/prd/red05.log') size 50m

Database altered.

SQL> /
Enter value for a: 6
Enter value for a: red06.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m
new   1: alter database add standby logfile group 6 ('/u01/app/oracle/oradata/prd/red06.log') size 50m

Database altered.

SQL> /
Enter value for a: 7
Enter value for a: red07.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m
new   1: alter database add standby logfile group 7 ('/u01/app/oracle/oradata/prd/red07.log') size 50m

Database altered.


DGMGRL> show database drs

Database - drs

  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-ON
  Transport Lag:     46 seconds (computed 17 seconds ago)
  Apply Lag:         2 minutes 36 seconds (computed 17 seconds ago)
  Apply Rate:        (unknown)
  Real Time Query:   OFF
  Instance(s):
    drs

  Database Warning(s):
    ORA-16789: standby redo logs not configured

Database Status:
WARNING

## Standby logs also not configured for standby database

SQL> alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m;
Enter value for a: 4
Enter value for a: red04.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m
new   1: alter database add standby logfile group 4 ('/u01/app/oracle/oradata/drs/red04.log') size 50m

Database altered.

SQL> /
Enter value for a: 5
Enter value for a: red05.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m
new   1: alter database add standby logfile group 5 ('/u01/app/oracle/oradata/drs/red05.log') size 50m

Database altered.

SQL> /
Enter value for a: 6
Enter value for a: red06.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m
new   1: alter database add standby logfile group 6 ('/u01/app/oracle/oradata/drs/red06.log') size 50m

Database altered.

SQL> /
Enter value for a: 7
Enter value for a: red07.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m
new   1: alter database add standby logfile group 7 ('/u01/app/oracle/oradata/drs/red07.log') size 50m

Database altered.


DGMGRL>  show database drs

Database - drs

  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-ON
  Transport Lag:     4 minutes 45 seconds (computed 6 seconds ago)
  Apply Lag:         4 minutes 45 seconds (computed 6 seconds ago)
  Apply Rate:        (unknown)
  Real Time Query:   OFF
  Instance(s):
    drs

  Database Error(s):
    ORA-16766: Redo Apply is stopped

Database Status:
ERROR

## Redo apply is stopped now because we had to add standby log and for that we have to disable MRP Process

SQL> alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m;
Enter value for a: 4
Enter value for a: red04.log
old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m
new   1: alter database add standby logfile group 4 ('/u01/app/oracle/oradata/prd/red04.log') size 50m
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/prd/red04.log') size 50m
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files


SQL> recover managed standby database cancel ;
Media recovery complete.

## Now i was able to add standby redo log files


DGMGRL> show database drs

Database - drs

  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-ON
  Transport Lag:     6 minutes 47 seconds (computed 28 seconds ago)
  Apply Lag:         6 minutes 47 seconds (computed 28 seconds ago)
  Apply Rate:        (unknown)
  Real Time Query:   OFF
  Instance(s):
    drs

Database Status:
SUCCESS


DGMGRL> show configuration

Configuration - myconfig1

  Protection Mode: MaxPerformance
  Databases:
  prd - Primary database
    drs - Physical standby database 
      Warning: ORA-16857: standby disconnected from redo source for longer than specified threshold

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL> disable database drs ;
Disabled.
DGMGRL> enable database drs ;
Enabled.
DGMGRL> show configuration

Configuration - myconfig1

  Protection Mode: MaxPerformance
  Databases:
  prd - Primary database
    drs - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

## Lets do a switchover and a failover

DGMGRL> edit database prd set property FastStartFailoverTarget = 'drs' ;
Property "faststartfailovertarget" updated

DGMGRL> edit database drs set property FastStartFailoverTarget = 'prd' ;
Property "faststartfailovertarget" updated

DGMGRL> enable fast_start failover ;
Enabled.

DGMGRL> show configuration

Configuration - myconfig1

  Protection Mode: MaxPerformance
  Databases:
  prd - Primary database
    Warning: ORA-16819: fast-start failover observer not started

    drs - (*) Physical standby database 
      Warning: ORA-16819: fast-start failover observer not started

Fast-Start Failover: ENABLED

Configuration Status:
WARNING

DGMGRL> start observer
Observer started

## While doing switchover and failover i kept on investigating for a long time why invalid username password error is coming even though the password and password file is same for primary and standby, i later realized that the password was not complex because it was simple 3 letter password. and that was the reason i was getting this error. Let us see now for switchover and failover example.

DGMGRL> switchover to prd ;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prd" on database "prd"
Connecting to instance "prd"...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

        connect to instance "prd" of database "prd"

DGMGRL> switchover to prd ;
not logged on
DGMGRL> connect sys@drs
Password:
ORA-01017: invalid username/password; logon denied

## Now i made sure my password is complex

DGMGRL> connect sys@drs
Password:
Connected as SYSDBA.
DGMGRL> switchover to prd ;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prd" on database "prd"
Connecting to instance "prd"...
Connected as SYSDBA.
New primary database "prd" is opening...
Operation requires startup of instance "drs" on database "drs"
Starting instance "drs"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prd"
DGMGRL> show configuration

Configuration - myconfig1

  Protection Mode: MaxPerformance
  Databases:
  prd - Primary database
    drs - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>  switchover to drs ;
Performing switchover NOW, please wait...
Operation requires a connection to instance "drs" on database "drs"
Connecting to instance "drs"...
Connected as SYSDBA.
New primary database "drs" is opening...
Operation requires startup of instance "prd" on database "prd"
Starting instance "prd"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "drs"


## It is really important to connect to the target


DGMGRL> connect sys@drs
Password:
Connected as SYSDBA.
DGMGRL> failover to prd ;
Performing failover NOW, please wait...
Error: ORA-16600: not connected to target standby database for failover

Failed.
Unable to failover

DGMGRL> connect sys@prd 
Password:
Connected as SYSDBA.
DGMGRL> failover to prd ;
Performing failover NOW, please wait...
Failover succeeded, new primary is "prd"

## Reinstating the standby database DRS

DGMGRL> reinstate database drs ;
Reinstating database "drs", please wait...
Operation requires shutdown of instance "drs" on database "drs"
Shutting down instance "drs"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "drs" on database "drs"
Starting instance "drs"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "drs" ...
Reinstatement of database "drs" succeeded

____________

DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:          30 seconds
  Target:             (none)
  Observer:           node4.oracle.com
  Lag Limit:          30 seconds
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    (none)

DGMGRL> enable fast_start failover condition "1031" ;
Succeeded.

DGMGRL> enable fast_start failover condition "1578" ;
Succeeded.
DGMGRL>  show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:          30 seconds
  Target:             (none)
  Observer:           node4.oracle.com
  Lag Limit:          30 seconds
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    ORA-01031: insufficient privileges
    ORA-01578: ORACLE data block corrupted (file # %s, block # %s)

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = "15" ;
Property "faststartfailoverthreshold" updated

DGMGRL> enable fast_start failover ;
Enabled.

SQL> conn scott 
Enter password: 
Connected.
SQL> alter system set log_checkpoints_to_alert=true;
alter system set log_checkpoints_to_alert=true
*
ERROR at line 1:
ORA-01031: insufficient privileges

### Broker Log is given below

7:48:32.60  Monday, September 08, 2014
Initiating Fast-Start Failover to database "drs"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "drs"
17:48:36.72  Monday, September 08, 2014

6 comments:

  1. Hi Karan,
    thanks for the nice article.
    I am also configuring some here and am getting the following errors

    DGMGRL> show database 'DRDB'

    Database - DRDB

    Role: PHYSICAL STANDBY
    Intended State: APPLY-ON
    Transport Lag: (unknown)
    Apply Lag: (unknown)
    Real Time Query: OFF
    Instance(s):
    DRDB

    Database Status:
    DGM-17016: failed to retrieve status for database "DRDB"
    ORA-16525: the Data Guard broker is not yet available
    ORA-16625: cannot reach database "DRDB"

    Any ideas??

    Thanks

    ReplyDelete
  2. I have read your article, it is very informative and helpful for me.I admire the valuable information you offer in your articles. Thanks for posting it.. Compare south african brokers

    ReplyDelete
  3. When you use a genuine service, you will be able to provide instructions, share materials and choose the formatting style. Pepperstone trading review

    ReplyDelete
  4. Crypto bots are instruments used by traders to eliminate anxieties as well as feelings from their trading. These robots will allow you to run methods normally offered in hedge funds. A Crypto trading robot essentially is a software program that automatically analyzes market data and makes trading operations based on indications built with these information. Auto bitcoin bot

    ReplyDelete
  5. Eyal Nachum is a fintech guru and a director at Bruc Bond. Eyal is the architect of the software that SMEs use to do cross-border payments.

    ReplyDelete
  6. Thank you for the auspicious writeup. It in fact was a amusement account it. Look advanced to far added agreeable from you! However, how can we communicate?

    Online Black Magic Mantra Expert,
    Astrologer in Texas,
    Best Indian Psychic Reader in Texas,
    Best Horoscope Reader In Texas,

    ReplyDelete