OEM Generic service

I want to monitor the output of one script in OEM.

Let’s say my script is that easy :

echo $RANDOM

So I go to

  • OEM 10gR2 Grid Control
  • All Targets
  • Add Generic Service – Go
  • Name : random
  • Select System : EM Website System
  • Define availability based on: Service Test
  • Test Type: custom test
  • Name : random
  • Collection Frequency : 1 minute
  • Command line : /home/oracle/random.sh
  • Username : oracle
  • Password : oracle
  • Add Beacon : oemsrv01_beacon
  • Next – Next – Finish
  • That’s all, your generic service is ready. Wait a bit, you will see this in your targets list. You can then click on perform in your “random” homepage and chose “custom metric 1” to have a graph of your values ($RANDOM).

    Happy Holidays !

    For those who like free games about skiing with really good graphics, check this :
    Ski Challenge 2007
    go to http://www.tsr.ch/tsr/index.html?siteSect=860020
    click Télécharger le jeu (about 41Mb)


    I am in the process in installing Oracle 10gR2 patch 2 with response file. There is no plenty of disk available, but installing a patch does not require as much space as specified by the runInstaller.

    when I start in silent mode, I get :

    $ ./runInstaller -responseFile /home/oracle/10202.rsp -silent
    Starting Oracle Universal Installer...
    You may not have enough space on your drive for a successful install. Do you still want to continue?

    Where should I answer yes? There is a secret parameter called -ignoreDiskWarning which does the trick. The parameter is not listed in ./runInstaller -help.

    So I installed my + patch and still have 500Mb free. It is ok for Oracle Home. Check your diskspace before using this to avoid filesystem full.


    What is the length of an empty string?

    According to Oracle documentation,

    Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls

    And therefore the length of null is null, not 0 (there are no character value with a length of zero :oops:).

    However, this is not true for clobs 😈

    SQL> create table t(x clob);
    Table created.
    SQL> insert into t values (empty_clob());
    1 row created.
    SQL> select x,length(x) from t where x is not null;
    X  LENGTH(X)
    - ----------

    search for a string in all tables of a schema

    this is often asked on the forums. I also needed this a while ago while reverse engineering a database model.

    Here is my today solution:

    1) select * and extract the first column found per table with regexp (10g)

    SQL> select table_name,column_name from (select rownum,table_name, regexp_substr(dbms_xmlgen.getxml(‘select * from “‘||table_name||'”‘),'<[^>]*>&string</[^<]*>’) column_name from user_tables) where length(column_name)!=0;
    Enter value for string: 20
    ———- ——————————
    DEPT       <DEPTNO>20</DEPTNO>
    EMP        <DEPTNO>20</DEPTNO>

    SQL> select table_name,column_name from (select rownum,table_name, regexp_substr(dbms_xmlgen.getxml(‘select * from “‘||table_name||'”‘),'<[^>]*>&string</[^<]*>’) column_name from user_tables) where length(column_name)!=0;
    Enter value for string: KING
    ———- ——————————
    EMP        <ENAME>KING</ENAME>

    2) add a where condition. much slower of course, because scanning the table more often. somehow nicer output. More sensible to datatypes. Here for number.

    SQL> select table_name, column_name from (select rownum,table_name, column_name, dbms_xmlgen.getxml(‘select 1 from “‘||table_name||'” where “‘||column_name||'”=&number’) x from user_tab_columns where data_type=’NUMBER’) where length(x)!=0;
    Enter value for number: 3000
    ———- ——————————
    EMP        SAL
    BONUS      COMM

    Oracle 9iR2 desupport

    How long is Oracle 9iR2/10g going to be supported? Infinitely!

    Metalink updated the upcoming desupport advisory.

    For 8iR3, you had something like :

    Error Correction Support (ECS):     31-DEC-2004
    Extended Support (ES):              31-DEC-2007
    Extended Maintenance Support (EMS): 31-DEC-2006

    Now for 9iR2, 10gR1, 10gR2, you have this :

    Release  GA Date   Premier   Extended  Sustaining
                       Support   Support   Support
    -------  --------  --------  --------  ----------
    DB 9.2   Jul 2002  Jul 2007  Jul 2010  Indefinite
    DB 10.1  Jan 2004  Jan 2009  Jan 2012  Indefinite
    DB 10.2  Jul 2005  Jul 2010  Jul 2013  Indefinite

    Sustaining support is less than extended support, you do not get upgrade script and compatibility with new products (ex: connection from 9i client to Oracle 14x is not supported). But it let you keep your very old stuff supported by Oracle.

    Read more :
    Oracle Lifetime Support Policy Datasheet PDF 110K
    Oracle Technical Support Policies PDF 258K

    su in sqlplus

    How to switch user in Oracle ?

    One approach is to change the password :

    SQL> connect / as sysdba
    SQL> select password from dba_users where username='SCOTT';
    SQL> alter user scott identified by abc123;
    User altered.
    SQL> connect scott/abc123
    SQL> create table t...
    SQL> connect / as sysdba
    SQL> alter user scott identified by values 'F894844C34402B67';
    User altered.

    but this is unfair. The user will be prevented from logging for a while, the password expire policy will be reset.

    Sometimes you can simply use

    SQL> alter session set current_schema=scott;
    Session altered.

    but this does not really mean a “su”, because you do not change your privileges. You just change the “default” schema.

    Another approach is to use the BECOME USER privilege and undocumented upicui OCI function. But this will not work in sqlplus.

    Thanks to Jonathan Lewis post today Proxy Users, I could imagine using the proxy functionality in sqlplus to do a su

    SQL> create user su identified by secret123;
    User created.
    SQL> alter user scott grant connect through su;
    User altered.
    SQL> connect su[scott]/secret123;

    The connect username[proxy]/password@db is documented in the 10gR2 SQL*Plus reference

    Set up ovid to use tns with your ldap server

    I had a question in my mailbox today about using TNS resolution with an unsupported LDAP Server like Sun Java System Directory Server.

    Supported in 9i and above are only Microsoft Active Directory and Oracle Internet Directory. In 8i also Novell.

    So I have done this once with OVID.

    1) download Oracle Virtual Directory
    2) Install the OVID on your server (or on a separate server). The latest release is 10.1.4. I used 3.0.3.

    $ sh ./ovid303j.bin -i console
    Preparing to install...
    Enter a uniquely descriptive name for the server. 

    used only by ovid, not related to hostname or whatever

    Server Name (DEFAULT: Virtual Directory 1): 
    Enter the port number on which to provide administrative services. 

    a console port, used only by ovid manager

    Port Number (DEFAULT: 8888): 

    a credential, only for ovid
    Root User DN (DEFAULT: cn=Admin): 
    Enter a port number to provide LDAP services on (e.g. 389, 636).

    this is what will be used by your TNS client, any free port will rule

    Port (DEFAULT: 389): 
    Please enter the base entry of your directory (e.g. o=YourCompany,c=US) 

    not your Server ldap, chose one db domain, here oracle.world.

    Directory Base Suffix[dc=YourCompany,dc=com]: dc=oracle,dc=world

    3) start the OVID
    $ ./vde.sh start
    Starting VDE...

    4) install the OVID manager (GUI-console) on your PC / workstation. I used Windows. It is available for Windows. If you do not have windows, you can edit the XML files directly (good luck)
    5) in the ovid manager, create a new project, add your ovid, and start creating adapters. For each possible DB domain, you must create one adaptor, for example if you try to tnsping LSC01.PROD.DB and you have the description stored as cn=lsc01, cn=oracleContext, cn=PROD.DB, ou=tns, ou=appl, dc=lcsys, dc=ch you will need have
    root: dc=PROD,dc=DB
    remoteBase: cn=PROD.DB,ou=tns,ou=appl,dc=lcsys,dc=ch

    This should work. Not sure if you will like OViD, the interface is pretty awful.

    Also it is not too flexible, you need to add an adaptor for each db domain. Still better than providing a TopDomain for each DB Domain with anonymous access to root.

    Yes, one more point, tns client access the ldap anonymously, so OViD does help to improve security, you set anonymous access to your OViD, you disable ACI on your OViD, and you use a credential to log in to your LDAP. It is safer, because you use a remoteBase, so anonymous access is granted only to that branch of your directory.

    Oracle Magazine January-February 2007

    Check the oracle magazine on otn.oracle.com/oramag

    In the peer-to-peer, you can read more about 3 Oracle Aces, Steve Karam, Nicolas Gasparotto, Dave Moore.

    In the technology columns, three more aces talk about Oracle technlogies. Steven Feuerstein about The Right Place for PL/SQL, Arup Nanda about Recover in a Flash, and Tom Kyte about On Top-n and Pagination Queries. Tom also remind us that group by does not sort. However, if you have one application which used to expect sorted result in 9i and is now produced unexpected result in 10g, you can still read the Note:345048.1 in Metalink to find some workarounds, for example by deactivating hash-group-by with _gby_hash_aggregation_enabled parameter set to false

    I successfully installed 10gR2 patchset 2 on my notebook :

    SQL> select ACTION,VERSION,COMMENTS from registry$history;
    ------- ---------- ------------------------------
    CPU CPUOct2006
    UPGRADE Upgraded from
    SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
    COMP_NAME                           VERSION    STATUS
    ----------------------------------- ---------- -----------
    Oracle Expression Filter   VALID
    Oracle Database Catalog Views VALID
    Oracle Database Packages and Types VALID
    Oracle Database Java Packages VALID
    JServer JAVA Virtual Machine VALID
    Oracle XDK                 VALID

    How Much Is My Blog Worth?

    Inspired by How Much Is My Blog Worth?

    This is all the pain by changing domain, bye bye top position in google, bye bye backlinks…

    My old blog is still active, and I hope to get equivalent ranking until end of 2007. I think it was a smart move, I have now more freedom to customize my site with any content, to edit my feed, my pages, my pictures, etc. I wish I had done it before 😮