Oracle Database Administration Case Studies
Click HERE for Oracle APEX Administration Case Studies
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. We are continuing to grow!
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: 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.
Santa Monica College
SkillBuilders completely renovated Santa Monica College’s (SMC) Oracle APEX Application known as “MyEdPlan”.
MyEdPlan is an internet-facing application that allows students to create their educational plan based on SMC enrollment requirements. It is one of the important applications that SMC students use.
MyEdPlan is built entirely using the latest techniques (Apex, HTML5, CSS3, Bootstrap, drag and drop, etc.) and works on the PC and mobile devices.
Due to SkillBuilders extra attention on the user-interface with intuitive operations, the student is now able to create a complex education plan with almost no online nor counselor-based assistance. The system helps the student during the entire process the the creation of their education plan and immediately alerts the student when date/time conflicts occur, non-compatible or non-compliant courses are selected or similar situations occur.
Because of the specific nature of an education plan and the large amount of context information that is needed, using standard design patterns were no longer sufficient. Therefore new UI components were designed and built that are suited for the functions they need to support. Also a completely new color-design was created that suits a modern college as the SMC.
The application is 508 American Disabilities Act compliant, thus the application is not only operable with the mouse, but also operable solely using the keyboard. The application also supports a screen reader.
Since 2011, SkillBuilders development team has been a key asset and extension to Stanford’s Oracle APEX projects. Our personnel provided systems analysis, development and provided a source of ongoing application review and best practices. Projects include Conflict of Interest, SERA, and FASA. In addition to application development, SkillBuilders has also provided on-site instructor-led APEX training to Stanford University developers.
Stanford University School of Medicine
In 2012 and 2013, Skillbuilders provided an Oracle APEX solution (analysis, design and development) for the Stanford School of Medicine, Information Resources & Technology Data Center.
The center runs a server hosting service for non-IRT School of Medicine departmental servers. Prior to SkillBuilders solution, the administration of the information and processes needed to support the full life-cycle of the servers was highly manual employing spreadsheets, email records, wiki notes and help-desk tickets. Skillbuilders Oracle APEX solution consolidated the information into a single repository providing the ability to track all processes from initial customer inquiry, through server and software installation, configuration, human resource planning, warranty, maintenance and service charge allocation.
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.
Brown University Facilities Management
In 2008, SkillBuilders began the design and development of an Oracle APEX-based Energy Efficiency Management Tracking and Workflow application. This internet-facing APEX application (called “Brown Energy Efficiency”, or “BEE”) allows vendors to input estimated energy savings for planned construction and upgrade projects for university buildings. The data is then used to calculate Greenhouse Gas reduction and Internal Rate of Return for each project. Management is automatically notified when new projects await approval. Approved projects move through various workflow stages, including comparison of actual savings against original projections. Today, “BEE” remains a mission critical application for Engineers, Project Managers and vendors at Brown’s Facilities Management division.
In November of 2008, SkillBuilders was an instrumental partner in the deployment of this APEX application. The technology at the time included Oracle APEX 3.2, Oracle 9i, Oracle Application Server (OAS / iAS), Apache FOP. Since, SkillBuilders remains the sole Univertiry partner for BEE application enhancements and upgrades.
In 2011, Skillbuilders assisted Brown University in developing an Oracle Application Express application to add extensive reporting functionality to its legacy facilities management system. The application leverages APEX’s powerful Interactive Reports, one of the key features of APEX.
SkillBuilders also provides ongoing data center support such as Server Migrations and Database Tuning.
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.
Federal Bureau of Investigation (FBI)
On-site (Washington, DC) Oracle APEX training for experienced team of developers. Customer testimonial:
“We had a great APEX class with Tyson Jouglet. He was very knowledgeable and provided an informative class that was interesting for the students. We are grateful for his patience with our technical hurdles and appreciate that you had a back-up plan ready to go if needed. Thank you for your very professional services and we will certainly keep you in mind for any future training needs”
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.
In addition to training BI developers Oracle database, SQL and APEX development, SkillBuilders provides on-going application design reviews, planning and development assistance. This greatly increases adherence to APEX best practices.
SkillBuilders also integrated Oracle BI Publisher with BI APEX applications.
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.
GE Capital calls on SkillBuilders for highly reviewed Oracle APEX and UNIX training. For example, one UNIX course taught to 15 GE Capital technicians by expert Mick Hosegood scored an average of 4.85 (out of 5). One student commented,
“I thought the instructor was excellent. Mick Hosegood did a very good job. The course content and materials were also well done!”
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, best of all, it worked with Standard Edition licences.
Lino comment link needs to reference tutorial page
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 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.