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=noSee 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
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
Step 8: Make an entry for TEST11G DB in oratab and Prepare init file
Step 9: Restore Database
Step11: Start upgrade mode of AutoUpgrade utility
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:
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.
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 ~]$
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 fileUpgrade 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=UPGTESTNote: 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;
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:
Happy Upgrading...!!!
