AutoUpgrade New Features: List All Checks

Oracle AutoUpgrade was made to make upgrading and patching easier. When doing so, there is a risk that we hide too much information and turn AutoUpgrade into a black box.

It has always been the intention that AutoUpgrade is fully transparent and enables you to see exactly what’s going on.

This new feature increases transparency by allowing you to get a list of all the checks that are performed before and after upgrading or patching.

How Does It Work?

Ensure that you have the latest version of AutoUpgrade:

wget https://download.oracle.com/otn-pub/otn_software/autoupgrade.jar

Start AutoUpgrade and list all checks. Pipe the output into a file for easier reading:
```
java -jar autoupgrade.jar -listchecks &gt; autoupgrade_checks.txt
```

You can shorten the output to just one of the checks:

java -jar autoupgrade.jar -listchecks ORACLE_RESERVED_USERS

Here’s the output from one of the checks:

Check : ORACLE_RESERVED_USERS
        Description : Oracle occasionally adds new internal USERs and ROLEs as the database evolves. To avoid a name conflict in the upgraded version, a source database must not contain any USER or ROLE with a name that matches one reserved by Oracle in the target release.
        Fixup Action : You must drop the following USERs or ROLEs from the database: {1}
        Severity : ERROR
        Fixup Stage : PRE
        Min Version(inclusive) Check applies : NONE
        Max Version(exclusive) Check applies : NONE
        Check Introduced Version : NONE
        Check Removed Version : NONE
        Manual Fixup or Automatic : MANUAL
        AutoUpgrade Only : NO
        Run for Datapatch : NO

Severity may take one of the following values: INFO, WARNING, RECOMMEND, ERROR.
Fixup Stage tells you when AutoUpgrade executes the check: PRE (before), POST (after)
If Manual Fixup or Automatic is AUTO it means AutoUpgrade will fix any issues for you during fixups or deploy mode. Otherwise, it is something that the user must fix. Depending on the severity a fix is mandatory.
If AutoUpgrade also executes the check during patching, then Run for Datapatch is set to YES.

How Can I Use the Information?

First, this feature adds transparency. We don’t want AutoUpgrade to become a black box.

Second, it allows you to correlate the information with your own runbook. Perhaps you are performing some of the same checks and that’s an opportunity to trim your runbook. I have seen this countless times when I talk to customers. Their runbook has evolved over many years and often contain checks that are no longer needed or executed by AutoUpgrade.

Final Words

At the time of writing, there are more than 200 checks in AutoUpgrade:

java -jar autoupgrade.jar -listchecks | grep "Check : " | wc -l

201

Happy upgrading!

Recreate Database Services After Moving An Oracle Database

Oracle recommends that you connect to the database via custom services. In your connect string, don’t connect:

Directly to the SID
Or to the database’s default service (the service with the same name as the database).

When you move a database around, in some situations, the database does not retain these services, for instance, when you:

Migrate a non-CDB to PDB using refreshable clone PDB
Upgrade a PDB using refreshable clone PDB
Move a PDB to a different CDB using refreshable clone PDB
Migrating a database using Full Transportable Export/Import or transportable tablespaces

The services are important because your application and clients connect to the database through that service. Also, the service might define important properties for things like Application Continuity or set default drain timeout.

Here’s how to recreate such services.

Database Managed Services

A database-managed service is one that you create directly in the database using dbms_service:

begin
   dbms_service.create_service(
      service_name=>'SALESGOLD',
      network_name=>'SALESGOLD');
   dbms_service.start_service('SALESGOLD');   
end;
/

After the migration, you must manually recreate the service in the target database.

dbms_metadata does not support services. So, you must query v$services in the source database to find the service’s defition. Then, construct a call to dbms_service.create_service and dbms_serice.start_service.

Clusterware Managed Services

I recommend defining services in Grid Infrastructure if you are using Oracle RAC or using Oracle Restart to manage your single instance database. Luckily, Grid Infrastructure supports exporting and importing service defitions.

You export all the services defined in the source database:

srvctl config service \
   -db $ORACLE_UNQNAME \
   -exportfile my_services.json \
   -S 2

You edit the JSON file.
1. Remove the default services. Keep only your custom services.
2. Remove the dbunique_name attribute for all services.
3. If you are renaming the PDB, you must update the pluggable_database attribute.
4. Update the res_name attribute so it matches the resource name of the target database. Probably you just need to exchange the db_unique_name part of the resource name. You can find the resource name as grid when you execute crsctl stat resource -t.

You can now import the services into the target database:

srvctl add service \
   -db $ORACLE_UNQNAME \
   -importfile my_services.json

Finally, you start the service(s):

export ORACLE_SERVICE_NAME=SALESGOLD
srvctl start service \
   -db $ORACLE_UNQNAME \
   -service $ORACLE_SERVICE_NAME

Additional Information

The export/import features work from Oracle Database 19c, Release Update 19 and beyond.
You can also export/import the definition of:
- Database: srvctl config database -db $ORACLE_UNQNAME -S 2 -exportfile my_db.json.json
- PDB: srvctl config pdb -db $ORACLE_UNQNAME -S 2 -exportfile my_pdb.json
- ACFS filesystem: srvctl config filesystem -S 2 -exportfile /tmp/my_filesystem.json
At time of writing, this functionality hasn’t made it into the documentation yet. Consider yourself lucky knowing this little hidden gem.

Final Words

Remember to recreate your custom services after a migration. Your application needs the service to connect in a proper way.

AutoUpgrade New Features: Upgrade RMAN Catalog Schema

With the latest version, 24.8, AutoUpgrade can upgrade the RMAN catalog schema after patching and upgrading. This is useful to those who take RMAN backups and duplicate their RMAN metadata to a catalog database.

If you don’t upgrade the catalog schema after patching and upgrading, you’ll see this message in the RMAN output:

PL/SQL package RCO.DBMS_RCVCAT version 19.24.00.00. in RCVCAT database is not current
PL/SQL package RCO.DBMS_RCVMAN version 19.24.00.00 in RCVCAT database is not current

Details

After patching or upgrading, AutoUpgrade upgrades the RMAN catalog schema in the postupgrade stage.
AutoUpgrade connects with RMAN to the recovery catalog and issues the upgrade catalog command.
AutoUpgrade does not execute dbmsrmansys.sql. Normally, this is only needed for the upgrade of the first catalog schema of a given release (like for the first database on Oracle Database 23ai), and even then, it might not be needed.

How To

Specify the connect string to the catalog database in the AutoUpgrade config file:
```
<prefix>.rman_catalog_connect_string=catalogdb
```
- catalogdb is a TNS alias to the catalog database.
Start AutoUpgrade to load the username and password for the recovery catalog:
```
java -jar autoupgrade.jar -config ... -load_password
```
Switch to the password group RMAN:
```
group rman
```
Add the username and password for a specific database:
```
add <ORACLE_SID> -user <catalog_schema_name>
```
- AutoUpgrade prompts for the password
Save the changes and exit the load password console.
```
save
exit
```

Start AutoUpgrade in deploy mode:

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

Happy Upgrading, Happy Patching

You can enhance the solution using an after_action script that starts a level 1 backup after the job. The after_action script takes place after the postupgrade stage, where AutoUpgrade upgrades the catalog schema.
Version 24.8 of AutoUpgrade does not support this feature when you use the -patch command line option. This is coming in a later version.

Appendix

Invalid credentials

When you enter the catalog credentials into the AutoUpgrade keystore, AutoUpgrade validates the credentials. Any errors result in AutoUpgrade returning the following message:
```
Invalid credentials, please try again.
```

To debug, run the following command:

$ORACLE_HOME/bin/rman TARGET / rcvcat <catalog_user>@<rman_catalog_connect_string>

Check the log files in:

<global_log_dir>/log/cfgtoollogs/upgrade/auto

AutoUpgrade New Features: Drop Database Link When Using Refreshable Clones

With the latest version, 24.8, AutoUpgrade can drop the database link after using refreshable clones.

Details

Certain jobs in AutoUpgrade require a database link to the source database.
Whenever you specify a database link using source_dblink, you can optionally instruct AutoUpgrade to drop it.
The default value is no, meaning AutoUpgrade leaves the database link in place.

How To

Get the latest version of AutoUpgrade:

wget https://download.oracle.com/otn-pub/otn_software/autoupgrade.jar

Instruct AutoUpgrade to drop the database link after completing the job:
```
upg1.drop_dblink=yes
```

Happy Upgrading

I’m a huge fan of using refreshable clones for upgrades and non-CDB to PDB migrations.

Granted, this is not the most ground-breaking enhancement we’ve introduced. But it’s yet another thing that makes your life a little easier.

What do you think could make AutoUpgrade even easier to use? Leave a comment and let us know.

Control The Final Refresh When Using Refreshable Clone PDBs in AutoUpgrade

When you migrate or upgrade with refreshable clone PDBs, you sometimes want to decide when the final refresh happens. Perhaps you must finish certain activities in the source database before moving on.

Here’s how to do that in AutoUpgrade.

The Final Refresh Dilemma

In AutoUpgrade, the final refresh happens at the time specified by the config file parameter start_time.

You specify start_time in the config file, but once you start the job, you cannot change it. Remember that you normally start AutoUpgrade a long time before start_time to allow the creation of the new PDB.

In some situations, you want more control. You might want to finish some work on the source database before AutoUpgrade starts the final refresh. Perhaps you need to kick users off or coordinate activities with other teams.

In that case, a fixed start time is not very flexible.

The Solution

Update: Check out the new proceed command.

Imagine my downtime window starting on Saturday, 30 November, 02:00.

At that time, I need to ask the application team to shut down the applications in the database, I need to run certain pre-migration tasks, and finally kill sessions if needed. So, I don’t want AutoUpgrade to start at 02:00 – I want to decide at which point after 02:00 that AutoUpgrade should start.

Here’s my approach

I create a config file and set the start_time parameter to the start of my downtime window.
```
upg1.start_time=30/11/2024 02:00:00
```
I start AutoUpgrade in deploy mode before my downtime starts:
```
java -jar autoupgrade.jar -config ... -mode deploy
```
- I must start well before the downtime window so AutoUpgrade has enough time to copy the database.
- Imagine my tests show it takes around four hours to copy the database. I decide to start on Friday, 29 November, 16:00, so the copy should end around 20:00 – well enough time before my downtime window.

AutoUpgrade now starts the CLONEPDB phase:

+----+-------+--------+---------+-------+----------+-------+---------------------------+
 |Job#|DB_NAME|   STAGE|OPERATION| STATUS|START_TIME|UPDATED|                    MESSAGE|
 +----+-------+--------+---------+-------+----------+-------+---------------------------+
 | 100|   TEAL|CLONEPDB|EXECUTING|RUNNING|  02:00:00| 4s ago|Creating pluggable database|
 +----+-------+--------+---------+-------+----------+-------+---------------------------+

Note the START_TIME value. It is the time when the final refresh happens.

I wait for AutoUpgrade to create the PDB and enter the REFRESHPDB phase:

+----+-------+----------+---------+-------+----------+-------+----------------------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|START_TIME|UPDATED|               MESSAGE|
+----+-------+----------+---------+-------+----------+-------+----------------------+
| 100|   TEAL|REFRESHPDB|EXECUTING|RUNNING|  02:00:00| 2s ago|PDB TEAL was refreshed|
+----+-------+----------+---------+-------+----------+-------+----------------------+

Then I stop the job:
```
upg> stop -job 100
```
- If I exit AutoUpgrade after stopping the job, don’t worry. As soon as I restart AutoUpgrade, it will pick up from where it left and continue with the job.
When I stop the job, there is no periodic refresh. I should refresh the PDB in the target CDB manually at regular intervals:
```
SQL> alter pluggable database teal refresh;
```
- If I don’t perform any periodic refresh, the redo will accumulate, and the final refresh will take longer. Keep the final refresh shorter by refreshing more often.
After the start of my downtime window (the start_time parameter), when I’m done on the source database and want to proceed with the final refresh, I resume the job in AutoUpgrade.
```
upg> resume -job 100
```
AutoUpgrade now realizes it is past the defined start_time and immediately moves on with the final refresh and the rest of the job.

Wrapping Up

Ideally, AutoUpgrade should offer better control over the process. We have a task on our backlog to come up with a better solution.

Update: Use the proceed command in AutoUpgrade to control the start time

However, refreshable clone PDBs are still a fantastic method for non-CDB to PDB migrations and for upgrades of individual PDBs.

There are a few quirks to be aware of, and if you are using Data Guard bear in mind that you can only plug in with deferred recovery. Other than that – it’s just to say…

Happy Migrating!

AutoUpgrade New Features: Update Enterprise Manager Configuration

With the latest version, 24.8, AutoUpgrade can interact with Oracle Enterprise Manager:

Blackout the database
Modify target configuration

Details

AutoUpgrade interacts with Enterprise Manager through the command line interface, EM CLI.
In the DRAIN phase, AutoUpgrade creates a blackout of the target. It will also remove the blackout again after the job.
The blackout is named blackout_<SID>.
AutoUpgrade updates the target configuration in Enterprise Manager to reflect the new Oracle home if the job succeeds.
If you restore the job (returning to the pre-upgrade state with Flashback Database), AutoUpgrade restores the original target configuration.

How To

Install and configure EM CLI on the database host.
Persist the credentials on the database host using the autologin feature.

Get the latest version of AutoUpgrade:

wget https://download.oracle.com/otn-pub/otn_software/autoupgrade.jar

Specify the path to EM CLI in the AutoUpgrade config file:
```
upg1.emcli_path=/u01/app/oracle/oem
```
Specify the target name from Enterprise Manager:
```
upg1.em_target_name=ORCL_myhost.domain.int
```

Happy Upgrading

You can use the functionality when you patch and upgrade your Oracle Database.

If you use Oracle Enterprise Manager, AutoUpgrade can handle even more tasks in your runbook.

What are the next tasks that AutoUpgrade should do for you? Leave a comment, and we’ll consider it.

How to Upgrade Oracle Database with Username and Password

Normally, you upgrade an Oracle Database with native operating system authentication. The upgrade tooling connects to the database as / as sysdba. However, in some organizations, this is not allowed for security reasons. Every connection must be with username and password.

How can you upgrade an Oracle Database using username and password?

Set-Up

First, I’ll disable connections using native operating system authentication. I do that in sqlnet.ora.

$ env | grep TNS
TNS_ADMIN=/u01/app/oracle/product/19/network/admin
$ cat $TNS_ADMIN/sqlnet.ora
sqlnet.authentication_services=(none)

Let me check that it is disabled:

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 6 09:55:05 2024
Version 19.21.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Upgrade Without Operating System Authentication

I ensure that my AutoUpgrade is the latest version, or at least version 24.7.241021:

$ java -jar autoupgrade.jar -version
build.version 24.7.241021
build.date 2024/10/21 11:16:20 -0400
build.hash babf5a631
build.hash_date 2024/10/18 18:36:27 -0400
build.supported_target_versions 12.2,18,19,21,23
build.type production
build.label (HEAD, tag: v24.7, origin/stable_devel, stable_devel)
build.MOS_NOTE 2485457.1
build.MOS_LINK https://support.oracle.com/epmos/faces/DocumentDisplay?id=2485457.1

This is my AutoUpgrade config file. There’s nothing special in it, except for global.keystore which tells AutoUpgrade where to store its keystore. AutoUpgrade uses the keystore to keep your password safe until it is needed.

$ cat UPGR.cfg
global.autoupg_log_dir=/home/oracle/logs/autoupgrade-UPGR
global.keystore=/home/oracle/autoupgrade-keystore
upg1.source_home=/u01/app/oracle/product/19
upg1.target_home=/u01/app/oracle/product/23
upg1.sid=CDB19
upg1.timezone_upg=NO

Now, I’m starting AutoUpgrade in -load_password mode. This is the first time I use it, so AutoUpgrade prompts for a password that it can use to encrypt the keystore:

$ java -jar autoupgrade.jar -config UPGR.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

To enter the username and password for my database, I change the group to PWD:’

PWD> group PWD
Group [PWD] is already active

Then, I can enter my username and password for my database (CDB19). AutoUpgrade validates the credentials, so you better make sure they work at this point:

PWD> add CDB19 -user SYS
Enter your secret/Password:
Re-enter your secret/Password:
Database SID: cdb19              User: SYS

I save the keystore and convert it into an auto-login keystore, so I don’t have to enter the keystore password every time I use AutoUpgrade:

PWD> save
Convert the AutoUpgrade keystore to auto-login [YES|NO] ? yes

Exit the keystore.

PWD> exit

AutoUpgrade Password Loader finished - Exiting AutoUpgrade

Finally, I start the upgrade by starting AutoUpgrade in deploy mode:

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

That’s it!

Now, I can upgrade my Oracle Database without relying on native operating system authentication, using username and password instead.

SQLNET.ORA

Just a few words about sqlnet.ora and how to set up native operating system authentication.

Your operating system user (for instance, oracle) must be added to the appropriate groups in your operating system.
On UNIX/Linux, you allow native operating system authentication by authentication_services=beq in sqlnet.ora, or you can omit the parameter completely.
On Windows, you allow native operating system authentication by authentication_services=nts in sqlnet.ora. If you omit the parameter, then it won’t work.

Happy Upgrading!

How to Solve DCS-12300:Failed to Clone PDB During Remote Clone (DBT-19407)

A customer reached out to me:

I want upgrade a PDB from Oracle Database 19c to 23ai. It’s in a Base Database Service in OCI. I use the Remote clone feature in the OCI console but it fails with DCS-12300 because IMEDIA component is installed.

The task:

Clone a PDB using the OCI Console Remote clone feature
From a CDB on Oracle Database 19c to another CDB on Oracle Database 23ai
Upgrade the PDB to Oracle Database 23ai

Let’s see what happens when you clone a PDB:

It fails, as explained by the customer.

Let’s dig a little deeper. Connect as root to the target system and check the DCS agent.

$ dbcli list-jobs

ID                                       Description                                                                 Created                             Status
---------------------------------------- --------------------------------------------------------------------------- ----------------------------------- ----------
...
6e1fa60c-8572-4e08-ba30-cafb705c195e     Remote Pluggable Database:SALES from SALES in db:CDB23                      Tuesday, September 24, 2024, 05:04:13 UTC Failure

$ dbcli describe-job -i 6e1fa60c-8572-4e08-ba30-cafb705c195e

Job details
----------------------------------------------------------------
                     ID:  6e1fa60c-8572-4e08-ba30-cafb705c195e
            Description:  Remote Pluggable Database:SALES from SALES in db:CDB23
                 Status:  Failure
                Created:  September 24, 2024 at 5:04:13 AM UTC
               Progress:  35%
                Message:  DCS-12300:Failed to clone PDB SALES from remote PDB SALES. [[FATAL] [DBT-19407] Database option (IMEDIA) is not installed in Local CDB (CDB23).,
 CAUSE: The database options installed on the Remote CDB(CDB19_979_fra.sub02121342350.daniel.oraclevcn.com) m
             Error Code:  DCS-12300
                  Cause:  Error occurred during cloning the remote PDB.
                 Action:  Refer to DCS agent log, DBCA log for more information.

...

What’s Going on?

First, IMEDIA stands for interMedia and is an old name for the Multimedia component. The ID of Multimedia is ORDIM.

Oracle desupported the Multimedia component:

Desupport of Oracle Multimedia Oracle Multimedia is desupported in Oracle Database 19c, and the implementation is removed. … Oracle Multimedia objects and packages remain in the database. However, these objects and packages no longer function, and raise exceptions if there is an attempt made to use them.

In the customer’s and my case, the Multimedia component is installed in the source PDB, but not present in the target CDB. The target CDB is on Oracle Database 23ai where this component is completely removed.

If you plug in a PDB that has more components than the CDB, you get a plug-in violation, and that’s causing the error.

Here’s how you can check whether Multimedia is installed:

select   con_id, status 
from     cdb_registry 
where    comp_id='ORDIM' 
order by 1;

Solution 1: AutoUpgrade

The best solution is to use AutoUpgrade. Here’s a blog post with all the details.

AutoUpgrade detects that multimedia is already present in the preupgrade phase. Here’s an extract from the preupgrade log file:

INFORMATION ONLY
  ================
    7.  Follow the instructions in the Oracle Multimedia README.txt file in <23
      ORACLE_HOME>/ord/im/admin/README.txt, or MOS note 2555923.1 to determine
      if Oracle Multimedia is being used. If Oracle Multimedia is being used,
      refer to MOS note 2347372.1 for suggestions on replacing Oracle
      Multimedia.

      Oracle Multimedia component (ORDIM) is installed.

      Starting in release 19c, Oracle Multimedia is desupported. Object types
      still exist, but methods and procedures will raise an exception. Refer to
      23 Oracle Database Upgrade Guide, the Oracle Multimedia README.txt file
      in <23 ORACLE_HOME>/ord/im/admin/README.txt, or MOS note 2555923.1 for
      more information.

When AutoUpgrade plugs in the PDB with Multimedia, it’ll see the plug-in violation. But AutoUpgrade is smart and knows that Multimedia is special. It knows that during the upgrade, it will execute the Multimedia removal script. So, it disregards the plug-in violation until the situation is resolved.

AutoUpgrade also handles the upgrade, so it’s a done deal. Easy!

Solution 2: Remove Multimedia

You can also manually remove the Multimedia component in the source PDB before cloning.

I grabbed these instructions from Mike Dietrich’s blog. They work for a 19c CDB:

cd $ORACLE_HOME/rdbms/admin
#First, remove ORDIM in all containers, except root
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b imremdo_pdbs -d $ORACLE_HOME/ord/im/admin imremdo.sql
#Recompile
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
#Last, remove ORDIM in root
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b imremdo_cdb -d $ORACLE_HOME/ord/im/admin imremdo.sql
#Recompile
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
#Remove leftover package in all containers
echo "drop package SYS.ORDIMDPCALLOUTS;" > vi dropim.sql
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b dropim -d '''.''' dropim.sql

Without the Multimedia component cloning via the cloud tooling works, but you are still left with a PDB that you attend to.

If you’re not using AutoUpgrade, you will use a new feature called replay upgrade. The CDB will see that the PDB is a lower-version and start an automatic upgrade. However, you still have some manual pre- and post-upgrade tasks to do.

One of the reasons I prefer using AutoUpgrade.

Upgrade Encrypted PDB in Cloud to Oracle AI Database 26ai

Here’s a cool way of upgrading your Oracle Database in OCI to Oracle AI Database 26ai.

This post was originally written for Oracle Database 23ai, but it can be used to Oracle AI Database 26ai as well.

I will move my PDB to a new Base Database System using refreshable clone PDBs and AutoUpgrade.

The benefit of using this approach is:

Much shorter downtime than an in-place upgrade.
A brand-new Base Database System, which means the operating system and Oracle Grid Infrastructure is already on a newer version.

I’m using a Base Database Service as an example, but the target system could also be Exadata Database Service, Exadata Cloud@Customer, or, in fact, any other offering of Oracle Database.

My Environment

I have one PDB that I want to upgrade. It’s called SALES.

Source

Base Database System on 19.23.0
Name DBS19

Target

Base Database System on 23.4.0
Name DBS23

How To

Prepare AutoUpgrade

Get the latest version of AutoUpgrade:
```
wget https://download.oracle.com/otn-pub/otn_software/autoupgrade.jar
```
Copy the new version of AutoUpgrade to your source and target system. You can put it into $ORACLE_HOME/rdbms/admin if you like, but it is not a requirement.
I create an AutoUpgrade config file, named sales.cfg, which I store on both servers:
```
global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade
global.keystore=/u01/app/oracle/cfgtoollogs/keystore
upg1.source_home=/u01/app/oracle/product/19.0.0.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/23.0.0.0/dbhome_1
upg1.sid=CDB19
upg1.pdbs=SALES
upg1.target_cdb=CDB23
upg1.source_dblink.SALES=CLONEPDB 600
upg1.target_pdb_copy_option.SALES=file_name_convert=none
upg1.target_version=23
upg1.start_time=12/05/2024 02:00:00
```
- I must specify global.keystore to allow AutoUpgrade to create a keystore to work with my encrypted PDB.
- source_home and target_home list the Oracle Home of the source and target CDB, respectively. It doesn’t matter that the two homes exist on one server only.
- sid and target_cdb contain the SID of the source and target CDB, respectively. The parameters are case sensitive.
- pdbs contains the name of the PDB I want to upgrade, sales. If needed, I could specify more PDBs. Don’t include the domain or use the service name.
- source_dblink has the name of the database link (CLONEPDB) and the rate at which the target CDB brings over redo and rolls forward the copy (600 seconds or 10 minutes).
- I want to use ASM and Oracle Managed Files, so I set target_pdb_copy_option accordingly.
- Since my source and target CDB are not on the same host, AutoUpgrade can’t automatically determine the target version. I specify that manually using target_version.
- start_time specifies when downtime starts. At this point, AutoUpgrade refreshes the PDB for the last time and then moves on with the upgrade.

Prepare Source PDB

I connect to the source PDB. I create a user (for a database link) and grant privileges:

create user dblinkuser identified by ... ;
grant create session, 
      create pluggable database, 
      select_catalog_role to dblinkuser;
grant read on sys.enc$ to dblinkuser;

After the upgrade, I can drop the user.

Prepare Target CDB

I connect to the target CDB and create a database link pointing to my source PDB:

create database link clonepdb
connect to dblinkuser
identified by dblinkuser
using '';

Check that the database link works:
```
select * from dual@clonepdb;
```
If the query fails with ORA-02085, then you can use alter system set global_names=false;.
- After the upgrade, I can drop the database link.

Analyze and Fix Source PDB

First, I analyze the source PDB for upgrade readiness. On the source system:
```
java -jar autoupgrade.jar -config sales.cfg -mode analyze
```
The summary report lists the following precheck failures, which I safely ignore:
- TDE_PASSWORDS_REQUIRED – I will fix that on the target system.
- TARGET_CDB_AVAILABILITY – the target CDB is remote, and AutoUpgrade can’t analyze it.
Then, I execute the preupgrade fixups:
```
java -jar autoupgrade.jar -config sales.cfg -mode fixups
```
- This changes my source PDB, so I do it as close to my maintenance window as possible.
- AutoUpgrade issues a warning that the target Oracle home is not present. You can safely disregard this.

Upgrade

Since my PDB is encrypted, I must add the source and target CDB keystore password to the AutoUpgrade keystore. I start the load password console on the target host:
```
java -jar autoupgrade.jar -config sales.cfg -load_password
```

In the load password console, I add the keystore passwords of the source and target CDB:

TDE&gt; add CDB19
Enter your secret/Password:    
Re-enter your secret/Password: 
TDE&gt; add CDB23
Enter your secret/Password:    
Re-enter your secret/Password:

I save the passwords and convert the AutoUpgrade keystore to an auto-login keystore:
```
TDE&gt; save
Convert the keystore to auto-login [YES|NO] ? 
```
I start AutoUpgrade in deploy mode:
```
java -jar autoupgrade.jar -config sales.cfg -mode deploy
```
- AutoUpgrade:
  - Copies the data files over the database link.
  - Rolls the copies of the data files forward with redo from the source.
  - At the time specified by start_time, issues a final refresh and disconnects the PDB from the source. You can change the start time using the proceed command in the AutoUpgrade console.
  - Upgrades the PDB.
- You can monitor the upgrade by using the command lsj -a 30.
I have now upgraded my PDB to Oracle Database 23ai.

The Fine Print

Before Upgrade

Ensure that redo is kept in the Fast Recovery Area of the source database until it has been applied on the target. Either postpone your archive backups or change the archive log deletion policy so the archive logs remain on disk.
Familiarize yourself with the concept of the AutoUpgrade keystore.
The source PDB must be Oracle Database 19c or newer to upgrade directly to Oracle Database 23ai.

During Upgrade

Disconnect users from the source database. Right before the upgrade starts, AutoUpgrade executes a final refresh. The last redo from the source database is applied to ensure no data is lost. You must ensure that no users are connected to the source database after this time. Otherwise, that data will be lost.
AutoUpgrade starts the final refresh at the start time specified in the config file:
```
upg1.start_time=25/09/2023 06:30:00
```
You must be careful about disconnecting users from the source database. Remember, AutoUpgrade connects to the source database over a database link as a regular user (not SYS). This means the listener must be available, and you can’t enable restricted session or similar means.
Check this blog post if you want to be in control over when the final refresh happens.

After Upgrade

Recreate the services that you use in your connect strings.
Update your connection string. The PDB is now on a different Base Database System.
Start a new full backup of the target database after the migration.
The OCI console will recognize and display the new PDB after a while. You don’t have to do anything … than to wait for the automatic sync job.

Data Guard

When using refreshable clone PDBs you can’t reuse the data files on the standby database. It is similar to STANDBYS=NONE on the CREATE PLUGGABLE DATABASE statement. So, you plug in with deferred recovery on the standby database.

The easiest solution is to configure Data Guard after you have upgraded the database. However, it might not always be feasible. If your target CDB already has Data Guard, then you need to restore the data files to the standby database and enable recovery. Check Making Use Deferred PDB Recovery and the STANDBYS=NONE Feature with Oracle Multitenant (Doc ID 1916648.1) for details.

Real Application Clusters (RAC)

AutoUpgrade detects if the target CDB is a RAC database. You don’t have to specify or do anything. AutoUpgrade handles everything for you.

How to Prepare Your Oracle Database for Release 23ai

Here is a list of things to check and carry out while preparing for Oracle Database 23ai.

These tasks are not mandatory, but I recommend them based on my experience. It increases your chance of upgrading with success.

Weeks Before

Platform Certification

Ensure that the new release supports your operating system. When available, you can check it in the installation guides. However, you can find the most up-to-date information in the Certifications tab in My Oracle Support.

Clients

Check the clients connecting to the database and ensure their client driver version is compatible with Oracle Database 23ai. The client/server interoperability matrix gives you an overview of which clients work with which servers.

Optionally, monitor the database over time to generate a list of clients connecting.

AutoUpgrade

Get the latest version of AutoUpgrade from My Oracle Support. I recommend that you always use the latest version. AutoUpgrade is fully backward compatible, so any newer version can upgrade older releases of Oracle Database.

Upgrade Readiness

Run AutoUpgrade in analyze mode to determine the upgrade readiness of your Oracle Database. Check the summary report for findings with no fixups. Such findings must be dealt with manually.

You can run the analysis even before you install the new Oracle home. Simply specify target_version=23 in your AutoUpgrade config file, and AutoUpgrade knows which checks to run.

Dictionary Check

Check the dictionary on your most important databases. You can run a dictionary check together with an AutoUpgrade analysis. In your config file:

upg1.run_dictionary_health=full

Or, you can run it separately using DBMS_DICTIONARY_CHECK.

Behavior Changes

Review the upgrade guide to find information about deprecated and desupported features, plus any behavior changes.

Grid Infrastructure

If Grid Infrastructure manages your database either in RAC or Oracle Restart configuration, you should upgrade it as well. If you can tolerate an additional maintenance window, I’d recommend upgrading Grid Infrastructure in advance.

Days Before

Statistics

Gather dictionary and fixed objects statistics:

begin
   dbms_stats.gather_fixed_objects_stats;
   dbms_stats.gather_schema_stats('SYS');
   dbms_stats.gather_schema_stats('SYSTEM')
end;

I prefer gathering schema stats rather than dictionary stats. I’ve seen a few edge cases solved by schema stats, and generally, current stats on SYS and SYSTEM are sufficient.

Gather statistics no earlier than seven days before the upgrade. If AutoUpgrade determines your statistics are older than that, it will gather them as part of the upgrade. This extends down time unnecessarily.

If you’re curious, you can find details about the importance of fixed objects statistics in a blog post by Maria Colgan.

Install Oracle Home

Install an Oracle Database 23ai Oracle home. In release 23ai, the Oracle homes that you download are already patched with the latest Release Update.

If you have additional patches to apply, do so now. You should have all patches in place before you start the upgrade.

On the Day of Upgrade

Backup

If time allows, run a level 0 backup. If not, you have to settle with a level 1.

Scheduler

Normally, it is not necessary to disable the scheduler. But in some situations, it can be beneficial.

That’s It

You are now ready to start the journey to Oracle Database 23ai and the many exciting new features.

Happy upgrading!

How Does It Work?

How Can I Use the Information?

Final Words

Further Reading

Database Managed Services

Clusterware Managed Services

Additional Information

Final Words

Further Reading

Details

How To

Happy Upgrading, Happy Patching

Appendix

Further Reading

Invalid credentials

Details

How To

Happy Upgrading

The Final Refresh Dilemma

The Solution

Wrapping Up

Further Reading

Details

How To

Happy Upgrading

Set-Up

Upgrade Without Operating System Authentication

SQLNET.ORA

Happy Upgrading!

What’s Going on?

Solution 1: AutoUpgrade

Solution 2: Remove Multimedia

Further Reading

My Environment

Source

Target

How To

Prepare AutoUpgrade

Prepare Source PDB

Prepare Target CDB

Analyze and Fix Source PDB

Upgrade

The Fine Print

Before Upgrade

During Upgrade

After Upgrade

Data Guard

Real Application Clusters (RAC)

Further Reading

Weeks Before

Platform Certification

Clients

AutoUpgrade

Upgrade Readiness

Dictionary Check

Behavior Changes

Grid Infrastructure

Days Before

Statistics

Install Oracle Home

On the Day of Upgrade

Backup

Scheduler

That’s It