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