Upgraded database broken in Enterprise Manager: No such metadata

This past week I have been having fun upgrading our AWR Warehouse instance from 12.1 to 12.2.  I thought this would be a good chance to get some exposure to 12.2 on a real-life system without affecting anything critical.  I have run into quite a few issues. First was that after I upgraded the instance, …

Upgraded database broken in Enterprise Manager: No such metadata Read More »

Example of using VPD with an Application Context

SQL> CREATE OR REPLACE PROCEDURE vpdtest_context_procedure(object_type IN VARCHAR2)                                                                                                              2  IS                                                                                                                                                                                          3  BEGIN                                                                                                                                                                                       4     DBMS_SESSION.SET_CONTEXT(‘vpdtest_context’, ‘object_type’, object_type);                                                                                                                 5  END;                                                                                                                                                                                        6  /                                                                                                                                                                                                                                                                                                                                                                                        Procedure created.                                                                                                                                                                                                                                                                                                                                                                            SQL>                                                                                                                                                                                           SQL> CREATE CONTEXT vpdtest_context using vpdtest_context_procedure;                                                                                                                                                                                                                                                                                                                          Context created.                                                                                                                                                                                                                                                                                                                                                                              SQL>                                                                                                                                                                                         …

Example of using VPD with an Application Context Read More »

“enq: TX – allocate ITL entry” wait event on index block

Over the weekend, some processes on one of our databases locked up.  Support killed some sessions to get things back running, but on Monday I wanted to try to drill into what had occurred. Querying dba_hist_active_sess_history I identified most problem sessions were waiting on the event “enq: TX – allocate ITL entry”. For more details …

“enq: TX – allocate ITL entry” wait event on index block Read More »

Creating SQL Plan Baseline from a different SQL statement

Really just documenting this for myself because I couldn’t easily find this on the web.  Most of the steps are taken from this page: Plan Stability using Sql Profiles and SQL Plan Management Observe the following query doing a full table scan: select * from table(dbms_xplan.display_cursor(‘8jng4anj19km4′,                                                format=>’peeked_binds’)); SELECT COUNT(1) FROM TOTS_QUE WHERE OTQUE_MODULE = …

Creating SQL Plan Baseline from a different SQL statement Read More »

Adaptive Dynamic Sampling and the Result Cache: RC Latch

After we migrated one of our instances from Oracle 11.2.0.4 to 12.1.0.2 we ran into some serious latch contention that basically took down the instance.  From online research, and discussion with other DBAs I don’t think we are the only customer that has run into this problem, so I wanted to share my investigation and …

Adaptive Dynamic Sampling and the Result Cache: RC Latch Read More »

Incorrect Cardinality Adjustment caused by Dynamic Statistics

During migration testing to Oracle 12, one of our teams complained about regression of a particular query. Drilling into what was going on identified some a problem with Adaptive Dynamic Sampling, and certainly not the first one we have encountered. I will detail the steps to reproduce, and my understanding of what is going on. …

Incorrect Cardinality Adjustment caused by Dynamic Statistics Read More »

Problems with opatchauto: Unknown host name in config

We have been attempting to install the latest GI PSU on  a new AIX system, but having problems with opatchauto failing out with the error  below: oracle.osysmodel.driver.sdk.productdriver.ProductDriverException: Unknown host name in config:hkxtsdbdv1         at oracle.oplan.db.driver.crs.AbstractCrsProductDriver.findHostByName(AbstractCrsProductDriver.java:124)         at oracle.oplan.db.driver.crs.AbstractCrsProductDriver.getLocalInfo(AbstractCrsProductDriver.java:774)         at oracle.oplan.db.driver.crs.CrsProductDriver.buildSystemInstance(CrsProductDriver.java:225)         at oracle.opatchauto.gi.GIPatchingUtil.generateOPatchAutoExecutionStep(GIPatchingUtil.java:202)         at oracle.opatchauto.gi.GIPatchingUtil.generateapplysteps(GIPatchingUtil.java:286)         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)         …

Problems with opatchauto: Unknown host name in config Read More »

“rtld: 0712-001 Symbol CreateIoCompletionPort was referenced” installing Oracle 12.1.0.2 on AIX

I first encountered this error message when installing 12.1.0.2 on AIX, and have since hit this with other Oracle software, particularly Enterprise Manager agent. HKE1DEV2@[/home/oracle]#sqlplus exec(): 0509-036 Cannot load program sqlplus because of the following errors: rtld: 0712-001 Symbol CreateIoCompletionPort was referenced from module /u01/app/oracle/product/12.1.0.2/client/lib/libttsh12.so(), but a runtime definition of the symbol was not found. …

“rtld: 0712-001 Symbol CreateIoCompletionPort was referenced” installing Oracle 12.1.0.2 on AIX Read More »