oracle 巡检 健康检查模版.doc

Service Document Page 1 of 57 rOracle SHC NECSL 060115 Oracle DB Healthcheck Report XXXXX CHINA Ltd Oracle healthcheck report Service doc Page 2 of 57 Document information Service Name Oracle HealthCheck Report Supporting Engineer Document Version 1 0 Service Phrase 7 2009Document Version Date 19 7 2009 Author Prepared Date 19 7 2009 Reviewer Review Date Accept List acceptorAimdateTEL DAX Revision History Version Version Date Revisor Description File Name 1 019 7 2009Bryan Miao Draftoracle HealthCheck 20090719 doc notice The report doesn t represent any official standpoint It is only used to discuss technological information Oracle healthcheck report Service doc Page 3 of 57 Contents CHAPTER 1 DOCUMENT IXSDODUCTION 4 1 1AIM OF THE DOCUMENT 4 1 2MAINTENANCE 4 1 3OWNER OF THE DOCUMENT 4 CHAPTER 2 SERVICE IXSDODUCTION 5 2 1AIM OF SERVICE 5 2 2SERVICE STAFF 5 2 3ITEMS TO BE CHECKED 5 2 4SUMMARY OF CHECK UP 6 CHAPTER 3 CONFIGURATION INFORMATION OF SYSTEM AND DATABASE 6 3 1 CONFIGURATION OF HOST AND OS 7 3 2 DATABASE CONFIGURATION 8 3 3 SYSTEM WAIT EVENT 28 3 4 NET8 CONFIGURATION 28 CHAPTER 4 SYSTEM AVAILABILITY 31 4 1 BACKUP 31 4 2 RESTORE 31 4 3 DB MONITOR 31 CHAPTER 5 SYSTEM INTEGRITY 34 5 1 POLICY OF SECURUTY 34 CHAPTER 6 SYSTEM PERFORMENCE 35 6 1 OS PERFORMANCE INFO 35 6 2 DB PERFORMANCE 38 6 3 OTHER INFO LOCK ENQUEUE LATCH 40 CHAPTER 7 APPENDIX 40 7 1 ORACLE DATABASE STATSPACK RESPORT 40 Oracle healthcheck report Service doc Page 4 of 57 Chapter 1 Document IXSDoduction 1 1 Aim of the Document Oracle database health check up is the important part of the integrated services supported by XX China The aim of the document is to confirm the content and effect of the database health check and to help the XXXXX to know the current condition of the database 1 2 Maintenance The report of the health check up service will be created by the engineers of XXX China after the check is completed and will be submitted to the XXXXX 1 3 Owner of the Document If you have any questions or suggestions to the document please contact Ji Lei who is the engineers of the XXX China Oracle healthcheck report Service doc Page 5 of 57 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 1Aim 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 2Service 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 NamePositionTelE mail TAE ADM Oracle Engineer Member of the XXXXX workgroup PositionTelE mail System Administrator 2 3Items to be Checked No TypeName HostNam e IP AddressbusinessSerial numberService record number 1Rx4640Db server1 2Rx4640 Db server2 Oracle healthcheck report Service doc Page 6 of 57 2 4summary of check up Name Applicati on Database Error Error Level Suggestion 1 None 2 OthersRemarks XXXX01 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 db file scattered read 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 See if partitioning can be used to reduce the amount of data you need to look at 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 Oracle healthcheck report Service doc Page 7 of 57 Chapter 3 Configuration information of system and database 3 1 Configuration of host and OS 3 1 1 Kernel parameter to relation to OS HostHost name name XSDUD11F HostHost providerprovider XXX OSOS version version 11 23 CPUCPU countcount 3 PhysicalPhysical MEMMEM sizesize 4GB SwapSwap sizesize 12G MountsMounts informationinformation 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 InstancesInstances ofof ORACLE ORACLE 1 RunningRunning DemandDemand 8 5 7 248 5 7 24 Y MaxMax timetime ofof shutdownshutdown 0 ISIS RACRACN NodeNode countcount 1 TheThe samesame configurationconfiguration ofof allall nodes nodes Y 3 1 2 Kernel parameter to relation to DB stand system Oracle healthcheck report Service doc Page 8 of 57 Parameter NameValue max shared memory segment size SHMMAX 2147483648 min shared memory segment size SHMMIN shared memory identifiers SHMMNI 1024 max attached shm segments per process SHMSEG 1024 semaphore identifiers SEMMNI 16424 semaphores in system SEMMNS 16428 Undo structures in system SEMMNU 8208 max semaphores per id SEMMSL 2048 max operations per semop call SEMOPM max undo eXSDies per process SEMUME 100 semaphore maximum value SEMVMX 32768 adjust on exit max value SEMAEM 16384 3 2 Database configuration 3 3 1 General Instance NameXXXX01 DB NameXXXX01 DB Version9 2 0 8 0 SQL Net Version9 2 0 8 0 DB BLOCK SIZE 8192 SGA Total System Global Area 1336886448 bytes Fixed Size 726192 bytes Variable Size 452984832 bytes Database Buffers 872415232 bytes Redo Buffers 10760192 bytes Using or not raw LV N using file system Log generate frenqucy6 Archive or not Y Archive information 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 Running requiring 8 5 7 24 Y Max time of shutdown 0 RACr or not N Instncce count 1 Interconnect information N Interconnect speed N The same configuration or not Y Oracle healthcheck report Service doc Page 9 of 57 3 3 2 DB parameter The parameter files locate in directory ORACLE HOME dbs unix named initSID ora and spfileSID ora The DB use spfile file priority if there are pfile and spfile NAME TYPE VALUE O7 DICTIONARY ACCESSIBILITY boolean FALSE active instance count integer aq tm processes integer 1 archive lag target integer 0 audit file dest string rdbms audit audit sys operations boolean FALSE audit trail string NONE background core dump string partial background dump dest string home oracle9i ora9i admin XXXX01 bdump backup tape io slaves boolean FALSE bitmap merge area size integer 1048576 blank trimming boolean FALSE buffer pool keep string buffer pool recycle string circuits integer 0 cluster database boolean FALSE cluster database instances integer 1 cluster interconnects string commit point strength integer 1 compatible string 9 2 0 0 0 coXSDol file record keep time integer 7 coXSDol files string home oracle9i ora9i oradata XXXX01 coXSDol01 ctl home oracle9i ora9i oradata XXXX01 coXSDol02 ctl home oracle9i ora9i oradata XXXX01 coXSDol03 ctl core dump dest string home oracle9i ora9i admin XXXX01 cdump cpu count integer 3 create bitmap area size integer 8388608 cursor sharing string EXACT cursor space for time boolean FALSE db 16k cache size big integer 0 db 2k cache size big integer 0 db 32k cache size big integer 0 db 4k cache size big integer 0 db 8k cache size big integer 0 db block buffers integer 0 db block checking string TRUE db block checksum boolean TRUE Oracle healthcheck report Service doc Page 10 of 57 db block size integer 8192 db cache advice string ON db cache size big integer 872415232 db create file dest string db create online log dest 1 string db create online log dest 2 string db create online log dest 3 string db create online log dest 4 string db create online log dest 5 string db domain string db file multiblock read count integer 32 db file name convert string db files integer 200 db keep cache size big integer 0 db name string XXXX01 db recycle cache size big integer 0 db writer processes integer 3 dblink encrypt login boolean FALSE dbwr io slaves integer 0 dg broker config file1 string dbs dr1 dat dg broker config file2 string dbs dr2 dat dg broker start boolean FALSE disk asynch io boolean TRUE dispatchers string distributed lock timeout integer 60 dml locks integer 2496 drs start boolean FALSE enqueue resources integer 2716 event string fal client string fal server string fast start io target integer 0 fast start mttr target integer 300 fast start parallel rollback string LOW file mapping boolean FALSE filesystemio options string asynch fixed date string gc files to locks string global context pool size string global names boolean FALSE hash area size integer 1048576 Oracle healthcheck report Service doc Page 11 of 57 hash join enabled boolean TRUE hi shared memory address integer 0 XXXux sched noage integer 0 hs autoregister boolean TRUE ifile file instance groups string instance name string XXXX01 instance number integer 0 java max sessionspace size integer 0 java pool size big integer 33554432 java soft sessionspace limit integer 0 job queue processes integer 10 large pool size big integer 33554432 license max sessions integer 0 license max users integer 0 license sessions warning integer 0 local listener string lock name space string lock sga boolean FALSE log archive dest string log archive dest 1 string LOCATION home oracle9i ora9i admin XXXX01 arc1 log archive dest 10 string log archive dest 2 string LOCATION home oracle9i ora9i admin XXXX01 arc2 log archive dest 3 string log archive dest 4 string log archive dest 5 string log archive dest 6 string log archive dest 7 string log archive dest 8 string log archive dest 9 string log archive dest state 1 string enable log archive dest state 10 string enable log archive dest state 2 string enable log archive dest state 3 string enable log archive dest state 4 string enable log archive dest state 5 string enable log archive dest state 6 string enable log archive dest state 7 string enable log archive dest state 8 string enable log archive dest state 9 string enable log archive duplex dest string log archive format string XXXX01 s arc Oracle healthcheck report Service doc Page 12 of 57 log archive max processes integer 2 log archive min succeed dest integer 1 log archive start boolean TRUE log archive trace integer 0 log buffer integer 10485760 log checkpoint interval integer 0 log checkpoint timeout integer 1800 log checkpoints to alert boolean FALSE log file name convert string log parallelism integer 1 logmnr max persistent sessions integer 1 max commit propagation delay integer 700 max dispatchers integer 5 max dump file size string UNLIMITED max enabled roles integer 30 max rollback segments integer 124 max shared servers integer 20 mts circuits integer 0 mts dispatchers string mts listener address string mts max dispatchers integer 5 mts max servers integer 20 mts multiple listeners boolean FALSE mts servers integer 0 mts service string XXXX01 mts sessions integer 0 nls calendar string nls comp string nls currency string nls date format string nls date language string nls dual currency string nls iso currency string nls language string AMERICAN nls length semantics string BYTE nls nchar conv excp string FALSE nls numeric characters string nls sort string nls territory string AMERICA nls time format string nls time tz format string nls timestamp format string nls timestamp tz format string Oracle healthcheck report Service doc Page 13 of 57 object cache max size percent integer 10 object cache optimal size integer 102400 olap page pool size integer 33554432 open cursors integer 300 open links integer 4 open links per instance integer 4 optimizer dynamic sampling integer 1 optimizer features enable string 9 2 0 optimizer index caching integer 0 optimizer index cost adj integer 100 optimizer max permutations integer 2000 optimizer mode string CHOOSE oracle trace collection name string oracle trace collection path string otrace admin cdf oracle trace collection size integer 5242880 oracle trace enable boolean FALSE oracle trace facility name string oracled oracle trace facility path string otrace admin fdf os authent prefix string ops os roles boolean FALSE parallel adaptive multi user boolean FALSE parallel automatic tuning boolean FALSE parallel execution message size integer 2152 parallel instance group string parallel max servers integer 5 parallel min percent integer 0 parallel min servers integer 0 parallel server boolean FALSE parallel server instances integer 1 parallel threads per cpu integer 4 partition view enabled boolean FALSE pga aggregate target big integer 325058560 plsql compiler flags string INTERPRETED plsql native c compiler string plsql native library dir string plsql native library subdir count integer 0 plsql native linker string plsql native make file name string plsql native make utility string plsql v2 compatibility boolean FALSE pre page sga boolean FALSE processes integer 512 Oracle healthcheck report Service doc Page 14 of 57 query rewrite enabled string FALSE query rewrite integrity string enforced rdbms server dn string read only open delayed boolean FALSE recovery parallelism integer 0 remote archive enable string true remote dependencies mode string TIMESTAMP remote listener string remote login passwordfile string NONE remote os authent boolean FALSE remote os roles boolean FALSE replication dependency tracking boolean TRUE resource limit boolean FALSE resource manager plan string rollback segments string row locking string always serial reuse string DISABLE serializable boolean FALSE service names string XXXX01 session cached cursors integer 0 session max open files integer 10 sessions integer 568 sga max size big integer 1336886448 shadow core dump string partial shared memory address integer 0 shared pool reserved size big integer 16777216 shared pool size big integer 335544320 shared server sessions integer 0 shared servers integer 0 sort area retained size integer 0 sort area size integer 524288 spfile string dev vg ora rlvol 01 SPFILE sql92 security boolean FALSE sql trace boolean FALSE sql version string NATIVE standby archive dest string dbs arch standby file management string MANUAL star transformation enabled string FALSE statistics level string TYPICAL tape asynch io boolean TRUE thread integer 0 Oracle healthcheck report Service doc Page 15 of 57 timed os statistics integer 0 timed statistics boolean TRUE trace enabled boolean TRUE tracefile identifier string transaction auditing boolean TRUE transactions integer 624 transactions per rollback segment integer 5 undo management string AUTO undo retention integer 10800 undo suppress errors boolean FALSE undo tablespace string UNDO use indirect data buffers boolean FALSE user dump dest string home oracle9i ora9i admin XXXX01 udump utl file dir string ext231 db tool workarea size policy string AUTO 3 3 3 CoXSDolfile information The db s structure has been recorded in the coXSDolfile including DB name datafile location online redolog location tablespace name current redolog SCN checkpoint information history redolog backup and so on There is one coXSDolfile in database at least To guard safely recommend multiple coXSDolfile and locate on different disks Namelocation coXSDol01 ctl home oracle9i ora9i oradata XXXX01 coXSDol02 ctl home oracle9i ora9i oradata XXXX01 coXSDol03 ctl home oracle9i ora9i oradata XXXX01 3 3 4 CoXSDolfile information PARAMETERVALUES Maxlogfiles5 Maxlogmembers3 Maxdatafiles1024 Maxinstances1 Maxloghistory226 3 3 5 Online redolog information GROUP FILEBytes M STATUS Oracle healthcheck report Service doc Page 16 of 57 home oracle9i ora9i oradata XXXX01 redo11 log511ONLINE1 home oracle9i ora9i oradata XXXX01 redo12 log511ONLINE home oracle9i ora9i oradata XXXX01 redo21 log511ONLINE2 home oracle9i ora9i oradata XXXX01 redo22 log511ONLINE home oracle9i ora9i oradata