SL No. Most common Activity Performed by Database Team
1 Server load monitoring and Memory Utilization.
2 File system monitoring and space management (by deleting old archive) as per database growth.
3 Connecting SQL Plus console to perform Database activity.
4 Monitoring Database through SAM (Simple Automated Manager).
5 Current SQL Activity (To check which sql is running and finding query cost).
6 Tablespace Status and its Management (like tablespace creation, allocated sapce ,max extent,fee space)
7 Datafile addition based on growth of database.
8 Parallel process Status.
9 Lock - Transaction Lock check & Release.
10 Lock - DD Lock check & Release.
11 Lock - Table Lock check & Release Using Synonym.
12 checking Latches on database and release.
13 Checking Blocking session by other instance or on same instance and release it.
14 Lock -Unknown locks release.
15 Lock Clear Based on SOL_ID,
16 ABH Sessions and Finacle Branches and Sessions.
17 Resource Status ( Max process,Max sessions and other resource).
18 Events and it's details.
19 User account - Check user account status & privileges.
20 Create and drop Database user.
21 Lock and unlock Database User.
22 Grant and Revoke privileges to Database User (like select ,upadate,delete.insert )
23 Table analysis and including partition Table.
24 After Monitoring Database generating Health Status Report.
25 DC and DR Sync Status.
26 Checking Temporary tablespace status.
27 Checking Sequence information.
28 Monitoring Long running sql full table scans.
29 Database objects details(like Object Name,Object Type).
30 Archive gap between DC and DR database.
31 Index Information.
32 ASM Disks Status (like ASM Free Space,ASM Disks Status,ASM DBFile Info,ASM Disk Groups).
33 Release Space from ASM Disk Groups by removing OLD Archive.
34 Wait Session Monitoring.
35 Job Monitoring.
36 Generate Performance Statistics Reports.
37 Table Information in Detail.
38 Clear active inactive sessions before down the database.
39 Clear inactive session after requesting by application Team.
40 Configure Job using Crontab.
41 Running Rman backup on scheduled time and checking confirmation.
42 Complete database analysis on Sunday.
43 down the database to take cold backup after DC-DR in sync
44 index creation and drop as per CR.
45 Table creation purging and truncation.
46 Table insertion and deletion activity.
47 DC-DR drill activity.
48 TDE Encryption implementation.
49 Masking Activity.
50 Database Migration, Upgradation
51 Patching activity on ASM level and database level.
52 Sql tuning after bad sql observation.
53 Awr report ,ASH report,ADDM report.
54 Generating Global AWR report.
55 sqltrt report for problemtic Sql_id.
56 Database log monitoring .
57 Report Database refresh on daily basis.
58 Database restoration as per request on quarterly Basis.
59 Db link creation and its management ( like Tns entries ,user creation based on requirement).
60 check listener status.
61 raising oracle support for bug fix and other performance issue.
62 Filesystem Utilization monitoring and audit / listener log purging proactively
63 checking SQL plan for specific SQL if plan changes , need to set profile.
------------------
S.No DAILY ACTIVITY Resource Type
-----------------------------------------------------------------------------
1 Oracle Database instance is running or not L1/L2
2 Database Listener is running or not. L1/L2
3 Check any session blocking the other session L1/L2
4 Check the alert log for an error L1/L2
5 Monitor DBA jobs / DBA Scheduler Jobs running status and take proactive/reactive action.L1/L2
6 Check the Top session using more Physical I/O L1/L2
7 Check the number of log switch per hour L1/L2
8 How_much_redo_generated_per_hour.sql L1/L2
9 Tablespace utilization L1/L2
10 Detect lock objects L1/L2
11 Check the SQL query consuming lot of resources. L2/L3
12 Check the usage of SGA L1/L2
13 Display database sessions using rollback segments L1/L2
14 State of all the DB Block Buffer L2/L3
15 DataGuard Sync Status L1/L2
16 Temp Tablespace Size and utilization L1/L2
17 Long running session L1/L2
18 Database status & DB Role (Mount, Open, Primary /Standby) L1/L2
19 FRA utilization L1/L2
20 Wait event on databases, is it available? L1/L2
21 Ensure the flash backup and RMAN backup status [ In case of any other
backup strategies respective monitoring should be in place] L1/L2
22 Materialized View Refresh / Refresh Group / Job status monitoring and proactive action.L1/L2
23 Filesystem Utilization monitoring and audit / listener log purging proactively L1/L2
WEEKLY ACTIVITY Resource Type
-----------------------------------------------------------------------
1 Check the objects fragmented L2
2 Check the Chaining & Migrated Rows L2
3 Check the tables having more Indexes and take consultation as and when require with Next Level.L2
4 Check for unindexed FK L2
5 Check the objects having the more extents and take consultation as and when require with L3.L2
6 Check the free space at O/s Level L1/L2
7 Check the CPU, Memory usage at O/s level to monitor the threshold and proactively act on it. L1/L2
8 Check for UNUSABLE INDEXES & Invisible indexes L2
9 Check the objects reaching to it’s Max extents L2
10 Generating Statistics L1
MONTHLY ACTIVITY Resource Type
-------------------------------------------------------------------------------------------------
1 Find Tablespace Status, segment management, initial & Max Extents and Extent Management L2
2 Check default tablespace & temporary tablespace of each user L1/L2
3 Check for fragmentation of Indexes L2
4 Check Patch status of database & bug fixation L2
5 Check for Block corruption periodically L2
General Checks L1/L2
----------------------
➢ Check for Backup Tables in the database and send report to bank
➢ Check for Compatible Parameter
➢ Check for Purge Recycle bin
➢ Check for Invalid Objects
➢ Check for User objects in SYSTEM tablespace
➢ Check for Archive Generation
➢ Check for db_files max number
➢ Check for Max db_files per tablespace
➢ Check for Database Registry Components
➢ Check for UNDO Tablespace Size Mismatch
➢ Check for ASM Free Space
➢ Check for ORA-ERRORS in alert logs
➢ Auto extensible datafiles
