oracle巡检
1，编写oracle巡检脚本并赋
#!/bin/bash # script_name: oramonitor.sh # Author: jiajia.zhang # Email:329575816@qq.com # usage: # chmod +x oramonitor.sh echo "it is start" file_output=141.128.251.111os_oracle_summary_`date +%y%m%d`.html#巡检的结果文件 td_str='' th_str='' export NLS_LANG='american_america.AL32UTF8' #html代码 create_html_css(){ echo -e "<html> <head> <style type="text/css"> body {font:12px Courier New,Helvetica,sansserif; color:black; background:White;} table,tr,td {font:12px Courier New,Helvetica,sansserif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 12px Courier New,Helvetica,sansserif; color:White; background:#0033FF; padding:0px 0px 0px 0px;} h1 {font:bold 12pt Courier New,Helvetica,sansserif; color:Black; padding:0px 0px 0px 0px;} </style> </head> <body>" } create_html_head(){ echo -e "<h1>$1</h1>" } create_table_head1(){ echo -e "<table width="68%" border="1" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">" } create_table_head2(){ echo -e "<table width="100%" border="1" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">" } create_td(){ td_str=`echo $1 | awk 'BEGIN{FS="|"}''{i=1; while(i<=NF) {print "<td>"$i"</td>";i++}}'` } create_th(){ th_str=`echo $1|awk 'BEGIN{FS="|"}''{i=1; while(i<=NF) {print "<th>"$i"</th>";i++}}'` } create_tr1(){ create_td "$1" echo -e "<tr> $td_str </tr>" >> $file_output } create_tr2(){ create_th "$1" echo -e "<tr> $th_str </tr>" >> $file_output } create_tr3(){ echo -e "<tr><td> <pre style=\"font-family:Courier New; word-wrap: break-word; white-space: pre-wrap; white-space: -moz-pre-wrap\" > `cat $1` </pre></td></tr>" >> $file_output } create_table_end(){ echo -e "</table>" } create_html_end(){ echo -e "</body></html>" } #表空间信息 ora_dbfile_info(){ echo "set lines 2500; col TABLESPACE_NAME for a15; col FILE_NAME for a60; select tablespace_name,file_name,status,BYTES/(1024*1024) M,autoextensible,increment_by from dba_data_files order by tablespace_name,file_name;" >ora_sql.sql sqlplus $sqlstr as sysdba <ora_sql.sql>>/tmp/tmpora_dbfile_`date +%y%m%d`.txt } #表空间使用率 ora_dbfile_useage_info(){ echo "set line 2500; col TABLESPACE_NAME for a14; SELECT UPPER(F.TABLESPACE_NAME) TABLESPACE_NAME,D.TOT_GROOTTE_MB TOT_GROOTTE_MB,D.TOT_GROOTTE_MB - F.TOTAL_BYTES sy,TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' pers,F.TOTAL_BYTES TOTAL_BYTES,F.MAX_BYTES MAX_BYTES FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024*1024), 2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 * 1024*1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE where tablespace_name<> 'USERS' GROUP BY TABLESPACE_NAME) F,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024*1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES where tablespace_name<> 'USERS' GROUP BY TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 1;" >ora_sql.sql sqlplus $sqlstr as sysdba <ora_sql.sql>>/tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt } #asm存储信息 ora_asm_useage_info(){ echo "set line 2500; select name,total_mb,free_mb,cold_used_mb from v\$asm_diskgroup;" >ora_sql.sql sqlplus $sqlstr as sysdba <ora_sql.sql>>/tmp/tmpora_asm_useage_`date +%y%m%d`.txt } #归档使用率及归档设置大小 ora_archive_info(){ echo "set linesize 333; col FILE_TYPE for a13; select FILE_TYPE,PERCENT_SPACE_USED,PERCENT_SPACE_RECLAIMABLE,NUMBER_OF_FILES from v\$flash_recovery_area_usage where FILE_TYPE = 'ARCHIVED LOG'; col NAME for a40; select NAME,SPACE_LIMIT/1024/1024 ,SPACE_USED/1024/1024 ,SPACE_RECLAIMABLE,NUMBER_OF_FILES from v\$recovery_file_dest;" >ora_sql.sql sqlplus $sqlstr as sysdba <ora_sql.sql>>/tmp/tmpora_archive_`date +%y%m%d`.txt } #表空间状态信息 ora_dbfile_status(){ echo "set lines 2500; col TABLESPACE_NAME for a15; col FILE_NAME for a60; select FILE_NAME,TABLESPACE_NAME,BYTES, STATUS from dba_data_files a where a.status!='AVAILABLE';" >ora_sql.sql sqlplus $sqlstr as sysdba <ora_sql.sql>>/tmp/tmpora_dbfile_status_`date +%y%m%d`.txt } #脚本入口 create_html(){ rm -rf $file_output touch $file_output create_html_css >> $file_output ORACLE_SID=hb2_std1#设置oracle实例变量 sqlstr=sys/oracle#数据库sys用户及密码 create_html_head "һ hb2_std1" >> $file_output create_html_head "1 dbfile autoextensible of Database" >> $file_output create_table_head1 >> $file_output ora_dbfile_info#调用表空间情况函数 create_tr3 "/tmp/tmpora_dbfile_`date +%y%m%d`.txt" create_table_end >> $file_output create_html_head "2 dbfile usage of Database" >> $file_output create_table_head1 >> $file_output ora_dbfile_useage_info#调用表空间使用率函数 create_tr3 "/tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt" create_table_end >> $file_output create_html_head "3 asm usage of Database" >> $file_output create_table_head1 >> $file_output ora_asm_useage_info#调用asm存储信息函数 create_tr3 "/tmp/tmpora_asm_useage_`date +%y%m%d`.txt" create_table_end >> $file_output create_html_head "4 Status of archive_log" >> $file_output create_table_head1 >> $file_output ora_archive_info#调用归档使用率及归档设置大小函数 create_tr3 "/tmp/tmpora_archive_`date +%y%m%d`.txt" create_table_end >> $file_output create_html_head "5 Status of datafile" >> $file_output create_table_head1 >> $file_output ora_dbfile_status#调用表空间状态信息函数 create_tr3 "/tmp/tmpora_dbfile_status_`date +%y%m%d`.txt" create_table_end >> $file_output create_html_end >> $file_output sed -i 's/BORDER=1/width="68%" border="1" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse"/g' $file_output rm -rf /tmp/tmp*_`date +%y%m%d`.txt#清理临时文件 rm -rf ora_sql.sql#清理临时文件 } #此脚本只适用于Linux操作系统 PLATFORM=`uname` if [ ${PLATFORM} = "HP-UX" ] ; then echo "This script does not support HP-UX platform for the time being" exit 1 elif [ ${PLATFORM} = "SunOS" ] ; then echo "This script does not support SunOS platform for the time being" exit 1 elif [ ${PLATFORM} = "AIX" ] ; then echo "This script does not support AIX platform for the time being" exit 1 elif [ ${PLATFORM} = "Linux" ] ; then create_html fi
2，使用oracle用户编写执行计划crontab
[oracle@dg1:/home/oracle]$crontab -l
1 0 * * mon /home/oracle/oramonitor.sh#每周一执行
3，浏览器打开效果
һ hb2_std1
1 dbfile autoextensible of Database
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 26 12:43:47 2021 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> SQL> SQL> SQL> TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ GG_HUBEI_DDL +DATAC1/hb2_std/datafile/gg_hubei_ddl.981.1058846189 AVAILABLE 10 NO 0 GG_HUBEI_TB +DATAC1/hb2_std/datafile/gg_hubei_tb.1103.1059419581 AVAILABLE 6960 YES 1280 GG_HUBEI_TB +DATAC1/hb2_std/datafile/gg_hubei_tb.884.1058813691 AVAILABLE 30720 NO 0 GG_HUBEI_TB +DATAC1/hb2_std/datafile/gg_hubei_tb.947.1058845533 AVAILABLE 14300 YES 1280 GG_HUBEI_TB +DATAC1/hb2_std/datafile/gg_hubei_tb_01.dbf AVAILABLE 5080 YES 1280 GG_HUBEI_TB +DATAC1/hb2_std/datafile/gg_hubei_tb_02.dbf AVAILABLE 4790 YES 1280 IDX +DATAC1/hb2_std/datafile/idx.940.1058844507 AVAILABLE 12288 NO 0 IDX +DATAC1/hb2_std/datafile/idx_01.dbf AVAILABLE 270 YES 1280 IDX_SP_2013 +DATAC1/hb2_std/datafile/idx_sp_2013.962.1058846169 AVAILABLE 50 NO 0 IDX_SP_2014 +DATAC1/hb2_std/datafile/idx_sp_2014.958.1058846157 AVAILABLE 100 NO 0 IDX_SP_2015 +DATAC1/hb2_std/datafile/idx_sp_2015.960.1058846163 AVAILABLE 100 NO 0 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ IDX_SP_2016 +DATAC1/hb2_std/datafile/idx_sp_2016.954.1058846109 AVAILABLE 1024 NO 0 IDX_SP_2017 +DATAC1/hb2_std/datafile/idx_sp_2017.959.1058846159 AVAILABLE 100 NO 0 IDX_SP_2018 +DATAC1/hb2_std/datafile/idx_sp_2018.956.1058846151 AVAILABLE 100 NO 0 IDX_SP_2019 +DATAC1/hb2_std/datafile/idx_sp_2019.972.1058846179 AVAILABLE 10 NO 0 IDX_SP_2019 +DATAC1/hb2_std/datafile/idx_sp_2019.986.1058846195 AVAILABLE 10 YES 1280 IDX_SP_2019 +DATAC1/hb2_std/datafile/idx_sp_2019.987.1058846197 AVAILABLE 10 YES 1280 IDX_SP_2019 +DATAC1/hb2_std/datafile/idx_sp_2019.988.1058846197 AVAILABLE 10 YES 1280 IDX_SP_2019 +DATAC1/hb2_std/datafile/idx_sp_2019.989.1058846199 AVAILABLE 10 YES 1280 IDX_SP_2019 +DATAC1/hb2_std/datafile/idx_sp_2019.990.1058846199 AVAILABLE 10 YES 1280 IDX_SP_2020 +DATAC1/hb2_std/datafile/idx_sp_2020.984.1058846193 AVAILABLE 10 NO 0 IDX_SP_2020 +DATAC1/hb2_std/datafile/idx_sp_2020.991.1058846201 AVAILABLE 10 YES 1280 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ IDX_SP_2020 +DATAC1/hb2_std/datafile/idx_sp_2020.992.1058846201 AVAILABLE 10 YES 1280 IDX_SP_2020 +DATAC1/hb2_std/datafile/idx_sp_2020.993.1058846203 AVAILABLE 10 YES 1280 IDX_SP_2020 +DATAC1/hb2_std/datafile/idx_sp_2020.994.1058846205 AVAILABLE 10 YES 1280 IDX_SP_2020 +DATAC1/hb2_std/datafile/idx_sp_2020.995.1058846205 AVAILABLE 10 YES 1280 IDX_SP_2020 +DATAC1/hb2_std/datafile/idx_sp_2020.996.1058846207 AVAILABLE 10 YES 1280 IDX_SP_2020 +DATAC1/hb2_std/datafile/idx_sp_2020.997.1058846207 AVAILABLE 10 YES 1280 IDX_SP_2020 +DATAC1/hb2_std/datafile/idx_sp_2020.998.1058846209 AVAILABLE 10 YES 1280 IDX_SP_2020 +DATAC1/hb2_std/datafile/idx_sp_2020.999.1058846209 AVAILABLE 10 YES 1280 IDX_SP_MAX +DATAC1/hb2_std/datafile/idx_sp_max.957.1058846153 AVAILABLE 100 NO 0 NUMEN +DATAC1/hb2_std/datafile/numen.961.1058846165 AVAILABLE 100 NO 0 SP_2013 +DATAC1/hb2_std/datafile/sp_2013.1104.1059419581 AVAILABLE 430 YES 1280 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ SP_2013 +DATAC1/hb2_std/datafile/sp_2013.891.1058817957 AVAILABLE 30720 NO 0 SP_2013 +DATAC1/hb2_std/datafile/sp_2013.924.1058838545 AVAILABLE 20480 NO 0 SP_2013 +DATAC1/hb2_std/datafile/sp_2013.951.1058845905 AVAILABLE 4560 YES 1280 SP_2013 +DATAC1/hb2_std/datafile/sp_2013.952.1058845979 AVAILABLE 4350 YES 1280 SP_2013 +DATAC1/hb2_std/datafile/sp_2013_01.dbf AVAILABLE 1444 YES 1280 SP_2013 +DATAC1/hb2_std/datafile/sp_2013_02.dbf AVAILABLE 360 YES 1280 SP_2013 +DATAC1/hb2_std/datafile/sp_2013_03.dbf AVAILABLE 1024 YES 1280 SP_2014 +DATAC1/hb2_std/datafile/sp_2014.1105.1059419581 AVAILABLE 10 YES 1280 SP_2014 +DATAC1/hb2_std/datafile/sp_2014.889.1058816777 AVAILABLE 30720 NO 0 SP_2014 +DATAC1/hb2_std/datafile/sp_2014.890.1058817261 AVAILABLE 30720 NO 0 SP_2014 +DATAC1/hb2_std/datafile/sp_2014.950.1058845819 AVAILABLE 5260 YES 1280 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ SP_2014 +DATAC1/hb2_std/datafile/sp_2014_01.dbf AVAILABLE 1024 YES 1280 SP_2014 +DATAC1/hb2_std/datafile/sp_2014_02.dbf AVAILABLE 10 YES 1280 SP_2014 +DATAC1/hb2_std/datafile/sp_2014_03.dbf AVAILABLE 1024 YES 1280 SP_2015 +DATAC1/hb2_std/datafile/sp_2015.1106.1059419581 AVAILABLE 10 YES 1280 SP_2015 +DATAC1/hb2_std/datafile/sp_2015.887.1058815705 AVAILABLE 30720 NO 0 SP_2015 +DATAC1/hb2_std/datafile/sp_2015.888.1058816241 AVAILABLE 30720 NO 0 SP_2015 +DATAC1/hb2_std/datafile/sp_2015.948.1058845649 AVAILABLE 7674 YES 1280 SP_2015 +DATAC1/hb2_std/datafile/sp_2015_01.dbf AVAILABLE 1024 YES 1280 SP_2015 +DATAC1/hb2_std/datafile/sp_2015_02.dbf AVAILABLE 10 YES 1280 SP_2015 +DATAC1/hb2_std/datafile/sp_2015_03.dbf AVAILABLE 1024 YES 1280 SP_2016 +DATAC1/hb2_std/datafile/sp_2016.1107.1059419583 AVAILABLE 640 YES 1280 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ SP_2016 +DATAC1/hb2_std/datafile/sp_2016.885.1058814265 AVAILABLE 30720 NO 0 SP_2016 +DATAC1/hb2_std/datafile/sp_2016.886.1058814911 AVAILABLE 30720 NO 0 SP_2016 +DATAC1/hb2_std/datafile/sp_2016.949.1058845735 AVAILABLE 5924 YES 1280 SP_2016 +DATAC1/hb2_std/datafile/sp_2016_01.dbf AVAILABLE 1584 YES 1280 SP_2016 +DATAC1/hb2_std/datafile/sp_2016_02.dbf AVAILABLE 570 YES 1280 SP_2016 +DATAC1/hb2_std/datafile/sp_2016_03.dbf AVAILABLE 1024 YES 1280 SP_2017 +DATAC1/hb2_std/datafile/sp_2017.1108.1059419583 AVAILABLE 10 YES 1280 SP_2017 +DATAC1/hb2_std/datafile/sp_2017.918.1058835043 AVAILABLE 30720 NO 0 SP_2017 +DATAC1/hb2_std/datafile/sp_2017.925.1058838931 AVAILABLE 20480 NO 0 SP_2017 +DATAC1/hb2_std/datafile/sp_2017.926.1058839305 AVAILABLE 20480 NO 0 SP_2017 +DATAC1/hb2_std/datafile/sp_2017.953.1058846055 AVAILABLE 2950 YES 1280 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ SP_2017 +DATAC1/hb2_std/datafile/sp_2017_01.dbf AVAILABLE 1024 YES 1280 SP_2017 +DATAC1/hb2_std/datafile/sp_2017_02.dbf AVAILABLE 10 YES 1280 SP_2017 +DATAC1/hb2_std/datafile/sp_2017_03.dbf AVAILABLE 1024 YES 1280 SP_2018 +DATAC1/hb2_std/datafile/sp_2018.1109.1059419585 AVAILABLE 10 YES 1280 SP_2018 +DATAC1/hb2_std/datafile/sp_2018.913.1058832027 AVAILABLE 30720 NO 0 SP_2018 +DATAC1/hb2_std/datafile/sp_2018.914.1058832503 AVAILABLE 30720 NO 0 SP_2018 +DATAC1/hb2_std/datafile/sp_2018.915.1058833257 AVAILABLE 30720 NO 0 SP_2018 +DATAC1/hb2_std/datafile/sp_2018.916.1058833833 AVAILABLE 30720 NO 0 SP_2018 +DATAC1/hb2_std/datafile/sp_2018.917.1058834419 AVAILABLE 30720 NO 0 SP_2018 +DATAC1/hb2_std/datafile/sp_2018.955.1058846125 AVAILABLE 1024 YES 1280 SP_2018 +DATAC1/hb2_std/datafile/sp_2018_01.dbf AVAILABLE 1024 YES 1280 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ SP_2018 +DATAC1/hb2_std/datafile/sp_2018_02.dbf AVAILABLE 10 YES 1280 SP_2018 +DATAC1/hb2_std/datafile/sp_2018_03.dbf AVAILABLE 1024 YES 1280 SP_2019 +DATAC1/hb2_std/datafile/sp_2019.1110.1059419585 AVAILABLE 10 YES 1280 SP_2019 +DATAC1/hb2_std/datafile/sp_2019.906.1058828531 AVAILABLE 30720 NO 0 SP_2019 +DATAC1/hb2_std/datafile/sp_2019.907.1058829095 AVAILABLE 30720 NO 0 SP_2019 +DATAC1/hb2_std/datafile/sp_2019.908.1058829611 AVAILABLE 30720 NO 0 SP_2019 +DATAC1/hb2_std/datafile/sp_2019.909.1058830117 AVAILABLE 30720 NO 0 SP_2019 +DATAC1/hb2_std/datafile/sp_2019.910.1058830631 AVAILABLE 30720 NO 0 SP_2019 +DATAC1/hb2_std/datafile/sp_2019.911.1058831117 AVAILABLE 30720 NO 0 SP_2019 +DATAC1/hb2_std/datafile/sp_2019.912.1058831571 AVAILABLE 30720 NO 0 SP_2019 +DATAC1/hb2_std/datafile/sp_2019.941.1058844713 AVAILABLE 9880 YES 1280 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ SP_2019 +DATAC1/hb2_std/datafile/sp_2019.942.1058844879 AVAILABLE 9810 YES 1280 SP_2019 +DATAC1/hb2_std/datafile/sp_2019.943.1058845043 AVAILABLE 9740 YES 1280 SP_2019 +DATAC1/hb2_std/datafile/sp_2019.944.1058845209 AVAILABLE 6450 YES 1280 SP_2019 +DATAC1/hb2_std/datafile/sp_2019.945.1058845313 AVAILABLE 6450 YES 1280 SP_2019 +DATAC1/hb2_std/datafile/sp_2019_01.dbf AVAILABLE 1024 YES 1280 SP_2019 +DATAC1/hb2_std/datafile/sp_2019_02.dbf AVAILABLE 10 YES 1280 SP_2019 +DATAC1/hb2_std/datafile/sp_2019_03.dbf AVAILABLE 1024 YES 1280 SP_2020 +DATAC1/hb2_std/datafile/sp_2020.1111.1059419587 AVAILABLE 1060 YES 1280 SP_2020 +DATAC1/hb2_std/datafile/sp_2020.921.1058837079 AVAILABLE 30000 NO 0 SP_2020 +DATAC1/hb2_std/datafile/sp_2020.929.1058840291 AVAILABLE 19140 NO 0 SP_2020 +DATAC1/hb2_std/datafile/sp_2020.930.1058840635 AVAILABLE 19140 NO 0 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ SP_2020 +DATAC1/hb2_std/datafile/sp_2020.931.1058840941 AVAILABLE 20170 YES 1280 SP_2020 +DATAC1/hb2_std/datafile/sp_2020.932.1058841287 AVAILABLE 20170 YES 1280 SP_2020 +DATAC1/hb2_std/datafile/sp_2020.933.1058841701 AVAILABLE 20170 YES 1280 SP_2020 +DATAC1/hb2_std/datafile/sp_2020.934.1058842107 AVAILABLE 20170 YES 1280 SP_2020 +DATAC1/hb2_std/datafile/sp_2020.935.1058842521 AVAILABLE 20170 YES 1280 SP_2020 +DATAC1/hb2_std/datafile/sp_2020.936.1058842957 AVAILABLE 20170 YES 1280 SP_2020 +DATAC1/hb2_std/datafile/sp_2020.937.1058843433 AVAILABLE 20100 YES 1280 SP_2020 +DATAC1/hb2_std/datafile/sp_2020.938.1058843777 AVAILABLE 19960 YES 1280 SP_2020 +DATAC1/hb2_std/datafile/sp_2020.939.1058844153 AVAILABLE 19820 YES 1280 SP_2020 +DATAC1/hb2_std/datafile/sp_2020_01.dbf AVAILABLE 1024 YES 1280 SP_2020 +DATAC1/hb2_std/datafile/sp_2020_02.dbf AVAILABLE 10 YES 1280 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ SP_2020 +DATAC1/hb2_std/datafile/sp_2020_03.dbf AVAILABLE 1024 YES 1280 SP_MAX +DATAC1/hb2_std/datafile/sp_max.920.1058836435 AVAILABLE 30000 NO 0 SP_MAX +DATAC1/hb2_std/datafile/sp_max.927.1058839651 AVAILABLE 19230 NO 0 SP_MAX +DATAC1/hb2_std/datafile/sp_max.928.1058839975 AVAILABLE 19230 NO 0 SP_MAX +DATAC1/hb2_std/datafile/sp_max_01.dbf AVAILABLE 26014 YES 1280 SP_MAX +DATAC1/hb2_std/datafile/sp_max_02.dbf AVAILABLE 16880 YES 1280 SP_MAX +DATAC1/hb2_std/datafile/sp_max_03.dbf AVAILABLE 12330 YES 1280 SP_MAX +DATAC1/hb2_std/datafile/sp_max_04.dbf AVAILABLE 10090 YES 1280 SP_MAX +DATAC1/hb2_std/datafile/sp_max_05.dbf AVAILABLE 8200 YES 1280 SP_MAX +DATAC1/hb2_std/datafile/sp_max_06.dbf AVAILABLE 8200 YES 1280 SP_MAX +DATAC1/hb2_std/datafile/sp_max_07.dbf AVAILABLE 2844 YES 1280 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ SYSAUX +DATAC1/hb2_std/datafile/sysaux.883.1058813115 AVAILABLE 32736 YES 1280 SYSTEM +DATAC1/hb2_std/datafile/system.1120.1059469189 AVAILABLE 1294 YES 1280 SYSTEM +DATAC1/hb2_std/datafile/system.946.1058845419 AVAILABLE 6866 YES 1280 SYSTEM +DATAC1/hb2_std/datafile/system_01.dbf AVAILABLE 1024 YES 1280 TYYW +DATAC1/hb2_std/datafile/tyyw.1100.1059419481 AVAILABLE 30004 YES 1280 TYYW +DATAC1/hb2_std/datafile/tyyw.1101.1059419575 AVAILABLE 8130 YES 1280 TYYW +DATAC1/hb2_std/datafile/tyyw.1102.1059419575 AVAILABLE 8130 YES 1280 TYYW +DATAC1/hb2_std/datafile/tyyw.892.1058818741 AVAILABLE 30720 NO 0 TYYW +DATAC1/hb2_std/datafile/tyyw.893.1058819437 AVAILABLE 30720 NO 0 TYYW +DATAC1/hb2_std/datafile/tyyw.894.1058820293 AVAILABLE 30720 NO 0 TYYW +DATAC1/hb2_std/datafile/tyyw.895.1058821187 AVAILABLE 30720 NO 0 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ TYYW +DATAC1/hb2_std/datafile/tyyw.896.1058821953 AVAILABLE 30720 NO 0 TYYW +DATAC1/hb2_std/datafile/tyyw.897.1058822809 AVAILABLE 30720 NO 0 TYYW +DATAC1/hb2_std/datafile/tyyw.898.1058823683 AVAILABLE 30720 NO 0 TYYW +DATAC1/hb2_std/datafile/tyyw.899.1058824549 AVAILABLE 30720 NO 0 TYYW +DATAC1/hb2_std/datafile/tyyw.900.1058825475 AVAILABLE 30720 NO 0 TYYW +DATAC1/hb2_std/datafile/tyyw.902.1058826169 AVAILABLE 30720 NO 0 TYYW +DATAC1/hb2_std/datafile/tyyw.903.1058826745 AVAILABLE 30720 NO 0 TYYW +DATAC1/hb2_std/datafile/tyyw.904.1058827421 AVAILABLE 30720 NO 0 TYYW +DATAC1/hb2_std/datafile/tyyw.905.1058827985 AVAILABLE 30720 NO 0 TYYW +DATAC1/hb2_std/datafile/tyyw.919.1058835689 AVAILABLE 30720 YES 1280 TYYW +DATAC1/hb2_std/datafile/tyyw_01.dbf AVAILABLE 9144 YES 1280 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ TYYW +DATAC1/hb2_std/datafile/tyyw_02.dbf AVAILABLE 8060 YES 1280 TYYW +DATAC1/hb2_std/datafile/tyyw_03.dbf AVAILABLE 4804 YES 1280 U370000 +DATAC1/hb2_std/datafile/u370000.967.1058846175 AVAILABLE 10 NO 0 U370100 +DATAC1/hb2_std/datafile/u370100.973.1058846181 AVAILABLE 10 NO 0 U370200 +DATAC1/hb2_std/datafile/u370200.980.1058846189 AVAILABLE 10 NO 0 U370300 +DATAC1/hb2_std/datafile/u370300.966.1058846173 AVAILABLE 10 NO 0 U370400 +DATAC1/hb2_std/datafile/u370400.983.1058846191 AVAILABLE 10 NO 0 U370500 +DATAC1/hb2_std/datafile/u370500.979.1058846187 AVAILABLE 10 NO 0 U370600 +DATAC1/hb2_std/datafile/u370600.982.1058846191 AVAILABLE 10 NO 0 U370700 +DATAC1/hb2_std/datafile/u370700.976.1058846185 AVAILABLE 10 NO 0 U370800 +DATAC1/hb2_std/datafile/u370800.975.1058846183 AVAILABLE 10 NO 0 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ U370900 +DATAC1/hb2_std/datafile/u370900.965.1058846173 AVAILABLE 10 NO 0 U371000 +DATAC1/hb2_std/datafile/u371000.978.1058846187 AVAILABLE 10 NO 0 U371100 +DATAC1/hb2_std/datafile/u371100.963.1058846169 AVAILABLE 10 NO 0 U371200 +DATAC1/hb2_std/datafile/u371200.985.1058846195 AVAILABLE 10 NO 0 U371300 +DATAC1/hb2_std/datafile/u371300.977.1058846185 AVAILABLE 10 NO 0 U371400 +DATAC1/hb2_std/datafile/u371400.974.1058846183 AVAILABLE 10 NO 0 U371500 +DATAC1/hb2_std/datafile/u371500.971.1058846179 AVAILABLE 10 NO 0 U371600 +DATAC1/hb2_std/datafile/u371600.970.1058846177 AVAILABLE 10 NO 0 U371700 +DATAC1/hb2_std/datafile/u371700.969.1058846177 AVAILABLE 10 NO 0 U379200 +DATAC1/hb2_std/datafile/u379200.964.1058846171 AVAILABLE 10 NO 0 UNDOTBS1 +DATAC1/hb2_std/datafile/undotbs1.922.1058837675 AVAILABLE 24185 YES 640 TABLESPACE_NAME FILE_NAME STATUS M AUT INCREMENT_BY --------------- ------------------------------------------------------------ --------- ---------- --- ------------ UNDOTBS2 +DATAC1/hb2_std/datafile/undotbs2.923.1058838119 AVAILABLE 24225 YES 3200 USERS +DATAC1/hb2_std/datafile/users.1000.1058846211 AVAILABLE 5 YES 160 VPD +DATAC1/hb2_std/datafile/vpd.968.1058846175 AVAILABLE 10 NO 0 168 rows selected. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options |
2 dbfile usage of Database
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 26 12:43:51 2021 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> SQL> SQL> TABLESPACE_NAM TOT_GROOTTE_MB SY PERS TOTAL_BYTES MAX_BYTES -------------- -------------- ---------- -------- ----------- ---------- GG_HUBEI_DDL .01 0 0.00% .01 .01 GG_HUBEI_TB 60.4 55.23 91.44% 5.17 1.37 IDX 12.26 11.67 95.19% .59 .37 IDX_SP_2013 .05 0 0.00% .05 .05 IDX_SP_2014 .1 0 0.00% .1 .1 IDX_SP_2015 .1 0 0.00% .1 .1 IDX_SP_2016 1 .02 2.00% .98 .98 IDX_SP_2017 .1 .04 40.00% .06 .06 IDX_SP_2018 .1 .02 20.00% .08 .08 IDX_SP_2019 .06 .01 16.67% .05 .01 IDX_SP_2020 .1 .01 10.00% .09 .01 TABLESPACE_NAM TOT_GROOTTE_MB SY PERS TOTAL_BYTES MAX_BYTES -------------- -------------- ---------- -------- ----------- ---------- IDX_SP_MAX .1 .01 10.00% .09 .09 NUMEN .1 0 0.00% .1 .1 SP_2013 61.88 59.06 95.44% 2.82 .9 SP_2014 67.16 65.23 97.13% 1.93 1.25 SP_2015 69.51 66.78 96.07% 2.73 1.98 SP_2016 69.51 66.29 95.37% 3.22 1.4 SP_2017 74.9 72.52 96.82% 2.38 1.45 SP_2018 153.02 148.76 97.22% 4.26 2.87 SP_2019 253.36 243.77 96.21% 9.59 1.9 SP_2020 246.38 237.49 96.39% 8.89 .92 SP_MAX 149.43 147.03 98.39% 2.4 1.13 TABLESPACE_NAM TOT_GROOTTE_MB SY PERS TOTAL_BYTES MAX_BYTES -------------- -------------- ---------- -------- ----------- ---------- SYSAUX 31.97 6.84 21.40% 25.13 1.73 SYSTEM 8.97 8.67 96.66% .3 .26 TYYW 486.67 471.3 96.84% 15.37 2.39 U370000 .01 0 0.00% .01 .01 U370100 .01 0 0.00% .01 .01 U370200 .01 0 0.00% .01 .01 U370300 .01 0 0.00% .01 .01 U370400 .01 0 0.00% .01 .01 U370500 .01 0 0.00% .01 .01 U370600 .01 0 0.00% .01 .01 U370700 .01 0 0.00% .01 .01 TABLESPACE_NAM TOT_GROOTTE_MB SY PERS TOTAL_BYTES MAX_BYTES -------------- -------------- ---------- -------- ----------- ---------- U370800 .01 0 0.00% .01 .01 U370900 .01 0 0.00% .01 .01 U371000 .01 0 0.00% .01 .01 U371100 .01 0 0.00% .01 .01 U371200 .01 0 0.00% .01 .01 U371300 .01 0 0.00% .01 .01 U371400 .01 0 0.00% .01 .01 U371500 .01 0 0.00% .01 .01 U371600 .01 0 0.00% .01 .01 U371700 .01 0 0.00% .01 .01 U379200 .01 0 0.00% .01 .01 TABLESPACE_NAM TOT_GROOTTE_MB SY PERS TOTAL_BYTES MAX_BYTES -------------- -------------- ---------- -------- ----------- ---------- UNDOTBS1 23.62 5.14 21.76% 18.48 .33 UNDOTBS2 23.66 3.89 16.44% 19.77 .34 VPD .01 0 0.00% .01 .01 47 rows selected. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options |
3 asm usage of Database
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 26 12:43:56 2021 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> SQL> NAME TOTAL_MB FREE_MB COLD_USED_MB ------------------------------ ---------- ---------- ------------ DATAC1 269033472 259979256 9054216 RECOC1 67283712 65483768 1799944 SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options |
4 Status of archive_log
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 26 12:44:00 2021 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> SQL> SQL> FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES ------------- ------------------ ------------------------- --------------- ARCHIVED LOG 34.95 7.03 913 SQL> SQL> SQL> NAME SPACE_LIMIT/1024/1024 SPACE_USED/1024/1024 SPACE_RECLAIMABLE NUMBER_OF_FILES ---------------------------------------- --------------------- -------------------- ----------------- --------------- +RECOC1 2048000 730173 1.5106E+11 927 SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options |
5 Status of datafile
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 26 12:44:04 2021 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> SQL> SQL> SQL> no rows selected SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
|
