One big Oracle ASM adventure

Well, this has happened long ago, but as I'm going to decomission http://dbseminar.r61.net, I think this information should be moved here...

Originally posted on on Tue, 04/07/2009

I had very unpleasant experience with RAC and ASM. I'd like to share it. Yesterday after VMware ESX Server upgrade (and maybe unsuccessfull live motion operations on one of two RAC nodes) I had the following records in asm instance alert log:

SQL> ALTER DISKGROUP ALL MOUNT
Mon Apr  6 15:48:52 2009
NOTE: cache registered group DATA number=1 incarn=0x3ad84292
NOTE: cache registered group FRA number=2 incarn=0x3b084293
NOTE: cache registered group LOGS number=3 incarn=0x3b084294
Mon Apr  6 15:48:52 2009
ERROR: no PST quorum in group 1: required 2, found 0
Mon Apr  6 15:48:52 2009
NOTE: cache dismounting group 1/0x3AD84292 (DATA)
NOTE: dbwr not being msg'd to dismount
ERROR: diskgroup DATA was not mounted
Mon Apr  6 15:48:52 2009
ERROR: no PST quorum in group 2: required 2, found 0
Mon Apr  6 15:48:52 2009
NOTE: cache dismounting group 2/0x3B084293 (FRA)
NOTE: dbwr not being msg'd to dismount
ERROR: diskgroup FRA was not mounted
Mon Apr  6 15:48:52 2009
ERROR: no PST quorum in group 3: required 2, found 0
Mon Apr  6 15:48:52 2009
NOTE: cache dismounting group 3/0x3B084294 (LOGS)
NOTE: dbwr not being msg'd to dismount
ERROR: diskgroup LOGS was not mounted

...

Diskgroups disappeared, and asm didn't want to see them. However, I could see /dev/sd* disks, where diskgroups were placed, and permissions were right: disks were owned by oracle user. select * from v$asm_diskgroup ; gave nothing and select path,header_status from v$asm_disk ; said that all disks were in provisioned state. It was awful, but in the end I've found sollution. I've noticed that kfed sees diskgroup and other attribute on the affected disks, but parameter kfdhdb.acdb.ub2spare is not 0 (as I found in Internet, it was its usual state). So I've dumped header info:

$ kfed read /dev/sdf > /tmp/sdf.noop.mod
$ vi /tmp/sdf.noop.mod  #Changed kfdhdb.acdb.ub2spare to 0
$ kfed op=write dev=/dev/sdf text=/tmp/sdf.noop.mod CHKSUM=YES

After this I could do "alter diskgroup fra mount" in asm instance, repeated this procedure for DATA asm diskgroup, after that I could mount diskgroups and recover my database. In conclusion I wish say that we are going to move our fra to OCFS2 fs the next weekend.

Thank you, Oracle engineers

After 2010, when Oracle acquired Sun, most of us, who followed OpenSolaris, were depressed. In one year one of the most advantageous operating systems was closed under steel curtain. Luckily, due to enormous efforts of community, of companies, dependent on OpenSolaris, the system survived. Currently we have several more or less successful illumos distributions, targeting different users. But nowadays there's a (of course, deserved) common negative feeling towards Oracle in illumos community. But let's speak from another point of view. Let's look at things, which illumos community (and in particular, OpenIndiana) got directly or indirectly from Oracle in recent years.

• Our userland build system, which constantly evolves, however, in different directions, under Oracle control and in our distribution. But still a lot of components can be easily migrated between build systems.
• A lot of software build receipts and patches, as result, were borrowed with small modifications, from Oracle userland-gate. The process is still going on.
• We still borrow patches from Solaris pkg-gate. Also differences in underlying kernels are currently rather significant, a lot of changesets from pkg-gate can be ported to OpenIndiana pkg5 repository.
• Of course, I can not avoid thanking Alan for his constant help in supporting Xorg subsystem and GUI parts of our distribution. He was always helpful to me and Aurélien.
• Evidently, recent KMS work, integrated into OpenIndiana, wouldn't be possible without Oracle's open drm port, which was ported from Solaris to illumos by Martin Bochnig, and later independently ported and enhanced by Gordon Ross.

- And of course, I cannot count patches, which were suggested to upstream projects by Oracle engineers. Just today when I tried to solve two issues related with IPS and apache 2.4 interaction, I've found two patches by Petr Sumbera, fixing Apache issues on Solaris. So, I want to use the chance and thank all Oracle Solaris engineers for their work on open source projects. I doubt that without them illumos could survive in large scale. Perhaps, we could be an excellent playground for ZFS development, but not an universal operating system...

Oracle Dispatcher startup after shutdown

There is an "alter system shutdown immediate 'D000'" command to shutdown dispatcher in Oracle. However, how will you launch disppatcher after shutdown? It's evident - with "alter system set dispatchers='...'" :), where necessary config may be get from v$parameter. It seems that Oracle developers hate their users...

Remember TRUNCATE peculiarities

Just remember the following about TRUNCATE:
1) It is DDL operation and so is not transactional in Oracle (issuing TRUNCATE will make an implicit commit)
2) It is not MVCC-safe in PostgreSQL.

What does it mean?

alp=> create table t199 as select * from pg_opclass ;
SELECT 117
alp=> BEGIN ;
BEGIN
alp=> TRUNCATE t199;
TRUNCATE TABLE
alp=> ROLLBACK ;
ROLLBACK                         ^
alp=> SELECT count(*) from t199;
 count 
-------
   117
(1 row)

So far, everything looks good: TRUNCATE was rolled back. Transactional DDL is a powerful PostgreSQL feature. But let's start parallel transaction. In first session I'll issue

alp=> \set PROMPT1 'T1> '

and in second:

alp=> \set PROMPT1 'T1> '

So, let's fun begin.

 
T1> BEGIN;
BEGIN
T1> SET transaction_isolation TO serializable;
SET
T1> UPDATE employees SET email=email;
UPDATE 103

T2> BEGIN ;
BEGIN
T2> TRUNCATE t199;
TRUNCATE TABLE
T2> COMMIT ;
COMMIT

T1> SELECT count(*) from t199;
 count 
-------
     0
(1 row)

Oops... What has happened? Let's repeat...

T2> INSERT INTO t199 SELECT * from pg_opclass;
INSERT 0 117

T1> BEGIN;
BEGIN
T1> SET transaction_isolation TO serializable;
SET
T1> UPDATE employees SET email=email;
UPDATE 103

T2> BEGIN ;
BEGIN
T2> DELETE FROM t199;
DELETE 117
T2> COMMIT ;
COMMIT

T1> SELECT count(*) from t199;
 count 
-------
   117
(1 row)

Do you see the difference? T2's DELETE didn't modified T1 snapshot... But TRUNCATE did. And what about "UPDATE employees"? It is just necessary to get real XID and snapshot for transaction.
So, the main idea: be attentive with "TRUNCATE". It is not just "fast DELETE", it has some not-so-evident consequences.

A little joke: Oracle donates openoffice.org code to Apache Foundation...

I've just had something like little orgasm on reading this piece of news... Oracle managers are genii, you can't underestimate this joke. We are goody-goody, we support open source, and you (Document Foundation) are in deep arse: try to compete with ASF, if you can :)

I like people filling oracle.com with info

Once you could see SPARK processors in Oracle portfolio, now we have Doc: Trace User's Guide. It seems that men filling these pages either have never listened about Sun or just sleep at work :)

More troubles with OpenVZ

I have two troubles with OpenVZ:
1) udevd works strange, in particular, /dev/ptmx gets incorrect permissions and so xterm, gnome-terminal, etc doesn't work. Solved it whith "chmod 666 /dev/ptmx" in rc.local
2) su doesn't work for usual user: asks a password, and when the password is correct, it returns immediately with "incorrect password", when the password is wrong, it returns the same after timeout. Decided to use sudo.

Vnc worked without surprises: just created necessary entries in /etc/sysconfig/vncservers:

VNCSERVERS="2:username"
VNCSERVERARGS[2]="-nohttpd"

I had to replace ~/.vnc/xstartup with the following to run gnome-session on each user login:

while /bin/true;  do
   /usr/bin/gnome-session
done

Now I have to install some supplementary software (OpenJDK, NetBeans, Oracle SQL Developer) and create template from this settings. After finishing deploying template, I'll have about 20 VMs for students...

OpenVZ and Oracle

I prepare for new Oracle course now. I'd like to provide every student a OpenVZ container running developer desktop and Oracle XE. Just for now, it seems XE runs normally in container. Just set SHMPAGES at least to 524288 in /etc/sysconfig/vz-scripts/<vid>.conf and install XE in container in the following way:

yum install libaio bc
rpm --nopre -ivh oracle-xe-univ-10.2.0.1-1.0.i386.rpm
/etc/rc.d/oracle-xe configure

SPARC is not dead...

I have missed T3 benchmarks so far. It seems that SPARC clusters rule as ever. I suspect that T3 price is not such pleasant as its performance, however I'm glad to see prosperous non-x86 platform.
And we have just given away our last SPARC server (Sun Fire V440). I'd be glad to work with SPARC servers in the future, however, the probability of this is quite low.

repmgr or manage your PostgreSQL cluster easily

I've looked through Greg Smith's post about repmgr and feel quite interested. As I understood, this utility significantly simplifies PostgreSQL cluster management. I'd like to test it this week. There are a lot of questions. Will this repmgr compile on FreeBSD? How will be fail of master processed? What will happen if master goes down, e.g. due to network failure, and then is reborn while old slave is a new master? What will happen if nodes are divided in several groups? We'll see. At least, first three questions are critical...

One more thing which I'd like to see in PostgreSQL is a transparent support for HA configurations from client library. The following scenario worked in Oracle RAC. We had two "IN A" DNS records for "oracle.our.domain" pointing to our RAC nodes, and applications transparently switched from one node to another on node's failure. More supported behavior was achieved by specifying both nodes in ADDRESS_LIST and using proper FAILOVER_MODE.

RIP, Solaris...

What are you doing when you want to promote some product? You say to you friends, colleagues and acquaintances: use it, it's great! And they understand, that it maybe not great, but at least useful. And they use it. Sun understood it. And we had OpenSolaris. I worked as Sun Campus Ambassador and promoted it. And (Open)Solaris was a great OS: it had ZFS, DTrace, Zones, Projects and quite understandable RBAC system. And it was cool to use this OS. I wanted to find and report some bugs to make it better. I wished to port some soft to make Solaris pleasant OS. And what do we have now? Solaris 11 Express and end of OpenSolaris.
Blame on Oracle. They are going to spoil everything they touch. I didn't believe that they were able to shoot themselves in the leg. But they are talented enough to do it. With OpenSolaris Sun Solaris began new life. Without such high priority project it is doomed. Other OS are coming. Of course, one of them will be Linux (it's not ideal, but free and have a lot of features that Solaris will never have: support for almost all hardware you can imagine and all soft you may ever need), I hope one of them will be FreeBSD (at least we'll have ZFS and DTrace here, jails are good replacement for zones, especially after completion of new jail resources control project), Windows Server will be alive and prosperous. But AIX, HP/UX and Solaris will be dead. Because they were already dead. One of this great systems was near its resurrection. But its owner decided to bury it alive.
It's not good news, but expected one. Oracle was quite stupid to make other irrelevant steps. So, I think the next time I'll see Solaris 10 will be in museum. It will be among OS/2, Amiga OS and SCO Unix. Without new blood every project is doomed. OpenSolaris gave this giant new blood. But this is just a part of history now. Blame on Oracle.

Why I'm going to read Oracle course in university... on PostgreSQL

I used to like Oracle. They gave us one of the best DBMS. They made a lot of work on promoting Linux. They used a lot of open source software in their products and promoted Java...
But their greed is unnatural. It is more than Microsoft's greed.
1) Their DBMS costs too much. When our university tried to prolong their support, they said taht we had to pay taxes... because we hadn't done this earlier.
2) They don't allow to use their products in education for free. Even MS Academy program is free for our University.
3) They have done everything to kill Sun OpenSolaris. Damn them!
4) They prohibited to use Sun Solaris for free.
5) And now they are suing Google for using Java technologies in Android.
IMHO, this company is crazy and ill on immediate profit. It doesn't matter to them that their steps only makes potential customers nervous. What is next? Will they sue Apache or SpringSource for developing Java-related products or FreeBSD for using ZFS?
No, thanks. I will not promote their products at our campus.
FreeBSD and PostgreSQL is a real base for DBMS server, not Solaris and one overpriced DBMS...