Learning Never Stops

ORA-04068: existing state of packages has been discarded

2011-11-14T17:40:00.001-08:00

As the error message indicates it should happen on a package execution however in my case it happened on a stored procedure. All the reference on ORA-4068 referred me to a condition where a package had a global variable and a stored procedure or function within a package used the variable and there was a dependency on a table. Whenever the table was modified, procedure became invalid while package itself was valid.

But re-executing same package procedure recompiled the procedure and everything worked.

My situation was different where there is a procedure PROC1 which calls another stored procedure PROC2. PROC2 inserts into a table with a Sequence. The sequence is dropped and recreated by a batch job and PROC1 execution failed intermittently with these errors.

::: Exception Information :::
Message: ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "PROC2"
ORA-06508: PL/SQL: could not find program unit being called: "PROC2"
ORA-06512: at "PROC1", line 161
ORA-06512: at line 1

It turned out that ORA-04068 is not restricted to a package alone, Oracle has a known bug related to recreating sequence and a procedure depending on it.

Here is a reference to the Oracle support document

Sequence Dependency May Cause ORA-04068 (Doc ID 334946.1)

The article suggests not using Sequence and use some other method of getting incremental value. But a better solution will be to recompile all dependent stored procedures as soon as the sequence is recreated.

Audit Report Generation For Oracle In HTML Format

2011-06-09T12:10:00.001-07:00

This document describes steps needed to setup auditing in Oracle, generating HTML report, managing audit records and sending HTML email to DBAs.

v Set audit_trail parameter in init.ora to and bounce the database. This will log the audit trail in the database sys.aud$ table.

AUDIT_TRAIL = DB

Or if spfile is in use it can be changed using

alter system set audit_trail=DB scope=spfile

and restarting the database.

v Following things are audited. These contain statement auditing, privilege auditing and object auditing. Statement auditing is enabled by just an audit on a particular type like audit cluster or audit trigger. Privilege auditing is enabled by an audit on a particular privilege like audit create index or audit drop index. We have not enabled audit on a particular object in which object name has to be explicitly specified. All the audit options are enabled by ACCESS(default) except logon audit which is a session level auditing whenever not successful.

AUDIT CLUSTER;

AUDIT CONTEXT;

AUDIT DATABASE LINK;

AUDIT DIMENSION;

AUDIT DIRECTORY;

AUDIT CREATE ANY INDEX;

AUDIT DROP ANY INDEX;

AUDIT PROCEDURE;

AUDIT PROFILE;

AUDIT PUBLIC DATABASE LINK;

AUDIT PUBLIC SYNONYM;

AUDIT ROLE;

AUDIT SEQUENCE;

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

AUDIT SYNONYM;

AUDIT SYSTEM GRANT;

AUDIT TABLE;

AUDIT TRIGGER;

AUDIT TYPE;

AUDIT USER;

AUDIT VIEW;

AUDIT ALTER TABLE;

AUDIT ALTER SEQUENCE;

AUDIT GRANT DIRECTORY;

AUDIT GRANT SEQUENCE;

AUDIT GRANT TABLE;

AUDIT GRANT TYPE;

v Generate an audit report using script audit_report.bat, which in turns call audit_report.sql

v Delete audit records older than 30 days every day using audit_purge.sql

v Send email by running auditsummary.bat wrapper batch file which merges all the reports generated by audit_report and sends email using sendmailHTML.vbs

Scripts

1. AUDIT_REPORT.BAT

Audit_Report.bat is called from a batch file (daily_other_jobs.bat) for each database which needs audit report (as defined in JobInfo configuration file).

@echo off

if "%1" equ "" goto usage

set DBNAME=%1

if not exist E:\Scripts\%DBNAME%\sql\logs mkdir E:\Scripts\%DBNAME%\sql\logs

rem purge audit records older than 30 days

for /F "tokens=1-3" %%i in (E:\Scripts\Sql\AdminInfo) do ( if /I %%i equ %DBNAME% (

if exist e:\scripts\%DBNAME%\sql\logs\auditreport.html del e:\scripts\%DBNAME%\sql\logs\auditreport.html

D:\oracle\ora92\bin\sqlplus -S -M "HTML ON PREFORMAT OFF TABLE 'BORDER="2"'" system/%%j@%DBNAME% @e:\scripts\sql\audit_report.sql > e:\scripts\%DBNAME%\sql\logs\auditreport.html

D:\oracle\ora92\bin\sqlplus -S system/%%j@%DBNAME% @e:\scripts\sql\audit_purge.sql 30 > e:\scripts\%DBNAME%\sql\logs\audit_purge.log

))

E:\scripts\saveauditlog.bat %DBNAME%

goto end

:usage

echo usage : %0 DBNAME

:end

1.1 AdminInfo

This file contains UserID and password file to run the audit report. The file is encrypted using Windows encryption and can only be open by the Windows account scheduling the job. The format of the file content is

SID SYSTEMPassword

DB1 db1password

DB2 db2password

1.2 Audit_Report.sql

It reads AUD$ file and produces a HTML formatted report. Some records are filtered to minimize the size of the report.

REM Filter out ALTER TABLESPACE done by SYSTEM ACCOUNT on locally managed tablespaces

REM Filter JOURNAL and IOT tables and indexes created by Oracle for online index rebuild.

SET FEEDBACK OFF

SET ECHO OFF

COLUMN ROWNUM HEADING 'SR #'

COLUMN NAME HEADING 'ACTION'

COLUMN OBJ$NAME HEADING 'OBJECT NAME'

COLUMN AUTH$PRIVILEGES HEADING 'PRIVILEGES'

COLUMN AUTH$GRANTEE HEADING 'GRANTEE'

COLUMN SPARE1 HEADING 'OS USER'

COLUMN STATUS HEADING 'STATUS' ENTMAP OFF

SET HEAD OFF

COLUMN DATABASE ENTMAP OFF

select '<b>' || name || '</b>' "DATABASE" from v$database;

SET HEAD ON

select rownum,

to_char(timestamp#,'MM/DD/YY HH24:MI:SS') TIMESTAMP,

userid,

terminal,

name,

obj$name,

decode(substr(NVL(auth$privileges,'----------------'), 1,1),'-','','ALT ') ||

decode(substr(NVL(auth$privileges,'----------------'), 2,1),'-','','AUD ') ||

decode(substr(NVL(auth$privileges,'----------------'), 3,1),'-','','COM ') ||

decode(substr(NVL(auth$privileges,'----------------'), 4,1),'-','','DEL ') ||

decode(substr(NVL(auth$privileges,'----------------'), 5,1),'-','','GRA ') ||

decode(substr(NVL(auth$privileges,'----------------'), 6,1),'-','','IND ') ||

decode(substr(NVL(auth$privileges,'----------------'), 7,1),'-','','INS ') ||

decode(substr(NVL(auth$privileges,'----------------'), 8,1),'-','','LOC ') ||

decode(substr(NVL(auth$privileges,'----------------'), 9,1),'-','','REN ') ||

decode(substr(NVL(auth$privileges,'----------------'), 10,1),'-','','SEL ') ||

decode(substr(NVL(auth$privileges,'----------------'), 11,1),'-','','UPD ') ||

decode(substr(NVL(auth$privileges,'----------------'), 12,1),'-','','REF ') ||

decode(substr(NVL(auth$privileges,'----------------'), 13,1),'-','','EXE ') ||

decode(substr(NVL(auth$privileges,'----------------'), 14,1),'-','','CRE ') ||

decode(substr(NVL(auth$privileges,'----------------'), 15,1),'-','','REA ') ||

decode(substr(NVL(auth$privileges,'----------------'), 16,1),'-','','WRI ')

auth$grantee,

decode(returncode,0, 'SUCCESS', '<font color=''red''>'|| 'FAIL' ) status,

spare1

from sys.aud$ a, audit_actions b

where a.action# = b.action

and trunc(timestamp#) between trunc(sysdate-1) and trunc(sysdate)

and NVL(obj$name,' ') not like 'SYS_JOURNAL%'

and NVL(obj$name,' ') not like 'SYS_IOT_TOP%'

and name not in (

'LOGOFF',

'SET ROLE',

'SESSION REC')

and not exists

(select 1 from dba_tablespaces dt

where b.name = 'ALTER TABLESPACE'

and a.obj$name = dt.tablespace_name

and dt.extent_management='LOCAL'

and a.terminal = (select host_name from v$instance)

and a.userid = 'SYSTEM'

);

exit;

1.3 Audit_Purge.sql

This script purges records older than x number of days.

set verify off;

delete from sys.aud$

where timestamp# < sysdate-&1;

commit;

exit;

1.4 Saveauditlog.bat

This script saves audit log by renaming the audit log file with a date timestamp.

@echo off

Rem %1 is database name

Rem get date

REM The FOR Loop gets the date convention, mm-dd-yyyy and saves the date part in three variables

FOR /f "tokens=2-4 skip=1 delims=(-)" %%G IN ('echo.^|date') DO (

SET v_first=%%G

SET v_second=%%H

SET v_third=%%I

REM FOR loop breaks date /t output and saves everything after blank delimiter, v_all has date

FOR /f "tokens=2 delims= " %%A IN ('date /t') DO (

SET v_all=%%A

)

REM saves date part values into date part variables

SET %v_first%=%v_all:~0,2%

SET %v_second%=%v_all:~3,2%

SET %v_third%=%v_all:~6,4%

copy e:\scripts\%1\sql\logs\auditreport.html e:\scripts\%1\auditlog\auditreport_%YY%%mm%%dd%.html

2. Auditsummary.bat

This is wrapper script which merges audit reports for all the databases and sends email. It reads Jobsinfo file to see if audit report is needed for a database. It looks at IfLastDDL settings, copies the report for a database that has Y in this column to auditsummary.html

@echo off

for /F "tokens=1-9" %%i in (E:\Scripts\Jobs\JobsInfo) do (

if exist e:\scripts\%%i\sql\logs\auditreport.html type e:\scripts\%%i\sql\logs\auditreport.html >> e:\scripts\auditsummary.html

)

cscript.exe e:\scripts\sendmailhtml.vbs "dbas@gotodba.com" "server1@gotodba.com" "Oracle Security Audit Summary Report" "e:\scripts\auditsummary.html" "HTML"

if exist e:\scripts\auditsummary.html del e:\scripts\auditsummary.html

JobsInfo

Format of JobsInfo

sid IfExport IfLastDDL IfStatsPack IfDelOldFiles IfIndexRbld IfRMan IfGatherStats Ifhotbackup

DB1 Y Y Y Y Y Y Y Y

DB2 Y Y Y Y Y Y N N

3. DailyOtherJobs.bat

This script also calls other jobs, but only audit_report.bat is listed here.

@echo off

for /F "tokens=1-9" %%i in (E:\Scripts\Jobs\JobsInfo) do (

if /I "%%k" equ "Y" (Call E:\scripts\sql\audit_report.bat %%i )

)

call E:\Scripts\AuditSummary.bat

4. SendmailHTML.Vbs

This is a VBScript program that calls Windows CDO object to send SMTP email. smtp.gotodba.com is the SMTP server name.

'**********************************************************************

' Visual Basic Script

'************************************************************************

' Usage: cscript.exe e:\scripts\sendmailhtml.vbs "to@gotodba.com" "from@gotodba.com" "Subject" "HTMLfile" "HTML"

'************************************************************************

const ForReading=1

Dim ObjArgs, MailTo, From, Subject, Message, wshshell, goFS, oFile, FileName, FileType

Set ObjArgs = WScript.Arguments

MailTo = ObjArgs(0)

From = ObjArgs(1)

Subject = ObjArgs(2)

FileName = ObjArgs(3)

if ObjArgs.Count = 5 then

FileType = ObjArgs(4)

end if

set wshshell=wscript.createobject("Wscript.Shell")

set goFs=createobject("Scripting.FileSystemObject")

'This code requires the file to exist to compile

set oFile = goFs.OpenTextFile(FileName, ForReading)

Message = oFile.ReadAll

Call SendMail(MailTo, From, Subject, Message)

oFile.Close

set wshshell = nothing

set goFs = nothing

set oFile = nothing

Function SendMail(MailTo, From, Subject, Message)

Dim iMsg

Set iMsg = CreateObject("CDO.Message")

With iMsg

.To = MailTo

.From = From

.Subject = Subject

If UCase(FileType) = "HTML" Then

.HTMLBody = Message

ElseIf UCase(FileType) = "ATTACHMENT" Then

.AddAttachment FileName

Else

.TextBody = Message

End If

End With

iMsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gotodba.com"

iMsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

iMsg.Configuration.Fields.Update

iMsg.Send

End Function

Install and Configure DB Link to DB2 Database in Linux

2011-06-09T12:09:00.001-07:00

This document provides step by step instructions to install IBM DB2 Driver 9.1 for ODBC and CLI for Linux 64bit

Prerequisites:

· Oracle Database Gateway for ODBC is installed

· unixODBC is installed

# rpm -q unixODBC

unixODBC-2.2.11-7.1

# rpm -q unixODBC-devel

unixODBC-devel-2.2.11-7.1

Installing IBM DB2 Driver for ODBC and CLI

There is no installation program for the IBM DB2 Driver for ODBC and CLI. You must install the driver manually

1. Login as Oracle user

#su - oracle

2. Create the directory /u01/app/orcbin/db2_odbc_driver, where you will install the driver.

$ mkdir –p /u01/app/orcbin/db2_odbc_driver

$ cd /u01/app/orcbin/db2_odbc_driver

3. Locate the compressed file that contains the driver /u01/install/Linux-64_11gR1/db2_client/v9fp6a_linuxx64_db2driver_for_odbc_cli.tar

4. Copy v9fp6a_linuxx64_db2driver_for_odbc_cli.tar to the install directory, /u01/app/orcbin/db2_odbc_driver.

$ cp /u01/install/Linux-64_11gR1/db2_client/v9fp6a_linuxx64_db2driver_for_odbc_cli.tar /u01/app/orcbin/db2_odbc_driver/

5. Uncompress v9fp6a_linuxx64_db2driver_for_odbc_cli.tar:

$ tar -xvf v9fp6a_linuxx64_db2driver_for_odbc_cli.tar

Configuring IBM DB2 Driver for ODBC and CLI

1. Add the following to the beginning of the db2cli.ini. Be sure to add an extra blank line at the end of the ini file. The file is readonly, use :wq! to save it.

$ vi clidriver/cfg/db2cli.ini

[COMMON]

Trace=0

TracePathName=/u01/app/orcbin/db2_odbc_driver/clidriver/trace/

TraceComm=1

TraceFlush=1

TraceTimeStamp=1

[MYDB2]

Database=MYDB2

Hostname=mydb2.gotodba.com

Port=50000

Protocol=TCPIP

2. Configure ODBC.ini

$ su – root

# vi /etc/odbc.ini

[ODBC Data Source]

MYDB2=IBM DB2 ODBC DRIVER

[WBBURT1]

Driver=/u01/app/orcbin/db2_odbc_driver/clidriver/lib/libdb2.so

Description=MYDB2 DB2 ODBC Database

3. Test DB2 ODBC connectivity

# isql -v MYDB2 userid password

+---------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+---------------------------------------+

SQL> quit

Configuring Oracle Database Gateway for ODBC

http://download.oracle.com/docs/cd/B28359_01/gateways.111/b31042/configodbc.htm#CIHFEGDA

1. Configure the Gateway Initialization Parameter File. For Oracle RAC database HRDCSTG, the initial parameter GLOBAL_NAMES = true, DB_Domain = mycompany.com, which enforce that database links have same name as remote database. So in the initMYDB2.ora, HS_DB_NAME and HS_DB_DOMAIN parameters have to be set.

# su – oracle

$ cd $ORACLE_HOME/hs/admin

$ cp initdg4odbc.ora initMYDB2.ora

$ vi initMYDB2.ora

# HS init parameters

#

HS_FDS_CONNECT_INFO = MYDB2

HS_FDS_TRACE_LEVEL = 0

HS_FDS_SHAREABLE_NAME = /u01/app/orcbin/db2_odbc_driver/clidriver/lib/libdb2.so

HS_DB_NAME = MYDB2

HS_DB_DOMAIN = GOTODBA.COM

#

# ODBC specific environment variables

#

set ODBCINI = /etc/odbc.ini

#

# Environment variables required for the non-Oracle system

#

#set =

2. Configure Oracle Net for the Gateway

$ cd $ORACLE_HOME/network/admin

$ vi listener.ora

# Append the following lines

SID_LIST_LISTENER_NYCDB01=

(SID_LIST=

(SID_DESC=

(SID_NAME = MYDB2)

(ORACLE_HOME = /u01/app/orcbin/oracle/product/11.1.0/db_1)

(PROGRAM = dg4odbc)

(ENVS=LD_LIBRARY_PATH=/u01/app/orcbin/db2_odbc_driver/clidriver/lib:/u01/app/orcbin/oracle/product/11.1.0/db_1/lib)

)

$ vi tnsname.ora

# Append the following lines

MYDB2 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = nycdb01-vip.gotodba.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = nycdb02-vip.gotodba.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = MYDB2)

)

(HS = OK)

)

3. Restart Listner

$lsnrctl reload

Create DB Link in the Oracle database

CREATE PUBLIC DATABASE LINK MYDB2

CONNECT TO userid

IDENTIFIED BY password

USING 'MYDB2';

Note:

Oracle RAC database ORCL, the initial parameter GLOBAL_NAMES = true, DB_Domain = gotodba.com, the above command will create db link MYDB2.gotodba.com, the db link name has to match initMYDB2.ora HS_DB_NAME. HS_DB_DOMAIN

How to Change Interconnect/Public Interface IP in Clusterware

2011-06-09T12:08:00.001-07:00

Change interface for interconnect

Confirm the current interfaces configuration used by the cluster

$ $ORA_CRS_HOME/bin/oifcfg getif

Following is an example.

[oracle@nycdb01 oracle]$ $ORA_CRS_HOME/bin/oifcfg getif

eth4 192.168.251.0 global cluster_interconnect

pub_bond 10.164.227.0 global public

Stop the database and nodeapps on both nodes.

$ srvctl stop database -d DB_NAME

$ srvctl stop nodeapps -n node_name_first

$ srvctl stop nodeapps -n node_name_second

Following is an example

[oracle@nycdb01 oracle]$ srvctl stop database -d dbprd

[oracle@nycdb01 oracle]$ srvctl stop nodeapps -n nycdb01

[oracle@nycdb01 oracle]$ srvctl stop nodeapps -n nycdb02

The only way to change interface is to delete the existing interface and add new interface. Delete the interface with

$ $ORA_CRS_HOME/bin/oifcfg delif -global interface_name

Add new interface

$ $ORA_CRS_HOME/bin/oifcfg setif -global new_if/network_number:cluster_interconnect

Following is an example.

[oracle@nycdb01 oracle]$ $ORA_CRS_HOME/bin/oifcfg getif

eth4 192.168.251.0 global cluster_interconnect

pub_bond 10.164.227.0 global public

[oracle@nycdb01 oracle]$ $ORA_CRS_HOME/bin/oifcfg delif -global eth4

[oracle@nycdb01 oracle]$ $ORA_CRS_HOME/bin/oifcfg getif

pub_bond 10.164.227.0 global public

[oracle@nycdb01 oracle]$ $ORA_CRS_HOME/bin/oifcfg setif -global int_bond/192.168.251.0:cluster_interconnect

Confirm the changes have taken place.

[oracle@nycdb01 oracle]$ $ORA_CRS_HOME/bin/oifcfg getif

int_bond 192.168.251.0 global cluster_interconnect

pub_bond 10.164.227.0 global public

Public Interface and VIP change

If changed interface is public interface then that also involves modification of VIP. Changing the VIP involves modification of the nodeapps.

Stop nodeapps. Though it is not mandatory, but changes will only take effect once nodeapps are restarted.

$ srvctl stop nodeapps -n node_name_first

$ srvctl stop nodeapps -n node_name_second

Confirm current configuration.

[oracle@nycdb01 ~]$ $ORA_CRS_HOME/bin/oifcfg getif

eth1 10.164.227.0 global public

eth4 192.168.251.0 global cluster_interconnect

We want to change public interface from eth1 to pub_bond

[oracle@nycdb01 ~]$ $ORA_CRS_HOME/bin/oifcfg delif -global eth1

[oracle@nycdb01 ~]$ $ORA_CRS_HOME/bin/oifcfg getif

eth4 192.168.251.0 global cluster_interconnect

[oracle@nycdb01 ~]$ $ORA_CRS_HOME/bin/oifcfg setif -global pub_bond/10.164.227.0:public

[oracle@nycdb01 ~]$ srvctl modify nodeapps -n nycdb01 -A 10.164.227.152/255.255.255.0/pub_bond

PRKO-2117 : This command should be executed as the system privilege user.

[oracle@nycdb01 ~]$ su root

Password:

[root@nycdb01 oracle]# srvctl modify nodeapps -n nycdb01 -A 10.164.227.152/255.255.255.0/pub_bond

[root@nycdb01 oracle]# srvctl modify nodeapps -n nycdb02 -A 10.164.227.151/255.255.255.0/pub_bond

Verify configuration

[root@nycdb01 oracle]# $ORA_CRS_HOME/bin/crs_stat -p ora.nycdb01.vip | grep USR_ORA_IF

USR_ORA_IF=pub_bond

[root@nycdb01 oracle]# $ORA_CRS_HOME/bin/crs_stat -p ora.nycdb02.vip | grep USR_ORA_IF

USR_ORA_IF=pub_bond

[root@nycdb01 oracle]# crs_stat2

Reference: Metalink notes

283684.1 How to Change Interconnect/Public Interface IP or Subnet in Oracle Clusterware

276434.1 Modifying the VIP or VIP Hostname of a 10g or 11g Oracle Clusterware Node

Install and Configure DB Link to SQL Server Database in Linux

2011-06-09T12:07:00.001-07:00

This document provides step by step instructions to install IBM DB2 Driver 9.1 for ODBC and CLI for Linux 64bit

Prerequisites:

· Oracle Database Gateway for ODBC is installed

· unixODBC is installed

# rpm -q unixODBC

unixODBC-2.2.11-7.1

# rpm -q unixODBC-devel

unixODBC-devel-2.2.11-7.1

Installing FreeTDS ODBC Driver for SQL Server

1. Login as su user

# su –

2. Create the directory /usr/local/freetds, where you will install the driver.

# mkdir –p / # mkdir /usr/local/freetds

3. Locate the compressed file that contains the driver / /u01/install/Linux-64_11gR1/SQL_Server_ODBC_Driver/ freetds-stable.tar

4. Copy v9fp6a_linuxx64_db2driver_for_odbc_cli.tar to , /u01/app/orcbin/db2_odbc_driver.

# cp /u01/install/Linux-64_11gR1/SQL_Server_ODBC_Driver/freetds-stable.tar .

5. Unpack freetds_stable.tar:

# tar -xvf freetds-stable.tar

6. Install freetds

# cd freetds-0.82

# ./configure --prefix=/usr/local/freetds --with-tdsver=8.0

7. Make

# make; make install; make clean

Configuring FreeTDS Driver for ODBC

1. Configure ODBC.ini

$ su – root

# vi /etc/odbc.ini

[ODBC Data Sources]

MYDB2=IBM DB2 ODBC DRIVER

SQLDB=FreeTDS

[SQLDB]

Driver = /usr/local/freetds/lib/libtdsodbc.so

#Driver = /usr/lib64/libodbc.so

Server = dbSQLDB.gotodba.com

Database = SQLDB

Port = 1433

TDS_Version = 8.0

2. Test SQL Server ODBC connectivity

# isql -v SQLDB userid password

+---------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+---------------------------------------+

SQL> quit

Configuring Oracle Database Gateway for ODBC

http://download.oracle.com/docs/cd/B28359_01/gateways.111/b31042/configodbc.htm#CIHFEGDA

1. Configure the Gateway Initialization Parameter File. For Oracle RAC database ORCL, the initial parameter GLOBAL_NAMES = true, DB_Domain = gotodba.com, which enforce that database links have same name as remote database. So in the initSQLDB.ora, HS_DB_NAME and HS_DB_DOMAIN parameters have to be set.

# su – oracle

$ cd $ORACLE_HOME/hs/admin

$ cp initdg4odbc.ora initSQLDB.ora

$ vi initSQLDB.ora

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = SQLDB

HS_FDS_TRACE_LEVEL = 0

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

HS_DB_NAME = SQLDB

HS_DB_DOMAIN = GOTODBA.COM

HS_FDS_SUPPORT_STATISTICS = FALSE

#

# ODBC specific environment variables

#

set ODBCINI = /etc/odbc.ini

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

Note:

Need to set

HS_FDS_SUPPORT_STATISTICS = FALSE

Check Metsalink Doc. ID: 744636.1 for

ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[FreeTDS][SQL Server]Invalid cursor state
ORA-02063: preceding 2 lines from DG4ODBC

2. Configure Oracle Net for the Gateway

$ cd $ORACLE_HOME/network/admin

$ vi listener.ora

# Append the following lines

SID_LIST_LISTENER_TWNYCSLD04=

(SID_LIST=

(SID_DESC=

(SID_NAME = MYDB2)

(ORACLE_HOME = /u01/app/orcbin/oracle/product/11.1.0/db_1)

(PROGRAM = dg4odbc)

(ENVS=LD_LIBRARY_PATH=/u01/app/orcbin/db2_odbc_driver/clidriver/lib:/u01/app/orcbin/oracle/product/11.1.0/db_1/lib)

)

(SID_DESC =

(ORACLE_HOME = /u01/app/orcbin/oracle/product/11.1.0/db_1)

(SID_NAME = SQLDB)

(PROGRAM = dg4odbc)

(ENVS=LD_LIBRARY_PATH=/usr/lib64:/usr/local/freetds/lib:/u01/app/orcbin/oracle/product/11.1.0/db_1/lib)

)

$ vi tnsname.ora

# Append the following lines

SQLDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = nycdb1-vip.gotodba.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = nycdb2-vip.gotodba.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = SQLDB)

)

(HS = OK)

)

3. Restart Listner

$lsnrctl reload

Create DB Link in the Oracle database

CREATE DATABASE LINK SQLDB

CONNECT TO userid

IDENTIFIED BY password

USING 'SQLDB';

Note:

Oracle RAC database ORCL, the initial parameter GLOBAL_NAMES = true, DB_Domain = gotodba.com, the above command will create db link SQLDB.GOTODBA.COM, the db link name has to match initSQLDB.ora HS_DB_NAME. HS_DB_DOMAIN

Rolling Forward Standby Database When Archive Log is Missing

2011-06-09T12:06:00.001-07:00

Oracle Dataguard is technology of choice for maintaining disaster recovery site. Over past few releases it has matured significantly and with data guard broker and grid control it is really easy to setup, maintain and perform switchovers.

To setup a standby you have to clone the primary database, after that depending on type of physical standby data guard you choose, either redo logs or archived logs are transferred to standby site and applied.
Standby data guard is quite resilient in terms of catching up with primary database. If network is down for some time, standby falls behind primary but as network becomes available standby requests missing logs and continues to apply them. It may take some time to catch up the primary depending on amount of logs generated during network disruption.

If for some reason some of the archived logs are missing then you may think you will have to redo the standby setting from scratch by cloning the database again. But there is good news for you, you can roll forward standby to recover up to current SCN of primary without applying missing archived logs.

The basic idea is to capture status of current standby database, then compare it with primary database and gather incremental change and apply it on standby.

Here are the step by step instructions:

1. Shutdown standby database. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> SHUTDOWN IMMEDIATE;

2. Rename existing control files in standby

3. Start a SQL session on the primary database and create the control file for the standby database:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'stbycf.ctl';

4. Copy the standby control file to all control files locations in standby server.

5. Connect to the standby database and mount (but do not open) the standby database:

SQL> STARTUP MOUNT;

6. If data file locations are different in primary and standby, verify that spfile has db_file_name_convert defined.

SQL> SHOW PARAMETER CONVERT;

7. If convert parameter is missing, set it and shutdown the database and startup mount again.

SQL>ALTER SYSTEM SET DB_FILE_NAME_CONVERT='H:\MYDB\SYSTEM01.DBF', 'H:\MYDB\SYS\SYSTEM01.DBF','M:\MYDB\UNDOTBS01.DBF','I:\MYDB\UNDO\UNDOTBS01.DBF' SCOPE=SPFILE;

8. Connect to the recovery catalog and the standby database as the target, and manually catalog the standby datafiles as datafile copies

RMAN>CONNECT CATALOG RMANUSER/PASSWORD@RMANDB
RMAN>CONNECT TARGET SYS/PASSWORD@STDBYMYDB
RMAN>CATALOG DATAFILECOPY
              'H:\MYDB\SYS\SYSTEM01.DBF',
              'I:\MYDB\UNDO\UNDOTBS01.DBF',
              'H:\MYDB\DATA1\MYSCHEMA_DATA01.DBF',
              'I:\MYDB\DATA2\MYSCHEMA_DATA02.DBF',
              'I:\MYDB\IDX1\MYSCHEMA_INDX01.DBF',
              'H:\MYDB\IDX2\MYSCHEMA_INDX02.DBF',
              'I:\MYDB\DATA2\TOOLS01.DBF',
              'H:\MYDB\SYS\SYSAUX01.DBF'
         LEVEL 0 TAG 'STBY';

9. Exit RMAN and start it again then connect to the recovery catalog and the primary database as the target, and create an incremental level 1 backup using the datafile copies as the parent level 0.

RMAN>CONNECT CATALOG RMANUSER/PASSWORD@RMANDB
RMAN>CONNECT TARGET SYS/PASSWORD@MYDB
RMAN> RUN {
    ALLOCATE CHANNEL DSK1 TYPE DISK FORMAT 'D:\BACKUP\MYDB\STDBY\INC_MYDB_%U_%S_%T.BAK';
    ALLOCATE CHANNEL DSK2 TYPE DISK FORMAT 'D:\BACKUP\MYDB\STDBY\INC_MYDB_%U_%S_%T.BAK';
    ALLOCATE CHANNEL DSK3 TYPE DISK FORMAT 'D:\BACKUP\MYDB\STDBY\INC_MYDB_%U_%S_%T.BAK';
    ALLOCATE CHANNEL DSK4 TYPE DISK FORMAT 'D:\BACKUP\MYDB\STDBY\INC_MYDB_%U_%S_%T.BAK';
              BACKUP AS COMPRESSED BACKUPSET
                     INCREMENTAL LEVEL 1 TAG 'STBY' FOR RECOVER OF COPY WITH TAG 'STBY' DATABASE;
              RELEASE CHANNEL DSK1;
              RELEASE CHANNEL DSK2;
              RELEASE CHANNEL DSK3;
              RELEASE CHANNEL DSK4;
       }

9. Exit RMAN

10. Copy the newly created backup piece to the same location on the standby system

COPY D:\BACKUP\MYDB\STDBY\*.* \STDBYSERVER\D$\BACKUP\MYDB\STDBY

10. To minimize the possibility of any other programs accessing Oracle files, stop Oracle agents and remove broker configuration as well.

11. Remove broker configuration using DGMGRL

DGMGRL> REMOVE CONFIGURATION;

12. Start RMAN and connect to the recovery catalog and the standby database as the target, and roll the datafile copies forward

RMAN>CONNECT CATALOG RMANUSER/PASSWORD@RMANREP
RMAN>CONNECT TARGET SYS/PASSWORD@STDBYMYDB

RMAN>RUN {
              SQL 'ALTER DATABASE MOUNT';
              ALLOCATE CHANNEL DSK1 TYPE DISK;
              ALLOCATE CHANNEL DSK2 TYPE DISK;
              ALLOCATE CHANNEL DSK3 TYPE DISK;
              ALLOCATE CHANNEL DSK4 TYPE DISK;
               RECOVER COPY OF DATABASE WITH TAG 'STBY';
              RELEASE CHANNEL DSK1;
              RELEASE CHANNEL DSK2;
              RELEASE CHANNEL DSK3;
              RELEASE CHANNEL DSK4;
       }

13. Using SQL Plus at standby database,

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP NOMOUNT;
SQL>ALTER DATABASE MOUNT STANDBY DATABASE;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

14. Verify that the database started without any error - check alert log file.

15. Start the Oracle agents and upload agent.

16. Once standby server appears online in Grid, setup the data guard configuration.

17. Verify that the logs are getting copied and applied.

18. Connect to RMAN and standby database and delete the incremental backup

RMAN>DELETE BACKUP TAG 'STBY';
RMAN>CHANGE COPY LIKE 'D:\BACKUP\MYDB\STDBY\%' UNCATALOG;

This has proved to be a life saver for me when some archived log files disappeared from primary database and I was worried that I have to start from scratch. It has been tested in Oracle 10g, and it should work in 11g as well. You may even chose to use this if standby is far behind primary after a long network disruption, thus avoiding long archive log transfer and apply process to sync up standby with primary.

Keeping Track of Virtual Databases in Windows

2011-06-09T12:04:00.000-07:00

If you have SQL Server or Oracle databases in Microsoft Windows clustered environment, then you always want to keep virtual servers running on their preferred nodes especially if you have more than one virtual database in a cluster.

However, sometimes you may discover that database servers are not performing well and you may find that two or more virtual servers may be running on same node. This can happen if there was any error on one of the servers and virtual databases failed over to other node. Or you may have applied patches on Windows operating servers and rebooted them but forgot to failover the virtual servers to proper nodes.

You may have server monitoring system in place to alert you about any resource failovers but as an administrator you may get tons of alerts of different criticality everyday and may miss such email.

To overcome such situation, you can write a script to go after all your clustered databases and find where they are running. You can run this VBScript every morning or schedule it and send report on email, which you should not ignore.

The following script (save as c:\checkservice\checkservice.vbs) generates a report in HTML format. You can change the color scheme to your liking.

The script uses arrays to store server name and service names you want to report on. It can report on clustered or stand alone servers, the number of services on standalone will be half of clustered ones.

First entry in the array is physical server name, rest are service names.

You can add as many servers and services in the list, just make sure to change the array SQLServer and OracleServer, also change the number in WHILE LOOP as well.

This script calls WMI script to query each server in the list to query service status, so the account you login to run the script must have administrative rights on all the servers.

'Usage     : Double click on the file if you are logged in with Admin account or
'            Open a command window with Admin account and run cscript.exe
'           checkservice.vbs

'

Const ForWriting = 2, ForReading = 1, RunningColor = "#339900", StoppedColor = "#CC0000", White = "#FFFFFF", LightGrey = "#C0C0C0"

Dim SQLServer(3,4), OracleServer(3,4), bgColor, sDisplayName

On Error Resume Next

' standalone server with MSSQL and Agent services only

SQLServer(0,0) = ("SKSQLSDB01")
SQLServer(0,1) = ("SQL Server (MSSQLSERVER)")
SQLServer(0,2) = ("SQL Server Agent (MSSQLSERVER)")

' clustered servers with named instances

SQLServer(1,0) = ("SKSQLCDB03")
SQLServer(1,1) = ("SQL Server (SKSQLCNDB03)")
SQLServer(1,2) = ("SQL Server Agent (SKSQLCNDB03)")
SQLServer(1,3) = ("SQL Server (SKSQLCNDB04)")
SQLServer(1,4) = ("SQL Server Agent (SKSQLCNDB04)")

SQLServer(2,0) = ("SKSQLCDB04")
SQLServer(2,1) = ("SQL Server (SKSQLCNDB04)")
SQLServer(2,2) = ("SQL Server Agent (SKSQLCNDB04)")
SQLServer(2,3) = ("SQL Server (SKSQLCNDB03)")
SQLServer(2,4) = ("SQL Server Agent (SKSQLCNDB03)")

' standalone server with Oracle database ORCL and listener services only

OracleServer(0,0) = ("SKORADB01")
OracleServer(0,1) = ("OracleServiceORCL")
OracleServer(0,2) = ("OracleOraDb10g_home1TNSListener")

' clustered servers Oracle FailSafe databases and listeners

OracleServer(1,0) = ("SKORACDB03")
OracleServer(1,1) = ("OracleServiceORCL03")
OracleServer(1,2) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB03")
OracleServer(1,3) = ("OracleServiceORCL04")
OracleServer(1,4) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB04")

OracleServer(2,0) = ("SKORACDB04")
OracleServer(2,1) = ("OracleServiceORCL03")
OracleServer(2,2) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB03")
OracleServer(2,3) = ("OracleServiceORCL04")
OracleServer(2,4) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB04")

Set fso = CreateObject("Scripting.FileSystemObject")

Set Logfile = fso.OpenTextFile("C:\CheckService\status.htm", ForWriting, True)
LogFile.WriteLine("<HTML>")
LogFile.WriteLine("<HEAD>")
LogFile.WriteLine("<META content=""text/html; charset=utf-8"" http-equiv=Content-Type>")

LogFile.WriteLine("<TITLE>Database Services Status</TITLE>")
LogFile.WriteLine("</HEAD>")
LogFile.WriteLine("<BODY>")

LogFile.WriteLine("<H3>SQL Server Services Status</H3>")
LogFile.WriteLine("<p style=""font-family:verdana"">")
LogFile.WriteLine("<P>")
LogFile.WriteLine("<P><TABLE BORDER=1>")
LogFile.WriteLine("<TR><TH ALIGN=LEFT>ServerName</TH><TH ALIGN=LEFT>ServiceName</TH><TH ALIGN=LEFT>Status</TH></TR>")

bgColor = White
i = 0

Do While i <= 3
strComputer = SQLServer(i,0)

       Set objWMIService = GetObject("winmgmts:" _
           & "{impersonationLevel=impersonate}!\" & strComputer & "\root\cimv2")

       If Err.Number <> 0 Then
              LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">Error " & CStr(Err.Number) & " Occurred</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & Err.Description & "</FONT></TD></TR>")

       Else
              j = 0
              Do While j <= 4
                     If IsEmpty(SQLServer(i,j)) Then
                           Exit Do
                     End If

                     sDisplayName = SQLServer(i,j)
                     Set colRunningServices = objWMIService.ExecQuery("Select * from Win32_Service where DisplayName = '" & sDisplayName & "'")

                     For Each objService in colRunningServices
                           If objService.State = "Running" Then

                                  LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & RunningColor & ">" & objService.State & "</FONT></TD></TR>")

                           Else
                                  LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & objService.State & "</FONT></TD></TR>")

                           End If
                     Next

                     j = j + 1
              Loop

       End If

       LogFile.WriteLine()

       i = i + 1
       If bgColor = White Then
              bgColor = LightGrey
       Else
              bgColor = White
       End If
Loop

LogFile.WriteLine("</TABLE>")

LogFile.WriteLine("<H3>Oracle Server Services Status</H3>")
LogFile.WriteLine("<p style=""font-family:verdana"">")
LogFile.WriteLine("<P>")
LogFile.WriteLine("<P><TABLE BORDER=1>")
LogFile.WriteLine("<TR><TH ALIGN=LEFT>ServerName</TH><TH ALIGN=LEFT>ServiceName</TH><TH ALIGN=LEFT>Status</TH></TR>")

bgColor = White
i = 0
Do While i <= 3
       strComputer = OracleServer(i,0)

       Set objWMIService = GetObject("winmgmts:" _
           & "{impersonationLevel=impersonate}!\" & strComputer & "\root\cimv2")

       If Err.Number <> 0 Then

              LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">Error " & CStr(Err.Number) & " Occurred</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & Err.Description & "</FONT></TD></TR>")

       Else
              j = 0
              Do While j <= 4
                     If IsEmpty(OracleServer(i,j)) Then
                           Exit Do
                     End If

                     sDisplayName = OracleServer(i,j)

                     Set colRunningServices = objWMIService.ExecQuery("Select * from Win32_Service where DisplayName = '" & sDisplayName & "'")

                     For Each objService in colRunningServices
                           If objService.State = "Running" Then

                                  LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & RunningColor & ">" & objService.State & "</FONT></TD></TR>")

                           Else
                                  LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & objService.State & "</FONT></TD></TR>")

                           End If
                     Next

                     j = j + 1
              Loop
       End If

       LogFile.WriteLine()
       i = i + 1

       If bgColor = White Then
              bgColor = LightGrey
       Else
              bgColor = White
       End If
Loop

LogFile.WriteLine("</BODY>")
LogFile.WriteLine("</HTML>")
Logfile.close

Set oShell = WScript.CreateObject ("WSCript.shell")
oShell.run "iexplore C:\CheckService\status.htm"
Set oShell = Nothing

You can run this script by double clicking it and it generates a file status.htm and opens it with Internet Explorer.

Other option is to comment out the oShell.run line, write a batch file and schedule it to run every morning and send the report in email.

Here is the batch file checkservice.bat

@echo off

cscript.exe c:\CheckService\CheckService.vbs

cscript.exe c:\checkservice\sendmailHTML.vbs "dba@mycompany.com" "serverstatus@mycompany.com" "Services Status Report" "c:\CheckService\status.htm" HTML

SendmailHTML.vbs is also a VBScript which sends email in HTML format using a SMTP server. Change YOUR_SMTP_SERVER_NAME with your smtp server name or IP. You can also send file attachment or plain text email with this script.

'**********************************************************************

' Usage: cscript.exe c:\checkservice\sendmailhtml.vbs "to@company.com" "from@company.com" "Subject" "HTMLfile" "HTML"

'***********************************************************************

const ForReading=1

Dim ObjArgs, MailTo, From, Subject, Message, wshshell, goFS, oFile, FileName, FileType

Set ObjArgs = WScript.Arguments

MailTo = ObjArgs(0)
From = ObjArgs(1)
Subject = ObjArgs(2)
FileName = ObjArgs(3)

if ObjArgs.Count = 5 then
       FileType = ObjArgs(4)
end if

set wshshell=wscript.createobject("Wscript.Shell")
set goFs=createobject("Scripting.FileSystemObject")

'This code requires the file to exist to compile

set oFile = goFs.OpenTextFile(FileName, ForReading)
Message = oFile.ReadAll
Call SendMail(MailTo, From, Subject, Message)
oFile.Close
set wshshell = nothing
set goFs = nothing
set oFile = nothing

Function SendMail(MailTo, From, Subject, Message)
       Dim iMsg

       Set iMsg = CreateObject("CDO.Message")

       With iMsg
              .To   = MailTo
              .From = From
              .Subject = Subject

              If UCase(FileType) = "HTML" Then
                     .HTMLBody = Message
              ElseIf UCase(FileType) = "ATTACHMENT" Then
                     .AddAttachment FileName
              Else
                     .TextBody = Message
              End If
       End With

       iMsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YOUR_SMTP_SERVER_NAME"

       iMsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

       iMsg.Configuration.Fields.Update
       iMsg.Send
End Function

Mixed Mode Share (NFS/CIFS) in a NetApp Filer

2011-06-07T07:46:00.000-07:00

We had a requirement to setup a mixed mode share where a Linux server will map to the share using NFS and Windows desktop users will access it using CIFS. We created a qtree and for security style we had three options.

1. Unix Style - where folder and files follow unix style security of drwxrwxrwx.
2. Windows Style - where folder and files follow Windows style security of access to users and groups and varied level of accesses.
3. Mixed Style - where windows machine will still use drwxrwxrwx style security and Windows machine will use users and group level access.

Netapp recommends against Mixed mode and prefers using security style of the dominant file system that accesses the qtree. Since Linux was the primary user of the share we decided to use Unix style, but we ran into issue where we could not grant fine grained access to Windows account as it is not available in Unix style security.

Next we decided to convert the security style to Mixed mode. It allowed us to grant ownership in Linux for the application user and group and fine grained access in Windows share. This created another problem. Whenever Windows priveleges were granted to the shared folder, windows users were able to access to the folder but Unix user lost privilge. The folder was owned by root and group bin.

We changed the owner of the folder from Linux machine to application user and group, that caused windows users to lose permission. Windows users were granted permission based on Active Directory group, no direct permission to a user.

The solution to this problem was to convert security style to unix and modify usermap.cfg file in /etc folder of the filer to add mapping for windows user to map as application user. For example one entry in usermap.cfg looked like

windowsdomain\user1 => linuxappuser

After that we restarted CIFS service in filer and that fixed the permission issues. Now both Linux and Windows users could access the share with one caveat, Windows users had same privilege as the application user account. You can remediate by creating a local Linux account for each user, but for our purpose all windows accounts mapping to application user account was acceptable.

Monitor Informatica PowerCenter using Oracle Enterprise Manager

2011-05-07T15:36:00.000-07:00

Oracle Enterprise manager provides plugins for other Oracle applications but there is nothing available from Oracle or Informatica to monitor PowerCenter. However OEM can monitor host systems and web applications. Since PowerCenter has an Admin Console, I decided to setup monitoring using this feature.

It involved multiple steps to complete the setup.

1. Install Oracle Enterprise Manager Agent in PowerCenter host system and register it with OEM
2. Create a system
3. Create a web application
4. Create beacons
5. Setup notification rule

This setup seemed to work until I ran into a situation where Admin Console site was responding with an error not loading login page. For OEM it looked up as it responded to http request.

I needed to find a better way to monitor this. I came across a feature called User Defined Metric (UDM) in OEM. It required me to write a script in host system, since my system is a Red Hat Linux, I used bash script. This solution involved

1. Create three bash scripts which ran Informatica provided infacmd.sh to ping PowerCenter services, one for domain, another for repository and the third for integration service.
2. Setup UDM in OEM with metric type as Numeric and both warning and critical threshold to > 0.
3. Setup new notification rule. Pick host as the target and go to Metrics tab and select User Defined metrics (it is the last metrics listed) you created.

Script to monitor a domain

INFA_HOME=/apps/informatica/910
INFA_DOMAIN=Domain_ABC
INFA_NODE=Node_mynode
INFA_HOST=myhost

$INFA_HOME/isp/bin/infacmd.sh ping -dn $INFA_DOMAIN -nn $INFA_NODE
OUT=`expr $?`

if [ "$OUT" -eq 0 ]; then
        echo -e "em_result=$OUT\nem_message=Informatica Node $INFA_NODE in Domain $INFA_DOMAIN is Up on $INFA_HOST\n"
else
        echo -e "em_result=$OUT\nem_message=Informatica Node $INFA_NODE in Domain $INFA_DOMAIN is Down on $INFA_HOST\n"
fi
exit
Script to monitor repository service

INFA_HOME=/apps/informatica/910
INFA_DOMAIN=Domain_ABC
INFA_NODE=Node_mynode
INFA_HOST=myhost
$INFA_HOME/isp/bin/infacmd.sh ping -dn $INFA_DOMAIN -sn $INFA_PCREP
OUT=`expr $?`
if [ "$OUT" -eq 0 ]; then
        echo -e "em_result=$OUT\nem_message=Informatica Repository Service is Up\n"
else
        echo -e "em_result=$OUT\nem_message=Informatica Repository Service is Down\n"
fi
exit

Script to monitor Integration service

INFA_HOME=/apps/informatica/910
INFA_DOMAIN=Domain_ABC
INFA_NODE=Node_mynode
INFA_HOST=myhost

$INFA_HOME/isp/bin/infacmd.sh ping -dn $INFA_DOMAIN -sn $INFA_PCIS
OUT=`expr $?`
if [ "$OUT" -eq 0 ]; then
        echo -e "em_result=$OUT\nem_message=Informatica Integration Service is Up\n"
else
        echo -e "em_result=$OUT\nem_message=Informatica Integration Service is Down\n"
fi
exit

LiteSpeed Log Shipping Error Msg 62301, Level 18, State 1, Line 0

2011-05-02T06:08:00.000-07:00

Msg 62301, Level 18, State 1, Line 0 is Microsoft SQL Server error which tells the log file you are trying to restore is not available.

But if you are using LiteSpeed for LogShipping, LiteSpeed may fail with this error if there are any issues with the transaction log file itself. I have seen it happen many times even though the file exists and is the same size as in the source.

LiteSpeed keeps track of all its error in LSTrackingLog table. The first thing to check is errors for latest restore attempt. Depending on how many files you are behind the same error may repeat many times so find the first error for the current batch.

select * from litespeed..lstrackinglog
where planid='plan id'
order by logtimestamp desc

Where plan id is for the database in concern. You can get that from LSPlans table.

Usually the error says the file you are trying to restore is newer than what LiteSpeed is looking for. An example of this error is

The log in this backup set begins at LSN 57971000000292000001, which is too late to apply to the database. An earlier log backup that includes LSN 57967000000278700001 can be restored.

Now how do you find out which backup set has the LSN of interest.

For that you need to query msdb database of the primary database.

select bs.database_name, bs.first_lsn, bs.last_lsn, bs.backup_start_date, bs.backup_finish_date, bm.physical_device_name
from backupset as bsjoin backupmediafamily as bm
on bs.media_set_id=bm.media_set_id
where bs.first_lsn=57967000000278700001

Next check if the backup file named by physical_device_name from the previous query exists in the secondary database server. If not copy it from the primary and run the LiteSpeed restore job.

If that still does not solve the problem, which seems to happen quite often, you will need to delete the file in secondary, copy the file again (no overwriting existing file), and run the restore manually with

exec master.dbo.xp_restore_log @database = 'YOUR_DATABASE_NAME_HERE',

@filename = 'INSERT_TRANSACTIONLOG_BACKUP_FILENAME_HERE',

@encryptionkey = 'INSERT_ENCRYPTION_KEY_HERE',

@filenumber = 1,

@with = 'NORECOVERY',

@with = 'NOUNLOAD',

@with = 'STATS = 10'

If this succeeds, then you should be in good shape.

Reason for Extremely slow NFS performance Revealed

2011-04-19T18:28:00.000-07:00

In a newly created Red Hat Linux 5.4 server, the NFS performance was extremely slow compared to local disk. Creating 1 GB file would complete in about a second with 878 MB/s

#dd if=/dev/zero of=/tmp/test.dat bs=1M count=1024
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 1.244 seconds, 878 MB/s

But doing the same on a NFS mount (mounted on NetApp filer with 4 x 1 GB port channeled network) was taking 66 times longer.

dd if=/dev/zero of=/u01/apps//test.dat bs=1M count=1024
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 81.5585 seconds, 13.2 MB/s

One another server the NFS write to same storage device was five times better than this.

#dd if=/dev/zero of=/tmp/test.dat bs=1M count=1024
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 12.434 seconds, 87.8 MB/s

One other interesting fact was if I mounted the same NFS export interactively rather than using /etc/fstab, the performance was as good as I got from other server.

The /etc/fstab setting was

10.164.192.69:/vol/vol7/test /u01/apps nfs _netdev,hard,suid,rsize=32768,wsize=32768,noac,intr
The first thing I changed after that was removing rsize and wsize parameter from /etc/fstab and remounted the NFS export. Now performance became even worse. Reading about rsize and wsize revealed that if these values are smaller than the NFS target, the performance suffers a lot. But checking /proc/mounts showed default value of wsize and rsize to be 65536, double the size in the original /etc/fstab.

Next I removed noac and kept the rsize and wsize parameters as before and remounted NFS shares again. Tada! the performance was way better and it was same as other server.

Further digging on noac parameter in NetApp documentation revealed my duh moment. It says

Using the noac mount option causes 40% performance degradation on typical workloads, but some common workloads, such as sequential write workloads, can be impacted by up to 70%. Database workloads that consist of random reads and writes are generally less affected by noac.

Then why would anyone use this parameter and how come it ended in my /etc/fstab. Finally I found that we had this setting for our Oracle RAC OCR mount point. Oracle recommended this value.

I needed to find the reason now. Same NetApp document says

noac disables file attributes caching. This means that every file operation on the client that requires file attribute information results in a GETATTR operation to retrieve a file’s attribute information from the server. Note that noac also causes a client to process all writes to that file system synchronously, just as the sync mount option does. Disabling attribute caching is only one part of noac; it also guarantees that data modifications are visible on the server so that other clients using noac can detect them immediately.

This makes sense for Oracle RAC as each node need to know if file changed immediately so that they can keep track of each other. But just copying the parameters meant for one use to other NFS mounts without realizing the repurcussion were kind of laziness in our part.

Oracle error 3113: ORA-03113: end-of-file on communication channel

2011-04-18T05:46:00.000-07:00

This is another one of common Oracle error which is hard to troubleshoot as there are so many reasons it can happen, all are related to Networking. But if you go to your network team with "anything happening with network?", their answer will be "Everything is fine, see ping responds within milli seconds", now what will you do?

We had this error happening intermittently when we moved Oracle database to a new network segment from application server. After many hours of troubleshooting, I started trusted SQL Net tracing on client side. It gave more detailed information but still pointed to network. Next I got network team to look at packets and found that network packets were going through a not traditional path. It turned out that application server had default gateway pointed to a F5 load balancer and when SQL Net packet passed through it, it did something to the packet and it appeared corrupt to Oracle server when it reached there.

The solution was to change the default gateway to a switch in that segment but this could not be done as application needed a way to trace the client machine of the user, F5 provided this feature. So we ended up adding persistent route to Oracle servers from each application server through the switch, in Windows this was done using

ROUTE ADD

command. Now all traffic to Oracle passed through switch instead of F5 but other traffic still used F5 and everyone was happy.

Firewalls have feature to inspect SQL packet and not being a network guy, I don't understand the utility for such feature but it seems to cause problem for Oracle connections. Not sure F5 did the same thing on SQL packets but avoiding it in Oracle connection path helped us.

Distribution Database Growing with Merge Replication

2011-04-16T05:35:00.000-07:00

The distribution database is supporting three relatively active merge publications and it kept on growing and reaching 60 GB in size. As the replication was working without any latency, replication cleanup job was also working fine and Replication Monitor showed everything in Excellent status.

If everything is excellent then why distribution database is growing, a look at cleanup history jobs run status showed that it was deleting 0 rows. I looked at top tables reports on distribution database and MSMerge_Hitory table had 100 million rows. So I ran the stored procedure run by cleanup history job with lower retention

EXEC dbo.sp_MShistory_cleanup @history_retention = 10

Still nothing got deleted. I had no choice but to look inside sp_MShistory_cleanup and the problem was in this delete statement

DELETE dbo.MSmerge_history
FROM dbo.MSmerge_history msmh
JOIN dbo.MSmerge_sessions msms
ON msmh.session_id = msms.session_id
WHERE msms.end_time <= @cutoff_time
OPTION(MAXDOP 1)

The MSmerge_sessions.end_time was the current time for every replication sessions. So nothing got deleted. This looked like a bug to to me as we are running continuous replication and the end time is contantly updated for these sessions. To resolve the issue at hand I stopped all replication jobs and started immediately causing SQL Server to start a new session in MSmerge_sessions table, now the DELETE can delete older records but I did not want to let the job run for hours to delete these records.

I took matter in my own hand and deleted them directly.

WHILE 1=1
BEGIN
   SET ROWCOUNT 100000

   DELETE dbo.MSmerge_history
   FROM dbo.MSmerge_history msmh
   JOIN dbo.MSmerge_sessions msms
   ON msmh.session_id = msms.session_id
   WHERE msms.end_time <= getdate()
   OPTION(MAXDOP 1)

   IF @@ROWCOUNT = 0
       BREAK
END
This deleted 100 million rows in 10 minutes. But I still needed to figure out how to avoid this in future. The problem manifested as the servers were not rebooted in 3 months. So either you need to reboot your server periodically and or create a job to stop and restart merge replication jobs every month, I opted for the later.

ORA-01410 No ROWID Found

2011-04-14T12:16:00.000-07:00

Problem - A PL/SQL block is called from Cobol NetExpress code which has a cursor for updating current row. The SQL is like this

DECLARE
   my_emp_id NUMBER(6);
   my_job_id VARCHAR2(10);
   my_sal    NUMBER(8,2);
   CURSOR c1 IS SELECT employee_id, job_id, salary
    FROM employees FOR UPDATE;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO my_emp_id, my_job_id, my_sal;
      IF my_job_id = 'SA_REP' THEN
        UPDATE employees SET salary = salary * 1.02
          WHERE CURRENT OF c1;
      END IF;
      EXIT WHEN c1%NOTFOUND;
   END LOOP;
END;
/

This code is from Oracle documents sample, the code I was troubleshooting is similar. But when this runs I get ORA-01410 No ROWID Found error.

My first reaction was to update the row from SQL Plus directly to see if there is any issue doing it but that ran fine. One of the document in metalink suggested that a corrupt index or table block can also do this. But direct update debunked that theory.

Metalink has registered bug report for this on version 7.x of Oracle and in one case for update on a DBLINKed table, again not applicable to me. But it did offer an work around, which is to use ROWID instead of WHERE CURRENT OF as

DECLARE
   my_emp_id NUMBER(6);
   my_job_id VARCHAR2(10);
   my_sal    NUMBER(8,2);

   rownum ROWID;
   CURSOR c1 IS SELECT employee_id, job_id, salary, rownum
    FROM employees FOR UPDATE;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO my_emp_id, my_job_id, my_sal;
      IF my_job_id = 'SA_REP' THEN
        UPDATE employees SET salary = salary * 1.02
          WHERE rowid=rownum;
      END IF;
      EXIT WHEN c1%NOTFOUND;
   END LOOP;
END;
/

This solved my problem for now.

Generating Database Objects Scripts in SQL Server 2005

2011-04-07T07:38:00.000-07:00

How many times you have been asked by a developer if you can recover a stored procedure or a table structure because he/she made a change in existing environment. In a perfect world all SQL code including database objects must be in a source control tool, but in most of the places database schema is not part of it as it is little cumbersome to include it.

SQL Server Management Studio provides a wizard to generate script out of a database. But it would be nice if we can automate the process so that database script is generated and stored somewhere.

The following script utilizes SQL Server Publishing Wizard to do just that. You can use it for one server or you can use it for all servers in your environment. The server details are maintained in a table called ServerNames in a database called DBServers.

SQL Server Publishing wizard is available at http://sqlhost.codeplex.com/wikipage?title=Database%20Publishing%20Wizard&ProjectName=sqlhost

USE [DBServers]

/****** Object: Table [dbo].[ServerNames] Script Date: 09/22/2009 16:18:56 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[ServerNames](

[ID] [int] IDENTITY(1,1) NOT NULL,

[SERVERNAME] [varchar](255) NULL,

[DDL] [tinyint] NULL CONSTRAINT [DF_ServerNames_DDL] DEFAULT ((0))

) ON [PRIMARY]

SET ANSI_PADDING OFF

A value of 1 in column Active indicates you want to generate DDL for this server. You can create a SQL Agent job and past the following script and schedule it to run as you need.

SET NOCOUNT ON

DECLARE @ServerNames TABLE

(ID INT IDENTITY(1,1),

SERVERNAME VARCHAR(255))

DECLARE @ServerDBNames TABLE

(ID INT IDENTITY(1,1),

SERVERNAME VARCHAR(255),

DATABASENAME VARCHAR(255))

DECLARE @servername varchar(255)

DECLARE @DBName varchar(255)

DECLARE @sql varchar(4000)

DECLARE @RowNum tinyint

DECLARE @MaxRow tinyint

Declare @CMD varchar(2000)

Declare @SQLPubWizExe varchar(150)

Declare @FolderName varchar(150)

SET @RowNum = 1

INSERT INTO @ServerNames (Servername)

SELECT Servername

FROM DBTracker.dbo.ServerNames

WHERE Active=1 AND DDL =1

ORDER BY ID

SELECT @MaxRow = max(ID) from @ServerNames

--loop through it and generate script

WHILE @RowNum <= @MaxRow

BEGIN

SELECT @servername=servername from @ServerNames where ID = @RowNum

SET @sql = 'SELECT '''+@servername+''',DB.Name FROM OPENROWSET(''SQLNCLI'', ''Server=' + @servername + ';Trusted_Connection=yes;'','

SET @sql = @sql + '''SELECT Name FROM sysdatabases WHERE dbid > 4 ORDER BY Name'') AS DB;'

INSERT INTO @ServerDBNames (servername, databasename)

EXEC (@sql)

SET @RowNum = @RowNum + 1;

END

SELECT @MaxRow = max(ID) from @ServerDBNames

SET @SQLPubWizExe = 'D:\"Program Files (x86)"\"Microsoft SQL Server"\90\Tools\Publishing\SQLPubWiz.Exe'

SET @FolderName = 'F:\DDL_OUTPUT\'

WHILE @RowNum <= @MaxRow

BEGIN

SELECT @servername=servername,@DBName=Databasename from @ServerDBNames where ID = @RowNum

SELECT @CMD ='EXEC master..xp_cmdshell ''' + @SQLPubWizExe + ' script -C "Data Source='

+@ServerName+';Initial Catalog='+ @DBName + ';Integrated Security=SSPI;" ' + @FolderName + REPLACE(@SERVERNAME,',','_')+'_'+@DBName+'_'+

REPLACE(convert(varchar(8),getdate(),112)+'_'+convert(varchar(8),getdate(),114),':','')+

'.sql -schemaonly -f'',NO_OUTPUT'

--print @CMD

Exec (@CMD)

SET @RowNum = @RowNum + 1;

END

Modify @SQLPubWizExe variable to point to the SQLPubWiz.exe in your environment and modify @FolderName to point to the script output location.

The job connect to each server using Windows authentication, so make sure that the SQL Server Agent running this job has access to all the servers listed in ServerNames table.

Since this calls xp_cmdshell to run the Publication Wizard, you will need to enable xp_cmdshell from SQL Configuration Manager if it is not enabled.

A script file is generated for each database and file name is tagged with date and time. So you do not have to worry about overwriting the script file from daily run.

Check Status of Your Windows Clustered Databases

2011-04-06T18:19:00.000-07:00

If you have SQL Server or Oracle databases in Microsoft Windows clustered environment, then you always want to keep virtual servers running on their preferred nodes especially if you have more than one virtual database in a cluster.

However, sometimes you may discover that database servers are not performing well and you may find that two or more virtual servers may be running on same node. This can happen if there was any error on one of the servers and virtual databases failed over to other node. Or you may have applied patches on Windows operating servers and rebooted them but forgot to failover the virtual servers to proper nodes.

You may have server monitoring system in place to alert you about any resource failovers but as an administrator you may get tons of alerts of different criticality everyday and may miss such email.

To overcome such situation, you can write a script to go after all your clustered databases and find where they are running. You can run this VBScript every morning or schedule it and send report on email, which you should not ignore.

The following script (save as c:\checkservice\checkservice.vbs) generates a report in HTML format. You can change the color scheme to your liking.

The script uses arrays to store server name and service names you want to report on. It can report on clustered or stand alone servers, the number of services on standalone will be half of clustered ones.

First entry in the array is physical server name, rest are service names.

You can add as many servers and services in the list, just make sure to change the array SQLServer and OracleServer, also change the number in WHILE LOOP as well.

This script calls WMI script to query each server in the list to query service status, so the account you login to run the script must have administrative rights on all the servers.

'Usage     : Double click on the file if you are logged in with Admin account or
'            Open a command window with Admin account and run cscript.exe
'           checkservice.vbs

'

Const ForWriting = 2, ForReading = 1, RunningColor = "#339900", StoppedColor = "#CC0000", White = "#FFFFFF", LightGrey = "#C0C0C0"

Dim SQLServer(3,4), OracleServer(3,4), bgColor, sDisplayName

On Error Resume Next

' standalone server with MSSQL and Agent services only

SQLServer(0,0) = ("SKSQLSDB01")
SQLServer(0,1) = ("SQL Server (MSSQLSERVER)")
SQLServer(0,2) = ("SQL Server Agent (MSSQLSERVER)")

' clustered servers with named instances

SQLServer(1,0) = ("SKSQLCDB03")
SQLServer(1,1) = ("SQL Server (SKSQLCNDB03)")
SQLServer(1,2) = ("SQL Server Agent (SKSQLCNDB03)")
SQLServer(1,3) = ("SQL Server (SKSQLCNDB04)")
SQLServer(1,4) = ("SQL Server Agent (SKSQLCNDB04)")

SQLServer(2,0) = ("SKSQLCDB04")
SQLServer(2,1) = ("SQL Server (SKSQLCNDB04)")
SQLServer(2,2) = ("SQL Server Agent (SKSQLCNDB04)")
SQLServer(2,3) = ("SQL Server (SKSQLCNDB03)")
SQLServer(2,4) = ("SQL Server Agent (SKSQLCNDB03)")

' standalone server with Oracle database ORCL and listener services only

OracleServer(0,0) = ("SKORADB01")
OracleServer(0,1) = ("OracleServiceORCL")
OracleServer(0,2) = ("OracleOraDb10g_home1TNSListener")

' clustered servers Oracle FailSafe databases and listeners

OracleServer(1,0) = ("SKORACDB03")
OracleServer(1,1) = ("OracleServiceORCL03")
OracleServer(1,2) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB03")
OracleServer(1,3) = ("OracleServiceORCL04")
OracleServer(1,4) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB04")

OracleServer(2,0) = ("SKORACDB04")
OracleServer(2,1) = ("OracleServiceORCL03")
OracleServer(2,2) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB03")
OracleServer(2,3) = ("OracleServiceORCL04")
OracleServer(2,4) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB04")

Set fso = CreateObject("Scripting.FileSystemObject")

Set Logfile = fso.OpenTextFile("C:\CheckService\status.htm", ForWriting, True)
LogFile.WriteLine("<HTML>")
LogFile.WriteLine("<HEAD>")
LogFile.WriteLine("<META content=""text/html; charset=utf-8"" http-equiv=Content-Type>")

LogFile.WriteLine("<TITLE>Database Services Status</TITLE>")
LogFile.WriteLine("</HEAD>")
LogFile.WriteLine("<BODY>")

LogFile.WriteLine("<H3>SQL Server Services Status</H3>")
LogFile.WriteLine("<p style=""font-family:verdana"">")
LogFile.WriteLine("<P>")
LogFile.WriteLine("<P><TABLE BORDER=1>")
LogFile.WriteLine("<TR><TH ALIGN=LEFT>ServerName</TH><TH ALIGN=LEFT>ServiceName</TH><TH ALIGN=LEFT>Status</TH></TR>")

bgColor = White
i = 0

Do While i <= 3
strComputer = SQLServer(i,0)

       Set objWMIService = GetObject("winmgmts:" _
           & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

       If Err.Number <> 0 Then
              LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">Error " & CStr(Err.Number) & " Occurred</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & Err.Description & "</FONT></TD></TR>")

       Else
              j = 0
              Do While j <= 4
                     If IsEmpty(SQLServer(i,j)) Then
                           Exit Do
                     End If

                     sDisplayName = SQLServer(i,j)
                     Set colRunningServices = objWMIService.ExecQuery("Select * from Win32_Service where DisplayName = '" & sDisplayName & "'")

                     For Each objService in colRunningServices
                           If objService.State = "Running" Then

                                  LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & RunningColor & ">" & objService.State & "</FONT></TD></TR>")

                           Else
                                  LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & objService.State & "</FONT></TD></TR>")

                           End If
                     Next

                     j = j + 1
              Loop

       End If

       LogFile.WriteLine()

       i = i + 1
       If bgColor = White Then
              bgColor = LightGrey
       Else
              bgColor = White
       End If
Loop

LogFile.WriteLine("</TABLE>")

LogFile.WriteLine("<H3>Oracle Server Services Status</H3>")
LogFile.WriteLine("<p style=""font-family:verdana"">")
LogFile.WriteLine("<P>")
LogFile.WriteLine("<P><TABLE BORDER=1>")
LogFile.WriteLine("<TR><TH ALIGN=LEFT>ServerName</TH><TH ALIGN=LEFT>ServiceName</TH><TH ALIGN=LEFT>Status</TH></TR>")

bgColor = White
i = 0
Do While i <= 3
       strComputer = OracleServer(i,0)

       Set objWMIService = GetObject("winmgmts:" _
           & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

       If Err.Number <> 0 Then

              LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">Error " & CStr(Err.Number) & " Occurred</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & Err.Description & "</FONT></TD></TR>")

       Else
              j = 0
              Do While j <= 4
                     If IsEmpty(OracleServer(i,j)) Then
                           Exit Do
                     End If

                     sDisplayName = OracleServer(i,j)

                     Set colRunningServices = objWMIService.ExecQuery("Select * from Win32_Service where DisplayName = '" & sDisplayName & "'")

                     For Each objService in colRunningServices
                           If objService.State = "Running" Then

                                  LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & RunningColor & ">" & objService.State & "</FONT></TD></TR>")

                           Else
                                  LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & objService.State & "</FONT></TD></TR>")

                           End If
                     Next

                     j = j + 1
              Loop
       End If

       LogFile.WriteLine()
       i = i + 1

       If bgColor = White Then
              bgColor = LightGrey
       Else
              bgColor = White
       End If
Loop

LogFile.WriteLine("</BODY>")
LogFile.WriteLine("</HTML>")
Logfile.close

Set oShell = WScript.CreateObject ("WSCript.shell")
oShell.run "iexplore C:\CheckService\status.htm"
Set oShell = Nothing

You can run this script by double clicking it and it generates a file status.htm and opens it with Internet Explorer.

Other option is to comment out the oShell.run line, write a batch file and schedule it to run every morning and send the report in email.

Here is the batch file checkservice.bat

@echo off

cscript.exe c:\CheckService\CheckService.vbs

cscript.exe c:\checkservice\sendmailHTML.vbs "dba@mycompany.com" "serverstatus@mycompany.com" "Services Status Report" "c:\CheckService\status.htm" HTML

SendmailHTML.vbs is also a VBScript which sends email in HTML format using a SMTP server. Change YOUR_SMTP_SERVER_NAME with your smtp server name or IP. You can also send file attachment or plain text email with this script.

'**********************************************************************

' Usage: cscript.exe c:\checkservice\sendmailhtml.vbs "to@company.com" "from@company.com" "Subject" "HTMLfile" "HTML"

'***********************************************************************

const ForReading=1

Dim ObjArgs, MailTo, From, Subject, Message, wshshell, goFS, oFile, FileName, FileType

Set ObjArgs = WScript.Arguments

MailTo = ObjArgs(0)
From = ObjArgs(1)
Subject = ObjArgs(2)
FileName = ObjArgs(3)

if ObjArgs.Count = 5 then
       FileType = ObjArgs(4)
end if

set wshshell=wscript.createobject("Wscript.Shell")
set goFs=createobject("Scripting.FileSystemObject")

'This code requires the file to exist to compile

set oFile = goFs.OpenTextFile(FileName, ForReading)
Message = oFile.ReadAll
Call SendMail(MailTo, From, Subject, Message)
oFile.Close
set wshshell = nothing
set goFs = nothing
set oFile = nothing

Function SendMail(MailTo, From, Subject, Message)
       Dim iMsg

       Set iMsg = CreateObject("CDO.Message")

       With iMsg
              .To   = MailTo
              .From = From
              .Subject = Subject

              If UCase(FileType) = "HTML" Then
                     .HTMLBody = Message
              ElseIf UCase(FileType) = "ATTACHMENT" Then
                     .AddAttachment FileName
              Else
                     .TextBody = Message
              End If
       End With

       iMsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YOUR_SMTP_SERVER_NAME"

       iMsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

       iMsg.Configuration.Fields.Update
       iMsg.Send
End Function

"ORA-12545: Connect failed because target host or object does not exist"

2011-04-06T13:43:00.000-07:00

ORA-12545 is a very generic message Oracle throws when it can't connect to a database. If you search Oracle support site you will get a general troubleshooting guide and it covers entire spectrum of connectivity issue.

I stumbled upon this in our environment few times already. One of the symptoms that we got was intermittent connection failure to Oracle database causing a long running job to crash in the middle. I noticed that when I connect from SQL Plus it will fail every few other times.

Now it's time to dig deeper. The Oracle database I was trying to connect is a 2-node Oracle 10g RAC. To understand the connection problem, I decided to do client side SQL Net tracing by adding

DIAG_ADR_ENABLED=OFF
TRACE_LEVEL_CLIENT=admin
TRACE_DIRECTORY_CLIENT=C:\oracle\product\11.1.0\client_1\network\trace
TRACE_FILE_CLIENT=cli.trc

in SQLNET.ORA file. I was using Oracle 11g client so ADR need to be disabled for the trace to go to a location I wanted. I again attempted SQL Plus connection and encountered a failure after few tries. The trace revealed an important clue that helped me resolve the issue.

[10-FEB-2011 14:21:21:287] nsc2addr: (ADDRESS=(PROTOCOL=TCP)(HOST=hrdb1-vip)(PORT=1541))
[10-FEB-2011 14:21:21:287] nttbnd2addr: entry
[10-FEB-2011 14:21:21:287] snlinGetAddrInfo: entry
[10-FEB-2011 14:21:21:287] snlinGetAddrInfo: getaddrinfo() failed with error 11001
[10-FEB-2011 14:21:21:287] snlinGetAddrInfo: exit
[10-FEB-2011 14:21:21:287] nttbnd2addr: looking up IP addr for host: hrdb1-vip
[10-FEB-2011 14:21:21:287] snlinGetAddrInfo: entry
[10-FEB-2011 14:21:28:068] snlinGetAddrInfo: getaddrinfo() failed with error 11001
[10-FEB-2011 14:21:28:068] snlinGetAddrInfo: exit
[10-FEB-2011 14:21:28:068] nttbnd2addr: *** hostname lookup failure! ***
[10-FEB-2011 14:21:28:068] nttbnd2addr: exit
[10-FEB-2011 14:21:28:068] nserror: nsres: id=0, op=77, ns=12545, ns2=12560; nt[0]=515, nt[1]=1001, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[10-FEB-2011 14:21:28:068] nsmfr: 352 bytes at 0x2470590
[10-FEB-2011 14:21:28:068] nsmfr: 212 bytes at 0x2469cc8
[10-FEB-2011 14:21:28:068] nladtrm: entry
[10-FEB-2011 14:21:28:068] nladtrm: exit
[10-FEB-2011 14:21:28:068] nioqper: error from nscall
[10-FEB-2011 14:21:28:068] nioqper:    ns main err code: 12545
[10-FEB-2011 14:21:28:068] nioqper:    ns (2) err code: 12560
[10-FEB-2011 14:21:28:068] nioqper:    nt main err code: 515
[10-FEB-2011 14:21:28:068] nioqper:    nt (2) err code: 1001
[10-FEB-2011 14:21:28:068] nioqper:    nt OS   err code: 0
[10-FEB-2011 14:21:28:068] niomapnserror: entry
[10-FEB-2011 14:21:28:068] niqme: entry
[10-FEB-2011 14:21:28:068] niqme: reporting NS-12545 error as ORA-12545
[10-FEB-2011 14:21:28:068] niqme: exit
[10-FEB-2011 14:21:28:068] niomapnserror: exit
[10-FEB-2011 14:21:28:068] niotns: Couldn't connect, returning 12545
[10-FEB-2011 14:21:28:162] niotns: exit
The error in red indicated that I was trying to connect to hrdb1-vip. I checked my tnsnames.ora file for the service I was attempting to connect to and found that it had client side failover setup. The host name was fully qualified with hrdb1-vip.xyz.com

HRDB =
(DESCRIPTION =
    (SDU = 4096)
    (ENABLE = BROKEN)
    (LOAD_BALANCE = yes)
    (ADDRESS = (PROTOCOL = TCP)(HOST = hrdb1-vip.xyz.com)(PORT = 1541))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hrdb2-vip.txyz.com)(PORT = 1541))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HRDB.xyz)
      (FAILOVER_MODE =
        (TYPE = select)
        (METHOD = basic)
        (RETRIES = 20)
        (DELAY = 5)
      )
    )
)

Then why is SQL Plus trying to connect to the host without the full name. With client side failover in tnsnames.ora entry, SQL Plus will alternately try to connect to different host in round robin fashion. But Oracle server also does server side load balancing, meaning if the host you are trying to connect to is busy then Oracle will tell the client to go the other node in RAC. When Oracle does that it sends the host name of other node to client as registered to current listener by other node. This will depend on how local_listener is configured. It turned out in our case that local_listener configuration did not use fully qualified host name.

So my client was trying connect on the server name without the domain name. Since my machine is not in the same domain as the Oracle RAC, the server name could not be resolved by my default DNS.

To get around this, I added the name of the server in my local hosts file and now Oracle connection works every time.

This was a puzzling problem to solve and required me to understand inner working of load balancing in Oracle. The main lesson to learn is being consistent with naming hosts when you are building a RAC to avoid heartburns later.

SQL Server PIVOT Operator Saves the Day

2011-04-03T18:34:00.000-07:00

As it always happens whenever there is a performance issue with an application, database is always thought to be a culprit. The application was timing out and we do not have any timeout set on SQL Server, the vendor was blaming on the database, which is hardly utilized.

I decided to look under the hood and traced the application using SQL Server Profiler. I saw a query like

select userid, LAST_NAME=MAX(CASE WHEN attr_name='LAST_NAME' then value_string END),
FIRST_NAME=MAX(CASE WHEN attr_name='FIRST_NAME' then value_string END),
HIRE_DATE=MAX(CASE WHEN attr_name='HIRE_DATE' then value_string END)
FROM
(select .......
FROM .....
WHERE ....)
group by userid

This looked like a typical query where rows in a reference table containing data for different attributes was being transformed to columns. The inline view was little complex but the whole query worked fine when there was only one attribute was selected and inline view returned 36,000 rows. But the problem query had inline view returning 300,000 rows. When I ran this query it did not finish in 10 minutes. Application had timeout of 30 seconds so for the vendor it was "database problem".

To optimize it I first looked at index on attribute table and saw that value_index was not indexes, and I went aha that's what they missed. But when I tried to create an index on value_string, SQL server complained that it can't do it on a VARCHAR(max) column. Oops, so I tried to be creating by creating a computed column with first 255 characters of value_string. But I learned that SQL Server does not allow you to specify the data type on a computed column, so computed column ended up being VARCHAR(max), so I was back to square one.

Then after scratching my head for sometime, I remembered PIVOT operator from SQL Server book I read five years ago, glad I had created an index on my head :D

I decided to give it a try and I rewrote the query as

select userid, [LAST_NAME], [FIRST_NAME], [HIRE_DATE]
FROM
(select .......
FROM .....
WHERE ....) as S
PIVOT
MAX(value_string)
FOR attr_name IN ([LAST_NAME], [FIRST_NAME], [HIRE_DATE])

) AS PivotTable;

and hit execute and bingo it returns results in 8 seconds. Now it's payback time for the vendor.