Solaris system Maintenance

5 system Maintenance
5.1 Solaris System
Involved Servers 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. Operating system basic information: UNAME-A will display the operating system name, hostname, operating system version information, operating system version information, hardware type, CPU type, platform information.
2. Kernel Information: Modify the/etc/system file to change the default kernel parameters, Modinfo display module information, sysdef display driver and kernel parameter information.
3. CPU Information:/usr/sbin/psrinfo-v display CPU state, quantity, CPU type and frequency.
4. Memory Information: prtconf | grep "Memory size" displays the memory size.
5. Hard disk/optical drive information: Iostat-en display hard disk and optical drive hardware address, various status information, model and hard disk size. This command allows you to check for any type of error.
6. Network card information: ifconfig-a, display network card name, status, Maximum transmission unit MTU,IP address, subnet mask, broadcast address.
7. HA Information:
8. Network information: NETSTAT-RN displays static routes and default routes.
9. Package pkg Information: pkginfo-l pkgname. Through it to determine whether a pkg is installed, and related to the pkgadd,pkgrm,pkgchk.
10. Patch Patch Information: showrev-p. Installing certain software requires a specific patch, judged by it.
11. File System Information: Df-k monitoring capacity to determine whether there is insufficient space.
12. User information: Who displays the current online user. Whodo displays the actions of the current user. User management through Useradd,groupadd or Admintool. The ID displays the login name,login ID of the current user, group name group name.
5. 1. 2 system Monitoring
1. CPU Performance monitoring: Vmstat/mpstat and SAR, Vmstat and SAR display summary information in SMP systems, while Mpstat can display the load per CPU. Vmstat monitors the number of R and B under Procs, which reflects the number of processes currently running and blocking, if the number of R is longer than the number of CPUs at the same time CPU Us+sy >80, indicating CPU processing capacity is insufficient.
2. Memory monitoring: VMSTAT, monitoring memory under the free and page Pi/po,pi/po reflect the number of units in K for each second in/out. If the number of free is small and the number of PI/PO is longer than 0, it indicates that there is not enough memory.
3. Hard drive monitoring: iostat-d and sar-d, you can see each hard disk and hard work of each partition
4. Exchange Zone monitoring: Swap-l and Swap-s, should try to place the swap area on the lighter load of the hard drive. The usage percentage of the swap area should not be too high.
5. HA System Monitoring:
6. Network monitoring: Netstat-in shows the workload of each NIC. Ipkts/opkts send packages and accept packages. Ierrs/oerrs,collis represents the errors and conflicts that occurred.
Ierrs/ipkts or Oerrs/opkts >2% indicates a serious network conflict.
7. Port monitoring: Monitor Whether a service is up or running properly,
Netstat-an |grep portnum or tcpdump |grep portnum.
8. Hardware fault diagnosis:/usr/platform/' uname-m '/sbin/prtdiag-v determine whether there is hardware failure through the output results.
9. SYSLOGD log monitoring, SYSLOGD background process to the system running information into the/var/adm/messages file, through it to monitor the presence of any abnormal software and hardware error messages.
Earlier information exists in the Messages.0,message.1 file in the same directory.
Mail monitoring: Some users through the crontab scheduled tasks, the task is completed, the cron process will be sent to the user mail, by looking at mail to determine whether the task is completed. In the event of a system exception, SYSLOGD is sent to the root user mail. Therefore, the root user mail needs special attention.
11. Process monitoring: Monitoring the process of special resource consumption 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 the activity processes of the statistical system and sort them in the order required.
12. System operating time monitoring: Uptime measure the length of a machine and the system load command. The greater the time the uptime shows, the more stable the machine is.
13. System Run Level monitoring: WHO-R Displays the current machine run level.


5. 2 Linux System
Front-End machines involved: FEP1 (218.29.0.227), Fep2 (218.29.0.228), Fep3 (218.29.0.229), Fep4 (218.29.0.230), FEP5 (218.29.0.231), FEP6 ( 218.29.0.232), FEP7 (218.29.0.233), 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. Operating system basic information: UNAME-A
2. Kernel information:/proc/sys/kernel directory of the file records the various kernel parameters, Modinfo display module information, sysdef display driver and kernel parameter information.
3. CPU information:.
4. Memory Information: prtconf | grep "Memory size" displays the memory size.
5. Hard disk/optical drive information:.
6. Network card information: ifconfig-a, display network card name, status, Maximum transmission unit MTU,IP address, subnet mask, broadcast address.
7. Network information: NETSTAT-RN displays static routes and default routes.
8. Package pkg Information: rpm (Redhat Package Manager). Through it to determine whether a PKG installation, rpm-q query, rpm-i installation
9. Patch Patch Information: File system Information: DF-K monitoring capacity determine whether there is insufficient space.
10. User information: Who displays the current online user. Add users through the KDE User Manager tool. The ID displays the login name,login ID of the current user, group name 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 running and blocking, if the number of R is longer than the number of CPUs at the same time CPU Us+sy >80, indicating CPU processing capacity is insufficient.
2. Memory monitoring: VMSTAT, monitoring memory under the free and page Pi/po,pi/po reflect the number of units in K for each second in/out. If the number of free is small and the number of PI/PO is longer than 0, it indicates that there is not enough memory.
3. HDD monitoring: Iostat-d and sar-d
4. Exchange Zone monitoring Swap-l
5. Network Monitoring netstat-in
6. Port monitoring: Monitoring whether a service starts Netstat-an |grep portnum or tcpdump |grep portnum
7. Hardware fault diagnosis:.
8. SYSLOGD log monitoring, SYSLOGD background process to the system running information into the/var/adm/messages file, through it to monitor the presence of any abnormal software and hardware error messages.
Earlier information exists in the Messages.0,message.1 file in the same directory.
9. Mail monitoring: Some users through the crontab scheduled tasks, the task is completed, the cron process will be sent to the user mail, by looking at mail to determine whether the task is completed. In the event of a system exception, SYSLOGD is sent to the root user mail. Therefore, the root user mail needs special attention.
10. Process monitoring: Monitoring the process of special resource consumption through PS or top.


6. Database Maintenance
6. 1 Installation of the database
6. 1. 1 preparatory work of the operating system
1. Modification of kernel parameters
Edit Modify/etc/system File
Set shmsys:shminfo_shmmax=4294967295
Set Shmsys:shminfo_shmmin=1
Set shmsys:shminfo_shmmni=100
Set shmsys:shminfo_shmseg=10
Set semsys:seminfo_semmns=2000
Set semsys:seminfo_semmsl=1000
Set semsys:seminfo_semmni=100
Set semsys:seminfo_semopm=100
Set semsys:seminfo_semvmx=32767
2. Check the required operating system patch
You can meet Oracle installation requirements by sunsolve.sun.com a specific version of the patch set and installing it.
3. Add the operating system user/group
L Groupadd dba
L Groupadd Oinstall
L useradd-c "Oracle dba"-d/home/oracle-g oinstall-g dba
L Modify/etc/passwd file, change user login shell or other information

4. Setting of User environment variables
6. 1. 2 Installation of the database
6. 1. 3 Database Patch Installation
1. Patch Download: Oracle database Patch from Oracle Metalink Web site, after successful login, select Oracle Family in the product databse drop-down box in the Patch page, product drop-down box, select "RDBMS Server", select "Oracle 8.1.7.4" in the Release Drop-down box, select "Sun Solaris OS (SPARC) 64-bit" In the Platform dropdown box, and select American in the Drop-down box language 中文版 (US), select "Latest Product patches or minipacks" in the Limit Search to Drop-down box and click Submit. Click Download to download patch on the page that appears later.
2. Patch installation: Patch download after the first need to decompress, installation and database installation method similar to the start of the Runinstaller installer, select the solution after the completion of the Patch directory of the product file to install. Pay special attention to the patch installation, the database must be in a graceful shutdown state.
3. After installation Complete inspection: SELECT * from V$version; Check that the version number of each product is upgraded from 8.1.7.0 to 8.1.7.4.
6. 2 Summary of database basic information
1. Version information
Sql>select * from V$version;
2. Database name, file 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. The database file includes three categories: control files, log files, data file 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, table space 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. Table space information (% of the remaining space used in size)
SELECT Dba.tablespace_name tablespace,
NVL (ROUND (used.bytes/(1024*1024), 0), 0) "Used MB",
NVL (ROUND (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. Roll back the information to see the rollback segment name, size, table space, 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. Database parameter Information view parameters that do not use default values
Col name format A20
Select Name,value from V$parameter where isdefault= ' FALSE ';

8. Database user information check for a user's default or temporary tablespace as 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 main parameter adjustment
The Oracle database reads the initialization parameter file named Rule Init<sid>.ora when it starts. Each parameter setting in this profile determines the performance of the entire Oracle database, briefly describing the meaning of each parameter, and then giving the recommended configuration. In addition to special instructions, the introduction of parameter changes requires a database restart operation. Depending on the size of the database system, Oracle offers a small, medium, Large (Small,medium,large) three recommended configuration file, configured as a reference.
L Db_block_size: Data block size, the smallest unit of data storage, which is immutable after the database creation is complete in oracle9i previous versions. The optional range is the 2k,4k,8k,16k,32k unit is byte. In the application system, some data table fields are more, the db_block_size setting is too small to be stored in a single block of data, so access to a user record can not be completed in a physical I/O operation, requiring multiple I/O, resulting in performance degradation; VARCHAR2 type fields and data updates can cause row links and row migrations, and db_block_size larger than setting Pctfree. The recommended configuration is 8K, which is specified when the database is created.
L Db_block_buffers: How much of the data high-speed block, which holds the data blocks read out from the hard disk. Db_block_buffers determines the number of Oracle data blocks in memory that can be stored in db_block_size size. The application reads the data from the hard disk the first time it accesses it, and then the data is stored in memory until the data is inaccessible for long periods of time, and when more data is not in memory, the data is written back to the hard disk and read from the hard disk the next time it needs to be accessed. The larger the parameter, the greater the likelihood that Oracle will find the data needed in memory. The same data memory reads faster than the hard drive dozens of times times, so the response speed will be greatly improved. The Db_block_size and the Db_block_buffers product determine that the Oracle database occupies about 90% of the memory. If the server is dedicated to running the database, it is recommended that the product of two parameters account for 40% of the server's physical memory, and make further adjustments after monitoring the hit rate of the data cache.
L Shared_pool_size: Store SQL statement access path in memory, related access rights, table structure information location, in bytes. It is recommended to use a large (large) file with a default configuration of 9000000 (bytes). Adjust the production machine cache hit rate after the SQL statement is monitored.
L Sort_area_size: The maximum number of bytes that can be sorted in memory. A sort operation is required when an application emits a similar orderby,group by or creates an index to the database. Sorting is done in memory as fast as the hard drive. The larger the sort_area_size, the greater the likelihood that the sort will be completed in memory. A large (large) file with a default configuration is recommended with a size of 524288 bytes. Further adjustments are made to the monitoring of the dynamic view.
L Log_buffer: Log cache size, the DML/DDL of the database, and so on, are first written to Log_buffer and then written to the log file. Recommended initial configuration of 512k, if the log cache waiting for obvious, and then increase, generally no more than 1M.
L Processes: The number of simultaneous accesses to the Oracle database, including background processes (DBWR,LGWR,CHPT) and the number of long connections to the database established by each module of Enus,cnus, is not negatively affected by setting the larger If it is small, it can cause errors that cannot be connected to the database. The recommended configuration is 150.
L Dml_locks: It represents the maximum number of locks placed by all users at any time in all tables. The default value is four times times the maximum number of transactions. You can also set a larger point, there will be no negative impact. The recommendation is 2000.
L Open_cursors: Number of cursors that the user can open at the same time. Setting a larger point does not have a negative effect, if you are small, you will not be able to open the list of errors. The recommended configuration is 500.
L Db_files: Number of database data files. The recommended setting is 200.
L Rollback_segments: The name of the rollback segment where the user adds the name after creating the rollback segment himself. In an OLTP environment, the number of rollback segments is the 1/3~1/4 of the maximum number of concurrent transactions.
6. 4 Backup/restore of database
6. 5 Database Monitoring/Maintenance
6. 5. 1 monitoring methods recommended for use
Statspack's introduction: Statspack is the Oracle Enterprise Edition 8.1.6 and above version of the Performance Monitoring toolkit. Used to replace the original utlbstat/utlestat. More data can be collected than utlbstat/utlestat, including more resources-consuming SQL. At the same time, many ratios (ratios) that are useful for performance tuning are calculated in advance. Can be more comprehensive monitoring the overall operation of the data. The resulting file can also be sent to Oracle Company for assistance analysis.
The main areas of monitoring are:
L The size of the main memory parameters (Db_block_buffers, db_block_size, Log_buffer, shared_pool_size).
L System Load: The amount of log generated per second, logical reading of the database, physical reading, number of transactions, etc.
L various cache hits. such as library cache hit ratio, dictionary cache hit ratio, db block buffer hit ratio, sort in memory rate, etc.
L Several kinds of events that cause slow response time of system, improve the system response speed by optimizing these kinds of events.
L A SQL statement sorted by various resource consumption resources. Typically, the system responds slowly, in addition to the parameter settings, and is related to the writing of an irregular SQL statement. Improve response speed by optimizing captured SQL statements.
L I/O load of each table space and the files that make up the table space. This part of the content to determine whether the various file I/O is balanced.
L rollback Segment Information: Reflect whether there is a rollback segment of the competition, whether there is excessive expansion of the rollback segment.
L All parameters that are not set by default.
Statspack Software package Creation:
L installation files: Under Unix operating system, the installation files are stored in the $oracle_home/rdbms/admin directory.
L EXECUTE the user and execute the file at the time of creation
Sql>connect internal;
sql>@?/rdbms/admin/spcreate.sql;
L The main actions during the installation process:
1 installation generates a user Perfstat/perfstat =>spcusr.sql,
At install time: DEFAULT tablespace/temporary tablespace
2 Create the required table =>spctab.sql
3 Generate Dbms_shared_pool and dbms_job pl/sql packages =>spcpkg.sql
Statspack software package execution/data acquisition
When the response speed of the system decreases obviously, the data acquisition can be carried out through Statspack, and the report file is generated and analyzed.
L Initial Preparation: Set the timed_statistics=true in the Init<sid>.ora and let it take effect. Or use alter system set timed_statistics=true;
L Use the created Perfstat user connection
Sql>connect Perfstat/perfstat;
• Implementation of the first acquisition process:
Sql>execute Statspack. Snap;l