Thinking Out Loud

February 17, 2022

DataGuard : ORA-16809: multiple warnings detected – Solution

Filed under: 19c,Dataguard,dgmgrl — mdinh @ 6:31 pm

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
$

February 15, 2022

DataGuard : ORA-16809: multiple warnings detected

Filed under: 19c,Dataguard,dgmgrl — mdinh @ 10:10 pm

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

November 6, 2021

Flashback DataGuard Primary Database

Filed under: 11g,Dataguard,dgmgrl,flashback — mdinh @ 1:26 am

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

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.

May 22, 2021

emcli dg_verify_config

Filed under: Dataguard,dgmgrl,emcli — mdinh @ 1:43 am

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.

October 22, 2020

Validate And Perform 19c Data Guard Switchover

Filed under: 19c,Dataguard,dgmgrl — mdinh @ 6:37 pm

Please click on link to open document:

Validate And Perform 19c Data Guard Switchover

September 20, 2020

19c New Feature DGMGRL validate database?

Filed under: 19c,Dataguard,dgmgrl — mdinh @ 2:35 pm

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.

September 19, 2020

Monitoring LAG Using DGMGRL Is Nice And Needs Improvements

Filed under: 19c,Dataguard,dgmgrl — mdinh @ 1:53 pm

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?

May 2, 2020

When To Use dgmgrl / vs dgmgrl sys@tns

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 3:41 pm

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.

 

May 1, 2020

12.1 Dataguard Multiple Standby Databases Switchover Using DGMGRL

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 2:28 am

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)

April 17, 2020

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

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 8:46 pm

Basically, my insanity is from my experience as Senior Tax Advisor and investment background.

Planning and organization will simplify processes in the long run.

I will share my thoughts for implementing Data Guard having two standby databases.

Data Guard configurations will be for the following hosts: LAX, PHX, SLC

  1. ORACLE_SID and db_name will be the same value.
  2. db_unique_name and service_names will be the same value.
  3. Listener will be LISTENER.
  4. TNS entries (alias) will be created for service_names and listener.
  5. Data Guard Broker is being used with Oracle Restart

Hostname: LAX (PRIMARY) : OEL6

Option #1: Using alphabet for configurations

ORACLE_SID(db_name)/db_unique_name(service_names): HAWK/HAWKA
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKA

dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKB,HAWKC'

Option #2: Using hostname for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/LAX
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_LAX

dg_config=(LAX,PHX,SLC)
fal_server='PHX,SLC'

Hostname: PHX (CURRENT STANDBY) : OEL6

Option #1: Using alphabet for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/HAWKB
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKB

dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKA,HAWKC'

Option #2: Using hostname for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/PHX
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_PHX

dg_config=(LAX,PHX,SLC)
fal_server='LAX,SLC'

Hostname: SLC (NEW STANDBY) : OEL7

Option #1: Using alphabet for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/HAWKC
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKC

dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKB,HAWKC'

Option #2: Using hostname for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/SLC
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_SLC

dg_config=(LAX,PHX,SLC)
fal_server='LAX,PHX'

I know what you are thinking. Why did I name local_listener when I specifically blogged Why Name Listener?!

I did so for consistency and to avoid having to make too many changes.

As stated from blog post, local_listener=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)) would be better since the only change required would be tns entry for listener alias.

Semi off topic: I still remembered from my martial art teacher, “Expected the unexpected.”

How does this relate to Data Guard and creating standby databases?

Always create database having db_name different from db_unique_name.

One day when there is a requirement to create standby database, environment will be configured with good naming convention. “Expect the unexpected.”

The demonstration will be using Option #2.

One downside, what happens when there is a new host added for the same location?
Use PHX2?
Me being as anal as I am, that’s going to be disturbing.
May be suffix all locations with numeric values?
You decide.

CONFIGURE ARCHIVELOG DELETION TO NONE

There are 1059 datafiles and 5 tempfiles for database duplication and don’t want archivelog to be deleted before second standby is duplicated.

Recovery Manager: Release 12.1.0.2.0 – Production on Mon Apr 13 19:06:26 2020
Finished Duplicate Db at 2020-APR-14 03:42:51

LAX: CONFIGURE ARCHIVELOG DELETION AND DB_UNIQUE_NAME

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
RMAN> CONFIGURE DB_UNIQUE_NAME 'SLC' CONNECT IDENTIFIER 'SLC';

PHX: CONFIGURE ARCHIVELOG DELETION

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

SLC : BUG 19504946 – FLASH CACHE DOESN’T WORK IN OEL7

Apply patch as required.

SLC : Create pfile and startup nomount database

Since Database Smart Flash Cache was configured at PHX (CURRENT STANDBY) and not at SLC (NEW STANDBY),
need to disable db_flash_cache_size and created dummy db_flash_cache_file for the time being until configured.

$ cat initHAWK.ora
*.db_name='HAWK'
*.db_flash_cache_size='0'
*.db_flash_cache_file='+DATA/flashfile.dat'

FOR ALL HOSTS : Verify TNS entries using tnsping from DB_HOME

tnsping LAX; tnsping PHX; tnsping SLC
tnsping LISTENER_LAX; tnsping LISTENER_PHX; tnsping LISTENER_SLC

FOR ALL HOSTS : Verify connect using TNS as SYSDBA

sqlplus sys@LAX as sysdba
sqlplus sys@PHX as sysdba
sqlplus sys@SLC as sysdba

PHX : Perform RMAN Active DB Duplication

$ rman checksyntax @ duplicate.rman

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 13 19:06:15 2020

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

RMAN> set echo on
2> connect target *
3> connect auxiliary *
4> run {
5> allocate channel c1 type disk maxopenfiles 1;
6> allocate channel c2 type disk maxopenfiles 1;
7> allocate channel c3 type disk maxopenfiles 1;
8> allocate auxiliary channel a1 type disk;
9> allocate auxiliary channel a2 type disk;
10> allocate auxiliary channel a3 type disk;
11> duplicate target database for standby from active database
12> spfile
13> parameter_value_convert 'phx','slc','PHX','SLC'
14> set db_unique_name='SLC'
15> set db_flash_cache_size='0'
16> set db_flash_cache_file='+DATA/flashfile.dat'
17> set pga_aggregate_limit='24159191040'
18> set pga_aggregate_target='11596411699'
19> set sga_max_size='32G'
20> set fal_server='LAX,PHX'
21> set local_listener='LISTENER_SLC'
22> nofilenamecheck;
23> }
24> exit
The cmdfile has no syntax errors

Recovery Manager complete.

$ nohup rman @ duplicate.rman > /tmp/duplicate_rman.log 2>&1 &

PHX : Add 2nd Standby (SLC) to DG Broker Configuration

Better not to use dgmgrl / as it may be problematic.

$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@PHX
DGMGRL> add database SLC as connect identifier is SLC;
DGMGRL> enable configuration
DGMGRL> show configuration
DGMGRL> show database SLC
DGMGRL> exit

SLC : Move spfile to ASM

$ echo "+DATA/spfile$ORACLE_SID.ora" > init$ORACLE_SID.ora

SQL> create pfile='/tmp/init@.ora' from spfile;
File created.

SQL> ! ls -l /tmp/init*
-rw-r--r-- 1 oracle oinstall 9511 Apr 14 17:33 /tmp/init$ORACLE_SID.ora

SQL> create spfile='+DATA/spfile$ORACLE_SID.ora' from pfile='/tmp/init$ORACLE_SID.ora';
File created.

SQL>

SLC : Add database to Oracle Oracle Restart

$ export ORACLE_SID=HAWK
$ export ORACLE_UNQNAME=SLC

$ srvctl add database -dbname $ORACLE_SID -db $ORACLE_UNQNAME -instance $ORACLE_SID -oraclehome $ORACLE_HOME \
  -spfile +DATA/spfile$ORACLE_SID.ora -pwfile $ORACLE_HOME/dbs/orapw$ORACLE_SID \
  -role PHYSICAL_STANDBY -diskgroup "FRA,DATA" -startoption MOUNT -stopoption IMMEDIATE

$ srvctl config database -d $ORACLE_UNQNAME
$ srvctl start database -d $ORACLE_UNQNAME
$ srvctl status database -d $ORACLE_UNQNAME -v

$ ps -ef|grep [p]mon

PHX/SLC : Review resource

crsctl stat res -t -w "TYPE = ora.database.type"
crsctl stat res -t -w "TYPE = ora.service.type"

LAX: CONFIGURE ARCHIVELOG DELETION

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

PHX/SLC: CONFIGURE ARCHIVELOG DELETION

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

REFERENCES:

What’s New in Oracle Data Guard?

New 11.2 Features Common to Redo Apply and SQL Apply
The FAL_CLIENT database initialization parameter is no longer required.

Data Guard Broker 12c Release 1 (12.1.0.2)

The following database initialization parameters are controlled by broker configurable properties.
Therefore, you should not set these parameters manually:

ARCHIVE_LAG_TARGET
DB_FILE_NAME_CONVERT
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_MAX_PROCESSES
LOG_ARCHIVE_MIN_SUCCEED_DEST
LOG_ARCHIVE_TRACE
LOG_FILE_NAME_CONVERT
STANDBY_FILE_MANAGEMENT

Configuring RMAN in a Data Guard Environment

CONFIGURE DB_UNIQUE_NAME defines a connection to a physical standby database and implicitly registers the new database.

  New standby databases are also automatically registered when RMAN connects as TARGET to a standby database for the first time.

CONFIGURE FOR DB_UNIQUE_NAME configures settings for a database in the Data Guard environment.

UPDATES:

The only consistency with Oracle is that it’s inconsistent.

*** Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)
Note: Static “_DGMGRL” entries are no longer needed as of Oracle Database 12.1.0.2 in Oracle Data Guard Broker configurations
that are managed by Oracle Restart, RAC On Node or RAC as the Broker will use the clusterware to restart an instance.

*** How to Configure Static Listener Registration for Standalone,RAC and Data Guard setup (Doc ID 2312510.1)

*** Connection to Auxiliary using connect string failed with ORA-12528 (Doc ID 419440.1)
If “Duplicate from Active Database” is used , you must connect using ‘TNS alias’ to both, Target and AUXiliary Instance.
Therefore, the AUXiliary instance should have a static listener configured like:
$ rman target sys/@ auxiliary sys/@

To test, sqlplus (sysdba user), and rman must be able to connect to AUXiliary Instance, even if AUX instance is down/nomount.

For this recommendation is to create a ‘Static Listener’ entry for the AUX instance.

The cause of the problem is that the Auxiliary Instance is started in NOMOUNT status, so it’s not registered with listener.
PMON process registers the instances with the listener when they are in MOUNT status, while instances in NOMOUNT status appear in listener as BLOCKED.

In addition, RMAN will shutdown the auxiliary instance several times during the execution.
Therefore, the connection may succeed initially, then fail with the error later in the execution.

*** Performing RMAN duplicate from standby to create a new clone (Doc ID 1665784.1)

*** Bug 11715084 : ACTIVE DUPLICATE SHOULD WORK WHEN CONNECTED TO STANDBY AS SOURCE DB
Updated : 25-Feb-2020. Fixed in Product Version 12.1

*** Data Guard Physical Standby Changing the SYS password when a broker configuration exists (Doc ID 1199943.1)

Q.E.D.

Next Page »

Create a free website or blog at WordPress.com.