Cross platform 11g (Windows) to 19c (Linux) upgrade and migration using AutoUpgrade utility

Source and Target details are as given below

In this post, let's look at the procedure on how to migrate and upgrade the database version 11.2.0.4 running on Windows OS to database version 19.12 running on Linux OS (Exadata in this case) and plug into an existing CDB using AutoUpgrade utility. 

Step 0: Download AutoUpgrade utility from Doc ID 2485457.1 and install latest Java from Java Downloads

AutoUpgrade utility is java based and generic for all OS.

Step 1: Prepare config files for AutoUpgrade utility

Create a config file for analyze and fix up phase of the AutoUpgrade.

global.autoupg_log_dir=C:\Oracle4Win\AutoUpgrade\logs
#
# Database number 1 
#
upg1.dbname=TEST11G
upg1.start_time=NOW
upg1.source_home=C:\Oracle11g\product\11.2.0\dbhome_1
upg1.target_home=/tmp
upg1.sid=TEST11G
upg1.log_dir=C:\Oracle4Win\AutoUpgrade\logs
upg1.upgrade_node=localhost
upg1.target_version=19
upg1.restoration=no

See that I have mentioned target_home as /tmp as we will not perform the upgrade in source server.

Step 2: Run the analyze mode of AutoUpgrade

DB environment is as below:

set ORACLE_SID=TEST11G
set ORACLE_HOME=C:\Oracle11g\product\11.2.0\dbhome_1
set PATH=C:\Oracle11g\product\11.2.0\dbhome_1\bin;%PATH%

Command used is as below:

C:\oracle\product\19.0.0\jdk\bin\java -jar C:\Oracle4Win\AutoUpgrade\autoupgrade.jar -config C:\Oracle4Win\AutoUpgrade\test11g.cfg -mode analyze

Note: Don't confuse with the 19c patch I'm using for java as I have the latest java needed in the same source server in the 19c home. You can download and install latest java without 19c on source. Actual analyze is done on the 11g database with 11g home

We can use lsj command within the utility to list the jobs with Summary

Step 3: Check status file for any action to be taken.

Though there are no errors in the status file, Oracle recommendations are to be followed for optimum upgrade and post upgrade performance. We can click on Precheck Report to go through all the recommendations provided by the utility if needed.

C:\Oracle4Win\AutoUpgrade\logs\cfgtoollogs\upgrade\auto\status

Step 4: Start fixups mode of AutoUpgrade utility

Command used is as below:

C:\oracle\product\19.0.0\jdk\bin\java -jar C:\Oracle4Win\AutoUpgrade\autoupgrade.jar -config C:\Oracle4Win\AutoUpgrade\test11g.cfg -mode fixups

Let us now see a few snaps of PRECHECKS progress...

Let us now see a few snaps of PREFIXUPS progress...

The below command will details of the specific job which we are interested in.. 

status -job <jobnumber>

Step 5: Check status file for any action to be taken.

The prechecks passed and no manual intervention needed in our case. If we click on the Prechecks Report and you will see all the details of what the software has tested, whether any user action required, any errors, warnings reported and the informational messages as well.

Mostly, the fixup stage will fix issues like purging recycle bin, gathering stats prior to upgrade, suggesting parameter values removal/changes, suggest removal of deprecated components, etc., 

If there are errors reported, they need to be taken care before attempting upgrade. 

C:\Oracle4Win\AutoUpgrade\logs\cfgtoollogs\upgrade\auto\status

This concludes the AutoUpgrade utility activity on source server. We have now prepared our source database to be upgrade ready. 

Step 6: Make a consistent backup to avoid issues during upgrade.

Consistent backup is needed here as the source is Windows and target is Linux (Cross Platform) where recovery of windows archives on Linux is not supported.

• Mount DB and trigger RMAN full backup
• Transfer the Backup files and initSID file to target server

SQL> create pfile='C:\SharedPath\Backup\initTEST11G.ora' from spfile;
File created.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  563691520 bytes
Fixed Size                  2283224 bytes
Variable Size             385878312 bytes
Database Buffers          167772160 bytes
Redo Buffers                7757824 bytes
Database mounted.
SQL> exit

C:\windows\system32>rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Oct 4 14:15:37 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST11G (DBID=1234192708, not open)

RMAN> backup database format 'C:\SharedPath\Backup\%U' include current controlfile;

Starting backup at 04-OCT-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
…
….
…
channel ORA_DISK_1: starting piece 1 at 04-OCT-21
channel ORA_DISK_1: finished piece 1 at 04-OCT-21
piece handle=C:\SHAREDPATH\BACKUP\020APFE3_1_1 tag=TAG20211004T141619 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-OCT-21

RMAN> exit
Recovery Manager complete.

Step 7: Transfer the files to target

We can use copy/paste or scp or any method to transfer files needed for the restore on target server

The above step concludes all the activity that is needed on the source system. The following steps will be performed in the target system where the database will be upgraded to 19c. 

Step 8: Make an entry for TEST11G DB in oratab and Prepare init file

TEST11G database will have 19c home as the home path in the oratab file. 

initTEST11G.ora will be as below. 

[oracle@srv1 ~]$ more /u02/app/oracle/product/19.0.0.0/dbhome_2/dbs/initTEST11G.ora
*.audit_file_dest='/u02/app/oracle/product/19.0.0.0/dbhome_2/rdbms/audit'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATAC3/WNCCPD01/CONTROLFILE/test11g_control.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test11g'
*.db_recovery_file_dest='+RECOC3'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u02/app/oracle'
*.memory_target=3G
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_create_file_dest='+DATAC3'
*.db_create_online_log_dest_1='+DATAC3'
[oracle@srv1 ~]$

Step 9: Restore Database

I have just provided the commands that needs to be run for restoring the database and not the output. We need to open the database in upgrade mode after restore as we are restoring 11g database under 19c home. 

## Mount the database
SQL> startup nomount


## Start RMAN and run the restore command
rman target /
run
{
restore controlfile from '/oradump/selva/TEST11G/020APFE3_1_1';
alter database mount;
catalog backuppiece '/oradump/selva/TEST11G/010APFDJ_1_1';
set newname for database to '+DATAC3';
set newname for tempfile 1 to ‘+DATAC3’;
restore database;
switch datafile all;
switch tempfile all;
}
RMAN> exit

## Rename the logfiles to proper location and open DB resetlogs in upgrade mode. 
sqlplus / as sysdba
SQL> select member from v$logfile;
SQL> alter database rename file 'C:\ORACLE11G\ORADATA\TEST11G\REDO03.LOG' to '+DATAC3/TEST11G/DATAFILE/REDO03.LOG';
SQL> alter database rename file 'C:\ORACLE11G\ORADATA\TEST11G\REDO02.LOG' to '+DATAC3/TEST11G/DATAFILE/REDO02.LOG';
SQL> alter database rename file 'C:\ORACLE11G\ORADATA\TEST11G\REDO01.LOG' to '+DATAC3/TEST11G/DATAFILE/REDO01.LOG';
SQL> alter database open resetlogs upgrade;
SQL> exit

Step 10: Prepare target config file

Upgrade configuration file will be as follows

[oracle@srv1]$ more upgtest.cfg
global.autoupg_log_dir=/home/oracle/logs
upg1.dbname=TEST11G
upg1.start_time=NOW
upg1.source_home=/u02/app/oracle/product/19.0.0.0/dbhome_2
upg1.target_home=/u02/app/oracle/product/19.0.0.0/dbhome_2
upg1.sid=TEST11G
upg1.log_dir=/home/oracle/logs
upg1.upgrade_node=localhost
upg1.target_version=19
upg1.restoration=no
upg1.target_cdb=WNCCPD018
upg1.target_pdb_name=UPGTEST
 

Note: I'm providing the target_pdb_name with a different name from the original name. Also note that we are not attempting to plug the database into an existing container database. 

Step11: Start upgrade mode of AutoUpgrade utility

Command will be as follows:  

java -jar /dcwnec_oradump/selva/xxx_TEST11G/autoupgrade.jar -config /dcwnec_oradump/selva/xxx_TEST11G/upgtest.cfg -mode upgrade

Let's see a few progress snaps. The upgrade process will take around 60 to 120 minutes depending upon various factors. So we can check the progress using lsj command as below

Detailed log will have the below entry..

All the below mentioned tasks will automatically be taken care. Config file should have details regarding keys if source is encrypted with TDE. Or the stage will error which then can be fixed manually by importing keys.

Few more status snaps before we conclude...

The upgrade process is completed and now we can see the PDB is created with the name we have provided in the config file. 

Step 12: Post Upgrade:

Run below query to identify PDB violations and fix accordingly.

column message format a50
column status format a9
column type format a9
column con_id format 9
column name for a30
select con_id, name, type, message, status from PDB_PLUG_IN_VIOLATIONS
where status<>'RESOLVED' order by time;

These violations will not stop the database from running normally as these are only WARNING messages but we need to take care of fixing the violations. 

If we get an ERROR message, we will not be able to open the PDB in read write mode which requires the violation to be fixed before opening the PDB in read write mode. 

With this, we are now concluding the upgrade exercise and the database is successfully upgraded from 11g running on windows to 19c running on Linux. 

References: 

Oracle AutoUpgrade between two servers (mikedietrichde.com)

About Oracle Database AutoUpgrade

Happy Upgrading...!!!