Oracle Database Administrator's responsibilities (posting)

Oracle Database Administrator responsibilities

The Oracle Database Administrator should perform regular monitoring on the Oracle database system as follows: (1) Check the running status, log files, backups, database space usage, and system resource usage of the Oracle database every day to discover and solve the problem. (2) Monitor the space expansion and data growth of database objects every week, check the database health, and check 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) confirm that all instances are in normal status Log on to all databases or routines to check Oracle background processes: $ PS-Ef | grep ora (2) Check the use 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. Connect to each system to be managed ? Use 'telnet' ? CD to the bdump directory for each database, usually $ oracle_base/& lt; Sid>/bdump ? Run the Unix 'tail' command to view the alert _ & lt; Sid>. log file. ? If any new ora-errors are found, record and resolve them. (4). Check the validity of the database backup on the current day. RMAN backup method: Check the backup logs of a third-party backup tool to check whether the backup is successful. Export backup mode: Check the exp log file to check whether the backup is successful Other backup methods: Check the corresponding log file (5) Check the data file whose status is not "online" and restore it. Select file_name from dba_data_files where status = 'offline' (6). Check the usage of the table space. Select tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m, to_char (100 * sum_free_m/sum_m, '99. 99') | '%' As pct_free From (select tablespace_name, sum (bytes)/1024/1024 as sum_m from dba_data_files group by tablespace_name ), (Select tablespace_name as fs_ts_name, max (bytes)/1024/1024 as max_m, count (blocks) as count_blocks, sum (Bytes/1024/1024) as sum_free_m from dba_free_space group by tablespace_name) Where tablespace_name = fs_ts_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_chunks From dba_free_space group by tablespace_name; (8). Monitor database performance Run bstat/estat to generate a system report Or use statspack to collect statistics. (9) Check the database performance and record the CPU usage, Io, buffer hit rate, and so on of the database. Use vmstat, iostat, glance, top, and other commands (10) Handling of daily problems. Weekly Work (1) control the space expansion of database objects Find the database objects that quickly expand the space according to the daily check results of this week, and take Measures -- Delete historical data --- Expand table space Alter tablespace & lt; Name> Add datafile '& lt; File> 'size & lt; size> --- Adjust the storage parameters of Data Objects Next extent Pct_increase (2) monitoring data volume growth Based on the daily check results of this week, find the database objects with a fast increase in the number of records, and collect Take corresponding measures -- Delete historical data --- Expand table space Alter tablespace & lt; Name> Add datafile '& lt; File> 'size & lt; size> (3). System Health Check Check the following: Init & lt; Sid>. ora Controlfile Redo log file Archiving Sort area size Tablespace (system, temporary, tablespace fragment) Datafiles (autoextend, location) Object (number of extent, next extent, index) Rollback segment Logging & 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) constraints that do not work Select owner, constraint_name, table_name, Constraint_type, status From dba_constraints Where status = 'Disabled 'and constraint_type = 'P' (6) Check for invalid triggers Select owner, trigger_name, table_name, status From dba_triggers Where status = 'Disabled' Monthly work (1). Analyze tables/indexes/Cluster Analyze table & lt; Name> estimate statistics sample 50 percent; (2). Check the table space fragments. Analyze database fragmentation Based on weekly checks this month and find the corresponding solution (3). Seek opportunities for database performance Adjustment Compare daily monitoring reports on database performance and determine whether it is necessary to adjust database performance (4). database performance Adjustment Adjust the performance if necessary (5) propose the next space management plan Based on weekly monitoring, this paper proposes an improvement method for space management. Routine Oracle DBA management Objective: This document provides detailed information about one or more Oracle databases on a daily, monthly basis, The results of the annual running status and the results of the check are displayed in the appendix of the document. And PL/SQL code. Directory 1. Routine Maintenance Procedures A. Check all started instances B. Search for some new warning logs C. Check whether Dbsnmp is running D. Check whether the database backup is correct E. Check whether the files backed up to the tape are correct. F. Check whether the database performance is normal and reasonable, and whether there is sufficient space and resources. G. Copy the document logs to the backup database. H. Read the DBA user manual frequently. 2. Night Maintenance Program A. Collect Volumetric Data 3. weekly maintenance A. Find the objects that break the rules. B. check whether any security policy violations occur. C. view the SQL * Net log of the error location D. archive all warning logs E. Visit the supplier's home page frequently. 4. Monthly Maintenance Program A. view the growth rate that harms the database B. Review previous database optimization performance adjustments C. view the I/O screen neck issues D. Review fragmentation E. Future execution plan F. View adjustment points and Maintenance 5. Appendix A. Monthly maintenance process B. Night maintenance process C. weekly maintenance process 6. References ---------------------------------------------------------------- 1. Daily maintenance process A. check whether all instances have started Determine whether the database is available, write each instance into the log and run the daily report or run the test File. Of course there are some operations that we want to automatically run. Optional execution: Use the 'probe' event in the Oracle manager to view B. Search for the new warning log file. 1. connect to each Operating System 2. Use 'telnet 'or a comparable Program 3. Perform $ oracle_base/& lt; Sid>/bdump for each management instance. And enables it to return to the SID of the control database. 4. Run the 'tail' command in UNIX to view alert _ & lt; Sid>. Log, or Check the warning logs of the latest period in the file in other ways 5. If some ora_errors appeared again before, record it to the database Recover logs and study them carefully. The recovery logs of this database are in file C. view the running status of Dbsnmp Check the 'dbsnmp 'processes of each managed machine and record them in the log. In UNIX, in the command line, type PS-Ef | grep Dbsnmp, and two The Dbsnmp process is running. If no, restart Dbsnmp. D. Check whether the database backup is successful E. Check whether the Backup Tape document is successful. F. Check whether sufficient resources are available for reasonable performance. 1. check whether there is any available space in the tablespace. For each instance, check whether there is any available space in the tablespace to meet the requirements of the current day. . When the existing data in the database is stable, the daily data growth average The number can also be calculated. The minimum remaining space must at least meet the daily data growth requirement. A) Run 'free. SQL 'to check the remaining space. B) Run 'space. SQL 'to check the percentage of available space in the space. 2. Check rollback segments The status of a rollback segment is generally online. Except for some dedicated segments prepared for complex work, it is generally offline. A) each database has a list of rollback segment names. B) You can use v $ rollstat to query the current status of online or offline rollback segments. C) the storage parameters and names of all rollback segments are available. Dba_rollback_segs. But it is not as accurate as V $ rollstat. 3. Identify excessive growth Check the storage parameters of the segments in the database that exceed the resource or the growth rate is too high. A) collect information about the daily data size, which can be used 'Analyze5pct. SQL '. Skip this step if you collect information every night. B) Check the current range. 'nr. extents. SQL 'is available '. C) query the size of the current table. D) query the current index size. E) query the growth trend. 4. Determine the space range. If the next_extent of the range space object is greater than the maximum range provided by the tablespace This will affect the operation of the database. If we find this goal Use 'alter tablespace coaleid' to investigate its location or add another data file. A) Run 'spacebound. SQL '. If they are all normal, no rows will be returned. 5. Review the process of CPU, memory, network, and hardware resource arguments A) Check the CPU utilization and enter X:. htm => System Metrics => CPU utilization page. The maximum CPU usage is 400. For a period of time above 350, we need to check and study the problems. G. Copy the archived logs to the standby database. If there is a backup database, it is expected to copy the appropriate archived logs to the backup database. Location, which saves the most recent data in the standby database. H. Check the DBA user manual frequently. If possible, read it extensively, including the DBA manual, industry magazine, news group, or email list. ------------------------------------------------------------- Ii. Night maintenance process Most database products will benefit from the running of check processes identified every night. A. Collect Volumetric Data 1. Analyze plans and collect data More Accurate Analysis and Computation and save results. A) if you haven't done this now, use 'mk volfact. SQL 'to create a table for volume determination. B) collect the data size in the evening, and use 'analyze Comp. SQL '. C) Collect statistical results and use 'pop vol. SQL '. D) Check the data when you are idle. If possible, check the data every week or every month. I used the connection between MS Excel and ODBC to check the growth of data and charts. ------------------------------------------------------------- 3. weekly maintenance process A. Search for damaged targets 1. For each given tablespace object, the next_extent size is the same, as shown in 12/14/98, the default next_extent datahi is 1 GB, and datalo is 500 mb, Indexes is 256 MB. A) Check next_extent settings and use 'nextext. SQL '. B) Check the existing extents and use 'existext. SQL '. 2. All Tables should have unique primary keys. A) Check the tables that do not have a primary key and can use 'no _ PK. SQL '. B) find those primary keys that do not play a role and use 'dis _ PK. SQL '. C) All primary keys used for indexing are unique and can be 'nonupk. SQL to check. 3. All indexes should be placed in the index tablespace. Run 'mkrebuild _ idx. SQL' 4. The plan should be the same between different environments, especially between the test environment and the finished environment. A) check whether the data types in the two running environments are consistent and available. 'Ype ype. SQL '. B) Find the differences between objects in two different instances. 'Obj _ coord. SQL '. C) A better way is to use a tool like a scheduler manager looking for software. B. check whether there is any problem that harms the security policy. C. view the SQL * Net log of the error. 1. client logs. 2. server logs. D. archive all warning logs E. Supplier Homepage 1. Oracle supplier Http://www.oracle.com Http://technet.oracle.com Http://www.oracle.com/support Http://www.oramag.com 2. Quest Software Http://www.quests.com 3. Sun Microsystems Http://www.sun.com ---------------------------------------------------------------- 4. Monthly maintenance process A. view the growth rate that harms the database 1. Review changes in segment growth from previous records or reports to identify the hazards of segment growth B. Review previous database optimization performance adjustments 1. Review the adjustment points of general Oracle databases and compare previous reports to identify harmful development trends. C. view the I/O screen neck issues 1. Check the activity of the database files in the early stage and compare the previous output to determine the trend that may cause the screen neck problem. D. Review fragmentation E. Plan database performance in the future 1. Compare the CPU, memory, network, and hard disk usage of Oracle and the operating system. To determine the trend of resource competition in the near future 2. When the system is out of scope, the performance trend should be regarded as a service level agreement. F. Complete adjustment and Maintenance 1. Make modifications meet the need to avoid competition for system resources, including adding new resources or making the expected shutdown.