HOW TO: Cloud Exadata Timezone Change

 A client recently realized that over the years, they had timezone inconsistencies across their database systems.  They had more or less adopted and learned to live with them.  While this can be done, complications can arise when using Data-guard, or doing timestamp recovery.  

As they were migrating to Oracle Cloud Infrastructure and CloudExa, I explained this would be an excellent time to make all of their systems consistent.  

They had already built out their test system using an inherited timezone, so we had to figure out how to change it.  There were no cloud-specific documents in the support area on this issue, so we decided to open a Support Request with Oracle.  This is not always known, but an "SR" can be opened to document or verify a procedure when the platform is newer and documents do not specifically mention it.  

It took a few days, but after some research and re-checking with development, a support analyst said that it was believed that an on-premise document and procedure could be followed, but he will leave the request open should we run into problems.  This document link is HERE.  

Unfortunately, while very close to our situation, we found an anamoly or difference on Cloud Exadata that made it impossible to follow verbatim.  It required the existence of the file: 

/etc/sysconfig/clock 

Our system did not have this file on either of it's two nodes!

So we notified Oracle.  Once again, they went back to cloud development to seek direction due to this unplanned situation.  Development was unaware that cloud systems could be delivered without this file, and they considered it a bug and will be opening one to address this.  

So now we needed a new plan.  Between development, the analyst and ourselves, we checked on the format of the file (it was actually very simple).  it consists of this verbiage for our case:

ZONE="America/New_York"  Oracle support did not give us this exact syntax, however, there is a group of files and directories with examples in /usr/share/zoneinfo.  Under subdirectory "America" is a file named "New_York", and that exact set of characters was consistent with the example in the documentation referenced above.  

The system is currently running in Mountain Time, which can be seen by executing this command:

$ date

Tue Jul 25 07:57:53 MDT 2023

So, nothing is changed yet.  We just needed to verify how to proceed.  After arranging for a complete database outage with interested parties, the next step was to shutdown all services in the Grid Infrastructure stack as user root:

$ crsctl stop crs

Messages returned:

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'exa-skyze1'

.........

CRS-4133: Oracle High Availability Services has been stopped.

Next we should verify everything is stopped:

$ crsctl stat res -t

Messages returned:

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4000: Command Status failed, or completed with errors.

Above errors are expected results - the CRS is not running!

Now onto the timezone specific changes:

As root, the file /etc/sysconfig/clock was created with the "ZONE=America/New_York":

[root@exa ~]# date

Tue Jul 25 07:38:51 MDT 2023

[root@exa- ~]# vi /etc/sysconfig/clock

[root@exa ~]# date

Tue Jul 25 07:39:31 MDT 2023

[root@exa- ~]# cat /etc/sysconfig/clock

ZONE="America/New_York"

(please note full system name is obfuscated to maintain client confidentiality).  

This next command will actually change the timezone on the nodes:

ln -sf /usr/share/zoneinfo/America/New_York /etc/localtime

Now for the results!

[root@exa ~]]# date

Tue Jul 25 09:45:14 EDT 2023

Above steps must be repeated on other node(s).  

Once both nodes show the correct local time, we can restart services:

# crsctl start crs

CRS-4123: Oracle High Availability Services has been started.

(We had to wait several minutes until all services were restared).  

Now the OS and all services are running on the new timezone.  

However, when we logged into the oracle account and the databases, we found the task was still incomplete!!!

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 25 07:54:59 2023

Version 19.18.0.0.0

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

Connected to:

Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production

Version 19.18.0.0.0

SQL> select systimestamp from dual;

SYSTIMESTAMP

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

25-JUL-23 07.55.08.703743 AM -06:00

The above time is still in the Mountain Timezone.

What could cause this?  The OS shows the correct time.  

Looking at the environment for the "oracle" user account discloses the reason:

cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

export PATH

export TZ=US/Mountain

At this point, we realize the database is only reflecting a local environment variable setting, so we modify the TZ attribute to "US/Eastern" with the desired result.

$ vi .bash_profile

[oracle@exa ~]$ date

Tue Jul 25 07:57:53 MDT 2023

[oracle@exa ~]$ source .bash_profile

Enter DB Name (TOOLUAT/ACMUAT/FMUAT): TOOLUAT

[oracle@exa1 ~]$ date

Tue Jul 25 09:58:03 EDT 2023

[oracle@exa ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 25 09:58:10 2023

Version 19.18.0.0.0

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

Connected to:

Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production

Version 19.18.0.0.0

SQL> select systimestamp from dual;

SYSTIMESTAMP

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

25-JUL-23 09.58.16.964305 AM -04:00

So as we can see in this vivid example, Oracle Support can only help so much when you have a process change.  They do not know about localized information for your environment.  They can only provide general guidelines.  In our case, we had to look at several configurable attributes to complete the effort.  

So far, testing with the timezone change is going well.  We may still run into an issue or two with the data and backups.  Time will tell, but at least, going forward, all of the systems will be running the same.  

This is a good argument for running all systems in the UTC (greenwich mean time) zone.  However, that would have required massive application changes and procedural adjustments for this particular system.  

PostgreSQL vs. Oracle SQL

 I once wrote a paper called "Postgres Goofiness" to help professionals familiar with Oracle become familiar with the quirks and idiosyncrasies of the Postgres implementation of SQL.  To be fair, and remove all bias, some of the Postgres implementation may be more ANSI-standard at times than Oracle, but not always (ANSI is a governing body that approves valid SQL syntax).  Still, Oracle (and Microsoft SQL Server and IBM DB2) are certainly still more prevalent, and most folks are far more familiar with them.  

Here is a less-than-comprehensive list.  Perhaps you can think of some more and comment.  I picked these because they immediately stand out and are often quite different and less than intuitive.

Compared to Oracle, Postgres SQL is quite similar, and is based upon ANSI standards which have been around for 40+ years.

However, the implementations of the standard are often curiously different, especially to an Oracle professional.  

For example:

1 - The LIMIT clause.

Select * from huge_table limit 100:  100 rows are returned in no particular order.  ROWNUM virtual column concept from Oracle does not exist.

More details on this clause and it's variations in this excellent POST.  

2 - Query Parallelism 

3 - Create index abx_index on schema name.abx (column_name);  - Notice schema_name is not specified for the index?  

It is assumed the same as the table.  Specifying it generates an error!

4 - SELECT clause without FROM.   

Select version();  

Postgres versions are (roughly) moved up a notch yearly.  

Postgres 9 was a longstanding, stable release, much like Oracle 11g.  

Companies are finding it hard to justify upgrades.

Security vulnerabilities are numerous with 9, and no patches available.

5 - SYSDATE becomes NOW?

master=> select now();

              now              

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

 2022-06-26 13:16:29.143339+00

(1 row)

5a A more conventional syntax:

master=> select current_date;

 current_date 

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

 2022-08-03

(1 row)

5b. 

master=> select current_time;

    current_time    

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

 02:07:19.259544+00

(1 row)

5c.

master=> select 6;

6

----------

6

Above is completely off-the-rails illegal in Oracle but will work in SQLServer!  

A more detailed post on this issue:  SQL Without FROM?

6 - backslash commands replace many queries in other SQL flavors.  

Examples:

\q.   Quits “psql”

\d    Same as describe in sqlplus

\timing same as “set timing on” - not too bad!  I can remember that!!!

See the documentation for a comprehensive list.  Keep in mind Postgresql originates from the eighties, when every key stroke was expensive, and every programming language strived for brevity.  It was also designed to run on much smaller systems.

7 - SQL errors often have “hints” to help resolve the problem.  Very similar to oerr without asking!

8 - The equivalent of sql*loader is built-in to the sql command line tool.

psql> \copy owner.table from ‘c:\local_file_name’;

Once again note the ubiquitous backslash!

9 - Dictionary views and catalog tables are similar, but have different names and often derivative functions - but they get the job done:

Select pg_size_pretty(pg_relation_size(‘your_object_name’));

10- Sometimes, these combinations of catalog views and functions must be combined in odd ways to produce the same output that one Oracle dictionary view produces, but they DO WORK!

SELECT

    relname AS "relation",

    pg_size_pretty (

        pg_total_relation_size (C .oid)

    ) AS "total_size"

FROM

    pg_class C

LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)

WHERE

    nspname NOT IN (

        'pg_catalog',

        'information_schema'

    )

AND C .relkind <> 'i'

AND nspname !~ '^pg_toast'

ORDER BY

    pg_total_relation_size (C .oid) DESC

LIMIT 5;

11 - Other oddities - a clause to use results of a subquery - sort of like “with”?

execute query into result;

12 - The term “table” and “relation” are often interchangeable in the documentation.

13 - “Tuples” are the equivalent to blocks, more or less.

I will cut it short at a "baker's dozen".  I am sure you will encounter many, many more.  

Feel free to comment or suggest still more, and I will revise or write another post on this subject!

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%';