Chapter 2. Oracle Tools and Products
In early 2010, when you went to the Oracle Technology Network (OTN) website, it had just one link called Information Integration. This link led you to a simple web page that had information on Oracle-to-Oracle database-centric migration and integration tools such as SQL Loader, Data Pump, Oracle Streams, and Oracle Data Guard. The OTN website has been updated, but still lacks comprehensive coverage of the Oracle Information Integration stack. In this chapter, we will provide you with the relevant information, so that you can use the right Oracle Enterprise Information Integration (EII) or data migration product for your data migration, consolidation, and information integration projects.
Oracle has always had a robust set of database-centric data integration tools. The products include Oracle Warehouse Builder (OWB) and Oracle Gateways to access other relational databases. Oracle has also offered middle tier products such as an enterprise service bus...
Database migration products and tools
Data migration is the first step when moving your mission critical data to an Oracle database. The initial data loading is traditionally done using Oracle SQL Loader. As data volumes have increased and data quality has become an issue, Oracle Data Warehouse and Oracle Data Integrator have become more important, because of their capabilities to connect directly to source data stores, provide data cleansing and profiling support, and graphical drag and drop development. Now, the base addition of Oracle Data Warehouse Builder is a free, built-in feature of the Oracle 11g database, and price is no longer an issue.
Oracle Warehouse Builder and Oracle Data Integrator have gained adoption as they are repository based, have built-in transformation functions, are multi-user, and avoid a proliferation of scripts throughout the enterprise that do the same or simpler data movement activity. These platforms provide a more repeatable, scalable, reusable, and model...
Physical federation products
Physical federation, as the name implies, involves physically moving data from its target data source into an Oracle Online Transaction Processing (OLTP) system, Data Warehouse (DW) or Operational Data Store (ODS). The data movement may take place using bulk file transport, data streaming through ODBC or JDBC, a messaging system, reading of database log files or any information integration mechanism.
Traditionally, physical federation has been associated with enterprise data warehousing. Master Data Management and data hubs have moved physical federation from being a read-only database (such as a data warehouse or data mart) to a component of a company's OLTP infrastructure. This complicates the situation as solutions like Change Data Capture (CDC), bi-directional replication, and data write back to the source may be required.
Since we are pulling data in many cases from legacy environments, non-Oracle databases, or consolidating information that may have duplicate...
Virtual federation products
Virtual federation is about leaving the data where it is and providing the user community with a view to the data through the tool of their choice, including a web browser. Instead of moving data around like physical federation, an Oracle product is used to provide users with a single view of their data. Virtual federation using Oracle products can be done at the developer level using SQL, using Java development tools to service-enable any data source, drag and drop development using Oracle JDeveloper, and by end users using Oracle Business Intelligence tools.
Oracle Gateways and Heterogeneous Services
The core of Oracle's database virtual federation strategy is Heterogeneous Services (HS). Heterogeneous Services provides transparent and generic gateway technology to connect to non-Oracle systems. Heterogeneous Services is an integrated component of the database. Therefore, it can exploit all the capabilities of the Oracle database including PL/SQL and Oracle SQL...
Data services are at the leading edge of data integration. Traditional data integration involves moving data to a central repository or accessing data virtually through SQL-based interfaces. Data services are a means of making data a 'first class' citizen in your SOA.
Recently, the idea of SOA-enabled data services has taken off in the IT industry. This is not any different than accessing data using SQL, JDBC, or ODBC. What is new is that your service-based architecture can now view any database access service as a web service. Service Component Architecture (SCA) plays a big role in data services as now data services created and deployed using Oracle BPEL, Oracle ESB, and other Oracle SOA products can be part of an end-to-end data services platform. No longer do data services deployed in one of the SOA products have to be deployed in another Oracle SOA product. SCA makes it possible to call a BPEL component from Oracle Service Bus and vice versa.
Oracle Data Integration Suite...
The mainframe was the ultimate solution when it came to data consolidation. All data in an enterprise resided in one or several mainframes that were physically located in a data center. The rise of the hardware and software appliance has created a 'what is old is new again' situation; a hardware and software solution that is sold as one product. Oracle has released the Oracle Exadata appliance and IBM acquired the pure database warehouse appliance company Netezza, HP, and Microsoft announced work on an SQL Server database appliance, and even companies like SAP, EMC, and CICSO are talking about the benefits of database appliances.
The difference is (and it is a big difference) that the present architecture is based upon open standards hardware platforms, operating systems, client devices, network protocols, interfaces, and databases. So, you now have a database appliance that is not based upon proprietary operating systems, hardware, network components, software, and data...
Instead of consolidating databases physically or accessing the data where it resides, a data grid places the data into an in-memory middle tier. Like physical federation, the data is being placed into a centralized data repository. Unlike physical federation, the data is not placed into a traditional RDBMS system (Oracle database), but into a high-speed memory-based data grid. Oracle offers both a Java and SQL-based data grid solution. The decision of what product to implement often depends on where the corporations system, database, and application developer skills are strongest. If your organization has strong Java or .Net skills and is more comfortable with application servers than databases, then Oracle Coherence is typically the product of choice. If you have strong database administration and SQL skills, then Oracle TimesTen is probably a better solution.
The Oracle Exalogic solution takes the data grid to another level by placing Oracle Coherence, along with other Oracle...
Information Lifecycle Management
Information Lifecycle Management (ILM) is a supporting solution for physical federation, data consolidation, and business intelligence. What all these data integration solutions have in common is that the database where the information is being stored continues to grow as daily activity is added to the centralized database. This is not only an issue of data storage costs, but also performance of the applications that access these databases and the cost of managing growing volumes of data. ILM provides a mechanism to automatically move data from primary high speed storage to lower cost storage.
Oracle Information Lifecycle Management
Oracle ILM is a combination of features that are part of the Oracle database and a lightweight GUI that can be used to build an ILM solution. These Oracle database features include Oracle table partitioning, Oracle advanced table compression, and Virtual Private Database (VPD). Oracle VPD is used in an ILM solution by eliminating...
Oracle-to-Oracle data integration is treated separately as Oracle offers products that have been optimized for moving data between Oracle database instances. These products can only be used when both the source and target databases are Oracle databases. Oracle has had a long history of Oracle-to-Oracle-based solutions, including Oracle Replication Server and Oracle imp/exp. These key legacy Oracle-to-Oracle integration solutions have been replaced by Oracle Streams and Oracle Data Pump, respectively.
Oracle Streams is a log-based solution that enables the propagation and management of data, transactions, and events in a data stream either within an Oracle database, or from one Oracle database to another. The stream routes published information to subscribed destinations. Streams supports capture and apply from Oracle to non-Oracle systems. However, the apply or capture from non-Oracle systems must be custom-coded, or through an Oracle Transparent or Generic...
Application integration takes many of the products and technologies we discussed in this chapter to provide a complete integration solution for orchestrating agile, user-centric business processes across your enterprise applications. The products that are bundled with the Oracle SOA Suite are the foundation of Oracle Application Integration Architecture (AIA), so this Oracle SOA product suite will be discussed first.
Oracle's Application Integration Architecture (AIA) offers pre-built solutions at the data, process, and user interface level, delivering a complete process solution to business end users. All Oracle AIA components are designed to work together in a mix-and-match fashion and are built for easy configuration, ultimately lowering the cost and IT burden of building, extending, and maintaining integrations.
The products included in this suite are: Oracle BPEL Process Manager, Oracle Human Workflow, Oracle Integration Adapters (Oracle SOA...
We have discussed each product in detail and provided use cases to help guide you as you move towards a more current information integration architecture. Before we end this chapter, we believe that providing you with a matrix will help in your decision making.