Apply PSU Patch on a DG Environment with MAX AVAILABILITY and FAST START FAILOVER

1.       In the Primary Site, change the Protection Mode to Max Performance.

This is required as in Max Availability, you will not be allowed to stop Log service, when you are running with ONE Standby, throwing the following error:

DGMGRL> edit database 'tdusdpr' set state='LOG-TRANSPORT-OFF';

Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode

Failed.

DGMGRL> edit database 'tdusgpr' set property LogShipping='OFF';

Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode

Failed.

The later command will succeed if you have more than one standby but we need to anyway stop Log Shipping and hence changing Protection Mode is the Only Solution to end in successful stoppage.

Ensure that the Dataguard is in complete sync and then DISABLE FAST_START FAILOVER.

DGMGRL> show configuration verbose;

Configuration

  Name:                tds_us_prod

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: ENABLED

  Databases:

    tdusdpr - Primary database

    tdusgpr - Physical standby database

            - Fast-Start Failover target

Fast-Start Failover

  Threshold:           180 seconds

  Observer:            glglprgr01

  Shutdown Primary:    TRUE

Current status for "tds_us_prod":

SUCCESS

DGMGRL> DISABLE FAST_START FAILOVER;

Disabled.

DGMGRL> show configuration verbose;

Configuration

  Name:                tds_us_prod

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: DISABLED

  Databases:

    tdusdpr - Primary database

    tdusgpr - Physical standby database

Current status for "tds_us_prod":

SUCCESS

Once FAST_START FAILOVER has been disabled, then change the protection mode.

The disable is required as with FSFO enabled, you cannot change the PROTECTION MODE as depicted here.

DGMGRL> edit database 'tdusgpr' set state='LOG-APPLY-OFF';

Succeeded.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;

Error: ORA-16628: the broker protection mode is inconsistent with the database setting

Failed.

                With FSFO disabled, change the Protection Mode.

                DGMGRL> EDIT DATABASE 'tdusgpr' SET STATE='LOG-APPLY-OFF'; --note this Standby Database

Succeeded.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;

Succeeded.

DGMGRL> show configuration verbose;

Configuration

                Name:                tds_us_prod

                Enabled:             YES

                Protection Mode:     MaxPerformance

                Fast-Start Failover: DISABLED

                Databases:

                tdusdpr - Primary database

                tdusgpr - Physical standby database

Current status for "tds_us_prod":

SUCCESS

2.        Disable Log Shipping to Standby Site.

DGMGRL> EDIT DATABASE 'tdusdpr' SET STATE='LOG-TRANSPORT-OFF'; --note this is Primary

Succeeded.

If the database are queried then the Log Services Status can be viewed.

DGMGRL> show database verbose 'tdusdpr';

Database

  Name:            tdusdpr

  Role:            PRIMARY

  Enabled:         YES

  Intended State:  LOG-TRANSPORT-OFF

  Instance(s):

    TDUSDPR

……….…

………….

DGMGRL> show database verbose 'tdusgpr';

Database

  Name:            tdusgpr

  Role:            PHYSICAL STANDBY

  Enabled:         YES

  Intended State:  LOG-APPLY-OFF

  Instance(s):

    TDUSGPR

……….…

………….

3.       Shutdown Standby database services, apply the patch at the Standby Site binaries and restart services

Stop Services

$ emctl stop agent

SQL> shutdown immediate

$ lsnrctl stop LISTENER_DG

Apply patch

$ unzip p12879933_102044_<platform>.zip

$ opatach lsinventory

$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./12879933

$ opatch lsinventory

$ opatch apply

Restart the listener first and then Standby Site database to MOUNT. Do NOT start MRP process.

$ emctl start agent

$ lsnrctl start LISTENER_DG

SQL> startup mount

4.       Apply the Patch to the Primary and Restart Database and Listener in Primary

Stop Services

$ emctl stop agent

SQL> shutdown immediate

$ lsnrctl stop LISTENER_DG

Apply patch

$ unzip p12879933_102044_<platform>.zip

$ opatach lsinventory

$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./12879933

$ opatch lsinventory

$ opatch apply

Apply catbundle only on Primary Database

$ cd $ORACLE_HOME/rdbms/admin 

$ sqlplus / as sysdba 

SQL> startup 

SQL> @catbundle opsu apply 

SQL> SELECT * FROM registry$history where ID = '6452863';
This returns 1 row hence view recompilation is not needed here.

SQL> @utlrp.sql

SQL> shu immediate

Restart the listener first and then Primary Site database

SQL> startup

SQL> lsnrctl start LISTENER_DG

5.       In the Primary Site, Reenable Log Shipping

DGMGRL> connect /

DGMGRL> show configuration;

Configuration

  Name:                tds_us_prod

  Enabled:             YES

  Protection Mode:     MaxPerformance

  Fast-Start Failover: DISABLED

  Databases:

    tdusdpr - Primary database

    tdusgpr - Physical standby database

Current status for "tds_us_prod":

SUCCESS

DGMGRL> 

DGMGRL> edit database 'tdusgpr' set state='ONLINE';

Succeeded.

DGMGRL> edit database 'tdusdpr' set state='ONLINE';

Succeeded.

DGMGRL> show database verbose 'tdusdpr';

Database

  Name:            tdusdpr

  Role:            PRIMARY

  Enabled:         YES

  Intended State:  ONLINE

  Instance(s):

    TDUSDPR

……..

..…...

DGMGRL> show database verbose 'tdusgpr';

Database

  Name:            tdusgpr

  Role:            PHYSICAL STANDBY

  Enabled:         YES

  Intended State:  ONLINE

  Instance(s):

    TDUSGPR

………

………

6.       Check that the Both Databases are Syncronised.

SQL> select thread#,max(sequence#),archived,applied from v$archived_log where archived='YES' group by thread#,archived,applied order by 1,2;

7.       Change the Protection Mode back to Maximum Availability

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Succeeded.

DGMGRL> enable fast_start failover;

Enabled.

This might end up in throwing the below error. In case, this is encountered, start the Observer.

DGMGRL> show configuration;

Configuration

  Name:                tds_us_prod

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: ENABLED

  Databases:

    tdusdpr - Primary database

    tdusgpr - Physical standby database

            - Fast-Start Failover target

Current status for "tds_us_prod":

Warning: ORA-16607: one or more databases have failed

DGMGRL> exit

                Once the observer is started, we can recheck the configuration status which now will be SUCCESS.

DGMGRL> show configuration;

Configuration

  Name:                tds_us_prod

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: Enabled

  Databases: 

    tdusdpr - Primary database

    tdusgpr - Physical standby database

            - Fast-Start Failover target

Current status for "tds_us_prod":

SUCCESS

8.       This will successfully end the Data Guard Patching on Max Availability running with Fast Start Failover.