Solaris System Maintenance

5. System Maintenance
5.1 Solaris System
Involved server account1 (218.29.0.239), Account2 (218.29.0.240), oradb1 (218.29.0.244), oradb2 (218.29.0.245)
5.1.1 System Overview
1. Basic information of the Operating System: uname-A displays the name, hostname, major version information, minor version information, hardware type, CPU type, and platform information of the operating system in sequence.
2. kernel information: Modify the/etc/system file to change the default kernel parameters. modinfo displays the module information and sysdef displays the driver and kernel parameters.
3. CPU information:/usr/sbin/psrinfo-V displays the CPU status, quantity, CPU type, and clock speed.
4. Memory information: prtconf | grep "memory size" displays the memory size.
5. Hard Disk/Optical Drive Information: iostat-en displays the hardware address, status information, model, and size of the hard disk and optical drive. You can use this command to check whether all kinds of errors have occurred.
6. Nic information: ifconfig-A. The NIC name, status, maximum transmission unit MTU, IP address, subnet mask, and broadcast address are displayed.
7. HA information:
8. Network Information: netstat-Rn displays static routes and default routes.
9. Package PKG information: pkginfo-l pkgname. It determines whether a PKG is installed and related pkgadd, pkgrm, and pkgchk.
10. patch information: showrev-P. Installing software requires specific patches.
11. File System Information: DF-K monitors capacity to determine whether there is insufficient space.
12. User information: who displays the current online user. Whodo displays the operations performed by the current user. Use useradd, groupadd, or admintool to manage users. The ID displays the current user's login name, Login ID, group name.
5.1.2 System Monitoring
1. CPU performance monitoring: vmstat/mpstat and SAR, vmstat and SAR display summary information in the SMP system, while mpstat can display the load of each CPU. Vmstat monitors the number of R and B under procs, which reflects the number of processes currently in the running and blocking status. If the number of R is greater than the number of CPU for a long time, and the number of US + Sy> 80 under the CPU, indicates insufficient CPU processing capability.
2. Memory monitoring: vmstat. It monitors PI/PO in free and page under memory. PI/PO indicates the number of incoming/outgoing records in K units per second. If the number of free is small and the number of PI/PO is greater than 0 for a long time, the memory is insufficient.
3. Hard Disk monitoring: iostat-D and Sar-D. You can see the working conditions of each hard disk and each partition of the hard disk.
4. Swap area monitoring: swap-L and swap-S. Try to place swap areas on hard disks with lighter loads. At the same time, the usage percentage of the swap area should not be too high.
5. HA System Monitoring:
6. Network Monitoring: netstat-in displays the workload of each network card. Ipkts/opkts sending and receiving packets. Ierrs/oerrs, Collis indicates errors and conflicts.
Ierrs/ipkts or oerrs/opkts> 2% indicates that the network conflict is serious.
7. Port Monitoring: monitor whether a service is started or running normally,
Netstat-an | grep portnum or tcpdump | grep portnum.
8. hardware fault diagnosis:/usr/platform/'uname-m'/sbin/prtdiag-V determines whether a hardware fault exists through the output result.
9. syslogd log monitoring: The syslogd background process writes information in the system running to the/var/adm/messages file to monitor whether there are any abnormal software or hardware errors.
Earlier information exists in the messages.0 and message.1 files in the same directory.
10. Mail monitoring: some users regularly execute tasks through crontab. After the task is completed, the cron process will send a mail to the user to check whether the task is completed by checking the mail. If an exception occurs, syslogd sends a mail to the root user. Therefore, pay special attention to the root user's mail.
11. Process Monitoring: Processes with special resource consumption are monitored through the PS, prstat command or/usr/dt/bin/sdtprocess tool. Sdtprocess is a graphical tool that can be sorted by CPU and MEM usage and execution time. Prstat collects statistics on the active processes of the system and sorts them in the order required.
12. system running time monitoring: the uptime command is used to measure the startup time and system load of a machine. The more time the uptime is displayed, the more stable the machine is.
13. system running Level Monitoring: Who-r displays the running level of the current machine.

5.2 Linux
Involved front-end machines: fep1 (pushed), fep2 (218.29.0.228), fep3 (pushed), fep4 (pushed), fep5 (pushed), fep6 (218.29.0.pushed), fep7 (pushed ), fep8 (218.29.0.234), fep9 (218.29.0.235), fep10 (218.29.0.236), fep11 (218.29.0.237), fep12 (218.29.0.238)
5.2.1 System Overview
1. Basic Operating System Information: uname-
2. kernel information: files in the/proc/sys/kernel directory record kernel parameters, modinfo displays module information, and sysdef displays driver and Kernel Parameter information.
3. CPU information :.
4. Memory information: prtconf | grep "memory size" displays the memory size.
5. Hard drive/Optical Drive Information :.
6. Nic information: ifconfig-A. The NIC name, status, maximum transmission unit MTU, IP address, subnet mask, and broadcast address are displayed.
7. Network Information: netstat-Rn displays static routes and default routes.
8. Package PKG information: rpm (RedHat Package Manager ). It determines whether a PKG is installed, rpm-Q query, and rpm-I installation.
9. patch information: File System Information: DF-K monitoring capacity determines whether there is insufficient space.
10. User information: who displays the current online user. Use the KDE User Manager tool to add users. The ID displays the current user's login name, Login ID, group name.
5.2.1 System Monitoring
1. CPU performance monitoring: vmstat and SAR. Vmstat monitors the number of R and B under procs, which reflects the number of processes currently in the running and blocking status. If the number of R is greater than the number of CPU for a long time, and the number of US + Sy> 80 under the CPU, indicates insufficient CPU processing capability.
2. Memory monitoring: vmstat. It monitors PI/PO in free and page under memory. PI/PO indicates the number of incoming/outgoing records in K units per second. If the number of free is small and the number of PI/PO is greater than 0 for a long time, the memory is insufficient.
3. Hard Disk monitoring: iostat-D and Sar-d
4. Swap area monitoring swap-l
5. Network Monitoring netstat-in
6. Port Monitoring: monitor whether a service has started netstat-an | grep portnum or tcpdump | grep portnum
7. hardware fault diagnosis :.
8. syslogd log monitoring: The syslogd background process writes information in the system running to the/var/adm/messages file to monitor whether there are any abnormal software or hardware errors.
Earlier information exists in the messages.0 and message.1 files in the same directory.
9. Mail monitoring: some users regularly execute tasks through crontab. After the task is completed, the cron process will send a mail to the user to check whether the task is completed by checking the mail. If an exception occurs, syslogd sends a mail to the root user. Therefore, pay special attention to the root user's mail.
10. Process Monitoring: Processes with special resource consumption are monitored through PS or top.

6. Database Maintenance
6.1 Database Installation
6.1.1 preparation of the Operating System
1. Kernel Parameter Modification
Edit and modify the/etc/System File
Set shmsys: shminfo_shmmax = 4294967295
Set shmsys: shminfo_shmmin = 1
Set shmsys: Sh minfo_shmmni = 100
Set shmsys: shminfo_shmseg = 10
Set semsys: seminfo_semmns = 2000
Set semsys: seminfo_semms = 1000
Set semsys: seminfo_semmni = 100
Set semsys: seminfo_semopm = 100
Set semsys: seminfo_semvmx= 32767
2. Check the required OS patch
Download and install a specific version of the patch set on sunsolve.sun.com to meet Oracle installation requirements.
3. Add operating system users/groups
L groupadd DBA
L groupadd oinstall
L useradd-c "Oracle DBA"-D/home/Oracle-G oinstall-G DBA
L modify the/etc/passwd file and change the user's login shell or other information

4. Set User Environment Variables
6.1.2 Database Installation
6.1.3 install database Patches
1. patch download: the Oracle database patch is downloaded from the Metalink website of Oracle. After Successful Logon, select "Oracle Databse" in the product family drop-down box on the patch page ", in the product drop-down box, select "RDBMS server"; in the release drop-down box, select "Oracle 8.1.7.4"; in the platform drop-down box, select "Sun Solaris OS (iSCSI) 64-bit ", select "American English (US)" in the language drop-down box, select "latest product patches or minipacks" in the limit search to drop-down box, and click Submit. On the page that appears later, click Download to download the patch.
2. patch installation: After the patch is downloaded, decompress the package. The installation method is similar to that of the database. Start the runinstaller installer and select the product file in the patch directory to install the package. Note that the database must be properly shut down during patch installation.
3. Check after installation: Select * from V $ version; check whether the version of each product has been upgraded from 8.1.7.0 to 8.1.7.4.
6.2 Summary of basic database information
1. Version Information
SQL> select * from V $ version;
2. Database Name, archiving mode status
SQL> Col name format A12
SQL> select name, log_mode, open_mode from V $ database;
3. Database Installation Options
SQL> Col parameter format A50
SQL> select * from V $ option;

4. There are three types of database files: control file, log file, and datafile.
L control file
SQL> select name from V $ controlfile;
L log file name and size
Col group # format 999 heading 'group'
Col member format a45 heading 'member' justify C
Col status format A10 heading 'status' justify C
Col archived format A10 heading 'archived'
Col fsize format 999 heading 'size | (MB )'
Select L. group #, Member, archived, L. status, (Bytes/1024/1024) fsize from V $ log L, V $ logfile f Where F. group # = L. group # order by 1;
L. Data File Name, tablespace and size
Col file_name format A50 heading 'datafile name'
Col tablespace_name format A18 heading 'related tablespace'
Select substr (file_id, 1, 3) "ID #",
File_name,
Tablespace_name,
Bytes/(1024*1024) "M ",
Status from sys. dba_data_files where tablespace_name <> 'perfstat' order by tablespace_name, file_name;
5. tablespace information (percentage of space remaining in size)
Select DBA. tablespace_name tablespace,
Nvl (round (used. Bytes/(1024*1024), 0), 0) "used MB ",
Nvl (role (free. Bytes/(1024*1024), 0), 0) "Free MB ",
Round (nvl (used. bytes, 0) + nvl (free. bytes, 0)/(1024*1024), 0) total --,
-- Nvl (round (used. bytes/(1024*1024), 0), 0)/round (nvl (used. bytes, 0) + nvl (free. bytes, 0)/(1024*1024), 0) rate
From dba_tablespaces dBA,
(Select tablespace_name, sum (bytes) bytes
From dba_segments
Group by tablespace_name) used,
(Select tablespace_name, sum (bytes) bytes
From dba_free_space
Group by tablespace_name) free
Where DBA. tablespace_name = used. tablespace_name (+)
And DBA. tablespace_name = free. tablespace_name (+)
And DBA. tablespace_name <> 'perfstat'
Order by DBA. tablespace_name;

6. Check the rollback segment name, size, tablespace, and status.
Select substr (SYS. dba_rollback_segs.segment_id, 1, 5) "ID #",
Substr (SYS. dba_segments.owner, 1, 8) "owner ",
Substr (SYS. dba_segments.tablespace_name, 1, 17) "tablespace name ",
Substr (SYS. dba_segments.segment_name, 1, 17) "rollback name ",
Substr (SYS. dba_segments.bytes, 1, 15) "size (bytes )",
Substr (SYS. dba_segments.extents, 1, 6) "extent #",
Substr (SYS. dba_rollback_segs.status, 1, 10) "status"
From SYS. dba_segments, SYS. dba_rollback_segs
Where SYS. dba_segments.segment_name = SYS. dba_rollback_segs.segment_name and
SYS. dba_segments.segment_type = 'rollback' order by sys. dba_rollback_segs.segment_id;

7. Check database parameter information for parameters without default values
Col name format A20
Select name, value from V $ parameter where isdefault = 'false ';

8. Check whether the default or temporary tablespace of the Database User information is system tablespace.

Select user_id,
Substr (username, 1, 15) username,
Substr (password, 1, 15) password,
Substr (default_tablespace, 1, 15) "Default TBS ",
Substr (temporary_tablespace, 1, 15) "temporary TBS ",
Created, substr (profile, 1, 10) Profile
From SYS. dba_users order by username;

6.3 database optimization and Major Parameter Adjustment
The Oracle Database reads the initialization parameter file named init <Sid>. ora at startup. The parameter settings in this configuration file determine the performance of the entire Oracle database. First, we will briefly introduce the meaning of each parameter and then provide the recommended configuration. Except for special instructions, database restart is required for parameter changes. Based on the size of the database system, Oracle provides three recommended configuration files: small, medium, and large (small, medium, and large.
L db_block_size: the size of the data block, the minimum unit of data storage. In versions earlier than Oracle9i, this parameter cannot be changed after the database is created. The value range is 2 K, 4 K, 8 K, 16 K. The unit of 32 K is byte. In the application system, some data tables have many fields and the db_block_size setting is too small to store records in a single data block. Therefore, accessing a user record cannot be completed in one physical I/O operation, multiple I/O operations are required, resulting in performance degradation. In addition, a large number of varchar2 fields and data updates may cause row link and row migration. In addition to setting a large pctfree, db_block_size must be larger. We recommend that you set this parameter to 8 KB, which is specified when you create a database.
L db_block_buffers: The quantity of high-speed data blocks and the data blocks read from the hard disk. Db_block_buffers determines the number of ORACLE data blocks in the memory that can be stored in db_block_size. When the application reads data from the hard disk for the first time, the data is stored in the memory until the data is not accessed for a long time and cannot accommodate more data in the memory, the data is written back to the hard disk. The data will be read from the hard disk the next time you need to access it. The larger the parameter, the more likely Oracle will find the required data in the memory. The reading speed of the same data memory is dozens of times faster than that of the hard disk, so the response speed will be greatly improved. The product of db_block_size and db_block_buffers determines about 90% of the memory occupied by the Oracle database. If the server runs the database exclusively, it is recommended that the product of the two parameters account for 40% of the server's physical memory. After monitoring the data cache hit rate, make further adjustments.
L shared_pool_size: memory stores the SQL statement access path, related access permissions, table structure information location, in bytes. We recommend that you use the default large (large) file, which is 9000000 (bytes ). Use SQL statements to monitor the cache hit rate of the production machine and then adjust it accordingly.
L sort_area_size: Maximum number of bytes that can be sorted in memory. When an application sends an order by, group by, or index to a database, it needs to sort the data. The sorting speed in the memory is also faster than that in the hard disk. The larger the sort_area_size, the larger the sorting may be in the memory. We recommend that you use the default large (large) file, which is 524288 bytes in size. By further adjusting the monitoring of the dynamic view.
L log_buffer: the log cache size. For database DML/DDL statements, first write log_buffer and then write the log file. We recommend that you set the initial configuration to 512 KB. If the log cache waits for a significant increase, it generally does not exceed 1 MB.
L processes: the number of processes that access the Oracle database at the same time, including the number of persistent connections established by the backend processes (dbwr, lgwr, Chpt), enus, and cnus modules to the database, setting a large value does not have a negative impact. If it is set to a small value, a database connection failure error occurs. The recommended configuration is 150.
L dml_locks: the maximum number of locks that all users place in all tables at any time. The default value is four times the maximum number of transactions. You can also set a larger value without any negative impact. The recommended value is 2000.
L open_cursors: Number of cursors that can be opened simultaneously. Setting a large value does not negatively affect the table. If it is set to a small value, the game table cannot be opened. The recommended configuration is 500.
L db_files: number of database data files. We recommend that you set it to 200.
L rollback_segments: the name of the rollback segment. After creating the rollback segment, you can add the segment name here. In the OLTP environment, the number of rollback segments is 1/3 ~ of the maximum number of concurrent transactions ~ 1/4.
6.4 database backup/recovery
6.5 database monitoring/Maintenance
6.5.1 recommended monitoring methods
Statspack is a performance monitoring toolkit launched by Oracle for Oracle Enterprise Edition 8.1.6 and later. Used to replace the original utlbstat/utlestat. More data can be collected than utlbstat/utlestat, including SQL statements that consume more resources. At the same time, a lot of ratios (ratios) that are useful for performance tuning are calculated in advance ). It can monitor the overall running status of data more comprehensively. At the same time, the generated report file can be sent to Oracle for help analysis.
The main monitoring scope is:
L size of main memory parameters (db_block_buffers, db_block_size, log_buffer, shared_pool_size ).
L system load: the number of logs generated per second, logical reads of the database, physical reads, and transaction quantities.
L various cache hit rates. Such as the library cache hit rate, Dictionary cache hit rate, db block buffer hit rate, and sort ratio in memory.
L several types of events that cause slow system response time. By optimizing these events, the system response speed is improved.
L SQL statements sorted by resource consumption. Generally, slow system response speed is related to parameter settings and writing of nonstandard SQL statements. Optimize the captured SQL statements to improve the response speed.
L I/O load of each tablespace and each file in the tablespace. Use this part to determine whether the I/O of each file is balanced.
L rollback segment information: whether there is competition for rollback segments and whether there is excessive expansion of rollback segments.
L all parameters not set by default.
Create a statspack package:
L Installation File: In a UNIX operating system, the installation file is stored in the $ ORACLE_HOME/rdbms/Admin directory.
L users and files executed during creation
SQL> connect internal;
SQL> @? /Rdbms/admin/spcreate. SQL;
L main actions during installation:
1 install and generate a user perfstat/perfstat => spcusr. SQL,
Enter default tablespace/Temporary tablespace during installation
2. Create the required table => spctl. SQL
3 generate dbms_shared_pool and dbms_job PL/SQL package => spcpkg. SQL
Statspack package execution/data collection
When the system response speed drops significantly, you can use statspack to collect system running data, generate report files, and analyze the data.
L initial preparation: Set timed_statistics = true in init <Sid>. ora and make it take effect. Or use alter system set timed_statistics = true;
L use the created perfstat user connection
SQL> connect perfstat/perfstat;
L execute the first collection program:
SQL> execute statspack. Snap; L