Migrate and Upgrade Oracle Database 12.1 on Windows to 12.2 on Linux Using Oracle Data Guard with Mixed Versions

Share on:

My customer needed to migrate and upgrade Oracle Databases from on Windows to on Linux with minimum downtime.

Starting Oracle Database it is possible to configure the Physical Standby database with at a higher patchset or major release Primary database for purposes of a migration to new hardware provided that after a switchover to that standby, the database is not allowed to open and is immediately upgraded in the normal manner (MOS Doc ID 785347.1).

Migration using Oracle Data Guard is the preferred choice for business-critical databases where downtime is limited. Migrating using Oracle Data Guard to clone and synch your source database, then activate and upgrade physical standby database which minimizes downtime.

Downtime begins with physical standby activation, which usually takes 3-5 minutes, but database upgrade time typically takes 25-30 minutes.

Data Guard will create a physical standby of the source database on the target system using Oracle home and keep it in sync with the original database, and during the cutover, the activated standby database will be upgraded to

The process has two phases:

  • Create a physical standby phase: A standby database will be created on the target system.
  • Cutover phase: where the standby database will be activated and upgraded to


Create a Physical Standby Phase

Below is a high-level action plan for creating the physical standby database on the target system using home.

  1. Download and run Oracle database Pre-Upgrade utility on the source database on Windows (MOS Doc ID 884522.1).
  2. Review Pre-Upgrade utility log file (log).
  3. Run Pre-Upgrade utility sqlscript on the source database.
  4. Confirm the source database is in ARCHIVELOG mode.
  5. Enable FORCE_LOGGING on the source database.
  6. Create standby redo log groups on the source database.
  7. Copy source database password file to the target system.
  8. Configure TNS connections for source and target databases on the source and target systems.
  9. Startup NOMOUNT a standby database instance on the target system using 2.0.1home with the same DB_NAME as the source database and different DB_UNIQUE_NAME.
  10. Create a standby database on the target system using source database RMAN backup.
  11. Setup DG initialization parameters on the source and target databases.
  12. Start the managed recovery process (MRP) on the standby database.
  13. Monitor standby replication and confirm it is working and standby database running on the target system from home is in sync with the source database.


Cutover Phase (30 min downtime)

  1. Stop applications running against the source database.
  2. Make sure all changes have been shipped to the standby database and applied.
- On source database

D:\oracle\product\\dbhome_1\bin\sqlplus / as sysdba
SQL> alter system switch logfile;
SQL> archive log list;

- On target standby database

# export ORACLE_HOME=/u01/app/oracle/product/
# export PATH=$PATH:$ORACLE_HOME/bin
# sqlplus / as sysdba
SQL> select max(sequence#) from v$archived_log where applied='YES';
  1. Shutdown source database running on Windows.
  2. Stop managed recovery process (MRP) on the target standby database.
SQL> alter database recover managed standby database cancel;

Database altered.
  1. Activate the target standby database.
SQL> alter database activate standby database;

Database altered.

SQL> select database_role,open_mode from v$database;

---------------- --------------------

SQL> select controlfile_type from v$database;

  1. Stop the target database and startup in upgrade mode.
SQL> shutdown immediate
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8622776 bytes
Variable Size             620760392 bytes
Database Buffers         1476395008 bytes
Redo Buffers               41705472 bytes
Database mounted.
Database opened.
  1. Upgrade target database to
# cd $ORACLE_HOME/rdbms/admin
# mkdir -p $HOME/working/upgrade/PRODDB/logs
# $ORACLE_HOME/perl/bin/perl catctl.pl -l /home/oracle/working/upgrade/PRODDB/logs catupgrd.sql
  1. Check the upgrade log file and confirm no errors.
# cat /home/oracle/working/upgrade/PRODDB/logs/upg_summary.log
# cat /home/oracle/working/upgrade/PRODDB/logs/catupgrd0.log | grep "^ORA-"

Note: below error is ignorable based on Doc ID 2325124.1

SP2-0310: unable to open file

  1. Run utlrp.sql script on the upgraded database.
SQL> @/u01/app/oracle/product/
  1. Confirm all DB components are VALID and version is
SQL> select substr(comp_id,1,15) comp_id,substr(comp_name,1,30)
comp_name,substr(version,1,10) version,status from dba_registry;

  1. Shutdown the new upgraded database and startup in normal mode.
SQL> shutdown immediate
SQL> startup
  1. Switch applications to connect to the new target database.


Upgrade Time Zone – Optional

If your time zone is too old, you can upgrade it by following steps in MOS Doc ID 1509653.1


Setting max_string_size=extended – optional

In Oracle 12.2 a new feature has been released that enables the 32767 byte limit for VARCHAR2, NVARCHAR2, and RAW data types in SQL. Activate this feature following the next steps:

SQL> shutdown immediate
SQL> startup upgrade
SQL> alter system set MAX_STRING_SIZE=EXTENDED scope=spfile;
SQL> @?/rdbms/admin/utl32k.sql
SQL> shutdown immediate
SQL> startup

That’s all! Enjoy your coming upgrade.

Share on:

More from this Author

Oracle 23c INTERVAL Data Type Aggregations

MySQL: Updating the Configuration of a MySQL DB System on OCI

Overview: A MySQL configuration is a collection of variables that define the operation of a MySQL DB system. It is analogous to ... Read More

Oracle 23c INTERVAL Data Type Aggregations

Oracle 23c: INTERVAL Data Type Aggregations

Overview: Oracle 23c introduces the use of SUM and AVG functions with INTERVAL datatype This enhancement makes it easier to calculate totals and ... Read More

Back to Top