Saturday 27 December 2008

Top SQLs by Disk Read via V$SQLAREA

with base_data as
  (
  select
  v.HASH_VALUE,
  disk_reads,
  executions,
  v.CPU_TIME,
  sql_text,
  v.FIRST_LOAD_TIME
 from v$sqlarea v
 order by 2 desc, 3 desc
 )
 select
 sysdate snaptime,
 null hash_value,
 sum(disk_reads) disk_reads,
 sum(executions) executions,
 sum(cpu_time)   cpu_time,
 '!ALL SQL SUMMARY' SQL_TEXT,
 NULL first_load_time
 from base_data
 union all
 select  
 sysdate,
 HASH_VALUE,
 disk_reads,
 executions,
 CPU_TIME,
 sql_text,
 FIRST_LOAD_TIME
from base_data
 where rownum <=20

Thursday 18 December 2008

Clear log file via inline edit

Trim off first 1000000 lines

# No backup
perl -i -ne 'print unless 1 ..1000000' server.log

# With backup

perl -i.bkp -ne 'print unless 1 ..1000000' server.log


du : Sort by size and display in human

Not terribly economic but functional

du -sk * | sort -nr | cut -f2 | xargs du -sh

Tuesday 9 December 2008

Wednesday 3 December 2008

Using Xming (against solaris)

Needed a portable x-server to run oracle installers etc on a solaris box without vnc installed

So dragged down xming from http://www.straightrunning.com/XmingNotes/

Installed into a temp directory - the app is portable
Included the local version of plink when prompted,  declined integration with the desktop (portable!)
Needed the optional font package - installed on top of the xming install but stripped it down to the TTF font directory to save space

Create a session by using the xlaunch.exe program - which is a wizard to create a .xlaunch file to store parameters to feed to xming.exe
xlaunch.exe
  • Chose
  • one window,
  • display 0,
  • start a program,
  • program = /usr/bin/gnome-session - note the path ! - other options /usr/openwin/bin/xterm, /usr/openwin/bin/twm (solaris default windows manager)
  • using putty
  • connect to <servername>
  • login as <username>
  • password <password and to hell with security though the password does seem to blank out when the wizard is re-run>
  • Clipboard
  • TURN OFF THE ACCESS CONTROL if NOT relying on plink
  • No other params
  • Save everything to a .xlaunch file
To run without the wizard - xlaunch.exe -run config.xlaunch
To re-edit - xlaunch.exe -run config.xlaunch


Even the clipboard worked





Tuesday 25 November 2008

Viewing a Refcursor in TOAD

Run a PL/SQL block in the SQL editor as follows:<br />declare<br />begin<br />   OPEN :pv_cur FOR 'select * from dual';<br />end;<br /><br />Or if you have a function returning a refcursor<br /><br />declare<br />BEGIN<br />   :pv_cur := function_name(param1,param_etc);<br />END;<br /><br />TOAD will prompt you for the datatype/value of ':pv_cur'. <br />Change the type to 'Cursor' and leave the value blank, hit OK. <br />The ref cursor will be displayed in the standard data grid below, as if you had run a query.<br /><br /><br />

Tuesday 18 November 2008

New database for crowd

Tough to find this one as its not in the install directories

/var/opt/atlassian-crowd-home/crowd.cfg.xml

See also
http://confluence.atlassian.com/display/CROWD/Important+Directories+and+Files

Tuesday 11 November 2008

Oracle: Checking a column is numeric

select * from table1
where
ltrim(rtrim(translate(col1,'0123456789-.',' '))) is not null

Wednesday 5 November 2008

Perl one-liner to summarise file volumes by hour



ls -l /u02/volestlivelogs01/oradata/ESTDB/archive/*.dbf | sed -e "s/:/ /g" -e "s/  / /g" | perl -lane 'BEGIN {%hash=();} $hash{"$F[5]$F[6]-$F[7]:00"}+= $F[4]; END {foreach $k (sort keys %hash) {print "$k " . int($hash{$k}/(1024*1024)) . " Mb"; } }'

Monday 3 November 2008

Lost msconfig.exe

The location is not in the path - rather it is held in a registry key

Create a .reg file with the following contents

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\MSCONFIG.EXE]
@="C:\\WINDOWS\\PCHealth\\HelpCtr\\Binaries\\MSConfig.exe"

Friday 24 October 2008

Where did my oratab go (on Solaris)

/var/opt/oracle/oratab

Wednesday 15 October 2008

Is my Oracle db in archivelog mode

To verify that the database is in no archive log mode log into the
sqlplus as sys and issue following command and u must get the following
output

archive log list

To enable the database in archive log mode following parameters must be set in init.ora file of instance


log_archive_dest_1=<path of the destination where u want the archive logs to be placed>


log_archive_dest_state_1=enable


log_archive_start=true( this parameter is deprecated in Oracle 10g)


log_archive_format=arc_%t_%s_%r.dbf

Friday 3 October 2008

RPM - Listing in date order

Useful for looking at recent changes

rpm -qa --last | less

rpm -qa --queryformat '%{installtime} (%{installtime:date}) %{name}\n'



Monday 29 September 2008

OEM Agent - Cleaning out

On the Repos Server

SELECT target_name
FROM sysman.MGMT_TARGETS
where 1=1
and target_type = 'oracle_emd'
and target_name like '%mis%'
order by 1

commit

execute sysman.mgmt_admin.cleanup_agent('target_name:port>');

If this hangs then kill it and run ...

update sysman.MGMT_TARGETS_DELETE set delete_complete_time = sysdate
where target_name like '%slofosdb01.odl.com:3872%'

On the Agent Machine
  • rm - rf $OracleHome/sysman/emd/upload
  • rm -rf $OracleHome/sysman/emd/recv
  • rm -rf $OracleHome/sysman/emd/state
  • Resecure the connection - emctl secure agent
  • Restart the agent - emctl start agent
  • Upload info - emctl upload

Tuesday 23 September 2008

Speaking Imap (in telnet)

http://support.microsoft.com/kb/189326

Telnet IP address of Mail server (Exchange) 143

LOGIN NTDOMAIN/NTACCOUNT/ALIAS PASSWORD

? LIST "" "*"

? Select Folder (where Folder is the mailbox folder that you want, such as Inbox or Deleted Items)

? FETCH message number> All (where message number is 1, 2, 3, and so on)
? FETCH message number Body (where message number is 1, 2, 3, and so on)

? LOGOUT

MS Server 2003 : Getting an RDP session

To use the command line hacks, you might need to run them from another server if your local operating system doesn't include the commands. You will also need to make sure that you are logged onto that server with an administrative account. The easiest way to do that is just map a drive (you don't have to use a drive letter unless you choose to)

net use /user:[username] \\servername\share

Here's a command line hack that you can use to figure out what
sessions are connected to the server. Note that you could substitute
the IP address for the server name.

query session /server:servername

Now we know that the session ID of the offending session is 2. We can
use that in the next step, which is using the reset command to log off
that user.

reset session [ID] /server:servername

Friday 19 September 2008

NTLM in Firefox

  • Go to your Firefox address bar and type about:config.
  • Look for the key called network.automatic-ntlm-auth.trusted-uris.
  • Set that key’s value to a comma separated list of servers you want NTLM auth for.
  • Name of each server must be exactly the same as in the URL used
  • Seem to need to restart Firefox for it to take effect



Wednesday 17 September 2008

Adding an 11G RAC service

  • . oraenv NDEV1C
  • srvctl add service -d NDEV1C-s "OTIIDEV" -r "NDEV1C1,NDEV1C2" -P BASIC
  • srvctl status service -d NDEV1C-s "OTIIDEV"
  • srvctl start service -d NDEV1C-s "OTIIDEV"
  • srvctl stop service -d NDEV1C-s "OTIIDEV"
  • srvctl remove service -d NDEV1C-s "OTIIDEV"
  • srvctl remove service -d NDEV1C-s "OTIIDEV" -r "NDEV1C1,NDEV1C2"
alter system set  service_names='NDEV1C.ODLS.COM,OTIIDEV.ODLS.COM' scope=both

alter system register

Tuesday 16 September 2008

Grep for rows after and before a pattern

Gnu gre.

grep -A 2 ORA-01555 bdump/alert_LIVE1.log | grep -B2 SYS.EXU9R

-A = After
-B = Before

Friday 5 September 2008

SmartCVS - ignoring files in foundation edition

Create the file .cvsignore in your home directory with exclusion pattern per line
F
or windows this is %USERPROFILE% and you need to rename the file to .cvsignore at the command prompt as explorer does not like files with only suffixes

Wednesday 28 May 2008

Find with Grep

find . -exec grep -H -n -i 'string' {} \;

Wednesday 6 February 2008

FoxyProxy

FoxyProxy is a Firefox addin that can automatically switch web proxy based on string matches to the entered URL.

Trying this out with Portable Tor - seems a goody

Saturday 2 February 2008

The Illusionist (2009)

Sylvain Chomet (of Belleville Rendez-Vous - a great film) has a current project The Illusionist based on a Jacques Tati script - out in 2009 - one to watch for :)

Friday 25 January 2008

Removing Duplicates in Excel 2007

Removing Duplicates in Excel 2007

1. To the right of your data, copy the heading from the column where you want to find unique values.
2. Select a cell in your data set.
3. In Excel 2007, choose the Advanced icon from the Sort & Filter group of the Data ribbon.
4. Choose Copy to another Location
5. In the Copy To box, specify the copy of your heading. In the Figure, this is cell D1
6. Click the box for Unique Records Only
7. Click OK"

Thursday 24 January 2008

Always show year with ls

ls -l --time-style=long-iso

Thursday 17 January 2008

Old rope : VNC Over SSH

Always forget how to do this and have to research it again.

This time the scenario is getting an X session on a db server to install oracle software, but the sys admin has locked down the ports so that only the conventional ones (ssh) are open to the outside (well inside the intranet) world (found out using /sbin/iptables --list) . At least vnc is still installed.

Of course the box is open for ssh - so using the very handy tool plink we first set off a vncserver process on the remote box noting down the port being used

Then run the following from the command line on my local windows workstation
plink.exe -ssh -L 5900:localhost:5903 REMOTE_USER@REMOTE_SERVER -pw PASSWORD

This tunnels from the local 5900 port to the remote 5903 port on REMOTE_SERVER by opening a remote session for REMOTE_USER with PASSWORD

Then by opening a vnc viewer locally looking at localhost:5900 we get the desired vnc session.

Fini

Wednesday 16 January 2008

Shuttle KPC


Also see koolu

What is BlogThis! ?

Clicking BlogThis! creates a mini-interface to Blogger prepopulated with a link to the web page you are visiting, as well as any text you have highlighted on that page.

Add additional text if you wish and then publish or post from within BlogThis!

Monday 14 January 2008

BlackBerry: Receiving messages auto-filed in subfolders


I wasn't receiving messages that were being filed by a rule I had set up in outlook - looks like by default the BlackBerry only replicates Inbox and Sent Items - but found a fix here - summary below

How To - Enable Folder Redirection with Inbox sub-folders

  1. From the Home screen, go to Messages.
  2. Click the trackwheel and select Options - Email Settings.
  3. Click the trackwheel and select Folder Redirection.
  4. In the Folder Redirection menu, expand the Mailbox by highlighting it, clicking trackwheel and selecting Expand.
  5. Under the Mailbox, expand the Inbox by highlighting it, clicking the trackwheel and selecting Expand.
  6. Highlight the folder on which you want to enable redirection. Click the trackwheel and select Change Option. Note: If
    you are unable to select Expand for any of the folders, or if the Change Option menu option is unavailable, try turning Wireless Reconcile on the handheld off and then on again. You can find Wireless Reconcile in Messages - Options - Email Reconciliation.
  7. Click the trackwheel and select Save to save your changes.

Fini