managing oracle applications data … smarter no part of this presentation may be reproduced or...
Post on 29-Dec-2015
215 Views
Preview:
TRANSCRIPT
Managing
Oracle Applications Data
…Smarter
No part of this presentation may be reproduced or transmitted in any form by any means, electronic or mechanical, including photocopying and recording, for any purpose without the express written permission of Princeton Softech, Inc.
22004 Princeton Softech, Inc.
Information Powers the Organization
Information is an increasingly valuable – and costly – organizational asset
Goals: minimize risk, reduce costs, and increase flexibility...
By aligning IT investments according to evolving information value UnstructuredUnstructured
Semi-StructuredSemi-Structured
StructuredStructured
42004 Princeton Softech, Inc.
How much? How fast?
2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011
Current Data
Historical Data
“With growth rates exceeding 125%, organizations face two basic options: continue to grow the infrastructure or develop processes to separate dormant data from active data.”
Source: META Group 2003
52004 Princeton Softech, Inc.
As Transaction Data Accrues…
Longer batch cyclesResources and maintenance increases
Increased storage and processing costsBatch window crosses into prime time
Impacts to the business process
Data Growth Impacts Business Process
62004 Princeton Softech, Inc.
What’s Driving Data Growth?
High-volume online transaction processing:– Customer facing eCommerce applications– ERP/CRM – Supply chain applications
Record retention requirements:– Financial Services – Sarbanes-Oxley– Healthcare – HIPAA– Pharmaceutical – 21 CFR 11 – Financial – IRS and SEC Rule 17a-4
Multiplicity of data:– Multiple operational, development and testing environments– Disaster recovery and business continuity– Routine backup and recovery
72004 Princeton Softech, Inc.
Data Retention Example
SEC Rule 17a-4– Retain records for six years from close of account
or termination of associated employees– Keep records in an "easily accessible place" – Produce records immediately if the records are
located in the office where the request is made – Produce records within three business days if the
requested records are located off-site– Display requested records electronically in a local
office and immediately produce printed copies to satisfy Rule requirements
82004 Princeton Softech, Inc.
Total Size = 1200 GB
6 200Quality Control
Data Multiplier Effect
1
Production 200 GB
5
Test 200
4
Development 2003Disaster Recovery 200
2 Backup 200
92004 Princeton Softech, Inc.
…continue to add– People– Processes– Technology
…continue to limit– Performance– Availability– Time for other projectsProduction
Database
Database Growth: Can’t Ignore It
102004 Princeton Softech, Inc.
Alternatives
Add more capacity – Bottom line impact– Uncontrolled cost spiral
Institute rigorous database tuning– Does not directly address data growth– Reaches point of diminishing returns
Delete/ purge data – Legal and data retention issues– Data may be needed for data warehousing
In-house development– Complex undertaking– Application specific– Support/upgrade/maintenance/opportunity cost
112004 Princeton Softech, Inc.
“Deploying a successful database archiving strategy is critical to ensure database availability.”
“Organizations should incorporate data archival policies within current design or implementation plans to maintain future performance and reduce related costs.”
What Can We Do?
122004 Princeton Softech, Inc.
About Princeton Softech
Providing relational data management solutions for enterprise IT since 1989
Global company; 2000+ customers in 30+ countries Recognized by Gartner, Giga and META as market
leader Pioneered enterprise database archiving and test
data management segments Partners with industry-leading database, enterprise
application and storage solution providers
-
132004 Princeton Softech, Inc.
Pre- Production Data
Production & History Data
OracleOracle SQLServer
SQLServer SybaseSybase InformixInformix DB2
UDB
DB2 UDB DB2DB2 LegacyLegacy
Relationship Engine
RelationalTools for Servers
Archive for DB2
Archivefor Servers
ClarifyCRMPeopleSoftOracle Apps
Manage Enterprise Data…Smarter
142004 Princeton Softech, Inc.
Archive Production Data
Smaller Production DB; Lower Cost
OfflineArchiveStorage
Near lineArchiveDBMS
ProductionDBMS
LiveBU
DR
HistoricalReporting
OfflineArchiveStorage
Near lineArchiveDBMS
ProductionDBMS
LiveBU
DR
OfflineArchiveStorage
Near lineArchiveDBMS
ProductionDBMS
LiveBU
DR
OfflineArchiveStorage
Near lineArchiveDBMS
ProductionDBMS
LiveBU
DR
ProductionDBMS
LiveBU
DR
HistoricalReporting
Streamline Test Data
TestDBMS
RefreshTest Data
CompareResults
EditTest Data
TestDBMS
RefreshTest Data
CompareResults
EditTest Data
TestDBMS
RefreshTest Data
CompareResults
EditTest Data
TestDBMS
RefreshTest Data
CompareResults
EditTest Data
152004 Princeton Softech, Inc.
Princeton Softech’s Active Archive Solutions
Reduce the amount of data in the application database– Remove obsolete or infrequently used data– Maintain “business context” of archived data
Enable easy user access to archived information– View, research and restore as needed
Support data and storage management strategies
Data Access (locate, browse, query, report)
Production Database
Active Archive
Database
ArchiveFiles
Archive Files
Archive&
Restore
162004 Princeton Softech, Inc.
• Support for Oracle Applications versions 11.0 & 11i
• Financials
• Manufacturing
• Supply Chain
• Human Resources
• Projects
• Transparent access to data via standard Oracle Applications forms and reports
• Pluggable archiving framework designed to support predefined archive templates and local customizations
Solution Overview
172004 Princeton Softech, Inc.
Archiving Historical Data
GL – Balances, Journals …AP – Payments, Invoices, Vendors…AR – Receipts, Invoices …FA – Depreciation, Adjustments
Locate, Browse, Query, Report . . .
Data Access
General Ledger
Payables
Receivables
Assets
Production Database
Active Archive Database
Archiving Oracle Financials Data
182004 Princeton Softech, Inc.
Transparent Access to Archived Data• Seamless access to BOTH
archived and production data via Oracle Applications
• Leverages “Responsibility” to access data, using standard Oracle forms and reports
• Steps to view archived data: Login Select Responsibility Access archived data,
production data or BOTH
ProductionData
ArchivedData
192004 Princeton Softech, Inc.
Archive Workbench Manage and report on the lifecycle
of the entire archive process– Current location of the archived
data– Run archive jobs
• To the active archive database
• To Offline storage• Restore
Detailed Reporting on the Archive Process– Request date and time– Current status– Module and user– Criteria used – Number of records archived
from each table
202004 Princeton Softech, Inc.
Verification Engine
System Verification Diagnostics – Comprehensive top to bottom
configuration analysis/validation
Archive Process Diagnostics – Performs application validation
prior to archiving data – Verifies and matches table
structures in the active archive database
Oracle Patch and Upgrade Checking – Detects mismatches– Provides upgrade procedures
212004 Princeton Softech, Inc.
Information Lifecycle Management
All data has a lifecycle Parameters differ over time
– Business value– Access requirements– Performance
requirements Store and manage
accordingly
Acquisition
Frequent Access
Sporadic Access
Minimal Access
Disposal
222004 Princeton Softech, Inc.
Customer Success - AVX Who is AVX?
– Kyocera Group company– manufacturer and supplier of electronic components
Challenges– Implement a single global instance without adversely impacting
business– Avoid business disruption during software upgrades– Reduce frequency of hardware upgrades– Maintain consistent database size and performance
Reasons– Consolidation will grow the database by magnitudes – Need to implement database archiving before adding the largest
manufacturing site (70,000+ WIP move transactions daily)
232004 Princeton Softech, Inc.
Customer Success - AVX
Implementation Approach– Business requirements
• Decrease upgrade time• Focus staff on core competencies• Prepare for archiving during the definition phase of the
global IT solution • Provide user community with transparent data access
Results– First phase: installed, configured & implemented in one week– Consistent database size and performance– Smaller test environments; refresh quickly
242004 Princeton Softech, Inc.
Database Archiving Benefits Summary
Improved Availability• Faster batch cycles• Ready for business• Transparent access
Cost Containment
• Postpone hardware upgrades• Postpone software upgrades• Deliver competitive advantage
Improved Performance• Faster response time• Increased productivity• Satisfied customers
252004 Princeton Softech, Inc.
Archiving Reduces Database Volume
Total Size = 1200 GB
200Quality ControlTest 200Development 200Disaster Recovery 200Backup 200Production 200 GB
Total Size = 720 GB
120Quality ControlTest 120Development 120Disaster Recovery 120Backup 120Production 120 GB
80 GB
120 GBProduction Data
Active Archive Data
Saving 480 GB (40%)Primary Storage
262004 Princeton Softech, Inc.
Streamlining Application Environments
Multiple environments required– Application deployments– Customizations – Upgrades– Patches– QA database– Training, Staging, etc.
Challenges– Space– Time– Cost
272004 Princeton Softech, Inc.
Typical Approaches
Purchase additional disk space? Stretch the project timeline? Discontinue one of your current
projects? Most common solution:
– Share test and development databases
– among projects/teams
282004 Princeton Softech, Inc.
Managing Oracle Applications Test Data
Extract precise subsets of related data to build realistic, “right-sized” test databases
– Create referentially intact subsets
– Remove the bulk of production data
– Minimize the load on testing and staging servers
Speed iterative testing tasks with reusable processing definitions and Extract Files to ensure consistency
Browse and edit complex relational data from multiple tables in a single display
ProductionDatabase
TestQA
Dev
292004 Princeton Softech, Inc.
Create, Review, Edit Templates Add or Remove
tables Include new DB
objects – Functions – Packages – Procedures – Rules– Sequences – Views
302004 Princeton Softech, Inc.
Creating a Subset using a Template
Criteria can be based on one or more modules
All Date Values– GL Date– Transaction Date– Default Effective
Date Sets of Books Organizations Status Invoice number(s) “And/Or” combos More….
Define your criteria…
312004 Princeton Softech, Inc.
Benefits
Maximize allocated disk space Increase number of test/ dev
environments Reduce infrastructure costs Realize development and test
efficiencies– Reduce the cycle times for
test upgrades– Reduces time and resources
required to backup and maintain non-production environments
322004 Princeton Softech, Inc.
Pre- Production Data
Production & History Data
OracleOracle SQLServer
SQLServer SybaseSybase InformixInformix DB2
UDB
DB2 UDB DB2DB2 LegacyLegacy
Relationship Engine
RelationalTools for Servers
Archive for DB2
Archivefor Servers
ClarifyCRMPeopleSoftOracle Apps
Manage Enterprise Data…Smarter
top related