Sunday, June 6, 2021

Oracle 19c Autopgrade utility


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
-analyze -fixups -upgrade -deploy

 
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.


2. Prerequisite for Autoupgrade   : Download the latest autoupgrade.jar file

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 ~]$

[oracle@new19c ~]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -version  

build.hash e84c9c2
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
Java version should be 8 or later, which is available by default in Oracle Database homes from release 
12.1.0.2 and latest.

[oracle@new19c temp]$ $ORACLE_HOME/jdk/bin/java -version
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)



3. Create the config file

Create a directory to hold all upgrade config and log files.

[oracle@new19c ~]$ mkdir /u01/19c-autoupg
[oracle@new19c ~]$ cd /u01/19c-autoupg

Create the sample config file
cd /u01/19c-autoupg
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 -create_sample_file config

--output
Created sample configuration file /u01/19c-autoupg/sample_config.cfg


Modify the config file

Copy the sample config file and make the necessary changes as per the database environment.
cd /u01/19c-autoupg
cp sample_config.cfg abdultest_db_config.cfg
vi abdultest_db_config.cfg

This is the config file I used for upgrade:
[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




4. Analyze the database
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.

Execute autoupgrade in analyze mode with the below syntax,
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/jdk/bin

cd /u01/19c-autoupg
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config abdultest_db_config.cfg -mode ANALYZE


Output
[oracle@new19c 19c-autoupg]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config cdbdev_db_config.cfg -mode ANALYZE

AutoUpgrade tool launched with default options
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
logs - to check log  folder 


All Analyze logs are created under autoupg_log_dir

[oracle@new19c 100]$ cd /u01/19c-autoupg/upg_logs/abdultest/abdultest/100/prechecks/
[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  36704 Nov 19 03:28 abdultest_preupgrade.html
-rwx------. 1 oracle oinstall  17649 Nov 19 03:28  abdultest_vpreupgrade.log
-rwx------. 1 oracle oinstall 158030 Nov 19 03:28 prechecks_cdb_root.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



We can review the html file (abdultest_preupgrade.html) which will list all precheck Errors, warnings and recommendations.


5. Deploy the upgrade

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.

Execute the autoupgrade in DEPLOY mode using the below syntax,

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/jdk/bin

cd /u01/19c-autoupg$ORACLE_HOME/jdk/bin/java -jar 
$ORACLE_HOME/rdbms/admin/autoupgrade.jar -config abdultest_db_config.cfg -mode DEPLOY

Once the upgrade process is started consider monitoring the logs to see the progress of the upgrade. Autoupgrade logs are available under,

/u01/19c-autoupg/upg_logs/abdultest/abdultest/101/dbupgrade



Output

[oracle@new19c 19c-autoupg]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config abdultest_db_config.cfg -mode DEPLOY

AutoUpgrade tool launched with default options

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 at your convenience once you consider it is no longer needed ----
Drop GRP from cdbdev: drop restore point AUTOUPGRADE_9212_CDBDEV122010

[oracle@new19c 19c-autoupg]$

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]



[oracle@new19c dbupgrade]$
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

Note: After the upgrade, database has to be tested properly before updating the compatible parameter. Once the parameter is updated database cannot be downgraded.
show parameter compatible
alter system set compatible='19.0.0' scope=spfile;
shutdown immediate;
startup;
show parameter compatible


--output
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.



Reference :
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/using-autoupgrade-oracle-database-upgrades.html#GUID-71883C8C-7A34-4E93-8955-040CB04F2109 

1 comment: