ORACLE 数据库巡检报告脚本

**************************************************************** # made by brain zhang# products made by brain zhang is competitive products**************************************************************** SET MARKUP HTML ON SPOOL ON pre off entmap offSET ECHO OFFSET TERMOUT OFFSET TRIMOUT OFFset feedback offset heading onset linesize 200set pagesize 10000col tablespace_name format a15col total_space format a10col free_space format a10col used_space format a10col used_rate format 99.99column dbid new_value spool_dbidcolumn inst_num new_value spool_inst_numselect dbid from v$database where rownum = 1;select instance_number as inst_num from v$instance where rownum = 1;column spoolfile_name new_value spoolfileselect 'spool_'||(select name from v$database where rownum=1) ||'_'|| (select instance_name from v$instance where rownum=1) ||'_'||to_char(sysdate,'yy-mm-dd_hh24.mi')||'_static' as spoolfile_name from dual;spool &&spoolfile..htmlset line 140 pages 9000;col action_time for a30;col action for a10;col namespace for a15;col version for a20;col comments for a30;prompt system info check!/sbin/ip addr!hostname!df -h!tail -10000 $ORACLE_BASE/admin/$ORACLE_SID/bdump/al*|grep ora-!tail -10000 $ORACLE_BASE/admin/$ORACLE_SID/bdump/al*|grep err!tail -10000 $ORACLE_BASE/admin/$ORACLE_SID/bdump/al*|grep failprompt 1.database version and patch checkselect action_time,action, namespace,version,comments from dba_registry_history;prompt 2.database id checkselect dbid from v$database;prompt 3. database force logging、SUPPLEMENTAL_LOG_DATA_MIN、FLASHBACK_ON checkcol FORCE_LOGGING for a3;col SUPPLEMENTAL_LOG_DATA_MIN for a10;col SUPPLEMENTAL_LOG_DATA_PK for a3;col SUPPLEMENTAL_LOG_DATA_UI for a3;col SUPPLEMENTAL_LOG_DATA_FK for a3col SUPPLEMENTAL_LOG_DATA_ALL for a3;col FLASHBACK_ON for a15;selectFORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEME NTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_F K,SUPPLEMENTAL_LOG_DATA_ALL,FLASHBACK_ONfrom v$database;prompt 4. database SESSIONS_CURRENT、SESSIONS_HIGHWATERselect INST_ID,SESSIONS_CURRENT,SESSIONS_HIGHWATER from gv$license;prompt 5. database profilescol limit for a30;select * from dba_profiles order by 1;prompt 6. database languageselect userenv('language') from dual;prompt 7. database instance statuscol INSTANCE_NAME for a20;col host_name for a20;selectinst_id,instance_number,instance_name,host_name,statusfrom gv$instance;prompt 8. database sum sizeselect sum(bytes)/1024/1024/1024 as GB from dba_segments;prompt 9. database controlfileCOL NAME FOR A50;select * from v$controlfile;prompt 10. database logfileselect THREAD#,GROUP#,SEQUENCE#, BYTES/1024/1024,status,FIRST_TIME from v$log;col member for a50;select * from v$logfile;prompt 11. database archivearchive log list;prompt 12. database tablespace checkcol file_name for a50;col tablespace_name for a20;selectfile_id,tablespace_name,file_name,bytes/1024/1024,status,AUT OEXTENSIBLE,MAXBYTES/1024/1024from dba_data_files;SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)", ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)"USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKSFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) D,(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL --if have tempfileSELECT 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(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKSFROM DBA_TEMP_FILESGROUP BY TABLESPACE_NAME) D,(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE FROM V$TEMP_SPACE_HEADERGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);prompt 13. database backup!sh rman_back.shspool offexit;#check rman back scriptscat >rman_back.sh<<EOFrman target / <<EOFlist backup of database summary; quitEOF。