My first contact with the log miner

I have never really played with log miner, but used it many times implicitly for streams. The need to get to grips with it arose when a developer created unrecoverable operations (i.e. something non-logging) in a QA system. I don’t want them to run the same in production so I had to find out who did it. First of all I needed a test case on my 10.2.0.4 32bit Linux system. First some theory though.

Grey is all theory

When starting with log miner you have the options to chose from a number of dictionaries. From my understanding the dictionary is where log miner stores intermediate results and for translating object_ids to object names. You can chose from:

• online catalog
• online redo logs
• flat file

Using the online catalog is the simplest of these approaches, you need supplemental logging for using the online redo logs.

Once that’s build, you simply add the archived logfiles you are interested in (can include the online logs if you like) and start the log miner session. The contents of the analysis is in v$logmnr_contents. When done with your investigation, you close the log miner session.

I opted to use the flat file approach for now, here’s the example.

Restore table statistics

This can be a common problem for production DBAs. Let’s assume that query performance has deteriorated after gathering of statistics. Since Oracle 10g the stats are automatically preserved (check select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual to find out for how long) and can be restored. In 11.1 and later, it’s even better in the way that you can defer statistics publishing (see this post for more information).

This is actually dead easy! Assume the following, very very basic setup:

SQL> create table s (id number);

Table created.

Let’s gather statistics for it, the table is empty.

SQL> exec dbms_stats.gather_table_stats(user,'s')

PL/SQL procedure successfully completed.

The view dba_optstat_operations lists the stats gathering operations against targets. As an interesting side effect, if you specify the table/index name in dbms_stats in lower case, the target column will list it in lower case as well.

Recreate the standby controlfile when using ASM

This is in reply to some work I was supposed to carry out over the weekend on a DR database. It could have been really simple, but it turned out it wasn’t. As part of a major migration I moved a 2TB database from OCFS to ASM, which worked really nicely with a Data Guard switchover operation. Trouble now was that the standby databases still references online redo log files on the file system rather than ASM. I wanted to get rid of these annoying messages by updating the control file which was already rectified on the primary.

Under normal (non ASM) circumstances that doesn’t cause any trouble, but since ASM has the naming convention “+DISGROUPNAME/db_unique_name” the standby controlfile isn’t that easy to replace. Well actually it is but I needed some more careful thinking.

The steps are quite simple:

• Create a standby controlfile on the primary: alter database create standby controlfile as ‘/tmp/primary.ctl’;
• Ship this file to the standby database server
• Create a backup of the existing standby controlfile just in case (rman: backup current controlfile format ‘/tmp/backup.ctl’)
• Cancel managed recovery: alter database recover managed standby database cancel;
• Shut the standby database down: shutdown immediate (if you’re a good citizen you also defer log shipping from the primary to avoid any errors poluting the alert.log and potentially triggering an alarm)
• Startup nomount
• RMAN: restore controlfile from ‘/tmp/primary.ctl’

A remark at this place: the controlfiles parameter usually uses OMF syntax when in ASM. Prior to 10.2.0.4 you couldn’t restore the controlfile if the spfile parameter was an OMF name, but it seems you can do this now. Users of other versions please reset the control_files parameter in the spfile and bounce the instance.

Explain plan doesn’t peek at variables

I was trying to experiment with the 11g SQL Plan Management feature when stumbing across this. You will most likely go “ohh-of course it doesn’t” but I didn’t know it for sure until now. It’s another reason the expected explain plan differs from the actual explain plan, as discussed on the oracle-l mailing list recently.

My test setup is 11.2.0.1 on RHEL 5.2 32bit, running para-virtualised on my laptop; your system might be different but I assume the general principle is still the same. So here we go:

SQL> desc test2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> var i number;
SQL> exec :i := 100

PL/SQL procedure successfully completed.

SQL> alter session set events '10053 trace name context forever,level 1';

Session altered.

SQL> explain plan for select id from test2 where id = :i;

Explained.

In the trace file (which again changed quite a lot from 10.2 I use in production), you find the bind variable section:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
 oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
 oacflg=00 fl2=1010000 frm=01 csi=46 siz=32 off=0
 kxsbbbfp=00e62024  bln=32  avl=00  flg=05

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TEST2"."ID" "ID" FROM "MARTIN"."TEST2" "TEST2" WHERE "TEST2"."ID"=TO_NUMBER(:B1)
kkoqbc: optimizing query block SEL$1 (#0)

You will notice the absence of the value of the bind variable. When I did my testing I randomly executed quieries with the explan plan option and without so this hasn’t become apparent at first. Re-executing the same statement without “explan plan” prefix yields the following result:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
 oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
 kxsbbbfp=007cca70  bln=22  avl=02  flg=05
 value=10

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TEST2"."ID" "ID" FROM "MARTIN"."TEST2" "TEST2" WHERE "TEST2"."ID"=:B1
kkoqbc: optimizing query block SEL$1 (#0)

Here we have the value in the relevant column. By the way, finding your trace file has become trivial in 11.1 and later:

SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3291.trc

Right, I agree this isn’t shaking the Oracle world but it cost me half an hour when developing my test case so I thought I’d share this with you. Bear in mind that your actual execution plan most likely will be different from the explain plan (with bind variables) so testing ACS and other new features will need to make use of dbms_xplan.display_cursor to fetch the plan from the v$ views.

UKOUG Conference-session review part 3

The final installment of this series deals with Tom Kyte’s top 10 features in 11.2 and Wolfgang Breitling’s “seeding statistics”.

Tom Kyte – Top 10 new 11.2 features

1) dbms_parallel_execution:

I was a little late so didn’t get the essence of this. Seems to automatically do what Tom described as “poor man’s parallelism” in his effective oracle by design book. Take a huge table and instruct Oracle to subdivide it into non overlapping rowid ranges and then give it a command to work on these ranges in parallel. All of this was possible before, but required manual skripting-now it should happen without that hassle. As always, this turns an atomic update into a number of atomic operations. Need to roll back: restore or flashback database.

2) More analytics:

listagg: create a whatever separated list
nth_value: return n-th field of a partition (as in max(cola) over (partition …))

3) Execute privilege on directory

Allows us to execute code (with ext table preprocessor) in a directory
Wasn’t too clear if we we need to get the output from the preprocessor to standard out?
Leaves a number of questions, most importantly: what about security? That preprocessor executes anything within the privileges of the oracle user. What about rm -rf $ORACLE_HOME if some malicious code replaces your own script?

4) Recursive subquery factoring

As Tom said: the recusrive subquery factoring is easier to understand if you don’t know connect_by yet. Otherwise the whole syntax is confusing at first, which I have to admit, and neither does it look more elegantly. I personally like the connect_by more, even though I haven’t used it for 22 years – apparently the feature has been in the database since version 2 of the kernel. The syntax of the new recursive subquery factoring can be found in the SQL Language Reference as part of tbe select statement

With the new syntax, you get the first (root) element, called anchor member. After you’ve got it, you add the recursive members. Main use: now ANSI compatible, don’t need to change the syntax for compatibility with db2 and sybase. Don’t know why this is hyped but never mind.

Let there be multipathing (when there wasn’t before)

One of the projects I was working on recently couldn’t be finished as it should have been due to a lack of switch ports in the SAN. The QA database servers therefore only had single pathing to the storage whereas the production kit (thank god!) had multipathing enabled and configured correctly.

This was far from ideal but couldn’t be helped, and it was also out of my control. You could argue that the project managers could have been aware of this and factor it in but I digress. On top of that I had a strange situation where the 3 nodes mapped the LNUs differently-what was seen as /dev/sda on node 1was /dev/sdf on the second node (but /dev/sda again on the third). The presentation of the LUNs to the database servers on the array was identical for each of the 3 nodes. How did I find out? I asked the storage admin to provide the WWIDs as seen on the management console and compared these against /dev/disk/by-id/. This only works with single pathing as explained further down.

The Linux kernel documentation states that the enumeration of devices isn’t static, and unfortunately there is no direct link between controller, target, disk and slice as in Solaris. These are RHEL 5.3 64bit system by the way, and Oracle 10.2.0.4.1 RAC.

There are 2 ways to get around this problem:

• Metalink note 414897.1 describes how to set up udev for this purpose (and it includes 11.2!)
• Use /dev/disk/by-id and /etc/rc.local

UKOUG conference 2009-session review part 2

This is the second part of my review of UKOUG conference 2009, day 1. Check out the first part as well! This picks up exactly where I left part 1 after Tom Kyte’s session.

Virtual Insanity

I remained in hall 1 to see the great presentation of James Morle. I have to say that even if you aren’t familiar with the subject of his presentations you should go to see him-he’s such a great presenter. The prank of today was a bottle of Oracle wine, distributed into 5 glasses (“everyone help yourself to your portion of Oracle”) to simulate the idea of virtualisation. James then offered insights into some of the VMWare internals alongside some competing offerings, mainly from Oracle and Cytrix (Xen) and Red Hat (KVM and RHEV). It seems the golden age of para-virtualisation is over, with AMD and Intel releasing so many features in their processors for hardware assisted virtualisation that VMWare’s offering caught up performance-wise. Personally I still love Xen (and I am writing this article on a virtual machine!) because it gives me all the performance I need on cheap hardware. I also don’t think the nehalem processors will make it into laptops, my venerable openSuSE 11.1 will support me for some more time. When it comes to performance, anything in running on vmware in userland will roughly match a physical box, but as soon as you enter kernel mode, due to modifications VMWare has to make in order for multiple instances of OSs to coexist. Again, this wasn’t tested on VT-d or IMMOU capable processors so your experience might be different. Overcommitting memory might work well with other than Oracle workloads, but James’s advise is not to use this feature in production.
All in all a very balanced presentation with the usual laugh at the beginning.

After this presentation I had some lunch and managed to see the folks from CERN which was interesting again. A great many of them were there to actually present and I briefly met Eva, their streams specialist who is responsible for pushing a lot of data from their site (tier 0) to sites all around the world (tier 1) for distributed computing. If I remember correctly this is part of the EGEE network but might be wrong there.

RAC round table

This was chaired by Julian Dyke and David Burnham (actually Julian had a problem with his voice which left him so David took over) and saw a great attendance from some reknown Oracle specialists: Piet de Visser, Phil Davies, David Burnham, Alex Gorbachev, Luca Canali, Mark Bobak, Jonathan Lewis to name just a few.

This was my first round table and I didn’t know what to expect. I greatly enjoyed the atmosphere up to the point where the discussion about block level replication started to drag on. The questions (and some answers) were:

• Block corruption with AIX 6.1 on p595 LPARs when accessing freshly created database (in ASM). Requires a lot of tracing on the net*8 layer all the way up a strace of the oracle process, can’t be diagnosed without a system. The block corruption didn’t result of a restore from a compressed backup, the system was freshly created via dbca.
• Update from Larry Carpenter about future of data guard/streams and golden gate. Very interesting stuff, in essence streams won’t go away and Golden Gate receives Log Miner code. Golden Gate will remain an independent product and won’t be assimilated in the streams group.
• There are plans in Oracle to extend RMAN in order to be able to restore across endian boundaries (not to be confused with the convert command!) Before that can happen, Phil Davis was sharing details of work on a project where Golden Gate could be used to cut downtime a lot, capturing changes while the rman convert was still running on the destination platform
• Using san block level replication:  mirroring oracle home and database to remote host still requires full license, unless you don’t mount it (or mount it only when the primary site is completely down)
• Phil Davies: does anyone know why in clusterware active/passive setups the cluster database resource doens’t write a trace file in $CRS_HOME/log/hostname/racg ? I didn’t, neither did anyone else. That question didn’t cover 11.2, but I didn’t have time to test cold failover clusters with 11.2 yet.
• RAC ONe node: really just a cold failover, additional benefit is mainly for maintenance. For RAC One Node you have to license only one node (apparently). Aimed primarily against VMWare

I will add yet another post about the final two sessions I attended about Tom Kyte’s top 10 11.2 features and Wolfgang Breitlings seeding statistics.

Do not shut down ASM when dropping a disk

I was doing some maintenance today with an ASM disk group (10.2.0.4.1 64bit on RHEL 5.3). In a nutshell, we swapped disks in the FRA to increase the capacity of the disk group. One of the nice things is that this can be done online (but not where I work), and in one command:

SQL> alter diskgroup fra drop disk 'FRA1' add disk 'ORCL:FRA3';

The keen observer will spot the use of ASMLib here. I have to say that ASMLib is a really nice tool. Exadata doesn’t use it which makes you think it’s deprecated internally. The download side had RPMs for RHEL 5.4 listed so we’re fine for some time.

Back to my problem. During the rebalance operation after the alter diskgroup command the 2nd node of the cluster went down. Oops, that wasn’t great: the entries in v$asm_operation were gone immediately even though there were a good 30 minutes remaining. A quick glance at the alert.log confirmed that the rebalance was indeed aborted:

Shutting down instance: further logons disabled
Tue Dec  1 14:32:33 2009
Shutting down instance (immediate)
License high water mark = 8
Tue Dec  1 14:32:33 2009
SQL> ALTER DISKGROUP ALL DISMOUNT
Tue Dec  1 14:32:33 2009
ERROR: ORA-1013 thrown in ARB0 for group number 3
Tue Dec  1 14:32:33 2009
Errors in file /u01/app/oracle/product/10.2.0/asm_1/admin/+ASM/bdump/+asm1_arb0_14234.trc:
ORA-01013: user requested cancel of current operation
Tue Dec  1 14:32:33 2009
NOTE: stopping process ARB0

A day at UKOUG Conference in Birmingham

What a great decision to go to UKOUG #tech_ebs on Monday! I’ve managed to see presentations from Wolfgang Breitling, James Morle and Tom Kyte (twice). So that was good. But the even better part was catching up with Julian Dyke, Piet de Visser (I promise I will read more of your blog!), David Kurtz, the guys from Nominet (feeling sorry for Jason who was on remote support duties today and I couldn’t see him), David Burnham and the e-DBA bunch. Thanks for the nice orange juice Mike! I saw Joel Goodman with Harald von der Brederode from a distance but couldn’t get through the crowd to say hello. Connor McDonald also attended the conference, but we have never really been introduced (he also used to work for e-DBA for a short period of time).

Over the following days I will try and milk it to get a number of blog posts, one per session I attended-the first is already done-see below.

Now, the conference certainly is a highlight, no doubt about it. I started my day with Tom Kyte’s “what are we stll doing wrong?” I immediately wanted to send all the “developers” I am working with to the talk. Simple, basic topics that haven’t changed for ages. Use of bind variables, simple non verbose but concise code. Oh how much I’d like to see this. No more “I didn’t write this query, hibernate did” excuses or “I don’t know where in the application that is executed”. You have to be a Java developer to appreciate the “beauty” of such code. As a DBA, I am not so sold on the whole thing. Neither do I like the term “legacy DBMS” for mature products such as Oracle, but the same applies for DB2 and Sybase. I also think that each developer I have to work with should be force-fed “effective Oracle by Design”, and I am going to have an exam with them in batches. But unfortunately that won’t happen.