Feed aggregator

Webinar: NoSQL - Data Center Centric Application Enablement

Charles Lamb - Thu, 2013-08-01 09:36

NoSQL - Data Center Centric Application Enablement

AUGUST 6 WEBINAR

About the Webinar

The growth of Datacenter infrastructure is trending out of bounds, along with the pace in user activity and data generation in this digital era. However, the nature of the typical application deployment within the data center is changing to accommodate new business needs. Those changes introduce complexities in application deployment architecture and design, which cascade into requirements for a new generation of database technology (NoSQL) destined to ease that complexity. This webcast will discuss the modern data centers data centric application, the complexities that must be dealt with and common architectures found to describe and prescribe new data center aware services. Well look at the practical issues in implementation and overview current state of art in NoSQL database technology solving the problems of data center awareness in application development.

REGISTER NOW>>

MORE INFORMATION >>

NOTE! All attendees will be entered to win a guest pass to the NoSQL Now! 2013 Conference & Expo.

About the Speaker Robert Greene, Oracle NoSQL Product Management

Robert GreeneRobert Greene is a principle product manager / strategist for Oracle’s NoSQL Database technology. Prior to Oracle he was the V.P. Technology for a NoSQL Database company, Versant Corporation, where he set the strategy for alignment with Big Data technology trends resulting in the acquisition of the company by Actian Corp in 2012. Robert has been an active member of both commercial and open source initiatives in the NoSQL and Object Relational Mapping spaces for the past 18 years, developing software, leading project teams, authoring articles and presenting at major conferences on these topics. In his previous life, Robert was an electronic engineer developing first generation wireless, spread spectrum based security systems.

JAX-WS SOAP over JMS

Edwin Biemond - Thu, 2013-08-01 07:39
With WebLogic 12.1.2 Oracle now also supports JAX-WS SOAP over JMS. Before 12.1.2 we had to use JAX-RPC and without any JDeveloper support. We need to use ANT to generate all the web service code. See this blogpost for all the details. In this blogpost I will show you all the necessary JDeveloper steps to create a SOAP over JMS JAX-WS Web Service  ( Bottom up approach) and generate a Web

runInstaller java.lang.reflect.InvocationTargetException

Dave Best - Wed, 2013-07-31 12:52
Well this one stumped me for a little bit today: Not much information on Metalink or Google.    In the past one issue that would crop up every now and then was /tmp mounted with noexec.   I checked for that but it was ok... On a whim I decided to set my tmp dir to another location and it worked: TMP=/u01/tmp; export TMP Update: I recall why changing the TMP directory worked.    

Learn To ______ In A Year

Chet Justice - Tue, 2013-07-30 22:02
It started at The Talent Code blog by Daniel Coyle a few weeks back, What's Your LQ (Learning Quotient)?. That led me to Diamondbacks’ Goldschmidt Has Little Ego and Few Limits. I like baseball stories. I especially like this passage:

“A lot of kids have so much pride that they want to show the coaches and the front office that they know what they’re doing, and they don’t need the help,” Zinter said. “They don’t absorb the information because they want us to think they know it already. Goldy didn’t have an ego. He didn’t have that illusion of knowledge. He’s O.K. with wanting to learn.”

I identify with that. I believe part of my success is because I ask questions.

Back to the original article. Then I end up here, Can Everyone Be Smart at Everything? I seem to lack the ability to focus for extended periods of time. Well, not quite true. I have the ability to focus, but I like to focus on a million different things. Does that count? I don't know.

I'm often envious of my friends who have been DBAs for 20 years, or worked with OBIEE for 10 years (don't argue with me...I know Oracle hasn't owned it for 10 years, I'm looking at you Christian), or APEX for 10 years (that's safe to say). I've flirted with all of those, but I've never committed...See how I get distracted easily? Wow.

And just as importantly, that mistakes are part of good learning. As a Wired article recently reported about why some are more effective at learning from mistakes, “the important part is what happens next.” People with a “growth mindset” — those who “believe that we can get better at almost anything, provided we invest the necessary time and energy” — were significantly better at learning from their mistakes.

and then...

“The meaning of difficulty changes. Difficulty means trying harder, trying a different strategy. They understand that change is possible, and progress occurs over time.”


OMFG. Focus!

Back to the original article and I'm reading through the comments. Someone links up to this young lady who taught herself how to dance in a year. Watch it.



Which finally brings me back to The Talent Code, To Improve Faster, Think Like a Startup. Staying with me? How about this?



Finally, there's a point. I want to do this. Maybe not dance (as much fun as that may be), but something else. Krav Maga? Algebra? Calculus (I'm pursuing my physics or engineering degree in 2035, I need to study my math). I want to test out her technique. Small, discrete steps practiced daily towards some end goal (pass a calc test, take a real estate licensing test, whatever). The problem for me, if you haven't noticed, is focus. This method may help.

If you were to try something like this, what would you set out to learn?
Categories: BI & Warehousing

IPython %helloworld extension

Catherine Devlin - Tue, 2013-07-30 04:47

At Monday's after-PyOhio sprint, I changed ipython-sql from an IPython Plugin to an Extension; this makes it compatible with IPython 1.0. Fortunately, this was really easy; mostly I just deleted Plugin code I didn't understand anyway.

But I do feel like "Writing Extensions" docs are lacking a "Hello World" example. Here's mine.


from IPython.core.magic import Magics, magics_class, line_magic, cell_magic

@magics_class
class HelloWorldMagics(Magics):
"""A simple Hello, <name> magic.

"""

@line_magic # or ``@line_magic("hi")`` to make ``%hi`` the name of the magic
@cell_magic
def helloworld(self, line='', cell=None):
"""Virtually empty magic for demonstration purposes.

Example::

In [1]: %load_ext helloworld

In [2]: %helloworld Catherine
Out[2]: u'Hello, Catherine'


"""
return "Hello, %s\n%s" % (line, cell or "")

def load_ipython_extension(ip):
ip.register_magics(HelloWorldMagics)

PyOhio Stone Soup

Catherine Devlin - Tue, 2013-07-30 04:12

Loved PyOhio once again! Thanks so much to everybody who came, participated, and made it happen! I get such a rush of joy from seeing the Ohio Union fill up with happy Pythonistas.

PyOhio has been a classic case of the Stone Soup story. When we started planning the first one, we really didn't have the resources to pull off a conference; we were just a handful of PyCon 2008 attendees who wanted to bring something like PyCon home. But as we put it together, people appeared, pitched in, and we had a modest, amateurish - but fun! - little conference in the Columbus Public Library. PyOhio 2008 drew participants and volunteers who helped make PyOhio 2009 bigger and better; 2009 drew in more involvement for 2010; and so forth, year after year.

July 26-27, 2014. See you in Columbus!

WITH enhancements in 12c

Gary Myers - Mon, 2013-07-29 15:30
There's been some mentions of this feature on Oracle-base and elsewhere, but here's an example of what excites me.

If you deal with nested arrays/tables in SQL, then you quickly bump into an impedance match. You can't readily get that embedded list into horizontal columns.

I'll use the example I'm most familiar with - 2-D geometry data types.

There's a concept called the MBR (minimum bounding rectangle). You can basically think of it as the most northerly, southerly, easterly and westerly points of an area.

select a.mbr from LGA_2012_AUST a  where lga_name12 = 'Darwin (C)';

MBR(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, 8311, NULL, 
   SDO_ELEM_INFO_ARRAY(1, 1003, 3), 
   SDO_ORDINATE_ARRAY(130.815117, -12.469386, 130.938563, -12.33006))

The MBR is a geometry column with an ordinate array listing the four points.

We can do a Collection Unnesting operation, but then we end up with rows.

select b.*  from LGA_2012_AUST a, table(a.mbr.sdo_ordinates) b 
where lga_name12 = 'Darwin (C)';

COLUMN_VALUE
------------
  130.815117
  -12.469386
  130.938563
   -12.33006

To get back to columns, you'd need a PIVOT operation. But because we've lost any sense of the order of those values in the array, that has limitations. In Australia we can easily tell the difference between our latitudes and longitudes, but that isn't possible for many locations.

The WITH operator allows us to extract those array elements easily, and without recourse to a stored function. Though the PRAGMA UDF mention by Tim may mean I don't need to fret about the context switches of switching between SQL and PL/SQL as much.

SQL> l
  1  WITH
  2  FUNCTION ext_val (i_arr in MDSYS.SDO_ORDINATE_ARRAY, i_val in number) RETURN NUMBER IS
  3  BEGIN
  4    return i_arr(i_val);
  5  END;
  6  select ext_val(a.mbr.SDO_ORDINATES,1) v1, ext_val(a.mbr.SDO_ORDINATES,2) v2,
  7         ext_val(a.mbr.SDO_ORDINATES,3) v3, ext_val(a.mbr.SDO_ORDINATES,4) v4
  8  from LGA_2012_AUST a
  9* where lga_name12 = 'Darwin (C)'
SQL> /

        V1         V2         V3         V4
---------- ---------- ---------- ----------
130.815117 -12.469386 130.938563  -12.33006

My demo in SQL*Plus works fine in version 12c.
In the 11.2 Instant Client, I could get it to run using
set sqlterminator #

That stops it treating the ";" in line 4 as a terminator, and allows it to pull in the whole statement. The backslash will send it off to the DB for processing, and it works fine then

SQL Developer 4 also seems to choke on the syntax. I'll have to work up the strength to see if it is already logged as an issue. I assume it won't work in 3.2, and that a 12c rollout will require SQL*Plus and SQL Developer installs to be upgraded. Not sure about TOAD and any other clients.

New interesting feature of Oracle 12c

Slavik Markovich - Mon, 2013-07-29 13:00
This looks like an interesting feature of Oracle 12c. I’m still not sure about the security implications but it does say interesting things about pure network monitoring security tools. Now, more than ever, what you see on the network can be something completely different than what runs on the database. So, you can see a […]

MorphoTrak: "Storing billions of images in a hybrid relational and NoSQL database using Oracle Active Data Guard and Oracle NoSQL Database"Database

Charles Lamb - Sun, 2013-07-28 07:37
Aris Prassinos of MorphoTrak posted a slide set entitled "Storing billions of images in a hybrid relational and NoSQL database using Oracle Active Data Guard and Oracle NoSQL". In it, he details how they migrated their application from being one that was implemented completely upon Oracle Server, to one that uses Oracle Server + NoSQL.

Are older releases of the database really unsupported?

OraFAQ Articles - Sun, 2013-07-28 05:19

I see posts on Oracle related forums about various releases (anything that isn't 11.x or 12.x) being "unsupported". This is wrong. Of course you should upgrade any 9i or 10g databases, but you don't have to.

Oracle Corporation's lifetime support policy is documented here,
Lifetime Support Policy
take a look, and you'll see that release 10.2 was in premier support until end July 2010 when it went into extended support. At end July 2013, it goes into sustaining support. Sustaining support will continue indefinitely. Even release 8.1.7 will have sustaining support indefinitely.
So what is sustaining support? That is documented here,
Lifetime support benefits
To summarize, extended support gives you everything you are likely to need. What you do not get is certification against new Oracle products or new third party products (principally, operating systems). But does that matter? I don't think so. For example, release 11.2.0.3 (still in premier support) is not certified against Windows 8, but it works fine.
Sustaining support has a more significant problem: no more patches. Not even patches for security holes, or changes in regulatory requirements. The security patch issue may of course be serious, but regulatory issues are unlikely to matter (this is a database, not a tax management system.) Think about it: 10g has been around for many years. It is pretty well de-bugged by now. If you hit a problem with no work around, you are pretty unlucky. Sustaining support gives you access to technical support, available patches, software, and documentation. That is all most sites will ever need.
Right now, I am working on a 9.2.0.8 database. It cannot be upgraded because the application software is written by a company that does not permit a database upgrade. Why not? Well, the reason may be commercial: they have a replacement product that is supported on newer databases. But that is nothing to do with me. The database works, the software works. Making it work better is a challenge - but that is what a DBA is paid to do. Don't just write it off as "unsupported".
Of course I am not suggesting that users should not upgrade to current releases - but upgrades are a huge project, and can have major implications. Running out dated software is silly, unless you have an irrefutable reason for so doing. The lack of security patches make you vulnerable to data loss. The lack of regulatory patches may make it illegal. The lack of newer facilities will be restricting the utility of the system. You may be losing money by not taking of advantage of changes of newer technology that can better exploit your hardware.
If anyone is looking for consulting support to upgrade their database - my boss will be happy to give you a quote. But I won't refuse to support you in the meantime.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com

articles: 

12C: IN DATABASE ARCHIVING

OraFAQ Articles - Sun, 2013-07-28 03:42

In this post, I will demonstrate a new feature introduced in 12c : In database archiving. It enables you to archive rows within a table by marking them as invisible. This is accomplshed  by means of a hidden column ORA_ARCHIVE_STATE. These invisible rows are not visible to the queries but if needed, can be viewed , by setting a session parameter ROW ARCHIVAL VISIBILITY.

Overview:

-- Create test user uilm, tablespace ilmtbs
-- Connect as user uilm
-- create and populate test table (5 rows) ilmtab with row archival clause
-- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created   and has the default value of 0 (indicates that row is active)
-- Note that this column is not visible when we describe the table or simply issue select * from ...
-- We need to access data dictionary to view the column
-- Make two  rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value.
-- Check that inactive rows are not visible to query
-- Set the parameter ROW ARCHIVAL VISIBILITY  = all to see inactive rows also
-- Set the parameter ROW ARCHIVAL VISIBILITY  = active to hide inactive rows
-- Issue an insert into ... select * and check that only 3 visible rows are inserted
-- Set the parameter ROW ARCHIVAL VISIBILITY  = all to see inactive rows also
-- Issue an insert into ... select * and check that all the rows are inserted but ORA_ARCHIVE_STATE    is not propagated in inserted rows
-- Disable row archiving in the table and check that column ORA_ARCHIVE_STATE is automatically dropped
-- drop tablespace ilmtbs and user uilm

Implementation :

-- Create test user, tablespace and test table
SQL> conn sys/oracle@em12c:1523/pdb1 as sysdba
sho con_name

CON_NAME
------------------------------
PDB1

SQL> set sqlprompt PDB1>

PDB1>create tablespace ilmtbs datafile '/u02/app/oracle/oradata/cdb1/pdb1/ilmtbs01.dbf' size 1m;
grant connect, resource, dba  to uilm identified by oracle;
alter user uilm default tablespace ilmtbs;

conn uilm/oracle@em12c:1523/pdb1
sho con_name

CON_NAME
------------------------------
PDB1
-- create table with "row archival clause"
PDB1>drop table ilmtab purge;
create table ilmtab (id number, txt char(15)) row archival;
insert into ilmtab values (1, 'one');
insert into ilmtab values (2, 'two');
insert into ilmtab values (3, 'three');
insert into ilmtab values (4, 'four');
insert into ilmtab values (5, 'five');
commit;
-- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created    and has the default value of 0 (indicates that row is active)
PDB1>col ora_archive_state for a20
select id, txt, ora_archive_state from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             0
2 two             0
3 three           0
4 four            0
5 five            0
-- Note that this column is not visible when we describe the table or simply issue select * from ...
PDB1>desc ilmtab
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
TXT                                                CHAR(15)

PDB1>select * from ilmtab;

ID TXT
---------- ---------------
1 one
2 two
3 three
4 four
5 five
-- Since the column is invisible, let me try and make it visible
-- Note that Since the column is maintained by oracle itself, user can't modify its attributes
PDB1>alter table ilmtab modify (ora_archive_state visible);
alter table ilmtab modify (ora_archive_state visible)
*
ERROR at line 1:
ORA-38398: DDL not allowed on the system ILM column
-- We need to access data dictionary to view the column
-- Note that this column is shown as hidden and has not been generated by user
PDB1>col hidden for a7
col USER_GENERATED for 20
col USER_GENERATED for a20

select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='ILMTAB';

TABLE_NAME  COLUMN_NAME          HID USER_GENERATED
----------- -------------------- --- --------------------
ILMTAB      ORA_ARCHIVE_STATE    YES NO
ILMTAB      ID                   NO  YES
ILMTAB      TXT                  NO  YES
-- We can make selected rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value.
This can be accomplished using update table... set ORA_ACRHIVE_STATE =
. <non-zero value>
. dbms_ilm.archivestatename(1)

-- Let's update row with id =1 with ORA_ARCHIVE_STATE=2
     and update row with id =2 with dbms_ilm.archivestatename(2)
PDB1>update ilmtab set ora_archive_state=2 where id=1;

update ilmtab set ora_archive_state= dbms_ilm.archivestatename(2) where id=2;
-- Let's check whether updates have been successful and hidden rows are not visible
PDB1>select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three           0
4 four            0
5 five            0
-- The updated rows are not visible!!
-- Quite logical since we have made the rows active and by default only active rows are visible

-- To see inactive rows also, we need to set the parameter ROW ARCHIVAL VISIBILITY  = all at session level
-- Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using
dbms_ilm.archivestatename(2)
PDB1>alter session set ROW ARCHIVAL VISIBILITY  = all;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             2
2 two             1
3 three           0
4 four            0
5 five            0
-- Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using    dbms_ilm.archivestatename(2)

-- Let's find out why
-- Note that The function dbms_ilm.archivestatename(n) returns only two values    0 for n=0 and 1 for  n <> 0
PDB1>col state0 for a8
col state1 for a8
col state2 for a8
col state3 for a8

select dbms_ilm.archivestatename(0) state0 ,dbms_ilm.archivestatename(1) state1,
dbms_ilm.archivestatename(2) state2,dbms_ilm.archivestatename(3) state3  from dual;

STATE0   STATE1   STATE2   STATE3
-------- -------- -------- --------
0        1        1        1
-- In order to make the inactive rows (id=1,2) hidden again, we need to set the parameter ROW ARCHIVAL VISIBILITY  = Active
PDB1>alter session set row archival visibility = active;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three           0
4 four            0
5 five            0
-- Let's issue an insert into ... select *
-- Note that only 3 new rows are visible
PDB1>insert into ilmtab select * from ilmtab;

select id, txt, ora_archive_state from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three           0
4 four            0
5 five            0
3 three           0
4 four            0
5 five            0

6 rows selected.
-- I want to check if hidden rows were also inserted
-- Let's check by making  hidden rows visible again
-- Note that only visible rows(id=3,4,5) were inserted
PDB1>alter session set row archival visibility=all;
select id, txt, ora_archive_state from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             2
2 two             1
3 three           0
4 four            0
5 five            0
3 three           0
4 four            0
5 five            0

8 rows selected.
-- Let's set row archival visibility = all and then again insert rows from ilmtab
-- Note that all the 8 rows are inserted but ORA_ARCHIVE_STATE ha not been copied    ORA_ARCHIVE_STATE <> 0 in only 2 records (id = 1,2) even now.
PDB1>alter session set row archival visibility=all;
insert into ilmtab select * from ilmtab;
select id, txt, ora_archive_state from ilmtab order by id;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             0
1 one             2
2 two             0
2 two             1
3 three           0
3 three           0
3 three           0
3 three           0
4 four            0
4 four            0
4 four            0
4 four            0
5 five            0
5 five            0
5 five            0
5 five            0

16 rows selected.
-- Disable row level archiving for the table
-- Note that as soon as row archiving is disabled, pseudo column ora_archive_state is dropped automatically
PDB1>alter table ilmtab no row archival;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier

PDB1>col hidden for a7
col USER_GENERATED for 20
col USER_GENERATED for a20

select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='ILMTAB';

TABLE_NAME  COLUMN_NAME          HID USER_GENERATED
----------- -------------------- --- --------------------
ILMTAB      ID                   NO  YES
ILMTAB      TXT                  NO  YES
Note : Had we created this table using sys, we could not have disabled row archiving .

-- cleanup --
PDB1>conn sys/oracle@em12c:1523/pdb1 as sysdba
drop tablespace ilmtbs including contents and datafiles;
drop user uilm cascade;
References:

http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_lifecycle.htm#VLDBG14154

----------------------------------------------------------------------------------------------------

Oracle 12c Index

----------------------------------------------------------------------------------------------

 

12c: CONNECTING TO PDB'S WITH SAME NAME

OraFAQ Articles - Sun, 2013-07-28 03:37

When you create a PDB, the database automatically creates and starts a service inside the CDB.The service has the same name as the PDB. It is possible that the name of the service will collide with an existing service name which is registered with the same listener. For example if two or more CDBs on the same computer system use the same listener, and the newly established PDB has the same service name as another PDB in these CDBs, then a collision occurs.

You must not attempt to operate a PDB that causes a collision with an existing service name.

I will demonstrate that a connection that specifies the default service name of a PDB can connect randomly to any of the PDBs with the same service name. To avoid incorrect connections, we should configure a separate listener for each CDB on a computer system.

Current scenario:

I have two CDB's (CDB1 and CDB2) on the same computer system.
Pluggable database PDB1 exists in both the CDB's CDB1 and CDB2

There are two listeners running in database home
listener1 on port 1523
listener2 on port 1524

Overview:

-- Register both the CDB's (and hence PDB's) with listener1 running on port 1523.
-- Verify that if we repeatedly connect to service PDB1, we are randomly connected to different pdb's (PDB1@CDB1 and PDB1@CDB2).
-- Register PDB1@CDB2 with listener2 on port 1524.
-- Verify that now we can connect to the right pdb

Implementation:

-- Register both the CDB's with listener1 running on port 1523

CDB1>alter system set local_listener='em12c.oracle.com:1523';

sho parameter local_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string em12c.oracle.com:1523

CDB2>alter system set local_listener='em12c.oracle.com:1523';

sho parameter local_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string em12c.oracle.com:1523
-- check that both the CDB's and PDB1 in both the CDB's are registered with listener1 (port 1523)

[oracle@em12c ~]$ lsnrctl stat listener1
(output trimmed)
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/em12c/listener1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1523)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "cdb1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb2" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 2 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully
It can be seen that PDB PDB1 has same name in both the CDB's (CDB1 and CDB2) and default service for both the PDB's (PDB1@CDB1 and PDB1@CDB2) are registered with the listener on the same port (1523).

-- Verify that if we repeatedly connect to service PDB1, we are randomly connected to different pdb's (PDB1@CDB1 and PDB1@CDB2)

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb2

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb1

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb2

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb1
Hence, to connect to the right PDB, we should register different PDB's with listeners running on different ports.

-- Let's register PDB1@CDB2 with listener2 on port 1524

CDB2>alter system set local_listener='em12c.oracle.com:1524';

sho parameter local_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string em12c.oracle.com:1524
-- check that CDB1 and pdb1@CDB1 are registered with listener1 (port 1523)

[oracle@em12c ~]$ lsnrctl stat listener1

(output trimmed)

Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/em12c/listener1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1523)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "cdb1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
-- check that CDB2 and PDB1@CDB2 are registered with listener2 (port 1524)

[oracle@em12c ~]$ lsnrctl stat listener2

(output trimmed)

Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/em12c/listener2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1524)))
Services Summary...
Service "cdb2" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully
-- Verify that now we can connect to the right pdb

-- connect to PDB1@CDB1 (listener1, port 1523)

SQL> conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb1

SQL> conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb1

-- connect to PDB1@CDB2 (listener2, port 1524)

SQL> conn system/oracle@em12c:1524/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb2

SQL> conn system/oracle@em12c:1524/pdb1
sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cdb2
Hence, it can be concluded that to avoid incorrect connections, we should configure a separate listener for each CDB on a computer system.
References:

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_6009.htm
http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdblogic.htm#CIHDEDCC
-------------------------------------------------------------------------------------

CURSOR_SHARING=SIMILAR available in Oracle 12c, or not ?

Mihajlo Tekic - Sun, 2013-07-28 00:05
It was announced before Oracle Database 12c was released that CURSOR_SHARING=SIMILAR will be deprecated (MOS Note 1169017.1). Moreover, according to the same note the ability to set this value will be removed.

And indeed, when I looked into 12c documentation, I found EXACT and FORCEbeing the only available values where the former is default value. (link)

I decided to play around a bit and immediately figured that SIMILAR could still be assigned to CURSOR_SHARING.

test[12,133]> ALTER SESSION SET CURSOR_SHARING=SIMILAR;

Session altered. 

When I tried to assign an invalid value to CURSOR_SHARING usingSQL*Plus I got an error message that says EXACT, SIMILAR and FORCE are acceptable values for CURSOR_SHARING parameter.

test[12,133]> ALTER SESSION SET CURSOR_SHARING=foo;
ERROR:
ORA-00096: invalid value FOO for parameter cursor_sharing, must be 
from among SIMILAR, EXACT, FORCE 

Couple of reasons I can think of as why SIMILAR is still allowed. The first, it may be some sort of backward compatibility (so that applications that explicitly set this value don't break when migrated to 12c); or it may be that CURSOR_SHARING=SIMILAR is still a valid option if COMPATIBLE parameter is set to an earlier release where the value was supported. (didn't have time to play with COMPATIBLE parameter)

Anyway, my main question was how Oracle will behave if CURSOR_SHARING is set to SIMILAR in 12c.

I ran a quick test. I created one table, named TAB1 and then executed one sub-optimally shareable statement 5 times passing 5 different literal values for each of the executions.

When CURSOR_SHARING is set to SIMILAR and a suboptimal statement using literals is executed, Oracle would not share any of the existing cursors and will perform a hard parse.

CREATE TABLE tab1 AS (SELECT * FROM user_objects);

ALTER SESSION SET CURSOR_SHARING=SIMILAR;

SELECT COUNT(1) FROM tab1 WHERE object_id>1;

SELECT COUNT(1) FROM tab1 WHERE object_id>2;

SELECT COUNT(1) FROM tab1 WHERE object_id>3;

SELECT COUNT(1) FROM tab1 WHERE object_id>4;

SELECT COUNT(1) FROM tab1 WHERE object_id>5; 

For each of these statements the literals were replaced with bind variables hence the SQL that was parsed has sql id c73v21bgp4956 and text like the one below:

SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"

After running the statements from above, I observed the content in v$sqlarea, v$sql and v$sql_shared_cursor views to see if Oracle did a hard parsing.
 
set line 200
col sql_text format a80
SELECT sql_id, sql_text, executions, child_number FROM v$sql WHERE sql_id='c73v21bgp4956';

SQL_ID SQL_TEXT EXECUTIONS CHILD_NUMBER
------------- -------------------------------------------------------------------------------- ---------- ------------
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1" 5 0


As you can see Oracle reused the same child cursor 5 times, which means even though CURSOR_SHARING was set to SIMILAR it behaved as it was set to FORCE.

I ran the same example from above in 11.2.0.2 database. This time Oracle behaved as expected when CURSOR_SHARING is set to SIMILAR and used 5 different child cursors.
 
set line 200
col sql_text format a80
SELECT sql_id, sql_text, executions, child_number FROM v$sql WHERE sql_id='c73v21bgp4956';

SQL_ID SQL_TEXT EXECUTIONS CHILD_NUMBER
------------- -------------------------------------------------------------------------------- ---------- ------------
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1" 1 0
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1" 1 1
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1" 1 2
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1" 1 3
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1" 1 4


When I checked v$sql_shared_cursor I found that all of the child cursors had Y for HASH_MATCH_FAILED column which is expected when suboptimal statement is executed with CURSOR_SHARING=SIMILAR.


Conclusion


Even though setting SIMILAR to CURSOR_SHARING initialization parameter is still acceptable value, it looks Oracle ignore it and behave as if FORCE is used. (this may not be true if compatible parameter is set to an earlier release, like 11.2.0.2 for example. I didn't have time to test this scenario)


Hope this helps.

On SQL Developer

Nuno Souto - Sat, 2013-07-27 07:29
First of all, apologies for the long delay between posts.  We've been evaluating various avenues for this cycle of hardware and software refresh in our data centres and for obvious reasons I could not make any public postings or comments that might be mis-interpreted by the various suppliers.      In these situations there is a confidentiality protocol that must be followed and simply cannot be Noonshttp://www.blogger.com/profile/04285930853937157148noreply@blogger.com2

ORA-12514 during switchover using Data Guard Broker (Update)

Mihajlo Tekic - Fri, 2013-07-26 21:34
This is just a short update for an earlier post about getting ORA-12514 while performing switchover using DataGuard broker.

There was a comment on whether or not _DGMGRL static service is still required when performing a switchover in 11.2 and onwards.

In order for the broker to be able to successfully start an instance during a switchover operation, static service needs to be registered with the listener. Starting from 11.2.0.1 this service doesn’t have to be "<db_unique_name>_DGMGRL.<db_domain>". Oracle introduced a new instance-level property, StaticConnectIdentifier. As a value, this property accepts a valid net service name (defined in tnsnames.ora) or full connection identifier. Therefore, starting from 11.2.0.1 you have flexibility to use any service, which still needs to be statically registered with the listener.

Here is an example:

There are two databases db112a (primary) and db112b(standby) hosted on hosta and hostb respectively.

Below is the content of the tnsnames.ora. It is identical for both hosts:



DB112A=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hosta)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB112A)
)
)

DB112B=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB112B)
)
)


listener.ora on HOSTA has the following content:


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db112a)
(ORACLE_HOME = /oracle/product/11.2/dbms)
(SID_NAME = db112a)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hosta)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)


listener.ora for HOSTB has db112b service statically registered:


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db112b)
(ORACLE_HOME = /oracle/product/11.2/dbms)
(SID_NAME = db112b)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostb)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)


Now the only thing left would be to set the StaticConnectIdentifier property for each of the databases.


DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration

Configuration - db112

Protection Mode: MaxPerformance
Databases:
dg112a - Primary database
dg112b - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

DGMGRL> edit database db112a set property staticConnectidentifier='db112a';
DGMGRL> edit database db112b set property staticConnectidentifier='db112b';


And you should be all set to perform a switchover:


DGMGRL> switchover to dg112b
Performing switchover NOW, please wait...
New primary database "dg112b" is opening...
Operation requires shutdown of instance "dg112a" on database "dg112a"
Shutting down instance "dg112a"...
ORACLE instance shut down.
Operation requires startup of instance "dg112a" on database "dg112a"
Starting instance "dg112a"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "dg112b"

DGMGRL> show configuration

Configuration - db112

Protection Mode: MaxPerformance
Databases:
dg112b - Primary database
dg112a - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


Should you not want to deal with staticConnectidentifieryou still need to have "<db_unique_name>_DGMGRL.<db_domain>" statically registered in order for the broker to be able to start the instance.

Useful resources:

MOS Note # 1305019.1 - 11.2 Data Guard Physical Standby Switchover Best Practices using the Broker


Hope this helps.

Maven support in WebLogic & JDeveloper 12.1.2

Edwin Biemond - Fri, 2013-07-26 09:32
In the 12.1.2 release of JDeveloper and WebLogic, Oracle really improved the support for Maven as build and provisioning tool. Oracle did this on multiple levels: an Utility to synchronize all the Oracle Middleware jars to a local ( .m2/repository) or a shared repository like nexus or artifactory ojmake maven plugin for just building JDeveloper projects. Updated its Weblogic plugin for

Technologies behind Oracle Transactional Business Intelligence (OTBI)

Dylan Wan - Thu, 2013-07-25 16:57

Oracle Transactional Business Intelligence (OTBI) is one of the business intelligence solutions provided as part of Fusion Applications. 

To build a real-time BI, the major challenge is to make sure that it can perform and has no or minimum interfere to the core objective of the transactional application, the online processing.

This is the reason why we need Oracle Business Intelligence Applications (OBIA) for Fusion Applications.  The idea is to keep the minimal processing of detecting changes and capturing changes in the transactional system and leave everything else, such as, preparing and consolidating the data for reporting, to BI Applications.

Here are some of the technologies available to make OTBI possible:

1. SQL Trimming from ADF

ADF stands for Application Development Framework.  It is the application development framework used in developing Fusion Applications.  In general, it is a declarative metadata driven framework to let the application developers to define the data model, define the data access layer, define the UI rendering, put the validation logic and processing in the middle tier.

The underlying data model, in most of cases, is still the relational model defined in the Fusion Apps transactional database under the 3rd NF design.

The key enabling technologies provided from ADF to OTBI is the “Composite VO” or “Composite View Object”.  For me, it can generate the database SQL for us based on the metadata.  Unlike the database technology using the database view, ADF engine can look further down to the entity objects included in the view object and selectively choose which entities are needed in a given SQL.  If the view object includes two tables (EOs), one primary EO for data at the line level, and the other EO for getting the parent data, When the query (Composite VO) does not include any column from the parent EO, the SQL generated by ADF will not include the table in the join. 

This is a superior technologies, comparing to the old technologies of building the business views.

If you are a Java programmer and would like to get the feeling about what Composite View Object looks like and how it works, here is a good blog post:

Do you know what is a Composite View Object?

2. BI Platform – ADFQuery to Composite VO

This enabling technology is provided by BI platform and available as a Java library. It adds a layer on top of the ADF composite VO.  Without writing the Java code, it generates the codes of creating the composite VO on the fly.  It allows us to query the data from the ADF engine by sending them a XML block called ADFQuery.

This doc shows some of the ADFQuery XML blocks.

http://docs.oracle.com/cd/E15586_01/fusionapps.1111/e20836/adf.htm#BIEMG3435

To see better examples, you can find them in NQQuery.log files.

It is a query language like SQL.  You have the section  for the column projection, the join criteria using view links, and the filter using view criteria.

Here are other enabling technologies behind OTBI.

3. ADFQuery generation from BI Server

4. SQL By Pass Database

5. Relational to Dimensional Mapping (Physical Layer to Business Model Layer)

6. SELECT Physical in initialization block

7. ADFQuery Initialization block

8. Physical Lookup function from BI platform

9. Logical Lookup function from BI platform

10. Data Security enabled at the VO layer via Fusion AppCore

11. Applcore Tree Flattening

12. Applcore Business Intelligence Column Flatten VO (BICVO)

13. BI Flexfield VO generator

14. BI Extender via Import Wizard

15. BI View Object created based on the BI EE Logical SQL (BIJDBC)

16. Effective Date VO with as of date filter

17. ADF Application Module to BI variable interface

and more…

Regardless, the goal of these technologies is to enable the users to get the real time data access to the Fusion Apps.  There is really little or no much we can do for providing the feature like data snapshot, pre-built aggregate, multiple currencies, data consolidation and conformance, cross subject area analysis, and the most important, the query performance with complexity logic to be available in a reasonable time without the interfere to the transactional system.

Categories: BI & Warehousing

Placing / Selling Oracle Service Procurement

Oracle e-Business Suite - Thu, 2013-07-25 09:02

Most of the time when you go for a pre-sale / sales meeting and lets say you are try to position oracle Service procurement . First question come from Client will be, why I need to Implement / use Oracle Service Procurement when I have iProcurement and I can procure services using Oracle iProcurement.

Answer is very simple and most of the time I found it convincing client,

Yes using Purchase basis feature you can buy services using Oracle Core Purchasing or Oracle iProcurement without Service procurement e.g. User Can enter requisition to buy 5000 AED worth of services and flow will go something like  Quantity 5000 and the Price will be 1 AED so total value of services will be 5000 AED.

True Value Addition by Oracle Services Procurement in this area is more organized and streamlined buying of Services. Real advantage comes once you talk about Contingent Workers (very common in GCC region, everything other Person is contingent worked  J ).  User can buy contingent Labor using Service Procurement in a very structured way. Oracle Service procurement has Standard interface with Oracle Time Labor. Using this Time sheet entries get imported and automatically create the receipts against the Purchase Order and it works even more streamlined if you are in an organization who is using or intended to use Oracle Project Suite.

Bottom Line Service Procurement is primarily about managing contingent labor. It is also a key component of Complex  Procurement.

TIP:- Don’t waste your words in talking about Complex Service Procurement if client business has nothing to do with Complex procurement mean if there is no progress based payment, no Recoupment etc. then Sell service Procurement simply using Contingent Worker Example


Categories: APPS Blogs

JDeveloper 12.1.2 EJB & Java Service Facade Datacontrol

Edwin Biemond - Thu, 2013-07-25 07:13
With JDeveloper 12c (12.1.2)  Oracle added some great updates to the ADF EJB / Java Facade Datacontrol. Oracle already added the Java Facade and Named Criteria support in its previous versions of JDeveloper but now also added the so wanted List of Values feature, just like we have in ADF BC.   In the the next part I will show you all the new features and some LOV bugs ( not everything is

Oracle SOA Suite Adapters and B2B Training - Register now!

Ramkumar Menon - Tue, 2013-07-23 23:15

Scott Haaland and I are delivering a SOA Suite 11g Adapters and B2B training as a part of the Oracle Fusion Middleware Summercamp III event  between August 26-30 between August 26-30 at Lisbon, Portugal.  Register now!  More details,including registration are available from our SOA Community page at http://soacommunity.wordpress.com/2013/07/16/soa-suite-11g-b2b-adapters-training-august-26th-30th-2013/

Pages

Subscribe to Oracle FAQ aggregator