Feed aggregator

Unique constraint WWV_FLOW_WORKSHEET_RPTS_UK violated

Jeff Kemp - Thu, 2016-05-12 19:31

If your Apex application import log shows something like this:

...PAGE 73: Transaction Lines Report
ERROR at line 1:
ORA-00001: unique constraint (APEX_040200.WWV_FLOW_WORKSHEET_RPTS_UK)
ORA-06512: at "APEX_040200.WWV_FLOW_API", line 16271
ORA-06512: at line 6

(this is on an Apex 4.2.4 instance)

This is due to a Saved Report on an Interactive Report that was included in the export, which conflicts with a different Saved Report in the target instance. The log will, conveniently, tell you which page the IR is on.

The solution for this problem is simple – either:

(a) Export the application with Export Public Interactive Reports and Export Private Interactive Reports set to No;
(b) Delete the Saved Report(s) from the instance you’re exporting from.

You can find all Saved Reports in an instance by running a query like this:

select workspace
where application_user not in ('APXWS_DEFAULT'

You can delete Saved Reports from the Application Builder by going to the page with the Interactive Report, right-click on the IR and choose Edit Saved Reports, then select the report(s) and click Delete Checked.

Filed under: APEX Tagged: APEX, problem-solved

Migrating Your Enterprise Applications To Amazon Web Services (AWS)

Pythian Group - Thu, 2016-05-12 15:34


Many of the enterprise clients we work with are looking at Amazon Web Services (AWS) to support their cloud strategies and reap the benefits of the public cloud: lower costs, higher scalability, greater availability of computing resources.


AWS is well known for its cutting edge service offerings, which are always growing and evolving—making them an easy choice to recommend to our enterprise clients. When used to provide infrastructure as a service (IaaS), it simplifies hardware provisioning and management and makes it easy to allocate compute, memory and storage capacity. In a platform as a service (PaaS) situation, it can streamline operations through automated backups and patching, and can offer the convenience of pay-as-you-go billing.


The challenge for many organizations migrating to the cloud comes when they start to move their enterprise applications. That’s because these applications often rely on highly customized infrastructure with tightly coupled components. They also tend to exist in silos. Some careful planning is needed to make sure the new cloud environment delivers the outperforms the legacy on-premises one in terms of scalability, reliability and performance. Whenever desired by the customer, we will often recommend and apply a number of optimizations during the migration process, such as decoupling of components, stability and performance tune-ups as well as improved operational  visibility and platform automation.


When we migrated Harvard Business Publishing to AWS, for example, we tailored the public cloud architecture to meet their specific needs. (Harvard Business Publishing is a subsidiary of Harvard University — based in Boston, with offices in New York City, India, Singapore, Qatar and the United Kingdom. They produce and distribute content across multiple platforms.)


Harvard Business Publishing was running Oracle applications in a data center on three Oracle Database Appliances. With the data center lease coming up for renewal, they asked us to help migrate their Oracle-based production, quality assurance and disaster recovery systems to an AWS cloud. Obviously, these systems were mission critical.


We did thorough capacity planning and developed a reliable, highly-automated and predictable migration approach up front, customized the AWS architecture, and migrated to a large number of virtual machines and Oracle schemas.


In another instance, we migrated CityRealty’s core applications to the cloud from on-premise hardware running Oracle databases. CityRealty is the oldest continuously operating real estate website in New York City — the world’s largest real estate market — and remains the city’s leading site today. We proposed moving three production databases to Amazon Elastic Compute Cloud (EC2), which provides highly scalable compute capacity, as well as upgrading the Oracle databases. We also built a highly efficient standby and recovery solution using Amazon Elastic Block Storage (EBS) snapshots.


In both of these cases, we did the planning up front to ensure the new cloud environment would be ready to receive the clients’ applications and run them without any compromise in performance. We’ve done similar migrations for clients operating e-commerce businesses with revenues of millions and even billions per year. Every situation is a little different: in some cases clients needed to simplify and standardize their mission-critical systems; in others, boost reliability; in others, increase automation or eliminate transaction-hampering latency.


We have the benefit of small, dedicated cloud teams around the world with multiple, advanced AWS certifications to address even the most complex requirements. Our goal always is to ensure the public cloud architecture is ideally suited to the enterprise, and to provide detailed implementation plans and data management solutions for optimal performance. That allows us to implement and manage public, private, and hybrid environments with lower risk and cost for organizations that want to seize the benefits of the cloud.

Find out how Pythian can help you with cloud solutions for AWS  today.

Categories: DBA Blogs

Understanding query slowness after platform change

Bobby Durrett's DBA Blog - Thu, 2016-05-12 14:54

We are moving a production database from 10.2 Oracle on HP-UX 64 bit Itanium to 11.2 Oracle on Linux on 64 bit Intel x86. So, we are upgrading the database software from 10.2 to 11.2. We are also changing endianness from Itanium’s byte order to that of Intel’s x86-64 processors. Also, my tests have shown that the new processors are about twice as fast as the older Itanium CPUs.

Two SQL queries stand out as being a lot slower on the new system although other queries are fine. So, I tried to understand why these particular queries were slower. I will just talk about one query since we saw similar behavior for both. This query has sql_id = aktyyckj710a3.

First I looked at the way the query executed on both systems using a query like this:

select ss.sql_id,
where ss.sql_id = 'aktyyckj710a3'
and ss.snap_id=sn.snap_id
and executions_delta > 0
order by ss.snap_id,ss.sql_id;

It had a single plan on production and averaged a few seconds per execution:

PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average rows processed
--------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
      918231698 11-MAY-16 PM              195         1364.80228     609.183405    831.563728                  0                      0                      0          35211.9487             1622.4             6974.40513
      918231698 11-MAY-16 PM              129         555.981481     144.348698    441.670271                  0                      0                      0          8682.84496         646.984496             1810.51938
      918231698 11-MAY-16 PM               39         91.5794872     39.6675128    54.4575897                  0                      0                      0          3055.17949          63.025641             669.153846
      918231698 12-MAY-16 AM               35         178.688971     28.0369429    159.676629                  0                      0                      0          1464.28571              190.8             311.485714
      918231698 12-MAY-16 AM              124         649.370258     194.895944    486.875758                  0                      0                      0           13447.871         652.806452             2930.23387
      918231698 12-MAY-16 AM              168         2174.35909     622.905935    1659.14223                  0                      0             .001303571          38313.1548         2403.28571             8894.42857
      918231698 12-MAY-16 AM              213         3712.60403     1100.01973    2781.68793                  0                      0             .000690141          63878.1362               3951             15026.2066
      918231698 12-MAY-16 PM              221         2374.74486      741.20133    1741.28251                  0                      0             .000045249          44243.8914         2804.66063               10294.81

On the new Linux system the query was taking 10 times as long to run as in the HP system.

PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average rows processed
--------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
     2834425987 10-MAY-16 PM               41         39998.8871     1750.66015    38598.1108                  0                      0                      0          50694.1463         11518.0244             49379.4634
     2834425987 10-MAY-16 PM               33         44664.4329     1680.59361    43319.9765                  0                      0                      0          47090.4848         10999.1818             48132.4242
     2834425987 11-MAY-16 AM                8          169.75075      60.615125      111.1715                  0                      0                      0             417.375                 92                2763.25
     2834425987 11-MAY-16 PM               11         14730.9611     314.497455    14507.0803                  0                      0                      0          8456.63636         2175.63636             4914.90909
     2834425987 11-MAY-16 PM                2           1302.774       1301.794             0                  0                      0                      0               78040                  0                  49013
     2834425987 11-MAY-16 PM                1           1185.321       1187.813             0                  0                      0                      0               78040                  0                  49013
     2834425987 11-MAY-16 PM               14         69612.6197     2409.27829     67697.353                  0                      0                      0          45156.8571         11889.1429             45596.7143
     2834425987 11-MAY-16 PM               16         65485.9254     2232.40963    63739.7442                  0                      0                      0          38397.4375         12151.9375             52222.1875
     2834425987 12-MAY-16 AM               61         24361.6303     1445.50141    23088.6067                  0                      0                      0          47224.4426         5331.06557              47581.918
     2834425987 12-MAY-16 AM               86         38596.7262     1790.56574    37139.4262                  0                      0                      0          46023.0349         9762.01163             48870.0465

The query plans were not the same but they were similar. Also, the number of rows in our test cases were more than the average number of rows per run in production but it still didn’t account for all the differences.

We decided to use an outline hint and SQL Profile to force the HP system’s plan on the queries in the Linux system to see if the same plan would run faster.

It was a pain to run the query with bind variables that are dates for my test so I kind of cheated and replaced the bind variables with literals. First I extracted some example values for the variables from the original system:

select * from 
(select distinct
sb.sql_id='aktyyckj710a3' and
order by 

Then I got the plan of the query with the bind variables filled in with the literals from the original HP system. Here is how I got the plan without the SQL query itself:

truncate table plan_table;

explain plan into plan_table for 
-- problem query here with bind variables replaced

set markup html preformat on

select * from table(dbms_xplan.display('PLAN_TABLE',

This plan outputs an outline hint similar to this:

      NO_ACCESS(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3")
      OPT_PARAM('query_rewrite_enabled' 'false')

Now, to force aktyyckj710a3 to run on the new system with the same plan as on the original system I had to run the query on the new system with the outline hint and get the plan hash value for the plan that the query uses.

explain plan into plan_table for 
Plan hash value: 1022624069

So, I compared the two plans and they were the same but the plan hash values were different. 1022624069 on Linux was the same as 918231698. I think that endianness differences caused the plan_hash_value differences for the same plan.

Then we forced the original HP system plan on to the real sql_id using coe_xfr_sql_profile.sql.

-- build script to load profile

@coe_xfr_sql_profile.sql aktyyckj710a3 1022624069

-- run generated script


Sadly, even after forcing the original system’s plan on the new system, the query still ran just as slow. But, at least we were able to remove the plan difference as the source of the problem.

We did notice a high I/O time on the Linux executions. Running AWR reports showed about a 5 millisecond single block read time on Linux and about 1 millisecond on HP. I also graphed this over time using my Python scripts:

Linux db file sequential read (single block read) graph:


HP-UX db file sequential read graph:


So, in general our source HP system was seeing sub millisecond single block reads but our new Linux system was seeing multiple millisecond reads. So, this lead us to look at differences in the storage system. It seems that the original system was on flash or solid state disk and the new one was not. So, we are going to move the new system to SSD and see how that affects the query performance.

Even though this led to a possible hardware issue I thought it was worth sharing the process I took to get there including eliminating differences in the query plan by matching the plan on the original platform.



Our Linux and storage teams moved the new Linux VM to solid state disk and resolved these issues. The query ran about 10 times faster than it did on the original system after moving Linux to SSD.

HP Version:

------------------------- ---------------- ------------------ PM                        245         5341.99923 PM                        250         1280.99632 PM                        341         3976.65855 PM                        125         2619.58894


------------------------- ---------------- ------------------
16-MAY-16 AM              162         191.314809
16-MAY-16 AM              342         746.313994
16-MAY-16 AM              258         461.641705
16-MAY-16 PM              280         478.601618

The single block read time is well under 1 millisecond now that 
the Linux database is on SSD.

END_INTERVAL_TIME          number of waits ave microseconds 
-------------------------- --------------- ---------------- 
15-MAY-16 PM           544681       515.978687
16-MAY-16 AM           828539       502.911935
16-MAY-16 AM           518322       1356.92377
16-MAY-16 AM            10698       637.953543
16-MAY-16 AM              193       628.170984
16-MAY-16 AM              112        1799.3125
16-MAY-16 AM             1680       318.792262
16-MAY-16 AM              140       688.914286
16-MAY-16 AM             4837       529.759768
16-MAY-16 AM            16082       591.632508
16-MAY-16 AM           280927       387.293204
16-MAY-16 AM           737846        519.94157
16-MAY-16 AM          1113762       428.772997
16-MAY-16 PM           562258       510.357372


Categories: DBA Blogs

Database Migration and Integration using AWS DMS

Kubilay Çilkara - Thu, 2016-05-12 14:12

Amazon Web Services (AWS) recently released a product called AWS Data Migration Services (DMS) to migrate data between databases.

The experiment

I have used AWS DMS to try a migration from a source MySQL database to a target MySQL database, a homogeneous database migration.

The DMS service lets you use a resource in the middle Replication Instance - an automatically created EC2 instance - plus source and target Endpoints. Then you move data from the source database to the target database. Simple as that. DMS is also capable of doing heterogeneous database migrations like from MySQL to Oracle and even synchronous integrations. In addition AWS DMS also gives you a client tool called AWS Schema Converter tool which helps you convert your source database objects like stored procedures to the target database format. All things a cloud data integration project needs!

In my experiment and POC, I was particularly interested in the ability of the tool to move a simple data model as below, with 1-n relationship between tables t0(parent) and t1(child) like below.

(Pseudo code to quickly create two tables t0, t1 with 1-n relationship to try it. Create the tables both on source and target database)

t0 -> t1 Table DDL (Pseudo code)

  `id` int(11) NOT NULL,
  `txt` varchar(100) CHARACTER SET ucs2 DEFAULT NULL,
  PRIMARY KEY (`id`)

  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `t0id` int(9) DEFAULT NULL,
  `txt` char(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `t0id` (`t0id`),

In this experiment, I didn't want to see just a migration, a copy, of a table from source database to a target database. I was interested more to see how easy is to migrate a data model - with Primary Key and Foreign Key relationship in place -  from the source database to the target database with zero downtime and using their CDC (Changed data Capture) or Ongoing-Replication migration option and capabilities of AWS DMS. That is, zero downtime database migration.

Here are the results of the experiment.

AWS DMS is ubiquitous, you can quickly set-up an agent (Replication Instance) and define source & target endpoints and start mapping your tables to be migrated from source database to target database with the tool. All conveniently using the AWS console.

Once you setup your replication instance and endpoints, create a Migration Task (say Alpha) and do an initial full migration (load) from the source database to the target database. Do this with the foreign keys (FKs) disabled on the target. This is a recommendation in the AWS DMS Guide in order to dump the data super fast as it does it with parallel threads, at least this is the recommendations for MySQL targets.

Then you can create a second Migration Task (say Beta) using a different endpoint, but this time with the foreign keys enabled on the target. You can do this even before your full load with Alpha to avoid waiting times. Configure Beta interface/task to run forever and let it integrate and sync the delta which occurred during the initial load. You can even start the Beta interface from a cut-off timestamp point. It uses source MySQL database's binlogs to propagate the changes. If you don't create beta interface, that is to use a different endpoint for the target with the parameter which enables the FKs, the DELETE SQL statements on the source which occur during the migration will not propagate to the target correctly and the CASCADEs to the child tables will not work on the target. CASCADE is a property of the Foreign Key.

To reconcile, to find out if you have migrated everything, I had to count the rows in each table on source and the target databases to monitor and see if it all worked. To do that I used Pentaho Spoon CE to quickly create a job to count the rows on both source and target database and validate migration/integration interfaces.

Overall, I found AWS DMS very easy to use, it quickly helps you wire an integration interface in the Cloud and start pumping and syncing data between sources and targets databases be it on Premise or Cloud. A kind of Middleware setup in AWS style, in the Cloud. No more middleware tools for data migration, AWS now has it's own. 
Categories: DBA Blogs

Simbec-Orion Group Streamlines its Clinical Trial Management Processes with Oracle’s eClinical Cloud Suite

Oracle Press Releases - Thu, 2016-05-12 12:25
Press Release
Simbec-Orion Group Streamlines its Clinical Trial Management Processes with Oracle’s eClinical Cloud Suite

Redwood Shores, Calif.—May 12, 2016

As more pharmaceutical companies outsource their clinical trials to contract research organizations (CROs), CROs are challenged to complete the clinical research in a more efficient and timely manner. Oracle Health Sciences today announced that Simbec-Orion Group Limited, a full service, international, boutique CRO, has adopted Oracle’s suite of eClinical cloud solutions to simplify the management of its customer’s clinical studies.

Oracle’s life sciences-specific solutions, combined with its broader product and service offering, are used by thousands of pharma, biopharma, and academic research centers worldwide to redesign clinical R&D processes, advance drug therapies, and optimize care delivery to improve the health, well-being, and lives of people globally.

Simbec-Orion Group selected cloud-based Oracle Health Sciences InForm, Siebel Oracle's Siebel Clinical Trial Management System, and Oracle Health Sciences IRT to supplement its clinical development processes, streamline data capture, and speed up trial timelines as Simbec-Orion expands its international, full-service, business offerings.

“We reviewed a wide range of providers to ensure, that as a world-class CRO, we were working with the best–in-class systems”, said Ronald Openshaw, CEO, Simbec-Orion. “We chose to partner with Oracle, the market leader in eClinical solutions, as we wanted to rely upon an excellent, secure, robust, and state-of-the-art service that will meet current and foreseeable, future, market needs.  We believe the scalability will drive efficiency in our work practices as we grow, especially in the light of increasingly complex clinical trials.”

“Oracle shares Simbec-Orion’s commitment to helping pharma and biotechnology companies deliver high-quality therapies to the patients we serve,” said Steve Rosenberg, senior vice president and general manager, Oracle Health Sciences. “ We look forward to working with Simbec-Orion as it further expands and enhances its clinical development services powered by our innovative technology.”

In 2014 through the merger of the two independent CROs, Simbec Research and Orion Clinical, Simbec-Orion Group was created as an international boutique CRO. Simbec-Orion Group now offers a full spectrum of drug development services from first-in-human Phase I clinical studies, through to pivotal Phase III studies and Phase IV post marketing studies. Starting in January 2016, unless the client expresses different requirements, new studies across the Simbec-Orion Group will use Oracle’s eClinical suite.

Contact Info
Samantha Thai
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit www.oracle.com.


Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Safe Harbor

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Samantha Thai

  • +1.510.917.5564

China-Based Contract Research Organization Selects Oracle Cloud to Optimize Clinical Trial Data Management

Oracle Press Releases - Thu, 2016-05-12 12:25
Press Release
China-Based Contract Research Organization Selects Oracle Cloud to Optimize Clinical Trial Data Management

Redwood Shores, Calif.—May 12, 2016

Fountain Medical Development (FMD), a full-service Contract Research Organization (CRO) headquartered in Beijing, China, and its US affiliate, FMD K&L, have adopted Oracle’s cloud-based clinical data management solutions, to increase operational efficiencies and speed time to market. The solutions deliver increased productivity to the company’s drug development process, reduce overall costs, improve communications with its sponsors, and advance clinical study quality and speed, worldwide.

FMD was able to retire its aging, antiquated, legacy system and migrate to a more cost-effective cloud-based environment with Oracle Health Sciences InForm and Oracle Siebel Clinical Trial Management System (CTMS). The new, unified, technology platform has enhanced capabilities in data management, study planning and design, trial operations, analytics, and patient engagement.

“We are committed to delivering innovative solutions to our sponsors. This is why FMD decided to work with Oracle,” said Dr. Henry Wu, CEO, Fountain Medical Development. “Oracle’s data management solutions will enable us to improve our data quality, advance our trial data management efficiencies, reduce costs, and gain a deeper understanding of our clinical trial data across FMD’s integrated enterprise globally.”

Fountain Medical Development is a leading CRO offering a full range of International Conference on Harmonization Good Clinical Practice (ICH GCP) compliant clinical research services for multinational clients conducting clinical research in China. As one of the fastest growing clinical CROs in East Asia and China, FMD has established 12 operational offices throughout China, Hong Kong, Taiwan, Japan, Korea, the United States, and Armenia, with a strong focus on data management and biostatistics.

By moving to Oracle Health Sciences InForm and Oracle Siebel CTMS), FMD will gain powerful analytics capabilities that will provide a faster and better understanding of its clinical trial data across its locations, saving the company time and effort.

“Clinical research is becoming more complex and holistic by the day, and contract researchers are challenged to provide more services than ever before. Oracle Health Sciences provides global contract research organizations, like FMD, with an integrated platform and advanced analytics tools to help CROs and their sponsors find the cures of tomorrow,” said Steve Rosenberg, senior vice president and general manager, Oracle Health Sciences.

FMD is a member of the Oracle CRO Advantage Program, an initiative that helps drive CRO success with Oracle Health Sciences products through training, sales and marketing support, and bi-directional communication on Oracle products and strategy.

Contact Info
Samantha Thai
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit www.oracle.com.


Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Safe Harbor

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Samantha Thai

  • +1.510.917.5564

Oracle BPM 12c: Browsing the SOAINFRA

Jan Kettenis - Thu, 2016-05-12 12:17
In this article I discuss some tables from the SOAINFRA schema that might be most interesting to use when trying to find out why you don't see in Enterprise Manager what you expect.

Going from 11g to 12c, some things have significantly changed in the SOAINFRA schema. For example, your normal partners in helping with "what happened with my process?" type of queries, like the component_instance, and bpm_process tables, have become obsolete. On the other hand you have new friends with tables like sca_flow_instance, and sca_entity.

The following discusses some tables that you might want to look into when digging in the dirt of the SOA/BPM engine's intestines.

The tables I would like to discuss in more detail are:
- sca_flow_instance
- cube_instance
- wftask
- sca_entity
- bpm_cube_process
- bpm_cube_activity

Given that there is no official documentation on these tables, this is based on my observations an interpretations. No guarantee that these are flawless, so if you have anything to improve or add, let me know!

To better understand the data in the SOAINFRA in relation to an actual process, I used 1 composite with the following processes, that has two subprocesses (another BPM process and a BPEL process). The BPM subprocess has not been implemented as a reusable process (with a Call activity) but instead as a process-as-a-service.

As a side note: originally I created this process to be able to verify how the different states a process and its children can have, are represented in Enterprise Manager. The reason being that on one of my projects there were some doubts if this is always correct, given some issues in the past with 11g. With 12c I could find none. However, as the test case does not concern inter-composite interaction, nor does it include all types of technologies, you could argue that the test case is too limited to conclude anything from it. Also worth to mention is that the instances are ran on a server in development mode, and without in-memory optimization. I have heard rumors that you will observer different behavior when you disabled auditing completely. In some next posting I hope to discuss that as well.

I initiated several instances, for each possible state one:

sca_flow_instanceAs the name already suggests, this table contains 1 entry for each flow instance. You might be interested in the following columns:
  •   flow_id
  •   title
  •   active_component_instances
  •   recoverable_faults
  •   created_time
  •   updated_time

When queried this looks similar to this:

    The query used is like this:

    select sfi.flow_id
    ,      sfi.title
    ,      sfi.active_component_instances
    ,      sfi.recoverable_faults
    ,      sfi.created_time
    ,      sfi.updated_time
    from  sca_flow_instance sfi
    order by sfi.created_time

    This table contains 1 entry for each component instance in the flow (e.g. bpmn, bpel). You might be interested in the following columns:
    • flow_id
    • composite_label (*)
    • cpst_inst_created_time (**)
    • composite_name
    • composite_revision
    • component_name
    • componenttype
    • state (of the component <== mention)
    • creation_date (incl time)
    • modify_date (incl time)
    • conversation_id

    (*) corresponds with the bpm_cube_process.scalabel
    (**) equals sca_flow_instance.created_time

    When queried this looks similar to this:

    The query used is like this:

    select cis.flow_id
    ,      cis.componenttype
    ,      cis.component_name
    ,      cis.state
    from   cube_instance cis
    order by cis.flow_id

    This table contains an entry for each open process activity and open or closed human activity. You might be interested in the following columns:
    • flow_id
    • instanceid
    • processname
    • accesskey (not for human tasks) (*)
    • createddate
    • updateddate
    • (only in case of human tasks, the flex fields)
    • componentname
    • compositename (not for human tasks)
    • conversationid
    • componenttype (***)
    • activityname
    • activityid (****)
    • component_instance_id (only for human tasks)
    • state (*****)

    (*) : the type of activity, e.g. USER_TASK, INCLUSIVE_GATEWAY, END_EVENT
    (**) not for human tasks
    (***) e.g. Workflow, BPMN
    (****) Corresponds with the activityid of bpm_cube_activity. The user activity and its corresponding human task appear to have the same activityid. After the human task is completed, the user activity disappears but the human task is kept with an null state.
    (*****) e.g. OPEN for running activities, ASSIGNED for running human tasks. Other states are ABORTED, PENDING_MIGRATION_SUSPENDED, ERRORED, etc.

    When queried this looks similar to this:

    The query used is like this:

    select wft.instanceid
    ,      wft.processname
    ,      wft.accesskey
    ,      wft.createddate
    ,      wft.updateddate
    ,      wft.componentname
    ,      wft.compositename
    ,      wft.conversationid
    ,      wft.componenttype
    ,      wft.activityname
    ,      wft.activityid
    ,      wft.component_instance_id
    ,      wft.state
    from   wftask wft
    where  wft.flow_id = 130001
    order by wft.updateddate

    sca_entity This table contains an entry for each SCA entity (e.g. service, wire). The following column might be of use:
    •  id
    •  composite (name)
    •  label (corresponds with the scalabel of bpm_cube_process)

    When queried this looks similar to this:

    The query used is like this:

    select sen.composite
    ,      sen.id
    ,      sen.label
    from   sca_entity sen
    where  sen.composite = 'FlowState'
    order by sen.composite

    bpm_cube_processThis table contains metadata. For each deployed composite it contains an entry for each BPM process. If 2 BPM processes in once composite: 2 entries. The following columns might be of use:
    • domainname
    • compositename
    • revision
    • processid
    • processname
    • scalabel
    • compositedn
    • creationdate  (incl time)
    • undeploydate
    • migrationstatus (*)
    (*) Values are LATEST, MIGRATED.

    When queried this looks similar to this:

    The query used is like this:

    select bcp.domainname
    ,      bcp.compositename
    ,      bcp.revision
    ,      bcp.processname
    ,      bcp.processid
    ,      bcp.scalabel
    ,      bcp.compositedn
    ,      bcp.creationdate
    ,      bcp.undeploydate
    ,      bcp.migrationstatus
    from   bpm_cube_process bcp
    where  bcp.compositename = 'FlowState'
    order by bcp.processname
    ,        bcp.creationdate

    bpm_cube_activityThis table contains metadata, There is an entry for each individual activity, event, and gateway of a bpmn process. The following column might be of use:
    • processid (corresponds with the bpm_cube_process.processid)
    • activityid
    • activityname (technical, internal name can be found in the .bpmn source)
    • activitytype (e.g. START_EVENT, SCRIPT_TASK, CALL_ACTIVITY, etc.)
    • label (name as in the BPMN diagram)
    The rows in the example below have been queried by a join with the bpm_cube_process table on processid, where undeploydate is not null and migrationstatus is 'LATEST' to get only the activities of the last revision of one particular process:

    The query used is like this:

    select cbi.flow_id
    ,      cbi.composite_label
    ,      cbi.cpst_inst_created_time
    ,      cbi.composite_name
    ,      cbi.composite_revision
    ,      cbi.component_name
    ,      cbi.componenttype
    ,      cbi.state
    ,      cbi.creation_date
    ,      cbi.modify_date
    ,      cbi.conversation_id
    from   cube_instance cbi
    order by cbi.creation_date

    Obsolete TablesThe following table have become obsolete:
    • bpm_activity
    • bpm_activity_instance
    • bpm_cube_activity_instance
    • bpm_process
    • component_instance
    The composite_instance is still used, but more or less superseded by the sca_flow_instance (although the number of instances are not the same). I do not longer find it useful to query.

    Properly removing users in MySQL

    Pythian Group - Thu, 2016-05-12 09:58

    Removing users in MySQL is an easy task, but there are some drawbacks to check carefully before dropping a user. Not taking these possible issues into consideration can render your application unusable.

    First it is important to understand the concept of user in MySQL. A user has three main functions: authentication, privileges and resources. This is different from other databases: in MySQL users don’t own objects. An object belongs to a database and there is no direct relationship between objects and users. This simplifies the process of deleting a user because no objects will be erased.

    But users, as I wrote before, have an associated set of privileges that define what database sessions can do, and the privileges applied both in stored programs and view execution.

    At the same time, procedures, functions, triggers, and views have two possible security contexts: invoker and definer. If they are created using the invoker security model, then the user calling the routine or view must have enough privileges to execute the code within the routine or view. But if created using the definer security model, the code can be executed if the definer has enough privileges to execute it. And yes, the default security model in MySQL is definer.

    This means that, unless declared otherwise, most routines will check the privileges for the user that created the routine. If somebody removes that user, querying the view or executing the code will fail with error. All the procedures, functions, views and triggers created by that user with the default options will become unusable.

    How do we fix this? The quick and dirty way is to recreate the user with a different password to avoid user logins. It is not an elegant solution but probably this is the first thing you will do while you solve the problem correctly.  Another alternative, if you are running MySQL 5.7 is account locking, this feature disables login for that account but allows code and views to be executed. In any case, it is a good practice to make a backup of the user creation scripts prior to dropping the user. Percona toolkit has the utility pt-show-grants for that purpose.

    The elegant way to avoid the problem is to check that there are not routines or views using the definer security model and configured to run with the user privileges of the user you want to remove. There are several tables in the mysql user catalog that provide you with this information.

    Unfortunately there is not an easy way to change this attributes. The best thing you can do is drop and recreate those objects using different security characteristics.

    Let see an example:

    Connect to the database using an account with enough privileges to create users, databases and procedures and create a new database only for testing purposes.

    mysql> create database remove_test;
    Query OK, 1 row affected (0,05 sec)

    Create a user with privileges on the database created in previous step:

    mysql> grant all privileges on remove_test.* to test_user@'%' identified by 'test';
    Query OK, 0 rows affected, 1 warning (0,20 sec)

    Connect to the database using the user created:

    $ mysql -u test_user -ptest remove_test

    Create a view, check the attributes and execute it. We are going to use a couple of functions that I will explain a bit later.

    mysql> create view test_view as select current_user(),user();
    Query OK, 0 rows affected (0,05 sec)
    mysql> show create view test_view\G
    *************************** 1. row ***************************
                    View: test_view
             Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`test_user`@`%` SQL SECURITY DEFINER VIEW `test_view` AS select current_user() AS `current_user()`,user() AS `user()`
    character_set_client: utf8
    collation_connection: utf8_general_ci
    1 row in set (0,00 sec)
    mysql> select * from test_view;
    | current_user() | user()              |
    | test_user@%    | test_user@localhost |
    1 row in set (0,00 sec)

    As we can see, the view has been created with SQL SECURITY DEFINER and DEFINER=`test_user`@`%`. The view returns the value from two functions: current_user() returns the value that matches against the mysql.user table and that defines the privileges the current session or current execution context has. The function user() returns the host you are connected from and the username specified as connection identifier.

    Now we reconnect to the database using the privileged account from the first step, and requery the view:

    $ mysql -u root -p remove_test
    mysql> select * from test_view;
    | current_user() | user()         |
    | test_user@%    | root@localhost |
    1 row in set (0,00 sec)

    The view is executed by root@localhost but with the privileges of test_user@%. It is important to note that if you monitor the execution of a routine or view, using SHOW PROCESSLIST or query against information_schema.processlist, the contents of user and host are the same that current_user() return, not the ones returned by user().

    Now we will drop the user and query once again the view.

    mysql> drop user test_user@'%';
    Query OK, 0 rows affected (0,07 sec)
    mysql> select * from test_view;
    ERROR 1449 (HY000): The user specified as a definer ('test_user'@'%') does not exist

    In case you don’t see the error and still get the correct results, this is because the contents of the view are stored in the query cache. Clear their contents and repeat the query.

    mysql> reset query cache;
    Query OK, 0 rows affected (0,00 sec)

    How to validate if it is safe to remove a user? You must query the information_schema tables to find objects than can bring problems and recreate them.

    mysql> select routine_schema as db,
        ->        routine_type as object_type,
        ->        routine_name as object_name
        -> from routines
        -> where security_type='DEFINER'
        ->   and definer='test_user@%'
        -> union all
        -> select trigger_schema as db,
        ->        'TRIGGER' as object_type,
        ->         trigger_name as object_name
        -> from triggers
        -> where definer='test_user@%'
        -> union all
        -> select table_schema as db,
        ->        'VIEW' as object_type,
        -> table_name as object_name
        -> from views
        -> where security_type='DEFINER'
        ->   and definer='test_user@%';
    | db          | object_type | object_name |
    | remove_test | VIEW        | test_view   |
    1 row in set (0,02 sec)

    Replace test_user@% with the value of the user you want to remove.

    This way you get a list of the objects you must change to avoid problems. As I said before the best thing you can do to change the definer is recreating the objects. To obtain the creation script you should use SHOW CREATE VIEW, SHOW CREATE TRIGGER, SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION.

    If you want to avoid problems in the future, my recommendation is that for every application, create an account for that application, lock it with an unknown password or using account locking feature. Grant that account all the privileges needed to run the application and make sure all the views, procedures, functions and triggers have that account in the definer field.

    Categories: DBA Blogs

    Oracle AVG Function with Examples

    Complete IT Professional - Thu, 2016-05-12 06:00
    The Oracle AVG function is a common function in Oracle SQL. I’ll explain how to use it in this article and show you some examples. Purpose of the Oracle AVG Function The AVG function calculates the average of a series of values that you provide to it. Most of the time, this will be a […]
    Categories: Development

    technology behind DBaaS

    Pat Shuff - Thu, 2016-05-12 02:07
    Before we can analyze different use cases we need to first look at a couple of things that enable these use cases. The foundation for most of these use cases is data replication. We need to be able to replicate data from our on-premise database into a cloud database. The first issue is replicating data and the second is access rights to the data and database allowing you to pull the data into your cloud database.

    Let's first look at how data is stored in a database. If you use a Linux operating system, this is typically done by splitting information into four categories; ORACLE_HOME, +DATA, +FRA, and +RECO. The binaries that represent the database and all of the database processes go into the ORACLE_HOME or ORACLE_BASE. In the cloud this is dropped into /u01. If you are using non-rac the file system is a logical volume manager (LVM) where you stripe multiple disks to mirror or triple mirror data to keep a single disk failure from bringing down your database or data. If you are using a rac database this goes into ASM. ASM is a disk technology that manages replication and performance. There are a variety of books and websites written on this technology

    LVM links

    ASM links

    The reason why we go into storage technologies is that we need to know how to manage how and where data is stored in our DBaaS. If we access everything with IaaS and roll out raw compute and storage, we need to know how to scale up storage if we run out of space. With DBaaS this is done with the scale up menu item. We can grow the file system by adding logical units to our instance and grow the space allocated for data storage or data logging.

    The second file system that we should focus on is the +DATA area. This is where data is stored and all of our file extents and tables are located. For our Linux cloud database this is auto-provisioned into /u02. In our test system we create a 25 GB data area and get a 20G file system in the +DATA area.

    If we look at the /u02 file system we notice that there is one major directory /u02/app/oracle/oradata. In the oradata there is one directory associated with the ORACLE_SID. In our example we called it ORCL. In this directory we have the control01.dbf, sysaux01.dbf, system01.dbf, temp01.dbf, undotbs01.dbf, and users01.dbf. These files are the place where data is stored for the ORCL SID. There is also a PDB1 directory in this file structure. This correlates to the pluggable database that we called PDB1. The files in this directory correspond to the tables, system, and user information relating to this pluggable database. If we create a second pluggable a new directory is created and all of these files are created in that directory. The users01.dbf, PDB1_users01.pdf in the PDB1 directory, file defines all of the users and their access rights. The system01.dbf file defines the tables and system level structures. In a pluggable database the system01 file defines the structures for the PDB1 and not the entire database. The temp01.dbf holds temp data tables and scratch areas. The sysaux01.dbf contains the system information contains the control area structures and management information. The undotbs01.dbf is the flashback area so that we can look at information that was stored three days ago in a table. Note that there is no undotbs01.dbf file in the pluggable because this is done at a global area and not at the pluggable layer. Backups are done for the SID and not each PID. Tuning of memory and system tunables are done at the SID layer as well.

    Now that we have looked at the files corresponding to tables and table extents, we can talk about data replication. If you follow the methodology of EMC and NetApp you should be able to replicate the dbf files between two file systems. Products like SnapMirror allow you to block copy any changes that happen to the file to another file system in another data center. This is difficult to do between an on-premise server and cloud instance. The way that EMC and NetApp do this are in the controller layer. They log write changes to the disk, track what blocks get changed, and communicate the changes to the other controller on the target system. The target system takes these block changes, figures out what actual blocks they correspond to on their disk layout and update the blocks as needed. This does not work in a cloud storage instance. We deal on a file layer and not on a track and sector or bock layer. The fundamental problem with this data replication mechanism is that you must restart or ingest the new file into the database. The database server does not do well if files change under it because it tends to cache information in memory and indexes into data get broken if data is moved to another location. This type of replication is good if you have an hour or more recovery point objective. If you are looking at minutes replication you will need to go with something like DataGuard, GoldenGate, or Active DataGuard.

    DataGuard works similar to the block change recording but does so at the database layer and not the file system/block layer. When an update or insert command is executed in the database, these changes are written to the /u04 directory. In our example the +REDO area is allocated for 9.8 GB of disk. If we look at our /u04 structure we see /u04/app/oracle/redo contains redoXX.log file. With DataGuard we take these redo files, compress them, and transfer them to our target system. The target system takes the redo file, uncompresses it, and applies the changes to the database. You can structure the changes either as physical logging or logical logging. Physical logging allows you to translate everything in the database and records the block level changes. Logic logging takes the actual select statement and replicates it to the target system. The target system either inserts the physical changes into the file or executes the select statement on the target database. The physical system is used more than the logical replication because logical has limitations on some of the statements. For example, any blob or file operations can not translate to the target system because you can't guarantee that the file structure is the same between the two systems. There are a variety of books available on DataGuard. It is also important to note that DataGuard is not available for Standard Edition and Enterprise Edition but for High Performance Edition and Extreme Performance Edition only.

    • Oracle Data Guard 11g Handbook
    • Oracle Dataguard: Standby Database Failover Handbook
    • Creating a Physical Standby Documentation
    • Creating a Logical Standby Documentation

      Golden Gate is a similar process but there is an intermediary agent that takes the redo log, analyzes it, and translates it into the target system. This allows us to take data from an Oracle database and replicate it to SQL Server. It also allows us to go in the other direction. SQL Server, for example, is typically used for SCADA or process control systems. The Oracle database is typically used for analytics and heavy duty number crunching on a much larger scale. If we want to look at how our process control systems is operating in relation to our budget we will want to pull in the data for the process systems and look at how much we spend on each system. We can do this by either selecting data from the SQL Server or replicating the data into a table on the Oracle system. If we are doing complex join statements and pulling data in from multiple tables we would typically want to do this on one system rather than pulling the data across the network multiple times. Golden Gate allows us to pull the data into a local table and perform the complex select statements without having to suffer network latency more than the initial copy. Golden Gate is a separate product that you must pay for either on-premise or in the cloud. If you are replicating between two Oracle databases you could use Active DataGuard to make this work and this is available as part of Extreme Edition of the database.

      The /u03 area in our file system is where backups are placed. The file system for our sample system shows /u03/app/oracle/fast_recovery_area/ORCL. The ORCL is the ORACLE_SID of our installation. Note that there is no PDB1 area because all of the backup data is done at the system layer and not at the pluggable layer. The tool used to backup the database is RMAN. There are a variety of books available to help with RMAN as well as an RMAN online tutorial

      It is important to note that RMAN requires a system level access to the database. Amazon RDS does not allow you to replicate your data using RMAN but uses a volume snapshot and copies this to another zone. The impact of this is that first, you can not get your data out of Amazon with a backup and you can not copy your changes and data from the Amazon RDS to your on-premise system. The second impact is that you can't use Amazon RDS for DataGuard. You don't have sys access into the database which is required to setup DataGuard and you don't have access to a filesystem to copy the redo logs to drop into. To make this available with Amazon you need to deploy the Oracle database into EC2 with S3 storage as the back end. The same is true with Azure. Everything is deployed into raw compute and you have to install the Oracle database on top of the operating system. This is more of an IaaS play and not a PaaS play. You loose patching of the OS and database, automated backups, and automatic restart of the database if something fails. You also need to lay out the file system on your own and select LVM or some other clustering file system to prevent data loss from a single disk corruption. All of this is done for you with PaaS and DBaaS. Oracle does offer a manual process to perform backups without having to dive deep into RMAN technology. If you are making a change to your instance and want a backup copy before you make the change, you can backup your instance manually and not have to wait for the automated backup. You can also change the timing if 2am does not work for your backup and need to move it to 4am instead.

      We started this conversation talking about growing a table because we ran out of space. With the Amazon and Azure solutions, this must be done manually. You have to attach a new logical unit, map it into the file system, grow the file system, and potentially reboot the operating system. With the Oracle DBaaS we have the option of growing the file system either as a new logical unit, grow the /u02 file system to handle more table spaces, or grow the /u03 file system to handle more backup space.

      Once we finish our scale up the /u03 file system is no longer 20 GB but 1020 GB in size. The PaaS management console allocates the storage, attaches the storage to the instance, grows the logical volume to fill the additional space, and grows the file system to handle the additional storage. It is important to note that we did not require root privileges to do any of these operations. The DBA or cloud admin can scale up the database and expand table resources. We did not need to involve an operating system administrator. We did not need to request an additional logical unit from the storage admin. We did not need to get a senior DBA to reconfigure the system. All of this can be done either by a junior DBA or an automated script to grow the file system if we run out of space. The only thing missing for the automated script is a monitoring tool to recognize that we are running into a limit. The Oracle Enterprise Manager (OEM) 12c and 13c can do this monitoring and kick off processes if thresholds are crossed. It is important to note that you can not use OEM with Amazon RDS because you don't have root, file system, or system access to the installation which is required to install the OEM agent.

      In summary, we looked at the file system structure that is required to replicate data between two instances. We talked about how many people use third party disk replication technologies to "snap mirror" between two disk installations and talked about how this does not work when replicating from an on-premise to a cloud instance. We talked about DataGuard and GoldenGate replication to allow us to replicate data to the cloud and to our data center. We looked at some of the advantages of using DBaaS rather than database on IaaS to grow the file system and backup the database. Operations like backup, growing the file system, and adding or removing processors temporarily can be done by a cloud admin or junior DBA. These features required multiple people to make this happen in the past. All of these technologies are needed when we start talking about use cases. Most of the use cases assume that the data and data structures that exist in your on-premise database also exist in the cloud and that you can replicate data to the cloud as well as back from the cloud. If you are going to run a disaster recovery instance in the cloud, you need to be able to copy your changes to the cloud, make the cloud a primary instance, and replicate the changes back to your data center once you bring your database back online. The same is true for development and testing. It is important to be able to attach to both your on-premise database and database provisioned in the cloud and look at the differences between the two configurations.

    IBM Bluemix Dedicated/Local Status Page

    Pas Apicella - Wed, 2016-05-11 20:37
    With Bluemix Public you can view the status page which details all the runtimes and services and thier current status on all 3 PUBLIC regions. Those customers with Bluemix Dedicated or Local get a status page which includes a column on the status of thier Dedicated or Local instance.

    To navigate to it perform the following steps:

    1. Log into your Bluemix dedicated or local instance web console

    2. Click on the Status link which is accessed through the profile icon on the top right hand corner

    3. You will see a table as follows as well as status messages to indicate the current status of your own Bluemix Local or Dedicated Environment.

    More Information

    Categories: Fusion Middleware

    Maven: how to copy files after a build into several distribution directories

    XTended Oracle SQL - Wed, 2016-05-11 17:37

    Sometimes it is convenient to copy jar-files automatically after a build into several different directories, for example if you have different config files for local tests and for office test server, then you may want to copy these files into local test directory, internal office test server and public distribution directory.
    This short part of pom.xml contains 2 different methods to build UBER-JAR and copying them into 3 different directories: localtest, officetest and public

                                    <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                                    <echo>ANT TASK - copying files....</echo>
                                    <copy todir="${basedir}/distribution/localtest" overwrite="true" flatten="true">
                                        <fileset dir="${basedir}" includes="*.bat" >
                                            <include name="*.bat" />
                                            <include name="ReadME.*" />
                                            <include name="target/*.jar" />
                                    <copy todir="${basedir}/distribution/officetest" overwrite="true" flatten="true">
                                        <fileset dir="${basedir}" includes="*.bat" >
                                            <include name="*.bat" />
                                            <include name="ReadME.*" />
                                            <include name="target/*.jar" />
                                    <copy todir="${basedir}/distribution/public" overwrite="true" flatten="true">
                                        <fileset dir="${basedir}" includes="*.bat" >
                                            <include name="*.bat" />
                                            <include name="ReadME.*" />
                                            <include name="target/*.jar" />
    Categories: Development

    Log Buffer #473: A Carnival of the Vanities for DBAs

    Pythian Group - Wed, 2016-05-11 16:32

    This Log Buffer Edition covers some of the hottest blog posts of Oracle, SQL Server and MySQL for the previous week.


    A recent post on the OTN database forum reminded Jonathan how easy it is to forget to keep repeating a piece of information after the first couple of hundred times you’ve explained it. No matter how “intuitively obvious” it is for one person, it’s new to someone else.

    The OMS Patcher is a newer patching mechanism for the OMS specifically.

    A SQLT report has all kinds of pertinent information including—to name just a few—optimizer settings, indexes, statistics, plan history, and view definitions.

    Joins are fundamental in SQL, and are used in most every production query. There are three types in particular that every developer should fully understand.

    Why should Oracle Database professionals care about NoSQL and where to start?

    SQL Server:

    Understanding OPENJSON and FOR JSON in Azure and SQL 2016

    Using Data Compression in Master Data Services 2016

    The most frustrating thing with any new system is often just working out how to connect to it.

    There are several decisions to be made when designing indexes for Memory-optimized tables in In-Memory OLTP, and to make the best choice, it pays to understand something about the nature and usage of memory-optimised indexes.

    Database Hardware and Infrastructure Trends

    STRING_SPLIT() in SQL Server 2016 : Follow-Up #2


    Why would I run MySQL/MariaDB on POWER anyway?

    By default, the write() system call returns after all data has been copied from the user space file descriptor into the kernel space buffers. There is no guarantee that data has actually reached the physical storage.

    OpenCPS: Vietnam’s Public Sector goes Open Source

    MySQL uses metadata locking to manage concurrent access to database objects, and to ensure data consistency when performing modifications to the schema

    Using Docker for development is a great way of ensuring that what you develop will be the same that you deploy in production.

    Categories: DBA Blogs

    Defining Digital Transformation

    Pythian Group - Wed, 2016-05-11 15:33


    Terminology is important—and it’s particularly important for us to define terms that are central to what we do. So when it comes to the subject of digital transformation, what exactly are we talking about?


    In speaking with clients and industry thought leaders, I’ve come to realize that the term “digital transformation” has a different meaning to different people. For a term that is so widely used — and that, on its surface, seems pretty straightforward — the range of interpretation is remarkable. It’s a bit like when we say “I’ll do it later.”  “Later” to one person means “before the sun goes down today.” “Later” to another person means “sometime in the future”, and it could mean days or weeks in their mind. “Later” to a third person can mean “I have no plans to do it, and this is my way of telling you nicely.”


    Because the term is so essential to the work we do for our clients, I thought it would be helpful to define what digital transformation means to us here at Pythian. There’s so much we can say on the topic, so I plan to follow up with a series of articles about how I’ve seen it implemented, or worse, not implemented or even embraced as a concept.


    To start, “digital transformation” is about technology. I know that to some people it isn’t, but I disagree. These days, you can’t transform your business without technology. It’s not about which technology you choose, as much as it’s about how to use it. Even more specifically, we’ve found that the businesses that are achieving positive transformation are using technology to capitalize on data. I have yet to see a single transformation project that didn’t use data as a major component of its success.


    Let’s look at the term “transformation.” This equates to change, but it doesn’t mean change for its own sake. The change we’re talking about has to benefit the business. However, the factor that can make or break successful change is people. Their attitudes, preconceptions, and ideas almost always have to be aligned with the change for successful transformation to occur. People need to get behind the initiative, people have to fund it, people have to develop it, and people have to support it once it’s developed. And we all know that getting people to change can be more difficult than developing any new technology. In short, the transformative capabilities inherent in technology can only be realized when coupled with the willingness to embrace change.

    Why Digital Transformation?

    Why is the concept of digital transformation important in the first place? At Pythian, we believe that it’s about using technology and data to change your business for the better. What do we mean when we say “for the better”? Therein lies the controversy.  “For the better” means different things to different people depending on their company’s key objectives.


    “For the better” can mean:

    • Becoming more efficient to drive costs down so your profitability can improve
    • Reducing mistakes and improving your reputation, or the quality of your product
    • Differentiating your product to get ahead of the competition
    • Doing what you do, only faster than your competitors
    • Creating new revenue streams
    • Improving the customer experience. This is a big one, so I will dedicate an entire blog post to exploring exactly what it means.


    Digital transformation is the key to achieving any one, or all of these benefits, and knowing your objectives and priorities will help you shape your digital transformation initiative. So to start, focus less on what digital transformation is, and more on what you want the outcome of a transformation to be.


    Categories: DBA Blogs

    select from table with no direct relation or foriegn keys

    Learn DB Concepts with me... - Wed, 2016-05-11 12:50
      scott.emp E

        SELECT      grade
      scott.emp E

    Categories: DBA Blogs

    GoldenGate 12.2 Big Data Adapters: part 4 – HBASE

    Pythian Group - Wed, 2016-05-11 11:51

    This is the next post in my series about Oracle GoldenGate Big Data adapters. Here is list of all posts in the series:

    1. GoldenGate 12.2 Big Data Adapters: part 1 – HDFS
    2. GoldenGate 12.2 Big Data Adapters: part 2 – Flume
    3. GoldenGate 12.2 Big Data Adapters: part 3 – Kafka
    4. GoldenGate 12.2 Big Data Adapters: part 4 – HBASE

    In this post I am going to explore HBASE adapter for GoldenGate. Let’s start by recalling what we know about HBASE. The Apache HBASE is non-relational, distributed database. It has been modelled after the Google’s Bigtable distributed database. It can provide read write access to the data and is based on top of Hadoop or HDFS.

    So, what does it tell us? First, we can write and change the data. Second, we need to remember that it is non-relation database and it is a bit of a different approach to data in comparison with traditional relation databases. You can think about HBase as about a key-value store. We are not going deep inside HBASE architecture and internals here, since our main task is to test Oracle GoldenGate adapter and see how it works. Our configuration has an Oracle database as a source with a GoldenGate extract and target system where we have Oracle GoldenGate for BigData.

    We have more information about setting up the source and target in the first post in the series about HDFS adapter. The source side replication part has already been configured and started. We have initial trail file for data initialization and trails for the ongoing replication. We capture changes for all tables in the ggtest schema on the oracle database.
    Now we need to prepare our target site. Let’s start from HBase. I used a pseudo-distributed mode for my tests where I ran a fully-distributed mode on a single host. It is not acceptable for any production configuration but will suffice for our tests. On the same box I have HDFS to serve as a main storage. Oracle documentation for the adapter states that they support HBase from version 1.0.x . In my first attempt I tried to use HBase version 1.0.0 (Cloudera 5.6) but it didn’t work. I got errors in the GoldenGate and my extract was aborted.
    Here is the error :

    2016-03-29 11:51:31  ERROR   OGG-15051  Oracle GoldenGate Delivery, irhbase.prm:  Java or JNI exception:
    java.lang.NoSuchMethodError: org.apache.hadoop.hbase.HTableDescriptor.addFamily(Lorg/apache/hadoop/hbase/HColumnDescriptor;)Lorg/apache/hadoop/hbase/HTableDescriptor;.
    2016-03-29 11:51:31  ERROR   OGG-01668  Oracle GoldenGate Delivery, irhbase.prm:  PROCESS ABENDING.

    So, I installed another version HBase and the version 1.1.4 worked just fine. I used simple, standard HBase configuration for pseudo-distributed mode where region server was on the same host as master and hbase.rootdir point to local hdfs.
    Here is example of configuration:

    [root@sandbox conf]# cat regionservers
    [root@sandbox conf]#

    As soon as we have HBase setup and running we can switch our attention to GoldenGate instead. We have already a trail file with initial load. Now we need to prepare our configuration files for initial and ongoing replication. Let’s go to our GoldenGate for Big Data home directory and prepare everything. In first, we need a hbase.conf file copied from $OGG_HOME/AdapterExamples/big-data/hbase directory to $OGG_HOME/dirprm. I left everything as it used to be in the original file changing only gg.classpath parameter to point it to my configuration files and libs for HBase.
    Here is an example of the configuration files:

    [oracle@sandbox oggbd]$ cat dirprm/hbase.props
    javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=ggjava/ggjava.jar

    In second, we have to prepare a parameter file for our initial load. I used a simple file with minimum parameters.

    [oracle@sandbox oggbd]$ cat dirprm/irhbase.prm
    -- passive REPLICAT irhbase
    -- Trail file for this example is located in "./dirdat/initld" file
    -- Command to add REPLICAT
    -- run replicat irhbase:
    -- ./replicat paramfile dirprm/irhbase.prm reportfile dirrpt/irhbase.rpt
    EXTFILE /u01/oggbd/dirdat/initld
    TARGETDB LIBFILE libggjava.so SET property=dirprm/hbase.props

    Having that configuration file we can run the replicat in passive mode from command line and see the result.
    Here is initial status for HBASE:

    hbase(main):001:0> version
    1.1.4, r14c0e77956f9bb4c6edf0378474264843e4a82c3, Wed Mar 16 21:18:26 PDT 2016
    hbase(main):001:0> list
    0 row(s) in 0.3340 seconds
    => []

    Running the replicat:

    oracle@sandbox oggbd]$ ./replicat paramfile dirprm/irhbase.prm reportfile dirrpt/irhbase.rpt
    [oracle@sandbox oggbd]$

    Now we have 2 tables in HBASE:

    hbase(main):002:0> list
    2 row(s) in 0.3680 seconds

    Let’s have a look to the tables structure and contains:

    hbase(main):004:0> describe 'BDTEST:TEST_TAB_1'
    N_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION_SCOPE => '0'}
    1 row(s) in 0.2090 seconds
    hbase(main):005:0> scan 'BDTEST:TEST_TAB_1'
    ROW                                            COLUMN+CELL
     1                                             column=cf:ACC_DATE, timestamp=1459269153102, value=2014-01-22:12:14:30
     1                                             column=cf:PK_ID, timestamp=1459269153102, value=1
     1                                             column=cf:RND_STR, timestamp=1459269153102, value=371O62FX
     1                                             column=cf:RND_STR_1, timestamp=1459269153102, value=RJ68QYM5
     1                                             column=cf:USE_DATE, timestamp=1459269153102, value=2014-01-24:19:09:20
     2                                             column=cf:ACC_DATE, timestamp=1459269153102, value=2014-05-11:05:23:23
     2                                             column=cf:PK_ID, timestamp=1459269153102, value=2
     2                                             column=cf:RND_STR, timestamp=1459269153102, value=371O62FX
     2                                             column=cf:RND_STR_1, timestamp=1459269153102, value=HW82LI73
     2                                             column=cf:USE_DATE, timestamp=1459269153102, value=2014-01-24:19:09:20
     3                                             column=cf:ACC_DATE, timestamp=1459269153102, value=2014-01-22:12:14:30
     3                                             column=cf:PK_ID, timestamp=1459269153102, value=3
     3                                             column=cf:RND_STR, timestamp=1459269153102, value=RXZT5VUN
     3                                             column=cf:RND_STR_1, timestamp=1459269153102, value=RJ68QYM5
     3                                             column=cf:USE_DATE, timestamp=1459269153102, value=2013-09-04:23:32:56
     4                                             column=cf:ACC_DATE, timestamp=1459269153102, value=2014-05-11:05:23:23
     4                                             column=cf:PK_ID, timestamp=1459269153102, value=4
     4                                             column=cf:RND_STR, timestamp=1459269153102, value=RXZT5VUN
     4                                             column=cf:RND_STR_1, timestamp=1459269153102, value=HW82LI73
     4                                             column=cf:USE_DATE, timestamp=1459269153102, value=2013-09-04:23:32:56
    4 row(s) in 0.1630 seconds
    hbase(main):006:0> scan 'BDTEST:TEST_TAB_2'
    ROW                                            COLUMN+CELL
     7|IJWQRO7T|2013-07-07:08:13:52                column=cf:ACC_DATE, timestamp=1459269153132, value=2013-07-07:08:13:52
     7|IJWQRO7T|2013-07-07:08:13:52                column=cf:PK_ID, timestamp=1459269153132, value=7
     7|IJWQRO7T|2013-07-07:08:13:52                column=cf:RND_STR_1, timestamp=1459269153132, value=IJWQRO7T
    1 row(s) in 0.0390 seconds

    Everything looks good for me. We have structure and records as expected. Let’s go forward and setup ongoing replication.
    I have created a parameter file for my replicat using the the initial load parameters as a basis:

    [oracle@sandbox oggbd]$ cat dirprm/rhbase.prm
    REPLICAT rhbase
    -- Trail file for this example is located in "dirdat/or" directory
    -- Command to add REPLICAT
    -- add replicat rhbase, exttrail dirdat/or
    TARGETDB LIBFILE libggjava.so SET property=dirprm/hbase.props
    MAP ggtest.*, TARGET bdtest.*;

    We are checking our trail files and starting our replicat using the latest trail file. By default, a replicat would be looking for a trail with sequential number 0, but, since I have a purging policy on my GoldenGate it deletes old files and I need tell to replicat where to start exactly.

    [oracle@sandbox oggbd]$ ll dirdat/
    total 4940
    -rw-r-----. 1 oracle oinstall    3028 Feb 16 14:17 initld
    -rw-r-----. 1 oracle oinstall 2015199 Mar 24 13:07 or000043
    -rw-r-----. 1 oracle oinstall 2015229 Mar 24 13:08 or000044
    -rw-r-----. 1 oracle oinstall 1018490 Mar 24 13:09 or000045
    [oracle@sandbox oggbd]$ ggsci
    Oracle GoldenGate Command Interpreter
    Version OGGCORE_12.
    Linux, x64, 64bit (optimized), Generic on Nov 10 2015 16:18:12
    Operating system character set identified as UTF-8.
    Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
    GGSCI (sandbox.localdomain) 1> info all
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    GGSCI (sandbox.localdomain) 2> add replicat rhbase, exttrail dirdat/or,EXTSEQNO 45
    REPLICAT added.
    GGSCI (sandbox.localdomain) 3> start replicat rhbase
    Sending START request to MANAGER ...
    REPLICAT RHBASE starting
    GGSCI (sandbox.localdomain) 4> info all
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    REPLICAT    RUNNING     RHBASE      00:00:00      00:00:06
    GGSCI (sandbox.localdomain) 5> info rhbase
    REPLICAT   RHBASE    Last Started 2016-03-29 12:56   Status RUNNING
    Checkpoint Lag       00:00:00 (updated 00:00:08 ago)
    Process ID           27277
    Log Read Checkpoint  File dirdat/or000045
                         2016-03-24 13:09:02.000274  RBA 1018490
    GGSCI (sandbox.localdomain) 6>

    I inserted number of rows to test_tab_1 on oracle side and all of them were successfully replicated to HBASE.

    hbase(main):015:0> count 'BDTEST:TEST_TAB_1'
    Current count: 1000, row: 1005694
    Current count: 2000, row: 442
    Current count: 3000, row: 6333
    3473 row(s) in 1.0810 seconds
    => 3473

    Let’s have a look bit close to test_tab_1 and test_tab_2:

    hbase(main):005:0> scan 'BDTEST:TEST_TAB_1'
    ROW                                            COLUMN+CELL
     1                                             column=cf:ACC_DATE, timestamp=1459269153102, value=2014-01-22:12:14:30
     1                                             column=cf:PK_ID, timestamp=1459269153102, value=1
     1                                             column=cf:RND_STR, timestamp=1459269153102, value=371O62FX
     1                                             column=cf:RND_STR_1, timestamp=1459269153102, value=RJ68QYM5
     1                                             column=cf:USE_DATE, timestamp=1459269153102, value=2014-01-24:19:09:20
     2                                             column=cf:ACC_DATE, timestamp=1459269153102, value=2014-05-11:05:23:23
     2                                             column=cf:PK_ID, timestamp=1459269153102, value=2
     2                                             column=cf:RND_STR, timestamp=1459269153102, value=371O62FX
     2                                             column=cf:RND_STR_1, timestamp=1459269153102, value=HW82LI73
     2                                             column=cf:USE_DATE, timestamp=1459269153102, value=2014-01-24:19:09:20
    hbase(main):006:0> scan 'BDTEST:TEST_TAB_2'
    ROW                                            COLUMN+CELL
     7|IJWQRO7T|2013-07-07:08:13:52                column=cf:ACC_DATE, timestamp=1459269153132, value=2013-07-07:08:13:52
     7|IJWQRO7T|2013-07-07:08:13:52                column=cf:PK_ID, timestamp=1459269153132, value=7
     7|IJWQRO7T|2013-07-07:08:13:52                column=cf:RND_STR_1, timestamp=1459269153132, value=IJWQRO7T
    1 row(s) in 0.0390 seconds

    You can see that row identifier for test_tab_1 is value for pk_id and for test_tab_2 it is concatenation of all values for all columns. Why is it so? The difference is in constraints for the tables. Since we don’t have a primary key or unique index for test_tab_2 it uses all columns as a key value. We can try to add a constraint and see the result.

    select * from dba_constraints where owner='GGTEST' and table_name='TEST_TAB_2';
    no rows selected
    alter table ggtest.test_tab_2 add constraint pk_test_tab_2 primary key (pk_id);
    Table altered.
    insert into ggtest.test_tab_2 values(9,'PK_TEST',sysdate,null);
    1 row created.
    Commit complete.

    And let us comare with result on the HBASE:

    hbase(main):012:0> scan 'BDTEST:TEST_TAB_2'
    ROW                                           COLUMN+CELL
     7|IJWQRO7T|2013-07-07:08:13:52               column=cf:ACC_DATE, timestamp=1459275116849, value=2013-07-07:08:13:52
     7|IJWQRO7T|2013-07-07:08:13:52               column=cf:PK_ID, timestamp=1459275116849, value=7
     7|IJWQRO7T|2013-07-07:08:13:52               column=cf:RND_STR_1, timestamp=1459275116849, value=IJWQRO7T
     8|TEST_INS1|2016-03-29:15:14:37|TEST_ALTER   column=cf:ACC_DATE, timestamp=1459278884047, value=2016-03-29:15:14:37
     8|TEST_INS1|2016-03-29:15:14:37|TEST_ALTER   column=cf:PK_ID, timestamp=1459278884047, value=8
     8|TEST_INS1|2016-03-29:15:14:37|TEST_ALTER   column=cf:RND_STR_1, timestamp=1459278884047, value=TEST_INS1
     8|TEST_INS1|2016-03-29:15:14:37|TEST_ALTER   column=cf:TEST_COL, timestamp=1459278884047, value=TEST_ALTER
     9                                            column=cf:ACC_DATE, timestamp=1462473865704, value=2016-05-05:14:44:19
     9                                            column=cf:PK_ID, timestamp=1462473865704, value=9
     9                                            column=cf:RND_STR_1, timestamp=1462473865704, value=PK_TEST
     9                                            column=cf:TEST_COL, timestamp=1462473865704, value=NULL
    3 row(s) in 0.0550 seconds

    It is fully dynamic and changed row id column on the fly. Will it work with unique index? Yes it will :

    delete from ggtest.test_tab_2 where pk_id=9;
    1 row deleted.
    alter table ggtest.test_tab_2 drop constraint pk_test_tab_2;
    Table altered.
    create unique index ggtest.ux_test_tab_2 on ggtest.test_tab_2 (pk_id);
    Index created.
    insert into ggtest.test_tab_2 values(10,'UX_TEST',sysdate,null);
    1 row created.

    Here is the newly inserted row.

    hbase(main):017:0> scan 'BDTEST:TEST_TAB_2'
    ROW                                           COLUMN+CELL
     10                                           column=cf:ACC_DATE, timestamp=1462474389145, value=2016-05-05:14:53:03
     10                                           column=cf:PK_ID, timestamp=1462474389145, value=10
     10                                           column=cf:RND_STR_1, timestamp=1462474389145, value=UX_TEST
     10                                           column=cf:TEST_COL, timestamp=1462474389145, value=NULL
     7|IJWQRO7T|2013-07-07:08:13:52               column=cf:ACC_DATE, timestamp=1459275116849, value=2013-07-07:08:13:52
     7|IJWQRO7T|2013-07-07:08:13:52               column=cf:PK_ID, timestamp=1459275116849, value=7

    But it will not make any difference if we just create an index on the source. It will not change anything. So, if we need to identify a key for a table we have to have at least unique constraint. Of course it is just default behavior for a schema replication and we may use KEYCOLS to identify keys for some tables.

    Interesting that if we change a table structure it will affect all newly inserted rows but will not change existing even if we update some values. It works by this way if you have an unique identifier and it was not changed by your DDL operation.
    Here is an example. We have a column “TEST_COL” in the table test_tab_2. Let’s drop the column and update the row. Keep in mind that our primary key is column PK_ID and we are not modifying the key.

    alter table ggtest.test_tab_2 drop column TEST_COL;
    Table altered.
    update ggtest.test_tab_2 set rnd_str_1='TEST_COL' where pk_id=9;
    1 row updated.

    In HBASE we can see the same set of columns:

    hbase(main):030:0> scan 'BDTEST:TEST_TAB_2'
    ROW                                           COLUMN+CELL
     9                                            column=cf:ACC_DATE, timestamp=1462477581440, value=2016-05-05:15:46:13
     9                                            column=cf:PK_ID, timestamp=1462477794597, value=9
     9                                            column=cf:RND_STR_1, timestamp=1462477794597, value=TEST_COL
     9                                            column=cf:TEST_COL, timestamp=1462477581440, value=NULL
    1 row(s) in 0.0200 seconds

    We still have the deleted column TEST_COL even we’ve updated the row.
    But if we insert any new row it will have the new set of columns:

    insert into ggtest.test_tab_2 values(10,'TEST_COL',sysdate);
    1 row created.
    Commit complete.

    And in HBASE:

    hbase(main):031:0> scan 'BDTEST:TEST_TAB_2'
    ROW                                           COLUMN+CELL
     10                                           column=cf:ACC_DATE, timestamp=1462477860649, value=2016-05-05:15:50:55
     10                                           column=cf:PK_ID, timestamp=1462477860649, value=10
     10                                           column=cf:RND_STR_1, timestamp=1462477860649, value=TEST_COL
     9                                            column=cf:ACC_DATE, timestamp=1462477581440, value=2016-05-05:15:46:13
     9                                            column=cf:PK_ID, timestamp=1462477794597, value=9
     9                                            column=cf:RND_STR_1, timestamp=1462477794597, value=TEST_COL
     9                                            column=cf:TEST_COL, timestamp=1462477581440, value=NULL
    2 row(s) in 0.0340 seconds

    And, as for all other cases, truncate on source table is not going to be replicated to the target and the operation will be ignored. You have to truncate the table in HBASE by yourself to keep the data in sync. In case you insert data again the data in HBASE will be “updated”. But it will not delete other rows. It will be more like a “merge” operation.
    Here is an example:

    truncate table ggtest.test_tab_2;
    Table truncated.
    insert into ggtest.test_tab_2 values(10,'TEST_COL2',sysdate);
    1 row created.
    Commit complete.
    select * from ggtest.test_tab_2;
    ---------------- ---------- -----------------
    	      10 TEST_COL2  05/05/16 16:01:20
    hbase(main):033:0> scan 'BDTEST:TEST_TAB_2'
    ROW                                           COLUMN+CELL
     10                                           column=cf:ACC_DATE, timestamp=1462478485067, value=2016-05-05:16:01:20
     10                                           column=cf:PK_ID, timestamp=1462478485067, value=10
     10                                           column=cf:RND_STR_1, timestamp=1462478485067, value=TEST_COL2
     9                                            column=cf:ACC_DATE, timestamp=1462477581440, value=2016-05-05:15:46:13
     9                                            column=cf:PK_ID, timestamp=1462477794597, value=9
     9                                            column=cf:RND_STR_1, timestamp=1462477794597, value=TEST_COL
     9                                            column=cf:TEST_COL, timestamp=1462477581440, value=NULL
    2 row(s) in 0.0300 seconds

    I spent some time testing performance and found the main bottleneck was my Oracle source rather than GoldenGate and HBASE. I was able to sustain transaction rate up to 60 DML per second and my Oracle DB started to struggle to keep pace because of waiting for a commit. The HBASE and replicat were absolutely fine. I also checked how it handles big transactions and inserted about 2 billion rows by one transaction. It worked fine. Of course it doesn’t prove that any of your production configurations will be without any performance issues. To conduct real performance tests I need to use much bigger environment.
    In addition, I noticed one more minor error in Oracle documentation for adapter related to “keyValuePairDelimiter” parameter. In documentation it is replaced by “keyValueDelimiter”. It just small mistype and the “keyValueDelimiter” is repeated twice. First time it is correct and the second time it stands on the place where “keyValuePairDelimiter” is supposed to be. Here is the link.

    As a summary I can say that despite some minor issues the adapters and GoldenGate for Big Data showed quite mature status and readiness for real work. I think it is good robust technology and, hopefully, its development will continue improving it with new releases. I am looking forward to use it in a real production environment with significant workload. In following posts I will try to test different DDL operations and maybe some other datatypes. Stay tuned.

    Categories: DBA Blogs

    Oracle Management Cloud – Log Analytics

    Marco Gralike - Wed, 2016-05-11 10:18
    In this last overview post I will give you a first glance of the Oracle…

    run sql from windows cmd prompt CLI

    Learn DB Concepts with me... - Wed, 2016-05-11 09:36

    Method 1:

    set ORACLE_HOME=D:\app\oracle\product\11.2.0\db_1

    sqlplus -s "USERNAME/PASSWORD" @C:\Shell\Drop_objs.sql

    sqlplus -s "USERNAME/PASSWORD" @C:\Shell\Change_pass.sql

    Method 2:

    set ORACLE_HOME=D:\app\oracle\product\11.2.0\db_1
    (ECHO select username from dba_users where username in ('SCOTT');
    ECHO exit;) | sqlplus -s "USER/PASS" > C:\Shell\test_out.txt

    Categories: DBA Blogs

    Oracle Midlands : Event #15

    Tim Hall - Wed, 2016-05-11 08:42

    Don’t forget Oracle Midlands Event #15 next week!


    Please show your support and come along. It’s free thanks to the sponsorship by RedStackTech.



    Oracle Midlands : Event #15 was first posted on May 11, 2016 at 2:42 pm.
    ©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

    MobaXterm 9.0

    Tim Hall - Wed, 2016-05-11 08:21

    Another recent release that I managed to miss was


    Subscribe to Oracle FAQ aggregator