A quick fly through the options for conditional uniqueness.
Requirement #1: I want uniqueness on a column but only under certain conditions.
For example, I have an active flag and I want to make sure there is only one active record for a particular attribute but there can be many inactive rows.
create table t1
(col1 number not null
,col2 varchar2(24) not null
,is_active number(1) not null
,constraint pk_t1 primary key (col1)
According to http://en.wikipedia.org/wiki/Perfect_storm:
A “perfect storm” is an expression that describes an event where a rare combination of circumstances will aggravate a situation drastically.
The other day we had a bit of a panic when AUD$ space grew by 45GB in 15 minutes and set off some alerts.
The audit_trail parameter was set to DB, EXTENDED.
Behaves the same as AUDIT_TRAIL=DB, but also populates the SQL bind and SQL text (more...)
When the latest quarterly Patch Set Update (PSU) came out from Oracle, we planned to get it in place as soon as reasonable due to our need to stay current with security patches, and my need to apply what I had assumed were well-tested bug fixes for our 22.214.171.124 installations. However we were in for an unpleasant surprise.
We first applied the April 2014 PSU on our staging & development database hosts (more...)
TL;DR: The archive_lag_target parameter will force log archiving in Standard Edition.
Just a quick note here that I wanted to share since I didn't see anything directly confirming this when I was searching around.
I have an Oracle 11gR2 Standard Edition (SE) database that I'm also maintaining a manual standby for, since Oracle Data Guard is not available in SE. I created a metric extension in EM12c to alert me if the standby is more (more...)
A few weeks ago, our customer support team asked us to automate part of their checklist that looks at the number of active sessions in our production database. In EM12c, this seemed like a no-brainer with the Average Active Sessions metric. So I added this to my production incident ruleset and went back to another project. Over the next few days we'd get pinged by EM12c but the support folks would say it shouldn't. After (more...)
A few weeks back, we began making changes to prepare for using Oracle Golden Gate. One of the first steps required is to enable "minimal supplemental logging" at the database level. We did this during an evening maintenance window. However by the time the morning workload picked up, we started seeing a lot of sessions blocking, and the root blocker was one of the DB Writer (DBWn) processes.
Looking at the blocked sessions, a query (more...)
This week I migrated our EM12c repository database to a new server as part of it's promotion to production status. Just to make it a little more exciting, the migration also involved an in-flight upgrade from 126.96.36.199 to 188.8.131.52. Much of this post is directly inspired by Martin Bach's post on the same subject
. I ran into a few other snags that weren't mentioned so I thought it would (more...)
This is the third part of the video tutorial "Analysing Parallel Execution Skew". In this part I show how to analyse a parallel SQL execution regarding Parallel Execution Skew.
If you don't have a Diagnostics / Tuning Pack license the options you have for doing that are quite limited, and the approach, as demonstrated in the tutorial, has several limitations and shortcomings.
Here is the video:
If you want to reproduce or play around with (more...)
Another annoying thing about unusable indexes
I’m surprised that I can’t remember coming across this before before.
I want to archive some data from a bunch of partitions.
Unfortunately, I can’t follow the virtuous circle of data lifecycling and partition the table in such a way that I can archive all the data from a particular partition (or subpartition) via exchange partition.
Without going into too much detail and skipping a bunch of other steps, (more...)
A minor update 4.01 to the XPLAN_ASH utility is available for download.As usual the latest version can be downloaded here.
These are the notes from the change log:
- More info for RAC Cross Instance Parallel Execution: Many sections now show a GLOBAL aggregate info in addition to instance-specific data
- The Parallel Execution Server Set detection and ASSUMED_DEGREE info now makes use of the undocumented PX_STEP_ID and PX_STEPS_ARG info (bit mask part (more...)
Here is an example of surprising behaviour from a remote DB from an OTN forum thread
Setup a link to a remote DB (I’ve used an actual remote DB and not tested a loopback)
create table t1
create or replace view v1
select count(*) c1 from t1@l1;
Then alternate variations on this sequence of events:
1. On local DB execute SELECT:
SELECT * FROM v1;
2. On remote (more...)
Last Friday I kicked off a database backup to an NFS destination, using the standard "backup as compressed backupset database" syntax. Loyal readers of this blog may recall that I'm the proud custodian of a 25 Tb database, so this backup normally takes a few days, with an expected completion on Monday morning. However it was still running on Wednesday, and reviewing the logs I saw that there was just 1 channel (of the original (more...)
Here’s an example of where the optimizer really should ignore a hint.
I’m testing an implementation of deliberately unusable index partitions – i.e. some empty index partitions, some usable.
This is 184.108.40.206 so whilst there are enhancements in 12c, they’re no use here.
SQL> create table t1
2 (col1 number)
3 partition by list(col1)
4 (partition p0 values(0),
5 partition pdef values(default));
SQL> create index i1 on t1 (col1) (more...)
I recently had to change the db_unique_name of a database to make it jive with our typical database/DataGuard naming policy of appending the datacenter location. For the sake of this post let's say it was changed from ORCL to ORCL_NYC, since this database is in our fictional New York City datacenter.
I did a quick set of tests and thought I'd share the findings to save anyone any unpleasant surprises. Here are the things to (more...)
Readers of an earlier post on this blog
will know about my latest forays into the world of Direct NFS. Part of that means stumbling over configuration hiccups or slamming into brick walls when you find new bugs.
To quickly re-set the table, my organization purchased the Oracle ZFS Storage Appliance (ZFSSA) 7420
. Oracle sold us on the Infiniband connectivity as a way to make a possible future transition to Exadata easier. However the pre-sales (more...)
When I sat down at my desk yesterday morning I was greeted with some disturbing email alerts notifying me that one of the NFS mounts on my standby database host was full. This was the NFS mount that held an image copy of my database that is updated daily from an incremental backup. The concept and an example can be found in the documentation
. With a 25Tb database, waiting to restore from backups is not (more...)
My friend asked me to check about error in alert log file - "dispatcher 'D000' encountered error getting listening address
". After checked, I found.
Wed Feb 12 09:46:27 2014
dispatcher 'D000' encountered error getting listening address
Wed Feb 12 09:46:27 2014
found dead dispatcher 'D000', pid = (17, 154)
I checked trace file about d000 processed.
Trace file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_d000_31988.trc
Oracle Database 11g Release 220.127.116.11.0 - 64bit Production
Today, while tuning a fairly complex query experiencing wrong cardinality estimates, I noticed something I was not aware of. Hence, I thought to write this short post to illustrate how to reproduce the problem I experienced…
- Create the test table (notice the correlation between the data of the two columns):
SQL> CREATE TABLE t
3 SELECT mod(rownum,50) AS n1, mod(rownum,50) AS n2
4 FROM dual
5 CONNECT BY level < = 1000;