UCP Connections Fail to Connect to DB in Mount Mode With ORA-12504 TNS:listener was not given the SID in CONNECT_DATA

Trying to connect to a database (non-CDB, CDB or PDB) in mount mode as sysdba using a UCP JDBC connection fails with

Exception in thread "main" java.sql.SQLException: Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SID in CONNECT_DATA

However there's no failure when OracleDataSource is used instead of UCP. Issue with UCP only appears in JDBC Driver versions 18.3.0.0.0 and 12.2.0.1.0.
No issue in connecting to database in mount mode with driver versions 12.1.0.2.0 and 11.2.0.4.0. This appears to be a bug on later version of JDBC drivers. After an SR this is been investigated under bug# 28780778.

Follow Java code could be used to recreate the issue. Change the ojdbc*.jar and ucp.jar as needed to try different drivers.

import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.StringTokenizer;
import oracle.jdbc.pool.OracleDataSource;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;

/**
 *
 * @author Asanga
 */
public class ConnectToMount {

    public static void main(String[] args) throws SQLException {

        String username = "sys";
        String password = "xxxxx";
        boolean isSysdba = true;
        String URL = "jdbc:oracle:thin:@192.168.0.79:1521/pdb";

        System.out.println("Driver Info");
        Connection con = usingODS(username, password, URL, isSysdba);
        printDriverInfo(con);
        
        System.out.println("\nusing ODS");
        runQuery(con);

        System.out.println("\nusing UCP");
        runQuery(usingUCP(username, password, URL, isSysdba));

    }

    static void runQuery(Connection con) throws SQLException {

        PreparedStatement pr = con.prepareStatement("select db_unique_name from v$database");

        ResultSet rs = pr.executeQuery();
        while (rs.next()) {

            System.out.println(rs.getString(1));
        }

        rs.close();
        pr.close();
        con.close();
    }

    static Connection usingUCP(String username, String password, String URL, boolean isSysDBA) throws SQLException {

        PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();
        ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        ds.setUser(username);
        ds.setPassword(password);
        ds.setURL(URL);

        if (isSysDBA) {
            Properties p = new Properties();
            p.put("internal_logon", "sysdba");

            ds.setConnectionProperties(p);
        }

        return ds.getConnection();

    }

    static Connection usingODS(String username, String password, String URL, boolean isSysDBA) throws SQLException {

        OracleDataSource ds = new OracleDataSource();
        ds.setUser(username);
        ds.setPassword(password);
        ds.setURL(URL);

        if (isSysDBA) {

            Properties p = new Properties();
            p.put("internal_logon", "sysdba");

            ds.setConnectionProperties(p);
        }

        return ds.getConnection();

    }

    static void printDriverInfo(Connection con) throws SQLException {

        DatabaseMetaData meta = con.getMetaData();

        System.out.println("Driver Name " + meta.getDriverName());
        System.out.println("Driver Version " + meta.getDriverVersion());
        System.out.println("Driver Major Version " + meta.getDriverMajorVersion());
        System.out.println("Driver Minor Version " + meta.getDriverMinorVersion());
        System.out.println("Database Major Version " + meta.getDatabaseMajorVersion());
        System.out.println("Database Minor Version " + meta.getDatabaseMinorVersion());

        java.util.Properties props = System.getProperties();
        System.out.println("\nJVM\n===");
        System.out.println(props.getProperty("java.vm.vendor"));
        System.out.println(props.getProperty("java.vm.name"));
        System.out.println(props.getProperty("java.vm.version"));
        System.out.println(props.getProperty("java.version"));
    }
}

Upgrading Oracle Restart Databases From 12.2 to 18c Using DBMS_ROLLING

DBMS_ROLLING allows upgrade of databases in a data guard configuration with minimum down time. In short the dbms_rolling upgrade process divide the databases in the data guard configuration in to leading group and a trailing group. It first upgrade the leading group databases, which include the future primary and standby to protect the future primary. After a switchover, the only time a downtime is encountered, the trailing group it upgraded. More on this could be read on data guard admin guide. Ideally one would need at least four databases in the DG configuration to illustrate the best use case of dbms_rolling. But same approach could be used for two database DG configuration as well(14.6 example). Only difference between the two scenarios with lower number of databases the setup is not protected during certain steps.
This post list the step of upgrading 12.2 Oracle restart databases in a DG configuration to 18c. The post primarily focuses only on the dbms_rolling aspect of it. An earlier post is available for upgrading Oracle restart from 12.2 to 18c.
Some of the enhancements made on dbms_rolling such as keeping dg broker enabled was mentioned in a previous post. The current data guard and database roles are as follows

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  colombo - Primary database
    london  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 25 seconds ago)

1. Upgrade the GI on the standby first. This would require downtime on the standby, during which primary will be unprotected. Primary's GI is not upgraded at this time, only the standby GI is upgraded. This way there's only one downtime for overall system. The listener.ora file gets copied over to 18c GI home automatically. However, if static listener entries were added on this file then ORACLE_HOME values require changing to reflect the 18c home. Once the GI home is upgraded install 18c DB software. Refer earlier post on 12.2 to 18c for upgrading GI and installing DB software. Copy the tnsnames.ora and password files to relevant directories in the 18c DB home. At the end of this first step the software versions on primary and standby is as follows.

primary :- GI = 12.2, DB 12.2
standby :- GI = 18.0 DB running out of 12.2  home , 18.0 DB software installed

2. Start the rolling upgrade process by executing initializing phase and check plan parameters.

SQL> exec DBMS_ROLLING.INIT_PLAN(future_primary=>'london');
PL/SQL procedure successfully completed.

select scope, name, curval from dba_rolling_parameters order by scope, name;

3. Execute build plan and check plan values.

SQL> exec DBMS_ROLLING.BUILD_PLAN;
PL/SQL procedure successfully completed.

SELECT instid, target, phase, description FROM DBA_ROLLING_PLAN;

4. Start the plan. This phase, among other things will convert the physical standby to a transient logical standby. Detail list of work carried out during this phase was mentioned in an earlier post.

SQL> exec DBMS_ROLLING.START_PLAN;
PL/SQL procedure successfully completed.

DG Broker status for configuration and databases after start of the plan is as follows

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  colombo - Primary database
    london  - Transient logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
    ROLLING DATABASE MAINTENANCE IN PROGRESS

DGMGRL> show database london

Database - london

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

  Database Warning(s):
    ORA-16866: database converted to transient logical standby database for rolling database maintenance

Database Status:
WARNING

DGMGRL> show database colombo

Database - colombo

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

Database Status:
SUCCESS

5. Upgrade of the standby could be carried out now as it is now a logical standby and open in read/write mode. Run preupgrade on transient logical standby

$ORACLE_HOME/jdk/bin/java -jar preupgrade.jar TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
18.0.0.0.0 on 2018-08-29T08:55:03

Upgrade-To version: 18.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  COLOMBO
     Container Name:  COLOMBO
       Container ID:  0
            Version:  12.2.0.1.0
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  26
  Database log mode:  ARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  Oracle Workspace Manager               [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  1.  (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade.

      The database contains 6 objects in the recycle bin.

      The recycle bin must be completely empty before database upgrade.

  RECOMMENDED ACTIONS
  ===================
  2.  Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
      objects.  You can view the individual invalid objects with

        SET SERVEROUTPUT ON;
        EXECUTE DBMS_PREUP.INVALID_OBJECTS;

      89 objects are INVALID.

      There should be no INVALID objects in SYS/SYSTEM or user schemas before
      database upgrade.

  3.  Review and remove any unnecessary HIDDEN/UNDERSCORE parameters.

      The database contains the following initialization parameters whose name
      begins with an underscore:

      _rac_dbtype_reset
      _transient_logical_clear_hold_mrp_bit

      Remove hidden parameters before database upgrade unless your application
      vendors and/or Oracle Support state differently.  Changes will need to be
      made in the pfile/spfile.

  4.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  5.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                            1190 MB      1302 MB
      SYSTEM                             700 MB      1048 MB
      TEMP                                20 MB       150 MB

      Minimum tablespace sizes for upgrade are estimates.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database COLOMBO
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/opt/app/oracle/cfgtoollogs/london/preupgrade/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  6.  Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 26 and the target 18.0.0.0.0
      release ships with time zone file version 31.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 18.0.0.0.0 Oracle Database
      Globalization Support Guide.

  7.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  8.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database COLOMBO
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/opt/app/oracle/cfgtoollogs/london/preupgrade/postupgrade_fixups.sql


==================
PREUPGRADE SUMMARY
==================
  /opt/app/oracle/cfgtoollogs/london/preupgrade/preupgrade.log
  /opt/app/oracle/cfgtoollogs/london/preupgrade/preupgrade_fixups.sql
  /opt/app/oracle/cfgtoollogs/london/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups
@/opt/app/oracle/cfgtoollogs/london/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/opt/app/oracle/cfgtoollogs/london/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2018-08-29T08:55:03

Execute the prefixup script.

SQL> @/opt/app/oracle/cfgtoollogs/london/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 18.0.0.0.0 Build: 1
Generated on:            2018-08-29 08:55:01

For Source Database:     COLOMBO
Source Database Version: 12.2.0.1.0
For Upgrade to Version:  18.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  purge_recyclebin          YES         None.
    2.  invalid_objects_exist     NO          Manual fixup recommended.
    3.  hidden_params             NO          Informational only.
                                              Further action is optional.
    4.  dictionary_stats          YES         None.
    5.  tablespaces_info          NO          Informational only.
                                              Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade.  To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

Some of the hidden parameters were added as part of the rolling upgrade. Leave them as it is. Run DBUA to from 18c home to upgrade the standby from 12.2 to 18c.



6. After the upgrade run postfixup script.

@/opt/app/oracle/cfgtoollogs/london/preupgrade/postupgrade_fixups.sql

Querying the DG status at this point will show standby DB is disable and as a physical standby. This is wrong information.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  colombo - Primary database
    Warning: ORA-16629: database reports a different protection level from the protection mode

    london  - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
    ROLLING DATABASE MAINTENANCE IN PROGRESS

DGMGRL> show database london

Database - london

  Role:               PHYSICAL STANDBY
  Intended State:     OFFLINE
  Transport Lag:      (unknown)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    london

Database Status:
SHUTDOWN

Enabling the standby doesn't resolve this either.

DGMGRL> enable database london
Enabled.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  colombo - Primary database
    Warning: ORA-16629: database reports a different protection level from the protection mode

    london  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
    ROLLING DATABASE MAINTENANCE IN PROGRESS

DGMGRL> show database london

Database - london

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          25 minutes 14 seconds (computed 0 seconds ago)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    london

  Database Error(s):
    ORA-16816: incorrect database role
Database Status:
ERROR

However querying the database role shows it's a logical standby.

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
LOGICAL STANDBY

At the end of the upgrade the transient logical standby must be open in read/write mode. However, the Oracle restart setting still has the old role and startup state associated with the standby database.

srvctl config database -d london
Database unique name: london
Database name:
Oracle home: /opt/app/oracle/product/18.0.0/dbhome_1
Oracle user: oracle
Spfile: /opt/app/oracle/product/18.0.0/dbhome_1/dbs/spfilelondon.ora
Password file:
Domain: domain.net
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services: flt,bx
OSDBA group:
OSOPER group:
Database instance: london

Due to this reason the DB doesn't open in read/write mode but only in mount mode. As DG broker is associated with the Oracle restart configuration it also take the database role to be a physical standby. In this status trying to switchover will also fail.

SQL> exec DBMS_ROLLING.SWITCHOVER;
BEGIN DBMS_ROLLING.SWITCHOVER; END;

*
ERROR at line 1:
ORA-45427: logical standby Redo Apply process was not running
ORA-06512: at "SYS.DBMS_ROLLING", line 89
ORA-06512: at line 1

7. To fix this manually start the standby DB in read/write mode and also start the standby apply process.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Database altered.
DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  colombo - Primary database
    london  - Transient logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
    ROLLING DATABASE MAINTENANCE IN PROGRESS

DGMGRL> show database london

Database - london

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          3 minutes 53 seconds (computed 1 second ago)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    london

  Database Warning(s):
    ORA-16866: database converted to transient logical standby database for rolling database maintenance

Database Status:
WARNING

8. Next is the switchover phase. This is the only time total system outage would occur. (until the applications connect to the standby, refer here for JDBC connection failover to further mitigate the down time). The switchover must be done using the dbms_rolling package and not using DG broker.

SQL> exec DBMS_ROLLING.SWITCHOVER;

PL/SQL procedure successfully completed.

With this the roles of the databases are reversed. The original standby become new primary while original primary become new transient logical standby.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  london  - Primary database
    Warning: ORA-16629: database reports a different protection level from the protection mode

    colombo - Transient logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
    ROLLING DATABASE MAINTENANCE IN PROGRESS

DGMGRL> show database london

Database - london

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

  Database Warning(s):
    ORA-16629: database reports a different protection level from the protection mode

Database Status:
WARNING

DGMGRL> show database colombo

Database - colombo

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      (unknown)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    colombo

  Database Warning(s):
    ORA-16866: database converted to transient logical standby database for rolling database maintenance

Database Status:
WARNING

At this stage the software versions on databases are as follows

Primary :- GI 18c, DB home 18c and DB running out of 18c home
Standby :- GI 12.2, DB 12.2

9.Upgrade GI on the standby (old primary) to 18c and install the 18c DB software. Copy tnsnames.ora, init file and password file to 18c DB home's relevant directories. Finally remove the datbaase from oracle restart config and add using 18c binaries, specifying 18c home. The database is added a physical standby to Oracle restart even though it's actual role is logical standby. This way when final finish phase is executed, there's no need to make any changes to Oracle restart configuration.

srvctl config database -d colombo
Database unique name: colombo
Database name: colombo
Oracle home: /opt/app/oracle/product/12.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/COLOMBO/PARAMETERFILE/spfile.266.981889629
Password file:
Domain: domain.net
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services: flt,tbx
OSDBA group:
OSOPER group:
Database instance: colombo

srvctl remove database -d colombo
Remove the database colombo? (y/[n]) y

export ORACLE_HOME=/opt/app/oracle/product/18.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

srvctl add database -d colombo -o /opt/app/oracle/product/18.0.0/dbhome_1 -r physical_standby -s mount -p +DATA/COLOMBO/PARAMETERFILE/spfile.266.981889629
srvctl config database -d colombo
Database unique name: colombo
Database name:
Oracle home: /opt/app/oracle/product/18.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/COLOMBO/PARAMETERFILE/spfile.266.981889629
Password file:
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups:
Services:
OSDBA group:
OSOPER group:
Database instance: colombo

srvctl start database -d colombo -o mount

Actual role of the standby database

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              LOGICAL STANDBY

If the DG broker shows the standby DB as disable, then enable it.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  london  - Primary database
    Warning: ORA-16629: database reports a different protection level from the protection mode

    colombo - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
    ROLLING DATABASE MAINTENANCE IN PROGRESS

DGMGRL> enable database colombo
Enabled.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  london  - Primary database
    Error: ORA-16810: multiple errors or warnings detected for the member

    colombo - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
    ROLLING DATABASE MAINTENANCE IN PROGRESS

10. To convert the new logical standby to physical standby, execute the finish plan. This will start redo apply on a branch.

SQL> EXECUTE DBMS_ROLLING.FINISH_PLAN;

PL/SQL procedure successfully completed.

Let the new standby catch up with the redo and query the DG status.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  london  - Primary database
    colombo - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 25 seconds ago)

DGMGRL> show database london

Database - london

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

Database Status:
SUCCESS

DGMGRL> show database colombo

Database - colombo

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 11.15 MByte/s
  Real Time Query:    OFF
  Instance(s):
    colombo

Database Status:
SUCCESS

11. Finally call the destroy plan.

SQL> exec DBMS_ROLLING.DESTROY_PLAN;

PL/SQL procedure successfully completed.

With this step the upgrading process is complete.

12. The upgrade only resulted in one down time during the switchover process. However, if the database configuration need to be back to it's original roles then another switchover is needed. This could be done using DG broker but would result in a second system outage.

DGMGRL> validate database colombo

  Database Role:     Physical standby database
  Primary Database:  london

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

  Managed by Clusterware:
    london :  YES
    colombo:  YES

DGMGRL> switchover to colombo
Performing switchover NOW, please wait...
Operation requires a connection to database "colombo"
Connecting ...
Connected to "colombo"
Connected as SYSDBA.
New primary database "colombo" is opening...
Oracle Clusterware is restarting database "london" ...
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to "london"
Connected to "london"
Switchover succeeded, new primary is "colombo"

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  colombo - Primary database
    london  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 69 seconds ago)

With this last step both databases return to their original roles before the upgrade process.

Converting a Physical Standby to a Snapshot Standby

Converting a physical standby to a snapshot standby allows it to be open for read/write access. This could enable various types of testing to be carried out on the snapshot standby. This post list steps for converting a physical standby to snapshot standby and then convert it back to a physical standby.
1. Current data guard configuration is as follows.

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Databases:
    prod  - Primary database
    stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

2. It is not necessary to have flashback enabled for snapshot to work. As per Oracle documentation "if Flashback database is disabled, it is automatically enabled during conversion to a snapshot standby database. The broker automatically restarts the database to the mounted state if it had been opened with Flashback Database disabled. No user action is required". However, a fast recovery area must be configured on the physical standby. According to Oracle documentation "this is because a guaranteed restore point is created during the conversion process, and guaranteed restore points require a fast recovery area". For this post the flashback feature was deliberately turned off to verify that snapshot conversion can go through without it. However, it's good practice to have flashback enabled in a DG configuration. Turning off flashback is not part of converting to snapshot standby and no need to do the below steps.
On primary

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter database flashback off;
Database altered.

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

On standby

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter database flashback off;
Database altered.

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

3. Conversion to snapshot standby is done using the DGMGRL.

DGMGRL> convert database stdby to snapshot standby;
Converting database "stdby" to a Snapshot Standby database, please wait...
Database "stdby" converted successfully

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Databases:
    prod  - Primary database
    stdby - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Use of DGMGRL automate lot of the steps needed for conversion such as stopping the redo apply and initializing DG after the conversion. These steps could be identified by looking at the standby alert log.

Wed Sep 19 16:22:13 2018
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Wed Sep 19 16:22:13 2018
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /opt/app/oracle/diag/rdbms/stdby/stdby/trace/stdby_pr00_2301.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 16269612
Wed Sep 19 16:22:13 2018
MRP0: Background Media Recovery process shutdown (stdby)
Managed Standby Recovery Canceled (stdby)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
alter database convert to snapshot standby
Starting background process RVWR
Wed Sep 19 16:22:14 2018
RVWR started with pid=34, OS id=2429
Wed Sep 19 16:22:15 2018
Archived Log entry 4995 added for thread 1 sequence 399 ID 0x172868bb dest 1:
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_09/19/2018 16:22:14
Killing 1 processes with pids 2334 (all RFS) in order to disallow current and fu                                                                                                              ture RFS connections. Requested by OS process 2293
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 16269612
Resetting resetlogs activation ID 388524219 (0x172868bb)
Online log +DATA/stdby/onlinelog/group_1.264.964883451: Thread 1 Group 1 was previously cleared
Online log +FRA/stdby/onlinelog/group_1.258.964883451: Thread 1 Group 1 was previously cleared
...
Standby became primary SCN: 16269610
Wed Sep 19 16:22:18 2018
Setting recovery target incarnation to 6
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
ALTER DATABASE OPEN
Data Guard Broker initializing...
Data Guard Broker initialization complete

4. After physical standby is converted to snapshot standby it will not carry out redo apply. However, it will continue to receive redo from primary thus protecting the primary in the event of failure. This could be verified by querying managed standby view. On standby run

SQL> select status,sequence#,block# from v$managed_standby where client_process='LGWR';

STATUS        SEQUENCE#     BLOCK#
------------ ---------- ----------
IDLE                402        409

Make a note of block and sequence number. Execute a log switch on primary and query the standby again, which would show change in sequence# and/or block#. This indicate redo shipping is working as expected.

STATUS        SEQUENCE#     BLOCK#
------------ ---------- ----------
IDLE                403          4

5. At this point the snapshot standby is open for read/write. Carry out any testing needed. Once done with testing convert back to physical standby. Any changes done on snapshot standby DB will be lost once converted back to physical standby.



6. Connect to DGMGRL from primary and execute the convert command.

DGMGRL> convert database stdby to physical standby;
Converting database "stdby" to a Physical Standby database, please wait...
Operation requires shutdown of instance "stdby" on database "stdby"
Shutting down instance "stdby"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "stdby" on database "stdby"
Starting instance "stdby"...
ORACLE instance started.
Database mounted.
Continuing to convert database "stdby" ...
Operation requires shutdown of instance "stdby" on database "stdby"
Shutting down instance "stdby"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "stdby" on database "stdby"
Starting instance "stdby"...
ORACLE instance started.
Database mounted.
Database "stdby" converted successfully

7. Finally verify the DG status and redo apply has begun.

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Databases:
    prod  - Primary database
    stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database stdby

Database - stdby

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

Database Status:
SUCCESS