Describe views in HDBSQL (SAP HANA 2.0 SP03 or higher)

For DBAs used to Oracle sqlplus, not being able to do a “describe my_view” via hdbsql can sometimes be frustrating. This is especially frustrating since it is possible to do it for a table.

In order to overcome this lack, I have written a small procedure which allows to reproduce almost the same result as the “\dc” command for a table.

This is still more constraining than a simple “describe” or “desc”, but it avoids having to manually query the “VIEW_COLUMNS” view or search the SAP documentation to get the view definition.

Continue reading “Describe views in HDBSQL (SAP HANA 2.0 SP03 or higher)”

Query Management Pack Acess from OMR in Enterprise Manager 13c

Like most of Oracle’s products, Enterprise Manager comes with options that are enabled by default, but licensed when used. In order to prevent these options from being used and to avoid having to pay the corresponding license fee, your company may ask you to list the status of these options on a regular basis.

On Oracle Enterprise Manager, these options are called “Management Packs” :

  • Oracle Cloud Management Pack for Oracle Database
  • Oracle Cloud Management Pack for Oracle Fusion Middleware
  • Application Replay Pack
  • Management Pack for Oracle Coherence
  • Database Diagnostics Pack
  • Oracle Database Lifecycle Management Pack
  • Oracle Data Masking and Subsetting Pack
  • Database Tuning Pack
  • Configuration Management Pack for Applications
  • WLS Management Pack EE

Recently, my client asked me to automate the collection of the status of these management packs. The information is easily retrievable from the Enterprise Manager console (Setup=>Management Packs=>Management Pack Access), but there is no possibility to export the list to Microsoft Excel for example, which is not practical to include in a reporting.

After some research, I developed a query that extracts this information from the OMR database in CSV format.

Continue reading “Query Management Pack Acess from OMR in Enterprise Manager 13c”

Where to find Oracle RDBMS Gold Image (aka “Database Software Clone”) for Exadata?

For some unknown reason, the search on MOS (My Oracle Support) does not return any results when looking for the patch corresponding to a specific Oracle RDBMS Gold Image although it is essential when you want to deploy a new RDBMS version on an Exadata. You must know the corresponding patch number to be able to find it.

After some research, here is the simplest method I found.

Continue reading “Where to find Oracle RDBMS Gold Image (aka “Database Software Clone”) for Exadata?”

Force metric collection in Oracle Enterprise Manager

When working on the implementation of monitoring via Oracle Enterprise Manager, it is sometimes necessary to perform several tests with different thresholds in order to refine alerting, or simply to verify that the monitoring is working properly.

The main problem is that you have to wait until the collection is done to see the result. Since most metrics have a “Collection Schedule” of more than 5 minutes, this can quickly become a waste of time.

To avoid this wait, it is possible to force the collection of a particular metric for a target directly from the EM agent.

Continue reading “Force metric collection in Oracle Enterprise Manager”

Error “platform=${platform/\)/%29}: bad substitution” when installing an Enterprise Manager Agent with AgentPull method on AIX

Today, I have been asked to deploy several Oracle Enterprise Manager agents on AIX 7.1 servers.
For this type of request, I have a preference for the “AgentPull” method when I don’t have a suitable Gold Image available.
Unfortunately, it seems that the “AgentPull” script does not work properly on AIX machines because when executing it, I got the following error: “platform=${platform/)/%29}: bad substitution“.

Continue reading “Error “platform=${platform/\)/%29}: bad substitution” when installing an Enterprise Manager Agent with AgentPull method on AIX”

How “Oracle Flashback Query” can save you from performing a full database restore or a TSPITR

Recently, I have been contacted several times by developers following manipulation errors that led to unwanted deletions of rows in a table on Oracle database. This can unfortunately happen frequently, especially on test or development environments.

The first thing that comes to mind is to restore a full backup of the database or perform a restore of the tablespace where the table is located (TSPITR) to retrieve the deleted rows. But there may be another faster solution if you do it quickly: using “Oracle Flasback Query“.

This feature can also be used to compare the content of a table with its content in the past, see the rows inserted/deleted/updated since a certain time,…

Continue reading “How “Oracle Flashback Query” can save you from performing a full database restore or a TSPITR”

Avoid TNS-00584 errors on listener restart

If you use the “TCP.VALIDNODE_CHECKING“, “TCP.INVITED_NODES” or “TCP.EXCLUDED_NODES” parameters in your listener’s sqlnet.ora file, you may have already encountered the following error when starting or restarting your listener:

TNS-12560: TNS:protocol adapter error
TNS-00584: Valid node checking configuration error

The problem is easy to fix when you have only a few hostnames referenced, but when the list is long, finding the server that caused the error can be much more time consuming.

Continue reading “Avoid TNS-00584 errors on listener restart”

How to restore root access on a KVM guest running Oracle Linux 7 on Exadata (X8M)

A few days ago, I made a handling error on an Oracle Linux 7 virtual machine on a freshly configured Exadata X8M. I inadvertently renamed the files “/etc/pam.d/password-auth” and “/etc/pam.d/system-auth”. Result: I was no longer able to connect to the VM in ssh, or even via the virsh console.

I will describe here the steps to regain access by renaming the files with the right name, but it is also applicable if you have lost your root password.

Continue reading “How to restore root access on a KVM guest running Oracle Linux 7 on Exadata (X8M)”