Thursday, February 23, 2012

Grid 11g:"Siebel Discovery Agent is not installed at one or more hosts" trying to discover Siebel topology

We installed agents in all our 4 Siebel servers, let's say named sappl1, sappl2, sappl3 and sappl4.
Under Hosts Tab we see they are up.
When we tried to discover Siebel topology, we got errors:
Discovery Summary
Add Enterprise operation is not successful.  Resolve the error messages and then try again

Error Messages
     Siebel Discovery Agent is not installed at one or more hosts
     Discovery From Gateway Server Failed

Trace Messages
     Discovered the Siebel topology from Gateway Server

In our OMS server, in ../gc_inst/em/EMGC_OMS1/sysman/log/emoms.trc, there were these entries:
2012-02-23 09:12:26,891 [Thread-114] ERROR enterprise.TDProcConfig log.625 - doConfiguration: There are some issues with respect to Agents.
2012-02-23 09:12:26,891 [Thread-114] ERROR enterprise.TDProcConfig log.625 - doConfiguration: Agent erros out for host  sapp3 with message : .Siebel Discovery Agent is not installed at one or more hosts
2012-02-23 09:12:26,891 [Thread-114] ERROR enterprise.TDProcConfig log.625 - doConfiguration: Agent erros out for host  sapp2 with message : .Siebel Discovery Agent is not installed at one or more hosts
2012-02-23 09:12:26,891 [Thread-114] ERROR enterprise.TDProcConfig log.625 - doConfiguration: Agent erros out for host  sapp4 with message : .Siebel Discovery Agent is not installed at one or more hosts

Names sapp2, sapp3, sapp4 were not correct.
OMS had discovered the servers as "sapplX".
In Siebel servers' /etc/hosts file the entries were:
10.101.1.17    sapp1        sappl1
10.101.1.18    sapp2        sappl2
10.101.1.19    sapp3        sappl3
10.101.1.20    sapp4        sappl4

We changed the order:
10.101.1.17    sappl1        sblapp1
10.101.1.18    sappl2        sblapp2
10.101.1.19    sappl3        sblapp3
10.101.1.20    sappl4        sblapp4

Discovery will succeed this time.

Find common dependencies between objects using DBA_DEPENDENCIES

Probably, you are familiar with the situation to discover a batch of objects in your database getting invalidated, all of the sudden.
This is an aftermath of a DDL statement executed on an object, that is used, directly or indirectly, by all invalid objects.
Also, this object will, probably, not be invalid, when you discover this situation.
The following query will find an object's dependencies:
SELECT REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE
FROM DBA_DEPENDENCIES
START WITH OWNER = [OBJECT'S OWNER]
AND NAME = [OBJECT'S NAME]
AND TYPE = [OBJECT'S TYPE]
CONNECT BY OWNER = PRIOR REFERENCED_OWNER
AND NAME = PRIOR REFERENCED_NAME
AND TYPE = PRIOR REFERENCED_TYPE;

If you want to find N objects' common dependencies, you should intersect the results of the above query for all objects:
SELECT REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE
FROM DBA_DEPENDENCIES
START WITH OWNER = [OBJECT_1'S OWNER]
AND NAME = [OBJECT_1'S NAME]
AND TYPE = [OBJECT_1'S TYPE]
CONNECT BY OWNER = PRIOR REFERENCED_OWNER
AND NAME = PRIOR REFERENCED_NAME
AND TYPE = PRIOR REFERENCED_TYPE
INTERSECT
SELECT REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE
FROM DBA_DEPENDENCIES
START WITH OWNER = [OBJECT_2'S OWNER]
AND NAME = [OBJECT_2'S NAME]
AND TYPE = [OBJECT_2'S TYPE]
CONNECT BY OWNER = PRIOR REFERENCED_OWNER
AND NAME = PRIOR REFERENCED_NAME
AND TYPE = PRIOR REFERENCED_TYPE
INTERSECT
...
...
...
INTERSECT
SELECT REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE
FROM DBA_DEPENDENCIES
START WITH OWNER = [OBJECT_N'S OWNER]
AND NAME = [OBJECT_N'S NAME]
AND TYPE = [OBJECT_N'S TYPE]
CONNECT BY OWNER = PRIOR REFERENCED_OWNER
AND NAME = PRIOR REFERENCED_NAME
AND TYPE = PRIOR REFERENCED_TYPE;

Now, we should create a procedure to get this result easier.
Fist, we will need 3 tables.

In COMP_OBJS table, you will need to insert all objects' names, types and owners, of which you want to find the common dependencies.
SQL> DESC HELPDESK.COMP_OBJS
 Name                 Null?    Type
 -------------------- -------- -------------
 OBJECT_NAME                   VARCHAR2(128)
 OBJECT_TYPE                   VARCHAR2(30)
 OWNER                         VARCHAR2(30)

In COMP_DEP1 table, the 1st object's dependencies will be stored and in this you will find the common dependencies, after you run the procedure.
SQL> DESC HELPDESK.COMP_DEP1
 Name                 Null?    Type
 -------------------- -------- -------------
 OBJECT_NAME                   VARCHAR2(128)
 OBJECT_TYPE                   VARCHAR2(30)
 OWNER                         VARCHAR2(30)

In COMP_DEP2 table, we will store, one by one, the 2nd to Nth objects' dependencies.
SQL> DESC HELPDESK.COMP_DEP2
 Name                 Null?    Type
 -------------------- -------- -------------
 OBJECT_NAME                   VARCHAR2(128)
 OBJECT_TYPE                   VARCHAR2(30)
 OWNER                         VARCHAR2(30)

After you have created them, grant privileges to SYSTEM.
SQL> GRANT ALL ON HELPDESK.COMP_OBJS TO SYSTEM;
Grant succeeded.
SQL> GRANT ALL ON HELPDESK.COMP_DEP1 TO SYSTEM;
Grant succeeded.
SQL> GRANT ALL ON HELPDESK.COMP_DEP2 TO SYSTEM;
Grant succeeded.

Procedure COMP_DEPS will check if each COMP_DEP1's row exists in COMP_DEP2.
If not, then it will delete this row from COMP_DEP1.
In the end, COMP_DEP1's contents will be the common dependencies of our N objects.
The procedure is:
CREATE OR REPLACE PROCEDURE SYSTEM.COMP_DEPS
IS
   TYPE T_CURSOR IS REF CURSOR;

   V_CURSOR1   T_CURSOR;
   V_CURSOR2   T_CURSOR;
   V_NAME1     VARCHAR2 (128);
   V_TYPE1     VARCHAR2 (30);
   V_OWNER1    VARCHAR2 (30);
   V_NAME2     VARCHAR2 (128);
   V_TYPE2     VARCHAR2 (30);
   V_OWNER2    VARCHAR2 (30);
   V_COUNT1    INTEGER;
   V_COUNT2    INTEGER := 0;
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE HELPDESK.COMP_DEP1';

   EXECUTE IMMEDIATE 'TRUNCATE TABLE HELPDESK.COMP_DEP2';

   OPEN V_CURSOR1 FOR
      SELECT OBJECT_NAME, OBJECT_TYPE, OWNER FROM HELPDESK.COMP_OBJS;

   LOOP
      FETCH V_CURSOR1
      INTO V_NAME1, V_TYPE1, V_OWNER1;

      EXIT WHEN V_CURSOR1%NOTFOUND;

      IF V_COUNT2 = 0
      THEN
         INSERT INTO HELPDESK.COMP_DEP1 (OBJECT_NAME, OBJECT_TYPE, OWNER)
            (    SELECT REFERENCED_NAME, REFERENCED_TYPE, REFERENCED_OWNER
                   FROM DBA_DEPENDENCIES
             START WITH     OWNER = V_OWNER1
                        AND NAME = V_NAME1
                        AND TYPE = V_TYPE1
             CONNECT BY     OWNER = PRIOR REFERENCED_OWNER
                        AND NAME = PRIOR REFERENCED_NAME
                        AND TYPE = PRIOR REFERENCED_TYPE);
         COMMIT;
         V_COUNT2 := 1;
      ELSE
         INSERT INTO HELPDESK.COMP_DEP2 (OBJECT_NAME, OBJECT_TYPE, OWNER)
            (    SELECT REFERENCED_NAME, REFERENCED_TYPE, REFERENCED_OWNER
                   FROM DBA_DEPENDENCIES
             START WITH     OWNER = V_OWNER1
                        AND NAME = V_NAME1
                        AND TYPE = V_TYPE1
             CONNECT BY     OWNER = PRIOR REFERENCED_OWNER
                        AND NAME = PRIOR REFERENCED_NAME
                        AND TYPE = PRIOR REFERENCED_TYPE);
         COMMIT;

         OPEN V_CURSOR2 FOR
            SELECT OBJECT_NAME, OBJECT_TYPE, OWNER FROM HELPDESK.COMP_DEP1;

         LOOP
            FETCH V_CURSOR2
            INTO V_NAME2, V_TYPE2, V_OWNER2;

            EXIT WHEN V_CURSOR2%NOTFOUND;

            SELECT COUNT (*)
              INTO V_COUNT1
              FROM HELPDESK.COMP_DEP2
             WHERE     OBJECT_NAME = V_NAME2
                   AND OBJECT_TYPE = V_TYPE2
                   AND OWNER = V_OWNER2;

            IF (V_COUNT1 = 0)
            THEN
               DELETE FROM HELPDESK.COMP_DEP1
                     WHERE     OBJECT_NAME = V_NAME2
                           AND OBJECT_TYPE = V_TYPE2
                           AND OWNER = V_OWNER2;

               COMMIT;
            END IF;
         END LOOP;

         EXECUTE IMMEDIATE 'TRUNCATE TABLE HELPDESK.COMP_DEP2';

         CLOSE V_CURSOR2;
      END IF;
   END LOOP;

   CLOSE V_CURSOR1;
END COMP_DEPS;
/

Let's do a test.
Initially, we insert into COMP_OBJS table the objects we want to compare:
SQL> SELECT * FROM HELPDESK.COMP_OBJS;

OBJECT_NAMEOBJECT_TYPEOWNER
XXACC_TEST_LOCKBOXPROCEDUREXXACC
AR_BOE_REMIT_RECEIPTS_VVIEWAPPS
DUNPACKAGE BODYDUNNER
XXCRM_PLIROMESVIEWXXE

Let's assume these 4 objects were invalidated simultaneously and we want to find their common dependencies, to see if we can discover on which object a DDL was executed and caused the invalidations.
We execute COMP_DEPS:
SQL> EXEC SYSTEM.COMP_DEPS;

PL/SQL procedure successfully completed.

Let's see the result:
SQL> SELECT * FROM HELPDESK.COMP_DEP1;

OBJECT_NAMEOBJECT_TYPEOWNER
AR_CASH_RECEIPTS_ALLTABLEAR

So, all 4 initial objects share only one dependency, table AR.AR_CASH_RECEIPTS_ALL.
An ALTER statement on this table invalidated all 4 of them.
Now, you can check your audit mechanisms to find out who executed the ALTER.

Thursday, February 16, 2012

Using DBMS_CRYPTO package to encrypt data in a table

We have a trigger, which fires whenever a DDL, GRANT and REVOKE statement is executed and stores some session information in a table. The trigger is under SYS schema:
CREATE OR REPLACE TRIGGER SYS.DDL_MONITOR
   AFTER CREATE OR DROP OR TRUNCATE OR ALTER OR GRANT OR REVOKE
   ON DATABASE
DECLARE
   V_TERMINAL   V$SESSION.TERMINAL%TYPE;
   V_OSUSER     V$SESSION.OSUSER%TYPE;
   V_MACHINE    V$SESSION.MACHINE%TYPE;
   V_IP         VARCHAR2 (20);
   V_MODULE     V$SESSION.MODULE%TYPE;
   V_DATABASE   V$PARAMETER.VALUE%TYPE;
   CRLF         VARCHAR2 (2) := CHR (13) || CHR (10);
BEGIN
   SELECT VALUE
     INTO V_DATABASE
     FROM V$PARAMETER
    WHERE NAME = 'db_name';

   V_OSUSER := SYS_CONTEXT ('USERENV', 'OS_USER');
   V_MACHINE := SYS_CONTEXT ('USERENV', 'TERMINAL');
   V_IP := SYS_CONTEXT ('USERENV', 'IP_ADDRESS');

   INSERT INTO HELPDESK.S_DDL_MONITOR (DATE_TIME,
                                       DDL_EVENT,
                                       OS_USER,
                                       MACHINE)
        VALUES (
                  SYSDATE,
                     'Username: ('
                  || ORA_LOGIN_USER
                  || ')   '
                  || 'Action: ('
                  || ORA_SYSEVENT
                  || ')   '
                  || 'Object: ('
                  || ORA_DICT_OBJ_OWNER
                  || '.'
                  || ORA_DICT_OBJ_NAME
                  || ')   '
                  || 'Type: ('
                  || ORA_DICT_OBJ_TYPE
                  || ')',
                  V_OSUSER,
                  V_IP || ' (' || V_MACHINE || ')');
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/

The table, in which information is stored is:
SQL> DESC HELPDESK.S_DDL_MONITOR

 Name                    Null? Type
 ----------------------- ----- -------------
 DATE_TIME                     DATE
 DDL_EVENT                     VARCHAR2(500)
 OS_USER                       VARCHAR2(30)
 MACHINE                       VARCHAR2(150)

Some users have the SELECT ANY TABLE system privilege and we cannot revoke it from them.
But we do not want them to read these data. So, we decided to encrypt all columns in this table, except DATE_TIME.
We will use DBMS_CRYPTO package to achieve this. All procedures we will create will be under SYSTEM schema, so, as SYSDBA, grant execute to the package:
SQL> GRANT EXECUTE ON SYS.DBMS_CRYPTO TO SYSTEM;

Grant succeeded.

First, we will need an encryption key. Obviously, this key should be stored and not to be given to anyone not trusted.
SQL> SELECT DBMS_CRYPTO.RANDOMBYTES(24) FROM DUAL;

DBMS_CRYPTO.RANDOMBYTES(24)
------------------------------------------------
FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A

The following function converts a string to its encrypted value, using the key we just generated.
The encryption key should be stored in the function's code and not be passed as an input variable.
CREATE OR REPLACE FUNCTION SYSTEM.ENC_VARCHAR (V_VARCHAR IN VARCHAR2)
   RETURN RAW
IS
   V_ENC_VARCHAR   RAW(500);
   V_KEY           RAW(24) := 'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A';
   V_MOD           NUMBER
      :=   SYS.DBMS_CRYPTO.ENCRYPT_AES
         + SYS.DBMS_CRYPTO.CHAIN_CBC
         + SYS.DBMS_CRYPTO.PAD_PKCS5;
BEGIN
   V_ENC_VARCHAR :=
      SYS.DBMS_CRYPTO.ENCRYPT (UTL_RAW.CAST_TO_RAW (V_VARCHAR), V_MOD, V_KEY);
   RETURN V_ENC_VARCHAR;
END;
/

The next function takes the encrypted value and the key (in this case, it should be passed as an input variable) and returns the original string:
CREATE OR REPLACE FUNCTION SYSTEM.DEC_VARCHAR (V_RAW IN RAW, V_KEY IN RAW)
   RETURN VARCHAR2
IS
   V_RET       VARCHAR2 (500);
   V_DEC_RAW   RAW (500);
   V_MOD       NUMBER
      :=   DBMS_CRYPTO.ENCRYPT_AES
         + DBMS_CRYPTO.CHAIN_CBC
         + DBMS_CRYPTO.PAD_PKCS5;
BEGIN
   V_DEC_RAW := DBMS_CRYPTO.DECRYPT (V_RAW, V_MOD, V_KEY);
   V_RET := UTL_RAW.CAST_TO_VARCHAR2 (V_DEC_RAW);
   RETURN V_RET;
END;
/

We should wrap these functions, so their code and, most importantly, the encryption key will not be readable by anyone:
SQL> DECLARE
    BEGIN
DBMS_DDL.CREATE_WRAPPED('CREATE OR REPLACE FUNCTION SYSTEM.ENC_VARCHAR (V_VARCHAR IN VARCHAR2)
   RETURN RAW
IS
   V_ENC_VARCHAR   RAW(500);
   V_KEY           RAW(24) := ''FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A'';
   V_MOD           NUMBER
      :=   SYS.DBMS_CRYPTO.ENCRYPT_AES
         + SYS.DBMS_CRYPTO.CHAIN_CBC
         + SYS.DBMS_CRYPTO.PAD_PKCS5;
BEGIN
   V_ENC_VARCHAR :=
      SYS.DBMS_CRYPTO.ENCRYPT (UTL_RAW.CAST_TO_RAW (V_VARCHAR), V_MOD, V_KEY);
   RETURN V_ENC_VARCHAR;
  END;');
END;
/
PL/SQL procedure successfully completed.

SQL> DECLARE
    BEGIN
DBMS_DDL.CREATE_WRAPPED('CREATE OR REPLACE FUNCTION SYSTEM.DEC_VARCHAR (V_RAW IN RAW, V_KEY IN RAW)
   RETURN VARCHAR2
IS
   V_RET       VARCHAR2 (500);
   V_DEC_RAW   RAW (500);
   V_MOD       NUMBER
      :=   DBMS_CRYPTO.ENCRYPT_AES
         + DBMS_CRYPTO.CHAIN_CBC
         + DBMS_CRYPTO.PAD_PKCS5;
BEGIN
   V_DEC_RAW := DBMS_CRYPTO.DECRYPT (V_RAW, V_MOD, V_KEY);
   V_RET := UTL_RAW.CAST_TO_VARCHAR2 (V_DEC_RAW);
   RETURN V_RET;
END;');
END;
/
PL/SQL procedure successfully completed.

Check, if we can see the code:
SQL> SELECT DBMS_METADATA.GET_DDL('FUNCTION','ENC_VARCHAR','SYSTEM') FROM DUAL;

DBMS_METADATA.GET_DDL('FUNCTION','ENC_VARCHAR','SYSTEM')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FUNCTION "SYSTEM"."ENC_VARCHAR" wrapped
a000000
25
abcd
ab

SQL> SELECT DBMS_METADATA.GET_DDL('FUNCTION','DEC_VARCHAR','SYSTEM') FROM DUAL;

DBMS_METADATA.GET_DDL('FUNCTION','DEC_VARCHAR','SYSTEM')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FUNCTION "SYSTEM"."DEC_VARCHAR" wrapped
a000000
25
abcd
ab

We check, if they work, by encrypting and decrypting string "This is a test".
SQL> SELECT SYSTEM.ENC_VARCHAR('This is a test') FROM DUAL;

SYSTEM.ENC_VARCHAR('THISISATEST')
----------------------------------
10009EB5AA82E2D10047A7B662720E29

SQL> SELECT SYSTEM.DEC_VARCHAR (
          '10009EB5AA82E2D10047A7B662720E29',
          'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
  FROM DUAL; 

SYSTEM.DEC_VARCHAR(
-------------------
This is a test

And if we use a wrong key:
SQL> SELECT SYSTEM.DEC_VARCHAR (
          '10009EB5AA82E2D10047A7B662720E29',
          'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2B')
  FROM DUAL;
SELECT SYSTEM.DEC_VARCHAR (
       *
ERROR at line 1:
ORA-28817: PL/SQL function returned an error.
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67
ORA-06512: at "SYS.DBMS_CRYPTO", line 41
ORA-06512: at "SYSTEM.DEC_VARCHAR", line 11

Now, as SYSDBA we change the trigger to use our encryption function:
CREATE OR REPLACE TRIGGER SYS.DDL_MONITOR
   AFTER CREATE OR DROP OR TRUNCATE OR ALTER OR GRANT OR REVOKE
   ON DATABASE
DECLARE
   V_TERMINAL        V$SESSION.TERMINAL%TYPE;
   V_OSUSER          V$SESSION.OSUSER%TYPE;
   V_MACHINE         V$SESSION.MACHINE%TYPE;
   V_IP              VARCHAR2 (20);
   V_MODULE          V$SESSION.MODULE%TYPE;
   V_DATABASE        V$PARAMETER.VALUE%TYPE;
   CRLF              VARCHAR2 (2) := CHR (13) || CHR (10);
   V_DDL_EVENT       VARCHAR2 (500);
   V_DDL_EVENT_ENC   RAW (500);
   V_OSUSER_ENC      RAW (500);
   V_MACHINE2        VARCHAR2 (150);
   V_MACHINE_ENC     RAW (500);
BEGIN
   SELECT VALUE
     INTO V_DATABASE
     FROM V$PARAMETER
    WHERE NAME = 'db_name';

   V_OSUSER := SYS_CONTEXT ('USERENV', 'OS_USER');
   V_MACHINE := SYS_CONTEXT ('USERENV', 'TERMINAL');
   V_IP := SYS_CONTEXT ('USERENV', 'IP_ADDRESS');
   V_DDL_EVENT :=
         'Username: ('
      || ORA_LOGIN_USER
      || ')   '
      || 'Action: ('
      || ORA_SYSEVENT
      || ')   '
      || 'Object: ('
      || ORA_DICT_OBJ_OWNER
      || '.'
      || ORA_DICT_OBJ_NAME
      || ')   '
      || 'Type: ('
      || ORA_DICT_OBJ_TYPE
      || ')';
   V_MACHINE2 := V_IP || ' (' || V_MACHINE || ')';
   V_DDL_EVENT_ENC := SYSTEM.ENC_VARCHAR (V_DDL_EVENT);
   V_OSUSER_ENC := SYSTEM.ENC_VARCHAR (V_OSUSER);
   V_MACHINE_ENC := SYSTEM.ENC_VARCHAR (V_MACHINE2);

   INSERT INTO HELPDESK.S_DDL_MONITOR (DATE_TIME,
                                       DDL_EVENT,
                                       OS_USER,
                                       MACHINE)
        VALUES (SYSDATE,
                UTL_RAW.CAST_TO_VARCHAR2 (V_DDL_EVENT_ENC),
                UTL_RAW.CAST_TO_VARCHAR2 (V_OSUSER_ENC),
                UTL_RAW.CAST_TO_VARCHAR2 (V_MACHINE_ENC));
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/

We execute a few DDL statements and query the table:
SQL> SELECT DATE_TIME, DDL_EVENT, OS_USER, MACHINE FROM HELPDESK.S_DDL_MONITOR;
  
DATE_TIMEDDL_EVENTOS_USERMACHINE
16-FEB-12ξΎΉ‰G« ω―ζ œCXά ’fƒp­0CΡηΌE)b¤Ιΐ ¨ψ‰ψ’ ψζˆ,κΣ¤ ί¥L—£pμί¦eΦ&ΚY*<<ό©¶Y /]― C@ˆιCc;d«>8υRπ`WXΪ,¥ΔLg…£JmΜ B»βν¬žciβ k|![•±Ώβ 6·Ζτ
16-FEB-12ξΎΉ‰G« ω―z fs2¥nEc+p)Ι ψΈR8¨iCT+&P0Y Ω˜Ε gΙ t*¶tΰΗkΩγΎ bιƒΦ Φ‡kΖ _z™ώΈ˜;ςpŒ/ϋ #uKλΐΘd«>8υRπ`WXΪ,¥ΔLg…£JmΜ B»βν¬žciβ k|![•±Ώβ 6·Ζτ

All info in this table, except the DATE_TIME column, is encrypted and unreadable without the encryption key.
Let's use it to decrypt our data:
SQL> SELECT DATE_TIME,
          DEC_VARCHAR (UTL_RAW.CAST_TO_RAW (DDL_EVENT),
                    'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
          "DDL_EVENT",
       DEC_VARCHAR (UTL_RAW.CAST_TO_RAW (OS_USER),
                    'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
          "OS_USER",
       DEC_VARCHAR (UTL_RAW.CAST_TO_RAW (MACHINE),
                    'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
          "MACHINE"
  FROM HELPDESK.S_DDL_MONITOR;
  
DATE_TIMEDDL_EVENTOS_USERMACHINE
16-FEB-12Username: (SYSTEM)   Action: (TRUNCATE)   Object: (HELPDESK.S_DDL_MONITOR)   Type: (TABLE)etsekouras10.100.25.65 (ETSEKOUR)
16-FEB-12Username: (SYSTEM)   Action: (ALTER)   Object: (APPS.ZEBRAGETPRINTERFROMXML)   Type: (FUNCTION)etsekouras10.100.25.65 (ETSEKOUR)

If you want to query with a WHERE clause in an encrypted column, e.g. OS_USER, you can:
SELECT DATE_TIME,
       DEC_VARCHAR (UTL_RAW.CAST_TO_RAW (DDL_EVENT),
                    'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
          "DDL_EVENT",
       DEC_VARCHAR (UTL_RAW.CAST_TO_RAW (OS_USER),
                    'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
          "OS_USER",
       DEC_VARCHAR (UTL_RAW.CAST_TO_RAW (MACHINE),
                    'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
          "MACHINE"
  FROM HELPDESK.S_DDL_MONITOR
 WHERE UPPER (
          DEC_VARCHAR (UTL_RAW.CAST_TO_RAW (OS_USER),
                       'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')) =
          'ETSEKOURAS';

Thursday, February 2, 2012

Backend WLS or EM application seems to be down

A few days ago, the server, where OMS and its DB reside, crushed.
After we restarted it, whenever we tried to access the grid console, we got error "Backend WLS or EM application seems to be down".
Agents failed to upload XML files to OMS and "emctl pingOMS" was giving an error "EMD pingOMS error: No response header from OMS".
We checked WebLogic and OMS .trc, .log and .out files, but there was no error recorded, neither before nor after the crush.

To correct this issue:

    1. Stop OMS
     emctl stop oms -all

    2. Kill -9 all WebLogic and OMS processes still running after the stop. You can find these processes, using ps.
     ps -ef | grep EMGC_ADMINSERVER
     ps -ef | grep EMGC_OMS1
     ps -ef | grep oms

    3. Delete every .lok file you find under WebLogic Domain
     find . -name "*.lok"

    These files were:
    ../gc_inst/user_projects/domains/GCDomain/config/config.lok
    ../gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/data/ldap/ldapfiles/EmbeddedLDAP.lok
    ../gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/EMGC_OMS1.lok
    ../gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/data/ldap/ldapfiles/EmbeddedLDAP.lok
    ../gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/tmp/EMGC_ADMINSERVER.lok

    4. Start OMS
     emctl start oms

The best matching Oracle Documents about this incident are:
  • ID 943790.1: What are the .lok Files Used For in a WebLogic Server (WLS) Domain? In general, these files are a mechanism to ensure file and server locks and to prevent a server from being booted twice.
  • ID 957377.1: Weblogic Fails To Start With Error "Unable To Obtain Lock"
  • ID 1235753.1: 11g Grid Control: OMS Startup Shows "AdminServer Could Not Be Started" but OMS is able to Startup
Another cause for this error could be incorrect entries in your /etc/hosts file.
Your 127.0.0.1 entry should be exactly like:
127.0.0.1    localhost.localdomain    localhost

And you should remove any entry about IPv6, so delete or comment:
::1    localhost6.localdomain6    localhost6