Thinking Out Loud

November 11, 2020

Troubleshooting ORA-01017 from DGMGRL validate

Filed under: 19c,Dataguard — mdinh @ 4:26 pm

I was reviewing new 19c Data Guard implementation.

DGMGRL validate is my favorite command to detect any issues.

Using validate, shows issue for one of the databases in Data Guard Configuration.

Apologizes for the funky format, since wordpress changed layout, it’s becoming more difficult to use, but I digress.

--- Here are the errors:

DGMGRL> validate network configuration for all;
ORA-01017: invalid username/password; logon denied

DGMGRL> validate static connect identifier for all;
ORA-01017: invalid username/password; logon denied

--- Check TNS from $DB_HOME:

$ cat tnsnames.ora

FALCON =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.71.242)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FALCON)
)
)

--- Connect using TNS from above SUCCEED.

$ sqlplus sys@FALCON as sysdba

--- Connect using static connect identifier FAILED.

$ sqlplus sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.71.242)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=FALCON_DGMGRL)(INSTANCE_NAME=FALCON)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))' as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 10 14:00:29 2020
Version 19.8.0.0.0

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

ORA-01017: invalid username/password; logon denied

--- Check password file from DB_HOME to find file size is different comparing to other host.
--- Create copy for password file and copy from other host to this host.
--- What's strange is if password file was an issue, then why did sqlplus sys@FALCON as sysdba succeeded?
--- Regardless, I like to keep them the same.

$ ls -l orapw*
-rw-r-----. 1 oracle oinstall 4608 Nov 10 14:11 orapwFALCON
-rw-r-----. 1 oracle oinstall 1536 Nov 9 16:45 orapwFALCON.bak

--- Check listener.ora from $GI_HOME and there's there's the problem.
ORACLE_HOME is set to GI vs DB home.

$ diff listener.ora listener.ora.bak
16c16
< (ORACLE_HOME = /u01/product/19c)
---
> (ORACLE_HOME = /u01/grid/19c)

 

Thinking out loud here.

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.

Since database was upgraded from 11.2 to 19c, existing static connect identifier was carried forward.

It might be better to remove existing configuration and create new configuration without _DGMGL; otherwise, may encounter the same issue for next upgrade to 20c.

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?

August 3, 2020

Last Time You Tested Data Guard Was?

Filed under: 19c,Dataguard — mdinh @ 12:05 am

Data Guard is like a spare tire. Don’t wait to find there is no air when you need it.

[oracle@ol7-112-dg1 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Aug 2 16:57:28 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.
DGMGRL> connect sys@hawk
Password:
Connected to "hawk"
Connected as SYSDBA.
DGMGRL> show configuration

Configuration - my_dg_config

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

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 52 seconds ago)

DGMGRL> switchover to hawk_stby
Performing switchover NOW, please wait...
Operation requires a connection to database "hawk_stby"
Connecting ...
Connected to "hawk_stby"
Connected as SYSDBA.
New primary database "hawk_stby" is opening...
Operation requires start up of instance "hawk" on database "hawk"
Starting instance "hawk"...
Connected to an idle instance.
ORACLE instance started.
Connected to "hawk"
Database mounted.
Connected to "hawk"
Switchover succeeded, new primary is "hawk_stby"
DGMGRL> show configuration

Configuration - my_dg_config

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

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 57 seconds ago)

DGMGRL> exit
[oracle@ol7-112-dg1 ~]$

==================================================

[oracle@ol7-112-dg1 upgrade19c]$ sqlplus / as sysdba @ status

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 2 16:59:45 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


Session altered.

SQL> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,FLASHBACK_ON from v$database
  2  ;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE      FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- -------------------- ------------------
HAWK      hawk                           MOUNTED              PHYSICAL STANDBY MAXIMUM PERFORMANCE  YES

SQL> ;
  1  select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,FLASHBACK_ON from v$database
  2*
SQL> 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  ;

PID                           INST   THREAD# CLIENT_P PROCESS   STATUS       SEQUENCE#    BLOCK# DELAY_MINS
------------------------ --------- --------- -------- --------- ------------ --------- --------- ----------
3118                             1         1 LGWR     RFS       RECEIVING          169      3237          0
3151                             1         1 N/A      MRP0      APPLYING_LOG       169      3237          0

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ol7-112-dg1 upgrade19c]$

==================================================

[oracle@ol7-112-dg1 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Aug 2 17:00:03 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.
DGMGRL> connect sys@hawk_stby
Password:
Connected to "hawk_stby"
Connected as SYSDBA.
DGMGRL> switchover to hawk
Performing switchover NOW, please wait...
Operation requires a connection to database "hawk"
Connecting ...
Connected to "hawk"
Connected as SYSDBA.
New primary database "hawk" is opening...
Operation requires start up of instance "hawk" on database "hawk_stby"
Starting instance "hawk"...
Connected to an idle instance.
ORACLE instance started.
Connected to "hawk_stby"
Database mounted.
Connected to "hawk_stby"
Switchover succeeded, new primary is "hawk"
DGMGRL> show configuration

Configuration - my_dg_config

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

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 59 seconds ago)

DGMGRL> exit
[oracle@ol7-112-dg1 ~]$

==================================================

[oracle@ol7-112-dg1 upgrade19c]$ sqlplus / as sysdba @ status

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 2 17:02:06 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


Session altered.

SQL> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,FLASHBACK_ON from v$database
  2  ;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE      FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- -------------------- ------------------
HAWK      hawk                           READ WRITE           PRIMARY          MAXIMUM PERFORMANCE  YES

SQL> ;
  1  select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,FLASHBACK_ON from v$database
  2*
SQL> 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  ;

PID                           INST   THREAD# CLIENT_P PROCESS   STATUS       SEQUENCE#    BLOCK# DELAY_MINS
------------------------ --------- --------- -------- --------- ------------ --------- --------- ----------
3328                             1         1 LNS      LNS       WRITING            172      3252          0

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ol7-112-dg1 upgrade19c]$

August 1, 2020

Rolling Upgrades Using Physical Standby and physru_v3.sh

Filed under: 19c,Dataguard,upgrade — mdinh @ 4:12 pm

I am not going to reiterate what is already out there; however, what is out there covers 12.1.0.2.0 vs 19.3.0.0.0.

Here is the syntax and what seems to be ambiguous is target version.

$./physru.sh <sysdba user> <primary TNS alias> <physical standby TNS alias> <primary db unique name> <physical standby db unique name> <target version> 

NOTE: This is NOT all the steps for upgrade but only applicable steps when running physru.sh.

First physru execution completed successfully.

### First physru execution:
Verifies that Data Guard Broker is disabled and FRA is configured.
Creates a guaranteed restore point
Converts the existing Physical Standby to a Logical Standby

oracle@ol7-112-dg2:hawk:/sf_working/upgrade19c
$ ./physru_v3.sh SYS hawk hawk_stby hawk hawk_stby 19.3.0.0
Please enter the sysdba password:

### Initialize script to either start over or resume execution
Jul 30 20:06:30 2020 [0-1] Identifying rdbms software version
Jul 30 20:06:31 2020 [0-1] database hawk is at version 11.2.0.4.0
Jul 30 20:06:31 2020 [0-1] database hawk_stby is at version 11.2.0.4.0
Jul 30 20:06:31 2020 [0-1] verifying fast recovery area is enabled at hawk and hawk_stby
Jul 30 20:06:31 2020 [0-1] verifying backup location at hawk and hawk_stby
Jul 30 20:06:31 2020 [0-1] verifying available flashback restore points
Jul 30 20:06:31 2020 [0-1] verifying DG Broker is disabled
Jul 30 20:06:31 2020 [0-1] looking up prior execution history
Jul 30 20:06:31 2020 [0-1] purging script execution state from database hawk
Jul 30 20:06:31 2020 [0-1] purging script execution state from database hawk_stby
Jul 30 20:06:31 2020 [0-1] starting new execution of script

### Stage 1: Backup user environment in case rolling upgrade is aborted
Jul 30 20:06:31 2020 [1-1] database hawk location for backup controlfile is /u01/app/oracle/fast_recovery_area
Jul 30 20:06:32 2020 [1-1] database hawk_stby location for backup controlfile is /u01/app/oracle/fast_recovery_area
Jul 30 20:06:32 2020 [1-1] creating restore point PRU_0000_0003 on database hawk_stby
Jul 30 20:06:32 2020 [1-1] backing up current control file on hawk_stby
Jul 30 20:06:32 2020 [1-1] created backup control file /u01/app/oracle/fast_recovery_area/PRU_0003_hawk_stby_f.f
Jul 30 20:06:32 2020 [1-1] creating restore point PRU_0000_0003 on database hawk
Jul 30 20:06:32 2020 [1-1] backing up current control file on hawk
Jul 30 20:06:32 2020 [1-1] created backup control file /u01/app/oracle/fast_recovery_area/PRU_0003_hawk_f.f

NOTE: Restore point PRU_0000_0001 and backup control file PRU_0003_hawk_stby_f.f
      can be used to restore hawk_stby back to its original state as a
      physical standby, in case the rolling upgrade operation needs to be aborted
      prior to the first switchover done in Stage 4.

### Stage 2: Create transient logical standby from existing physical standby
Jul 30 20:06:32 2020 [2-1] verifying RAC is disabled at hawk_stby
Jul 30 20:06:32 2020 [2-1] verifying database roles
Jul 30 20:06:32 2020 [2-1] verifying physical standby is mounted
Jul 30 20:06:32 2020 [2-1] verifying database protection mode
Jul 30 20:06:32 2020 [2-1] verifying transient logical standby datatype support
Jul 30 20:06:33 2020 [2-2] starting media recovery on hawk_stby
Jul 30 20:06:39 2020 [2-2] confirming media recovery is running
Jul 30 20:06:39 2020 [2-2] waiting for apply lag to fall under 30 seconds
Jul 30 20:06:52 2020 [2-2] apply lag measured at 13 seconds
Jul 30 20:06:52 2020 [2-2] stopping media recovery on hawk_stby
Jul 30 20:06:53 2020 [2-2] executing dbms_logstdby.build on database hawk
Jul 30 20:06:59 2020 [2-2] converting physical standby into transient logical standby
Jul 30 20:07:03 2020 [2-3] opening database hawk_stby
Jul 30 20:07:05 2020 [2-4] configuring transient logical standby parameters for rolling upgrade
Jul 30 20:07:05 2020 [2-4] starting logical standby on database hawk_stby
Jul 30 20:07:10 2020 [2-4] enabling log archive destination to database hawk_stby
Jul 30 20:07:11 2020 [2-4] waiting until logminer dictionary has fully loaded
Jul 30 20:07:51 2020 [2-4] dictionary load 03% complete
Jul 30 20:08:01 2020 [2-4] dictionary load 62% complete
Jul 30 20:08:11 2020 [2-4] dictionary load is complete
Jul 30 20:08:11 2020 [2-4] waiting for apply lag to fall under 30 seconds
Jul 30 20:08:15 2020 [2-4] apply lag measured at 3 seconds

NOTE: Database hawk_stby is now ready to be upgraded.  This script has left the
      database open in case you want to perform any further tasks before
      upgrading the database.  Once the upgrade is complete, the database must
      opened in READ WRITE mode before this script can be called to resume the
      rolling upgrade.

NOTE: If hawk_stby was previously a RAC database that was disabled, it may be
      reverted back to a RAC database upon completion of the rdbms upgrade.
      This can be accomplished by performing the following steps:

          1) On instance hawk, set the cluster_database parameter to TRUE.
          eg: SQL> alter system set cluster_database=true scope=spfile;

          2) Shutdown instance hawk.
          eg: SQL> shutdown abort;

          3) Startup and open all instances for database hawk_stby.
          eg: srvctl start database -d hawk_stby

oracle@ol7-112-dg2:hawk:/sf_working/upgrade19c

Second physru execution FAILED.

### Second physru execution to switchover (APPLICATION BROWNOUT):
Executes a switchover making the upgraded standby database the primary database.
Executes a flashback of the original primary database to the guaranteed restore point from step 1 and shuts it down.

[oracle@ol7-112-dg2 upgrade19c]$ ./physru_v3.sh SYS hawk hawk_stby hawk hawk_stby 19.3.0.0.0
Please enter the sysdba password:

### Initialize script to either start over or resume execution
Aug 01 01:55:56 2020 [0-1] Identifying rdbms software version
Aug 01 01:55:56 2020 [0-1] database hawk is at version 11.2.0.4.0
Aug 01 01:55:57 2020 [0-1] database hawk_stby is at version 19.0.0.0.0
Aug 01 01:56:00 2020 [0-1] verifying fast recovery area is enabled at hawk and hawk_stby
Aug 01 01:56:02 2020 [0-1] verifying backup location at hawk and hawk_stby
Aug 01 01:56:03 2020 [0-1] verifying available flashback restore points
Aug 01 01:56:04 2020 [0-1] verifying DG Broker is disabled
Aug 01 01:56:05 2020 [0-1] looking up prior execution history
Aug 01 01:56:08 2020 [0-1] last completed stage [2-4] using script version 0003
Aug 01 01:56:08 2020 [0-1] resuming execution of script

### Stage 3: Validate upgraded transient logical standby
Aug 01 01:56:09 2020 [3-1] database hawk_stby is no longer in OPEN MIGRATE mode
Aug 01 01:56:09 2020 [3-1] ERROR: hawk_stby is not at version 19.3.0.0.0
[oracle@ol7-112-dg2 upgrade19c]$

Second physru execution SUCCEEDED as 19.0.0.0.0 (base release) is used vs 19.3.0.0.0 (actual release).

[oracle@ol7-112-dg2 upgrade19c]$ ./physru_v3.sh SYS hawk hawk_stby hawk hawk_stby 19.0.0.0.0
Please enter the sysdba password:

### Initialize script to either start over or resume execution
Aug 01 02:48:40 2020 [0-1] Identifying rdbms software version
Aug 01 02:48:40 2020 [0-1] database hawk is at version 11.2.0.4.0
Aug 01 02:48:41 2020 [0-1] database hawk_stby is at version 19.0.0.0.0
Aug 01 02:48:45 2020 [0-1] verifying fast recovery area is enabled at hawk and hawk_stby
Aug 01 02:48:47 2020 [0-1] verifying backup location at hawk and hawk_stby
Aug 01 02:48:48 2020 [0-1] verifying available flashback restore points
Aug 01 02:48:49 2020 [0-1] verifying DG Broker is disabled
Aug 01 02:48:50 2020 [0-1] looking up prior execution history
Aug 01 02:48:53 2020 [0-1] last completed stage [2-4] using script version 0003
Aug 01 02:48:53 2020 [0-1] resuming execution of script

### Stage 3: Validate upgraded transient logical standby
Aug 01 02:48:54 2020 [3-1] database hawk_stby is no longer in OPEN MIGRATE mode
Aug 01 02:48:54 2020 [3-1] database hawk_stby is at version 19.0.0.0.0

### Stage 4: Switch the transient logical standby to be the new primary
Aug 01 02:48:59 2020 [4-1] waiting for hawk_stby to catch up (this could take a while)
Aug 01 02:49:00 2020 [4-1] waiting for apply lag to fall under 30 seconds
Aug 01 02:49:12 2020 [4-1] apply lag measured at 9 seconds
Aug 01 02:49:16 2020 [4-2] using fast switchover optimizations

NOTE: A switchover is about to be performed which will incur a brief outage
      of the primary database.  If you answer 'y' to the question below,
      database hawk_stby will become the new primary database, and database hawk
      will be converted into a standby in preparation for upgrade.  If you answer
      'n' to the question below, the script will exit, leaving the databases in
      their current roles.
Are you ready to proceed with the switchover? (y/n): y

Aug 01 02:49:31 2020 [4-2] continuing
Aug 01 02:49:31 2020 [4-2] switching hawk to become a logical standby
Aug 01 02:49:39 2020 [4-2] hawk is now a logical standby
Aug 01 02:49:39 2020 [4-2] waiting for standby hawk_stby to process end-of-redo from primary
Aug 01 02:49:44 2020 [4-2] switching hawk_stby to become the new primary
Aug 01 02:49:45 2020 [4-2] hawk_stby is now the new primary

### Stage 5: Flashback former primary to pre-upgrade restore point and convert to physical
Aug 01 02:49:49 2020 [5-1] shutting down database hawk
Aug 01 02:50:03 2020 [5-1] mounting database hawk
Aug 01 02:50:12 2020 [5-2] flashing back database hawk to restore point PRU_0000_0003
Aug 01 02:50:15 2020 [5-3] converting hawk into physical standby
Aug 01 02:50:17 2020 [5-4] shutting down database hawk

NOTE: Database hawk has been shutdown, and is now ready to be started
      using the newer version Oracle binary.  This script requires the
      database to be mounted (on all active instances, if RAC) before calling
      this script to resume the rolling upgrade.

[oracle@ol7-112-dg2 upgrade19c]$

Third and Final physru execution completed successfully.

### Execute physru for the third and final time. 
Start redo apply
Prompt whether to switch back to original configuration
Remove guaranteed restore points

[oracle@ol7-112-dg2 upgrade19c]$ ./physru_v3.sh SYS hawk hawk_stby hawk hawk_stby 19.0.0.0.0
Please enter the sysdba password:

### Initialize script to either start over or resume execution
Aug 01 02:48:40 2020 [0-1] Identifying rdbms software version
Aug 01 02:48:40 2020 [0-1] database hawk is at version 11.2.0.4.0
Aug 01 02:48:41 2020 [0-1] database hawk_stby is at version 19.0.0.0.0
Aug 01 02:48:45 2020 [0-1] verifying fast recovery area is enabled at hawk and hawk_stby
Aug 01 02:48:47 2020 [0-1] verifying backup location at hawk and hawk_stby
Aug 01 02:48:48 2020 [0-1] verifying available flashback restore points
Aug 01 02:48:49 2020 [0-1] verifying DG Broker is disabled
Aug 01 02:48:50 2020 [0-1] looking up prior execution history
Aug 01 02:48:53 2020 [0-1] last completed stage [2-4] using script version 0003
Aug 01 02:48:53 2020 [0-1] resuming execution of script

### Stage 3: Validate upgraded transient logical standby
Aug 01 02:48:54 2020 [3-1] database hawk_stby is no longer in OPEN MIGRATE mode
Aug 01 02:48:54 2020 [3-1] database hawk_stby is at version 19.0.0.0.0

### Stage 4: Switch the transient logical standby to be the new primary
Aug 01 02:48:59 2020 [4-1] waiting for hawk_stby to catch up (this could take a while)
Aug 01 02:49:00 2020 [4-1] waiting for apply lag to fall under 30 seconds
Aug 01 02:49:12 2020 [4-1] apply lag measured at 9 seconds
Aug 01 02:49:16 2020 [4-2] using fast switchover optimizations

NOTE: A switchover is about to be performed which will incur a brief outage
      of the primary database.  If you answer 'y' to the question below,
      database hawk_stby will become the new primary database, and database hawk
      will be converted into a standby in preparation for upgrade.  If you answer
      'n' to the question below, the script will exit, leaving the databases in
      their current roles.
Are you ready to proceed with the switchover? (y/n): y

Aug 01 02:49:31 2020 [4-2] continuing
Aug 01 02:49:31 2020 [4-2] switching hawk to become a logical standby
Aug 01 02:49:39 2020 [4-2] hawk is now a logical standby
Aug 01 02:49:39 2020 [4-2] waiting for standby hawk_stby to process end-of-redo from primary
Aug 01 02:49:44 2020 [4-2] switching hawk_stby to become the new primary
Aug 01 02:49:45 2020 [4-2] hawk_stby is now the new primary

### Stage 5: Flashback former primary to pre-upgrade restore point and convert to physical
Aug 01 02:49:49 2020 [5-1] shutting down database hawk
Aug 01 02:50:03 2020 [5-1] mounting database hawk
Aug 01 02:50:12 2020 [5-2] flashing back database hawk to restore point PRU_0000_0003
Aug 01 02:50:15 2020 [5-3] converting hawk into physical standby
Aug 01 02:50:17 2020 [5-4] shutting down database hawk

NOTE: Database hawk has been shutdown, and is now ready to be started
      using the newer version Oracle binary.  This script requires the
      database to be mounted (on all active instances, if RAC) before calling
      this script to resume the rolling upgrade.

[oracle@ol7-112-dg2 upgrade19c]$

[oracle@ol7-112-dg2 upgrade19c]$ ./physru_v3.sh SYS hawk hawk_stby hawk hawk_stby 19.0.0.0.0
Please enter the sysdba password:

### Initialize script to either start over or resume execution
Aug 01 03:26:16 2020 [0-1] Identifying rdbms software version
Aug 01 03:26:17 2020 [0-1] database hawk is at version 19.0.0.0.0
Aug 01 03:26:18 2020 [0-1] database hawk_stby is at version 19.0.0.0.0
Aug 01 03:26:26 2020 [0-1] verifying fast recovery area is enabled at hawk and hawk_stby
Aug 01 03:26:29 2020 [0-1] verifying backup location at hawk and hawk_stby
Aug 01 03:26:31 2020 [0-1] verifying available flashback restore points
Aug 01 03:26:34 2020 [0-1] verifying DG Broker is disabled
Aug 01 03:26:36 2020 [0-1] looking up prior execution history
Aug 01 03:26:39 2020 [0-1] last completed stage [5-4] using script version 0003
Aug 01 03:26:39 2020 [0-1] resuming execution of script

### Stage 6: Run media recovery through upgrade redo
Aug 01 03:26:47 2020 [6-1] upgrade redo region identified as scn range [995261, 2453907]
Aug 01 03:26:47 2020 [6-1] enabling log archive destination to database hawk
Aug 01 03:26:51 2020 [6-1] starting media recovery on hawk
Aug 01 03:26:57 2020 [6-1] confirming media recovery is running
Aug 01 03:26:59 2020 [6-1] waiting for media recovery to initialize v$recovery_progress
Aug 01 03:27:20 2020 [6-1] monitoring media recovery's progress
Aug 01 03:27:32 2020 [6-3] recovery of upgrade redo at 07% - estimated complete at Aug 01 03:31:24
Aug 01 03:27:57 2020 [6-3] recovery of upgrade redo at 26% - estimated complete at Aug 01 03:30:06
Aug 01 03:28:21 2020 [6-3] recovery of upgrade redo at 42% - estimated complete at Aug 01 03:30:00
Aug 01 03:28:45 2020 [6-3] recovery of upgrade redo at 52% - estimated complete at Aug 01 03:30:10
Aug 01 03:29:10 2020 [6-3] recovery of upgrade redo at 61% - estimated complete at Aug 01 03:30:25
Aug 01 03:29:36 2020 [6-3] recovery of upgrade redo at 73% - estimated complete at Aug 01 03:30:27
Aug 01 03:30:00 2020 [6-3] recovery of upgrade redo at 82% - estimated complete at Aug 01 03:30:35
Aug 01 03:30:24 2020 [6-3] recovery of upgrade redo at 90% - estimated complete at Aug 01 03:30:42
Aug 01 03:30:51 2020 [6-3] recovery of upgrade redo at 96% - estimated complete at Aug 01 03:30:55
Aug 01 03:31:12 2020 [6-4] media recovery has finished recovering through upgrade

### Stage 7: Switch back to the original roles prior to the rolling upgrade

NOTE: At this point, you have the option to perform a switchover
     which will restore hawk back to a primary database and
     hawk_stby back to a physical standby database.  If you answer 'n'
     to the question below, hawk will remain a physical standby
     database and hawk_stby will remain a primary database.

Do you want to perform a switchover? (y/n): y

Aug 01 03:31:26 2020 [7-1] continuing
Aug 01 03:31:36 2020 [7-2] waiting for apply lag to fall under 30 seconds
Aug 01 03:31:44 2020 [7-2] apply lag measured at 5 seconds
Aug 01 03:31:48 2020 [7-3] switching hawk_stby to become a physical standby
Aug 01 03:31:55 2020 [7-3] hawk_stby is now a physical standby
Aug 01 03:31:55 2020 [7-3] shutting down database hawk_stby
Aug 01 03:31:57 2020 [7-3] mounting database hawk_stby
Aug 01 03:32:08 2020 [7-3] starting media recovery on hawk_stby
Aug 01 03:32:15 2020 [7-3] confirming media recovery is running
Aug 01 03:32:16 2020 [7-3] waiting for standby hawk to process end-of-redo from primary
Aug 01 03:32:21 2020 [7-3] switching hawk to become the new primary
Aug 01 03:32:23 2020 [7-3] hawk is now the new primary
Aug 01 03:32:23 2020 [7-3] opening database hawk

### Stage 8: Statistics
script start time:                                           31-Jul-20 23:54:44
script finish time:                                          01-Aug-20 03:32:36
total script execution time:                                       +00 03:37:52
wait time for user upgrade:                                        +00 02:52:39
active script execution time:                                      +00 00:45:13
transient logical creation start time:                       31-Jul-20 23:54:46
transient logical creation finish time:                      31-Jul-20 23:55:14
primary to logical switchover start time:                    01-Aug-20 02:49:14
logical to primary switchover finish time:                   01-Aug-20 02:49:47
primary services offline for:                                      +00 00:00:33
total time former primary in physical role:                        +00 00:40:57
time to reach upgrade redo:
time to recover upgrade redo:                                      +00 00:03:44
primary to physical switchover start time:                   01-Aug-20 03:31:25
physical to primary switchover finish time:                  01-Aug-20 03:32:34
primary services offline for:                                      +00 00:01:09

SUCCESS: The physical rolling upgrade is complete

[oracle@ol7-112-dg2 upgrade19c]$

References:

Oracle11g Data Guard: Database Rolling Upgrade Shell Script (Doc ID 949322.1)

Data Guard physru_v3.sh Script Errors ORA-01403 ORA-06512 (Doc ID 2570572.1)

Oracle Database Rolling Upgrades

High_Level_Action_Plan

================================================================================
1. Convert physical standby to transient logical standby by running PHYSRU_v3.sh
================================================================================
Step100-Disable_DG_Broker_GoldenGate_Configuration.txt
Step110-Check_Primary_Standby_Configuration.txt
Step120-Convert_PhysicalStandby_To_TransientLogicalStandby_using_physru_v3.sh.txt
Verifies that Data Guard Broker is disabled and FRA is configured.
Creates a guaranteed restore point
Converts the existing Physical Standby to a Logical Standby

================================================================================
2. Upgrade the transient logical standby using DBUA
================================================================================
Step200-Upgrade_TransientLogicalStandby_using_dbua.txt

================================================================================
3. Convert transient logical standby to Primary by running PHYSRU_v3.sh
================================================================================
Step300-Convert_TransientLogicalStandby_To_Primary_using_physru_v3.sh.txt
Executes a switchover making the upgraded standby database the primary database.
Executes a flashback of the original primary database to the guaranteed restore point and shuts it down.

ORIGINAL_PRIMARY is down and ORIGINAL_STANDBY is now NEW_PRIMARY.

================================================================================
4. Configure 11.2 ORIGINAL_PRIMARY for 19c
================================================================================
Step400-Configure_11g_ORIGINAL_PRIMARY_for_19c.txt

================================================================================
5. Upgrade 11.2 ORIGINAL_PRIMARY to 19c by running PHYSRU_v3.sh
================================================================================
Step500-Upgrade19c_ORIGINAL_PRIMARY_using_physru_v3.sh.txt
Start redo apply
Prompt whether to switch back to original configuration
Remove guaranteed restore points

July 15, 2020

Create 19c RAC Standby Using RMAN

Filed under: 19c,Dataguard,RAC — mdinh @ 11:55 pm

See RAC_19c_rman_duplicate_standby_same_sid.log

Confirmed!

*** 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 One Node or RAC as the Broker will use the clusterware to restart an instance.

May 2, 2020

glogin.sql for Data Guard Environment

Filed under: Dataguard — mdinh @ 10:19 pm

I got tired for seeing for db_name vs db_unique_name for sql prompt connecting with sqlplus for Data Guard environment.

Update $ORACLE_HOME/sqlplus/admin/glogin.sql

column NAME_COL_PLUS_SHOW_PARAM format a40 wrap
column VALUE_COL_PLUS_SHOW_PARAM format a80 wrap
set lines 200 echo on trimsp on tab off pages 10000 serverout on size 1000000 feedback off verify off term off echo off arraysize 5000
define _pr="SQL> "
column pr new_value _pr
select UPPER(SYS_CONTEXT('USERENV','SERVER_HOST'))||':(&_USER@'||SYS_CONTEXT('USERENV','DB_UNIQUE_NAME')||':'||SYS_CONTEXT('USERENV','DATABASE_ROLE')||'> '
pr from dual;
set sqlprompt "&_pr"
column pr clear
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
set termout on

Test:

[oracle@ol7-121-dg3 ~]$ . oraenv <<< hawk
ORACLE_SID = [hawk] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@ol7-121-dg3 ~]$ sysresv|tail -1
Oracle Instance alive for sid "hawk"

[oracle@ol7-121-dg3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 2 22:16:36 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

OL7-121-DG3:(SYS@hawkc:PRIMARY> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ol7-121-dg3 ~]$

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)

Next Page »

Blog at WordPress.com.