Patching Oracle Software With Multiple Homes on Server Fails

We recently ran into an issue patching an Oracle home (the directory holding database executables and other system files):

RawInventory gets null OracleHomeInfo

The above occurred for many reasons.  First, Oracle software was installed with a vendor package on one mount point.  Then, that same software was copied to another location.  Finally, the Oracle Enterprise Manager agent was added.  

The problem happens if any of these steps have incorrect Oracle home settings at the time they were done, or when the correct Oracle tools are not used.  The tools will always report the installation to the inventory pointer XML file (inventory.xml).  

When this is done, the contents of that file could be wrong, or there could be multiple copies.  Each of these situations can contribute to future difficulties with patching.  

Because this can happen so frequently, Oracle finally developed a workaround, via a script:

How to Change Central Inventory Directory Location by Using attachHome Command (Doc ID 2876813.1)

You will, of course, need an Oracle Support account to access the above. 

In some cases, even that script will not work without modification.  

The contents should be checked to assure it will attach the correct Oracle home, and it must be modified if not.  

Here is another fine blog with the details: 

https://doyensys.com/blogs/lsinventorysession-failed-rawinventory-gets-null-oraclehomeinfo/

In our case, the OHOME variable was referencing the directory where the agent was installed, since that was the last operation performed.  This needed to be changed.

Here is what the script will produce when executed properly:

./attachHome.sh 

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 17504 MB    Passed

The inventory pointer is located at /etc/oraInst.loc

You can find the log of this install session at:

 /u01/app/oracle/oraInventory/logs/AttachHome2022-12-29_10-16-30AM.log

'AttachHome' was successful.

Once above is done, verification can be done with $ORACLE_HOME/OPatch/opatch lspatches:

./opatch lspatches

34133642;Database Release Update : 19.16.0.0.220719 (34133642)

29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.

 

 On the Issue Of Handedness

People often ask, after watching me play tennis (which I love), or occasionally darts or billiards:

Are you right-handed or left-handed?  My reply: "Yes!"  

For quite some time now - I'd guess since I was a child, even before I can remember - I do believe this question has come up from time to time.  

I remember my mother bragging that my kindergarten teacher would explain that one day I would finger-paint left-handed, then the next day practice writing my name right-handed.  Then, I got to first grade, and went to a catholic school where being left-handed was considered an inconvenience - all students who wrote lefty were given separate desks on the far side of the room.  I think it was at that point, being quite shy, I decided to always write right-handed.  

This same scenario played out again in sports.  At this point, I was playing baseball and throwing right-handed, but I wanted to bat left-handed.  My Catholic Youth Organization coach said: "If you throw right-handed, you should bat right-handed."  So that is what I did - in organized sports.  I continued to switch hands, seeming to naturally trend left-handed in any sport requiring fine motor skills.  This would go for archery, shooting, darts, softball and tennis (sort of - I served right-handed).   

As the years passed, I grew into a husky kid who could hit a baseball quite well from either side of the plate, but when I reached the age of 13, a stigmatism developed, causing my eyesight to decline.  Wearing glasses was never easy for me while playing sports, so I stopped playing baseball.  Later, when I discovered tennis (at about age 21), wearing glasses was no big deal - except I found myself switching between lefty and righty forehands naturally (an instructor told me I could not do this - then watched for a while, and said "I guess you can?").  

As time went on, and I became an adult, I noticed I did a great many things left-handed, but I'd just never noticed:  cut meat with a knife, sweep with a broom, shovel dirt, deal cards and many more.  I am also a self-taught guitarist of some ability (having played in bands), and of course, I play left-handed!

When I tried to switch and do most of these things with the other hand, I found I probably could, but it was not nearly as comfortable.  People who do some things with one hand, and some with the other are are said to have "mixed laterality" or are cross-dominant.  This was once considered to be a disability by the medical profession, but this was likely due to bias or anecdotal information.  

In any event, I am quite happy with my unique "aflliction".  Less than 1% of the population has it, and in some sports (such as baseball), it can be a distinct advantage.  

Postgresql Upgrade 12>>>14

As part of a migration to new hardware and a new OS, today we are also upgrading postgresql.  

While it is normally dangerous to mix/match HW/SW upgrades along with DB Engine changes, the client has overgrown their old systems, and this is their last chance before a year-end freeze to upgrade and move to a supported operating system, so we've agreed to help.  

One of the first things that must be done on a new host is to get the Postgres binaries installed, which is done thusly:

dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

/usr/pgsql-14/bin/postgresql-14-setup initdb

systemctl enable --now postgresql-14

systemctl status postgresql-14 

Yes, it can be that simple on a new system!  

This blog post is not really about installation, there are other fine blog posts that go into explicit details of this activity, or you can check the official documentation and download site at postgresql.org.  

Remember, Postgresql is community supported (unless an enterprise support license is obtained), so you should always get installation media from a reputable source, and for the appropriate platform and OS.

One of the first things to do when upgrading is to assure that any custom configuration parameters are carried over to the new system.  Default parameters can change their value from release to release, and new ones are added and depricated.  We will focus on some easy ways to check and verify postgresql.conf and pg_hba.conf to assure they are consistent (where possible) and appropriate for the new environment.  

To check on set values in the existing (Version 12) postgresql.conf, you can run the following command:

[postgres@postgresb data]$ grep -v "#" postgresql.conf | grep -v '^[[:space:]]*$'

max_wal_size = 5GB

archive_command = '/bin/true'

pg_stat_statements.track = all

shared_preload_libraries = 'pg_stat_statements'

track_activity_query_size = 2048

log_timezone = 'America/New_York'

datestyle = 'iso, mdy'

timezone = 'America/New_York'

default_text_search_config = 'pg_catalog.english'

Grep (globally search for a regular expression and print matching lines) is a very powerful unix/linux command.  There are variations for it in other OS, but the goal of the above set of commands was to remove commented lines (#) and blank lines, as the delivered file is full of them.  A reference blog post for this command and more can be found here. 

Here is the comparison file for the target (version 14) system:

[postgres@rh-postgresa data]$ grep -v "#" postgresql.conf | grep -v '^[[:space:]]*$'

max_wal_size = 1GB

min_wal_size = 80MB

log_timezone = 'America/New_York'

datestyle = 'iso, mdy'

timezone = 'America/New_York'

default_text_search_config = 'pg_catalog.english'

As we can see, there are a few inconsistencies.  We cannot always be sure, without user documentation, why these settings differ, but the safest thing to do is to make them match, so:

Use vi (or another editor) to make the values match.

It is always best to back up postgresql.conf to another location (not in this same directory) before making any changes.  

Then, reload postgres and check results:

systemctl stop postgresql

systemctl start postgresql

- or - 

systemctl reload postgresql

- or - 

pg_ctl reload -D your-pg-data-directory

In this specific case  pg_stat_statements was an "extension" to the Postgresql binaries, so had to be installed separately. See this blog post as to how that is done.  

This was a long one.   I will follow up with more details on how the implementation turned out.

Patching Oracle Databases In OCI DBaaS

 Oracle has numerous database products in their cloud environment, and one that sees little attention is actually a cool product for small to medium workloads.  It is popularly called "DBaaS", which stands for Database-as-a-Service.  

DBaaS is a similar offering to Amazon's RDS (relational database service) in that it presents a complete and working managed database and is very simple to provision and build from the OCI (Oracle Cloud Interface) management console.  

A DBaaS database can be built with standard linux file systems, but there are also options to build it with Grid Infrastructure and ASM for more flexible disk management.   For more complete and detailed information, I refer you to the complete documentation.

Once built and provisioned, the Oracle database is yours to manage and maintain.  Most routine administrative tasks can be performed from the console.  Nightly backups are automatically scheduled when the system is provisioned.  This makes administration and management much less of a chore.  

However, certain tasks, as well as tuning and troubleshooting, can involve looking under the covers.  This post centers around one area where it may be necessary to troubleshoot or run patching for databases that are unable to be patched from the console user interface.  

DBCLI is a tool that can be used for a great many things in DBaaS. 

It is a tool name and an acronym for "data base command line interface".  It is only used on virtual machine systems with DBaaS.  

Ironically there is another tool, called "dbaascli" which is only used for Exadata Cloud systems.

dbcli is necessary to be used for patching DBaaS databases and systems in certain cases.  One such case is with a database supporting Enterprise Business Suite (EBS).  Another, of course, is when the console patching fails.  

To patch a database, there are two fairly simple commands to run:

dbcli update-dbhome -i dbhomeid --version 19.16.0.0.0 --precheck

dbcli update-dbhome -i dbhomeid --version 19.16.0.0.0 

A precheck is first necessary, as when doing traditional patching with the opatch utility, because it checks if the in-place Oracle home is in good condition, that the patch is available on the system and is complete, and that the system itself is ready to accept the patch.  

(this saves over half-a-dozen manual checks in opatch)!

The patching itself does numerous things, including shutting down the connected database and listener, which normally would be done manually by the DBA.  While this is a timesaver, please note it is a potentially troublesome issue if applications or sessions are connected, as they will be terminated!

I am making this post as simple as possible, but if you need to obtain the dbhomeid for your server, there is a command for that as well:

dbcli list-dbhomes 

dbcli list-dbhomes

ID                                       Name                 DB Version                               Home Location                                 Status    

---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------

f5c954e1-9f8c-4470-b994-d5e29ca97399     OraDB19000_home1     19.15.0.0.0                              /u01/app/oracle/product/19.0.0.0/dbhome_1     Configured

All dbcli commands must be run as root.  If the command itself is not found, you should open a support request with Oracle, because it is an essential part of the tooling for a provisioned DBaaS system.  In fact, Oracle uses it in the background to take backups, verify system integrity and monitoring.  

Were the dbcli command itself to fail, refer to /opt/oracle/dcs/log directory for logs specific to the tool, such as dcscli.0.0.log and dcs-agent.0.0.log .  These will usually contain detailed information as to why the command failed.  If not, they will be required by Oracle support to do troubleshooting!

For more detailed information specific to this utility, please refer to the dbcli documentation.  It might also be wise to check this before you begin, as these tools and products are constantly evolving, and some of the syntax here might change over time.  There is also information about how to repair and update the version for this tool within the above documentation.  

Prerequisites:

This post assumes you are running the latest version of the dbcli utilty.  it must also be patched from time to time.  This is a topic for a future post. 

Grid Infrastructure and ASM must also be patched if your DBaaS VM is built using these features.  I will cover this in a future post as well.  

If your DBaaS system is a RAC cluster, patching is more or less the same.  In fact, the patches are run, by detault, in a "rolling upgrade" fashion, usually without downtime.  This can be overriden, but that is usally a bad idea.  I will look into posting on this in a future blog.  

That's all for now.  Let me know in the comments if you have any questions or have used this tool.  I would love to hear from you!

SQL LISTAGG Function Used In An Interesting Way

 I am working on a migration of a database from on-premise to the cloud.  I am using a scripting tool to aid in the migration, and one of the required parameters is as follows:

"Supply a list of tablespaces you wish to migrate, delimited by commas.  All the names must be on a single line.  Additional lines will be ignored."

Well, jiminy cricket, how in the world am I gonna do that?  

And there are quite a number of tablespaces.  Will they even fit?

Luckily, this utility runs in linux, and lines can be of extended length.

I did not know how to do this on the fly, but I looked around, and came up with this query, which also contains some where clauses to qualify what was actually needed (we will not need to migrate SYSTEM, TEMP, or UNDO tablespaces).  

The listagg function is an ansi-standard function that was actually first seen in Microsoft SQLServer, so I am going to guess they pushed it forward to the ANSI SQL standards committee, and I am glad they did so.  

Here is my implementation, in Oracle, in case it can come in handy:

select listagg(tablespace_name,',') within group (order by tablespace_name) item_name_list

   from dba_tablespaces

   where contents = 'PERMANENT'

     and tablespace_name not like 'SYS%';