Feed aggregator

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/

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/

The IPython Notebook Revolution

Catherine Devlin - Fri, 2013-07-19 09:19
Among the many great talks coming to PyOhio at the end of this month: The IPython Notebook Revolution Catherine Devlin If you think of IPython as simply an enhanced version of the live Python prompt, you are in need of re-education. No matter what you do with Python, applying the IPython Notebook imaginatively will revolutionize the way you do it.

I'd like to focus on aspects of IPython outside the traditional number-crunching, plot-making realm, simply because those have been covered so well already - videos by the actual IPython team already have. I'd like to fill up a talk with edgy, imaginative, experimental uses of IPython that aren't well-known yet, or that suggest new ways IPython (and especially the Notebook) may be used in the future. I have a bunch of ideas along those lines...

... but I'd like your input! I don't want to miss anything awesome just because I wasn't aware, and there's a lot being done in the IPython world - more than I've been able to keep track of. Erik Welch has already thoughtfully given me a bunch of links and suggestions from SciPy. Let's crowdsource my talk even further!

Some of the goodies I already plan to include:

  • notebook-based presentations
  • ipython_blocks: probably my Holy Grail of imaginative uses)
  • d3js in IPython: (OK, this still fits the data graphing theme, but it's also ultra-snazzy)
  • ipython_sql: (everybody's got to toot her own horn sometimes)
  • ipfl (web-style forms in a Notebook - very preliminary but an interesting idea)
  • xkcd and hand-drawn mode
  • Wakari

How would you shake up people's notions of "what IPython is for"?

My Sessions at Oracle OpenWorld and JavaOne 2013

Kuassi Mensah - Thu, 2013-07-18 17:18
Have you ever needed to deploy Java applications with tens of thousands of concurrent users?
Have you ever experienced paying twice the same flight ticket, the same article or your taxes?
Have you ever wanted the system to just deal with database failure and not ask you to restart your transaction from start?
Looking to exploit the new Oracle Multitenant Container Database with Java?
Looking to analyze structured data using Mapreduce patterns?
Looking to implement MapReduce patterns using SQL and Hadoop?
If you are a Java architect, Java designer or wannabe looking to exploit new Oracle database 12c enhancements in the areas of performance, scalability, availability, security and manageability/ease-of-use, here are sessions for you. Register and book your seats ASAP!
My Sessions
9/24/13 (Tuesday) 5:15 PM - Marriott Marquis - Salon 8
9/25/13 (Wednesday) 10:15 AM - Marriott Marquis - Salon 7
9/24/13 (Tuesday) 3:00 PM - Hilton - Continental Ballroom 5
9/25/13 (Wednesday) 8:30 AM - Hilton - Continental Ballroom 4
9/23/13 (Monday) 10:45 AM - Marriott Marquis - Salon 3/4


Oracle GoldenGate 11g Handbook

Amardeep Sidhu - Thu, 2013-07-18 10:02

Few months ago I contributed a chapter (on Monitoring, Troubleshooting and Performance tuning) to a GoldenGate book on Oracle Press that Robert Freeman was authoring. Thought of posting a small update that the book is now out. My name doesn’t appear on the main page Sad smile but you will see it in the Acknowledgements section Winking smile Below is a screenshot taken from Amazon preview Smile.

You may want to grab a copy if you are using/planning to use Oracle GoldenGate 11g.

Here is the link to the book page on Amazon. It seems the book is not published in India yet but one can order the imported edition on amazon.in

image

Categories: BI & Warehousing

How to implement Document Versioning with Couchbase

Tugdual Grall - Thu, 2013-07-18 08:59
Introduction Developers are often asking me how to "version" documents with Couchbase 2.0. The short answer is: the clients and server do not expose such feature, but it is quite easy to implement. In this article I will use a basic approach, and you will be able to extend it depending of your business requirements.  Design The first thing to do is to select how to "store/organize" the Tugdual Grallhttps://plus.google.com/103667961621022786141noreply@blogger.com0

Something for the future

Dominic Giles - Thu, 2013-07-18 06:46

A nice little feature in Oracle Database 12c is to query patching information via SQL. You can do this from SQLPlus or any other SQL interface jdbc/odbc etc. You can find more details here

http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_qopatch.htm#CBHEBGIB

However you won't be surprised to find that the following query doesn't currently return any useful information.

SYS@//oracle12c/orcl > select DBMS_QOPATCH.GET_OPATCH_LIST from dual;
GET_OPATCH_LIST
------------------------------------------------------------------------------------------------------------------------
<patches/>


Exalytics - Version X3-4 is Here

Look Smarter Than You Are - Tue, 2013-07-16 21:15
I've mentioned before that the Exalytics X3-4 was nearly available (the first clue was when it hit the engineered system price list back on June 4).  It was talked about at-length during the Kscope13 Sunday Developer's Symposium and... it's finally here.


Hardware Upgrades
  • RAM.  Doubling from 1 terabyte to 2 terabytes.  This will help everything on the box but those of us running Essbase now have even more RAM to use for making RAM drives.
  • Flash.  Exalytics now comes standard with 2.4 TB of flash.  I mentioned this earlier as an upgrade option to the Exalytics X2-4, but it now is native to the X3-4.  As mentioned in my earlier article, flash impacts Essbase performance far more than OBIEE (which isn't as disk I/O intensive).  Having .25 millisecond read latency (what these flash drives are rated) means there's virtually no seek time finding values in an Essbase cube on disk.  I'm expecting most Essbase customers will put their physical cubes on the flash drives and then quickly load them into a RAM drive upon start up (which has better performance than reading into the Essbase caches for each database).
  • Hard Drive.  They are upping the traditional hard drives from 3.6 TB to 5.4 TB.  It still has 6 physical drives in it, but they are going from 600GB drives to 900GB drives. [Updated on 8-25-2013.]
The cores (still 40) stay the same... for now.  At some point, someone is going to start hitting these limits and they're probably up the cores and I wouldn't be surprised if they went 100% flash drives in a future release.

Software
X3-4 supports OBIEE 11.1.1.7, Endeca 3.0, Essbase 11.1.2.3, and any Linux-allowed Hyperion EPM product on 11.1.2.3.  They also strongly imply that there are some Essbase optimizations in 11.1.2.3 that only work on Exalytics, but I haven't found them yet to verify.  Regardless, Exalytics X3-4 is the best engineered system you can currently buy for Essbase, bar none.

Pricing: $175,000
The X2-4 was $135,000 for the hardware (software sold separately), but to add-on flash, you paid an additional $35,000 giving us a real price for X2-4 of $170,000.  The new box is $175,000... and for that additional $5,000, they double the RAM and increase the hard drives 50%.  In other words, you're getting a hell of a deal.  For what is literally $40,000 more in total, you're getting 1 TB more of RAM, 2.4 TB of really good flash, and 1.8 TB of additional hard drive.

What if I Already Bought an X2-4?
First of all, congratulations.  You're really smart, despite what your high school guidance counselor said.  To upgrade your X2-4 to an X3-4, you can buy an upgrade kit!  The upgrade kit (to get flash and the 1 TB of RAM) does cost $105,000 though.  So your X2-4 with an upgrade to an X3-4 will end up costing you $240,000 in total.  Oracle will support your X2-4 under their lifetime support policy even though it is being phased out.  

Availability
You can order an X3-4 now.  I haven't seen one actually ship yet, but it was just officially launched yesterday.  While I think you can still buy the X2-4 until the end of this quarter (August 31, 2013), I'm not at all sure why you would.  Find the extra $40K and get not only blazingly fast flash drives but more RAM than you know what to do with.
Categories: BI & Warehousing

July 2013 Critical Patch Update Released

Oracle Security Team - Tue, 2013-07-16 15:34

Hello, this is Eric Maurice.

Oracle just released the July 2013 Critical Patch Update.  This Critical Patch Update provides 89 new security fixes across a wide range of product families: Oracle Database, Oracle Fusion Middleware, Oracle Hyperion, Oracle Enterprise Manager Grid Control, Oracle E-Business Suite, Oracle PeopleSoft Enterprise, Oracle industry Applications, Oracle Supply Chain Products Suite, Oracle VM, Oracle MySQL, and Oracle and Sun Systems Products Suite.

As a reminder, security fixes for Java SE will continue to be released on a separate Critical Patch Update schedule until October this year.  Starting with the October 2013 Critical Patch Update, Java SE security fixes will be released on the normal Critical Patch Update schedule, along with the security fixes for all other Oracle products, thus likely to increase the total number of security fixes released with each Critical Patch Update.

Out of the 89 new security fixes included with this Critical Patch Update, 6 are for Oracle Database.  One of these database vulnerabilities is remotely exploitable without authentication.  The highest CVSS Base Score for these database vulnerabilities is 9.0.  This score is related to a vulnerability (CVE-2013-3751) which affects the XML Parser on Oracle Database 11.2.0.2 and 11.2.0.3. 

21 of the fixes included in this Critical Patch Update are for Oracle Fusion Middleware.  16 of these vulnerabilities are remotely exploitable without authentication, and the highest CVSS Base Score for these vulnerabilities is 7.5.  This score affects a JRockit vulnerability (CVE-2013-2461), which in fact is related to a series of Java vulnerabilities fixed with the June 2013 Critical Patch Update for Java SE and applicable to JRockit.   With the inclusion of Java in the normal Critical Patch Update schedule starting in October 2013, the release of JRockit and Java security fixes will be integrated.  Note also that with this Critical Patch Update and the previously-released Critical Patch Update, Oracle has been working on addressing a series of known Apache bugs in Oracle HTTP Server.  Finally, note that a number of the Oracle Fusion Middleware vulnerabilities have already been fixed on all supported versions.  The listing of these vulnerabilities in the Oracle Fusion Middleware risk matrix should provide an additional impetus for users of affected versions to update their systems to a more secure release.

The Oracle and Sun Systems Products Suite receive a total of 16 new security fixes.  8 of the vulnerabilities are remotely exploitable without authentication, and the maximum CVSS base Score for these vulnerabilities is 7.8.

Oracle MySQL receives 18 new security fixes.  2 of the MySQL vulnerabilities are remotely exploitable without authentication.  The highest CVSS Base Score for these bugs is 6.8. 

As usual, Oracle recommends that customers apply this Critical Patch Update as soon as possible.  In addition, as previously discussed, Oracle does not test unsupported products, releases and versions for the presence of vulnerabilities addressed by each Critical Patch Update.  However, it is often the case that earlier versions of affected releases are affected by vulnerabilities fixed in recent Critical Patch Updates.  As a result, it is highly desirable that organizations running unsupported versions, for which security fixes are not available under Oracle Premier Support, to update their systems to a current release so as to fully benefit from Oracle’s ongoing security assurance effort (see for example Ovum’s Paper: Avoiding Security Risks with Regular Patching and Support).

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /> 

For More Information:

The July 2013 Critical Patch Update Advisory is located at http://www.oracle.com/technetwork/topics/security/cpujuly2013-1899826.html  

The Oracle Software Security Assurance web site is located at http://www.oracle.com/us/support/assurance/overview/index.html. 

Pages

Subscribe to Oracle FAQ aggregator