How to view the version history of an Oracle database?

Often an Oracle database, even being say 19c, was initially created as 10g and upgraded with scripts over the years. From v$database and v$instance, we can find out the current version, the platform name and when the database was created but how to view the version history of that database? Often simple SQL statements can extract important data that we need. We should just know what table or view to query.

Two important views, which actually came with Oracle 10g, can shed some light on the question above: DBA_HIGH_WATER_MARK_STATISTICS and DBA_REGISTRY_HISTORY.

Let us see what information they provide:

SELECT * from DBA_REGISTRY_HISTORY where version is not null order by 4 desc;

I said above “shed some light” and not “answer the question” as the view came only with 10gR2. So we can see the history from 10.2.0.3 until now (19.13) but whether the database was created initially as 10g, 9i or even as v7 is an open question.

Note that since since 12.1.0.1 Oracle use DBA_REGISTRY_SQLPATCH instead of DBA_REGISTRY_HISTORY to track PSUs and BPs applied to the database. Check the post by Mike Dietrich called DBA_REGISTRY_HISTORY vs DBA_REGISTRY_SQLPATCH.

The view DBA_HIGH_WATER_MARK_STATISTICS is based on the table WRI$_DBU_HIGH_WATER_MARK:

create table WRI$_DBU_HIGH_WATER_MARK

(name                 varchar2(64)  not null,

 dbid                 number        not null,

 version              varchar2(17)  not null,

 highwater            number,

 last_value           number,

 error_count          number,

 constraint WRI$_DBU_HIGH_WATER_MARK_PK primary key

    (name, dbid, version)

 using index tablespace SYSAUX

) tablespace SYSAUX

/

Next, we run the following query which shows us along with the database size the database version as well:

SELECT * from DBA_HIGH_WATER_MARK_STATISTICS where name = 'DB_SIZE' order by 3 desc;

From DBA_HIGH_WATER_MARK_STATISTICS, we can view several other historical stats about the database: number of user tables, size of the largest segment, maximum number of partitions belonging to an user table, maximum number of partitions belonging to an user index, number of user indexes, maximum number of concurrent sessions seen in the database, maximum number of datafiles, maximum number of tablespaces, maximum number of CPUs and maximum query length.

If the high-water mark statistics are not populated, then execute manually DBMS_FEATURE_USAGE_INTERNAL.SAMPLE_ONE_HWM. The internal package looks like this:

PROCEDURE CLEANUP_DATABASE

Argument Name                  Type                    In/Out Default?

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

CLEANUP_LOCAL                  PL/SQL BOOLEAN          IN     DEFAULT 

PROCEDURE EXEC_DB_USAGE_SAMPLING

Argument Name                  Type                    In/Out Default?

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

CURR_DATE                      DATE                    IN             

PROCEDURE SAMPLE_ONE_FEATURE

Argument Name                  Type                    In/Out Default?

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

FEAT_NAME                      VARCHAR2                IN             

PROCEDURE SAMPLE_ONE_HWM

Argument Name                  Type                    In/Out Default?

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

HWM_NAME                       VARCHAR2                IN             

If you would like to update the HWM statistics manually as they are gathered once a week, here is an example:

SQL> SELECT name, highwater, last_value FROM dba_high_water_mark_statistics WHERE name = 'USER_TABLES';

NAME                            HIGHWATER LAST_VALUE

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

USER_TABLES                   533        533

SQL> CREATE TABLE JMD (c1 json);

Table created.

SQL> exec dbms_feature_usage_internal.sample_one_hwm('USER_TABLES');

PL/SQL procedure successfully completed.

SQL> SELECT name, highwater, last_value FROM dba_high_water_mark_statistics WHERE name = 'USER_TABLES';

NAME                            HIGHWATER LAST_VALUE

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

USER_TABLES                   534        534

As post scriptum, I can say without ever being able to prove it scientifically, a better performing database is one created as a fresh database and not upgraded with scripts. I would always advise to create a new database and transfer the data and all objects from the previous version than just upgrade the database (the data dictionary) with scripts. But with current DB sizes and limited downtime, this is getting more and more difficult to achieve.

Source: juliandontcheff.wordpress.com

Continue reading

Oracle enables revenue transformation with Fusion CX Analytics

Today, Oracle announced Oracle Fusion CX Analytics as part of the launch of the next generation of Oracle Fusion Sales. Fusion CX Analytics is a unified solution that combines data from the front, mid-, and back offices to provide actionable intelligence to help users learn from revenue to grow revenue.

In this blog, I will walk you through the journey of how and why we conceptualized this product and what specific business problems it addresses.

Key pain points

Fusion CX Analytics was conceptualized after extensive user research to understand the analytical needs of people in different roles in the Sales, Marketing, Service, and Revenue Operations departments, from leadership positions to individual contributors, across various organizations. Four key pain points emerged:

 

◉ Difficulty and delays in analyses of the entire customer lifecycle from lead to contract through renewal

◉ Overly complex processes required to blend data from multiple sources to perform analyses

◉ Limited ability to answer key business questions, especially for detecting anomalies or exceptions

◉ Inability to quickly predict future outcomes from all available data as an end-user capabilty and not as a request-based process to data science and data engineering teams.

Revenue funnel

To build a comprehensive, cross-departmental solution that could address these needs, we needed to understand and define the revenue landscape these personas sought to navigate.

Many B2B or B2B2C buying processes take a complex journey involving multiple systems before revenue is recognized, with siloed transaction or event reporting only within each system. The journey typically starts with the Marketing Automation Platform (MAP), using multiple channels to process and track how marketing teams engage and convert prospects to leads; these leads get handed to sales teams once qualified. The leads are processed and converted to opportunities by sales, using a Sales Force Automation (SFA) system to track progress within the sales cycle. Sales will generate a quote if successful, typically following the process within a Configure-Pricing-Quote (CPQ) system. Once the pricing is negotiated, an order gets generated in an Order Management system (usually part of the Supply Chain system). If the company’s business model involves a Subscription model, then the Subscription Billing system comes into the picture. Generated invoices lead to Accounts Receivable entries in the ERP system.

The users we talked to wanted to perform analyses across the revenue cycle, using data from all the disparate systems mentioned above and in one place. E.g.,

◉ Where are we leaking revenue in this entire cycle?

◉ Are we acquiring profitable customers with high lifetime value?

◉ What is the impact on our margins with our current discounting strategy?

◉ Did we give away more than optimal discounts?

◉ Which marketing campaigns or channels lead to more qualified leads?

◉ Are there certain touchpoints that have an asymmetrical impact on our revenue?

◉ Given the pipeline for the next four quarters, do we have enough capacity in our production line?

◉ Which pricing model (recurring vs.one-time usage vs. some combination) drives more revenue?

◉ What product configurations are selling more? Which region?

◉ What activities are leading to higher customer satisfaction?

People were looking for ways to understand what the data from Sales, Marketing, Service, CPQ, Subscription, and Finance was showing and in one system. In other words, they wanted to access the data from the front-office, mid-office, and back-office within one system to see the interdependent patterns and assess what could happen next, looking at the business as one block instead of several disjointed units. Plus, they wanted to derive a direct line from all activities to topline revenue, so they could optimize the company's resources and activities through analytics.

Technology struggle

A few companies have embarked on the journey to build systems to address the problems above centered around a data-warehouse strategy. From our research, we found they have faced several roadblocks/pain points:

◉ How do I connect to the source system? Which APIs to use? Which ETL tool is the best? Often, the simplest way was to extract files and ingest them into the warehouse, leading to brittle connections with a high probability of inaccurate data showing up in end-user reports.

◉ How do I create a data model to blend data from these disparate systems? What are the right join keys? What do particular tables or columns mean in the source system? Where do I find experts who understand the source data model?

◉ How do I enforce governance so that accurate interpretations of KPIs are used in my organization?

◉ How do I deal with different vendors for ETL, DW, and BI layers? How do I find experts in each of these systems and manage them?

◉ How do I re-create the same security model that I built in the transactional system?

Evidently, it’s been a struggle, marked by complexity and confusion. Hence, many organizations wouldn’t even consider starting projects on this path.

Alignment and collaboration using KPIs 

Within the context of the holistic revenue process outlined previously, the community of users wanted to define KPIs, get alignment with different business leaders from various departments, monitor these KPIs, and get notified proactively if there were exceptions. Most of them were getting lost with multiple isolated reports and dashboards, which required IT involvement to create. Often they would forget their own initial question by the time that particular dashboard or report was delivered. The technology struggles detailed above make it a massive hurdle to blend reporting and create those cross-departmental KPIs.

Introducing Oracle Fusion CX Analytics

Oracle Fusion CX Analytics was designed to address these needs laid out by our users:

◉ KPI-driven with the ability to drill down into any level of detail with proactive notifications

◉ The ability to analyze the entire contact-to-cash or lead-to-order process to optimize revenue

◉ Cross-departmental alignment and collaboration using shared KPIs

◉ Less complexity around data acquisition & management, and increased trust in shared data

◉ Simplified security by propagating security configurations from the source system

As a foundation, we created new cloud-native data pipelines that simplified the entire data acquisition process through wizards. No need to worry about APIs, connectors, ETL, etc. Just provide the URL to your Oracle Cloud Applications, configure a few parameters, and the data from the source systems gets transferred on a regular cadence. Not only that, but we validate the data and also handle all the complexity related to incremental updates and source schema changes.

Next, leveraging our deep knowledge of source system data models, we crafted a very analytically efficient data model to ensure all kinds of joins perform correctly. On top of that, we layered a cross-departmental semantic model that enables a unified interpretation of all KPIs and metrics across the entire revenue cycle. In other words, we do the heavy lifting to conform dimensions across Sales, Marketing, Finance, and operations systems to enable cross-departmental analysis for business users.

And to further reduce time-to-value, our product contains hundreds of prebuilt KPIs and curated drill-thru reports that enable anyone to increase productivity as soon as the system is provisioned — on day one. 

Finally, Oracle Fusion CX Analytics includes prebuilt mechanisms to easily extend the data pipelines and models with their customizations, whether in Oracle cloud apps, on-premises, or third-party systems.

Source: oracle.com

Continue reading

DBMS_CLOUD : Installation on 19c and 21c On-Prem Databases

This article describes how to install the DBMS_CLOUD package in on-prem 19c and 21c databases. This package is already installed in databases on the Oracle Cloud.

◉ Reference

This article is a run through of the installation instructions in this MOS note.

- How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)

You should always refer to this note, as the instructions may change over time.

◉ Install DBMS_CLOUD

Create a directory to hold the installation files and an SSL wallet.

mkdir -p /home/oracle/dbc/commonstore/wallets/ssl

Create a file called "/home/oracle/dbc/dbms_cloud_install.sql" with the following contents.

@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql

set verify off

-- you must not change the owner of the functionality to avoid future issues

define username='C##CLOUD$SERVICE'

create user &username no authentication account lock;

REM Grant Common User Privileges

grant INHERIT PRIVILEGES on user &username to sys;

grant INHERIT PRIVILEGES on user sys to &username;

grant RESOURCE, UNLIMITED TABLESPACE, SELECT_CATALOG_ROLE to &username;

grant CREATE ANY TABLE, DROP ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE,

CREATE ANY CREDENTIAL, CREATE PUBLIC SYNONYM, CREATE PROCEDURE, ALTER SESSION, CREATE JOB to &username;

grant CREATE SESSION, SET CONTAINER to &username;

grant SELECT on SYS.V_$MYSTAT to &username;

grant SELECT on SYS.SERVICE$ to &username;

grant SELECT on SYS.V_$ENCRYPTION_WALLET to &username;

grant read, write on directory DATA_PUMP_DIR to &username;

grant EXECUTE on SYS.DBMS_PRIV_CAPTURE to &username;

grant EXECUTE on SYS.DBMS_PDB_LIB to &username;

grant EXECUTE on SYS.DBMS_CRYPTO to &username;

grant EXECUTE on SYS.DBMS_SYS_ERROR to &username;

grant EXECUTE ON SYS.DBMS_ISCHED to &username;

grant EXECUTE ON SYS.DBMS_PDB_LIB to &username;

grant EXECUTE on SYS.DBMS_PDB to &username;

grant EXECUTE on SYS.DBMS_SERVICE to &username;

grant EXECUTE on SYS.DBMS_PDB to &username;

grant EXECUTE on SYS.CONFIGURE_DV to &username;

grant EXECUTE on SYS.DBMS_SYS_ERROR to &username;

grant EXECUTE on SYS.DBMS_CREDENTIAL to &username;

grant EXECUTE on SYS.DBMS_RANDOM to &username;

grant EXECUTE on SYS.DBMS_SYS_SQL to &username;

grant EXECUTE on SYS.DBMS_LOCK to &username;

grant EXECUTE on SYS.DBMS_AQADM to &username;

grant EXECUTE on SYS.DBMS_AQ to &username;

grant EXECUTE on SYS.DBMS_SYSTEM to &username;

grant EXECUTE on SYS.SCHED$_LOG_ON_ERRORS_CLASS to &username;

grant SELECT on SYS.DBA_DATA_FILES to &username;

grant SELECT on SYS.DBA_EXTENTS to &username;

grant SELECT on SYS.DBA_CREDENTIALS to &username;

grant SELECT on SYS.AUDIT_UNIFIED_ENABLED_POLICIES to &username;

grant SELECT on SYS.DBA_ROLES to &username;

grant SELECT on SYS.V_$ENCRYPTION_KEYS to &username;

grant SELECT on SYS.DBA_DIRECTORIES to &username;

grant SELECT on SYS.DBA_USERS to &username;

grant SELECT on SYS.DBA_OBJECTS to &username;

grant SELECT on SYS.V_$PDBS to &username;

grant SELECT on SYS.V_$SESSION to &username;

grant SELECT on SYS.GV_$SESSION to &username;

grant SELECT on SYS.DBA_REGISTRY to &username;

grant SELECT on SYS.DBA_DV_STATUS to &username;

alter session set current_schema=&username;

REM Create the Catalog objects

@$ORACLE_HOME/rdbms/admin/dbms_cloud_task_catalog.sql

@$ORACLE_HOME/rdbms/admin/dbms_cloud_task_views.sql

@$ORACLE_HOME/rdbms/admin/dbms_cloud_catalog.sql

@$ORACLE_HOME/rdbms/admin/dbms_cloud_types.sql

REM Create the Package Spec

@$ORACLE_HOME/rdbms/admin/prvt_cloud_core.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_task.plb

@$ORACLE_HOME/rdbms/admin/dbms_cloud_capability.sql

@$ORACLE_HOME/rdbms/admin/prvt_cloud_request.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_internal.plb

@$ORACLE_HOME/rdbms/admin/dbms_cloud.sql

@$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int.plb

REM Create the Package Body

@$ORACLE_HOME/rdbms/admin/prvt_cloud_core_body.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_task_body.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_capability_body.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_request_body.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_internal_body.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_body.plb

@$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int_body.plb

-- Create the metadata

@$ORACLE_HOME/rdbms/admin/dbms_cloud_metadata.sql

alter session set current_schema=sys;

@$ORACLE_HOME/rdbms/admin/sqlsessend.sql

Run the script in all containers including this seed. This will make new containers already include the DBMS_CLOUD package installation.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \

  -u sys/SysPassword1 \

  --force_pdb_mode 'READ WRITE' \

  -b dbms_cloud_install \

  -d /home/oracle/dbc \

  -l /home/oracle/dbc \

  dbms_cloud_install.sql

Check the log files once the command is complete, to make sure it has run in all containers.

◉ Create a Wallet

Create a wallet to allow HTTPS access to the cloud URIs.

Download the dbc_certs.tar file from the link provided in the MOS note. These instructions assume it is placed on the "/tmp" directory.

mkdir -p /home/oracle/dbc/commonstore/wallets/ssl

cd /home/oracle/dbc/commonstore/wallets/ssl

tar -xvf /tmp/dbc_certs.tar

Create a wallet and load the certificates. We are using the wallet password "MyPassword1", but you should change it to something more secure for your installation.

orapki wallet create -wallet . -pwd MyPassword1 -auto_login

orapki wallet add -wallet . -trusted_cert -cert ./VeriSign.cer -pwd MyPassword1

orapki wallet add -wallet . -trusted_cert -cert ./BaltimoreCyberTrust.cer -pwd MyPassword1

orapki wallet add -wallet . -trusted_cert -cert ./DigiCert.cer -pwd MyPassword1

Edit the "sqlnet.ora" file, adding in the following entry to identify the wallet. For read-only Oracle homes, this will be in the "/u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora" file. For regular Oracle homes it will be in the "$ORACLE_HOME/network/admin/sqlnet.ora" location.

WALLET_LOCATION=

  (SOURCE=(METHOD=FILE)(METHOD_DATA=

  (DIRECTORY=/home/oracle/dbc/commonstore/wallets/ssl)))

◉ Create Access Control Entries (ACEs)

We need to create an Access Control Entry (ACE) so the C##CLOUD$SERVICE can access the cloud services.

Create a file called "/home/oracle/dbc/dbc_aces.sql" with the following contents. Edit the sslwalletdir setting if you have altered the location.

@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql

-- you must not change the owner of the functionality to avoid future issues

define clouduser=C##CLOUD$SERVICE

-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER

-- - SSL Wallet directory

define sslwalletdir=/home/oracle/dbc/commonstore/wallets/ssl

--

-- UNCOMMENT AND SET THE PROXY SETTINGS VARIABLES IF YOUR ENVIRONMENT NEEDS PROXYS

--

-- define proxy_uri=<your proxy URI address>

-- define proxy_host=<your proxy DNS name>

-- define proxy_low_port=<your_proxy_low_port>

-- define proxy_high_port=<your_proxy_high_port>

-- Create New ACL / ACE s

begin

-- Allow all hosts for HTTP/HTTP_PROXY

dbms_network_acl_admin.append_host_ace(

host =>'*',

lower_port => 443,

upper_port => 443,

ace => xs$ace_type(

privilege_list => xs$name_list('http', 'http_proxy'),

principal_name => upper('&clouduser'),

principal_type => xs_acl.ptype_db));

--

-- UNCOMMENT THE PROXY SETTINGS SECTION IF YOUR ENVIRONMENT NEEDS PROXYS

--

-- Allow Proxy for HTTP/HTTP_PROXY

-- dbms_network_acl_admin.append_host_ace(

-- host =>'&proxy_host',

-- lower_port => &proxy_low_port,

-- upper_port => &proxy_high_port,

-- ace => xs$ace_type(

-- privilege_list => xs$name_list('http', 'http_proxy'),

-- principal_name => upper('&clouduser'),

-- principal_type => xs_acl.ptype_db));

--

-- END PROXY SECTION

--

-- Allow wallet access

dbms_network_acl_admin.append_wallet_ace(

wallet_path => 'file:&sslwalletdir',

ace => xs$ace_type(privilege_list =>

xs$name_list('use_client_certificates', 'use_passwords'),

principal_name => upper('&clouduser'),

principal_type => xs_acl.ptype_db));

end;

/

-- Setting SSL_WALLET database property

begin

-- comment out the IF block when installed in non-CDB environments

if sys_context('userenv', 'con_name') = 'CDB$ROOT' then

execute immediate 'alter database property set ssl_wallet=''&sslwalletdir''';

--

-- UNCOMMENT THE FOLLOWING COMMAND IF YOU ARE USING A PROXY

--

-- execute immediate 'alter database property set http_proxy=''&proxy_uri''';

end if;

end;

/

@$ORACLE_HOME/rdbms/admin/sqlsessend.sql

Run script in the root container.

conn / as sysdba

@@/home/oracle/dbc/dbc_aces.sql

◉ Verify the Installation

Create a file called "/home/oracle/dbc/verify.sql" with the following contents. Edit the wallet path and password as required.

-- you must not change the owner of the functionality to avoid future issues

define clouduser=C##CLOUD$SERVICE

-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER

-- - SSL Wallet directory and password

define sslwalletdir=/home/oracle/dbc/commonstore/wallets/ssl

define sslwalletpwd=MyPassword1

-- create and run this procedure as owner of the ACLs, which is the future owner

-- of DBMS_CLOUD

CREATE OR REPLACE PROCEDURE &clouduser..GET_PAGE(url IN VARCHAR2) AS

request_context UTL_HTTP.REQUEST_CONTEXT_KEY;

req UTL_HTTP.REQ;

resp UTL_HTTP.RESP;

data VARCHAR2(32767) default null;

err_num NUMBER default 0;

err_msg VARCHAR2(4000) default null;

BEGIN

-- Create a request context with its wallet and cookie table

request_context := UTL_HTTP.CREATE_REQUEST_CONTEXT(

wallet_path => 'file:&sslwalletdir',

wallet_password => '&sslwalletpwd');

-- Make a HTTP request using the private wallet and cookie

-- table in the request context

req := UTL_HTTP.BEGIN_REQUEST(

url => url,

request_context => request_context);

resp := UTL_HTTP.GET_RESPONSE(req);

DBMS_OUTPUT.PUT_LINE('valid response');

EXCEPTION

WHEN OTHERS THEN

err_num := SQLCODE;

err_msg := SUBSTR(SQLERRM, 1, 3800);

DBMS_OUTPUT.PUT_LINE('possibly raised PLSQL/SQL error: ' ||err_num||' - '||err_msg);

UTL_HTTP.END_RESPONSE(resp);

data := UTL_HTTP.GET_DETAILED_SQLERRM ;

IF data IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE('possibly raised HTML error: ' ||data);

END IF;

END;

/

set serveroutput on

BEGIN

&clouduser..GET_PAGE('https://objectstorage.eu-frankfurt-1.oraclecloud.com');

END;

/

set serveroutput off

drop procedure &clouduser..GET_PAGE;

Run the script. The script should produce the phrase "valid response".

conn / as sysdba

@/home/oracle/dbc/verify.sql

◉ List the Contents of a Bucket

This section of the article assumes you have an object storage bucket on Oracle Cloud and you've defined an Auth Token to access it. You can read how to create a bucket and an Auth Token in this article.

- Oracle Cloud Infrastructure (OCI) : Create an Object Storage Bucket

Create a test user.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

--drop user testuser1 cascade;

create user testuser1 identified by testuser1 quota unlimited on users;

grant connect, resource to testuser1;

Make sure the test user can create credentials and has access to the DBMS_CLOUD package.

grant create credential to testuser1;

grant execute on dbms_cloud to testuser1;

Connect to the test user and create a credential.

conn testuser1/testuser1@//localhost:1521/pdb1

begin

  dbms_credential.drop_credential(

    credential_name => 'obj_store_cred');

end;

/

begin

  dbms_credential.create_credential(

    credential_name => 'obj_store_cred',

    username        => 'me@example.com',

    password        => 'my-auth-token');

end;

/

We can now use the LIST_OBJECTS table function to get a list of objects in the bucket.

select object_name

from   dbms_cloud.list_objects(

         'obj_store_cred',

         'https://objectstorage.uk-london-1.oraclecloud.com/n/{my-namespace}/b/ob-bucket/o/');

OBJECT_NAME

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

Image 930.png

SQL>

Source: oracle-base.com

Continue reading

Power your data applications with a semantic model using Oracle Analytics Cloud

Users have come to expect compelling experiences in their modern data applications. Creating these experiences can be challenging and require costly bespoke solutions. Sometimes you want to direct users through the data; in other cases, you want users to explore the data themselves. With Oracle Analytics Cloud (OAC) and our new Semantic Modeling Markup Language (SMML), you can now create a powerful semantic model which acts as a translation layer between your application and your underlying data structures. This semantic model exposes a metrics-oriented data layer that can be used directly in your application via APIs, with embedded visualizations, or in other analytics tools.

Putting your data into the right context

Oracle Analytics offers a wide range of capabilities that provide both governed and self-service analytics. The backbone of these capabilities is an intelligent query engine that provides centralized data access, computes calculations, and enables data governance by creating a pipeline through which anyone can consume information specific to their roles across an enterprise. The query engine is central to data visualizations, dashboards, ad-hoc queries, mobile access, enterprise reporting, data flows, and more. The brains of this query engine is a conceptual data model defined in terms of business semantics — a Semantic Model.

Defining your data

In a decision support ecosystem, a semantic model enables you to structure data in a business-friendly way. It enables you to add business semantics to provide meaning to the data and the governance rules that secure data access. In doing so, it masks the complexity of the underlying data models and data access challenges to analytics consumers, rendering it more sensible to the business user. 

Data models are typically specific to their implementation and to their data source type. Most transactional applications data are held in 3NF schemas, data warehouses in dimensional format with facts and dimensions, a data vault with hubs, satellites and links, multi-dimensional data or streaming data.. 

A well-designed analytics platform should have the capability to consume information in any format or structure and make it available to consumers in a business-friendly view. Most enterprise applications have the semantics for the data defined in the application layer. When bringing in data from these data sources, the semantics needs to be defined in the Analytics system in addition to those for data interaction, calculations, and data governance. The semantic model in Oracle Analytics serves that purpose.

Oracle Analytics has a rich semantic model that enables a developer to build robust, scalable data frameworks needed to support advanced analytics applications across the enterprise. This has been a feature of OAC for many years, powering the robust, scalable applications used by thousands of Oracle Fusion applications

Two great new features to preview

We are excited to share two new developments that will be available as a preview feature in the upcoming release of Oracle Analytics Cloud:

◉ The first is a newly designed, fully browser-based semantic modeler that enables you to create semantic layers on top of federated data sources. Fully compatible with existing enterprise data models that use relational sources in repository document (RPD) format, the new semantic modeler introduces a modern experience that emphasizes  team development and version control. 

◉ The second one is the Semantic Model Markup Language (SMML), which enables the definition of semantic models using Javascript Object Notation (JSON). Developers have the option to create semantic models using SMML or using the Semantic Modeler with its more familiar diagramming capabilities. The Semantic Modeler in turn generates SMML to define semantic models. With SMML, developers can use any editor to make changes to the semantic model source code or they can use the integrated JSON editor in Semantic Modeler.

The Semantic Modeler has the ability to integrate with any Git-compatible repository, such as GitHub, GitLab, or Git on Oracle Visual Builder. With full support for branching, merging, pull, push, and commit from within Semantic Modeler, multiuser development becomes much less complicated. With Git integration, you have full visibility to a complete change history and the ability to publish to multiple targets.

Compelling data experiences are a key differentiator between good applications and amazing applications. Using a rich semantic model can help your users make the most of their data and provide a seamless, powerful user experience.

Source: oracle.com

Continue reading

Create a Python function working with a PostgreSQL cluster

The Oracle Cloud Infrastructure (OCI) Functions service is a fully managed, multi-tenant, highly scalable, on-demand, functions-as-a-service platform. It’s built on enterprise-grade OCI and powered by the Fn Project open source engine. When the functions are deployed, you can call them directly or trigger them in response to events, and you’re billed only for the resources consumed during the run.

The serverless and elastic architecture of OCI Functions means you have no infrastructure administration or software administration to perform. Functions ensures that your app is highly available, scalable, secure, and monitored, and you can write code in Java, Python, Node, Go, Ruby, and C#. Advanced users can build functions directly from their own Docker file, which is especially helpful when you need to build functions with customized library requirements or want to build your functions based on a special Docker images, instead from the standard runtime.

PostgreSQL is one of the most widely used open source databases in the industry. OCI Compute provides better performance for PostgreSQL clusters than many other clouds. Python is a popular language for writing functions, and many independent software vendors (ISVs) build their cloud native solutions on Python and Postgres. Sometimes they need to migrate their functions from other clouds to OCI with corresponding support.

Python requires Psycopg2 library to connect to a Postgre database. Psycopg2 depends on libpq, which the default OCI Functions environment doesn’t include. So, we need to build the function from a Docker image with PostgreSQL library installed to allow our functions to connect to the PostgreSQL cluster.

This blog walks through the steps to build a Python function that can connect with a Postgres cluster. Because we’re building the foundation, the function only shows you how to connect to a Postgres database. From there, you can build your business logic. The environment can run on OCI’s Free Tier, so try it out!

Prerequisites

The tutorial requires some knowledge of Python and Docker. Refer to corresponding documents to start and have the following prerequisites:

◉ Because we’re building our function from a Docker image, we need Docker Desktop running locally.

◉ Install the Fn project on your development environment. The steps of this tutorial are built on Mac OS and Linux. If your development environment is Windows, the installation steps are similar. After installation, you can use command "fn start" to start the Fn server to verify that Fn is properly installed.

◉ Install and configure the OCI CLI.

◉ Function development kit (FDK) is installed. FDK lets you write functions with Python.

◉ Configure a compartment, virtual cloud network (VCN), and subnet to run the function.

◉ Create an OCI registry and login. The registry stores the OCI Fn image.

◉ A Postgres cluster running in the tenancy. I installed Postgres on an OCI instance.

◉ Necessary policies, security groups, and route tables configured to allow the function to run in the tenancy

Create a function

Usually, we manage a function using the Fn CLI. An OCI function is a containerized function that FDK can run. However, the default function image doesn’t allow us to install any OS level libraries, such as the necessary libpg program that Python requires to access a Postgres cluster. So, we use a Linux image as a base image to create our own Docker image that packages FDK, Postgres, and Psycopg2 to support the function to access the backend Postgres database.

1. Create a Python virtual environment to develop the function. In a working folder to store the function project, create a folder.

mkdir pgdemo

Then enable the newly created Python environment with the following command:

python3 -m env pgdemo

source pgdemo/bin/activate

2. Create a context for Functions development environment. The context is a placeholder that describes the repository and compartment for the function. A proper configuration contains these directives. Your specific configuration is different, with your tenancy and compartment to be deployed.

api-url: https://functions.us-phoenix-1.oci.oraclecloud.com

oracle.compartment-id: ocid1.compartment.oc1..aaaaaaaauifmbfrwd34pswaqrjrwqh7ivhckdglrhvyrdt4mlkh2jcgxqfsq

oracle.profile: DEFAULT

provider: oracle

registry: phx.ocir.io/orasenat/pgdemo

3. Use your favorite editor to create the Docker file from which we build the Docker image.

FROM oraclelinux:7-slim

RUN  yum install -y oracle-softwarecollection-release-el7 && \

     yum -y install scl-utils rh-python38 && \

     scl enable rh-python38 bash

RUN yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm && \

    yum install -y postgresql13 && \

     rm -rf /var/cache/yum

ENV PATH="/opt/rh/rh-python38/root/usr/bin:/opt/rh/rh-python38/root/usr/local/bin:/usr/pgsql-13/bin:${PATH}"

WORKDIR /function

ADD . /function

RUN pip3 install -r requirements.txt

CMD fdk pgdemo.py handler

4. Create the Python dependency file requirements.txt for the function. It includes the required Python libraries.

<codefdk>=0.1.41 psycopg2-binary=""></codefdk>=0.1.41>

5. Create the function metadata file, func.yml. The Fn CLI command depends on the func.yml file to deploy function to OCI. It also provides the entry point to allow FDK to bootstrap the function.

schema_version: 20180708

name: pgdemo

version: 0.0.1

runtime: docker

entrypoint: python3 /function/pgdemo.py handler

memory: 256

6. Create the function. This function is for demo purposes only. It connects to the test database and retrieves the ID column in the test1 table.

import logging

import datetime

import psycopg2 as pg

import json

import io

from fdk import response

PG_HOST="10.0.1.240" #Change to your server address or DNS name

PG_PORT=5432

PG_USER='pguser'

PG_PASSWORD='pg1234'

PG_DB = 'test'

def handler(ctx,data: io.BytesIO=None):

    logging.getLogger().info("Start hander")

    return response.Response(ctx, response_data=json.dumps(

            {"message": "Postgres Function" ,

            "pg":pg_result()}),

        headers={"Content-Type": "application/json"}

    )

def pg_result():

    result=[]

    conn = pg.connect(

        host=PG_HOST,

        port=PG_PORT,

        user=PG_USER,

        password= PG_PASSWORD,

        database= PG_DB)

    pg_cursor = conn.cursor()

    pg_cursor.execute("SELECT ID FROM TEST1")

    for row in pg_cursor:

        logging.getLogger().info(f'row value: {row}')

        result.append(row)

    conn.close()

    return result

Deploy and invoke the function

Now that we have all the codes ready, we can deploy the function to OCI and push the image to the registry. Here, we’re using OCI Registry (OCIR) to store the image.

1. Log in to the registry. The OCIR is a regional resource. Use the region where you want to deploy. For example, you use the following command to log in into the private OCIR:

docker login phx.ocir.io

2. Create an application. An OCI function needs to belong to an application to exist. We can quickly create the application in the Oracle Cloud Console.

You also can use the Fn command to create the application. To verify that the application was created properly, use the following command:

fn list app

The terminal displays the current app similar to the following example:

“pgdemo ocid1.fnapp.oc1.phx.aaaaaaaa7wa4u657h2tlnqiiqxc3svcfmmbc63o4izjwdnxgz6awe4db7zma”

3. Deploy this function and switch to the folder of the function. Because we already created a function meta file, we can use Fn CLI to deploy the function. The “fn deploy” command builds the function, tags the image, and pushes the image to OCIR.

fn deploy --app pgdemo

You can see similar outputs on the terminal.

Bumped to version 0.0.2

Building image phx.ocir.io/orasenatdpltintegration03/pgdemo/pgdemo:0.0.2 .......

Parts:  [phx.ocir.io orasenatdpltintegration03 pgdemo pgdemo:0.0.2]

Pushing phx.ocir.io/orasenatdpltintegration03/pgdemo/pgdemo:0.0.2 to docker registry...The push refers to repository [phx.ocir.io/orasenatdpltintegration03/pgdemo/pgdemo]

758ef514ba41: Pushed 

e1df353b4576: Pushed 

ed69c4e08095: Layer already exists 

f4f452bf3b05: Layer already exists 

dbd8fc086680: Layer already exists 

43038c62fc57: Layer already exists 

0.0.6: digest: sha256:4c3317c49c47d325a260c8b9dcfd4366d630b91ad3a3a3d240b8a9d4750e9506 size: 1580

Updating function pgdemo using image phx.ocir.io/orasenatdpltintegration03/pgdemo/pgdemo:0.0.2…

4. Invoke the function. When the function is deployed successfully, you can invoke the function from the terminal with the following command:

fn invoke pgdemo pgdemo

We return the query from the Postgres database for the result. The table in the result for reference has two rows with ID 1 and 2.

{"message": "Postgres Function", "pg": [[1], [2]]}

Congratulations! We created our own function that can call Postgres database successfully. Based on the guidance, you can implement your business logic using Postgres.

Source: oracle.com

Continue reading

Microsoft Azure and Oracle Database on OCI: A Big Win for Users

For decades, enterprises have built their businesses using applications running on Windows with databases in Oracle Database. They have been seeking to migrate their operations to Microsoft Azure, but until recently, their database options were limited. They could either deploy Oracle Database on Azure as a self-managed instance in a virtual machine, or switch to another RDBMS. For most, switching to another RDBMS is a non-starter. Over the last few years, Oracle Database has added exciting new features for scalability, performance, and impressive levels of automation, including Oracle Autonomous Database and the Oracle Exadata Database Service. These Oracle Database cloud configurations represent superior and more cost-effective options than the self-managed VM option on Azure. The problem is that these only run on Oracle Cloud Infrastructure (OCI).

This issue is particularly acute for users of Oracle Exadata Cloud@Customer, who have enjoyed those advanced, OCI-based Oracle Database services in their datacenter, directly attached to their Microsoft Windows applications. Moving their applications to Azure would mean giving up those advanced services. Or would it?

In 2019, Oracle and Microsoft introduced the Oracle Interconnect for Microsoft Azure, which enabled a high-speed connection of Oracle Database on OCI to Microsoft Azure applications, so those applications could interact with the database at the speed of a local system, and without egress or ingress charges. However, manual effort and technical expertise are still required in both environments to set up and manage the interconnect between applications and the database. Also, management of applications would need to be done on Azure while management of Oracle databases would need to be done on OCI using a very different user interface. So, it worked, but the user experience was awkward, to say the least.

A Comprehensive and Seamless Service

In July 2022, Oracle and Microsoft announced a new interconnect service, called Oracle Database Service for Microsoft Azure, which is much more comprehensive than the prior offering. With this new service, Oracle Database appears on Azure as a native database service and can be managed through an Azure native user interface. The connection is fairly automatic, requiring no special technical expertise. To the Azure user, the look and feel of the service makes it seem as if the database is local to the application. In many ways, this service delivers what users really want: the best possible platform for each element of their application system provided in a seamless manner.

The service operates across a private interconnect that, according to Oracle, delivers sub-two millisecond latency and a full OCI-based database experience in terms of speed, availability, and scalability (both in IOPS power and database size) with automated identity management, networking, and monitoring integration with Azure. Users can deploy the interconnect service for a Base Database Service, Exadata Database Service, or full Autonomous Database Service, all seamlessly integrated with the Azure user experience. These capabilities make it easier for developers to build applications that take advantage of the best offerings of the two clouds and for operations teams to manage them. A similar capability for MySQL HeatWave is planned for the near future.

There is no charge for the Oracle Database Service for Microsoft Azure or for data egress or ingress when moving data between OCI and Azure. Users pay only for the other Azure or Oracle services they consume, such as Azure Synapse or Oracle Autonomous Database. Azure and OCI accounts are linked and enable full observability from either vantage point. Also, Microsoft and Oracle have established a single support model for this service, with one service ticket applying to both clouds for smooth joint issue resolution.

The physical connection between OCI and Azure is already established for 11 OCI and Azure cloud regions around the world, and more are coming online. Hundreds of customers are already using the interconnect, with several providing positive comments about the new service during its launch.

A New Approach to Cloud Services

This exciting development points to a new way of thinking about and using cloud services. Since the beginning, cloud service platforms have been built like castles, with thick walls and alligator-laden moats. You want to move data to another cloud? Sure, but it will cost you dearly. Users have had to make Solomonic choices regarding the database, analytics, integration, and application services they used based on which cloud platform offers most of what they want while compromising on the rest.

At IDC, we have been expecting and hoping for another mode of operation to emerge. The vision of the cloud was that of having access to virtualized, fungible resources that can be accessed and deployed without regard for their underlying details. This vision has been true, but only on a cloud platform-by-platform basis.

The cooperative agreement between Microsoft and Oracle indicates that there is another way. By connecting Oracle database services with applications on Microsoft Azure in a seamless way that is transparently effortless to the user, these firms are taking a giant step toward offering their users the ability to choose the best services for the job at hand, link them together, and run them as a singular system, regardless of the cloud platform upon which they happen to physically reside.

We hope to see agreements like this extended to other services and include other cloud platforms. We hope to see a time when users can link applications, databases, integration services, streaming services, analytic systems, and any other cloud-based services to each other, regardless of the cloud platforms involved. And we look forward to the day when seamless interoperation, easy to use connectivity, management, and unified monitoring capabilities that dispense with complicated, tech expert-oriented setup operations and the dreaded egress fees become the norm.

A Big Win for Users

The business of the cloud has seemed to be focused on "let the best cloud platform win." However, at this time no single cloud platform offers everything users want from basic services to databases, development tools and applications. Oracle and Microsoft have shown that users, indeed, can have a choice to use the best services across their two clouds. With the ability to connect and integrate services, such as Azure applications and Oracle Database on OCI, this new approach makes the users the real winners.

Source: oracle.com

Continue reading