Author: Daniel Overby Hansen

Disstinguished Product Manager for Cloud Migrations at Oracle. I help customers make the most out of Oracle Database.

Sign up for Our Sessions at Oracle DatabaseWorld at CloudWorld

The content catalog for this year’s Oracle DatabaseWorld at CloudWorld is now ready. Overall there are more than 1.000 sessions, and almost 300 of those are strictly database related.

You can now start to add sessions to your schedule. I suggest that you hurry up and get started. Some of the sessions will for sure sell out quickly, especially the hands-on labs.

Sign up before it’s too late!

I have created an overview of all the sessions hosted by Database Upgrade, Migration and Patching. If you enjoy sessions by Mike Dietrich, Rodrigo Jorge, Roy Swonger or Bill Beauregard, add all of them to your schedule.

My Sessions

Best Practices for Upgrade and Migration to Oracle Database 23c

Usually, our most popular talk. What are the best practices for upgrading your Oracle Database and which of those best practices apply to database migrations as well? In Oracle Database 23c, you must migrate to the multitenant architecture; we will also discuss this. Presenting with us is Marco Oberli from Postfinance in Switzerland. They made some cool automation to upgrade their databases and allow users to provision copies for test and developer with the click of a button.

Link

No-Slide Zone – Database Patchings Insights

No slides at all – it’s an open discussion about patching Oracle Database and Grid Infrastructure. Bring all your questions, and together we will work it out. This is definitely my personal favorite. I always learn so much from all these great questions and discussions. Plus, it’s usually a lot of fun.

Link

Help! My Database Is Still on 8i!

We are often involved in migrations of really old databases. Not just 11g or 10g. Even older! Recently we had a question about Oracle7. We have demos to show how to migrate from those old databases. Also, we spin up an Oracle 8i database. Do you remember how to connect? How to take a backup? Finally, we dig into our archives and find a few horror stories. On stage is also Julian Dontcheff from Accenture. He also has some horror stories to share.

Link

Upgrade and Migrate to Oracle Database 19c and 23c the Easy Way

Our all-time favorite hands-on lab got an overhaul for this year’s event. If you are afraid of plan changes after an upgrade, come to this session. You will learn how to avoid that and keep your users happy. Also, we will guide you to upgrade and migrate your databases.

Remember to bring your laptop

Link

See You There

I really hope to see you at Oracle DatabaseWorld at CloudWorld. Remember to come by our demo booth and have a talk about Oracle Database.

We have so many exciting things to share. It’s a shame to miss out on the greatest Oracle Database event of the year.

How to Clone Oracle Grid Infrastructure Home Using Golden Images

Cloning Oracle Grid Infrastructure (GI) homes is a convenient way of getting a new GI Home. It’s particularly helpful when you need to patch out-of-place using the SwitchGridHome method.

When you have created a new GI home and applied all the necessary patches, you can turn it into a golden image. Later on, you can deploy from that golden image and avoid updating OPatch and apply patches.

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/grid/goldimages
mkdir -p $GOLDIMAGEDIR

  3. Finally, you create the golden image. This command creates a golden image of the specified GI home:

    export NEW_GRID_HOME=/u01/app/19.20.0/grid
$NEW_GRID_HOME/gridSetup.sh -createGoldImage \
   -destinationLocation $GOLDIMAGEDIR \
   -silent

    Be sure to do this before you start to use the new GI home.

  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. You can also use the secret parameter -name to specify a name for the zip file. To name the zip file gi_19_20_0.zip:

    $NEW_GRID_HOME/gridSetup.sh -createGoldImage \
   ... \
   -name gi_19_20_0.zip

No software must run out of the Oracle Home, when you create the gold image. Don’t use a production Oracle Home. I recommend using a test or staging server instead.

Check the documentation for further details.

How to Deploy from a Golden Image

  1. You must create a folder for the new GI home. You do it as root:

    export NEW_GRID_BASE=/u01/app/19.20.0
export NEW_GRID_HOME=$NEW_GRID_BASE/grid
mkdir -p $NEW_GRID_HOME
chown -R grid:oinstall $NEW_GRID_BASE
chmod -R 775 $NEW_GRID_BASE

    If you install the new GI home in a cluster, you must create the folder on all nodes.

  2. Then, you extract the golden image as grid:

    export NEW_GRID_HOME=/u01/app/19.20.0/grid
cd $NEW_GRID_HOME
unzip -q /u01/app/grid/goldimages/gi_19_20_0.zip

  3. Finally, you use gridSetup.sh to perform the installation:

    ./gridSetup.sh

That’s it!

I recommend using golden images when you patch out-of-place using the SwitchGridHome method.

Appendix

Oracle Restart vs. Oracle RAC

If you create a GI home for use with Oracle RAC, you can’t use that gold image for a new GI home for Oracle Restart.

Such two GI homes would be very different. You must have two gold images. One for RAC and one for Restart.

Further Reading

Other Blog Posts in This Series

How to Remove an Old Oracle Grid Infrastructure 19c Home

When you patch your Oracle Grid Infrastructure 19c (GI) using the out-of-place method, you should also remove the old GI homes.

I recommend that you keep the old GI home for a while. At least until you are convinced that a rollback is not needed. Once you are comfortable with the new GI home, you can safely get rid of it.

How to Remove an Oracle Grid Infrastructure 19c Home

  1. I set the path to my old GI home as an environment variable:
    export REMOVE_ORACLE_HOME=/u01/app/19.0.0.0/grid
  2. Optionally, I take a backup of the GI home for safekeeping:
    export GOLDIMAGEDIR=/u01/app/grid/goldimages
mkdir -p $GOLDIMAGEDIR
$REMOVE_ORACLE_HOME/gridSetup.sh -createGoldImage \
   -destinationLocation $GOLDIMAGEDIR \
   -silent
  3. I verify that the GI home, is not the active one. This command returns the active GI home. It must not return the path of the GI home, which I want to delete. As grid:
    $REMOVE_ORACLE_HOME/srvm/admin/getcrshome
  4. I double-check that the GI home to remove is not the active one. The XML tag returned must not contain an CRS=“true” attribute. As grid:
    export ORA_INVENTORY_XML=/u01/app/oraInventory/ContentsXML/inventory.xml
grep "$REMOVE_ORACLE_HOME" $ORA_INVENTORY_XML

#This is good
#   <HOME NAME="OraGrid190" LOC="/u01/app/19.0.0.0/grid" TYPE="O" IDX="1"/>
#This is bad
#.  <HOME NAME="OraGrid190" LOC="/u01/app/19.0.0.0/grid" TYPE="O" IDX="1" CRS="true"/>
  5. I run the deinstall tool. I switch to my home directory to ensure I am not interfering with the de-installation. As grid:
    cd ~
$REMOVE_ORACLE_HOME/deinstall/deinstall
    The script:
    • Detects the nodes in my cluster.
    • Prints a summary and prompts for confirmation.
    • Deinstalls the GI home on all nodes.
    • Instructs me to run a script as root on all nodes.
    • Prints a summary including any manual tasks in the end.
  6. I verify that the GI home is marked as deleted in the inventory. The XML tag should have a Removed=“T” attribute. As grid:
    export ORA_INVENTORY_XML=/u01/app/oraInventory/ContentsXML/inventory.xml
grep "$REMOVE_ORACLE_HOME" $ORA_INVENTORY_XML

#This is good
#   <HOME NAME="OraGrid190" LOC="/u01/app/19.0.0.0/grid" TYPE="O" IDX="1" Removed="T"/>
  7. Often the deinstall tool can’t remove some files because of missing permissions. I remove the GI home manually. As root on all nodes:
    export REMOVE_ORACLE_HOME=/u01/app/19.0.0.0/grid
rm -rf $REMOVE_ORACLE_HOME

Silent Mode

There is also a silent mode if you want to script the removal. Check the -checkonly and -silent parameters in the documentation.

You can also find a sample response file in the documentation.

Appendix

Further Reading

Other Blog Posts in This Series

How to Roll Back Oracle Grid Infrastructure 19c Using SwitchGridHome

Let me show you how I roll back a patch from Oracle Grid Infrastructure 19c (GI) using the out-of-place method and the -switchGridHome parameter.

My demo system:

  • Is a 2-node RAC (nodes copenhagen1 and copenhagen2).
  • Runs Oracle Linux.
  • Was patched from 19.17.0 to 19.19.0. I patched both GI and database. Now I want GI back on 19.17.0.

I only roll back the GI home. See the appendix for a few thoughts on rolling back the database as well.

This method works if you applied the patch out-of-place – regardless of whether you used the OPatchAuto or SwitchGridHome method.

Preparation

  • I use the term old Oracle Home for the original, lower patch level Oracle Home.

    • It is my 19.17.0 Oracle Home
    • It is stored in /u01/app/19.0.0.0/grid
    • I refer to this home using the environment variable OLD_ORACLE_HOME
    • This is the Oracle Home that I want to roll back to

  • I use the term new Oracle Home for the higher patch level Oracle Home.

    • It is my 19.19.0 Oracle Home
    • It is stored in /u01/app/19.19.0/grid
    • I refer to this home using the environment variable NEW_ORACLE_HOME
    • This is the Oracle Home that I want to roll back from

Both GI homes are present in the system already.

How to Roll Back Oracle Grid Infrastructure 19c

1. Sanity Checks

I execute the following checks on both nodes, copenhagen1 and copenhagen2. I show the commands for one node only.

  • I verify that the active GI home is the new GI home:

    [grid@copenhagen1]$ export ORACLE_HOME=$NEW_GRID_HOME
[grid@copenhagen1]$ $ORACLE_HOME/srvm/admin/getcrshome

  • I verify that the cluster upgrade state is NORMAL:

    [grid@copenhagen1]$ $ORACLE_HOME/bin/crsctl query crs activeversion -f

  • I verify all CRS services are online:

    [grid@copenhagen1]$ $ORACLE_HOME/bin/crsctl check cluster

  • I verify that the cluster patch level is 19.19.0 – the new patch level:

    [grid@copenhagen1]$ $ORACLE_HOME/bin/crsctl query crs releasepatch

2. Cluster Verification Utility

  • I use Cluster Verification Utility (CVU) to verify that my cluster meets all prerequisites for a patch/rollback. I do this on one node only:
    [grid@copenhagen1]$ $CVU_HOME/bin/cluvfy stage -pre patch
    • You can find CVU in the GI home, but I recommend always getting the latest version from My Oracle Support.

3. Roll Back Node 1

The GI stack (including database, listener, etc.) needs to restart on each instance. But I do the rollback in a rolling manner, so the database stays up all the time.

  • I drain connections from the first node, copenhagen1.

  • I unlock the old GI home, root:

    [root@copenhagen1]$ export OLD_GRID_HOME=/u01/app/19.0.0.0/grid
[root@copenhagen1]$ cd $OLD_GRID_HOME/crs/install
[root@copenhagen1]$ ./rootcrs.sh -unlock -crshome $OLD_GRID_HOME
    • This is required because the next step (gridSetup.sh) runs as grid and must have access to the GI home.
    • Later on, when I run root.sh, the script will lock the GI home.

  • I switch to old GI home as grid:

    [grid@copenhagen1]$ export OLD_GRID_HOME=/u01/app/19.0.0.0/grid
[grid@copenhagen1]$ export ORACLE_HOME=$OLD_GRID_HOME
[grid@copenhagen1]$ export CURRENT_NODE=$(hostname)
[grid@copenhagen1]$ $ORACLE_HOME/gridSetup.sh \
   -silent -switchGridHome \
   oracle.install.option=CRS_SWONLY \
   ORACLE_HOME=$ORACLE_HOME \
   oracle.install.crs.config.clusterNodes=$CURRENT_NODE \
   oracle.install.crs.rootconfig.executeRootScript=false

  • I complete the switch by running root.sh as root:

    [root@copenhagen1]$ export OLD_GRID_HOME=/u01/app/19.0.0.0/grid
[root@copenhagen1]$ $OLD_GRID_HOME/root.sh
    • This step restarts the entire GI stack, including resources it manages (databases, listener, etc.). This means downtime on this node only. The remaining nodes stay up.
    • In that period, GI marks the services as OFFLINE so users can connect to other nodes.
    • If my database listener runs out of the Grid Home, GI will move it to the new Grid Home, including copying listener.ora.
    • In the end, GI restarts the resources (databases and the like).

  • I update any profiles (e.g., .bashrc) and other scripts referring to the GI home.

  • I verify that the active GI home is the new GI home:

    [grid@copenhagen1]$ $OLD_ORACLE_HOME/srvm/admin/getcrshome

  • I verify that the cluster upgrade state is ROLLING PATCH:

    [grid@copenhagen1]$ $OLD_ORACLE_HOME/bin/crsctl query crs activeversion -f

  • I verify all CRS services are online:

    [grid@copenhagen1]$ $OLD_ORACLE_HOME/bin/crsctl check cluster

  • I verify all resources are online:

    [grid@copenhagen1]$ $OLD_ORACLE_HOME/bin/crsctl stat resource -t

  • I verify that the GI patch level is 19.17.0 – the old patch level:

    [grid@copenhagen1]$ $OLD_ORACLE_HOME/bin/crsctl query crs releasepatch

4. Roll Back Node 2

  • I roll back the second node, copenhagen2, using the same process as the first node, copenhagen1.
    • I double-check that the CURRENT_NODE environment variable gets updated to copenhagen2.
    • When I use crsctl query crs activeversion -f to check the cluster upgrade state, it will now be back in NORMAL mode, because copenhagen2 is the last node in the cluster.

5. Cluster Verification Utility

  • I use Cluster Verification Utility (CVU) again. Now I perform a post-rollback check. I do this on one node only:
    [grid@copenhagen1]$ $CVU_HOME/bin/cluvfy stage -post patch

That’s it!

My cluster is now operating at the previous patch level.

Appendix

SwitchGridHome Does Not Have Dedicated Rollback Functionality

OPatchAuto has dedicated rollback functionality that will revert the previous patch operation. Similar functionality does not exist when you use the SwitchGridHome method.

This is described in Steps for Minimal Downtime Grid Infrastructure Out of Place ( OOP ) Patching using gridSetup.sh (Doc ID 2662762.1). To rollback, simply switch back to the previous GI home using the same method as for the patch.

There is no real rollback option as this is a switch from OLD_HOME to NEW_HOME To return to the old version you need to recreate another new home and switch to that.

Should I Roll Back the Database as Well?

This post describes rolling back the GI home only. Usually, I recommend keeping the database and GI patch level in sync. If I roll back GI, should I also roll back the database?

The short answer is no!

Keeping the GI and database patch in sync is a good idea. But when you need to roll back, you are in a contingency. Only roll back the component that gives you problems. Then, you will be out of sync for a period of time until you can get a one-off patch or move to the next Release Update. Being in this state for a shorter period is perfectly fine – and supported.

Other Blog Posts in This Series

Files to Move During Oracle Grid Infrastructure Out-Of-Place Patching

Like with Oracle Database, I strongly recommend patching Oracle Grid Infrastructure using the out-of-place method. It has many advantages over in-place patching

A while ago, I wrote about the files you must move during Oracle Database out-of-place patching. There are quite many files to consider. A blog reader left a comment asking about a similar blog post for Oracle Grid Infrastructure, so here it is.

Listener

The listener runs out of the GI home. When you patch out-of-place, the listener must also start in the new GI home.

If you patch using any of the below methods, it will move the listener for you:

The two methods also move listener.ora as part of the process.

If you use opatchauto you should note that the tool moves listener.ora when preparing the new GI using opatchauto apply ... -prepare-clone. You can run that command hours or days before you move the listener. If you add things to listener.ora in between, you must also add it to listener.ora in the new GI home.

Conclusion

There is really nothing to worry about when you patch Oracle Grid Infrastructure out-of-place. The above-mentioned two tools will take care of it for you.

Happy Patching

A Rare Insight Into the Oracle Database Development Process

Have you ever wondered why Oracle didn’t include your bug fix in the next Release Update? Or what good is it that your bug is fixed in 23.1 when Oracle Database isn’t released yet?

We’ll explain this – and much more in our next webinar.

  • Episode 17: From SR to Patch June 22, 2023, 16:00 CEST

Oracle Database 19c Upgrade Virtual Classroom

Mike and I will show you what happens behind the scenes, from opening a service request 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 can sign up here.

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. On the same page, you can also watch all previous webinars and get the slides.

But it’s better to watch it live. You can ask questions to us live. I promise you; we won’t leave until we have answered all your questions.

All Tech, No Marketing

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

I hope to see you there!

How to Apply Patches Out-of-place to Oracle Grid Infrastructure and Oracle Data Guard Using Standby-First

I strongly recommend that you always patch out-of-place. Here’s an example of how to do it on Oracle Grid Infrastructure (GI) and Oracle Data Guard using Standby-First Patch Apply.

Standby-First Patch Apply allows you to minimize downtime to the time it takes to perform a Data Guard switchover. Further, it allows you to test the apply mechanism on the standby database by temporarily converting it into a snapshot standby database.

The scenario:

  • Oracle Grid Infrastructure 19c and Oracle Database 19c
  • Patching from Release Update 19.17.0 to 19.19.0
  • Vertical patching – GI and database at the same time
  • Data Guard setup with two RAC databases
    • Cluster 1: copenhagen1 and copenhagen2
    • Cluster 2: aarhus1 and aarhus2
    • DB_NAME: CDB1
    • DB_UNIQUE_NAME: CDB1_COPENHAGEN and CDB1_AARHUS
  • Using Data Guard broker
  • Patching GI using SwitchGridHome method

Let’s get started!

Step 1: Prepare

I can make the preparations without interrupting the database.

  • I ensure my environment meets the requirements for Standby-First Patch Apply.

  • I deploy new GI homes to all four hosts.

    • I use the SwitchGridHome method.
    • Very important: I only perform step 1 (Prepare a New Grid Home).
    • I apply the Release Update 19.19.0 as part of the deployment using gridSetup.sh ... -applyRU ... -applyOneOffs as described in the blog post.

  • I deploy new database homes to all four hosts.

  • I also recompile invalid objects. This can make it easier for Datapatch later in the process:

    PRIMARY SQL> @?/rdbms/admin/utlrp

Step 2: Restart Standby in New Oracle Homes

Now, I can move the standby database to the new GI and database homes.

  • On the standby hosts, aarhus1 and aarhus2, I first move the database configuration files from the old database home to the new one.

  • I change the database configuration in GI. Next time the database restarts, it will be in the new Oracle Home:

    [oracle@aarhus1]$ $OLD_ORACLE_HOME/bin/srvctl modify database \
   -db $ORACLE_UNQNAME \
   -oraclehome $NEW_ORACLE_HOME

  • I switch to the new GI on all standby hosts, aarhus1 and aarhus2, by executing step 2 (Switch to the new Grid Home) of the SwitchGridHome method.

    • It involves running gridSetup.sh ... -switchGridHome and root.sh.
    • You can perform the switch in a rolling manner or all at once.
    • The switch restarts the standby database instance. The standby database instance restarts in the new Oracle Home.
    • If the profile of grid (like .bashrc) sets the ORACLE_HOME environment variable, I ensure to update it.

  • If I had multiple standby databases, I would process all standby databases in this step.

Step 3: Test Standby Database

This is an optional step, but I recommend that you do it.

  • I convert the standby database (CDB1_AARHUS) to a snapshot standby database:
    DGMGRL> convert database CDB1_AARHUS to snapshot standby;
  • I test Datapatch on the standby database. It is important that I run the command on the standby database:
    [oracle@aarhus1]$ $ORACLE_HOME/OPatch/datapatch -verbose
  • I can also test my application on the standby database.
  • At the end of my testing, I revert the standby database to a physical standby database. The database automatically reverts all the changes made during testing:
    DGMGRL> convert database CDB1_AARHUS to physical standby;

Step 4: Switchover

I can perform the previous steps without interrupting my users. This step requires a maintenance window because I am doing a Data Guard switchover.

  • I check that my standby database is ready to become primary. Then, I start a Data Guard switchover:
    DGMGRL> connect sys/<password> as sysdba
DGMGRL> validate database CDB1_AARHUS;
DGMGRL> switchover to CDB1_AARHUS;

A switchover does not have to mean downtime.

If my application is configured properly, the users will experience a brownout; a short hang, while the connections switch to the new primary database.

Step 5: Restart New Standby in New Oracle Homes

Now, the primary database runs on aarhus1 and aarhus2. Next, I can move the new standby hosts, copenhagen1 and copenhagen2, to the new GI and database homes.

  • I repeat step 2 (Restart Standby In New Oracle Homes) but this time for the new standby hosts, copenhagen1 and copenhagen2.

Step 6: Complete Patching

Now, both databases in my Data Guard configuration run out of the new Oracle Homes.

Only proceed with this step once all databases run out of the new Oracle Home.

I need to run this step as fast as possible after I have completed the previous step.

  • I complete the patching by running Datapatch on the primary database (CDB1_AARHUS). I add the recomp_threshold parameter to ensure Datapatch recompiles all objects that the patching invalidated:

    [orale@aarhus1]$ $ORACLE_HOME/OPatch/datapatch \
   -verbose \
   -recomp_threshold 10000
    • I only need to run Datapatch one time. On the primary database and only on one of the instances.

  • I can run Datapatch while users are connected to my database.

  • Optionally, I can switch back to the original primary database on copenhagen1 and copenhagen2, if I prefer to run it there.

That’s it. Happy patching!

Appendix

Further Reading

Other Blog Posts in This Series

Files to Move During Oracle Database Out-Of-Place Patching

I strongly recommend that you patch your Oracle Database using the out-of-place method. It has many advantages over in-place patching. But when you move your Oracle Database from one Oracle Home to another, you also need to move a lot of files.

Which files are that, and how can you make it easier for you? Also, some files might exist already in the target Oracle Home; what do you do then?

Files to Move

Password File

Linux:

dbs/orapw<ORACLE_SID>

Windows:

database\pwd<ORACLE_SID>.ora

You can override the default location in Windows using the following registry entries:

ORA_<ORACLE_SID>_PWFILE
ORA_PWFILE

If you use Grid Infrastructure, you can put the password file outside of the Oracle Home:

srvctl modify datatabase \
   -d $ORACLE_UNQNAME
   -pwfile <NEW_LOCATION_OUTSIDE_ORACLE_HOME>

I recommend storing it in ASM.

Parameter Files

Linux:

dbs/init<ORACLE_SID>.ora
dbs/spfile<ORACLE_SID>.ora

Windows:

database\init<ORACLE_SID>.ora
database\spfile<ORACLE_SID>.ora

Parameter files may include other files using the IFILE parameter.

You can redirect the server parameter file to a location outside the Oracle Home using the SPFILE parameter in your parameter file. If you use Grid Infrastructure, you can also redirect the server parameter file:

srvctl modify datatabase \
   -d $ORACLE_UNQNAME
   -spfile <NEW_LOCATION_OUTSIDE_ORACLE_HOME>

I recommend storing it in ASM.

Oratab

You need to update the database instance entry in the oratab file:

/etc/oratab

On Solaris, you find the file in:

/var/opt/oracle/oratab

On Windows, the file does not exist. Instead, you re-register the instance in the registry when you use oradim.exe.

Profile Scripts

Many people have profile scripts that set the environment to a specific database. Be sure to update the Oracle Home in such scripts.

Network Files

Network configuration files:

network/admin/ldap.ora
network/admin/listenener.ora
network/admin/sqlnet.ora
network/admin/tnsnames.ora

tnsnames.ora, sqlnet.ora and listener.ora can include contents from other files using the IFILE parameter, although the support of it is somewhat… questionable according to Allows for IFILE Ifile Support and Oracle Net (Doc ID 1339269.1).

You can redirect the files using the TNS_ADMIN environment variable. On Windows, you can also redirect using the TNS_ADMIN registry entry. If you use Grid Infrastructure, you can set the TNS_ADMIN environment variable as part of the cluster registration:

srvctl setenv database \
   -d $ORACLE_UNQNAME \
   -env "TNS_ADMIN=<NEW_LOCATION_OUTSIDE_ORACLE_HOME>"

Data Guard Broker Config Files

Linux:

dbs/dr1<ORACLE_SID>.dat
dbs/dr2<ORACLE_SID>.dat

Windows:

database\dr1<ORACLE_SID>.dat
database\dr2<ORACLE_SID>.dat

You can redirect the broker config files using the parameter DG_BROKER_CONFIG_FILEn:

alter system set db_broker_start=false;
alter system set dg_broker_config_file1='<NEW_LOCATION>/dr1<ORACLE_SID>.dat';
alter system set dg_broker_config_file2='<NEW_LOCATION>/dr2<ORACLE_SID>.dat';
alter system set db_broker_start=true;

I recommend storing the files in ASM.

Admin directory

admin subdirectory in Oracle Home:

admin

If you don’t set ORACLE_BASE environment variable, the database uses the Oracle Home for that location. It can contain diagnostic information like logs and tracing which you might want to move to the new Oracle Home.

In rare cases, the TDE keystore will go in there as well. This is definitely a folder that you want to keep.

admin/$ORACLE_UNQNAME/wallet

I recommend having a dedicated ORACLE_BASE location. Always set ORACLE_BASE environment variable for all databases. This will ensure that the database will not create an admin directory in the Oracle Home.

If you use TDE Tablespace Encryption, I strongly recommend that you store the database keystore outside of the Oracle Home using the WALLET_ROOT parameter.

Direct NFS

The Direct NFS configuration file:

dbs/oranfstab

The file might exist in the target Oracle Home, in which case you must merge the contents.

Typically, on Windows, the files from dbs are stored in database folder. But that’s different for this specific file (thanks Connor for helping out).

Centrally Managed Users

One of the default locations of the configuration file for Active Directory servers for centrally managed users is.

ldap/admin/dsi.ora

I recommend using the LDAP_ADMIN environment variable to redirect the file to a location outside of the Oracle Home.

LDAP

Configuration of Directory Usage Parameters:

ldap/admin/ldap.ora

I recommend using the LDAP_ADMIN or TNS_ADMIN environment variable to redirect the file to a location outside of the Oracle Home.

Oracle Messaging Gateway

The Messaging Gateway default initialization file:

mgw/admin/mgw.ora

The file might exist in the target Oracle Home, in which case you must merge the contents.

Oracle Database Provider for DRDA

Configuration file for Oracle Database Provider for DRDA:

drdaas/admin/drdaas.ora

The file might exist in the target Oracle Home, in which case you must merge the contents.

Oracle Text

If you use Oracle Text, you can generate a list of files that you must copy to the target Oracle Home:

ctx/admin/ctx_oh_files.sql

Oracle Database Gateway for ODBC

ODBC gateway initialization file:

hs/admin/init<ORACLE_SID>.ora

External Procedures

You can define the environment for external procedures in extproc.ora. Such configuration might exist in the target Oracle Home already, in which case you must merge the contents:

hs/admin/extproc.ora

If the file contains references to external DLLs using the parameter EXTPROC_DLLS, you should ensure those files are placed outside the Oracle home. Otherwise, you must also copy the DLL and update extproc.ora.

Other Things to Consider

Enterprise Manager

After moving the database to a new Oracle Home, you need to reconfigure the target in Enterprise Manager. The Oracle Home path is part of the configuration. You can easily change it with emcli:

emcli modify_target \
   -type='oracle_database' \
   -name='<target_name>' \
   -properties='OracleHome:<new_oracle_home_path>'

Also, if you moved the listener as part of the patching to a new Oracle Home, you need to update that as well.

On My Oracle Support you can find an example on how to bulk update multiple targets.

In a future version of AutoUpgrade, it will be able to modify the target in Enterprise Manager for you.

Oracle Key Vault

There should be no additional configuration needed if you are using Oracle Key Vault and you move the database to a new Oracle Home.

You can find information on how to configure Oracle Key Vault in an Oracle Database in the documentation.

ZDLRA

When you patch out-of-place, you should always ensure that the target Oracle Home has the latest version of libra.so. AutoUpgrade does not copy this file for you, because there is no way to tell which version is the latest version.

Ideally, you configure ZDLRA in via sqlnet.ora and store the wallet outside of the Oracle Home. If so, the ZDLRA configuration works in the target Oracle Home because AutoUpgrade takes care of sqlnet.ora.

If you use ra_install.jar to configure ZDLRA, the script will:

  • Create a file: $ORACLE_HOME/dbs/ra<ORACLE_SID>.ora
  • Create a folder with a wallet: $ORACLE_HOME/dbs/wallet

In this case, you must manually copy the files to the target Oracle Home. You can avoid this by using sqlnet.ora for the configuration instead.

AutoUpgrade does not copy these files for you, because of the issue described above with libra.so.

Database Directories

You must update certain internal database directories, when you move the database to a new Oracle Home. The easiest way is to run:

@?/rdbms/admin/utlfixdirs.sql

In multitenant, you need to run the script in CDB$ROOT only.

On My Oracle Support you find a list of all the directories that you must update if you don’t use the script above.

In a future version of AutoUpgrade, it will change all the applicable directories for you automatically.

How to Make It Easy

Use AutoUpgrade

The easiest way to patch your Oracle Database is to use AutoUpgrade. It takes care of everything for you (unless stated otherwise above). You need a config file:

patch1.source_home=/u01/app/oracle/product/19.18.0
patch1.target_home=/u01/app/oracle/product/19.19.0
patch1.sid=MYDB

Then you start AutoUpgrade:

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

That’s it! You don’t need to worry about all those configuration files. AutoUpgrade got you covered.

Update: AutoUpgrade doesn’t copy any DLLs referenced by EXTPROC_DLLS in extproc.ora. Those files should be placed outside the Oracle home. If placed inside the Oracle home, it’s considered a misconfiguration.

Use Read-Only Oracle Home

If you use Read-Only Oracle Home, the process is slightly easier.

You can’t store any custom files in the Oracle Home. Instead, you store all configuration files in Oracle Base Home; a directory outside the Oracle Home. You can find all the files you need to copy in Oracle Base Home.

When you create a new Read-Only Oracle Home, the installer will create a new Oracle Base Home specifically for that new Oracle Home. As part of the patching, you move the files into that new Oracle Base Home.

Conclusion

Did I miss any files? Please leave a comment if you move other files when you patch out-of-place.

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