Written by Nassyam Basha and Monowar Mukul
Introduction
In this article, we will learn about the concepts of AutoUpgrade, a 19c new feature, and also we will do an upgrade of the Oracle Database from 12.2 to 21.3 using the new AutoUpgrade tool on the Linux Platform.
Using the AutoUpgrade, there are different ways to upgrade the Database.
- AutoUpgrade DBUA method using GUI
- Traditional & Manual method using the script preupgrade.jar
The mode can be chosen based on customer accessibility and restrictions.
The AutoUpgrade is now much improved with 21c by adding more options to do the upgrades. The master document for the AutoUpgrade tool is AutoUpgrade Tool (Doc ID 2485457.1). This MOS document can download the latest and most excellent tool, installation methods, Version compatibility, documentation, etc.
We will go through the key features of the AutoUpgrade tool.
- It acts as a ninja tool, where it can upgrade more than one or a hundred databases with a single click.
- Flexibility to the customer to run either as GUI or CLI
- All-In-One Utility, with no human intervention. Why?
- Self Pre-checks
- Self Automatic Fixups
- Executes/Upgrades
- Post-Upgrade Tasks
- Internal retries and fallbacks.
- Schedule your database upgrades at your convenience
- Conversion and Upgrade of the Non-CDB to PDB into 21c. Earlier it was a complex task without AutoUpgrade now;, it is a piece of cake.
- Unplug/Plug from Source/Target + Upgrade in one click.
- Retry, Resume, Fallback at any point of time during the upgrade process
- Extended AutoUpgrade support to the Oracle Restart and Oracle RAC as well.
- Upgrades Data Guard environments.
- OEM, DBUA, ORAchk integrated with AutoUpgrade.
- & many more
Before you plan AutoUpgrade your environment, you must read these documents:
- AutoUpgrade Tool (Doc ID 2485457.1)
- https://mikedietrichde.com/2019/04/29/the-new-autoupgrade-utility-in-oracle-19c/
AutoUpgrade Functionality
Consider we have Oracle Database 12.2 is running, and we are about to do the upgrade from 12.2. to 21c using the AutoUpgrade Tool. Before doing the test case, let’s understand the functionality and the stages designed for the AutoUpgrade Tool.
AutoUpgrade Processing Mode stages/Phases
- ANALYZE
- FIXUP
- DEPLOY
- UPGRADE
The details of each node are described in Oracle Document. Indeed the AutoUpgrade stages are to be performed in sequential order, and we will go through briefly each of them.
- Analyze Mode: Setup, Prechecks.
- Fixups Mode: Setup, Prechecks, and Prefixups. After Analyze mode, if any potential errors/warnings are found, then ensure to resolve them.
- Deploy Mode: In this mode, an actual database upgrade will be done, and it takes ownership starting from source pre-upgrade analysis until post-upgrade checks. The beautiful feature of the Deploy mode is if we run directly Deploy mode without Analyze and Fixup. Automatically internally, it performs the Analyze, fixup, and then performs Deploy mode.
- Upgrade Mode: This mode suits well for the platform migration along with the upgrade.
AutoUpgrade Processing Model (Database Upgrade Guide)
- Download the latest version of the AutoUpgrade tool autoupgrade.jar file from the MOS ID (2485457.1). By default, this file is available as part of the Oracle Home from 19c but is highly recommended to download and use the latest available version.
- Create a sample Auto-Upgrade config file.
AutoUpgrade Preparation
- Create the necessary directories (example- dbupg12cto21c) for storing all the config files, log files during the upgrade process
mkdir /home/oracle/dbupg12cto21c
mkdir -p /home/oracle/dbupg12cto21c/upg_logs
mkdir -p /home/oracle/dbupg12cto21c/upg_logs/madhobi
- Check Java and the AutoUpgrade jar version
- Create the sample config file
Go to the our staging directory and use the syntax “-create_sample_file” along with the AutoUpgrade tool, like below. Always its good idea to check the syntax before executing.
cd /home/oracle/dbupg12cto21c
oracle:devops:> $ORACLE_HOME/jdk/bin/java -jar /tmp/autoupgrade.jar -create_sample_file –help
Created sample configuration file /home/oracle/dbupg12cto20c/sample_config.cfg
-create_sample_file <create_sample_file> Creates a sample configuration file that be used as reference
<create_sample_file> = <config|settings> [filename] [type]
<type> = [full|unplug|noncdbtopdb]
[oracle@devops dbupg12cto21c]$ java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file config
Created sample configuration file /home/oracle/dbupg12cto21c/sample_config.cfg
- Modify sample file for the target database
Then copy the config file to some other file (for example, madhobi.cfg ) and edit it to make necessary changes as per the target environment.
[oracle@devops dbupg12cto21c]$ cat madhobi.cfg
global.autoupg_log_dir=/home/oracle/dbupg12cto21c/upg_logs/madhobiupg
global.target_home=/u01/app/oracle/product/21.3/db100
global.target_version=21
global.restoration=yes
global.run_utlrp=yes
global.timezone_upg=yes
upg1.upgrade_node=localhost
upg1.source_home=/u01/app/oracle/product/12.2/db100
upg1.sid=madhobi
upg1.dbname=madhobi
upg1.start_time=NOW
upg2.pdbs=kachu
Review carefully when entering the above details. Ensure for each AutoUpgrade run, and you have to change the directory for autoupg_log_dir.
Global.restoration refers to the guaranteed restore point, so ensure FRA is enabled with minimum 14GB of space and flashback to be enabled.
Global. restoration — this is for creating a guarantee restore point. [~14GB space for FRA]
Running AutoUpgrade.jar ANALYZE mode
Before starting the analyze mode, you can keep the PDB’s in OPEN status. AutoUpgrade can itself Open/Read Write if the PDB’s are in not up and running.
[oracle@devops dbupg12cto21c]$ java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config madhobi.cfg -mode ANALYZE
AutoUpgrade 21.2.210721 launched with default options
Processing config file …
Pluggable database KACHU in madhobi is MOUNTED and it will not be processed
+ — — — — — — — — — — — — — — — — +
| Starting AutoUpgrade execution |
+ — — — — — — — — — — — — — — — — +
1 databases will be analyzed
Type’ help’ to list console commands
Note: Whenever we run AutoUpgrade Deploy or Analyze, it takes us to the AutoUpgrade Command prompt.
Now check the status, if done it will prompt you on the screen. We can monitor, review and control the job status from the AutoUpgrade console. The most commonly used commands are
lsj: to list the jobs
status: To show the job status
tasks: shows the tasks executing
We can keep monitoring the job status by “lsj.” Once the AutoUpgrade is finished in ANALYZE mode, we can review the HTML output of the script under specified the log directory or review the log file, which contains the same information.
The HTML file is so beautified and easy to verify the status of the job of each level. If there are any issues, we have to fix them accordingly.
AutoUpgrade Deploy Mode
Once the “Analyze” mode is a success, we can proceed with the DEPLOY mode using below command. Autoupgrade Deploy mode performs the actual upgrade of the database from preugrade source database analysis to post-upgrade checks.
[oracle@devops dbupg12cto21c]$ java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config madhobi.cfg -mode DEPLOY
AutoUpgrade 21.2.210721 launched with default options
Processing config file …
+——————————–+
| Starting AutoUpgrade execution |
+——————————–+
1 databases will be processed
upg> status
In parallel, we can also tail the log file of the upgrade process. To view the log location, use the below command.
upg> logs
AutoUpgrade logs folder [/home/oracle/dbupg12cto21c/upg_logs/madhobiupg/cfgtoollogs/upgrade/auto]
logs folder [madhobi][/home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi]
From Second session:
[oracle@devops dbupg12cto21c]$ cd /home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi
[oracle@devops madhobi]$ ls
100 101 temp
[oracle@devops madhobi]$ cd 101
[oracle@devops 101]$ ls
autoupgrade_20210820.log autoupgrade_20210820_user.log autoupgrade_err.log dbupgrade prechecks preupgrade
autoupgrade_20210820.log.lck autoupgrade_20210820_user.log.lck autoupgrade_err.log.lck drain prefixups
upg> tasks
+—+——————+————-+
| ID| NAME| Job#|
+—+——————+————-+
| 1| main| WAITING|
| 43| event_loop|TIMED_WAITING|
| 44| console| RUNNABLE|
| 45| queue_reader| WAITING|
| 46| cmd-0| WAITING|
| 47| job_manager-0| WAITING|
| 48| bqueue-101| WAITING|
|208|actionworker-101-0| WAITING|
|209|actionworker-101-1|TIMED_WAITING|
|211| madhobi-puifxl0-0| WAITING|
|212| madhobi-puifxl0-1| WAITING|
|221| quickSQL| RUNNABLE|
+—+——————+————-+
Similarly, review the status of the job by using the “lsj” command as below. It does also shows the message information what currently it is ding and which task is being performed.
To view the specific job, we can pass the job id using the below command.
Upon the successful completion of the database upgrade job, we will get a message like as below as the job finished count to [1]
upg> Job 101 completed
——————- Final Summary ——————–
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Jobs pending [0]
—- Drop GRP at your convenience once you consider it is no longer needed —-
Drop GRP from madhobi: drop restore point AUTOUPGRADE_9212_MADHOBI_UN122010
Please check the summary report at:
/home/oracle/dbupg12cto21c/upg_logs/madhobi/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/dbupg12cto21c/upg_logs/madhobi/cfgtoollogs/upgrade/auto/status/status.log
Review the upgrade log and see if any potential errors/warnings.
[oracle@devops dbupg12cto21c]$ cat /home/oracle/dbupg12cto21c/upg_logs/madhobiupg/cfgtoollogs/upgrade/auto/status/status.log
==========================================
Autoupgrade Summary Report
==========================================
[Date] Sat Aug 21 00:24:55 AEST 2021
[Number of Jobs] 1
==========================================
[Job ID] 101
==========================================
[DB Name] madhobi
[Version Before Upgrade] 12.2.0.1.0
[Version After Upgrade] 21.3.0.0.0
——————————————
[Stage Name] GRP
[Status] SUCCESS
[Start Time] 2021-08-20 18:29:17
[Duration] 0:00:01
[Detail] Please drop the following GRPs after Autoupgrade completes:
AUTOUPGRADE_9212_MADHOBI122010
——————————————
[Stage Name] PREUPGRADE
[Status] SUCCESS
[Start Time] 2021-08-20 18:29:18
[Duration] 0:00:00
[Log Directory] /home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi/101/preupgrade
——————————————
[Stage Name] PRECHECKS
[Status] SUCCESS
[Start Time] 2021-08-20 22:20:50
[Duration] 0:00:26
[Log Directory] /home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi/101/prechecks
[Detail] /home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi/101/prechecks/madhobi_preupgrade.log
Precheck passed and no manual intervention needed
——————————————
[Stage Name] PREFIXUPS
[Status] FAILURE
[Start Time] 2021-08-20 22:21:17
[Duration] 0:00:33
[Log Directory] /home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi/101/prefixups
——————————————
[Stage Name] DRAIN
[Status] SUCCESS
[Start Time] 2021-08-20 22:21:50
[Duration] 0:00:34
[Log Directory] /home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi/101/drain
——————————————
[Stage Name] DBUPGRADE
[Status] SUCCESS
[Start Time] 2021-08-20 22:22:24
[Duration] 1:36:14
[Log Directory] /home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi/101/dbupgrade
——————————————
[Stage Name] POSTCHECKS
[Status] SUCCESS
[Start Time] 2021-08-20 23:58:39
[Duration] 0:00:50
[Log Directory] /home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi/101/postchecks
[Detail] /home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi/101/postchecks/madhobi_postupgrade.log
——————————————
[Stage Name] POSTFIXUPS
[Status] SUCCESS
[Start Time] 2021-08-20 23:59:29
[Duration] 0:25:11
[Log Directory] /home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi/101/postfixups
——————————————
[Stage Name] POSTUPGRADE
[Status] SUCCESS
[Start Time] 2021-08-21 00:24:41
[Duration] 0:00:13
[Log Directory] /home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi/101/postupgrade
——————————————
[Stage Name] SYSUPDATES
[Status] SUCCESS
[Start Time] 2021-08-21 00:24:55
[Duration] 0:00:00
[Log Directory] /home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi/101/sysupdates
——————————————
Upgrade Summary: /home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi/101/dbupgrade/upg_summary.log
Now, let’s review the upgrade summary log as below. Under the Log Directory we can see the file named “upg_summary_report.log”, in our case the log directory is /home/oracle/dbupg12cto21c/upg_logs/madhobi/madhobi/101/dbupgrade and our job number is 101.
/home/oracle/dbupg12cto21c/upg_logs/madhobiupg/madhobi/101/dbupgrade
[oracle@devops dbupgrade]$ cat upg_summary.log
Oracle Database Release 21 Post-Upgrade Status Tool 08-20-2021 23:07:5
Container Database: MADHOBI
[CON_ID: 1 => CDB$ROOT]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 21.3.0.0.0 00:20:27
JServer JAVA Virtual Machine UPGRADED 21.3.0.0.0 00:05:22
Oracle XDK UPGRADED 21.3.0.0.0 00:01:55
Oracle Database Java Packages UPGRADED 21.3.0.0.0 00:00:26
OLAP Analytic Workspace UPGRADED 21.3.0.0.0 00:00:42
Oracle Label Security UPGRADED 21.3.0.0.0 00:01:41
Oracle Database Vault UPGRADED 21.3.0.0.0 00:01:23
Oracle Text UPGRADED 21.3.0.0.0 00:01:41
Oracle Workspace Manager UPGRADED 21.3.0.0.0 00:01:18
Oracle Real Application Clusters UPGRADED 21.3.0.0.0 00:00:01
Oracle XML Database UPGRADED 21.3.0.0.0 00:02:14
Oracle Multimedia UPGRADED 21.3.0.0.0 00:02:27
Spatial UPGRADED 21.3.0.0.0 00:09:36
Oracle OLAP API UPGRADED 21.3.0.0.0 00:00:32
Oracle Locator UPGRADED 21.3.0.0.0 00:00:00
Datapatch 00:00:20
Final Actions 00:00:38
Post Upgrade 00:00:01
Total Upgrade Time: 00:43:55 [CON_ID: 1 => CDB$ROOT]
Database time zone version is 26. It is older than current release time
zone version 35. Time zone upgrade is needed using the DBMS_DST package.
Oracle Database Release 21 Post-Upgrade Status Tool 08-20-2021 23:56:0
Container Database: MADHOBI
[CON_ID: 3 => KACHU]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 21.3.0.0.0 00:20:21
JServer JAVA Virtual Machine UPGRADED 21.3.0.0.0 00:06:04
Oracle XDK UPGRADED 21.3.0.0.0 00:00:51
Oracle Database Java Packages UPGRADED 21.3.0.0.0 00:00:08
OLAP Analytic Workspace UPGRADED 21.3.0.0.0 00:02:01
Oracle Label Security UPGRADED 21.3.0.0.0 00:00:10
Oracle Database Vault UPGRADED 21.3.0.0.0 00:00:39
Oracle Text UPGRADED 21.3.0.0.0 00:01:17
Oracle Workspace Manager UPGRADED 21.3.0.0.0 00:01:47
Oracle Real Application Clusters UPGRADED 21.3.0.0.0 00:00:01
Oracle XML Database UPGRADED 21.3.0.0.0 00:02:12
Oracle Multimedia UPGRADED 21.3.0.0.0 00:01:00
Spatial UPGRADED 21.3.0.0.0 00:06:41
Oracle OLAP API UPGRADED 21.3.0.0.0 00:00:22
Final Actions 00:00:21
Post Upgrade 00:00:01
Total Upgrade Time: 00:38:15 [CON_ID: 3 => KACHU]
Database time zone version is 26. It is older than current release time
zone version 35. Time zone upgrade is needed using the DBMS_DST package.
Oracle Database Release 21 Post-Upgrade Status Tool 08-20-2021 23:56:0
Container Database: MADHOBI
[CON_ID: 2 => PDB$SEED]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 21.3.0.0.0 00:20:26
JServer JAVA Virtual Machine UPGRADED 21.3.0.0.0 00:06:04
Oracle XDK UPGRADED 21.3.0.0.0 00:00:51
Oracle Database Java Packages UPGRADED 21.3.0.0.0 00:00:08
OLAP Analytic Workspace UPGRADED 21.3.0.0.0 00:01:45
Oracle Label Security UPGRADED 21.3.0.0.0 00:00:20
Oracle Database Vault UPGRADED 21.3.0.0.0 00:00:46
Oracle Text UPGRADED 21.3.0.0.0 00:01:16
Oracle Workspace Manager UPGRADED 21.3.0.0.0 00:01:44
Oracle Real Application Clusters UPGRADED 21.3.0.0.0 00:00:05
Oracle XML Database UPGRADED 21.3.0.0.0 00:02:11
Oracle Multimedia UPGRADED 21.3.0.0.0 00:01:00
Spatial UPGRADED 21.3.0.0.0 00:06:41
Oracle OLAP API UPGRADED 21.3.0.0.0 00:00:23
Final Actions 00:00:22
Post Upgrade 00:00:01
Total Upgrade Time: 00:38:22 [CON_ID: 2 => PDB$SEED * ]
Asterisks denotes compilation time has been included during the upgrade process.
Database time zone version is 26. It is older than current release time
zone version 35. Time zone upgrade is needed using the DBMS_DST package.
Upgrade Times Sorted In Descending Order
Total Upgrade Time: 00:43:55 [CON_ID: 1 => CDB$ROOT]
Total Upgrade Time: 00:38:22 [CON_ID: 2 => PDB$SEED * ]
Total Upgrade Time: 00:38:15 [CON_ID: 3 => KACHU]
Grand Total Upgrade Time: [0d:1h:35m:29s]
Validate the Database
SQL> select BANNER from v$version;
BANNER
—————————————————————
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
SQL> set pagesize 120
SQL> set linesize 180
SQL> select con_id,name from v$restore_point;
CON_ID NAME
———- —————————————–
0 AUTOUPGRADE_9212_MADHOBI122010
SQL> show parameter pfile;
NAME TYPE VALUE
————- ———– ——————-
spfile string /u01/app/oracle/dbs/spfilemadhobi.ora
SQL> select count(*) from dba_invalid_objects;
COUNT(*)
———-
0
Verify Timezone File
SQL> SELECT VERSION FROM V$TIMEZONE_FILE;
VERSION
———-
35
SQL> SELECT DBMS_DST.get_latest_timezone_version FROM DUAL;
GET_LATEST_TIMEZONE_VERSION
—————————
35
SQL> alter session set container=KACHU;
Session altered.
SQL> SELECT VERSION FROM V$TIMEZONE_FILE;
VERSION
———-
35
As we can see, the latest timezone version is 35, so autoupgrade utility was upgraded to it both for CDB and PDB.
Update Compatible Parameter Value
SQL> conn / as sysdba
Connected.
SQL> show parameter compatible
NAME TYPE VALUE
———————————— ———– ——————-compatible string 12.2.0
noncdb_compatible boolean FALSE
SQL> DROP RESTORE POINT &RP;
Enter value for rp: AUTOUPGRADE_9212_MADHOBI122010
old 1: DROP RESTORE POINT &RP
new 1: DROP RESTORE POINT AUTOUPGRADE_9212_MADHOBI122010
Restore point dropped.
SQL> alter system set compatible=’21.3.0′ scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1577057552 bytes
Fixed Size 9686288 bytes
Variable Size 385875968 bytes
Database Buffers 1174405120 bytes
Redo Buffers 7090176 bytes
Database mounted.
Database opened.
SQL> show parameter compatible
NAME TYPE VALUE
———————————— ———– ———
compatible string 21.3.0
noncdb_compatible boolean FALSE
NOTE: To exit or rerun
After validating all the upgrade logs and details, we can exit from the command line as below. If any failed/pending jobs are available, it shows the information.
Summary
We’ve seen how to upgrade the Oracle Single Instance database using the AutoUpgrade tool for both the container and pluggable database from Oracle RDBMS 12.2 to Oracle RDBMS 21.3.
- Let’s review the summary of the article. We’ve learned the
- Introduction and the key benefits/features of the AutoUpgrade Utility.
- The functionality of the AutoUpgrade Tool and its modes.
- AutoUpgrade Preparation and the readiness of the environment
- Verified the versions of AutoUpgrade and Java.
- Performed Analyze and Upgrade phases of the AutoUpgrade.
- Various CLI commands to interpret the Jobs status
- Understating the HTML report of analyze mode and locating the log files of the analyze mode.
- Deploy mode: Monitoring the jobs and troubleshooting the deploy mode from the logs.
- Validating the database status, version, time zone versions, Registry components status.
References
- Using AutoUpgrade for Oracle Database Upgrades
- AutoUpgrade Tool (Doc ID 2485457.1)
- Troubleshooting, Restoring and Restarting AutoUpgrade
About the Authors
Nassyam Basha is a Database Administrator. He has more than 13 years of experience as a Production Oracle DBA. He holds a master’s degree in Computer Applications from the University of Madras. He is an Oracle 11g Certified Master and honored as Oracle ACE Director from Oracle Corp. He actively participates in Oracle-related forums such as ODevC/Hub with a status of Silver Crown, Oracle support awarded as “Guru” and acting as Oracle Developer Community Moderator and written 60+ articles with OTN, Dell, Toad World, UKOUG, Quest Community, etc. He holds the prestigious “Select Journal Editor’s Choice Award” in 2018 and 2020 based on the Quest Community’s best Oracle technical article. He is a board member for the KSAOUG, MAA SIG, and IDGOUG Oracle User Groups. He maintains an Oracle technology-related blog, www.oracle-ckpt.com, and can be reached at https://www.linkedin.com/in/nassyambasha/
Monowar Mukul is currently working as a Principal Oracle Database Specialist. I am an Oracle Certified Master (Oracle 12c Certified Master Administration, Oracle 12c Certified Master MAA, and Oracle 11g Certified Master Administration). He has been working as an Oracle DBA consultant for over 17 years with Oracle MAA space for Exadata and non-Exadata Systems, Oracle Cloud Space, and SOA Middleware. He worked across various business sectors, including tertiary education, energy, government, mining, and transport in Australia. He demonstrated highly developed critical thinking and analytical skills working as a Principal Oracle Database Specialist. You can find more details about him and his work achievements at https://www.linkedin.com/in/monowarmukul/