Monthly Archives: June 2021


Opatchauto does not work without DB created. And Oracle support… well

Few months ago I hit an interesting case. After installing Oracle Grid Infrastructure and Database software on a server, the new Release Update come out and I wanted to use opatchauto to apply it automatically on both homes.

It doesn’t work.

sudo ${ORACLE_HOME}/OPatch/opatchauto apply -phBaseDir /tmp/RU_19c_JAN2021/32126842 -prepare-clone -silent /tmp/RU_19c_JAN2021/clone.properties
...
OPATCHAUTO-72128: Cannot execute out of place patching prepare session.
OPATCHAUTO-72128: Clone home properties /tmp/RU_19c_JAN2021/clone.properties specified has entry for non requested home(s) [/u00/app/oracle/product/19.8.0].
OPATCHAUTO-72128: Please correct properties file and re-run out of place patching prepare operation.
OPatchAuto failed.

The setup is all correct, opatchauto is the latest version (13.9.4.5.0, as of February 2021), the inventory has the two homes:

<HOME NAME="OraGI19Home1" LOC="/u00/app/grid/19.8.0" TYPE="O" IDX="1" CRS="true"/>
<HOME NAME="OraDB19Home1" LOC="/u00/app/oracle/product/19.8.0" TYPE="O" IDX="2"/>

The clone.properties file has the name of these homes:

/u00/app/grid/19.8.0=/u00/app/grid/19.10.0
/u00/app/oracle/product/19.8.0=/u00/app/oracle/product/19.10.0

The only “problem” is that no database is created. In fact, the workaround is just to register a fake DB on the CRS:

srvctl add db XX -oraclehome /u00/app/oracle/product/19.8.0

Then the opatchauto works.

I open a SR with Oracle. Inform about the OPatch bug and ask what is the meaning of the OPATCHAUTO-72128 error, because there is no documentation of Opatchauto.

The normal ping-pong with Oracle support starts, they ask me to run with maximum debug level before they can do something. I say that the complete use case is already described and they can try in-house and can/should open a bug.

No success. The support person says that if the customer doesn’t do more work, Oracle doesn’t care about the bug and problem will remain unfixed. I inform that I will blog about this bug, so that the community is aware.

Final answer from this SR:

“Knowledge content not created because the issue was resolved by the customer with no further information provided. Also no further ODM headings were used for the same reason.”


Find the most used indexes

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.