Skip navigation

The latest version of the ServiceNow ticketing connector comes pre-installed for Enterprise Manager Cloud Control 13c and does not need to be downloaded however, a set of pre-requisite actions must be performed before setting up the integration.

 

Pre-requisites:

  1. A service account is created in ServiceNow and appropriate permissions assigned. In most cases: itil, soap_create, soap_update, soap_query roles should be enough for OEM to automatically create, update, resolve and re-open a ticket in ServiceNow. Additionally, admin role can be assigned to the service account to close an incident.
  2. The target ServiceNow instance is configured for BasicAuth.
  3. OEM is configured for SSL based connectivity with ServiceNow.

 

Create a service account in ServiceNow:

Please refer to ServiceNow product documentation to create a new user.

 

Assign the required roles to service account in ServiceNow:

Please refer to ServiceNow product documentation to assign the required roles.

 

Configure ServiceNow for BASICAuth:

  • Login to ServiceNow.
  • In the Filter field, type script includes, then press Enter.
  • Click the New button on the Script Includes menu bar.
  • In the Name field, enter BASICAuth as the Name (using exact capitalization).
  • Replace the contents of the Script field with the following:

gs.include("BasicAuth");

var BASICAuth = BasicAuth;

Click on Submit.

 

Configure OEM for SSL connectivity with ServiceNow:

Access the following article on a web browser (ServiceNow KB0563633).

Copy the certificate Base64 content of the certificate from the section (Root Certificate Authority - Entrust Root Certification Authority - G2). This should only include the following and nothing else:

-----BEGIN CERTIFICATE----- <<<Certificate details>>> -----END CERTIFICATE-----

  • Login to the machine running OEM and navigate to OMS_home.
  • Take a backup of the b64LocalCertificate.txt file.

 

$ cp b64LocalCertificate.txt b64LocalCertificate.txt_orig

  • Edit the b64LocalCertificate.txt file and append the Entrust Root Certification Authority - G2 root certificate.
  • Restart OMS:

./emctl stop oms

./emctl start oms

[oracle@itnoesis-oem134 bin]$ ./emctl stop oms
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down
[oracle@itnoesis-oem134 bin]$

 [oracle@itnoesis-oem134 bin]$ ./emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
WebTier is Up
Oracle Management Server is Down
JVMD Engine is Down
BI Publisher Server is Up

[oracle@itnoesis-oem134 bin]$ ./emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
JVMD Engine is Up
Starting BI Publisher Server ...
BI Publisher Server Already Started
BI Publisher Server is Up
[oracle@itnoesis-oem134 bin]$


Activate ServiceNow connector in OEM:

To ensure that the connector has been downloaded:

Login to enterprise manager and go to Setup --> Extensibility --> Self Update

1.png

On the resultant page, click on “Management Connector”.

2.png

The “Management Connector Updates” page will show the details of available connectors:

3.png

 

Select the “ServiceNow Connector” and click on “Apply”. On the pop-up message, click on OK.4.png

5.png

6.png

 

Once the action completes successfully, the status will change to “Applied”.

7.png

 

Configure ServiceNow connector in OEM:

Go to Setup --> Extensibility --> Management Connectors

8.png

 

Select the ServiceNow connector type from the Create Connector drop-down list, then click Go.

9.png

 

Enter the connector name and description on the next screen, then click OK. The connector instance will now appear as unconfigured in the Connector Setup page.

10.png

11.png

 

Select the checkbox against the newly created connector and click on Configure.

12.png

 

In the resultant page, provide the servicenow SOAP urls for creating, fetching and updating a ticket in ServiceNow. In most cases, only the [InstanceName] is required to be updated.

Additionally, you can enable web console settings as well as grace period and retry options.

Once all the details are provided, click on OK.

13.png

 

Notice the warning message [Connection test failed, check the oms log file for details. The configuration was saved.]. It could be because of the following reasons:

  • Incorrect sys_id
  • Incorrect web service end-points.
  • Incorrect permissions to service account.
  • SSL is not enabled.

14.png

Once all the pre-requisites are met and the configuration is saved, the test will be successful and a green checkmark would appear against the configured connector name. (This also means that the connector has been enabled!)

15.png

What is a deadlock?

A deadlock occurs when two or, more sessions/ processes are waiting on each other to release the lock held by another sessions/ processes.
Once a deadlock scenario is detected, Oracle will perform a statement-level rollback to one of the sessions (deadlock participants). The session of which the statement is being rolled back, will encounter an “ORA-00060/ ORA-4020: Deadlock detected while waiting for resource.” error message.

How to troubleshoot a deadlock situation?

There are a ton of articles with several approaches on how to troubleshoot a deadlock situation including documents available on MOS. However, in my opinion, it boils down to:

  1. How can you avoid a deadlock scenario, and
  2. How to act once a deadlock situation is encountered.

So, I will not go into the troubleshooting details, however, please check the “references” section of this article to quickly browse through them if that is the intent.

How to monitor a deadlock?

In Oracle RAC, for both enqueue and library cache deadlock events (ORA-00060 and ORA-4020), a related generic message (Global Enqueue Services Deadlock detected) is recorded in the database alert log. The trace files which gets generated in each scenario, would have a difference in the naming convention. The trace file which gets generated as a result of enqueue deadlock will have the following naming convention: <SID>_ora_<pid>.trc.
On the other hand, if a deadlock was encountered because of library cache, the trace file will have the following naming convention: <SID>_lmd0_<pid>.trc.

You can easily find the alert and trace file location by executing the below command:

select di.value PATH, 'alert_' || i.instance_name || '.log' ALERT_LOG from v$diag_info di, v$instance i where di.name = 'Diag Trace';

While Oracle database is smart enough to handle a deadlock situation and perform the required action (statement rollback), often, it is necessary to be alerted if such a situation occurs. One obviously would not want too many deadlocks occurring in the database.
To achieve this, I will detail 3 methods to get alerted when a deadlock occurs in the database.

Method 1: OMC (Oracle Management Cloud)

OMC is the next generation cloud-based offering from Oracle with integrated monitoring, management and analytics. It works equally for your cloud or, on-premise environment.
If you have OMC configured and working, follow the below procedure to monitor, analyze and get alerted for a deadlock scenario.

Login to OMC and click on “Log Analytics”.
Log Explorer_1

In “Log Explorer”, Click on “Database Alert Logs”.
Log Explorer_2

Type or, copy/paste the following into the query criteria and click on “RUN”:

'Problem Priority' != null and Label = Deadlock | timestats count as logrecords by Entity | sort -logrecords

Additionally, you can specify the entity name to narrow to a specific instance or, instances:

'Problem Priority' != null and Label = Deadlock and Entity = 'My_prod_DB/database/dbprd1' | timestats count as logrecords by Entity | sort -logrecords
'Problem Priority' != null and Label = Deadlock and Entity in ('My_prod_DB/database/dbprd1' , 'My_prod_DB/database/dbprd2' , 'My_QA_DB/database/dbqa1' , 'My_QA_DB/database/dbqa2' | timestats count as logrecords by Entity | sort -logrecords

Log Explorer_3

Based on the date criteria defined (can be easily changed from the dropdown on top right hand corner), it will instantly populate the graph with deadlock occurrences.
Log Explorer_4

While you can choose to analyze the details, click on “Save as” to either add the graph as custom widget to an existing OMC dashboard and / or, setup an alert rule.
The notifications section of the pop-up window lets you choose on who should be alerted and how. Click on “Save” and you will get notified based on the settings you chose.
Log Explorer_5

Method 2: OEM Monitoring Template

Oracle Enterprise has always been a preferred choice when monitoring of Oracle environment is concerned. On your OEM instance, click on Enterprise  and navigate to Monitoring –> Monitoring Templates.
Deadlock_OEM_1

Select the monitoring template you have configured for target type: “database instance” and click on Edit.
Deadlock_OEM_2

Provide / add ORA-0060 and ORA-4020 as one of the criteria for warning or, critical thresholds and click on OK. Based on the incident rules you have configured for this monitoring template, you will get alerted once a deadlock condition is logged in the alert log.
Deadlock_OEM_3

Method 3: Custom Script

Use the below script to get an e-mail-based alert when a deadlock situation occurs and “Global Enqueue Services Deadlock detected” message appears in the database alert log.

The script can be executed as part of OEM custom metric or, job and can also be added as part of cron.

 

SET SERVEROUTPUT ON;
SET DEFINE OFF;
DECLARE
LONG_RUNNING NUMBER := 0;
P_SMTP_HOST VARCHAR2(30) := 'your_smtp_server.yourcompany.com'; --< Provide the SMTP Host name here
P_SMTP_PORT NUMBER := 25;
V_DB_NAME VARCHAR(20);
V_ALERT_TIME VARCHAR(20);
V_ALERT_MESSAGE VARCHAR(2000);
P_FROM VARCHAR2(30) := 'noreply@your_company.com'; --< Provide the sender's e-mail address here. This must be one e-mail address only
P_RECIPIENTS VARCHAR2(500) := 'Support_DL_1@your_company.com, Support_DL_2@your_company.com'; --< Provide comma separated list of recipients.
P_SUBJECT VARCHAR2(100);
P_MESSAGE VARCHAR2(4000);
L_MAIL_CONN UTL_SMTP.CONNECTION;


BEGIN
SELECT COUNT (*) INTO LONG_RUNNING from (SELECT originating_timestamp, message_text
FROM v$diag_alert_ext
WHERE originating_timestamp > cast(sysdate-0.25/24 as timestamp)
AND message_text like '%Global Enqueue Services Deadlock detected%')
WHERE rownum = 1;
dbms_output.put_line('Number of Deadlock occurances in last 15 mins: ' || LONG_RUNNING);
IF LONG_RUNNING > 0 THEN
--dbms_output.put_line('Let us send email');
DECLARE my_recipients VARCHAR2(32000);
location NUMBER := 0;
my_index NUMBER := 1;
BEGIN


SELECT * INTO V_DB_NAME, V_ALERT_TIME, V_ALERT_MESSAGE 
from (SELECT b.instance_name, TO_CHAR (a.originating_timestamp, 'DD-MON-YYYY HH24:MI:SS') alert_time, a.message_text alert_message
FROM v$diag_alert_ext a, v$instance b
WHERE a.originating_timestamp > cast(sysdate-0.25/24 as timestamp)
AND a.message_text like '%Global Enqueue Services Deadlock detected%')
WHERE rownum = 1;
dbms_output.put_line('Number of Deadlock occurances in last 15 mins: ' || LONG_RUNNING);


P_SUBJECT := 'Deadlock Alert' || '-' || V_DB_NAME;
P_MESSAGE := 'A deadlock has been detected in ' || V_DB_NAME || '. ' ||
'Please see below details and take appropriate action: ' || CHR(10) || CHR(10) ||
' ============================================= ' || CHR(10) || 
' Deadlock Occurred at: ' || V_ALERT_TIME || CHR(10) || CHR(10) ||
' Alert Message: ' || V_ALERT_MESSAGE || CHR(10) ||
' ============================================= ' || CHR(10) || CHR(10) ||
'Regards,' || CHR(10) ||
'OEM Monitoring-Your Team';
L_MAIL_CONN := UTL_SMTP.open_connection(P_SMTP_HOST, P_SMTP_PORT);
UTL_SMTP.helo(L_MAIL_CONN, P_SMTP_HOST);
UTL_SMTP.mail(L_MAIL_CONN, P_FROM);


my_recipients := RTRIM(P_RECIPIENTS,',; ');
my_index := 1;


WHILE my_index < LENGTH(my_recipients) LOOP
location := INSTR(my_recipients,',',my_index,1);
IF location = 0 THEN
location := INSTR(my_recipients,';',my_index,1);
END IF;


IF location <> 0 THEN
UTL_SMTP.RCPT(L_MAIL_CONN, TRIM(SUBSTR(my_recipients,my_index,location-my_index)));
my_index := location + 1;
ELSE
UTL_SMTP.RCPT(L_MAIL_CONN, TRIM(SUBSTR(my_recipients,my_index,LENGTH(my_recipients))));
my_index := LENGTH(my_recipients);
END IF;
END LOOP;


my_recipients := REPLACE(my_recipients,';',',');
UTL_SMTP.open_data(L_MAIL_CONN);
UTL_SMTP.write_data(L_MAIL_CONN, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(L_MAIL_CONN, 'To: ' || my_recipients || UTL_TCP.crlf);
UTL_SMTP.write_data(L_MAIL_CONN, 'From: ' || P_FROM || UTL_TCP.crlf);
UTL_SMTP.write_data(L_MAIL_CONN, 'Subject: ' || P_SUBJECT || UTL_TCP.crlf);
UTL_SMTP.write_data(L_MAIL_CONN, 'Reply-To: ' || P_FROM || UTL_TCP.crlf || UTL_TCP.crlf);


UTL_SMTP.write_data(L_MAIL_CONN, P_MESSAGE || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.close_data(L_MAIL_CONN);


UTL_SMTP.quit(L_MAIL_CONN);


END;
END IF;
END;
/

Additional documents for reference:

Document 1443482.1 Troubleshooting “Global Enqueue Services Deadlock detected”
Document 62365.1 What to do with “ORA-60 Deadlock Detected” Errors
Document 62354.1 TX Transaction locks – Example wait scenarios
Document 262226.1 Deadlock Error Not in Alert.log and No Trace File Generated on OPS or RAC (this document covers Global Wait-For-Graph(WFG))

Originally posted on: https://itnoesis.com/