DBA responsibilities and daily responsibilities

Original address: http://blog.csdn.net/justdb/article/details/8923677 duty

1. Install and upgrade the database server, and the application tools to build and configure the network environment.

2. Familiar with the storage structure of the database system to predict future storage requirements, the development of database storage scheme.

3. Create a database storage structure based on the application requirements designed by the developer.

4. Create database objects based on application requirements designed by the developer

5. Modify the structure of the database when necessary, based on feedback from the developer

6. Manage database security for users who maintain databases

7. Control and monitor user access to the database

8. Monitor and optimize the performance of your database

9. Develop a database backup plan to recover database information when a disaster occurs

10. Backing up and recovering databases

11. Contact the database system manufacturer to track technical information

12. Troubleshoot client-side middle-tier and server link issues

13. Ensure secure connection

Daily work

1 Ensure the server is working properly, all kinds of databases are in good condition

2 Check if the hard disk space is sufficient

3 View database logs, view trace files, check for error messages

4 checking the validity of backups

5 Monitor the performance of the server through the Performance Monitor of the system to discover whether the performance of the database is degraded, find the cause and resolve

6 Completing the DBA log

Weekly work

1 Monitoring the spatial expansion of database objects

2 Deciding whether to adjust storage parameters

3 System Health Checks

4 Check to delete data objects that do not have any meaning

5 Check Delete meaningless constraints and triggers

6 on weekly backup check processing

Monthly work

1 based on the above checks, analyze if there is any storage fragmentation that affects performance in the database

2 determine how to optimize performance based on historical data for database performance

3 processing daily Weekly monthly backups

4 summarize the work and propose the next management plan

Oracle DBA Daily Summary

Oracle administrators should regularly monitor the Oracle database system as follows:

1, every day to the Oracle database running status, log files, backup situation, database space usage, system resource usage to check, to identify and resolve the problem.

2, weekly database object space expansion, data growth monitoring, the database to do health checks, the status of database objects to check.

3, the monthly table and index, such as analyze, check the table space fragmentation, the opportunity to look for database performance adjustment, database performance adjustment, proposed next space management plan. Perform a full check of the Oracle database status.

Work--sql every day

1. Confirm all instance status log in to all databases or instances to detect Oracle background process: #ps-ef | grep ora

2, check the use of the file system (the remaining space). If the file system has less than 20% remaining space, you need to delete unused files to free up space. #df-K

3. Check log files and trace files to record errors in alert and trace files. CD $Oracle _base/oradata/<sid>/bdumptail-f Alert_<sid>.log

Discover any new Ora_ errors, record and resolve

4. Check the validity of backup on database day

For Rman Backup, check the backup log of the third-party backup tool to determine if the backup was successful

For export backup mode, check the exp log file to determine if the backup was successful

For other backup methods, check the corresponding log file

5, check the status of the data file status record is not "online" data file, and do recovery.

[SQL]View PlainCopy

1. Select file_name from Dba_data_files where status= "OFFLINE"

6, check the use of the table space

[SQL]View PlainCopy

1. Select A.tablespace_name,round ((total-free)/total,3) *100 pecentfrom from (select Tablespace_name,sum (bytes) free from Dba_free_space GROUP by Tablespace_name) A, (select Tablespace_name,sum (bytes) Total from Dba_data_files GROUP by Tablespa Ce_name) b where a.tablespace_name=b.tablespace_name

7. Check the remaining table space

[SQL]View PlainCopy

1. Select Tablespace_name,sum (Blocks) as Free_blk,trunc (sum (bytes)/(1024*1024)) as Free_m,max (bytes)/(1024x768) as Big_chunk _k,count (*) as Num_chunksfrom from Dba_free_space Group by Tablespace_name;

8. Monitor database performance

Run Utlbstat.sql/utlestat.sql to generate system reports, or use Statspace to collect statistical data

9, check the database performance, record the database CPU usage, IO, buffer hit ratio and so on, using vmstat,iostat,glance,top

10, the daily occurrence of problems to deal with

Weekly work--sql

1, control the spatial expansion of the database objects, according to the daily inspection of the week to find space to expand the database objects quickly, and take appropriate measures.

Delete historical data, extend table space

[SQL]View PlainCopy

1. Alter tablespace <name> add datafile ' <file> ' size <size>

To adjust the storage parameters of a data object next extent Pct_increase

2, monitor the growth of data volume

Find database objects with a growing number of recorded data based on daily checks this week and take appropriate action

Delete historical data, enlarge table space

[SQL]View PlainCopy

1. Alter tablespace <name> add datafile ' <file> ' size <size>

3. System Health Check

Check the following:

[SQL]View PlainCopy

1. Init<sid>.oracontrolfileredo Log filearchiveingsort area sizetablespace (System,temporary,tablespace fragment) Datafiles (autoextend,location) object (number of Extent,next extent,index) rollback segmentlogging & Tracing ( Alert.log,max_dump_file_size,sqlnet)

4. Check for invalid database objects

[SQL]View PlainCopy

1. Select Owner,object_name,object_type from dba_objects where status= ' INVALID '

5. Check for constraints that do not work

[SQL]View PlainCopy

1. Select Owner,constraint_name,table_name,constraint_type,status from dba_constraints where status= ' DISABLED ' and Constraint_type= ' P '

6, check the invalid trigger

[SQL]View PlainCopy

1. Select Owner,trigger_name,table_name,status from dba_triggers where status= ' DISABLED '

Monthly work--sql

1, Analyze Tables/indexes/cluster

[SQL]View PlainCopy

1. Analyze table <name> estimate statistics sample percent;

2. Check table space Fragmentation

Analyze database fragmentation based on weekly check-up this month to find the appropriate solution

3. Look for database Performance tuning Opportunities

Compare daily monitoring reports on database performance to determine if database performance needs to be adjusted

4, database performance adjustment, if necessary for performance tuning

5, proposed the next step space management plan, according to the weekly monitoring, proposed the space management improvement method

DBA responsibilities and daily responsibilities