ITBrief: Rubicon Red on track for bumper year

ITBrief shares the details of several major announcements by Rubicon Red, that will help the company grow to new heights, putting it on track for a bumper year.

Rubicon Red has achieved a major go-live at an Australian Tier 1 financial institution, has achieved Oracle BPM 12c Specialisation and will feature as a sponsor of the upcoming inaugural Oracle Cloud Summit in Melbourne.

The fintech ‘go-live’ will transform the lending fulfilment process for both fulfilment staff and 2000-5000 bankers across the country. The new system will allow banking customers to potentially experience the green light on loan approvals up to three times faster, and with better customer service.

Rubicon Red has also achieved Oracle PartnerNetwork Specialisation for Oracle Business Process Management Suite 12c. This suite was the major delivery method that enabled the new banking system.

“As Oracle BPM and SOA Specialists, maintaining our staff expertise and ongoing enablement has always been important for Rubicon Red and we recognise the added value of specialisation for us and our customers,” comments Rubicon Red’s CEO John Deeb.

“With our focus now on helping our customers with their digital transformation and transition to the Cloud, we are also investing heavily in building and maintaining our expertise in Oracle Cloud,” Deeb says.

Read the full article here.

Monitoring DB Growth for FMW

While working on the customer site, I am sure most of us would have encountered the following questions(or more) from the clients (especially the DBA team):

  • How to track the table space growth for fusion middleware products and SOA-INFRA in particular?
  • When a new business process is deployed in to an environment, how to determine the  amount of table space its instances will require over time?
  • What will be the impact of increasing the SOA audit level on the table space growth ?

Its very difficult to answer the above questions upfront, but if we implement a monitoring solution on the table space growth, we could potentially be able to answer them. This blog aims at providing a solution that monitors the database growth both at the table and tablespace level.

This solution can be scheduled at a  weekly/Monthly frequency on production to monitor how the table space is growing (or) can be used to capture the snapshot before & after a load test to understand how much growth the table space has undergone. This could act as a vital statistic for increasing the table space whenever a new business process is rolled out to production.

The solution contains two tables to hold the db growth statistics :

  • SCH_TBL_SIZE_STATS_HDR - Captures the table space level growth statistics
  • SCH_TBL_SIZE_STATS_DTL - Captures the table level growth statistics

The below script creates the above mentioned tables and ideally requires to be created under a schema that has the DBA privileges to monitor any required tablespace:

  1. /**
  2. #####################################################################
  4. #####################################################################
  5. @schema_table_size_stats_tbl_script.sql
  6. Tables to contain the statistics regarding the tablespace size growth by schema.
  7. Copyright Rubicon Red Pty Ltd
  8. Author - gkrishna
  9. **/
  11. /
  13. /
  15. /
  17. /
  20. /
  22. /
  23. CREATE
  25.     (
  27.       OWNER_SCHEMA              VARCHAR2(30) NOT NULL,
  28.       RUN_DATE                  DATE NOT NULL,
  29.       MB_ALLOCATED              NUMBER NOT NULL,
  30.       MB_FREE                   NUMBER NOT NULL,
  31.       MB_USED                   NUMBER NOT NULL,
  32.       PCT_FREE                  NUMBER NOT NULL,
  33.       PCT_USED                  NUMBER NOT NULL
  34.     );
  35.   /
  36. CREATE
  38.     (
  41.       TABLE_NAME                VARCHAR2(30) NOT NULL,
  42.       NO_OF_ROWS                NUMBER(15) NOT NULL,
  43.       TABLE_SIZE_IN_MB          NUMBER,
  47.     );
  48.   /

The below package contains the procedure 'GATHER_SCHEMA_TABLE_SIZE' to gather the table space growth statistics and it needs to be created/compiled on the same schema as the above tables:

Package Specification

  1. --Package Specification
  3. AS
  4.   --Type to hold the list of schema for which the statistics needs to be calculated.
  6.   -- Procedure to gather the schema statistics
  9.   -- Procedure to clean up the stats before re-runs for the same day
  12.       P_RUN_DATE    IN DATE);
  14. /

Package Body

  1. --Package Body
  3. AS
  4. -- Procedure to gather the schema statistics
  7. IS
  9.   IS
  10.     SELECT
  11.       OBJECT_ID,
  12.       OBJECT_NAME
  13.     FROM
  14.       DBA_OBJECTS
  15.     WHERE
  16.       OBJECT_TYPE = 'TABLE'
  17.     AND OWNER     = P_OWNER
  18.     AND STATUS    = 'VALID'
  19.     AND GENERATED = 'N'
  20.     AND OBJECT_NAME NOT LIKE '%$%'; --System tables.
  21.   l_index  NUMBER;
  22.   l_hdr_id       NUMBER(18);
  23.   l_dtl_id       NUMBER(18);
  24.   l_schema_found VARCHAR2(1);
  25. BEGIN
  27.   LOOP
  28.     -- check to make sure the schema exists otherwise just continue with the
  29.     -- rest of the schemas in the list.
  30.     BEGIN
  31.       SELECT
  32.         'Y'
  33.       INTO
  34.         l_schema_found
  35.       FROM
  36.         DBA_USERS
  37.       WHERE
  38.         USERNAME = P_SCHEMA_LIST(l_index);
  39.     EXCEPTION
  41.       dbms_output.put_line('Invalid Schema'||P_SCHEMA_LIST(l_index));
  42.       CONTINUE;
  43.     END;
  44.     --clean up the statistics if it already exists for the day.
  46.     -- getting the primary key value for the header table.
  47.     SELECT
  49.     INTO
  50.       l_hdr_id
  51.     FROM
  52.       DUAL;
  53.     -- populating the header table with schema level details
  54.     INSERT
  55.     INTO
  57.       (
  58.         SCH_TBL_SIZE_STATS_HDR_ID,
  59.         OWNER_SCHEMA,
  60.         RUN_DATE,
  61.         MB_ALLOCATED,
  62.         MB_FREE,
  63.         MB_USED,
  64.         PCT_FREE,
  65.         PCT_USED
  66.       )
  67.     SELECT
  68.       *
  69.     FROM
  70.       (
  71.         SELECT
  72.           l_hdr_id,
  73.           a.tablespace_name OWNER_SCHEMA,
  74.           SYSDATE RUN_DATE,
  75.           ROUND(a.bytes /1048576,2) MB_ALLOCATED,
  76.           ROUND(b.bytes /1048576,2) MB_FREE ,
  77.           ROUND((a.bytes-b.bytes)/1048576,2) MB_USED,
  78.           ROUND(b.bytes /a.bytes * 100,2) PCT_FREE,
  79.           ROUND((a.bytes-b.bytes)/a.bytes,2) * 100 PCT_USED
  80.         FROM
  81.           (
  82.             SELECT
  83.               tablespace_name,
  84.               SUM(a.bytes) bytes
  85.             FROM
  86.               DBA_DATA_FILES a
  87.             GROUP BY
  88.               tablespace_name
  89.           )
  90.           a,
  91.           (
  92.             SELECT
  93.               a.tablespace_name,
  94.               NVL(SUM(b.bytes),0) bytes
  95.             FROM
  96.              DBA_DATA_FILES a,
  97.              DBA_FREE_SPACE b
  98.             WHERE
  99.               a.tablespace_name = b.tablespace_name (+)
  100.             AND a.file_id       = b.file_id (+)
  101.             GROUP BY
  102.               a.tablespace_name
  103.           )
  104.           b,
  105.           DBA_TABLESPACES c
  106.         WHERE
  107.           a.tablespace_name   = b.tablespace_name(+)
  108.         AND a.tablespace_name = c.tablespace_name
  109.         AND a.tablespace_name = P_SCHEMA_LIST(l_index)
  110.         ORDER BY
  111.           a.tablespace_name
  112.       );
  113.     -- Now find all the non-system tables in the schema and then populate the
  114.     -- statistics
  115.     -- to the detail table
  117.     LOOP
  118.       -- make sure we compute the statistics first before calculating the table
  119.       -- size.
  120.       EXECUTE immediate 'ANALYZE TABLE '||P_SCHEMA_LIST(l_index)||'.'||tab.OBJECT_NAME||
  121.       ' COMPUTE STATISTICS';
  122.       -- getting the primary key value for the detail table.
  123.       SELECT
  125.       INTO
  126.         l_dtl_id
  127.       FROM
  128.         DUAL;
  129.       -- populating the statistics for each table.
  130.       INSERT
  131.       INTO
  132.         SCH_TBL_SIZE_STATS_DTL
  133.         (
  134.           SCH_TBL_SIZE_STATS_DTL_ID,
  135.           SCH_TBL_SIZE_STATS_HDR_ID,
  136.           TABLE_NAME,
  137.           NO_OF_ROWS,
  138.           TABLE_SIZE_IN_MB
  139.         )
  140.       SELECT
  141.         l_dtl_id,
  142.         l_hdr_id,
  143.         table_name,
  144.         NVL(num_rows,0) ,
  145.         (
  146.           SELECT
  147.             SUM(bytes_in_mb) AS total_size_in_mb
  148.           FROM
  149.             (
  150.               SELECT
  151.                 dbs.bytes/(1024)/(1024) AS bytes_in_mb
  152.               FROM
  153.                 dba_segments dbs,
  154.                 dba_lobs dbl
  155.               WHERE
  156.                 dbl.table_name    =tab.OBJECT_NAME
  157.               AND dbs.segment_name=dbl.segment_name
  158.               UNION
  159.               SELECT
  160.                 dbs.bytes/(1024)/(1024) AS bytes_in_mb
  161.               FROM
  162.                 dba_segments dbs,
  163.                 dba_indexes dbi
  164.               WHERE
  165.                 dbi.table_name    =tab.OBJECT_NAME
  166.               AND dbs.segment_name=dbi.index_name
  167.               UNION
  168.               SELECT
  169.                 dbs.bytes/(1024)/(1024) AS bytes_in_mb
  170.               FROM
  171.                 dba_segments dbs,
  172.                 dba_tables dbt
  173.               WHERE
  174.                 dbt.table_name    =tab.OBJECT_NAME
  175.               AND dbs.segment_name=dbt.table_name
  176.             )
  177.             tbl_size
  178.         ) AS total_size_in_mb
  179.       FROM
  180.         dba_tables tbl
  181.       WHERE
  182.         tbl.table_name=tab.OBJECT_NAME
  183.       AND tbl.owner   =P_SCHEMA_LIST(l_index);
  184.       -- May be we need a better strategy here for commit.. for now
  185.       -- this should be ok
  186.       COMMIT;
  187.     END LOOP;
  188.   END LOOP;
  190. -- Procedure to clean up the stats before re-runs for the same day
  193.     P_RUN_DATE    IN DATE)
  194. IS
  195. BEGIN
  196.   --deleting the detail table statistics for the given schema
  197.   DELETE
  198.   FROM
  200.   WHERE
  202.     (
  203.       SELECT
  204.         SCH_TBL_SIZE_STATS_HDR_ID
  205.       FROM
  206.         SCH_TBL_SIZE_STATS_HDR
  207.       WHERE
  208.         TRUNC(RUN_DATE) = TRUNC(P_RUN_DATE)
  210.     );
  211.   --deleting the header table statistics for the given schema
  212.   DELETE
  213.   FROM
  215.   WHERE
  216.         TRUNC(RUN_DATE) = TRUNC(P_RUN_DATE)
  218.     COMMIT;
  221. /

Now lets execute the procedure to analyze the statistics for SOA-INFRA schema:

  3. BEGIN
  6. END;
  7. /

Once the script finishes, lets check the generated data:


FMW DB Growth statistics tablespace level

The above result set shows the statistics for 25th & 26th September 2014 and it can be seen that there is a growth of 20 MB over all in the schema. Querying the detail table should give us the statistics at the table level:


FMW DB Growth statistics table level

Above is the snapshot of table-level growth for 26th September and simple queries can be written to calculate the difference based on the previous run dates to identify how much the table has grown in rows/size.

SOA Suite 12c Quick Start

Lets get started!

In the below tutorial, we will show you just how easy it is to get a "basic" SOA Suite 12c environment up, running and ready for development to commence!

Oracle announced last month, the release of Oracle SOA Suite 12c which marks a major step forward in supporting “industrial” SOA, and offer the industry’s most highly integrated middleware platform. With the rapid adoption of Cloud, Mobile and Internet of Things, the need for a robust, proven and standards based SOA platform has become central to an organisations ability to deliver on these key initiatives.

SOA Suite 12c offers a significantly improved development experience. One such example of this is the "quick start" installer which contains everything required to get started developing for SOA Suite 12c in under 30 minutes!

So what is in included in the "Quick Start" installer?

  • WebLogic Application Server
  • Coherence  In-Memory Grid
  • SOA Suite including:
    • Oracle BPEL
    • Oracle Mediator
    • Oracle Human Workflow
    • Oracle Service Bus
    • Oracle Rules
    • Technology Adapters
  • Enterprise Manager Fusion Middleware Control
  • Lightweight In-Memory Database
  • ...and of course, JDeveloper IDE
    • with all of the mandatory plugins pre-installed!

Please Note: B2B, Healthcare & Oracle Event Processing are separate downloadable add-ons to SOA Suite.

What about Enterprise Deployment?

It is important to note that the SOA Suite 12c "quick start" installer will not give you a production ready environment. It is merely designed to meet the development or evaluation use cases.

When it comes to Enterprise Deployment of SOA Suite 12c, there are a number of important steps to take and the SOA Suite 12c Enterprise Deployment Guide (EDG) is the best place to start!

The EDG can be a daunting guide at first with some 250+ pages but there is no need to be afraid! In a series of posts Rubicon Red will guide you in your journey to SOA Suite 12c production. We'll show you how to ensure reliable, repeatable and consistent environment delivery that meets the requirements outlined in the EDG. With the Rubicon Red MyST declarative-based provision tool, you'll see how a highly-available, secure and robust environment can be realised without the pain, sorrow and despair... With SOA Suite 12c and MyST, the Journey is the Reward.

Case Study: Premium Wine Brands – Oracle Fusion Middleware

Premium Wine Brands Pty Ltd Leverages SOA for Process Automation and Application Integration; increasing Return on Investment while Reducing Cost of Ownership

Premium Wine Brands and implementation partner Rubicon Red were recently recognized for their innovative use of Oracle Fusion Middleware at the Oracle Innovation Award ceremony held at Oracle Open World 2010 in San Francisco.

By using Oracle SOA Suite 11g on Oracle WebLogic Server 11g, Premium Wine Brands has been able to build an environment leveraging service reuse (via a Central Service Repository) to reduce complexity and total cost of ownership. Oracle Service Bus 11g is central to this aspect of the architecture.

Click here to read the full article

Case Study: Powercor and Oracle Directory Services

Rubicon Red enables Powercor to simplify their SOA deployment and reduce time to market with Oracle Directory Services

Powercor is a leading electricity generating company in Australia.

To make it easier to communicate with electricity end-users in particular as Powercor updates the electrical meters, they deployed a new IT infrastructure based around Oracle technologies.  This includes Oracle SOA Suite and Oracle Identity Management.

The foundation of this deployment is Oracle Directory Services which is used to provide authentication, user contact information and makes it easy for applications to connect to the data via standard interfaces such as LDAP and DSML.

This has made it simpler for Powercor to deploy their new infrastructure and reduced time to market.

This case study shows how Rubicon Red used Oracle Directory Services to enable Powercor to simplify their SOA deployment and reduce time to market.

Click here to view the Oracle Whitepaper