Some of our batch jobs write log information to standard output. Others create dated log files.

Our scheduling tool kind of works best if log information goes to standard output - it captures it then allows you to inspect it from console.

I wrote the following script to spool out the latest version of a given dated log file.

I called it show_log.bat So if you run it as follows:

cmd /k show_log d:\logfiles\my_batch_job_*.log

…it displays the latest my_batch_job_*.log that it can find, where * is any string - in our case typically a date of some sort. So I put a call to show_log at the end of each batch job which creates a dated log file and I can see it from the scheduler console too.

If you have a directory that is called my_batch_job_*.log it wouldn’t work so well - I had to use /S to get dir to return the full pathname for the file.

echo off

rem This routine accepts a file specification (in the
rem format you use for rem dir) as a paremeter, then
rem displays the last updated file matching that
rem specification to stdout
rem
rem It replaces log2stdout.pl

set DIR_STRING="%1"
echo Dir String %DIR_STRING%

rem
rem These couple of lines work out what the last
rem updated file is. The dir command orders by
rem the updated date. The /S means do a recursive
rem search. This is there to ensure that the dir
rem returns the full path name rather than to
rem actually search a directory structure.
rem
rem We would get unexpected results if the
rem dir_string does actually match
rem a directory.

set LAST_FILE=
for /f %%I in ('dir /B /N /S /OD %DIR_STRING% ') do set LAST_FILE=%%I
echo Last file is %LAST_FILE%

if "%LAST_FILE%"=="" exit

rem
rem Retreiving the date stamp of the file for display

for /f "tokens=1,2" %%I in ('dir %LAST_FILE% ^| findstr /V "Volume Directory ( ^$"')  do set DATE_CREATED=%%I %%J
echo Date created is %DATE_CREATED%

echo Going to output this file:
dir %LAST_FILE% | findstr /V "Volume Directory ( ^$"
echo -----

rem
rem Echoing line by line rather than simply using
rem 'type' because I wanted to prefix each line
rem with the files datestamp. The reason for this is
rem because I don't want there to be any confusion
rem as to what file is being looked at, either
rem because this script has failed or because
rem the file you're expecting hasn't actually
rem been created

for /F  "delims=" %%I in (%LAST_FILE%) do echo %DATE_CREATED% Log: %%I

exit

We’re currently migrating our backups from TSM to netbackup.

I know TSM quite well, but I’m learning netbackup.

The first lesson was that its case-sensitive on Windows:

bplist  -R d:\backup
   EXIT STATUS 227: no entity was found

fixed by

bplist  -R D:\backup
   D:\backup
   D:\backup\MTP1
   D:\backup\MTP1\DB_MTP1_5_1_605982503_05I1T3P7_1_1
   D:\backup 

I scripted our backup jobs to send an email to me if there’s a problem. This works well enough, usually.

However this week the backups started failing because of a dodgy disk, but it seemed I wasn’t getting the alerts. It wasn’t until one of the sysadmins mentioned the problem with the disk that I found out there was a problem. I made a mental note to check out why I hadn’t been alerted but, so far, hadn’t got any further than that.

As it turns out the alerts weren’t failing, but were being created with no ‘Sent’ date, and for some reason I must have chosen to display the ‘Sent’ time rather than the ‘Received’ time in Outlook, and to sort by it.

So, the email alerts were coming in to the bottom of my Inbox where in the normal course of events I’d never see them. Doh!

I need to implement some sort of positive check on the success of the backups - in previous jobs I’ve set up a directory listing of the directory with the backup files in and incorporated that in a daily check. I’ll probably do the same again.

I scripted our backup jobs to send an email to me if there’s a problem. This works well enough, usually.

However this week the backups started failing because of a dodgy disk, but it seemed I wasn’t getting the alerts. It wasn’t until one of the sysadmins mentioned the problem with the disk that I found out there was a problem. I made a mental note to check out why I hadn’t been alerted but, so far, hadn’t got any further than that.

As it turns out the alerts weren’t failing, but were being created with no ‘Sent’ date, and for some reason I must have chosen to display the ‘Sent’ time rather than the ‘Received’ time in Outlook, and to sort by it.

So, the email alerts were coming in to the bottom of my Inbox where in the normal course of events I’d never see them. Doh!

I need to implement some sort of positive check on the success of the backups - in previous jobs I’ve set up a directory listing of the directory with the backup files in and incorporated that in a daily check. I’ll probably do the same again.

I’m mucking about with VSS from the command line.

To do this you need to be in the VSS directory, which looks like this, for me:

     C:\Program Files\Microsoft Visual Studio\VSS\win32>

This doesn’t leave much room for typing before the line starts wrapping.

This command:

    prompt $d$g

Sets the prompt to:

    31/08/2006>

Which is a bit better….

I’m mucking about with VSS from the command line.

To do this you need to be in the VSS directory, which looks like this, for me:

     C:\Program Files\Microsoft Visual Studio\VSS\win32>

This doesn’t leave much room for typing before the line starts wrapping.

This command:

    prompt $d$g

Sets the prompt to:

    31/08/2006>

Which is a bit better….

This is relatively handy, particularly when running the same thing against multiple databases. I’ve saved this in a script called spool.sql, so I can type @spool to start spooling to a file which has the database name in the middle

undef prefix
column name new_value dbname
select name from v$database;
spool &&prefix._&dbname
prompt Spooling:
host dir /b &&prefix._&dbname..lst 

I didn’t know you could do this. Its quite handy!

SQL> column name new_value dbname
SQL> select name from v$database;
XXX1
SQL> spool test_&dbname
SQL> select sysdate from dual;
03-JUL-06
SQL> spool off
SQL> host dir /od *.lst
03/07/2006  13:16                61 test_XXX1.LST
SQL> 

Create a link to the remote database then supply the name of the link as &1

set pagesize 1000
col name format a28
col local format a20
col remote format a20
select local.name, local.value local,  
                   remote.value remote, 
       substr(local.isdefault, 1,1) || '/' ||
       substr(remote.isdefault,1,1)
from v$parameter local,
     v$parameter@&1 remote
where local.name=remote.name
and   lower(local.value)lower(remote.value)
order by local.name
/ 

Here is some stuff I learnt yesterday evening’s SQL Server 2005 New Features session at Microsoft in Reading

Enterprise Manager

• Enterprise Manager has been re-hashed, and re-named Management Studio.
• It didn’t seem radically different in terms of what it does.
• It will work with previous versions
• Unlike Oracle’s offering, it isn’t browser based

Osql - Now called sqlcmd

• It has definitely been much improved.
• There are a lot of command line switches.
• You can now pass in parameters from the command line. Like Oracle
• I’d be interested to see it would work with sql 2000. And sybase

Security

• The bloke said that S2K had been ’re-architected' - I couldn’t really see this at all
• The concept of schemas (or schema owners) has been separated out from that of users. I think in practice this just means you have a ‘special’ type of user for owning the objects.
• There is supposed to be better levels of encryption.
• SAC - see below

Surface Area Configuration

• Embodies concept of trying to reduce the ‘surface area’ of the database i.e. the amount of stuff you’ve got installed
• There’s a specific GUI to control this, and (interestingly) a very full-featured command line option

Other

• There’s a database mail tool. I couldn’t see much advantage to this other than not having to install the Outlook client
• There’s a concept of database snapshots. You ‘take’ a snapshot of all the data, then can optionally go back to it in an Oracle-flashback way
• Due to changes in the licensing laws, they no longer give out free beer

Here is some stuff I learnt yesterday evening’s SQL Server 2005 New Features session at Microsoft in Reading

Enterprise Manager

• Enterprise Manager has been re-hashed, and re-named Management Studio.
• It didn’t seem radically different in terms of what it does.
• It will work with previous versions
• Unlike Oracle’s offering, it isn’t browser based

Osql - Now called sqlcmd

• It has definitely been much improved.
• There are a lot of command line switches.
• You can now pass in parameters from the command line. Like Oracle
• I’d be interested to see it would work with sql 2000. And sybase

Security

• The bloke said that S2K had been ’re-architected' - I couldn’t really see this at all
• The concept of schemas (or schema owners) has been separated out from that of users. I think in practice this just means you have a ‘special’ type of user for owning the objects.
• There is supposed to be better levels of encryption.
• SAC - see below

Surface Area Configuration

• Embodies concept of trying to reduce the ‘surface area’ of the database i.e. the amount of stuff you’ve got installed
• There’s a specific GUI to control this, and (interestingly) a very full-featured command line option

Other

• There’s a database mail tool. I couldn’t see much advantage to this other than not having to install the Outlook client
• There’s a concept of database snapshots. You ‘take’ a snapshot of all the data, then can optionally go back to it in an Oracle-flashback way
• Due to changes in the licensing laws, they no longer give out free beer

opatch failed with the above error.

Again its really a problem with the wetware (i.e. me), but worth typing up as its fairly obscure.

The problem seems to have been that I was logged in as a different user on the server which holds our staging area when I copied the software across. I mapped the drive on the the target server as the Oracle user, but it still seems to have inherited the wrong ownership at some level. I didn’t have much time to investigate so I can’t be 100% sure.

opatch failed with the above error.

Again its really a problem with the wetware (i.e. me), but worth typing up as its fairly obscure.

The problem seems to have been that I was logged in as a different user on the server which holds our staging area when I copied the software across. I mapped the drive on the the target server as the Oracle user, but it still seems to have inherited the wrong ownership at some level. I didn’t have much time to investigate so I can’t be 100% sure.

This is a fairly foolish pitfall that I stumbled into last week.

One of the recommendations from our external security audit was that we rename the listener from the default ‘LISTENER’ to something else.

I’m a little bit dubious as to how much renaming the listener increases ‘security’ since the listener name is visible in the Services panel or through net start. However, from a security POV it can’t do any harm, and it’s not a lot of effort, so we went ahead with it.

Semi-interestingly, as far as I can see there’s no Oracle recommendation to rename the listener.

This went fine for all but one of our servers.

The problem arose for one particular application which had some of its own services dependent on the Oracle listener. When the listener was renamed from ‘LISTENER’ to something else, the application didn’t come up.

Service dependencies are viewable under:

• Services Panel
• Right click the Listener Service
• Select the Dependencies Tab
• This shows services which are dependent on the Listener, and any services on which the listener depends.

I got this error running an analyze from within a package called by perl:

[Oracle][ODBC][Ora]ORA-02074: cannot ANALYZE TABLE in a distributed transaction

It’s basically the same issue as descibed in the link - you have to turn off the Microsoft Distributed Transaction Server, as follows:

• fire up the Microsoft ODBC Administrator
• Select the System DSN Tab
• Select the DSN that it’s using
• Click Configure
• Select the Workarounds Tab
• click Disable Microsoft Transaction Server.

You have to go into Microsoft ODBC Administrator

I’ve been having problems with opatch.

todo - fix the link Setting ACTIVE_STATE_PERL=TRUE cured at least one of these (although I’m not sure that I didn’t patch at least once without the setting, because there was another version of perl - mksnt - on the server) as per the previous post

Its sometimes failed, then worked without me being entirely sure what I changed in the meantime.

Last night I sussed that at least some of this is caused by having a space following the ACTIVE_STATE_PERL=TRUE

This is a stupid, but unlikely mistake - certainly a problem with the wetware, but it’s not easy to spot and it cost me a lot of time (and grief).

The following test case does the following:

1. Sets the other variables
2. Sets ACTIVE_STATE_PERL=TRUE with a trailing space on the end of true
3. echos the variable back to display the space
4. runs opatch -lsinventory and it fails
5. Re-sets ACTIVE_STATE_PERL=TRUE with the trailing space removed
6. runs opatch -lsinventory and it succeeds

-some other variables. Allegedly the case of the D: is significant
set ORACLE_HOME=D:\ORACLE\ORA92
set PERL5LIB=D:\Perl\lib

-- typing TRUE then a space before the carriage return
set ACTIVE_STATE_PERL=TRUE

-- show there's a space at the end
echo %ACTIVE_STATE_PERL%x
TRUE x

-- run opatch
D:\oracle\ora92\OPatch>d:\perl\bin\perl.exe opatch.pl lsinventory -all

-- it fails. Its possibly another discussion, but you wouldn't necessarily know
-- this was failure if you were doing an apply and you hadnt seem it before, but
-- anyway...

Oracle Interim Patch Installer version 1.0.0.0.53
Copyright (c) 2005 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

Oracle Home = D:\oracle\ora92
Location of Oracle Universal Installer components = D:\oracle\ora92\oui
Location of OraInstaller.jar = "D:\oracle\ora92\oui\jlib"
Oracle Universal Installer shared library = D:\oracle\ora92\oui\lib\win32\oraInstaller.dll
Location of Oracle Inventory Pointer = N/A
Location of Oracle Inventory = D:\oracle\ora92\inventory
Path to Java = "D:\oracle\ora92\jre1.4.2\bin\java.exe"
Log file = D:\oracle\ora92\.patch_storage\\*.log

-- reset without the spurious space
D:\oracle\ora92\OPatch>set ACTIVE_STATE_PERL=TRUE

-- prove there's no space
D:\oracle\ora92\OPatch>echo %ACTIVE_STATE_PERL%x
TRUEx

-- and it works
D:\oracle\ora92\OPatch>d:\perl\bin\perl.exe opatch.pl lsinventory -all

Oracle Interim Patch Installer version 1.0.0.0.53
Copyright (c) 2005 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

Oracle Home = D:\oracle\ora92
Location of Oracle Universal Installer components = D:\oracle\ora92\oui
Location of OraInstaller.jar = "D:\oracle\ora92\oui\jlib"
Oracle Universal Installer shared library = D:\oracle\ora92\oui\lib\win32\oraInstaller.dll
Location of Oracle Inventory Pointer = N/A
Location of Oracle Inventory = D:\oracle\ora92\inventory
Path to Java = "D:\oracle\ora92\jre1.4.2\bin\java.exe"
Log file = D:\oracle\ora92\.patch_storage\\*.log

Remove extra quote surrounding system commands on Windows...
Creating log file "D:\oracle\ora92.patch_storage\LsInventory__01-25-2006_10-35-14.log"

Remove extra quote surrounding system commands on Windows...
ORACLE HOME LOCATION
----------- --------

OraHome8160 D:\Oracle\Ora\8160
OraHome92 d:\oracle\ora92


OPatch succeeded.

Grrrr!

Keyboard macros in gvim are much easier than using the map command. Basically you do:

qx - to start recording a macro called ‘x’ q - to stop recording the macro @x - to execute it

So, Raptor seems to need 10G client.

I knew there was not going to be a 10G version of Names, as it was going to be discontinued in favour of OID.

What I didn’t realize was that 10G wouldn’t work at all with Names.

Sadly all our clients and servers use Names. So until I install OID, switch all of those clients and servers I’m going to have to create and maintain a separate directory of all our Names to run Raptor.

ShowDoc

So, Raptor seems to need 10G client.

I knew there was not going to be a 10G version of Names, as it was going to be discontinued in favour of OID.

What I didn’t realize was that 10G wouldn’t work at all with Names.

Sadly all our clients and servers use Names. So until I install OID, switch all of those clients and servers I’m going to have to create and maintain a separate directory of all our Names to run Raptor.

ShowDoc

I think this is because we had a problem with our corporate mail server, because:

a) I have a vague memory (but sadly no notes!) that it happened before, and b) in this case we had the problem when the mail server was knackered, it’s now fixed and the problem has gone away.

Make of this what you will

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "DBUSER.PK_ESERVICE_LOAD", line 496
ORA-20000: Unable to send e-mail message from pl/sql : ORA-29260: network error: TNS:operation timed out 

Having a first look at Raptor, I got an error message to the effect that it couldn’t find ocijdbc10.

This was because I didn’t have Oracle 10G client installed. I installed it and it now connects OK.

I always used to find it a nuisance that applying the lower level Oracle patches was such a manual activity - you got a bunch of exe’s, dll’s etc and have to manually copy them into place. Typically you would create a script to do it, and wonder why Oracle hadn’t done so.

Recently patches though have been applicable with opatch - a perl script to do the donkey work.

However, admittedly mainly through my own ability to rtfm (or maybe to rt the whole fm, and all the surrounding fm), I missed these two crucial variables that you sometimes need to set.

set PERL5LIB=C:\Perl\lib

set ACTIVE_STATE_PERL=TRUE    -- must be upper case 'TRUE'

I didn’t need to do this in development - my guess is that it picked up another perl install, but in acceptance test I got the following:

d:\perl\bin\perl d:oracleora92opatchopatch.pl lsinventory


Oracle Interim Patch Installer version 1.0.0.0.53

Copyright (c) 2005 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


Oracle Home = d:oracleora92

Location of Oracle Universal Installer components = D:oracleora92oui

Location of OraInstaller.jar = "D:oracleora92ouijlib"

Oracle Universal Installer shared library =

D:\oracle\ora92\oui\lib\win32\oraInstaller.dll

Location of Oracle Inventory Pointer = N/A

Location of Oracle Inventory = D:\oracle\ora92\inventory

Path to Java = "D:\oracle\ora92\jre1.4.2\bin\java.exe"

Log file = d:/oracle/ora92/.patch_storage//*.log

…and then nothing, and no log files.

Here are instructions for disabling wsh. This is particularly used by DBAs to run RDA (Remote Diagnostic Agent) to gather Oracle and Oracle-related diagnostics.

• Run regedit

• Go to HKEY_LOCAL_MACHINE\Software\Microsoft\Windows Script Host\Settings\Enabled (the Microsoft doc says to create it if its not there)

• Set the value to 0

That’s it. You get the message:

Windows Script Host access is disabled on this machine. Contact your administrator for details.

If you try to use it.

If it needs to be re-enabled, then you set the value to 1 - we often need to use it on the database servers to get diags for Oracle, but we should disable it each time after use.

The MS reference is:

Ms ref

Here are instructions for disabling wsh. This is particularly used by DBAs to run RDA (Remote Diagnostic Agent) to gather Oracle and Oracle-related diagnostics.

• Run regedit

• Go to HKEY_LOCAL_MACHINE\Software\Microsoft\Windows Script Host\Settings\Enabled (the Microsoft doc says to create it if its not there)

• Set the value to 0

That’s it. You get the message:

Windows Script Host access is disabled on this machine. Contact your administrator for details.

If you try to use it.

If it needs to be re-enabled, then you set the value to 1 - we often need to use it on the database servers to get diags for Oracle, but we should disable it each time after use.

The MS reference is:

Ms ref

I got this error trying to specify the tablespace for my text indexes. I was moving them from a default tablespace because they are taking up quite a lot of space.

Looking up oerr, you get:

Cause:
The user specified an invalid option like ASC, DESC, SORT or a parallel clause, partitioning clause or physical attributes clause.
Action:
Choose one of the valid clauses for creation of domain indexes.

You can' specify a tablespace as such in the create index statement. Something I would have found out had I been arsed to rtfm in the first place.

Anyway, as per Oracle note 150453.1 , you have to create a preference for the storage, and then use the parameter syntax when you create the index, as follows:

begin 
Ctx_Ddl.Drop_Preference ( 'eservice_text_storage' ); 
end; 
/
begin 
Ctx_Ddl.Create_Preference('eservice_text_storage', 'BASIC_STORAGE'); 
ctx_ddl.set_attribute('eservice_text_storage',
'I_TABLE_CLAUSE', 
'tablespace text_indexes'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'K_TABLE_CLAUSE', 
'tablespace text_indexes'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'R_TABLE_CLAUSE', 
'tablespace text_indexes storage (initial 1M) lob (data) store as (cache)'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'N_TABLE_CLAUSE', 
'tablespace text_indexes'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'I_INDEX_CLAUSE', 
'tablespace text_indexes compress 2'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'P_TABLE_CLAUSE', 
'tablespace text_indexes'); 
end; 
/ 

drop index sc_sol_textidx_desc;
create index sc_sol_textidx_desc on owner.sc_solutions(description)
indextype is ctxsys.Context
parameters ('storage eservice_text_storage'); 
;

drop index sc_sol_textidx_dref;
create index sc_sol_textidx_dref on sc_solutions (document_ref)
indextype is ctxsys.context 
parameters ('storage eservice_text_storage LEXER ctxsys.mylex');