Usn's IT Blog

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…

In the last days, I touched hierarchical queries – it’s a strange field for a DBA, but I did need it for DBA desires: A sophisticated query on statspack. Why? Since oddly, the stats$sql_text table of statspack / perfstat DOES contain the full SQL for a statement, but fragmented over various lines. But I had to display all queries in one line, as one string. The hierachical function sys_connect_by_path() looked sexy for it, so I dived into for good. :)

But soon a problem occured: sys_connect_by_path() needs a seperator string, that does not already occur within the to-be-concatenated strings. Okay, this is understandable and rather easy to cope with: Just replace the known string out of the result, done. But most annoying, this seperator has to be LITERAL – no function is allowed there! So in my case, a cause-loop rised its ugly head: As soon as this literal string is stated, it will occur within the stats$sql_text table, because this table contains (nearly) all SQL ran against the DB. Whoop – I’m out of business?

Not really: the oracle-l mailing list saved me (again). This thread resolved the problem – if you ever should need a similar query, just have a look at the solution of this nice little puzzle:

Read more…

Sometimes you want to use datapump import (impdp) for smart problems. An example is excluding (or including) a subset of objects, like tables. The usual way to do so is the EXCLUDE keyword.
(Footnote: All examples in this post are written for a parameter file, so don’t forget a proper quoting for your shell if you are using them on command line.)

(1) The basic syntax for excluding the table EMPLOYEES_1 from import is:

EXCLUDE=TABLE:EMPLOYEES_1

(2) More sophisticated is the use of an IN() statement for more than one table, let’s ignore EMPLOYEES_1, CARS_1 and TRUCKS_1: Read more…