Improved dgmgrl_validate_srl_all.sh

The orginal script was not able to handle multiple standbys.

This is the orginal script.

$ cat old_dgmgrl_validate_srl_all.sh
red='\033[0;31m'
green='\033[0;32m'
nc='\033[0m'
ps -ef|grep ora_[d]mon|sort -k8|awk -F "_" '{print $3}'|grep APEX18 > /tmp/sids.txt
arr=(`grep '^[A-Z].*' < /tmp/sids.txt`)
for i in "${arr[@]}"
do
  echo ======================== $i ========================
  . oraenv <<< $i >/dev/null
  stby=`dgmgrl / "show configuration" | grep "Physical standby" | awk  '{print $1;}'`
  echo $stby
  dgmgrl / "validate database verbose '$stby'" | grep -B4 "SRL"
done
exit

This is the orginal result.

$ ./old_dgmgrl_validate_srl_all.sh
======================== APEX181 ========================
APEX18_PHO APEX18_ASH
$

This is the improved script.

$ cat dgmgrl_validate_srl_all.sh
#!/bin/bash
# Created by Vyacheslav Rasskazov
# Updated by Michael Dinh : Add functionality for multiple standbys
red='\033[0;31m'
green='\033[0;32m'
nc='\033[0m'
ps -ef|grep ora_[d]mon|sort -k8 | awk -F "_" '{print $3}'|grep APEX18 > /tmp/sids.txt
arr=(`grep '^[A-Z].*' < /tmp/sids.txt`)
#set -x
for i in "${arr[@]}"
do
  . oraenv <<< $i >/dev/null
  echo ========= $i ":" $ORACLE_HOME =========
  stby=`dgmgrl / "show configuration" | grep "Physical standby" | awk  '{print $1;}'`
  # Add functionality for multiple standbys
  for x in $stby
  do
    echo "STANDBY DB_UNIQUE_NAME: " $x
    dgmgrl / "validate database verbose '$x'" | grep -B4 "SRL"
  done
done
exit
$

This is the improved result.

$ ./dgmgrl_validate_srl_all.sh     
========= APEX181 : /u02/app/oracle/product/18.0.0.0/dbhome_7 =========
STANDBY DB_UNIQUE_NAME:  APEX18_PHO

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (APEX18_ASHBURN)        (APEX18_PHO)
    1         4                       5                       Sufficient SRLs
    2         4                       5                       Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (APEX18_PHO)            (APEX18_ASHBURN)
    1         4                       5                       Sufficient SRLs
    2         4                       5                       Sufficient SRLs
STANDBY DB_UNIQUE_NAME:  APEX18_ASH

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (APEX18_ASHBURN)        (APEX18_ASH)
    1         4                       5                       Sufficient SRLs
    2         4                       5                       Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (APEX18_ASH)            (APEX18_ASHBURN)
    1         4                       5                       Sufficient SRLs
    2         4                       5                       Sufficient SRLs
$

Advertisement

DataGuard : ORA-16809: multiple warnings detected – Solution

DataGuard : ORA-16809: multiple warnings detected

This is the solution for the issues identified from post above.

--- Use SYS password vs OS authenitication.
--- Note this is RAC.

$ dgmgrl       
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Feb 15 11:55:28 2022
Version 19.13.0.0.0

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

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

Configuration - EAGLE

  Protection Mode: MaxPerformance
  Members:
  EAGLE     - Primary database
    EAGLE_PHO - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 49 seconds ago)

DGMGRL> validate network configuration for all;
Connecting to instance "EAGLE1" on database "EAGLE" ...
Connected to "EAGLE"
Checking connectivity from instance "EAGLE1" on database "EAGLE to instance "EAGLE1" on database "EAGLE_PHO"...
Succeeded.
Checking connectivity from instance "EAGLE1" on database "EAGLE to instance "EAGLE2" on database "EAGLE_PHO"...
Succeeded.
Connecting to instance "EAGLE2" on database "EAGLE" ...
Connected to "EAGLE"
Checking connectivity from instance "EAGLE2" on database "EAGLE to instance "EAGLE1" on database "EAGLE_PHO"...
Succeeded.
Checking connectivity from instance "EAGLE2" on database "EAGLE to instance "EAGLE2" on database "EAGLE_PHO"...
Succeeded.
Connecting to instance "EAGLE1" on database "EAGLE_PHO" ...
Connected to "EAGLE_PHO"
Checking connectivity from instance "EAGLE1" on database "EAGLE_PHO to instance "EAGLE1" on database "EAGLE"...
Succeeded.
Checking connectivity from instance "EAGLE1" on database "EAGLE_PHO to instance "EAGLE2" on database "EAGLE"...
Succeeded.
Connecting to instance "EAGLE2" on database "EAGLE_PHO" ...
Connected to "EAGLE_PHO"
Checking connectivity from instance "EAGLE2" on database "EAGLE_PHO to instance "EAGLE1" on database "EAGLE"...
Succeeded.
Checking connectivity from instance "EAGLE2" on database "EAGLE_PHO to instance "EAGLE2" on database "EAGLE"...
Succeeded.

Oracle Clusterware on database "EAGLE" is available for database restart.

Oracle Clusterware on database "EAGLE_PHO" is available for database restart.

DGMGRL> validate static connect identifier for all;
Oracle Clusterware on database "EAGLE" is available for database restart.

Oracle Clusterware on database "EAGLE_PHO" is available for database restart.

DGMGRL> exit
$

DataGuard : ORA-16809: multiple warnings detected

EM13c Daily Report was report contained the errors as shown below:

The Data Guard status of EAGLE_PHO is

Warning ORA-16809: multiple warnings detected for the member.

Started to investigate as shown below and what do you think is wrong?

Will give you a chance to guess and post solution later.

$ oerr ora 16809
16809, 00000, "multiple warnings detected for the member"
// *Cause:  The broker detected multiple warnings for the member.
// *Action: To get a detailed status report, check the status of the member
//          specified using either Enterprise Manager or the DGMGRL CLI SHOW
//          command.


$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Feb 15 11:33:54 2022
Version 19.13.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "EAGLE"
Connected as SYSDG.
DGMGRL> show configuration

Configuration - EAGLE

  Protection Mode: MaxPerformance
  Members:
  EAGLE     - Primary database
    EAGLE_PHO - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 29 seconds ago)

DGMGRL> validate network configuration for all;
Connecting to instance "EAGLE1" on database "EAGLE" ...
ORA-01017: invalid username/password; logon denied

Warning: Cannot connect to instance "EAGLE1" on database "EAGLE".
Connecting to instance "EAGLE2" on database "EAGLE" ...
ORA-01017: invalid username/password; logon denied

Warning: Cannot connect to instance "EAGLE2" on database "EAGLE".
Connecting to instance "EAGLE2" on database "EAGLE_PHO" ...
ORA-01017: invalid username/password; logon denied

Warning: Cannot connect to instance "EAGLE2" on database "EAGLE_PHO".
Connecting to instance "EAGLE1" on database "EAGLE_PHO" ...
ORA-01017: invalid username/password; logon denied

Warning: Cannot connect to instance "EAGLE1" on database "EAGLE_PHO".

Oracle Clusterware on database "EAGLE" is available for database restart.

Oracle Clusterware on database "EAGLE_PHO" is available for database restart.

DGMGRL> validate static connect identifier for all;
Oracle Clusterware on database "EAGLE" is available for database restart.

Oracle Clusterware on database "EAGLE_PHO" is available for database restart.

DGMGRL> exit

Flashback DataGuard Primary Database

Practicing flashback just in case it is needed during production deployment.

It’s recommended to use DGMGRL where DataGuard Broker is used.

The disadvantage is commands will be different for each environment vs simple and consistent

recover managed standby database cancel and

recover managed standby database using current logfile disconnect

--------------------------------------------------
--- CHECK FLASHBACK ENABLED:
--------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

### PRIMARY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;

CURRENT_SCN NAME                           OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
    1056941 HAWK                           READ WRITE           PRIMARY          YES

SQL>

-- Enable flashback if required:
alter database flashback on;
select current_scn,name,open_mode,database_role,flashback_on from v$database;

### STANDBY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;

CURRENT_SCN NAME                           OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
    1037296 HAWK                           MOUNTED              PHYSICAL STANDBY YES

SQL>

-- Enable flashback if required:
recover managed standby database cancel; 
alter database flashback on;
select current_scn,name,open_mode,database_role,flashback_on from v$database;

--------------------------------------------------
--- CREATE GRP:
--------------------------------------------------

### PRIMARY:
SQL> create restore point RP_TEST guarantee flashback database;

Restore point created.

SQL>

--------------------------------------------------
--- CHECK GRP:
--------------------------------------------------

Depending on database version, GRP is replicated to standby.

### PRIMARY:
SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

       SCN NAME                           GUA
---------- ------------------------------ ---
   1057112 RP_TEST                        YES

SQL>

### STANDBY:
SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

no rows selected

SQL>

##################################################
### FLASHBACK TO RESTORE POINT:
##################################################

### Flashback PRIMARY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;

CURRENT_SCN NAME                           OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
    1057767 HAWK                           READ WRITE           PRIMARY          YES

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

SQL> startup mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             469765280 bytes
Database Buffers         1124073472 bytes
Redo Buffers                7319552 bytes
Database mounted.

SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

       SCN NAME                           GUA
---------- ------------------------------ ---
   1057112 RP_TEST                        YES

SQL> flashback database to restore point RP_TEST;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL>

### Flashback STANDBY:
SQL> recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active

--- (USE PRIMARY v$restore_point.SCN)
SQL> flashback standby database to scn &scn;
Enter value for scn: 1057112
old   1: flashback standby database to scn &scn
new   1: flashback standby database to scn 1057112

Flashback complete.

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

--------------------------------------------------
--- Check DG using dgmgrl:
--------------------------------------------------

DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Databases:
    hawk      - Primary database
    hawk_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawk

Database - hawk

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

Database Status:
SUCCESS

DGMGRL> show database hawk_stby

Database - hawk_stby

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 12 seconds ago)
  Apply Lag:       0 seconds (computed 12 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: OFF
  Instance(s):
    hawk

Database Status:
SUCCESS

DGMGRL>

Q.E.D.

emcli dg_verify_config

I must love looking for trouble.

There are 47 RAC databases with Data Guard distributed among 7 clusters.

Being as lazy as I am, I did not want to connect to all the hosts to verify Data Guard configuration and create standby redo logs (SRL) for environments with Insufficient SRLs.

After some searching, I have found emcli dg_verify_config can be used; however, the documentation is not that great.

Currently working with Oracle Support and crossing my fingers.

$ $OMS_HOME/bin/emcli login -username=sysman
Enter password :

Login successful
$ $OMS_HOME/bin/emcli sync
Synchronized successfully

$ $OMS_HOME/bin/emcli dg_verify_config -primary_target_name="APEX18_XXXXXXX" -primary_target_type="rac_database" -verify_only
Parsing command line arguments...
Verify Data Guard Configuration procedure VERIFY_DG_CONFIG_20210520114056703 has been submitted for primary database APEX18_XXXXXXX.

$ $OMS_HOME/bin/emcli dg_verify_config -primary_target_name="APEX18_XXXXXXX" -primary_target_type="rac_database" -create_srls

The intention is to run emcli from OMS host to verify config and create srls where required.

However, there’s no documentation for how to retrieve results from verify_only at the OS level.

Seeing that I am struggling with Oracle support, Lead Database Consultant shared his implementation to verify Data Guard status before performing switchover.

Using the same concept, check for Insufficient SRLs from standby databases.

--- Prerequisite: Data Guard Broker is implemented.

--- Find Oracle Data Guard monitor process (DMON) and write to sids.txt file.
ps -ef|grep [d]mon|grep -v ASM|sort -k8 | awk -F "_" '{print $3}' > sids.txt

--- Create shell script dgmgrl_validate_srl.sh
arr=(`grep '^[A-Z].*' < sids.txt`)
for i in "${arr[@]}"
do
  echo ======================== $i ========================
  . oraenv <<< $i >/dev/null
  stby=`dgmgrl / "show configuration" | grep "Physical standby" | awk  '{print $1;}'`
  echo $stby
  dgmgrl / "validate database verbose '$stby'" | grep -B4 "Insufficient SRLs"
done


--- Run dgmgrl_validate_srl.sh
$ ./dgmgrl_validate_srl.sh
======================== ORACLE_SID ========================
XXXXXXXX_SPYBRACDR

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (XXXXXXXX_SPYBRAC)      (XXXXXXXX_SPYBRACDR)
    0         10                      0                       Insufficient SRLs
--

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (XXXXXXXX_SPYBRACDR)    (XXXXXXXX_SPYBRAC)
    0         10                      0                       Insufficient SRLs


Future Log File Groups Configuration - When current standby becomes primary.

Checking is different from fixing as 50%+ of RAC databases have issues with Insufficient SRLs.

Currently both processes (shell script and emcli) are deficient since only standby databases are checked.

When there is switchover or failover for primary databases, then check will have to be performed yet again.

At least, using emcli dg_verify_config -create_srls, does simplify the process since it can be execute from OMS host if it works.

Validate And Perform 19c Data Guard Switchover

Please click on link to open document:

Validate And Perform 19c Data Guard Switchover

19c New Feature DGMGRL validate database?

Not too long ago, I had blogged about When To Use dgmgrl / vs dgmgrl sys@tns

I believe this is New Feature for 19c (but not 100% certain) may resolved the question above?.

DEMO:
Connect using OS authentication from standby host.

ERROR:
ORA-01017: invalid username/password; logon denied

[oracle@ol7-112-dg2 ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Sep 20 14:22:13 2020
Version 19.3.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "hawk_stby"
Connected as SYSDG.
DGMGRL> validate database hawk;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Managed by Clusterware:
    hawk:  NO
    Validating static connect identifier for the primary database hawk...

ORA-01017: invalid username/password; logon denied

    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

DGMGRL> validate database hawk_stby;

  Database Role:     Physical standby database
  Primary Database:  hawk

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    hawk     :  NO
    hawk_stby:  NO
    Validating static connect identifier for the primary database hawk...

ORA-01017: invalid username/password; logon denied

    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Log Files Cleared:
    hawk Standby Redo Log Files:       Cleared
    hawk_stby Online Redo Log Files:   Not Cleared
    hawk_stby Standby Redo Log Files:  Available

DGMGRL>

DEMO:
Connect to primary using tns from standby host.

DGMGRL> connect sys/oracle@hawk
Connected to "hawk"
Connected as SYSDBA.
DGMGRL> validate database hawk;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Managed by Clusterware:
    hawk:  NO
    Validating static connect identifier for the primary database hawk...
    The static connect identifier allows for a connection to database "hawk".

DGMGRL> validate database hawk_stby;

  Database Role:     Physical standby database
  Primary Database:  hawk

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    hawk     :  NO
    hawk_stby:  NO
    Validating static connect identifier for the primary database hawk...
    The static connect identifier allows for a connection to database "hawk".

  Log Files Cleared:
    hawk Standby Redo Log Files:       Cleared
    hawk_stby Online Redo Log Files:   Not Cleared
    hawk_stby Standby Redo Log Files:  Available

DGMGRL>

This will at least address one example for when to use TNS vs OS authentication for DGMGRL.

Monitoring LAG Using DGMGRL Is Nice And Needs Improvements

On November 2, 2017, I had blogged about Monitoring Standby – SQLPlus or DGMGRL

Since the post, I do not recall using dgmgrl much for monitoring lag.

Almost 3 years later and 19c, let’s revisit the topic.

Here is what monitoring lag looks like from SQLPlus.
Notice BLOCK# increased which mean transfer is working.

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589                         1        1 LGWR         RFS       RECEIVING          175     8540      0
9059                         1        1 N/A          MRP0      APPLYING_LOG       175     8540      0

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589                         1        1 LGWR         RFS       RECEIVING          175     8554      0
9059                         1        1 N/A          MRP0      APPLYING_LOG       175     8554      0

SQL> 

For 19c, show configuration lag will provide info on lag and is knowing Lag is 0 seconds good enough?

[oracle@ol7-112-dg2 sql]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Sep 19 12:50:58 2020
Version 19.3.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "hawk_stby"
Connected as SYSDG.
DGMGRL> show configuration lag

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 9 seconds ago)
                Apply Lag:          0 seconds (computed 9 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 20 seconds ago)

DGMGRL> show configuration lag verbose

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 12 seconds ago)
                Apply Lag:          0 seconds (computed 12 seconds ago)

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = ''

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS
DGMGRL>

Using SendQEntries shows LOG_SEQ but RecvQEntries does not.

DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)

DGMGRL> show database hawk SendQEntries
PRIMARY_SEND_QUEUE
        STANDBY_NAME       STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)
                          CURRENT       1047346434                1                  175  09/19/2020 12:32:29                                   2984164                                 23586

DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)

DGMGRL>

Disable apply and compare differences between SQLPlus and DGMGRL.

DGMGRL> edit database hawk_stby set state=APPLY-OFF
> ;
Succeeded.
DGMGRL> show database hawk_stby

Database - hawk_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-OFF
  Transport Lag:      0 seconds (computed 8 seconds ago)
  Apply Lag:          0 seconds (computed 8 seconds ago)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    hawk

Database Status:
SUCCESS

DGMGRL>

### From Primary:

*** gv$managed_standby ***

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9030                         1        1 LNS          LNS       WRITING            180     1311      0

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9030                         1        1 LNS          LNS       WRITING            180     1314      0

SQL>

### From Standby:

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       1        1 NO             179 19-SEP-2020 13:12:25         5 39.93333
       1        1 YES            174 19-SEP-2020 12:32:29

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589                         1        1 LGWR         RFS       RECEIVING          180      284      0

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589                         1        1 LGWR         RFS       RECEIVING          180      298      0

SQL>

From DGMGRL:

DGMGRL> show configuration lag

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 6 seconds ago)
                Apply Lag:          6 minutes 48 seconds (computed 6 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 31 seconds ago)

DGMGRL> show database hawk SendQEntries
PRIMARY_SEND_QUEUE
        STANDBY_NAME       STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)
                          CURRENT       1047346434                1                  180  09/19/2020 13:12:25                                   2992416                                   270

DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)
   PARTIALLY_APPLIED       1047346434                1                  175  09/19/2020 12:32:29  09/19/2020 13:12:16              2984164              2992388            24709
         NOT_APPLIED       1047346434                1                  176  09/19/2020 13:12:16  09/19/2020 13:12:17              2992388              2992393                1
         NOT_APPLIED       1047346434                1                  177  09/19/2020 13:12:17  09/19/2020 13:12:20              2992393              2992400                2
         NOT_APPLIED       1047346434                1                  178  09/19/2020 13:12:20  09/19/2020 13:12:20              2992400              2992403                1
         NOT_APPLIED       1047346434                1                  179  09/19/2020 13:12:20  09/19/2020 13:12:25              2992403              2992416                3

DGMGRL>

APPLY-ON

DGMGRL> edit database hawk_stby set state=APPLY-ON;
Succeeded.
DGMGRL> show configuration lag

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 3 seconds ago)
                Apply Lag:          0 seconds (computed 3 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 41 seconds ago)

DGMGRL> /

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 4 seconds ago)
                Apply Lag:          0 seconds (computed 4 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 56 seconds ago)

DGMGRL> show database hawk_stby

Database - hawk_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 6 seconds ago)
  Apply Lag:          0 seconds (computed 6 seconds ago)
  Average Apply Rate: 1.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    hawk

Database Status:
SUCCESS

DGMGRL>

It would be nice if show configuration lag is able to provide some high level info frequently asked by management.

What is lag time, how many sequence is the standby behind, what is the apply rate, what is LOG_SEQ at primary and standby?

When To Use dgmgrl / vs dgmgrl sys@tns

There’s been discussion on twitter about using dgmgrl / vs dgmgrl sys@tns.

For the most part, I typically use dgmgrl sys@tns only for switch over and fail over.

Update: Should use dgmgrl sys@tns when there is an update to the database role, i.e. switch over, fail over, convert

Even Oracle’s documentation (Doc ID 278641.1) uses dgmgrl /

================================================================================
ORA-1031 Insufficient Privileges During Switchover via DGMGRL (Doc ID 740327.1)
================================================================================

CAUSE
Customer connected to database using o/s authentication from dgmgrl that resulted in ORA-01031
DGMGRL> connect /

SOLUTION
When we perform switchover using DGMGRL that requires database restarts, one must connect to dgmgrl by explicitly specifying a valid SYSDBA username/password and not using the OS Authentication.
DG Broker cannot complete a switchover using ‘connect /’ since it doesn’t have the credentials to restart the standby after shutting it down.

DGMGRL> connect sys/<password>
DGMGRL> connect sys/<pwd>@<connect string>

================================================================================
DGMGRL>CONVERT TO PHYSICAL STANDBY Fails With ORA-01031 (Doc ID 2398886.1)
================================================================================

CAUSE
The connection to the database through dgmgrl utility was not using the TNS_ALIAS
dgmgrl /

SOLUTION
Connect to the primary database through dgmgrl in order to have the convert command successfully executed:
dgmgrl sys@<primary tns_alias>

================================================================================
How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration (Doc ID 278641.1)
================================================================================

1. Disable REDO Transport on Primary
1.1 Disable log shipping using DGMGRL.

If DG broker in place it is mandatory to disable log shipping via DG broker.
DGMGRL> connect /

Still do not know when dgmgrl sys@tns is absolutely necessary.

dgmgrl / works fine for monitoring, editing configuration, and modifying transport/apply.

In summary, use what makes you comfortable.

 

12.1 Dataguard Multiple Standby Databases Switchover Using DGMGRL

Followup from How To Create New Standby Using Active Standby Duplication From Existing Standby

There are too many contents to post to blog.

Note: 12c = 12.1 and 12.2

Access 12c Dataguard Multiple Standby Databases Switchover Using DGMGRL for more details.

Thanks to CrossMyP4th on Twitter about Password file not syncing between primary and standby. As of 12.2, this is automatic.

What can happen when password files are not in sync: 12.1.0.2.0 ORA-01033: ORACLE initialization or shutdown in progress cascade standby

12.1 LOG_ARCHIVE_TRACE

A useful enhancement in 12cR2 is that password files are automatically synchronized in a Data Guard environment: Auto Sync for Password Files in #Oracle 12.2 Data Guard

12c Dataguard Switchover Best Practices using DGMGRL(Dataguard Broker Command Prompt) (Doc ID 1582837.1)

12c: Data Guard Physical Standby – Managing password files in a RAC Physical Standby (Doc ID 1984091.1)