Monday, October 14, 2013

Allow Oracle Users to Remove Their Own Locks

Somehow your oracle users have an uncanny ability to leave locks on their tables making them inaccessible for an unusually long time. This could happen due to poor commit practises or simply because a remote session timed out at the wrong instant. You would like to avoid having the issue escalated by giving the user the authority to remove the locks themselves. We can do this using a stored procedure to kill the offending sessions.

The desired procedure should accomplish the following:

  • User can kill only those sessions they own. In other words they can't kill other user's sessions if they happen to know other user ids.
  • Kills will occur in sessions the user holds other than the current session.  When they are connected (or reconnected) in an attempt to perform the unlock we don't want to kill the session they are currently using.


Logged in as a user that has ALTER SYSTEM privileges create the following stored procedure.




create or replace procedure selfkillSessionProc IS

strNeeded VARCHAR2(50);

cursor_name     pls_integer default dbms_sql.open_cursor;

ignore          pls_integer;

  BEGIN

    FOR x in (SELECT s.inst_id,

       s.sid,

       s.serial#,

       p.spid,

       s.username,

       s.program

FROM   gv$session s

       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != 'BACKGROUND' AND s.username in (SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL) AND

      s.sid NOT IN (select sid from V$SESSION where AUDSID = userenv('SESSIONID')))

    LOOP

     DBMS_OUTPUT.PUT_LINE(x.sid || ',' || x.serial#);

     strNeeded := '''' || x.sid || ',' || x.serial# || '''' ;

     DBMS_OUTPUT.PUT_LINE(strNeeded);

        dbms_sql.parse(cursor_name,

                      'alter system kill session '''

                                ||x.sid||','||x.serial#||'''',

                       dbms_sql.native);

       ignore := dbms_sql.execute(cursor_name);

      END LOOP;

  END;


 
The above procedure ultimately uses the ALTER SYSTEM command to end the current user's other sessions.  To do this we need to gather the session IDs and the session serial numbers from the V$SESSION table.

Additionally, the ALTER SYSTEM command cannot be called directly from a stored procedure so we use a DBMS parser to run the command.

Finally, give your users permission to use the stored procedure.


grant execute on selfkillSessionProc to public;



Tuesday, June 11, 2013

vCloud Director 5.1.2 Removing Org VDC Networks

I ran into a little issue the other day configuring vcloud director. I wanted to remove an organization VDC completely. Of course to do this you must disable the VDC and remove all of it's resources (networks, templates, and vApps). Upon trying to remove the networks I kept getting the error

Entity xxx.xxx.xxx.xxx cannot be deleted, because it is in use.

This error occurs even though there is absolutely nothing using the network; no templates, VMs, vApps, ....nothing. Our networks were of the direct connect type, although this may also occur with an Edge Gateway network. We didn't want to remove the upper level networks from the entire cloud. The system seems to want to hold on to these networks once they were added for the first time. It seems that we may not be able to remove these networks from the system, but we were able to move them to a new organization VDC. We did this directly on the vcloud database.

/******
First lets see what is in the table
******/

SELECT TOP 1000 [id]
      ,[vdc_id]
      ,[lr_type]
      ,[name]
FROM [vcloud].[dbo].[vdc_logical_resource]


Take a look at what you have in the table. The records you care about have a lr_type of NETWORK.

Once you have the IDs from the table you can update the records accordingly.

In the change query below the first parameter (vdc_id) is the organization you want the network to move to. You can usually find the proper id by noting the vcd_id of the record that has the organization name in the 'name' field. The second parameter (id) is the network you want to change. In other words, you are going to set the network's organization id field.

UPDATE vcloud.dbo.vdc_logical_resource
SET vdc_id = 0xabunchofUUIDnumbersfortheorganization
WHERE id = 0xabunchofUUIDnumbersforthenetwork;

Another unique issue we were seeing occurs when we try to disable sharing this network with other VDCs in the organization. If we try to uncheck the box in it's properties we get the following error in vCloud Director:

Index: 0, Size: 0

Not sure what this one is about. Seems it could be an out of bounds exception with the program accessing a list.