Friday 30 January 2009

Oracle : Display refcursor content in SqlPlus or Toad

Proc to xml-ify the refcursor

create or replace procedure refcursor_print (p_refcursor in sys_refcursor,
                 p_null_handling in number := 0)
as
  l_xml      xmltype;
  l_context  dbms_xmlgen.ctxhandle;
  l_clob     clob;

  l_null_self_argument_exc exception;
  pragma exception_init (l_null_self_argument_exc, -30625);
 
  procedure print (p_msg in varchar2)
  as
    l_text varchar2(32000) := p_msg;
  begin
    loop
      exit when l_text is null;
      dbms_output.put_line(substr(l_text,1,250));
      l_text:=substr(l_text, 251);
    end loop;
  end print;
begin
  /*
  Purpose:    print debug information (ref cursor)
  Remarks:    outputs weakly typed cursor as XML
  */

  /* get a handle on the ref cursor */
  l_context:=dbms_xmlgen.newcontext (p_refcursor);
  /*
  # DROP_NULLS CONSTANT NUMBER:= 0; (Default) Leaves out the tag for NULL elements.
  # NULL_ATTR CONSTANT NUMBER:= 1; Sets xsi:nil="true".
  # EMPTY_TAG CONSTANT NUMBER:= 2; Sets, for example, <foo/>.
  */
  /* how to handle null values */
  dbms_xmlgen.setnullhandling (l_context, p_null_handling);
  /* create XML from ref cursor */
  l_xml:=dbms_xmlgen.getxmltype (l_context, dbms_xmlgen.none);

  print('Number of rows in ref cursor: ' || dbms_xmlgen.getnumrowsprocessed (l_context));
 
  begin
    l_clob:=l_xml.getclobval();
    print('Size of XML document (anything over 32K will be truncated): ' || length(l_clob));
    print(substr(l_clob,1,32000));
  exception
    when l_null_self_argument_exc then
       print('Empty dataset.');
  end;
end ;

Call like this
Nb returns a refcursor into c1
Output via dbms_output

c1 sys_refcursor;

begin
   get_refcursor_with_some_params('X1001',TO_DATE('12/02/2008','DD/MM/YYYY'),c1);
  refcursor_print(c1);
end;


Wednesday 28 January 2009

Rdesktop under linux into a Cisco VPN

  • Using Centos 5.2 - need to add additional repos to find a copy of kvpnc (?)
  • Correct version of packages (vpnc-0.3.3-1.2.el5.rf, kvpnc-0.8.8-1.el5.rf)
  • Need kde
  • Watch out for later version of vpnc (0.5+) being incompatible with syntax used by kvpnc - downgraded to get them to match 
  • Run kpvnc and import .pcf file to set up a new connection definition
  • Had some problems with Perfect Foward Secrecy being passed as a parameter to vpnc and vpnc not understanding - can be turned off via Profile->General->Advanced->PFS
  • Edit /etc/vpnc.conf to be something like
### This is the gateway configuration
IPSec gateway
IPSec ID
IPSec secret

### Put your username here
Xauth username
Xauth password

  • Install rdesktop
  • Use a command like "rdesktop  -u \\ -p -f -a 16 -k en-uk MACHINE
  • -f is fullscreen mode - use Ctrl-Alt-Enter to get out
  • On vmware this would be Ctrl-Alt-(Space-then-Enter) holding down Ctrl-Alt the whole time

Misc
Putty - selection cursor shows up black on black - change the mouse pointer on the remote machine to something more usable (Control panel -> Mouse->Pointers Tab->Text Selection)


Friday 23 January 2009

Conficker related links


Stopping Autorun
http://nick.brown.free.fr/blog/2007/10/memory-stick-worms.html

How to disable the use of USBs (MS)
http://support.microsoft.com/kb/823732


Monday 19 January 2009

Windows: Remote query for installed patches

wmic /user:Administrator /password:,password> /node:"spread-00811" qfe | find /N "KB958644"

Windows : Remote registry query/changes

reg query \\SPREAD-01058\HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Advanced\Folder\Hidden\SHOWALL /s

Windows: Admin command window via Runas

runas /user:odl\administrator cmd

Thursday 15 January 2009

Oracle: DBCA template reverse engineering problem solved


Silent create template
dbca -createTemplateFromDB -sourceDB lontestdb02:1521:NEWDEV4 -templateName ZPFL1 -sysDBAUserName sys -sysDBAPassword password -maintainFileLocations false -silent


Problem
When using the DBCA to create a RAC database when selecting next on the node selection screen are
receiving the following error:

java.security.AccessControlException: access denied (java.sql.SQLPermission setLog)
at
java.security.AccessControlContext.checkPermission(AccessControlContext.java:270)
at
java.security.AccessController.checkPermission(AccessController.java:401)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:542)
at java.sql.DriverManager.setLogStream(DriverManager.java:392)

Cause

The problem is that the user (oracle user) which use the DBCA does not have the permission to use the java packages for the RAC environment across the nodes. To create a file which set the permissions it will be checked and used to perform the action needed to be able to create the instances across the nodes

Fix

Create the file:

.java.policy on each node in the Oracle Users Home directory.

containing the following code.

grant {
permission java.security.AllPermission;
};

When done, changed the permission to 770 for the .java.policy file for the files/nodes
Start the DBCA again.

Template created in $ORACLE_HOME/dbca

Tuesday 6 January 2009

Oracle 10.2 RAC reboottime tweakables


http://mengmark.spaces.live.com/blog/cns!9BA8E9209B123692!179.entry


Oracle Database 10g Release 2 CSS (Cluster Synchronization Service) parameters:

 

With
different patch-sets of Oracle Database 10g Release 2; there exist
different timeout parameters which are used by CSS while accessing
storage data. In this document we will cover following Oracle Database
10g Release 2 patch-set versions:

1. Oracle Database 10.2.0.1

2. Oracle Database 10.2.0.1 + Patch for Bug 4896338

3. Oracle Database 10.2.0.2

4. Oracle Database 10.2.0.3

 

1. Oracle Database 10.2.0.1

 

There
is only one CSS parameter available in this version of Oracle and it is
called misscount which represents the maximum time in seconds that, a
heartbeat can be missed before entering into cluster reconfiguration to
evict the node, and the maximum time allowed for a voting file I/O to
complete.

The default value for misscount is 60 seconds.

 

2. Oracle Database 10.2.0.1 + Patch 4896338 and Oracle Database 10.2.0.2

There
is bug 4896338 with Oracle Database 10.2.0.1 which is a placeholder bug
for PCW 10.2.0.1 merge for very low brownout. Please refer
www.metalink.oracle.com for more details.

Oracle Database 10.2.0.2 has a fix for this bug.

There are three CSS parameters available in 10.2.0.2 and 10.2.0.1 + patch for bug 4896338; they are as follows:

 

a)
misscount - It represents maximum time in seconds that, a heartbeat can
be missed before entering into a cluster reconfiguration to evict the
node.

 

b)
disktimeout - It is the maximum amount of time allowed for a voting
file I/O to complete; if this time is exceeded the voting disk will be
marked as offline.

 

c) reboottime - It is the amount of time allowed for a node to complete a reboot after the CSS daemon has been evicted.

 

Default values for these parameters are as follows:

misscount = 60 seconds

disktimeout = 200 seconds

reboottime = 3 seconds

Using
"crsctl get css disktimeout / reboottime" will not show parameter value
unless you modify it explicitly. You can check the parameter's values
using ocssd.log under $CRS_HOME directory. 8

CRS internally calculates two parameters namely diskshorttimeout and disklongtimeout (can be checked in ocssd.log), where

 

a)
diskshorttimeout = misscount - reboottime : This value is used during
reconfiguration and initial cluster formation as a timeout for voting
file I/O to complete.

 

b)
disklongtimeout = disktimeout : This value is used during normal
operation of RAC as a timeout for voting file I/O to complete.

 

3. Oracle Database 10.2.0.3

 

This
version also has same parameters as that of Oracle Database 10.2.0.2;
also the default values are same as Oracle Database 10.2.0.2. There is
slight difference in the internal calculation of there parameter
values; If disktimeout is less than the misscount value then during
cluster formation and throughout cluster operation misscount -
reboottime is considered as disktimeout and the modified parameter
disktimeout is ignored.

That is in Oracle Database 10.2.0.3 diskshorttimeout = disklongtimeout if css disktimeout parameter is less than css misscount.

 

4. Recommendations for Oracle Database 10g Release 2 CSS parameter values to be used with NetApp storage:

 

As
diskshorttimeout = misscount - reboottime; and if misscount &
reboottime are kept as default values i.e. 60 seconds & 3 seconds
respectively; the time for accessing voting file will be considered as
57 seconds by CSS, so If the reconfiguration happens during the NetApp
Storage takeover or giveback process there are chances of CRS reboot
taking place; hence following are the recommended values for CSS
timeout parameters for Oracle Database 10g Release 2 RAC to work
smoothly during NetApp Storage takeover and giveback process.

1. Oracle Database 10.2.0.1

misscount = 120 seconds (default is 60 seconds)

2. Oracle Database 10.2.0.1 + Patch for Bug 4896338

misscount = 120 seconds (default is 60 seconds)

disktimeout = 200 seconds (default)

reboottime = 3 seconds (default)

3. Oracle Database 10.2.0.2

misscount = 120 seconds (default is 60 seconds)

disktimeout = 200 seconds (default)

reboottime = 3 seconds (default)

4. Oracle Database 10.2.0.3

misscount = 120 seconds (default is 60 seconds)

disktimeout = 200 seconds (default)

reboottime = 3 seconds (default)

All the above recommendations are for Linux Operating system.

Note:
The stock version of Oracle database 10g Release 2 lower than 10.2.0.2
do not provide all the configurable CSS parameters; hence it is
advisable to upgrade Oracle Database to 10.2.0.2 or higher.

 

Appendix

Commands to check / modify CSS parameters:

1. crsctl get css misscount ---------- to check misscount value

2. crsctl get css disktimeout --------- to check disktimeout value

3. crsctl get css reboottime ---------- to check reboottime value

4. crsctl set css misscount 120 --------- to set misscount to 120 seconds

5. crsctl set css disktimeout 200 ------- to set disktimeout to 200 seconds

6. crsctl set css reboottime 3 ----------- to set reboottime to 3 seconds 


http://el-caro.blogspot.com/2006/10/case-study-on-how-to-diagnose-node.html


There have been a couple of additional CSS related parameters introduced in the
10.2.0.2 patchset to address long I/O requirements of storage vendors such as
EMC and NetApp.

• reboottime: (default 3 seconds)
The amount of time
allowed for a node to complete a reboot after the CSS daemon has been
evicted.
This parameter can be set via the command
crsctl set css
reboottime R [-force] (R is seconds)

• disktimeout (default 200 seconds)

The maximum amount of time allowed for a voting file I/O to complete; if
this time is exceeded the voting disk will be marked as unavailable
This
parameter can be set via the command
crsctl set css disktimeout D [-force] (D
is seconds)

These commands must be run as root on a node where the CRSD
is up, unless
'-force' is specified, in which case the the CRS stack should
not be up on any other node.
After the commands are run all CSS daemons must
be restarted for the settings to take effect on all nodes asap.
You can
verify the settings by doing an ocrdump and checking the values in the
OCRDUMPFILE

 

Office : Outlook Rule limits

http://corinnalo.blogspot.com/2008/10/exchange-outlook-rules-32k-limit.html

There is a Microsoft KB that talks about some workarounds on the
Exchange 32K rules storage limit problem. Basically, rename your rules
to shorter names, merge some of your rules, change your local folders
to shorter names, move your Outlook data files to another location on
the file system that has a shorter path, etc.