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

Oracle Restore Points - Guaranteed/Clean/Normal - Which One To Use?

 A 'restore point' is a designated point of recovery for an Oracle database.  It can be a timestamp, or an SCN (system change number).

One might want to create a restore point to identify a significant point where data was in a certain condition.  Typically, this is before an upgrade, or application changes, or patching.  

Some other reasons to create one might be before a migration, or perhaps an OS change of some kind.  

While a database can be recovered to a point in time should any of the above activities cause a problem, it can be difficult to identify the correct moment before the trouble started after the fact.  

Using restore points, most often when database activity is paused, or ideally, when all connections to the database are stopped and suspended, assures a quicker valid recovery.  Usually much faster than a standard RMAN recovery!

There are three main types of recovery in the Oracle documentation.  Even an experienced DBA can be unsure of which one to use, since they've sprouted up at different periods and database versions.  These are, for a better word "standard" recovery points (the word standard is implied, and not included in the syntax), guaranteed recovery points. and "clean" recovery points.  Each one represents a different concept and presents different capabilities to aide in a quick recovery to a designated point in time.  Using each one for their specific purpose is the ideal way to assure a quick recovery, including the ability to repeat it, as necessary.  

GUARANTEED RESTORE POINTS:

Any of these options must be used with care. Any and all manipulations involving database recovery can have side-effects. One big issue is the use of redo.  Redo logs will be necessary to perform any of these options.  If the length of time between the creation of a restore point is extended, these logs will remain in the FRA (fast recovery area), and could cause space pressure.  The database could even stop until space is made available in such a case. THIS IS WHY GUARANTEED RESTORE POINTS SHOULD BE DROPPED within seven days of creation!  They should be used for a planned period of time.  They do not expire on their own.  

"NORMAL" RESTORE POINTS:

A restore point with out the guaranteed option will automatically expire once the backup retention period is expired.  "Normal" is not an Oracle syntax, but is a restore point without the "guarantee flashback database" is what is referred to here.  

CLEAN RESTORE POINTS:

The Oracle documentation defines a "clean restore point" as a point in time when the database is in closed or mounted mode.  This assures that no transactions are running before or during the creation of this restore point.  The limits on this type of restore point, as well as a normal one, is that they will automatically expire after the period defined by the spfile parameter DB_FLASHBACK_RETENTION_TARGET.

Complete documentation of this complex topic, including the implications of using it in a multitenant environment, is available using this link.

Renaming Oracle Pluggable Databases in Oracle 19c (Or: How seemingly trivial tasks can burn an entire day!)

Today's little thought provoking exercise is - what if you need to change the name of an existing PDB (pluggable database) in Oracle?  

The reason this is worthy of this write-up is as follows: back in the old days (I am thinking version 12c), this was a fairly complex operation.  It involved "unplugging" and "replugging" and exporting and importing keys and Geesh!!!  A colleague was about to start this operation when I caught on and notified him there is a much better way!

If you're not on at least 19c, get there as soon as you can!!!

So we had a little problem - we are pumping out PDBs for projects and suddenly realized we would soon run out of letters and numbers...not to mention that while it's not always good to add intelligence to a technical name, in the case of a database, one minor keystroke due to an unrecognizable sequence could be disastrous.  So we came up with a little standard.  First 3 letters are a mnemonic for the application, second 3 were environment purpose.  In our case these are:

1 - GLD for Gold environment.  Used as the golden image to build out the others.   No code changes.

2 - DEV for a development environment.  Where change rules, and all the fun happens (haha)!

3 - TST is a place for testing changes which pass muter in DEV.

4 - QAS the Quality Assurance System.  This is where project leaders make final integration tests.

5 - PRD is Production, where real end users and complex and costly transactions involving data occur.

So, in our case, the app is EBS and the environment is GLD.  

We have 8 letter positions to work with, so the seventh is a static P, standing for pluggable, and the eighth is a number designation so we can have more than one (needed during migrations, or for cloning for new environments, or so that parallel development an take place.

So now that I've laid out the reasons for a PDB rename, let's get into the mechanics.  I found the syntax in an Oracle support manual.  I cannot publish the full statement and all options verbatim here, because it is copyrighted, but here is a link.  You may need a valid Oracle support contract to view it.  

Here is what we actually used:

alter pluggable database rename global_name to EBSGLDP1;

It is really that simple.  But it's not.  Please note the slightly tricky global_name syntax.  

I have this theory.  There is code in the back of all complex software that prevents someone from executing a statement for the first time to have success, even with perfect syntax, and dedicated research.  Now, I do understand that all sorts of havoc can ensue were you to rename a database on the fly, but a novice is often prone, or even led to believe,  that it can be so easy. Likely from reading a google post like this one!  Not to "throw shade" on any other posts, but I do think renaming needs to be thought through.  

Even though I am not a novice, in a development environment, with a good backup, sometimes it is useful to try simple things first and learn from them.  

So here is the actual sequence of statements which must occur to rename a PDB in an open CDB (Container database), and have network end users be able to connect to it with the new name:

 $ sqlplus / as sysdba

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

SQL> alter pluggable database ebsgld1 close; -- You must close the PDB using the OLD name.  

Pluggable database altered.

SQL> alter pluggable database ebsgld1 open restricted; -- Restricted mode is required for renaming

Pluggable database altered.

SQL>  alter pluggable database rename global_name to EBSGLDP1; -- Here is the actual rename!

Pluggable database altered.

 SQL> alter pluggable database ebsgldp1 close; -- Now it must be closed because it is "restricted". 

Pluggable database altered.

SQL> alter pluggable database ebsgldp1 open read write;  -- Now end users can connect but only locally.

Pluggable database altered.

SQL> alter session set container = ebsgldp1; -- now we need to connect to the PDB

Session altered.

SQL> alter system register; -- this is the first step in getting the PDB "registered" with the Oracle Listener.

System altered.

Now we have to move over to the network side of things.  The network listener configuration needs to be updated to reflect the new name.  

First we will check to see if the listener "sees" the database with it's new name.  The output below is abbreviated and masked for confidentiality, but I will highlight what we are looking for in red:

lsnrctl stat

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-SEP-2021 13:48:16

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date                16-SEP-2021 16:54:45

Uptime                    0 days 20 hr. 53 min. 31 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/19.0.0.0/grid/network/admin/listener.ora

Listener Log File         /u01/app/grid/diag/tnslsnr/yourhostname/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yourhostname)(PORT=1521)))

Services Summary...

Service "+APX" has 1 instance(s).

  Instance "+APX1", status READY, has 1 handler(s) for this service...

Service "+ASM" has 1 instance(s).

  Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "+ASM_DATA" has 1 instance(s).

  Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "+ASM_RECO" has 1 instance(s).

  Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "ebsgldp1" has 1 instance(s).

  Instance "DB0720", status READY, has 1 handler(s) for this service...

The command completed successfully

Now, each end device will need this name recorded, unless it's entered in DNS.  The connect string needs to look something like this:

EBSGLDP1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = yourhostname)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = EBSGLDP1)

    )

  )

The proof is in the pudding!  A sqlplus "loopback" connection shows if we can successfully connect:

 sqlplus system/password@ebsgldp1

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 17 13:54:33 2021

Version 19.11.0.0.0

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

Last Successful login time: Fri Sep 17 2021 13:46:21 -04:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

SQL> show con_name

CON_NAME

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

EBSGLDP1

-- Above validates we're in the right container, provided your account has proper privileges.  

If you do not, check with security to see which command you are permitted to execute.  

In summary, even though this seems simple enough, some things went wrong even though I've done it a few times.  First, the original name was very close to the new one, so some typos were made.  Secondly, we needed to get the restricted syntax right, and found it was REQUIRED to make the name change.  Finally, we needed to get the service name correct in the connect string.  

Also, as a colleague notes in a different blog, the directory names for database files will not be impacted by the name change.  If this matters to you, that would be a separate operation.  

Good luck to you.  I hope this helps!

Oracle OCI Port Troubleshooting

 Oracle Cloud Infrastructure, or OCI, can be a daunting environment for the most experienced technical professional.  While it is exceedingly quick to build out an entire infrastructure, and administrative tasks that used to be measure in months can now be done in days, finding our where or why something is not working can still have the twists and turns of a detective novel:

  

My subject for today is troubleshooting connectivity issues, and especially to an Oracle database.  Oracle typically runs on port 1521 for transactional connections.  In most on-premise environments, this port was 'open' to traffic on the network specifically and intentionally a long time ago.  However, as delivered, OCI locks down all ports unless explicitly open to traffic between each device.  Doing this up front used to be the standard some years back, but with a Cloud implementation, you want to be very careful about opening ports to all traffic due to security and audit concerns.  This has been flagged as an ongoing concern due to famous incidents of hacking and ransomware exploits - as described on this website.

I recently had two projects where, although this was identified, and the network admins claimed to have opened traffic on this port between all recognized and identified "instances" (usually virtualized servers), we were getting connection errors, such as, from an Oracle SOA Suite installation: 

'Unable to connect to the database.  Check if DB connection details entered are correct'.  

No error codes, no specific details as to what went wrong.  We verified everything was correct, and could connect from a client to the database over port 1521 without issue.  This seemingly proved the port was open, but......not between this specific port and target.  After much deliberation, and assurances from network teams that the ports were open, we did some more detective work.  

There are many ways to validate port connectivity, but the tool of choice for us is called netcat.  This free tool is available for both linux and Windows, and has a simple syntax.  It can also be abbreviated as ncat or just 'nc'.   

What is nice about this utility is that it is easily installed (root permission needed in linux, admin permission for Windows), and will be available to run in mere seconds. It might be a wise standard to include it with a hardened OS build, as it only connects if allowed by security audit rules, and reports back if unsuccessful.  Here is an installation tutorial.

I will also mention, should it not be accepted as permanent, it is easily uninstalled as well.  

Simple is as simple does.  Or not!!!!  In our use cases, this tool proved to our network administration team that port 1521 was not open system-wide, and they quickly took steps to correct this.  It's probably safest to open it only for specified source and targets, due to security concerns. 

Once installed, here is the syntax to validate a port as open, and the results:

1-michael.fontana@10.31.2.5's password:
Last failed login: Sat Jul 24 13:05:33 EDT 2021 from 10.31.2.84 on ssh:notty
There was 1 failed login attempt since the last successful login.
Last login: Wed Jul 21 16:38:01 2021 from 10.31.2.84
0(1-michael.fontana@testserver1 [~])$ sudo su -
Last login: Fri Jul 23 15:33:07 EDT 2021 on pts/0
## - You are now root - ##
0(root@testserver1 ~)$ nc -vz testserver5 1521
Connection to 10.31.2.105 1521 port [tcp/ncube-lm] succeeded!
0(root@testserver1 ~)$ nc -vz testserver3 1521
Connection to 10.31.2.103 1521 port [tcp/ncube-lm] succeeded!
0(root@testserver1 ~)$ nc -vz testserver4 1521
Connection to 10.31.2.104 1521 port [tcp/ncube-lm] succeeded!
0(root@testserver1 ~)$ #An example of a failure:

0(root@testserver1 ~)$ nc -v faketarget 1521

Ncat: Version 6.47 ( http://nmap.org/ncat )

Ncat: Connection timed out.

The command will hang if the port is not open.  You can use options like -w (wait) to return an error code after a few seconds if you want to use netcat for scripting or other purposes.  

Oracle 21C New Features?

 Oracle 21c New Features    

I just finished migrating to Oracle 19c.  It was a lot of work.

Now I find out that Oracle 21c is out?  Please click on the preceding link for detailed documentation on what's new in 21c.

My first thought (and maybe yours), is: "What's 21c got that I ain't got?".  My second thought is that these major releases are coming way too fast!  

At first glance, this appears to be true.  In fact, the length of time between Oracle 11g and Oracle 12c release dates was six years!  The clear and logical explanation for this is that Oracle has moved to a yearly release cycle.  What is less obvious is that what Oracle is calling new "versions" more or less correspond to what used to be called "patchsets".  For example, Oracle 11g was originally identified as version 11.1.0.6 in 2007.  By 2013, subversion identifiers were added as patchsets leading up to the terminal release of 11.2.0.4.  In between, these patchsets came out with new features (often announced at Oracle Openworld) every September.  

All of this was documented with the "shocking!" release of Oracle 18c, and the move to a yearly release cycle.  While it was explained as a logical, well-thought out change,  I couldn't help but recall someone explaining that Larry Ellison was an admitted triskaidekaphobic with a fear of the number 13.  Keep in mind that the newly modified release designations also identify the significance of the version.  For example, Oracle 20c is called an innovation release.  So is 21c.  So no compelling reason to upgrade to them.   If some feature(s) are of interest, you can download and evaluate them.  I cannot identify a single one that is a must have.  For more technical details on what significant changes are in 21c, I invite you to review Oracle VP William Hardie's excellent blog post on the subject.  

While there is no specific technical detail in this post, I hope you will find it enjoyable from a historical point of view.  I invite you to click on the imbedded links to get the technical scoop.  This blog was written to help myself (and others) find detailed information about Oracle releases corresponding to versions, as I am often asked to support or upgrade an older version.  We might need to know if a feature is/was available for a given release.  

This is my first blog post in a while.  I hope to do many more in the near future!

Oracle Enterprise Manager (OEM) Patching

Be forewarned - patching OEM has changed!

I assume you are reading this because it has become necessary to patch your installation of the product now known as "Cloud Control".  Past iterations of this product were called:

Oracle Enterprise Manager (OEM)
Oracle Monitoring System (OMS)
Oracle Grid Control (GC)

In fact, many of the commands in the software stack for this product still have these legacy names, and many of the documents associated with installation, administration, maintenance and use of OEM still use these names/acronyms. 

If you are about to patch, keep in mind, very similar to standard Oracle database software, that a utility named "opatch" will be used.  It comes with the software stack (still called "oms"), and is located in the standard location: $ORACLE_HOME/Opatch (which indicates the home directory for the product + the location of the "opatch" utility.  However, there is another patching utility, specifically for the OMS, called OMSPatcher, which is used as well. 

As of this writing, the proper document to be followed for applying the April 2020 OMS "PSU" patch are at the Oracle Support site (support.oracle.com).  It is also assumed you have access to support and a valid product license.  Please reference:

Patch 31058360: enterprise manager for oms plugins 13.3.1.0.200331

If your OMSPatcher software is out of date, you will have to update it by following procedures documented for patch 19999993 OMSPatcher v13.9.3.

This patch is applied the traditional way.   Move the patch bundle zip to $ORACLE_HOME/OMSpatch and then unzip it into the same directory (after backing up first).   Patching Opatch (when required) is where it gets interesting!  THE OMS MUST NOW BE DOWN TO PATCH OPATCH! We may no longer use the standard method.  You must now unzip the patch, and then invoke java (assuring the correct version is in your path, or calling the version imbedded in your Oracle home):

- If needed, install the software with custom inventory via:

java -jar /6880880/opatch_generic.jar -silent oracle_home=$ORACLE_HOME -invPtrLoc $YOUR_INVENTORY where inventory pointer location is the absolute path to the oraInst.loc file  In my next post, I will detail patching specifics, including the necessary steps  of backing up the OMS software and running a "prechceck".