Configuring Clients for Database Gateway for Websphere MQ 19c

In this post, I will try to explain the upgrade of Database Gateway for MQ to version 19c.
It comes with two problems that makes an upgrade from e.g. Database Gateway 12c to 19c a bit more challenging than one would expect.

No support for PDBs

The first problem to address is that Database Gateway setup scripts for the clients do still have no support for Pluggable Databases (PDBs). Already the 12c version scripts, used to deploy the code in a client database had to be manually edited to correct the connection string. Back then this was still understandable as the major bulk of existing client database was a single instance database. 
In version 19c, I would expect to see some kind of support for the use of PDBs, but there is not. In both versions you still have to edit the deployment files when using it with a PDB. Otherwise the packages will be installed in the CDB, where they are not accessible from the PDBs.

The first thing that needs to be fixed, is that the pgmdeploy.sql program simply exits without doing a thing. This is caused by the "whenever sqlerror exit" in the beginning of the script, followed by a connection attempt using the "scott/tiger" account, that (should) nowhere exist. 
The general way to solve this is to comment out the line that has this connect command. It is around line 65 in the script. Simply put two dashes in front of it, to comment it out.

The second line that needs to be edited is found on line 111 of the pgmdeploy script. There the connection string used, should be appended with the PDB name the installation will take place in.

Change the lines:

PROMPT Verifying SYS Password...
connect SYS/&INPASS2 as sysdba

into

PROMPT Verifying SYS Password...
connect SYS/&INPASS2@<PDB NAME> as sysdba

For the pgmundeploy.sql the same needs to be done on line 91.

Remark: There were the scripts used with a single instance database were able to continue, by simply using the [Enter] key only, with a PDB you actually have to specify a SYS password.

Now the deploy and undeploy scripts should work OK, for Oracle 19c clients, but.....


No support for lower level databases

We all know that Oracle 12.1 is out of support, not even speaking about Oracle 11 databases.
When you because of that, decide to upgrade Database Gateway 12c to Database Gateway 19c, you will run into a major but solvable issue, when the client databases you use are on Oracle 12.1 or even lower.

If you however start using Database Gateway now for the first time and you decide to start with the latest (19c) release, then this issue is not easy to solve.

The problem is that as of release 12.2 ( 19c is 12.2.0.4), the DBMS_STANDARD package has been extended with a new variable ORA_MAX_NAME_LEN and some dependent functions. 
Oracle Database Gateway 19c uses this new variable, which does not exist on lower level databases. 

This makes the deploy of the PGM packages on Oracle 11 and Oracle 12.1 database impossible.
Only if you have or can get the deploy scripts for an earlier Database Gateway release then you can use Database Gateway 19c


Solution

To use an Oracle Database 11g or 12.1 with Database Gateway 19c, you should use the Database Gateway 12.1 deploy scripts.
As soon as they are deployed, you need to replace the library definition for LIBDG4MQ that has been created, making it point to the new 19c location of the library. 

Use a statement similar to this and execute it as SYS in the client database ( PDB or single instance):

create or replace library LIBDG4MQ as
'[your dbgateway_home]/lib/libdg4mqc.so'
transactional; 

Afterwards recompile all PGM packages under the SYS schema and your done. Note that the PGM_UTL8 package remains Invalid. This is expected.

DBCA hangs after entering Database Location (19c)

Today we were installing a new Oracle server on IBM Pseries running AIX 7.2.
The installation was done with the, at this moment, newest release of Oracle, being 19.9

The installation of the base release 19.3 and the release update went successful.

Creating a database however using dbca got stuck after entering the database file location in Step 5.
The trace.log file of dbca only showed:

INFO: Nov 03, 2020 3:29:54 PM oracle.install.commons.util.StatusControl showMessage
INFO: Validating writability of the storage location provided

Since this was a new server and we already had installed multiple servers with this Oracle release but on Redhat Enterprise Linux 7, we suspected the AIX server to be the cause of this problem.

UNIX administrators were involved and together we started searching for the cause.
The file location was writable, the process state using "truss" showed a continues loop of checks, with certain repeating functions like "ptx_get_cpuinfo", "ptx_get_wparsysinfo" and  "ptx_get_wparsysstats" etc..etc..

Oracle Support gave some result on these functions, but these results where linked to 'srvctl' and not 'dbca'. After entering a more detailed search description: 

"dbca hang database files location"

the first result was Doc ID 2706037.1 with the title:

Database creation via DBCA hangs when the database files location does not include {DB_UNIQUE_NAME} 

It turns out that according to this Doc that DBCA as of release 19.3 requires a DB_UNIQUE_NAME in the file location, whenever you deviate from the given default. 

Again, on Linux we have never seen this issue, and all our systems have the same non-oracle-default setup. 

After appending our default location, with this DB_UNIQUE_NAME, dbca continued as expected.


Conclusion:  On AIX it seems that DBCA can't work with a non-default database location, if it does not contain the DB_UNIQUE_NAME.
It does however also not generate an error message to tell you about this.
It simply gets stuck. 

However AIX is a very stable and reliable operating system, I tend to move all databases to Linux, since it is more reliable and thrustfull.

Top Activity option in OEM13 gone

 After an upgrade from Oracle Enterprise Manager 12c to version 13c, we noticed that we had lost the "Top Activity" option, that used to be in the Performance Menu of the database page.

Seen here is the Performance Menu of the version 13 of Oracle Enterprise Manager.

Below the "Performance Home" option the "Top Activity" option was available in version 12c

As you can see however, we are still able to access the screen.
It does still exist. It is only that the menu option is gone.
Luckily we kept the old http-link from version 12c, which we had saved as a Favorite in the browser.

Accessing this page was now possible by simply editing this saved link and changing the server name.

For all of you who didn't save this link and also upgraded to the new version, the link looks like this:

https://<your oem13 server>:7803/em/faces/sdk/nonFacesWrapper?target=<SID>&_em.coBM=%2Fconsole%2Fdatabase%2Finstance%2FwaitDetails%3F_em.coIFR%3Dtrue%26event%3DdoLoad%26target%3D<SID>%26type%3Doracle_database%26datasource%3DSQL%26waitClass%3DOverview&type=oracle_database

Replace <your oem13 server> with the hostname of your Oracle Enterprise Manager server and <SID> with the database instance you want to monitor.

Keep in mind that this page requires the Oracle Diagnostic Pack license to access. 

If you have the license, you can enable the Diagnostic Pack via Setup => Management Pack => Management Pack Access.

You can also enable it from SQLplus by entering the command:

SQL> alter system set control_management_pack_access='DIAGNOSTIC' scope=both;