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 installed2. 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:03Execute 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.sqlQuerying 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:
SHUTDOWNEnabling 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:
ERRORHowever querying the database role shows it's a logical standby.SQL> select database_role from v$database; DATABASE_ROLE ---------------- LOGICAL STANDBYAt 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: londonDue 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 17. 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:
WARNING8. 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:
WARNINGAt this stage the software versions on databases are as followsPrimary :- 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 mountActual role of the standby database
SQL> select open_mode,database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- MOUNTED LOGICAL STANDBYIf 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 PROGRESS10. 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:
SUCCESS11. 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.
