Feed aggregator

Healthcare Organizations Turn to Oracle ERP Cloud for Industry’s Most Complete, Modern, and Proven Solution

Oracle Press Releases - Fri, 2016-05-13 07:00
Press Release
Healthcare Organizations Turn to Oracle ERP Cloud for Industry’s Most Complete, Modern, and Proven Solution Oracle expands portfolio of healthcare customers with Adventist Health, Family Health, Presbyterian Medical Services, and Southern New Hampshire Health

Redwood Shores, Calif.—May 13, 2016

Oracle today announced that an increasing number of hospitals and healthcare systems worldwide are choosing Oracle Enterprise Resource Planning (ERP) Cloud to help increase productivity, lower costs, and improve controls. Adventist Health, Family Health, Presbyterian Medical Services, and Southern New Hampshire Health are just a few of the more than 1,800 organizations that have recently turned to Oracle ERP Cloud for a collaborative, efficient, and intuitive back-office hub with rich financial and operational capabilities to help reduce costs and modernize business practices to gain insight and productivity.

Healthcare organizations are under increasing pressure from regulatory agencies, consumers, employers, and governing boards to drive cost efficiency while maintaining quality patient care. Today’s healthcare systems are slow and expensive to maintain, especially when integrating a merged or acquired company. By standardizing on Oracle Modern Best Practice for Healthcare using embedded analytics, contextual social collaboration, and mobile technologies, healthcare organizations can achieve economies of scale faster to lower costs and access the innovative technologies to stay ahead of industry changes.

“Presbyterian Medical Services was looking for a complete cloud ERP solution that would help us drive efficiencies while maintaining quality patient care in a diverse, complex industry," said Chad Morris, senior ERP systems analyst, Presbyterian Medical Services. "In just 14 weeks, we were able to go live on Oracle ERP Cloud implementing a wide range of efficiencies with our 90+ locations across New Mexico. We are now in the process of going live on Oracle HCM Cloud."

“Hospitals and healthcare systems are contending with increased financial pressures resulting from rising costs coupled with reduced collections, an influx of data, and changing patient interactions due to high-deductible healthcare plans,” said Rod Johnson, senior vice president, Oracle. “We are committed to delivering innovative cloud solutions that help these healthcare organizations thrive and gain competitive advantage amid shifting industry conditions. Oracle ERP Cloud increases operational efficiencies and identifies growth areas to help healthcare organizations cost-effectively meet these challenges across their complex organizations and workforces.”

Built on a secure and scalable architecture, Oracle ERP Cloud provides customers with flexible alternatives via full or modular adoption, seamless integration across clouds, on-premise and third party applications, standardized best practices and rapid implementation templates, as well as extensive support for global companies in a wide variety of industries, including healthcare. Oracle ERP Cloud delivers complete ERP capabilities across financials, procurement, and project portfolio management, as well as Enterprise Performance Management (EPM), Governance Risk and Compliance (GRC) and Supply Chain Management (SCM). The portfolio includes deep global and industry-specific capabilities and is fully integrated with Oracle Human Capital Management (HCM) and Customer Relationship Management (CRM) solutions.

Contact Info
Nicole Maloney
Oracle
1.650.506.0806
nicole.maloney@oracle.com
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.

Trademarks

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

Nicole Maloney

  • 1.650.506.0806

OPN Webcast (VAR) Next Generation Oracle Database Appliance

MAY 2016 ...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Emoji? Look at the Oracle New Gen Developers

Christopher Jones - Fri, 2016-05-13 05:36

Forget emoji, look at the Oracle New Gen Developers!

South Korean Therapeutic Cancer Vaccine Company Improves Drug Safety Processes with Oracle Cloud Solution

Oracle Press Releases - Fri, 2016-05-13 02:24
Press Release
South Korean Therapeutic Cancer Vaccine Company Improves Drug Safety Processes with Oracle Cloud Solution GemVax & KAEL Group optimizes clinical development processes with Oracle Argus Cloud

Redwood Shores, Calif.—May 13, 2016

Patient safety is critical when pharmaceutical organizations develop new drug therapies and treatments. At the same, these pharmaceutical companies face pressures to deliver timely products to market and minimize costs. Oracle Health Sciences today announced that GemVax & KAEL Group, a novel therapeutic cancer vaccine company in South Korea, is using Oracle Argus Cloud to make faster, more informed drug safety decisions, while reducing the costs of outsourcing its business to a contract resource organization.

With the domestic launch of a new oncology drug and the upcoming global NDA approval for the drug, GemVax wanted to set up an in-house database to manage and have more control over both its new and existing cases. Previously, GemVax’s pharmacovigilance processes were managed through a global contract resource organization.

Oracle Argus Cloud enables GemVAX to improve its pharmacovigilance efficiency and effectiveness, while reducing the overall cost to manage its system in-house. It will also allow GemVax to create new insight into its safety data collection and case processing, and users can quickly drill down into case details.

“We are committed to developing new cancer therapies that harness the selective power of the immune system that seek out and destroy tumor cells,” said Michelle Kim, GemVax. “We rely on technologies like Oracle Argus Cloud to help ensure that we are making safety decisions, reduce risk and help ensure global compliance, so that we can focus on delivering therapies that will improve the quality of life of patients.”

Contact Info
Samantha Thai
Oracle
+1.510.917.5564
samantha.thai@oracle.com
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.

Trademarks

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

DBaaS for real this time

Pat Shuff - Fri, 2016-05-13 02:07
We have danced around creating a database in the Oracle Public Cloud for almost a week now. We have talked about Schema as a Service, Exadata as a Service, licensing, and the different versions of DBaaS. Today, let's tackle what it takes to actually create a database. It is important to note that the accounts that we are using are metered services accounts. We don't have the option to run as a non-metered service and have to provision the services on an hourly or monthly basis. Unfortunately, we are not going to go through the step by step process of creating a database. There are plenty of other sites that do this well

And my personal favorite

I personally like the Oracle by Example links. Most of the screen shots are out of date and look slightly different if you go through the steps now. For example, the Configure Backup and Recovery screen shots from the first link above shows local backup as an options. This option has been removed from the menu. My guess is a few months from now all of this will be removed and you will be asked for a container that will be automatically created for you rather than having to enter a container that was previously created as is done now. The critical steps that are needed to follow these examples are

  1. Get a trial cloud account - instructions on how to do this
  2. Log into your cloud account - Account documentation
  3. Navigate to the Database Cloud Service console
  4. Click the Create Instance button
  5. Define the Subscription type, billing type, software release, software edition
  6. Configure your instance with name, description, ssh public key, compute shape, backup mechanism and location, storage size, sys password, SID and PID, and optional configurations (like potentially DataGuard, RAC, and GoldenGate).
  7. Wait for instance to be provisioned
  8. Connect to the database via ssh using ssh private key and putty/ssh
  9. Optionally open up ports (port 1521 for client connect, port 80 for apex)
  10. Do something productive

The tutorials go through screen shots for all of these services. You can also watch this on youtube

Things to watch out for when you create a database instance in the Oracle Public Cloud
  1. If you configure a backup service on a demo system and increase the database size to anything of size, you will overflow the 500 GB of storage in about three weeks. Things will stop working when you try to create a service
  2. All ports are locked down with the exception of ssh. You can use an ssh tunnel to securely connect to localhost:1521 if you tunnel this port. If you are using a demo account you can only open port 1521 to the world. White listing and ip address lists are not supported in the demo accounts
  3. Play with SQL Developer connections across the internet. It works just like it does on-premise. The DBA tool has good management interfaces that allows you to do simple administration services from the tool
  4. Play with Enterprise Manager 13c. It is easy to connect to your database via ssh and add your cloud instance to the OEM console. You can manage it just like an on-premise database. Cloning a PDB to the cloud is trivial. Database backup to the cloud is trivial
  5. Play with unplugging and replugging a PDB in 12c. You can clone and unplug from your on-premise system, copy the xml files to the cloud, and plug in the PDB to create a clone in the cloud.
  6. The longer you let a database run, the smaller your credit will get. If you are playing with a sandbox you can stop a database. This will stop charging for the database (at $3-$5/hour) and you will only get charged for the compute and storage (at $0.10/hour). If you leave a database running for 24 hours you burn through $72-$120 based on your edition selection. You will burn through $3 in 24 hours if you turn off the database and restart it when you want to jump back into your sandbox. Your data will still be there. That is what you are paying $3 a day for.
  7. If you are using a demo system, you can extend your evaluation once or twice. There is a button at the top right allowing you to extend you evaluation period. Make sure you do this before time runs out. Once time runs out you need to request another account from another email address.
  8. If you are playing with an application, make sure that you spin up WebLogic or Tomcat in a Java or Compute instance in the same account. Running a application server on-premise and a database in the cloud will suffer from latency. You are shipping MB/GB across with select statement returns. You are shipping KB/MB to paint part of a screen. It is better to put the latency between the browser and the app server than the app server and the database server
  9. Request an account in Amazon and Azure. The more you play with DBaaS in the Oracle environment the more you will appreciate it. Things like creating a RAC cluster is simple. Linking a Java Service to a Database Service is simple. Running a load balancer in front of a Java Service is easy. Play with the differences between Iaas with a database and Paas DBaaS. There is a world of difference.
  10. If you run your demo long enough, look at the patch administration. It is worth looking at since this is a major differential between Oracle, Amazon, and Azure.

In summary, we didn't go through a tutorial on how to create a database as a service. At this point all of you should have looked at one or two tutorials, one or two videos, and one or two documentation pages. You should have a sample database to move forward with. It does not matter if it is Standard Edition, or Enterprise Edition, High Performance, or Extreme Performance. You should have a simple database that we can start to play with. The whole exercise should have taken you about an hour to learn and play and an hour to wait for the service to run to completion. Connect via ssh and run sqlplus as the oracle user. Open up port 1521 and download SQL Developer and connect to your cloud instance. Explore, play, and have fun experimenting. That is the primary reason why we give you a full database account and not a quarter of an account that you can't really do much with.

Links for 2016-05-12 [del.icio.us]

Categories: DBA Blogs

FREE Webinar: Efficient techniques to create and maintain your #Oracle Standby Database

The Oracle Instructor - Fri, 2016-05-13 01:36

Join us with this FREE event on May 19, 13:00 CET and register now, because seats are limited.

DataGuard_Lunchtime_Webinar

I will be talking about

  • how to create and maintain a 12c Standby Database in the most efficient way
  • how to do switchover and failover
  • how to keep up client connectivity after role changes

These topics will be live demonstrated – positively no slide show reading.

After this major part of the event, we will briefly advertise our digital learning offerings that relate to Oracle Database Core Technology and how you may take advantage of them.

Hope to see YOU in the session:-)


Categories: DBA Blogs

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
declare
*
ERROR at line 1:
ORA-00001: unique constraint (APEX_040200.WWV_FLOW_WORKSHEET_RPTS_UK)
violated
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;
OR
(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
      ,application_id
      ,application_name
      ,page_id
      ,application_user
      ,report_name
      ,report_alias
      ,status
from APEX_APPLICATION_PAGE_IR_RPT
where application_user not in ('APXWS_DEFAULT'
                              ,'APXWS_ALTERNATIVE');

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,
ss.plan_hash_value,
sn.END_INTERVAL_TIME,
ss.executions_delta,
ELAPSED_TIME_DELTA/(executions_delta*1000),
CPU_TIME_DELTA/(executions_delta*1000),
IOWAIT_DELTA/(executions_delta*1000),
CLWAIT_DELTA/(executions_delta*1000),
APWAIT_DELTA/(executions_delta*1000),
CCWAIT_DELTA/(executions_delta*1000),
BUFFER_GETS_DELTA/executions_delta,
DISK_READS_DELTA/executions_delta,
ROWS_PROCESSED_DELTA/executions_delta
from DBA_HIST_SQLSTAT ss,DBA_HIST_SNAPSHOT sn
where ss.sql_id = 'aktyyckj710a3'
and ss.snap_id=sn.snap_id
and executions_delta > 0
and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER
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 06.00.40.980 PM              195         1364.80228     609.183405    831.563728                  0                      0                      0          35211.9487             1622.4             6974.40513
      918231698 11-MAY-16 07.00.53.532 PM              129         555.981481     144.348698    441.670271                  0                      0                      0          8682.84496         646.984496             1810.51938
      918231698 11-MAY-16 08.00.05.513 PM               39         91.5794872     39.6675128    54.4575897                  0                      0                      0          3055.17949          63.025641             669.153846
      918231698 12-MAY-16 08.00.32.814 AM               35         178.688971     28.0369429    159.676629                  0                      0                      0          1464.28571              190.8             311.485714
      918231698 12-MAY-16 09.00.44.997 AM              124         649.370258     194.895944    486.875758                  0                      0                      0           13447.871         652.806452             2930.23387
      918231698 12-MAY-16 10.00.57.199 AM              168         2174.35909     622.905935    1659.14223                  0                      0             .001303571          38313.1548         2403.28571             8894.42857
      918231698 12-MAY-16 11.00.09.362 AM              213         3712.60403     1100.01973    2781.68793                  0                      0             .000690141          63878.1362               3951             15026.2066
      918231698 12-MAY-16 12.00.21.835 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 07.00.09.243 PM               41         39998.8871     1750.66015    38598.1108                  0                      0                      0          50694.1463         11518.0244             49379.4634
     2834425987 10-MAY-16 08.00.13.522 PM               33         44664.4329     1680.59361    43319.9765                  0                      0                      0          47090.4848         10999.1818             48132.4242
     2834425987 11-MAY-16 11.00.23.769 AM                8          169.75075      60.615125      111.1715                  0                      0                      0             417.375                 92                2763.25
     2834425987 11-MAY-16 12.00.27.950 PM               11         14730.9611     314.497455    14507.0803                  0                      0                      0          8456.63636         2175.63636             4914.90909
     2834425987 11-MAY-16 01.00.33.147 PM                2           1302.774       1301.794             0                  0                      0                      0               78040                  0                  49013
     2834425987 11-MAY-16 02.00.37.442 PM                1           1185.321       1187.813             0                  0                      0                      0               78040                  0                  49013
     2834425987 11-MAY-16 03.00.42.457 PM               14         69612.6197     2409.27829     67697.353                  0                      0                      0          45156.8571         11889.1429             45596.7143
     2834425987 11-MAY-16 04.00.47.326 PM               16         65485.9254     2232.40963    63739.7442                  0                      0                      0          38397.4375         12151.9375             52222.1875
     2834425987 12-MAY-16 08.00.36.402 AM               61         24361.6303     1445.50141    23088.6067                  0                      0                      0          47224.4426         5331.06557              47581.918
     2834425987 12-MAY-16 09.00.40.765 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
to_char(sb.LAST_CAPTURED,'YYYY-MM-DD HH24:MI:SS') DATE_TIME,
sb.NAME,
sb.VALUE_STRING 
from 
DBA_HIST_SQLBIND sb
where 
sb.sql_id='aktyyckj710a3' and
sb.WAS_CAPTURED='YES')
order by 
DATE_TIME,
NAME;

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',
NULL,'ADVANCED'));

This plan outputs an outline hint similar to this:

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$683B0107" 
      ...
      NO_ACCESS(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      UNNEST(@"SEL$2")
      OUTLINE_LEAF(@"SEL$5DA710D3")
      OUTLINE_LEAF(@"SEL$683B0107")
      ALL_ROWS
      OPT_PARAM('query_rewrite_enabled' 'false')
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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 
  SELECT 
    /*+
        BEGIN_OUTLINE_DATA
...
        END_OUTLINE_DATA
    */
  *
    FROM
...
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

@coe_xfr_sql_profile_aktyyckj710a3_1022624069.sql

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:

Linux

HP-UX db file sequential read graph:

HP

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.

Bobby

Postscript:

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:

END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms
------------------------- ---------------- ------------------
02.00.03.099 PM                        245         5341.99923 
03.00.15.282 PM                        250         1280.99632 
04.00.27.536 PM                        341         3976.65855 
05.00.39.887 PM                        125         2619.58894

Linux:

END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms
------------------------- ---------------- ------------------
16-MAY-16 09.00.35.436 AM              162         191.314809
16-MAY-16 10.00.38.835 AM              342         746.313994
16-MAY-16 11.00.42.366 AM              258         461.641705
16-MAY-16 12.00.46.043 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 11.00.54.676 PM           544681       515.978687
16-MAY-16 12.00.01.873 AM           828539       502.911935
16-MAY-16 01.00.06.780 AM           518322       1356.92377
16-MAY-16 02.00.10.272 AM            10698       637.953543
16-MAY-16 03.00.13.672 AM              193       628.170984
16-MAY-16 04.00.17.301 AM              112        1799.3125
16-MAY-16 05.00.20.927 AM             1680       318.792262
16-MAY-16 06.00.24.893 AM              140       688.914286
16-MAY-16 07.00.28.693 AM             4837       529.759768
16-MAY-16 08.00.32.242 AM            16082       591.632508
16-MAY-16 09.00.35.436 AM           280927       387.293204
16-MAY-16 10.00.38.835 AM           737846        519.94157
16-MAY-16 11.00.42.366 AM          1113762       428.772997
16-MAY-16 12.00.46.043 PM           562258       510.357372

Sweet!

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)

CREATE TABLE `t0` (
  `id` int(11) NOT NULL,
  `txt` varchar(100) CHARACTER SET ucs2 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t1` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `t0id` int(9) DEFAULT NULL,
  `txt` char(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `t0id` (`t0id`),
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`t0id`) REFERENCES `t0` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


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
Oracle
+1.510.917.5564
samantha.thai@oracle.com
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.

Trademarks

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
Oracle
+1.510.917.5564
samantha.thai@oracle.com
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.

Trademarks

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

    cube_instance
    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


    wftask
    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

    https://console.ng.bluemix.net/docs/admin/index.html#oc_status
    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

        <build>
            <plugins>
                <plugin>
                    <artifactId>maven-assembly-plugin</artifactId>
                    <version>2.6</version>
                    <configuration>
                        <archive>
                            <manifest>
                                <mainClass>tv.tmd.YourMainClass</mainClass>
                            </manifest>
                            <manifestEntries>
                                <Class-Path>.</Class-Path>
                            </manifestEntries>
                        </archive>
                        <descriptorRefs>
                            <descriptorRef>jar-with-dependencies</descriptorRef>
                        </descriptorRefs>
                    </configuration>
                    <executions>
                        <execution>
                            <id>make-assembly</id>
                            <phase>package</phase>
                            <goals>
                                <goal>single</goal>
                            </goals>
                        </execution>
                    </executions>
                </plugin>
    
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-shade-plugin</artifactId>
                    <version>2.4.3</version>
                    <executions>
                        <execution>
                            <phase>package</phase>
                            <goals>
                                <goal>shade</goal>
                            </goals>
                            <configuration>
                                <transformers>
                                    <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                                        <manifestEntries>
                                            <Main-Class>tv.tmd.YourMainClass</Main-Class>
                                            <Build-Number>2</Build-Number>
                                        </manifestEntries>
                                    </transformer>
                                </transformers>
                            </configuration>
                        </execution>
                    </executions>
                </plugin>
    
                <plugin>
                    <artifactId>maven-antrun-plugin</artifactId>
                    <version>1.8</version>
                    <executions>
                        <execution>
                            <id>copy</id>
                            <phase>package</phase>
                            <configuration>
                                <target>
                                    <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" />
                                        </fileset>
                                    </copy>
    
                                    <copy todir="${basedir}/distribution/officetest" overwrite="true" flatten="true">
                                        <fileset dir="${basedir}" includes="*.bat" >
                                            <include name="*.bat" />
                                            <include name="ReadME.*" />
                                            <include name="target/*.jar" />
                                        </fileset>
                                    </copy>
    
                                    <copy todir="${basedir}/distribution/public" overwrite="true" flatten="true">
                                        <fileset dir="${basedir}" includes="*.bat" >
                                            <include name="*.bat" />
                                            <include name="ReadME.*" />
                                            <include name="target/*.jar" />
                                        </fileset>
                                    </copy>
                                </target>
                            </configuration>
                            <goals>
                                <goal>run</goal>
                            </goals>
                        </execution>
                    </executions>
                </plugin>
            </plugins>
        </build>
    
    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.

    Oracle:

    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

    MySQL:

    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

    Pages

    Subscribe to Oracle FAQ aggregator