How to escalate Oracle Support Service Request MOS SR
If you are not satisfied with the resolution or response to a critical Service Request (SR) at My Oracle Support (MOS), you can escalate it. SR escalation process (formerly known as the Duty Manager Process) is officially known as Requesting Manager Attention to a Service Request. This procedure will facilitate the assignment of a Support Manager [and potentially another support Engineer] to the SR, creation of an Action Plan and as the result it will usually speed up the resolution of the issue.
See below a Workbook of how to properly escalate MOS SR which is based on the Oracle official procedure, best practices and my own experience (this is a draft document -> it will be adjusted based on your valuable comments and experiences that you can post right below this post).
Before Escalating MOS SR
- Raise SR Severity (do not escalate if you have an ability to raise the severity)
- Work properly with the SR to avoid delays using my MOS SR Tips and Best practices
- In case you decide to escalate SR ensure the following:
- You have a valid reason and business case at your hands.
- Decide who will be the main contact from your side for the escalation process and that contact information is properly documeted in the SR (either as Primary or Alternate SR contact or if other: place it in SR body).
1. Insert the below template into the SR
This step is optional but makes sense to ensure proper communication Read more »
Oracle slow SQL query against dba_segments solved
I remember when long time ago one database consultant confused my manager saying that our Oracle 9i database had poor performance just taking into account a slow response from dba_segments data dictionary view. That was a nasty trick to blame a DBA and the Oracle database for poor performance at that time. In fact there were a few Oracle bugs related to those performance issues after switching from dictionary to locally managed tablesspaces at that time. Recently I’ve noticed similar performance degradation on Oracle 11gR2 (11.2.0.2 and 11.2.0.3) by querying DBA_SEGMENTS or USER_SEGMENTS data dictionary views involving the columns BYTES, BLOCKS, or EXTENTS. Queries on DBA_TS_QUOTAS or USER_TS_QUOTES on columns BYTES or BLOCKS were also slow.
Even if you personally do not care about these dictionary views they are still very important since they are used by some Oracle internal components and the other database tools including Oracle Enterprise Manager (OEM) Cloud Control and its Database Home Page. Thus, I’ll describe below the problematic of those data dictionary views and the way how to fix their performance issues.
First of all do not wonder why queries against those views often seem to slow. DBA_SEGEMENTS for example is a very complex view that is built on another SYS_DBA_SEGS view. In summary DBA_SEGMENTS view on Oracle 11gR2 consists of the following components:
– 25 columns
– around 110 lines of SQL code
– 3 UNION ALL clauses
– A lot of joins between following tables: sys.user$, sys.ts$, sys.undo$, sys.seg$, sys.file$
Oracle PatchSet 11.2.0.4 upgrade issues ORA- PLS-
Below is a list of issues (ORA- and PLS- errors) I encountered after upgrading Oracle 11gR2 Database Enterprise Servers to the latest Patchset 11.2.0.4.
The upgrade mostly done:
– from 11.2.0.3.6-8
– to 11.2.0.4.1 (including Patchset Update PSU 1)
– on platforms: HP-UX Itanium, Linux
ORA-01979: missing or invalid password for role
Problem: Roles with passwords are failing after Oracle database Patchset upgrade
Solution: Recreate a role with a new password or without it.
Example:
alter role REPORTING_RW not identified;
ORA-01791: not a SELECTed expression
Problem: SQL having different number of columns in DISTINCT and ORDER BY clauses failing after Oracle database upgrade but it was working on previous release before last Patchset 11.2.0.4 since developers happily used an Oracle Bug that was fixed in 11.2.0.4. So the correct behavior is on 11.2.0.4 and not on older versions.
SQL> select distinct sal, empno from scott.emp order by deptno;
select distinct sal, empno from scott.emp order by deptno
*
ERROR at line 1:
ORA-01791: not a SELECTed expression
Solution: include all ORDER BY columns into Read more »
Database hang and Row Cache Lock concurrency troubleshooting
Issue background
This post will help to analyze Oracle database instance slowdown that can happen due to considerable row cache lock (enqueue) wait events. It’s is based on a real case of a database hang that I worked on recently. I must admit this type of situation does not appear often but it’s very dangerous since it can considerably slow down a database instance or even freeze it for a short period of time. In most cases SQL against ASH view and Systemstate dumps can help to nail down the problem unless this is an Oracle bug.
Usually it occurs suddenly and disappears quickly. See an example ASH graph below with brown peak that represents this type of concurrency: row cache lock wait events.
What is a Row Cache Enqueue Lock?
The Row Cache or Data Dictionary Cache is a memory area in the shared pool that holds data dictionary information. Row cache holds data as rows instead of buffers. A Row cache enqueue lock is a lock on the data dictionary rows. It is used primarily to serialize changes to the data dictionary and to wait for a lock on a data dictionary cache. The enqueue will be on a specific data dictionary object. This is called the enqueue type and can be found in the v$rowcache view. Waits on this event usually indicate some form of DDL occurring, or possibly recursive operations such as storage management, sequence numbers incrementing frequently, etc. Diagnosing the cause of the contention
Diagnosing the cause of the contention
How to fix ORA-12547 TNS lost contact when try to connect to Oracle
Issue / Oracle error
sqlplus scott/tiger
SQL*Plus: Release 10.2.0.5.0 - Production on Wed May 18 09:32:35 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
ERROR:
ORA-12547: TNS :lost contact when try to connect to Oracle.
Issue description
I saw that TNS connection issue along with ORA-12547 Oracle error several times, usually when trying to connect to Oracle database server on Unix / Linux host with an OS user that does not belong to oinstall group (Oracle binaries owner group). In this case, interesting enough that local TNS connection to database (when using tnsnames alias) works fine: Read more »