Blog Feed

Using OMS DEBUG mode to troubleshoot OEM 12c problems

This time, I want to show you how to troubleshoot OEM problems by enabling DEBUG mode in the OMS. The virtual machine (VM) running my sandbox installation of OEM 12c 12.1.0.4 crashed during the night. After restarting the VM and all the OEM components, I wasn’t able to login using the SYSMAN account. The error from the console was not very explicit, just, “Authentication failed. If problem persists, contact your system administrator.”

In order to get more details about the error, I decided to enable DEBUG mode for the OMS and reproduce the error. This is what I did to enable DEBUG mode.

$ cd /u01/app/oracle/oms/oms/bin
$ ./emctl set property -name log4j.rootCategory -value “DEBUG, emlogAppender, emtrcAppender” -module logging
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
SYSMAN password:
Property log4j.rootCategory has been set to value DEBUG, emlogAppender, emtrcAppender for all Management Servers
OMS restart is not required to reflect the new property value
After enabling DEBUG mode, I reproduced the error several times using the console. I also wrote down the approximate time of the error, just to easy the search in the log file. Searching in the emoms.trc file located under /em/EMGC_OMS1/sysman/log/, found an ORA-14400 error. The MOS note 1493151.1, explains how to fix the issue by adding a new audit partition.

$ cd /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log/
$ view emoms.trc
java.sql.SQLException: ORA-14400: inserted partition key does not map to any partition
The final step is to disable the DEBUG mode for your OMS, otherwise the log files can grow real big and the performance could be affected.

$ ./emctl set property -name log4j.rootCategory -module LOGGING -value “WARN, emlogAppender, emtrcAppender”
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
SYSMAN password:
Property log4j.rootCategory has been set to value WARN, emlogAppender, emtrcAppender for all Management Servers
OMS restart is not required to reflect the new property value
I hope this information is useful to you next time you are troubleshooting an OEM 12c issue.
Thanks,

Alfredo

OEM 12c very slow after upgrade to 12.1.0.4

I noticed that OEM 12c console was very slow a few hours after the upgrade to 12.1.0.4 version. 

Looking at the repository DB, found several OMS sessions consuming significant CPU resources.
Bug 19199023 explains that some SQL queries executed against the repository consume high CPU on the servers. This bug affects the DB plug-in 12.1.0.6 and the patch 19176910 should be applied to the plug-ins.

More information available on MOS note, 12.1.0.4 OEM: High CPU utilization on Repository Database due to SYSMAN query WITH TARGETGUID AS (SELECT target_guid, host_name FROM mgmt_targets (Doc ID 1912172.1)
Thanks,

Alfredo

Cannot start ASM – ORA-15063: ASM discovered an insufficient number of disks for diskgroup “DATA”

Today, I faced and issue with an ASM instance. After bouncing the server, CRS went up along with the ASM instance, but the diskgroups were offline.
$ crsctl status resource -t
——————————————————————————–
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
               ONLINE  OFFLINE      hosta
ora.FRA.dg
               ONLINE  OFFLINE      hosta
ora.LISTENER.lsnr
               ONLINE  ONLINE       hosta
ora.LISTENER_1.lsnr
               ONLINE  ONLINE       hosta
ora.asm
               ONLINE  ONLINE       hosta                 Started
ora.ons
               OFFLINE OFFLINE      hosta
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
      1        ONLINE  ONLINE       hosta
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  ONLINE       hosta
ora.database.db
      1        ONLINE  OFFLINE                               Instance Shutdown
ora.database1.db
      1        OFFLINE OFFLINE                               Instance Shutdown
I tried to start ora.DATA.dg resource, but it failed.

$ crsctl start resource ora.DATA.dg
CRS-2672: Attempting to start ‘ora.DATA.dg’ on ‘hosta’
CRS-5017: The resource action “ora.DATA.dg start” encountered the following error:
ORA-15032: not all alterations performed
ORA-15017: diskgroup “DATA” cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup “DATA”
. For details refer to “(:CLSN00107:)” in “/u01/oracle/grid/log/hosta/agent/ohasd/oraagent_oracle/oraagent_oracle.log”.
CRS-2674: Start of ‘ora.DATA.dg’ on ‘hosta’ failed
CRS-2679: Attempting to clean ‘ora.DATA.dg’ on ‘hosta’
CRS-2681: Clean of ‘ora.DATA.dg’ on ‘hosta’ succeeded
CRS-4000: Command Start failed, or completed with errors.
After checking the RAW devices on the host, everything appeared to be properly configured. Then I checked the configuration of the ASM instance, finding the ASM_DISKSTRING empty.
$ crsctl stat resource ora.asm -f
NAME=ora.asm
TYPE=ora.asm.type
STATE=OFFLINE
TARGET=OFFLINE
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r–
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
ALIAS_NAME=
ASM_DISKSTRING=
AUTO_START=restore
CHECK_INTERVAL=1
CHECK_TIMEOUT=30
CREATION_SEED=11
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=asm) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)
DEGREE=1
DESCRIPTION=Oracle ASM resource
ENABLED=1
GEN_USR_ORA_INST_NAME=+ASM
ID=ora.asm
LOAD=1
LOGGING_LEVEL=1
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=60
SPFILE=+DATA/asm/asmparameterfile/registry.123.785123625
START_DEPENDENCIES=hard(ora.cssd) weak(ora.LISTENER.lsnr)
START_TIMEOUT=900
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(ora.cssd)
STOP_TIMEOUT=600
TYPE_VERSION=1.2
UPTIME_THRESHOLD=1d
USR_ORA_ENV=
USR_ORA_INST_NAME=+ASM
USR_ORA_OPEN_MODE=mount
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.3.0
I updated ASM_DISKSTRING with the discovery path of the disks and then bounced ASM instance.
$ srvctl modify asm -d ‘/dev/sd*’
$ srvctl stop asm
$ srvctl start asm
After this the ASM instance came up cleanly and the diskgroups were mounted.

$ crsctl status resource -t
——————————————————————————–
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
               ONLINE  ONLINE      hosta
ora.FRA.dg
               ONLINE  ONLINE      hosta
ora.LISTENER.lsnr
               ONLINE  ONLINE       hosta
ora.LISTENER_1.lsnr
               ONLINE  ONLINE       hosta
ora.asm
               ONLINE  ONLINE       hosta                 Started
ora.ons
               OFFLINE OFFLINE      hosta
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
      1        ONLINE  ONLINE       hosta
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  ONLINE       hosta
ora.database.db
      1        ONLINE  ONLINE                               Open
ora.database1.db
      1        OFFLINE ONLINE                               Open
Hope this help you to troubleshoot and fix the issue on your ASM, when is not able to find the disks.

Thanks,

Alfredo

Understanding Oracle SQL Plan Management SPM – Part 3

This is the third post of SQL Plan Management. In the previous post, Understanding SQL Plan Management– Part 2, I described the three main components of SPM, this time I want to show you how to manually capture plans from the cursor cache. In other words, manually capture plans that reside in memory.

In order to manually capture plans, I’m considering that automatic capture is disabled or OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to FALSE. Oracle provides a function named DBMS_SPM.load_plans_from_cursor_cache to manual capture plans from cursor cache.

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   sql_text          IN  CLOB,
   fixed             IN  VARCHAR2 := ‘NO’,
   enabled           IN  VARCHAR2 := ‘YES’)
 RETURN PLS_INTEGER;
The common use for this function, is to specify the sql_id of the statement we want to capture. We also can specify the plan_hash_value or the sql_text. Two more attributes can be set within this function, fixed is ‘NO’ by default and enabledis ‘YES’ by default. If you want to load plans, but you don’t want the CBO to make use if them; set the attribute enabled to ‘NO’.

In the below example I’m capturing all the SQL plans for sql_id4c372tsuhtunm and printing the number of plans loaded:
SQL> SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => ‘4c372tsuhtunm’);
  DBMS_OUTPUT.put_line(‘Plans Loaded: ‘ || l_plans_loaded);
END;
/SQL>   2    3    4    5    6    7    8    9
Plans Loaded: 8
PL/SQL procedure successfully completed.
To verify the SQL baselines created for this sql_id, I just need to query DBA_SQL_PLAN_BASELINES view:
SQL> select SIGNATURE,SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,FIXED,REPRODUCED from dba_sql_plan_baselines;
            SIGNATURE SQL_HANDLE            PLAN_NAME                      ORIGIN         ENABLED ACCEPTED FIXED REPRODUCED
——————— ——————— —————————— ————– ——- ——– —– ———-
  5535156277878016190 SQL_4cd0d34ee73148be  SQL_PLAN_4tn6m9vmm2k5ye8c8b02d MANUAL-LOAD    YES     YES      NO    YES
  5535156277878016190 SQL_4cd0d34ee73148be  SQL_PLAN_4tn6m9vmm2k5yc568a49d MANUAL-LOAD    YES     YES      NO    YES
  5535156277878016190 SQL_4cd0d34ee73148be  SQL_PLAN_4tn6m9vmm2k5ybff74238 MANUAL-LOAD    YES     YES      NO    YES
  5535156277878016190 SQL_4cd0d34ee73148be  SQL_PLAN_4tn6m9vmm2k5y0310173f MANUAL-LOAD    YES     YES      NO    YES
  5535156277878016190 SQL_4cd0d34ee73148be  SQL_PLAN_4tn6m9vmm2k5y8e42f3cc MANUAL-LOAD    YES     YES      NO    YES
  5535156277878016190 SQL_4cd0d34ee73148be  SQL_PLAN_4tn6m9vmm2k5y25375ef9 MANUAL-LOAD    YES     YES      NO    YES
  5535156277878016190 SQL_4cd0d34ee73148be  SQL_PLAN_4tn6m9vmm2k5y9de69d5d MANUAL-LOAD    YES     YES      NO    YES
  5535156277878016190 SQL_4cd0d34ee73148be  SQL_PLAN_4tn6m9vmm2k5yb76f0084 MANUAL-LOAD    YES     YES      NO    YES
8 rows selected.
Look at the data for the ORIGIN column, it shows from where the plans were loaded, either from manual load or auto capture.

Another method is to manual load plans from SQL Tuning Sets (STS). The STS may contain plans that are not present in memory, like plans in the AWR repository. This method is very useful when you want to create baselines of plans that were created by the CBO few days ago and are not in the cursor cache at this time. Keep in mind that STS requires a special license in order to be used. Oracle provides the function DBMS_SPM.load_plans_from_sqlset to accomplish this task.
DBMS_SPM.LOAD_PLANS_FROM_SQLSET (
   sqlset_name      IN  VARCHAR2,
   sqlset_owner     IN  VARCHAR2 := NULL,
   basic_filter     IN  VARCHAR2 := NULL,
   fixed            IN  VARCHAR2 := ‘NO’,
   enabled          IN  VARCHAR2 := ‘YES’
   commit_rows      IN  NUMBER   := 1000)
RETURN PLS_INTEGER;
You just need to specify the sqlset_name and the sqlset_owner in order to load the plans contained in the STS. It also has the default parameters fixed, set to ‘NO’ and enabled,set to ‘YES’. Two interesting attributes can be set within this function. The basic_filter attribute allows you to select only the plans that meet this filter criteria, in other words, is like a ‘where’ in a query.
basic_filter => ‘sql_text like ”select /*LOAD_STS*/%”’ orbasic_filter => ‘sql_id=”4c372tsuhtunm“‘
The commit_rows attribute, allows you to commit after the value specified, which is 1000 by default. Let’s assume that inside this STS we have 30000 plans and we want to load all of them, SPM will commit every 1000 plans to help to reduce the undo log.

Keep in mind that once you manually load plans for a specific SQL (signature), the CBO will continue capturing plans for it, even if the automatic capture is disabled. Those automatic captured plans will not be used by the CBO until you verify and evolve them.

In the next post, I’m going to show you how to evolve plans in 11g and 12c versions.

Thanks,

Alfredo

Understanding Oracle SQL Plan Management SPM – Part 2

In my previous post Understanding SQL Plan Management – Part 1, I tried to cover some basic concepts of SQL Plan Management (SPM). This post will show you the main components of SPM and how they work in the different versions of Oracle.   

SQL plan management framework has three main components, plan capture, plan selection and plan evolution. These components allow administrators choose which plans should be executed by the database. Plan capture is the process of loading execution plans from different sources into the SQL management base. Plan selection is the process the database follows in order to choose the best plan available after considering many factors including SQL baselines. Plan evolution is the process of making baselines executable after verifying if any performance improvement is available.
Plan capture can be done in two forms, automatic and manual. Automatic plan capture takes place when the database initialization parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to TRUE. Keep in mind that the default value for this parameter is FALSE. If automatic capture is enabled, the CBO will log the SQL signature of any SQL statement executed for the very first time in the database. When the SQL statement is executed for the second time it will recognize it as a repeatable statement in the database and SQL baselines are automatically created and marked as “ACCEPTED” for each repeatable SQL statement. Be careful with this feature because the baseline will be created using the execution plan of the second execution whether is optimal or not. If the CBO produces a better execution plan after the baseline is created, the plan will be stored in the SMB but not used until the plan is evolved.
Manual plan capture is the most common method to capture plans. It can be done in conjunction or instead of automatic capture for a single SQL statement or a group of statements. Plans can be manually loaded by using either DBMS_SPM package or Oracle Enterprise Manager (OEM) from four different sources. Manual loaded plans are automatically “ACCEPTED” and the CBO will continue to load plans automatically for this plans even if automatically plan capture is disabled. Plans automatically loaded after the manual load are marked as not “ACCEPTED”. You can load plans from four sources. From cursor cache, which are the active plans in memory. From SQL tuning sets, which require the SQL tuning pack or real application testing license. From stored outlines and from another Oracle database system using datapump. In order to transfer baselines from one database system to another, you must pack, load and unpack the baseline by following the below steps:
1. On the original system, create a staging table using the DBMS_SPM.CREATE_STGTAB_BASELINE procedure.
2. Pack the SQL plan baselines you want to export from the SQL management base into the staging table using the DBMS_SPM.PACK_STGTAB_BASELINE function.
3. Export the staging table into a flat file using the export command or Oracle Data Pump.
4. Transfer this flat file to the target system.
5. Import the staging table from the flat file using the import command or Oracle Data Pump.
6. Unpack the SQL plan baselines from the staging table into the SQL management base on the target system using the DBMS_SPM.UNPACK_STGTAB_BASELINE function.
The CBO calculates the execution plan every time the SQL statement is parsed (compiled) and then proceeds to execute the statement. If the OPTIMIZER_USE_SQL_PLAN_BASELINES is set to TRUE, the CBO will check if a SQL baseline exists before the plan is executed. If there is no SQL signature that matches the parsed SQL statement then the CBO will log this signature in the statement log if the automatic capture is enabled or just proceeds to execute the statement if disabled. If a signature matches the statement and a baseline doesn’t exists, it will proceed to create a plan for verification but if a baseline already exists then the baseline will be executed. If more than one “ACCEPTED” plan exists in the baseline, the CBO costs each plan for the given SQL statement and picks the one with the lowest cost. If a baseline is marked as “FIXED”, the CBO picks the lowest cost fixed plan unless all fixed plans are marked as non-reproducible.
Plan evolution, in simple terms is the process to “ACCEPT” or “REJECT” plans after verifying that performance is better or not compared with the current plan. Plan evolution as plan capture can be done automatic and manual. Automatic plan evolution is managed by the SQL tuning advisor task in 11g versions, while is managed by the SPM Evolve Advisor in 12c.
Adaptive SQL Plan Management  is one of the new features of Oracle 12c. Adaptive SQL Plan Management, is just the new automatic evolve task SYS_AUTO_SPM_EVOLVE_TASK that runs in the nightly maintenance window and is enabled by default. This task ranks all unaccepted plans and runs the evolve process for them. If the plan performs 1.5x times better than the current plan in the SQL plan baseline, then the plan is automatically accepted and becomes usable by the optimizer. This ratio defined by the hidden parameter _PLAN_VERIFY_IMPROVEMENT_MARGIN and can be modified. After the evolve task is complete, a persistent report is generated with details on how the non-accepted plan perform compared to the accepted plan performance. Administrators can go back and check what plans were evolved to any point in time.
      
Manual plan evolution is bit different in every version of the database. In 11g release 1, is being controlled using the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function and by changing the attribute_name ‘ACCEPTED’ to ‘YES’. Is the administrator’s responsibility verify that the plan is performing better than the current plan. In 11g release 2, the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function helps the adminstrator verify if the plan performs better than the current plan and if it does, the plan is marked as “ACCEPTED”. A new SPM Evolve Advisor API is available in 12c version. Create task, execute task and report the evolve taks are part of the three step process of evolving plans. Unaccepted plans are not manually evolved when using the SPM Evolve Advisor, therfore the plan must be manually accepted using DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE function.   
In the next post I will show you how to use the different DBMS_SPM functions to capture and evolve plans.

Thanks,

Alfredo

Understanding Oracle SQL Plan Management SPM – Part 1

This time I want to talk about SQL Plan Management, explain how this feature works and how to make the best use of it. As is a very extensive topic I will split it in three posts.
Introduction
The Oracle Cost Based Optimizer (CBO) introduced in Oracle version 7. It determines the most efficient way to execute SQL statements after considering several factors such as database initialization parameters, optimizer statistics and many others like bind peeking, cardinality feedback, etc.

The CBO’s goal is to produce an optimal execution plan for the SQL statements executed in the database system. This is the reason why we want the CBO to be flexible enough to produce the best execution plans for the SQL statements. We must not forget that DB systems are dynamic enough to have data distribution changes, new Indexes may be added and initialization parameters could be modified, leading the CBO to produce multiple execution plans for the same SQL statement. This is an expected behavior of the CBO and we should be glad that is smart enough to produce optimal plans most of the times during the hard parsing, however sometimes it may also produce sub-optimal plans under special circumstances.

Here’s where plan stability tools come on the scene. Tools like hints, outlines, SQL Plan Management and custom SQL Profiles help the DBA and developers to allow only optimal plans be executed, hence avoiding severe performance problems within the database.
SQL Plan Management (SPM) is a new feature of Oracle 11g. SPM provides a framework for plan stability and control by ensuring that only selected plans are executed. If new plans are created by the CBO, they will not be executed until they are verified by the database or by the administrator and marked as accepted.

SPM controls the execution plans by using three control flags. The “ENABLED” flag that accepts two values YES & NO controls if the plan is available or not for the CBO to be considered. If the “ACCEPTED” flag is set to YES and “ENABLED” is set to YES, then the CBO will execute the plan. If “ACCEPTED” is set to NO, the plan should be verified by the database system or the administrator to check if it has comparable or better performance than the current plan. This process of making not “ACCEPTED” plans into “ACCEPTED” is called plan evolution. The last control flag name is “FIXED” and can only be set to YES if the plan is “ENABLED” and “ACCEPTED”, if set to YES plans have priority over “ACCEPTED” plans just because they are not expected to change.

SPM uses a mechanism called SQL plan baseline (baseline). Baseline is a group or set of “ACCEPTED” plans the CBO is allowed to use for a particular SQL statement. These baselines and the not “ACCEPTED” plans are stored into the SQL plan history. All baselines and the plan history are logically stored into the SQL management base (SMB) in the data dictionary. The SQL management base stores the statement log, plan histories, SQL plan baselines, and SQL profiles. Oracle provides information about SQL baselines in the DBA_SQL_PLAN_BASELINES and v$SQL system views. Database administrators can query information about what SQL baselines exists, their status and origin.

More information about DBA_SQL_PLAN_BASELINES can be found here https://docs.oracle.com/database/121/REFRN/refrn23714.htm#REFRN23714.

Oracle matches SQL statements with SQL baselines by using signatures. SQL signature is a unique identifier created from the normalized SQL text, uncased and whitespaces removed. SQL signatures ensure that the same SQL statement is always having the same SQL signature independent of the upper/lower case or spaces. SQL signatures are stored into the SQL management log in the SMB. If a SQL statement has its signature stored in the SMB, means to be a repeatable statement and a baseline will be created during the next execution.

SQL baselines also have three status flags. The first flag “REPRODUCED” is automatically set to YES when the CBO is able to reproduce the plan for the given SQL statement and NO when not possible, like when an Index is dropped. The second flag “AUTOPURGE” is user modifiable, if set to YES the plan will be purged when not used and reaches the SPM plan retention limit parameter. The last status flag “REJECTED” is set to YES when “ACCEPTED” is set to NO and the plan was verified (has LAST_VERIFIED), or “ENABLED” is set to no in 11gR2 and 12c versions.

SQL plan management is controlled by two initialization parameters. When the optimizer_use_sql_plan_baselines parameter is set to TRUE (default), the CBO will make use of the baselines created and stored in the SMB. The second parameter optimizer_capture_sql_plan_baselines is set to FALSE by default and controls if the database will automatically capture the SQL signature in the SQL management log and automatically create a SQL baseline on the second execution of the SQL statement. As a best practice and personal recommendation, don’t set optimizer_capture_sql_plan_baselines to TRUE. If set to TRUE, any baseline that is automatically created is also set to “ACCEPTED”, this means that the second execution of a SQL statement will always be used by the CBO even if there’s a better execution plan available. 

In the next post I’m going to cover how to capture and evolve SQL baselines in 11g and 12c version, stay tuned!

Thanks,

Alfredo

Why you should attend to IOUG Collaborate 2015?

Today I want to share a personal story about IOUG’s Collaborate, a great conference that supports products from Engineered Systems, Oracle Databases to Fusion Middleware and Siebel. Last year was my first time as a speaker and was one of kind experience as I was able to share my knowledge in Oracle Database performance tuning with professionals that work on a daily basis with Oracle technologies.

This is what makes Collaborate great, professionals sharing their experiences to other professionals. You can be sure that the guys who are speaking at Collaborate really know what they are talking about, that’s a guarantee. Apart of education, networking is also great at Collaborate, so you can meet IT professionals, Oracle Product Managers and technology gurus (you may already seen their blogs).

This year Collaborate will be greater than ever, just imagine to learn from Tom Kyte, Craig Shallahamer and Kellyn Pot’Vin-Gorman, you don’t want to miss the opportunity!

If you want to be part of this big IT event, want to learn great stuff that will help you with your daily work and want to network with great people, you should register right away!

More information available at http://collaborate.ioug.org/

BTW. If you attend the Collaborate 2015, be sure to stop by my sessions about Oracle DB Performance and Oracle Enterprise Manager

Oracle Enterprise Manager at Collaborate 2015 https://lnkd.in/e3G6ZzK
Thanks,

Alfredo

Oracle Enterprise Manager Security– Disable SYSMAN access

In Enterprise Manager 12c SYSMAN user is the schema owner and as a best practice all the users should log in using their own individual accounts. To enforce this you can prevent SYSMAN from login into the console and/or emcli by setting SYSTEM_USER to -1 in the MGMT_CREATED_USERS table:
UPDATE MGMT_CREATED_USERS
SET SYSTEM_USER=’-1’
WHERE user_name=’SYSMAN’
To re-enable the access just set it to 1.
UPDATE MGMT_CREATED_USERS
SET SYSTEM_USER=’1’
WHERE user_name=’SYSMAN’
Refer to Oracle Support’s note:
How To Disable SYSMAN & SYSTEM Users from Logging into Grid Console? (Doc ID 867360.1)
Thanks,

Alfredo

Oracle Enterprise Manager – Reducing the noise, Part 1

Enterprise Manager 12c is a great monitoring tool, with it you can monitor a wide range of target types from databases to middleware; although out-of-the-box metrics can suit your monitoring requirements they can generate a considerable amount of white noise. In order to reduce this noise first you have to identify which are the top alerts in your system; Cloud Control comes with several predefined reports that help you to dig into multiple areas of your system, there’s a report “20 Most Common Alerts” which shows you the incidence of common alerts.


In the picture above, you can clearly see that metric “Database Time Spent Waiting (%)” appears twice in my Top 3, let’s find out our metric setting for my DB targets; in order to do this we must go to a DB home page then Oracle Database -> Monitoring -> Metrics and Collection Settings.  

 
Wait a minute! Why I’m receiving alerts if there are no thresholds setup for any of those metrics?, this behavior is clearly explained in MOS note 1500074.1 about a default warning threshold of 30% inside the database configuration. Let’s take a look to dba_threshold to confirm.
set lines 300
column METRICS_NAME format a30
column WARNING_OPERATOR format a30
column WARNING_VALUE format a30
column CRITICAL_OPERATOR format a30
column CRITICAL_VALUE format a30
SELECT METRICS_NAME,WARNING_OPERATOR ,WARNING_VALUE,CRITICAL_OPERATOR ,CRITICAL_VALUE FROM DBA_THRESHOLDS;
METRICS_NAME                        WARNING_OPERATOR               WARNING_VALUE                  CRITICAL_OPERATOR              CRITICAL_VALUE
———————————– —————————— —————————— —————————— ——————————
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             30                             NONE
Average Users Waiting Counts        GT                             30                             NONE
Blocked User Session Count          GT                             0                              NONE
Current Open Cursors Count          GT                             1200                           NONE
Database Time Spent Waiting (%)     GT                             30                             NONE
Database Time Spent Waiting (%)     GT                             30                             NONE
Database Time Spent Waiting (%)     GT                             30                             NONE
Database Time Spent Waiting (%)     GT                             30                             NONE
Database Time Spent Waiting (%)     GT                             30                             NONE
Database Time Spent Waiting (%)     GT                             30                             NONE
Database Time Spent Waiting (%)     GT                             50                             NONE
Database Time Spent Waiting (%)     GT                             50                             NONE
Logons Per Sec                      GE                             100                            NONE
Session Limit %                     GT                             90                             GT                             97
Tablespace Bytes Space Usage        DO NOT CHECK                   0                              DO_NOT_CHECK                   0
Tablespace Space Usage              GE                             85                             GE                             97
22 rows selected.
There you go!, all metrics for “Database Time Spent Waiting (%)” are set to 30% or 50% values, now the trick to disable these metrics is to set them to a different value like 99%; this will override the default value as follows:

  
Let’s look at the database setting again:
set lines 300
column METRICS_NAME format a30
column WARNING_OPERATOR format a30
column WARNING_VALUE format a30
column CRITICAL_OPERATOR format a30
column CRITICAL_VALUE format a30
METRICS_NAME                        WARNING_OPERATOR               WARNING_VALUE                  CRITICAL_OPERATOR              CRITICAL_VALUE
———————————– —————————— —————————— —————————— ——————————
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             30                             NONE
Average Users Waiting Counts        GT                             30                             NONE
Blocked User Session Count          GT                             0                              NONE
Current Open Cursors Count          GT                             1200                           NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Database Time Spent Waiting (%)     GT                             99                             NONE
Logons Per Sec                      GE                             100                            NONE
Session Limit %                     GT                             90                             GT                             97
Tablespace Bytes Space Usage        DO NOT CHECK                   0                              DO_NOT_CHECK                   0
Tablespace Space Usage              GE                             85                             GE                             97
25 rows selected.
We successfully modified these metrics to a very high value; at this point you can decide to stay at 99% or you can remove that threshold in order to completely disable them.

Now let’s confirm those settings in the database:
set lines 300
column METRICS_NAME format a30
column WARNING_OPERATOR format a30
column WARNING_VALUE format a30
column CRITICAL_OPERATOR format a30
column CRITICAL_VALUE format a30
METRICS_NAME                        WARNING_OPERATOR               WARNING_VALUE                  CRITICAL_OPERATOR              CRITICAL_VALUE
———————————– —————————— —————————— —————————— ——————————
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             10                             NONE
Average Users Waiting Counts        GT                             30                             NONE
Average Users Waiting Counts        GT                             30                             NONE
Blocked User Session Count          GT                             0                              NONE
Current Open Cursors Count          GT                             1200                           NONE
Logons Per Sec                      GE                             100                            NONE
Session Limit %                     GT                             90                             GT                             97
Tablespace Bytes Space Usage        DO NOT CHECK                   0                              DO_NOT_CHECK                   0
Tablespace Space Usage              GE                             85                             GE                             97
14 rows selected.
The metrics are not there anymore and hopefully the alerts neither. This behavior is also noted for “Average Users Waiting Counts” metric, if you are receiving considerable white noise for this metric you can disable as well following the same procedure. A good practice is to create a Monitoring template to help you modify these thresholds for multiple targets at once.
Stay tuned for my next post about reducing OEM 12c noise.
Thanks,

Alfredo

Upgrade Oracle Enterprise Manager to 12.1.0.4

Enterprise Manager 12.1.0.4 was just released and now is time to test and document the process. The official documentation can be obtained from here (http://docs.oracle.com/cd/E24628_01/upgrade.121/e22625/upgrading_12101_PS1_gtgstrtd.htm) and this document cover the steps I followed and may not work on yours (I’m not responsible of any issues/problems caused to your system).
This document covers Oracle Enterprise Manager upgrade from 12.1.0.3 to 12.1.0.4 version in a Single-OMS configuration using one system upgrade method and is divided in 3 sections describing prerequisites, upgrade and post upgrade tasks.


Prerequisites


  • Download the software from (http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/index.html) on your server and extract it to a stage directory.


mkdir /stage
unzip em12104_linux64_disk1.zip -d /stage
unzip em12104_linux64_disk2.zip -d /stage
unzip em12104_linux64_disk3.zip -d /stage


  • Ensure that you have a valid OMS (middleware home & inventory), Repository and Software Library backup.
  • If you have BI Publisher installed, then stop BIP server through WebLogic Admin Console.
  • Drop any snapshot created against Repository tables.


SQL> select master , log_table from all_mview_logs where log_owner=’SYSMAN’


no rows selected


  • Check and compile invalid objects in the Repository schema.


SQL> select ‘alter ‘||object_type||’ ‘||owner||’.’||object_name||’ compile;’ from all_objects
 2  where status=’INVALID’;


no rows selected


  • Execute privileges on DBMS_RANDOM are required during the upgrade, run the below commands to set them.


SQL> grant execute on DBMS_RANDOM to DBSNMP;
SQL> grant execute on DBMS_RANDOM to SYSMAN;
SQL> revoke execute on DBMS_RANDOM from PUBLIC;


  • Copy the emkey from OMS to the Management Repository.


$ emctl config emkey -copy_to_repos
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
The EMKey has been copied to the Management Repository. This operation will cause the EMKey to become unsecure.
After the required operation has been completed, secure the EMKey by running “emctl config emkey -remove_from_repos”.



  • Stop OMS, extended instances and both OMS and Repository agents.


$ emctl extended oms jvmd stop -all
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Please enter the SYSMAN password:
Stopping all Engines
{}
No engines found for this operation
Emctl Extended verb failed. Please check /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for more details


$ emctl extended oms adp stop -all
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Please enter the SYSMAN password:
Stopping all Engines
{}
No engines found for this operation
Emctl Extended verb failed. Please check /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for more details


$ emctl stop oms -all
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Stopping WebTier…
WebTier Successfully Stopped
Stopping Oracle Management Server…
Oracle Management Server Successfully Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down


$ ./emctl stop agent
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Stopping agent ….. stopped.


Upgrade

  • Run the runInstaller executable from the folder where the software was extracted.


$ cd /stage
$ ./runInstaller



  • Type My Oracle Support account details.


  • Choose if you want to search for any software updates.


  • Review and fix any prerequisite failure.


  • Choose the upgrade method, in my case One system Upgrade then select the Oracle Home.


  • Type the “NEW” Middleware Home location.


  • Type the required Repository credentials.


  • Some prerequisites failed, click Yes to let the installer fix them automatically.


  • Review the list of plug-ins that are going to be automatically upgraded.


  • Select any additional plug-ins to be deployed after the upgrade.


  • Type the required WebLogic Domain credentials.


  • Review the all the information provided and click Install.


  • Review the progress.


  • Run the required allroot.sh script as root.


  • Review the final information about the upgrade process.



  • Login to Cloud Control and verify the version. Note that some targets are marked as down, we are going to fix them in the post upgrade tasks.


  • Upgrade Oracle Management Agents (at least OMS and Repository). Go to Setup -> Manage Cloud Control -> Upgrade Agents.



  • Type a Job Name (or use the default name) and click Add to select the agents you want to upgrade to 12.1.0.4.
  • Select the agents from the list and click OK.


  • Choose credentials and click Submit. There’s the option to schedule the upgrade for later under Additional Inputs.



  • You must run the root.sh script after the upgrade, click OK.


  • Review the agent upgrade process.



  • Agent upgrade was successful.


Post Upgrade

Deleting the Old OMS Home is an optional task, however I have decided to perform it in order to save space and have a clean Inventory.


  • Invoke the installer from the old OMS home


$ export ORACLE_HOME=/u01/app/oracle/oms12cr3/oms
$ /oui/bin/runInstaller -deinstall



  • Select only the OMS-related plug-ins homes (from the Old Home only!!) and click Remove.


  • Select the Java Development Kit JDK home and click Remove.


  • Select the Oracle WebTier home and click Remove. After finishing the deinstall click Close.


  • Finally select the OMS home & the Oracle Common directories and click Remove.


All targets marked up.

Thanks,
Alfredo