Job Title: Senior DBA Locations: Remote / Home Based Duration: Long Term
Description:
Job Duties:
- Design and develop the databases as per the application workload and client requirements – 25%
- Interact with architects and key client stakeholders for the purpose of business requirements to understand the load and stress of the database
- Specify the memory and other parameters of database by considering the concurrent users accessing the database and the level of parallelism that is required for parallel operations.
- Create new database structures as per business and application flow requirements
- Configure Oracle Enterprise Manager Grid Control or other monitoring tools to have the database monitored and alert the teams when the thresholds breach.
- Create the standby databases in a different datacenter, according to the Business requirements and applicable licenses, for Businesses which requires querying to be offloaded from primary database, Active data guard can be configured at the additional license pricing, for all other requirements mounted physical standby can be configured.
- For high critical databases, inform the Business about the necessity of having data guard broker, as it can failover the primary database without manual intervention to desired standby database, in case the primary database is unavailable, and it decreases the database downtime.
- Configure the backup jobs and other maintenance jobs using Autosys or Oracle Enterprise Manager Grid Control and configure notifications incase the same fails.
- Configure block change tracking, so that the amount of time required for backups and also resource consumption of weekday incremental backups of databases decreases
- Implement database backup strategies using RMAN and Flashback utilities and perform database recoveries
- Create High level and Low-Level Design documents for replication, patching and failover on high availability environments.
- Implementation of Data-replication tools like Share plex & Golden-Gate and to configure database replications across platforms using both unidirectional and bidirectional approach
- Setup the TDE wallets to enable the encryption of databases in public cloud, and periodically rotate the wallet keys.
- Carry out detailed analysis to understand requirements and devise plan for database migrations and upgrades – 25%
- For database migrations from On-premises to On-premises or from On-premises to Cloud, design the process of migration based on the size of database and down time allowed for migration. For smaller databases export, import or export data pump, import data pump can be considered and for larger databases rman, golden gate or even building the new database as standby and performing switchover will result in lesser downtime.
- Upgrade the databases using the Database Upgrade Assistant or Manual upgrade method to keep the database version up to date and get the required support from Oracle vendor for issues and also to get new bug fixes or patches if required.
- Use Real Application Testing (RAT) to capture the real time work load on primary and to replay the same on test database of migrated environment, to analyze the performance of queries using SQL Performance Analyzer (SPA) and if any regressed SQL execution plans are found they can be improved before we perform the actual cutover.
- Also, any dependencies like materialized views or database links on the migrated database [if it is part of a distributed environment] dependencies must be noted before the migration and should be configured on the new environment to have the business run efficiently.
- If there are any replication tools configured ex: golden gate on the old environment, they must be configured for the new migration environment.
- Migrate the Db2 databases to Oracle 12.2,19c using Sql Developer version 17.1 using both online and offline migration techniques depending on the size of database
- Cluster design for high availability of database instances - 15%
- For Businesses which require cluster database, identify the similar work load patterns and create cluster services to service them better, also configure high availability of cluster services, so that they run even if few nodes of cluster go down for any reasons.
- Ensure that alerts or notifications are configured to notify the team for any cluster resource changes using enterprise manager grid control or standard monitoring tool.
- Changes in the cluster services status should be notified using shell scripts and user callout scripts so that if any cluster service goes down for any reason, appropriate service can be started on other available nodes and ensure that the performance of user connections shared by the service are not affected.
- Configure the load balancing advisory for cluster services using Fast Application Notification, so that the load of the cluster database instances is notified to services and user connections are appropriately routed to the lightly used database instance.
- Configure Transparent Application Failover and Application continuity, so that both DML and queries of sessions can be failed over to existing nodes incase if the current nodes become unavailable for any reasons.
- Backup in cluster database can be configured to utilize all cluster instances and run in parallel to reduce the time needed for backup and utilize all database instances equally
- Optimize and tune database/application performance – 20%
- Recommending indexes to improve the performance of queries
- Creating SQL profiles or SQL Plan Baselines for the reported queries with plan fluctuations picking the bad execution plans, to fix them to use the best available plan with the optimizer with every execution
- Analyze the segment advisory reports to shrink the segments and release the space. This decreases the space usage, also increases the efficiency of queries which use the objects.
- Generating AWR, ADDM, ASH reports for incident durations and comparing it with the good run timelines to find out RCA of any application job slowness or if there is a need to change/increase any database parameters
- Creating knowledge repository on the Design and Implementations, best practices and lessons learnt for the future use.
- Preparing and reporting the status metrics of the project and provide assistance to the database support teams for all production database critical issues.
- Project Coordination/Liaison – 15%
- Work on day-to-day activities including maintaining databases and see they are highly available.
- If for any reason entire database is running slow, gather the automatic workload repository snapshots and see the event which is contributing to the slowness and remediate the same.
- Creating different database objects such as tables, indexes, constraints, synonyms, views, packages, procedures, and functions etc. as per application requirement and provide required support to application teams.
- Investigating user issues encountered at the application front-end and recommending changes to the application codes or performing the changes required in database to address the same.
- Handle Business continuity i.e., disaster recovery exercise by performing planned switchover of databases from primary to standby using sql or data guard broker, and ensure that the standby is capable of handling failover scenarios in case of real situations where primary fails.
- Identify the object usage, and compress the least frequently used objects by seeking business and application team approvals to reduce the disk usage.
- Implement new features of database like DDL logging to better track the modifications of application objects, use data redaction to mask critical data on lower environments, transparent data encryption to encrypt the data at rest and make it accessible only for authorized connections.
- Use Service fallback for newer 19c databases to preferred instances when they are available again, this helps in maintaining the services on the desired nodes, there by the users are optimally serviced.
- Tools and Technologies Used: Oracle 12c, 18c, 19c Oracle Enterprise Manager Grid Control 13c, Oracle Golden Gate 11g and 12c, SQL, Unix Shell scripting, Oracle ASM, ServiceNow, Toad, Putty, SQL Developer, Win SCP, Oracle database utilities (SQL Loader, RMAN, Data Pump SQL Performance Analyzer, SQL Replay, Transparent data encryption - TDE), TAF- Transparent Application failover, Fast Application notification- FAN, Exadata
Responsibilities:
You will work on full cycle database administration on multiple database technologies hosted on both in house and cloud platforms. You will interface with multiple development teams and support their project life cycles while ensuring that the databases are available, recoverable, secure and meeting the performance expectations. You will also mentor and assist junior staff and contribute to projects based on new or changed technology. Support both the objectives from the development teams and the Corporate/Departmental goals in the areas of implementing standards across the team for database deployment, recoverability, performance, compliance, and security. Support the migration of databases from in house datacenters to both the AWS and Oracle cloud environments. In this capacity you must either possess the skills to work with these cloud providers or rapidly acquire these skills on the job. Release deployments typically occur after normal business hours and on weekends. Additionally, you will be part of a DBA team providing rotating 24x7 pager support. You will provide such after hours and weekend support to both the applications directly assigned to you and other applications supported by the Team. You will work multiple teams, particularly with the Development, Security and Systems Administrators and any other interfacing groups. You will interface with both development and monitoring staff located in countries outside of the United States and in other corporate entities supporting out sourced applications.
You'll need to have:
Bachelor’s degree or four or more years of work experience. Four or more years of relevant work experience. Oracle database administration experience Experience with an additional database such as but not limited to MySQL, SQLServer, Postgres, Sybase, DB2 (on LUW or z/OS).
Even better if you have:
A degree or Master’s degree in Computer Engineering and / or relevant technical disciplines. End-to-end knowledge of Oracle database technologies including Oracle RAC, Golden Gate, Dataguard, RMAN, and OEM. Experience tuning on multiple database technologies. Experience with problem analysis and tuning on multiple database technologies. Design and deployment skills. Ability to implement physical designs from logical designs. Unix level scripting skills Unix OS fundamentals. Experience programming in Java, Python or any other language.