Category Archives: Best Practices

Today’s Nugget: Oracle GoldenGate on E-Business Suite Databases

Throughout my work week, I often learn something new and unexpected about my travels. Instead of writing a full blog post about the subject, I figure I’ll occasionally post these as “nuggets” – bite-size chunks which are easily digested!

Here is today’s “nugget”:

Oracle GoldenGate is certified to capture data from E-Business Suite databases, but it cannot be used to apply data to E-Business Suite databases.

This includes writing back to the source database in an active-active configuration.

 

 

Advertisements
Tagged , , , , ,

Oracle Critical Patch Update for July 2015

Oracle’s Critical Patch Update is out for July 2015:

http://www.oracle.com/technetwork/topics/security/cpujul2015-2367936.html

Affected are database versions 11.1.0.7, 11.2.0.3, 11.2.0.4, 12.1.0.1 and 12.1.0.2.

This is the final patch for both the 11.1.0.7 and 11.2.0.3 releases. The final patch for 12.1.0.1 will be released in January 2016.

The most prominent bug on the risk matrix is CVE-2015-2629 whereby a remote authenticated user can exploit a flaw in the Java VM component to gain elevated privileges.

For the 11.2.0.4 patches, you can apply one of the following:

11.2.0.4 SPU for UNIX: patch 20803583
11.2.0.4.7 PSU for UNIX: patch 20760982
11.2.0.4.17 Quarterly Database Patch for Exadata (July 2015): patch 21142006
July 2015 Quarterly Full-Stack Patch for Exadata: patch 21186703

Don’t forget your Grid Infrastructure patching:

11.2.0.4 PSU for UNIX: patch 20996923

And, of course, ever since those Java bugs were discovered, we should also patch the JVM:

11.2.0.4.4 Database PSU for UNIX: patch 21068539

Happy patching!

Tagged , , ,

Exadata Critical Issue EX21

Oracle announced a new Exadata Critical Issue this morning (EX21) which applies to the ESS software versions 12.1.1.1.2 and 12.1.2.1.1.

“This issue is encountered only when a disk media error occurs while synchronous I/O is performed. Because the majority of I/O operations issued with Exadata storage are done asynchronously, and this problem is possible only when disk media errors are experienced while synchronous I/O is performed, the likelihood of experiencing this problem is low. However, the impact of hitting this problem can potentially be high.

This problem affects Exadata Storage Server software versions 12.1.2.1.1 and 12.1.1.1.2.

Disk corruption symptoms are varied. Some corruptions will be resolved automatically by Oracle Database, while other corruptions will lead to unexpected process shutdown due to internal errors.”

ESS 12.1.1.2.1 DOES have a patch available, but 12.1.2.1.1 does not at the moment (the patch is “pending”).  I’m sure it will become available soon.

I have MOS email me whenever the Exadata Critical Issues document (1270094.1) is updated so I’m quickly aware of the latest important bugs. It’s pretty neat and I’d advise other Exadata types to make use of it as well.

Tagged , , , ,

Exadata: why a half-rack is the “recommended minimum size”

Lots of shops dipped their toes in the Exadata water with a quarter-rack first of all.

(For those who are new to the Exadata party and don’t know of a world without elastic configurations, a quarter-rack is a machine with two compute nodes and three storage cells).

If you are / were one of those customers, you’ll probably have winced at the difference between the “raw” storage capacity and the “usable” storage capacity when you got to play with it for the first time.

While you could choose to configure your DATA and RECO diskgroups with HIGH redundancy in ASM, did you notice that you couldn’t do the same with the DBFS_DG / SYSTEM_DG?

Check out page 5 in this document about best practices for consolidation on Exadata.

“A slight HA disadvantage of an Oracle Exadata Database Machine X3-2 quarter or eighth rack is that there are insufficient Exadata cells for the voting disks to reside in any high redundancy disk group which can be worked around by expanding with 2 more Exadata cells. Voting disks require 5 failure groups or 5 Exadata cells; this is one of the main reasons why an Exadata half rack is the recommended minimum size.”

Basically, you need at least 5 storage cells for each Exadata environment if you want to have true “high availability” with your Exadata machine.

While quarter-rack machines have 3 storage cells, half-rack machines have 7 or 8 storage cells, depending on the model.

Let’s say that you have the model with 8 storage cells:  if you split a half-rack machine equally, you’ll have 2x quarter-rack machines with 4 storage cells, so you would need one more storage cell per machine to provide HA for the SYSTEMDG / DATA_DG diskgroup.

For some reason, this nugget escaped my attention until recently.  Even more reason to have a standby Exadata machine at your DR site …

Mark

 

Tagged , , , , ,

Oracle OpenWorld 2015

Submitted my Oracle OpenWorld 2015 presentation earlier.  Today is the last day to submit proposals for presentations or tutorials.

Oracle have extended their deadline for proposals until May 6th!

 

 

Tagged , , , , ,

Exadata System Statistics

Since August 2012, the DBMS_STATS.GATHER_SYSTEM_STATS procedure has offered an ‘EXADATA‘ option to allow Exadata-specific system statistics to be gathered.   The following versions / patchsets of the database include this option:

  • 11.2.0.2.18
  • 11.2.0.3.8
  • 11.2.0.4
  • Any version of 12c

Gathering Exadata-specific system statistics ensures the optimizer is aware of the Exadata performance features and takes them into account when determining the execution plan – often resulting in SmartScans (full-table scans) instead of indexes.

To determine the last time that system statistics were gathered on the database:

COL statistic FORMAT a55
COL value FORMAT a20
SELECT pname AS statistic,
pval2 AS value
FROM aux_stats$
WHERE pname IN (‘STATUS’,’DSTART’,’DSTOP’)
ORDER BY pname;


DSTART                                                          03-28-2011 13:59
DSTOP                                                            03-28-2011 13:59
STATUS                                                          COMPLETED

This indicates that the system statistics were not run AFTER the ability to compile Exadata-specific statistics was made available (August 2012).  Also, they have not run since this particular database migrated from a V2 machine to an X3-2 machine earlier in the year, thus they are unlikely to be accurate.

This was the likely cause of a performance problem we encountered recently, which eventually required a FULL hint to be made to the optimizer to allow the query to complete in an acceptable timeframe.

To determine the values of the system statistics (I used DECODE to format them nicely):

SELECT DECODE(pname,
‘CPUSPEED’,’CPUSPEED: (Workload) CPU speed in millions of cycles/second’,
‘CPUSPEEDNW’,’CPUSPEEDNW: (No Workload) CPU speed in millions of cycles/second’,
‘IOSEEKTIM’,’IOSEEKTIM: Seek time + latency time + operating system overhead time in milliseconds’,
‘IOTFRSPEED’,’IOTFRSPEED: Rate of a single read request in bytes/millisecond’,
‘MAXTHR’,’MAXTHR: Maximum throughput that the I/O subsystem can deliver in bytes/second’,
‘MBRC’,’MBRC: Average multiblock read count sequentially in blocks’,
‘MREADTIM’,’MREADTIM: Average time for a multi-block read request in milliseconds’,
‘SLAVETHR’,’SLAVETHR: Average parallel slave I/O throughput in bytes/second’,
‘SREADTIM’,’SREADTIM: Average time for a single-block read request in milliseconds’
) AS statistic,
pval1 AS value
FROM aux_stats$
WHERE pname IN (‘CPUSPEEDNW’,
‘IOSEEKTIM’,’IOTFRSPEED’,
‘SREADTIM’,’MREADTIM’,
‘CPUSPEED’,’MBRC’,
‘MAXTHR’,’SLAVETHR’)
AND sname = ‘SYSSTATS_MAIN’
ORDER BY pname;

CPUSPEED: (Workload) CPU speed in millions of cycles/second
CPUSPEEDNW: (No Workload) CPU speed in millions of cycles/second                            2351.43
IOSEEKTIM: Seek time + latency time + operating system overhead time in milliseconds 10
IOTFRSPEED: Rate of a single read request in bytes/millisecond                                      4096
MAXTHR: Maximum throughput that the I/O subsystem can deliver in bytes/second
MBRC: Average multiblock read count sequentially in blocks
MREADTIM: Average time for a multi-block read request in milliseconds
SLAVETHR: Average parallel slave I/O throughput in bytes/second
SREADTIM: Average time for a single-block read request in milliseconds

If the value for the MBRC system statistic is NULL, the optimizer uses the value for db_file_multiblock_read_count which, on this database, is 64.

Exadata benefits from higher values for MBRC as this increases the likelihood that the optimizer will choose to perform full table scans over an index.  Gathering Exadata system statistics will set the MBRC at 128 and will likely set a significantly higher value for the IOTFRSPEED.   These statistics are set based on your machine, not gathered, because:

  • the database won’t take the storage cells into account when calculating multi-block reads
  • direct path reads are not counted as multi-block reads for the MBRC system statistic

We should NOT gather system statistics with a workload on Exadata as the database will attempt to calculate the MBRC rate itself, likely resulting in a significantly lower (and inaccurate) MBRC value.

Gathering Exadata system statistics is pretty simple and should be done if the statistics are older than August 2012 or when you migrate a database to an Exadata machine. We can either back up the stats beforehand or keep a note of their values which we can use to manually set if required, then issue the following command:

EXEC DBMS_STATS.GATHER_SYSTEM_STATS(‘EXADATA’);

Tagged , , , ,

DBA 3.0 – How to Become a Real-World Exadata DBA – IOUG Collaborate 2015

According to a Book of Lists survey, 41% of people’s biggest fear is “public speaking”.  To put that into perspective, “death” is the biggest fear for 19%, “flying” for 18% and “clowns” don’t even register (which does make me seriously doubt the survey’s credibility).

I gave my first public presentation at IOUG Collaborate 2015 last week in Las Vegas and I didn’t die.

Why did do make your presentation debut at the second largest Oracle event on the calendar?  Excellent question.

Continue reading

Tagged , , , ,

You WILL use container databases … and you WILL like it!

For those who haven’t climbed aboard the 12c train yet, note that using a “traditional” or non-CDB architecture is deprecated by Oracle from 12.1.0.2.

That does not mean you have to use the multi-tenant cost option: container databases with a single pluggable database (“single-tenant”) will be OK.  The cost option only comes into play whenever you attach more than one pluggable database to a container database (“multi-tenant”).

I’m still surprised that Oracle charge you for the multi-tenant option, to be honest.

Earlier in the week, Oracle released patch set 12.1.0.2.3. I’m not sure why Oracle didn’t call 12.1.0.2 “12c Release 2 / 12.2”, as it includes a lot of major new features rather than just bug fixes, but I’m sure their marketing types had a reason.

In reality, this means that “12c Release 2, Patch Set 1” is now available. There are a lot of DBAs out there, myself included, who adopt a “wait until Release 2, Patch Set 1” approach before getting serious about upgrading to a new version. If you’re one of those DBAs, happy upgrading!

Tagged , , , , ,

Oracle’s Critical Patch Update for January 2015

Oracle announced their Critical Patch Update for January 2015 today.

The CPU includes a fix for this troubling exploit in E-Business Suite found by David Litchfield where EBS grants index privileges on the (SYS-owned) DUAL table to the public role by default.

The database exploit with the highest Homeland Security threat level is CVE-2014-6567 which could allow for pre-12c databases on Windows to be “entirely compromised”.  If you’re not running pre-12c databases on Windows, the threat score is noticeably reduced, but still a 6.5.

In other news, 12.1.0.2.3 is out, should you live your life on the bleeding edge of technology.  Quarterly Full Stack Download Patches for Exadata are referenced in the availability note but don’t yet link to public documents; no doubt they will soon.

SSL 3.0 is disabled by default in Java SE – thanks to POODLE (really), it’s now considered obsolete and SSL as a whole should be disabled as organizations “can no longer rely on SSL to ensure secure communications between systems”.

Quite a scary world out there, huh?

MOS reference notes: 1935468.1, 1942215.1

Tagged

My Collaborate IOUG 2015 Abstract

I will be presenting DBA 3.0 or “How to Become a Real-World Exadata DBA” at Collaborate 2015 – IOUG’s annual user conference – from April 12th to 16th at the Mandalay Bay Resort and Casino in Las Vegas. I submitted this as my abstract:

“DBA resources are more scarce than ever before and it can be very difficult to allocate time on anything but keeping the lights on – even when an organization has made a (substantial) hardware investment in Exadata.

However, if Exadata is treated like any other Oracle database, the promised “extreme performance” will likely be very underwhelming to developers, users and managers and can become unwieldy for DBAs to support.

On the other hand, when an organization configures and supports Exadata properly, they can realize exponential performance improvements in key IT infrastructure, can facilitate better business decisions and may actually reduce infrastructure costs.

The customer has bought a sports car – but might not realize that they haven’t taken it out of second gear (yet).

I will talk about the evolution of Exadata and then get into the “nuts and bolts” of how to support a high-performance Exadata environment as a Production DBA.

I will discuss how to get performance improvements of up to 20x, what NOT to do as an Exadata DBA and how Exadata can become the foundation of your organization’s high-performance enterprise infrastructure.”

I hope to see you in Las Vegas!

Tagged , , ,
%d bloggers like this: