Oracle DBA responsibilities and daily work analysis

DBA's responsibilities and daily work responsibilities:

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

2. Familiar with the storage structure of the database system to predict future storage requirements, and develop database storage solutions.

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

4. Create Database Objects Based on application system requirements designed by developers

5. Modify the database structure as necessary based on the feedback from developers.

6. database management users maintain database security

7. control and monitor users' access to the database

8. Monitor and optimize database performance

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

10. Back up and restore the database

11. Contact the database system manufacturer to track technical information.

12. Solve the Problem of link between client intermediate layer and Server

13. Secure Connection

Daily Work:

1. Ensure that the server is working normally and all types of databases are in normal state.

2. Check whether the hard disk space is sufficient.

3. view database logs, trace files, and check for errors

4. Check the backup validity.

5. Monitor the server performance parameters through the system performance monitor to find out whether the database performance is degraded, find the cause and solve the problem.

6. Enter the DBA log

Weekly work:

1. Monitor the space expansion of database objects

2. Determine whether to adjust storage Parameters

3. System Health Check

4. Check to delete data objects that have no meaning

5. Check to delete meaningless constraints and triggers.

6. Check and handle weekly backups

Monthly work:

1. Based on the above check results, analyze whether there are any storage fragments that affect performance in the database.

2. Determine how to optimize the performance based on historical database performance data

3. Process backups every week and every month.

4. Summarize the work and propose the next Management Plan

Summary of routine Oracle DBA work

The Oracle administrator should perform regular monitoring on the Oracle database system as follows:

1. Check the running status, log files, backup status, database space usage, and system resource usage of the Oracle database every day to discover and solve the problem.

2. Monitors the space expansion and data growth of database objects every week, performs health checks on databases, and checks the status of database objects.

3. Perform analyze on tables and indexes every month, check table space fragments, find opportunities for database performance adjustment, adjust database performance, and propose the next space management plan. Perform a comprehensive check on the Oracle database status.

Daily Work

1. Check that all instances are in normal status and log on to all databases or instances. Check the Oracle background process: # ps-Ef | grep ora

2. Check the usage of the file system (available space ). If the remaining space of the file system is less than 20%, delete unnecessary files to release space. # DF-K

3. Check the log file and trace file to record errors in the alert and trace files. CD $ oracle_base/oradata/<Sid>/bdumptail-F alert _ <Sid>. Log

Discover any new ora _ errors, record and solve them

4. Check the validity of the database backup on the current day.

For the RMAN backup method, check the backup logs of the third-party backup tool to determine whether the backup is successful.

For the export backup mode, check the exp log file to determine whether the backup is successful

Check the corresponding log files for other backup methods

5. Check that the status record of the data file is not an "online" data file and restore it.

Select file_name from dba_data_files where status = "offline"

6. Check table space usage
Select. tablespace_name, round (total-free)/Total, 3) * 100 pecentfrom (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 tablespace_name) bwhere. tablespace_name = B. tablespace_name

7. Check the remaining tablespace
Select tablespace_name, sum (blocks) as free_blk, trunc (sum (bytes)/(1024*1024) as free_m, max (bytes)/(1024) as big_chunk_k, count (*) as num_chunksfrom 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 statistics

9. Check the database performance, record the CPU usage, Io, buffer hit rate, and so on, and use vmstat, iostat, glance, and top

10. Handling of daily problems

Weekly Work

1. Control the space expansion of database objects. Based on the daily check results of this week, find the database objects with fast space expansion and take corresponding measures.

Delete historical data and extended tablespaces
Alter tablespace <Name> Add datafile '<File> 'size <size>

Adjust the storage parameter next extent pct_increase of the Data Object

2. Monitor Data Volume Growth

Find the database objects that record the rapid growth of Data Volume Based on the daily check results of this week, and take corresponding measures

Delete historical data and tablespace Expansion

Alter tablespace <Name> Add datafile '<File> 'size <size>

3. System Health Check

Check the following:
Init <Sid>. oracontrolfileredo log filearchiveingsort area sizetablespace (system, temporary, tablespace fragment) datafiles (autoextend, location) object (number of extent, next Ext, index) rollback segmentlogging & tracing (alert. log, max_dump_file_size, sqlnet)

4. Check invalid database objects

Select owner, object_name, object_type from dba_objects where status = 'invalid'

5. Check for ineffective Constraints

Select owner, constraint_name, table_name, constraint_type, status from dba_constraintswhere status = 'Disabled 'and constraint_type = 'P'

6. Check for invalid triggers
Select owner, trigger_name, table_name, status from dba_triggerswhere status = 'Disabled'

Monthly work

1. Analyze tables/indexes/Cluster

Analyze table <Name> estimate statistics sample 50 percent;

2. Check the table space fragments.

Analyze database fragments based on weekly checks this month and find corresponding solutions

3. Seek opportunities for database performance Adjustment

Compare daily monitoring reports on database performance to determine whether it is necessary to adjust database performance

4. database performance adjustment, and performance adjustment if necessary

5. Propose the next space management plan, and propose improvement methods for space management based on weekly monitoring.