Oracle healthcheck report ?
Chapter 2 Service IXSDoduction
The aim of the service is to help the customer to check the current status of the database through
the Oracle database health check-up service, to find the potential problems, and to give suggestions and recommend methods to resolve the problems.
2.1 Aim of Service
? XXX China has plentiful experience on Oracle DB System. The aim of the check-up service is
to find the potential problems, maximize the usability of the database, and minimize the adverse affect of the improper configuration.
? Give helpful suggestions according to the database status of the XXXXX to avoid the similar
problems in the future.
2.2 Service Staff
Engineers from the XXX China will be in charge of the database health check-up. The cooperation and supervision is necessary throughout the check-up.
? Member of the XXX China workgroup: Name Position TAE ADM Oracle Engineer Tel E-mail
? Member of the XXXXX workgroup: Position System Administrator Tel E-mail 2.3 Items to be Checked
No. Type 1 2 Rx4640 Rx4640 Name（HostName） IP Address business Db server1 Db server2 Serial number Service record number Service doc
)
Page 5 of 57
Oracle healthcheck report ?
2.4 summary of check-up
Name XXXX01 Application Database Error 1. None 2. Others Error Suggestion Level Remarks System Status remain no change refer to last check, no obvious error been found except lack of memory
Error Level:
? Normal:No abnormal conditions and errors are found during the database health
check.
? Error grade high:Daatbase is failed,high risk to the business,need to be repaired
asap;
? Error grade medium:Database is warning, the medium risk to the business,suggest
to be repaired asap;
? Error grade low:need to be repaired in a selective time;
Summary :
1. we suggest that the database backup/restore strategy be checked periodically according to the
variable data volume to make sure it meets the system operating demand.; 2. when startup oracle database ,don’t forget to start Listener ; 3. ‘coXSDol file parallel write’ wait event
when oracle server process update all coXSDolfiles and the disk i/o of coXSDolfile is very high, so ‘coXSDol file parallel write’ wait event happen . The following points may help:
? ?
We recommend strongly that the db’s coXSDolfiles have been located in defferent disks. If system support asynch i/o ,then suggest to use disk_asynch_io .
4. ‘db file sequential read’ wait event
This signifies a wait for an I/O read request to complete. This call differs from \ in that a sequential read reads data into contiguous memory (whilst a scattered read reads multiple blocks and scatters them into different buffers in the SGA): The following points may help: ? ? ? ?
Check for SQL using unselective index scans ;
A larger buffer cache can help - test this by actually increasing
It can help to place files which incur frequent index scans on disks which have are buffered by an O/S file system cache. Often this will allow some of Oracles read requests to be satisfied from the OS cache rather than from a real disk IO.
Service doc
)
Page 6 of 57
Oracle healthcheck report ?Chapter 3 Configuration information of system and database
3.1. Configuration of host and OS
3.1.1. Kernel parameter to relation to OS
Host name: Host provider: OS version: CPU count: Physical MEM size: Swap size: Mounts information: XSDUD11F XXX 11.23 3 4GB 12G Filesystem kbytes used avail %used Mounted on /dev/vg00/lvol3 2097152 248384 1834456 12% / /dev/vg00/lvol1 262144 169464 92000 65% /stand /dev/vg00/lvol8 15728640 2232512 13396456 14% /var /dev/vg00/lvol11 12582912 3483189 8530991 29% /var/adm/crash /dev/vg00/lvol7 4194304 2636880 1545304 63% /usr /dev/vg00/lvol4 4194304 421000 3748592 10% /tmp /dev/vg00/lvol6 4194304 2626080 1555992 63% /opt /dev/vg00/lvol5 6291456 772520 5476944 12% /home /dev/vg00/lvol12 4194304 259470 3696706 7% /home/patch /dev/vg00/lvolora 6291456 4673832 1516620 76% /home/oracle9i /dev/vg_arc/lvol01 20971520 21675 19640550 0% /ext104 /dev/vg_arc/lvol02 20963328 23290 19632956 0% /ext105 /dev/vg_arc_bk/lvol01 62902272 28928359 31852766 48% /ext106 /dev/vg_app/lvol01 5242880 760237 4203852 15% /ext101 /dev/vg_app/lvol02 10477568 5138400 5020566 51% /ext102 /dev/vg_fsbk/lvol03 62709760 34131643 26795914 56% /ext103 $ 1 Y 0 Instances of ORACLE: Running Demand （8×5,7×24） Max time of shutdown: IS RAC N Node count: 1 The same Y configuration of all nodes?: 3.1.2. Kernel parameter to relation to DB /stand/system
Service doc
)
Page 7 of 57
Oracle healthcheck report Value 2147483648 1024 1024 16424 16428 8208 2048 100 32768 16384 ?Parameter Name max shared memory segment size (SHMMAX) min shared memory segment size (SHMMIN) shared memory identifiers (SHMMNI) max attached shm segments per process (SHMSEG) semaphore identifiers (SEMMNI) semaphores in system (SEMMNS) Undo structures in system (SEMMNU) max semaphores per id (SEMMSL) max operations per semop call (SEMOPM) max undo eXSDies per process (SEMUME) semaphore maximum value (SEMVMX) adjust on exit max value (SEMAEM)
3.2. Database configuration
3.3.1. General
Instance Name DB Name DB Version SQL*Net Version DB_BLOCK_SIZE: SGA: XXXX01 XXXX01 9.2.0.8.0 9.2.0.8.0 8192 Total System Global Area 1336886448 bytes Fixed Size 726192 bytes Variable Size 452984832 bytes Database Buffers 872415232 bytes Redo Buffers 10760192 bytes N using file system 6 Y Database log mode Archive Mode Automatic archival Enabled Archive destination /home/oracle9i/ora9i/admin/XXXX01/arc2/ Oldest online log sequence 16784 Next log sequence to archive 16785 Current log sequence 16785 Y 0 N 1 N N Y Using or not raw: LV Log generate frenqucy Archive or not: Archive information: Running requiring（8×5,7×24）: Max time of shutdown: RACr or not: Instncce count: Interconnect information: Interconnect speed: The same configuration or not:
Service doc
)
Page 8 of 57