Oracle Information Integration, Migration, and Consolidation - book review

Recently I read interesting book about data migration and consolidation published by PACKT Publishing titled “Oracle Information Integration, Migration, and Consolidation”.

Whole book is split into 9 chapters covering different areas of this complicated topic. In my opinion this book is intended for architects or anyone who want to have a general overview of data migration and integration based on Oracle tools. You can find some details there as well but this book is covering too many tools to do a deep dive into each of them.
 
Chapter 1: Getting Started with Information Integration
This is a overview of data and process integration challenges and general approaches. Some high level use cases are presented as well.

Chapter 2: Oracle Tools and Products
This chapter is describing all major tools created or acquired by Oracle Corp. and used for  consolidation, migration or data exchange. It is a general overview and can be used as easier way to go thought all application owned by Oracle.

Chapter 3: Application and Data Integration Case Study
Proof of Value (POV) for insurance company is a case study described in this chapter. This project include process and data integration using Oracle BPEL between mainframe VSAM, flat files, COBOL application, MQ Series and Oracle database. Author is presenting his findings and solutions to migrate one of business process in organization. Using step by step approach and Oracle SOA Suite project achieve all goals and bring some more benefits for organization.

Chapter 4: Oracle Database Migrations
There are many way to migrate data from one database to other and between versions of this same database. A few ways are mentioned here but author focused on migration from non-oracle database to Oracle database using SQL Developer. All important steps are presented and everyone can try to reproduce it on his own test environment.

Chapter 5: Database Migration Challenges and Solutions
Database migration is a tough projects and list of well know problems described in this chapter is a good point to start thinking about it. Most of RDBMS systems have their own ways to tread null/not-null columns, objects names have a different rules and procedural language running inside database is not a standard neither. Addressing most of these issues before starting a project is a good way to avoid unexpected results at the end.

Chapter 6: Data Consolidation and Management
Trusted data sources are one of base requirements in every enterprise application or reporting tools. Oracle solutions like Oracle Master Data Management Suit is presented here as an answer to these needs. Beside that some other solutions like data centric consolidation solution using in-memory TimesTen database or application centric data consolidation using Oracle Coherence are presented as well.

Chapter 7: Database-centric Data Integration
Database centric data integration is based on continuous data exchange process in heterogeneous database environment. Two very powerful products are presented here: Oracle GoldenGate and Oracle Data Integrator. In addition to that Oracle Gateways (Heteregeneous Services Agent) is presented as well. This chapter together with chapter 4 are most technical and detailed ones.

Chapter 8: Application and Process Integration
This chapter is presenting history of Oracle application and process integration solutions over the years. Starting with basic export / import tables for Oracle Apps and ending with Oracle Fusion Middleware and Oracle SOA Suite.

Chapter 9: Information Lifecycle Management for Transactional Applications
Last chapter is covering problem of data and application retention. How and how long data have to be kept in database, how to managed them and how to deal with application retirement process.
 
I can recommend that book for every one who want to have a general overview of Oracle approach to integration problems. As I said before one book can’t cover all technical problems/issues but it is good starting point to enter integration world.

regards,
Marcin

New release of S-ASH v.2.3

New version of Oracle Simulate ASH is ready to download and test. You can find it here - sash-v2.3
New features:
- new easier installation process
- new metrics history (IO and system)
- improved support for RAC / multi database in one repository
- Oracle scheduler is used to manage jobs

This version of OraSASH has been tested with Oracle 10g and 11g R1 and R2 as a target database and Oracle 11gR2 Express Edition as repository.

regards,
Marcin

Quick note about Hybrid Columnar Compression running on my laptop

As I mentioned in previous post Oracle allowed users to use Hybrid Columnar Compression on ZFS appliance. Two days ago there was a note in MOS about bug/enhancement but there was no patch for it. Yesterday I was able to download patch number 13041324 for Oracle 11.2.0.3 and successfully applied it against my installation on virtual machine. After that I run my Oracle's Sun Unified Storage Simulator and I created a new table space on it using NFS share together with Oracle Direct NFS client. Next I try to create compressed table using old existing table space on local ASM disk and it fail as expected. Than I give a try on new table space and this time table has been created :

SQL> create tablespace SUNNFSTS datafile '/nfs/sunnfsts.dbf' size 10M;
Tablespace created.

SQL> create table SCOTT.test1 tablespace USERS  compress for archive high as select * from dba_objects where rownum < 100;
create table SCOTT.test1 tablespace USERS  compress for archive high as select * from dba_objects where rownum < 100
                                                                                      *
ERROR at line 1:
ORA-64307: hybrid columnar compression is not supported for tablespaces on this
storage type

SQL> create table SCOTT.test tablespace SUNNFSTS  compress for archive high as select * from dba_objects where rownum < 100;
Table created.
SQL> exit

Looks like it is working and HCC can be tested without expensive hardware. I hope I will find some time to write down all my notes and create proper blog post about installation and configuration.
regards,
Marcin

Oracle 11.2.0.3 and Hybrid Columnar Compression Support

Oracle announced support for Hybrid Columnar CompressionSupport for ZFS Storage Appliances and Pillar Axiom Storage Systems on Sep 30^th. According to Steve Tunstall's blog these feature should work with 11.2.0.3 and any ZFS Appliance. I have tested it on Oracle Enterprise Linux 5.5 with 11.2.0.3 and ZFS Appliance software emulator and it didn’t work. I have read Steve post again and he mentioned in comment that additional patch is required. There is was no information about it on MOS yesterday evening but today morning I found following note - "Bug 13041324 - Enhancement to allow HCC on ZFS and Pillar Storage". I still can’t find patch and I’m really tempted to try it on my virtual machines and has a possibility to test Oracle HCC feature. Hope this patch will be available soon.

Regards,

Marcin

Grid Control 11g - 2 small improvments

I installed Oracle Grid Control 11g months ago and left it with typical configuration. Recently it becomes a little bit unstable and did a quick audit. Here are my first 2 finding but and will do a deeper dive into into if only I will find some more time.

Memory settings
In default configuration of Weblogic Management server running Grid Control instance maximum number of Java stack memory (parameter -Xmx) is unset and it is using default value of 512 MB. It is too small for big installation with many agents and concurrent users. Weblogic configuration allow users to set USER_MEM_ARGS variable with Java memory settings in starting script which will be used instead of MEM_ARGS variable. I has goint to change startManagedWebLogic.sh file as I want to keep default values for admin server but I found better solution on  Oracle Enterprise Manager blog in "Increasing the JAVA heap size of the OMS" section. Instead of startManagedWebLogic.sh you need to edit startEMServer.sh file
and add following section just before last line

if [ "${SERVER_NAME}" != "EMGC_ADMINSERVER" ] ; then
   USER_MEM_ARGS="-Xms256m -Xmx1024m -XX:MaxPermSize=512m -XX:CompileThreshold=8000 -XX:PermSize=128m"
   export USER_MEM_ARGS
fi

Log rotation
It was very typical for old Oracle Application Server or Grid Control 10g that logs weren't rotated in default configurations. I have seen this many times when I have been asked to do some troubleshooting. After installation of Grid Control 11g together with Weblogic I was thinking how many logs has been left without rotation this time. Most of them looks OK but I found one mod_wl_ohs.log belonging to WebTier (Oracle HTTP server) which is still not rotated. This log file is configured in this configuration file mod_wl_ohs.conf and it is quite easy to add rotation to it. As all rotated Apache logs this line

WLLogFile ${ORACLE_INSTANCE}/diagnostics/logs/${COMPONENT_TYPE}/${COMPONENT_NAME}/mod_wl_ohs.log

should be started with rotatelog command:

WLLogFile "|${ORACLE_HOME}/ohs/bin/rotatelogs ${ORACLE_INSTANCE}/diagnostics/logs/${COMPONENT_TYPE}/${COMPONENT_NAME}/mod_wl_ohs.log 43200"

Update:
Oracle Enterprise Manager 12c (like Cloud) has been just officially presented. There is still not documentation but you can download and install it.
regards,
Marcin

Oracle PeopleSoft Enterprise Financial Management - book review

This is my second review in short time frame but totally different from first one. It is not about Oracle DB and I’m doing it from newbie position. This is my first experience with Oracle PeopleSoft from business perspective. Oracle PeopleSoft Enterprise Financial Management 9.1 Implementation is covering following topics from business perspective:
1. Financials Fundamentals
2. PeopleSoft Security
3. PeopleSoft Billing Module
4. PeopleSoft Accounts Receivable Module
5. PeopleSoft Asset Management Module
6. PeopleSoft Accounts Payable Module
7. PeopleSoft General Ledger Module
8. PeopleSoft Expenses Module
9. PeopleSoft Commitment Control

Whole book is covering one example based on Automotive company and every chapter is describing other business requirements.
All topics are explained in details and every topic has a short introduction from functional perspective. There are also implementation challenges for most common issues. Every chapter is guiding user though module configuration and typical actions. Examples have slides and description of all actions.

I can recommend this book for all for people without financial background like me as a starting point into PeopleSoft application world.

regards,
Marcin

Online index rebuild cleanup

 If session performing online index rebuild will be killed by mistake Oracle end up with some inconsistency inside dictionary. If you try to re-run killed command to create index once more database will complain that index already exist. Although if you try to drop index database will complain that index is in rebuild state and could not be dropped at that time.
To fix that issue you need to use package dbms_repair as showed in example:

1. Building test table
   

   SQL> create table test tablespace users as select rownum id, 'xxxxxxxxxxxxxxxxxxxxxxxx' col1 from dba_source, dba_source where rownum < 10000000;
   Table created.
   

2. New index build has been started with online clause and session has been killed
   

   SQL> create index test_index on test (id) tablespace users online;
   create index test_index on test (id) tablespace users online
                                                              *
   ERROR at line 1:
   ORA-03113: end-of-file on communication channel
   Process ID: 4356
   Session ID: 191 Serial number: 13
   

3. Sanity check - index exist even if build has been never completed.
   

   SQL> select index_name from dba_indexes where table_name = 'TEST';
   INDEX_NAME
   ------------------------------
   TEST_INDEX
   

4. Now let's try to drop it
   

   SQL> drop index test_index;
   drop index test_index
              *
   ERROR at line 1:
   ORA-08104: this index object 66960 is being online built or rebuilt
   

5. So maybe we can create it again ?
   

   SQL> create index test_index on test (id) tablespace users online;
   create index test_index on test (id) tablespace users online
                *
   ERROR at line 1:
   ORA-00955: name is already used by an existing object
   

6. Let's clean it up and allow Oracle to drop index - ONLINE_INDEX_CLEAN need a object number which can be taken from drop index error message or using the following query (at least it worked for me).
   

   SQL> select min(object_id) from dba_objects where object_name = 'TEST_INDEX';
   
   MIN(OBJECT_ID)
   --------------
           66960
   
   SQL> declare
     2  ret boolean;
     3
     4  begin
     5      ret:=dbms_repair.ONLINE_INDEX_CLEAN(66960);
     6  end;
     7  /
   
   PL/SQL procedure successfully completed.
   

   and now let's check if index is still there
   

   SQL> select index_name from dba_indexes where table_name = 'TEST';
   no rows selected
   

Hope it help to solve some of your problems.

regards,

Marcin