Author: Daniel Overby Hansen

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

Fast Refresh Materialized Views and Migrations Using Transportable Tablespaces

In some databases, fast refresh materialized views are important for good performance. During a migration using transportable tablespaces, how do you ensure the materialized views are kept up-to-date and avoid a costly complete refresh or stale data?

The Basics

The database populates a materialized view with data from a master table. To enable fast refresh on a materialized view, you must first create a materialized view log on the master table. The materialized view log captures changes on the master table. Then, the database applies the changes to the materialized view, thus avoiding a complete refresh.

When a user changes data in the master table (SALES), those changes are recorded in the materialized view log (MLOG$_SALES) and then used to refresh the materialized view (SALES_MV).

The master table and the materialized view log must reside in the same database (known as the master database). Hence, they are depicted in bluish colors. The materialized view is often in a different database, and then uses a database link to get the changes.

You only need materialized view logs if the materialized view is a fast refresh type.

A fast refresh materialized view needs to perform a complete refresh the first time, before it can move on with fast refreshes (thanks to my good friend, Klaus, for leaving a comment).

Remote Master Table, Local Materialized View

In this example, you are migrating a database from source to target. This database holds the materialized view, and a remote database acts as the master database, where the master table and materialized view log reside.

Migrating a database which holds a materialized view that uses a remote master database

The materialized view and the underlying segment are stored in a tablespace in the source database. That segment is used to recreate the materialized view in the target database without any refresh needed.

  1. You must perform the following in the migration downtime window.
  2. In the source database, stop any periodic refresh of the materialized view.
  3. Optionally, perform a fast refresh of the materialized view:
    exec dbms_mview.refresh ('sales_mv','f');
  4. Start the migration using transportable tablespaces.
    • Set the tablespaces read-only.
    • Perform a final incremental backup.
    • Data Pump sets the tablespaces read-write in the target database.
    • Shut down the source database. Otherwise, you might risk it interfering with the target database’s refresh.
  5. In the target database, you can now perform a fast refresh.
    • Data Pump has already recreated the database link to the remote master database.
    • Although the materialized view is now in a different database, it can fetch the recent changes from the master database starting with the last refresh in the source database.
  6. In the master database, both materialized views from the source and target database are now registered:
    select * from dba_registered_mviews where name='SALES_MV';
  7. Purge materialized view log entries that are related to the materialized view in the source database:
    exec dbms_mview.purge_mview_from_log('<mview-owner>', 'SALES_MV', '<source-db>');
  8. Unregister the materialized view in the source database:
    exec dbms_mview.unregister_mview('<mview-owner>', 'SALES_MV', '<source-db>');
  9. In the target database, re-enable periodic refresh of the materialized view.

Local Master Table, Remote Materialized View

In this example, you are migrating a database from source to target. This database holds the master table and materialized view log, while a remote database contains the materialized view.

Migrating a database which acts as master database holding a master table and materialized view log

The master table and materialized view log are stored in a tablespace in the source database. The migration moves the data to the target database. The materialized view is in the same database, so no remote database or database link is involved.

  1. You must perform the following in the migration downtime window.
  2. In the remote database, stop any periodic refresh of the materialized view.
  3. Optionally, perform a fast refresh of the materialized view:
    exec dbms_mview.refresh ('sales_mv','f');
  4. Start the migration using transportable tablespaces.
    • Set the tablespaces read-only.
    • Perform a final incremental backup.
    • Data Pump sets the tablespaces read-write in the target database.
    • Shut down the source database. Otherwise, you might risk it interfering with the target database’s refresh.
  5. In the remote database, ensure that the database link now points to the new target database.
    • If the database link uses a TNS alias, you can update it.
    • Or recreate the database link with a new connect descriptor.
  6. Perform a fast refresh.
    exec dbms_mview.refresh ('sales_mv','f');
    • If you hit ORA-04062: timestamp of package "SYS.DBMS_SNAPSHOT_UTL" has been changed, run the refresh again.
  7. Re-enable periodic refresh of the materialized view.
  8. In the target database, ensure that the materialized view log is now empty.
    select * from mlog$_sales;

Local Master Table, Local Materialized View

In this example, you are migrating a database from source to target. This database holds the master table, the materialized view log, and the materialized view. There is no remote database involved.

Migrating a database which acts as master database holding a master table and materialized view log

The master table and materialized view log are stored in a tablespace in the source database. The migration moves the data to the target database. The materialized view is in the same database, so there is no remote database or database link involved.

  1. You must perform the following in the migration downtime window.
  2. In the source database, stop any periodic refresh of the materialized view.
  3. Perform a fast refresh of the materialized view:
    exec dbms_mview.refresh ('sales_mv','f');
  4. Ensure the materialized view log is empty, i.e., all rows have been refreshed into the materialized view.
    select count(*) from mlog$_sales;
    • The query must return 0 rows. If more rows, then perform an additional fast refresh.
    • If a remote materialized view uses the materialized view log then it is acceptable to move on if you are sure the local materialized view is completely updated.
  5. Start the migration using transportable tablespaces.
    • Set the tablespaces read-only.
    • Perform a final incremental backup.
    • Data Pump sets the tablespaces read-write in the target database.
    • Shut down the source database. Otherwise, you might risk it interfering with the target database’s refresh.
  6. In the target database, perform a fast refresh.
    exec dbms_mview.refresh ('sales_mv','f');
  7. Re-enable periodic refresh of the materialized view.
  8. Ensure that the materialized view log is now empty.
    select * from mlog$_sales;

A word of caution here. The materialized view must be completely up-to-date in the source database before the migration. After the migration, the same materialized view won’t be able to refresh the pre-migration rows. That is why you are checking for rows in mlog$_sales.

Any new changes made in the target database will sync fine.

Further Reading

AutoUpgrade New Features: Upgrade RMAN Catalog Schema

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

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

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

Details

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

How To

  • Specify the connect string to the catalog database in the AutoUpgrade config file:

    <prefix>.rman_catalog_connect_string=catalogdb
    • catalogdb is a TNS alias to the catalog database.

  • Start AutoUpgrade to load the username and password for the recovery catalog:

    java -jar autoupgrade.jar -config ... -load_password

  • Switch to the password group RMAN:

    group rman

  • Add the username and password for a specific database:

    add <ORACLE_SID> -user <catalog_schema_name>
    • AutoUpgrade prompts for the password

  • Save the changes and exit the load password console.

    save
exit

  • Start AutoUpgrade in deploy mode:

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

Happy Upgrading, Happy Patching

  • You can enhance the solution using an after_action script that starts a level 1 backup after the job. The after_action script takes place after the postupgrade stage, where AutoUpgrade upgrades the catalog schema.

  • Version 24.8 of AutoUpgrade does not support this feature when you use the -patch command line option. This is coming in a later version.

Appendix

Further Reading

Invalid credentials

  • When you enter the catalog credentials into the AutoUpgrade keystore, AutoUpgrade validates the credentials. Any errors result in AutoUpgrade returning the following message:

    Invalid credentials, please try again.

  • To debug, run the following command:

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

  • Check the log files in:

    <global_log_dir>/log/cfgtoollogs/upgrade/auto

AutoUpgrade New Features: Drop Database Link When Using Refreshable Clones

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

Details

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

How To

  • Get the latest version of AutoUpgrade:

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

  • Instruct AutoUpgrade to drop the database link after completing the job:

    upg1.drop_dblink=yes

Happy Upgrading

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

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

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

Control The Final Refresh When Using Refreshable Clone PDBs in AutoUpgrade

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

Here’s how to do that in AutoUpgrade.

The Final Refresh Dilemma

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

Overview of the phases when using refreshable clone PDBs

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

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

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

The Solution

Update: Check out the new proceed command.

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

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

Here’s my approach

  • I create a config file and set the start_time parameter to the start of my downtime window.
    upg1.start_time=30/11/2024 02:00:00
  • I start AutoUpgrade in deploy mode before my downtime starts:
    java -jar autoupgrade.jar -config ... -mode deploy
    • I must start well before the downtime window so AutoUpgrade has enough time to copy the database.
    • Imagine my tests show it takes around four hours to copy the database. I decide to start on Friday, 29 November, 16:00, so the copy should end around 20:00 – well enough time before my downtime window.
  • AutoUpgrade now starts the CLONEPDB phase:
    +----+-------+--------+---------+-------+----------+-------+---------------------------+
 |Job#|DB_NAME|   STAGE|OPERATION| STATUS|START_TIME|UPDATED|                    MESSAGE|
 +----+-------+--------+---------+-------+----------+-------+---------------------------+
 | 100|   TEAL|CLONEPDB|EXECUTING|RUNNING|  02:00:00| 4s ago|Creating pluggable database|
 +----+-------+--------+---------+-------+----------+-------+---------------------------+
    • Note the START_TIME value. It is the time when the final refresh happens.
  • I wait for AutoUpgrade to create the PDB and enter the REFRESHPDB phase:
    +----+-------+----------+---------+-------+----------+-------+----------------------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|START_TIME|UPDATED|               MESSAGE|
+----+-------+----------+---------+-------+----------+-------+----------------------+
| 100|   TEAL|REFRESHPDB|EXECUTING|RUNNING|  02:00:00| 2s ago|PDB TEAL was refreshed|
+----+-------+----------+---------+-------+----------+-------+----------------------+
  • Then I stop the job:
    upg> stop -job 100
    • If I exit AutoUpgrade after stopping the job, don’t worry. As soon as I restart AutoUpgrade, it will pick up from where it left and continue with the job.
  • When I stop the job, there is no periodic refresh. I should refresh the PDB in the target CDB manually at regular intervals:
    SQL> alter pluggable database teal refresh;
    • If I don’t perform any periodic refresh, the redo will accumulate, and the final refresh will take longer. Keep the final refresh shorter by refreshing more often.
  • After the start of my downtime window (the start_time parameter), when I’m done on the source database and want to proceed with the final refresh, I resume the job in AutoUpgrade.
    upg> resume -job 100
  • AutoUpgrade now realizes it is past the defined start_time and immediately moves on with the final refresh and the rest of the job.

Wrapping Up

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

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

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

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

Happy Migrating!

Further Reading

AutoUpgrade New Features: Update Enterprise Manager Configuration

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

  • Blackout the database
  • Modify target configuration

Details

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

How To

  • Install and configure EM CLI on the database host.

  • Persist the credentials on the database host using the autologin feature.

  • Get the latest version of AutoUpgrade:

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

  • Specify the path to EM CLI in the AutoUpgrade config file:

    upg1.emcli_path=/u01/app/oracle/oem

  • Specify the target name from Enterprise Manager:

    upg1.em_target_name=ORCL_myhost.domain.int

Happy Upgrading

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

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

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

Easier Patching of Oracle Database on Windows

We just released a new version, 24.8, of AutoUpgrade that supports the new patching features on Windows.

Further, we also made it easier to download the latest version of AutoUpgrade:

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

How Does It Work?

Here’s a simple demo of the new functionality.

Also, you can get much more information about the new patching features in general in our webinar One-Button Patching – makes life easier for every Oracle DBA.

A Few Notes

Credential File

Currently, if you use a credential file to store the username/password of the Windows service, then AutoUpgrade delete it afterwards. We consider that the most secure approach.

But that’s not suitable for automation, so in the next version of AutoUpgrade, a config file parameter will allow you to re-use the credential file repeatedly.

Missing Registry Entries

If you use the credential file – depending on your configuration – you might see that entries are missing in Windows Registry for the database instance.

You can add those manually after patching by executing:

%NEW_ORACLE_HOME%\bin\oradim.exe -edit -sid %ORACLE_SID% -startmode auto

Oradim will see that registry keys are missing and add those.

This will also be automated in a future version of AutoUpgrade. We’re also working on adding support for virtual users.

Happy Patching

AutoUpgrade patching makes it easier to patch your Oracle Database.

Give it a try on Windows, and let us know if something is missing.

Patching Oracle Database should be as easy as patching a smartphone.

How to Patch Oracle Restart 19c and Oracle Data Guard Using Out-Of-Place Switch Home

I strongly recommend that you always patch out-of-place. Here’s an example of how to do it on Oracle Restart and Oracle Data Guard.

This procedure is very similar to patching without Oracle Data Guard, so I’ll often refer to a previous blog post.

My demo system

  • Data Guard setup with two single instance databases in Oracle Restart configuration
  • GI and database home are currently on 19.24

I want to:

  • patch to 19.25
  • patch both the GI and database home in one operation

Preparation

  • I download the same patches are mentioned in the Preparations.

  • I place the software in /u01/software which is an NFS share accessible to both servers.

How to Patch Oracle Restart 19c and Oracle Database

1. Prepare a New GI Home

I can do this in advance. It doesn’t affect my current environment and doesn’t cause any downtime.

2. Prepare a New Database Home

I can do this in advance. It doesn’t affect my current environment and doesn’t cause any downtime.

3. Prepare Database

I can do this in advance. It doesn’t affect my current environment and doesn’t cause any downtime.

4. Standby Database: Switch to the New GI and Database Homes

Now, I can complete the patching process on the standby database by switching to the new Oracle homes.

  • I perform 4. Switch to the New GI and Database Homes on the standby host.

  • There is no downtime on the primary database. But the standby database will be shortly down which might mean something to you if you use Active Data Guard.

  • I make sure the standby database is brought back to the desired mode (mount or open). I check redo apply is running.

5. Primary Database: Switch to the New GI and Database Homes

Next, I can complete the patching process on the primary database by switching to the new Oracle homes.

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.

That’s it! I have now patched my Oracle Restart and Data Guard deployment.

Happy Patching!

Appendix

Check the appendix in the related blog post for additional information and troubleshooting.

Further Reading

Other Blog Posts in This Series

Goodbye X, Hello Bluesky

Do you remember the good, ol’ days when we could discuss tech on Twitter? Without all the ads and the craziness that X turned into?

I miss those days.

But I hope to experience them again on Bluesky. Find me on Bluesky and let’s start talking tech again.

https://bsky.app/profile/dohdatabase.com

Bluesky

So far, Bluesky appears like Twitter did in the old days. It’s free and without all the ads and bots that are on X these days.

There is a growing Oracle community on Bluesky. A good number of Product Managers from Oracle, Oracle ACEs, and other significant voices in the community have joined already.

Gerald Venzl has made a starter pack with people you could follow. That should get you started on Bluesky quickly:

Give it a try!

I hope to see you on Bluesky

How to Patch Oracle Restart 19c and Oracle Database Using Out-Of-Place Switch Home

Let me show you how I patch Oracle Restart and Oracle Database 19c using the out-of-place method by switching to the new Oracle homes.

The advantages of this solution:

  • I get more control over the process
  • I can perform the entire operation with just one database restart
  • I can create my Oracle homes using gold images
  • I can prepare the new Oracle homes in advance
  • Overall, I find this method less riskier

My demo system

  • Single instance database in Oracle Restart configuration
  • Runs Oracle Linux
  • GI and database home are currently on 19.24

I want to:

  • patch to 19.25
  • patch both the GI and database home in one operation

Preparation

I need to download:

  1. The base releases of:
    • Oracle Grid Infrastructure (LINUX.X64_193000_grid_home.zip)
    • Oracle Database (LINUX.X64_193000_db_home.zip)
  2. Latest OPatch from My Oracle Support (6880880).
  3. Patches from My Oracle Support:
    • 19.25 Release Update for Grid Infrastructure (36916690)
    • Matching OJVM bundle patch (36878697)
    • Matching Data Pump bundle patch (36682332)

You can use AutoUpgrade to easily download GI patches.

I place the software in /u01/software.

How to Patch Oracle Restart 19c and Oracle Database

1. Prepare a New GI Home

I can do this in advance. It doesn’t affect my current environment and doesn’t cause any downtime.

  1. I need to create a folder for the new GI home. I must do this as root:

    [root@node1]$ mkdir -p /u01/app/19.25.0/grid
[root@node1]$ chown -R grid:oinstall /u01/app/19.25.0
[root@node1]$ chmod -R 775 /u01/app/19.25.0

  2. I switch to the GI home owner, grid.

  3. I extract the base release of Oracle Grid Infrastructure into the new GI home:

    [grid@node1]$ export OLDGRIDHOME=$ORACLE_HOME
[grid@node1]$ export NEWGRIDHOME=/u01/app/19.25.0/grid
[grid@node1]$ cd $NEWGRIDHOME
[grid@node1]$ unzip -oq /u01/software/LINUX.X64_193000_grid_home.zip

    Optionally, I can use a golden image.

  4. I update OPatch to the latest version:

    [grid@node1]$ cd $NEWGRIDHOME
[grid@node1]$ rm -rf OPatch
[grid@node1]$ unzip -oq /u01/software/p6880880_190000_Linux-x86-64.zip

  5. Then, I check the Oracle Grid Infrastructure prerequisites. I am good to go if the check doesn’t write any error messages to the console:

    [grid@node1]$ export ORACLE_HOME=$NEWGRIDHOME
[grid@node1]$ $ORACLE_HOME/gridSetup.sh -executePrereqs -silent

  6. I want to apply the 19.25 Release Update while I install the GI home. To do that, I must extract the patch file:

     [grid@node1]$ cd /u01/software
 [grid@node1]$ unzip -oq p36916690_190000_Linux-x86-64.zip -d 36916690
    • The GI Release Update is a bundle patch consisting of:
      • OCW Release Update (patch 36917416)
      • Database Release Update (36912597)
      • ACFS Release Update (36917397)
      • Tomcat Release Update (36940756)
      • DBWLM Release Update (36758186)
    • I will apply all of them.

  7. Finally, I can install the new GI home:

    • The parameter -applyRU is the path to the OCW Release Update.
    • The parameter -applyOneOffs is a comma-separated list of the paths to each of the other Release Updates in the GI bundle patch.
    • The environment variable CLUSTER_NAME is the name of my Oracle Restart stack.
    [grid@node1]$ export ORACLE_BASE=/u01/app/grid
[grid@node1]$ export ORA_INVENTORY=/u01/app/oraInventory
[grid@node1]$ export ORACLE_HOME=$NEWGRIDHOME
[grid@node1]$ cd $ORACLE_HOME
[grid@node1]$ ./gridSetup.sh -ignorePrereq -waitforcompletion -silent \
   -applyRU /u01/software/36916690/36916690/36917416 \
   -applyOneOffs /u01/software/36916690/36916690/36912597,/u01/software/36916690/36916690/36917397,/u01/software/36916690/36916690/36940756,/u01/software/36916690/36916690/36758186 \ \   
   -responseFile $ORACLE_HOME/install/response/gridsetup.rsp \
   INVENTORY_LOCATION=$ORA_INVENTORY \
   ORACLE_BASE=$ORACLE_BASE \
   SELECTED_LANGUAGES=en \
   oracle.install.option=CRS_SWONLY \
   oracle.install.asm.OSDBA=asmdba \
   oracle.install.asm.OSOPER=asmoper \
   oracle.install.asm.OSASM=asmadmin \
   oracle.install.crs.config.ClusterConfiguration=STANDALONE \
   oracle.install.crs.config.configureAsExtendedCluster=false \oracle.install.crs.config.gpnp.configureGNS=false \
   oracle.install.crs.config.autoConfigureClusterNodeVIP=false
    • Although the script says so, I don’t run root.sh.
    • I install it in silent mode, but I could use the wizard instead.
    • You need to install the new GI home in a way that matches your environment.
    • For inspiration, you can check the response file used in the previous GI home on setting the various parameters.
    • If I have additional one-off patches to install, I add them to the comma-separated list.

2. Prepare a New Database Home

I can do this in advance. It doesn’t affect my current environment and doesn’t cause any downtime.

  1. I need to create a folder for the new database home. I must do this as oracle:

    [oracle@node1]$ export NEW_ORACLE_HOME=/u01/app/oracle/product/dbhome_1925
[oracle@node1]$ mkdir -p $NEW_ORACLE_HOME

  2. I extract the base release of Oracle Database into the new database home:

    [oracle@node1]$ cd $NEW_ORACLE_HOME
[oracle@node1]$ unzip -oq /u01/software/LINUX.X64_193000_db_home.zip

    Optionally, I can use a golden image.

  3. I update OPatch to the latest version:

    [oracle@node1]$ rm -rf OPatch
[oracle@node1]$ unzip -oq /u01/software/p6880880_190000_Linux-x86-64.zip

  4. I want to apply the 19.25 Database Release Update. In addition, I must also apply the OCW Release Update to the database home. I take those from the GI Release Update that I used earlier. In addition, I want to apply the OJVM and Data Pump bundle patches. Those I must extract.

    [oracle@node1]$ cd /u01/software
[oracle@node1]$ unzip -oq p36878697_190000_Linux-x86-64.zip -d 36878697
[oracle@node1]$ unzip -oq p37056207_1925000DBRU_Generic_1925.zip -d 37056207

  5. Then, I can install the new database home and apply the patches at the same time:

    • The parameter -applyRU is the path to the Database Release Update.
    • The parameter -applyOneOffs is a comma-separated list of the paths to the OCW Release Update plus OJVM and Data Pump bundle patches.
    [oracle@node1]$ export ORACLE_BASE=/u01/app/grid
[oracle@node1]$ export ORA_INVENTORY=/u01/app/oraInventory
[oracle@node1]$ export OLD_ORACLE_HOME=$ORACLE_HOME
[oracle@node1]$ export ORACLE_HOME=$NEW_ORACLE_HOME
[oracle@node1]$ cd $ORACLE_HOME
[oracle@node1]$ ./runInstaller -ignorePrereqFailure -waitforcompletion -silent \
     -responseFile $ORACLE_HOME/install/response/db_install.rsp \
     -applyRU /u01/software/36916690/36916690/36912597 \
     -applyOneOffs /u01/software/36916690/36916690/36917416,/u01/software/36878697/36878697,/u01/software/37056207/37056207 \
     oracle.install.option=INSTALL_DB_SWONLY \
     UNIX_GROUP_NAME=oinstall \
     INVENTORY_LOCATION=$ORA_INVENTORY \
     SELECTED_LANGUAGES=en \
     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 \
     SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
     DECLINE_SECURITY_UPDATES=true
    • I install it in silent mode, but I could use the wizard instead.
    • You need to install the new database home in a way that matches your environment.
    • For inspiration, you can check the response file used in the previous database home on setting the various parameters.
    • If I have additional one-off patches to install, I add them to the comma-separated list.

  6. I run the database root script:

    [root@node1]$ $NEW_ORACLE_HOME/root.sh
    • I run just the database root script. Not the GI root script.

3. Prepare Database

I can do this in advance. It doesn’t affect my current environment and doesn’t cause any downtime.

I will move the database into a new Oracle home, so I need to ensure the database configuration files are either outside the Oracle home or move them to the new Oracle home.

  1. I verify that my SP file and password file are stored in ASM – or at least outside the Oracle home:
    [oracle@node1]$ export ORACLE_HOME=$OLD_ORACLE_HOME
[oracle@node1]$ srvctl config database -db $ORACLE_UNQNAME | grep file
    • If the files are stored in the dbs folder, I copy them to new Oracle home.
  2. I copy tnsnames.ora and sqlnet.ora to the new Oracle home:
    [oracle@node1]$ cp $OLD_ORACLE_HOME/network/admin/sqlnet.ora $NEW_ORACLE_HOME/network/admin
[oracle@node1]$ cp $OLD_ORACLE_HOME/network/admin/tnsnames.ora $NEW_ORACLE_HOME/network/admin
  3. I take care of any other configuration files in the Oracle home.
  4. I modify the database so it starts in the new Oracle home on the next restart.
    [oracle@node1]$ srvctl modify database -d $ORACLE_UNQNAME -o $NEW_ORACLE_HOME

4. Switch to the New GI and Database Homes

Now, I can complete the patching process by switching to the new Oracle homes.

  1. I connect as root and start the switch:

    [root@node1]$ export ORACLE_HOME=/u01/app/19.25.0/grid
[root@node1]$ $ORACLE_HOME/rdbms/install/rootadd_rdbms.sh
[root@node1]$ $ORACLE_HOME/crs/install/roothas.sh -prepatch -dstcrshome $ORACLE_HOME

  2. Downtime starts now!

  3. Then, I complete the switch.

    • This step stops the entire GI stack, including resources it manages (databases, listener, etc.).
    • Everything is restarted in the new Oracle homes.
    [root@node1]$ $ORACLE_HOME/crs/install/roothas.sh -postpatch -dstcrshome $ORACLE_HOME

  4. Downtime ends now. Users may connect to the database.

  5. As grid, I update the inventory, so the new GI home is registered as the active one:

    [grid@node1]$ export OLD_ORACLE_HOME=/u01/app/19.24.0/grid
[grid@node1]$ export NEW_ORACLE_HOME=/u01/app/19.25.0/grid
[grid@node1]$ $NEW_ORACLE_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=$NEW_ORACLE_HOME CRS=TRUE
[grid@node1]$ $OLD_ORACLE_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=$OLD_ORACLE_HOME CRS=FALSE

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

  7. As oracle, I update any profiles (e.g., .bash_profile) and other scripts referring to the database home.

5. Complete Patching

  1. I complete patching of the database by running Datapatch (ensure the environment is set correctly):
    [oracle@node1]$ env | grep ORA
[oracle@node1]$ $ORACLE_HOME/OPatch/datapatch

Most likely, there are other changes that you need to make in your own environment:

  • Update Enterprise Manager registration
  • Upgrade RMAN catalog
  • Update other scripts
  • Update /etc/oratab

That’s it! I have now patched my Oracle Restart deployment.

Happy Patching!

Appendix

Deinstall

In the future, I should remove the old Oracle homes. I use the deinstall tool in the respective Oracle homes.

I would recommend waiting a week or two until I’m confident the new Release Updates are fine.

CRS-0245: User doesn’t have enough privilege to perform the operation

  • If you get the following error:
    [oracle@node1]$ srvctl modify database -d $ORACLE_UNQNAME -o $NEW_ORACLE_HOME
PRCD-1163 : Failed to modify database DB19
PRCR-1071 : Failed to register or update resource ora.db19.db
CRS-0245:  User doesn't have enough privilege to perform the operation
  • Be sure to include patch 29326865 in GI and database home.
  • Run the srvctl modify database command as grid instead.
  • Be sure that the Oracle user is still set to oracle after running the command as grid:
    [oracle@node1]$ srvctl config database -db $ORACLE_UNQNAME | grep user

Rollback

If you need to roll back, you more or less reverse the process. The switch home method works to a newer and lower patch level.

OCW Release Update

Thanks to Jan for commenting on the blog post. The initial version didn’t include the OCW Release Update into the database home, which is needed when the database is managed by Grid Infrastructure in any way.

Incorrect Information in ocr.loc

In the ocr.loc file for Oracle Restart, only the local_only property is used. All other properties can be ignored (like ocrconfig_loc).

In Oracle Database 23ai, the file will be cleaner in Oracle Restart. But for Oracle Database 19c there will be these superfluous properties.

Further Reading

Other Blog Posts in This Series

How to Upgrade Oracle Database with Username and Password

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

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

Set-Up

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

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

Let me check that it is disabled:

$ sqlplus / as sysdba

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

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

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

Upgrade Without Operating System Authentication

  1. I ensure that my AutoUpgrade is the latest version, or at least version 24.7.241021:
$ java -jar autoupgrade.jar -version
build.version 24.7.241021
build.date 2024/10/21 11:16:20 -0400
build.hash babf5a631
build.hash_date 2024/10/18 18:36:27 -0400
build.supported_target_versions 12.2,18,19,21,23
build.type production
build.label (HEAD, tag: v24.7, origin/stable_devel, stable_devel)
build.MOS_NOTE 2485457.1
build.MOS_LINK https://support.oracle.com/epmos/faces/DocumentDisplay?id=2485457.1
  1. This is my AutoUpgrade config file. There’s nothing special in it, except for global.keystore which tells AutoUpgrade where to store its keystore. AutoUpgrade uses the keystore to keep your password safe until it is needed.
$ cat UPGR.cfg
global.autoupg_log_dir=/home/oracle/logs/autoupgrade-UPGR
global.keystore=/home/oracle/autoupgrade-keystore
upg1.source_home=/u01/app/oracle/product/19
upg1.target_home=/u01/app/oracle/product/23
upg1.sid=CDB19
upg1.timezone_upg=NO
  1. Now, I’m starting AutoUpgrade in -load_password mode. This is the first time I use it, so AutoUpgrade prompts for a password that it can use to encrypt the keystore:
$ java -jar autoupgrade.jar -config UPGR.cfg -load_password
Processing config file ...

Starting AutoUpgrade Password Loader - Type help for available options
Creating new AutoUpgrade keystore - Password required
Enter password:
Enter password again:
AutoUpgrade keystore was successfully created
  1. To enter the username and password for my database, I change the group to PWD:’
PWD> group PWD
Group [PWD] is already active
  1. Then, I can enter my username and password for my database (CDB19). AutoUpgrade validates the credentials, so you better make sure they work at this point:
PWD> add CDB19 -user SYS
Enter your secret/Password:
Re-enter your secret/Password:
Database SID: cdb19              User: SYS
  1. I save the keystore and convert it into an auto-login keystore, so I don’t have to enter the keystore password every time I use AutoUpgrade:
PWD> save
Convert the AutoUpgrade keystore to auto-login [YES|NO] ? yes
  1. Exit the keystore.
PWD> exit

AutoUpgrade Password Loader finished - Exiting AutoUpgrade
  1. Finally, I start the upgrade by starting AutoUpgrade in deploy mode:
java -jar autoupgrade.jar -config UPGR.cfg -mode deploy

That’s it!

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

SQLNET.ORA

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

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

Happy Upgrading!