Monthly Archives: November 2018


Dataguard – convert from MaxPerformance to MaxAvailability

We just found that one of the setups was wrongly configured as MaxPerformance. Since Oracle 11.2 it is possible to increase the protection mode without restarting the primary (if going from MaxPerformance to MaxProtection you need to do in two steps, through MaxPerformance).

Here how to move from MaxPerformance to MaxAvailability in a config with DataGuard Broker (removed some lines to make it shorter):

DGMGRL> connect /
Connected.

DGMGRL> show configuration

Configuration - azores

  Protection Mode: MaxPerformance
  Databases:
    azores_site1 - Primary database
    azores_site2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Error: ORA-16654: fast-start failover is enabled

Failed.

DGMGRL> stop observer
Done.
DGMGRL> show configuration

Configuration - azores

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

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

Fast-Start Failover: ENABLED

Configuration Status:
WARNING

DGMGRL>  DISABLE FAST_START FAILOVER
Disabled.
DGMGRL> show configuration

Configuration - azores

  Protection Mode: MaxPerformance
  Databases:
    azores_site1 - Primary database
    azores_site2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
Failed.

DGMGRL> show database verbose azores_site1

Database - azores_site1

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    azores

  Properties:
    DGConnectIdentifier             = 'azores_site1.portugal'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'async'
	...
	
Database Status:
SUCCESS

DGMGRL> show database verbose azores_site2

Database - azores_site2

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 2 seconds ago)
  Apply Lag:       0 seconds (computed 2 seconds ago)
  Apply Rate:      1.84 MByte/s
  Real Time Query: OFF
  Instance(s):
    azores

  Properties:
    DGConnectIdentifier             = 'azores_site2.portugal'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'async'
	...
	
Database Status:
SUCCESS

DGMGRL> edit database azores_site2 set state=APPLY-OFF;
Succeeded.
DGMGRL> edit database azores_site2 set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database azores_site1 set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
DGMGRL> show configuration;

Configuration - azores

  Protection Mode: MaxAvailability
  Databases:
    azores_site1 - Primary database
    azores_site2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> enable fast_start failover;
Enabled.

DGMGRL> edit database azores_site2 set state=APPLY-ON;
Succeeded.

DGMGRL> show database verbose azores_site2

Database - azores_site2

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)

Database Status:
SUCCESS

DGMGRL> show configuration;

Configuration - azores

  Protection Mode: MaxAvailability
  Databases:
    azores_site1 - Primary database
    azores_site2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

Query DGMGRL silently from observer site

When the observer is “checking” several dataguard configurations, we can use this server to easily make queries on all of them, for instance using:

for db in `ps -ef | grep observer | awk -F '/' '{print $6}' | sort`; do
conn=`sed -n 1p /u00/app/oracle/admin/${db}/observer/fsfo_${db}.conf`
conndb=`echo $conn | cut -d "@" -f2`
dgmgrl -silent ${conn} "show configuration" | grep -E 'Primary'
done;

It does for every observed configuration:

  1. gets the DB name
  2. find the connection string in the config file
  3. connects to DGMGRL in silent more and runs a command