The Database Tools Service in Oracle Cloud Infrastructure (OCI)

Service Overview

In November of 2021, the Oracle Database Development Tools team launched a service in OCI. This service, called the DB Tools Service, aimed to create a reusable resource for connecting to cloud databases by consolidating all the pieces of information you need to have to connect to a database. No need to remember that the database on the server called ncr3342szw is development while the one on ncr3342szx is UAT. Or remembering passwords for database connections which someone inevitability locks the account for too many failed attempts at least once a week. Add the ability to use OCI policies and define dynamic groups who can use these connections; we can securely store the hostname, port, database name, username, and password creating a reusable, portable resource that can be utilized across clouds, development tools, services, and pipelines.

Connections

We must create one before we can utilize these database connections across OCI or development tools. Throughout this service, we aimed to aid the user as much as possible to help discover database details where ever we could. We wanted the service to be used not only by DBAs but by application developers, citizen data scientists, anyone who wants to use a database connection. One of the features added to the service to help guide users is found in the Create Connection user flow.

Someone using a database for the first time may not understand the critical information they need. Hostnames, ports, and database identifiers are not always easily found, let alone given to anyone other than DBAs. This lack of database connection information is a point we immediately address when creating a connection with the Database Tools Service.

After naming the connection, all you need to do is choose a database type from an Autonomous database, a database created on a VM, or a database created on an Exadata Infrastructure. Once that option is selected, available databases are pre-filled in the database select list. Once you find the one you want to work with (and policies can be placed on these databases so only ones that a user should see will be shown), select it. The service will pre-fill all other connect information such as database home, database name, and then let you select a PDB to use. In the case of databases on Exadata Infrastructure, you can choose from among database homes as well. Once the database you want to use is selected, you can see that the connect string is created for you automatically.

Next up are usernames and passwords. We can approach this in two ways. One is that whoever creates the service has the username and password for the database they want to connect. If so, the DB Tools Service will help you store this password securely, encrypted in an OCI vault. Another use case could be that the user comes to the service with a username, and they can select the password using the dropdown; they just look for the entry that matches their username. Whichever way is chosen, they enter the user and password, and the connection details are complete.

Private Endpoints and Database Connections

Depending on how you have the database set up, a Private Endpoint may be needed. So, what is a private endpoint, and why do I need one? 

Cloud Databases are usually protected by multiple layers of network security so that they are isolated from the public network and only allow access from particular IPs or applications. For the Database Tools service to access these database services, a path into a virtual cloud network (VCN) must exist so that the service can connect to these databases.

The diagram below shows that the DB Tools Service can deploy a private endpoint into a private subnet in a VNC. The private endpoint is then given an IP address from that subnet and acts as a secure bridge from the database tools service to that secure database with a private IP in that private subnet.

To note here, only the database tools service can utilize this private endpoint. It cannot be used independent of the Database Tools Service or by any other service in OCI and must be created by an end-user. The service will not self/auto-create private endpoints on behalf of a connection or when it sees that database is using private IPs. OCI policies also control the creation of private endpoints; just having the ability to use or create a connection in the Database Tools Service does not entitle a user to create Private Endpoints. You can read about the Database Tools Service policies here and how we recommend splitting up into different management roles within the service.

An excellent example of using a Private Endpoint with the Database Tools Service is when you want to connect to a database that is using a private IP address. These databases are usually deployed with multiple layers of security such as hardened Subnet-wide Security Lists and Network Security Groups; configured to only allow communication over a specific port to and from a particular IP address. Looking at this database that was created in a private subnet, it is now assigned the address of 10.0.1.60.

We can start by adding a Network Security Group to the DB VM instance, allowing us to add specific firewall rules to the VNIC of the DB System so we can restrict traffic. Network Security Groups offer a more granular level of security than Subnet level Security Lists.

After the Network Security Group (NSG) has been added to the DB VM, we can go find the details of our Private Endpoint for our Database Tools Service.

Here we want to take note of the two Reverse Connection Source IPs. Once we have those, we can add these IP addresses to the NSG with access over port 1521:

Now, using the Validate button on the Database Tools Connection Details page, we can test the connection and see that we can access the Database.

Wallets Details

Some connections need to include a wallet or java keystore for additional security. The Wallet details step allows you to upload these credentials or let the service automatically get the wallet from the autonomous database you are using. Wallets are then encrypted and stored in the OCI vault and only used when connecting to the database.

You can read about mTLS and the autonomous database here.

SQL Worksheet

SQL Worksheet is available to use with any Database Connection you have access to use. Run SQL commands right from the OCI console in your browser.

The SQL Worksheet also has some tricks such as code insight similar to SQL Developer

code case conversion

save and load SQL scripts from your local drive or from cloud object storage

the ability to change your connection right in the SQL Worksheet and start working with another database immediately

and you can even download the results of a SQL query in multiple different formats

Connection Details Page

The Connection Details page has some valuable features to help you get connected to the database as quickly as possible. You can see the Launch SQLcl button at the top of the page.

This button will open up the OCI Cloud Shell and log you directly into the database with SQLcl. Additionally, suppose the database you are connecting to is in a private subnet using a Private Endpoint. In that case, the Database Tools Service will use the OCI Bastion as a Service to create a connection right there in the UI for you.

and then proceed to log you into the database with SQLcl via an SSH Tunnel.

Use Cases

Secure Database Tools Connections with OCIDs in OCI via Policies

So now that we can store database connection information as a resource, what can we do with it? To start, we can secure them down to the resource ID/OCID. Database Connections are a top-level resource in OCI which means you can reference that OCID in policies. You can create a group/dynamic group consisting of a specific user class then assign or restrict access to a connection or an entire set of connections within a particular compartment.

For example, we have a set of connections we want to give to data scientists in an OCI group called Analytics.

Now, assign users to this group

Once the users are in the group, we can create a policy that restricts access to Database Tools Connections in a specific compartment (the Data_Science compartment). The following table is from the documents for the service just updated to reflect the groups and compartments we are using as an example.

allow group Analytics to use database-tools-connections in compartment Tools_PM

allow group Analytics to read database-family in compartment Tools_PM

allow group Analytics to read autonomous-database-family in compartment Tools_PM

allow group Analytics to read secret-family in compartment Tools_PM

allow group Analytics to read database-tools-family in compartment Tools_PM

Just create the policy in the Identity & Security category in the OCI console, and the connection is secured.

Suppose you want to restrict access to a specific connection. In that case, you can add the OCID of a connection to the

allow group Analytics to use database-tools-connections in compartment Tools_PM

policy. For example, you wanted to restrict this groups access to just one connection, you would add:

allow group Analytics to use database-tools-connections in compartment Tools_PM where target.DatabaseToolsConnection.id = 'ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6'

or a group of connection OCIDs:

allow group Analytics to use database-tools-connections in compartment Tools_PM where

any

{target.DatabaseToolsConnection.id = 'ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6',  

 target.DatabaseToolsConnection.id = 'ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.a443xxsedsda443xxsedssd443xxsedsdxama443xxsedsdxa443xxsedsdx',

 target.DatabaseToolsConnection.id = 'ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.addddaau1XXXaddddaau1XXXaddddaau1XXXaddddaau1XXXaddddaau1XXX'}

SQLcl and Database Tools Connections with OCIDs

Say goodbye to TNS Names files and hello to Database Tools Connections with OCIDs. No more do you need to remember connection strings, username, and passwords in SQLplus. Use the Database Tools Connection OCID and SQLcl to log in directly to the database. On the Connections details page, just copy the OCID of the connection.

Set your OCI profile in SQLcl

SQL> oci profile DEFAULT

(More on how to do that here)

and connect via the OCID.

And that’s it; you are connected to the database with the Tools Service doing all the heavy lifting.

OCI DevOps pipelines with Database Tools Connection OCIDs for Deployment

We have written a lot on CICD with the database using SQLcl (more here), but in an upcoming article, we will be going over pipelines to test code and deployment. One of the gotchas to watch out for in these pipelines is passing sensitive information such as IP addresses, usernames, and passwords. With that in mind, we can combine the above two use cases to create a secure connection to specific deployment targets within CICD pipelines.

You can create a Dynamic Group in OCI that contains just a compute instance you want to use as a deployment platform using policy security. For example, here is a sample Dynamic Group Policy with the OCID of a compute instance that is contained in a private subnet in OCI:

instance.id = 'ocid1.instance.oc1.eu-frankfurt-1.antheljantheljantheljantheljantheljantheljantheljantheljantheljantheljantheljanthelj'

Now that the dynamic group is defined, we can create policies for this group and the Database Connection OCID:

allow dynamic-group DeploymentInstance to read database-family in compartment Tools_PM

allow dynamic-group DeploymentInstance to read autonomous-database-family in compartment Tools_PM

allow dynamic-group DeploymentInstance to read secret-family in compartment Tools_PM

allow dynamic-group DeploymentInstance to read database-tools-family in compartment Tools_PM

allow dynamic-group DeploymentInstance to use database-tools-connections compartment Tools_PM where target.DatabaseToolsConnection.id = 'ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6'

Then, when creating our deployment configuration in OCI DevOps for a deployment pipeline, we can include the following code in the YAML file. Combine connecting DB Tools Service Connection OCIDs with Liquibase in SQLcl and you have a secure full database CICD pipeline!

    command: |

        cd /var/lib/ocarun/

        OCI_SQLCL_CONFIG_FILE=/var/lib/ocarun/.oci/config

        export OCI_SQLCL_CONFIG_FILE

        env

        {

        echo “oci profile DEFAULT”

        echo "conn ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6"

        echo “lb update -changelog controller.xml”

        echo “exit”

        } | sql /nolog

No passwords or sensitive information in the logs or any static files plus by using the policies, we have secured this connection to only run on the compute defined in the Dynamic Group.

Wrap-up

The Database Tools Service brings order to connection chaos by consolidating all the essential information for database access. Reusable, secure, and flexible resources can be used across OCI services and locally when using development tools such as SQLcl. What’s even more exciting is that the service is just getting started. We have many new features and enhancements coming in the near and far future that deal with additional database flavors, global logins, and bringing SQL Developer Web to the OCI Web Console.

Just click Database Tools in the Developer Services category on the OCI Web Console Menu to get started.

Source: oracle.com

Continue reading

Data Safe and Cloud Guard: A Match Made in Heaven

The rise of cloud adoption has come with a parallel rise of cloud security posture management (CSPM) as a tool to help organizations manage the security of their cloud tenancies. It’s no wonder that Oracle Cloud Guard has been so well received in the last year, with its improved visibility to identify misconfigured resources, possible vulnerabilities with Oracle Cloud Infrastructure Vulnerability Scanning Service (OCI VSS), and insecure activities across tenants. With Cloud Guard, security administrators can identify, prioritize, and remediate issues before they get out of control.

At the same time, organizations rely on databases to manage their most critical asset: the data. But if that data is not well protected, it can become a big liability. Today, Cloud Guard already monitors database configurations with detectors that check for public access of databases, databases without automatic backups, or possible problems when databases do not have the latest patches. Some existing database-specific detectors include identifying if:

◉ an automatic backup isn't enabled for a database

◉ an available database patch has not been applied within your specified number of days

◉ a database system has a public IP address assigned

◉ a database is publicly accessible

◉ an available database system patch has not been applied.

◉ a database is running on an unsanctioned software version

There are additional aspects you should consider to further strengthen your databases security posture. You should make sure your databases are configured securely, you should understand your users, and you should monitor critical database activities and protect any sensitive data you are storing in your databases. Oracle is helping to address these challenges with Oracle Data Safe. Data Safe provides essential security capabilities for your Oracle databases, whether they are running on-premises, in the cloud, or in Oracle Cloud@Customer deployments. Data Safe empowers organizations to assess configuration risks, evaluate database users, manage audit settings, analyze database activity, discover sensitive data stored in databases and mask sensitive data for use in non-production copies of databases – all in a single, unified console.

Oracle Cloud Guard has continued to expand its capabilities through integration with other services like Certificates, Bastions and Vulnerability Scanning Service, and we are excited that there is now an integration with Oracle Data Safe as well. Where Cloud Guard watches for security concerns in the cloud infrastructure, Data Safe provides a single security tool to monitor the security posture of your databases. The integration of the two services allows customers to get not just visibility of database configurations but further data security monitoring.

New detectors called “Data Safe is not enabled” and “Database not registered with Data Safe” allow customers to confirm that Data Safe is enabled and that their cloud databases are being monitored by Data Safe. For the latter detector, the Cloud Guard problem summary will indicate the list of cloud databases that are not yet registered in Data Safe. Registering databases in Data Safe (cloud and on-premises) takes only a few minutes and is easy and convenient with target registration wizards that guide you step-by-step through the process. And while the Cloud Guard detector only indicates any cloud database that is not yet registered, you can easily register your on-premises databases as well, helping you to achieve a fleetwide view of database security. After registration, you will get immediate visibility on some potential risks, including misconfigurations, as well as an overview of all highly privileged accounts across your database fleet.

This is just the first step of integration between Cloud Guard and Data Safe. More detectors are planned to provide security administrators with a single view to not only see the security and posture management of the infrastructure, but also see risks detected around the configuration, data and users of databases. This combination of information integrates two key cloud security services with security information at the fingertips of the security administrators. The combination of the services helps enable the security experts to successfully manage their databases and infrastructure and prioritize strategic work where it is needed.

If you are already an OCI customer, you will be able to easily turn on Cloud Guard for your tenancy and see the visibility of Data Safe in Cloud Guard for yourself. We think you will be as excited as we are for the next step of integration.

Source: oracle.com

Continue reading

Database Recovery Techniques in DBMS

Database systems, like any other computer system, are subject to failures but the data stored in it must be available as and when required. When a database fails it must possess the facilities for fast recovery. It must also have atomicity i.e. either transactions are completed successfully and committed (the effect is recorded permanently in the database) or the transaction should have no effect on the database.

There are both automatic and non-automatic ways for both, backing up of data and recovery from any failure situations. The techniques used to recover the lost data due to system crash, transaction errors, viruses, catastrophic failure, incorrect commands execution etc. are database recovery techniques. So to prevent data loss recovery techniques based on deferred update and immediate update or backing up data can be used.

Recovery techniques are heavily dependent upon the existence of a special file known as a system log. It contains information about the start and end of each transaction and any updates which occur in the transaction. The log keeps track of all transaction operations that affect the values of database items. This information is needed to recover from transaction failure.

◉ The log is kept on disk start_transaction(T): This log entry records that transaction T starts the execution.

◉ read_item(T, X): This log entry records that transaction T reads the value of database item X.

◉ write_item(T, X, old_value, new_value): This log entry records that transaction T changes the value of the database item X from old_value to new_value. The old value is sometimes known as a before an image of X, and the new value is known as an afterimage of X.

◉ commit(T): This log entry records that transaction T has completed all accesses to the database successfully and its effect can be committed (recorded permanently) to the database.

◉ abort(T): This records that transaction T has been aborted.

◉ checkpoint: Checkpoint is a mechanism where all the previous logs are removed from the system and stored permanently in a storage disk. Checkpoint declares a point before which the DBMS was in consistent state, and all the transactions were committed.

A transaction T reaches its commit point when all its operations that access the database have been executed successfully i.e. the transaction has reached the point at which it will not abort (terminate without completing). Once committed, the transaction is permanently recorded in the database. Commitment always involves writing a commit entry to the log and writing the log to disk. At the time of a system crash, item is searched back in the log for all transactions T that have written a start_transaction(T) entry into the log but have not written a commit(T) entry yet; these transactions may have to be rolled back to undo their effect on the database during the recovery process

◉ Undoing – If a transaction crashes, then the recovery manager may undo transactions i.e. reverse the operations of a transaction. This involves examining a transaction for the log entry write_item(T, x, old_value, new_value) and setting the value of item x in the database to old-value.There are two major techniques for recovery from non-catastrophic transaction failures: deferred updates and immediate updates.

◉ Deferred update – This technique does not physically update the database on disk until a transaction has reached its commit point. Before reaching commit, all transaction updates are recorded in the local transaction workspace. If a transaction fails before reaching its commit point, it will not have changed the database in any way so UNDO is not needed. It may be necessary to REDO the effect of the operations that are recorded in the local transaction workspace, because their effect may not yet have been written in the database. Hence, a deferred update is also known as the No-undo/redo algorithm

◉ Immediate update – In the immediate update, the database may be updated by some operations of a transaction before the transaction reaches its commit point. However, these operations are recorded in a log on disk before they are applied to the database, making recovery still possible. If a transaction fails to reach its commit point, the effect of its operation must be undone i.e. the transaction must be rolled back hence we require both undo and redo. This technique is known as undo/redo algorithm.

◉ Caching/Buffering – In this one or more disk pages that include data items to be updated are cached into main memory buffers and then updated in memory before being written back to disk. A collection of in-memory buffers called the DBMS cache is kept under control of DBMS for holding these buffers. A directory is used to keep track of which database items are in the buffer. A dirty bit is associated with each buffer, which is 0 if the buffer is not modified else 1 if modified.

◉ Shadow paging – It provides atomicity and durability. A directory with n entries is constructed, where the ith entry points to the ith database page on the link. When a transaction began executing the current directory is copied into a shadow directory. When a page is to be modified, a shadow page is allocated in which changes are made and when it is ready to become durable, all pages that refer to original are updated to refer new replacement page.

Some of the backup techniques are as follows :

◉ Full database backup – In this full database including data and database, Meta information needed to restore the whole database, including full-text catalogs are backed up in a predefined time series.

◉ Differential backup – It stores only the data changes that have occurred since last full database backup. When same data has changed many times since last full database backup, a differential backup stores the most recent version of changed data. For this first, we need to restore a full database backup.

◉ Transaction log backup – In this, all events that have occurred in the database, like a record of every single statement executed is backed up. It is the backup of transaction log entries and contains all transaction that had happened to the database. Through this, the database can be recovered to a specific point in time. It is even possible to perform a backup from a transaction log if the data files are destroyed and not even a single committed transaction is lost.

Source: geeksforgeeks.org

Continue reading

How to boost data-driven marketing?

According to McKinsey “Savvy marketers are rethinking their tech and data strategies to double down on precision marketing following COVID-19. By capturing new data, searching for new behavioral relationships, and enabling rapid experimentation, marketers can seize granular growth opportunities and enter the recovery with significantly greater ROI and resilience.”

As opposed to reverting to mass communications and promotions, marketers who can rapidly integrate new data sources and update their modeling approach will be able to spot emerging trends, shifts in customer behaviors, and target specific groups with tailored messaging, content, and offers.

Technology is key to this equation. It is therefore not surprising that according to Gartner, “marketing analytics proficiency is critical for success.”

How can marketers and data analysts boost data-driven marketing initiatives to deliver business growth—while ensuring data governance and security?

Real-time insights to drive growth

Let’s consider the following customer stories outlining how, with Oracle’s solution, marketing teams have been able to dramatically improve their data analytics processes—increasing customer satisfaction, and revenue.

Benefit One offers membership-based services to about 11,000 organizations and 9 million people in Japan. The Benefit One product marketing department had been handling data aggregation with a standalone tool to extract data about member trends, then manually aggregated and analyzed that data with spreadsheets. Using Oracle Autonomous Database for analytics and data warehousing, along with Oracle Analytics Cloud, allowed the company to fully automate the processing of massive datasets, and eliminate the need to manually extract data. Report generation time was slashed by 90%. With daily reports, Benefit One gained a real-time understanding of changes in the services members are using. Having current data lets the company create more effective marketing plans, like quickly deploying new sales promotions. “Oracle Autonomous Database allowed us to raise our productivity to a startling degree, but what caught us off guard was how helpful it was to even the heads of business departments with no IT experience.” said Masami Matsuzaki, Head of Data Management Group, Product Marketing Department, Benefit One.

Smartclip helps its customers plan, create, target, and analyze digital advertising on platforms such as news sites, blogs, social platforms, and smart TVs. Smartclip had been storing its data, as well as the performance data from campaigns, on many different and siloed platforms, which meant it couldn’t easily be analyzed. Plus, using spreadsheets made work slow and risked generating multiple versions of the truth. Smartclip’s Oracle solution lets it combine data from more than 20 disparate sources into Oracle Autonomous Database for analytics and data warehousing—allowing the company to drive secure collaboration around a single source of truth. Smartclip has gone from seeing profitability in five areas to a breakdown along 700 product lines. The company went from weekly to daily updates, with reports on all advertising campaigns in about 15 minutes—allowing them to react more quickly to optimize campaigns. The solution also freed Smartclip analysts from manual work with spreadsheets and opened up a new level of self-service analytics—business users can now access, prepare, collaborate on, analyze, and share data and insights for much faster service to customers. “Our children don't know what life without a mobile phone is like, they think it's wonderful and can't imagine it any other way. That’s the way I feel about Autonomous Database. Many more will probably feel this way in the future." said Alexys Urvoy." Head of Business and Competitive Intelligence, SmartClip

Of the 100,000-plus gas stations across the US, roughly 65% are independently owned under a patchwork of local groups. Drop Tank aims to stitch these stations together to give major fuel brands as well as independent owners a much better loyalty program. Drop Tank uses Autonomous Database for analytics and data warehousing with Oracle Analytics Cloud to analyze daily point-of-sale fuel data from the pump, as well as packaged goods sales data inside the convenience stores. Through customer segmentation and prediction models, it creates targeted promotions and measures marketing campaign effectiveness. Not only does the Oracle data platform help Drop Tank reduce time to market and scale, but it eliminates database administration such as provisioning, tuning, securing, and patching. Instead of hiring more database administrators to support its rapid growth, the loyalty company staffs data scientists who can take advantage of the tools to experiment with marketing campaigns in days compared to weeks or months.

A complete, self-service, secure data analytics solution

Oracle delivers a complete, self-service, secure data analytics solution empowering marketing teams to rapidly get the deep, trustworthy, data-driven insights they need to make quick decisions.

The architecture of the solution is represented below:

Data from all sources and formats can be combined in Autonomous Database for analytics and data warehousing. It is the only cloud data warehouse that is autonomous, self-service, and complete, providing marketing teams with a comprehensive suite of built-in tools:

◉ Data tools enable self-service drag-and-drop data loading, data transformation, and business modeling. Marketing analysts can automatically discover insights with machine learning algorithms—no coding required—saving them significant time and effort.

◉ Built-in graph analytics enables analysts to visualize relationships and connections between data entities. They can for example instantly see connections among users to identify influencers and build communities on social media, and obtain a 360-degree view of their customers.

◉ With built-in spatial analytics, they can rapidly answer marketing questions such as “what were the results of our location-based push notifications?”, or “where are customers corresponding to specific sociodemographic characteristics?”

◉ Marketing analysts can build machine learning models—with a no-code interface for business users—to predict likely outcomes, e.g., customers with the highest propensity to purchase a given offering, actions to reduce customer churn, optimal lead scoring based on patterns…etc

◉ With the built-in Oracle APEX low-code development platform, marketing teams can quickly develop applications for ad hoc needs and gaps/processes handled outside of their CX application—without having to join a queue of IT projects. Such applications can include a calendar of worldwide marketing activities that anyone can update, a success story hub platform that all regions can contribute to, and marketing portals.

“It's like the iOS of the enterprise cloud data warehouse space.”  

Patrick Moorhead

Founder, President, & Principal Analyst at Moor Insights & Strategy

Oracle Analytics Cloud is connected to Autonomous Database, empowering business users and executives with modern, AI-powered, self-service analytics capabilities for data preparation, visualization, enterprise reporting, augmented analysis, and natural language processing/generation. Alternatively, Autonomous Database is certified with all popular analytics tools including Tableau, Looker, and Microsoft Power BI, ensuring freedom of choice for customers.

Immediately improve your data-driven marketing results

Over 40% of marketers and analysts struggle with manual time and effort to wrangle data for reporting, and about 70% of them rate integrating all their data sources as their top data priority. With Oracle’s complete, self-service data analytics solution powered by Autonomous Database, marketing teams can rapidly integrate all data sources into a single source of truth, focusing on modeling and data analysis to design highly effective marketing plans. For IT teams, Oracle’s solution represents a simple, reliable, and repeatable approach for all data analytics requests from marketing teams—saving them significant time and effort while ensuring data governance and security.

Source: oracle.com

Continue reading

Oracle Cloud Infrastructure (OCI) Dashboards for Monitoring ORDS and Databases

Welcome to Dashboards

Brand new in Oracle Cloud Infrastructure (OCI) are Dashboards. This no-cost feature of OCI is easy to find; once you log into your OCI account, on the homepage, you will see a new tab in the top left entitled Dashboard.

Click the link and off you go; create dashboards that show key metrics for all your services in one place. So how can we use this for Cloud Native application and database monitoring? To start, remember that out of the box, the services in OCI can produce logs and/or metrics. We can leverage this built-in framework when creating these dashboards.

When you initially land on the Dashboard page, you can create a new dashboard by clicking the New Dashboard button on the upper right of the page.

Give your dashboard a name, place it in a compartment and add it to a new dashboard group and click the Create button. We now have a blank canvas to start adding widgets.

Monitor the Estate

We can use Dashboards to monitor our entire enterprise application deployment; each individual component getting a widget to show critical metrics. In this example, we are going to monitor an Oracle REST Data Services (ORDS) deployment. The major components in this architecture are the compute instance where ORDS is running, ORDS itself and the cloud database back-end.

Starting with the compute instance, we can create a new widget with the +Add widget button.

Here we are presented with a few options, but we want to select the Monitoring chart.

Once selected, you will see it on your dashboard. Just click Configure to get started with our first widget.

Using the Monitoring Widget Configuration slider, we start at the top with the Name. Our first metric we can capture is the CPU utilization of this compute instance so simply change the name to Compute CPU Utilization. Next, move down to the Configure metric query section. The first dropdown is Region and yes, you can have metrics on a single dashboard from not only multiple regions, but multiple compartments in those regions! Pretty useful if you say want to see the status of all your Autonomous Databases in multiple regions or how your load balancers are doing in Phoenix and Tokyo.

Ignoring Region for now, move to the next dropdown, Namespace. Using this dropdown in a region and compartment where you have a compute instance, we can see three entries relating to a compute instance.

We want to look at the oci_computeagent namespace. This namespace allows us to display metrics the compute agent is sending from the compute instance itself and where we will find metrics such as Memory and CPU utilization, disk IOPS and the load on the VM.

Choose CpuUtilization and set the next dropdown, Statistic, to mean. The upper region will refresh, and we will see a sample of what the chart will look like.

If you have multiple compute instances in this compartment, you can filter them out using the Metric Settings, choosing resourceDisplayName as the Dimension Name and then the name of the compute instance you want to monitor as the Dimension value.

And we are done; just click the Submit button on the bottom left of the slider to see it on your dashboard.

Everyone Loves a Log

Next up are the ORDS logs. To get the logs into OCI logging there is a little pre-work that needs to be done. You can see how to do that in the Oracle DB Tools Development Group's GitHub repository here.

Clicking the +Add widget button in the upper right of the page and using the Add new widget slider, choose Logging data table.

With the widget on the dashboard, click the Configure button as you did before. Using the Logging Data Table Widget Configuration slider, use the Name field to name the widget ORDS Logs. next, use the Select logs to search field to find the log holding the ORDS logs. Here my ORDS_Custom_Logs are in the ORDS_LogGroup in the Tools_PM compartment. Use the plus sign to the right of the log to add it to the logs to search section.

Once the log is selected, click the Continue button in the lower left.

Back on the main slider, with our log selected,

we can click the submit button in the lower left. We now have 2 widgets on our dashboard.

Time to add a Database one.

All the Flavors of Database

The last widget we are going to add is a database one. Now we have our choice here of adding any Cloud Database we want. In fact, Autonomous Databases have many metrics you can choose from right from the start. If you want to see metrics for a Cloud Database VM or a database on an Exadata Cloud Service there is a few prerequisites that need to be done before hand. In this example, we will be using a Cloud Database VM.

Just as before, click the +Add widget button in the upper right of the page and using the Add new widget slider, choose Monitoring chart.

With the widget on the dashboard, again click the Configure button.

Start with the Name field in the Monitoring Widget Configuration slider. We are interested in the database IOPS so name the widget Database IOPS. Now in the Configure metric query section, use the Namespace dropdown, and select oracle_oci_database.

And for the Statistic, choose Max. The widget is done, just click Submit in the lower left of the slider to finish.

Now that you have the basics of creating these dashboards to monitor our ORDS implementation, explore more metrics and see what you can come up with!

Warning, Squirrel on the Tracks!

One final note to mention. Observability & Management in OCI can create alarms on any of the metrics you can use in the Dashboard for an extra level of monitoring. Set an alarm for thresholds or a state change of a service and be notified immediately. You can read more about this here.

Source: oracle.com

Continue reading