Welcome to your test blog! You can create new posts here to test theme and design changes.

Trying to change the sql server agent to run as a non-admin user, I couldn’t get Sql Server Agent to work without giving it administrator privileges. I got:

---------------------------
WMI Provider Error
---------------------------
Access is denied. [0x80070005]
---------------------------
OK
---------------------------

You have to grant two privileges - both the Agent User and the MSSQL user.

• SQLServerSQLAgentUser$bigserver$inst01
• SQLServerMSSQLUser$bigserver$inst01

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:

/<tr>/{printf("\n")}
/<td/{printf("%s~", $2)}

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

awk95 -F">" -f risk_matrix.awk risk_matrix_raw.txt |^ sed -e "s/<\/tr//g" -e "s/<\/td//g" \> risk_matrix.txt

• 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.

Note (April 2020): for some reason in the original version of this post I had the code in .jpg files. Mea culpa. I’ve typed the code in, but not been able to test as yet, so, just for reference here are the original .jpgs

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 

I’m planning to type up a couple of DOS and Windows equivalents of some of my most used unix commands.

These are very much limited, partial equivalents. In this particular case, I’m not replicating all the functionality of the unix ‘find’, but 9 times out of 10 when I use find, I use it like this:

find . -name 'sqlnet.ora' -print

You could of course use the Windows Explorer search. However, this doesn’t help if you want to, say, save the results into a text file or process them in a batch script. And, also, I prefer the commandline!

Anyhow, my quick and dirty DOS equivalent is:

dir /B /S sqlnet.ora

Some explanation, if its needed:

• The /S says do a recursive listing
• The /B is actually do a ‘bare’ listing with no size or date info. In this context though its use is that it prefixes the directory onto the output for each file

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

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….

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

BJS is the scheduling tool we use on our Windows servers. Its produced by Camellia Software:

• Camellia website
  
  ….and it’s very good.
  
  I had a problem this morning though.
  
  

BJS jobs would go into ‘Awaiting Exec’ state for up to 45 minutes before running.

The Log file would show:


* BATCH JOB SERVER
* A Camellia Software Product - www.CamelliaSoftware.com
* Copyright 1994-1998
* Version 2.1 - A Build 529
* ***** J O B L O G

JOB NO. 0737


Job Name: BIG Backup PWHC Open
Username: xxx
Submitted by:

Server: bigserver
Source File: \bigserverD$BJSbig_backup_pwhc_open.bat
Output Directory: D:BJSBJSWORK
*
***

>
> 11/04/05 08:00:29 PM Job scheduled for execution
>
> 11/04/05 08:45:00 PM Job started execution
>

The reason for this was/is that there is a setting under Server-Administer-Change Service Parameters called ‘Maximum Batch Job Processes’. This limits the number of jobs you can run at any one time. It should be set to twice the number of jobs you might want to have running conccurrently.

There is also a setting against Job Class which does something similar - under Server-Administer-Job Class Maintenance

A case of RTFM….

…but although there is a Patron Saint of Nursing, there isn’t yet a Patron Saint of Talking Like a Pirate