Skip to Main Content

Enterprise Manager

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

OMS doesn't start - can't connect to database

EdSpnOct 10 2013 — edited Nov 4 2013

Hi all,

My dev/test environment won't start OMS anymore. This really has us stopped in our tracks...

It will start the WebTier, but OMS won't come up:

[oracle@ora-emcc log]$ $OMS_HOME/bin/emctl status oms

Oracle Enterprise Manager Cloud Control 12c Release 2

Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.

WebTier is Up

Oracle Management Server is not functioning because of the following reason:

Failed to connect to repository database. OMS will be automatically restarted once it identifies that database and listener are up.

Trying to check oms details results in an error as well:

[oracle@ora-emcc config]$ $OMS_HOME/bin/emctl status oms -details -sysman_pwd $SYSMAN_PWD

Oracle Enterprise Manager Cloud Control 12c Release 2

Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.

Exception in thread "main" java.util.MissingResourceException: Can't find bundle for base name oracle.security.jps.internal.common.resources.keystore.KeyStoreResources, locale

        at java.util.ResourceBundle.throwMissingResourceException(ResourceBundle.java:1427)

        at java.util.ResourceBundle.getBundleImpl(ResourceBundle.java:1250)

        at java.util.ResourceBundle.getBundle(ResourceBundle.java:952)

        at oracle.security.jps.util.JpsBundle.<init>(JpsBundle.java:70)

        at oracle.security.jps.util.JpsBundle.getResourceBundle(JpsBundle.java:151)

        at oracle.security.jps.util.JpsBundle.getResourceBundle(JpsBundle.java:106)

        at oracle.security.jps.JpsLocalizableException.getResourceBundle(JpsLocalizableException.java:157)

        at oracle.security.jps.JpsLocalizableException.getLocalizedMessage(JpsLocalizableException.java:140)

        at oracle.security.jps.JpsLocalizableException.getLocalizedMessage(JpsLocalizableException.java:131)

        at java.lang.Throwable.toString(Throwable.java:343)

        at java.lang.String.valueOf(String.java:2826)

        at java.lang.StringBuilder.append(StringBuilder.java:115)

        at java.lang.Throwable.printStackTraceAsCause(Throwable.java:536)

        at java.lang.Throwable.printStackTraceAsCause(Throwable.java:545)

        at java.lang.Throwable.printStackTraceAsCause(Throwable.java:545)

        at java.lang.Throwable.printStackTrace(Throwable.java:516)

        at org.apache.log4j.spi.ThrowableInformation.getThrowableStrRep(ThrowableInformation.java:59)

        at org.apache.log4j.spi.LoggingEvent.getThrowableStrRep(LoggingEvent.java:342)

        at org.apache.log4j.WriterAppender.subAppend(WriterAppender.java:304)

        at org.apache.log4j.RollingFileAppender.subAppend(RollingFileAppender.java:234)

        at org.apache.log4j.WriterAppender.append(WriterAppender.java:159)

        at org.apache.log4j.AppenderSkeleton.doAppend(AppenderSkeleton.java:230)

        at org.apache.log4j.helpers.AppenderAttachableImpl.appendLoopOnAppenders(AppenderAttachableImpl.java:65)

        at org.apache.log4j.Category.callAppenders(Category.java:203)

        at org.apache.log4j.Category.forcedLog(Category.java:388)

        at org.apache.log4j.Category.error(Category.java:319)

        at oracle.sysman.emctl.oms.PrintOMSSetupInfo.main(PrintOMSSetupInfo.java:240)

In the WebLogic Admin Console, I can see that BIP, EMGC_ADMINSERVER and EMGC_OMS1 are all in state Running and health Ok.

Our OMS and OMR servers are (and have always been) 2 different machines. The database on OMR has always been (and is currently) up and available.

Checking that I can connect to the repository database on OMR from the OMS server:

[oracle@ora-emcc ~]$ sqlplus sysman/$SYSMAN_PWD@ora-emccdb:1521/emccdb

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Oct 10 23:34:53 2013

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Any suggestions will be much appreciated.

Ed

This post has been answered by EdSpn on Nov 4 2013
Jump to Answer

Comments

Frank Kulash
Answer

Hi,

Here's one way:

SELECT DISTINCT

          d.*

,         NVL2 ( o.rail_no

               , 'INVALID'  -- or 'UNVALID', if you really want that

               , 'VALID'

               )    AS check_row

FROM             t_plan  d

LEFT OUTER JOIN  t_plan  o  ON   o.rail_no    =  d.rail_no

                            AND  o.time_from  <= d.time_until

                            AND  o.time_until >= d.time_from

ORDER BY  d.time_from;

If you'd care to post INSERT statements for your sample data, then I could test this.

Two ranges overlap if (and only if) each one begins before the other one ends.  This will be more clear if you consider that two ranges do not overlap if (and only if) one of them begins after the other one ends.

This solution assumes that time_from <= time_until on every row.  Is that true?  For example, if a rail is in use at midnight, could you have (for example) time_from='23:55' and time_intil='00:05' on the same row?

Marked as Answer by Tobias Arnhold · Sep 27 2020
RogerT

and another way...

WITH d AS (SELECT 172 train, to_date('07:06','hh24:mi') AS sd, to_date('15:50','hh24:mi') AS ed, 1 rail FROM dual UNION ALL

           select 120,to_date('07:33','hh24:mi'),to_date('15:41','hh24:mi') as ed, 2 rail from dual union all

           select 229,to_date('07:56','hh24:mi'),to_date('14:52','hh24:mi') as ed, 3 rail from dual union all

           SELECT 217,to_date('15:05','hh24:mi'),to_date('18:47','hh24:mi') as ed, 1 rail from dual union all

           select 189,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 2 rail from dual union all

           SELECT 185,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 3 rail from dual union all

           SELECT 181,to_date('20:10','hh24:mi'),to_date('23:53','hh24:mi') AS ed, 2 rail FROM dual UNION ALL

           SELECT 142,to_date('20:14','hh24:mi'),to_date('23:32','hh24:mi') AS ed, 3 rail FROM dual)

SELECT train, rail, sd, ed, prev_ed, next_sd

     , CASE WHEN (    (prev_ed IS NULL OR sd > prev_ed)

                  and (next_sd is null or ed < next_sd))

            THEN 'VALID'

            ELSE 'INVALID'

       end as validity

  from (SELECT train, rail

            , to_number(to_char(sd,'hh24mi')) AS sd

            , to_number(to_char(ed,'hh24mi')) AS ed

            , to_number(to_char(lag(ed) OVER (PARTITION BY rail ORDER BY sd),'hh24mi')) prev_ed

            , to_number(to_char(lead(sd) OVER (PARTITION BY rail ORDER BY sd),'hh24mi')) next_sd

           from d)          

Frank Kulash

Hi,

LEAD and LAG can't do this job.  LEAD and LAG can only compare a row to 1 neighboring row.  In this problem (if I understand it correctly) we need to compare each row to all rows with the same rail_no, not just the neighboring rows.  For example, if, on the same rail_no we had ranges like this:

01:00   02:00

01:05   01:10

01:15   01:20

01:25   01:30

we would want to say that they were all invalid, since the first row (01:00-02:00) overlaps each of the others.

RogerT

Good point

Nimish Garg

Try this

WITH t AS

(

  SELECT 172 TRAIN_NO, to_date('07:06','hh24:mi') AS TIME_FROM, to_date('15:50','hh24:mi') AS TIME_UNTIL    , 1 RAIL_NO FROM dual UNION ALL

  select 120,to_date('07:33','hh24:mi'),to_date('15:41','hh24:mi') as ed, 2 rail from dual union all

  select 229,to_date('07:56','hh24:mi'),to_date('14:52','hh24:mi') as ed, 3 rail from dual union all

  SELECT 217,to_date('15:05','hh24:mi'),to_date('18:47','hh24:mi') as ed, 1 rail from dual union all

  select 189,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 2 rail from dual union all

  SELECT 185,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 3 rail from dual union all

  SELECT 181,to_date('20:10','hh24:mi'),to_date('23:53','hh24:mi') AS ed, 2 rail FROM dual UNION ALL

  SELECT 142,to_date('20:14','hh24:mi'),to_date('23:32','hh24:mi') AS ed, 3 rail FROM dual

)

select

  t.TRAIN_NO,

  t.TIME_FROM,

  t.TIME_UNTIL,

  t.RAIL_NO,

  decode(t2.TRAIN_NO,NULL,'VALID','INVALID') CHECK_ROW

from

  t,

  (

    select

      t1.TRAIN_NO

    from

      t t1, t t2

    where

      t1.RAIL_NO = t2.RAIL_NO

      and t1.TIME_FROM <= t2.TIME_UNTIL

      and t1.TIME_UNTIL >= t2.TIME_FROM

      and t1.TRAIN_NO <> t2.TRAIN_NO

  ) t2

  where t.TRAIN_NO = t2.TRAIN_NO (+)

  order by TIME_FROM

Partha Sarathy S

WITH T_PLAN(TRAIN_NO  ,  TIME_FROM ,   TIME_UNTIL ,   RAIL_NO) AS (

SELECT 172,'07:06','15:50', 1 FROM DUAL UNION ALL

SELECT 120,'07:33','15:41', 2 FROM DUAL UNION ALL

SELECT 229,'07:56','14:52', 3 FROM DUAL UNION ALL

SELECT 217,'15:05','18:47', 1 FROM DUAL UNION ALL

SELECT 189,'16:10','19:53', 2 FROM DUAL UNION ALL

SELECT 185,'16:10','19:53', 3 FROM DUAL UNION ALL

SELECT 181,'20:10','23:53', 2 FROM DUAL UNION ALL

SELECT 142,'20:14','23:32', 3 FROM DUAL)

SELECT T.TRAIN_NO,

       T.TIME_FROM,

       T.TIME_UNTIL,

       T.RAIL_NO,

       CASE WHEN (T.TRAIN_NO IN (SELECT T1.TRAIN_NO FROM T_PLAN T1,

     T_PLAN T2

WHERE (TO_TIMESTAMP(T1.TIME_FROM,'HH24:MI') BETWEEN TO_TIMESTAMP(T2.TIME_FROM,'HH24:MI') AND TO_TIMESTAMP(T2.TIME_UNTIL,'HH24:MI')

OR    TO_TIMESTAMP(T1.TIME_UNTIL,'HH24:MI') BETWEEN TO_TIMESTAMP(T2.TIME_FROM,'HH24:MI') AND TO_TIMESTAMP(T2.TIME_UNTIL,'HH24:MI'))

AND T1.TRAIN_NO != T2.TRAIN_NO

AND T1.RAIL_NO = T2.RAIL_NO)) THEN 'INVALID' ELSE 'VALID' END AS CHECK_NOW

FROM T_PLAN T;

OUTPUT:

  TRAIN_NO TIME_ TIME_    RAIL_NO CHECK_N
---------- ----- ----- ---------- -------
       172 07:06 15:50          1 INVALID
       120 07:33 15:41          2 VALID
       229 07:56 14:52          3 VALID
       217 15:05 18:47          1 INVALID
       189 16:10 19:53          2 VALID
       185 16:10 19:53          3 VALID
       181 20:10 23:53          2 VALID
       142 20:14 23:32          3 VALID

8 rows selected.

Message was edited by: Parth272025

Stew Ashton

I agree that LAG and LEAD are not enough, but it can be done with other analytical functions:

WITH d AS (

  SELECT 172 train, to_date('07:06','hh24:mi') AS sd, to_date('15:50','hh24:mi') AS ed, 1 rail FROM dual UNION ALL

  select 120,to_date('07:33','hh24:mi'),to_date('15:41','hh24:mi') as ed, 2 rail from dual union all

  select 229,to_date('07:56','hh24:mi'),to_date('14:52','hh24:mi') as ed, 3 rail from dual union all

  SELECT 217,to_date('15:05','hh24:mi'),to_date('18:47','hh24:mi') as ed, 1 rail from dual union all

  select 189,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 2 rail from dual union all

  SELECT 185,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 3 rail from dual union all

  SELECT 181,to_date('20:10','hh24:mi'),to_date('23:53','hh24:mi') AS ed, 2 rail FROM dual UNION ALL

  SELECT 142,to_date('20:14','hh24:mi'),to_date('23:32','hh24:mi') AS ed, 3 rail FROM dual

)

select train, sd, ed, rail,

case when ed > next_sd or sd < prev_ed then 'INVALID'

     else 'VALID'

end check_row

from (

  select d.*,

  min(sd) over(partition by rail order by sd rows between 1 following and unbounded following) next_sd,

  max(ed) over(partition by rail order by ed rows between unbounded preceding and 1 preceding) prev_ed

  from d

)

order by sd;

TRAINSDEDRAILCHECK_ROW
1722014-02-01 07:06:002014-02-01 15:50:001INVALID
1202014-02-01 07:33:002014-02-01 15:41:002VALID
2292014-02-01 07:56:002014-02-01 14:52:003VALID
2172014-02-01 15:05:002014-02-01 18:47:001INVALID
1892014-02-01 16:10:002014-02-01 19:53:002VALID
1852014-02-01 16:10:002014-02-01 19:53:003VALID
1812014-02-01 20:10:002014-02-01 23:53:002VALID
1422014-02-01 20:14:002014-02-01 23:32:003VALID
Tobias Arnhold

Thanks. Exactly what I was looking for.

Stew Ashton

Here's an alternative for when we all have 12c. I added a line to create multiple overlaps.

WITH d AS (

  SELECT 172 train, to_date('07:06','hh24:mi') AS sd, to_date('15:50','hh24:mi') AS ed, 1 rail FROM dual UNION ALL

  select 120,to_date('07:33','hh24:mi'),to_date('15:41','hh24:mi') as ed, 2 rail from dual union all

  select 229,to_date('07:56','hh24:mi'),to_date('14:52','hh24:mi') as ed, 3 rail from dual union all

  SELECT 217,to_date('15:05','hh24:mi'),to_date('18:47','hh24:mi') as ed, 1 rail from dual union all

  SELECT 218,to_date('15:10','hh24:mi'),to_date('19:47','hh24:mi') as ed, 1 rail from dual union all

  select 189,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 2 rail from dual union all

  SELECT 185,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 3 rail from dual union all

  SELECT 181,to_date('20:10','hh24:mi'),to_date('23:53','hh24:mi') AS ed, 2 rail FROM dual UNION ALL

  SELECT 142,to_date('20:14','hh24:mi'),to_date('23:32','hh24:mi') AS ed, 3 rail FROM dual

)

select train, sd, ed, rail,

nvl2(mn, 'INVALID', 'VALID') check_row

from d

match_recognize(

  partition by rail order by sd

  measures match_number() mn

  all rows per match with unmatched rows

  pattern (a b+)

  define b as b.sd < a.ed

)

order by sd;

TRAINSDEDRAILCHECK_ROW
1722014-02-01 07:06:002014-02-01 15:50:001INVALID
1202014-02-01 07:33:002014-02-01 15:41:002VALID
2292014-02-01 07:56:002014-02-01 14:52:003VALID
2172014-02-01 15:05:002014-02-01 18:47:001INVALID
2182014-02-01 15:10:002014-02-01 19:47:001INVALID
1892014-02-01 16:10:002014-02-01 19:53:002VALID
1852014-02-01 16:10:002014-02-01 19:53:003VALID
1812014-02-01 20:10:002014-02-01 23:53:002VALID
1422014-02-01 20:14:002014-02-01 23:32:003VALID
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 2 2013
Added on Oct 10 2013
9 comments
24,433 views