Sean's Blog

Friday, July 3, 2015

The plug-in configuration for the oracle.sysman.oh monitoring plug-in may have failed

If you're having trouble installing the OEMGC Agent on Windows, and receiving that error despite having the file 12.1.0.3.0_PluginsOneoffs_233.zip present in the agent installation folder, then create the following batch file and run it:

runme.bat:

set PERL5LIB=
set ORACLE_HOME=
set path=%CD%;%WINDIR%;%WINDIR%\SYSTEM32
set ORATMP=c:\TMP
MKDIR %ORATMP%
SET TMP=%ORATMP%
SET TEMP=%ORATMP%
DEL %ORATMP%\*.* /Q
sc delete Oracleagent12c1Agent

call agentDeploy.bat AGENT_BASE_DIR=c:\oemgc\agent12c RESPONSE_FILE=%CD%\agent.rsp
pause

Comments: This batch file is attempting to pre-empt a whole bunch of pitfalls.Unset PERL5LIB, unset ORACLE_HOME, clean up the PATH, create a clean TMP directory, delete any service from a previous installation attempt.

Wednesday, July 9, 2014

Bugzilla on Windows 2008 & Oracle 11g

Having found absolutely NO correct documentation on how to install Bugzilla on Windows Server 2008 64-bit and Oracle 11g, I thought I'd publish the results of many days of research. As with many open source projects, it's free only if your time is worth nothing, and it's held together by spit.

I'm providing the condensed version of steps to implement.

Software Prerequisites / Assumptions:

1. Oracle 11.2.0.4 64-bit for Windows
2. Apache installer, httpd-2.2.25-win32-x86-openssl-0.9.8y.msi
3. Perl 32-bit. ActivePerl-5.16.3.1604-MSWin32-x86-298023.msi

Yes, on Windows 2008 64-bit you NEED 32-bit Perl from Activestate. Strawberry Perl doesn't work with Oracle (there's no Oracle DBD or so).

4. Bugzilla. bugzilla-4.4.4.tar.gz
5. Oracle Instantclient, instantclient-basic-nt-11.2.0.4.0.zip

Steps:

1. Install Oracle 11g, create a database called bugs.
2. Login as SYS, and create a user called bugs, such as:

create user bugs identified by bugs default tablespace users;
grant connect,resource,unlimited tablespace to bugs;

3. Install Perl. I'm assuming you accept the defaults and the location installed is C:\Perl.
4. Merge this REG file into the registry:

Windows Registry Editor Version 5.00
[HKEY_CLASSES_ROOT\.cgi\Shell\ExecCGI\Command]
@="C:\\Perl\\bin\\perl.exe -wT"

[HKEY_CLASSES_ROOT\.pl\Shell\ExecCGI\Command]
@="C:\\Perl\\bin\\perl.exe -wT"

Note: The command parameter is wT

5. Install Apache. Where you install it is up to you, but I would recommend a location without spaces e.g. C:\Apache2

6. Extract the contents of bugzilla-4.4.4.tar.gz to C:\bugzilla, such that C:\bugzilla\checksetup.pl exists.

7. From instantclient-basic-nt-11.2.0.4.0.zip, copy oci.dll and oraociei11.dll to C:\bugzilla

8. In C:\Bugzilla, create the file install-ppms.bat with the following contents:

call ppm install TimeDate
call ppm install DateTime
call ppm install DateTime-TimeZone
call ppm install Template-Toolkit
call ppm install Email-Send
call ppm install Email-MIME
call ppm install Math-Random-ISAAC
call ppm install Chart
call ppm install Template-GD
call ppm install MIME-tools
call ppm install XML-Twig
call ppm install PatchReader
call ppm install perl-ldap
call ppm install Authen-SASL
call ppm install Net-SMTP-SSL
call ppm install RadiusPerl
call ppm install SOAP-Lite
call ppm install XMLRPC-Lite
call ppm install JSON-RPC
call ppm install JSON-XS
call ppm install Test-Taint
call ppm install HTML-Scrubber
call ppm install Encode-Detect
call ppm install Email-Reply
call ppm install HTML-FormatText-WithLinks
call ppm install TheSchwartz
call ppm install Daemon-Generic
call ppm install mod_perl
call ppm install Apache-SizeLimit
call ppm install File-MimeInfo
call ppm install IO-stringy

9. Configure Apache by following these steps:

Stop the Apache service.

Configure Port and DocumentRoot

Edit C:\Apache2\conf\httpd.conf with notepad.

To change the port that Apache runs on (listens on, or binds to), edit the Listen option.

#
# Listen: Allows you to bind Apache to specific IP addresses and/or
# ports, instead of the default. See also the <VirtualHost>
# directive.
#
# Change this to Listen on specific IP addresses as shown below to 
# prevent Apache from glomming onto all bound IP addresses (0.0.0.0)
#
#Listen 12.34.56.78:80
Listen 80

Change the DocumentRoot setting to point to C:/Bugzilla. Note there are two locations in httpd.conf that need to be updated. Note you need to use / instead of \ as a path separator.

#
# DocumentRoot: The directory out of which you will serve your
# documents. By default, all requests are taken from this directory, but
# symbolic links and aliases may be used to point to other locations.
#

DocumentRoot "C:/Bugzilla"

#
# Each directory to which Apache has access can be configured with respect
# to which services and features are allowed and/or disabled in that
# directory (and its subdirectories). 
#
# First, we configure the "default" to be a very restrictive set of 
# features.  
#
<Directory />
   Options FollowSymLinks
   AllowOverride None
</Directory>

#
# Note that from this point forward you must specifically allow
# particular features to be enabled - so if something's not working as
# you might expect, make sure that you have specifically enabled it
# below.
#

#
# This should be changed to whatever you set DocumentRoot to.
#
<Directory "C:/Bugzilla">

Configure CGI

To enable CGI support in Apache, you need to enable the CGI handler, by uncommenting the AddHandler cgi-script .cgi line.

#
# AddHandler allows you to map certain file extensions to "handlers":
# actions unrelated to filetype. These can be either built into the server
# or added with the Action directive (see below)
#
# To use CGI scripts outside of ScriptAliased directories:
# (You will also need to add "ExecCGI" to the "Options" directive.)
#
AddHandler cgi-script .cgi

And allow .cgi scripts in the Bugzilla directory by adding the ExecCGI option. We also need to allow Bugzilla's .htaccess file to restrict access to sensitive documents by allowing it to override the defaults. This involves changing AllowOverride None to AllowOverride All.

Apache also needs to know to use Perl to execute .cgi files, via the ScriptInterpreterSource directive.

#
# This should be changed to whatever you set DocumentRoot to.
#
<Directory "C:/Bugzilla">

#
# Possible values for the Options directive are "None", "All",
# or any combination of:
#   Indexes Includes FollowSymLinks SymLinksifOwnerMatch ExecCGI MultiViews
#
# Note that "MultiViews" must be named *explicitly* --- "Options All"
# doesn't give it to you.
#
# The Options directive is both complicated and important.  Please see
# http://httpd.apache.org/docs-2.0/mod/core.html#options
# for more information.
#
    Options Indexes FollowSymLinks ExecCGI

#
# AllowOverride controls what directives may be placed in .htaccess files.
# It can be "All", "None", or any combination of the keywords:
#   Options FileInfo AuthConfig Limit
#
    AllowOverride All

#
# Controls who can get stuff from this server.
#
   Order allow,deny
   Allow from all

#
# Tell Apache to use Perl to execute .cgi
#
    ScriptInterpreterSource Registry-Strict

</Directory>

You also should add index.cgi to the DirectoryIndex list.

#
# DirectoryIndex: sets the file that Apache will serve if a directory
# is requested.
#
# The index.html.var file (a type-map) is used to deliver content-
# negotiated documents.  The MultiViews Option can be used for the 
# same purpose, but it is much slower.
#
DirectoryIndex index.html index.html.var index.cgi

Disable Logging

Unless you want to keep statistics on how many hits your Bugzilla install is getting, it's a good idea to disable logging by commenting out the CustomLog directive.

#
# The location and format of the access logfile (Common Logfile Format).
# If you do not define any access logfiles within a <VirtualHost>
# container, they will be logged here.  Contrariwise, if you *do*
# define per-<VirtualHost> access logfiles, transactions will be
# logged therein and *not* in this file.
#
#CustomLog logs/access.log common

Restart Apache

Finally, restart the Apache service.

9. Execute install-ppms.bat:

cd /d c:\bugzilla

C:\bugzilla>install-ppms

This takes a while to run while all the required modules are installed.

10. execute checksetup.pl

C:\bugzilla>perl checksetup.pl

If this succeeds, it should generate a file called localconfig.

11. Configure localconfig

C:\bugzilla>notepad localconfig

Change the following lines:

$db_driver = 'oracle';

$db_host = 'localhost';

$db_name = 'bugs';

$db_user = 'bugs';

$db_pass = 'bugs';

12. Re-run checksetup.pl

C:\bugzilla>perl checksetup.pl

If you're asked for an smtp server, enter 'localhost'; this can be changed later.

When this script completes, that's it, should be sorted.

You can test your bugzilla installation by using:

C:\bugzilla>perl testserver.pl http://localhost

TEST-OK Got padlock picture.

TEST-OK Webserver is executing CGIs via mod_cgi.

TEST-OK Webserver is preventing fetch of http://localhost/localconfig.

TEST-OK GD library generated a good PNG image.

defined(@array) is deprecated at C:/Perl/site/lib/Chart/Base.pm line 181.

(Maybe you should just omit the defined()?)

defined(@array) is deprecated at C:/Perl/site/lib/Chart/Base.pm line 233.

(Maybe you should just omit the defined()?)

TEST-OK Chart library generated a good PNG image.

TEST-FAILED Template::Plugin::GD is not installed.

Notice how the last test failed, but Bugzilla still works. The missing plugin must be needed for an optional feature.

Friday, July 26, 2013

TCP.VALIDNODE_CHECKING

Well, this is embarassing :)

I unwittingly learnt how these parameters work:

In my SQLNET.ORA, I had

TCP.VALIDNODE_CHECKING=YES
TCP.EXCLUDED_NODES=SQLSERVER1

Looks like this file gets read by the LISTENER and NOT the database! Argh!

When I restarted the listener, the customer's SQL Server host was blocked from accessing any Oracle databases on my db host. Had to troubleshoot using listener tracing, and figured out that the listener was the culprit.

So, I simply removed those lines and restarted the listener - problem gone away.

Note that the error message on the client-side is misleading, since it says that the service is not found:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Off course, this is totally misleading.

Wednesday, July 24, 2013

SQL *Net more data to client

I was performance tuning a customer's production database, so naturally I ran AWR Reports.

Whenever I ran AWR reports I kept getting the event "SQL *Net more data to client" as the second highest wait event (after CPU time). When I googled it, I came across this post on AskTom:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:951335700013

In it, SDU (Session Data Unit) and MTU (Maximum Transmission Unit) are mentioned. What's the relationship between SDU and MTU ?

If you look through the articles on Google, it would seem that they've all just plagiarised each other, with the wrong information. They all incorrectly say SDU should be a multiple of MTU. This lone post on the same AskTom article has the correct info:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:951335700013#67529392482665

The author says that SDU should be a multiple of MSS (Maximum Segment Size of the network protocol in use), and NOT the MTU.

I had to search for a more authoritative source.

I looked towards Oracle Support as the holy grail of Oracle knowledge. Here I found Oracle Support document ID 274483.1 The relationship between MTU (Maximum Transmission Unit) and SDU (Session Data Unit) . It says

"The principle is that the SDU value be a multiple of the MTU."

Now, this may the source of all the wrong information on the internet.

This document attempts to summarize another doc, SQL Net Packet Sizes (SDU & TDU Parameters) Doc ID 44694.1. This second document says:

"...set the SDU size as a multiple of the MSS."

The reason why MSS is used and not MTU, is that the MTU includes two headers, which reduces the amount of data which Oracle NS (Network Substrate) can transmit per TCP packet. Only MSS determines how much data Oracle can transmit via the lower network protocols.

To calculate the MSS:

MSS = MTU - TCP header size - IP header size

For bog-standard TCP over Ethernet:

MTU = 1500 bytes

TCP = 20 bytes

IP = 20 bytes

Thus, the MSS for TCP/IP over Ethernet is 1460.

I confirmed that the customer's network has an MTU of 1500.

Given that for Oracle 10g the maximum for SDU size is 32767, what's the optimal SDU with an MSS of 1460? I've done the simple math, and it's 32120.

That's what we use for the SDU size for a simple Ethernet network.

The results are quite impressive:

Before:

Top 5 Timed Events Avg % Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 1,203 70.5
db file sequential read 140,179 266 2 15.6 User I/O
SQL*Net more data to client 2,669,360 153 0 8.9 Network
control file parallel write 3,755 115 31 6.7 System I/O
db file scattered read 44,471 101 2 5.9 User I/O

After:

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time Wait Class

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

CPU time 636 72.4

db file sequential read 81,221 160 2 18.3 User I/O

control file parallel write 3,794 117 31 13.3 System I/O

db file scattered read 22,473 55 2 6.2 User I/O

log file parallel write 3,648 51 14 5.8 System I/O

(Sorry about the formatting, it's like herding cats.)

The "sql net more data to client" wait is eliminated! :)

At first I thought there was something wrong with the AWR Report, maybe the wrong snapshot was chosen, so I double-checked, and ran it for different days. But, it's really gone! :)

Tuesday, July 23, 2013

Database Network Performance Tuning

I recently looked at the database performance of a production database. This post will explain the network performance parameters which I implemented as a result of this work.

Here's the best SQLNET.ORA (server) I could come up for our OLTP application:

SQLNET.AUTHENTICATION_SERVICES=(NTS)
NAMES.DIRECTORY_PATH=(TNSNAMES)
DISABLE_OOB=ON
TCP.NO_DELAY=YES
DEFAULT_SDU_SIZE=32120
USE_DEDICATED_SERVER=ON
SQLNET.EXPIRE_TIME=10

And here's the relevant part of the LISTENER.ORA:

LISTENER =

(DESCRIPTION =

(SDU = 32120) (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1521)(SEND_BUF_SIZE = 65535)(RECV_BUF_SIZE = 65535))

)

Now, these settings are specifically chosen for an OLTP application, where response time is more important than throughput. I'll explain the settings and values.

SQLNET.AUTHENTICATION_SERVICES=(NTS)

Not much to say here except on the server I always use NTS because it's required by ASM, and on the Citrix server I set this variable to NONE.

NAMES.DIRECTORY_PATH=(TNSNAMES)

We use TNSNames for service resolution, so that's what we put.

DISABLE_OOB=ON

Disable Out-of-Band protocol (disables Ctrl-C). The application doesn't require OOB, so why bother with this overhead? Disable it.

Note: do not include the parameter BREAK_POLL_SKIP. With BREAK_POLL_SKIP, the Oracle client will check for a Ctrl-C, while DISABLE_OOB completely disables it. If both are used, I am not sure which takes precendence.

USE_DEDICATED_SERVER=ON

I use Dedicated Server mode for all production databases to ensure maximum performance. We have enough RAM, so why not?

SQLNET.EXPIRE_TIME=10

Closes a dead connection after 10 minutes e.g. application crashed and we need to clean up the dead connection.

TCP.NO_DELAY=YES

This is the most important parameter for OLTP apps. It tells the Oracle network software "stop messing about with buffers, just send the data back to the client ASAP!". This had a substantial performance improvement.

Sybase, in its ASE documentation, recommends this to be set on for its ASE. The relevant document can be found here:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1335.htm

Sybase puts it eloquently:

The tcp no delay parameter controls TCP (Transmission Control Protocol) packet batching. The default value is 1, which means that TCP packets are not batched.

TCP normally batches small logical packets into single larger physical packets (by briefly delaying packets) fill physical network frames with as much data as possible. This is intended to improve network throughput in terminal emulation environments where there are mostly keystrokes being sent across the network.

However, applications that use small TDS (Tabular Data Stream) packets may benefit from disabling TCP packet batching.

Yes, I realise it's Sybase doc and not Oracle, but the concept is the same, and their explanation is the best I could find.

The anti-thesis of this parameter is these two:

RECV_BUF_SIZE

SEND_BUF_SIZE

These two parameters set up the data buffers for the packets going back & forth. It's perfect for DSS (reporting) type applications. Put these in your SQLNET.ORA if you want your OLTP app to run slowly!

If both sets of parameters are used, I'm not sure which takes precedence.

SQLNET.ORA: DEFAULT_SDU_SIZE=32120 & SDU=3120 in Listener.ora

This is extremely important. I'll explain how I arrived at this in a separate post. For the new SDU size to take effect, you must update the SQLNET.ORA (client and server) and the LISTENER.ORA (as shown above), and restart the listener.

Tuesday, March 12, 2013

Very useful RARarchive script

set year=%date:~6,4%
set yr=%date:~8,2%
set month=%date:~3,2%
set day=%date:~0,2%
set hour=%time:~0,2%
set hour=%hour: =0%
set min=%time:~3,2%
set sec=%time:~6,2%

rar a -r -m5 DEV[%year%%month%%day%_%hour%_%min%].rar E:\Backups\DEV\*.*
forfiles /p "E:\Backups" /m "*.rar" /d -14 /c "cmd /c del @path"

Monday, February 25, 2013

Rebuild Indexes

Here's s a neat script I wrote for re-building indexes. One of the apps I look after has a requirement of having indexes stored in a tablespace called INDEXES. This is the script I wrote for doing this programmatically.

This script can be easily modified to re-build all indexes, or all non SYS/SYSTEM indexes, or just invalid indexes. It can also be wrapped in a package, and then called by a job.

The cool thing about this script is the way it uses a BULK COLLECT :).

DECLARE
sql_stmt VARCHAR2 ( 2000 ) DEFAULT NULL;

TYPE sql_stmt_array_type IS TABLE OF sql_stmt%TYPE;

sql_stmt_array sql_stmt_array_type DEFAULT NULL;

CURSOR cursor1
IS
SELECT 'ALTER INDEX ' || a.owner || '.' || a.index_name
|| ' REBUILD TABLESPACE INDEXES' sql1
FROM dba_indexes a, dba_objects b
WHERE a.owner IN
( 'SCHEMA_OWNER1'
,'SCHEMA_OWNER2')
AND a.index_type = 'NORMAL'
AND a.TEMPORARY LIKE 'N'
AND a.owner = b.owner
AND a.index_name = b.object_name
AND b.object_type = 'INDEX'
AND a.tablespace_name != 'INDEXES';
BEGIN
OPEN cursor1;

FETCH cursor1
BULK COLLECT INTO sql_stmt_array;

FOR i IN 1 .. sql_stmt_array.COUNT
LOOP
sql_stmt := sql_stmt_array ( i );

EXECUTE IMMEDIATE sql_stmt;
END LOOP;

CLOSE cursor1;
END;
/