Blogs

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

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

Starting Oracle Database 11.2.0.1 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 12.1.0.2 database on the target system using 12.2.0.1 Oracle home and keep it in sync with the original database, and during the cutover, the activated standby database will be upgraded to 12.2.0.1.

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 12.2.0.1.

 

Create a Physical Standby Phase

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

1. Download and run Oracle database 12.2.0.1 Pre-Upgrade utility on the source 12.1.0.2 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 12.2.0.1 home is in sync with the source 12.1.0.2 database.

 

Cutover Phase (30 min downtime)

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

- On source database

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

- On target standby database

# export ORACLE_SID=PRODDB
# export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
# export PATH=$PATH:$ORACLE_HOME/bin
# sqlplus / as sysdba
SQL> select inst_id,PROCESS, STATUS, THREAD#, SEQUENCE#,BLOCK#, BLOCKS FROM GV$MANAGED_STANDBY order by inst_id;
SQL> select max(sequence#) from v$archived_log where applied='YES';
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

3. Shutdown 12.1.0.2 source database running on Windows.
4. Stop managed recovery process (MRP) on the target standby database.

SQL> alter database recover managed standby database cancel;

Database altered.

5. Activate the target standby database.

SQL> alter database activate standby database;

Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          MOUNTED

SQL> select controlfile_type from v$database;

CONTROL
-------
CURRENT

6. 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.

7. Upgrade target database to 12.2.0.1.

# 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

8. 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
“/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/dbmsclr.plb”

9. Run utlrp.sql script on the upgraded database.

SQL> @/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/utlrp.sql

10. Confirm all DB components are VALID and version is 12.2.0.1.

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;

11. Shutdown the new upgraded database and startup in normal mode.

SQL> shutdown immediate
SQL> startup

12. Switch applications to connect to the new 12.2.0.1 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.

More from this Author