I have a 2 node RAC -DG setup between 2 remote data centres. After building DataGuard between them I am now coming across stange latency stats.

set pagesize 2000;
COLUMN NAME FORMAT A30;
COLUMN value FORMAT A20;
COLUMN UNIT FORMAT A20;
COLUMN time_computed FORMAT A20;
select name
 , value
 , unit
 , time_computed
 from v$dataguard_stats;

NAME                           VALUE                UNIT                 TIME_COMPUTED
------------------------------ -------------------- -------------------- --------------------
transport lag                  +00 00:02:21         day(2) to second(0)  06/15/2010 13:12:36
 interval
apply lag                      +00 00:02:23         day(2) to second(0)  06/15/2010 13:12:36
 interval
apply finish time              +00 00:00:01.846     day(2) to second(3)  06/15/2010 13:12:36
 interval
estimated startup time         16                   second               06/15/2010 13:12:36

The lag stats range from 2 mins to upto 3 hours.

select name
 , value
 , unit
 , time_computed
 from v$dataguard_stats;
 NAME                           VALUE                UNIT                 TIME_COMPUTED
------------------------------ -------------------- -------------------- --------------------
transport lag                  +00 00:53:10         day(2) to second(0)  06/15/2010 14:03:16
 interval
apply lag                      +00 00:53:12         day(2) to second(0)  06/15/2010 14:03:16
 interval
apply finish time              +00 00:00:01.846     day(2) to second(3)  06/15/2010 14:03:16
 interval
estimated startup time         16                   second               06/15/2010 14:03:16

DGMGRL> show database 'STDBY';
Database - STDBY
 Role:            PHYSICAL STANDBY
 Intended State:  APPLY-ON
 Transport Lag:   52 minutes 21 seconds
 Apply Lag:       52 minutes 23 seconds
 Real Time Query: OFF
 Instance(s):
 STDBY1 (apply instance)
 STDBY2
Database Status:
SUCCESS

DGMGRL> show database 'PROD' statusreport;
STATUS REPORT
 INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show database 'STDBY' statusreport;
STATUS REPORT
 INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show configuration verbose;
Configuration - dataguard
 Protection Mode: MaxPerformance
 Databases:
 PROD  - Primary database
 STDBY - Physical standby database
 Properties:
 FastStartFailoverThreshold      = '30'
 OperationTimeout                = '30'
 FastStartFailoverLagLimit       = '30'
 CommunicationTimeout            = '180'
 FastStartFailoverAutoReinstate  = 'TRUE'
 FastStartFailoverPmyShutdown    = 'TRUE'
 BystandersFollowRoleChange      = 'ALL'
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> show database verbose 'PROD';
show database verbose 'STDBY';
Database - PROD
 Role:            PRIMARY
 Intended State:  TRANSPORT-ON
 Instance(s):
 PROD1
 PROD2
 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          = '5'
 LogArchiveMinSucceedDest        = '1'
 DbFileNameConvert               = ''
 LogFileNameConvert              = ''
 FastStartFailoverTarget         = ''
 InconsistentProperties          = '(monitor)'
 InconsistentLogXptProps         = '(monitor)'
 SendQEntries                    = '(monitor)'
 LogXptStatus                    = '(monitor)'
 RecvQEntries                    = '(monitor)'
 SidName(*)
 StaticConnectIdentifier(*)
 StandbyArchiveLocation(*)
 AlternateLocation(*)
 LogArchiveTrace(*)
 LogArchiveFormat(*)
 TopWaitEvents(*)
 (*) - Please check specific instance for the property value
Database Status:
SUCCESS

DGMGRL>
Database - STDBY
 Role:            PHYSICAL STANDBY
 Intended State:  APPLY-ON
 Transport Lag:   28 minutes 40 seconds
 Apply Lag:       1 hour(s) 13 minutes 18 seconds
 Real Time Query: OFF
 Instance(s):
 STDBY1 (apply instance)
 STDBY2
 Properties:
 DGConnectIdentifier             = 'STDBY'
 ObserverConnectIdentifier       = ''
 LogXptMode                      = 'ARCH'
 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          = '5'
 LogArchiveMinSucceedDest        = '1'
 DbFileNameConvert               = ''
 LogFileNameConvert              = ''
 FastStartFailoverTarget         = ''
 InconsistentProperties          = '(monitor)'
 InconsistentLogXptProps         = '(monitor)'
 SendQEntries                    = '(monitor)'
 LogXptStatus                    = '(monitor)'
 RecvQEntries                    = '(monitor)'
 SidName(*)
 StaticConnectIdentifier(*)
 StandbyArchiveLocation(*)
 AlternateLocation(*)
 LogArchiveTrace(*)
 LogArchiveFormat(*)
 TopWaitEvents(*)
 (*) - Please check specific instance for the property value
Database Status:
SUCCESS

DGMGRL> show database 'PROD' StatusReport
show database 'STDBY' StatusReport
STATUS REPORT
 INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> STATUS REPORT
 INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show database 'PROD' InconsistentProperties
show database 'STDBY' InconsistentProperties
INCONSISTENT PROPERTIES
 INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL>
INCONSISTENT PROPERTIES
 INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL> DGMGRL> show database 'PROD' InconsistentLogXptProps
show database 'STDBY' InconsistentLogXptPropsINCONSISTENT LOG TRANSPORT PROPERTIES
 INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE

DGMGRL> show database 'PROD' SendQEntries
PRIMARY_SEND_QUEUE
 STANDBY_NAME       STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs)
 STDBY     ARCHIVED        732278995                1                 4924  11/15/2010 09:29:54  11/15/2010 11:00:15        405196462        405290625            25968
 CURRENT        732278995                1                 4925  11/15/2010 11:00:15                             405290625                              6667
 CURRENT        732278995                2                 5016  11/15/2010 10:15:37                             405250777                             14538

DGMGRL> show database 'PROD' LogXptStatus
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME               STATUS
 PROD1              STDBY
 PROD2              STDBY

DGMGRL> show database 'STDBY' RecvQEntries
STANDBY_RECEIVE_QUEUE
 STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs)
 PARTIALLY_APPLIED        732278995                1                 4924  11/15/2010 09:29:54  11/15/2010 11:00:15        405196462        405290625            25968

Conclusion:
The stats above show archives are sent over immediately so there is no issue with the transport or the apply on the Standby.
The archiving to the standby is done by the Archiver, so this is only when a logswitch occures. This is why there is a ‘lag’:
Using LGWR instead of ARCH will minimize the ‘Lag’ values

1) Remove the Data Guard Broker Configuration

Using the Commandline DGMGRL
SQL> show parameter dg_broker;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
dg_broker_config_file1               string      /mnt/data/oradata/PROD/dr1PROD.dat
dg_broker_config_file2               string      /mnt/data/oradata/PROD/dr2PROD.dat
dg_broker_start                      boolean     TRUE

DGMGRL> connect sys/sysgsadm
Connected.
DGMGRL> show configuration;
Configuration – dataguard
Protection Mode: MaxPerformance
Databases:
PROD  – Primary database
PROD – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> remove configuration;
Removed configuration
DGMGRL> show configuration;
ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL

2) On the primary database set dg_broker_start=false:

SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
———————————— ———– ——————————
dg_broker_config_file1               string      ?/dbs/dr1@.dat
dg_broker_config_file2               string      ?/dbs/dr2@.dat
dg_broker_start                      boolean     TRUE

SQL> alter system set dg_broker_start=false scope=both;

System altered.

SQL> show parameter dg_broker;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
dg_broker_config_file1               string      /mnt/data/oradata/PROD/dr1PROD.dat
dg_broker_config_file2               string      /mnt/data/oradata/PROD/dr2PROD.dat
dg_broker_start                      boolean     TRUE

3) On the primary disable any archive destinations that are archiving to the standby:

SQL> column A format 999;
column B format a40;
column C format a10;
column C word_wrapped;
column D word_wrapped;
select dest_id A,destination B,status C
from v$archive_dest
where target=’STANDBY’;
A B                                        C
—- —————————————- ———-
2 dg_PROD                                VALID

Primary> show parameter log_archive_dest_state_2;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
log_archive_dest_state_2             string      ENABLE

Primary> alter system set log_archive_dest_state_2=defer scope=both;
System altered.

Primary> show parameter log_archive_dest_state_2;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
log_archive_dest_state_2             string      DEFER

 

4) on the standby set the dg_broker_start parameter to false:

SQL> show parameter dg_broker;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
dg_broker_config_file1               string      /mnt/data/oradata/PROD/dr1PROD.dat
dg_broker_config_file2               string      /mnt/data/oradata/PROD/dr2PROD.dat
dg_broker_start                      boolean     TRUE

SQL> alter system set dg_broker_start=false scope=both;
System altered.

5) On both system remove metadata files

oracle@hasunclu1:/u03/oracle/9.2.0/dbs> rm /mnt/data/oradata/PROD/dr1PROD.dat
oracle@hasunclu1:/u03/oracle/9.2.0/dbs> rm /mnt/data/oradata/PROD/dr2PROD.dat

If required you can activate standby database as below :

Standby> Shutdown abort;

Standby> startup;
ORACLE instance started.
Total System Global Area 9152860160 bytes
Fixed Size                  2234056 bytes
Variable Size            6610225464 bytes
Database Buffers         2516582400 bytes
Redo Buffers               23818240 bytes
Database mounted.
ORA-10456: cannot open standby database; media recovery session may be in progress

SQL> alter database activate standby database;
Database altered.

SQL> startup force;
ORACLE instance started.
Total System Global Area 9152860160 bytes
Fixed Size                  2234056 bytes
Variable Size            6610225464 bytes
Database Buffers         2516582400 bytes
Redo Buffers               23818240 bytes
Database mounted.
Database opened.

[oracle@Node2 PROD]$ srvctl stop database -d PROD
[oracle@Node2 PROD]$ srvctl start database -d PROD

[oracle@Node2 PROD]$ srvctl status instance -d PROD -n Node1
Instance PROD1 is running on node Node1
[oracle@Node2 PROD]$ srvctl status instance -d PROD -n Node2
Instance PROD2 is running on node Node2

The database won’t be opened by srvctl commands as it’s configured for STANDBY mode under Datauard setup.
Please change service mode using srvctl from MOUNT TO OPEN.

I generally don’t cut and paste metalink notes. But in case you are using DG , for diagnosis you will have to use 2 metalink scripts, [ID 241438.1] & [ID 241374.1]

Script to Collect Data Guard Physical Standby Diagnostic Information [ID 241438.1]
 Modified 20-APR-2011     Type SCRIPT     Status PUBLISHED      
Overview
-------
This script is intended to provide an easy method to provide information
necessary to troubleshoot Data Guard issues.

-- NAME: DG_phy_stby_diag.sql    
-- ------------------------------------------------------------------------ 
-- AUTHOR:  
--    Michael Smith - Oracle Support Services - DataServer Group
--    Copyright 2002, Oracle Corporation      
-- ------------------------------------------------------------------------ 
-- PURPOSE: 
--    This script is to be used to assist in collection information to help
--    troubeshoot Data Guard issues.
-- ------------------------------------------------------------------------ 
-- DISCLAIMER: 
--    This script is provided for educational purposes only. It is NOT  
--    supported by Oracle World Wide Technical Support. 
--    The script has been tested and appears to work as intended. 
--    You should always run new scripts on a test instance initially. 
-- ------------------------------------------------------------------------ 
-- Script output is as follows:

set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol,
'.out' spool_extension from sys.dual;
column output new_value dbname
select value || '_' output
from v$parameter where name = 'db_name';
spool dgdiag_phystby_&&dbname&&timestamp&&suffix
set lines 200
set pagesize 35
set trim on
set trims on
alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';
set feedback on
select to_char(sysdate) time from dual;

set echo on

--
-- ARCHIVER can be  (STOPPED | STARTED | FAILED) FAILED means that the archiver failed
-- to archive a -- log last time, but will try again within 5 minutes. LOG_SWITCH_WAIT
-- The ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching is waiting for. Note that 
-- if ALTER SYSTEM SWITCH LOGFILE is hung, but there is room in the current online 
-- redo log, then value is NULL 

column host_name format a20 tru
column version format a9 tru
select instance_name,host_name,version,archiver,log_switch_wait from v$instance;

-- The following select will give us the generic information about how this standby is
-- setup.  The database_role should be standby as that is what this script is intended 
-- to be ran on.  If protection_level is different than protection_mode then for some
-- reason the mode listed in protection_mode experienced a need to downgrade.  Once the
-- error condition has been corrected the protection_level should match the protection_mode
-- after the next log switch.

column ROLE format a7 tru
select name,platform_id,database_role role,log_mode,
 flashback_on flashback,protection_mode,protection_level 
from v$database;

-- Force logging is not mandatory but is recommended.  Supplemental logging should be enabled
-- on the standby if a logical standby is in the configuration. During normal 
-- operations it is acceptable for SWITCHOVER_STATUS to be SESSIONS ACTIVE or NOT ALLOWED.

column force_logging format a13 tru
column remote_archive format a14 tru
column dataguard_broker format a16 tru
select force_logging,remote_archive,supplemental_log_data_pk,supplemental_log_data_ui,
switchover_status,dataguard_broker from v$database; 

-- This query produces a list of all archive destinations and shows if they are enabled,
-- what process is servicing that destination, if the destination is local or remote,
-- and if remote what the current mount ID is. For a physical standby we should have at
-- least one remote destination that points the primary set but it should be deferred.

COLUMN destination FORMAT A35 WRAP
column process format a7
column archiver format a8
column ID format 99

select dest_id "ID",destination,status,target,
archiver,schedule,process,mountid 
from v$archive_dest;

-- If the protection mode of the standby is set to anything higher than max performance
-- then we need to make sure the remote destination that points to the primary is set
-- with the correct options else we will have issues during switchover.

select dest_id,process,transmit_mode,async_blocks,
net_timeout,delay_mins,reopen_secs,register,binding
from v$archive_dest;

-- The following select will show any errors that occured the last time an attempt to
-- archive to the destination was attempted.  If ERROR is blank and status is VALID then
-- the archive completed correctly.

column error format a55 tru
select dest_id,status,error from v$archive_dest;

-- Determine if any error conditions have been reached by querying thev$dataguard_status
-- view (view only available in 9.2.0 and above):

column message format a80
select message, timestamp
from v$dataguard_status
where severity in ('Error','Fatal')
order by timestamp;

-- The following query is ran to get the status of the SRL's on the standby.  If the
-- primary is archiving with the LGWR process and SRL's are present (in the correct
-- number and size) then we should see a group# active.

select group#,sequence#,bytes,used,archived,status from v$standby_log;

-- The above SRL's should match in number and in size with the ORL's returned below:

select group#,thread#,sequence#,bytes,archived,status from v$log;

-- Query v$managed_standby to see the status of processes involved in the
-- configuration.

select process,status,client_process,sequence#,block#,active_agents,known_agents
from v$managed_standby;

-- Verify that the last sequence# received and the last sequence# applied to standby
-- database.

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
 from v$archived_log
 where resetlogs_change#=(select resetlogs_change# from v$database)
 group by thread#) al,
 (select thread# thrd, max(sequence#) lhmax
 from v$log_history
 where first_time=(select max(first_time) from v$log_history)
 group by thread#) lh
where al.thrd = lh.thrd;

-- The V$ARCHIVE_GAP fixed view on a physical standby database only returns the next
-- gap that is currently blocking redo apply from continuing. After resolving the
-- identified gap and starting redo apply, query the V$ARCHIVE_GAP fixed view again
-- on the physical standby database to determine the next gap sequence, if there is
-- one.

select * from v$archive_gap;

-- Non-default init parameters.

set numwidth 5
column name format a30 tru
column value format a50 wra
select name, value
from v$parameter
where isdefault = 'FALSE';

spool off

I generally don’t cut and paste metalink notes. But in case you are using DG , for diagnosis you will have to use 2 metalink scripts, [ID 241438.1] & [ID 241374.1]

Script to Collect Data Guard Primary Site Diagnostic Information [ID 241374.1]
 Modified 20-APR-2011     Type SCRIPT     Status PUBLISHED      

Overview
--------
This script is intended to provide an easy method to provide information
necessary to troubleshoot Data Guard issues.

Script Notes
-------------

This script is intended to be run via sqlplus as the SYS or Internal user.

Script
-------
 - - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - -

-- NAME: dg_prim_diag.sql  (Run on PRIMARY with a LOGICAL or PHYSICAL STANDBY)
-- ------------------------------------------------------------------------ 
--    Copyright 2002, Oracle Corporation      
-- LAST UPDATED: 2/23/04
--
-- Usage: @dg_prim_diag
-- ------------------------------------------------------------------------ 
-- PURPOSE: 
--    This script is to be used to assist in collection information to help
--    troubeshoot Data Guard issues with an emphasis on Logical Standby.
-- ------------------------------------------------------------------------ 
-- DISCLAIMER: 
--    This script is provided for educational purposes only. It is NOT  
--    supported by Oracle World Wide Technical Support. 
--    The script has been tested and appears to work as intended. 
--    You should always run new scripts on a test instance initially. 
-- ------------------------------------------------------------------------ 
-- Script output is as follows:

set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol,
'.out' spool_extension from sys.dual;
column output new_value dbname
select value || '_' output
from v$parameter where name = 'db_name';
spool dg_prim_diag_&&dbname&&timestamp&&suffix
set linesize 79
set pagesize 35
set trim on
set trims on
alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';
set feedback on
select to_char(sysdate) time from dual;

set echo on

-- In the following the database_role should be primary as that is what
-- this script is intended to be run on.  If protection_level is different
-- than protection_mode then for some reason the mode listed in
-- protection_mode experienced a need to downgrade.  Once the error
-- condition has been corrected the protection_level should match the
-- protection_mode after the next log switch.

column role format a7 tru
column name format a10 wrap

select name,platform_id,database_role role,log_mode,
 flashback_on flashback,protection_mode,protection_level 
from v$database;

-- ARCHIVER can be (STOPPED | STARTED | FAILED). FAILED means that the
-- archiver failed to archive a log last time, but will try again within 5
-- minutes. LOG_SWITCH_WAIT The ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log
-- switching is waiting for.  Note that if ALTER SYSTEM SWITCH LOGFILE is
-- hung, but there is room in the current online redo log, then value is
-- NULL

column host_name format a20 tru
column version format a9 tru

select instance_name,host_name,version,archiver,log_switch_wait
from v$instance;

-- The following query give us information about catpatch.
-- This way we can tell if the procedure doesn't match the image.

select version, modified, status from dba_registry
where comp_id = 'CATPROC';

-- Force logging is not mandatory but is recommended.  Supplemental
-- logging must be enabled if the standby associated with this primary is
-- a logical standby. During normal operations it is acceptable for
-- SWITCHOVER_STATUS to be SESSIONS ACTIVE or TO STANDBY.

column force_logging format a13 tru
column remote_archive format a14 tru
column dataguard_broker format a16 tru

select force_logging,remote_archive,
 supplemental_log_data_pk,supplemental_log_data_ui,
 switchover_status,dataguard_broker
from v$database; 

-- This query produces a list of all archive destinations.  It shows if
-- they are enabled, what process is servicing that destination, if the
-- destination is local or remote, and if remote what the current mount ID
-- is.

column destination format a35 wrap
column process format a7
column archiver format a8
column ID format 99
column mid format 99

select dest_id "ID",destination,status,target,
 schedule,process,mountid  mid
from v$archive_dest order by dest_id;

-- This select will give further detail on the destinations as to what
-- options have been set.  Register indicates whether or not the archived
-- redo log is registered in the remote destination control file.

set numwidth 8
column ID format 99

select dest_id "ID",archiver,transmit_mode,affirm,async_blocks async,
 net_timeout net_time,delay_mins delay,reopen_secs reopen,
 register,binding
from v$archive_dest order by dest_id;

-- The following select will show any errors that occured the last time
-- an attempt to archive to the destination was attempted.  If ERROR is
-- blank and status is VALID then the archive completed correctly.

column error format a55 wrap

select dest_id,status,error from v$archive_dest;

-- The query below will determine if any error conditions have been
-- reached by querying the v$dataguard_status view (view only available in
-- 9.2.0 and above):

column message format a80

select message, timestamp
from v$dataguard_status
where severity in ('Error','Fatal')
order by timestamp;

-- The following query will determine the current sequence number
-- and the last sequence archived.  If you are remotely archiving
-- using the LGWR process then the archived sequence should be one
-- higher than the current sequence.  If remotely archiving using the
-- ARCH process then the archived sequence should be equal to the
-- current sequence.  The applied sequence information is updated at
-- log switch time.

select ads.dest_id,max(sequence#) "Current Sequence",
 max(log_sequence) "Last Archived"
 from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
 where ad.dest_id=al.dest_id
 and al.dest_id=ads.dest_id
 and al.resetlogs_change#=(select max(resetlogs_change#) from v$archived_log )
 group by ads.dest_id;

-- The following select will attempt to gather as much information as
-- possible from the standby.  SRLs are not supported with Logical Standby
-- until Version 10.1.

set numwidth 8
column ID format 99
column "SRLs" format 99
column Active format 99

select dest_id id,database_mode db_mode,recovery_mode,
 protection_mode,standby_logfile_count "SRLs",
 standby_logfile_active ACTIVE,
 archived_seq#
from v$archive_dest_status;

-- Query v$managed_standby to see the status of processes involved in
-- the shipping redo on this system.  Does not include processes needed to
-- apply redo.

select process,status,client_process,sequence#
from v$managed_standby;

-- The following query is run on the primary to see if SRL's have been
-- created in preparation for switchover.

select group#,sequence#,bytes from v$standby_log;

-- The above SRL's should match in number and in size with the ORL's
-- returned below:

select group#,thread#,sequence#,bytes,archived,status from v$log;

-- Non-default init parameters.

set numwidth 5
column name format a30 tru
column value format a48 wra
select name, value
from v$parameter
where isdefault = 'FALSE';

spool off

 - - - - - - - - - - - - - - - -  Script ends here  - - - - - - - - - - - - - - - -

 

 

DGMGRL> show database PROD;
Object “prod” was not found

** 11.2.0.2 – You may see errors at dgmgrl if you don’t include database name in quotes

Use : DGMGRL> show database ‘prod’;

1    Check the DG configuration status.
The status of the broker configuration is an aggregated status of all databases and instances in the broker configuration

DGMGRL> show configuration
Configuration – dataguard
Protection Mode: MaxPerformance
Databases:
stdby – Primary database
prod  – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

2   Check the database status  :

DGMGRL> show database  STDBY;
Database – prod
Role:            PHYSICAL STANDBY
Intended State:  APPLY-ON
Transport Lag:   0 seconds
Apply Lag:       1 hour(s) 3 minutes 6 seconds (Note Apply Lag of an hour)
Real Time Query: OFF
Instance(s):
PROD1 (apply instance)
PROD2
Database Status:
SUCCESS

We can also run an SQL to locate the lag between Primary and Standby

set linesize 200;
set pagesize 2000;
COLUMN NAME FORMAT A30;
COLUMN value FORMAT A20;
COLUMN UNIT FORMAT A20;
COLUMN time_computed FORMAT A20;
select name
 , value
 , unit
 , time_computed
 from v$dataguard_stats;
NAME                           VALUE                UNIT                 TIME_COMPUTED
------------------------------ -------------------- -------------------- --------------------
transport lag                  +00 00:22:37         day(2) to second(0)  06/09/2011 12:06:09
 interval
apply lag                      +00 00:22:40         day(2) to second(0)  06/09/2011 12:06:09
 interval
apply finish time              +00 00:00:00.035     day(2) to second(3)  06/09/2011 12:06:09
 interval
estimated startup time         18                   second               06/09/2011 12:06:09

3. Check the monitorable property StatusReport
To identify which database has the failure, you need to go through all of the databases in the configuration one by one.

DGMGRL> show database prod statusreport;
STATUS REPORT
INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show database stdby statusreport;
STATUS REPORT
INSTANCE_NAME   SEVERITY ERROR_TEXT

4   Check the monitorable property LogXptStatus
To identify exact log transport errors, we can use monitorable property LogXptStatus

DGMGRL> show database prod logxptstatus
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME               STATUS

5.    Check the monitorable property InconsistentProperties
If you see warning ORA-16714 reported, to identify inconsistent values for property LogArchiveTrace we can use property InconsistentProperties

DGMGRL> SHOW DATABASE prod InconsistentProperties;
INCONSISTENT PROPERTIES
INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

6.    Check the monitorable property InconsistentLogXptProps
To identify the inconsistent values for the redo transport property use monitorable property InconsistentLogXptProps:

DGMGRL> show database prod InconsistentLogXptProps
INCONSISTENT PROPERTIES
INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

This is most likely because the special service db_unique_name_DGMGRL has not been registered properly with the listener.

This is one of the requirements when configuring Data Guard broker.

From Oracle Docs :

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.

sys@ PRIMARY> show parameter db_uniqu
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      primary

sys@ SECONDARY> show parameter db_unique
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      secondary

Updated Listener.ora  : I had to add GLOBAL_DBNAME for both databases

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (SID_NAME = primary)
 (ORACLE_HOME = C:\app\patilsa\product\11.2.0\dbhome_1)
 (GLOBAL_DBNAME = primary_DGMGRL)
 )
 (SID_DESC =
 (SID_NAME = secondary)
 (ORACLE_HOME = C:\app\patilsa\product\11.2.0\dbhome_1)
 (GLOBAL_DBNAME = secondary_DGMGRL)
 )
 )

In this post, I will show you how easy it is to fail-over on standby dataguard database when primary is not available.

sys@ PRIMARY> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\app\product\11.2.0\dbhome_1\oradata
Oldest online log sequence     207
Next log sequence to archive   209
Current log sequence           209

sys@ SECONDARY> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     207
Next log sequence to archive   0
Current log sequence           209

DGMGRL> connect sys/manager@secondary
Connected.
DGMGRL> startup mount;
ORACLE instance started.
Database mounted.

DGMGRL> connect sys/manager@primary
Connected.
DGMGRL> shutdown abort;
ORACLE instance shut down.

DGMGRL> switchover to ‘secondary’;
Performing switchover NOW, please wait…
Error: ORA-01034: ORACLE not available
Error: ORA-16625: cannot reach database “primary”
Failed.
Unable to switchover, primary database is still “primary”

DGMGRL> connect sys/manager@secondary
Connected.
DGMGRL> failover to secondary;
Performing failover NOW, please wait…
Failover succeeded, new primary is “secondary”
DGMGRL> show configuration;
Configuration – sample
Protection Mode: MaxPerformance
Databases:
secondary – Primary database
primary   – Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
fast-Start Failover: DISABLED
Configuration Status: SUCCESS

Please note Secondary which is now a PRIMARY Database , it’s ARCHIVE log sequence is reset to 1

sys@ SECONDARY> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

If you try and start Primary database (failed over) it won’t be opened by dgmgrl , alrtlog should have following error message

Data Guard: version check completed
Data Guard determines a failover has occurred – this is no longer a primary database
ORA-16649 signalled during: alter database open…

My DGMGRL configuration is returning warnings as below.

DGMGRL> show database primary;
Database – primary
Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
primary
Database Warning(s):
ORA-16789: standby redo logs not configured

Database Status:
WARNING

sys@ PRIMARY> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
no rows selected

Let’s go ahead & add Standby Redo Log files at primary

sys@ PRIMARY> select bytes from v$log;
BYTES
———-
52428800
52428800
52428800
3 rows selected.

sys@ PRIMARY> select group#, member from v$logfile;
GROUP#                    MEMBER
——————————————————————————–
1     C:\APP\PATILSA\ORADATA\PRIMARY\REDO01.LOG
2    C:\APP\PATILSA\ORADATA\PRIMARY\REDO02.LOG
3    C:\APP\PATILSA\ORADATA\PRIMARY\REDO03.LOG
3 rows selected.

sys@ PRIMARY> ALTER DATABASE ADD STANDBY LOGFILE GROUP 1 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 1 SIZE 50M
*
ERROR at line 1:
ORA-01184: logfile group 1 already exists

My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:

sys@ PRIMARY> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
Database altered.
sys@ PRIMARY> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
Database altered.
sys@ PRIMARY> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
Database altered.

sys@ PRIMARY> select * from v$standby_log;
GROUP# DBID                                        THREAD#  SEQUENCE#      B
YTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
———- —————————————- ———- ———- ——
4 UNASSIGNED                                        0          0   52428800        512        512 YES UNASSIGNED             0                      0
0
5 UNASSIGNED                                        0          0   52428800        512        512 YES UNASSIGNED             0                      0
0
6 UNASSIGNED                                        0          0   52428800        512        512 YES UNASSIGNED             0                      0
0
3 rows selected.

DGMGRL> show database primary;
Database – primary
Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
primary

Database Status:
SUCCESS

If you try and switchover at this point after adding the Standby Redo logs, you will receive a ORA-16826 from the Data Guard Broker.

DGMGRL> switchover to secondary;
Performing switchover NOW, please wait…
Error: ORA-16775: target standby database in broker operation has potential dataloss
Failed.
Unable to switchover, primary database is still “primary”

DGMGRL> show configuration verbose;
Configuration – sample
Protection Mode: MaxPerformance
Databases:
primary – Primary database
secondary – Physical standby database
Warning: ORA-16826: apply service state is inconsistent with the DelayMins
property
Fast-Start Failover: DISABLED
Configuration Status:
WARNING

DGMGRL> remove database secondary;
Removed database “secondary” from the configuration

DGMGRL> ADD DATABASE ‘secondary’ AS CONNECT IDENTIFIER IS secondary MAINTAINED AS PHYSICAL;
Database “secondary” added

DGMGRL> show database secondary;
Database – secondary
Role:            PHYSICAL STANDBY
Intended State:  OFFLINE
Transport Lag:   (unknown)
Apply Lag:       (unknown)
Real Time Query: OFF
Instance(s):
secondary
Database Status:
DISABLED

DGMGRL> enable database secondary;
Enabled.

DGMGRL> show configuration;
Configuration – sample
Protection Mode: MaxPerformance
Databases:
primary   – Primary database
secondary – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> switchover to secondary;
Performing switchover NOW, please wait…
New primary database “secondary” is opening…
Operation requires shutdown of instance “primary” on database “primary”
Shutting down instance “primary”…
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “primary” on database “primary”
Starting instance “primary”…
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connectdescriptor Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
start up and mount instance “primary” of database “primary”

Don’t worry on TNS error ORA-12514: I had to start my PRIMARY database Manually  to fix it.

sys@ SECONDARY> select instance_name, status from v$instance;
INSTANCE_NAME    STATUS
—————- ————
secondary        OPEN
sys@ SECONDARY> select count(*) from tab;
COUNT(*)
———-
4731

sys@ SECONDARY> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     129
Next log sequence to archive   131
Current log sequence           131

sys@ PRIMARY> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
—————- ————
primary          MOUNTED

sys@ PRIMARY> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\app\patilsa\product\11.2.0\dbhome_1\oradata\pr
imary
Oldest online log sequence     131
Next log sequence to archive   0
Current log sequence           133

DGMGRL> connect sys/manager@secondary
Connected.
DGMGRL> show configuration verbose;

Configuration – sample

Protection Mode: MaxPerformance
Databases:
secondary – Primary database
primary – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> show database ‘primary’ StatusReport;
STATUS REPORT
INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show database ‘secondary’ StatusReport;
STATUS REPORT
INSTANCE_NAME   SEVERITY ERROR_TEXT

There are no errors so everything is fine now.

Generally you don’t have to set up Dataguard on a single machine. But there are occasions when you have to build test environment for destruction and this post is tuned for such opportunity.

There are couple of things you need to worry about when primary & standby are on the same box. This post will address those issues. I have created a new Database called Primary using DBCA. The standby will be addressed as “Secondar” instance here. Log and init parameter files attached.

Files Used : INITprimary.ORA , INITsecondary.ORA

1.    Check the primary database name:

SQL> select name from v$database;
NAME
———
PRIMARY

2.    For physical Dataguard we need to have a Forced logging ON . Let’s check that and enable it.

SQL> select force_logging from v$database;
FOR
—
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
—
YES

3.    Create a password file

D:\oracle\product\10.2.0\db_1\database>orapwd file=PWDbsl01lab.ora password=manager entries=5
–To verify the new password file
D:\oracle\product\10.2.0\db_1\database>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Jul 25 22:24:41 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect sys/manager as sysdba
Connected to an idle instance.

4.    Configure a Standby Redo Log

Ensure log file sizes are identical on the primary and standby database
SQL> select group#,thread#,bytes/1024/1024,members from v$log;
GROUP#    THREAD# BYTES/1024/1024    MEMBERS
———- ———- ————— ———-
1          1              50          1
2          1              50          1
3          1              50          1

Determine the appropriate number of standby redo log file groups
The minimal number should be 3+1=4

Verify related database parameters and settings
Maxlogfiles
Maxlogmembers

Create standby redo log file groups
SQL> alter database add standby logfile (‘C:\app\patilsa\oradata\primary\redo01d.log’) size 50M;
Database altered.
SQL> alter database add standby logfile (‘C:\app\patilsa\oradata\primary\redo02d.log’) size 50M;
Database altered.
SQL> alter database add standby logfile (‘C:\app\patilsa\oradata\primary\redo03d.log’) size 50M;
Database altered.

Step 5 Verify the standby redo log file groups were created
SQL> select group#,thread#,sequence#,archived,status from v$standby_log;

GROUP#    THREAD#  SEQUENCE# ARC STATUS
———- ———- ———- — ———-
4          0          0 YES UNASSIGNED
5          0          0 YES UNASSIGNED
6          0          0 YES UNASSIGNED

5.     Set Primary Database Initialization Parameters

SQL> create pfile from spfile;
File created.

–Content of initprimary.ora

primary.__db_cache_size=100663296
primary.__java_pool_size=4194304
primary.__large_pool_size=4194304
primary.__oracle_base='C:\app\patilsa'#ORACLE_BASE set from environment
primary.__pga_aggregate_target=146800640
primary.__sga_target=281018368
primary.__shared_io_pool_size=0
primary.__shared_pool_size=163577856
primary.__streams_pool_size=0
*.audit_file_dest='C:\app\patilsa\admin\primary\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='C:\app\patilsa\oradata\primary\control01.ctl','C:\app\patilsa\product\11.2.0\dbhome_1\oradata\primary\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='primary'
*.db_recovery_file_dest='C:\app\patilsa\product\11.2.0\dbhome_1\oradata'
*.db_recovery_file_dest_size=5218762752
*.dg_broker_start=TRUE
*.diagnostic_dest='C:\app\patilsa'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'
*.log_archive_format='ARC%S_%R.%T'
*.memory_target=427819008
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

SQL> create spfile from pfile;
File created.

6.    Check DB Archiving

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     58
Next log sequence to archive   60
Current log sequence           60

If primary is not in Archive mode use following commands to Enable primary DB Archiving

SQL> startup mount;
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1247900 bytes
Variable Size              83887460 bytes
Database Buffers           79691776 bytes
Redo Buffers                2945024 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.

7.    Create a Backup Copy of the Standby Database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Now Copy all data files/Redos from “”C:\app\patilsa\oradata\primary”  to “C:\app\patilsa\oradata\secondary”

8.    Create a Control File for the Standby Database at Primary

Primary> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1247900 bytes
Variable Size              83887460 bytes
Database Buffers           79691776 bytes
Redo Buffers                2945024 bytes
Database mounted.

Primary> Alter Database Create Standby Controlfile as ‘C:\app\patilsa\oradata\secondary\control01.ctl’;
Database altered.

SQL> alter database open;
Database altered.

9.    Create the parameter file for Standby Database : InitStandby.ora

secondary.__db_cache_size=247463936
secondary.__java_pool_size=4194304
secondary.__large_pool_size=4194304
secondary.__oracle_base='C:\app\patilsa'#ORACLE_BASE set from environment
secondary.__pga_aggregate_target=339738624
secondary.__sga_target=507510784
secondary.__shared_io_pool_size=0
secondary.__shared_pool_size=243269632
secondary.__streams_pool_size=0
*.audit_file_dest='C:\app\patilsa\admin\secondary\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='C:\app\patilsa\oradata\secondary\control01.ctl','C:\app\patilsa\flash_recovery_area\secondary\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='primary'
*.db_unique_name='secondary'
*.db_recovery_file_dest='C:\app\patilsa\flash_recovery_area'
*.db_recovery_file_dest_size=5218762752
*.db_unique_name='secondary'
*.diagnostic_dest='C:\app\patilsa'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=secondaryXDB)'
*.log_archive_format='ARC%S_%R.%T'
*.memory_target=847249408
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
db_file_name_convert=('C:\app\patilsa\oradata\primary' 'C:\app\patilsa\oradata\secondary')
log_file_name_convert=('C:\app\patilsa\oradata\primary' 'C:\app\patilsa\oradata\secondary')
FAL_SERVER=primary
FAL_CLIENT=secondary
STANDBY_FILE_MANAGEMENT=auto 

*.db_name=’primary’ at secondary & *.db_unique_name=’secondary’ # If you don’t set this parameter the standby will fail to start with ORA-01103:

10.    Setup the Environment to Support the Standby Database

Create a windows-based service
D:\oracle\product\10.2.0\db_1>oradim -new -sid secondary -intpwd secondary -startmode manual
Instance created.

Create a password file
D:\oracle\product\10.2.0\db_1>set ORACLE_SID=secondar

C:\data guard\database>orapwd file=PWDsecondary.ora password=secondary entries=5

Configure listeners for the primary and standby database

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (SID_NAME = CLRExtProc)
 (ORACLE_HOME = C:\app\patilsa\product\11.2.0\dbhome_1)
 (PROGRAM = extproc)
 (ENVS = "EXTPROC_DLLS=ONLY:C:\app\patilsa\product\11.2.0\dbhome_1\bin\oraclr11.dll")
 )
 )
LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = sagar-pc)(PORT = 1521))
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
 )
 )

Create a server parameter file for a Standby database
D:\oracle\product\10.2.0\db_1\database>set oracle_sid=secondary

D:\oracle\product\10.2.0\db_1\database>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Wed Jul 26 03:11:04 2006

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

SQL> connect sys/secondary as sysdba
Connected to an idle instance.

SQL> create spfile from pfile;
File created.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      C:\APP\PATILSA\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESECONDARY.ORA

11.    Mount the standby DB

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             281018832 bytes
Database Buffers          247463936 bytes
Redo Buffers                5804032 bytes
SQL> alter database mount standby database;
Database altered.

12.    Start Redo Apply

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> show parameter dump

NAME                                 TYPE        VALUE
———————————— ———– ——————————
background_core_dump                 string      partial
background_dump_dest string      c:\app\patilsa\diag\rdbms\secondary\secondary\trace
core_dump_dest                       string      c:\app\patilsa\diag\rdbms\secondary\secondary\cdump
max_dump_file_size                   string      unlimited
shadow_core_dump                     string      none
user_dump_dest                       string      c:\app\patilsa\diag\rdbms\secondary\secondary\trace

13.    Verify the status of primary and standby database

D:\oracle\product\10.2.0\db_1\database>set oracle_sid=primary
SQL> connect / as sysdba
Connected.

SQL> select instance_name, status from v$instance;
INSTANCE_NAME    STATUS
—————- ————
primary      OPEN

D:\oracle\product\10.2.0\db_1\database>set oracle_sid=secondar

SQL> select instance_name,status from v$instance;
INSTANCE_NAME    STATUS
—————- ————
secondar    MOUNTED
Both instance status are OK

Let’s configure a Dataguard BROKER utility for this configuration

• In order to get Dataguard Broker working make sure you configure a primary & Sandby with spfile
• Also make sure password file is same for both systems

sys@ PRIMARY>CREATE SPFILE FROM PFILE;
sys@ PRIMARY>SHUTDOWN IMMEDIATE
sys@ PRIMARY>STARTUP
sys@ Secondary>STARTUP MOUNT

To get a clean start, we have to remove all “evidence” left over from possible failed attempts earlier

1. Make sure the listener is configured with a special entry for DGMGRL
2. CleanUp archive log destination on the STANDBY database:  [ALTER SYSTEM SET log_archive_dest_1=”;]
3. Stop DG broker process (DMON) on both, primary and standby database: [ALTER SYSTEM SET dg_broker_start=FALSE SCOPE=spfile SID=’*’;]
4. Delete any existing DG broker configuration files from the following locations:

$ORACLE_BASE/admin/<db_unique_name> or
$ORACLE_HOME/dbs, namely dr1<db_unique_name>.dat and dr2<db_unique_name>.dat

5. Start the DG broker process (DMON) again on both, the primary and standby databases:
ALTER SYSTEM SET dg_broker_start=TRUE  SCOPE=spfile;

Make sure it is running – check output of SHOW PARAMETER DG

sys@ PRIMARY> ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=spfile ;
System altered.
sys@ PRIMARY> shutdown abort;
ORACLE instance shut down.
sys@ PRIMARY> startup;
ORACLE instance started.
Total System Global Area  426852352 bytes
Fixed Size                  1375060 bytes
Variable Size             318768300 bytes
Database Buffers          100663296 bytes
Redo Buffers                6045696 bytes
Database mounted.
Database opened.
sys@ PRIMARY> SHOW PARAMETER DG
NAME                                 TYPE        VALUE
———————————— ———– ——————————
dg_broker_start                      boolean     TRUE
sys@ PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Documents and Settings\patilsa>dgmgrl
DGMGRL> connect sys/manager@primary
Connected.
DGMGRL> CREATE CONFIGURATION ‘sample’ AS PRIMARY DATABASE IS ‘primary’ CONNECT IDENTIFIER IS primary;
Configuration “sample” created with primary database “primary”
DGMGRL> show  configuration
Configuration – sample
Protection Mode: MaxPerformance
Databases:
primary – Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

DGMGRL> ADD DATABASE ‘secondary’ AS CONNECT IDENTIFIER IS secondary MAINTAINED A
S PHYSICAL;
Error: ORA-16796: one or more properties could not be imported from the database
Failed.

ORA-16796 as Standby database was not started in Recovery mode. I had to run following commands at standby.

sys@ SECONDARY> startup Mount;
sys@ SECONDARY> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

DGMGRL> ADD DATABASE ‘secondary’ AS CONNECT IDENTIFIER IS secondary MAINTAINED AS PHYSICAL;
Database “secondary” added
DGMGRL> show configuration;
Configuration – sample
Protection Mode: MaxPerformance
Databases:
primary   – Primary database
secondary – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

DGMGRL > ENABLE CONFIGURATION ;
Enabled.

DGMGRL> SHOW CONFIGURATION
Configuration – sample
Protection Mode: MaxPerformance
Databases:
primary   – Primary database
secondary – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

dataguard broker primary after Install.log ,  dataguard broker secondary after Install.log

DGMGRL> show database verbose 'primary';
Database - primary
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):   primary
  Properties:
    DGConnectIdentifier             = 'primary'
    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           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'sagar-pc'
    SidName                         = 'primary'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Sagar-pc)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=primary_DGMGRL)(INSTANCE_NAME=primary)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'C:\app\patilsa\product\11.2.0\dbhome_1\oradata\primary'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'ARC%S_%R.%T'
    TopWaitEvents                   = '(monitor)'
Database Status:
SUCCESS

DGMGRL> show database verbose 'secondary';
Database - secondary
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       19 hours 47 minutes 43 seconds
  Real Time Query: ON
  Instance(s):   secondary
  Properties:
    DGConnectIdentifier             = 'secondary'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ARCH'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '0'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'C:\app\patilsa\oradata\primary, C:\app\patilsa\oradata\secondary'
    LogFileNameConvert              = 'C:\app\patilsa\oradata\primary, C:\app\patilsa\oradata\secondary'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'sagar-pc'
    SidName                         = 'secondary'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sagar-pc)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=secondary_DGMGRL)(INSTANCE_NAME=secondary)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'ARC%S_%R.%T'
    TopWaitEvents                   = '(monitor)'
Database Status:
SUCCESS

DGMGRL> show database 'primary' StatusReport;
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show database 'secondary' StatusReport;
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

No Errors at   StatusReport means everything is fine. Let’s have a look at Archive listings on Primary and Standby

sys@ PRIMARY> alter system switch logfile;
System altered.
sys@ PRIMARY> alter system switch logfile;
System altered.
sys@ PRIMARY> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\app\patilsa\product\11.2.0\dbhome_1\oradata\primary
Oldest online log sequence     102

sys@ PRIMARY> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$
MANAGED_STANDBY;
PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1         93          1       1361
ARCH      CLOSING               1         97          1          1
ARCH      CLOSING               1         94          1          1
ARCH      CLOSING               1         95          1         11
ARCH      CLOSING               1         99          1         26
ARCH      CLOSING               1         94          1          1
ARCH      CLOSING               1         96          1          2
ARCH      CLOSING               1         98       2049        510
ARCH      CLOSING               1         98          1       2558
ARCH      CLOSING               1         96          1          2
ARCH      CLOSING               1         99          1         26
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1        100          1        136
ARCH      CLOSING               1        101          1         17
ARCH      CLOSING               1        102          1          8
ARCH      CLOSING               1        101          1         17
ARCH      CLOSING               1        102          1          8
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1        103          1          8
ARCH      CLOSING               1        103          1          8
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1         97          1          1
ARCH      CLOSING               1         95          1         11
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1         93          1       1361
30 rows selected.

sys@ SECONDARY> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM
V$MANAGED_STANDBY;
PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
MRP0 WAIT_FOR_LOG 1 104 0 0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
43 rows selected.

See tail of Broker log files on what broker does when switchover happens    : drcprimary.log & drcsecondary.log

DGMGRL> show configuration;
Configuration – sample
Protection Mode: MaxPerformance
Databases:
secondary – Primary database
primary   – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> show database ‘secondary’ StatusReport;
STATUS REPORT
INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show database ‘primary’ StatusReport;
STATUS REPORT
INSTANCE_NAME   SEVERITY ERROR_TEXT

Let’s carry another switchover to make sure it’s all OK.

I was looking for a NAGIOS monitoring script which will list the archive gap between Primary and 2 of my Standby databases.

http://www.oracledbasupport.co.uk/wp-content/uploads/2010/09/dg_archive_gap_listing-1.txt

When compiled the script will return results as below

[oracle@scripts]$ ./dg_archive_gap.sh
Primary_Arc_No=61077 but Stby_Arc_No=61066

[oracle@scripts]$ ./dg_archive_gap.sh
Primary_Arc_No=61088 but Stby_Arc_No=61047
Stby_Arc_No=61067 but Stby_Apply_No=61047
Dr_Arc_No=61067 but Dr_Apply_No=61047

If you have multiple standby databases , please make sure you Add SQL stmts with “Dest_Id=X” , here 1 (Primary), 2 (Standby) & 3 (DR standby)

#!/bin/bash
#set Oracle environment for Sql*Plus
export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_SID=prod
export PATH=$PATH:$ORACLE_HOME/bin

#set working directory. script is located here..
cd /home/oracle/scripts

#Problem statement is constructed in message variable
MESSAGE=””

#hostname of the primary DB.. used in messages..
HOST_NAME=`hostname`

#Get Data guard information to Unix shell variables…

MESSAGE=`
sqlplus -s sys/sys as sysdba <<EOSQL
set pages 0 feed off
set serverout on size 1000000;
Var Rc Number;
Declare
Local_Arc             Number:=0;
Stby_Arc              Number:=0;
Dr_Arc                Number:=0;
Stby_Apply            Number:=0;
Dr_Apply              Number:=0;
Msg                   Varchar2(4000):=”;
Begin
:Rc:=0;
Select Archived_Seq# Into Local_Arc
From V\\$Archive_Dest_Status Where Dest_Id=1;
Select Archived_Seq#, Applied_Seq#
Into Stby_Arc, Stby_Apply
From V\\$Archive_Dest_Status Where Dest_Id=2;
Select Archived_Seq#, Applied_Seq#
Into Dr_Arc, Dr_Apply
From V\\$Archive_Dest_Status Where Dest_Id=3;

If Local_Arc > Stby_Arc + 10 Then
Msg:=Msg||’Primary_Arc_No=’||Local_Arc||’ but Stby_Arc_No=’||Stby_Arc||Chr(10) || ‘–‘;
:Rc:=1;
End If;

If Stby_Arc > Stby_Apply + 10 Then
Msg:=Msg||’Stby_Arc_No=’||Stby_Arc||’ but Stby_Apply_No=’||Stby_Apply||Chr(10) || ‘–‘;
:Rc:=1;
End If;

If :Rc != 0 Then
Dbms_Output.Put_Line(Msg);
End If;

If Dr_Arc > Dr_Apply + 10 Then
Msg:=Msg||’Dr_Arc_No=’||Dr_Arc||’ but Dr_Apply_No=’||Dr_Apply||Chr(10)|| ‘–‘;
:Rc:=1;
End If;

If :Rc != 0 Then
Dbms_Output.Put_Line(Msg);
End If;
End;
/
exit :rc
EOSQL`
echo $MESSAGE
if [ $? -ne 0 ]; then
MESSAGE=”Error on $HOST_NAME Standby -log APPLY- service!\n${MESSAGE}\nThis problem may cause the archive directories to get full!!!\n\n”
echo -e $MESSAGE
fi

The following represents the Oracle recommended method for automating database startup and shutdown of Oracle 10g instances.

Once the instance is created, edit the “/etc/oratab” file setting the restart flag for each instance to ‘Y’.
dev:/u01/app/oracle/product/10.2.0:N
Change N to Y
dev:/u01/app/oracle/product/10.2.0:Y

Next, create a file called “/etc/init.d/dbora” as the root user, containing the following.

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORA_HOME=/u01/app/oracle/product/10.2.0/
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo “Oracle startup: cannot start”
exit
fi

case “$1” in
‘start’)
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su – $ORA_OWNER -c “$ORA_HOME/bin/dbstart $ORA_HOME”
;;
‘stop’)
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su – $ORA_OWNER -c “$ORA_HOME/bin/dbshut $ORA_HOME”
;;
esac

set the chmod command to set the privileges to 750
chmod 750 /etc/init.d/dbora

Associate the dbora service with the appropriate run levels and set it to auto-start using the following command.
chkconfig –add dbora

The default TNS listener (LISTENER) will be started by dbshut/dbstart scripts.

If you are using customized listener name like LISTENER_TEST, LISTENER_PRE then please edit dbshut and dbstart lines as below

— At DBSHUT
$ORACLE_HOME_LISTNER/bin/lsnrctl LISTENER stop    “Change to”
$ORACLE_HOME_LISTNER/bin/lsnrctl LISTENER_TEST stop

–AT DBSTART
$ORACLE_HOME_LISTNER/bin/lsnrctl LISTENER start    “Change to”
$ORACLE_HOME_LISTNER/bin/lsnrctl LISTENER_TEST start

The relevant instances should now startup/shutdown automatically at system startup/shutdown

LOGICAL	DBA_LOGSTDBY_EVENTS	Contains information about the activity of the logical standby database system. It can be used to determine the cause of failures that occur when SQL Apply is applying redo to logical standby databases.
LOGICAL	DBA_LOGSTDBY_LOG	Shows the log files registered for logical standby databases.
LOGICAL	DBA_LOGSTDBY_NOT_UNIQUE	Identifies tables that have no primary and no non-null unique indexes.
LOGICAL	DBA_LOGSTDBY_PARAMETERS	Contains the list of parameters used by SQL apply.
LOGICAL	DBA_LOGSTDBY_PROGRESS	Describes the progress of SQL Apply on the logical standby database.
LOGICAL	DBA_LOGSTDBY_SKIP	Lists the tables that will be skipped by SQL Apply.
LOGICAL	DBA_LOGSTDBY_SKIP_TRANSACTION	Lists the skip settings chosen.
	DBA_LOGSTDBY_UNSUPPORTED	Identifies the schemas and tables (and columns in those tables) that contain unsupported datatypes. Use this view when you are preparing to create a logical standby database.
PHYSICAL/LOGICAL	V$ARCHIVE_DEST	Describes, for the current instance, all of the destinations in the Data Guard configuration, including each destination’s current value, mode, and status. Note: The information in this view does not persist across an instance shutdown.
PHYSICAL/LOGICAL	V$ARCHIVE_DEST_STATUS	Displays runtime and configuration information for the archived redo log destinations. Note: The information in this view does not persist across an instance shutdown.
PHYSICAL/LOGICAL	V$ARCHIVE_GAP	Displays information to help you identify a gap in the archived redo log files.
PHYSICAL/LOGICAL	V$ARCHIVED_LOG	Displays archive redo log information from the control file, including names of the archived redo log files.
PHYSICAL/LOGICAL	V$DATABASE	Provides database information from the control file.
PHYSICAL/LOGICAL	V$DATABASE_INCARNATION	Displays information about all database incarnations. Oracle Database creates a new incarnation whenever a database is opened with the RESETLOGS option. Records about the current and the previous incarnation are also contained in the V$DATABASE view.
PHYSICAL/LOGICAL	V$DATAFILE	Provides datafile information from the control file.
PHYSICAL/LOGICAL	V$DATAGUARD_CONFIG	Lists the unique database names defined with the DB_UNIQUE_NAME and LOG_ARCHIVE_CONFIG initialization parameters.
PHYSICAL/LOGICAL	V$DATAGUARD_STATUS	Displays and records events that would typically be triggered by any message to the alert log or server process trace files.
PHYSICAL/LOGICAL	V$LOG	Contains log file information from the online redo log files.
PHYSICAL/LOGICAL	V$LOGFILE	Contains information about the online redo log files and standby redo log files.
PHYSICAL/LOGICAL	V$LOG_HISTORY	Contains log history information from the control file.
PHYSICAL	V$MANAGED_STANDBY	Displays current status information for Oracle database processes related to physical standby databases. Note: The information in this view does not persist across an instance shutdown.
PHYSICAL/LOGICAL	V$STANDBY_LOG	Contains log file information from the standby redo log files.

The primary and standby database have the same filesystem layout, i.e. archive redo is at same place on both servers.

References: Metalink note 150214.1 – Synchronization of primary and standby database due to log transfer gap

Identify the missing log sequence numbers by running this SQL on the STANDBY database:

SQL> select thread#, low_sequence#, high_sequence#  from v$archive_gap;

Identify the filenames of the missing logs by running this SQL on the PRIMARY database:

SQL>SELECT   NAME
FROM   V$ARCHIVED_LOG
WHERE   DEST_ID = 1 AND SEQUENCE# BETWEEN &Low_Sequence AND &High_Sequence;

1. Cancel managed recovery in the STANDBY database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Copy the missing archive redo logs from primary to standby server.

This can be done using any available O/S utility. If you use ftp make sure the transfer is done in binary mode. We assume here that the redo files are going to the same location on the standby server.

3. Recover the STANDBY database

SQL> RECOVER AUTOMATIC STANDBY DATABASE;

When all logs have been applied Oracle will ask for the next, as yet unarchived, redo log. At this point cancel the recovery, e.g.

ORA-00279: change 2668131127884 generated at 12/16/2004 07:45:58 needed forthread 2
ORA-00289: suggestion : /oraarchive/standby/arc2_00000001992.log
ORA-00280: change 2668131127884 for thread 2 is in sequence #1992
ORA-00278: log file ‘/oraarchive/standby/arc2_00000001992.log’no longer needed for this recovery
ORA-00308: cannot open archived log ‘/oraarch01/smp/arch/arc2_00000001992.log’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL

4. Put the STANDBY database back into managed recovery mode:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

5. Check that the archive redo gaps have been resolved byt running thie SQL on the STANDBY database:

SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;

Why standby periodically lags during the day?
The script reports the time it took to apply the log, the size of the log, and the redo apply rate for that log.

Diff(sec) reports the actual time difference between redo logs applied on the standby.
Lag(sec) reports the time difference between when the archive log was completed on the primary and when it was applied on the standby.

If  you come across issues with script below please download it from here

rem Reports standby apply rate with lag

SELECT   TIMESTAMP,
completion_time “ArchTime”,
SEQUENCE#,
ROUND ( (blocks * block_size) / (1024 * 1024), 1) “SizeM”,
ROUND (
(TIMESTAMP
– LAG (TIMESTAMP, 1, TIMESTAMP) OVER (ORDER BY TIMESTAMP))
* 24
* 60
* 60,
1
)
“Diff(sec)”,
ROUND (
(blocks * block_size) / 1024
/ DECODE (
( (TIMESTAMP
– LAG (TIMESTAMP, 1, TIMESTAMP) OVER (ORDER BY TIMESTAMP))
* 24
* 60
* 60),
0,
1,
(TIMESTAMP
– LAG (TIMESTAMP, 1, TIMESTAMP) OVER (ORDER BY TIMESTAMP))
* 24
* 60
* 60
),
1
)
“KB/sec”,
ROUND (
(blocks * block_size) / (1024 * 1024)
/ DECODE (
( (TIMESTAMP
– LAG (TIMESTAMP, 1, TIMESTAMP) OVER (ORDER BY TIMESTAMP))
* 24
* 60
* 60),
0,
1,
(TIMESTAMP
– LAG (TIMESTAMP, 1, TIMESTAMP) OVER (ORDER BY TIMESTAMP))
* 24
* 60
* 60
),
3
)
“MB/sec”,
ROUND (
( (LEAD (TIMESTAMP, 1, TIMESTAMP) OVER (ORDER BY TIMESTAMP))
– completion_time)
* 24
* 60
* 60,
1
)
“Lag(sec)”
FROM   v$archived_log a, v$dataguard_status dgs
WHERE   a.name = REPLACE (dgs.MESSAGE, ‘Media Recovery Log’)
AND dgs.FACILITY = ‘Log Apply Services’
ORDER BY   TIMESTAMP DESC;

Another quickie SQL

@Standby> select snaphot_time,thread#,sequence#,applied_scn from v$standby_apply_snapshot;

If your log application is running slow please alter parallel_execution_message_size parameter to increase the buffer size.

From 10G DataGuard:  Set PARALLEL_EXECUTION_MESSAGE_SIZE = 4096

When using parallel media recovery or parallel standby recovery, increasing the PARALLEL_EXECUTION_MESSAGE_SIZE database parameter to 4K (4096) can improve parallel recovery by as much as 20 percent. Set this parameter on
both the primary and standby databases in preparation for switchover operations. Increasing this parameter requires more memory from the shared pool by each parallel execution slave process.

The PARALLEL_EXECUTION_MESSAGE_SIZE parameter is also used by parallel query operations and should be tested with any parallel query operations to ensure there is sufficient memory on the system. A large number of parallel query
slaves on a 32-bit installation may reach memory limits and prohibit increasing the PARALLEL_EXECUTION_MESSAGE_SIZE from the default 2K

This process will reverse database roles in a Data Guard setup, i.e. standby database becomes primary.

It’s assumed that:

1. You already have working Data Guard setup between primary and standby
2. Archive redo is begin shipped and applied from primary to standby
3. Archive redo application is up to date between primary & standby

Requirements :

1. All applications must be shutdown
2. Switchover must be initiated on primary database before being completed on standby database
3. The primary database must be open, the standby database must be mounted and in managed recovery mode

Note
When you perform a switchover the controlfile type is converted in-place, i.e. the primary controlfile becomes a standby controlfile and vice-versa. You must be CAREFUL which database you are working on…..

On the STANDBY database

1. Check archive_lag_target

SQL> show parameters lag

NAME TYPE VALUE ———————————— ———– —————————-
archive_lag_target integer 0

If non-zero set to zero using

SQL> alter system set archive_lag_target=0 scope=both;

On the PRIMARY database

1. Shutdown RAC databases

(RAC environment only) Shutdown all but one of the primary database instances.

2. Verify it is possible to switchover

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

——————

SESSIONS ACTIVE

Switchover requires that there be only one active session (the one performing the switchover).

Documentation states the above query should return “TO STANDBY” for switchover to succeed but it – always returns “SESSIONS ACTIVE” because of the one active session – Problemo!!

The key point is to make sure there is only one user connected – SYS – check V$SESSION

3. Initiate the switchover

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

Database altered.

This converts the primary database to standby. Current controlfile is backed up to trace should you need to reverse the switchover. At this stage we temporarily have two standby databases…

4. Shutdown, restart and mount as standby

SQL> SHUTDOWN NORMAL;

ORA-01507: database not mounted <= This can be ignored

ORACLE instance shut down.

SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 920912008 bytes
Fixed Size 711896 bytes
Variable Size 285212672 bytes
Database Buffers 111554492 bytes
Redo Buffers 811008 bytes

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

5. Verify switchover status

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

——————

SESSIONS ACTIVE

According to documentation this should return “SWITCHOVER PENDING”, same catch as before, there has to be one session active to do the switchover.

6. Verify switchover status

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

——————

SESSIONS ACTIVE

According to the documentation this should return “SWITCHOVER PENDING” but I have only ever seen “SESSIONS ACTIVE”, same situation as before – there has to be one session active to do the switchover.

On the STANDBY database

6. Switch standby database to primary

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY ;

Database altered.

7. Shutdown and restart the new primary database

SQL> SHUTDOWN

ORA-01507: database not mounted <= This can be ignored

ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.
Total System Global Area 920912008 bytes
Fixed Size 711896 bytes
Variable Size 285212672 bytes
Database Buffers 111554492 bytes

Redo Buffers 811008 bytes
Database mounted.
Database opened.

The database is now the primary database.

On the STANDBY database (the original primary)

8. Start managed recovery and log apply services and set archive lag target

SQL> ALTER SYSTEM SET ARCHIVE_LAG_TARGET=900 SCOPE=BOTH;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

On the PRIMARY database (the original standby)

9. Begin archiving logs

SQL> ALTER SYSTEM ARCHIVE LOG START;

System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

On BOTH databases

10. Check the alert logs to make sure archived redo is being sent and received

The primary alert log should look like this

ARC1: Evaluating archive log 3 thread 1 sequence 30

ARC1: Archive destination LOG_ARCHIVE_DEST_2: Previously completed

ARC1: Beginning to archive log 3 thread 1 sequence 30

Creating archive destination LOG_ARCHIVE_DEST_1: ‘/u01/app/oracle/archive/arch/DATAGD_1_30.arc’

ARC1: Completed archiving log 3 thread 1 sequence 30

The standby alert log should look like this

Media Recovery Waiting for thread 1 seq# 30 (in transit)

Fri Sep 3 10:12:14 2004

Media Recovery Log  ‘/u01/app/oracle/archive/arch/DATAGD_1_30.arc