Wednesday, December 31, 2014

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

Sunday, July 27, 2014

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

Saturday, June 14, 2014

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
$ <OMS_HOME>/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