Author: Daniel Overby Hansen

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

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

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.

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

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

Why Does Data Pump Need a Read Write Tablespace

The other day I had to export data using Data Pump and ran into an error:

expdp appuser/appuser ...

ORA-31626: job does not exist
ORA-31633: unable to create master table "APPUSER.SYS_EXPORT_SCHEMA_01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1163
ORA-01647: tablespace 'APPTS' is read-only, cannot allocate space in it
ORA-06512: at "SYS.KUPV$FT", line 1056
ORA-06512: at "SYS.KUPV$FT", line 1044

APPTS is the name of the default tablespace of the exporting user, APPUSER. It appears that Data Pump requires that the default tablespace of the exporting user has its default tablespace in read write mode. Why is that?

The Control Table

To keep track of an export or import job, Data Pump writes information to a so-called control table. The control table is a regular table stored in the database.

Data Pump creates the control table in the default tablespace of the user executing the Data Pump job. It is not possible to store the control table in a different tablespace.

At the end of an export, as the very last thing, Data Pump exports the control table to the dump file. Similarly, at the start of an import, as the very first thing, Data Pump imports the control table.

Normally, you don’t see the control table in the database because Data Pump drops it when a job ends. You can change that by setting KEEP_MASTER=YES. This allows you to query the control table. The name of the control table is the same as the Data Pump job:

SQL> select * 
     from <executing_user>.<name_of_data_pump_job>

If you change the name of the Data Pump job using JOB_NAME, you can alter the name of the control table.

Previously, the control table was called the master table. That’s why the parameter KEEP_MASTER is named as it is.

Learn more about the control table in the webinar Data Pump Extreme – Deep Dive with Development.

Other Requirements

So, if Data Pump must create a table in the database, does that mean there are other requirements?

Yes, it does. Here is a list of the requirements:

  • Database must be open in read write mode
  • Executing user must have CREATE TABLE privilege
  • Executing user must have a quota on its default tablespace

Exporting From a Standby Database

Since there is a requirement that the database must be open in READ WRITE mode, it means that you can’t use Data Pump to export from a standby database. A standby database is always either MOUNTED or OPEN READ ONLY.

Workarounds

  1. If you have a standby database, you can temporarily convert it into a snapshot standby database and perform the export..
  2. Perform the import directly over a database link using the NETWORK_LINK option. See My Oracle Support note How To Use DataPump Export (EXPDP) To Export From Physical Standby Database (Doc ID 1356592.1) for details. Thanks to Franck Pachot for the tip.

Option 1 is preferable since Data Pump in network mode has a few restrictions.