<Insert Picture Here>

Oracle Enterprise Manager Grid Control:Day in the Life of An AdminWilson N. López – Solution Specialist

Day In the Life of An Admin Demo

Services Dashboard

Line of Business(LOB) Executive

Apps Manager /Apps Support

Services Homepage

Support Analyst

Helpdesk TicketIT Operations/DBA DB Performance page

Day in the Life of An AdminManagement Solutions

• Service Level Management• Application Performance management

• Database Diagnostics • Application Server Diagnostics

• Configuration Management• Integration with Helpdesk system (Remedy)

Day in the Life of An Admin Management Pack Focus

Oracle Database EM Grid Control tools used:

• Service Level Management Pack• Application Server Diagnostics Pack Plus• Database Configuration Management Pack• Database Diagnostics Management Pack• Database Tuning Pack• Remedy Connector

Day in the Life of An Admin Storyline

In a far away land…

1. The off-shore team makes some configuration changes while applying a patch to one of the production databases

2. The Applications team finishes rolling out the latest version of the Pet Store application from the vendor to take advantage of the newest features

Day in the Life Storyline

• C-level / VP reviews his enterprise applications dashboard

• Pet Store application is down

• Meanwhile, the admin team was already working to identify the problem

• The admin drilled down the application where Root-cause analysis indicates performance issues with database in supporting technology stack

• Service level transaction alerts show a critical transaction failing because it’s timing out within the database

• Automated database diagnostics shows two recommendations:

1. Kill the session responsible for row lock contention

2. Change the DB init parameter setting

Day in the Life Storyline

• The admin would like to validate these ADDM recommendations

1. Verifies the same SQL statement responsible for the lock from the application perspective

2. Confirms the difference between the current init parameter setting to the production gold image

• The admin resolves both issues using Enterprise Manager

• The row lock contention is fixed so the application can now update the database

• By updating the init parameter value to the original gold configuration standard, the database can run more efficiently

• Service is back up and running efficiently!

Oracle Enterprise Manager:Day in the Life

• Vignette 1 – Evaluating your Enterprise Services• Vignette 2 – Investigating Root-cause• Vignette 3 – Resolving Database Lock Contention• Vignette 4 – Resolving Production Configuration

Drift• Vignette 5 – Verifying Enterprise Services Health

Root Cause Analysis & Remediation of Situation

• Executive Level Dashboard

Root Cause Analysis & Remediation of Situation

• Pet Store Service Homepage

Root Cause Analysis & Remediation of Situation

• Root Cause Analysis Details

Root Cause Analysis & Remediation of Situation

• Topology view shows the service and supporting system components graphically.

Root Cause Analysis & Remediation of Situation

• Returning to the Home page to check all services

Root Cause Analysis & Remediation of Situation

• The alert details are listed and we see that a ticket was Updated.

Root Cause Analysis & Remediation of Situation

• EM has launched another window displaying the login screen for Remedy.

• From Remedy we see the ticket number that was created from the event details sent to it from EM via the Remedy Connector..

• We’ve just seen that the event information has already kicked off a ticket to remedy, let’s see what other service alerts we’ve looked at.

Root Cause Analysis & Remediation of Situation

• We saw it was the “Online Purchase” transaction that has been triggered..

•By examining them, we see that all 3 Beacons (user communities) are failing to complete the transaction. This indicates the issue is with the application and not the network in any of the Beacon locations.

•We saw earlier, Database was identified as possible cause of service failure., so let’s examine the Database in more detail.

Root Cause Analysis & Remediation of Situation

• The Database Home Page of a 10.2 database...•DBA received a call indicating that the response time of one of your applications is below expected limits. Your end users are experiencing unusually slow response time.

• This may very well mean that the performance of your database has changed. Indeed, in examining the Database Home Page you notice the performance of your database is below acceptable levels-.

Root Cause Analysis & Remediation of Situation

• The Performance page ...

• rolls up all critical metrics for an at-glance view of the overall performance of your database. Note the unusual amount of activity just happening on your system:

• On the top, you see a graph of the load on your system for the past few hours. In the middle you have Active Sessions, which is analogous to Load Average on the host.

Root Cause Analysis & Remediation of Situation

• The Performance page ...

•The window highlights application wait – “row lock contention” Let’s drill down further on the SQL.

•Click on the “SQL ID” link under “Top SQL Application”

Root Cause Analysis & Remediation of Situation

• The Performance page ...

•New we see the text of the SQL query, “UPDATE inventory SET qty = 10000 WHERE itemid = ‘EST-3’ Now that we’ve identified the culprit SQL, let’s see what ADDM has to offer us.•Let’s Return to the Database Central Location and then to the “Advisor Central”

Root Cause Analysis & Remediation of Situation

• Automatic Database Diagnostic Monitor (ADDM) Home Page ...

•ADDM has identified a couple issues. We’re interested in the Application specific findings. The impact % shows a 97.6% (+/-) impact. •Follow the line to the recommendations on the right to see there are 2 Application Analysis recommendations.•Click at the second line, “SQL statements were found waiting for row lock waits”.

Root Cause Analysis & Remediation of Situation

• Automatic Database Diagnostic Monitor (ADDM) Home Page ...

•The Action describes tracing the cause of the row contention in the application logic. Since we are trying to quickly identify the issue and get the application working again, we can kill the blocking session while the application developer lead examines the application logic.

Root Cause Analysis & Remediation of Situation

• Database Home Page ...•From the “Blocking Session” screen, we can select the Blocking SQL and Kill it.

•Once it’s killed, it won’t show any more blocking sessions.

•Now, we’ve resolved the 1st application specific issue ADDM identified, let’s look at the remaining one.

Root Cause Analysis & Remediation of Situation: Resolving Production Configuration Drift

• Automatic Database Diagnostic Monintor (ADDM) – Advisor Central Page •In the “Advisor Tasks”, select the other Application specific finding is around the usage of literals.

•Drilling down, we see the first Action is to set the parameter “cursor_sharing” to “force”. This is troublesome because these DB parameters are set and shouldn’t be changed without proper authority.

•We need to see if this parameter has changed from our original gold configuration.

Root Cause Analysis & Remediation of Situation: Resolving Production Configuration Drift

• Database Home Page •Click Software and Support.

•Click “Last Collected Configuration”•This shows us the latest configuration capture of the database.• Click “Refresh” to collect new values.• Click “Initialization Parameters” then “Next 25”.

Root Cause Analysis & Remediation of Situation: Resolving Production Configuration Drift

• Database Home Page •View “cursor sharing” and confirm the value is set to “EXACT”•Here we can confirm the “cursor_sharing” parameter is set to “Exact” while ADDM recommends “Force”. Before we make any changes, let’s compare it to our certified production standard configuration.

•Click “Compare Configuration” button at the top of the screen.•This is a list of all the other targets we can compare our database configuration to. We’re interested in comparing to our saved version instead of another live database.•Click “Saved Configurations” link• Select radio button for the line where “Description” is “Production DB Gold Configuration”

Root Cause Analysis & Remediation of Situation: Resolving Production Configuration Drift

• Database Home Page •View “cursor sharing” and confirm the value is set to “EXACT”•Here we can confirm the “cursor_sharing” parameter is set to “Exact” while ADDM recommends “Force”. Before we make any changes, let’s compare it to our certified production standard configuration.

•Click “Compare Configuration” button at the top of the screen.•This is a list of all the other targets we can compare our database configuration to. We’re interested in comparing to our saved version instead of another live database.•Click “Saved Configurations” link.• Select radio button for the line where “Description” is “Production DB Gold Configuration”.•Click “Compare” button

Root Cause Analysis & Remediation of Situation: Resolving Production Configuration Drift

•On the left is our live configuration and on the right is our saved gold image. The summary shows there are a lot of differences in each category, but we’re mainly interested in the Initialization parameters

•Click “Initialization Parameters” .•We see differences – Parameters that can change.•Examine cursor sharing value between PROD and gold configuration•Click on “PROD.oracleads.com” under “First Database”

• Compare Database Configuration – Results Page

Root Cause Analysis & Remediation of Situation: Resolving Production Configuration Drift

• Compare Database Configuration – Results Page •Click “Server”.

•Click “Initialization Parameters” under “Database Configuration”.•If you remember, there was an option to change the initialization parameter directly from ADDM, but we’ll do it manually since it’s a dynamic parameter.•Change “cursor sharing” value to “Force” •Click “Apply”

Root Cause Analysis & Remediation of Situation: Verifying Enterprise Services Health

• Database – Home Page •Click Database Instance: PROD.oracleads.com (breadcrumb)•Click Performance.•First, let’s see how the database has reacted to our changes. We see the “Application Wait” block has completely dropped off – now our database is behaving properly.

•Click “Services” •Enter “pet” in the search bar •Click Go.•We see our Pet Store service is back up.

Root Cause Analysis & Remediation of Situation: Verifying Enterprise Services Health

• Web Application – Home Page •Click DIL PET STORE •From the Pet Store homepage, we see the alerts have cleared and both of the critical transactions are successfully completing.

•Click “Test Performance”.•This shows the critical transactions performance from the different Beacons we have deployed. In this case, India, the U.K., and our Austin Data Center

Root Cause Analysis & Remediation of Situation: Verifying Enterprise Services Health

• Web Application/Service Monitoring Dashboard •Select “Online Purchase” from the “Key Test” dropdown•Click “Go”•The “Online Purchase” transaction has shown us some varying results over the past 24 hours. This is mostly because it’s a demo environment. If there is a spike from a single location, we know it is network and not the application.

•View Day in the Life – Services Monitoring Dashboard – do this by going back to the original screen from the first step

Day in the Life of An Admin Management Pack Focus

Oracle Database EM Grid Control tools used:

• Service Level Management Pack• Application Server Diagnostics Pack Plus• Database Configuration Management Pack• Database Diagnostics Management Pack• Database Tuning Pack• Remedy Connector