OIC | Embedded file server in Oracle integration | using file server in oracle integration generation 2

File Server provides an embedded SFTP server within Oracle Integration, enabling organizations to focus on building integrations without needing to host and maintain a separate SFTP server.

Three Reasons to Use File Server:

File Server is an SFTP server that is bundled with Oracle Integration. With File Server, you get 500 GB of storage for free, allowing you to store, share, and receive files, including files for SaaS integrations and third-party transfers.

1. Eliminate the cost and operational expenses associated with hosting and maintaining an SFTP server: Managing a do-it-yourself file-based storage system is complex and daunting. From setting up hardware to maintaining the server, implementing a homegrown solution costs both time and money. With File Server, you get free file storage. Moreover, Oracle manages the operational tasks for you, so you can focus on solving more pressing business problems.
2. Create file-based integrations easily: If your organization needs an integration solution that supports file-based integrations, FileServer is for you. And because File Server is embedded in Oracle Integration, you can start building file-based integrations quickly.
3. Manage permissions in one place, using in an intuitive interface: Configuring and managing multiple users, groups, and folders in an SFTP environment can be challenging, but this work is simplified with File Server. File Server has a powerful administrative console that contains the core set of features that you expect from an SFTPserver, all in an intuitive user interface that streamlines your workflows.

To begin using File Server in Oracle Integration, it must first be enabled for the Oracle Integration instance in the Oracle Cloud Infrastructure Console. Enabling File Server is a one time action.

Common Use Cases

File Server can be used in a variety of scenarios. Here are some common use cases

1. Communication with trading partners Communication with trading partners such as customers and suppliers. In these cases, File Server enables trading partners to send information such as purchase orders, invoices, and shipping information using SFTP.
2. Integration with SaaS applications SaaS (or on-premises) applications often export bulk data to files on an SFTP server such as File Server. For example, Oracle E-Business Suite generates a zip file with external transactions, which need to be bulk uploaded to ERP. Oracle Integration can pick up the files, process them, and send them to a target system.
3. SFTP server lift-and-shift If your organization is running an on-premise SFTP server with Oracle Integration using the SFTP adapter, you may want to move this SFTP server to the cloud. Move the SFTP files into the Oracle Integration File Server, and redirect the SFTP adapter.

Reference:

https://docs.oracle.com/en/cloud/paas/integration-cloud/file-server/using-file-server-oracle-integration-generation-2.pdf

OIC | Oracle Cloud Integrarions | Restrictions using Oracle Integearion Generation 2

Restrictions

Note the following current restrictions when creating and using Oracle Integration.

• You can create Oracle Integration Generation 2 instances in any Oracle data region listed in Availability.

• You can create Oracle Integration for Oracle SaaS Generation 2 instances in any Oracle data region if you created a new Oracle Cloud account on or after February 11, 2020.

• Email notifications from Processes work correctly. However, it's not possible to set a custom “from” sender (that is, the from address is the default).

OIC | Oracle Integrations Editions | Standard vs Enterprise features

 Oracle Integration Editions

Oracle Integration is available in two editions: 

• Standard 
• Enterprise.

Either edition gives you the power to integrate your Software as a Service (SaaS) applications and your on‑premises applications. 

Enterprise edition enables you to also design, automate, and manage your business processes in the cloud.

Regardless of which edition you choose, Oracle handles cloud and database management, backup, restore, and other administrative tasks for you.

Here’s a side-by-side comparison of what’s licensed in each edition.

OIC Service limits | Generation 2 Resources limitation

This is very important to know the service limits. A service limit is the quota or a allowance set on a resource.

Follow are few of the service limits. 

1. Oracle Cloud Infrastructure Console Service Limits

Resource: Integration instance count

Service Limit: 200 instances per region

2. Adapters

A. Timeouts for all outbound adapter invocations. The following values are set and cannot be changed:

• READ timeout is set to 5 minutes.

• Connection timeout is set to 5 minutes.

B. Oracle Autonomous Data Warehouse Adapter, Oracle Autonomous Transaction Processing Adapter, Oracle Database Cloud Service Adapter, MySQL Adapter, Microsoft SQL Server Adapter, Oracle Database Adapter, and IBM DB2 Adapter:

Starting with the August 2021 release, all new integrations that include stored procedure or PureSQL database operations must finish within 240 seconds. Otherwise, the query times out.

C. Timeouts for all connectivity agent-based outbound adapter invocations:

Connection timeout is set to 4 minutes.

D. File Adapter - file size:

10 MB.

Note:
The size of CSV files increases when translated into a message. Therefore, the file size must be less than 10 MB, so that the after-translation message size does not exceed 10 MB.

E. FTP Adapter - file size

For invoke configurations

Read File operation:

• 1 GB when used without a schema (with a connectivity agent).

• 10 MB when using a schema for transformation.

Write File operation:

• 1 GB when used without a schema (with a connectivity agent).

• 10 MB when using a schema for transformation.

Download File operation: 1 GB.

F. AS2 Adapter - file size limit

10 MB

G. REST Adapter

For trigger configurations

• XML document size for schema generation: 3 MB. See REST Adapter Capabilities.

• Incoming message size (without attachment): 10 MB. Messages with attachments, for example, multipart/mixed and multipart/form-data, are not subject to this constraint.

• Incoming JSON attachments size: 1 GB.

• Incoming structured message payload size (any content-type header containing JSON, XML, HTML, YAML, or YML): 10 MB.

• Incoming content as raw bytes (application/octet-stream as content type): 1 GB.

• Specifying the response payload format: JSON sample files of up to 100 KB in size are supported.

For invoke configurations

• XML document size for data definition generation: 3 MB. See REST Adapter Capabilities.

• Attachment size in outbound requests: 1 GB. These attachments can be multipart/mixed, multipart/form-data, or application/octet-stream.

• Outgoing structured message payload size (any content-type header containing JSON, XML, HTML, YAML, or YML): 10 MB.

• Outgoing unstructured message payload size (multipart/form-data and binary/octet-stream): 1 GB.

• Specifying the request payload format: JSON sample files of up to 100 KB in size are supported.

H. REST-Based Adapters (Adapters that expose REST endpoints on the inbound or adapters invoking external REST endpoints. For example, Oracle Commerce Cloud Adapter, Oracle Field Service Adapter, etc.)

For trigger configurations (wherever applicable)

• XML document size for schema generation: 3 MB. See REST Adapter Capabilities.

• Incoming message size (without attachment): 10 MB. Messages with attachments, for example, multipart/mixed and multipart/form-data, are not subject to this constraint.

• Incoming JSON attachments size: 1 GB.

• Incoming structured message payload size (any content-type header containing JSON, XML, HTML, YAML, or YML): 10 MB.

• Incoming content as raw bytes (application/octet-stream as content type): 1 GB.

• Specifying the response payload format: JSON sample files of up to 100 KB in size are supported.

For invoke configurations (wherever applicable)

• XML document size for data definition generation: 3 MB. See REST Adapter Capabilities.

• Attachment size in outbound requests: 1 GB. These attachments can be multipart/mixed, multipart/form-data, or application/octet-stream.

• Outgoing structured message payload size (any content-type header containing JSON, XML, HTML, YAML, or YML): 10 MB.

• Outgoing unstructured message payload size (multipart/form-data and binary/octet-stream): 1 GB.

• Specifying the request payload format: JSON sample files of up to 100 KB in size are supported.

I. Salesforce Adapter - batch file size

8 MB (10,000 records).

J.SOAP Adapter

For trigger configurations

• Structured payload (XML) size in Request and Response: 10 MB.

For invoke configurations

• Structured payload (XML) size in Request and Response: 10 MB.

• MTOM attachment (binary and non-binary content) size in Request and Response: 1 GB.

K. SOAP-Based Adapters (Adapters that expose SOAP endpoints on the inbound or adapters invoking external SOAP endpoints. For example, Oracle Logistics Adapter)

For trigger configurations (wherever applicable)

• Structured payload (XML) size in Request and Response: 10 MB.

For invoke configurations (wherever applicable)

• Structured payload (XML) size in Request and Response: 10 MB.

• MTOM attachment (binary and non-binary content) size in Request and Response: 1 gb

L. Database Adapters (Oracle Database Adapter, IBM DB2 Adapter, Microsoft SQL Server Adapter, MySQL Adapter, Oracle Autonomous Data Warehouse Adapter, Oracle Autonomous Transaction Processing Adapter, and Oracle Database Cloud Service Adapter)

For trigger configurations

• Polling Operation: 10 MB with schema transformation.

For invoke configurations

• Stored Procedure/Operation on Table/Run PureSQL Statement Operations: 10 MB with schema transformation for all the outbound operations.

M. Apache Kafka Adapter

For invoke configurations

• Produce/Consume Message Operations: 10 MB with schema transformation for all the outbound operations.

N. JMS Adapters (Oracle WebLogic JMS Adapter and IBM MQ Series JMS Adapter)

For trigger configurations

• Consume Message Operation: 10 MB with schema transformation.

For invoke configurations

• Produce Message Operation: 10 MB with schema transformation.

O. Oracle CPQ Cloud Adapter - response payload

10 MB.

3. Integrations:

A. Connectivity agent - memory

A minimum of 8 GB memory with 4 GB of heap size dedicated to the on-premise agent's Java Virtual Machine (JVM). To include any other processes on the host besides the agent, increase the physical memory to a value greater than 8 GB.

B. Connectivity agent - message payload

10 MB, through the use of compression.

All connectivity-agent-enabled adapters

• 10 MB as request.

• 10 MB as response.

SOAP and REST adapters configured with connectivity agent

• 10 MB (structured XML/JSON document) as request.

• 10 MB (structured XML/JSON document) as response from private SOAP/REST endpoints.

• 1 GB for attachments as part of a request.

• 1 GB for attachments as part of a response from private SOAP/REST endpoints.

C. Stage File action (in orchestrated integrations) - file size

Read Entire File operation: 10 MB. For files greater than 10 MB, use the Read File in Segments operation.

Encrypt File operation: 1 GB.

Decrypt File operation: 1 GB.

D. Synchronous integration message timeout

300 seconds.

Synchronous integrations (integrations that return a response payload) return a timeout error if they run more than 300 seconds.

E. Oracle Integration Messaging - message size

10 MB.

F. Encode and Decode File Attachment Content (mapper)

The functions encodeReferenceToBase64(String reference) and decodeBase64ToReference(String base64Content) have a file size limit of 10 MB.

4. File Server

Storage: 500 GB.

Concurrent connections: Maximum of 20 connections per service instance

Oracle doc reference:

https://docs.oracle.com/en/cloud/paas/integration-cloud/oracle-integration-oci/service-limits.html#GUID-EB64C32A-BAEE-4ABC-BF2D-0E8B3FD5B848

OIC Gen 1 vs Gen 2 | Benefits of OIC Generation 2

Gen1 vs Gen2:

In Oracle Cloud terminology, "Classic" refers to the generation 1 cloud. OCI refers to generation 2 cloud which introduced Bare metal and off-box virtualization.

Oracle Strongly encourages cuatomers ro migrate their existing cloud resources from OCI compute classic regions to OCI GEN2.

OCI is a second generation cloud that provides a better than ever foundation for AI. It offers :

• MULTIPLE layers of  Security for users, data and infrastructures and the oracle autonomous database.
• Lower Compute costs
• Manage new high performance workloads.
• Application development
• Integration of Oracle and Non oracle apps(both on-prem and in the cloud).

Some Benefits of OIC Generation 2:

• Natively integrated with the Oracle Cloud Infrastructure Console : Simplifies creating and managing user accounts, permissions, and Oracle Integration instances

• Integration Insight : Integration Insight in Oracle Integration dramatically simplifies the process of modeling and extracting meaningful business metrics, allowing business executives to understand, monitor, and react quickly to changing demands

• File Server : File Server provides an embedded SFTP server within Oracle Integration, enabling organizations to focus on building integrations without needing to host and maintain a separate SFTP server

• Support for Oracle Cloud Infrastructure (OCI) Compartments : Organize your Oracle Integration instances into OCI compartments (for example, separate dev, test and production compartments). This lets you separate access to instance creation and control instance level management by department.

• Oracle Cloud Infrastructure Identity and Access Management (IAM)

 Provides a rich permission model that gives Oracle Integration users fine-grained access to Oracle Integration instances—for example, manage (create, edit, move, …) or view only.

OIC Retention period | Impact of increasing the retention period | Standard and Warning settings

This feature control how oracle integration retains your historical data. Runtime instance data is purged to free up space for the newer data. 

The default rentention period is 3 days.

What are the impacts of increasing the retention period if the default 3 days are not enough? 

Solution:

Following 3 ways we can do the retention settings:

• Standard
• Warning(Low Space) 
• Manual purge

Standard:

We can adjust the Database retention period for the "Standard Setting" to a higher number (for example, 7 or 10 days), based on your retention requirements. Every day at midnight, data older than this retention period is purged.

Warning(Low Space):
If the database space reaches a warning state, more aggressive settings are used. These settings are configured under "Warning (Low Space) Settings." It is recommended to set the "Warning Settings" lower than "Standard settings". Also, you may want to consider setting the "Keep Faulted Instances" to a higher number of days than the timeframe to retain "Successful Instances".

As for the impact to changing the settings, you will need to monitor your instance to make sure that you are not frequently hitting Warning or Critical states. You can monitor the Data Retention status in the same "Retention Settings" page. If you have Critical Health Alerts configured, you will receive an email notification if the database space reaches a critical state.

Manual Purge:
If the database space reaches a critical state, the system rejects incoming requests and stops all integrations and processes from running until more space becomes available. In these cases, you will need to perform a manual purge.

Steps to configure rentention settings:

Here , we have kept rentention period 30 days for standard , 90 days for Insight . For warning, its 10 days retention.

Setting

Data Retention

Setup 

ERP BI - How to create a simple BI publisher Report | get a sequence number using bip report

Here, we will show you how to create a BI report. Basically we will generate an unique Invoice id based on SQL sequence from dual.

SQL query:

Select AP_INVOICES_S.nextval invoice_id from dual

Steps in detail:

Login to ERP and Navigate to Tools >> Reports & Analytics

Browse Catalog

Create a new folder "Poc" under /Shared Folder /custom/Integration

New >> Data Model

Click "+" sign and Select SQL Query.

Provide Name, select data soirce and SQL query >> ok

Data >> view

Data 》 Save as Sample data

Save Data Model

Go back to Catalog

Now you can see data model is ready.

New 》Report

Select the created data model 》 Next

Select Table 》 Next

Drag and drop field names from data source 》 Next

Finish

Action》Edit Report

View list 

Select output format as Data(csv)

Save Report

Go back to Catalog

Open

You can see its showing the expected invoice id.