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.
On this page
Share this
Share this
More resources
Learn more about Pythian by reading the following blogs and articles.
Mining Autoupgrade Results
![]()
Mining Autoupgrade Results
Dec 3, 2019 12:00:00 AM
7
min read
Database Upgrade Fails With “unexpected error in validate_credentials”
Database Upgrade Fails With “unexpected error in validate_credentials”
Nov 12, 2021 12:00:00 AM
10
min read
How to Create an Oracle SE2 Database and Avoid Licensing Problems
How to Create an Oracle SE2 Database and Avoid Licensing Problems
Jun 16, 2022 12:00:00 AM
9
min read
Ready to unlock value from your data?
With Pythian, you can accomplish your data transformation goals and more.
.svg){kind=small}
.svg){kind=small}