Jobsite – Database Administration & Support

Role/Objectives

• To provide 24/7 Database Administration Services to all the Oracle databases and Application Support ensuring 99.99% Database Uptime and SLA response time of <=3 minutes
• In addition to proactive monitoring, recommend and implement scalability solutions
• Work closely with the development and support teams to help with performance optimization. Ensure that queries perform well even with sharp increase in number of active users/sessions due to TV Ad campaigns and applications
• Coordinate with Oracle Support to install security patches and bug fixes without impacting the uptime of the website
• Set up Data Warehouses
• Plan and Implement Oracle upgrades and Database Migrations

Database Maintenance Tasks

Utilizing advanced monitoring techniques, our team of certified Oracle Database Professionals have been providing Database support to Production databases since 2003. We have been providing Application support since 2007.

Database Maintenance Tasks include the following

• Automating of daily routine activities and monitoring the health of databases
• Frequent monitoring of alert logs and trace files and resolving problems encountered, if any
• Developed and implemented scripts to automatically send custom defined alerts to support team
• Set up customized alerting systems to send alerts on database related issues to Pagers/Cell phones of support team
• Oracle software installation and periodic upgrades to Databases
• Configuration and setup of Databases and recommend Backup Strategies
• Proactive Database and Application Performance Monitoring & Tuning by analyzing the database performance at peak hours and resolving the bottlenecks using tools like AWR, Statspack etc.
• Managing Backups and Regular Backup Integrity Testing and running restoration exercises to check database consistency
• Capacity Planning for Database servers
• Applying security and interim patches whenever required
• Emergency support for Production Database outages

Projects

1. Real Application Cluster setup, upgrades and migrations
2. Data Warehouse setup and maintenance
3. Setup of Fault Tolerant Solutions
4. Database Character set migration + implementation of hundreds of database upgrades & migrations

1. Recommendation and Implementation of RAC as a scalability solution

• Requirement- TV advert campaign launch, estimated post launch load higher by 30- 50%
• We suggested implementation of Real Application Cluster
• Challenges and Constraints- Limited time for test runs
• Implemented RAC within 7 days
• Unscheduled downtime reduced. Site uptime increased by 1 % in the subsequent months

2. Warehouse Database setup and maintenance

• Requirement was to build a Warehouse database that could be sync’d with the Live database
• We suggested that we write PL/SQL scripts.
• Complexity and Challenges- Warehouse database should respect all the constraints of live database
• Highlights- PL/SQL system that can be re-run several times as required in the event of the script breaking in between.
• Results- Warehouses have been in use since 2005 , reducing load from the live databases and improving response time. Reporting is being done from the data Warehouse now with a reduction in load on the OLTP database. This has also helped in trimming down the size ofthe OLTP database.

3. Setup of Fault Tolerant Solutions

Changes can be transmitted from the primary database to these standby databases in a synchronous manner enabling zero data loss, or in an asynchronous manner – minimizing any potential performance impact on the production system. This technology also provides an automated framework to witch over to the standby system in the event of a disaster or a corruption on the production site, or even during planned maintenances. We have set up several Standby Databases and provided Fault Tolerant Solutions to ensure recoverability in the event of hardware/database crashes. We have ensured zero data loss over the years.

4. Database Character set migration

• Requirement was to migrate a 10g RAC Database to new hardware, Upgrade the database from 10g to 11g, upgrade the Database Word size and time zone file as well as character set conversion to UTF8
• Challenges and Constraints- Several parallel upgrades on the live database with limited time to test and zero error tolerance
• We suggested parallel migrations for different characterset data and ran several test migrations before the live migration

Result: Overall migration time dropped to 24 hours from 48 hours after optimization and migration was very smooth and went as planned.

Testimonials

RAC Migration

“Our live database is now running on RAC and ASM on 2 new servers plus ASM enabled standby on a new server. All looks fine so far! Congratulations on a fantastic effort by Mike and the Wisdomleaf DBA team.”

– Cheers, Paul

Database Upgrade

“Many thanks for your invaluable help. The faultless upgrade of the database last weekend was yet another example of the great work we enjoy from Wisdomleaf. I am also very grateful for your flexibility and responsiveness to requests made at short notice and for your commitment to getting the job done and keeping our systems in goodshape.”

– Kind regards, Andrew