Oracle Migration
A very large health insurance company wanted us to assist in their migration to Oracle 8i Parallel Server from Oracle 7.3. In addition the database server was being changed too. The objective was to assist in the formulation and validation of migration strategy, creating actual scripts, etc. as well as to advise on the best practices of the Oracle 8i new features. This was expected to produce a savings of $4 million per year in support contracts.
Our involvement was in many different ways. We
Validated the load handling capability of the new machine by simulating the user load.
Configured the DLM settings to reduce pinging and validated it using a exclusively designed pinging test.
Wrote actual scripts, etc. to do the migration, and were part of the DBA team in doing it.
Implemented partitioning in the new database.
Implemented load balancing among all Oracle nodes using Net8, and even SQL*Net 2.
Diagnosed and successfully rectified a job taking more than 48 hours to run to less than 2 hours.
At the end of the process several documents were produced and delivered as a part of the deliverable to the customer. A sample document that explains how to set up load balancing and fail-over for the clients is available here. For additional sample documents, see here.
Setting Up Oracle Parallel Server
Customer was a Specialized Software and Services vendor in the Insurance business. They wanted to have a rock solid database setup with highest possible failover and no load balancing between nodes but at the lowest possible cost. We made an initial assessment study, setup the hardware and installed the database prior to any other activity. We provided the basic and advanced DBA support fro the entire duration of the project during which we identified and eliminated several actual and potential problems. At the end of the assignment we developed a complete database configuration document and tuned the system over to the customer’s DBA staff. Here is the document (modified to hide the customer’s details).
At the end of the process several documents were produced and delivered as a part of the deliverable to the customer. A sample document that explains the design of the database and forms part of a handover document to the customer DBAs is available here. For additional sample documents, see here.
Setting Up a Replication System
Customer was a Specialized Software and Services vendor in the Insurance business. The need was to create a replicated environment where reports can be run as well as the site can be used as a disaster recovery facility. In our initial analysis we indicated that the two objectives – disaster recovery and reporting database, should be in separate servers. However, due to resource limitation, only one server was chosen.
We set up snapshot replication with about 5 minutes refresh interval on a physically separate server where reporting database was running. The assignment entailed all aspects of replication and modes of handling the two basic problems in any replication site – full refresh after a broken job and altering the master database tables by adding columns. Both were successfully addressed and documented. Click here for a copy of the document modified to hide the customer’s details). The customer’s in-house staff was trained in the whole process at the end of the assignment.
At the end of the process several documents were produced and delivered as a part of the deliverable to the customer. A sample document that explains the design of the replication system and a sort of how-to manual for the customer DBAs is available here. For additional sample documents, see here.
Setting Up a Multi Site Distributed Unified Database
Customer was a multinational telecom manufacturing company with several divisions and profit centers. This led to the diversified databases and in course of time the databases were so far removed from each other that they were very difficult to be homogenized with each one offering a separate set of the data. So the managers were logging on the several systems just to create a homogenized picture of the entire enterprise, leading to inaccuracies and delays. We were assigned to task of creating a homogeneous view.
Our approach was elegantly simple – we adapted one of the central databases as a master and created database links to all other systems that we could have access to. Some of these systems were non-Oracle based, like SQL Server and Informix. We used Oracle Transparent Gateways for creating the links. In other enhancements we eliminated huge batch loads from each system by creating delta- views and letting the client systems build their database by incremental loads, reducing time and improving currency of the data. We were project manager for the entire project.
Creating a XML document for B2B Transaction
Customer was a leader in business to consumer market in loan financing. In one of the processes the loan applications collected over the internet were batched and forwarded to the business partners to be processed, leading to delays and customer dissatisfaction. We were assigned the task of somehow speeding up the process. The process was changed to http posting to the partners using XML. However creating an XML document confirming to the Document Type Definition (DTD) of each partner from over 100 tables proved to be a performance drain. We created a system where a stored procedure could be called which can create an XML document on the fly to be passed on the client application, increasing the performance about twenty fold. At the end of the assignment we developed a detailed specs and design document and trained the customer’s staff in the process.
Creating a Backup/ Recovery Manual for a Large Database
Customer was a multinational telecom manufacturing company with several divisions and profit centers with individual databases for customer records. There was need to create an unambiguous document which can be followed by novice as well as trained DBAs in case disaster strike. The objective was to provide the client DBAs with a single document to reduce errors, eliminate guesswork and enable junior DBAs to act in recovery if needed.
We identified all possible scenarios in disaster and played them out in a test environment, with the recovery. All the commands and responses were recorded in the document so that the reader will have an accurate following when doing the recovery. Finally one scenario - the most disastrous one, was simulated on the production database and was documented for reference.
Database Performance Tuning of a System Using a Canned Product
Customer was implementing a Customer Relationship Management (CRM) tool that was bought from a third party. This tool was based on Oracle database. However the performance was extremely poor when the tool was adapted at the customer site. We are assigned to do the tuning when the source code was not available.
We approached the tuning from the database side with a thorough examination of the internal statistics of the database. We tweaked and tuned the several tunable oracle parameters like shared pool size, db block size, multiblock fetch size, etc. to increase the performance of the tool to a manageable level. At the end of the assignment we provided a detailed documentation to record all actions for future reference.