Oracle PatchSet upgrade issues ORA- PLS-

May 15, 2014 by 4 Comments 

Below is a list of issues (ORA- and PLS- errors) I encountered after upgrading Oracle 11gR2 Database Enterprise Servers to the latest Patchset
The upgrade mostly done:
– from
– to (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.
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 since developers happily used an Oracle Bug that was fixed in So the correct behavior is on 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 »

Oracle Configuration Manager OCM installation administration

May 9, 2014 by Leave a comment 

OCM Proactive database monitoring with MOS

OCM proactive database monitoring with MOS

The Oracle Configuration Manager (OCM) is a tool to proactively monitor your Oracle environment (your Oracle database in particular) with combination to My Oracle Support (MOS) website. OCM uploads this configuration information to Oracle Customer Configuration Repository on a regular basis and replicates this to My Oracle Support so that you and MOS engineers can view your Oracle database configuration, see alerts, and perform health-checks against your Oracle database. OCM also simplifies and speeds up a creation and handling of MOS Service Requests.

How to download latest OCM

All Oracle customers are enabled for Configuration Manager by default and they obtain the latest version of Configuration Manager (OCM) with different ways. Read more »

Oracle DBaaS in Private Database Cloud workshop

April 28, 2014 by Leave a comment 

Designing Database as a Service (DBaaS) in own Database Cloud is very comprehensive topic and it requires a lot of things: clear concept and strategy, solid planning, important architecture decisions, considerable involvement of different stakeholders, extra budget, etc. To start talking about this topic I’ll give my own definition of DBaaS which is a managed database service, hosted and offered by DBaaS provider on pay-per-usage basis, which provides access to own database resource with flexible on-demand system scaling capabilities. DBaaS on high level consists of three major pillars: Business, Operation and Technology and you cannot start with the most interesting one, which is certainly Technology (at least for me :).

Oracle DBaaS in Private Database Cloud workshop

DBaaS workshop in Munich with Ralf Durben and Manual Hossfeld

Last week being a part of exclusive Oracle Database Day workshop I learned and discussed the possibilities of building a DBaaS based on Private Database Cloud with best Oracle Germany experts in Munich. This time we talked exactly about DBaaS Technology and specifically about Oracle Cloud Management capabilities. With help of Oracle Enterprise Manager (OEM) 12c Cloud Control and OEM Self Service Portal you can establish a framework for end-users to provision own DBaaS components with a few mouse clicks. In Oracle terms these DBaaS components can be the following:
Read more »

Database hang and Row Cache Lock concurrency troubleshooting

April 14, 2014 by Leave a comment 

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.

ASH graph - Row Cache Lock concurrency

ASH graph – Row Cache Lock concurrency

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

Read more »

Oracle database housekeeping methods – ADR files purge

March 28, 2014 by 1 Comment 

Since Oracle Database 11g, all traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more files are stored in the ADR, a file-based Automatic Diagnostic Repository for simplified database diagnostic data management. In spite of that new 11g feature, the housekeeping or purging of those files got not considerably simplified. Unfortunately the new Oracle utility ADRCI can not purge all the files that Oracle database generates in ADR. Thus, you should use the other Unix OS methods and tools to accomplish Oracle 11g database housekeeping. In previous posts I described the Unix log rotation mechanism and SYS auditing files purge in adump directory. This time we will talk exactly about ADR purge functionality. But before, let’s look at some basics.

Location for Diagnostic Traces

Location of Diagnostic Traces of Oracle ADR

Location of Diagnostic Traces in Oracle 11g ADR

The table shown above in the slide describes the different classes of trace data and dumps that reside both in Oracle Database 10g and 11g.
With Oracle Database 11g, there is no distinction between foreground and background trace files. Both types of files go into the $ADR_HOME/trace directory.
All non-incident traces are stored inside the trace subdirectory. This is the main difference compared with previous releases where critical error information is dumped into the corresponding process trace files instead of incident dumps. Incident dumps are placed in files separated from the normal process trace files starting with Oracle Database 11g. We can also check V$DIAG_INFO view lists all important ADR locations including.

ADR retention policy – automatic purge

A retention policy allows you to specify how long to keep the diagnostic data. ADR incidents are controlled by two different policies:

The incident metadata retention policy controls how long the metadata is kept around. This policy has a default setting of one year (default is 8760 = 365 days = 1 year). Here are the components that currently get purged by LONGP_POLICY:

ALERT — files in the ./alert directory
INCIDENT – files in the ./incident/incdir_ directory.
SWEEP — files in the ./sweep directory
STAGE — files in the ./stage directory
HM — files in the ./hm directory and metadata in the HM schema

The incident files and dumps retention policy controls how long generated dump files are kept around. This policy has Read more »

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email