Diagnose cardinality errors in explain plan

Actual vs Estimated Row in Explain plan:

We can use several tools to see the internals of the SQL optimizer execution plan details in Oracle database. These are:

• TKPROF (SQL*Trace) 10046
• SQL*Plus "set autotrace" or explain plan for <query>
• Extended execution plan statistics (v$sql_workarea)

We can use third party tools for the same.

Sometimes we may see number of rows are extremely high but cardinality is less for the tables. This may be the issue with optimizer or stats collection. So you may have following common question now.

“How do I know if the cardinality estimates in a plan are accurate?”

To find actual and estimated rows in explain plan, use “/*+ GATHER_PLAN_STATISTICS */” hint and execute the query and then run below command to find it.

Sample Example:

The Query:

SELECT /*+ GATHER_PLAN_STATISTICS */ MAX (to_number(ORDER_NO||PRE_ORDER_NO)) 

FROM ORDERS OR1 

WHERE TRUNC(OR1.OR_EFFECTIVE_DATE_START) <='07-FEB-2018' 

AND ( greatest (TRUNC(OR1.RECV_DATE),TRUNC(OR1.DATE_POSTED)) >= '01-JAN-1995' 

AND TRUNC(OR1.RECV_DATE) <= '07-FEB-2018' ) 

AND OR1.DELIVERY_STATUS = 'YES' ;

Collect explain plan after execution:

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));  

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------

SQL_ID  9ybvktk8n7d9v, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ MAX (to_number(ORDER_NO||PRE_ORDER_NO))

FROM ORDERS OR1

WHERE

TRUNC(OR1.OR_EFFECTIVE_DATE_START) <=:"SYS_B_0"

AND ( greatest(TRUNC(OR1.RECV_DATE),TRUNC(OR1.DATE_POSTED)) >= :"SYS_B_1"

AND TRUNC(OR1.RECV_DATE) <= :"SYS_B_2" )

AND OR1.DELIVERY_STATUS =:"SYS_B_3"

Plan hash value: 1806674675

---------------------------------------------------------------------------------------------

| Id  | Operation              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |          |      1 |        |      1 |00:05:11.90 |      27 |

|   1 |  SORT AGGREGATE        |          |      1 |      1 |      1 |00:05:11.90 |      27 |

|   2 |   PX COORDINATOR       |          |      1 |        |     32 |00:05:05.89 |      27 |

|   3 |    PX SEND QC (RANDOM) | :TQ10000 |      0 |      1 |      0 |00:00:00.01 |       0 |

|   4 |     SORT AGGREGATE     |          |      0 |      1 |      0 |00:00:00.01 |       0 |

|   5 |      PX BLOCK ITERATOR |          |      0 |  67667 |      0 |00:00:00.01 |       0 |

|*  6 |       TABLE ACCESS FULL| ORDERS   |      0 |  67667 |      0 |00:00:00.01 |       0 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   6 - access(:Z>=:Z AND :Z<=:Z)

       filter(("OR1"."DELIVERY_STATUS"=:SYS_B_3 AND

              GREATEST(TRUNC(INTERNAL_FUNCTION("OR1"."RECV_DATE")),TRUNC(INTERNAL_FUNCTION("OR1

              "."DATE_POSTED")))>=:SYS_B_1 AND TRUNC(INTERNAL_FUNCTION("DM_EFFECTIVE_DATE_STAR

              T"))<=:SYS_B_0 AND TRUNC(INTERNAL_FUNCTION("RECV_DATE"))<=:SYS_B_2))

Note

-----

   - dynamic statistics used: dynamic sampling (level=AUTO)

   - Degree of Parallelism is 32 because of table property

In order to see the A-Rows values for all of the operations in the plan you would have to use the FORMAT value  'ALLSTATS ALL', which will show you the execution statistics for ALL executions of the cursor.

Click here to learn more…

Oracle 12c SQL Plan Directives - Disable | Enable | Use As Hint

What is it?

In previous releases the database stored compilation and execution statistics in a shared sql area which is non persistent. Starting in 12c the database can use a sql plan directive, which is additional information and instructions that the optimizer can use to generate a better execution plan. SQL plan directive may instruct the optimizer to record a column groups and expressions statistics.

For example, a sql plan directive might instruct the optimizer to:

• Collect missing statistics
• Create column group statistics
• Perform dynamic statistics (Dynamic Sampling).

During sql compilation or execution, the database analyzes the query that is missing statistics or that miss-estimated for cardinality to create a sql plan directive.

When the optimizer generates an execution plan, the directives give the optimizer additional information about objects that are referenced in the plan. SQL plan directives are not tight to a specific statement or specific sql id. Directives can be used and shared between multiple statements:

• The optimizer can use directives that are collected on query expressions.
• Directives are automatically maintained created as needed during compilation or execution (missing statistics, cardinality misestimates) and purged if not used after a year.
• Directives can be monitored using dba_sql_plan_directives and dba_sql_plan_dir_objects dictionary tables.
• Sql plan directives are persisted to disk in the SYSAUX tablespace; a plan directive is created and purged to disk (SYSXAUX) every 15 minutes.

Disable SQL Plan Directives: 

alter system set "_sql_plan_directive_mgmt_control" = 0; 
alter system set "_optimizer_dsdir_usage_control" = 0; 

Enable SQL Plan Directives: 

The first two commands below will set the default values for the two hidden parameters in the running instance and revert the change introduce previously. The last two commands will remove the parameters from the spfile if they were added to ensure default are used at next startup.

alter system set "_sql_plan_directive_mgmt_control" = 3; 
alter system set "_optimizer_dsdir_usage_control" = 126; 
alter system reset "_sql_plan_directive_mgmt_control"; 
alter system reset "_optimizer_dsdir_usage_control"; 

Use as Hint to disable:

select /*+ opt_param('_optimizer_use_feedback' 'false') */
* from table;


Warnings !
Don't change any underscore ( _ ) / hidden parameter unless until tested in test environments / recommended by Oracle support.

Clearing Stateless Alerts in OEM 12c/13c using EM CLI

Here I will explain how to check /to see if you have alerts that can be cleared with an EMCLI command and then also take you through clearing out from the GUI, which is available in the OEM 12cR2/13c.

If you want to know what kinds of stateless alerts that can be cleared manually, then once logged into the EMCLI, (Enterprise Manager Command Line Interface) you can do the following:

./emcli login -username=<login name>
./emcli get_metrics_for_stateless_alerts  -target_type=osm_instance

$ emcli get_metrics_for_stateless_alerts  -target_type=osm_instance
Metric                                    Metric Internal Name                    
=======                                   =====================                   
Archive Hung Error Stack                  osm_instance:db_alert_log:archiveHungErrStack 
Data Block Corruption Error Stack
osm_instance:db_alert_log:blockCorruptErrStack
Alert Log Error Stack
osm_instance:db_alert_log:genericErrStack
Media Failure Error Stack
osm_instance:db_alert_log:mediaFailureErrStack
Session Terminated Error Stack
osm_instance:db_alert_log:sessTerminateErrStack
Archive Hung Error Stack
osm_instance:alertLog:archiveHungErrStack
Data Block Corruption Error Stack
osm_instance:alertLog:blockCorruptErrStack
Alert Log Error Stack
osm_instance:alertLog:genericErrStack
Media Failure Error Stack
osm_instance:alertLog:mediaFailureErrStack
Session Terminated Error Stack
osm_instance:alertLog:sessTerminateErrStack
Checker Failure Detected
osm_instance:adrAlertLogCheckerFailure:genericCheckerFailureErrStack
Access Violation
osm_instance:adrAlertLogIncidentError:accessViolationErrStack
ASM Block Corruption
osm_instance:adrAlertLogIncidentError:asmBlockCorruptionErrStack 
Cluster Error
osm_instance:adrAlertLogIncidentError:clusterErrStack 
Deadlock
osm_instance:adrAlertLogIncidentError:deadlockErrStack
File Access Error
osm_instance:adrAlertLogIncidentError:fileAccessErrStack
Generic Incident
osm_instance:adrAlertLogIncidentError:genericIncidentErrStack
Generic Internal Error osm_instance:adrAlertLogIncidentError:genericInternalErrStack 
Internal SQL Error
osm_instance:adrAlertLogIncidentError:internalSqlErrStack
Out of Memory
osm_instance:adrAlertLogIncidentError:outOfMemoryErrStack
Redo Log Corruption osm_instance:adrAlertLogIncidentError:redoLogCorruptionErrStack 
Session Terminated
osm_instance:adrAlertLogIncidentError:sessTerminateErrStack
Last Read Errors
osm_instance:Instance_Disk_Performance:last_read_errs
Data Block Corruption osm_instance:adrAlertLogOperationalError:dataBlockCorruptionErrStack
Generic Operational Error osm_instance:adrAlertLogOperationalError:genericOperationalErrStack
Media Failure
osm_instance:adrAlertLogOperationalError:mediaFailureErrStack
$

This command will return all metric types and alert types that can be cleared out for ASM alerts, (osm_instance).  You can do this for oracle_database or any of the other target_types.

As always, if you need help, EMCLI offers a good help option.  You can save off the file for reference and then gather more help info on specific verbs by doing the following:

-- creates the output file of the emcli verbs and descriptions.
./emcli help > emcli_verbs_help.txt
--gives you a descriptions, syntax and examples for a specific verb.

./emcli help <verb>  

If you aren’t sure of Target Type names, you can query the database or you can do wild card searches through the EMCLI:

./emcli get_targets -targets=%database% -- List all databases
./emcli get_targets -targets=%rac_database% -- List all RAC databases
./emcli get_targets -targets=%osm% -- List osm instances
./emcli get_targets -targets=% -- List all type of targets

This first option above returns a list of all targets that are database type targets.  The second returns all ASM targets and the third returns everything.  Some of the output from the third looks like the following:

ID      Status           Target Type           Target Name
0       Down             oracle_listener       LISTENER_SCAN2_exa1-cluster
1       Up               oracle_oms            oemdb.testdb.com:48
1       Up               oracle_oms_console    oemdb.testdb.com:48 
1       Up               oracle_oms_pbs        oemdb.testdb.com:48
1       Up               oracle_database       testdb_db3.world_testdb2
1       Up               oracle_database       testdb_db4.world_testdb1
1       Up               osm_cluster           +ASM_exa1-cluster
1       Up               osm_cluster           +ASM_exa1-cluster
1       Up               osm_instance          +ASM1_exa1db01.testdb.com

Before attempting to run a clearstate on incidents, lets look at how to check for these.Note that we use two arguments here with the clear_stateless_alerts verb:

Unacknowledged_only–  Only those alerts that have not been acknowledged, which helps distinguish from any that may be currently worked by someone.
Preview –  I want to just check and see how many are available to be cleared.  The command will not clear anything this way, only tell me how many!

$ ./emcli clear_stateless_alerts -older_than=0 -target_type=oracle_database -target_name=<target name> -unacknowledged_only -ignore_notifications -preview

e.g,
$emcli clear_stateless_alerts -older_than=0 -target_type=oracle_database -target_name=TESTDB -unacknowledged_only -ignore_notifications -preview
The following alerts can be cleared:

Total Alerts
==============
1
$

Now, if you want to clear the one alert that is available to be cleared as stateless, you can:

$  ./emcli clear_stateless_alerts -older_than=0 -target_type=oracle_database -target_name=<target_name>

e.g.,
$emcli clear_stateless_alerts -older_than=0 -target_type=oracle_database -target_name=TESTDB

1 alerts were cleared successfully.
$

if no alerts available, then following message will come:

$ emcli clear_stateless_alerts -older_than=0 -target_type=oracle_database -target_name=TESTDB

 No alert found.
$

Now if you have a huge quantity of alerts that are not available for clearing with this process, the EM12c/13c console Incident interface offers the ability to mass clear alerts in release 2,
From the EM12c/13c console, there are a number of ways to access alerts, via the Incident Manager from the Enterprise drop down or from a database target summary page, but for this example, we will work from groups.

By clicking on All Targets, then Groups, we are brought to the groups summary page.

Remember-  Any incidents that are still an incident and need attention will recreate on the next metric collection for the target.

Named Credential Management in OEM 13c using EM CLI

The Enterprise Manager credential subsystem enables Enterprise Manager administrators to store credentials in a secure manner as preferences or operation credentials. The credentials can then be used to perform various system management activities such as real-time monitoring, patching, provisioning, and other target administrative operations.

In Oracle Enterprise Manager 12c/ 13c (aka OEM) there is such a great facility called “Named Credentials”. This allows someone to create a pair of username/password which can be used afterwards without knowing the actual password. Named credentials can be defined on target (i.e. single database or host) or on global level (available on all targets of specified type).

Here are highlights for my posted article:

1) List Named Credentials
2) Create Named Credentials
- Target Based
- Global
3) Add Grantee to Named Credentials
4) Testing Credentials
5) Modify Existing Named Credentials
6) Delete Named Credentials


Log In to OEM console via EM CLI:

$ export EMCLI_HOME=/u01/app/OEM/13c
$ export PATH=$PATH:$EMCLI_HOME/bin

$ emcli login -username=gmohapatra
Enter password : 

Login successful
$ 

Lets see some commands to view or list targets with different ways:
-- To list all database targets
emcli get_targets targets='%database%'
-- To List all Administrators with specifics columns only
emcli list -resource="Administrators" -columns="USER_NAME,REPOS_ACCOUNT_STATUS"
-- List all Targets for few columns with format emcli list -resource="Targets"  -columns="TARGET_TYPE,TARGET_NAME" -colsize="TARGET_TYPE:25,TARGET_NAME:60" -search="TARGET_TYPE='oracle_database'" 
-- List all Targets for few columns with format for database targets
emcli list -resource="Targets" -columns="TARGET_TYPE,TARGET_NAME" -colsize="TARGET_TYPE:25,TARGET_NAME:60" -search="TARGET_TYPE='oracle_database'" 
-- List all Targets for few columns with format for database targets with condition
emcli list -resource="Targets" -columns="TARGET_TYPE,TARGET_NAME" -colsize="TARGET_TYPE:25,TARGET_NAME:60" -search="TARGET_TYPE='oracle_database'" 
-search="TYPE_QUALIFIER3='DB'" 

1) List Named Credentials:

emcli list -resource=NamedCredentials

emcli list -resource=NamedCredentials | awk '{ print $2 $3}'

emcli list -resource=NamedCredentials -search="CredCreatedDate > '1-Jan-2017 12:37:20 PM'"

emcli list -resource=NamedCredentials -columns="CredName,CredOwner"

emcli list -resource=NamedCredentials -format=name:csv > '/path/filename.csv'

Note: timestamp has to be in the format DD-MON-YYYY HH:MI:SS AM/PM
Some resources need a bind variable to be passed to get output. A bind variable is created in the resource and then referenced in the command. For example, this command will list all the default preferred credentials for target type oracle_database.

The below one is the best approch to list Named Credentials:

$ emcli list -resource=NamedCredentials -columns="CredName,CredOwner"
Credential Name                                                 Credential Owner    
NC_ORACLE_D_2017-12-28-054606                                   GMOHAPATRA         
NC_INST_SYS_TESTDB                                              GMOHAPATRA         
NC_GL_TESTDB_SYS                                                SYSMAN             
NC_TESTBOX_SYS                                                  SCOTT          
Rows:4
$ 

If you want to take all Named credentials to be stored as .csv file, then you can use below command:

emcli list -resource=NamedCredentials -format=name:csv > '/u03/Backup/oem/NC/all_nc_05Feb2018.csv'

Similarly, you can use bellow command to list "Prefered Credentials"

Here is an example,

$ emcli list -resource="PreferredCredentialsDefault" -bind="TargetType='oracle_database'" -colsize="SetName:15,TargetType:15"

May be you have requirenment to list all administrator's Named Credentials. At that time you need to connect OMS and run the below query from sysman

user or connect sys as sysdba and fetch the result:

e.g.,
SELECT cred_owner, cred_name, cred_type_name, target_type, user_name,
DECODE (cred_scope, 1, 'Global', 2, 'Target') "cred_scope",
to_char(trunc(cred_created_date),'DD-Mon-RRRR') "created_on" ,
to_char(trunc(cred_updated_date),'DD-Mon-RRRR') "updated_on"
FROM sysman.em_nc_creds
WHERE cred_owner <> '<SYSTEM>' 
ORDER BY 1,2,3;


2) Create Named Credentials :

-- Create Named Credentials as a Target Scope:

To be able to create a named credential, you need to know the target name (unless you create a global credential), target type and credential type associated with the target type. Let’s say I want to create a named credential for my database named “TESTDB”. First I need to login to our EM12c server, and list targets named “TESTDB”:

./emcli create_named_credential -auth_target_type=oracle_database \
-cred_scope=Instance -target_type=oracle_database \
-target_name=TESTDB -cred_type=DBCreds -cred_name=NC_INST_SYS_TESTDB \
-attributes="DBUserName:SYS;DBPassword:Hell0W0rld;DBRole:sysdba" -test

Note: Use \ symbol to continue multiple lines to execute

-- Creating GLOBAL credential

emcli create_named_credential -auth_target_type=oracle_database \
-cred_scope=GLOBAL -target_type=oracle_database \
-target_name=TESTDB -cred_type=DBCreds -cred_name=NC_GL_TESTDB_SYS \
-attributes="DBUserName:SYS;DBPassword:Hell0W0rld;DBRole:sysdba" -test

Error:

Provide -test_target_name and -test_target_type to test global named credential.

Fix: chnage -test to -test_target_name=Global

emcli create_named_credential -auth_target_type=oracle_database \
-cred_scope=GLOBAL -target_type=oracle_database \
-target_name=TESTDB -cred_type=DBCreds -cred_name=NC_GL_TESTDB_SYS \
-attributes="DBUserName:SYS;DBPassword:Hell0W0rld;DBRole:sysdba" -test_target_name=Global

Credential NC_GL_TESTDB_SYS created.

Note: Use target_type=Cluster Database for RAC environments

-- creating Named Credential with passing a properties file.

$ cat named.txt

auth_target_type=oracle_database
cred_scope=Instance
target_type=oracle_database
target_name=TESTDB
cred_type=DBCreds
cred_name=TEST_CRED
attributes=DBUserName:SYS;DBPassword:yourpassword;DBRole:sysdba

./emcli create_named_credential -properties_file=named.txt

3) Add Grantee to Named Credentials:

-- Add Grantee to existing named credential

$ emcli grant_privs -name="SCOTT" -privilege="GET_CREDENTIAL;CRED_NAME=NC_GL_TESTDB_SYS:CRED_OWNER=GMOHAPATRA"
Privileges granted to user/role "SCOTT" successfully
$ 

-- Add to a group like DBA_ADMIN_GROUP

$ emcli grant_privs -name="DBA_ADMIN_GROUP" -privilege="GET_CREDENTIAL;CRED_NAME=NC_GL_TESTDB_SYS:CRED_OWNER=GMOHAPATRA"
Privileges granted to user/role "DBA_ADMIN_GROUP" successfully
$

--To grant read access to all users who are in the group “SEC_USER_GROUP” execute the following statement:

emcli grant_privs -name="SEC_USER_GROUP" -privilege="GET_CREDENTIAL;CRED_NAME=<credential_name>:CRED_OWNER=Credential owner"

--To grant full access to the users in the group “My_SEC_ADMIN”you can use the following:

emcli grant_privs -name="SEC_USER_GROUP" -privilege="FULL_CREDENTIAL;CRED_NAME=<credential_name>:CRED_OWNER=Credential owner"

Of course there is a lot more that can be achieved on this subject.

-- Access on named Credential: Predefine access

FULL_CREDENTIAL
EDIT_CREDENTIAL
GET_CREDENTIAL
CREATE_CREDENTIAL

-- To Revoke access

emcli revoke_privs -name="SCOTT" -privilege="FULL_CREDENTIAL;CRED_NAME=NC_DBSNMP_GLOBAL:CRED_OWNER=GMOHAPATRA"

e.g.,

$ emcli revoke_privs -name="SCOTT" -privilege="FULL_CREDENTIAL;CRED_NAME=NC_DBSNMP_GLOBAL:CRED_OWNER=GMOHAPATRA"
Privileges revoked from user/role "SCOTT" successfully
$ 

4) Testing Credentials:

The following command can be used to test named credentials.

emcli test_named_credential -cred_names="NC_INST_SYS_TESTDB" -target_type="oracle_database" -target_name="TESTDB"

5) Modify Existing Named Credentials;

modify_named_credential

This command modifies the settings of a named credential.  Only specify the particular option you wish to modify.

emcli modify_named_credential
-cred_name=NC_OS_EXAM01
-attributes=”HostPassword:Tiger”

emcli modify_named_credential -cred_name=NC_INST_SYS_TESTDB2 -attributes="DBUserName:SYS;DBPassword:pass123word;DBRole:sysdba" -test

More on Grants and Privilleges:

Imagine we have several administrators who only consume the Named Credentials. If a new colleague arrives, he has to be granted use ‘View’ rights on all (maybe a lot) Named Credentials. Same applies if a new Named Credential is added to the pool. It has to be granted to all admins. Or the other way around: the current admin leaves his job: all change rights on all Named Credentials have to be changed…. This calls for the usage of a group functionality.

This functionality is  not available in the GUI of OEM, neither explained in the manuals (at least I couldn’t find it). Luckely we have EMCLI (Enterprise Manager Command Line Interface), giving a lot more functionality then the GUI does. And since it is command line, we can also script against it. Installation instructions for EMCLI can be found in your OEM instance: Navigate to Setup > Command Line Interface…

One small downside on this construction is the fact that the admins can not be ‘Super Administrator’ anymore, the will have be ‘demoted’ to ‘Admin’.

What we will do is:

Create required accounts and roles in OEM
Create a user who will be owner of all named credentials (it’s pure administrative)
List all named credentials
Grant access to the named credentials to the roles
Grant the appropriate roles to the administrators
Login in the OEM console using an account with super administrator privileges (i.e. sysman).

Navigate to Setup > Security > Roles and click “Create”. Give this role the name “MY_SEC_ADMIN”, Click “Review” and then “Finish”
Navigate to Setup > Security > Roles and click “Create”. Give this role the name “SEC_USER_GROUP”, Click “Review” and then “Finish”
Navigate to Setup > Security > Roles and click “Create”. Give this role the name “MY_PUBLIC”, click “Next”, grant all privileges you might need  and continue until “Finish”
Navigate to Setup > Security > Administrators  and click “Create”. Give this user the name “Credential_owner”, select “Superadministrator”, click “Review” and then “Finish”

-- Create DBA_ADMIN_GROUP:

Navigate to Setup > Security > Roles and click “Create”. Give this role the name “DBA_ADMIN_GROUP”, click “Next”, grant all privileges you might need  and continue until “Finish”

Logout from OEM console, and login again using the newly created account Credential_owner

Navigate to Setup > Security > Named Credentials
Create a new named credential

e.g.,

emcli grant_privs -name="DBA_ADMIN_GROUP" -privilege="GET_CREDENTIAL;CRED_NAME=NC_GL_TESTDB_SYS:CRED_OWNER=GMOHAPATRA"

Here,  DBA_ADMIN_GROUP -- is a group

$ emcli grant_privs -name="SCOTT" -privilege="GET_CREDENTIAL;CRED_NAME=NC_DBSNMP_GLOBAL:CRED_OWNER=GMOHAPATRA"
Privileges granted to user/role "SCOTT" successfully
$

Here, The administrator can view or use the credential
emcli grant_privs -name="SCOTT" -privilege="GET_CREDENTIAL;CRED_NAME=NC_DBSNMP_GLOBAL:CRED_OWNER=GMOHAPATRA" 

e.g., Giving FULL access on NC

emcli grant_privs -name="SCOTT" -privilege="FULL_CREDENTIAL;CRED_NAME=NC_GL_TESTDB_SYS:CRED_OWNER=GMOHAPATRA"

$ emcli grant_privs -name="SCOTT" -privilege="FULL_CREDENTIAL;CRED_NAME=NC_DBSNMP_GLOBAL:CRED_OWNER=GMOHAPATRA"
Privileges granted to user/role "SCOTT" successfully
$

Here, Admin can view,edit and drop the NC also.

The NC owner can give the above access to any admin but not other owner's NC. It will throw error like below example:
e.g.

$ emcli grant_privs -name="SCOTT" -privilege="FULL_CREDENTIAL;CRED_NAME=NC_GL_TESTDB_SYS:CRED_OWNER=SYSMAN"
Error: Insufficient privileges: "GMOHAPATRA"
$

-- To Revoke access

emcli revoke_privs -name="SCOTT" -privilege="FULL_CREDENTIAL;CRED_NAME=NC_DBSNMP_GLOBAL:CRED_OWNER=GMOHAPATRA"

e.g.,

$ emcli revoke_privs -name="SCOTT" -privilege="FULL_CREDENTIAL;CRED_NAME=NC_DBSNMP_GLOBAL:CRED_OWNER=GMOHAPATRA"
Privileges revoked from user/role "SCOTT" successfully
$ 

6) Delete Named Credentials

To Delete any named credential, you should have FULL access on that NC or you should be owner of that NC.

$ emcli delete_named_credential -cred_owner='GMOHAPATRA' -cred_name='NC_DBSNMP_GLOBAL'
Credential deleted.
$

Upgrade OEM agent 12c to 13c

Find current agent version before you upgrade on a host:

$ emctl status agent

Oracle Enterprise Manager Cloud Control 12c Release 4 

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

---------------------------------------------------------------

Agent Version          : 12.1.0.4.0

OMS Version            : 13.2.0.0.0

Protocol Version       : 12.1.0.1.0

Agent Home             : /u01/app/oracle/oem/12c/agent12c/agent_inst

Agent Log Directory    : /u01/app/oracle/oem/12c/agent12c/agent_inst/sysman/log

Agent Binaries         : /u01/app/oracle/oem/12c/agent12c/core/12.1.0.4.0

Agent Process ID       : 35881

Parent Process ID      : 33350

Agent URL              : https://sample01.example.com:3872/emd/main/

Local Agent URL in NAT : https://sample01.example.com:3872/emd/main/

Repository URL         : https://oem13ccloudcontrol:1159/empbs/upload

Started at             : 2018-01-13 12:16:01

Started by user        : oracle

Operating System       : Linux version 2.6.32-358.el6.x86_64 (amd64)

Last Reload            : (none)

Last successful upload                       : 2018-02-08 09:21:12

Last attempted upload                        : 2018-02-08 09:21:12

Total Megabytes of XML files uploaded so far : 53.13

Number of XML files pending upload           : 0

Size of XML files pending upload(MB)         : 0

Available disk space on upload filesystem    : 31.79%

Collection Status                            : Collections enabled

Heartbeat Status                             : Ok

Last attempted heartbeat to OMS              : 2018-02-08 09:21:02

Last successful heartbeat to OMS             : 2018-02-08 09:21:02

Next scheduled heartbeat to OMS              : 2018-02-08 09:22:02

---------------------------------------------------------------

Agent is Running and Ready

$

Now proceed with upgrade process:

Step-1: Connect to your OEM console and follow below steps:

Set up à Manage Cloud Control à Upgrade Agents  

Then select "+ Add" button to add host(s) to upgrade.

Like-wise you can add multiple hosts. Remember, in case of RAC databases, give the cluster name, automatically it will discover all the hosts.

After Adding hosts then use "Submit" and then Click "OK" to proceed agent upgrade process. You can see below screen like this after upgrade.

In the above screen-shot, it is seen agent is upgraded successfully. Next you need to run "root.sh" from the new home in root (#) prompt.

To find new home, now login to target host and check the agent status:

$ emctl status agent

Oracle Enterprise Manager Cloud Control 13c Release 2 

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

---------------------------------------------------------------

Agent Version          : 13.2.0.0.0

OMS Version            : 13.2.0.0.0

Protocol Version       : 12.1.0.1.0

Agent Home             : /u01/app/oracle/oem/12c/agent12c/agent_inst

Agent Log Directory    : /u01/app/oracle/oem/12c/agent12c/agent_inst/sysman/log

Agent Binaries         : /u01/app/oracle/oem/12c/agent12c/agent_13.2.0.0.0

Core JAR Location      : /u01/app/oracle/oem/12c/agent12c/agent_13.2.0.0.0/jlib

Agent Process ID       : 29870

Parent Process ID      : 6455

Agent URL              : https://sample01.example.com:3872/emd/main/

Local Agent URL in NAT : https://sample01.example.com:3872/emd/main/

Repository URL         : https://oem13ccloudcontrol:1159/empbs/upload

Started at             : 2018-02-08 10:10:03

Started by user        : oracle

Operating System       : Linux version 2.6.32-358.el6.x86_64 (amd64)

Number of Targets      : 24

Last Reload            : (none)

Last successful upload                       : 2018-02-08 10:32:28

Last attempted upload                        : 2018-02-08 10:32:28

Total Megabytes of XML files uploaded so far : 0.4

Number of XML files pending upload           : 0

Size of XML files pending upload(MB)         : 0

Available disk space on upload filesystem    : 27.32%

Collection Status                            : Collections enabled

Heartbeat Status                             : Ok

Last attempted heartbeat to OMS              : 2018-02-08 10:32:14

Last successful heartbeat to OMS             : 2018-02-08 10:32:14

Next scheduled heartbeat to OMS              : 2018-02-08 10:33:14

---------------------------------------------------------------

Agent is Running and Ready

$

Here you can see now agent is running from new 13c home. Go to 13c home location and run root.sh.

Finally, you can set your new home in .profile / .bash_profile like below:

Before:

$ cat .bash_profile|grep agent

export AGENT_HOME=/u01/app/oracle/oem/12c/agent12c/agent_inst

alias agent='unset ORACLE_SID;export ORACLE_HOME=$AGENT_HOME;export PATH=$ORACLE_HOME/bin:/bin:/usr/bin'

$

After:

$ cat .bash_profile|grep agent

export AGENT_HOME=/u01/app/oracle/oem/12c/agent12c/agent_13.2.0.0.0

alias agent='unset ORACLE_SID;export ORACLE_HOME=$AGENT_HOME;export PATH=$ORACLE_HOME/bin:/bin:/usr/bin'

$ 

EMCLI @ script mode - python script examples

Example-1: List Linux Targets

# Source Code List Targets
if (len(sys.argv) <> 1 ):
    print "Usage: emcli @list_targets.py OSname"
    exit()
login( username="gouranga", password="password")
myDBs = list( resource="Targets", search="TARGET_TYPE='oracle_database'" )            
for DB in myDBs.out()['data']:
    # we get the host info for the oracle database target
    hostinfo = list( resource="Targets",
    search="TARGET_NAME='" + DB['HOST_NAME'] + "'" )
    # the above one should return only one item, so we use the first item
    OS = hostinfo.out()['data'][0]['TYPE_QUALIFIER1']
    # we print only the targets if their OS name equal to the first argument of script
    if ( OS.lower() == sys.argv[0].lower() ):
        print DB['TARGET_NAME'],hostinfo.out()['data'][0]['TYPE_QUALIFIER1']

# End of script list_targets.py

How to Run?
e.g., emcli @list_targets.py linux

The above script will show the target details as passed the operating system type. In the script I used password. But this can be ignored for security reason for production quality. if ignored, then @ run time password to be inserted. You can change username or you can change the script little bit so that both username and password will be prompted.

Example-2: Create Bulk users with various Roles

# source code 
#################################################
# Creating bulk users with passing x.csv file   #
# Created Date  : 02-Feb-2018                   #
# Created by    : Gouranga Mohapatra            #
################################################# 
# source code to create bulk users with different roles
# Sample EMCLI Python script file to create users
# check number of arguments
if len(sys.argv) <> 1:
    print "Usage: emcli @create_bulk_users.py userslist.csv"
    exit()
# login to OMS
#
lg = login( username="gouranga" )
if lg.exit_code() <> 0:
    print lg.error()
# open file for reading
#
f = open( sys.argv[0], 'r' ) 
# loop for each line of the file
#
for line in f:
    # split line to list (array)
    #
    sp = line.split(',')
    #
    # create user, save the file name as description
    #
    create_user( name=sp[0], password=sp[1], roles=sp[2],
    desc="Listed in " + sys.argv[0])
# End of script

Note : The script accepts only one argument (the name of the CSV file containing the information of new users. The arguments can be accessed via sys.argv object. Then it logins to OMS (it will ask the password of SYSMAN/ Super Admin. You can modify the script and add your EM administrator username and password.

Here’s the sample CSV file (userslist.csv):

Test11,oracle,em_all_operator
test12,oracle,EM_ALL_VIEWER
test13,oracle,EM_CLOUD_ADMINISTRATOR
test14,oracle,em_all_designer

How to Run?
$ emcli @create_bulk_users.py filename.csv

Now you can use below command to view whether your users created or not?

$ emcli list -resource="Administrators" -columns="USER_NAME,REPOS_ACCOUNT_STATUS"

Example-3: CREATE BLACKOUT FOR MULTIPLE TARGETS ON EM13C

Sometimes you may need to blackout all or group of targets due to some emergency outage like power failures or storage level activities. So it is very difficut to do one by one blackouts. In otherhand, The notifications emails of a “planned” downtime is one of the annoying things of monitoring systems. You forget creating blackout and you start a maintenance work, and you get lots of notifications mails. Most of the time, the target which goes down, also affect other targets so it will multiple the number of unwanted notifications mails. Good thing is, it’s very easy to create blackouts on EM13c. We can do it through web console, emctl or emcli tools. A one-liner is enough for it:

$ emctl start blackout BlackOutName -nodeLevel  

The above code will create a blackout for the all targets on the server. We can achieve the same thing by EMCLI:

For single targets, you can do like below:

$ emcli create_blackout -name="BlackOutName" -reason="imergency outage" -add_targets="server_name:host" -propagate_targets 
   -schedule="duration:-1" # indefinite  

If we use emcli, we have more options such as creating repeating blackouts, entering a reason for blackout, enabling blackout for a group of targets (which resides on different hosts). What if we need to create blackout for multiple targets. As I mentioned, EMCLI can be used to create blackout for groups. We can create groups on  EM13c, and instead of passing names of all targets in a group, we can give the group name:

# source code 
#################################################
# Blackout for group of targets                 #
# Created Date  : 02-Feb-2018                   #
# Created by    : Gouranga Mohapatra            #
################################################# 
# Example EMCLI Python script file to create blackout for multiple targets
#
# check number of arguments
if len(sys.argv) <> 2:
    print "Usage to start a blackout: emcli @multiblackout.py targets.csv blackout_name"
    print "Usage to stop a blackout: emcli @multiblackout.py stop blackout_name"
    exit()

blackout_name = sys.argv[1].upper()

# login to OMS
login( username="SYSMAN", password="yoursupersecretpassword" )

if sys.argv[0].lower() == "stop":
    stop_blackout( name= blackout_name )
    # comment below line to keep old blackouts
    delete_blackout( name = blackout_name )
    print "%s blackout stopped and deleted." % blackout_name
    exit()

# open file for reading
f = open( sys.argv[0], 'r' )

# variable to keep all targets
alltargets = ""

# loop for each line of the file
for target in f:
    # build alltargets string
    alltargets += target.replace("\n","") + ";"

create_blackout( name = blackout_name, add_targets=alltargets, reason=blackout_name, schedule="duration:-1" )
# End of script

The script accepts two parameters. First parameter is the path of the file containing the targets, the second parameter is the name of the blackout.

The targets file should be something like this:

e.g., targets.csv
hcmdb:oracle_database  
obiee:oracle_database  
example01:host

After you create a blackout, you can stop (and delete) the blackout by running it again, but this time you need to enter “stop” as the file name:

-- start blackout
$./emcli @multiblackout.py /u01/OEM/scripts/mytargets.csv TESTBLACKOUT   
$./emcli @multiblackout.py stop TESTBLACKOUT 

-- Stop blackout
$./emcli @multiblackout.py /u01/OEM/scripts/mytargets.csv TESTBLACKOUT   
$./emcli @multiblackout.py stop TESTBLACKOUT 

You can do error handling in the script for more efficient.

Example-4 : Delete All Named Credentials:

When you want to delete all the created named credentials by as administrator, then you need a loop to collect all the NC and delete them which are owned by the same administrator.

Here I am writing a shell script this time.

e.g.,

$ cat deleteNC.sh

#!/bin/bash
#################################################
# delete all your Named Credentials             #
# Created Date  : 10-Feb-2018                   #
# Created by    : Gouranga Mohapatra            #
################################################# 

export EMCLI_HOME=/u01/app/OEM/Middleware13c
export PATH=$PATH:$EMCLI_HOME/bin
export LOG_HOME=/u01/app/OEM/backup/log
export DATE=`date +%d%m%y_%H%M%S`

echo -n "Enter OEM Username:"
read USERNAME
echo -n "Enter password:"
read -s PWD
echo ""
echo "Authonticating..."
echo ""
(
login=$(emcli login -username=$USERNAME -password=$PWD | grep -c "successful")
if [ $login -eq 1 ]; then
  echo "Login Succeeded..."
  echo "Finding Named Credentials..."
  echo "Listing and Deleteing Named Credentials for user "$USERNAME
  echo ""
  for CN in $(emcli list_named_credentials -script -noheader | cut -f1);
 do
        echo $CN
        emcli delete_named_credential -cred_name="$CN"
 done
 emcli logout
fi
) 2>&1 | tee ${LOG_HOME}/delete_NC_${USERNAME}_${DATE}.log

How to Run?

$./deleteNC.sh

Disclaimer : Run  these scripts at your own risk.

Reference:
http://www.oaktable.net/category/tags/emcli

Click here to See more scripts.