Oracle Database Autoupgrade Utility is a new feature designed in Oracle 19c to automate the Upgrade process which Identifies issues before upgrade, Performs Preupgrade actions, Deploying the upgrades and Performs Post upgrade actions . You can upgrade multiple databases at the same time using a single configuration file.
Below i did for testing purpose only . I personally use dbua .
AutoUpgrade Tool | AutoUpgrade - 4 modes
Below are the High level steps:
Install Oracle 19.3.0.0 binaries
Prerequisite for Autoupgrade
Create the config file
Analyze the database
Deploy the upgrade
Post upgrade task
Environment Details:
Source Hostname: new19c
Database version: 12.2.0.1
Database Name: Abdultest
ORACLE_HOME: /u01/app/oracle/product/12.2.0/db_1/
Target Hostname: new19c
Databaes Version: 19.3.0.0
Database name: Abdultest
ORACLE_HOME: /u01/app/oracle/product/19.3.0/dbhome_1
Source DB Details
SQL> select name, open_mode, version, status from v$database, v$instance;
NAME OPEN_MODE VERSION STATUS
--------- -------------------- ----------------- ------------
ABDULTEST READ WRITE 12.2.0.1.0 OPEN
1. Install Oracle 19.3.0.0 binaries
I have already completed the installation of 19c, Refer here for step by step installation of Oracle 19.3.0.0 on Linux 7 64bit.
Autoupgrade utility autoupgrade.jar file exists by default under $ORACLE_HOME/rdbms/admin directory from Oracle 19.3 release onwards, however Oracle strongly recommends to download the latest AutoUpgrade version before doing the upgrade. Refer 2485457.1
Replace the autoupgrade.jar with the latest version downloaded
[oracle@new19c ~]$ mv $ORACLE_HOME/rdbms/admin/autoupgrade.jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar-bkp
[oracle@new19c ~]$ cp /tmp/autoupgrade.jar $ORACLE_HOME/rdbms/admin/
[oracle@new19c ~]$
build.version 19.10.0
build.date 2020/10/23 10:36:46
build.max_target_version 19
build.supported_target_versions 12.2,18,19
build.type production
Java version should be 8 or later, which is available by default in Oracle Database homes from release
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
[oracle@new19c ~]$ cd /u01/19c-autoupg
cd /u01/19c-autoupg
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/jdk/bin
Created sample configuration file /u01/19c-autoupg/sample_config.cfg
cd /u01/19c-autoupg
cp sample_config.cfg abdultest_db_config.cfg
vi abdultest_db_config.cfg
[oracle@new19c 19c-autoupg]$ cat abdultest_db_config.cfg
global.autoupg_log_dir=/u01/19c-autoupg/upg_logs
#
# Database abdultest
#
upg1.dbname=abdultest
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/12.2.0/db_1/
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.sid=abdultest
upg1.log_dir=/u01/19c-autoupg/upg_logs/abdultest
upg1.upgrade_node=new19c
upg1.target_version=19.3
upg1.run_utlrp=yes
upg1.timezone_upg=yes
Autoupgrade Analyze mode checks your database to see if it is ready for the upgrade. This will reads data from the database and does not perform any updates.
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/jdk/bin
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config abdultest_db_config.cfg -mode ANALYZE
[oracle@new19c 19c-autoupg]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config cdbdev_db_config.cfg -mode ANALYZE
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
| 100| cdbdev|PRECHECKS|PREPARING|RUNNING|20/11/19 03:27|03:27:28|Loading database information|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
Total jobs 1
upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished successfully [1]
Jobs failed [0]
Jobs pending [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 100 for abdultest
We can monitor, manage and control the jobs from the autoupgrade console. Example:
lsj – to list the jobs
status – to show the job status
tasks – shows the tasks executing
All Analyze logs are created under autoupg_log_dir
[oracle@new19c prechecks]$ ls -l
total 536
-rwx------. 1 oracle oinstall 5051 Nov 19 03:28 abdultest_checklist.cfg
-rwx------. 1 oracle oinstall 18050 Nov 19 03:28 abdultest_checklist.json
-rwx------. 1 oracle oinstall 17101 Nov 19 03:28 abdultest_checklist.xml
-rwx------. 1 oracle oinstall 17649 Nov 19 03:28 abdultest_vpreupgrade.log
-rwx------. 1 oracle oinstall 140241 Nov 19 03:28 prechecks_pdbdev.log
-rwx------. 1 oracle oinstall 139243 Nov 19 03:28 prechecks_pdb_seed.log
Autoupgrade Deploy mode performs the actual upgrade of the database from preugrade source database analysis to post-upgrade checks.
Note: Before deploying the upgrade, you must have a backup plan in place.
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands
upg> tasks
+--+-------------+-------------+
|ID| NAME| Job#|
+--+-------------+-------------+
| 1| main| WAITING|
|35| jobs_mon| WAITING|
|36| console| RUNNABLE|
|37| queue_reader| WAITING|
|38| cmd-0| WAITING|
|54|job_manager-0| WAITING|
|56| event_loop|TIMED_WAITING|
|57| bqueue-101| WAITING|
|61| quickSQL| RUNNABLE|
+--+-------------+-------------+
upg>
upg> logs
AutoUpgrade logs folder [/u01/19c-autoupg/upg_logs/cfgtoollogs/upgrade/auto]
logs folder [abdultest][/u01/19c-autoupg/upg_logs/abdultest/abdultest]
upg> lsj
+----+-------+---------+---------+--------+--------------+--------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+--------+--------------+--------+----------------------------+
| 101| abdultest|PREFIXUPS|EXECUTING|FINISHED|20/11/19 03:46|03:48:44|Loading database information|
+----+-------+---------+---------+--------+--------------+--------+----------------------------+
Total jobs 1
upg> lsj
+----+-------+-----+---------+-------+--------------+--------+----------------------+
|Job#|DB_NAME|STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+-----+---------+-------+--------------+--------+----------------------+
| 101| abdultest|DRAIN|EXECUTING|RUNNING|20/11/19 03:46|03:48:52|Shutting down database|
+----+-------+-----+---------+-------+--------------+--------+----------------------+
Total jobs 1
upg> status
---------------- Config -------------------
User configuration file [/u01/19c-autoupg/abdultest_db_config.cfg]
General logs location [/u01/19c-autoupg/upg_logs/cfgtoollogs/upgrade/auto]
Mode [DEPLOY]
DB upg fatal errors ORA-00600,ORA-07445
DB Post upgrade abort time [60] minutes
DB upg abort time [1440] minutes
DB restore abort time [120] minutes
DB GRP abort time [3] minutes
------------------------ Jobs ------------------------
Total databases in configuration file [1]
Total Non-CDB being processed [0]
Total CDB being processed [1]
Jobs finished successfully [0]
Jobs finished/aborted [0]
Jobs in progress [1]
Jobs stage summary
Job ID: 101
DB name: abdultest
SETUP <1 min
GRP <1 min
PREUPGRADE <1 min
PRECHECKS <1 min
PREFIXUPS 1 min
DRAIN <1 min
DBUPGRADE 12 min (IN PROGRESS)
------------ Resources ----------------
Threads in use [32]
JVM used memory [115] MB
CPU in use [13%]
Processes in use [18]
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------------------+
| 101| cdbdev|DBUPGRADE|EXECUTING|RUNNING|20/11/19 03:46|04:38:42|70%Upgraded CDB$ROOT|
+----+-------+---------+---------+-------+--------------+--------+--------------------+
Total jobs 1
upg> /
+----+-------+---------+---------+-------+--------------+--------+--------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------------------+
| 101| cdbdev|DBUPGRADE|EXECUTING|RUNNING|20/11/19 03:46|06:31:20|95%Upgraded PDB$SEED|
+----+-------+---------+---------+-------+--------------+--------+--------------------+
Total jobs 1
upg> /
+----+-------+----------+---------+-------+--------------+--------+-------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+-------------+
| 101| cdbdev|POSTFIXUPS|EXECUTING|RUNNING|20/11/19 03:46|06:38:37|Remaining 1/9|
+----+-------+----------+---------+-------+--------------+--------+-------------+
Total jobs 1
upg> /
+----+-------+----------+---------+-------+--------------+--------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+----------------------------+
| 101| cdbdev|POSTFIXUPS|EXECUTING|RUNNING|20/11/19 03:46|06:43:37|Loading database information|
+----+-------+----------+---------+-------+--------------+--------+----------------------------+
Total jobs 1
upg> /
+----+-------+-----------+---------+-------+--------------+--------+---------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+-----------+---------+-------+--------------+--------+---------------------+
| 101| cdbdev|POSTUPGRADE|EXECUTING|RUNNING|20/11/19 03:46|06:45:16|Creating final SPFILE|
+----+-------+-----------+---------+-------+--------------+--------+---------------------+
Total jobs 1
upg> /
+----+-------+-----------+---------+-------+--------------+--------+----------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+-----------+---------+-------+--------------+--------+----------+
| 101| cdbdev|POSTUPGRADE|EXECUTING|RUNNING|20/11/19 03:46|06:46:01|Restarting|
+----+-------+-----------+---------+-------+--------------+--------+----------+
Total jobs 1
upg> Job 101 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished successfully [1]
Jobs failed [0]
Jobs pending [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 101 for cdbdev
Drop GRP from cdbdev: drop restore point AUTOUPGRADE_9212_CDBDEV122010
Check the upg_summary.log
Oracle Database Release 19 Post-Upgrade Status Tool 11-19-2020 06:30:0
Container Database: CDBDEV
[CON_ID: 2 => PDB$SEED]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.3.0.0.0 00:34:10
JServer JAVA Virtual Machine VALID 19.3.0.0.0 00:02:25
Oracle XDK VALID 19.3.0.0.0 00:01:19
Oracle Database Java Packages VALID 19.3.0.0.0 00:00:10
OLAP Analytic Workspace VALID 19.3.0.0.0 00:00:55
Oracle Label Security VALID 19.3.0.0.0 00:00:11
Oracle Database Vault VALID 19.3.0.0.0 00:03:00
Oracle Text VALID 19.3.0.0.0 00:00:42
Oracle Workspace Manager VALID 19.3.0.0.0 00:00:52
Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:00
Oracle XML Database VALID 19.3.0.0.0 00:03:32
Oracle Multimedia VALID 19.3.0.0.0 00:00:46
Spatial VALID 19.3.0.0.0 00:09:15
Oracle OLAP API VALID 19.3.0.0.0 00:00:26
Datapatch 00:04:50
Final Actions 00:05:11
Post Upgrade 00:02:06
Post Compile 00:11:29
Total Upgrade Time: 01:17:50 [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 32. Time zone upgrade is needed using the DBMS_DST package.
Upgrade Times Sorted In Descending Order
Total Upgrade Time: 01:17:50 [CON_ID: 2 => PDB$SEED * ]
Total Upgrade Time: 01:06:31 [CON_ID: 1 => CDB$ROOT]
Total Upgrade Time: 01:06:12 [CON_ID: 3 => PDBDEV]
Grand Total Upgrade Time: [0d:2h:42m:43s]
Timezone file upgrade and database recompilation has already completed by the autoupgrade utility as the below values are adjusted as “yes” in the config file,
upg1.run_utlrp=yes =yes # yes(default) to run utlrp as part of upgrade
upg1.timezone_upg=yes # yes(default) to upgrade timezone if needed
Check the Timezone version
SQL> SELECT VERSION FROM V$TIMEZONE_FILE;
VERSION
----------
32
SQL>
Check the db details
SQL> select name, open_mode, version, status from v$database, v$instance;
NAME OPEN_MODE VERSION STATUS
--------- -------------------- ----------------- ------------
ABDULTEST READ WRITE 19.0.0.0.0 OPEN
SQL>
6. Post-upgrade task
Once the upgrade is successful and all testing is done, drop the restore point.
Drop the Guaranteed restore point
SQL> select name from v$restore_point;
NAME
------------------------------
AUTOUPGRADE_9212_ABDULTEST122010
SQL>
SQL> drop restore point AUTOUPGRADE_9212_ABDULTEST122010;
Restore point dropped.
SQL>
Change the compatible parameter
show parameter compatible
alter system set compatible='19.0.0' scope=spfile;
shutdown immediate;
startup;
show parameter compatible
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0
noncdb_compatible boolean FALSE
SQL> alter system set compatible='19.0.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 1560278096 bytes
Fixed Size 9135184 bytes
Variable Size 973078528 bytes
Database Buffers 570425344 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0
noncdb_compatible boolean FALSE
SQL>
It’s Done. Database is successfully upgraded from 12c to 19c.
Superb blog Sir 👌
ReplyDelete