I love valuable metric data and I wanted to see what’s offered from different cloud providers. Metrics are data and data ensures that when performance is impacted, we have the information we need to answer questions and resolve issues.
I encountered an issue today trying to create a table in an Oracle 12cR2 database, the DDL for which, I extracted from an Oracle 11gR2 database. The error returned when trying to create the table was:
ORA-54002: only pure functions can be specified in a virtual column expression
The definition of the table included a Virtual Column which used a REGEXP_REPLACE call to derive a value from another column on the table.
Here is a simplified (more...)
Very often, significant performance benefits can be obtained by using some very basic knowledge of the application, its data and business rules. Sometimes even less than that: even if you are not familiar with the application logic at all, you can still use common sense to make some reasonable guesses that would get you a long way in improving query’s performance. Here is an example (based on an actual query that I had to tune (more...)
It’s barely a week since 5.1.16 dropped and here we have VirtualBox 5.1.18!
I’ve done the installation on my Windows 7 desktop at work. I’ll do the updates of the Mac and Oracle Linux 6 hosts over the weekend probably.
VirtualBox 5.1.18 was first posted on March 16, 2017 at 2:58 pm.
©2012 "The ORACLE-BASE Blog". Use (more...)
My website has been using HTTPS for over 18 months now. A couple of months ago I came across Let’s Encrypt and thought, “Free Certificates? That’s interesting!”, so I gave it a shot for some other stuff I look after, just to see how I got on with it.
The certificates only last 3 months, but you can automate their renewal using CertBot. You set it up to check every day, and the certificates get renewed (more...)
Recently I was applying the data dictionary part from an (exadata bundle) patch and ran into the following errors:
ORA-24324: service handle not initialized ORA-24323: value not allowed ORA-27140: attach to post/wait facility failed ORA-27300: OS system dependent operation:invalid_egid failed with status: 1 ORA-27301: OS failure message: Operation not permitted ORA-27302: failure occurred at: skgpwinit6 ORA-27303: additional information: startup egid = 1001 (oinstall), current egid = 1002 (dba)
This was very weird, I had just (more...)
A couple of articles have crept out over the last few days that relate to online operations on Oracle Database 12c Release 2 (12.2). Both of them are pretty small in terms of what you have to do, but are pretty awesome in terms of impact, so I’m giving them the official thumbs-up!
The first one is about moving tables online.
There’s a nice little touch to the “set feedback” command in SQL PLus in 12.2. There is a new “only” clause in the SET FEEDBACK command, so you can run queries but the resulting rows are not shown in the display. They are still queried, fetched and “returned”, but just rendered to the screen. Here’s an example
$ sqlplus hr/hr SQL*Plus: Release 188.8.131.52.0 Production on Tue Mar 14 22:59:15 2017 (more...)
Geeks around the world will be celebrating Pi day, a shameless excuse to put the month before the day like the Americans do so we can talk Mathematics on “3.14” day
So what better way to show how cool SQL can be with some expressions to approach Pi with continuous series
All we really need to know by way of background is the CONNECT BY LEVEL trick to generate arbirtary sequences of row, eg
Database query tuning is mostly about getting better plans. Mostly, but not always. Sometimes, the problem has nothing to do with the plan, and you might need to get a bit creative to find a solution. In this recent case a query was showing a decent performance when running from SQL Developer, but it took about 5 times longer to complete when running from R. The plan was the same, so I knew that it (more...)
One of our recent successful migrations was a single instance Oracle EBS 12cR1 database to Oracle Super Cluster M7 as a RAC database with 2 instances on the same DB version (184.108.40.206). Subsequently, the customer wants to run through EBS cloning and set up an Oracle active data guard configuration.
The target systems are not Super Cluster. (more...)
Result! I have finally been able to gather a complete RDA (Oracle Remote Diagnostic Agent) output on my 2 node RAC system. After consulting the relevant documentation on MOS-which is spread over at least 42 Doc IDs-I found them not to be very helpful to the degree that some of what I read is actually wrong or contradicting. I put together a short note, primarily to myself, to remind me of the process. I hope you find it useful, (more...)
Removing the SYSMAN schema and other OEM-related objects from a database consisted of disabling constraints and running a series of DDL scripts in earlier versions of OEM. Always use RepManager to perform this cleanup for OEM 13c.
RepManager ‘-drop’ command drops the SYSMAN, SYSMAN_MDS, SYSMAN_OPSS, SYSMAN_RO, and SYSMAN_BIPLATFORM schemas and removes their artifacts, including tablespaces and datafiles.
Note: The following run-time example has been simplified from actual stdout and uses a reference to $MW_HOME (more...)
“The only thing you can do easily is be wrong, and that's hardly worth the effort.” ― Norton Juster, The Phantom Tollbooth
Oracle provides two parameters that affect the PGA that look very similar but operate very differently. One of these parameters is the well-known pga_max_size and the otheris a hidden parameter, _pga_max_size. Let’s look at both and see how one can be very effective while the other can create problems with respect to (more...)
Swingbench is a one of the best choices for easy loads on a database. I wanted to use it against the SH sample schema I loaded into my Oracle Source database and I haven’t used Swingbench outside of the command line quite a while back, (my databases seem to always come with a load on them!) so it was time to update my Swingbench skills and catch (more...)