list database monitoring users

I am quite familiar with the SYSMAN tables but this one required me some googling beyond the Oracle documentation.

The list of targets in your Oracle Enterprise Manager is in SYSMAN.MGMT_TARGETS. Each database target is monitored by a database user, typically DBSNMP.

To retrieve this information, you need some to hijack your database, read this : Gökhan Atil

  1. you copy your encryption key to your repository database, on the OMS server
    $ emctl config emkey -copy_to_repos
    Enter Enterprise Manager Root (SYSMAN) Password :

    Now anyone with select any table on your repository will see all passwords. You don’t want to do this, but unfortunately you have to do this because even the username is encrpyted.

  3. you decrypt the credentials for db monitoring
    SELECT *
    FROM (
      SELECT target_name,
        sysman.em_crypto.decrypt (
          c.cred_salt) cred,
        cred_attr_name attr
      JOIN SYSMAN.mgmt_targets t USING (target_guid)
      JOIN sysman.EM_NC_CRED_COLUMNS c USING (cred_guid)
      WHERE c.target_type = 'oracle_database'
      AND c.set_name = 'DBCredsMonitoring' 
    ) PIVOT ( 
      MAX (cred)
      FOR (attr) IN (
        'DBUserName' AS USERNAME, 
        'DBRole' AS "ROLE")

    ----------- -------- ------
    DB01        dbsnmp   NORMAL
    DB02        dbsnmp   NORMAL
    DB03        sys      SYSDBA

  5. remove the security leak
    $ emctl config emkey -remove_from_repos
    Enter Enterprise Manager Root (SYSMAN) Password :

Now the em_crypto won’t work any more

from dual
Error at line 2
ORA-28239: no key provided
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67
ORA-06512: at "SYS.DBMS_CRYPTO", line 44
ORA-06512: at "SYSMAN.EM_CRYPTO", line 250
ORA-06512: at line 1

This information could be used to change the password dynamically accross all databases.

emcli login \
  -username=sysman \
emcli update_db_password \
  -target_name=DB01 \
  -user_name=dbsnmp \
  -change_at_target=yes \
  -old_password=oldpw \
  -new_password=newpw \

Change background of Oracle Entreprise Manager

If you have more than one OEM and want a red status bar in your production OEM, here the proceedings

Stop your cloud control

emctl stop oms -all

Unzip the $MW/oracle_common/modules/oracle.adf.view_11.1.1/adf-richclient-impl-11.jar in a new directory

$MW/jdk16/jdk/bin/jar -xvf $MW/oracle_common/modules/oracle.adf.view_11.1.1/adf-richclient-impl-11.jar

edit the picture dbd_topShadow.png (for instance) in adf/images/fusion- and copy it to META-INF/adf/images/fusion- with your favorite picture editor

recreate the jar

$MW/jdk16/jdk/bin/jar -cvf $MW/oracle_common/modules/oracle.adf.view_11.1.1/adf-richclient-impl-11.jar *

restart your OEM

emctl start oms

and empty your browser cache

You should see red


Enhancement Request : SSL listener and OEM

#em12c still does not support SSL ! Encrypting network connection (https, ssh, sftp) is common sense in today’s business.

In Enhancement Request 6512390, Created 19-Oct-2007, the customer requested support for SSL.

Most recent update : it is postponed to 13cR2 at least !

*** 09/14/12 04:04 am DISCUSSION ***As we kick off 13c release, cleaning up the ERs. Mass updating all 13%GC% ERs to fixby 13.2GC DEFER. If you want to implement ER in 13.1GC, please update the fixby to = 13.1GC, and update all the other fields as per guidelines published.

Considering the cost of oracle advanced security option (required to get ssl), the lack of ability to influence future product enhancement is disappointing

Enterprise Manager command line interface

emcli has been around for a while, but in 12c the installation has never been easier

do not search on otn for the jar, go to

download with : Setup –>My Preferences –>Command line interface –> download

install with : java -jar emclikit.jar client -install_dir=/u01/app/oracle/emcli

configure with : emcli setup -url= -username=sysman -password=sysmanpw -dir=/u01/app/oracle/emcli.

That’s it.

Let’s try

$ emcli get_targets
Status  Status           Target Type           Target Name                        
1       Up               host                      
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/e
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/O
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/e
-9      n/a              metadata_repository   /EMGC_GCDomain/GCDomain/EMGC_ADMINS
-9      n/a              metadata_repository   /EMGC_GCDomain/GCDomain/EMGC_ADMINS
1       Up               oracle_apache         /EMGC_GCDomain/instance1/ohs1      
1       Up               oracle_apm            /EMGC_GCDomain/GCDomain/EMGC_OMS1/o
1       Up               oracle_beacon         EM Management Beacon               
1       Up               oracle_database       LSC01                              
1       Up               oracle_database       LSC02                              
1       Up               oracle_database       LSC03                              
1       Up               oracle_database       LSC05                              
1       Up               oracle_database       LSC04                              
1       Up               oracle_dbsys          LSC01_sys                          
1       Up               oracle_dbsys          LSC03_sys                          
1       Up               oracle_dbsys          LSC04_sys                          
1       Up               oracle_dbsys          LSC02_sys                          
1       Up               oracle_em_service     EM Console Service                 
1       Up               oracle_em_service     EM Jobs Service                    
1       Up               oracle_emd           
1       Up               oracle_emrep          Management Services and Repository 
-9      n/a              oracle_home           oms12g1_8_precision                
-9      n/a              oracle_home           WebLogicServer10_3_5_0_0_precision 
-9      n/a              oracle_home           OraDb10g_home1_5_precision         
-9      n/a              oracle_home           OraDb11g_home1_1_precision         
-9      n/a              oracle_home           agent12g1_13_precision             
-9      n/a              oracle_home           webtier12g1_24_precision           
-9      n/a              oracle_ias_farm       EMGC_GCDomain                      
1       Up               oracle_listener     
1       Up               oracle_oms  
1       Up               oracle_oms_console
1       Up               oracle_oms_pbs
-9      n/a              weblogic_domain       /EMGC_GCDomain/GCDomain            
1       Up               weblogic_j2eeserver   /EMGC_GCDomain/GCDomain/EMGC_OMS1  
1       Up               weblogic_j2eeserver   /EMGC_GCDomain/GCDomain/EMGC_ADMINS

All green (one could argue command line has no color)

Ow yes it is a “c” !

Rumours about what would come after 8i (internet) 10g (grid) were around, but now it is official, there will be an Oracle Enterprise Manager 12c

Read more :

And in the blogosphere

How to change the connection string of the Oracle Enterprise Manager Grid Control 11g repository

If you moved your repository to a new host and want to change the connection string, no need to drop it, no need to messup in the properties or xml files, simply read the doc
emctl config oms -store_repos_details (-repos_host <host> -repos_port <port> -repos_sid <sid> | -repos_conndesc <connect descriptor>) -repos_user <username> [-repos_pwd <pwd>] [-no_check_db]

Yes it works!

[alert] Oracle agents on AIX may not work in 2011 with OMS10g

Fuadar recently wrote : Grid Control AIX Alert

Basically, if you have an 10g oms Server (any OS / any release) and aix agents (any release), and according to Note 1171558.1, communication between [10g] Oracle Management Service and [AIX] Management Agents will break due to a default self-signed certificate expiring in 31 Dec 2010.

There is more than one way to solve this

1) you upgrade your oms to 11g. Good luck to do this before end of year…

2) You upgrade your oms to, apply patch 10034237 on your oms, create a new certificate, resecure all your agents. Pretty heavy stuff I promise.

3) You use a Third Party Certificate. This may work. I have not tested this for you.

4) You switch from https to http… this is of course not an acceptable workaround as the connection between the agent and the oms will be unsecure, but it may save your Silvester Party.

  • allow both secure and unsecure connections to the oms
  • on all your OMS instances

    opmnctl stopall
    emctl secure unlock
    opmnctl startall

  • switch all your agents to http
  • On all your AIX hosts with an agent installed

    emctl unsecure agent -omsurl http://omsserver:4890/em/*

    You can find the port for unsecure in your oms server in OMSHOME/sysman/config/ under oracle.sysman.emSDK.svlt.ConsoleServerPort.

Happy holidays !