DAILY DBA PROCEDURES

DAILY DBA PROCEDURES

This section summarizes the procedures we recommend you follow on a daily basis to check the status of each of your Oracle database:

1 . Verify that all instances are up .    Make sure the databases are available. Log into each instance and run daily reports or test scripts. Some sites may want you to automate this step. As an option , consider using Oracle Enterprise Manager's probe event.

2.Look for any new alert log entries by doing the following : ➔- Connect to each managed system . Use Telnet , SSH, or a similar protocol to connect.        - For each managed instance , go to the background dump destination (usually        $ORACLE_BASE/<SID>/bdump, where <SID> is the database system identifier , or SID ).        make sure to look under the SID for each database you are managing.

- At the prompt , use Unix tail command to check the alert_<SID>. Log , or examine the  most recent entries in the alert log file in some other way. - If any ORA errors have appeared since the last time you looked, note them in your  Database Recovery. Log and investigate each one. The Database Recovery Log is a text file you should create and maintain; there you can record for future reference any problems you find and  any actions you take.

3. Verify that the simple Network Management protocol (SNMP) subagent for the Oracle         Database , dbsnmp , is running         - Log on to each machine you are managing , to check for the dbsnmp process.         - For Unix , at the command line ,type:            ps -ef | grep dbsnmp         there should be two dbsnmp processes running . If not restart dbsnmp.     4. Verify that the database backup was successful .     5. Verify that the database archiving to tape was successful .     6. Verify that you have enough resources for acceptable performance by doing the following ;         -Verify free space in tablespaces .     For each instance , make sure the enough free space exist in each tablespace to handle the day's expected growth . When incoming data is stable and the average daily growth can be calculated , your minimum free space should at least equal the amount of data growth you accept during the time it will take to order , receive ,and install additional disks.

Verify rollback segment as follow:    i. To obtain the current status of each ONLINE or FULL rollback segment(by ID, not by name),         query           on the V$ROLLSTAT view.

i. Status should be ONLINE, not OFFLINE or FULL,except in those cases in which you have a     special rollback segment for large batch jobs whose normal status is OFFLINE. ii. Optional: for each database you may have a list of rollback segment names and their expected     statuses.

iv. For storage parameter and names of all rollback segments , query on DBA_ROLLBACK_SEGS. This views STATUS field is less accurate then V$ROLLSTAT , however , since it lacks the PENDINGING OFFLINE and FULL status ; It shows these as OFFLINE and ONLINE , respectively. - Identify bad growth projections:      i. Gather daily sizing information .      ii. Check current extents.      iii. Query current table sizing information.      iv. Query current index sizing information.      v. Query growth trends. Look for segments in the database that are running out of resources (e.g .., extents ) or growing at an excessive rate. You may need to adjust the storage parameters of these segments. For example, if any objects has reached 200 as the number of current extents , upgrade the MAX_EXTENTS parameter in the INIT.ORA file to a value of UNLIMITED.      – Identify space-bound objects.

The NEXT_EXTENT values for space-bound objects are bigger than the largest extent that the table space can offer. Space-bound objects can harm database performance . If you encounter such objects , you first need to investigate the situation . Then you can either add another datafile or manually defragment the tablespace using the COALESCE clause of the ALTER TABLESPACE command :                                 ALTER TABLESPACE name COALESCE where name is the tablespace name .     – Be sure to review to connection for CPU , memory , network , and disk resource .

7. As a final daily requirement,keep improving your overall DBA skills by spending at least one hour a day reading Oracle your DBA manuals.

WEEKLY DBA PROCEDURES

This section summarizes the procedures we recommend you follow on a weekly basis to check the status of each of your Oracle database:

1. Look for objects that break rules:          For each object-creation policy (naming convention,storage parameter,etc.),institute an automated check to verify that the policy is being followed. Make sure every object in a given tablespace has the exact same size for NEXT_EXTENT parameter value.

2. Ensure that all tables have unique primary keys.       – Check for missing primary keys.       – Check for disabled primary keys.       – Make sure all primary key indexes are unique.    3. Ensure that all indexes use an index tablespace.

4. Ensure that schemas look identical between environments (especially test and production      environments):      - Check for datatype consistency.      - Check for the consistency of other objects.  5. Look for security policy violations. 6. Look in Net8 logs for errors and other issues. 7. Archive all alert logs to history.

Monthly DBA procedures

This section summarizes the procedures we recommend you follow on a monthly basis to check the status of each of your Oracle databases:

1. Look for harmful growth rates.

Review changes in segment growth, as compared to previous reports, to identify segment that may be growing in a harmful way.

2. Examine tuning opportunities.

Review common Oracle tuning points,such as cache hit ratio, latch contention, and other points dealing with memory management. Compare these with past reports to identify harmful trends and determine the impact of recent tuning adjustment.

3. Look for I/O contention     Review database file activity. Compare this activity to past output to identify trends that could    lead to possible contetion. 4. Review fragmentation by investigating row chaining and other areas of fragmentation. 5. Project performance into the future as follows:

- Compare reports on CPU, memory, network, and disk utilization from both Oracle and the operating system to identify trends that could lead to contention for any one of these resources in the near future.

- Compare performance trends to your organization`s service level agreement to see when your system will go out of bounds.

6. Perform tuning and maintenance.    Make whatever adjustments are necessary to avoid contention for system resources.    These adjustments may include scheduled downtime or requests for additional resources.