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 126.96.36.199 on Windows to 188.8.131.52 on Linux with minimum downtime.
Starting Oracle Database 184.108.40.206 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 220.127.116.11 database on the target system using 18.104.22.168 Oracle home and keep it in sync with the original database, and during the cutover, the activated standby database will be upgraded to 22.214.171.124.
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 126.96.36.199.
Create a Physical Standby Phase
Below is a high-level action plan for creating the physical standby database on the target system using 188.8.131.52 home.
- Download and run Oracle database 184.108.40.206 Pre-Upgrade utility on the source 220.127.116.11 database on Windows (MOS Doc ID 884522.1).
- Review Pre-Upgrade utility log file (log).
- Run Pre-Upgrade utility sqlscript on the source database.
- Confirm the source database is in ARCHIVELOG mode.
- Enable FORCE_LOGGING on the source database.
- Create standby redo log groups on the source database.
- Copy source database password file to the target system.
- Configure TNS connections for source and target databases on the source and target systems.
- 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.
- Create a standby database on the target system using source database RMAN backup.
- Setup DG initialization parameters on the source and target databases.
- Start the managed recovery process (MRP) on the standby database.
- Monitor standby replication and confirm it is working and standby database running on the target system from 18.104.22.168 home is in sync with the source 22.214.171.124 database.
Cutover Phase (30 min downtime)
- Stop applications running against the source 126.96.36.199 database.
- Make sure all changes have been shipped to the 188.8.131.52 standby database and applied.
- On source database D:\oracle\product\184.108.40.206\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/220.127.116.11/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;
- Shutdown 18.104.22.168 source database running on Windows.
- Stop managed recovery process (MRP) on the target standby database.
SQL> alter database recover managed standby database cancel; Database altered.
- 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
- 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.
- Upgrade target database to 22.214.171.124.
# 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
- 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
- Run utlrp.sql script on the upgraded database.
- Confirm all DB components are VALID and version is 126.96.36.199.
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;
- Shutdown the new upgraded database and startup in normal mode.
SQL> shutdown immediate SQL> startup
- Switch applications to connect to the new 188.8.131.52 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.