Miguel Anjo


Find the most used indexes   Recently updated !

At a customer I was asked to check for missing indexes and add them. Some days later the application automatically dropped them. The customer come back to me and asked to check which indexes were the most important.

As maybe not all my added indexes were needed and used, instead of just sending (again) the document with their definition, I checked which indexes were actively used since adding the new indexes.

I come up with this query:

select distinct object_owner,object_name from dba_hist_sql_plan where plan_hash_value in (select plan_hash_value from 
(
/* Day after adding new indexes */
with snapshot as (select min(snap_id) snap_id from dba_hist_snapshot where begin_interval_time>=to_date('27-MAY-2021','DD-MON-YYYY')),
/* Plans using new indexes */
plans as (select plan_hash_value from dba_hist_sql_plan where object_name like '%\_ANJO\_%' escape '\')
/* Plans using new indexes after my intervention */
select plan_hash_value, sum(executions_delta) execs from dba_hist_sqlstat where snap_id> (select snap_id from snapshot)
  and plan_hash_value in (select plan_hash_value from plans)
  group by plan_hash_value
  having sum(executions_delta)>10
))
and object_name like '%\_ANJO\_%' escape '\' 
order by 1,2;

I’ve limited the search to the indexes I added (having _ANJO_ in their name).

Surprise, all of the indexes I added were actively used. Hopefully they will add them again for good.


Recover OEM repository from old schema backup

Things not always go right, and recently I had to recover the OEM repository using a one month old datapump backup. Only SYSMAN schema was corrupted, and I did not touch the other schemas.

I probably still miss doing some things and this post is mostly a note to make it faster for other people going through the process and not having a DB backup…

A full restore of the database would be certainly a much better and efficient solution than the one below. 🙂

To recover the corrupted SYSMAN schema where the OEM repository resides, first I drop it and then I did:

# Restore user
impdp schemas=SYSMAN directory=BACKUP_DIR dumpfile=expdp_sysman_april2021.dmp include=USER

# Restore system grants:
SQL> grant execute on dbms_crypto to sysman;
SQL> grant execute on dbms_lock to sysman;
SQL> grant execute on dbms_rls to sysman;
SQL> grant execute on dbms_aq to sysman;
SQL> grant execute on dbms_aqadm to sysman;
SQL> grant execute on dbms_alert to sysman;

# Restore other object grants 
impdp directory=BACKUP_DIR dumpfile=expdp_sysman_april2021.dmp include=GRANT

# Restore data
impdp schemas=SYSMAN directory=BACKUP_DIR dumpfile=expdp_sysman_april2021.dmp 

# Restart queues
/* select 'exec DBMS_AQADM.START_QUEUE(queue_name => ''SYSMAN.'||name||''');' from dba_queues where enqueue_enabled like '%NO%' and owner='SYSMAN' order by 1; */
SQL> exec dbms_aqadm.start_queue('SYSMAN.MGMT_ADMINMSG_BUS',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_CNTR_QUEUE',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_LOADERJOB_QUEUE',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_EVENT_BUS',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_NOTIFY_Q',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_GROUP_EVENT_Q',true,true);
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.EM_JOB_STATUS_UPD_Q');
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.MGMT_HOST_PING_Q');
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.MGMT_LOADER_Q');
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.MGMT_NOTIFY_INPUT_Q');
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.MGMT_TASK_Q');
SQL> commit;

# Perform partitionion maintenance (as SYSMAN):
SQL> exec gc_interval_partition_mgr.partition_maintenance;
SQL> exec mgmt_audit_admin.add_audit_partition;

Start OEM again:

emctl start oms

Unblock the agents (Setup – Manage Cloud Control – Agents – select blocked agents + “unblock”) and on each monitored host, clear the agent state, restart it and test:

emctl clearstate agent
emctl stop agent
emctl start agent
emctl control agent runCollection $(hostname -f):host Response
emctl status agent

After this, OEM was running again without problems.


CPU pinning overview directly from physical server using OracleVM / Xen

When using Oracle VM, there is the the possibility to use the ‘ovm_vmcontrol’ tool to check and set the CPU Pinning. However the output is not the best for a overview picture.

Today I just wrote a short script to print this overview, which can be run directly on the Physical Server which we are interested at:

[root@lxsrv0001 ~]# for vm in $(xm vcpu-list | egrep '^000' | cut -c1-32 | uniq); do 
  echo "${vm} - $(grep OVM_simple_name /OVS/Repositories/*/VirtualMachines/${vm}/vm.cfg | cut -d'=' -f2) - $(xm vcpu-list | grep -c ${vm}) CPUs - Pin to $(xm vcpu-list | grep -m1 ${vm} | rev | cut -d' ' -f1 | rev)"; 
done

The output will be something like

0004fb000006000031f0e14272fa90d1 - 'vmsrv01' - 6 CPUs - Pin to 0-5
0004fb0000060000b7fcb78fa7888d37 - 'vmsrv03' - 6 CPUs - Pin to 0-5
0004fb00000600004568a88b95d9ea3f - 'vmsrv05' - 4 CPUs - Pin to 6-10
0004fb00000600004ae9d1f0c8e4b8fb - 'vmsrv07' - 2 CPUs - Pin to 11-12

This can always be useful when we need to fast check the CPU pinning for the whole Physical Server.


What is the Dell BSAFE Micro-Edition Suite MES ?

On the April 2021 Oracle Critical Patch Update, the top vulnerability affects “Oracle Database – Enterprise Edition Security (Dell BSAFE Micro Edition Suite)”.

But what exactly is this Dell MES ? The answer comes on MES v4.1.6 to v4.5 update 18c / 19c databases (Doc ID 2746801.1) note on My Oracle Support. It explains:

” The BSAFE MES is the underlying encryption libraries used by the Database and associated technologies and products to encrypt data at rest and in transit.  Upgrading to MES 4.5 resolves a number of vulnerabilities and provides the latest and most secure encryption ciphers.”

Please note that already on the January 2021 Oracle Release Update (19.10 or 18.13 there was an update of this component and “Anonymous RC4 Cipher” is not supported and does not work anymore.

Other ciphers are deprecated and should not be used anymore, as they are not fully secure:

  • Unix Crypt (MD5crypt) Password verified (used with Oracle Internet Directory OID)
  •  MD4, MD5, DES, and RC4-related algorithms used for instance in Network encryption (recommended to use AES)
  • SHA-1 used in DBMS_CRYPTO and Checksum in SQLNET (recommended to use SHA-2)
  • TLS 1.0 and TLS 1.1 used to network connection authentication to the database using certificates (recommended to use TLS 1.2)

In my case I had problems not with the database, but with an old OEM Agent, that could not communicate anymore to OEM (after the January RU on OEM). The solution was to remove all deprecated algoritms and find common accepted ones between to old agent and patched OEM.


Go to MOS directly from Google results – part 2

We know that Metalink search engine is not the fastest one. Luckily My Oracle Support is now partly accessible directly from Google Search.

Using a shortcut, we can quickly limit our search to My Oracle Support website. Then using the URL Redirector as explained here, one jumps directly to the Oracle note.

On Chrome (similar on Firefox and Edge) it is possible to set Search Engine shortcuts.

Just go to chrome://settings/searchEngines and add a new search engine as below :

For easy copy/paste:

Search engine: Oracle Support
Keyword: mos
URL: https://www.google.com/search?q=site%3Asupport.oracle.com+%s

Now we have a quick way into MOS:

🙂


Go to MOS directly from Google results – part 1

I’m sure you know this annoying sequence, when you click on a My Oracle Support result from Google:

It would be nice to go from the Google results page directly into the MOS website.

This can be easily done with a Google Chrome (I believe also exists for Firefox and Edge) that rewrites the URL.

I used Redirector which I configured with:

To make copy/paste easier:

Redirect: ^(?:https?://)support.oracle.com/knowledge/(.*)/(.*)_([0-9]).html
To: https://support.oracle.com/epmos/faces/DocumentDisplay?id=$2.$3

Now from Google results I go directly the Oracle Support note page. 🙂


OEM Agent error with [handshake has no peer]

At a client, there is OEM 13.4 running without problems, being patched every three months with the latest PSU.

Some old VMs have old Agents, like OEM Agent 13.2.

Recently we noticed one corrective action failed with:

Corrective action output=Step aborted after 30 failed attempts to run. Last captured error message was: Caught agent exception[unable to connect to http server at https://srv01234.domain.com:3872/emd/main/. [handshake has no peer](oracle.sysman.emSDK.agent.comm.exception.VerifyConnectionException)]
Error communicating with agent. Please also check incident console for possible job-system related problems.

The problem is that OEM Agent 13.2 uses by default the following Ciphers to communicate:

(more…)

Explore multiple optimizer features and fixes with SQLT Xplore

Yesterday I described how I come to SQLT Xplore and it helped me to find out, on Oracle 12.2.0.1, that optimizer_features_enable=8.1.3 decreased the parsing time of a query from 5 seconds to 0.2 seconds. Today I show how to use SQLT Xplore.

What is SQLT Xplore?

SQL Xplore automatises the test of almost 2’000 optimizer parameters and bug fixes control against one query, allowing to discover which parameter was eventually the reason of a performance regression.

(more…)

Long parsing on Oracle 12.2 and the discovery of SQLT Xplore

Today I discovered a fantastic free tool from Oracle: SQLT Xplore!

At a client I had one query that took long time parsing. The tkprof result of the 10046 trace showed it took 5 seconds to parse:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      5.17       5.30          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0        665          0          26
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      5.17       5.30          0        665          0          26

Looking around I’ve noticed that disabling the cost based transformation:

ALTER SESSIONS SET "_optimizer_cost_based_transformation"=off;  

…the query was much faster to parse: only 0,5 seconds.

(more…)

Why PGA_AGGREGATE_LIMIT appears to be set, when it is not?

Today I had to explain why the pga_aggregate_target was showing a value, but one does not have to care about it most of the cases. Here the same explanation.

When doing:

SQL> show parameter pga

NAME                                 TYPE                                         VALUE
------------------------------------ -------------------------------------------- ----------------------
pga_aggregate_limit                  big integer                                  3000M
pga_aggregate_target                 big integer                                  1G

One would think that pga_aggregate_target is set to 3000M. However when one checks on the spfile, there is nothing defined:

(more…)