Database (Oracle) O & M work content and common script commands

Database (Oracle) O & M work content and common script commands 1. system resource status: -- Memory and CPU resources -- linux, solaris, aix vmstat 5 -- Note: 1) observe the Amount of idle memory and whether the amount of idle memory is stable. If it is unstable, you have to find a solution. The solution depends on the specific situation. Generally, you can adjust the relevant memory parameters to solve the problem, the output indicators, explanations, and memory Adjustment Parameters and methods of various operating systems are not exactly the same; 2) observe the utilization of CPU resources. First, observe the number of tasks running on the CPU, that is, the indicator in the first column of the vmstat output. If this indicator continues to exceed the number of CPU cores, you should pay attention to it. If this indicator continues to exceed twice the number of CPU cores, you should pay attention to it; if the number of CPU cores is multiple times, the system may be perceptible to applications. You must immediately find a solution. Of course, when observing this indicator, we also need to combine the indicators of CPU utilization, such as user usage percentage, system usage percentage, and idle percentage. If the idle percentage continuously falls below 20%, attention should be paid; Attention should be paid if the duration is lower than 10%; if the duration is 0, the system may be perceptible to applications, and the problem should be solved immediately; 3) pay attention to the ratio of CPU usage to system usage. In general, in a normal system, the user usage percentage should be much higher than the system usage percentage, several times to dozens of times or even higher. If the system usage percentage keeps approaching the user usage percentage, even greater than the user's usage percentage indicates that the system status is abnormal, possibly due to hardware or operating system problems or application problems. -- IO status -- linux, solaris iostat-dx 5 -- aix iostat 5 -- Note: 1) this command is mainly used to observe the load and performance of the system storage device. First, you need to observe the degree of busy storage devices in the system. If this indicator continues to exceed 80%, you should pay attention to it. If it continues to exceed 90%, you should pay attention to it. If it continues to exceed 100%, generally, Application Awareness occurs, and you should try to solve the problem immediately. 2) second, pay attention to the I/O capabilities of various storage devices on the system, that is, the amount of input and output data of each storage device per second. This is related to the hardware and configuration of a specific device. There is no strict standard, and the performance is better than G per second, even a few G, the difference can only be dozens or even dozens of megabytes per second; 3) Finally, you need to observe the time it takes for the storage device to complete each read/write operation, this is also related to the hardware and configuration of specific devices. A good device may be less than 1 millisecond, but the difference may be dozens or even hundreds of milliseconds. iostat output, the output and interpretation on various operating systems are also different. 2. disk space and usage df-k -- Note: 1) The command outputs the device names, total space, used space, and percentage of each storage device on the system, the amount and percentage of available space, and the mount point name. The unit of the bucket here is K. 3. view the system log. Note: 1) the name and location of the log to be viewed, it is related to the specific operating system and your concerns. For more information, see related articles. 4. view database alarm logs (alert. log) -- Note: 1) the location of the log varies depending on the database version. Generally, in a 10 Gb database

ORACLE_BASE/admin/ORACLE_SID/bdump/alert_ORACLE_SID.log

On the 11g database

ORACLE_BASE/diag/rdbms/ORACLE_SID/ORACLE_SID/trace/alert_ORACLE_SID.log

You can log on to the database and run the following command:

select value from v$diag_info where name ='Diag Trace';

 

2) Find alert. after logging, you can check for exceptions, such as errors, suspicious alarms, and suspicious commands. If such exceptions occur, you can report the information to professional DBAs, professional DBAs can follow up and analyze other information until the cause of the exception is found and properly resolved. 5. view the database tablespace. Note: 1) the most important thing is that, to view the space usage of the database and the space usage of each tablespace in the database, run the following command to check whether the tablespace usage is normal or abnormal, if the space caused by normal use is insufficient, you can consider resizing the table space or other measures to solve the problem. Otherwise, you have to check what causes the abnormal use of the table space, locate the cause, and solve the problem;

Select d.tablespace_name,space "sum_space(m)",blocks sum_blocks,         space-nvl(free_space,0) "used_space",round((1-nvl(free_space,0)/space)*100,2) "used_rate(%)",         Free_space "free_space(M)"    From (select tablespace_name,round(sum(bytes)/(1024*1024),2) space,sum(blocks) blocks            From dba_data_files           Group by tablespace_name) d,         (select tablespace_name,round(sum(bytes)/(1024*1024),2) free_space            From dba_free_space           Group by tablespace_name) f           Where d.tablespace_name=f.tablespace_name(+)  Union all  Select d.tablespace_name,space "sum_space(M)",blocks sum_blocks,used_space "used_space(M)",round(nvl(used_space,0)/space*100,2) "used_rate(%)",         Nvl(free_space,0) "free_space(m)"    From         (select tablespace_name,round(sum(bytes)/(1024*1024),2) space,                 Sum(blocks) blocks            From dba_temp_files           Group by tablespace_name) d,         (select tablespace_name,round(sum(bytes_used)/(1024*1024),2) used_space,round(sum(bytes_free)/(1024*1024),2) free_space            From v$temp_space_header           Group by tablespace_name) f    Where d.tablespace_name=f.tablespace_name(+)    order by tablespace_name;

 

2) Check the table space settings and status. Note the automatic expansion settings of the data files in the user data table space. Generally, we recommend that you disable automatic expansion of the data files in the user data table space, in this way, unexpected events can be avoided, such as system space depletion caused by abnormal use of tablespaces, and automatic growth of closed data files. When the tablespace is used up, an error will be reported on the user end when the database is used up, in this way, space warnings can be triggered. You can query tables such as ba_data_files and dba_temp_files. 6. You can view database users in the dba_users view, if any exception information is found, inform professional DBAs and other relevant personnel in a timely manner and follow up and analyze the information until the cause of the exception is found and properly resolved. For example, a new database user is found; the user that was originally locked has been opened; the user's default tablespace has changed; the user's default resource configuration synopsis or the resource consumption group has changed. 7. view User Permissions and audit information -- Note: 1) check user permissions to see if user permissions have changed and whether excessive authorization exists, to view all permissions owned by a user, use the following statement:

SELECT 'ROLE' TYPE,grantee,granted_role privilege,admin_option ad,         '/' table_name,'/' column_name,'/' owner    FROM dba_role_privs   WHERE grantee = UPPER ('&&1')  UNION ALL  SELECT 'SYSTEM' TYPE,grantee,privilege privilege,admin_option ad,         '/' table_name,'/' column_name,'/' owner    FROM dba_sys_privs   WHERE grantee = UPPER ('&&1')  UNION ALL  SELECT 'TABLE' TYPE,grantee,privilege privilege,grantable ad,         table_name,'/' column_name,owner    FROM dba_tab_privs   WHERE grantee = UPPER ('&&1')  UNION ALL  SELECT 'COLUMN' TYPE,grantee,privilege privilege,         grantable ad,table_name,column_name,owner    FROM dba_col_privs   WHERE grantee = UPPER ('&&1')   ORDER BY 1;

 

2) If the system enables the audit function, you can view the corresponding tables, views, dictionaries, or operating system files based on the specific audit configuration; 8. view the number of system sessions and sessions -- Note: 1) Check the number of system sessions. Run the following command multiple times to check whether the total number of sessions is stable, you can also view the number of sessions by user, check whether the number of sessions is stable for each user, and compare it with the previous data. If any instability or exception is found, notify professional DBAs and other related personnel in a timely manner, further tracking and analysis until the cause of the exception is found and properly resolved, for example, abnormal session growth.

       select count(*) from v$session;       select username,count(*) from v$session group by username;

 

2) Check sessions in general to see if any exception information exists. If any exception is found, notify professional DBAs and other related personnel in a timely manner and perform further tracking and analysis, wait until the cause of the exception is found and properly resolved, such as abnormal host login and abnormal user login;

 select * from v$session;

 

9. view database wait events-Description: 1) The database wait event can reflect the current running status of the database. You can run the following command to query the event. If any exception information is found, inform professional DBAs and other related personnel in a timely manner, follow up and analyze the problem, find out the cause of the exception, and properly solve the problem. In addition, due to the high professionalism of this check, many times, it is recommended that professional DBAs or O & M personnel check the project with professional DBAs to promptly and accurately locate and solve problems.

Select event "wait event",sum(seconds_in_wait) "waited so far(sec)",         Count(sid) "num sess waiting"    From v$session_wait   Group by event   order by "waited so far(sec)" desc,"num sess waiting" desc;

 

10. Check the user password-Description: 1) Check the operating system user password and check whether the password of each user changes. If any exception is found, notify the system administrator and other related personnel in a timely manner, and further tracking and analysis, until the cause of the exception is found and properly resolved, it is best to change the password regularly; 2) Check the Database User Password, check whether the password of each database user changes, if any exception is found, inform professional DBAs and other related personnel in a timely manner, and follow up and analyze the problem until the cause of the exception is found and properly resolved. It is best to change the password on a regular basis. 11. obtain and analyze AWR reports on a regular basis-description: 1) The AWR report is a feature supported by the oracle database, which can reflect the load and Performance Status of the database during the corresponding time period. By default, the system retains a snapshot of one week to generate the corresponding AWR, the Snapshot granularity is 1 hour; 2) AWR contains comprehensive information. Professional DBAs can learn the overall status of the database and even the server at the corresponding time, professional DBAs are required to analyze AWR reports, or professional DBAs are required to cooperate. Once an exception is found, relevant personnel should be notified and further tracked and analyzed until the cause of the exception is found and properly resolved.