How to monitor SQL in real time using OEM Grid Control

When we open the performance page of a database, then after the graphs we see the "Additional Monitoring Links"

The concept of real-time SQL monitoring was introduced in Oracle Database Enterprise Edition 11g. This feature allows tracking the execution of SQL statements that take 5 seconds or more, or those that use parallel execution.

In this way, it is possible to understand exactly what is being done by the SQL statement. In real-life situations when batch jobs seem to run for an abnormally long period and there is pressure from management is to kill such jobs and restart them, this feature is of great use for determining if the batch job is frozen or just running slowly at some particular step of the execution plan.

This displays the Monitored SQL Executions page shown in below figure. SQL statements displayed with a tick mark have completed their execution, whereas currently executing SQL statements are displayed with a rotating sphere. It is possible to refresh this page manually or automatically at a specified time period in seconds.

We can now select any of the SQL statements on this page and click the corresponding SQL ID. Then the Monitored SQL Execution Details page is displayed.

The execution plan steps are shown along with the timeline. A tick mark is also visible at the top of the page, indicating that the SQL statement has completed without any errors.

A rotating sphere at the top of the page indicates that the SQL statement is still executing. In this case, the entire page is refreshed automatically, and the steps of the execution plan, as they run one after the other, can be identified easily.

The green arrows in this page’s Plan Statistics section in the above figure change their position on every refresh to indicate the currently executing steps, and some of the data columns in this section are also colored momentarily upon being refreshed with the latest data. This provides a very dynamic view of the progress of the execution plan.

How to monitor CPU utilization through OEM Grid Control

The Host chart on the Performance page of the database displays various utilization information about the system which is hosting the database. We have to determine if the host system has enough resources available to run the database. Here resources refers to the the amount of CPU, memory, and disk resources that our system should be using to function the database properly. We can then verify that the database is not consuming too many of these resources.

To view details about CPU, memory, and disk utilization. Click Load Average on the right pannel of the Host chart. The Host page appears, showing the Performance subpage.

The Performance Summary view shown above is by default. The Performance Summary view displays metric values for CPU utilization, memory utilization, disk I/O utilization, and the top 10 processes ordered by both CPU and memory utilization.


Monitoring CPU Utilization

1. From the Database Home page, click Performance. The Performance page appears.

2. In the Host chart, click Load Average in the right pannel. The Host page appears, showing the Performance subpage.

3. Select CPU Details as shown above from the View list. The CPU Details view appears. This view contains statistics about CPU utilization, I/O wait times, and load gathered over the last hour. The top 10 processes are also listed ordered by CPU utilization.


4. Verify the current CPU utilization using the CPU Utilization chart as shown below.
The CPU Utilization chart shows CPU utilization over the last hour. The current value is displayed below the chart. During standard workload hours, the value should not exceed the critical threshold.

5. Click CPU Utilization. The CPU Utilization page appears.

This page contains CPU utilization statistics and related alerts generated over the last 24 hours.
Here the warning threshold is set at 80% and critical at 95%. So if we see CPU spikes anytime reaches these threshold then we need to investigate more on this.

How to monitor TOP SESSIONS through OEM Grid Control

Open the performance page of a database, here in the Average Active Session chart, click on the CPU block on the chart of its corresponding wait class. The Active Sessions Working page appears. And when we click on the dark area which is pointed out here with arrow, then we can see the 'Detail for select 5 minute interval' page. Here we can see the 'Top Working session' table.

Under the 'top working session' table, click on the SESSION ID link of the most active session link. The session details page appears.

This page contains information such as session activity, session statistics, open cursors, blocking sessions, wait events, and parallel SQL for the selected session.
In this example user SYS is consuming 100% of database activity and should be investigated.
Because the session is consuming 100 percent of database activity, consider ending the session by clicking Kill Session, and proceeding to tune the SQL statement that this session is running.

How to monitor TOP SQL through OEM Grid Control

Open the performance page of a database, here in the Average Active Session chart, click on the CPU block on the chart of its corresponding wait class. The Active Sessions Working page appears. And when we click on the dark area which is pointed out here with arrow, then we can see the 'Detail for select 5 minute interval' page. Here we can see the 'Top Working SQL' table.

When we click on the SQL ID link of the most active SQL statement in the Top Working SQL table. The SQL Details page appears.

For SQL statements that are using the majority of the wait time, use SQL Tuning Advisor or create a SQL Tuning Set to tune the problematic SQL statements.

Performance monitoring with Average Waiting Session Graph of OEM grid control

When we open the performance page of a particular database we can see the below image:

Working with Average Waiting Session Graph of OEM grid control

The Average Active Sessions chart of the Performance page shows potential problems inside the database, including how much CPU users are consuming. The wait classes show how much of the database activity is consumed by waiting for a resource such as disk I/O and many more details. We can see all the detals in right panel of the Average Waiting Session graph.
Suppose when we open the performance tab we see the huge increase in “application waits”. this is the colored portion of the Average Active Sessions graph.

Then we decide to investigate this further, and clicks the graph. This drills down to next figure, which shows the actual breakdown of the application waits, most of which are type “enq: TX – row lock contention.”
It is possible to change the period of time to be examined by simply dragging the shaded area of the graph with the mouse. Doing this displays the sessions and SQL statements active in that time period.

Now we examines the Top SQL list and clicks the top-most SQL ID.
The SQL statement corresponding to this SQL ID is displayed in next figure. The sessions that have issued this SQL statement are also displayed on the same page.

The issue has been identified as row lock contention, and the SQL statement causing this issue has also been found. So far, this information has come from the Database Performance page and the drill-downs

OEM Database Control Versus OEM Grid Control

Enterprise Manager provides two separate consoles that you can use to monitor your database: Database Control and Grid Control.

Database Control is the Enterprise Manager Web-based application for managing Oracle Database 11g Release 1 (11.1) and later. Database Control is installed and available with every Oracle Database 11g installation. From Database Control, you can monitor and administer a single Oracle Database instance or a clustered database.


Grid Control is the Enterprise Manager console you use to centrally manage your entire Oracle environment. Within Grid Control, you access the multiple database targets using the Targets tab, then Databases.

How to manage locks from OEM Grid Control

We can use Oracle Enterprise Manager (OEM) grid control to identify and resolve locking situations. We can find all current locks in the instance, including the blocking and the blocked sessions. We can also kill the blocking session from OEM Grid control.

We can view blocking session details by clicking the Performance tab in the Home page. Click Blocking Sessions under the Additional Monitoring Links section to go to the Blocking Sessions page. The Blocking Sessions page contains details for both the blocking as well as the blocked sessions. We can see the exact wait event, which will be enq: TX row lock contention when one session blocks another. We can find out the exact SQL statement that’s involved in blocking sessions, by clicking the SQL ID link on this page. We can kill the blocking session from this page by clicking the Kill Session button at the top left side of the page.

In the Additional Monitoring Links section is another link named Instance Locks, which takes us to the Instance Locks page. The Instance Locks page shows the session details for both the blocking and blocked sessions. We can click the SQL ID link to view the current SQL that’s being executed by the blocker and the blocked sessions. We can also find out the name of the object that’s locked. You can kill the blocking session by clicking the Kill Session button.

Resolving ORA-01031 while configuring EM 10g

EMCTL is one service which we never know whether it will run fine or not…. :-)

Just kidding, here is one such problem with Enterprise Manager configuration and its solution

Problem:

When trying to configure EM for 10g or trying to drop repository or trying to recreate repository you may get below error
[oracle@issgascop218 ~]$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Aug 24, 2010 7:51:44 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: PASQ
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: y
Aug 24, 2010 7:51:54 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /opt/oracle/product/10.2.0/cfgtoollogs/emca/PASQ/emca_2010-08-24_07-51-44-AM.log.
Aug 24, 2010 7:51:55 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) …
Aug 24, 2010 7:51:57 AM oracle.sysman.emcp.EMReposConfig stopDBMSJobs
WARNING: Error initializing SQL connection. SQL operations cannot be performed
Aug 24, 2010 7:51:57 AM oracle.sysman.emcp.EMReposConfig invoke
WARNING: Unable to remove DBMS jobs.
Aug 24, 2010 7:51:57 AM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) …
Aug 24, 2010 7:51:57 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
WARNING: Error executing /opt/oracle/product/10.2.0/sysman/admin/emdrep/bin/RepManager -connect (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=issgascop218)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PASQ.issgascop218.global.iss.biz))) -repos_user SYSMAN -action drop -verbose -output_file /opt/oracle/product/10.2.0/cfgtoollogs/emca/PASQ/emca_repos_drop_2010-08-24_07-51-57-AM.log
Aug 24, 2010 7:51:57 AM oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Error dropping the repository
Aug 24, 2010 7:51:57 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Refer to the log file at /opt/oracle/product/10.2.0/cfgtoollogs/emca/PASQ/emca_repos_drop_<date>.log for more details.
Aug 24, 2010 7:51:57 AM oracle.sysman.emcp.EMConfig perform
SEVERE: Error dropping the repository
Refer to the log file at /opt/oracle/product/10.2.0/cfgtoollogs/emca/PASQ/emca_2010-08-24_07-51-44-AM.log for more details.
Could not complete the configuration. Refer to the log file at /opt/oracle/product/10.2.0/cfgtoollogs/emca/PASQ/emca_2010-08-24_07-51-44-AM.log for more details.
When i opened log file, i found below information…

[24-08-2010 07:51:57] Enter SYS user’s password :
[24-08-2010 07:51:57]
[24-08-2010 07:51:57] Enter repository user password :
[24-08-2010 07:51:57]
[24-08-2010 07:51:57] Getting temporary tablespace from database…
[24-08-2010 07:51:57] Could not connect to SYS/(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=issgascop218)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PASQ.issgascop218.global.iss.biz))): ORA-01031: insufficient privileges (DBD ERROR: OCISessionBegin)

One of possible reasons for ORA-01031 is OS user is not added to either oinstall or dba group. But in my case, its there.

After a search, i found that the reason is lack of PASSWORD FILE. Then i created password file which solved the issue

[oracle@issgascop218 ~]$ cd $ORACLE_HOME/dbs
[oracle@issgascop218 dbs]$ orapwd file=orapw$ORACLE_SID password=oracle entries=1 force=y
[oracle@issgascop218 dbs]$ ls -ltr
total 16096
-rw-r—–  1 oracle dba    8385 Sep 11  1998 init.ora
-rw-r—–  1 oracle dba   12920 May  3  2001 initdw.ora
-rw-rw—-  1 oracle dba      24 Mar 22  2007 lkPAS
-rw-rw—-  1 oracle dba    1552 Mar 22  2007 hc_PAS.dat
-rw-rw—-  1 oracle dba    1552 May  9  2007 hc_PASQ.dat
-rw-rw—-  1 oracle dba      24 May  9  2007 lkPASQ
-rw-r—–  1 oracle dba    2430 May  9  2007 initPASQ.ora
-rw-r—–  1 oracle dba    2560 Dec 28  2007 spfilePAS.oraold
-rw-r—–  1 oracle dba    2393 Dec 28  2007 initPAS.ora
-rw-r—–  1 oracle dba    1536 Apr 30 11:54 orapwPAS
-rw-r—–  1 oracle dba    2560 Jul  1 22:00 spfilePAS.ora
-rw-r—–  1 oracle dba    3584 Jul 12 22:00 spfilePASQ.ora
-rw-r—–  1 oracle dba 7716864 Aug 23 18:30 snapcf_PASQ.f
-rw-r—–  1 oracle dba 8601600 Aug 23 18:57 snapcf_PAS.f
-rw-r—–  1 oracle dba    1536 Aug 24 07:54 orapwPASQ

[oracle@issgascop218 dbs]$ emca -config dbcontrol db -repos recreate

Last few lines of above command

INFO: Repository successfully created
Aug 24, 2010 8:01:56 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) …
Aug 24, 2010 8:03:32 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Aug 24, 2010 8:03:33 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://issgascop218:5500/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
So, whenever you get ora-01031 while performing some action on EM, plz do check if this solution works out

User not able to login through OEM

Have you any time tried to login as normal user like scott or HR into OEM and landed up with below message?

The application requires more database privileges than you have currently been granted. Click on Help to get more version specific information.

Unfortunately, when you click on HELP, it may not give proper details on which privilege is missing.

When checked at DB level, may be that user will have all the required privileges to connect to the database, but still login to OEM is impacted.

The solution for this is to grant SELECT_CATALOG_ROLE to that user.

sqlplus / as sysdba

SQL> grant select_catalog_role to schemaname;

for example, if you are facing issue with scott schema, then issue below command

SQL> grant select_catalog_role to scott;

After above grant, user should able to login to OEM.

But, even after granting the above role, still if you face issue, then you may need to grant select on dictionary views to that user. But do remember, with this user can able to retrieve dictionary information from oracle database. So, grant it only if it is very much required.

SQL> grant select any dictionary to scott;

Hope this helps…

Upgrade OEM 11g to 12C - Two System Approach

Pre-requisite before you start Upgrade –

Once the pre-requisite patches (PSU and 12c pre-upgrade patch) are done, login to OEM console.

Go to 11g OMS deployment page and Select UPGRADE TYPE.

Also one needs to restore the backup of the OMS Repository i.e. 11g OMS database on different host, which will be used for 12c upgrade.

Make sure database name stays same otherwise the upgrade will fail

ENTER HOST AND PORTS, WHERE YOU ARE GOING TO INSTALL NEW 12c OMS

ALSO PROVIDE THE TIME OF THE BACKUP, THE ONE YOU USED TO RESTORE THE DATABASE.

On the new host start the 12c installer

[oracle@Host11G em12c]$ ./runInstaller

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 400 MB.   Actual 3611 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 65535 MB    Passed

Checking monitor: must be configured to display at least 256 colors.    Actual 65536    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-07-12_06-24-41AM. Please wait ...[oracle@ushofsvpracb

PROVIDE MIDDLEWARE HOME LOCATION WHERE YOUR WEBLOGIC 10.3.5 AND ABOVE SERVER IS RUNNING. 

Also enter your Agent Base directory and Host name.

If you hit the above error, one needs to modify following parameters from Repository Database.

SQL> conn / as sysdba

Connected.

SQL> alter system set job_queue_processes=0;

System altered.

SQL> exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');

BEGIN dbms_scheduler.disable('SYS.GATHER_STATS_JOB'); END;

*

ERROR at line 1:

ORA-27476: "SYS.GATHER_STATS_JOB" does not exist

ORA-06512: at "SYS.DBMS_ISCHED", line 4407

ORA-06512: at "SYS.DBMS_SCHEDULER", line 2737

ORA-06512: at line 1

One has to run following pre-req checks and modify the parameters on the restored oms repository database.

SQL> conn / as sysdba

Connected.

SQL> alter system set job_queue_processes=0;

System altered.

SQL> show parameter nls_length

NAME                                 TYPE        VALUE

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

nls_length_semantics                 string      BYTE

SQL> grant execute on dbms_random to dbsnmp;

Grant succeeded.

SQL> grant execute on dbms_random to sysman;

Grant succeeded.

SQL> alter user oracle_ocm account unlock;

User altered.

SQL> sho parameter process

NAME                                 TYPE        VALUE

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

aq_tm_processes                      integer     1

cell_offload_processing              boolean     TRUE

db_writer_processes                  integer     2

gcs_server_processes                 integer     0

global_txn_processes                 integer     1

job_queue_processes                  integer     0

log_archive_max_processes            integer     4

processes                            integer     500

processor_group_name                 string

SQL> show parameter stream

NAME                                 TYPE        VALUE

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

streams_pool_size                    big integer 0

SQL> alter system set streams_pool_size=300m;

System altered.

 SQL> alter database add logfile group 4 '+DATA_PSFT_HR'  size 300M;

 Database altered.

SQL> alter database add logfile group 5  '+DATA_PSFT_HR'  size 300M;

 Database altered.

SQL>  alter database add logfile group 6 '+DATA_PSFT_HR'  size 300M;

 Database altered.

SQL> alter database drop logfile group 1;

 Database altered.

SQL> alter database drop logfile group 2;

 Database altered.

SQL> alter database drop logfile group 3;

 Database altered.
Now select the plugins required for the 12c OMS. 

Enter the WebLogic server credentials.

Now one needs to enter old OMS Repository connection string. What happens is that the link to old OMS Repository is created and is being used to sync data from old OMS to new OMS.

This information is also available at:

                /u01/app/oracle/Middleware/oms/install/setupinfo.txt

See below for information pertaining to your Enterprise Manager installation:

Use the following URL to access:

                1. Enterprise Manager Cloud Control URL: https://Host11G.searshc.com:7799/em

                2. Admin Server URL: https://Host11G.searshc.com:7101/console

The following details need to be provided during the additional OMS install:

                1. Admin Server Hostname: Host11G.searshc.com

                2. Admin Server Port: 7101

 NOTE:

 An encryption key has been generated to encrypt sensitive data in the Management Repository. If this key is lost, all encrypted data in the Repository becomes unusable. Please run following command to backup the Management Service configuration including the emkey and keep the backup on another system:

 emctl exportconfig oms -dir <backup location>

Since the OMS also needs to backup one needs to export the OMS configuration as follows and keep it some where safe. 
Note - This is one of the component needed as part of OMS backup strategy, others are OMS_HOME, GC_INST Home and Fusion MIDDLEWARE_HOME

[oracle@Host11G OEM12c]$ cd ../Middleware/oms/bin/

[oracle@Host11G bin]$ ./emctl exportconfig oms -dir /u01/app/oracle/OEM12c/emctl_confg_exp_DONOTDELETE

Oracle Enterprise Manager Cloud Control 12c Release 2

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

Enter Enterprise Manager Root (SYSMAN) Password :

ExportConfig started...

<Jul 15, 2013 11:18:33 AM CDT> <Info> <Security> <BEA-090905> <Disabling CryptoJ JCE Provider self-integrity check for better startup performance. To enable this check, specify -Dweblogic.security.allowCryptoJDefaultJCEVerification=true>

Machine is Admin Server host. Performing Admin Server backup...

Exporting emoms properties...

Exporting secure properties...

Export has determined that the OMS is not fronted

by an SLB. The local hostname was NOT exported.

The exported data can be imported on any host but

resecure of all agents will be required. Please

see the EM Advanced Configuration Guide for more

details.

Exporting configuration for pluggable modules...

Preparing archive file...

Backup has been written to file:/01/app/oracle/OEM12c/emctl_confg_exp_DONOTDELETE/opf_ADMIN_20130715_111837.bka

The export file contains sensitive data.

Please ensure that it is kept secure.

ExportConfig completed successfully!