Skip navigation.

Feed aggregator

Quick and Dirty - Refreshing a Select List of Values

Denes Kubicek - Tue, 2015-06-23 05:14
This is a quick and dirty solution but it works. It shows hot to update a select list of values on demand without a lot of coding.

Categories: Development

AJAX Callback and jQuery creating Page Items Dynamically

Denes Kubicek - Tue, 2015-06-23 04:40
This example shows a couple of interesting techniques:
  • 1. How to create and use an AJAX Callback process instead of an Application Process on Demand,
  • 2. how to create a dynamic page item,
  • 3. how to modify the item display settings and reference Font Awsome icon set and finaly,
  • 4. it shows how to call an AJAX process from javascript.
This question is based on a recent forum posting where this question was asked.

Have a look and enjoy.

Categories: Development

RMAN - 3 : The DB_UNIQUE_NAME in Backups to the FRA

Hemant K Chitale - Tue, 2015-06-23 03:14
When you run RMAN Backups to the FRA without using the FORMAT clause, Oracle automatically generates filenames for the BackupPieces.  The folder name is derived from the system date.  But what is the parent folder for backups ?  Is it simply the DB_RECOVERY_FILE_DEST ?  Actuallly, the DB_UNIQUE_NAME comes into play as well.

For example :

[oracle@localhost ~]$ rman target /

Recovery Manager: Release - Production on Tue Jun 23 16:57:19 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> list backup of datafile 1;

using target database control file instead of recovery catalog

List of Backup Sets

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 831.23M DISK 00:03:32 07-JUN-15
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20150607T165914
Piece Name: /NEW_FS/oracle/FRA/ORCL/backupset/2015_06_07/o1_mf_nnndf_TAG20150607T165914_bq81z2y6_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14068320 07-JUN-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 366.89M DISK 00:01:56 07-JUN-15
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20150607T170754
Piece Name: /NEW_FS/oracle/FRA/ORCL/backupset/2015_06_07/o1_mf_nnndf_TAG20150607T170754_bq82hc5f_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14068721 07-JUN-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf


[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release Production on Tue Jun 23 16:58:34 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_recovery_file_dest

------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /NEW_FS/oracle/FRA
db_recovery_file_dest_size big integer 8G

We can see that the DB_RECOVERY_FILE_DEST is defined as "/NEW_FS/oracle/FRA". However, the backups go into a "backupset" folder under "/NEW_FS/oracle/FRA/ORCL/". The "ORCL" is part of the path to the folder holding the backups. How is this "ORCL" derived ?

SQL> show parameter db_name

------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> show parameter db_unique_name

------------------------------------ ----------- ------------------------------
db_unique_name string orcl

By default, the DB_UNIQUE_NAME is the same as DB_NAME. Let's see what happens after I change the DB_UNIQUE_NAME.

SQL> !ls -l /NEW_FS/oracle/FRA/
total 4
drwxrwx--- 5 oracle oracle 4096 Jun 7 17:10 ORCL

SQL> alter system set db_unique_name='HEMANTDB' scope=SPFILE;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 385878712 bytes
Database Buffers 62914560 bytes
Redo Buffers 6008832 bytes
Database mounted.
Database opened.
SQL> !ls -l /NEW_FS/oracle/FRA/
total 4
drwxrwx--- 5 oracle oracle 4096 Jun 7 17:10 ORCL


After resetting the DB_UNIQUE_NAME, Oracle doesn't immediately create the folder for the new DB_UNIQUE_NAME until and unless I run an RMAN Backup.

RMAN> exit

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-03135: connection lost contact
Process ID: 3344
Session ID: 67 Serial number: 13

Recovery Manager complete.
[oracle@localhost ~]$ rman target /

Recovery Manager: Release - Production on Tue Jun 23 17:07:14 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> backup datafile 1;

Starting backup at 23-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=38 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 23-JUN-15
channel ORA_DISK_1: finished piece 1 at 23-JUN-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_06_23/o1_mf_nnndf_TAG20150623T170721_brl8g9od_.bkp tag=TAG20150623T170721 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 23-JUN-15

Starting Control File and SPFILE Autobackup at 23-JUN-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_06_23/o1_mf_s_883156126_brl8k0w4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 23-JUN-15


SQL> !ls -l /NEW_FS/oracle/FRA/
total 8
drwxrwx--- 3 oracle oracle 4096 Jun 23 17:07 HEMANTDB
drwxrwx--- 5 oracle oracle 4096 Jun 7 17:10 ORCL

SQL> show parameter db_name

------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> show parameter db_unique_name

------------------------------------ ----------- ------------------------------
db_unique_name string HEMANTDB

Notice how Oracle created the "HEMANTDB" folder under the designated DB_RECOVERY_FILE_DEST. It then created the "backupset" and "autobackup" folders also as subfolders under this.  BackupSet BackupPieces and Controlfile Autobackups are now going to the new path.  The backups are go to folders under {DB_RECOVERY_FILE_DEST}/{DB_UNIQUE_NAME}

Categories: DBA Blogs

Creating Custom Listening Connectors using Integration Broker SDK

Javier Delgado - Tue, 2015-06-23 02:48
One of my customers recently had the need of allowing a third party web application to attach files into PeopleSoft. After trying a number of different approaches (the integration had to be done at the web application client level, which significantly reduces the options to manipulate the request to PeopleSoft before sending it, particularly when dealing with old web browsers), I gave up and came to the conclusion that I needed a custom listening connector in Integration Broker to implement such integration. 

The process of developing and installing a custom listening connector in Integration Broker is quite well described in PeopleBooks, however, I thought it would helpful to document the process I have followed.
PeopleSoft comes with some samples of connectors developed using the Integration Broker SDK. The most interesting one for listening connectors is located in the following folder:
I've used this sample as the basis for my custom connector. In some cases, I also found quite handy to decompile the standard connectors such HttpListeningConnector. I could not find any exhaustive source of documentation of the Integration Broker SDK, so decompiling the existing connectors proved to be a good way to understand how to best use the SDK.
Note: For decompiling the Java class files I have used a very simple tool names cavaj. It is a very simple tool, but still helpful.
DevelopmentTaking the previously mentioned sample as the basis, I have coded my own connector. If you are not interested in the details, you may want to skip to the next section.
What I needed to do in my connector was basically two things:
  1. Encode the incoming file using Base64, as binary files could not be processed otherwise by the existing PeopleCode Message API.
  2. Pass any parameters received in the HTTP Header or the URL as part of the IBInfo (Integration Broker information included in every internal Integration Broker message.

If you want to check the actual code, you can download the source code from this link:

CompilingOnce your source code is ready, you need to compile it. The first step for Java compilation is to set the environment variables so they point to the Java SDK. In my case, I was using the PeopleSoft HCM 9.2 Update Image 11, and these were the commands I needed to use:
export JAVA_HOME=/opt/oracle/psft/pt/jdk1.7.0_71 export PATH=$PATH:$JAVA_HOME/bin
Then I went to the directory were my java file was placed and run the following command to compile the file:
javac -cp /home/psadm2/psft/pt/8.54/webserv/peoplesoft/applications/peoplesoft/PSIGW.war/WEB-INF/classes:/home/psadm2/psft/pt/8.54/webserv/peoplesoft/applications/peoplesoft/PSIGW.war/WEB-INF/lib/mail.jar:/opt/oracle/psft/pt/bea/wlserver/server/lib/weblogic.jar 
The paths may obviously differ in your case, but the important thing is to include the following directories/jar files in your class path:
  • $PIA_HOME/webserv/peoplesoft/applications/peoplesoft/PSIGW.war/WEB-INF/classes
  • mail.jar
  • weblogic.jar

Please note that weblogic.jar was needed because I was using WebLogic as my web server. In case you use WebSphere, you need to change this jar file.
DeploymentOnce the file is compiled, you need to copy the resulting class file to the following directory:
The next step is to let WebLogic know that there is a new servlet available. This can be done by editing the following file:

All you need to do is to duplicate the sections referring to ExampleServletListeningConnector and replace those appearances with your connector name. In my case, I was using PeopleTools 8.54, and these were the lines I had to include:
(...) <servlet> <servlet-name>ExampleServletListeningConnector</servlet-name> <servlet-class></servlet-class> </servlet> <servlet> <servlet-name>FileUploadListeningConnector</servlet-name> <servlet-class></servlet-class> </servlet>(...) <servlet-mapping> <servlet-name>ExampleServletListeningConnector</servlet-name> <url-pattern>/ExampleServletListeningConnector/*</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>FileUploadListeningConnector</servlet-name> <url-pattern>/FileUploadListeningConnector/*</url-pattern> </servlet-mapping>(...)
TestingIn order to test the new connector, you need to reboot the web server so the changes made to register the new servlet are taken. Once this is done, you can check if the new listening connector is responding by using the declared URL:

Empty Leaf Blocks After Rollback Part I (Empty Spaces)

Richard Foote - Tue, 2015-06-23 00:09
There’s been an interesting recent discussion on the OTN Database forum regarding “Index blank blocks after large update that was rolled back“. Setting aside the odd scenario of updating a column that previously had 20 million distinct values to the same value on a 2 billion row table, the key questions raised are why the blank index leaf blocks […]
Categories: DBA Blogs

Moved to new/better blog Feeds via eMail

Online Apps DBA - Mon, 2015-06-22 18:21

Screen shot 2015-06-23 at 01.12.28


If you subscribed to our blog onlineAppsDBA (using RSS feed) prior to April 2015 then from today you will receive email for new posts via new/better email service provider.

Emails for new post will come from email ID contactus[@] and subject as [New Post] … and will look like image above.

Note: Ensure that you add email address contactus[@] as safe sender list.

The post Moved to new/better blog Feeds via eMail appeared first on Oracle : Design, Implement & Maintain.

Categories: APPS Blogs

Python cx_Oracle 5.2 driver for Oracle Database has been released

Christopher Jones - Mon, 2015-06-22 17:03

Anthony Tuininga just released an updated Python cx_Oracle 5.2 driver for Oracle Database. This release brings a number of enhancements, many of them for Oracle Database 12c features such as longer VARCHARS.

cx_Oracle 5.2 is the first release Oracle has contributed code for (thanks Avinash!) so we're pretty happy all around. And a big thank you to all the other contributors and users who have made this release possible.

The new code features are:

  • Support for Oracle Database 12c strings up to 32k characters.
  • Support for LOB values larger than 4 GB.
  • Support for Oracle Database 12c array DML row counts.
  • Support for fetching batch errors.
  • Support for connections as SYSASM.
  • Added types NCHAR, FIXED_NCHAR and LONG_NCHAR to replace the types UNICODE, FIXED_UNICODE and LONG_UNICODE (which are now deprecated). These types are available in Python 3 as well so they can be used to specify the use of NCHAR type fields when binding or using setinputsizes().
  • Support for building without any configuration changes to the machine when using instant client RPMs on Linux.
  • Fixed session releasing to the pool when calling connection.close() (Issue #2)
  • Fixed binding of booleans in Python 3.x.
  • Added __version__ attribute to conform with PEP 396.
  • Fixed handling of datetime intervals (Issue #7)

The complete release notes are here.

My favorite feature is the installation improvement. (Disclaimer: I contributed the initial implementation!) With this change, Instant Client RPMS on Linux can now be used. The best bit is cx_Oracle will automatically locate Instant Client and will then also automatically build using rpath. The installation of cx_Oracle on Linux is now as simple as installing the Instant Client Basic & SDK RPMs, and running 'pip install cx_Oracle'. No need to set ORACLE_HOME during installation. No need to set LD_LIBRARY_PATH at runtime. If you have a Linux ULN support subscription you can install Instant Client via yum, which makes it even simpler.

Users of Database Resident Connection Pooling will like the connection.close() fix since it allows DRCP to be used effectively without requiring a cx_Oracle session pool.

In summary the cx_Oracle 5.2 release adds great features to the already impressive Oracle Database support available to Python applications. Application development and deployment just got better.


Thanks again to Anthony, Avinash and all the contributors who have made cx_Oracle so good.

12c Parallel Execution New Features: 1 SLAVE distribution

Randolf Geist - Mon, 2015-06-22 14:39
When certain SQL features get used in pre-12c versions that force non-parallel evaluation, like using ROWNUM or certain Analytic Functions like LAG/LEAD, then - depending on the overall plan shape - Oracle can start to decompose the parallel execution plan into several so called DFO trees (If you want learn more about DFO trees and DFOs I recommend watching my online tutorial on my Youtube channel).

Now having multiple DFO trees in a single parallel execution plan comes with several side effects that are confusing and complicate matters unnecessarily, like each DFO tree allocates its own PX slave set(s), and so each one can potenially end up with a different DOP, which means you can have more than one DOP in a single parallel execution plan.

Depending on the overall plan shape this might also mean that a DFO tree can get started multiple times, and again this means that each time it is started / completed PX slaves need to be allocated and de-allocated, potentially causing a significant overhead coordinating all that activity that is not directly related to the actual execution.

This also means that having multiple DFO trees can lead to a situation where (a lot) more PX slaves are allocated than expected, in case multiple DFO trees are active at the same time - which again means that if you believe you can limit the number of PX slaves allocated by a single parallel execution using Resource Manager directives you might be wrong.

Since all these are undesirable side effects, starting with release 12c Oracle has put effort into new features that minimize the need for such a decomposition into multiple DFO trees. One of these new features is the so called "1 SLAVE" distribution method that can get used if such a non-parallel evaluation is required.

Quite similar to the recently described "PX SELECTOR" operator the "1 SLAVE" distribution uses a single PX slave out of a slave set to execute the non-parallel operations instead of the Query Coordinator. The main difference this makes is that the parallel and serial operations now are still part of the same DFO tree instead of having parts of the execution plan executed by the Query Coordinator and different DFO trees before and after such serial operations.

Let's have a look at a simple example to demonstrate the new feature. I use here three identical tables, just for the sake of being able to differentiate the tables in the plan output - in principle re-using a single table three times would be sufficient.

create table t2
(rownum * 2) + 1 as id
, mod(rownum, 2000) + 1 as id2
, rpad('x', 100) as filler
(select /*+ cardinality(100000) */ * from dual
connect by
level <= 100000) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b

exec dbms_stats.gather_table_stats(null, 't2')

create table t4
(rownum * 2) + 1 as id
, mod(rownum, 2000) + 1 as id2
, rpad('x', 100) as filler
(select /*+ cardinality(100000) */ * from dual
connect by
level <= 100000) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b

exec dbms_stats.gather_table_stats(null, 't4')

create table t6
(rownum * 2) + 1 as id
, mod(rownum, 2000) + 1 as id2
, rpad('x', 100) as filler
(select /*+ cardinality(100000) */ * from dual
connect by
level <= 100000) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b

exec dbms_stats.gather_table_stats(null, 't6')

explain plan for
select /*+ no_merge(x) */
select /*+ parallel(t6 4)
select /*+ parallel(t2 4) */
--lag(id) over (order by id) as v1_rn
rownum as v1_rn
, as v1_id
, t2.filler as v1_filler
) v1
, (
select /*+ parallel(t4 2) */
--lag(id) over (order by id) as v2_rn
rownum as v2_rn
, as v2_id
, t4.filler as v2_filler
) v2
, t6
v1_id = v2_id
and v1_id =
) x
rownum > 1

-- plan shape
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
| 0 | SELECT STATEMENT | | | | |
| 1 | COUNT | | | | |
|* 2 | FILTER | | | | |
| 3 | PX COORDINATOR | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ30002 | Q3,02 | P->S | QC (RAND) |
| 5 | VIEW | | Q3,02 | PCWP | |
|* 6 | HASH JOIN | | Q3,02 | PCWP | |
| 7 | PX RECEIVE | | Q3,02 | PCWP | |
| 8 | PX SEND HASH | :TQ30001 | Q3,01 | P->P | HASH |
| 9 | PX BLOCK ITERATOR | | Q3,01 | PCWC | |
| 10 | TABLE ACCESS FULL | T6 | Q3,01 | PCWP | |
| 11 | BUFFER SORT | | Q3,02 | PCWC | |
| 12 | PX RECEIVE | | Q3,02 | PCWP | |
| 13 | PX SEND HASH | :TQ30000 | | S->P | HASH |
|* 14 | HASH JOIN | | | | |
| 15 | VIEW | | | | |
| 16 | COUNT | | | | |
| 17 | PX COORDINATOR | | | | |
| 18 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 19 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 20 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 21 | VIEW | | | | |
| 22 | COUNT | | | | |
| 23 | PX COORDINATOR | | | | |
| 24 | PX SEND QC (RANDOM)| :TQ20000 | Q2,00 | P->S | QC (RAND) |
| 25 | PX BLOCK ITERATOR | | Q2,00 | PCWC | |
| 26 | TABLE ACCESS FULL| T4 | Q2,00 | PCWP | |

Predicate Information (identified by operation id):

2 - filter(ROWNUM>1)
6 - access("V1_ID"="T6"."ID")
14 - access("V1_ID"="V2_ID")

-- plan shape
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
| 0 | SELECT STATEMENT | | | | |
| 1 | COUNT | | | | |
|* 2 | FILTER | | | | |
| 3 | PX COORDINATOR | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10004 | Q1,04 | P->S | QC (RAND) |
| 5 | VIEW | | Q1,04 | PCWP | |
|* 6 | HASH JOIN BUFFERED | | Q1,04 | PCWP | |
| 7 | PX RECEIVE | | Q1,04 | PCWP | |
| 8 | PX SEND HASH | :TQ10002 | Q1,02 | P->P | HASH |
| 9 | PX BLOCK ITERATOR | | Q1,02 | PCWC | |
| 10 | TABLE ACCESS FULL | T6 | Q1,02 | PCWP | |
| 11 | PX RECEIVE | | Q1,04 | PCWP | |
| 12 | PX SEND HASH | :TQ10003 | Q1,03 | S->P | HASH |
|* 13 | HASH JOIN BUFFERED | | Q1,03 | SCWC | |
| 14 | VIEW | | Q1,03 | SCWC | |
| 15 | COUNT | | Q1,03 | SCWP | |
| 16 | PX RECEIVE | | Q1,03 | SCWP | |
| 17 | PX SEND 1 SLAVE | :TQ10000 | Q1,00 | P->S | 1 SLAVE |
| 18 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 19 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 20 | VIEW | | Q1,03 | SCWC | |
| 21 | COUNT | | Q1,03 | SCWP | |
| 22 | PX RECEIVE | | Q1,03 | SCWP | |
| 23 | PX SEND 1 SLAVE | :TQ10001 | Q1,01 | P->S | 1 SLAVE |
| 24 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
| 25 | TABLE ACCESS FULL| T4 | Q1,01 | PCWP | |

Predicate Information (identified by operation id):

2 - filter(ROWNUM>1)
6 - access("V1_ID"="T6"."ID")
13 - access("V1_ID"="V2_ID")
Let's start with the plan shape: We can see from multiple occurrences of the PX COORDINATOR operator, the TQ column and the corresponding TQ entries in the NAME column that this parallel execution plan consists of three DFO trees. When analyzing the actual run time activity it would also become obvious that the different DFO trees actually run at a different DOP and use different PX slave sets. In this particular case here it would even become obvious that two DFO trees are active at the same time - so it's pretty much demonstrating all the undesirable side effects of having multiple DFO trees that I mentioned above.

The HASH JOIN between V1 and V2 runs serially, whereas the HASH JOIN between T6 and the join result of V1 and V2 runs parallel. Since we have a Serial->Parallel distribution between these two HASH JOINs, an additional BUFFER SORT operation gets added - as outlined in the PX SELECTOR note.

If we now look at the plan shape we notice that the execution plan consists of a single DFO tree, and hence all the side effects I've outlined are gone. The serial evaluation is done by a single PX slave of one set, again indicated by the SCWC/SCWP decorator, similar to the PX SELECTOR operator.

However, the plan shape also demonstrates one possible disadvantage of the new feature: Since now everything is part of a single DFO tree we end up with more re-distributions per DFO tree, and hence might have an increased demand for the dreaded BUFFERing to prevent multiple re-distributions being active at the same time - here this can be seen in operation ID 13 - the HASH JOIN executed serially by one PX slave is actually turned into a HASH JOIN BUFFERED (so the worst case for a (not really) "parallel" hash join - executed only by a single PX slave and needs to buffer the right hand row source in addition), and also the second HASH JOIN is turned into its BUFFERED variant. This means compared to the plan shape that contains no HASH JOIN BUFFERED but an additional BUFFER SORT operation, the plan shape with this data pattern actually requires more PGA / TEMP space than the plan shape due to the double buffering now necessary.

The new 1 SLAVE distribution doesn't get used always in 12c. If you run just the join between V1 and V2 for example, then the old plan shape will be used, and there are again multiple DFO trees. Furthermore, in this particular case, when you start changing the DOP used in the PARALLEL hints you also might end up with a plan shape where one view uses the 1 SLAVE distribution whereas the other one uses the old plan shape with Query Coordinator activity - I haven't investigated further why this happens.

If Analytic Functions get used, you might also see a "1 SLAVE (ORDER)" variation of the distribution that enforces a certain order when re-distributing the data, similar to the "PX SEND QC (ORDER)" operator.

Log Buffer #428: A Carnival of the Vanities for DBAs

Pythian Group - Mon, 2015-06-22 11:45

The Log Buffer Edition once again is sparkling with some gems, hand-picked from Oracle, SQL Server and MySQL.


  • Oracle GoldenGate  is now certified with Unity 14.10.  With this certification, customers can use Oracle GoldenGate to deliver data to Teradata Unity which can then automate the distribution of data to multiple Teradata databases.
  • How do I change DNS servers on Exadata storage servers.
  • Flushing Shared Pool Does Not Slow Its Growth.
  • Code completion is the key feature you need when adding support for your own JavaScript framework to NetBeans IDE.
  • Replicating Hive Data Into Oracle BI Cloud Service for Visual Analyzer using BICS Data Sync.

SQL Server:

  • Trigger an Email of an SSRS Report from an SSIS Package.
  • Script All Server Level Objects to Recreate SQL Server.
  • A Syntax Mystery in a Previously Working Procedure.
  • Using R to Explore Data by Analysis – for SQL Professionals.
  • Converting Rows to Columns (PIVOT) and Columns to Rows (UNPIVOT) in SQL Server.


  • Some applications, particularly those written with a single-node database server in mind, attempt to immediately read a value they have just inserted into the database, without making those operations part of a single transaction. A read/write splitting proxy or a connection pool combined with a load-balancer can direct each operation to a different database node.
  • Q&A: High availability when using MySQL in the cloud.
  • MariaDB 10.0.20 now available.
  • Removal and Deprecation in MySQL 5.7.
  • Getting EXPLAIN information from already running queries in MySQL 5.7.

Learn more about Pythian’s expertise in Oracle , SQL ServerMySQL, as well as the author Fahd Mirza.

Categories: DBA Blogs

Cost of PeopleSoft vs SaaS and Other Options

PeopleSoft Technology Blog - Mon, 2015-06-22 11:32

There is a lot of publicity around SaaS and various cloud options for enterprise applications these days.  This leaves PeopleSoft customers wondering whether they should stay with PeopleSoft and what that real cost comparisons are.  Is SaaS really less expensive for them?  Is it really the best option for their business?   What are the factors they need to consider?  Can they stay with PeopleSoft and run it in the cloud, getting the best of both worlds?

Quest, the international user group organization recently published an informative article titled How Much Does Your ERP Cost?  In this short article, the author provides ways to consider cost, but importantly, examines the value of these systems to the enterprise and why that should be a factor in your decision.  The author interviews several customers in assessing these questions.  Worth a read.

Server refused public-key signature despite accepting key!

Vikram Das - Mon, 2015-06-22 11:23
A new SFTP connection was not working, even though everything looked fine:

1. Permissions were correct on directories:
chmod go-w $HOME/
chmod 700 $HOME/.ssh
chmod 600 $HOME/.ssh/authorized_keys
chmod 600 $HOME/.ssh/id_rsa
chmod 644 $HOME/.ssh/
chmod 644 $HOME/.ssh/known_hosts

2. Keys were correctly placed
However, it still asked for password, whenever SFTP connection was done:
Using username "sftpuser".Authenticating with public key "rsa-key-20150214"Server refused public-key signature despite accepting key!Using keyboard-interactive authentication.Password:
I tried various things, none worked and I eventually went back to my notes for SFTP troubleshooting:
1. Correct Permissionschmod go-w $HOME/chmod 700 $HOME/.sshchmod 600 $HOME/.ssh/authorized_keyschmod 600 $HOME/.ssh/id_rsachmod 644 $HOME/.ssh/id_rsa.pubchmod 644 $HOME/.ssh/known_hosts
2. Make sure the owner:group on the directories and files is correct:
ls -ld  $HOME/ls -ld  $HOME/.sshls -ltr $HOME/.ssh
3. Login as root
chown user:group $HOME chown user:group $HOME/.sshchown user:group $HOME/.ssh/authorized_keyschown user:group $HOME/.ssh/id_rsachown user:group $HOME/.ssh/id_rsa.pubchown user:group $HOME/.ssh/known_hosts
4. Check for user entries in /etc/passwd and /etc/shadow
5. grep user /etc/shadow
When I did the 5th step, I found that /etc/shadow entry for the user didn't exist.  So I did these steps:
chmod 600 /etc/shadowvi /etc/shadowInsert this new line at the endsftpuser:UP:::::::Save Filechmod 400 /etc/shadow
It started working after that.
Categories: APPS Blogs

Buffer pool advisory in AWR

Yann Neuhaus - Mon, 2015-06-22 07:45

In Oracle memory advisors: how relevant ? I said that advisors are calculating their recommendations from statistics cumulated since the begining of the instance, even in AWR which is supposed to cover only a short period. Here is a quick test on buffer pool advisory to validate that assumption.

  I'm running the following query to compare the 'physical reads cache' from DBA_HIST_SYSSTATS and the value from the advisor in DBA_HIST_DB_CACHE:

SQL> column sysstat_value format 999G999G999G999
SQL> column advisor_value format 999G999G999G999
SQL> select snap_id,sysstat_value,advisor_value from
-- physical reads cache
(select snap_id,dbid,instance_number,stat_name,value sysstat_value from dba_hist_sysstat where stat_name like 'physical reads cache')
natural join
(select snap_id,dbid,instance_number,'ADVISOR',actual_physical_reads advisor_value from DBA_HIST_DB_CACHE_ADVICE where size_factor=1 and name='DEFAULT')
order by 1 desc,2,3;

Here is the result where I can see that they match for all snapshots I have in history:

---------- ---------------- ----------------
      3025      708,373,759      731,359,811
      3024      708,364,027      731,350,072
      3023      708,284,582      731,270,631
      3022      708,281,965      731,268,020
      3021      708,280,406      731,266,424
      3020      708,252,249      731,238,240
      2133       45,538,775       46,930,580
      2132       45,533,062       46,924,865
      2131       30,030,094       31,423,247
      2130          138,897          138,406
      2129          125,126          124,637
      2128          114,556          114,052
      2127          113,455          112,959
      2126          112,378          111,890
      2125          111,179          110,682
      2124          106,701          106,197
      2123          104,782          104,287
      2122           59,071           58,578
      2121           57,972           57,476

I'm not sure about the three columns available in that view: PHYSICAL_READS BASE_PHYSICAL_READS ACTUAL_PHYSICAL_READS so let's check that the one I used is the one that is displayed in an AWR report. Here is the latest report for snapshots 3024 to 3025:  


Here is how I verified my assumtions, on an instance that is running for a long time. When you read at the advisor recommendations, you should know whether the activity since instance startup is relevant or not. And I don't know how to reset the counters (except with an instance restart).

Oracle BPEL & BPM 12c Set Flow Instance Title

Jan Kettenis - Mon, 2015-06-22 07:44
In this article I describe how to set the instance title for a composite in Oracle BPEL or BPM 12c.

Sometimes little, annoying things that are fixed with a new release can give great joy. With 11g you could set the composite instance title using the (advanced) XPath function setCompositeInstanceTitle(). This helps to find or identify instances in Enterprise Manager. However, for high volume composites you may want to configure in-memory-optimization as well by adding the following properties to the BPEL process in the composite.xml:

The problem is that when you configure in-memory-optimization, that setCompositeInstanceTitle() fails because there is a relation with auditing.

In 12c the composite instance does no longer have such a prominent role in the SOA/BPM Suite. Instead the flow instance now has that role. And with that setCompositeInstanceTitle() has been deprecated, and setFlowInstanceTitle() should be used instead:

You can set the flow instance title by adding a script activity right after the Receive with assign to some dummy string variable, using the setFlowInstanceTitle() as show above. To make it work I had to wrap the string variable in a string function.
Unlike the setCompositeInstanceTitle(), with the setFlowInstanceTitle() you can configure in-memory-optimization and still display the title in Enterprise Manager:

Setting the title for a BPM flow instance can be done using the same XPath function and use it in an assignment to some dummy variable in the start event.

Now Launching Oracle Process Cloud Service

WebCenter Team - Mon, 2015-06-22 06:45

Announcing Oracle Process Cloud Service
-       Empowering Business Users with Process Automation

Oracle Process Cloud Service equips your line of business to deliver on digital automation of process applications - independent of IT. In a zero-code, cloud based infrastructure, Oracle Process Cloud Service empowers the business user to manage the entire lifecycle of business processes and gain actionable insight on process health and SLAs. This takes business empowerment to a new level of automation and efficiency.

Join us in Making History

We are excited Larry Ellison will be breaking ground with Oracle Process Cloud Service on Monday June 22. Join us online or on the web – we have an exciting lineup of customers, hands on demos, product experts and more.

[Event |Redwood Shores] Jun 22 Integrate, Accelerate, Lead with Oracle Cloud Platform (link
[Event |Webcast] Jun 22 Larry Ellison Unveils New Oracle Cloud Platform Services (link)

How to Learn More on Oracle Process Cloud Service?

[BPM-CON Webcast] Rapid Process Automation on the Cloud (link
[Solution Brief] A New Approach to Digital Transformation (link)
[Datasheet]  Productive Work Management in the Cloud (link)
[Video] Introducing Oracle Process Cloud Service (link)
[Infographic] Empowering the Business to Deliver at Market Speed (link)
[eBook] Rapidly Design, Automate and Manage Processes on the Cloud (link)
[Video] Technical dive into Process Cloud Service (link)

Testimonials: PCS Customers and Partners in Action 

[Video] Land O Lakes Extends SaaS with Oracle Process Cloud Service (link)
[Video] Flextronics Speeds up BPM with Oracle Process Cloud Service (link)
[Video] Avio Empowers Business with Oracle Process Cloud Service (link)
[Video] eProseed Digitizes with Oracle Process Cloud Service (link)
[Video] Sofbang Extends SOA with with Oracle Process Cloud Service (link)
[Video] Hitachi Manages Contracts with Oracle Process Cloud Service (link)


[Analyst Report] Ovum: Oracle Brings Process to the Cloud (link)
[Byline] Forbes: What IT Pros Must do to Survive, Thrive Amid Rapid Change (link)


[Event |Webcast] Jun 19  Oracle Public Cloud: Platform as a Service (link)
[Event |Webcast] Jun 22  Larry Ellison Unveils New Oracle Cloud Platform Services (link)
[Event |Redwood Shores] Jun 22 Integrate, Accelerate, Lead with Oracle Cloud Platform (link)
[More Events]  BPM Events: Click here

To learn more join us at Follow us on twitter @OracleBPM

Index Tree Dumps in Oracle 12c Database (New Age)

Richard Foote - Sun, 2015-06-21 23:56
I’ve previously discussed Index Tree Dumps but I’ve recently found a nice little improvement that’s been introduced in Oracle Database 12c. Let’s begin by creating a little table and index: To generate an Index Tree Dump, we first need the OBJECT_ID of the index: And then use it to generate the Index Tree Dump: Previously, an […]
Categories: DBA Blogs

ODTUG KScope15: Sunday Symposium

Luc Bors - Sun, 2015-06-21 14:14

From Tea 'n' Cakes to Tablets: Oracle ERP Cloud UX Bakes In Exceedingly Better Business*

Usable Apps - Sun, 2015-06-21 07:58

Robots—software, really—continue to revolutionize enterprise finance departments. Automation is replacing traditional financial roles and transforming others, offering even more innovative opportunities in the enterprise.

Accounts payable clerks, accounts receivable specialists, inventory control admins, and more enterprise positions, are being automated completely, freeing up headcount for new roles to deliver more business effectiveness. The Oracle Applications Cloud user experience (UX) strategy reflects these kinds of trends and innovates accordingly.

And yet, the birth of such financial innovation was in the least likely, most un-sci-fi place, you might imagine: the teashops of post-World War II Great Britain. In 1951, British catering giant J Lyons & Co. kick-started ERP as we now know it by introducing the first enterprise computer: LEO (Lyons Electronic Office).

Computing was not new, of course. What was innovative was how Lyons used it in business. Starting with the replacement of dull, repetitive tasks performed by clerks collecting and entering data, LEO went on to manage the Lyons payroll, the catering supply chain, and more.

LEO led the world in business computing at the time, and gave rise to today’s systems engineering. And all because of a dream of one day being able to add up the receipts for Lyons's iced buns in the teashops of Great Britain.

Enterprise Resource Planning (ERP) user experience (UX) is no longer just icing on the cake. It's central to user happiness and business productivity, satisfying that ever-ravenous appetite for consumer-like apps in the enterprise. UX is now that must-have item on the menu of enterprise cloud software adoption.

David Haimes glances across the Oracle ERP Cloud

Number-munching: David Haimes glances across the Oracle ERP Cloud UX over the best of Oracle HQ catering.

I chatted with David Haimes (@dhaimes), Senior Director of Oracle Applications Product Development and all-things Financials Cloud UX champ, about financial departments moving from cupcakes to computers and now to the cloud.

Over the best of Oracle HQ Building 300 bakery cakes, David explained:

“That mundane, day-to-day work of calculations has been transformed by enterprise software. Now finance departments do things automatically, faster, and with fewer errors. Oracle ERP Cloud enhances daily activities, enabling departments to analyze data for profit, revenue, and cash flow insights for business planning and financial forecasting, and to manage by exception.

For example, with the Oracle ERP Cloud visualizations of financial data, finance departments can easily spot trends and opportunities to run the business better. At a glance, a department can see those outliers and exceptions that may be threats and deal with them before they become real problems.”

What's going on here? Glance at Oracle ERP Cloud and the Finance Department's questions are answered

What's going on here? At a glance, period close, revenue, expenses, and more questions are answered for the finance department using Oracle ERP Cloud.

David demonstrated how the Oracle ERP Cloud user experience enables higher-value finance department activities using the Glance, Scan, Commit design philosophy. At a glance, from one page an analyst can see what’s going on with a company’s finances, what needs attention, and then scan for more detail and commit to act on it easily, if necessary.

Oracle ERP Cloud user experience is no amuse-bouche, but the main course for CIOs and decision makers, saving their businesses time and labor costs. With the median number of full-time financial employees falling in big companies by about 40% since 2004, there are now about 70 finance people needed for every $1 billion of revenue, according to consultants, the Hackett Group. It's all about ROI. Oracle ERP Cloud serves up a great recipe for user satisfaction (key ingredients: zero training, talent attraction, and retention) too. 

Oracle ERP Cloud watchface

Oracle ERP Cloud watchface on Android Wear smartwatch. With financial data in the cloud, the glance design philosophy enables finance departments to work seamlessly across devices.

We’ve already busted that myth that financial departments are far from social, and now another stereotypical view of accountancy is up-ended. Emerging financial roles have become sexy, the analysts required for today’s financial departments are hot talent demanding higher remuneration.

This is one sweet story about Oracle Cloud ERP and an awesome baked-in UX: automating the routine; enabling a eminently more interesting world of work for real people previously dismissed as being disinterested in such things; delivering benefits across the entire business; and being easily customized to suit any business taste.

Now, who wouldn’t want a slice of that action?

Time for Tea?

David Haimes takes the time to glance at Oracle ERP Cloud

Take some time to explore the Oracle ERP Cloud user experience more with our online resources.

Find out more about the Oracle Applications Cloud User Experience and Oracle ERP Cloud on YouTube and the Oracle VOX blog.

And, watch out for some tasty ERP Cloud sessions at Oracle OpenWorld 2015.

David Haimes blogs on the Oracle Intercompany Financials Blog.

* With apologies to Mr. K

GoldenGate Initial Load by Senthil Rajendran

Senthil Rajendran - Sun, 2015-06-21 02:42
Welcome to the world of replication. Recently have been deep into GoldenGate and here are the series of presentation that can help you to start with GoldenGate

GoldenGate Fundamentals Session
GoldenGate Implementations Essential
GoldenGate Monitoring, Troubleshooting and Tuning
GoldenGate Use Cases
GoldenGate Initial Load

GoldenGate Use Cases by Senthil Rajendran

Senthil Rajendran - Sun, 2015-06-21 02:41
Welcome to the world of replication. Recently have been deep into GoldenGate and here are the series of presentation that can help you to start with GoldenGate

GoldenGate Fundamentals Session
GoldenGate Implementations Essential
GoldenGate Monitoring, Troubleshooting and Tuning
GoldenGate Use Cases
GoldenGate Initial Load