3 Case Studies
The following case studies for SQL Server demonstrate some of the features of the Oracle Database Gateway. You can verify that the gateway is installed and operating correctly by using the demonstration files included in the distribution media.
The demonstration files are automatically copied to disk when the gateway is installed.
This chapter contains the following sections:
Installation media contents, demonstration files, demonstration requirements, creating demonstration tables, case 1: simple queries, case 2: a more complex query, case 3: joining sql server tables, case 4: write capabilities, case 5: data dictionary query, case 6: the pass-through feature, case 7: executing stored procedures.
The cases illustrate:
A simple query (Case 1)
A more complex query (Case 2)
Joining SQL Server tables (Case 3)
Write capabilities (Case 4)
A data dictionary query (Case 5)
The pass-through feature (Case 6)
Executing stored procedures (Case 7)
The installation media contains the following:
One SQL script file that creates the demonstration tables and stored procedures in the SQL Server database
One SQL script file that drops the demonstration tables and stored procedures from the SQL Server database
After a successful gateway installation, use the demonstration files stored in the directory ORACLE_HOME\dg4msql\demo where ORACLE_HOME is the directory under which the gateway is installed. The directory contains the following demonstration files:
The case studies assume these requirements have been met:
The gateway demonstration tables and stored procedures are installed in the SQL Server database
The Oracle database has an account named SCOTT with a password of TIGER
The Oracle database has a database link called GTWLINK (set up as public or private to the user SCOTT ) which connects the gateway to a SQL Server database as SCOTT with password TIGER2
For example, you can create the database link as follows:
Oracle Net Services is configured correctly and running
The case studies are based on the GTW_EMP , GTW_DEPT , and GTW_SALGRADE tables and the stored procedures InsertDept and GetDept . If the demonstration tables and stored procedures have not been created in the SQL Server database, use the bldmsql.sql script to create them. Enter the following:
The script creates the demonstration tables and stored procedures in the SQL Server database accordingly:
Demonstration Table Definitions
The following table definitions use information retrieved by the SQL*PLUS DESCRIBE command:
Demonstration Table Contents
The contents of the SQL Server tables are:
Case 1 demonstrates the following:
A simple query
A simple query retrieving full date information
The first query retrieves all the data from GTW_DEPT and confirms that the gateway is working correctly. The second query retrieves all the data from GTW_EMP including the time portion of the hire date because the default date format was set to DD-MON-YY HH24:MM:SS for the session by an ALTER SESSION command.
Case 2 demonstrates the following:
The functions SUM( expression ) and NVL ( expr1, expr2 ) in the SELECT list
The GROUP BY and HAVING clauses
This query retrieves the departments from GTW_EMP whose total monthly expenses are higher than $10,000 .
Case 3 demonstrates the following:
Joins between SQL Server tables
The query retrieves information from three SQL Server tables and relates the employees to their department name and salary grade, but only for those employees earning more than the average salary.
Case 4 is split into three cases and demonstrates the following:
Update statement, insert statement.
Case 4a demonstrates bind values and subselect. All employees in department 20 and one employee, WARD , in department 30 are deleted.
Case 4b provides an example of a simple UPDATE statement. In this example, employees are given a $100 a month salary increase.
Case 4c is an example of a simple insert statement that does not provide information for all columns.
Case 5 demonstrates data dictionary mapping. It retrieves all the tables and views that exist in the SQL Server database that begin with GTW .
Case 6 demonstrates the gateway pass-through feature which allows an application to send commands or statements to SQL Server.
This case demonstrates:
A pass-through UPDATE statement using bind variables
A pass-through SELECT statement
Case 6a provides an example of a pass-through UPDATE statement with bind variables. In this example, the salary for EMPNO 7934 is set to 4000 .
Case 6b provides an example of a pass-through SELECT statement. The data that is returned from the SELECT statement is inserted into a local table at the Oracle database.
Case 7 demonstrates the gateway executing a stored procedure in the SQL Server database.
Scripting on this page enhances content navigation, but does not change the content in any way.
- Oracle Database Administration Case Studies
- Case Studies
Success Stories with Oracle Database Administration
Click HERE for Oracle APEX Administration Case Studies
Client: Leading independent, non profit research and development organization
Project: Oracle Database Training for the JUNO Mission
SkillBuilders’ John Watson began teaching the Oracle DBA team at this Research Organization starting in 2014; the training and mentoring is ongoing and continues to this day. They’ve attended training in Oracle RAC, Security, Data Guard, Tuning, RMAN and more. (Additionally, developers in the organization have attended Java training.) More recently they’ve hired us in a consulting role to assist with, for example, tricky RMAN issues. Almost nine years of successful services delivered and counting!
Testimonial from Chris
“We have worked with John Watson during on-site engagements as well as numerous online classes. His depth of knowledge and his ability to teach complex topics is simply outstanding. Both my colleague and I are seasoned DBAs but we always gained a vast amount of new knowledge to be applied in our daily tasks and corporate operations.”
Client: Silicon Valley Power
Silicon Valley Power has provided the city of Santa Clara California with electricity for 125 years.
Project: Configure MAA on Usage/Billing Oracle Database
The primary project was configuring MAA (Maximum Availability Architecture): RAC-to-RAC Data Guard, between two ODAs (Oracle Data Appliances). Secondary projects included configuring Data Guard between two Windows systems, apply the ODA full stack patches, and setup an RMAN catalog database to manage backups for the whole environment. This was a lot of work for a short engagement, but we did manage to complete the project within budget. The result is a highly resilient environment for various databases, critical for the effective management of electricity usage and billing.
Client: Foremost Farms
Foremost Farms is a milk solids processing business owned by the dairy farm families who supply our milk. That’s what it means to be a cooperative – and it’s why our farmer-members have a vested interest in providing the highest-quality milk possible. They own Foremost Farms.
Project: Research running Oracle Data Appliance (ODA) in Virtualized Environments
This project was about investigating the ODA virtualized architecture and understanding how best to use it with single instance databases. Not because of RAC, because of licences.
You can run ODA as “bare metal” or “virtualized”. Typically, people who installed ODA more than, say, two years ago run bare metal: the two ODA nodes run normal Linux and the databases reside on ASM disc groups. This is all straightforward and should present no surprises to the DBA or the SA. However, most people installing now follow Oracle’s recommendation and run it virtualized. The configuration can strike you as bizarre the first time you see it. The nodes run Xen Linux (Oracle’s version, of course) and everything happens in virtual machines. The databases reside on ACFS file systems, and are created from ACFS snapshots of preconfigured databases. The networking may take a while to get your head around. In general, the virtualized platform is probably the way to go, but you do need to be a bit of a wizard with Grid Infrastructure and Xen to understand what is going on. There are many GI facilities that can be used for fault tolerance and failover without RAC, which is not something that Is very well documented. Perhaps it should be.
XIFIN is a health information technology company that leverages diagnostic information to improve the quality and economics of healthcare. The XIFIN technology platform facilitates connectivity and workflow automation for accessing and sharing clinical and financial diagnostic data, linking healthcare stakeholders in the delivery and reimbursement of care.
Project: Configure New Oracle RAC Database Environment plus Migrations and Consolidations
SkillBuilders Oracle Certified Master DBA John Watson assisted a medical insurer to move several Oracle databases supporting OLTP, BI, and batch workloads from single instance 220.127.116.11 to a three node 18.104.22.168 RAC, consolidating the applications into just two databases as we did it. The destination platform was Sun servers with infiniband networking and EMC VMAX storage: this is a pretty big environment with demanding service level agreements. We trained the DBA and Systems Admin staff and provided further support during the implementation. Problems encountered, identified, and resolved included storage issues and performance problems, and (surprise!) a couple of Oracle bugs.
Client: Shoe Carnival
Shoe Carnival is one of the nation’s largest family footwear retailers with its own brand of style. Headquartered in Evansville, Indiana, Shoe Carnival stores are located in 35 states and Puerto Rico, as well as online at shoecarnival.com.
Project: Oracle Data Guard Implementation on RAC Exadata
SkillBuilders was initially asked to get the in-house DBA team up-to-speed on Oracle Data Guard. So, SkillBuilders’ Oracle Certified Master DBA John Watson taught our Data Guard Administration class over one week to the in-house DBA team. After the class wrapped up, Shoe Carnival IT management, aware of the time pressure and critical nature of the project, asked John to implement their first Data Guard configuration. Specifically, configuring one of their production databases for Data Guard and RAC (RAC -> RAC between two Exadata’s) and of course thorough testing including switch-over (failover) and switch-back.
Client: Kyocera (formerly SLD Laser)
SLD Laser – Brighter. Smaller. Safer. LaserLight delivered. The next evolution in lighting has arrived, shining brighter and farther than ever before. LaserLight surpasses the limits of LED to deliver safe, high luminance white laser light from an incredibly small light source. It’s enabling entirely new opportunities with SLD Laser’s GaN laser technology.
Project: Design and Implement Secure Channels for Hybrid Database Connections in AWS for 24×7 Multi-State Communications
Starting point: SkillBuilders had previously designed and implemented an Oracle APEX stack on Amazon AWS for the company’s internet-facing APEX applications. The company also has critical information in Microsoft SQLServer Databases.
Now we were tasked with constructing secure communication channels for Oracle and SQLServer connections between the company’s headquarters and three remote locations.
Our team configured a VPN Tunnel between three of the customer’s remote facilities and the AWS subnet where SkillBuilders had previously deployed their Web Tier and DB machines in a private Virtual Private Cloud (VPC). The environment consisted of a Customer Gateway which describes the onsite router (in this case Cisco), its external ip, software & version, and their LAN subnet; this is then connected to an AWS Virtual Private Gateway which describes the AWS LAN, which are connected with a “Site-to-Site VPC Connection”.
These VPN Tunnels provide fully encrypted, 24×7, two-way traffic, including sqlnet & odbc connections between Oracle and Microsoft SQLServer Databases.
Large Advertising & Marketing Services Company
The project: build a secure Oracle APEX environment on Oracle Cloud for the company’s internet-facing applications.
Starting point: one virtual machine running a pre-created database with APEX installed End point: the full Apache/Tomcat/ORDS/APEX/database stack, SSL enabled Budget: 20 hours
As with any cloud provider, it takes a while to understand how to use the cloud infrastructure, particularly the networking and security. Once you have got past that, you can start work. First, the Oracle Enterprise Linux default configuration isn’t good enough. You need to install a graphical desktop, and also various RPMs and update OpenSSL. The firewall needs some configuration too. Then the usual: download and install Tomcat; download ORDS, configure and deploy to Tomcat; download the Apache http server plus the APR libraries and compile. We added an OpenVPN server for SQL*Net and ssh access, so that only the Apache listening ports would be exposed to the internet. Everything configured for automatic start on boot up.
All done within budget.
Mid-Size American City
Designed and implemented Oracle Database Standard Edition Failover via intelligent use of scripting and various OVM and networking facilities. Implemented OVM (Oracle Virtual Machine, Oracle’s take on Xen) at two sites, including a DR solution such that database VMs at either site can failover to the other in the event of problems with minimal data loss. Zero data loss and full automation were not possible as only Standard Edition licences were available, but intelligent use of scripting and various OVM and networking facilities give the client something very close to that. Each site had two physical machines: one (with magnetic discs) to run the OVM Manager and be an NFS server, the other (with mixed-use and write-intensive SSDs) to run the VMs to be used as database servers. Using NFS repositories, we directed backups and archives from each site to the other site. A minor complication was that for licence compliance we had to use hard partitioning to restrict the CPUs available to the database servers. In the event of a failure at one site, new VMs can be launched from templates at the surviving site and the databases restored onto them. Not Data Guard level resilience, but pretty darn good.
Brown University Administration
Since 2008, SkillBuilders has trained Brown University developers and Oracle Administrators in new skills vital to the Universities success. This includes Oracle Database performance tuning, new features, RMAN, Enterprise Manager / Grid Control and Grid Infrastructure. For developers SkillBuilders has provided education in Java Programming, Servlets, JSP, XML, Ruby on Rails and more.
SkillBuilders designed and installed active standby using Oracle Database Standard Edition, which supports Soraa production line. SkillBuilders also provides Oracle licenses, in this case saving Soraa tens of thousands (perhaps hundreds) by advising customer Standard Edition would meet customer requirements.
We also implemented RMAN backups, Alert/Incident monitoring and general database tuning. We remain on-call for support issues.
North Carolina Department of Labor
In 2012, SkillBuilders provided the Department of Labor in North Carolina state government with a turnkey APEX configuration, i.e. everything they needed to start deploying internal and internet-facing APEX applications. This included database installation and patching, APEX and APEX listener configuration, Java container (Glassfish) installation and configuration and assistance with firewall updates.
SkillBuilders continues to provide Oracle Database, APEX and infrastructure support; patches, upgrades and troubleshooting.
SkillBuilders provides ongoing Oracle Database advanced administration training. This includes Oracle Data Guard, Administration and Performance Tuning training. One student, who provided a perfect 5.0 score in his post-course evaluation, exclaimed, “I really enjoyed John as my instructor. He was extremely professional, personal, and deeply concerned about us understanding the concepts of in this course.”
SkillBuilders teaches 12c Database new features to Oracle Corporation employees. We developed a custom course and have delivered it 12 times and counting! We also teach other subjects such as Oracle Database Performance Tuning and Data Guard Administration.
When Waters Corp software customers needed a tool to manage ASM disks, non-ASM disk, RMAN backups, imports/exports, monitor and send alerts, manage database users (e.g. password management), SkillBuilders developed an integrated Oracle APEX solution. The “Waters Database Manager”, or WDM, also provides other general management functions such as job scheduling and reporting.
Stretched (Extended Distance) Clusters / ASM
In 2010, Oracle Master John Watson was the development DBA (designer / implementer) behind the successful implementation of a stretched cluster (aka extended distance / geo-cluster). Here’s the story….
We all know what happens if the database that tracks passengers through security fails: the queues grow longer, and within minutes they stretch half way round the terminal. The database that tracks the baggage is as critical: thousands of bags stack up when they should be on the conveyor belts.
At this client (a large airport in Africa) it appeared that even Data Guard would not be adequate to keep things moving. Fast Start Failover with the Data Guard Broker is fast: it can initiate within seconds. But most DBAs will want to build in a delay of a few minutes. Then it takes time actually to switch over to the standby and reconnect all the sessions. That would have been too slow, given the speed with which chaos would escalate in that environment. And it would need Enterprise Edition licences.
RAC looks like the answer: near instantaneous failover of services and sessions from one instance to another if you lose a node. But it doesn’t protect you against losing the site. Or does it? Yes, if you set up a stretched cluster. At each of two airport terminals, we had a database server and a storage array, connected through a fiber switch. A separate ethernet gave the terminals on the security desks and the baggage scanners access, load balanced across both server nodes. ASM handled the mirroring. Losing a server node (not uncommon given erratic power suppliers and unreliable networks) caused all the broken sessions to reconnect (yes, we automated that) to the surviving node with a break in service time of only seconds. When the node came back online, ASM would re-synchronize the database copies. It really worked.
And, perhaps best of all, it worked with Standard Edition licences.
Manual Failover with Standard Edition
In 2013 we configured a manual failover on Standard Edition for an LED light manufacturer. This new company uses their Oracle database to keep their production lines moving – but the cost of Enterprise Edition and Advanced Data Guard is prohibitive and unnecessary, since our manual failover implementation will keep them up and running with 15 minutes of a failure on the primary server.
Advanced Queueing and Resource Manager
In 2008, Oracle Certified Master Administrator John Watson used his knowledge of Oracle Database features to create a clever solution to a performance problem. The client was maxed at 48,000 transactions per hour; John used Advanced Queuing and Resource manager to quadruple that. Read on…
SMSs (i.e. texts) make money – if you can process them fast enough!
The volume of SMS traffic is unbelievable. This isn’t just people voting for a Simon Cowell show: it is requests for ringtones, wallpapers, jokes, games, who knows what else. Millions of SMSs, and they all generate income. Or they should.
At this client (a cellphone network operator in Africa) they were processing maximum 48000 requests per hour. More than that could not go through the database: the sessions just hanged. When the SMS aggregators were throwing messages at peak rate to the application servers, the queues would shoot out, and eventually the application servers would start dropping messages. Bad, very bad: that meant not just losing a sale, but probably losing a customer.
What is the answer to a scalability problem? RAC, of course. Well, not always. We investigated the transactions, and the problem was enqueue contention: the business model relied on a pyramid hierarchical sales structure, so concurrent transactions at a low level would block each other at a higher level. We had to explain: RAC can’t help with TX enqueues, and might make things worse. The answer: re-design the transactions to use Advanced Queueing. The low level updates put a message on a queue, and commit. Fast. Then de-queueing every few seconds would process a group of a several hunded updates as one logical transaction. Problem solved: now processing up to 160000 requests per hour.
But they wanted more. Rolling out a banking application for transferring funds from one subscriber account to another, all with SMSs, looked like doubling the request rate. And the box was already running at around 90% utilization. It was trime to look at processing priorities, and what else was running on that box. The “what else” was reports. Such as analyzing the effect on sales of a radio advert – in real time. This is important and the information needs to be timely, but does it really matter if the analysis take three minutes instead of thirty seconds? This is where the Resource Manager comes into its own. Not widely used and understood (a lazy DBA can just throw more hardware and Oracle licences at the problem) but in this case perfect. By setting up appropriate priorities, every sales request would respond fast no matter what the workload, and at times the reports would degrade. Just another example of how enabling a couple of advanced features can save the need for more hardware and more licences.
Login with your site account
Not a member yet? Register now
Register a new account
Email preferences, select your interests.
I accept the Terms of Service
Are you a member? Login now
Oracle Migration Case Study
The customer wanted to migrate to Oracle Cloud Infrastructure for its ability to retrieve faster queries and lower their cost of ownership. A summary of the requirement was to move from on-premises to Oracle as quickly as possible minimizing cost and risk.
The objective was to migrate the on-premises oracle database to the Oracle Cloud Infrastructure. Oracle offers the most comprehensive database migration services in the industry. However, the strategy for moving on-premises applications and their data to the cloud depends on your environment, customized configurations, and the applications that use data sources. We can migrate an on-premises Oracle database to Oracle Cloud Infrastructure by creating a backup of the on-premises database in Oracle Cloud Infrastructure’s Database service.
Matching data types to Oracle Cloud data types.
Moving 100% of the data to Oracle Cloud and validating that all data has been moved successfully.
Performing these steps quickly, cheaply, and with 100% reliability.
The client wants to migrate the business to the cloud because of the high availability, scalability, and easy management of resources.
Migrating to the cloud is not a one-off decision. It has long-term and company-wide implications and therefore, the decision to move to the cloud should not be made in isolation.
1. The migration of the database involved research, testing, planning, deployment, and monitoring.
2. Research about the client’s infrastructure will be conducted on-premises environment.
3. Find out the best suitable environment in the client’s preferred cloud platform.
4. Provide information about other cloud platforms that would be suitable and cost-effective to the client.
5. The entire process will be under continuous monitoring to ensure the migration is going on smoothly.
Reasons to Migrate to Oracle Cloud
The customer faced numerous challenges while operating on on-premise infrastructure. Various challenges were faced while operating the server as well as while maintaining it, which forced the customer to decide migrating the business to an oracle infrastructure.
A few of the reasons for the decision are stated below:
- Traffic spikes handling
- Inability to scale server resources
- Sudden demands for operations
- High cost involved
- Inefficient processes
- The requirement for data storage
- Geographical distribution challenge
- Accessing application-related methods
- Disaster recovery
- Price of such inhouse disaster recovery processes
1. here are some factors that were considered during migration.
1.1. On-Premises Host details
1.2. Database Size
1.3. Network Bandwidth
1.4. Database Name, version, edition, character set
1.5. Downtime estimation and planning
1.6. Performance analysis of infrastructure, database
2. Preparation of the back-out plan
Under any circumstances, if the client wishes to not go for the cloud, we will be ready with the back-out plans to implement at any phase of the migration.
3.The key tool
Terraform tool will be used for building, changing, and versioning infrastructure safely and efficiently. Terraform runs in a consistent and reliable environment, and includes easy access to shared state and secret data, access controls for approving changes to infrastructure, a private registry for sharing Terraform modules, detailed policy controls for governing the contents of Terraform configurations, and more.
To migrate the on-premises environment we must create few services in Oracle Cloud.
Using Terraform we will manage the following services:
1.1. Users, groups, compartments, policies.
1.2. Virtual Cloud Network, subnets, route tables, security lists
1.3. Launching the compute instances
1.4. Creating and adding block storage to the compute instance
Start network Peering. We will peer the On-premises network with Oracle Cloud virtual cloud network.
Using the RMAN active duplicate method we will build the standby in the compute instance.
Announcing downtime. To perform the cut-over operation we must announce the downtime.
Do fail-over operation to perform the role changes. The standby database (in OCI) will transform into the new primary database.
Performance Monitoring of the entire stack (Infrastructure, DB, and Application) during the migration.
- In case, any redo has not got shipped to the new primary database, we will ship those redo log files to make sure there is no data loss.
- We will enable the alarms and health checks for monitoring the services in the cloud.
- Verify the connectivity between the application and database and test the connectivity of applications from the public internet.
- Routing the incoming connections to the cloud environment.
Benefits After Migration
- High Availability
- Highly Scalable (Horizontal and Vertical)
- Easy Resource Management
Final Results of Migration
- Better work-life balance
- Better storage
- Faster setup
- Less maintenance
About Banyan Data Services