Unplug-Plug-Upgrade from 19c to 21c Using Autoupgrade

This post shows the steps for upgrading a 19c PDB with autoupgrade tool using the unplug-plug-uprade method. The 21c CDB is called cdb21c and 19c CDB is called testcdb. The PDB that will be unpluged from 19c and plugged into 21c and upgraded is called testpdb2. Both 19c and 21c CDBs reside in the same Oracle restart configuration and the PDB has service dbxrw associated with it.

Resource Name             Type                      Target             State              Host
-------------             ------                    -------            --------           ----------
ora.cdb21c.db             ora.database.type         ONLINE             ONLINE             ip-172-31-10-91
ora.testcdb.db            ora.database.type         ONLINE             ONLINE             ip-172-31-10-91
ora.testcdb.dbxrw.svc     ora.service.type          ONLINE             ONLINE             ip-172-31-10-91

If any privileges were reovked from public due to security reason (CIS standard recommendations) then grant those privileges back before starting the ugprade process. Once the upgrade is completed then those privilges could be revoked again.
Generate a sample upgrade config file with

java -jar autoupgrade.jar -create_sample_file config sample_config.cfg

and customize the "Unplug/Plug upgrade" section.

global.autoupg_log_dir=/home/oracle/upgradelogs
global.keystore=/home/oracle/upgtde

#
# Database number 1 - Unplug/Plug upgrade
#
upg1.log_dir=/home/oracle/autoupgrade/cdb21c
upg1.sid=testcdb
upg1.source_home=/opt/app/oracle/product/19.x.0/dbhome_2
upg1.target_cdb=cdb21c
upg1.target_home=/opt/app/oracle/product/21.x.0/dbhome_1
upg1.pdbs=testpdb2                    # Comma delimited list of pdb names that will be upgraded and moved to the target CDB
upg1.run_utlrp=yes                   # Optional. Whether or not to run utlrp after upgrade
upg1.timezone_upg=yes                # Optional. Whether or not to run the timezone upgrade
upg1.target_version=21       # Oracle version of the target ORACLE_HOME.  Only required when the target Oracle database version is 12.2


# copies the pdb to inside target cdb directory structure. default is nocopy where datafiles remains in 19c cdb directory strcuture in ASM. 
upg1.target_pdb_copy_option=file_name_convert=NONE

global.keystore is used to give the location for the autoupgrade key store. Since both target and source CDBs use TDE, it helps to create a autoupgrade keystore and save the respective CDBs key store passowrd in it.
Secondly the "upg1.target_pdb_copy_option=file_name_convert=NONE" copies the data files inside the target CDB datafile directory structure. If not the PDB datafile will remain where they are (under the source CDB's directory structure).
To create autoupgrade keystore run the autoupgrade with load_password option.

java -jar autoupgrade.jar -config unplug_plug.cfg -load_password
Processing config file ...

Starting AutoUpgrade Password Loader - Type help for available options
Creating new AutoUpgrade keystore - Password required
Enter password:
Enter password again:
AutoUpgrade keystore was successfully created

TDE> list
+----------+----------------+------------------+-----------+------------------+
|ORACLE_SID| Action Required|      TDE Password|SEPS Status|Active Wallet Type|
+----------+----------------+------------------+-----------+------------------+
|    cdb21c|Add TDE password|No password loaded|   Inactive|               Any|
|   testcdb|Add TDE password|No password loaded|   Inactive|               Any|
+----------+----------------+------------------+-----------+------------------+

It detects the databases that are up and running in the host and could be listed with list command. Next add the each database's keystore password to autoupgrade keystore.

TDE> add testcdb
Enter your secret/Password:
Re-enter your secret/Password:
TDE> add cdb21c
Enter your secret/Password:
Re-enter your secret/Password:
TDE> list
+----------+---------------+------------+-----------+------------------+
|ORACLE_SID|Action Required|TDE Password|SEPS Status|Active Wallet Type|
+----------+---------------+------------+-----------+------------------+
|    cdb21c|               |    Verified|   Inactive|               Any|
|   testcdb|               |    Verified|   Inactive|               Any|
+----------+---------------+------------+-----------+------------------+
TDE> save
Convert the AutoUpgrade keystore to auto-login [YES|NO] ? YES

It helps to have a auto-login keystore so the upgrade could run smoothly without waiting for password to be entered.
Next run the preuprage checks. This is done using the analyze option.

java -jar autoupgrade.jar -config unplug_plug.cfg -mode analyze
AutoUpgrade 22.4.220712 launched with default internal options
Processing config file ...
Loading AutoUpgrade keystore
AutoUpgrade keystore was successfully loaded
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 PDB(s) will be analyzed
Type 'help' to list console commands
upg> lsj -a 10
upg> +----+-------+---------+---------+-------+----------+-------+----------------------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|                     MESSAGE|
+----+-------+---------+---------+-------+----------+-------+----------------------------+
| 100|testcdb|PRECHECKS|EXECUTING|RUNNING|  13:58:20| 7s ago|Loading database information|
+----+-------+---------+---------+-------+----------+-------+----------------------------+
Total jobs 1

The command lsj is running every 10 seconds. PRESS ENTER TO EXIT
Job 100 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]

Please check the summary report at:
/home/oracle/upgradelogs/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/upgradelogs/cfgtoollogs/upgrade/auto/status/status.log

Check the status html for any of the precheck failed. In this case there was no failures.

If any fixup are needed then run the autoupgrade with fixup option.

java -jar autoupgrade.jar -config unplug_plug.cfg -mode fixups

This will generate a fixup report.

Finally run the autoupgrade with deploy option to begin the upgrade.

java -jar autoupgrade.jar -config unplug_plug.cfg -mode deploy

When this is run on the source CDB alert log the PDB unplug could noticed.

2023-03-09T14:13:16.820993+00:00
Completed: alter pluggable database TESTPDB2 unplug into '/home/oracle/upgtde/testcdb-TESTPDB2.xml' encrypt using *
drop pluggable database TESTPDB2 keep datafiles
2023-03-09T14:13:17.607648+00:00
Deleted Oracle managed file +DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/TEMPFILE/temp.329.1131016147
2023-03-09T14:13:17.635197+00:00
Stopped service testpdb2
Completed: drop pluggable database TESTPDB2 keep datafiles

On the target CDB's alert log the PDB plug-ing couldbe noticed.

2023-03-09T14:13:17.933191+00:00
create pluggable database "TESTPDB2"   using '/home/oracle/upgtde/testcdb-TESTPDB2.xml' COPY file_name_convert=NONE tempfile reuse keystore identified by * decrypt using *

Monitor the upgrade process and once completed check the status.html that all tasks ran without any issue.

The service associated with the PDB is not migrated as part of the upgrade. This will remain in the offline status.

Resource Name             Type                      Target             State              Host
-------------             ------                    -------            --------           ----------
ora.cdb21c.testpdb2.pdb   ora.pdb.type              ONLINE             ONLINE             ip-172-31-10-91
ora.testcdb.db            ora.database.type         ONLINE             ONLINE             ip-172-31-10-91
ora.testcdb.dbxrw.svc     ora.service.type          OFFLINE            OFFLINE

Oracle confirmed via SR that migration of service is not available with autoupgrade. So this has to be done manually. Remove the old service using the 19c Oracle Home's srvctl

srvctl remove service -db $ORACLE_SID -service dbxrw

Add the service again using 21c Oracle Home's srvctl.

srvctl add service -db $ORACLE_SID -pdb testpdb2 -service dbxrw -role PRIMARY -notification TRUE -failovertype NONE -failovermethod NONE -failoverdelay 0 -failoverretry 0 -drain_timeout 5 -stopoption IMMEDIATE

With this both PDB and service are now part of the target CDB.

Resource Name             Type                      Target             State              Host
-------------             ------                    -------            --------           ----------
ora.cdb21c.db             ora.database.type         ONLINE             ONLINE             ip-172-31-10-91
ora.cdb21c.dbxrw.svc      ora.service.type          ONLINE             ONLINE             ip-172-31-10-91
ora.cdb21c.testpdb2.pdb   ora.pdb.type              ONLINE             ONLINE             ip-172-31-10-91

Check the PDB data files reside inside the target CDB directory path.

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TESTPDB2                       READ WRITE NO

SQL>  select name from v$datafile where con_id=3;

NAME
----------------------------------------------------------------------------------------------------
+DATA/CDB21C/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/system.332.1131027199
+DATA/CDB21C/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/sysaux.326.1131027199
+DATA/CDB21C/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/undotbs1.280.1131027199

This conclude the upgrade of PDB using unplug-plug-upgrade method.

Active Data Guard instances are not supported with DBMS_ROLLING

There is an earlier post on how to use dbms_rolling for database upgrade. The configuration used in that post consists of two instances. The data guard configuration this post is based on had three instances. One of them was is an active data guard instnace, open read only. This instance is to simulate a reader farm (Example 14-9 Rolling Upgrade on a Reader Farm). Below is the database names, their role and current open mode.

DB_UNIQUE_NAME   OPEN_MODE              DATABASE_ROLE
---------------- ----------             -------------
dgtest           READ WRITE             PRIMARY
dgtest2          MOUNTED                PHYSICAL STANDBY
dgtest3          READ ONLY WITH APPLY   PHYSICAL STANDBY

dgtest2 is designated as the future primary. This standby DB, as shown above is already in mount mode. MOS doc 2832235.1 states standby must be in mount mode.

SQL> exec DBMS_ROLLING.INIT_PLAN(future_primary=>'dgtest2');

PL/SQL procedure successfully completed.

However, when the build plan is called it gives the below error.

SQL> exec DBMS_ROLLING.BUILD_PLAN;
BEGIN DBMS_ROLLING.BUILD_PLAN; END;

*
ERROR at line 1:
ORA-45438: database is not in mounted mode
ORA-06512: at "SYS.DBMS_ROLLING", line 16
ORA-06512: at line 1

This is strange as the future primary, the standby that will be invovled in the switchover is already in mount mode but build plan is giving an error.
To see if the error is due to the active data guard instance (dgtest3), that instance was closed and started in mount mode. The build plan executes without error. So it seems all instances must be in mount mode for build plan to work. This means any application that rely on reader farm will incur down time.



After raising a SR Oracle confirmed that this is a bug and gave the following "Bug 25882840 : ACTIVE DATA GUARD NOT PERMITTED AS DBMS_ROLLING MEMBER". Unfortunately this is an internal bug so couldn't get much details on it. There was also no ETA on thie fix for the bug nor confirmation that ADG will be supported in the future.

Related MOS Docs
Oracle Exadata Database Service 19c Rolling Upgrade with DBMS_ROLLING from Oracle Database 12.1.0.2 [ID 2889464.1]
Exadata Cloud Database 19c Rolling Upgrade With DBMS_ROLLING [ID 2832235.1]

Related Posts
Upgrading Oracle Restart Databases From 12.2 to 18c Using DBMS_ROLLING
DG Broker Continue With Status "ROLLING DATABASE MAINTENANCE IN PROGRESS" Even After ROLLBACK_PLAN is Called

Far Sync Targets are not Discovered Correctly by OEM Auto Discovery Process

If there are far sync instances in a data guard configuration then those will not be correctly identified as far sync instances by the OEM target discovery process. There were two posts related to the same issue where far sync types were not listed for template apply. The other issue was related to data guard performance page where far sync instace graphs had error status.
Root cause for all of this is that auto discovery will assign target type of "database instance" for the far sync instance.

After an SR that spaned year and half Oracle support said that currently OEM auto discovery process is unable to correctly detect the far sync instance types. Their solution was to manaully add the far sync instance.



To manually add the far sync go to add target -> add target manually and select far sync type and the host where far sync resides.

Afterwards the far sync instaces has be views via all targets and filtering by far syncs. They will not appear under database targets which is the incorrect behaviour.

The data guard performance graphs for far sync will appear without any error status.

As far sync has been around over 8 years now and for OEM to not correctly identify them was very puzzling to say the least. The issue was first discovered on 13.4 and exists on the 13.5 (latest at the time of this post). After further deliberations Oracle has created an enhancement request (ER-34673700) to address this issue. Whether this ER will be accepted and worked upon is still unknown.