Oracle Application DBA – Daily, Weekly, Monthly Tasks

A general Interview question ” What is your day-to-day tasks ?” Sometime it becomes difficult to answer, but here is a cheklist what a Apps DBA or a DBA does.

Being working as an DBA you should be having some checklist for the daily,weekly,monthly tasks. From my own experience I have compiled a list but I am sure

you can suggest some more task and I will append it in this list.

This is what a life of Apps DBA is !!! If I have missed out anything please comment so I can append the list.

So here it goes

Daily Database Tasks

•On database startup, pin objects in the Shared Pool

• –AD_TOP/sql/ADXGNPIN.sql => ADXSPPIN.sql

•Consider these tasks for monitoring on an HOURLY basis throughout the day

• –Users or processes that are holding exclusive locks
• –Open cursors
• –New Invalid database objects

•Verify all instances are online

• –Could be automated, or monitored 24/7 by Enterprise Manager

•Review Alert Log entries

• –Check for “ORA” errors or other unusual entries

•Verify success of database backups

• –Including archiving to tape or disk (depending on backup schedule frequency)

•Monitor Cache Hit ratio

•Run Stats pack

•Analyze Tables and Gather Statistics

• –11i Concurrent Request “Gather Schema Stats”
• –Run at off-hours to reduce table access or performance problems; ensure no other concurrent jobs are running at the same time.

•Verify resources for acceptable performance

• –Free space

1. Operating System “TEMP” directories.
2. Applications APPLCSF (“log” and “out”) directories.
3. Tablespaces.

• –Rollback segments’ status.

1. ONLINE, not OFFLINE or FULL

• –Identify bad growth projections and space-bound objects.

1. •Segments growing at a rate that will exceed max_extent parameters
2. •next_extents exceed the largest extent the tablespace can offer

• –Review contention: CPU, memory, network, disk resources.

•Log File Management

• –Copy Archived logs to Standby database, roll forward

1. Can be automated; then your daily task is to confirm that this happened correctly each day.

• –Clean up log files

1. •User, Background, Core dumps
2. •Apache and Jserv log files
3. •Net8 logs
4. •AD utility log files

Daily Application Related Tasks

•Verify Applications services are on-line

• –Successful Log in to Apps
• –Concurrent Managers are up & running

•Concurrent Request Management

• –Identify problem requests and chec “log” and “out” files for indications of the issue.
• •Follow up troubleshooting if required
• –Purge Concurrent Requests (all requests over 14 days old or depending on organisation policy)

Purge Obsolete Workflow Definitions

Weekly Database Tasks

•Verify scheduled scripts are completing successfully

•Look for objects that “break the rules”

• –Naming conventions, storage parameters, etc.
• –Check for fragmentation/row-chaining
• –Dictionary-managed tablespaces pct_increase set to 1%
• –Check interim tables defined with their own tablespaces

•Look for Security Policy violations

•Verify remote-access tools are on-line

•Synchronize “Test” and/or “Development” instances with “Production”

• –Verify patch-level differences between instances

Monthly Tasks

•Monitor for harmful growth rates

• –Rapid Table and Index growth

•Performance and Tuning

• –Look for I/O contention
• –Watch for poor performance caused by fragmentation
• –Resolve these issues as needed

•User maintenance

• –Password changes
• –FNDCPASS command

•Patches

• –Review available database patches, particularly those related to security bugfixes.

•Prediction

• –System performance issues

1. CPU and RAM usage
2. Network performance

• –Database space utilization

1. •Active instance requirements
2. •Archives & Backups

Monthly Application Tasks

•Concurrent Manager Tuning

• –Adjust the number of “target” processes
• –Create special Concurrent Managers for specific kinds of requests

•Patches

• –Review latest available patches

1. •Individual Patches
2. •Family Packs for installed modules