Working with Fast Start Failover (FSFO) in DATA GUARD

Working with Fast Start Failover (FSFO) in DATA GUARD

Fast start failover is a feature which allows a primary database to failover to one of the target standby terminal automatically in case any error conditions becomes true

Prerequisites of FSFO

# First we will check whether flashback is enabled or not on both primary and standby

# FastStartFailoverTarget property of broker should be set to a target standby terminal

# enable fast_start failover

# start the observer

__________________________________________________________________________

Lets check on primary database

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> @role

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          READ WRITE

_____________________________________________

Lets check on standby database

SQL> @role

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

DGMGRL> connect sys@prod
Password:
Connected.
DGMGRL> enable database sby1;
Enabled.
DGMGRL>
DGMGRL>
DGMGRL> show configuration

Configuration - dgconf1

  Protection Mode: MaxPerformance
  Databases:
    prod - Primary database
    sby1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        30 seconds
  Target:           (none)
  Observer:         (none)
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

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 '1578';
Succeeded.
DGMGRL>
DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        30 seconds
  Target:           (none)
  Observer:         (none)
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

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

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


## We will simulate an insufficient privilege error and invoke fast start failover


DGMGRL> enable fast_start failover condition '1031';
Succeeded.
DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        30 seconds
  Target:           (none)
  Observer:         (none)
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

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

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


DGMGRL> show database verbose prod

Database - prod

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

  Properties:
    DGConnectIdentifier             = 'prod'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'auto'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'node4.oracle.com'
    SidName                         = 'prod'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node4.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod_DGMGRL)(INSTANCE_NAME=prod)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL>
DGMGRL> edit database prod set property 'FastStartFailoverTarget' = 'sby1';
Property "FastStartFailoverTarget" updated

DGMGRL> enable fast_start failover ;
Enabled.

Now we will simulate the failure and invoke error number 1031 (insufficient privileges)

SQL> conn scott/s
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


SQL> /
alter system set log_checkpoints_to_alert=true
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> /
alter system set log_checkpoints_to_alert=true
*
ERROR at line 1:
ORA-01031: insufficient privileges


DGMGRL>
DGMGRL> start observer
Observer started

14:08:19.81  Sunday, November 29, 2015
Initiating Fast-Start Failover to database "sby1"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "sby1"
14:08:21.11  Sunday, November 29, 2015


DGMGRL> connect sys@sby1
Password:
Connected.
DGMGRL> stop observer
Error: ORA-16636: fast-start failover target standby in error state, cannot stop observer

Failed.

DGMGRL> disable fast_start failover;
Error: ORA-16652: fast-start failover target standby database is disabled

Failed.
DGMGRL> disable fast_start failover force;
Disabled.
DGMGRL> stop observer
Done.
DGMGRL>
DGMGRL> reinstate database prod;
Reinstating database "prod", please wait...
Operation requires shutdown of instance "prod" on database "prod"
Shutting down instance "prod"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "prod" on database "prod"
Starting instance "prod"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "prod" ...
Reinstatement of database "prod" succeeded


DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        30 seconds
  Target:           (none)
  Observer:         (none)
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

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

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


You can give all oracle error numbers for fast start failover conditions except ora-600 and ora-7445

How to do a switchover in Data Guard with Data guard Broker

How to do a switchover with Data guard Broker

Below you can see i have primary database prod and standby database sby1, so in this demo we will switchover where standby (sby1) becomes primary and primary (prod) becomes standby

DGMGRL>  show configuration

Configuration - dgconf1

  Protection Mode: MaxPerformance
  Databases:
    prod - Primary database
    sby1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to sby1;
Performing switchover NOW, please wait...
New primary database "sby1" is opening...
Operation requires shutdown of instance "prod" on database "prod"
Shutting down instance "prod"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "prod" on database "prod"
Starting instance "prod"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "sby1"

DGMGRL>
DGMGRL>   show configuration

Configuration - dgconf1

  Protection Mode: MaxPerformance
  Databases:
    sby1 - Primary database
    prod - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

How to enable flashback in Standby database

How to enable flashback in Standby database

Below is the command to start MRP process

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

Below we get error because MRP is still active and applying the changes

SQL>  alter database flashback on;
 alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

Below is the command to stop the MRP process

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

So now we will be able to enable flashback

SQL>  alter database flashback on;

Database altered.

Below is the command to start the MRP process

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

Changing delaymins property of a standby through broker

Changing delaymins property of a standby through broker

Sometimes we dont want MRP process to apply the redo changes immediately to standby database, so in this case we have to use delaymins feature. Let us see how we can work with it :-

My standby database is sby1

DGMGRL> edit database sby1 set property 'DelayMins' = '2';

At primary

SQL> @role

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          READ WRITE

SQL> @logswitch

System altered.

### On standby

SQL> select archived,sequence#,applied,to_Char(completion_Time,'dd-mon-yyyy hh24:mi:ss') from v$archived_log
...
....
......

ARC  SEQUENCE# APPLIED   TO_CHAR(COMPLETION_T
--- ---------- --------- --------------------
YES         70 YES       29-nov-2015 10:02:19
YES         71 NO        29-nov-2015 10:02:36

## As we can see Applied is NO

## Now after we set delaymins to 0 then immediately MRP will apply the changes to standby

DGMGRL> edit database sby1 set property 'DelayMins' = '0';
Property "DelayMins" updated

## And now we will see applied column will have YES

SQL> @arch
..
.....
........
ARC  SEQUENCE# APPLIED   TO_CHAR(COMPLETION_T
--- ---------- --------- --------------------
YES         70 YES       29-nov-2015 10:02:19
YES         71 YES       29-nov-2015 10:02:36
YES         72 YES       29-nov-2015 10:02:55
YES         73 YES       29-nov-2015 10:06:06

## Finally we confirm and see all the properties with delaymins for our standby database sby1

DGMGRL> show database verbose sby1

Database - sby1

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

  Properties:
    DGConnectIdentifier             = 'sby1'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'auto'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'prod, sby1'
    LogFileNameConvert              = 'prod, sby1'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'node4.example.com'
    SidName                         = 'sby1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node4.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sby1_DGMGRL)(INSTANCE_NAME=sby1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

How to configure a physical standby in 11G

My primary database name will be PROD

Standby database name will be SBY1

Below are the contents of primary Pfile :-

[oracle@node4 u01]$ cat /u01/initprod.ora
prod.__db_cache_size=322961408
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod.__pga_aggregate_target=314572800
prod.__sga_target=524288000
prod.__shared_io_pool_size=0
prod.__shared_pool_size=176160768
prod.__streams_pool_size=8388608
*.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/prod/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_unique_name='prod'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.log_archive_config='dg_config=(prod,sby1)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)'
*.log_archive_dest_2='service=sby1 valid_for=(primary_role,online_logfiles) db_unique_name=sby1'
*.log_archive_dest_state_2='ENABLE'
prod.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
prod.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=314572800
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='prod'
*.sga_target=524288000
*.standby_file_management='auto'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'


Below are the contents of Standby Pfile

[oracle@node4 u01]$ cat initsby1.ora
*.audit_file_dest='/u01/app/oracle/admin/sby1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/sby1/control01.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sby1XDB)'
*.open_cursors=300
*.pga_aggregate_target=314572800
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=524288000
*.undo_tablespace='UNDOTBS1'

log_archive_Config='dg_config=(prod,sby1)'
log_archive_Dest_2='service=prod valid_for=(primary_role,online_logfiles) db_unique_name=prod'

db_unique_name=sby1
service_names=sby1
standby_File_management=auto
db_file_name_convert='prod','sby1'
log_file_name_convert='prod','sby1'
_____________________________________

Make sure you create appropriate directories which are mentioned below :-

/u01/app/oracle/admin/sby1/adump
/u01/app/oracle/oradata/sby1

Start the standby instance in no mount

SQL> create spfile from pfile='/u01/initsby1.ora';

File created.

SQL> startup force nomount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2214936 bytes
Variable Size             159384552 bytes
Database Buffers          356515840 bytes
Redo Buffers                3821568 bytes

Below are network files entries for TNS and Listener :-

[oracle@node4 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node4.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
    )
  )

CAT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node4.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cat)
    )
  )

SBY1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node4.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sby1)
    )
  )

[oracle@node4 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sby1)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = sby1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = prod)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = prod)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = sby1_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = sby1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = prod_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = prod)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node4.example.com)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle


Do the following on primary database RMAN Prompt :-

[oracle@node4 ~]$ rman target/

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Nov 28 22:14:23 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=308370143)

RMAN> connect auxiliary sys@sby1

auxiliary database Password:
connected to auxiliary database: PROD (not mounted)

RMAN>  duplicate target database for standby  from active database;

Starting Duplicate Db at 28-NOV-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprod' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwsby1'   ;
}
executing Memory Script

Starting backup at 28-NOV-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
Finished backup at 28-NOV-15

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/sby1/control01.ctl';
}
executing Memory Script

Starting backup at 28-NOV-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_prod.f tag=TAG20151128T221444 RECID=4 STAMP=896998484
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 28-NOV-15

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/sby1/temo02.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/sby1/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/sby1/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/sby1/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/sby1/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/sby1/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/sby1/tbs1a.dbf";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/sby1/undo2.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u01/app/oracle/oradata/sby1/system01.dbf"   datafile
 2 auxiliary format
 "/u01/app/oracle/oradata/sby1/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u01/app/oracle/oradata/sby1/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u01/app/oracle/oradata/sby1/users01.dbf"   datafile
 5 auxiliary format
 "/u01/app/oracle/oradata/sby1/example01.dbf"   datafile
 6 auxiliary format
 "/u01/app/oracle/oradata/sby1/tbs1a.dbf"   datafile
 7 auxiliary format
 "/u01/app/oracle/oradata/sby1/undo2.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/sby1/temo02.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 28-NOV-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/prod/system01.dbf
output file name=/u01/app/oracle/oradata/sby1/system01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/prod/sysaux01.dbf
output file name=/u01/app/oracle/oradata/sby1/sysaux01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/prod/undotbs01.dbf
output file name=/u01/app/oracle/oradata/sby1/undotbs01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/prod/example01.dbf
output file name=/u01/app/oracle/oradata/sby1/example01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/prod/tbs1a.dbf
output file name=/u01/app/oracle/oradata/sby1/tbs1a.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/prod/undo2.dbf
output file name=/u01/app/oracle/oradata/sby1/undo2.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/prod/users01.dbf
output file name=/u01/app/oracle/oradata/sby1/users01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 28-NOV-15

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=9 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/tbs1a.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=10 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/undo2.dbf
Finished Duplicate Db at 28-NOV-15

Add standby logfiles with below command :-

 alter database add standby logfile group &a ('/u01/app/oracle/oradata/sby1/&a') size 50m;

Make sure you add one extra log group on standby for standby logfiles

#Finally start the MRP Process

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

SQL> select archived,sequence#,applied,to_Char(completion_Time,'dd-mon-yyyy hh24:mi:ss') from v$archived_log

ARC  SEQUENCE# APPLIED   TO_CHAR(COMPLETION_T
--- ---------- --------- --------------------
YES         57 YES       28-nov-2015 22:17:27
YES         58 YES       28-nov-2015 22:17:28

How to configure a logical standby

First step is to configure a physical standby, below is the link of my blog post where you can see how to configure a physical standby :-

Configure a physical standby

After configuring the physical standby follow below simple steps to configure a logical standby :-

Do the following steps on standby database :-

SQL> select archived,sequence#,applied,to_Char(completion_Time,'dd-mon-yyyy hh24:mi:ss') from v$archived_log;
...
....
......

ARC  SEQUENCE# APPLIED   TO_CHAR(COMPLETION_T
--- ---------- --------- --------------------
YES         43 YES       28-nov-2015 21:15:26
YES         44 YES       28-nov-2015 21:15:28
YES         45 YES       28-nov-2015 21:36:11

Stop the MRP Process

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

Do the below step on primary database

Build a Dictionary in the Redo Data

A LogMiner dictionary must be built into the redo data so that the LogMiner component of SQL Apply can properly interpret changes it sees in the redo. As part of building the LogMiner dictionary, supplemental logging is automatically set up to log primary key and unique-constraint/index columns. The supplemental logging information ensures each update contains enough information to logically identify each row that is modified by the statement.

To build the LogMiner dictionary, issue the following statement:

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.

Do the below steps on standby database

SQL> alter database recover to logical standby sby1;

Database altered.

Shutdown the standby instance and bring it to mount state

SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2214936 bytes
Variable Size             159384552 bytes
Database Buffers          356515840 bytes
Redo Buffers                3821568 bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> alter database start logical standby apply immediate;

Database altered.

SQL> desc v$logstdby_progress
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 APPLIED_SCN                                        NUMBER
 APPLIED_TIME                                       DATE
 RESTART_SCN                                        NUMBER
 RESTART_TIME                                       DATE
 LATEST_SCN                                         NUMBER
 LATEST_TIME                                        DATE
 MINING_SCN                                         NUMBER
 MINING_TIME                                        DATE
 RESETLOGS_ID                                       NUMBER

SQL> select APPLIED_SCN,LATEST_SCN from v$logstdby_progress;

APPLIED_SCN LATEST_SCN
----------- ----------
    1294315    1294373

Data Guard Redo Transport Encryption

Data Guard Redo Transport Encryption

There are cases when redo encryption is enabled and encryption must be enabled between primary and far sync instance as well as the far sync and other standby terminal databases, This is due to the redo being unencrypted when taken off the wire at the Far Sync instance.

Advanced Security Option network encryption has been available since Oracle version 7,  For example, enabling Advanced Encryption Standard (AES) encryption algorithm requires only a few parameter changes in sqlnet.ora file. No certificate or directory setup is required and only requires restart of the database.  With Oracle Database Version 11g Release 2, network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of the Oracle Advanced Security Option and are available in all licensed editions of all supported releases of the Oracle database. See My Oracle Support Note 749947.1 for details on enabling transport encryption.

In order to enable encryption of the redo information, follow these requirements:

# Install Oracle Advanced Security option at both the primary and standby database.

# Set up the appropriate sqlnet.ora parameters as documented in the Oracle Advanced Security manual to allow Oracle Net to encrypt and integrity checksum the redo traffic shipped to the standby.

Oracle Advanced Security Transparent Data Encryption

ENCRYPTION_WALLET_LOCATION = (SOURCE =
                                  (METHOD = FILE)
                                  (METHOD_DATA =
                                  (DIRECTORY =
                                   /oracle/dbsid/admin/pdcs11/wallet)))

Oracle Advanced Security Network Encryption

#ASO Encryption

sqlnet.encryption_server=accepted
sqlnet.encryption_client=requested
sqlnet.encryption_types_server=(RC4_40)
sqlnet.encryption_types_client=(RC4_40)

Oracle Advanced Security Network Data Integrity

#ASO Checksum

sqlnet.crypto_checksum_server=requested
sqlnet.crypto_checksum_client=requested
sqlnet.crypto_checksum_types_server = (MD5)
sqlnet.crypto_checksum_types_client = (MD5)

SSL

#SSL

WALLET_LOCATION = (SOURCE=
                          (METHOD = FILE)
                          (METHOD_DATA =
                           DIRECTORY=/wallet)

SSL_CIPHER_SUITES=(SSL_DH_anon_WITH_RC4_128_MD5)
SSL_VERSION= 3
SSL_CLIENT_AUTHENTICATION=FALSE 

12C Data guard Broker ORA-01017: invalid username/password; logon denied

## While doing switchover and failover with 12C Broker 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 and that was the reason i was getting this error. Let us see now for a 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

Tracing in Oracle Data Guard

Sometime to do additional analysis and troubleshooting in oracle databases or even to provide additional information to Oracle Support we need to enable tracing, and when it comes to oracle data guard we have several tracing levels as well, like other oracle events.

Gathering trace data for database workload there are lot of ways which is also shown in my another article,  however today we will see special type of tracing which is for log archive and log apply services. To gather and generate trace data for archiving you can enable tracing on the Primary and Standby Databases. Archive Tracing can be enabled via the Initialization Parameter ‘log_archive_trace’.

There are several Levels available. To enable different levels of tracing for oracle data guard archiving set the ‘log_archive_trace’ to the Sum of the desired Levels. Remember that setting this parameter back to ‘0’ disables the Tracing. Relevant and common Levels for Log Transport Services are:


For the Primary Database:

1:            Tracks archiving of log files
2:            Tracks archive status by archive log file destination
64:         Tracks ARCn process state activity
128:       Tracks FAL server process activity
512:       Tracks LGWR redo shipping network activity
2048:     Tracks RFS/ARCn ping heartbeat

For the Standby Database:

1:            Tracks archiving of log files
2:            Tracks archive status by archive log file destination
64:          Tracks ARCn process state activity
256:       Tracks RFS Logical Client
1024:     Tracks RFS physical client
2048:     Tracks RFS/ARCn ping heartbeat

There are some more Levels which are concerning local Archiving and Log Apply Services which can be set as well if required. See below

4096 Tracks real-time apply activity

8192 Tracks Redo Apply activity (media recovery or physical standby)

16384 Tracks archive I/O buffers

32768 Tracks LogMiner dictionary archiving

You can combine tracing levels by setting the value of the LOG_ARCHIVE_TRACE parameter to the sum of the individual levels. For example, setting the parameter to 6 generates level 2 and level 4 trace output.

Note: The level numbers do not appear in the actual trace output; they are shown here for clarification only.

You can read oracle documentation for further information from following link of doc

Reference :- http://docs.oracle.com/cd/E11882_01/server.112/e41134/toc.htm

Switchover not happening in Oracle Data guard

Usually when we are trying to switchover, problems come when sessions are active, that is why session shutdown option is there in oracle, so in case some sessions are

active, make sure you wait for them to end or kill them, also do the following workaround in order to make sure the a clean switchover happens.


So like i said When sessions are active we should not try to switchover, even if we do an attempt to switch over fails with the following error message:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY * 
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
Action: Query the V$SESSION view to determine which processes are causing the error. For example:

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION WHERE TYPE = 'USER' AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT);

SID        PROCESS   PROGRAM
---------  --------  ------------------------------------------------
        7      3537  oracle@nhclone2 (CJQ0)
       10
       14
       16
       19
       21

6 rows selected.

In the above output and example, the JOB_QUEUE_PROCESSES parameter corresponds to the CJQ0 process entry.JOB_QUEUE_PROCESSES parameter is used for lot of things in

oracle like for example refresh support for materialized views or may be advanced queus using job queues for message propagation. Oracle says the job queue process is

a user process, it is counted as a SQL session that prevents switchover from taking place. The entries with no process or program information are threads started by

the job queue controller.

JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per

instance (J000, ... J999).


Verify the JOB_QUEUE_PROCESSES parameter is set using the following SQL statement:

SQL> SHOW PARAMETER JOB_QUEUE_PROCESSES;
NAME                           TYPE      VALUE
------------------------------ -------   --------------------
job_queue_processes            integer   5

Then, set the parameter to 0. For example:

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;


Some more sessions can also be active and below processes are very critical to stop in order for a clean switchover to take place.

## Change the AQ_TM_PROCESSES dynamic parameter to the value 0. The change will take effect immediately without having to restart the instance.

## Issue the emctl stop agent command from the operating system prompt.

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