ORA-20001 when dropping DBMS_SCHEDULER job with white spaces in name

Environment: Oracle Database 11g EE 11.2.0.3.0

When I tried to drop a scheduled job using DBMS_SCHEDULER.DROP_JOB, I received the following error message:

BEGIN
DBMS_SCHEDULER.DROP_JOB(
job_name => 'MATTHIASH.gather schema stats SCOTT'
);
END;

ORA-20001: comma-separated list invalid near T
ORA-06512: in "SYS.DBMS_UTILITY", regel 236
ORA-06512: in "SYS.DBMS_UTILITY", regel 272
ORA-06512: in "SYS.DBMS_SCHEDULER", regel 623
ORA-06512: in line 2

I was puzzled for a while… and then I saw what the problem was: the job name (“gather schema stats SCOTT”) contains white spaces, and this causes DBMS_SCHEDULER.DROP_JOB to throw an error…
However, there’s a workaround! If you enclose the job name in double quotes, the procedure DROP_JOB works fine:

BEGIN
DBMS_SCHEDULER.DROP_JOB(
job_name => 'MATTHIASH."gather schema stats SCOTT"'
);
END;

Btw: the same issue occurs with other DBMS_SCHEDULER procedures, like CREATE_JOB, RUN_JOB and such…

HTH,
Matthias

Advertisements

Finding duplicate BLOBS using DBMS_CRYPTO.HASH and analytic functions

Environment: Oracle Database EE 10.2.0.5

Suppose that you have a table with a BLOB column, and you want to know if there are duplicate BLOBS in that table. A GROUP BY query with COUNT doesn’t work on BLOB columns… One way would be to write a PL/SQL or Perl program that extracts all BLOBS and compares the values between all rows. So, is there no simple way to do this in SQL? Yes, there is! You can use the DBMS_CRYPTO package to hash the values of the BLOB column and then group the hashed values using an analytic function.

Let’s work out an example.

First, we create a simple table with one BLOB column:

CREATE TABLE BLOBS
(
  BLOBID       NUMBER                           NOT NULL,
  BLOBNAME     VARCHAR2(50)                     NOT NULL,
  BLOBCONTENT  BLOB                             NOT NULL
)
;

ALTER TABLE BLOBS ADD (
  CONSTRAINT BLOBS_PK
 PRIMARY KEY
 (BLOBID))
;

Next, let’s fill the table with some records. In this example, records 1, 4 and 5 have the same BLOB values, as have records 2 and 6.

insert into blobs(blobid,blobname,blobcontent)
values(1,'blob 1',utl_raw.cast_to_raw('contents of blob 1'));

insert into blobs(blobid,blobname,blobcontent)
values(2,'blob 2',utl_raw.cast_to_raw('contents of blob 2'));

insert into blobs(blobid,blobname,blobcontent)
values(3,'blob 3',utl_raw.cast_to_raw('contents of blob 3'));

insert into blobs(blobid,blobname,blobcontent)
values(4,'blob 4',utl_raw.cast_to_raw('contents of blob 1'));

insert into blobs(blobid,blobname,blobcontent)
values(5,'blob 5',utl_raw.cast_to_raw('contents of blob 1'));

insert into blobs(blobid,blobname,blobcontent)
values(6,'blob 6',utl_raw.cast_to_raw('contents of blob 2'));

commit;

Now we need to grant our user execute rights on the DBMS_CRYPTO package.
Execute the following SQL with user SYS as SYSDBA:

grant execute on dbms_crypto to matthiash;

Now comes the difficult part: create a query so that the contents of the BLOBS are hashed using DBMS_CRYPTO, hashes that have the same value are grouped together, and the record ID’s are shown so that it’s clear which records have identical BLOB values. I managed to do this using window partitioning. Basically, we group the results into different windows, where each window contains the records which have the same hashed BLOB contents.

This is the query:

select blobid, blobname, blobcontent,row_number()
over(partition by dbms_crypto.hash(blobcontent,2) order by blobid asc) row_number,
count(blobid) over(partition by dbms_crypto.hash(blobcontent,2)) blobcount 
from blobs 
order by blobcount desc, dbms_crypto.hash(blobcontent,2) asc, row_number asc;

And these are the results:

BLOBID,BLOBNAME,ROW_NUMBER,BLOBCOUNT
1,blob 1,1,3
4,blob 4,2,3
5,blob 5,3,3
2,blob 2,1,2
6,blob 6,2,2
3,blob 3,1,1

So, BLOBS 1, 4 and 5 are grouped, next we have BLOBS 2 and 6, and finally BLOB 3. Mission accomplished! :-) Mind you that this is a very small table, so the query runs very fast. Using DBMS_CRYPTO.HASH on tables with millions of rows or very large BLOBS might need some CPU power!

HTH,
Matthias

 

Script to monitor process on Linux

Environment: Oracle Linux 6.2

Sometimes you want to monitor a process on your Linux server and do some alerting when the process is not running anymore.

The following shell script can be used as an example. It will monitor the “dbconsole” process (database control) and send a e-mail message when the process is not running anymore.

$ vi check_db_control.sh

#!/bin/sh

PROCESSFILE='dbconsole'

if [ `ps ax|grep -v grep|grep -ic $PROCESSFILE` -gt 0 ]
then
        echo "$PROCESSFILE is running, everything is fine"
else
        echo "$PROCESSFILE is not running on `hostname -s`!" | mail -s "$PROCESSFILE down!" matthiash@mydomain.com
fi

exit

$ chmod u+x check_db_control.sh

To schedule the script, you can add it to cron. For example, this script will run every day at 10:00AM:

$ crontab -e

# Monitoring dbconsole process
00 10 * * * /home/oracle/scripts/check_db_control.sh 1>/dev/null 2>&1

 

HTH,
Matthias

Turning off dynamic listener registration on non-RAC systems to protect against “Oracle TNS Listener Poison Attack”

Environment: Oracle database 11.2.0.3 64-bit, Oracle Linux 6.2 64-bit

Oracle recently released a security alert (CVE-2012-1675) where they warn against a possible “Oracle TNS Listener Poison Attack”. There is no real fix, but there are a number of workarounds that you can use to protect your listeners against unauthorised hijacking.

One of the workarounds involves turning off dynamic registration of database instances by the listener service. However, this workaround can only be used for stand-alone, non-RAC installations. For RAC, you can implement secure transports, which are explained in My Oracle Support document 1453883.1 (if you have access to Oracle support).

Dynamic registration is by default turned on in Oracle 11g. To turn it off, there are two things you need to modify in your $ORACLE_HOME/network/listener.ora file:

First, you need to add a description for all the database instances that the listener will handle. If you fail to do this, any clients trying to connect will receive “ORA-12154: TNS:could not resolve the connect identifier specified” errors after you turned off the dynamic registration.

This is an example for my instance “oratst.mydomain.com”:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = oratst.mydomain.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = oratst)
    )
  )

Note: the GLOBAL_DBNAME and SID_NAME can be found by checking the Oracle initialization parameters db_name, db_domain and instance_name.

Next, to turn off dynamic registration, you need to add the following line to the listener.ora file:

DYNAMIC_REGISTRATION_LISTENER = OFF

This is how my full listener.ora file now looks like:

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-tst.mydomain.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = oratst.mydomain.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = oratst)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

# needed to solve a conflict with the ONS service installed in the OHS home
# see metalink note 284602.1
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER = OFF

DYNAMIC_REGISTRATION_LISTENER = OFF

After this, use the listener control utility (lsnrctl) to reload your listener’s configuration, and check the status of the services and the dynamic registration:

>lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 24-MAY-2012 13:45:49

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-tst.mydomain.com)(PORT=1521)))
The command completed successfully
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-tst.mydomain.com)(PORT=1521)))
Services Summary...
Service "oratst.mydomain.com" has 1 instance(s).
  Instance "oratst", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully
LSNRCTL> show dynamic_registration
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-tst.mydomain.com)(PORT=1521)))
LISTENER parameter "dynamic_registration" set to OFF
The command completed successfully

Note: the status “UNKNOWN” is normal when you are not using dynamic registration.

Finally, check if you can still connect to the database using a remote client!

HTH,
Matthias

Oracle 11g listener causing failed tcp connection attempts on a non-RAC system

Environment: Oracle database 11.2.0.3, Oracle Linux 6.2

I was debugging a possible network problem on a Oracle Linux server with a fresh installation of Oracle 11gR2, when I noticed the following:

$ netstat -s

Tcp:
1178980 active connections openings
273496 passive connection openings
911657 failed connection attempts

The number of “failed connection attempts” increased by one for every 2 seconds. So, something was clearly trying to connect to something else and this failed for some reason. I didn’t find how I could further debug this problem with netstat. However, I had free access to the server, so I tried to shut down the Oracle services one by one to see if this would stop the growing number of failed connection attempts. When I stopped the Oracle listener service, it did…

Listener logging was on and there were a lot of warnings in the listener.log file:

WARNING: Subscription for node down event still pending

I did a search on My Oracle Support and I found more information in the MOS document with ID 372959.1. Apparently, the listener service tries to contact a RAC service, but, since this is a non-RAC installation, the connection fails. The solution is to add SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener name>=OFF to your listener.ora file, and then restart the listener. This should remove the warnings and stop the failed connection attempts.

This is how the listener.ora file now looks like for my listener with name “LISTENER”:

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-tst.mydomain.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

# see metalink note 372959.1
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

Matthias

Posting source code in WordPress blogs

In my previous blog post, I published the source code of a bash shell script. I noticed that WordPress kept on messing around with the code (like replacing some of the double quotes), and at first I didn’t know how to solve this (I’m quite new to WordPress).

And then I found the following support article:

http://en.support.wordpress.com/code/posting-source-code/

So, basically, you need to wrap your source code into “sourcecode” blocks, using the HTML editor.

There are also some nice options, like setting the language, enabling highlighting or line wrapping etc.

This is an example with sourcecode language=”bash” as option:

#!bin/bash

# description: source code example

echo "hello world!"

exit 0

Matthias