AutoUpgrade: Plug Noarchive Non-CDB Database as Upgraded PDB

The Database AutoUpgrade utility has been slowly developing its capability to cover all possibilities of database upgrades. Recently I’ve tested an upgrade of a database running in NOARCHIVELOG mode using this utility. I added more complications with this testing phase; these are my environment details:

• The source database namely “testnew” was running in rdbms 12.1.0.2 version, NOARCHIVELOG mode. This was a non-CDB database.
• The target database namely “test19c” was running in rdbms 19.3.0 version, NOARCHIVELOG mode. This was a CDB database.

First I verified the MOS note 2485457.1 and noticed the most recent version of the AutoUpgrade utility was version 20201214. Hence, I checked which version was available in my rdbms 19.3 oracle home and found the build version was 20190207.

[oracle@vm130-196 admin]$ java -jar autoupgrade.jar -version
build.version 20190207
build.date 2019/02/07 12:35:56
build.label RDBMS_PT.AUTOUPGRADE_LINUX.X64_190205.1800

I downloaded the latest version and copied across this Oracle home.

[oracle@vm130-196 admin]$ ls -ltr auto*
-rw-r--r--. 1 oracle oinstall 3360892 Feb  8  2019 autoupgrade.jar_old
-rw-r--r--. 1 oracle oinstall 2868558 Feb 21 23:53 autoupgrade.jar
[oracle@vm130-196 admin]$ java -jar autoupgrade.jar -version
build.hash 8ee6880
build.version 21.1.1
build.date 2020/12/14 14:41:34
build.max_target_version 21
build.supported_target_versions 12.2,18,19,21
build.type production

I created a very basic configuration file(config.txt) for this upgrade activity and executed it in Analyze Mode; it failed with the “ARCHIVE_MODE_ON” error.

[oracle@vm130-196 ~]$ pwd
/home/oracle
[oracle@vm130-196 ~]$ cat config.txt
upg1.source_home=/u01/app/oracle/product/12.1.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.sid=testnew
[oracle@vm130-196 admin]$ java -jar autoupgrade.jar -config /home/oracle/config.txt -mode analyze
No parameter 'global.autoupg_log_dir' found in config file, using /u01/app/oracle/cfgtoollogs/autoupgrade
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED WITH ERROR -------------
Job 100 for testnew

Please check the summary report at:
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
[oracle@vm130-196 admin]$ cat /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
==========================================
          Autoupgrade Summary Report
==========================================
[Date]           Mon Feb 22 00:04:48 EST 2021
[Number of Jobs] 1
==========================================
[Job ID] 100
==========================================
[DB Name]                testnew
[Version Before Upgrade] 12.1.0.2.0
[Version After Upgrade]  19.3.0.0.0
------------------------------------------
[Stage Name]    PRECHECKS
[Status]        FAILURE
[Start Time]    2021-02-22 00:04:31
[Duration]      0:00:16
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/100/prechecks
[Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/100/prechecks/testnew_preupgrade.log
                Precheck failed for testnew, manual intervention needed for the below checks
                [ARCHIVE_MODE_ON]
------------------------------------------

The configuration file was added with the parameter “restoration=no,” which was related to the ARCHIVELOG mode of the database. The utility with Analyze Mode didn’t throw any errors this time.

[oracle@vm130-196 admin]$ cat /home/oracle/config.txt
upg1.source_home=/u01/app/oracle/product/12.1.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.sid=testnew
upg1.restoration=no
[oracle@vm130-196 admin]$ java -jar autoupgrade.jar -config /home/oracle/config.txt -mode analyze
No parameter 'global.autoupg_log_dir' found in config file, using /u01/app/oracle/cfgtoollogs/autoupgrade
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg> Job 101 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]
Jobs pending                   [0]

Please check the summary report at:
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log

This means I could upgrade this as a non-CDB database. However, since my objective was to plug this non-CDB database as an upgraded PDB, I added an associated parameter “target_cdb” with the configuration file. The Analyze Mode failed with a “TARGET_CDB_COMPATIBILITY” error.

[oracle@vm130-196 admin]$ cat /home/oracle/config.txt
upg1.source_home=/u01/app/oracle/product/12.1.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.sid=testnew
upg1.restoration=no
upg1.target_cdb=test19c
[oracle@vm130-196 admin]$ java -jar autoupgrade.jar -config /home/oracle/config.txt -mode analyze
No parameter 'global.autoupg_log_dir' found in config file, using /u01/app/oracle/cfgtoollogs/autoupgrade
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg> Job 102 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED WITH ERROR -------------
Job 102 for testnew

Please check the summary report at:
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
[oracle@vm130-196 admin]$ cat /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
==========================================
          Autoupgrade Summary Report
==========================================
[Date]           Mon Feb 22 00:18:43 EST 2021
[Number of Jobs] 1
==========================================
[Job ID] 102
==========================================
[DB Name]                testnew
[Version Before Upgrade] 12.1.0.2.0
[Version After Upgrade]  19.3.0.0.0
------------------------------------------
[Stage Name]    PRECHECKS
[Status]        FAILURE
[Start Time]    2021-02-22 00:18:17
[Duration]      0:00:26
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/102/prechecks
[Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/102/prechecks/testnew_preupgrade.log
                Precheck failed for testnew, manual intervention needed for the below checks
                [TARGET_CDB_COMPATIBILITY]
------------------------------------------

I reviewed the associated logfiles and noticed the error was due to APEX mismatch. This meant the source database was running with Oracle Application Express database component, version 4.2.5. But the target database was running without this component.

2021-02-22 00:18:42.460 INFO Total number of rows found in PDB_PLUG_IN_VIOLATIONS was [3] - AbstractNonCDBToPDB.getPDBPlugInViolations
2021-02-22 00:18:42.463 INFO Plug in violation [TESTNEW 1 ERROR PENDING PDB's version does not match CDB's version: PDB's version 12.1.0.2.0. CDB's version 19.0.0.0.0.] explicitly ignored - CheckPluginCompatibility.getNonIgnorableViolations
2021-02-22 00:18:42.463 INFO Plug in violation [TESTNEW 1 ERROR PENDING PDB's version is lower than CDB and PDB has more components than the CDB, plug in is not allowed.] explicitly ignored - CheckPluginCompatibility.getNonIgnorableViolations
2021-02-22 00:18:42.464 INFO Plug in violation [TESTNEW 1 ERROR PENDING APEX mismatch: PDB has installed common APEX. CDB has not installed APEX.] not ignored - CheckPluginCompatibility.getNonIgnorableViolations
2021-02-22 00:18:42.464 INFO Total plug in violations after safety analysis: 1 - CheckPluginCompatibility.getNonIgnorableViolations
2021-02-22 00:18:42.473 INFO Finished check [TARGET_CDB_COMPATIBILITY][testnew][NOT PASSED] - CheckTrigger.call

I reviewed various scenarios and decided to remove the APEX component as it wasn’t getting used (Ref MOS note 558340.1). I executed the required scripts in the source database and compiled the invalid objects. There was only one invalid object as follows:

SQL> select owner,object_name,object_type from DBA_OBJECTS WHERE STATUS='INVALID';

OWNER
--------------------------------------------------------------------------------------------------------------------------------
OBJECT_NAME                                                                                                                      OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -----------------------
SYS
WWV_DBMS_SQL                                                                                                                     PACKAGE BODY
SQL> alter package WWV_DBMS_SQL compile body;

Warning: Package Body altered with compilation errors.

SQL> show error
Errors for PACKAGE BODY WWV_DBMS_SQL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
150/5    PL/SQL: Statement ignored
150/8    PLS-00201: identifier 'APEX_040200.WWV_FLOW_SECURITY' must be
         declared

158/5    PL/SQL: Statement ignored
158/8    PLS-00201: identifier 'APEX_040200.WWV_FLOW_SECURITY' must be
         declared

162/4    PL/SQL: Statement ignored
162/7    PLS-00201: identifier 'APEX_040200.WWV_FLOW_SECURITY' must be
         declared

166/4    PL/SQL: Statement ignored
166/7    PLS-00201: identifier 'APEX_040200.WWV_FLOW_SECURITY' must be
         declared

181/4    PL/SQL: Statement ignored
181/78   PLS-00201: identifier 'APEX_040200.WWV_FLOW_SECURITY' must be
         declared

341/5    PL/SQL: Statement ignored
347/28   PLS-00201: identifier 'APEX_040200.WWV_FLOW' must be declared
385/5    PL/SQL: Statement ignored
394/28   PLS-00201: identifier 'APEX_040200.WWV_FLOW' must be declared
443/6    PL/SQL: Statement ignored
449/21   PLS-00201: identifier 'APEX_040200.WWV_FLOW' must be declared
468/15   PL/SQL: Statement ignored
468/15   PLS-00201: identifier 'APEX_040200.WWV_FLOW' must be declared
525/6    PL/SQL: Statement ignored
533/21   PLS-00201: identifier 'APEX_040200.WWV_FLOW' must be declared

You may need to analyze various scenarios if you’re actually using Oracle Application Express; learn more in the associated documents.

Reference:
https://docs.oracle.com/en/database/oracle/application-express/19.1/htmig/multitenant-architecture-in-Oracle-db-12c.html#GUID-972D1EC3-84AD-4C36-A4B1-167C3610BE7F

The execution of AutoUpgrade in Analyze Mode was done without any issues. Once the utility was executed with deploy option, the upgrade started its execution.

[oracle@vm130-196 admin]$ java -jar autoupgrade.jar -config /home/oracle/config.txt -mode deploy
No parameter 'global.autoupg_log_dir' found in config file, using /u01/app/oracle/cfgtoollogs/autoupgrade
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands
upg> lsr
No jobs in the restoration queue
upg> lsa
No jobs in the abort queue
upg> tasks
+---+----------------------+-------------+
| ID|                  NAME|         Job#|
+---+----------------------+-------------+
|  1|                  main|      WAITING|
| 52|            event_loop|TIMED_WAITING|
| 53|               console|     RUNNABLE|
| 54|          queue_reader|      WAITING|
| 55|                 cmd-0|      WAITING|
| 56|         job_manager-0|      WAITING|
| 57|            bqueue-104|      WAITING|
|249|             exec_loop|      WAITING|
|423|       monitor_testnew|TIMED_WAITING|
|424|        catctl_testnew|      WAITING|
|425| abort_monitor_testnew|TIMED_WAITING|
|426|            async_read|     RUNNABLE|
+---+----------------------+-------------+
upg> status
---------------- Config -------------------
User configuration file    [/home/oracle/config.txt]
General logs location      [/u01/app/oracle/cfgtoollogs/autoupgrade/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         [1]
Total CDB being processed             [0]
Jobs finished successfully            [0]
Jobs finished/aborted                 [0]
Jobs in progress                      [1]
Jobs stage summary
    Job ID: 104
    DB name: testnew
        SETUP             <1 min
        PREUPGRADE        <1 min
        PRECHECKS         <1 min
        PREFIXUPS         3 min
        DRAIN             <1 min
        DBUPGRADE         2 min (IN PROGRESS)
------------ Resources ----------------
Threads in use                        [37]
JVM used memory                       [101] MB
CPU in use                            [13%]
Processes in use                      [16]
upg> status -job 104
Progress
-----------------------------------
Start time:      21/02/23 22:44
Elapsed (min):   36
End time:        N/A
Last update:     2021-02-23T23:20:02.704
Stage:           DBUPGRADE
Operation:       EXECUTING
Status:          RUNNING
Pending stages:  6
Stage summary:
    SETUP             <1 min
    PREUPGRADE        <1 min
    PRECHECKS         <1 min
    PREFIXUPS         3 min
    DRAIN             <1 min
    DBUPGRADE         31 min (IN PROGRESS)

Job Logs Locations
-----------------------------------
Logs Base:    /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew
Job logs:     /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/104
Stage logs:   /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/104/dbupgrade
TimeZone:     /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/temp

Additional information
-----------------------------------
Details:
[Upgrading] is [59%] completed for [testnew]
                 +---------+-------------+
                 |CONTAINER|   PERCENTAGE|
                 +---------+-------------+
                 |  testnew|UPGRADE [59%]|
                 +---------+-------------+

Error Details:
None

The AutoUpgrade execution took around 80 minutes to complete its execution. It didn’t report any errors.

upg> status
---------------- Config -------------------
User configuration file    [/home/oracle/config.txt]
General logs location      [/u01/app/oracle/cfgtoollogs/autoupgrade/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: 104
    DB name: testnew
        SETUP             <1 min
        PREUPGRADE        <1 min
        PRECHECKS         <1 min
        PREFIXUPS         3 min
        DRAIN             <1 min
        DBUPGRADE         48 min
        POSTCHECKS        <1 min
        POSTFIXUPS        9 min
        POSTUPGRADE       <1 min
        NONCDBTOPDB       4 min (IN PROGRESS)
------------ Resources ----------------
Threads in use                        [37]
JVM used memory                       [104] MB
CPU in use                            [13%]
Processes in use                      [14]

upg> Job 104 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]
Jobs pending                   [0]

Please check the summary report at:
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log

[oracle@vm130-196 ~]$ cat /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
==========================================
          Autoupgrade Summary Report
==========================================
[Date]           Wed Feb 24 00:04:03 EST 2021
[Number of Jobs] 1
==========================================
[Job ID] 104
==========================================
[DB Name]                testnew
[Version Before Upgrade] 12.1.0.2.0
[Version After Upgrade]  19.3.0.0.0
------------------------------------------
[Stage Name]    PREUPGRADE
[Status]        SUCCESS
[Start Time]    2021-02-23 22:44:57
[Duration]      0:00:00
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/104/preupgrade
------------------------------------------
[Stage Name]    PRECHECKS
[Status]        SUCCESS
[Start Time]    2021-02-23 22:44:57
[Duration]      0:00:27
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/104/prechecks
[Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/104/prechecks/testnew_preupgrade.log
                Precheck passed and no manual intervention needed
------------------------------------------
[Stage Name]    PREFIXUPS
[Status]        SUCCESS
[Start Time]    2021-02-23 22:45:24
[Duration]      0:03:22
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/104/prefixups
------------------------------------------
[Stage Name]    DRAIN
[Status]        SUCCESS
[Start Time]    2021-02-23 22:48:47
[Duration]      0:00:22
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/104/drain
------------------------------------------
[Stage Name]    DBUPGRADE
[Status]        SUCCESS
[Start Time]    2021-02-23 22:49:09
[Duration]      0:48:25
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/104/dbupgrade
------------------------------------------
[Stage Name]    POSTCHECKS
[Status]        SUCCESS
[Start Time]    2021-02-23 23:37:34
[Duration]      0:00:49
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/104/postchecks
[Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/104/postchecks/testnew_postupgrade.log
------------------------------------------
[Stage Name]    POSTFIXUPS
[Status]        SUCCESS
[Start Time]    2021-02-23 23:38:24
[Duration]      0:09:41
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/104/postfixups
------------------------------------------
[Stage Name]    POSTUPGRADE
[Status]        SUCCESS
[Start Time]    2021-02-23 23:48:05
[Duration]      0:00:00
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/104/postupgrade
------------------------------------------
[Stage Name]    NONCDBTOPDB
[Status]        SUCCESS
[Start Time]    2021-02-23 23:48:05
[Duration]      0:15:57
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/testnew/testnew/104/noncdbtopdb
------------------------------------------

As a part of verification, I checked the invalid objects and status of database components. I didn’t see any issues.

SQL> select con_id,name,OPEN_MODE from v$pdbs order by 1;

    CON_ID NAME                 OPEN_MODE
---------- -------------------- ----------
         2 PDB$SEED             READ ONLY
         3 TEST19CPDB           READ WRITE
         4 TESTNEW              READ WRITE

SQL> alter session set container=TESTNEW;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
TESTNEW

SQL> SELECT comp_id, status, SUBSTR(version,1,10) version, comp_name FROM dba_registry ORDER BY 1;

COMP_ID STATUS     VERSION    COMP_NAME
------- ---------- ---------- -----------------------------------
APS     VALID      19.0.0.0.0 OLAP Analytic Workspace
CATALOG VALID      19.0.0.0.0 Oracle Database Catalog Views
CATJAVA VALID      19.0.0.0.0 Oracle Database Java Packages
CATPROC VALID      19.0.0.0.0 Oracle Database Packages and Types
CONTEXT VALID      19.0.0.0.0 Oracle Text
DV      VALID      19.0.0.0.0 Oracle Database Vault
JAVAVM  VALID      19.0.0.0.0 JServer JAVA Virtual Machine
OLS     VALID      19.0.0.0.0 Oracle Label Security
ORDIM   VALID      19.0.0.0.0 Oracle Multimedia
OWM     VALID      19.0.0.0.0 Oracle Workspace Manager
RAC     OPTION OFF 19.0.0.0.0 Oracle Real Application Clusters
SDO     VALID      19.0.0.0.0 Spatial
XDB     VALID      19.0.0.0.0 Oracle XML Database
XML     VALID      19.0.0.0.0 Oracle XDK
XOQ     VALID      19.0.0.0.0 Oracle OLAP API

15 rows selected.

SQL> select owner,object_name,object_type from DBA_OBJECTS WHERE STATUS='INVALID';

OWNER      OBJECT_NAME                              OBJECT_TYPE
---------- ---------------------------------------- -----------------------
SYS        WWV_DBMS_SQL                             PACKAGE BODY

So the usage of parameters “restoration=no” and “target_cdb” helped to complete the requirement.

This looks easy, doesn’t it?

If you have any questions, or thoughts about the above, please leave them in the comments.