State Farm Insurance Company - Bloomington, IL
August 2015 – Present
Oracle Database Administrator
Configure Data Guard on both standby and primary for numerous production databases.
Troubleshoot and maintain ORACLE Data guard environment where TBs of log shipping and apply happen every day.
Highly experienced in Cloning of Oracle databases from multi-node to single-node and single node to multimode.
Troubleshoot Database Performance Issues, developed custom scripts and implemented standard procedures to troubleshoot Live Production issues by leveraging ORACLE ASH and AWR Data. Managing, Monitoring and optimizing the performance of the production databases on a periodic basis using OEM, AWR, ASH, TKPROF 10046 traces etc reports and providing recommendations from SQL Tuning Advisor and SQL Access Advisor.
Optimal Database parameter, Capacity Design for Heavy Loaded High End OLTP systems.
Database Capacity Management (Working with Storage teams for ASM Disks) using automated scripts/processes and Reports.
Experience managing Tablespaces, Data Files, Archive Logs, Redo Logs, and Standby Redo Logs and Logical Database Objects.
Performing tuning of application using CBO, SqlTrace, Tkprof, Explain Plan, Statspack, AWR, ASH and ADDM.
Proficient in writing UNIX Shell scripts to automate business process and daily backups.
Experience with Oracle OEM cloud control 12c, Oracle EBS R12 for Financial management and supply chain management.
Exposure to new 12c Features, Container and Pluggable Database, FAR-SYNC Instance, Cascade Redo Transport Destination, allowed usage of SYNC and NOAFFIRM to extend Data Guard Range, Moving online Data file and so on.
Troubleshooting ORACLE RAC issues using ORACLE cluster ware and Database logs.
Applied the Clusterware (GI), Database (RDBMS) Patches like PSU patches and one-off patches (for Bug resolution) using Opatch, Data Patch and AutoPatch utilities.
Upgraded the RAC and standalone databases from 10g (10.2.0.4 and 10.2.0.5) to 11g (188.8.131.52, 184.108.40.206 and 220.127.116.11) as part of performance improvement by utilizing the new features. Also upgraded 11g (18.104.22.168) Databases to 12c (22.214.171.124).
Creation, Configuration and Setting up of RMAN Catalog, databases and registering target Databases with the Catalog.
Worked on setting up of Goldengate 12c Integrated Capture on Exadata Servers for replication purposes from OLTP system to Downstream systems. On Non-EXADATA servers we used separate mining database Goldengate replication and on EXADATA source databases is used as mining database too.
Installed Goldengate version 11 and version 12 with latest patches applied on DBFS filesystem and ACFS filesystem, also working on testing new OGG 12.2 new features available.
Implemented Goldengate integrated replicat and co-ordinated replicat(available from OGG 12c) to improve the performance of data replication to targets
Applying upgrade patch, maintenance and interim (opatch) patches on all the databases and using datapatch for Oracle databases 12c.
Refreshing Dev and Test instances with data from Production on a regular basis. (Object, Schema and Database refresh)
Developed PL/SQL packages, DML, DDL, Oracle tables, Stored Procedures, functions, cursors, triggers and UNIX shell scripts.
Resolving issues related to Network Congestion, Memory (SGA and PGA),CPU & IO and other oracle related issues.
Migration of database from one server to another server.
Responsible for setup and implementation of 2 Node RAC on Linux in 10g and 11g R2.
Responsible for configuring datagaurd physical standby database setup for disaster recovery and high availability and handling log gap issues between primary and standby.
Implemented ASM on RAC and stand-alone databases. Managing disks and disk groups with ASM.
Done migration of non ASM database to ASM database
Handling and debugging of high priority and sudden database service interruption issues like service interruption due to ORA-600 errors in the dump and database hung due to ORA 7445 errors and restoring the service to normal and providing efficient root cause analysis.
Upgraded standalone and RAC databases from 126.96.36.199 to 12c on QA database for proof-of-concept
Monitoring free space in tablespaces, space requirements of tables and indexes.
Experience with testing and pre-production and production of Oracle 10gR2 with 2 node RAC on Red Hat Linux with ASM
Vast Experience with RMAN to implement Backup/Recovery strategies and clear understanding of the data structures and architecture provided by Oracle for Recovery in all kinds of Disasters.
Capacity and Reseource Planning for OLTP environment- Allocating system storage and planning future storage requirements for existing and new databases.
Developed database system build sheets, code review check lists, code promotion process documents, outage reporting documents.
Sizing the SGA, db buffers, shared SQL area, log buffer based on the requirements.
J. J. Keller & Associates, Inc. - Neenah, WI
August 2012 – July 2015
Oracle Production Support Database Administrator
Active member in making enterprise hardware infrastructure related decisions with IT management
Involve in meetings with top IT management to discuss platform migrations
Installed, Setup, Configured Oracle version 10g Release2 64 Bit on Solaris 10 and HP-UX11
Installed, Setup, Configured Oracle version 11g Release2 64 Bit on SLES 10
Migrating from Oracle 10g Sun Solaris 10 to Oracle 11g SUSE Linux (SLES) 10
Built Oracle MAA Physical Standby and Logical Standby and configure Data Guard Broker for Disaster Recovery purpose.
Perform planned switchover and failover to physical standby database with and without Data Guard Broker to verify availability status.
Transferring data from one server to another server by Export/Import Data Pump and Normal Export /Import utilities
Refresh Test/Dev/Quality/Training/Sandbox environment from Production for Apps Team
Regularly applying all CPU & PSU Patches to All Oracle Databases using Opatch
Creating and monitoring TAR/SR with Oracle Support
Extensively used Oracle 10gR2 new features like automatic SGA tuning, enhanced RMAN with Flash Recovery Area, Block Change Tracking, Merge backup, Oracle Data Pump, big file tablespaces, new EM Grid Control, Database Control, Studio Control, AWR, ADDM, SQL Tuning/Access Advisors, Flashback Database and several others
Implemented Oracle Streams for propagation and management of data, transactions and events in the data stream to ensure smooth and transparent data flow using Oracle Streams methodology of Capture, Staging and Apply from the production Database to the Target database.
Rebuilding of Indexes for better performance, maintenance of Oracle Database
Tuned parameters for a large monthly SQL* Loader job resulting in 90% reduction of operation time.
Worked with Blobs and Clobs, Materialized views with Query Rewrite, Bitmapped Indexes, etc
Database monitoring/maintenance, replication process and performance tuning are done using OEM (Oracle Enterprise Manager).
Experience in implementation of enterprise databases on enterprise Storage Solutions using Storage area Networks (SAN) and Network attached storage (NAS).
Documented the Standard Processes for peers to follow.
Experience in system Monitoring and Database Performance Tuning using Explain plan, SQL Trace, TKPROF, AWR and also Cluster Interconnect Tuning (RAC).
Provided valuable inputs for various performance tuning issues.
Proficient in performance tuning using Explain Plan, STATSPACK, TKPROF, AWR, ADDM. Performance Tuning of the database (Memory Tuning, I/O Tuning).
Refreshed/cloned databases using RMAN utility.
Performed Database upgrade from Oracle 9i (188.8.131.52) to Oracle 10g (10.2.0.5) and to Oracle 11g (184.108.40.206) RAC database.
Successful in applying RDBMS patches, security patches for improving the security and functionality of the databases.
Achieved optimized performance for the databases by rebuilding indexes, on tables and indexes.
Environment: Oracle 220.127.116.11, 18.104.22.168, IBM AIX V6.1, Suse Linux, RAC, ASM, RMAN, AWR, ADDM, SQL* Plus, SQL*Loader, OEM, TOAD 10.0
5 years’ of experience in the field of Database Technology with experience in designing, developing, implementing Databases, Web development and Application development.
Administer over 600 databases across Development, QA, UAT and PROD environments via use of command line and Oracle tools-Oracle Enterprise Manager Cloud 12c.
Manage large complex Oracle RAC clusters with 50+ applications.
Installed Oracle 11g RAC and Upgrading Oracle 10g RAC databases to 11g databases.
Upgraded databases from 11g to 12c.
Set up Active Dataguard Configurations for oracle database 22.214.171.124 for thirty databases whose size varies from 10TB to 30TB on a 6 node RAC.
Designed GoldenGate configuration for databases & implemented filters with rules to meet the business usage which will reduce load on the production databases for better performance with replication on multiple sites.
Migrated and upgraded Oracle 11gR2 Databases from Sun Solaris 10 to Red hat Linux 5 using Goldengate.
Worked on SQL Tuning and how to use the available tools outside of Oracle Enterprise Manager.
Worked on Performance Tuning, identifying large objects, analyze those objects and recommend changes to improve performance.
Updated MVIEWS and test code to improve performance.
Identifying objects no longer accessed and remove those objects which results in saving space in the databases.
Worked on moving data from one storage area to another.
Installed and Administered Oracle 11g & 10g RAC software with ASM.
Had fixed several performance tuning issues which relates to RAC & standalone databases by recommending better solutions that could save cost, virtualization, and resources.
Implemented fast start failover on databases and configuring disaster recovery site using dataguard. Also proficient with new 11g features such as Active Dataguard and Snapshot standby database.
Implemented & Managed Physical Standby and even Configured Automatic Storage Management (ASM).
Applying Patch set, Critical Patch Update (CPU)/Patch Set Update (PSU) and One-off Patches using OPatch and run Installer under UNIX and Windows.
Performance Tuning of Oracle database using OEM, SQL analyzer Stats pack, AWR, ADDM, ASH, RDA, Explain plan, TKPROF, SQL TRACE and Oradebug.
Data transferring using Export/Import utilities and SQL*Loader.
Expertise in creating and maintaining databases to Oracle Optimal Flexible Architecture (OFA) standards in very large scale database environment (VLDB) and mission critical OLTP and DSS systems.
Performed database tuning by using various tools such as ADDM (Automatic Database Diagnostic Monitor) from AWR (Automatic Workload Repository), OEM (Oracle Enterprise Manager) Cloud control and STATSPACK.
Performed SQL Tuning using Explain Plan, TKPROF with SQL trace and STA (SQL Tuning Advisor). Coordinated with System administrators, application developers and other DBAs.
Performance Tuning i.e. tuning applications, shared pool, I/O distribution, rollback segments, buffer cache, redo mechanisms
Installed Oracle 10g,11g RAC software with ASM & ACFS File systems
Resolved database network problems using Oracle Net Configuration Assistant, Oracle Net Manager, Netstat and ping utility.
Involved in writing stored Procedures, Functions and Triggers in PL/SQL with developers to interface applications with database system.
Bachelor of Science in Applied Accounting
Oxford Brookes University, London England
Operating Systems: HP-UX 10.x, 11.x. Sun Solaris 2.5, 2.6, 8, 9, 10. AIX 5.1, 5.2, 5.3, 6.1 RHEL 2.1, 3.x, 4.x, 5.x.Linux Enterprise Server 9, 10. Windows 95/98/NT/2000
RDBMS: Oracle 10g, 11g, 12c
Languages: SQL, PL/SQL, UNIX Shell Scripting
Tools: TKPROF, EXPLAIN PLAN, STATSPACK, AWR, and ADDM, Toad, Putty, SQL Developer, OEM GRID CONTROL, WinSCP, Cisco VPN Client
Database Utilities: SQL*Plus, Oracle Enterprise Manager, Real Application Cluster, ASM, Transportable Tablespaces, SQL*Loader, RMAN, DataPump Export and Import and several others.