Feed aggregator

Video : JSON Support in Oracle Database 12c

Tim Hall - Sat, 2016-05-14 09:58

Today’s video is a sprint through some of the JSON support in Oracle Database 12c.

If videos aren’t your thing, you might want to read these instead.

The cameo in this video comes courtesy of Yves Colin, who I’ll see again in a couple of weeks at the Paris Province Oracle Meetup. A couple of extras (Bertrand Drouvot and Osama Mustafa) wanted to get in on the act too.

Email, where art thou?

Tim Hall - Sat, 2016-05-14 05:17

email-at-1020116_640Followers of the blog will know I’ve recently migrated the website to AWS. Yesterday I bit the bullet and cancelled my dedicated server.

As part of that process I had to move my email account from that service too. I always pull all my emails into Gmail, so there is no point paying for something cool. A little POP account is fine.

I started the process yesterday afternoon/evening, thinking it would be a quick drop on the old service and recreate on the new one. Unfortunately the old service held on to the domain reference overnight, so it was a quiet evening on the email front.

Fail Fast

Cary Millsap - Fri, 2016-05-13 17:22
Among movements like Agile, Lean Startup, and Design Thinking these days, you hear the term fail fast. The principle of failing fast is vital to efficiency, but I’ve seen project managers and business partners be offended or even agitated by the term fail fast. I’ve seen it come out like, “Why the hell would I want to fail fast?! I don’t want to fail at all.” The implication, of course: “Failing is for losers. If you’re planning to fail, then I don’t want you on my team.”

I think I can help explain why the principle of “fail fast” is so important, and maybe I can help you explain it, too.

Software developers know about fail fast already, whether they realize it or not. Yesterday was a prime example for me. It was a really long day. I didn’t leave my office until after 9pm, and then I turned my laptop back on as soon as I got home to work another three hours. I had been fighting a bug all afternoon. It was a program that ran about 90 seconds normally, but when I tried a code path that should have been much faster, I could let it run 50 times that long and it still wouldn’t finish.

At home, I ran it again and left it running while I watched the Thunder beat the Spurs, assuming the program would finish eventually, so I could see the log file (which we’re not flushing often enough, which is another problem). My MacBook Pro ran so hard that the fan compelled my son to ask me why my laptop was suddenly so loud. I was wishing the whole time, “I wish this thing would fail faster.” And there it is.

When you know your code is destined to fail, you want it to fail faster. Debugging is hard enough as it is, without your stupid code forcing you to wait an hour just to see your log file, so you might gain an idea of what you need to go fix. If I could fail faster, I could fix my problem earlier, get more work done, and ship my improvements sooner.

But how does that relate to wanting my business idea to fail faster? Well, imagine that a given business idea is in fact destined to fail. When would you rather find out? (a) In a week, before you invest millions of dollars and thousands of hours investing into the idea? Or (b) In a year, after you’ve invested millions of dollars and thousands of hours?

I’ll take option (a) a million times out of a million. It’s like asking if I’d like a crystal ball. Um, yes.

The operative principle here is “destined to fail.” When I’m fixing a reported bug, I know that once I create reproducible test case for that bug, my software will fail. It is destined to fail on that test case. So, of course, I want for my process of creating the reproducible test case, my software build process, and my program execution itself to all happen as fast as possible. Even better, I wish I had come up with the reproducible test case a year or two ago, so I wouldn’t be under so much pressure now. Because seeing the failure earlier—failing fast—will help me improve my product earlier.

But back to that business idea... Why would you want a business idea to fail fast? Why would you want it to fail at all? Well, of course, you don’t want it to fail, but it doesn’t matter what you want. What if it is destined to fail? It’s really important for you to know that. So how can you know?

Here’s a little trick I can teach you. Your business idea is destined to fail. It is. No matter how awesome your idea is, if you implement your current vision of some non-trivial business idea that will take you, say, a month or more to implement, not refining or evolving your original idea at all, your idea will fail. It will. Seriously. If your brain won’t permit you to conceive of this as a possibility, then your brain is actually increasing the probability that your idea will fail.

You need to figure out what will make your idea fail. If you can’t find it, then find smart people who can. Then, don’t fear it. Don’t try to pretend that it’s not there. Don’t work for a year on the easy parts of your idea, delaying the inevitable hard stuff, hoping and praying that the hard stuff will work its way out. Attack that hard stuff first. That takes courage, but you need to do it.

Find your worst bottleneck, and make it your highest priority. If you cannot solve your idea’s worst problem, then get a new idea. You’ll do yourself a favor by killing a bad idea before it kills you. If you solve your worst problem, then find the next one. Iterate. Shorter iterations are better. You’re done when you’ve proven that your idea actually works. In reality. And then, because life keeps moving, you have to keep iterating.

That’s what fail fast means. It’s about shortening your feedback loop. It’s about learning the most you can about the most important things you need to know, as soon as possible.

So, when I wish you fail fast, it’s a blessing; not a curse.

The April That Was and Our Plans for May and June

Oracle AppsLab - Fri, 2016-05-13 14:31

Hi there, remember me? Wow, April was a busy month for us, and looking ahead, it’s getting busy again.

Busy is good, and also good, is the emergence of new voices here at the ‘Lab. They’ve done a great job holding down the fort. Since my last post in late March, you’ve heard from Raymond (@yuhuaxie), Os (@vaini11a), Tawny (@iheartthannie), Ben (@goldenmean1618) and Mark (@mvilrokx).

Because it’s been a while, here comes an update post on what we’ve been doing, what we’re going to be doing in the near future, and some nuggets you might have missed.

What we’ve been doing

Conference season, like tax season in the US, consumes the Spring. April kicked off for me at Oracle HCM World in Chicago, where Aylin (@aylinuysal) and I had a great session. We showed a couple of our cool voice demos, powered by Noel’s (@noelportugalfavorite gadget, the Amazon Echo, and the audience was visibly impressed.

@jkuramot @theappslab #oaux wows @OracleHCM customers with emerging tech demos & HR tasks use cases #OracleHCMWorld pic.twitter.com/CwkzVeKdVJ

— Gozel Aamoth (@gozelaamoth) April 7, 2016

I like that picture. Looks like I’m wearing the Echo as a tie.

Collaborate 16 was next, where Ben and Tawny collected VR research and ran a focus group on bots. VR is still very much a niche technology. Many Collaborate attendees hadn’t even heard of VR at all and were eager to take the Samsung Gear VR for a test drive.

During the bots focus group, Ben and Tawny tried out some new methods, like Business Origami, which fostered some really interesting ideas among the group.

Business origami taking shape #oaux #CLV16 pic.twitter.com/PJARBrZGka

— The AppsLab (@theappslab) April 12, 2016

Next, Ben headed out directly for the annual Oracle Benelux User Group (OBUG) conference in Arnhem to do more VR research. Our research needs to include international participants, and Ben found more of the same reactions we’ve seen Stateside. With something as new and different as VR, we cast a wide net to get as many perspectives and collect as much data as possible before moving forward with the project.

Oracle Modern Customer Experience was next for us, where we showed several of our demos to a group students from the Lee Business School at UNLV (@lbsunlv), who then talked about those demos and a range of other topics in a panel session, hosted by Rebecca Wettemann (@rebeccawettemann) of Nucleus Research.

#UNLV #MBA students discuss the future of work with @theappslab @usableapps #ModernCX #SalesX16 pic.twitter.com/5tPl8Y6c95

— Geet (@geet_s) April 28, 2016

The feedback we got on our demos was very interesting. These students belong to a demographic we don’t typically get to hear from, and their commentary gave me some lightning bolts of insight that will be valuable to our work.

As with VR, some of the demos we showed were on devices they had not seen or used yet, and it’s always nice to see someone enjoy a device or demo that has become old hat to me.

Because we live and breathe emerging technologies, we tend to get jaded about new devices far too quickly. So, a reset is always welcome.

What we’re going to be doing in the near future

Next week, we’re back on the road to support an internal IoT hackathon that Laurie’s (@lsptahoeApps UX Innovation team (@InnovateOracle) is hosting in the Bay Area.

The countdown has started! Register at https://t.co/Gyx8d2Oh7k and be part of this huge Oracle Conference in June. pic.twitter.com/18fGbG9L74

— AMIS, Oracle & Java (@AMISnl) May 9, 2016

Then, June 2-3, we’re returning to the Netherlands to attend and support AMIS 25. The event celebrates the 25th anniversary of AMIS (@AMISnl), and they’ve decided to throw an awesome conference at what sounds like a sweet venue, “Hangaar 2” at the former military airport Valkenburg in Katwijk outside Amsterdam.

Our GVP, Jeremy Ashley (@jrwashley) will be speaking, as will Mark. Noel will be showing the Smart Office, Mark will be showing his Developer Experience (DX) tools, and Tawny will be conducting some VR research, all in the Experience Zone.

I’ve really enjoyed collaborating with AMIS in the past, and I’m very excited for this conference/celebration.

After a brief stint at home, we’re on the road again in late June for Kscope16, which is both an awesome conference and happily, the last show of the conference year. OpenWorld doesn’t count.

We have big fun plans this year, as always, so stay tuned for details.

Stuff you might have missed

Finally, here are some interesting tidbits I collected in my absence from blogging.

I love me some bio-hacking, so here’s an ingestible meat robot prototype and flexible electronic skin.

automateadvisediscover7

And here are some OAUX (@usableapps) links you should read:

Possibly Related Posts:

Oracle and Benefit Management LLC to Help Ease the Complexity of Bundled Payments Amidst Healthcare Reform

Oracle Press Releases - Fri, 2016-05-13 14:30
Press Release
Oracle and Benefit Management LLC to Help Ease the Complexity of Bundled Payments Amidst Healthcare Reform Oracle and Benefit Management LLC Enable Healthcare Payors and Providers to Shift from Retrospective Payments to Prospective Bundled Payments

Redwood Shores, Calif.—May 13, 2016

Oracle today announced that Benefit Management LLC, a progressive health Third Party Administrator (TPA) and joint venture of NueHealth, has selected Oracle Health Insurance Value Based Payment Cloud Service to deliver a cloud-based prospective bundled payments system for healthcare payors and providers.

While the goal of healthcare reform has been to decrease costs and improve patient outcomes, it has healthcare payors and providers grappling with how to master the complexity of bundled payment models—specifically the shift from retrospective payments (paying for healthcare services after they are completed) to prospective payments (at the time the service is requested and agreement on a flat fee). 

Benefit Management LLC will harness the power of Oracle Health Insurance Value Based Payments Cloud Service to help payors seamlessly maneuver and scale complex bundled payments, and help ensure timely and accurate payment to providers as they move to a prospective bundled payments model.

“The transition to value-based payments is one of the biggest challenges facing payors and providers today,” said Chad Somers, CEO of Benefit Management. “It is imperative that all aspects of value-based payments be considered before making this change. Never before has combining the clinical outcomes with payment been so important. Oracle Health Insurance Value Based Payment Cloud Service will allow us to revolutionize bundled payments by processing payment at the time service is rendered instead of months later.”

The combination of Benefit Management’s services, backed by 20 years of success with a diverse client base of self-funded employers, and Oracle’s offering will allow Benefit Management customers to:

  • Easily navigate the transition to prospective bundled payments
  • Reduce the provider risk making it easier for providers to participate
  • Decrease the total cost of bundled procedures
  • Increase the quality of care for bundled procedures

“The Oracle insurance cloud solution can also be leveraged by Benefit Management’s self-funded employers,” Somers said. “Value-based programs, especially when you’re talking about bundles, are of tremendous benefit to self-funded employers, and the capability to manage and adjudicate claims based on those kinds of value-based contracts will be something employers can take advantage of to great effect.”

“Oracle Health Insurance Value Based Payment Cloud Service provides the technology platform to administer many different non fee-for-service arrangements in a single solution,” said Srini Venkatasanthanam, Vice President for Oracle Insurance. “Oracle’s health insurance solution enables organizations such as Benefit Management to reduce time-to-market and significantly lower overall cost of ownership. We look forward to enabling Benefit Management to deliver an innovative prospective bundling offering to the market.”

To learn more about Oracle’s Value Based Payment Component and the opportunities it will provide to provider, payors and employers, visit Benefit Management online at www.BenefitManagementLLC.com

 
Contact Info
Valerie Beaudett
Oracle
+1.650.400.7833
valerie.beaudett@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.

About Benefit Management LLC

Benefit Management provides customized, high-quality health benefits administration programs to partially self-insured companies and association plans nationwide, and also delivers value-based administration solutions to providers and payors. The company, founded in 1995, has long been regarded as one of the Midwest’s leading third party administrators, with a reputation for flexibility, innovative services and outstanding customer service. Benefit Management is headquartered in Great Bend, Kan., with offices in Wichita, Kansas City and St. Louis, and administers insured lives in all 50 states.

About NueHealth

NueHealth is building a nationwide system of clinically integrated provider networks that puts healthcare into the hands of consumers. With a vast network of purpose-driven surgical centers and hospitals, NueHealth connects providers directly to consumers and aids them in delivering value-based payment options and improved outcomes. To deliver this improved value, NueHealth leverages proprietary technologies, online platforms, bundled payments and targeted programs and services. NueHealth gives providers and payors the tools and resources to stay ahead of healthcare’s continued evolution, while giving employers, insurance companies and patients access to a simplified model and affordable, high-quality, streamlined care.

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

Valerie Beaudett

  • +1.650.400.7833

JDeveloper 10g Certified on Windows 10 for EBS 12.1

Steven Chan - Fri, 2016-05-13 14:19

Jdeveloper logoJDeveloper 10g is now certified for Windows 10 desktops for Oracle E-Business Suite 12.1.  See:

When you create extensions to Oracle E-Business Suite OA Framework pages, you must use the version of Oracle JDeveloper shipped by the Oracle E-Business Suite product team.

The version of Oracle JDeveloper is specific to the Oracle E-Business Suite Applications Technology patch level, so there is a new version of Oracle JDeveloper with each new release of the Oracle E-Business Suite Applications Technology patchset.

This Note lists the JDeveloper with OA Extension updates for EBS 11i, 12.0, 12.1, and 12.2:

Pending Certification 

Our certification of JDeveloper 10g on Windows 10 for EBS 12.2 is still underway. 

Oracle's Revenue Recognition rules prohibit us from discussing certification and release dates, but you're welcome to monitor or subscribe to this blog. I'll post updates here as soon as soon as they're available.    

Related Articles

The preceding 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 decision.  The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

Categories: APPS Blogs

Win a FREE PASS to Oracle OpenWorld 2016: Oracle Cloud Platform Innovation Awards - Submit Nominations by June 20th 2016!

WebCenter Team - Fri, 2016-05-13 11:49


Calling all Oracle Cloud Platform Innovators

We invite you to Submit Nominations for the 2016
Oracle Excellence Awards:
Oracle Cloud Platform Innovation



Click here, to submit your nomination today!


Call for Nominations:
Oracle Cloud Platform Innovation 2016

Are you using Oracle Cloud Platform to deliver unique business value? If so, submit a nomination today for the 2016 Oracle Excellence Awards for Oracle Cloud Platform Innovation. These highly coveted awards honor customers and their partners for their cutting-edge solutions using Oracle Cloud Platform. Winners are selected based on the uniqueness of their business case, business benefits, level of impact relative to the size of the organization, complexity and magnitude of implementation, and the originality of architecture.


Customer Winners receive
a free pass to Oracle OpenWorld 2016 in San Francisco (September 18-September 22) and will be honored during a special event at OpenWorld.  

Our 2016 Award Categories are:

To be considered for this award, complete the online nomination forms and submit before June 20th, 2016. For any questions email: innovation-cloud-platform_ww_grp@oracle.com

NOTE: The deadline to submit all nominations is 5pm Pacific on June 20th, 2016. Customers don't have to be in production to submit a nomination and nominations are for both Cloud and on-premise solutions.

Analyze Index Validate Structure – The Dark Side

Pythian Group - Fri, 2016-05-13 09:38

Recently a co-worker wanted to discuss a problem he had encountered after upgrading a database.

The upgrade plan included steps to verify object integrity; this was being done with analyze table <tablename> validate structure cascade. All was fine until one particular table was being analyzed.  Suddenly it seemed the process entered a hung state.

The job was killed and separate commands were created to analyze each object individually.  That went well up until one of the last indexes was reached.

Me: How long has it been running?

Coworker: Three days.

Yes, you read that correctly, it had been running for three days.

My friend ran a 10046 trace to see what the process was doing; nearly all the work was ‘db file sequential read’ on the table.

At this time I suspected it was related to the clustering_factor for the index in question.  The analyze process for an index verifies each row in the index.  If the cluster is well ordered then the number of blocks read from the table will be similar to the number of blocks making up the table.

If however the table is not well ordered relative to the columns in the index the number of blocks read from the table can be many times the total number of blocks that are actually in the table.

Consider for a moment that we have rows with an  ID of 1,2,3,4 and 5.  Let’s assume that our index is created on the ID column.

If these rows are stored in order in the table, it is very likely these rows will all be in the same block, and that a single block read will fetch all of these rows.

If however the rows are stored in some random order, it may be that a separate block read is required for each lookup.

IDBlock Number122275316425104

In this case 5 separate blocks must be read to retrieve these rows.

In the course of walking the index, some  minutes later these rows must also be read:

IDBlock Number104857622104857775104857816104857921048580104

The blocks where these rows reside are the same blocks as the earlier example. The problem of course is that quite likely the blocks have been removed from cache by this time, and must be read again from disk.

Now imagine performing this for millions of rows. With a poor clustering factor the analyze command on an index could take quite some time to complete.

This seemed worthy of a test so we could get a better idea of just how bad this issue might be.

The test was run with 1E7 rows. The SQL shown below creates 1E7 rows, but you can simply change the value of level_2 to 1e3 to reduce the total rows to 1E6, or even smaller if you like.

 


-- keep this table small and the rows easily identifiable
-- or not...

-- 1e3 x 1e4 = 1e7
def level_1=1e3
def level_2=1e4

drop table validate_me purge;

create table validate_me
tablespace
   alloctest_a -- EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
   --alloctest_m -- EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL
   --alloctest_u -- EXTENT MANAGEMENT LOCAL UNIFORM SIZE 65536 SEGMENT SPACE MANAGEMENT AUTO
pctfree 0
as
select
   -- for a good clustering factor
   --id
   --
   -- for a bad clustering factor
   floor(dbms_random.value(1,1e6)) id
   , substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mod(id,10),15) search_data
   , to_char(id,'99') || '-' || rpad('x',100,'x') padded_data
from (
   select rownum id
   from
   (
      select null
      from dual
      connect by level <= &level_1
   ) a,
   (
      select null
      from dual
      connect by level <= &level_2
   ) b
)
/

create index validate_me_idx1 on validate_me(id,search_data);

exec dbms_stats.gather_table_stats(user,'VALIDATE_ME',method_opt => 'for all columns size 1')


 

Let’s see just what the clustering factor is for this index. The following script cluster-factor.sql will get this information for us.

 


col v_tablename new_value v_tablename noprint
col v_owner new_value v_owner noprint

col table_name format a20 head 'TABLE NAME'
col index_name format a20 head 'INDEX NAME'
col index_rows format 9,999,999,999 head 'INDEX ROWS'
col table_rows format 9,999,999,999 head 'TABLE ROWS'
col clustering_factor format 9,999,999,999 head 'CLUSTERING|FACTOR'
col leaf_blocks format 99,999,999 head 'LEAF|BLOCKS'
col table_blocks format 99,999,999 head 'TABLE|BLOCKS'


prompt
prompt Owner:
prompt

set term off feed off verify off
select upper('&1') v_owner from dual;
set term on feed on

prompt
prompt Table:
prompt

set term off feed off verify off
select upper('&2') v_tablename from dual;
set term on feed on


select
   t.table_name
   , t.num_rows table_rows
   , t.blocks table_blocks
   , i.index_name
   , t.num_rows index_rows
   , i.leaf_blocks
   , clustering_factor
from all_tables t
   join all_indexes i
      on i.table_owner = t.owner
      and i.table_name = t.table_name
where t.owner = '&v_owner'
   and t.table_name = '&v_tablename'

/

undef 1 2

 

Output from the script:

 

SQL> @cluster-factor jkstill validate_me

Owner:

Table:


                                          TABLE                                            LEAF     CLUSTERING
TABLE NAME               TABLE ROWS      BLOCKS INDEX NAME               INDEX ROWS      BLOCKS         FACTOR
-------------------- -------------- ----------- -------------------- -------------- ----------- --------------
VALIDATE_ME              10,000,000     164,587 VALIDATE_ME_IDX1         10,000,000      45,346     10,160,089

1 row selected.

Elapsed: 00:00:00.05

 

On my test system creating the table for 1E7 rows required about 2 minutes and 15 seconds, while creating the index took 28 seconds.

You may be surprised at just how long it takes to analyze that index.

 

SQL> analyze index jkstill.validate_me_idx1 validate structure online;

Index analyzed.

Elapsed: 00:46:06.49

 

Prior to executing this command a 10046 trace had been enabled, so there is a record of how Oracle spent its time on this command.

 

If you are wondering how much of the 46 minutes was consumed by the tracing and writing the trace file, it was about 6 minutes:

 

$>  grep "WAIT #48004569509552: nam='db file sequential read'"; oravm1_ora_2377_VALIDATE.trc  | awk '{ x=x+$8 } END { printf ("%3.2f\n",x/1000000/60) }'
  40.74

A Well Ordered Table

Now lets see how index analyze validate structure performs when the table is well ordered. The table uses the DDL as seen in the previous example, but rather than use dbms_random to generate the ID column, the table is created with the rows loaded in ID order.  This is done by uncommenting id in the DDL and commenting out the call to dbms_random.

 

SQL> analyze index jkstill.validate_me_idx1 validate structure online;

Index analyzed.

Elapsed: 00:01:40.53

That was a lot faster than previous.  1 minute and 40 seconds whereas previously the same command ran for 40 minutes.

 

Using some simple command line tools we can see how many times each block was visited.

 

First find the cursors and verify this cursor used only once in the session

$> grep -B1 '^analyze index' oravm1_ora_19987_VALIDATE.trc
PARSING IN CURSOR #47305432305952 len=64 dep=0 uid=90 oct=63 lid=90 tim=1462922977143796 hv=2128321230 ad='b69cfe10' sqlid='318avy9zdr6qf'
analyze index jkstill.validate_me_idx1 validate structure online


$> grep -nA1 'PARSING IN CURSOR #47305432305952' oravm1_ora_19987_VALIDATE.trc
63:PARSING IN CURSOR #47305432305952 len=64 dep=0 uid=90 oct=63 lid=90 tim=1462922977143796 hv=2128321230 ad='b69cfe10' sqlid='318avy9zdr6qf'
64-analyze index jkstill.validate_me_idx1 validate structure online
--
276105:PARSING IN CURSOR #47305432305952 len=55 dep=0 uid=90 oct=42 lid=90 tim=1462923077576482 hv=2217940283 ad='0' sqlid='06nvwn223659v'
276106-alter session set events '10046 trace name context off'

As this cursor was reused, we need to limit the lines we considered from the trace file.

 

One wait line appears like this:

WAIT #47305432305952: nam=’db file sequential read’ ela= 317 file#=8 block#=632358 blocks=1 obj#=335456 tim=1462923043050233

As it is already known the entire table resides in one file, it is not necessary to check the file.

From the following command it is clear that no block was read more than once during the analyze index validate structure when the table was well ordered in relation to the index.

 

$> tail -n +64 oravm1_ora_19987_VALIDATE.trc| head -n +$((276105-64)) | grep "WAIT #47305432305952: nam='db file sequential read'" | awk '{ print $10 }' | awk -F= '{ print $2 }' | sort | uniq -c | sort -n | tail
      1 742993
      1 742994
      1 742995
      1 742996
      1 742997
      1 742998
      1 742999
      1 743000
      1 743001
      1 743002

 

That command line may look a little daunting, but it is really not difficult when each bit is considered separately.

From the grep command that searched for cursors we know that the cursor we are interested in first appeared at line 64 in the trace file.

tail -n +64 oravm1_ora_19987_VALIDATE.trc

The cursor was reused at line 276105, so tell the tail command to output only the lines up to that point in the file.

head -n +$((276105-64))

The interesting information in this case is for ‘db file sequential read’ on the cursor of interest.

grep “WAIT #47305432305952: nam=’db file sequential read'”

Next awk is used to output the block=N portion of each line.

awk ‘{ print $10 }’

awk is again used, but this time to split the block=N output at the ‘=’ operator, and output only the block number.

awk -F= ‘{ print $2 }’

The cut command could have been used here as well. eg. cut -d= -f2

Sort the block numbers

sort

Use the uniq command to get a count of how many times each value appears in the output.

uniq -c

Use sort -n to sort the output from uniq.  If there are any counts greater than 1, they will appear at the end of the output.

sort -n

And pipe the output through tail. We only care if any block was read more than once.

tail

Now for the same procedure on the trace file generated from the poorly ordered table.

 


$> grep -B1 '^analyze index' oravm1_ora_2377_VALIDATE.trc
PARSING IN CURSOR #48004569509552 len=64 dep=0 uid=90 oct=63 lid=90 tim=1462547433220254 hv=2128321230 ad='aad620f0' sqlid='318avy9zdr6qf'
analyze index jkstill.validate_me_idx1 validate structure online

$> grep -nA1 'PARSING IN CURSOR #48004569509552' oravm1_ora_2377_VALIDATE.trc
51:PARSING IN CURSOR #48004569509552 len=64 dep=0 uid=90 oct=63 lid=90 tim=1462547433220254 hv=2128321230 ad='aad620f0' sqlid='318avy9zdr6qf'
52-analyze index jkstill.validate_me_idx1 validate structure online
--
6076836:PARSING IN CURSOR #48004569509552 len=55 dep=0 uid=90 oct=42 lid=90 tim=1462550199668869 hv=2217940283 ad='0' sqlid='06nvwn223659v'
6076837-alter session set events '10046 trace name context off'

 

The top 30 most active blocks were each read 53 or more times when the table was not well ordered in relation to the index.

$> tail -n +51 oravm1_ora_2377_VALIDATE.trc | head -n +$((6076836-51)) | grep "WAIT #48004569509552: nam='db file sequential read'" | awk '{ print $10 }' | awk -F= '{ print $2 }' | sort | uniq -c | sort -n | tail -30
     53 599927
     53 612399
     53 613340
     53 633506
     53 640409
     53 644099
     53 649054
     53 659198
     53 659620
     53 662600
     53 669176
     53 678119
     53 682177
     53 683409
     54 533294
     54 533624
     54 537977
     54 549041
     54 550178
     54 563206
     54 568045
     54 590132
     54 594809
     54 635330
     55 523616
     55 530064
     55 532693
     55 626066
     55 638284
     55 680250

 

Use RMAN

There is a feature of RMAN that allows checking for logical and physical corruption of an Oracle database via the  command backup check logical validate database.  This command does not actually create a backup, but just reads the database looking for corrupt blocks. Following is an (edited) execution of running this command on the same database where the analyze index commands were run.

A portion of the block corruption report is included.

RMAN> backup check logical validate database;
2>
Starting backup at 06-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 instance=oravm1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=+DATA/oravm/datafile/alloctest_a.273.789580415
input datafile file number=00009 name=+DATA/oravm/datafile/alloctest_u.272.789582305
input datafile file number=00024 name=+DATA/oravm/datafile/swingbench.375.821472595
input datafile file number=00023 name=+DATA/oravm/datafile/swingbench.374.821472577
input datafile file number=00019 name=+DATA/oravm/datafile/bh08.281.778786819
input datafile file number=00002 name=+DATA/oravm/datafile/sysaux.257.770316147
input datafile file number=00004 name=+DATA/oravm/datafile/users.259.770316149
input datafile file number=00001 name=+DATA/oravm/datafile/system.256.770316143
input datafile file number=00011 name=+DATA/oravm/datafile/alloctest_m.270.801310167
input datafile file number=00021 name=+DATA/oravm/datafile/ggs_data.317.820313833
input datafile file number=00006 name=+DATA/oravm/datafile/undotbs2.265.770316553
input datafile file number=00026 name=+DATA/oravm/datafile/undotbs1a.667.850134899
input datafile file number=00005 name=+DATA/oravm/datafile/example.264.770316313
input datafile file number=00014 name=+DATA/oravm/datafile/bh03.276.778786795
input datafile file number=00003 name=+DATA/oravm/datafile/rcat.258.861110361
input datafile file number=00012 name=+DATA/oravm/datafile/bh01.274.778786785
input datafile file number=00013 name=+DATA/oravm/datafile/bh02.275.778786791
input datafile file number=00022 name=+DATA/oravm/datafile/ccdata.379.821460707
input datafile file number=00007 name=+DATA/oravm/datafile/hdrtest.269.771846069
input datafile file number=00010 name=+DATA/oravm/datafile/users.271.790861829
input datafile file number=00015 name=+DATA/oravm/datafile/bh04.277.778786801
input datafile file number=00016 name=+DATA/oravm/datafile/bh05.278.778786805
input datafile file number=00017 name=+DATA/oravm/datafile/bh06.279.778786809
input datafile file number=00018 name=+DATA/oravm/datafile/bh07.280.778786815
input datafile file number=00020 name=+DATA/oravm/datafile/bh_legacy.282.778787059
input datafile file number=00025 name=+DATA/oravm/datafile/baseline_dat.681.821717827
input datafile file number=00027 name=+DATA/oravm/datafile/sqlt.668.867171675
input datafile file number=00028 name=+DATA/oravm/datafile/bh05.670.878914399

channel ORA_DISK_1: backup set complete, elapsed time: 00:25:27

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              75632        256074          375655477
  File Name: +DATA/oravm/datafile/system.256.770316143
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              158478
  Index      0              17160
  Other      0              4730

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              36332        394240          375655476
  File Name: +DATA/oravm/datafile/sysaux.257.770316147
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              170007
  Index      0              138603
  Other      0              49298

 

As shown in the report, only 25 minutes were required to check the entire database for physically or logically corrupt blocks, as opposed to the 40 minutes needed to analyze index validate structure.

While the RMAN corruption check is not the same as the check performed by analyze index validate structure, it is a test that can be completed in a much more timely manner, particularly if some indexes are both large and have a high value for the clustering factor.

Rebuild the Index?

If you have strong suspicions that a large index with an unfavorable clustering factor has corrupt blocks, it may be more expedient to just rebuild the index.  If the database is on Oracle Enterprise Edition, the rebuild can also be done with the ONLINE option.

Consider again the index on the test table with 1E7 rows.  Creating the index required 28 seconds, while validating the structure required 40 minutes.

 

 SQL> alter index validate_me_idx1 rebuild online;

Index altered.

Elapsed: 00:00:59.88


 

The conclusion is quite clear; the use of analyze index validate structure needs to be carefully considered when its use it contemplated for large indexes. The use of this command could be very resource intensive and take quite some time to complete. It is worthwhile to consider alternatives that my be much less resource intensive and time consuming.

Categories: DBA Blogs

MySQL encrypted streaming backups directly into AWS S3

Pythian Group - Fri, 2016-05-13 09:25
Overview

Cloud storage is becoming more and more popular for offsite storage and DR solutions for many businesses. This post will help with those people that want to perform this process for MySQL backups directly into Amazon S3 Storage. These steps can probably also be adapted for other processes that may not be MySQL oriented.

Steps

In order to perform this task we need to be able to stream the data, encrypt it, and then upload it to S3. There are a number of ways to do each step and I will try and dive into multiple examples so that way you can mix and match the solution to your desired results.  The AWS S3 CLI tools that I will be using to do the upload also allows encryption but to try and get these steps open for customization, I am going to do the encryption in the stream.

  1. Stream MySQL backup
  2. Encrypt the stream
  3. Upload the stream to AWS S3
Step 1 : Stream MySQL Backup

There are a number of ways to stream the MySQL backup. A lot of it depends on your method of backup. We can stream the mysqldump method or we can utilize the file level backup tool Percona Xtrabackup to stream the backup. Here are some examples of how these would be performed.

mysqldump

When using mysqldump it naturally streams the results. This is why we have to add the greater than sign to stream the data into our .sql file. Since mysqldump is already streaming the data we will pipe the results into our next step

[root@node1 ~]# mysqldump --all-databases > employee.sql

becomes

[root@node1 ~]# mysqldump --all-databases |
xtrabackup

xtrabackup will stream the backup but with a little more assistance to tell it to do so. You can reference Precona’s online documentation (https://www.percona.com/doc/percona-xtrabackup/2.4/innobackupex/streaming_backups_innobackupex.html) for all of the different ways to stream and compress the backups using xtrabackup. We will be using the stream to tar method.

innobackupex --stream=tar /root > /root/out.tar

becomes

innobackupex --stream=tar ./ |
Step 2 : Encrypt The Stream

Now that we have the backup process in place, we will then want to make sure that our data is secure. We will want to encrypt the data that we are going to be sending up to AWS S3 as to make sure the data is protected. We can accomplish this a couple of ways. The first tool I am going to look at is GnuPG (https://www.gnupg.org/), which is the open source version of PGP encryption. The second tool I will look at is another very popular tool OpenSSL (https://www.openssl.org/).  Below are examples of how I set them up and tested their execution with streaming.

GnuPG

I will be creating a public and private key pair with a password that will be used to encrypt and decrypt the data. If you are going to do this for your production and sensitive data, please ensure that your private key is safe and secure.  When creating the keypair I was asked to provide a password.  When decrypting the data I was then asked for the password again to complete the process. It was an interactive step and is not shown in the example below. To accept a stream, you don’t provide a file name to encrypt, then to stream the output, you just don’t provide an output parameter.

KEY PAIR CREATION
[root@node1 ~]# gpg --gen-key
gpg (GnuPG) 2.0.14; Copyright (C) 2009 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

Please select what kind of key you want:
   (1) RSA and RSA (default)
   (2) DSA and Elgamal
   (3) DSA (sign only)
   (4) RSA (sign only)
Your selection? 1
RSA keys may be between 1024 and 4096 bits long.
What keysize do you want? (2048)
Requested keysize is 2048 bits
Please specify how long the key should be valid.
         0 = key does not expire
      <n>  = key expires in n days
      <n>w = key expires in n weeks
      <n>m = key expires in n months
      <n>y = key expires in n years
Key is valid for? (0)
Key does not expire at all
Is this correct? (y/N) y

GnuPG needs to construct a user ID to identify your key.

Real name: root
Name must be at least 5 characters long
Real name: root@kmarkwardt
Email address: markwardt@pythian.com
Comment:
You selected this USER-ID:
    "root@kmarkwardt <markwardt@pythian.com>"

Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O
You need a Passphrase to protect your secret key.

can't connect to `/root/.gnupg/S.gpg-agent': No such file or directory
gpg-agent[1776]: directory `/root/.gnupg/private-keys-v1.d' created
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.

After typing for what felt like FOREVER, to generate enough entropy

gpg: /root/.gnupg/trustdb.gpg: trustdb created
gpg: key 1EFB61B1 marked as ultimately trusted
public and secret key created and signed.

gpg: checking the trustdb
gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model
gpg: depth: 0  valid:   1  signed:   0  trust: 0-, 0q, 0n, 0m, 0f, 1u
pub   2048R/1EFB61B1 2016-04-29
      Key fingerprint = 8D98 2D23 3C49 F1E7 9CD2  CD0F 7163 EB03 1EFB 61B1
uid                  root@kmarkwardt <markwardt@pythian.com>
sub   2048R/577322A0 2016-04-29

[root@node1 ~]#

 

SAMPLE USAGE
ENCRYPT
[root@node1 openssl]# echo "test" | gpg --output install.log.gpg --encrypt -r root 
[root@node1 openssl]# cat install.log.gpg
?
 ???    Ws"???l?
??g             ?w??g?C}P
   ?5A??f?6?p?
???Qq?m??&?rKE??*}5.?4XTj?????Th????}A???: ^V?/w?$???"?<'?;
?Y?|?W????v?R??a?8o<BG??!?R???f?u?????????e??????/?X?y?S7??H??@???Y?X~x>qoA0??L?????*???I?;I?l??]??Gs?G'?!??
                                                                                                            ??k>?
DECRYPT
[root@node1 ~]# gpg --decrypt -r root --output install.log.decrypted install.log.gpg
install.log.decrypted
You need a passphrase to unlock the secret key for
user: "root@kmarkwardt <markwardt@pythian.com>"
2048-bit RSA key, ID 577322A0, created 2016-04-29 (main key ID 1EFB61B1)

can't connect to `/root/.gnupg/S.gpg-agent': No such file or directory
gpg: encrypted with 2048-bit RSA key, ID 577322A0, created 2016-04-29
     "root@kmarkwardt <markwardt@pythian.com>"
[root@node1 ~]# ls
install.log.decrypted
install.log.gpg

ENCRYPT STREAM

[root@node1 ~]# mysqldump --all-databases | gpg --encrypt -r root 
or
[root@node1 ~]# innobackupex --stream=tar ./ | gpg --encrypt -r root 

 

OpenSSL

As with GPG we will generate a public and private key with a pass phrase.  There are other ways to use openssl to encrypt and decrypt the data such as just using a password with no keys, using just keys with no password, or encrypt with no password or keys.  I am using keys with a password as this is a very secure method.

KEY PAIR CREATION
[root@node1 openssl]# openssl req -newkey rsa:2048 -keyout privkey.pem -out req.pem
Generating a 2048 bit RSA private key
.......................................+++
........+++
writing new private key to 'privkey.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

[root@node1 openssl]# openssl x509 -req -in req.pem -signkey privkey.pem -out cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd
Getting Private key
Enter pass phrase for privkey.pem:
[root@node1 openssl]# ls -al
total 20
drwxr-xr-x  2 root root 4096 May  5 10:47 .
dr-xr-x---. 9 root root 4096 May  4 04:38 ..
-rw-r--r--  1 root root 1103 May  5 10:47 cert.pem
-rw-r--r--  1 root root 1834 May  5 10:43 privkey.pem
-rw-r--r--  1 root root  952 May  5 10:43 req.pem
[root@node1 openssl]# rm -rf req.pem 
SAMPLE USAGE
ENCRYPT
[root@node1 openssl]# echo "test" | openssl smime -encrypt -aes256 -binary -outform DER cert.pem > test.dat
[root@node1 openssl]# cat test.dat 
???0??1?k0?g0O0B1
                 0    UXX10U

                              Default City10U

?V??p?A$????PO??+???q@t??????\"%:0
??J?????5???0?D/?1z-?xO??&?#?;???E>^?g??#7??#m????lA???'??{)?*xM
P?l????]iz/???H???????[root@node1 openssl]#
DECRYPT
[root@node1 openssl]# openssl smime -decrypt -in test.dat -inform DER -inkey privkey.pem -out test.txt
Enter pass phrase for privkey.pem:
[root@node1 openssl]# cat test.txt 
test

ENCRYPT STREAM

[root@node1 ~]# mysqldump --all-databases | openssl smime -encrypt -aes256 -binary -outform DER cert.pem
or 
[root@node1 ~]# innobackupex --stream=tar ./ | openssl smime -encrypt -aes256 -binary -outform DER cert.pem
Step 3 : Stream to Amazon AWS S3

Now that we have secured the data, we will want to pipe the data into an Amazon AWS S3 bucket.  This will provide an offsite copy of the MySQL backup that you can convert to long term storage, or restore into an EC2 instance.  With this method I will only be looking at one.  The Amazon provided AWS CLI tools incorporates working with S3.  Allowing you to copy your files up into S3 with the ability to stream your input.

AWS CLI

In order to tell the AWS CLI S3 copy command to accept STDIN input you just have to put a dash in the place of the source file.  This will allow the command to accept a stream to copy.  The AWS CLI tools for copying into S3 also allows for encryption.  But I wanted to provide other methods as well to allow you to customize your own solution.   You can also stream the download of the S3 bucket item, which could allow for uncompression as you download the data or any other number of options.

UPLOAD STREAM

echo "test" | aws s3 cp - s3://pythian-test-bucket/incoming.txt 

BACKUP / ENCRYPT / UPLOAD STREAM

-- MySQL Dump -> OpenSSL Encryption -> AWS S3 Upload
[root@node1 ~]# mysqldump --all-databases | openssl smime -encrypt -aes256 -binary -outform DER cert.pem | aws s3 cp - s3://pythian-test-bucket/mysqldump.sql.dat
-- Xtrabackup -> OpenSSL Encryption -> AWS S3 Upload
[root@node1 ~]# innobackupex --stream=tar ./ | openssl smime -encrypt -aes256 -binary -outform DER cert.pem |aws s3 cp - s3://pythian-test-bucket/mysqldump.tar.dat
-- MySQL Dump -> GPG Encryption -> AWS S3 Upload
[root@node1 ~]# mysqldump --all-databases | gpg --encrypt -r root | aws s3 cp - s3://pythian-test-bucket/mysqldump.sql.gpg
-- MySQL Dump -> GPG Encryption -> AWS S3 Upload
[root@node1 ~]# innobackupex --stream=tar ./ | gpg --encrypt -r root | aws s3 cp - s3://pythian-test-bucket/mysqldump.tar.gpg

References

  • https://www.percona.com/doc/percona-xtrabackup/2.4/innobackupex/streaming_backups_innobackupex.html
  • https://linuxconfig.org/using-openssl-to-encrypt-messages-and-files-on-linux
  • https://www.gnupg.org/gph/en/manual/c14.html
  • https://www.gnupg.org/gph/en/manual/x110.html
  • https://linuxconfig.org/using-openssl-to-encrypt-messages-and-files-on-linux
  • https://www.openssl.org/docs/manmaster/apps/openssl.html
  • http://docs.aws.amazon.com/cli/latest/reference/s3/cp.html

 

 

Categories: DBA Blogs

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

Pages

Subscribe to Oracle FAQ aggregator