Author: Daniel Overby Hansen

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

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!

What Is Data Pump Import Doing In PROCACT_SCHEMA

At a recent event, a customer asked me a question.

I’m doing a Data Pump import, and processing PROCACT_SCHEMA takes a lot of time. What’s going on?

I love meeting our customers and hearing about these cases. So, let’s dig into it.

Situation

Here is an extract from the Data Pump import log:

08-SEP-24 18:10:18.604: W-4 Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
08-SEP-24 22:08:24.804: W-6      Completed 78693 PROCACT_SCHEMA objects in 14283 seconds
08-SEP-24 22:08:24.805: W-6      Completed by worker 1 78692 PROCACT_SCHEMA objects in 14283 seconds
08-SEP-24 22:08:24.826: W-6 Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER

A few observations:

  • Seeing a customer using logtime=all and metrics=yes in their Data Pump jobs is great. It adds valuable diagnostic information to the log file.
  • Almost four hours on PROCACT_SCHEMA.
  • Almost 80.000 schemas – that’s a lot.
  • Only one active worker.

Tracing

The customer sent me a 10046 trace for that period. They had the suspicion that Data Pump was doing something for each schema. I had the same thought, so I sorted the trace file:

sort CDB01_dw00_54345.trc > sort.txt

Using my favorite text editor, I could scroll through the content and find this repeating call:

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'...', inst_scn=>'...');COMMIT; END;

There’s not much public information about this package, but it is a package used during import to move logical replication settings – used by Oracle Streams and Oracle GoldenGate.

Oracle Streams

I’m not a big Streams expert, but I do know that it is desupported in Oracle Database 19c. But could there be some leftovers in the dictionary that caused Data Pump to move the configuration?

I asked the customer to query many of the DBA_STREAMS_* views in the database. One of them returned a lot of rows:

select count(*) from dba_streams_columns;

  COUNT(*)
----------
     72636

Solution

The customer asked for advice on using the Data Pump import parameter STREAMS_CONFIGURATION=NO. Judging from the parameter name, it sounds like a good idea, but the documentation reads:

… STREAMS_CONFIGURATION parameter specifies whether to import any GoldenGate Replication metadata that may be present in the export dump file.

But it states GoldenGate metadata – not Streams. But look in the 18c documentation:

… import any Streams metadata that may be present in the export dump file.

Streams and GoldenGate share a lot of the same architecture in the database because they serve the same purpose: Data replication. That’s why the parameter still exists even if Streams is desupported.

The customer didn’t use GoldenGate, so they decided to exclude the replication metadata during import:

impdp ... streams_configuration=no

The PROCACT_SCHEMA part of the import went from almost four hours to just 30 seconds!

Final Words

In this case, a full import, the issue happened during DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA. It could happen in a schema import as well. In that case, the phase would be SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA.

What is PROCACT? It is short for procedural actions. That phase handles things in the database that are not real objects nevertheless something you want to include. Data Pump calls out to other parts of the code to get the job done. That’s also why you typically see one worker active. I’d like to write a separate blog post about that one day.

A full export/import is convenient, but it includes everything, including the things you didn’t know you had in the database – leftovers or garbage data. A schema export/import is a cleaner approach but would still be affected by the above issue. If you’re short on time, consider a schema export/import and include just the objects that you really need. Something like:

impdp ... schemas=abc,def include=table,index,constraint,trigger,package

 

Happy data-pumping!

An Important Milestone

This is the 200th blog post on dohdatabase.com.

Take that, Sinan. Here’s my blog post number 200!

For a while, my good friend Sinan and I have been racing to the 200th blog post.

He has more page hits than me, but luckily the bet was on number of posts. Sinan, you owe me one.

My prize

The History

I started at Oracle almost five years ago, and I was encouraged by my boss, Mike Dietrich, to start a blog.

With 200 blog posts and almost five years at Oracle, that’s almost a blog post a week. I’m proud of that. Page hits doubled last year and this year will see some good growth as well.

What does that mean? I’m helping people!

I like helping people and it is my job. But blogging is some times hard – so I need fuel. That fuel is feedback.

No More Navel-gazing

For a content creator like me, feedback is essential for many reasons:

  • It is my fuel. Knowing that my blog posts help you is what helps me to put in the extra effort it takes to blog.
  • Your comments make my blog posts even better. Often, I’ve re-written blog posts based on your feedback.
  • Your tricky questions make me learn new learn new things. I’m above to deep-dive into Oracle Restart because you’re asking for it.
  • Your ideas help me shape the requirements for the next features in Oracle Database. The next version of AutoUpgrade can upgrade the RMAN catalog as part of a database upgrade – because a user gave me that good idea.
  • Your complaints help me understand how we can improve our product. Today, a user reported an error to us, which led to a new pre-check in AutoUpgrade.
  • Your likes are a quick (but sometimes needed) dosis feel-good.
  • Your re-posts and comments on social media boost my reach and enable me to help even more people in the Oracle community.

Your Feedback Matters!

I first got in touch with my current boss, Mike, through a comment I left on his blog. This led to cooperation between my former employer and Oracle. Our cooperation turned into beta and reference projects and peaked – for me personally – when I presented with Mike and Roy at Oracle OpenWorld.

Oracle OpenWorld presentation with Mike and Roy

You never know what your feedback will end up with.

Thanks for staying with me for the first 200 blog posts. Here’s to the next 200 blog posts!

Your Feedback Matters!

How to Patch Oracle Database With One Command

With the latest release of Oracle AutoUpgrade, patching an Oracle Database is much easier. Once I’ve configured AutoUpgrade, it takes just one command. That includes finding and downloading the right patches and creating a new Oracle home.

Let’s see how I can do it.

Configuration

  1. Get the latest version of AutoUpgrade:
    wget https://download.oracle.com/otn-pub/otn_software/autoupgrade.jar
  2. I need to create a config file:
    • global.global_log_dir is the logging and working directory of AutoUpgrade.
    • global.keystore is where AutoUpgrade can create a software keystore that stores my My Oracle Support credentials.
    • sid and source_home describe the database that I want to patch.
    • target_home is the location of the new Oracle home that AutoUpgrade creates for me.
    • folder is the location where AutoUpgrade can find or download patches.
    • patch tells AutoUpgrade to apply the latest Release Update including the OJVM and Data Pump bundle patches, plus update OPatch.
global.global_log_dir=/home/oracle/autoupgrade-patching/log
global.keystore=/home/oracle/autoupgrade-patching/keystore
patch1.source_home=/u01/app/oracle/product/19
patch1.target_home=/u01/app/oracle/product/19_25_0
patch1.sid=FTEX
patch1.folder=/home/oracle/autoupgrade-patching/patch
patch1.patch=RECOMMENDED
  1. Currently, AutoUpgrade can’t download the Oracle Database 19c base release (LINUX.X64_193000_db_home.zip). I must do that manually and place it in the folder specified by the config file entry folder. I expect the next version to handle it automatically.
$ cd /home/oracle/autoupgrade-patching/patch
$ ls -l
total 2987996
-rw-r--r--. 1 oracle oinstall 3059705302 Mar 18  2020 LINUX.X64_193000_db_home.zip
  1. I need to load my My Oracle Support credentials into the AutoUpgrade keystore so AutoUpgrade can use them to download patches. The first time AutoUpgrade creates the keystore, and I must provide a keystore password:
$ java -jar autoupgrade.jar -config FTEX.cfg -patch -load_password
Processing config file ...

Starting AutoUpgrade Patching Password Loader - Type help for available options
Creating new AutoUpgrade Patching keystore - Password required
Enter password:
Enter password again:
AutoUpgrade Patching keystore was successfully created
  1. I add my credentials. This is the username and password that I use to connect to My Oracle Support:
MOS> add -user daniel.overby.hansen@oracle.com
Enter your secret/Password:
Re-enter your secret/Password:
  1. I save the keystore and convert it to an auto-login keystore:
MOS> save
Convert the AutoUpgrade Patching keystore to auto-login [YES|NO] ? YES
  1. I exit the keystore.
MOS> exit

AutoUpgrade Patching Password Loader finished - Exiting AutoUpgrade Patching

Patch

With one command, I will patch my Oracle Database:

$ java -jar autoupgrade.jar -config FTEX.cfg -patch -mode deploy
AutoUpgrade Patching 24.7.241021 launched with default internal options
Processing config file ...
Loading AutoUpgrade Patching keystore
AutoUpgrade Patching keystore was successfully loaded

Connected to MOS - Searching for specified patches

------------------------------------------------------------
Downloading files to /home/oracle/autoupgrade-patching/patch
------------------------------------------------------------
DATABASE RELEASE UPDATE 19.25.0.0.0
    File: p36912597_190000_Linux-x86-64.zip - VALIDATED

DATAPUMP BUNDLE PATCH 19.25.0.0.0
    File: p37056207_1925000DBRU_Generic.zip - VALIDATED

OJVM RELEASE UPDATE 19.25.0.0.0
    File: p36878697_190000_Linux-x86-64.zip - VALIDATED

OPatch 12.2.0.1.44 for DB 19.0.0.0.0 (Oct 2024)
    File: p6880880_190000_Linux-x86-64.zip - VALIDATED
------------------------------------------------------------

+-----------------------------------------+
| Starting AutoUpgrade Patching execution |
+-----------------------------------------+
1 Non-CDB(s) will be processed
Type 'help' to list console commands
patch> Job 100 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]
Jobs restored                  [0]
Jobs pending                   [0]

---- Drop GRP at your convenience once you consider it is no longer needed ----
Drop GRP from FTEX: drop restore point AU_PATCHING_9212_FTEX1921000


Please check the summary report at:
/home/oracle/autoupgrade-patching/log/cfgtoollogs/patch/auto/status/status.html
/home/oracle/autoupgrade-patching/log/cfgtoollogs/patch/auto/status/status.log

That’s it! My database is now running on Oracle Database 19.25.0.

Fine Print

AutoUpgrade does out-of-place patching according to our recommendations. It will use a brand-new Oracle home, and copies all configuration files from the old to the new Oracle home.

AutoUpgrade currently requires:

  • Oracle Database 19c
  • Single instance database (non-CDB or entire CDB)

If you need more details, please check out the webinar One-Button Patching with AutoUpgrade. The slide deck has additional details.

What About

This is the first release, and naturally, it comes with restrictions. We’re working hard behind the scenes to lift those requirements.

Will AutoUpgrade support other platforms? Yes. AutoUpgrade supports most of the platforms where you run Oracle Database, including Windows, AIX and SPARCH Solaris.

What about in-place patching, using cloned Oracle homes, and support for gold images? It’s in the plans.

Will you support other Oracle Database releases? Yes – anything newer than Oracle Database 19c

What about Data Guard and RAC? You can use AutoUpgrade for Data Guard. RAC is in the plans.

What about Grid Infrastructure? We’re focusing on Oracle Database. You should have a look at Oracle Fleet Patching and Provisioning.

What about … We’re looking for new, great ideas. Please create an enhancement request in My Oracle Support and send me the number. I’ll check it and add it to our backlog. Feel free to also send me an email with your suggestion.

Happy Patching!