OraNA :: Oracle News Aggregator

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

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
• Have a function with the same name as a script
• Don't name the parameter correctly
• Not (re-) sourcing the function! :)

Leave the '.' off of a label

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

SYNOPSIS
Lists services  (more...)

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


Happy Christmas!

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

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'

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 (more...)

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

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:

• John Glen, Conservative candidate
• Nick Radford, Liberal Democrat candidate
• Tom Gann, Labour candidate 

Back to techie stuff with the next post!

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() (more...)

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 (more...)

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 (more...)

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 (more...)

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 (more...)

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 (more...)

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>

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.

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.

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 (more...)

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\

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