Database Connection Management with ORDS Instance API

INSTANCE API

Oracle REST Data Services (ORDS) offers a powerful solution for managing connection pools, enabling efficient communication between applications and databases. The Instance API, introduced in ORDS 20.4, revolutionizes connection pool monitoring by providing a dedicated interface to assess the health and performance of these pools. This API equips administrators with the ability to easily track the status of numerous connection pools across various setups, making it a valuable tool for optimizing system-wide performance and resource allocation. In this overview, we delve into the key features of the Instance API, its setup requirements, and its role in enhancing connection pool management.

Step 1:Verifying the Apex and tomcat services are working,

Step 2: Bringing down the tomcat services,

Step 3: Configuring the INSTANCEAPI:

[test@demoapex2 ords]$ java -jar ords.war user instanceAPI “Listener Administrator,System Administrator” Enter a password for user instanceAPI: welcome@2023

Confirm password for user instanceAPI: welcome@2023

2023-05-01T09:16:44.906Z INFO Created user: instanceAPI in file: /u01/ords/conf/ords/credentials

[test@demoapex2 ords]$ java -jar ords.war set-property instance.api.enabled true

2023-05-01T09:17:35.465Z INFO Modified: /u01/ords/conf/ords/defaults.xml, setting: instance.api.enabled = true

Step 4:Saving the css file in pool.html in the /u01/ords/conf/ords

<html>

<head>

var refreshInteval;

function poolListing(){

// populate the list of pools

fetch(‘https://192.168.0.1:8080/ords/instanceAPI/_/instance-api/stable/database-pools-cache/’,

{ headers: {‘Authorization’: ‘Basic ‘ + btoa(‘instanceAPI:<password>’)} }

)

.then(response => response.text())

.then((response) => {

items = JSON.parse(response).items;

var pools = document.getElementById(“pools”);

for( item in items) {

pool = document.createElement(“option”)

pool.text =items[item].id

pool.value =items[item].links[0].href;

pools.add(pool);

}

});

// if change in pool cancel interval

if ( refreshInteval ) {clearInterval(refreshInteval); }

// update stats every 250ms

refreshInteval = setInterval(reloadData, 250);

}

function reloadData(){

// Get the pool data based on the select list of pools

fetch( document.getElementById(‘pools’).selectedOptions[0].value,

{ headers: {‘Authorization’: ‘Basic ‘ + btoa(‘instanceAPI:<password>’)} })

.then(response => response.text())

.then((response) => {plot(JSON.parse(response))});

}

function updateTable(data){

Object.keys(data.statistics).forEach(function(key) {

var value = data.statistics[key];

var old = document.getElementById(key + “_value”).innerHTML;

// Mark up changes to be obvious

if ( value != old ) {

document.getElementById(key + “_value”).innerHTML = value;

document.getElementById(key + “_value”).style.color =”red”;

document.getElementById(key + “_value”).style.fontSize =”xx-large”;

} else {

document.getElementById(key + “_value”).innerHTML = value ;

document.getElementById(key + “_value”).style.color =”black”;

document.getElementById(key + “_value”).style.fontSize =”initial”;

}

});

}

function plot(data){

if ( ! document.getElementById(‘stats’) ) {

// Make the new Table

txt = “<table id=’stats’ border=’0′>”;

Object.keys(data.statistics).forEach(function(key) {

var value = data.statistics[key];

txt += “<tr><td style=’font-size:xx-large;’ id='” + key + “‘>” + key + “</td><td id='”+key+”_value’ style=’font-family:Hack;’>” + value+ “</td></tr>”;

});

txt += “</table>”;

document.getElementById(“poolinfo”).innerHTML = txt;

} else {

// Update the values

updateTable(data);

}

poolListing();

</script>

</head>

<body>

<div style=’font-family:Hack;font-size:xx-large;’>Choose a Pool to monitor:<select style=’font-family:Hack;font-size:xx-large;’ name=”pools” id=”pools” onchange=”pickAPool()”></select></div>

</body>

</html>

Step 5: Saved the html file in the ords location: