This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Oct 30, 2012 7:04 AM by BrianP RSS

Increased repository DB redo logging after EM12cR2 upgrade?

BrianP Journeyer
Currently Being Moderated
Has anyone else noticed increased redo logging in their repository database after an upgrade to EM12cR2?

I'm running on Linux x86-64 with an 11.2.0.3 repository database. Prior to the upgrade I was running 12.1.0.1.0 with BP1, and my repository database was producing, on average, about 80-120MB of archived redo logs per hour. Since the upgrade to 12.1.0.2.0, my repository database is producing, on average, 450-500MB of archived redo logs per hour.

Upgrading the deployed agents from 12.1.0.1.0 to 12.1.0.2.0 hasn't seemed to help; I've upgraded about 10 of 15 without any apparent decrease in redo logging.

Just wondering if this is comparable to what others are seeing or if I have a local issue I should investigate.
  • 1. Re: Increased repository DB redo logging after EM12cR2 upgrade?
    Akanksha Sheoran Kaler Pro
    Currently Being Moderated
    Hi Brian, i have worked with lot of customer on Upgarde to EM 12.1.0.2 and non of them has reported this But i suggets you to open and SR and let support have a look at the this.
  • 2. Re: Increased repository DB redo logging after EM12cR2 upgrade?
    BrianP Journeyer
    Currently Being Moderated
    Hi Akanksha,

    Thank you for confirming that this is not expected behavior. I've opened an SR, and will post an update here once I have more information.
  • 3. Re: Increased repository DB redo logging after EM12cR2 upgrade?
    BrianP Journeyer
    Currently Being Moderated
    Just posting an update, and still wondering if anybody else out there is experiencing this. My repository database continues to produce significantly increased amounts of redo logging since the EM12cR2 upgrade. This site has fewer than 200 monitored targets.

    I filed this as SR 3-6242142021 (sev 4). The tech advised me to run LogMiner and upload the output, which I've done, though I haven't received any followup. I'm giving the tech time to work since things are still running OK despite the increased redo logging.

    I ran LogMiner against about 20 minutes (200MB+ of logs) worth of repository database operation. It seems like SYSMAN is doing a ton of inserts on EM_JOB_METRICS, about 60 inserts per second:
    insert into "SYSMAN"."EM_JOB_METRICS"("SOURCE","LOG_TIMESTAMP","METRIC_NAME","METRIC_QUALIFIER","METRIC_VALUE") values 
    ('omshost.domain.com:4890_Management_Service',TO_TIMESTAMP('26-SEP-12 03.09.25.215415 PM'),'DISP_REQ_STEPS','0','25');
    This keeps running as a series of 10 inserts all with the same timestamp, 6 times a second, and the exact same sequence of 10 sets of distinct values for METRIC_QUALIFIER and METRIC_VALUE:
    '0', '25'
    '0', '0'
    '1', '12'
    '1', '0'
    '2', '25'
    '2', '0'
    '3', '10'
    '3', '0'
    '4', '10'
    '4', '0'
       SQL> select seg_owner, operation, count(*) from v$logmnr_contents group by seg_owner, operation order by 1;
                                                  
                                                  SEG_OWNER     OPERATION     COUNT(*)  
                                                   DBSNMP        DELETE             13  
                                                   DBSNMP        INSERT             13  
                                                   DBSNMP        UPDATE              4  
                                                    RCAT         DELETE            269  
                                                    RCAT         INSERT           1372  
                                                    RCAT    SELECT_FOR_UPDATE       50  
                                                    RCAT         UPDATE            884  
                                                     SYS         DELETE             88  
                                                     SYS         INSERT           1041
                                                     SYS    SELECT_FOR_UPDATE      265
                                                     SYS         UPDATE           4802  
                                                   SYSMAN        DELETE          97325  
                                                   SYSMAN        INSERT         106610  
                                                   SYSMAN       INTERNAL           196  
                                                   SYSMAN       LOB_TRIM             5  
                                                   SYSMAN       LOB_WRITE          326  
                                                   SYSMAN   SELECT_FOR_UPDATE     1203  
                                                   SYSMAN    SEL_LOB_LOCATOR       103  
                                                   SYSMAN      UNSUPPORTED       13626  
                                                   SYSMAN        UPDATE          21808
                                                   UNKNOWN       DELETE             82  
                                                   UNKNOWN       INSERT             80  
                                                   UNKNOWN  SELECT_FOR_UPDATE       65
                                                   UNKNOWN       UPDATE             65
                                                                 COMMIT          19792
                                                              DPI SAVEPOINT         15
                                                                INTERNAL        124178
                                                                ROLLBACK            33
                                                                  START          19825
       SQL> select TABLE_NAME, count(*) from v$logmnr_contents group by table_name order by 2 desc;
                                                          
                                                           TABLE_NAME           COUNT(*)
                                                         EM_JOB_METRICS           201740
                                                                                  163843
                                                        EM_METRIC_ITEMS            13374
                                                        EM_METRIC_VALUES           12524
                                                    MGMT_AVAILABILITY_MARKER        3845
                                                           COL_USAGE$               2403
                                                              ROUT                  1453
                                                         SCHEDULER$_JOB             1067
    (remainder trimmed)
    My OMS generally runs a user job once every couple minutes, so I'm not sure what would be causing all these inserts into EM_JOB_METRICS. I'm going to grab the latest omsvfy, repvfy and agtvfy to see if those can shed any light on the issue.


    Edit to add: Logged as BUG 14726136 - INCREASED REDO LOGGING ON REPOSITORY DB AFTER UPGRADING TO OEM 12.1.0.2

    And thanks to the invisible Oracle folks monitoring the forums and logging bugs!

    Edited by: BrianP on Oct 5, 2012 2:25 PM
  • 4. Re: Increased repository DB redo logging after EM12cR2 upgrade?
    966707 Newbie
    Currently Being Moderated
    Brian,

    I confirm, I have exactly the same issue. Before upgrade it was Oracle RDBMS 11.2.0.1, OEM 12.1.0.1, I did upgrade of RDBMS to 11.2.0.3 and OEM to 12.1.0.2, after few days of running new configuration the whole filesystem was 100% full. Everything related to em_job_metrics. Looks like a new bug, introduced. SR has been logged to Oracle.

    However, I have to admit, I have another environment where I did installation from scratch. I did installation of 11.2.0.3 and OEM 12.1.0.2 without upgrade. Looks like it does not have this issue. However, need to keep an eye on it, though.

    Anton
  • 5. Re: Increased repository DB redo logging after EM12cR2 upgrade?
    BrianP Journeyer
    Currently Being Moderated
    Hi Anton,

    Thank you for confirming that I'm not the only one experiencing this issue. I've uploaded output from omsvfy/repvfy/agtvfy to my SR, hopefully that will help with identifying the problem.

    Just curious -- I have no reason to believe this is related to the issue, but -- do you have an open incident stating "ORA-20241: ORA-20906: Metric configuration for Job Dispatcher Processing Time (% of last hour) is missing -(evaluate_plsql)"? I'm a little suspicious about that one since it's on the EM Jobs Service target and I haven't been able to clear it. It may be unrelated, though.
  • 6. Re: Increased repository DB redo logging after EM12cR2 upgrade?
    522945 Newbie
    Currently Being Moderated
    What is the collection schedule for the "Oracle Management Service" metric "Job Dispatcher Performance"? If it is greater than 10mins, could you please check whether setting it to 10mins reduces the redo thrash?
  • 7. Re: Increased repository DB redo logging after EM12cR2 upgrade?
    BrianP Journeyer
    Currently Being Moderated
    Hi slohit,

    I've just checked, and in the management service target, under "Other Collected Items", I see "Job Dispatcher Performance" is currently set to a collection schedule of 10 minutes.

    In case there was something misconfigured, I changed it to 30 minutes, saved the change, then immediately set it back to 10 minutes and saved the change again. There does not appear to be any change in redo generation.
  • 8. Re: Increased repository DB redo logging after EM12cR2 upgrade?
    BrianP Journeyer
    Currently Being Moderated
    Hi slohit,

    As a followup, I disabled collection of the Job Dispatcher Performance metric to see if that would help. It seems to have somewhat reduced my redo generation (down to about 320-360MB/hour instead of 450MB/hour just before, but still more than the 80-120MB/hour it was pre-upgrade).

    But, according to the logminer output I'm still seeing those same inserts into EM_JOB_METRICS even with this metric collection disabled.
     229302238 10-OCT-12 
    insert into "SYSMAN"."EM_JOB_METRICS"("SOURCE","LOG_TIMESTAMP","METRIC_NAME","METRIC_QUALIFIER","METRIC_VALUE") values 
    ('omshost.domain.com:4890_Management_Service',TO_TIMESTAMP('10-OCT-12 03.42.06.517409 PM'),'DISP_REQ_STEPS','0','25');
    
     229302238 10-OCT-12
    insert into "SYSMAN"."EM_JOB_METRICS"("SOURCE","LOG_TIMESTAMP","METRIC_NAME","METRIC_QUALIFIER","METRIC_VALUE") values 
    ('omshost.domain.com:4890_Management_Service',TO_TIMESTAMP('10-OCT-12 03.42.06.517409 PM'),'DISP_GOT_STEPS','0','0');
    
     229302238 10-OCT-12
    insert into "SYSMAN"."EM_JOB_METRICS"("SOURCE","LOG_TIMESTAMP","METRIC_NAME","METRIC_QUALIFIER","METRIC_VALUE") values 
    ('omshost.domain.com:4890_Management_Service',TO_TIMESTAMP('10-OCT-12 03.42.06.517409 PM'),'DISP_REQ_STEPS','1','12');
    
     229302238 10-OCT-12
    insert into "SYSMAN"."EM_JOB_METRICS"("SOURCE","LOG_TIMESTAMP","METRIC_NAME","METRIC_QUALIFIER","METRIC_VALUE") values 
    ('omshost.domain.com:4890_Management_Se rvice',TO_TIMESTAMP('10-OCT-12 03.42.06.517409 PM'),'DISP_GOT_STEPS','1','0');
    
     229302238 10-OCT-12
    insert into "SYSMAN"."EM_JOB_METRICS"("SOURCE","LOG_TIMESTAMP","METRIC_NAME","METRIC_QUALIFIER","METRIC_VALUE") values 
    ('omshost.domain.com:4890_Management_Service',TO_TIMESTAMP('10-OCT-12 03.42.06.517409 PM'),'DISP_REQ_STEPS','2','25');
    
     229302238 10-OCT-12
    insert into "SYSMAN"."EM_JOB_METRICS"("SOURCE","LOG_TIMESTAMP","METRIC_NAME","METRIC_QUALIFIER","METRIC_VALUE") values 
    ('omshost.domain.com:4890_Management_Service',TO_TIMESTAMP('10-OCT-12 03.42.06.517409 PM'),'DISP_GOT_STEPS','2','0');
    
     229302238 10-OCT-12
    insert into "SYSMAN"."EM_JOB_METRICS"("SOURCE","LOG_TIMESTAMP","METRIC_NAME","METRIC_QUALIFIER","METRIC_VALUE") values 
    ('omshost.domain.com:4890_Management_Service',TO_TIMESTAMP('10-OCT-12 03.42.06.517409 PM'),'DISP_REQ_STEPS','3','10');
    
     229302238 10-OCT-12
    insert into "SYSMAN"."EM_JOB_METRICS"("SOURCE","LOG_TIMESTAMP","METRIC_NAME","METRIC_QUALIFIER","METRIC_VALUE") values 
    ('omshost.domain.com:4890_Management_Service',TO_TIMESTAMP('10-OCT-12 03.42.06.517409 PM'),'DISP_GOT_STEPS','3','0');
    
     229302238 10-OCT-12
    insert into "SYSMAN"."EM_JOB_METRICS"("SOURCE","LOG_TIMESTAMP","METRIC_NAME","METRIC_QUALIFIER","METRIC_VALUE") values 
    ('omshost.domain.com:4890_Management_Service',TO_TIMESTAMP('10-OCT-12 03.42.06.517409 PM'),'DISP_REQ_STEPS','4','10');
    
     229302238 10-OCT-12
    insert into "SYSMAN"."EM_JOB_METRICS"("SOURCE","LOG_TIMESTAMP","METRIC_NAME","METRIC_QUALIFIER","METRIC_VALUE") values 
    ('omshost.domain.com:4890_Management_Service',TO_TIMESTAMP('10-OCT-12 03.42.06.517409 PM'),'DISP_GOT_STEPS','4','0');
  • 9. Re: Increased repository DB redo logging after EM12cR2 upgrade?
    522945 Newbie
    Currently Being Moderated
    Without the collection of the metric, the EM_JOB_METRICS table may keep increasing in size.

    To reduce the frequency of inserts, it would be better to slow down the frequency of the dispatcher. This is OK only if you are fine with a lower response time between steps for your jobs. (If it is only a few jobs/job steps scheduled to run together, this is all right)
    emctl set property -name oracle.sysman.core.jobs.minDispatcherSleepInterval -value 0.5
    emctl set property -name oracle.sysman.core.jobs.linearBackoff -value 0.5

    You would need to bounce the OMS once these settings are made. These settings will reduce the number of dispatcher sweeps and slow down the collection of the raw metrics.

    Edited by: slohit on Oct 12, 2012 4:39 PM
  • 10. Re: Increased repository DB redo logging after EM12cR2 upgrade?
    venkata thiruveedhi Guru
    Currently Being Moderated
    Hi,

    We already have a bug logged for this case:

    BUG 14726136 - INCREASED REDO LOGGING ON REPOSITORY DB AFTER UPGRADING TO OEM 12.1.0.2

    DEV looking into this analyzing the case. Will get the confirmations soon.

    Seems some issue with the JOB system, will confirm more on this.

    Thanks,
    Venkat
  • 11. Re: Increased repository DB redo logging after EM12cR2 upgrade?
    BrianP Journeyer
    Currently Being Moderated
    Hi slohit and Venkat,

    First, thanks for mentioning the table growth in EM_JOB_METRICS if I were to leave that metric collection disabled -- I forgot to mention that I turned it back on after leaving it off for two hours so that will not be an issue here.

    I've made the property configuration settings that slohit suggested, bounced the OMS, and allowed OEM to run for two hours with the new settings. The changes have reduced redo generation to about 215-250MB per hour. This is half as much redo as it was generating before the change, but still 2-3x more than before the upgrade (80-120MB per hour). LogMiner shows the same series of ten inserts to the EM_JOB_METRICS table, but it is down to 20 inserts per second instead of 60 inserts per second.

    The jobs running from this OMS are all simple database backups, RMAN and SQL scripts, and not any complex multi-step jobs. This seems like an acceptable workaround for now as we can tolerate a delay between job steps in exchange for the reduced redo logging (now the redo should be only 5GB per day on the 13GB repository database instead of the 10GB per day I was seeing).

    I will leave this modified configuration setting in place unless advised to remove it through the SR.

    Thank you very much!
  • 12. Re: Increased repository DB redo logging after EM12cR2 upgrade?
    743919 Newbie
    Currently Being Moderated
    Patch 14726136: INCREASED REDO LOGGING ON REPOSITORY DB AFTER UPGRADING TO OEM 12.1.0.2 has been released.
    Tom
  • 13. Re: Increased repository DB redo logging after EM12cR2 upgrade?
    BrianP Journeyer
    Currently Being Moderated
    Thanks Tom.

    I tried that patch this morning, but in my environment it had an unfortunate side-effect where none of my scheduled OEM jobs would ever start running. They all just sat in the "Scheduled" state for about 3 hours before I rolled back the patch. My redo logging did decrease, though. :)

    For what it's worth, the rollback process went flawlessly and jobs began running again after I backed out the patch so it seems like it's safe if others would like to try it.
  • 14. Re: Increased repository DB redo logging after EM12cR2 upgrade?
    743919 Newbie
    Currently Being Moderated
    We don't use OEM for starting jobs, so the patch works great for us. A little background: I noticed that the DB was running out of undo space. So I increased the undo space to 32 gig, for a 40 gig DB. Make a long story short, for some reason the delete from em_job_metrics could not keep up with the creation of rows. So I shutdown the DB until the patch was released. I applied the patch, and prior to starting OEM, I truncated the em_job_metrics table. The table contained 95 million rows.
    Tom
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points