Oracle Database 21c Is Here

Last week Oracle released Oracle Database 21c for additional platforms: Linux and Exadata. Other platforms will follow. You should keep an eye out for Release Schedule of Current Database Releases (Doc ID 742060.1) for further information.

Things to Notice

In my part of the Oracle Database, there are things to notice. I want to highlight:

To get all the details, visit the Upgrade and Utilities part of the new features documentation. There are some good examples of how the features can be used.

Behaviour Changes

Read-Only Oracle Home (ROOH) is now the default. Be sure to set the following environment variables to control the location of these directories:

  • ORACLE_BASE_HOME
  • ORACLE_BASE_CONFIG

I like ROOH, but it takes some time to get used to. For instance, network/admin files (tnsnames, sqlnet) and dbs files (pfile, spfile) are now in a new location.

The Pre-Upgrade Information Tool or preupgrade.jar is removed and replaced by AutoUpgrade. A few new parameters have been introduced to make the transition easier.

Innovation Release

Remember, 21c is an innovation release, which means a shorter support window than Long Term Releases such as Oracle Database 19c. If you adopt Innovation Releases, you should be prepared to upgrade to the next database release within one year after the next database release ships.

I would not recommend that you upgrade your production systems to Oracle Database 21c due to the limited support period. Not unless you are prepared to upgrade the database soon again – when support runs out. Oracle Database 19c is the current Long Term Support release. I recommend that for production databases.

Different release types for Oracle Database - innovation vs long term support

To learn more about innovation release and our release model, have a look at our slide deck. We discuss it in the first chapter.

New Features

I want to mention a few new features. They haven’t attracted as much attention as the marque features, but they are still cool.

Expression based init.ora parameters make it possible to base database parameters (init.ora) on calculations made on the system’s configuration. For example, setting the database parameter CPU_COUNT to half the number of CPUs (Windows):

alter system set cpu_count='$NUMBER_OF_PROCESSORS/2';

For more details, check out my video on YouTube.

Placeholders in SQL DDL Statements can improve application security because sensitive information, like passwords, doesn’t need to be hardcoded in SQL DDL. Example: You can make this statement:

CREATE USER :!username IDENTIFIED BY :!password ...

And Oracle Call Interface programs can substitute the placeholders into:

CREATE USER "DANIEL" IDENTIFIED BY "MyS3cr3tP!d" ...

This is similar to data binding but occurs in Oracle Client.

Conclusion

The complete 21c documentation is online, so I suggest that you head on over there and have a look. In the upgrade guide, you can find the list of behavior changes and also deprecated and desupported functionality. And finally, but most interesting perhaps, is Learning Database New Features.

Try it out in Always Free ADB or explore the Oracle LiveLabs.

Upgrade with Less Downtime

You can upgrade your Oracle Database with less downtime. With the release of AutoUpgrade 21.2 we added the fast deploy option. It allows you to run the preupgrade fixups while the database is still online. Only the actual upgrade will now require downtime.

The Usual Upgrade

When you upgrade the usual way, i.e., using AutoUpgrade in deploy mode, then you:

  1. Analyze the database – java -jar autoupgrade.jar -mode analyze This is non-intrusive and doesn’t change anything on the database. You get a report, and you can fix any issue that AutoUpgrade can’t fix for you.
  2. Upgrade the database – java -jar autoupgrade.jar -mode deploy The database is analyzed again. Based on the findings of the analysis, a set of preupgrade fixups are executed. Finally, the database upgrade takes place.

Best practice is to use AutoUpgrade in deploy mode

During step #2, the database is not available – you have downtime. Even though the database was analyzed previously, it will be re-analyzed to catch any new issues. This is the safest approach. From the first analysis (in step #1) to the second analysis (in step #2), potentially new issues can occur.

We talked to a few of our customers who are really trying to reduce the downtime of an upgrade. For them – every second counts and has a significant impact on their business. They asked us to make the upgrade faster.

Fast Deploy Mode

One customer suggested moving the final analysis and preupgrade fixup outside of the downtime window. Shortly before the downtime would start, they would like to run an analysis and the preupgrade fixups. Next, they would wait until the downtime window starts and do only the upgrade.

I must stress; there is a risk that a new issue pops up between the fixups and the upgrade – but this one customer is willing to take the risk. In exchange, they will get upgrades with less downtime.

We call this approach fast deploy. The procedure is:

  1. Analyze the database – java -jar autoupgrade.jar -mode analyze
  2. Run the preupgrade fixups – java -jar autoupgrade.jar -mode fixups
  3. Now downtime starts
  4. Upgrade the database – java -jar autoupgrade.jar -mode upgrade

To reduce downtime use the new fast deploy mode - with increased risk

Fast deploy requires AutoUpgrade 21.2 or newer. The target database release can be any of the target releases already supported by AutoUpgrade, meaning 12.2 or higher.

Here are the details of the feature – from the AutoUpgrade change log:

This feature enables DBA’s to run the prechecks and prefixups while the database is still online and then run deploy to complete the upgrade. Once the fixups have been run on the source database, DBA can then skip the prechecks and prefixup stages and proceed directly to the phases that follow for example: Database can be online: java -jar autoupgrade.jar -mode fixups -config yourconfig Upgrade time: java -jar autoupgrade.jar -mode upgrade -config yourconfig The -mode upgrade command proceeds directly to the phases that follow the prechecks and prefixups stages during deploy. The main goal of this feature is to reduce database downtime by running the fixups when your database is online and open for business and then skipping the fixups when your database is offline. See Oracle documentation for further details.

Conclusion

If your application is really sensitive to downtime, you can now upgrade with less downtime using fast deploy. It introduces a slight risk that the preupgrade analyze will not detect an issue that will cause troubles later on. You have to choose whether that risk is acceptable to you.

Our recommendation is to use the old approach – AutoUpgrade in deploy mode. Consider only fast deploy if downtime really hurts.

If you are interested in seeing which other things got put into AutoUpgrade 21.2, you can check the change log.

Upgrade Internals – Webinar

Here is a little teaser for our upcoming webinar on Database Upgrade Internals – and so much more on Wednesday 10 February 2021 at 10:00 CET. You can still sign up.

Virtual Classroom Series - Upgrade to Oracle Database 19c

We have a special guest star joining, Frederick Alvarez – one of the masterminds behind AutoUpgrade. In addition, as usual Mike Dietrich will be there, and we have prepared a whole lot of new contents for this webinar. And – if I have to say so myself – it has become quite good and interesting. If you are still not convinced take a look at this!

What Actually Happens During Upgrade

We will go into the upgrade itself, dissect it, and discover what actually happens. Hopefully, it will no longer be a black box to you.

How are the workers processing the upgrade? How are the workers processing the upgrade?

How is a container database upgraded? How is a container database upgraded?

What goes on in the phases of the upgrade? What goes on in the phases of the upgrade?

I Need More Power, Scotty

We will also cover, how you can make your upgrade faster and show what you can achieve.

What is the benefit of gathering stats before the upgrade? What is the benefit of gathering stats before the upgrade?

What Can You Achieve With AutoUpgrade

A deep dive into the checks that are executed by AutoUpgrade. A list of some of the comprehensive checks that are executed by AutoUpgrade A list of some of the comprehensive checks that are executed by AutoUpgrade

One page monitoring One page monitoring

The Old Releases

For those of you on very old releases, we will discuss how you can upgrade to Oracle Database 19c.

What to consider when doing upgrades from very old releases What to consider when doing upgrades from very old releases

See You

Once the webinar is over we will publish a recording and make the slides available for download.

See you on wednesday!

Control Upgrade Parallelism In AutoUpgrade

Under the hood, AutoUpgrade uses the Parallel Upgrade Utility or catctl.pl to do the database upgrade. The Parallel Upgrade Utility has a long list of options that you can configure. The parallel options being the most notable. In your AutoUpgrade config file you can now specify a subset of options to catctl.pl using the parameter catctl_options.

Now, you should not expect a 10x improvement by adding a ton of CPUs to your upgrade. Mike Dietrich posted a really good article that explains what matters to an upgrade when it comes to performance. But f you want to squeeze out the very last resources on your system during upgrade, or want to fine-tune the distribution of CPUs the resource consumption, you can do it with AutoUpgrade.

How

First, you can find a list of catctl parameters in the documentation.

When you have determined the parameters that you want to use, specify them in your config file either globally (for all upgrades):

global.catctl_options=-N 8

or locally for a specific upgrade:

upg1.catctl_options=-N 8

The above examples will run the upgrade of PDBs using eight parallel processes.

You should have a look in the documentation to know the minimum and maximum values for the settings. At least for the parallel settings it is really good to know.

Non-CDB

Support for this parameter and non-CDB databases was added in 21.1.2. To ensure that the upgrade runs with as many parallel processes as possible:

upg1.catctl_options=-n 8

CDB and PDB

When you upgrade a CDB the following happen:

  • First, CDB$ROOT is upgraded using eight parallel processes.
  • Next, a number of PDBs are upgraded concurrently, starting with PDB$SEED. The total number of parallel processes to use is controlled by the parameter n.
  • Each individual PDB is upgraded using a number of SQL processes as well. This is controlled by the parameter N.

This means that the number of PDBs that are upgraded at the same time is: n / N

What is the best value of n?

  • If you are conservative you set n to CPU_COUNT.
  • If you are bold, you could probably raise it further, because some of the phases in the upgrade runs serially or doesn’t use the full parallel capacity. Try to set n to CPU_COUNT + 10 % and see how loaded your CPUs get. Keep increasing until you find a suitable level.

In the documentation there are some really good examples and explanations of using N and n together.

On a system with CPU_COUNT=48, put the following in your AutoUpgrade config file to run 6 PDB upgrades concurrently using 8 parallel processes:

upg1.catctl_options=-n 48 -N 8

Note, regardless of what you specify, when it comes to CDB$ROOT AutoUpgrade will always run with the maximum number of parallel processes. CDB$ROOT is special and it must be upgraded before any of the other ones can start. Hence, it makes sense to get it completed as fast as possible.

If you are doing an unplug-plug upgrade of a single PDB it could be a good idea to add more parallel processes to that single upgrade. If you want to use 8 parallel processes:

upg1.catctl_options=-N 8

Conclusion

It can be useful to override the default parallel settings during upgrades. You should not expect a 10x performance improvement, but you might squeeze out the very last resources. What is the best setting? It depends. You should go with the defaults, or test it using your own databases.

History

12 April 2021: Added information about support of non-CDB databases as of AutoUpgrade 21.1.2.

AutoUpgrade One-liner

You can use AutoUpgrade to upgrade a database using only a single command line. No config file is needed!

Whenever we talk about AutoUpgrade, we also mention the config file. The file that contains information about what has to be upgraded. A very simple version of such a config file could look like this:

upg1.sid=DB12
upg1.source_home=/u01/app/oracle/product/12.2.0.1
upg1.target_home=/u01/app/oracle/product/19

This is the preferred and recommended way of using AutoUpgrade. But you can actually specify everything on the command line.

Upgrade in One Line

By using the command line option config_values you can now specify the config file entries on the command line. Instead of using the above config file you could execute:

java -jar autoupgrade.jar \
   -config_values "sid=DB12,source_home=/u01/app/oracle/product/12.2.0.1,target_home=/u01/app/oracle/product/19" \
   -mode analyze

Notice how I used config_values to specify the contents of the config file. The prefix that you have to use in the config file (in this case upg1) is not used here (only for global entries, like global.autoupg_log_dir).

If you have several databases to upgrade, you separate them with as asterisk (*):

-config_values "sid=DB1,...,*,sid=DB2,...,*,sid=DB3,..."

Using Environment Variables

A few of the config file entries can be specified as environment variables instead:

Config file entry Environment variable
sid $ORACLE_SID
source_home $ORACLE_HOME
target_home $ORACLE_TARGET_HOME
target_version $ORACLE_TARGET_VERSION

So, you can start AutoUpgrade like this as well:

export ORACLE_SID=DB12
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1
export ORACLE_TARGET_HOME=/u01/app/oracle/product/19
java -jar autoupgrade.jar -config_values -mode analyze

And it works fine on Windows as well. Just use set instead of export.

What Happens

AutoUpgrade first needs to determine the global logging directory (global.autoupg_log_dir).

  • If you specify the global logging directory it will be used.
  • If you do not specify the global logging directory one of the following will be used:
    • Linux/Unix: /tmp/autoupgrade
    • Windows: C:\Users\name\AppData\Local\Temp\autoupgrade

Next, AutoUpgrade will create a config file using the information supplied either using config_values or from the environment.

From here on AutoUpgrade behaves as usual.

Conclusion

If needed, you can provide all input to AutoUpgrade in one command line. This is useful if you are using AutoUpgrade in scripts or from Ansible or similar orchestration tools.

I would still recommend the use of a config file. It is easier to read and write the options in a nice formatted text file. Further, you avoid the potential trouble of escaping characters on the command line. And, finally, you avoid having a very long an unreadable command line. These arguments are, by the way, the same we use when we recommend using a parameter file (.par) for Data Pump.

Further Reading

How to Upgrade a Single PDB

AutoUpgrade now supports unplug-plug upgrades. You unplug a PDB from a lower release CDB and you plug it into a higher release CDB. After plug-in the PDB is upgraded and eventually it can be opened in normal, READ WRITE mode.

Concept of unplug-plug upgrades which are supported with AutoUpgrade version 21.1.1

When it comes to upgrading in the multitenant world, I am a big fan of unplug-plug upgrades. The concept comes with a number of benefits:

  • It is much faster to upgrade an individual PDB using unplug-plug compared to a CDB with just one PDB in it. When you do an unplug-plug upgrade, the database just need to upgrade the PDB. Compare that to a CDB which first upgrades CDB$ROOT, and then PDB$SEED and any user PDBs.
  • You don’t have to arrange downtime for all the PDBs in the CDB. Downtime is just needed for the PDB that you will upgrade.
  • Combine it with refreshable PDBs and you can still have a really good fallback option. You can check out a previous blog post to see how you can use refreshable PDBs.

AutoUpgrade and Unplug-plug Upgrade

Starting from version 21, AutoUpgrade can now perform unplug-plug upgrades. A newer version of AutoUpgrade can upgrade to older database releases as well, so don’t worry if the AutoUpgrade version doesn’t match the Oracle Database release that you are upgrading to.

There are some requirements that must be met in order to perform unplug-upgrade, so I suggest that you take a look in the documentation.

You have to create the target CDB yourself. It is by design that AutoUpgrade doesn’t do this for you. First, creating a CDB requires a lot of information and it can be done in many different ways (ASM? Which components? RAC?). You would need a very long config file to supply all that information. Also, it takes time to create a CDB and if AutoUpgrade would have to do that inside the maintenance window, it would be prolonged considerably.

During unplug-plug upgrades AutoUpgrade also allows you to change the name of the PDBs and you can decide whether you want to reuse the unplugged data files or take a copy.

How to

Imagine the following AutoUpgrade config file:

upg1.sid=CDB1
upg1.target_cdb=CDB2
upg1.pdbs=hr,logistics
upg1.source_home=/u01/app/oracle/product/12.2.0.1
upg1.target_home=/u01/app/oracle/product/19
upg1.target_pdb_name.hr=people

AutoUpgrade will unplug PDBs hr and logistics from CDB1 and plug them into CDB2. In addition, it will change the name of hr to people when it is plugged into CDB2. Finally, you must specify the Oracle Home of the two CDBs, so AutoUpgrade can set the environment correctly and connect to the databases.

If you use lsj command to monitor the progress it does actually look like you are only upgrading one of the PDBs:

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+------------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME| UPDATED|           MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+------------------+
| 100|   CDB1|DBUPGRADE|EXECUTING|RUNNING|20/12/22 15:25|15:29:03|13%Upgraded PEOPLE|
+----+-------+---------+---------+-------+--------------+--------+------------------+
Total jobs 1

But if you look into the details with status -job 100 you can see that both PDBs are upgraded in parallel:

upg> status -job 100

... (removed a lot of information)

Details:
[Upgrading] is [0%] completed for [cdb1-people] 
                 +---------+-------------+
                 |CONTAINER|   PERCENTAGE|
                 +---------+-------------+
                 |   PEOPLE|UPGRADE [13%]|
                 |LOGISTICS|UPGRADE [13%]|
                 +---------+-------------+

When the upgrade completes, the PDBs are ready to be used. I suggest that you verify that the databases are open in READ WRITE mode and not in restricted mode. Finally, save the state, so the PDBs start automatically together with the CDB:

SQL> select name, open_mode, restricted from v$pdbs where name in ('PEOPLE', 'LOGISTICS');
SQL> --Verify open_mode=read write and restricted=no
SQL> alter pluggable database people save state;
SQL> alter pluggable database logistics save state;

Caution

With unplug-plug upgrades you can’t use Flashback Database as your fallback plan. It doesn’t work across the plug-in operation. You either have to:

  • Instruct AutoUpgrade to copy the unplugged data files before it plugs into the higher release CDB. That way, you still have the old unplugged data files, and just re-create the PDB in the lower release CDB. But you will have extra downtime because you need to copy the data files.
  • Use Refreshable PDBs to build a copy of your PDB in the higher release, target CDB. When you want to do the upgrade, perform the last refresh and upgrade the refreshable PDB.

Both of the above options require additional disk space to hold a copy of the database.

Of course, you can also use your regular backups as fallback.

What If

Your Target CDB Has a Standby Database?

For now, don’t use AutoUpgrade to make unplug-plug upgrades, if the target CDB has standby databases. A plug-in operation with a standby database is a tricky maneuvre, at least when you want to re-use the data files. We are still trying to figure out how to implement it in AutoUpgrade.

Having said that, it is absolutely doable. You can read more about in the following MOS notes:

You Are Using TDE Tablespace Encryption?

For now, don’t use AutoUpgrade to perform unplug-plug upgrades, if any tablespace in the PDB is encrypted with TDE Tablespace Encryption. We are working on making AutoUpgrade capable of better interacting with the TDE keystore, so keep an eye out for coming versions.

If TDE Tablespace Encryption is enabled in the target CDB, you can still use AutoUpgrade. The PDB will be plugged in as an unencrypted PDB.

Conclusion

Doing unplug-plug upgrades is now supported by AutoUpgrade as of version 21. It includes useful features for renaming PDBs and using copies of unplugged data files.

There is a video on YouTube that shows the procedure. And while you are there, I suggest that you subscribe to our channel.

Further Reading

How to Upgrade with AutoUpgrade and Data Guard

You can upgrade your database to a new release with AutoUpgrade and keep the Data Guard setup intact. The standby database(s) can be upgraded implicitly via the redo from the primary database, and there is no need to rebuild the standby database after upgrade.

The process: Overview of upgrade with a data guard

In the following I will be using this setup: Overview of the environment that is used for this procedure

In advance, you should install the new Oracle Home on both primary and standby host. The two Oracle Homes should have the same patches applied, and I recommend that you always apply the latest Release Update.

Before Upgrade

You must use AutoUpgrade version 21.1.1 or newer. A newer version of AutoUpgrade can upgrade to older database releases as well, so don’t worry if the AutoUpgrade version doesn’t match the Oracle Database release that you are upgrading to.

AutoUpgrade can handle a Data Guard environment that is manually configured or via Data Guard Broker.

The procedure starts right before you start AutoUpgrade in DEPLOY mode (or alternatively in FIXUPS mode). Downtime has started and users are no logged connected to the database.

Stop Data Guard

On the standby database, generate commands to copy the Data Guard broker config files. Don’t execute them yet:

PROD2 SQL> select 'cp ' || value || ' /tmp' as cmd from v$parameter where name like 'dg_broker_config_file%';

Shut down the standby database. Disabling the database is strictly speaking not necessary, but a better-safe-than-sorry approach:

[oracle@bm2]$ $ORACLE_HOME/bin/srvctl stop database -d PROD2 -stopoption immediate
[oracle@bm2]$ $ORACLE_HOME/bin/srvctl disable database -d PROD2

If you are not managing the database with Grid Infrastructure (GI), just do a regular shutdown:

PROD2 SQL> shutdown immediate

Now, copy the broker config files into a temporary location. Use the cp commands that was executed earlier

[oracle@bm2]$ cp <broker_config_1> /tmp
[oracle@bm2]$ cp <broker_config_2> /tmp

Since redo transport has not been deferred yet in the primary database, it will complain about losing connection to the standby database. The alert log will contain an entry similar to this:

2020-12-03T06:30:12.751693+00:00
TT03 (PID:47477): Attempting LAD:2 network reconnect (3113)
TT03 (PID:47477): LAD:2 network reconnect abandoned
2020-12-03T06:30:12.752104+00:00
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD/trace/PROD_tt03_47477.trc:
ORA-03113: end-of-file on communication channel
TT03 (PID:47477): Error 3113 for LNO:3 to 'prod2'

It can be safely ignored, because it is after all a maintenance window and the database is about to be upgraded. Your monitoring system might detect this and start to complain.

Upgrade

Upgrade the database by starting AutoUpgrade in DEPLOY mode. AutoUpgrade will defer redo transport and stop Data Guard broker (if in use) automatically:

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

After the upgrade you should perform the necessary tests to validate the new database release. Only when you are convinced to go live on the new release, you should continue.

Remember that the standby database was left behind before we started touching anything, so if all other fails, simply restart the standby database, and connect your users to it.

After Upgrade

Restart Data Guard

Update the listener on the standby host. Be sure to update the Oracle Home information in the listener.ora entry. Note, that your listener.ora might be stored in a non-default location, so use lsnrctl status to get the location. Finally, reload the listener:

[grid@bm2]$ $GRID_HOME/bin/lsnrctl status
[grid@bm2]$ vi $GRID_HOME/network/admin/listener.ora
[grid@bm2]$ $GRID_HOME/bin/lsnrctl reload

For the next commands, I will be using the same prompt, and I will need the following environment variables:

[oracle@bm2]$ export OLD_HOME=/u01/app/oracle/product/18.0.0.0/dbhome_1
[oracle@bm2]$ export NEW_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@bm2]$ export ORACLE_HOME=$NEW_HOME
[oracle@bm2]$ export ORACLE_SID=PROD
[oracle@bm2]$ #Set ORACLE_UNQNAME to DB_UNIQUE_NAME
[oracle@bm2]$ export ORACLE_UNQNAME=PROD2 

Next, if the standby database is using TNS_ADMIN in the default location ($ORACLE_HOME/network/admin), then be sure to copy the relevant TNS aliases into the new tnsnames.ora. There should be TNS aliases to the primary and standby database. Or, if there are no other databases in the same Oracle Home, you can simply copy the files:

[oracle@bm2]$ #Back up files
[oracle@bm2]$ cp $NEW_HOME/network/admin/sqlnet.ora $NEW_HOME/network/admin/sqlnet.ora.backup
[oracle@bm2]$ cp $NEW_HOME/network/admin/tnsnames.ora $NEW_HOME/network/admin/tnsnames.ora.backup
[oracle@bm2]$ #Copy from old to new home
[oracle@bm2]$ cp $OLD_HOME/network/admin/sqlnet.ora $NEW_HOME/network/admin
[oracle@bm2]$ cp $OLD_HOME/network/admin/tnsnames.ora $NEW_HOME/network/admin

Now, you can edit /etc/oratab and update the information about the Oracle Home to match the new Oracle Home. In my example, the database is managed by GI, so I should not configure auto-start in /etc/oratab. If you are not managing your databases with GI, you probably want to configure the standby database to start automatically (see appendix):

[oracle@bm2]$ #Backup file
[oracle@bm2]$ cp /etc/oratab /tmp/oratab
[oracle@bm2]$ #Use sed to remove the line that starts with ORACLE_SID
[oracle@bm2]$ sed '/^'"$ORACLE_SID"':/d' /tmp/oratab > /etc/oratab
[oracle@bm2]$ #Add new entry
[oracle@bm2]$ echo "$ORACLE_SID:$ORACLE_HOME:N" >> /etc/oratab

Copy SPFile and password file to the new Oracle Home:

[oracle@bm2]$ cp $OLD_HOME/dbs/orapw$ORACLE_SID $ORACLE_HOME/dbs
[oracle@bm2]$ cp $OLD_HOME/dbs/spfile$ORACLE_SID.ora $ORACLE_HOME/dbs

Copy the broker config files into the new Oracle Home. If you store your broker config files outside of the Oracle Home this might not be necessary to you:

[oracle@bm2]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@bm2]$ export ORACLE_UNQNAME=PROD2 
[oracle@bm2]$ cp /tmp/dr1$ORACLE_UNQNAME.dat $ORACLE_HOME/dbs
[oracle@bm2]$ cp /tmp/dr2$ORACLE_UNQNAME.dat $ORACLE_HOME/dbs

Upgrade the database in GI, which updates the Oracle Home information, so GI will start the database in the correct Oracle Home. Next, re-enable and start the database:

[oracle@bm2]$ $ORACLE_HOME/bin/srvctl upgrade database -db $ORACLE_UNQNAME -oraclehome $ORACLE_HOME
[oracle@bm2]$ $ORACLE_HOME/bin/srvctl modify database -db $ORACLE_UNQNAME -startoption MOUNT -role PHYSICAL_STANDBY
[oracle@bm2]$ $ORACLE_HOME/bin/srvctl enable database -d $ORACLE_UNQNAME
[oracle@bm2]$ $ORACLE_HOME/bin/srvctl start database -d $ORACLE_UNQNAME

Or, if you are not using GI, simply start the database in the new Oracle Home:

PROD2 SQL> startup mount

Re-enable Data Guard

To re-enable the Data Guard config use DG CLI:

[oracle@bm1]$ $ORACLE_HOME/bin/dgmgrl sys@PROD1

And re-enable redo transport:

DGMGRL SYS@PROD1> edit database prod1 set state=transport-on;

Now, redo is shipping to the standby database, and it will apply it. When the redo that was generated during the upgrade is applied on the standby database, it is implicitly upgraded. You can monitor the progress of the apply by looking at the Apply Lag information. The Apply Lag will decrease until the standby database eventually catches up and they are fully synchronized:

DGMGRL SYS@PROD1> show database prod2;

The apply lag will continue to decrease when the redo stream is applied on the standby, and, thus, implicitly upgrades the database

Test

Use the broker to ensure everything is fine:

DGMGRL SYS@PROD1> show configuration
DGMGRL SYS@PROD1> show database prod1
DGMGRL SYS@PROD1> show database prod2

You should have SUCCESS listed for both databases Use Data Guard Broker to verify data guard setup after upgrade

Let’s validate the setup and try to make a switchover. The database will not allow a switchover if there are any problems in the Data Guard setup. It is a good way of checking things are fine:

DGMGRL SYS@PROD1> validate database prod1
DGMGRL SYS@PROD1> validate database prod2
DGMGRL SYS@PROD1> switchover to prod2

After upgrading a primary database (data guard) with autoupgrade you can use validate database to ensure everything is fine

If you don’t use Data Guard Broker, you use regular SQLs and SQLPlus to verify the Data Guard environment.

Conclusion

It is actually not that complicated to upgrade your database, even if it is part of a Data Guard setup. And with version 21.1.1 of AutoUpgrade is has become easier. A little extra legwork is needed to take care of the standby database. But the good thing is that your Data Guard setup is maintained throughout the process.

I made a video on YouTube that shows the procedure. And while you are there, I suggest that you subscribe to our channel.

Appendix

Config File

For your reference this is the config file, that I used. It contains only the required information. All other parameters have a default value:

upg1.sid=PROD
upg1.source_home=/u01/app/oracle/product/18.0.0.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.0.0.0/dbhome_1

Synchronize Standby Database

When you run un AutoUpgrade in ANALYZE mode and check the preupgrade report, you will find this information message:

[checkname]          SYNC_STANDBY_DB
[stage]              PRECHECKS
[fixup_available]    NO
[runfix]             N/A
[severity]           INFO
[action]             Synchronize your standby databases before database upgrade.
[broken rule]        The standby database is not currently synchronized with its associated primary database.
[rule]               To keep data in the source primary database synchronized with its associated standby databases, all standby databases must be synchronized before database upgrade.  See My Oracle Support Note 2064281.1 for details.

What does it say? Basically, it says that all redo generated on the primary database before the downtime window started, should be sent to and applied on the standby database. This way, your standby database is ready to replace your primary database at any time, if something goes really wrong. Strictly speaking it is not necessary to ensure that, but it is strongly recommended.

GI-managed Database in /etc/oratab

When the database is managed by GI, you don’t need to have it configured in /etc/oratab. Personally, I like to have it anyway, because then you have a clear overview of what databases are on the server, and you can use /etc/oratab to set your environment, like when you are using oraenv script.

But I know that die-hard GI-folks might roll their eyes when I say it, but I like it this way.

Further Reading

Upgrading in the Cloud – VM DB Systems – Automated Upgrade to 19c – The Details

Following a previous blog post here are all the details on automated upgrades in OCI, and (possibly) the answers to your questions.

Precheck

The precheck ensures the database is ready to upgrade. It uses DBUA which again uses preupgrade.jar to execute the checks. It is similar to running AutoUpgrade in analyze mode. The check is non-intrusive and can be executed while the database is in use.

Normally, when you use preupgrade.jar we always recommend you download the latest version from My Oracle Support. However, this is not possible when you use the tooling. The new, target Oracle Home is always deployed as part of the precheck process – and deleted again after the precheck. There is no way you can replace the preupgrade.jar package. You must use the version of preupgrade.jar that comes with the Oracle Home.

If there are no issues that prevent you from upgrading, you will see this message: The precheck completed and found no errors - the database is ready to upgrade

However, it could also be that there is an error in the database that must be fixed: If a critical issue prevents you from upgrading, a message will be displayed in the console

If you want to see the output from the precheck you must log on to the database host and find the file:

vi $ORACLE_BASE/cfgtoollogs/dbua/upgrade<timestamp>/$ORACLE_UNQNAME/upgrade.xml

Only the XML output is available, which might be a little hard to read. If you prefer you can also download AutoUpgrade to the server and run it in analyze mode. It can produce a much better output, and it works even if the target Oracle Home is not present. Create a simple config file:

upg1.sid=DB11204
upg1.source_home=/u01/app/oracle/product/11.2.0.4/dbhome_1
upg1.target_version=19

And now start AutoUpgrade in analyze mode:

java -jar autoupgrade.jar -config DB11204.cfg -mode analyze

You can use the preupgrade report to determine which issues prevents the upgrade from starting.

The database must be in ARCHIVELOG mode and the size of your Fast Recovery Area (FRA) must be at least 15G (parameter db_recovery_file_dest_size). As well, you must have 15G of free space on the mount point that hosts the FRA.

Upgrade

When you upgrade your database, all PDBs in the database are upgraded as well. There is no way to change it. If a PDB is closed when the upgrade starts, it is opened and upgraded. After the upgrade, the PDB is left opened and in READ WRITE state. But the state is not saved, so after a CDB restart, the PDB will start in whatever state that has been previously saved.

The first version of the tooling does not support standby database. If your database is a primary database, you must remove the standby database, upgrade, and then recreate the standby database. It is in the plan for future enhancements to get this streamlined.

Fallback

Enterprise Edition databases are protected by a guaranteed restore point (GRP) and Flashback Database. The tooling automatically creates the GRP before it starts to work on the database. If an error occurs during the upgrade, you can use the OCI console to initiate a roll back.

If the database upgrade fails on an Enterprise Edition database it is possible to roll back to a guaranteed restore point using Flashback Database

After successful upgrade the GRP is dropped again. The GRP only protects the database during the upgrade, so you can’t rely on the GRP as a fallback mechanism if you decide to fall back after the upgrade. Let’s say that your testing reveals a critical problem after the upgrade, then your only fallback mechanism is to restore a backup.

Since Flashback Database is an Enterprise Edition feature, this fallback mechanism is not available on Standard Edition databases.

In addition, it is strongly recommended that you perform a manual backup of the database before you start the upgrade. The console will also give you this warning, before you can start the upgrade.

Monitoring and Troubleshooting

When you have started the upgrade, you can’t monitor it from the console. You must log on to the host. When you do so, be aware that the timestamps shown in the OCI console are UTC, but the timestamps in the log files on the host is local timestamp (depending on your region).

Using dbcli

Log on as root and use the dbcli tool to monitor the progress. First, list jobs:

[root@host]$ dbcli list-jobs

Which should produce a list like this: Use dbcli list-jobs to list the jobs - including the upgrade - that run on the host Next, you can get additional information about the job using the ID:

[root@host]$ dbcli describe-job -i <id>

Which give you more details: dbcli describe-job can give you more detailed information about the upgrade

Using DBUA Log Files

But you can get even better information by looking in the log files from DBUA. Use the job id from the dbcli command to find the log file:

[oracle@host]$ export ORACLE_BASE=/u01/app/oracle
[oracle@host]$ export DBCLI_JOBID=f4b2597f-990f-4442-a774-153f3713fb7a
[oracle@host]$ tail -f -n 10 $ORACLE_BASE/cfgtoollogs/dbua/$DBCLI_JOBID/silent.log

And for really detailed information look in this directory:

[oracle@host]$ export ORACLE_BASE=/u01/app/oracle
[oracle@host]$ export DBCLI_JOBID=f4b2597f-990f-4442-a774-153f3713fb7a
[oracle@host]$ cd $ORACLE_BASE/cfgtoollogs/dbua/$DBCLI_JOBID/$ORACLE_UNQNAME

Using DCS Agent

The OCI control plane communicates with your DB System using an agent, and sometimes it can be useful to look in those logs:

[root@host]$ cd /opt/oracle/dcs/log
[root@host]$ vi dcs-agent.log

To find the log entries that are related to a specific upgrade search for the job ID:

[root@host]$ cat dcs-agent.log | grep "<job-id>" | more

Q&A

Which version and release update can I upgrade to?

The tooling only allows upgrades to Database 19c. If you need to upgrade to any other version, you must do it manually.

You can decide to upgrade to an Oracle provided image or a custom image: When you upgrade you can choose an Oracle provided image, or your own custom database software image However, for both type of images, the Release Update (or patch level) must be the latest or previous two Release Updates. Even if you have a custom database software image that is older, it can’t be used. You must upgrade to one of the recent Release Updates. If you select 19.0.0.0 you will not get the base release, but the latest Release Update. If you use the APIs this is a smart way of specifying that you always want the latest Release Update.

Where are my log files?

The output from the precheck is stored here:

  • $ORACLE_BASE/cfgtoollogs/dbua/upgrade<timestamp>

The output from the actual upgrade is stored here:

  • $ORACLE_BASE/cfgtoollogs/dbua/<job-id>
  • $ORACLE_BASE/cfgtoollogs/dbua/<job-id>/$ORACLE_UNQNAME

In addition, you can get details about the upgrade using dbcli:

[root@host]$ dbcli list-jobs
[root@host]$ dbcli describe-job -i <job-id>

Why is it taking so long to perform a precheck?

It consists of three phases:

  1. Deploy new Oracle Home to the VM DB System
  2. Precheck of the database
  3. Removing the new Oracle Home

The precheck (phase 2) is really fast. Just as fast as if you would run AutoUpgrade in analyze mode or using preupgrade.jar. The extra time is needed to deploy and remove the Oracle Home again. For each execution of the precheck the procedure repeats, and a new Oracle Home is deployed. It is never re-used.

Why is the upgrade slower than if I do it manually?

Typically, when you upgrade a database you have already – outside of the maintenance window – deployed a new Oracle Home. When you use the tooling, this happens inside the maintenance window. The tooling can’t deploy an Oracle Home prior to the upgrade. In addition, the upgrade is executed with DBUA using default options, which for instance means that the time zone file is upgraded as well. If you are sensitive to downtime and would like to complete the upgrade faster, you must perform the upgrade manually.

Will my 11.2.0.4 database get converted to a PDB?

No, the database is upgraded as-is and there is no PDB conversion. We are working on making it possible to perform the non-CDB conversion as well. If you must convert the non-CDB to a PDB, you must move the database to a new VM DB System that already have a CDB provision. In that case, I would recommend that you use the manual upgrade and plug-in as described in another blog post.

Can I perform an automated upgrade using dbcli?

No, although the command line help of dbcli suggests that such an option exist, it can’t be used.

Other Posts in This Series

New Version of AutoUpgrade

Our developers just published a new version of AutoUpgrade. Head over to My Oracle Support and download version 21.1.1.

The version says 21, but it can still upgrade your databases to lower release. So, you should use this new version to upgrade to Oracle Database 19c as well.

The supported target Oracle Database versions that are supported by the new version of AutoUpgrade

What’s New

A total of 48 enhancements and bug fixes made it into the new release. It has been 35 working days since the last release (and that includes Thanksgiving week) which means that the team has put in – on average – close to 1,5 changes into AutoUpgrade every, single working day. I find that quite impressive. And it really supports our message that you should always use the latest version of AutoUpgrade.

In My Oracle Support note 2485457.1 you can find a complete change log. Also, you can also visit the 21c New Feature Guide for additional information.

Unplug/plug Upgrade

The first things that I would like to highlight is unplug/plug upgrade. Instead of upgrading an entire CDB, you can now choose to unplug one or many PDBs, plug them into a higher release CDB, and upgrade the individual PDBs.

How an unplug/upgrade works of an Oracle Database PDB

How do you specify to upgrade only one PDB? Use target_cdb and pdbs?

upg1.sid=CDB1
upg1.pdbs=PDB3
upg1.source_home=/u01/app/oracle/product/12.2.0.1
upg1.target_home=/u01/app/oracle/product/19
upg1.target_cdb=CDB2

When doing unplug/plug upgrades you must be aware of the COMPATIBLE setting in the higher release CDB. If COMPATIBLE is higher, when the PDB plugs in, COMPATIBLE will silently and automatically be raised by the CDB. This means that you lose the capability of doing downgrades and flashback database.

You can read more about unplug/plug upgrades in the documentation

AutoUpgrade and Oracle Data Guard

In addition, AutoUpgrade now detects that you are upgrading a primary database in a Data Guard setup. In that case, it will automatically defer redo log transport to the standby databases and disable the Data Guard broker. After the upgrade, the broker is automatically restarted, but you need to manually reconfigure the standby databases, and eventually re-enable redo log transport.

You might ask: Why don’t AutoUpgrade handle it all for me? This is to preserve your fallback capabilities. Even after a successful upgrade, you still might want to revert back to the old version. Let’s say your testing finds a critical issue. You might still have a restore point on the primary database, but what if – for some reason – flashing back fails? Then you still have your standby database which was disabled right before the upgrade.

You can read more about AutoUpgrade and Data Guard in the documentation.

More

A few hints for the interested reader to explore. Have a look at the config file option catctl_options. You can use that to control the level of parallelism for a specific upgrade.

Also, if you don’t want AutoUpgrade to handle your network files (e.g. sqlnet.ora, tnsnames.ora) you disable it using manage_network_files.

You can read more about those options in the documentation.

Conclusion

Get the latest version of AutoUpgrade and benefit from the many new features. Keep an eye out for additional blog posts that will dig deeper into the new features.

Upgrading in the Cloud – VM DB Systems – Automated Upgrade to 19c

It is really easy to upgrade a database to 19c using the console:

Upgrade your Oracle Database in OCI with just a few clicks

In addition, the documentation is quite good and elaborate. I won’t make a copy/paste of the documentation, and also because things really move fast in the cloud, you should visit the documentation for up-to-date information.

Before Upgrade

First, your VM must be running Oracle Linux 7 and Grid Infrastructure must be version 19. If your system doesn’t meet these requirements either:

  • Move the database to a new DB System that meets these requirements via cloning or backup/restore, or
  • Use manual upgrade which is described in other post in the series.

To check the OS version:

[oracle@host]$ cat /etc/os-release

To check GI version:

[grid@host]$ crsctl query crs activeversion

Precheck

Start by running a precheck. I recommend to always upgrade to 19.0.0.0 as it will automatically give you an Oracle Home including the latest release update. Always choose 19.0.0.0 as your Oracle Home as it includes the latest release update

The console will notify you if something critical comes up during the precheck. If a critical issue prevents you from upgrading, a message will be displayed in the console

If you want to see the output from the precheck you must log on to the database host and find the file:

vi $ORACLE_BASE/cfgtoollogs/dbua/upgrade<timestamp>/$ORACLE_UNQNAME/upgrade.xml

Only the XML output is available, which might be a little hard to read. If you prefer you can also download AutoUpgrade to the server, and run it in analyze mode. It can produce a much better output, and it works even if the target Oracle Home is not present. Just use the target_version config file parameter.

Backup

Automatic backup must be disabled during upgrade.

In addition, it is recommended to create a manual backup before you start the upgrade. Enterprise Edition databases will be protected by a Guaranteed Restore Point which is the primary fallback method, however, it is recommended to also have a manual backup. For Standard Edition databases a manual backup is the only fallback method available.

Upgrade

Under the hood the upgrade consists of:

  1. Deploy new Oracle Home You should use the same Oracle Home version that was used during the previous precheck.
  2. Create guaranteed restore point (Enterprise Edition only) The name of the restore point is prefixed BEFORE#DB#UPGRADE# and the restore point is automatically removed following a successful upgrade.
  3. Upgrade using DBUA (incl. an additional precheck) The upgrade is executed using the default settings which does include an upgrade of the timezone file. This is not configurable. If you are upgrading a CDB all PDBs will be upgraded as well. It is not possible to exclude some of the PDBs. Any PDB that is closed when the upgrade starts, will open and upgrade.

While you are upgrading the database it is unavailable. Upgrade requires downtime. How long? As always in IT – it depends! These things matter a lot:

  • Number of installed components (select * from dba_registry)
  • Size & complexity of dictionary (select count(*) from obj$)
  • Some feature/version combinations

These things matter a little:

  • CPU
  • Disk speed
  • RAM

These things doesn’t matter at all:

  • Amount of user data

But an estimate is anywhere between 20 minutes to an hour.

After Upgrade

Parameter COMPATIBLE

The COMPATIBLE parameter is not changed by the tooling. You are in full control of this parameter, and should decide when you want to change it to the default value in 19c. Check the documentation to read more about this parameter.

I would recommend that you do it one or two weeks after the upgrade. This preserves the option of performing a database downgrade. But it requires a database restart, so you must be able to tolerate the additional maintenance window. If you can’t tolerate additional downtime, or you don’t care about the downgrade option, you should update COMPATIBLE right away.

In any case this is how you do it:

SQL> alter system set compatible='19.0.0' scope=spfile;
SQL> shutdown immediate
SQL> startup

And always set COMPATIBLE to the default value of the release. Mike Dietrich explain it really good in a blog post.

Update Profile

.bashrc must be updated to reflect the new Oracle Home. There are a few environment variables that should be updated. Either do it manually or you can use sed:

export OLD_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
export NEW_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_2
cp ~/.bashrc ~/.bashrc_backup_`(date -u +%Y%m%d-%H%M%S)`
sed -i "s,$OLD_HOME,$NEW_HOME,g" ~/.bashrc
source ~/.bashrc

Backup

Ensure that you have proper backups:

  • Re-enable automatic backup, if you want to use it
  • Personally, I would add a manual backup taken right after upgrade

Remove Old Oracle Home

You can remove the old Oracle Home. It is no longer supported to use it. You should use dbcli. First, get a list of Oracle Homes:

[root@host]$ dbcli list-dbhomes

Output from dbcli command that list all Oracle Homes - list-dbhomes

Use the ID to delete the Oracle Home:

[root@host]$ dbcli delete-dbhome -i <id>

Output from dbcli command that deletes an Oracle Home - delete-dbhome If you really want you can monitor the progress of the deletion:

[root@host]$ dbcli describe-job -i <id>

Conclusion

It is simple to upgrade a database in OCI to 19c. The upgrade itself is just a click on a button, but for real databases there is a little more to it. But it is still very easy, and most tasks can be executed from the console. If you want to be in better control, or want to minimize the downtime required, you should look into doing the upgrade manually.

I made a video that I posted to our YouTube channel which demos the process.

Other Posts in This Series