When the database dies….

Yesterday was a bad day for me as DBA. All begone with two distributed transactions which had a lock held on a table. Turned out that these two transactions were In-Doubt transaction and could be roll-backed. So what do you do in this case: Select on DBA_2PC_PENDING view, get the global transaction id and execute a rollback force.

It just took two minutes the first developer came to me and asked me why the hell the database is down on 10am. “What, database down?”, I said and reconnected me. And really, the database went down. So I restarted it and opened the alert log to look what was going on. Nice,  ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []. As I read in the log file, it just took one minute and the database died again. Same ORA-600 three times. And again and again and again. Shit…. Ok, opened metalink, and ora-600 lookup tool and searched for it. Nothing useful, just something about block corruption, damn. Ok, time to log a SR with severity 1 to Oracle after around 40 developers couldn’t work anymore. And what turned out. A little bit funny I think:

The two transactions looked at two different primary keys which had a corrupt block inside. The instance recognized that, when I roll-backed the transactions and stopped the database. After startup the instance recognized that there is something to recover, and of course also this two distributed transactions. So it crashed again. After finding with Oracle support out which two indexes made trouble, it was a easy job. I mounted the database, put the datafile of the indexes offline, opened the database, droped the indexes, recovered the datafiles, recreated the indexes and put the datafile back online.

Quiet another funny day in a DBAs life…

Oracle partner network – technical training…. nearly

Oh damn, yesterday an email arived to join a techincal training provided by Oracle partner network on 27th of November in Vienna. Agenda is: High availibillity, desaster recovery, avoidance of downtimes and new Oracle 11g features! Well, today I got the OK from my boss but, already too late. There were only 15 seats free, as I wanted to log on there were already 11 people on the waiting list! Well the chance that 12 of 15 people will not come is very very tiny so I didn’t put myself on the waitlist anymore.

But there is also good news: I’m also allowed in future to join, so lets see when the next intresting training is planed and Venzi goes to Vienna joining it! 😉

SYS not almighty!?

I always thought that SYS is the almighty user of the database like root on linux OS. But it seems that he isn’t. A few days ago a developer got following error on the database: ORA-01927: cannot REVOKE privileges you did not grant Ok, that seems to be clear, he tried to revoke a grant which he didn’t grant. But he told me that he granted it just before, so I logged on with SYS and had a look on the privileges. And he was right, he granted it with SYSTEM. So I tried to revoke the privilege with SYS and also got the same error?! Then a look at error messages page on oracle:

ORA-01927: cannot REVOKE privileges you did not grant

Cause: You can only revoke privileges you granted.

Action: Don”t revoke these privileges.

Ok, i can’t revoke the privilege with SYS, but with SYSTEM from a SYS object?
Searching the Oracle Forums and Internet also said the same, can that be? Time for a little test:

Lets make a little function stored by sys and grant it to a simple user with SYSTEM and then try to revoke it with SYS:

SQL> conn sys@test1 as sysdba
Connected.

SQL> create function test return varchar2 as begin return ‘Hello World!’; end;
2  /
Function created.

SQL> create user venzi identified by venzi;
User created.

SQL> grant resource to venzi;
Grant succeeded.

SQL> grant connect to venzi;
Grant succeeded.

SQL> grant execute on SYS.test to system with grant option;
Grant succeeded.
Ok, now we’ve the function, the user and SYSTEM has the grant with the option to grant to other user. Now we gave the simple user the grant from SYSTEM:

SQL> conn system@test1
Connected.

SQL> grant execute on sys.test to venzi;
Grant succeeded.

And test it out:

SQL> conn venzi@test1
Connected.
SQL> select sys.test from dual;

TEST
——————————————————————————–
Hello World!

Ok, works fine, SYS now will try to revoke the grant:

SQL> conn sys@test1 as sysdba
Connected.
SQL> revoke execute on test from venzi;
revoke execute on test from venzi
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant

With SYS it doesn’t work although it’s a SYS object…. the user still has the privilege to execute the function:

SQL> conn venzi@test1
Connected.
SQL> select sys.test from dual;

TEST
——————————————————————————–
Hello World!

With system it works fine:

SQL> conn system@test1
Connected.
SQL> revoke execute on sys.test from venzi;

Revoke succeeded.
Only chance with SYS is to revoke the privilege from SYSTEM:

SQL> conn system@test1
Connected.
SQL> grant execute on sys.test to venzi;

Grant succeeded.

SQL> conn venzi@test1
Connected.
SQL> select sys.test from dual;

TEST
——————————————————————————–
Hello World!

SQL> conn sys@test1 as sysdba
Connected.

SQL> revoke execute on test from system;
Revoke succeeded.

SQL> conn venzi@test1
Connected.
SQL> select sys.test from dual;
select sys.test from dual
*
ERROR at line 1:
ORA-00904: : invalid identifier
So very very strange, I always thought that with SYS you can perform all operations, but it seems that you can’t. I’m sure that there is a dirty solution from deleting it out from data dictionary but as said: Very dirty (never modify data dictionary except Oracle Support wants you to do this!)

The beauty of Java stored procedures

Since 8i you can write stored procedures also in Java. But what is the advantage of writing a procedure in Java? Well the first advantage is that you’re now able to write complex solutions and use them in PL/SQL. You can write your own package for file I/O, email reading/sending, OS watching, an API for ZIP-files and many more. You’ve all the possibilities from Java now available in PL/SQL! And another advantage: It runs in the Oracle JVM which means it have access to the huge SGA and can do DB operations with better performance than outside it! And it’s easy to write it. For example a little Java stored procedure which shows you the properties of the Oracle JVM:

The first step is to write the java source which looks like this:

create or replace and compile java source named “JavaProps” as

And now continue with normal Java code:

public class props
{
public static void GetProps( )
{
System.getProperties().list(System.out);
}
}
/

Well this now creates the Java source inside the database. Now we create the stored procedure over it to use it inside PL/SQL:

create or replace procedure JavaProps
as language java name ‘props.GetProps()’;
/

And that it was! Now run the procedure in an anonymous PL/SQL block:

set serveroutput on size 10000;
begin
dbms_java.set_output (10000);
JavaProps;
end;
/

So you see, writing Java stored procedures isn’t difficult and allows you to do now powerful operations inside of PL/SQL!