Once again this year, the German Oracle Users Group has its annual conference and exhibition in Nuremberg. (DOAG Konferenz und Ausstellung 2011, Nürnberg). Being there is nearly a must for Oracle guys in German speaking countries.

As usual, here comes my unordered, incomplete and ad-hoc list of things I wrote down to have a closer look at in the next year, coming up during or from random talks I attended.

Day 1 (Tue 15.11.2011)

• AVG_ROW_LENGTH of a table vs. Blocksize
• CHAIN_CNT
• analyze table X validate stucture cascade
• Linux: Transcendent Memory
• Linux: CleanCache and zcache
• Linux: Cgroups
• Linux: Transparent Huge Pages (wow!)
• Linux: DTrace
• Linux proprietary: Ksplice
• View: registry$history for the REAL version number
• Rolling Upgradable patches means minimal downtime on on Single Instance DBs, in combination with Out-of-Place-Upgrade)
• Bug 10187168 in PSU 11.2.0.2.2 (_cursor_features_enables=1026
• Typeset conversions: CSscan and DMU utilities
• AIX patch following note 1246995 (Memory Footprint)

Read more…

These days, a customer’s Oracle Clusterware (2 nodes) crashed one ASM instance at every startup.

More Facts:

• It was not possible to start it manually, too.
• The CSSD was running.
• For obvious reasons, CRSD did not start.
• The other ASM instance in the cluster recognized CLUSTER RECONFIGURATION for a short period of time.

The ASM Alert Log file looked like:

Sun Nov 13 13:44:08 2011
 MMNL started with pid=21, OS id=7783
 lmon registered with NM - instance number 2 (internal mem no 1)
 Sun Nov 13 13:46:05 2011
 System state dump requested by (instance=2, osid=7684 (PMON)),
         summary=[abnormal instance termination].
 System State dumped to trace file /u01/app/oracle/diag/asm/+asm/+ASM2/trace/+ASM2_diag_7706.trc
 Sun Nov 13 13:46:05 2011
 PMON (ospid: 7684): terminating the instance due to error 481
 Dumping diagnostic data in directory=[cdmp_20111113134605], requested by (instance=2, osid=7684 (PMON)),
         summary=[abnormal instance termination].
 Instance terminated by PMON, pid = 7684

Strange problem. Looking up device permissions, read write tests, rebooting the cluster in a downtime window – nothing.

To make a long story short: The NTP daemon did not get his time synchronisation, but was running. Thus, CTSS was in observer mode, and server time started drifting apart. Fixing NTP, fixed the cluster.

Nota bene
Martin

After looking up Linux bonding stuff for the thrid time, I planned to write an article aubout it. But there are lots of good blog posts on this, so just click here at unixfoo:

Linux Network bonding – setup guide

(strange link, I know, but it works)

Hope it helps for you next high avaliability project, like Oracle RAC, Oracle Grid Infrastructure or Oracle DataGuard.

Take care
Martin

Oracle Database 11g and above offers a great feature by managing its trace files by itself. In combination with the new “incident” system, a tool was necessary to handle those without enterprise manager: ADRCI. It has several other cool functions, such as a tail-like log file display, but today I just want to show a very short reference for the lifetime management of trace files and their friends.

1. Basics

The file lifetime for alerts, traces, incidents and dumps is calculated staring from the last modification done by Oracle. For example, a trace file can be used for hours (and days), but it will be deleted X hours after creation. Following this logic, a running logfile like alert.log will never be purged.

Keep in mind, the way how to read and specify times is confusing: They show in hours, and you configure in minutes.

2. Look up current configuration

Of course it’s useful to see how we are confugured. SHORTP_POLICY is for trace files and other things generated automatically. Incidents and other stuff done by purpose is cleaned following the LONGP_POLICY. The other fields (there are more than I am showing here) should be self-explanatory.

Read more…

Oracle Grid Infrastructure 11.2 with voting files and OCR in an ASM diskgroup can be a little tricky if you mess up the voting file voodoo. You know the basic situation?

With Oracle Grid Infrastructure aka Oracle Clusterware, we are storing your cluster quorum and config repository (OCR) into a disk group. But CSSD needs the voting files before ASM is online: At its startup, CSSD scans the device headers of every device in the disk string (configured by you at ASM initial setup time). If it can find at least two valid voting files, the party takes place. Otherwise, your CSSD will cycle with appropriate error messages in $GRID_HOME/log/hostname/log/cssd/ocssd.log for each loop.

This is where I did find myself today: I changed the ASM diskstring to an insane value, and whoops – at next reboot, my Node1 cycled its CSSD forever in a few minute’s interval, and Node2 was caught in a rock solid reboot loop. Looking up the CSSD logfile, I saw that the CSSD had trouble identifying its voting files. (In fact, there have been multiple devices pointing to the same physical device. Interestingly, thus, CSSD dropped both of them. But this is not the issue of this post.)

Now, tell me, how do you change back the ASM disk_string parameter without having ASM running, and with no CSSD available, which is necessary to start it? And how do we tell the CSSD, that’s running fairly in advance of ASM, to scan the right devices?

Read more…

Sad to see, at least from June 1st 2011 the new Grid Infrastructure ASM features “ACFS” (ASM cluster file system) and “ADVM” (ASM dynamic volume manager) are now licensed as “Cluster FIle System” (ClusterFS).

As far as I can see from the Technology Global Price List, it’s US$ 5000 per CPU, US$ 1100 perpetual. Alternatively, it’s US$ 100 per Named User Plus (NUP), minimum licensing 25 users, US$ 22 perpetual.

Citation from the Features and Editions page:

A restricted use license to use Oracle Cloud File System is included with all editions of the database specifically for storing Oracle Database-related configuration files, including Oracle Database software binaries and homes, Oracle Database software administrative files, and Oracle Database software diagnostic files. Customers wishing to store their own data files, or non-Oracle Database software files including data files, binaries, administrative files, and diagnostic files, in Oracle Cloud File System must separately license Oracle Cloud File System.

Well, another good feature becoming expensive, it’s a figures-driven company. :)

Yours
Usn

PS: No official Oracle figures here, just for orientation.

Oh really, there ARE guys with deep insight into Microsoft SQL server. This notes absolutely look like a willing professional who did learn something new: http://blogs.technet.com/b/josebda/archive/2009/03/20/sql-server-2008-fragmentation.aspx

But what about me? I am still looking for a course or material giving me the SQL Server architecture and logic in a way I can understand. At least one resource that looks promising in this sense: http://www.akadia.com/services/sqlsrv_data_structure.html

Sad feeling: SQL Server seems to be much, much more an oracle than Oracle is to me.

Struggeling on ;)
Martin

How to find out the (biggest) table size in MS SQL Server? To find the answer in your preferred search engine is difficult – not because there are no hits, but there is that lot of crap to see, it’s just unbelievable. If you don’t want to enjoy this experience, maybe have a look into this page: http://blogs.technet.com/b/mdegre/archive/2009/10/14/determining-sql-server-table-size.aspx

The author, Michel Degremont, did a good job, but his post is ranked way too badly for the quality provided. Give credit where credit is due!

Standing on the shoulders of giants,
Yours, Martin
Read more…

In Oracle often we are using

CREATE TABLE TABLE_B AS SELECT * FROM TABLE_A;

But in SQL Server, this syntax does not work. Use

SELECT * INTO TABLE_B FROM TABLE_A;
COMMIT;

instead.

Hope this helps
Martin