Oracle, Sqlserver, Linux. Mainly.

Mis-spelling in Powershell

2012-12-07T14:49:00.000Z

This was spectacularly incompetent even by my high standards of incompetence.

PS SQLSERVER:\> get-wmiobject -class win32_operatingsystem -computer rdm016 | 
select __Server, ServicePackMajorVerstion

And got:

__SERVER                                ServicePackMajorVerstion
--------                                ------------------------
RDM016

I thought that no service packs had been applied. This, as more attentive readers will have spotted, was no the case. The null under ServicePackMajorVerstion is because you don't spell 'version' with a 't'. Two learnings from this:

one should test one's code
if you put something invalid in a 'select' list in Powershell it won't necessarily error

4 ways of breaking comment-based help in Powershell

2012-10-10T18:02:00.003+01:00

A quick note on the different ways I've found of breaking the comment basaed help in Powershell. I've only started playing with it today. It's a great feature, but it does perhaps seem a little fussy. I've not verified all of these - I'll do so when time allows

Leave the '.' off of a label

If I leave out the full-stop on one of the labels:

SYNOPSIS
Lists services on specified server which are set as autostart but are currently stopped
.DESCRIPTION

Uses wmi to get services into

It breaks the whole of the help:

PS C:\> help sstop

show-stoppedservices [[-MyServer] ]

Have a function with the same name as a script

If my function name is kept in a ps1 file with the same name as the function, then the help subsytem just shows the different items. If I rename the .ps1 file as follows:

move function-show-stoppedservices.ps1 show-stoppedservices.ps1

And I try to get help on the function (using the full function name - the alias would be OK):

PS C:\> help show-stoppedservices
Name Category Synopsis
---- -------- --------

show-stoppedservices                  Function    Lists services on specified serv...
show-stoppedservices.ps1          Extern...  show-stoppedservices.ps1 ...

Don't name the parameter correctly

If you mis-spell the name of the parameter in the Help text, then you'll lose any description that you add in, obviously enough:

.PARAMETER NotMyServer
Specify the remote server. If null tells you about wherever you are
running

Code:
Param ( [String] $MyServer = "." )

Powershell will still give you the definition of the parameter but you lose the help text.

PS p:\powershell\functions> help show-stoppedservices -parameter MyServer

type                       name                       parameterValue
----                       ----                       --------------
@{name=String}             MyServer                   String

Putting this right:

.PARAMETER MyServer

Allows Powershell to use your help:
PS p:\powershell\functions> help show-stoppedservices -parameter MyServer

type                name                parameterValue      description
----                ----                --------------      -----------
@{name=String}      MyServer            String              {@{Text=Specify ...

Not (re-) sourcing the function! :)

To be completed

Create dated folders in Linux

2011-12-21T14:32:00.003Z

For reasons too tedious to relate here, I wanted to create a lot of folders in the format:

/tmp/20120101_matt
/tmp/20120102_matt
/tmp/20120103_matt/tmp/20120104_matt/tmp/20120105_matt/tmp/20120106_matt

This does the trick

for i in {0..365}

do

   mkdir /tmp/$(date --date="+$i days" +%Y%m%d)_matt

done

Happy Christmas!

Dos code to remove duplicates

2011-11-24T11:12:00.001Z

There's probably a much easier way to do this, but this code removes duplicate lines from a file. Only the first occurrence of a line is kept, but the order of the first occurrences is maintained

for /F %I in (cfc_scorers.txt) do (findstr %I cfc_order_of_scoring.txt
if ERRORLEVEL 1 echo %I >> cfc_order_of_scoring.txt
)

So if cfc_scorers.txt is:

Anelka
Malouda
Boswinga
Lampard
Mata
Mata
Terry
Sturridge
Torres
Torres
Ramires
Ramires
Lampard
Sturridge
Lampard
Sturridge

Then the created file will be:

Anelka
Malouda
Boswinga
Lampard
Mata
Terry
Sturridge
Torres
Ramires

Maintenance Cleanup Task not working

2011-11-09T12:12:00.000Z

I set up a Sqlserver Maintenance Cleanup Task, but couldn't get it to delete the appropriate backup files.

The log looked like this:

Maintenance Cleanup Task ()
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 5 Weeks
Task start: 2011-11-09T11:45:01.
Task end: 2011-11-09T11:45:01.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\mssql_backup'',N''.bak'',N''2011-10-05T11:45:01'',1

GO

...which looks reasonable.

It was showing 'Success'

My mistake was that I had entered .bak rather than just bak as the 'File extension'. The '.' is not needed. Presumably it was looking for files called 'whatever..bak'

Sqlserver Management Studio

2011-10-13T12:42:00.000+01:00

I spent a couple of lunchtimes going through Sqlserver Management Studio's 'Object Explorer' panel, typing up all the different elements. This might seem like an odd thing to do, but I thought it might be useful for the following reasons:

- as a reference. A searchable list of where everything is

- as the beginnings of a checklist. Making a system wide change it might be handy to scan through this list and consider whether there's an impact on each element

- to get me blogging again :)

Databases
System Databases
Database Snapshots
User Databases
    Database Diagrams
    Tables
    Views
    Synonyms
    Programmability
      Stored Procedures
      Functions
      Triggers
      Assemblies
      Types
      Rules
      Defaults
      Plan Guides
    Service Broker
      Message Types
      Contracts
      Queues
      Services
      Routes
      Remote ServiceBindings
      Broker Priorities
    Storage
      Full Text Catalogs
      Partition Schenes
      Patition Functions
      Full Text Stoplists
    Security
      Users
      Roles
      Schemas
      Asymmetric Keys
      Certificates
      Symmetric Keys
      Database Audit Specifications
Security
Logins
Server Roles
Credentials
Cryptographic Providers
Audits
Server Audit Specifications
Server Objects
Backup Devices
Endpoints
    System Endpoints
      Database Mirroring
      Service Broker
      SOAP
      TSQL
        Dedicated Admin Connection
        TSQL Default TCP
        TSQL Default VIA
        TSQL Local Machine
        TSQL Named Pipes
    Database Mirroring
    Service Broker
    SOAP
    TSQL
Linked Servers
    Providers
Triggers
Replication
Local publications
Local subscriptions
Management
Data-tier Applications
Policy Management
    Policies
      System Policies
    Conditions
      System Conditions
    Facets
Data Collection
Resource Governor
    Resource Pools
      System Resource Pools
Maintenance Plans
SQL Server Logs
Database Mail
Distributed Trnasaction Co-ordinator
Legacy
Database Maintenance Plans
Data Transformation Services
SQL Mail
SQL Server Agent
Jobs
Alerts
Operators
Proxies
Error Logs

Nid(dbnewid) hangs

2011-07-26T11:59:00.000+01:00

I had to run nid (also known as dbnewid) on a database as part of a test refresh at the weekend. This changes the dbid of the database.

Out of sheer laziness I ran it from with sqlplus by using the '!' host command feature.

Nid, quite understandablly doesn't really like this - it hangs.

I ctrl-c'd it, exited to the OS and re-ran and it worked fine.

The 10g doc for nid is here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dbnewid.htm

OT: A bit of politics....

2010-04-27T09:42:00.000+01:00

As some will know, there's a General Election happening in the UK next month. It's likely to be the closest for some time.

Anyhow, on "my other blog" I've managed to get the three main candidates for my local constituency, which is Salisbury in Wiltshire, to do an email Question and Answer thing. Some of the questions were dreamt up by me, and others by local Twitterers.

If you're interested the posts are:

Back to techie stuff with the next post!

Mucking about with analytic functions

2010-04-19T16:45:00.003+01:00

I've never really used the analytic functions in anger, so consequently I don't really have the skills at my fingertips. By way of re-learning them (using this old-ish book by Tom Kyte) I've been playing about with a list of English Premiership scorers.

The table is:



SQL> desc goals

Name                                      Null?    Type

----------------------------------------- -------- ------------



DIVISION                                  NOT NULL VARCHAR2(20)

END_DATE                                  NOT NULL DATE

PLAYER                                    NOT NULL VARCHAR2(40)

TEAM                                               VARCHAR2(20)

GOALS                                              NUMBER

The third highest scorer for each team, should you want to know that bit of information, is given by:



select * 

from (select team, player, goals, 

row_number() over (partition by team order by goals desc) ranking

from goals)

where ranking = 3

Output is:

TEAM             PLAYER                GOALS RANKING
---------------- --------------------- ----- -------
Arsenal          Robin van Persie          7       3
Aston Villa      Ashley Young              5       3
Birmingham       Sebastian Larsson         4       3
Blackburn        Morten Gamst Pedersen     3       3
Bolton           Kevin Davies              5       3
Burnley          David Nugent              6       3
Chelsea          Florent Malouda          11       3
Everton          Mikel Arteta              5       3
Fulham           Damien Duff               6       3
Hull City        Deiberson Geovanni        3       3
Liverpool        Steven Gerrard            7       3
Man Utd          Antonio Valencia          5       3
Manchester City Craig Bellamy             9       3
Portsmouth       Nadir Belhadj             3       3
Stoke City       Ricardo Fuller            3       3
Sunderland       Fraizer Campbell          4       3
Tottenham        Niko Kranjcar             6       3
West Ham Utd     Guillermo Franco          4       3
Wigan Athletic   Paul Scharner             4       3
Wolves           Matthew Jarvis            3       3

To further develop this query to return a pivot table, you use a decode on the artificial 'ranking' field, as follows:



select team,

max(decode(ranking, 1, player, null)) golden_boot,

max(decode(ranking, 2, player, null)) silver_boot,

max(decode(ranking, 3, player, null)) bronze_boot

from

(select team, player, goals, row_number()

over (partition by team order by goals desc) ranking

from goals)

where ranking <= 3 group by team

This returns:



SQL> /



TEAM             GOLDEN_BOOT         SILVER_BOOT         BRONZE_BOOT

---------------- ------------------- ------------------- -------------------

Arsenal          Francesc Fabregas   Andrey Arshavin     Robin van Persie

Aston Villa      Gabriel Agbonlahor  John Carew          Ashley Young

Birmingham       Cameron Jerome      Lee Bowyer          Sebastian Larsson

Blackburn        David Dunn          Jason Roberts       Morten Gamst Peders

Bolton           Matthew Taylor      Ivan Klasnic        Kevin Davies

Burnley          Steven Fletcher     Graham Alexander    David Nugent

Chelsea          Didier Drogba       Frank Lampard       Florent Malouda

Everton          Louis Saha          Tim Cahill          Mikel Arteta

Fulham           Bobby Zamora        Clinton Dempsey     Damien Duff

Hull City        Stephen Hunt        Jimmy Bullard       Deiberson Geovanni

Liverpool        Fernando Torres     Dirk Kuyt           Steven Gerrard

Man Utd          Wayne Rooney        Dimitar Berbatov    Antonio Valencia

Manchester City  Carlos Tevez        Emmanuel Adebayor   Craig Bellamy

Portsmouth       Aruna Dindane       Frederic Piquionne  Nadir Belhadj

Stoke City       Matthew Etherington Tuncay Sanli        Ricardo Fuller

Sunderland       Darren Bent         Kenwyne Jones       Fraizer Campbell

Tottenham        Jermain Defoe       Peter Crouch        Niko Kranjcar

West Ham Utd     Carlton Cole        Alessandro Diamanti Guillermo Franco

Wigan Athletic   Hugo Rodallega      Charles N'Zogbia    Paul Scharner

Wolves           Kevin Doyle         Jody Craddock       Matthew Jarvis



20 rows selected.

Using wget, sed and gawk to get a description of the DBA_ views

2010-03-30T17:46:00.002+01:00

I'm studying for Oracle certification, and I thought it would be handy to get a cribsheet list of all of the DBA_ views and what they are for.

Rather than create this manually, I did the following.

Downloaded the top level contents



wget -r -l1 -k http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/toc.htm

wget gets pages from the web. '-r' says to also retrieve links. '-l1' says to only get one level of links (i.e. get the pages linked to from the first page, but don't get the pages linked to by them). '-k' says to edit the internal links so you can go from one page to another on the downloaded copy.

Next I run the following from the dos prompt:



findstr /S DBA_ *.htm |^

findstr "\"|^

sed -e "s//~/" -e "s/<\/code>/~/" |^

gawk -F~ "!/same as those/{print $2 \"~\" $3,$4,$5,$6,$7,$8,$9}" |^

sed -e  "s/<[^>]*>//g" |^

sort -u

I'll go through that line by line.

Find occurences of the string DBA_ in any htm files. The /S on findstr makes it search subdirectories too



findstr /S DBA_ *.htm |^

Look for the html paragraph tag. This just cut out some of the occurences of DBA_ where it isn't the descriptive line I want



findstr "\"|^

This sed command replaces both the code begin and end tags with a '~' (tilda). There are probably ways of avoiding this step by making the following awk do more work, but I don't how!. The 'sed' executable is from the unxutils package.



sed -e "s//~/" -e "s/<\/code>/~/" |^

'Same as those' is just another filter. The second field is the name of the DBA_ view.



gawk -F~ "!/same as those/{print $2 \"~\" $3,$4,$5,$6,$7,$8,$9}" |^

This bit of sed removes html tags from the text. To be honest I'm not entirely sure how it works but I can see that it does. When I tried to sed it myself I found that if there were two tags on a line it would remove both the tages and anything between them. I found the code here: remove html tags from a file | UNIX | Tech-Recipes. One day I'll get out my sed book and work it out !



sed -e  "s/<[^>]*>//g" |^

This is the Unix sort, also from unxutils.



sort -u

The output from all of this is like this:



DBA_TABLES~ describes all relational tables in the database.

DBA_TABLESPACE_GROUPS~ describes all tablespace groups in the database.

DBA_TABLESPACES~ describes all tablespaces in the database.

DBA_TEMP_FILES~ describes all temporary files (tempfiles) in the database.

It needed a little bit of cleaning up. I would post the whole list, but I would assume Oracle wouldn't be pleased. I am assuming that the wget is OK - in principle it's little different to browsing.

You can adapt the technique for the initialization parameters etc

ORA-01552: cannot use system rollback segment.....

2010-02-24T16:42:00.000Z

Another post that's unlikely to be all that useful....but you never know.

As per a couple of my previous posts, I'm re-doing my Oracle certification from the ground up instead of doing the 10G new features only.

I'm using Oracle Database 10g OCP Certification by Damir Bersinic and John Watson, which from the bits I've read so far, I would recommend.

Anyhow, the book says:

If you are using UNDO_MANAGEMENT=AUTO, you must also specify UNDO_TABLESPACE.

I was curious to see how this was enforced, in particular whether you would get an error if you tried to ALTER SYSTEM to nullify the undo tablespace parameter. You don't - you get an error when you try to use the undo, as follows:

SQL> @sparam

Enter value for parameter_name: UNDO

old   5: where name like lower('%¶meter_name%')

new   5: where name like lower('%UNDO%')

undo_management                AUTO                                          F

undo_retention                 900                                           T

undo_tablespace                UNDOTBS1                                      F



SQL> alter system set undo_tablespace='' scope =both;



System altered.



SQL> delete from scott.emp;

delete from scott.emp

                  *

ERROR at line 1:

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

When does the 'shutdown database' trigger fire?

2010-02-12T12:32:00.009Z

The chances of this being useful to anybody are fairly remote, but I was reading through my 'Bumper Book of Oracle Certification' and thought I'd check in what circumstances the 'before shutdown' trigger fires.

This summarizes what I found:

Shutdown normal		Yes
Shutdown immediate		Yes
Shutdown transactional		Yes
Shutdown abort		No
Shutdown of a startup-nomounted database		No
Shutdown of a mounted database		No

To be fair, this is exactly what I would have expected. I wasn't entirely sure about shutdown immediate, but you would expect a) a trigger to only fire when the database is open and b) shutdown abort to do nothing other than shutdown.

Demo follows

Create a trigger
First create a table to hold the messages and a trigger



SQL> create table my_messages(message_date date, message_text varchar2(1000));



Table created.



SQL> save ctmm

Created file ctmm.sql

SQL> host gvim ctrbshut.sql



SQL> @ctrbshut.sql



Trigger created.

...where the script is



SQL> get ctrbshut.sql

1  create trigger before_shutdown before shutdown on database

2  begin

3    insert into my_messages(message_date, message_text)

4    select sysdate, 'Database shutting down' from dual;

5* end;

Check the shutdown trigger works with a normal shutdown



SQL> set head off

SQL> col message_text format a30

SQL> select to_char(message_date, 'DD-MON-YYYY HH:MI:SS'), 

message_text 

from my_messages;



no rows selected



SQL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;



12-FEB-2010 11:28:30



SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Database mounted.

Database opened.

SQL> select to_char(message_date, 'DD-MON-YYYY HH:MI:SS'), 

message_text 

from my_messages;



12-FEB-2010 11:29:05 Database shutting down   <---the shutdown normal

Does the shutdown trigger fire for a shutdown transactional?



SQL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;



12-FEB-2010 11:30:00



SQL> shutdown transactional

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Database mounted.

Database opened.



SQL> select to_char(message_date, 'DD-MON-YYYY HH:MI:SS'), 

message_text 

from   my_messages;

12-FEB-2010 11:29:05 Database shutting down   <---the shutdown normal 

12-FEB-2010 11:30:25 Database shutting down   <---the shutdown transactional

Does the shutdown trigger fire for a shutdown immediate?



SQL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;



12-FEB-2010 11:31:44



SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Database mounted.

Database opened.

SQL> select to_char(message_date, 'DD-MON-YYYY HH:MI:SS'), 

message_text 

from   my_messages;

12-FEB-2010 11:32:05 Database shutting down   <---the shutdown immediate 

12-FEB-2010 11:29:05 Database shutting down   <---the shutdown normal 

12-FEB-2010 11:30:25 Database shutting down   <---the shutdown transactional

Does the shutdown trigger fire for a shutdown abort?



SQL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;



12-FEB-2010 11:32:52



SQL> shutdown abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Database mounted.

Database opened.

SQL> select to_char(message_date, 'DD-MON-YYYY HH:MI:SS'), 

message_text 

from   my_messages;

12-FEB-2010 11:32:05 Database shutting down   <---the shutdown immediate 

12-FEB-2010 11:29:05 Database shutting down   <---the shutdown normal 

12-FEB-2010 11:30:25 Database shutting down   <---the shutdown transactional

No!

Shutdown to do a startup mount
This isn't relevant to the discussion of the shutdown trigger - I did a shutdown here just to get the database down and then up to a nomount state



SQL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;



12-FEB-2010 11:34:23



SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

Does the shutdown trigger fire if the database is shutdown from a nomount or mounted state?
In this passage, I do a startup nomount and shutdown, then a startup mount and shutdown.

The trigger does not fire.



SQL> startup nomount

ORACLE instance started.



SQL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;



12-FEB-2010 11:35:15



SQL> shutdown

ORA-01507: database not mounted





ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Database mounted.

SQL> select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;



12-FEB-2010 11:36:16



SQL> shutdown

ORA-01109: database not open





Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Database mounted.

Database opened.

SQL> select to_char(message_date, 'DD-MON-YYYY HH:MI:SS'), 

message_text 

from   my_messages;

12-FEB-2010 11:32:05 Database shutting down   <---the shutdown immediate 

12-FEB-2010 11:29:05 Database shutting down   <---the shutdown normal 

12-FEB-2010 11:30:25 Database shutting down   <---the shutdown transactional

12-FEB-2010 11:34:32 Database shutting down   <---from the 2nd normal shutdown  SQL>

The 'after shutdown' trigger
I couldn't resist seeing whether Oracle would let me create an 'after shutdown' trigger. I was pleased to see there's a special error message for it :)



SQL> create trigger after_shutdown after shutdown on database

2  begin

3    insert into my_messages(message_date, message_text)

4    select sysdate, 'Database shutting down' from dual;

5  end;

6  /

create trigger after_shutdown after shutdown on database

*

ERROR at line 1:

ORA-30501: instance shutdown triggers cannot have AFTER type

10G Oracle certification

2010-02-05T11:44:00.003Z

I'm studying for my 10G certification, and to add a bit of variety I thought it would be worth typing up some odd bits and pieces here.

I originally did Oracle certification for version 7.3, when you had to do 4 exams.

Since then, I've always done the upgrade exams. I found this very useful in that you have to learn all of the new features - I tend to find that otherwise I concentrate only on the things that I find interesting or useful at the time.

Anyhow, this time I've taken a different tack - I'm doing the two exams which would certify you as a DBA 'from scratch'.

A couple of reasons for this:

it makes a change

I did a 10G New Features course back in 2008. That, and the passage of time, mean that I think I've got a good enough grasp on the new features

I thought there would be some value in going 'back to basics' - to revisiting some of the theory I may have forgotten over all these long years of practice :)

I'm using the book below - it seems quite good so far:
Bumper Book of Oracle Certification

Convert an html table to excel spreadsheet

2009-03-01T16:27:00.000Z

A quick and dirty post for a quick and dirty technique that I used to get the Oracle CPU Database Risk Matrix into a spreadsheet. This won't always work (the html table I was interested in happened to have the data in the same line of html as the tag), but it's a start.

- Do 'View Source' to get the html for the table. - Find the table - Cut and paste the table into a text file. I called mine "risk_matrix_raw.txt" - Create an awk file (mine was called risk_matrix.awk) with the following lines:

- awk the text file, then pipe into sed to remove some of the tags

- Open the .txt file in Excel. When I did this, Excel asks whether its a fixed width or a delimited file. I specified that the file was delimited with a '~' and Excel opened it.

Changing the DOS command prompt

2008-03-12T10:35:00.002Z

All the options for changing the command prompt are, logically enough, shown by:

prompt /?

My current favorite, especially when my current directory path is quite long is:

prompt $P$_$T$G

$P is the path
$_ is a line feed
$T is the time
$G is a '>'

So my current command prompt would be, for example:

D:\oracle\product\10.2.0\db_1\OPatch\opatchprereqs\oui
19:44:36.95>

Creating a timestamp string in DOS

2008-02-27T10:49:00.004Z

I found a better way of doing this using the DOS variables %DATE% and %TIME%

To extract the elements individually you can do:

set DD=%DATE:~0,2%
set MM=%DATE:~3,2%
set YYYY=%DATE:~6,4%
set HH=%TIME:~0,2%
set MI=%TIME:~3,2%
set SS=%TIME:~6,2%

....or to do it in one hit:

set TIMESTAMP=%DATE:~6,4%%DATE:~3,2%%DATE:~0,2%^
%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
echo %TIMESTAMP%

TIMESTAMP will then look like this:

20080811100011

Note: I only just noticed, somewhat shamefully, that if you do this before 10:00am, you get a space in the timestamp, because the hour is represented as ' 9', rather than '09'. When I figure out a workaround I'll post it.

DOS equivalent of unix 'whence' or 'type'

2008-01-25T16:44:00.000Z

Found this construct looking in the help for FOR - I can't quite work out how to use it in a batch file, sadly.....

for %I in ("sqlplus.exe") do echo %~$PATH:I

gives

D:\oracle\product\10.2.0\client_1\BIN\sqlplus.exe

Update:

With many thanks to my friend Dave....

To create a whence.bat script, you need to have the following content:

@echo off
set TO_FIND=%1
for %%I in ("%TO_FIND%") do echo %%~$PATH:I

...then you can type, for example, 'whence sqlplus.exe' to locate sqlplus.

opatch error: Perl lib version (v5.8.3) doesn't match executable version (v5.6.1) at x:\oracle...

2008-01-19T12:32:00.000Z

Got the following error:

C:\oracle\ora92\OPatch>perl opatch.pl version
Perl lib version (v5.8.3) doesn't match executable version (v5.6.1) at x:\oracle
\10.2.0\perl\5.8.3\lib/MSWin32-x86-multi-thread/Config.pm line 32.
Compilation failed in require at x:\oracle\10.2.0\perl\5.8.3\lib/MSWin32-x86-mul
ti-thread/lib.pm line 6.
BEGIN failed--compilation aborted at x:\oracle\10.2.0\perl\5.8.3\lib/MSWin32-x86
-multi-thread/lib.pm line 6.
Compilation failed in require at opatch.pl line 88.
BEGIN failed--compilation aborted at opatch.pl line 88.

Kind of obvious, but the fix here is to set your PERL5LIB variable correctly...

C:\oracle\ora92\OPatch>set PERL5LIB=C:\perl\lib
C:\oracle\ora92\OPatch>perl opatch.pl version

Oracle Interim Patch Installer version 1.0.0.0.57
Copyright (c) 2007 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

OPatch Version: 1.0.0.0.57

instType uknown

2007-12-31T11:49:00.000Z

Started getting this error from a batch job which was using oradim to do a shutdown.

instType uknown: db not started; use RDBMS as default

I think this has just been occurring since we put a 10G install on the same server as the existing 9i and 8i databases.

Anyhow it was fixed by explicitly setting ORACLE_HOME and prefixing oradim with %ORACLE_HOME%\bin\

Determining which CPUs have been applied

2007-12-07T14:44:00.000Z

The question of how to determine which CPUs have been applied to a database came up at a couple of presentations at the UKOUG, including mine.

A quick and dirty way (which I didn't think of at the time) would be to look in the CPU directory:

c:\> dir t:\oracle\ora92\cpu

11/06/2007 18:12 .
11/06/2007 18:12 ..
02/05/2006 17:30 CPUApr2006
30/04/2007 19:21 CPUApr2007
28/03/2006 18:51 CPUJan2006
......

Then to check which databases the catcpu script was run against, you can look in the directories themselves - the spoolfiles have the name of the database in the title:

c:\> dir t:\oracle\ora92\cpu\CPUAPr2007

30/04/2007 19:21 .
30/04/2007 19:21 ..
30/04/2007 18:19 4,918 APPLY_SID1_30Apr2007_19_19_49.log
30/04/2007 18:26 4,918 APPLY_SID2_30Apr2007_19_20_11.log

TNS-03505: Failed to resolve name - OID glitch

2007-09-24T14:03:00.000+01:00

We're in the process of migrating from Oracle Names to LDAP, and found the following fairly trivial gotcha.

If you have a space between the word LDAP and the comma in the DIRECTORY_PATH, as below, then it won't work

NAMES.DIRECTORY_PATH= ( LDAP , ONAMES, TNSNAMES)

Oracle options - update from Niall

2007-09-18T13:02:00.000+01:00

Subsequent to the previous post, I got a note from Niall, saying that

at least in 10g you can query dba_feature_usage_statistics as follows

SELECT NAME,VERSION,DETECTED_USAGES
FROM DBA_FEATURE_USAGE_STATISTICS
WHERE DETECTED_USAGES > 0;

Thanks, Niall!

Oracle lifetime support

2007-09-12T15:22:00.000+01:00

I went to the UK Oracle User Group Unix SIG meeting yesterday. There was a lot of good stuff, as usual.

Something I had missed entirely (I have no idea how, to be honest) was that the 'Oracle Lifetime Support' policy has been published.

The significant changes from my POV is that 5 years after the General Availability date of Oracle software, CPUs and other software updates will have to be paid for on top of the existing support payments - at least that's my understanding.

Details are here, and there's also a handy table showing the different support levels here

Getting Windows server and Oracle options details from the command line

2007-09-12T15:02:00.000+01:00

I've been asked to retrieve some details on:
- the options in use on our databases and
- the servers they run on.

Oracle feature usage can be retrieved as follows:

col parameter format a35
col value format a10
select name, banner from v$version, v$database;
select name,parameter, value from v$option, v$database
where parameter in (
'Partitioning',
'Real Application Clusters',
'Spatial',
'OLAP Window Functions',
'Oracle Label Security',
'OLAP',
'Oracle Data Mining'
)
order by 2,1;

Both of the commands below get server information from the commandline.

psinfo is from sysinternals and runs as follows:

psinfo \\servername

This gives uptime, service packs, CPUs and memory installed

systeminfo seems to be part of Windows XP but seems to work running it against Windows 2000 servers. It runs as follows:

systeminfo /S servername /U username /P password

This gives a lot more than psinfo - it lists all the Windows patches, and a bit more of the basic detail.

I learnt about systeminfo from this online version of Robbie Allen's Windows Server Cookbook, which looks really good.

Most used tiddlywiki commands for MainMenu

2007-05-10T10:42:00.000+01:00

I put this into my MainMenu tiddler to give me a quick reference for my most used tiddlywiki formatting commands.

!Cheat sheet
|''Bold''|{{{''text''}}}|
|__Uline__|{{{__text__}}}|
|//Italic//|{{{//text//}}}|
|Bullets|{{{*text}}}|
|No.s|{{{#text}}}|
|Heads|{{{!text}}}|
|Table|{{{|t|t|}}}|
|Quote|{{{<<<>>>}}}|
|{{{Mono}}}|{{{{{{text}}}}}}|
|[[Tid]]|{{{[[Text]]}}}|
|[[Help|http://www.blogjones.com/TiddlyWikiTutorial.html#EasyToEdit]]|{{{[[t|url]]}}}|