Oracle trigger: capturing one less column of a table

If you want to fire a trigger when any of columns but one is updated, the solution ca be to put all the wanted columns in the script, but there is an easier way:

CREATE OR REPLACE TRIGGER ....... before UPDATE or delete ON
 .......... REFERENCING old AS old new as new FOR EACH ROW
declare
...............................
BEGIN
...........................
if updating ('unwanted_column') then  
         null;
             else
    ........the action of trigger..........;
end if;
exception
WHEN exc THEN
............
    rollback;
END;
/



Be aware that if you use 

if not updating ('unwanted_column') then
   ........the action of trigger..........;
end if;

you'll notice, when you update two or more columns, and the unwanted column is not included, anyway, the condition will not be satisfied and the action will not be done. Only in the case of updating one column will succeed, which is not a solid solution.
So, use the condition somehow that the action will be on the ELSE   branch.
Enjoy!

ORA-00054: resource busy and acquire with NOWAIT specified

First of all, i must say this message maybe is not really scary, because you might think with some patience the problem can be overridden. Well, there is a possible scenario giving some headaches to a DBA:

alter table  tn disable constraint tnc;

Everything is fine 'till now, you have disabled a constraint in order to update some data.

After all these updates, and after you verify the data is compliant with the bussiness rules enforced by the constraint tnc:

alter table  tn enable constraint tnc;

 ORA-00054: resource busy and acquire with NOWAIT specified

Now you are in serious trouble. Working hours mean lots of data inserted in your tables, not anymore protected by that constraint, and the users can broke the bussiness rule from now.

Of course, you may say the same problem can occur during that few seconds while you ran the two DDL commands and the DML commands which modified th table tn. Agree, was not a good practice way of doing things, but this kind of manipulating data can be seen very often in practice, when a DBA or a developer doesn't care or can't wait for a maintenance window, in order to manipulate data, ignoring the possibility of any other session, locking a table.

Unfortunately, there isn't any complete solution for this problem. You can run the following code (for Oracle <10g), which tries for n minutes to achieve the lock on the table and run the DDL command:

DECLARE

  is_ok  BOOLEAN;

  t_l    EXCEPTION;

  PRAGMA EXCEPTION_INIT(t_l, -00054);

  start_time     DATE; -- the moment of the first cycle

  no_min number; --how many minutes the program will run, trying to

                 -- achieve the lock

BEGIN

  is_ok := FALSE;

  SELECT SYSDATE INTO start_time FROM DUAL;

  WHILE (NOT is_ok) AND (SYSDATE < start_time + no_min/24/60)

  LOOP

    BEGIN

      EXECUTE IMMEDIATE ('alter table  tn enable constraint tnc');

        is_ok := TRUE;

      DBMS_OUTPUT.PUT_LINE('Succes!');

    EXCEPTION

    WHEN t_l THEN

      NULL;

      DBMS_LOCK.SLEEP(0.1);

    END;

  END LOOP;

  IF is_ok = FALSE THEN

    DBMS_OUTPUT.PUT_LINE('the constraint is still disabled!');

  END IF;

END;

/

For Oracle versions >10g, there is a parameter, ddl_lock_timeout , doing the same job like the code from above. Any DDL command issued against a table will try for a number of seconds equal to ddl_lock_timeout.

And there is another solution, involving   the QUIESCE concept, but i don't like it and i don't recommend it to anyone.

Good luck!

The ESC key problem when you putty a linux machine

You are under vi or vim and, after some edit of the file, try to exit. ESC key doesn't work.
Instead of ESC, press ctrl+[

That's all. Enjoy!

How to change vnc server password

Shortly:

su - name_of_the_user    

(vnc server has different settings for different users)

vncpasswd

sudo service vncserver restart

or 

su -
service vncserver restart

ORA-00119: invalid specification for system parameter REMOTE_LISTENER ORA-00132: syntax error or unresolved network name 'name_of_scan:1521'

When you try to startup a cluster database:

ORA-00119: invalid specification for system parameter REMOTE_LISTENER 
ORA-00132: syntax error or unresolved network name 'name_of_scan:1521'

This issue appears, mostly, after some cluster problems, solved by network reconfigurations, etc.

The workaround is very easy. 
First of all, you have to notice there are two sqlnet.ora files. One of them, in the grid home. The second, in the database home.
For the sake of our problem here, important is the bellow database home one.

So, run the db environment script, go to $TNS_ADMIN folder and observe the lack of the EZCONNECT method in the sqlnet.ora file. Be sure it will be:

cd $TNS_ADMIN
vi sqlnet.ora

....
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
 .....

Then, everything will work like a charm. Enjoy!

When CRS-4535 Cannot communicate with Cluster Ready Services

Usually you can workaround this situation by crsctl stop and crsctl start, but let's assume that it won't work and:

tail -f -n 50 $ORACLE_BASE/grid/log/rac2/cssd/ocssd.log

you will notice something like "has a disk hb but no network hb" in it.


Well, from now, all you should know is that you've got a network problem.
Ping all the nodes through all the networks (vip, priv, etc), check if it is a problem then:

ifdown ethX
ifupe thX
 
if the problem is still there:

ifconfig -a

mind the IPs, maybe one of the interfaces has lost the settings, usually the problem is on the priv network...

Repair the network and you will notice the crsctl start crs won't bother you again.
Enjoy!

What to do when sshd is not working

 Hypothesis:

# /etc/init.d/sshd start
Starting sshd: [ OK ]
# /etc/init.d/sshd status
openssh-daemon is stopped

 The best way to to debug is, guess what, to run the sshd in the debug mode:

/usr/sbin/sshd -dddd

Now you are capable to see where the problem is and where the config file is.
My case: /etc/ssh/sshd_config

Here, you should locate the bug. It could be a device problem (usually "dev/null" is a regular file and you should remove it and recreate with mknod command), an ip address problem, a wrong port, etc...

Make the proper settings and run again:

/etc/init.d/sshd start

Now, if you are still in trouble, because the authorized keys, look in the same config file and mind the paths to the key files and the rest of the settings. 

The ssh-agent:

#ssh-agent -s

Agent pid 3426

You may generate a new ssh key:

#ssh-add -l

If u are still in trouble, let me know!