Usn’s IT Blog

These days, the DOAG Conference 2009 happens in Nuremberg, Germany. First day was yesterday, Tuesday November 11th 2009. This is a machine-readable copy of my scratchpad, coming from the different talks I attended.

• Sun Oracle Database Machine Exadata 2: The “Quarter Rack” (consisting of 2 Servers and 3 storage nodes) sounds interesting.
• 11gR2 has some features only available with Exadata: For example, a special “Storage Index”, grouping several lines with theit MIN and MAX values in order to minimize scan ranges (called Smart Scan)
• Exadata provides inter-instance IO ressource management possibilities, it might be useful to look at the IO ressource management capabilities of 11g without Exadata as well!
• Really interesting stuff was “Righttime Data Warehousing – reducing latency” of Sven Bosinger; his presentation and the paper will be great material to explain some stuff for conceptional discussions. Thanks, Sven!
• Look at issues for your DB release version! Metalink Note 161818.1 is great for that!!
• Flashback Data Archive may be a good tool for an archiving concept (Oracle sales tells us about Basel II and stuff), but have care!Firstly, DDL on tables prepared for FDA is slow, and secondly and worse, if you have tablespace quotas on your schema, the archiving might stop if it’s full. And that without error messages due to asynchronous archiving. Only way to monitor that is to scan for new trace files all the time. :(
• The new Oracle Grid Infrastructure and its Instance Caging sounds interesting as well. Especially the CPU assignment with CPU_COUNT parameter sound nice, as well as CPU overcommitment (assigning more CPUs than you have).
• Oracle OMotion and RAC One Node are other nice ones: But at the moment, RAC One Node is not available with Standard Edition!

At a glance: The first day was great, and absolutely interesting. Let’s see what Wednesday brings, the program looks promising so far.

Best regards from Nuremberg
Martin Klier

In the last days, My Oracle Support was redesigned. But since flash is a performance eater and not always available, people like to have an option to use My Oracle Support (formerly Metalink) with HTML only.

The link to it is well-hidden, but nevertheless useful!

Give it a try: https://supporthtml.oracle.com

Best regards
Usn

drop table plan_table;
@?/rdbms/admin/utlxplan.sql

Did you ever change lock_sga = true for an Oracle Instance on AIX 5.3, and did see

ORA-27126: unable to lock shared memory segment in core
IBM AIX RISC System/6000 Error: 1: Not owner

in Return?

Do the following:

# vmo -o v_pinshm
v_pinshm = 0
# vmo -o v_pinshm=1
Setting v_pinshm to 1
# lsuser -a capabilities oracle
oracle
# chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oracle
# lsuser -a capabilities oracle
oracle capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE

It should do now.

Regards
Usn

Hi folks,

since Oracle 11g Release 2 is out now, I had to test one of the most-missed ASM features: the ASM cluster file system ACFS.

My Setup:

• Two VMware nodes with 2 CPUs and 1,5GB of RAM each
• Oracle Enterprise Linux 5.3 x86_64
• Four virtual cluster disks from the ESX server, 10GB in size each
• Building disk group DATA from them, with redundancy NORMAL
• containing four failgroups with each one of the disks within
• In DATA, one ACFS volume of 1 GB in size, mounted to /acfs1

Read more…

Did you recently see the new error ‘ORA-15260: permission denied on ASM disk group’ in your ASM administration? Maybe you are still connected as SYSDBA, old habit from 10g?

A quick citation from the Oracle Docs:

The SYSOPER privilege permits the following subset of the ALTER DISKGROUP operations: diskgroup_availability, rebalance_diskgroup_clause, check_diskgroup_clause (without the REPAIR option). All other ALTER DISKGROUP clauses require the SYSASM privilege.

Just connect with

sqlplus / as sysasm

and you are happy.

Have fun
Usn

The long-awaited 11gR2 is out, at least for Linux x86 and x86_64 now.

Marketing will show you all the features, but the most important link is to the documentations:

http://www.oracle.com/pls/db112/homepage

Use it well!
Usn

Executing OS commands from SQL or PL/SQL is easy as long as you are in SQL*PLUS – just do a host command. But I must confess, that this is a really useless hint. :) Much more interesting is to execute an OS command with a DBMS_SCHEDULER job, but this is not possible in every case, as it wasn’t in mine: I had to execute an ICMP ping and wait for the result to proceed, all that within a periodic DBMS_SCHEDULER job. No way to launch another job and wait until it’s started.

It took not much time to find out, that PL/SQL provides no native method to execute something on OS level. But the web was full of more or less useful hints what to do, the most useful one I found was from Ask Tom, a question about problems calling a in-database-java method for this case. The guy there used rt.exec() out of the database – nice, but Java and me …

Read more…

I simply love 10046 level 12 traces. I described session tracing based on a logon trigger quite earlier, but some situations in real DBA life need this trace event switched on for a session other than my own AND for a short peroid in time (with no need for the connected user to log out). The package/function dbms_system.set_ev is cool for that – the syntax and the handling is very simple, and I like it much more than ORADEBUG. So it’s syntax is like:

Read more…

Oracle has a disadvantage: It allows no trigger BEFORE LOGON ON DTATBASE! :) For obvious reasons, this would be nonsense, but there’s a need for it! Auditing failed logon attempts, for example. Of course, there’s Oracle Auditing. But IMO, setting up an audit trail for one Email in one case looked like the overkill to me and my simple DB decommissioning needs (finding autistic developers).

The concept with a trigger was too nice to drop it after examining the AFTER LOGON trigger, which is useless for my problem. But the oracle-l mailing list helped AGAIN: There’s another oracle trigger: AFTER SERVERERROR ON DATABASE – catch ORA-28000, and you are perfectly auditing logon attempts on locked user accounts.

This is a code snippet for 10g and above, that sends an email on each attempt. Might be a spam bomb if abused, but as I said: My simple needs …

Read more…