Category: Uncategorized

Release and Patching Strategies for Oracle Database 23c

A few weeks ago my team hosted the sixteenth episode of our Virtual Classroom Seminars. The webinar is called Release and Patching Strategies for Oracle Database 23c.

If you couldn’t participate you can now watch the recording and flip through the slides.

Recording

The recording of the webinar is posted on our YouTube channel:

The video is divided into chapters and from the video description you can jump right into the topic of your interest.

My favorite moments:

If you have a question, leave a comment on YouTube and we will get back to you.

Slides

You can download the slides here.

We received a lot of questions during the webinar. So many of them were really good and relevant. I decided to make a new version of the slide deck which answers many of the questions you asked.

I would like to thank all that asked a question. It provides us with valuable feedback and enables us to make even better material for you.

What’s Next?

Mike Dietrich and I will host episode 17 of our Virtual Classroom on Thursday June 22, 16:00 CEST:

From SR to Patch – Insights into the Oracle Database Development Process

Have you ever wondered why this bug fix hasn’t been included in the next Release Update? Or why somebody from Oracle Support asked you to upgrade to Oracle Database 23c – even Oracle 23c is not available yet for your environment? We’ll explain this – and much more. From SR to Patch describes the whole process from you, opening a service request for a defect to the final delivery of a fix. This is your rare chance to get insights into the Oracle Database development process from insiders. And even if you are a long-time Oracle expert you will still learn something new you are not aware about yet.

Sign up and put a mark in your calendar.

And After That?

The autumn will be quite busy with Oracle CloudWorld and all the preparations. But I hope we will be able to make another webinar towards the end of the year.

If you have any ideas and have a request for a topics that we should cover, please leave a comment and we will take it into consideration.

Did You Sign Up For Oracle CloudWorld Yet?

Oracle CloudWorld takes place in Las Vegas, September 18-21. Before the summer holiday season kicks in, you should get a ticket and mark your calendar for the coolest Oracle event of the year.

This year will be even better!

Our Sessions and Labs

I am very excited about the plans we have for Oracle CloudWorld. My team (like most other teams at Oracle) works really hard to prepare new content for you. We will have brand-new sessions and hands-on labs ready.

Currently, this is our confirmed session:

And our hands-on labs:

And we will repeat our very popular no-slide zone on patching GI and database:

Is that it? Of course not; we have more in the pipeline. It’s just waiting for a final confirmation. Keep an eye out on the session catalog; we update it constantly.

Master Classes

In addition, we have something new and special for you this year – a 4-hour top-notch learning experience.

  • Oracle Database Upgrade and Performance Tuning Master Class

You’ll leave this master class knowing how to use all the tools in your toolbox to ensure great database performance after your upgrade.

We have taken as much knowledge as we can and compiled it into an intense 4-hour learning experience. It’s our course, and we will be there to train you. This is a unique opportunity.

Check out the details and other training classes at the Pre-Event Training page.

Free Digital Training and Certification

Registering for Oracle CloudWorld gives you access to free digital training on Oracle Cloud Infrastructure and Cloud Apps. After that, you can take free certification exams as well. All part of the deal.

You can start your learning experience now – and then complete it with sessions at Oracle CloudWorld. This is a great opportunity.

Want More?

Then there’s also:

  • CloudWorld Party – who’s gonna play this year?
  • Demogrounds – see all the cool stuff in action
  • Exhibition area – wander around and get inspired
  • Networking – talk to your peers and grow your career
  • LiveLabs – try and learn
  • Events – cool stuff waiting to happen
  • Las Vegas – it’s a unique and crazy place, worth a visit (although I do miss San Francisco)

Book Your Ticket

If you need to convince your manager, here’s some ammo for that talk.

The sooner you book your ticket, the cheaper it is.

Fun Facts 2022

  • 1.304 customer sessions with 855 different customers
  • 168 partners sponsoring and exhibiting in the CloudWorld Hub
  • 7.233 hours of live-stream and on-demand video
  • 25.000+ liters of coffee served
  • 2.250 cake pops consumed at the CloudWorld party (I won’t say how many I ate)
  • 6 tons of left-over food and materials donated to local organizations
  • 100% of energy sourced from renewables such as solar and wind
  • 0 plastic water bottles distributed to CloudWorld attendees

See you in Las Vegas

Is Oracle Java Virtual Machine (OJVM) Used In My Database?

In Oracle Database, you can store and execute Java code directly in the database using Oracle Java Virtual Machine (OJVM). You can take your Java code, put it into the database and execute it. Now your code sits right next to the data, and you can get some amazing benefits.

But it comes at a price:

It’s really great if you are using OJVM; it has awesome functionality. But if you don’t, consider removing it. When I talk to customers, the big question is often:

How do I know if it is in use in my database?

Setting Things Straight

First, this blog post is about using OJVM: Java code in the database. Often, people mistakenly think they use OJVM because they have a Java application connecting to the database. They don’t. Having a Java application connecting to the database and using OJVM are two completely different things.

OJVM is often also referred to as:

  • JAVAVM
  • JServer JAVA Virtual Machine

Is OJVM Installed?

You can check if the OJVM component is installed in the database:

SQL> select con_id, comp_id, comp_name, version, status
     from cdb_registry
     where comp_id='JAVAVM';

If the query returns no rows, then OJVM is not installed.

Has Someone Added Java Code?

You can check if someone has added Java code to the database:

SQL> select con_id, owner, oracle_maintained, status, count(*) 
     from cdb_objects
     where object_type like '%JAVA%' 
     group by con_id, owner, oracle_maintained, status;

The column ORACLE_MAINTAINED indicates whether a regular user added it. If a user has added Java code, you can use the columns CREATED and LAST_DDL_TIME to find out when it happened. This might help you.

Is Someone Using OJVM Right Now ?

You can query x$kglob to determine the use of OJVM since the last startup. Refer to RAC Rolling Install Process for the "Oracle JavaVM Component Database PSU/RU" (OJVM PSU/RU) Patches (Doc ID 2217053.1) for details.

The MOS note also shows how to identify which sessions that use OJVM.

OJVM Dependencies

Be advised the following components in Oracle Database depend on OJVM. If you are using one of them, you can’t remove OJVM. The following components use OJVM:

  • Spatial Data Option (SDO)
  • Oracle XDK (XDK)
  • Oracle Multimedia (ORDIM)

Conclusion

Oracle Database is a converged database. You have so many great features directly available in the database. You can do many cool things with them – including OJVM. But if you are not using OJVM or any dependent components in Oracle Database, you can remove OJVM. It will save you time during patch installation and upgrades.

I used an example from oracle-base.com. Visit the article for all the details.

Appendix

Further Reading

Test Data

conn / as sysdba

--Query the current status
select comp_id, comp_name, version, status
from dba_registry
where comp_id='JAVAVM';

select con_id, owner, oracle_maintained, status, count(*) 
from cdb_objects
where object_type like '%JAVA%' 
group by con_id, owner, oracle_maintained, status;

--Shows status in current container and on current instance
--since last startup (MOS Doc ID 2217053.1)
select count(*) from x$kglob where KGLOBTYP = 29 OR KGLOBTYP = 56;

--Create user and small java code 
create tablespace appts;
create user appuser identified by appuser;
alter user appuser default tablespace appts;
grant dba to appuser;
conn appuser/appuser
--Thanks to oracle-base.com for a good example
--For further details:
--https://oracle-base.com/articles/8i/jserver-java-in-the-database
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Mathematics" AS
import java.lang.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class Mathematics
{
  
  public static int addNumbers (int Number1, int Number2)
  {
    try
    {
      int iReturn = -1;
      
      // Connect to the database
      Connection conn = null;
      OracleDriver ora = new OracleDriver(); 
      conn = ora.defaultConnection(); 
      
      // Check record exists, and create it if it doesn't
      Statement statement = conn.createStatement();
      ResultSet resultSet = statement.executeQuery("SELECT " + Number1 + " + " + Number2 + " FROM dual");
      if (resultSet.next())
      {
        iReturn = resultSet.getInt(1);
      }
      resultSet.close();
      statement.close();
      conn.close();

      return iReturn;
    }
    catch (Exception e)
    {
      return -1;
    }
  } 

};
/
show errors java source "Mathematics"

--Query the current status
conn / as sysdba
select con_id, owner, oracle_maintained, status, count(*) 
from cdb_objects
where object_type like '%JAVA%' 
group by con_id, owner, oracle_maintained, status;

select comp_id, comp_name, version, status
from dba_registry
where comp_id='JAVAVM';

select count(*) from x$kglob where KGLOBTYP = 29 OR KGLOBTYP = 56;

--Expose java code
conn appuser/appuser
CREATE OR REPLACE FUNCTION AddNumbers (p_number1  IN  NUMBER, p_number2  IN  NUMBER) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'Mathematics.addNumbers (int, int) return int';
/

--Query the current status
conn / as sysdba
select con_id, owner, oracle_maintained, status, count(*) 
from cdb_objects
where object_type like '%JAVA%' 
group by con_id, owner, oracle_maintained, status;

select comp_id, comp_name, version, status
from dba_registry
where comp_id='JAVAVM';

select count(*) from x$kglob where KGLOBTYP = 29 OR KGLOBTYP = 56;

--Use Java code
conn appuser/appuser
SELECT addNumbers(1,2)
FROM   dual;

--Check which sessions have actively used Java since last startup
--Refer to MOS Doc ID 2217053.1 for the query

How to Clone Oracle Home Without Using Clone.pl

Cloning Oracle Homes is a convenient way of getting a new Oracle Home. It’s particularly helpful when you need to patch out-of-place.

A popular method for cloning Oracle Homes is to use clone.pl. However, in Oracle Database 18c, it is deprecated.

[INFO] [INS-32183] Use of clone.pl is deprecated in this release. Clone operation is equivalent to performing a Software Only installation from the image. 
You must use runInstaller script available to perform the Software Only install. For more details on image based installation, refer to help documentation.

This Is How You Should Clone Oracle Home

You should use runInstaller to create golden images instead of clone.pl. Golden image is just another word for the zip file containing the Oracle Home.

How to Create a Golden Image

  1. First, only create a golden image from a freshly installed Oracle Home. Never use an Oracle Home that is already in use. As soon as you start to use an Oracle Home you taint it with various files and you don’t want to carry those files around in your golden image. The golden image must be completely clean.

  2. Then, you create a directory where you can store the golden image:

    export GOLDIMAGEDIR=/u01/app/oracle/goldimages
mkdir -p $GOLDIMAGEDIR

  3. Finally, you create the golden image:

    $ORACLE_HOME/runInstaller -createGoldImage \
   -destinationLocation $GOLDIMAGEDIR \
   -silent
    • If you need to exclude files, you can use -exclFiles. It accepts a wilcard, so for example you can specify -exclFiles network/admin* to exclude all files and subdirectories in a directory.

  4. The installer creates the golden image as a zip file in the specified directory. The name of the zip file is unique and printed on the console.

Check the documentation for further details.

How to Deploy from a Golden Image

  1. First, you create a directory for the new Oracle Home and unzip the golden image:
    export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_2
mkdir -p $ORACLE_HOME
cd $ORACLE_HOME
unzip -q /u01/app/oracle/goldimages/my_golden_image.zip
  2. Next, you need to install the Oracle Home. You can do it interactively:
    cd $ORACLE_HOME
./runInstaller
    Or, you can do it in silent mode:
    cd $ORACLE_HOME
./runInstaller -ignorePrereq -waitforcompletion -silent \
...

That’s it!

If you need to read further on silent installations, check out oracle-base.com.

The Oracle Database 23ai documentation has an good example of using use the new procedure.

But How about OraInst.loc?

One of the differences between clone.pl and runInstaller is that the latter does not include the file $ORACLE_HOME/oraInst.loc.

This is intentional because the file is not needed for golden image deployment. runInstaller recreates the file when you install the golden image.

One of the things listed in oraInst.loc is the location of the Oracle inventory. Either runInstaller finds the value itself, or you can specify it on the command line using INVENTORY_LOCATION=<path-to-inventory>.

You can read more about oraInst.loc in the documentation or MOS note Significance of oraInst.loc When Installing Oracle Products and Applying Patches (Doc ID 418537.1).

Naming Your Golden Image

If you want your zip file (the golden image) to have a specific name, you have two options:

  1. Rename the zip file after executing runInstaller -createGoldImage.
  2. Use the secret parameter -name, which allows you to specify a name for the zip file. To name the zip file my_golden_image.zip:
    $ORACLE_HOME/runInstaller -createGoldImage \
   ... \
   -name my_golden_image.zip

Why Is Clone.pl Deprecated?

Previously, many tools existed to do the same – clone an Oracle Home. Now, we have consolidated our resources into one tool.

From now on, there is one method for cloning Oracle Home. That is easier for everyone.

In addition, runInstaller has some extra features that clone.pl doesn’t. For instance:

  • Better error reporting
  • Precheck run
  • Multimode awareness
  • Ability to apply patches during installation

When Will It Be Desupported?

I don’t know. Keep an eye out on the Upgrade Guide, which contains information about desupported features.

However, I can see in the Oracle Database 23c documentation that clone.pl is still listed. But that’s subject to change until Oracle Database 23c is released.

Pro Tips

  • Remember, you can install patches to your golden image after it has been unzipped – as part of the installation.

  • If you clone Oracle Homes because you are doing out-of-place patching, you are on the right track. I strongly recommend always using out-of-place patching. Also, when you patch out-of-place, remember to move all the database configuration files.

  • If you clone Oracle Homes, you keep adding stuff to the same Oracle Home. Over time the Oracle Home will increase in size. The more patches you install over time, the more the Oracle Home increases in size. OPatch has functionality to clean up inactive patches from an Oracle Home. Consider running it from time to time using opatch util deleteinactivepatches. Mike Dietrich has a really good blog post about it. I also describe it in our of our previous webinars:

Appendix

Thanks to Anil Nair for pointing me in the right direction.

Further Reading

Data Pump and a Norwegian Sneak Peek

Earlier this month, the team and I presented our webinar Data Pump – Best Practices and Real World Scenarios. Over the years, we have accumulated information from many different customer projects, and we wanted to compile all that information into a webinar. You can watch the result on YouTube or flip through the slides.

On YouTube, the recording is divided into pieces, so you can easily dive right into the subject that has your particular interest.

My two favorite chapters:

Next month, in May, we are hosting another webinar about Release and Patching Strategies for Oracle Database 23c. Sign up now and secure your seat. It is, by the way, the 16th webinar in our series. If you want, you can watch the previous ones on demand.

The Norwegian Sneak Peak

Next week, the Norwegian Oracle User Group is having its annual spring conference. I am attending, and I am really excited. The agenda and line-up are really amazing. It’s not too late to sign up.

I am presenting at the conference as well. The slides are ready and uploaded to my blog. If you want, you can take a sneak peek:

See you in Oslo?

Oracle Data Pump and BFILEs

If you need to move data using Oracle Data Pump and have BFILEs in your database, what do you need to be aware of?

What Is a BFILE?

From the documentation:

BFILEs are data objects stored in operating system files, outside the database tablespaces. Data stored in a table column of type BFILE is physically located in an operating system file, not in the database. The BFILE column stores a reference to the operating system file. BFILEs are read-only data types. The database allows read-only byte stream access to data stored in BFILEs. You cannot write to or update a BFILE from within your application.

They are sometimes referred to as external LOBs.

You can store a BFILE locator in the database and use the locator to access the external data:

To associate an operating system file to a BFILE, first create a DIRECTORY object that is an alias for the full path name to the operating system file. Then, you can initialize an instance of BFILE type, using the BFILENAME function in SQL or PL/SQL …

In short, it is stuff stored outside the database that you can access from inside the database. Clearly, this requires special attention when you want to move your data.

How Do I Move It?

There are three things to consider:

  1. The file outside the database – in the operating system.
  2. The directory object.
  3. The BFILE locator stored in the table.

Table and Schema Mode Export

  1. You must copy the file in the operating system. Since a BFILE is read-only, you can copy the file before you perform the actual export.
  2. You must create the directory object. Directory objects are system-owned objects and not part of a table or schema mode export.
  3. Data Pump exports a BFILE locator together with the table. It exports the BFILE locator just like any other column. On import, Data Pump inserts the BFILE locator but performs no sanity checking. The database will not throw an error if the file is missing in the OS or if the directory is missing or erroneous.

Full Export

  1. Like table and schema mode, you must copy the file.
  2. Directory objects are part of a full export. On import, Data Pump creates a directory object with the same definition. If you place the external files in a different location in the target system, you must update the directory object.
  3. Like table and schema mode. Data Pump exports the BFILE locator as part of the table.

Do I Have BFILEs in My Database?

You can query the data dictionary and check if there are any BFILEs:

SQL> select owner, table_name 
     from dba_tab_cols 
     where data_type='BFILE';

Further Reading

Pro Tips

Here’s a collection of good tips and tricks I found while writing this series of blog posts.

Pro Tip #1: How Do You Determine Grid Infrastructure Patch Level?

To determine the GI patch level:

[grid@copenhagen1]$ $ORACLE_HOME/OPatch/opatch lspatches | grep "OCW"

34444834;OCW RELEASE UPDATE 19.17.0.0.0 (34444834)

The inventory registers the GI Release Updates as OCW RELEASE UPDATE. In this example, GI is running on 19.17.0.

Sometimes critical one-off patches are delivered as merge patches with the GI Release Update. It can mess up the patch description. This example is from a Base Database Service in OCI:

[grid@copenhagen1]$ $ORACLE_HOME/OPatch/opatch lspatches | grep "OCW"

34122773;OCW Interim patch for 34122773

The patch description no longer contains the name of the Release Update. In this case, you can trawl through MOS to find the individual patches in the merge patch to identify which Release Update it contains. Or, you can often look at the ACFS patch instead:

[grid@copenhagen1]$ $ORACLE_HOME/OPatch/opatch lspatches | grep "ACFS"

34139601;ACFS RELEASE UPDATE 19.16.0.0.0 (34139601)

Pro Tip #2: Where Can You Find the Log Files?

Logging happens in different places depending on which method you use. Here are a few locations to browse when there are problems:

  • $GRID_HOME/install
  • $GRID_HOME/cfgtoollogs
  • $GRID_BASE/crsdata/<node>/crsconfig
  • /u01/app/oraInventory/logs

Pro Tip #3: Where Can You Find Information On Troubleshooting?

A few good MOS notes:

OPatchAuto enables you to control the logging granularity. If you run into problems, increase the logging level to get more information:

$ORACLE_HOME/OPatch/opatchauto ... -logLevel FINEST

In addition, OPatchAuto can resume a broken session. Fix the issue and restart OPatchAuto. It will pick up from where it left off:

$ORACLE_HOME/OPatch/opatchauto resume

Pro Tip #4: How Can I Install Patches Manually?

If you don’t want to use the automation tools (like OPatchAuto), you can install the patches manually using OPatch.

The details are in Supplemental Readme – Grid Infrastructure Release Update 12.2.0.1.x / 18c /19c (Doc ID 2246888.1).

The GI patch bundle contains several sub patches that must be installed in the correct order using opatch apply.

Pro Tip #5: How Do You Roll Back A Patch?

In-place OPatchAuto

You can find patch rollback (or deinstallation) instructions in the patch readme file. In short, you execute the following command:

$ORACLE_HOME/OPatch/opatchauto \
   rollback <unzipped_patch_location>/<patch_dir>

Note you might need to reboot the server.

Out-of-place OPatchAuto

You find rollback instructions in MOS note Grid Infrastructure Out of Place ( OOP ) Patching using opatchauto (Doc ID 2419319.1). In short, you execute the following command:

$NEW_ORACLE_HOME/OPatch/opatchauto \
   rollback \
   -switch-clone

Out-of-place SwitchGridHome

Check out this blog post.

Zero Downtime Oracle Grid Infrastructure Patching

You find rollback instructions in MOS note Step by Step Zero Downtime Oracle Grid Infrastructure Patching in Silent Mode (Doc ID 2865083.1). You need to execute a few commands. Check the MOS note for details.

Pro Tip #6: The FAQ

On My Oracle Support there is an extensive FAQ. Bookmark it: RAC: Frequently Asked Questions (RAC FAQ) (Doc ID 220970.1)

Appendix

Other Blog Posts in This Series

New Webinars Coming Up

I am really excited to announce two new webinars:

  • Data Pump Best Practices and Real World Scenarios April 5, 2023, 16:00 CET
  • Release and Patching Strategies for Oracle Database 23c May 10, 2023, 16:00 CET

Oracle Database 19c Upgrade Virtual Classroom

You can sign up here.

The entire team, Roy, Mike, Bill, Rodrigo, and myself, are working hard to polish all the details.

Data Pump Best Practices and Real World Scenarios

In short: It’s all the stuff we couldn’t fit into our last Data Pump webinar.

Here’s the full abstract: > We promised to share more information in our last Data Pump Deep Dive With Development seminar. And here we are back again. Data Pump best practices is the topic we would like to emphasize on today. This will include some common tips and tricks but target especially parallel optimizations and transformation. It is quite common that you restructure objects and types when you use Data Pump for a migration. So we will give a detailed overview on the most common scenarios. This will guide us directly to real world scenarios where we’ll demonstrate several of those best practices used by customers.

Release and Patching Strategies for Oracle Database 23c

This is a revamped version of our very first webinar, Release and Patching Strategy. It’s updated to reflect the latest changes, and we have included even more details and demos.

Last time, the interest for this webinar was huge, and we ended up maxing out of Zoom capacity. A lot of you couldn’t get it. So, you better be ready on time, or you might miss your seat.

The full abstract: > This is a session every Oracle customer needs to attend to. Oracle Database 23c, the next long-term support release will be available sometime this year. Now it is time to refresh your knowledge about the best and most efficient strategies for your future release planning. Are there changes to the release numbering? Are there important changes regarding database patching? We will give you a complete overview on the available patch bundles and recent and future changes. We’ll discuss and showcase why a proper patching strategy is of vital importance – and how you can automate and optimize certain essential tasks.

But I Can’t Make It

Don’t worry. As usual, we will publish the recording on our YouTube channel and share the slides with you. Keep an eye out on my Webinars page.

But it’s better to watch it live. The entire team will be there, and we will answer all your questions. I promise you; we won’t leave until all questions have been answered.

All Tech, No Marketing

Remember, our mantra is: All tech, no marketing.

These webinars are technical. This is the place for you if you want all the gory details and cool demos.

I hope to see you there

Oracle CloudWorld 2022 On-Demand

Get the very last out of Oracle CloudWorld 2022 by watching some of the recordings. You can find some really great sessions available on demand. All available for free on YouTube.

Upgrade to Oracle Database 19c

My session shows how to upgrade to Oracle Database 19c using AutoUpgrade. A short demo to get you started, and then I talk about more advanced scenarios, like upgrading with Data Guard and RAC.

AutoUpgrade 2.0: internals and new features

In this session, I talk about some of the newest features in AutoUpgrade. A lot of customers have asked us to enhance AutoUpgrade with patching capabilities. We heard you – and that is one of the new features I present in this session.

What Else

The playlist has close to 60 videos, so there is much to dig into. Some of my personal favorites:

And then there are so many more exciting sessions to watch.

Oracle CloudWorld 2023

The next CloudWorld takes place in Las Vegas on September 18-21.

Registration opens on April 11 with attractive early bird prices.

I hope to see you in Las Vegas.

Installing Oracle Database 19c and All the Things to Put on Top

When you prepare for patching or upgrading Oracle Database 19c, you must also prepare an Oracle Home. Installing the Oracle Home is easy, but there is more to it.

Out-of-place Installation

I always use out-of-place installation. I install a new, fresh Oracle Home. I will move the databases into that Oracle Home as I upgrade or patch.

The alternative, in-place installation, leads to more downtime, is more error-prone, and makes fallback more complicated. In addition, in-place installation will gradually slow down patching; as Mike Dietrich describes in Binary patching is slow because of the inventory.

Download and Prepare Oracle Home

First, I download the base release from Oracle Software Delivery Cloud, aka e-delivery.

Find REL: Oracle Database 19.3.0.0.0 – Long Term Release, the right platform, and download.

Extract the zip file into a new Oracle Home location:

export NEW_ORACLE_HOME=<path>
mkdir -p $NEW_ORACLE_HOME
cd $NEW_ORACLE_HOME
unzip -oq /tmp/LINUX.X64_193000_db_home.zip
rm /tmp/LINUX.X64_193000_db_home.zip

Don’t run the installer yet.

Clone Existing Oracle Home

I could clone an existing Oracle Home and then just apply the new patches. But it will make me susceptible to the same issue described above about in-place patching. Plus, if you clone an Oracle Home with one-offs then you might need to roll them off before you can apply the next Release Update.

Update OPatch

OPatch is needed later on to apply patches to the new Oracle Home. Get the latest version and install it into the new Oracle Home:

rm -rf $NEW_ORACLE_HOME/OPatch
cd $NEW_ORACLE_HOME
unzip -oq /tmp/<opatch_zip_file>
rm /tmp/<opatch_zip_file>

Patches

Now, I will determine which patches to apply to the Oracle Home.

  • Start by getting the latest Release Update. I really mean the latest. I have helped too many customers with issues, only to find out the issue is already solved in a later Release Update. If your database has JAVAVM installed, then get the combo patch.
  • Review the list of important one-off patches for the specific Release Update. The list contains important fixes that haven’t made into a Release Update yet. I don’t need to get all of them, but based on my knowledge of my database, I can cherrypick those that could be relevant.
  • If I am using Data Pump, I get the Data Pump bundle patch. Data Pump fixes rarely make it into Release Updates, because they are not RAC-Rolling Installable which is a clear requirement for inclusion in Release Update.
  • If I am using GoldenGate, I get the GoldenGate bundle patch.
  • If my database uses OJVM (see appendix), I get the OJVM patch that matches the Release Update I am using. I can also get the OJVM patch as a combo patch together with the Release Update.

Unzip

Now that I have downloaded a number of zip files, I go ahead and unzip the files into separate directories. In the below example, I am using 19.16 Release Update and Data Pump bundle patch:

#Release Update 19.16.0
mkdir -p $NEW_ORACLE_HOME/patch/p34133642
cd $NEW_ORACLE_HOME/patch/p34133642
unzip -oq /tmp/p34133642_190000_Linux-x86-64.zip
rm /tmp/p34133642_190000_Linux-x86-64.zip

#Data Pump bundle patch
mkdir -p $NEW_ORACLE_HOME/patch/p34294932
cd $NEW_ORACLE_HOME/patch/p34294932
unzip -oq /tmp/p34294932_1916000DBRU_Generic
rm /tmp/p34294932_1916000DBRU_Generic

Install

Now, I can install the Oracle Home and apply all the patches in one operation. Mike has a really good description of the functionality and a demo.

I do a silent installation using a response file. Notice how I am applying the patches during the installation using -applyRU and -applyOneOffs:

export ORACLE_BASE=<path_to_oracle_base>
export ORACLE_HOME=<path_to_oracle_home>
#Path to inventory is most likely /u01/app/oraInventory
export ORA_INVENTORY=<path_to_inventory>
cd $ORACLE_HOME
./runInstaller -ignorePrereqFailure -waitforcompletion -silent \
   -responseFile $ORACLE_HOME/install/response/db_install.rsp \
   -applyRU patch/p34133642/34133642 \
   -applyOneOffs patch/p34294932/34294932 \
   oracle.install.option=INSTALL_DB_SWONLY \
   UNIX_GROUP_NAME=oinstall \
   INVENTORY_LOCATION=$ORA_INVENTORY \
   SELECTED_LANGUAGES=en,en_GB \
   ORACLE_HOME=$ORACLE_HOME \
   ORACLE_BASE=$ORACLE_BASE \
   oracle.install.db.InstallEdition=EE \
   oracle.install.db.OSDBA_GROUP=dba \
   oracle.install.db.OSBACKUPDBA_GROUP=dba \
   oracle.install.db.OSDGDBA_GROUP=dba \
   oracle.install.db.OSKMDBA_GROUP=dba \
   oracle.install.db.OSRACDBA_GROUP=dba \
   oracle.install.db.isRACOneInstall=false \
   oracle.install.db.rac.serverpoolCardinality=0 \
   oracle.install.db.config.starterdb.type=GENERAL_PURPOSE \
   oracle.install.db.ConfigureAsContainerDB=false \
   SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
   DECLINE_SECURITY_UPDATES=true

You can read more about silent installation on oracle-base.com. That’s where I got inspired from. The reponse file db_install.rsp is the default one that comes with the Oracle Home. I don’t change anything in it.

Finally, I execute root.sh as root:

$ORACLE_HOME/root.sh

AutoUpgrade

Download the latest version of AutoUpgrade, and put it into $ORACLE_HOME/rdbms/admin.

Et Voilà

That’s it. I can now use the Oracle Home to upgrade or patch my Oracle Database 19c.

When you move your Oracle Database to the new Oracle Home, be sure to move all the necessary configuration files as well.

Appendix

Patches

As if the list of patches to apply wasn’t long enough. There are even more MOS notes!

Good news is that you don’t have to go through them, as long as you stay on the latest Release Update. If you check the notes, you will see that almost all bugs are already included in a Release Update. That’s a pretty strong argument for always using the latest Release Update.

  • Things to Consider to Avoid Prominent Wrong Result Problems on 19C Proactively (Doc ID 2606585.1)
  • Things to Consider to Avoid Database Performance Problems on 19c (Doc ID 2773012.1)
  • Things to Consider to Avoid SQL Performance Problems on 19c (Doc ID 2773715.1)
  • Things to Consider to Avoid SQL Plan Management (SPM) Related Problems on 19c (Doc ID 2774029.1)

Grid Infrastructure

If Grid Infrastructure manages my database, I must remember to keep GI and database patch level in sync.

It Looks Complicated

It is a little to cumbersome. We know, and that’s why there are several initiatives to make it easier.

You could also look at Oracle Fleet Patching & Provisioning (FPP). Philippe Fierens is product manager for FPP. You can read his blog posts or reach out to him (he is a nice guy who takes every opportunity to talk about FPP).

OJVM

If your database is using OJVM, then you must also apply the OJVM patch to your Oracle Home. You can check it using:

select version, status from dba_registry where comp_id=’JAVAVM’

I have seen many databases that had OJVM installed, but it was never used. In such case, you can remove the component from your database. Then you no longer need to apply the OJVM patch to your Oracle Home. Plus it has the added benefit that it will make your upgrades faster.

Mike Dietrich has a good blog – the OJVM Patching Saga. Catchy title!