Problems with big SGAs (>200G) on Linux

I recently had an issue where a database with 240GB SGA (1 huge shared memory segment) configured with hugepages on a system with 512G RAM was suddenly becoming instable and new logons were denied with these error message:


ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
Linux-x86_64 Error: 22: Invalid argument
Additional information: 2667
Additional information: 1736718
Additional information: 215016800256

This was strange because ipcs -a showed all shared memory segments and all processes existed. It turned out that there is a known issue with the Linux feature “prelink” and very big SGAs (>200g). This MOS Note gives some details:

Connected to an idle instance, while database instance is running (Process J000 died, see its trace file kkjcre1p: unable to spawn jobq slave process ) (Doc ID 1578491.1)

Obviously, exadata customers also suffered from this issue and therefore prelink is disabled beginning with exadata version 11.2.3.3. Redhat is reporting the same issue in combination with Informix databases in Knowledgebase article: https://access.redhat.com/solutions/1186953

EM 13c: Do not change OPatch

In previous versions, it was “best practice” to always get the most current opatch (patch 6880880) from MOS. Unfortunately, with Enterprise Manager Cloud Control 13c, this is problematic at the moment. The reason is that OMS 13.1 is shipped with OPatch 13.6:

[oracle@em13c ~]$ opatch version
OPatch Version: 13.6.0.0.0
 
OPatch succeeded.

Currently, OPatch 13.6 is not available in MOS. Only OUI Nextgen 13.2 is available, but not opatch 13.6. Do not overwrite OPatch 13.6 with OUI Nextgen 13.2 because it will break it.

EM12c: opatchauto failed with error code 231

When trying to patch OMS 12.1.0.5 in a VirtualBox environment with latest OMS PSU, I came across a strange problem which took quite a while to solve. The opatchauto apply and also -analyze commands failed every time with this error after several minutes of hanging:

opatchauto failed with error code 231

Manual connect to WLS console with relevant protocol/host/port/username/password was working fine. Then I realized that there was an issue with the entropy pool on the VirtualBox VM.

I followed this Note to resolve the isse:

E1: OS: Linux Servers Hang or Have Delays on Any JAVA Process Affecting Performance (Doc ID 1525645.1)

After implementing rngd, the patching worked successfully without any hangs.

AWR Warehouse – security issue

During implementation of AWR Warehouse, I discovered that AWR warehouse is using temporary staging schemas in the AWR warehouse repository database. These schemas life approximately for the duration of a datapump import job and are then dropped again. Due to the fact that the used password is not compliant with customers password verification function, the jobs failed.

v_sql := ‘ CREATE USER ‘ || STAGING_SCHEMA || ‘ IDENTIFIED BY SYS_GUID ‘ ||
‘ DEFAULT TABLESPACE ‘ || tbsname;

The staging schemas are created with the password “SYS_GUID” in capital lettters. This looks to me like the developer tried to generate a random string as password but instead overlooked that the password is set to fixed string “SYS_GUID” instead.

Oracle support has noticed this issue and filed an enhancement request. It is a pity, that this is not filed as a bug, but an enhancement.

Well, I hope this improves in a future version together with dynamic retention and purging options as well as customizable staging directories.

Happy AWR´ing.

OPatch bugs when applying Grid Infrastructure 12.1.0.2.5

For one of my clients, I experienced several issues with applying PSU 12.1.0.2.5 with opatch 12.1.0.1.9.

There were some code changes in opatchauto, which are not yet production-ready. I am looking forward to seeing a new opatch release (maybe 12.1.1.10 or 11) which has these issues fixed.

  • Bug 22091017 : OPATCHAUTO -ANALYZE COMMAND SHUTS DOWN THE RDBMS HOME IN 12C
    “opatchauto apply -analyze” is normally used before patching to verify if there are any conflicts. In our case, the “opatchauto apply -analyze” did a shutdown of the database instances. Not good.
  • BUG 22202019 – OPATCHAUTO FAILS WITH “TOO MANY ARGUMENTS” FROM CHECKSPFFILE SCRIPT
    When applying GI PSU patch to RDBMS Home and database instances are already stopped, patch apply fails. The reason is that opatchauto is trying to query database instances for information before shutting down instances. If instances are already stopped, these queries fail and opatchauto aborts.
  • OPatchauto is shutting down database instance with “srvctl stop home -stopoption TRANSACTIONAL”. I was of the opinion that this is doing a “shutdown transactional”, which in turn will wait until the last session is performing a commit. This can take a long time. Support Engineer explained that the wait will be limited to 10min, after which the system is begin stopped with “shutdown abort”. This is not documented in “http://docs.oracle.com/database/121/RACAD/srvctladmin.htm#RACAD5040” and in my opinion a bug. I expect the shutdown to be “immediate” whithout delaying the patching process.

Oracle releases new Standard Edition 2 for 12.1.0.2

Customers have been waiting for a Standard Edition release for version 12.1.0.2. Then, in July, a support engineer leaked that there will be a whole new type of “Edition”, called “Standard Edition 2” to replace both “Standard Edition” and “Standard Edition One”. The respective MOS Note disappeared shortly after the accidental publication. Now today the release finally became official and can be downloaded via OTN. The “SE2” is priced exactly as the previous “Standard Edition”. SE2 is limited to a single server with 2 CPU sockets, or when used with RAC to two servers with one socket each. In addition, there seems to be a limit of 16 CPU threads (e.g. 8 cores with hyperthreading) when using one host and 8 CPU threads each for both nodes when using RAC.

So beginning with 12.1.0.2, there will be no more “Standard Edition (4 CPU socket limit)” or “Stanard Edition One (2 socket limit)” releases anymore. There is no way to avoid going to “Standard Edition 2 (2 socket limit)” in the future.

This might mean for SE customers, which were using 4 CPU sockets (occupied) on a single host or a 2-node RAC Cluster with 2 CPU sockets (occupied) per host, that they have to remove half the (occupied) sockets from their installation. I am not sure what happens with already bought SE licences and how they can be “migrated” to SE2 licenses. I hope that this will not start a new wave of license-driven Oracle->PostgreSQL/MySQL/MS SQL Migrations.

Now would probably be the right time for 11gR2 Standard Edition users to start upgrade tests for 12c to gather experience. On the other hand, waiting until end of year and bugs found by “early adaptors” might safe you some troubles.

Oracle Disaster Recovery with Microsoft Hyper-V Replica

There is one very interesting feature of Microsoft Hyper-V called Replica, which allows replication of Virtual Machines without shared storage. With Hyper-V Replica, one can set up Oracle Database VMs to replicate between independent nodes. This document (in german) describes the setup in more detail:

Hyper-V Replica für Oracle Database

Enterprise User Security – Presentation Material available

On the presentations page you can find the my presentation material (german) from DOAG regional meeting September 2014 in Munich about “Enterprise User Security”.

11.2.0.4 DBUA silently changing NLS_TERRITORY init.ora Parameter during upgrade

In case you are planning to perform any upgrades with DBUA, double-check that the init.ora parameters after the upgrade. In a production upgrade at a client site, dbua silently changed init.ora parameter NLS_TERRITORY from GERMANY to AMERICA. It was only noticed when after the upgrade, decimal and grouping number characters were swapped, leading to application problem.

Support had to admit that it is unpublished bug 16538186 and that there is NO documentation of it anywhere at the moment.

We learned from this to double-check init.ora parameters after dbua with before upgrade parameters.

Index Clustering Factor finally more realistic

I just stumbled upon this bug reference on My Oracle Support:

Bug 13262857  Enh: provide some control over DBMS_STATS index clustering factor computation

This enhancement was long due. Previously, when computing the clustering factor during gathering statistics, the value was incremented, whenever the row was not found in the same block as the previous row. Now, it is finally possible to determine how many blocks should be considered when computing clustering factor. The patch delivers an improved DBMS_STATS package body that can be used to set preferences with value TABLE_CACHED_BLOCKS.

The flaw in the over-simplistic and pessimistic original computation was something that Jonathan Lewis documented in his Book “Cost Based Fundamentals” in year 2006 along with an workaround to manually gather improved clustering factor values!

The relevant chapter of this book is available as PDF at this URL.