# $Header: fullTbsp.pl 13-jul-2005.08:49:55 rreilly Exp $ # # Copyright (c) 2001, 2005, Oracle. All rights reserved. # # NAME # fullTbsp.pl - # # DESCRIPTION # Event file that checks the tablespaces full problems # # OUTPUT: # The percentage of space used in the tablespaces # # NOTES # # # MODIFIED (MM/DD/YY) # rreilly 07/13/05 - bug 4145603 undo tbsp support # rreilly 11/22/04 - bug 3966407 select works differently, fix err catch # rreilly 11/17/04 - bug 3966407 do not do truncate for gtt # rreilly 07/29/04 - make sure close cursors before exit # rreilly 07/28/04 - enh 3062024 add free space support # rreilly 07/22/04 - bug 3777094 exit for 10i if there are no # dictionary managed tablespaces # kmckeen 08/15/03 - Add tbspc space-used metric for 10i db dictionary # mngd tbspc # lhan 08/11/03 - fix bug 3067929 - read only mode # pbantis 07/11/03 - Convert from Oraperl to Perl DBI # lhan 06/30/03 - Change gtt DDL # lhan 06/19/03 - fix bug 3012332, remove flg # lhan 06/02/03 - performance improvement (bug 2954259) # aaitghez 05/27/03 - review comments # aaitghez 05/19/03 - remove credentials from environment # lhan 05/14/03 - remove db_version check # lhan 05/14/03 - remove db_version check # lhan 04/14/03 - New way to check temp tablespaces # lhan 04/14/03 - Remove seg ext related stuff # lhan 04/08/03 - lhan_tbsp_030408 # lhan 04/08/03 - into Main branch # lhan 04/03/03 - lhan_tbsp_030328 # lhan 03/31/03 - split problem tablespaces metric into two # lhan 03/31/01 - Creation # use strict; use DBI; require "emd_common.pl"; require "semd_common.pl"; # For TESTING: comment out stdin args my %stdinArgs = get_stdinvars(); my $username = $stdinArgs{"EM_TARGET_USERNAME"}; my $password = $stdinArgs{"EM_TARGET_PASSWORD"}; my $address = $ENV{EM_TARGET_ADDRESS}; my $role = $ENV{EM_TARGET_ROLE}; my $mode = 0; my $meta_db_version; my $db_version; my $db_is9iOrHigher; if($role =~ /SYSDBA/i) { $mode = 2; } elsif($role =~ /SYSOPER/i) { $mode = 4; } # TESTING: Un-comment for manual testing #my $username = "system"; #my $password = "manager"; #my $address = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))(CONNECT_DATA=(SID=)))"; # -------------------------------------------------------------------- # +++ Establish Target DB Connection # -------------------------------------------------------------------- my $lda = DBI->connect('dbi:Oracle:', "$username@".$address, "$password", {ora_session_mode => $mode, PrintError => 0, RaiseError => 0, AutoCommit => 0}) or die "em_error=Could not connect to $username/$address: $DBI::errstr\n"; register_metric_call($lda); # -------------------------------------------------------------------- # +++ Get DB Version # -------------------------------------------------------------------- # Major version number $meta_db_version = $ENV{VersionCategory}; if (!defined($meta_db_version)) { $meta_db_version = $ENV{VERSIONCATEGORY}; } $db_version = $meta_db_version; if (index($db_version, '8') == 0) { $db_version = 8; } elsif (index($db_version, '9') == 0) { $db_version = 9; } else { $db_version = substr($db_version, 0, 2); } # True if the db is 9i or higher. $db_is9iOrHigher = 0; if ($db_version > 8) { $db_is9iOrHigher = 1; } # check if the tablespace and segment sql condition clause is set # if so, append the string value to the proper sql statment # NOTE: for the 10g+ metric, the extent mgmt clause is passed in # my $hasTbspCondition = 0; my $hasSegCondition = 0; my $hasMgmtCondition = 0; my $target_tbsp_condition_clause = $ENV{EM_TARGET_TABLESPACE_CONDITION_CLAUSE}; my $target_seg_condition_clause = $ENV{EM_TARGET_SEGMENT_CONDITION_CLAUSE}; my $target_mgmt_condition_clause = $ENV{EM_TARGET_EXTENT_MGMT_CONDITION_CLAUSE}; # TESTING: Sets the 10g metric clauses, uncomment for testing 10g # $target_mgmt_condition_clause="\=\'DICTIONARY\'"; if ($target_tbsp_condition_clause ne "" ) { $hasTbspCondition = 1; } if ($target_seg_condition_clause ne "" ) { $hasSegCondition = 1; } if ($target_mgmt_condition_clause ne "" ) { $hasMgmtCondition = 1; } #------------------------------------------------------------------------------- # Error message for insufficient dbsnmp privilege in v817 database # Note for TESTING - comment out stdinArgs #------------------------------------------------------------------------------- my $error_detail = "Make sure that user ". $stdinArgs{"EM_TARGET_USERNAME"} ." has been granted " . "select on sys.ts\$, sys.user\$, sys.seg\$, " . "sys.obj\$ and sys.sys_objects\n" ; #------------------------------------------------------------------------------- # Fix bug 3067929 - exit if database is read only #------------------------------------------------------------------------------- my $ro_sql = "select open_mode from v\$database "; my $ro_cur = $lda->prepare($ro_sql) or die "em_error=prepare($ro_sql): $DBI::errstr\n"; $ro_cur->execute() or die "em_error=ro_cur->execute(): $DBI::errstr\n"; my @fetch_row; @fetch_row = $ro_cur->fetchrow_array(); my $open_mode = $fetch_row[0]; if ( $open_mode eq "READ ONLY" ) { EMD_PERL_DEBUG("open_mode: $open_mode\n"); $lda->commit(); $lda->disconnect or warn "disconnect $DBI::errstr\n"; exit 0; } #------------------------------------------------------------------------------- # Retrieve a list of all online tablespaces keeping track of their name, # contents (permanent or temporary) and extent management (locally managed or # dictionary managed) # # NOTE: For 10g+ the extent management clause specifies only DICTIONARY #------------------------------------------------------------------------------- # the tablespace sql condition clause is set, append it to the sql statement my $sql = "select tablespace_name, contents "; $sql .= ", extent_management "; $sql .= "from sys.dba_tablespaces where status = 'ONLINE'"; if ( $hasTbspCondition == 1 ) { $sql .= " and tablespace_name $target_tbsp_condition_clause"; } if ( $hasMgmtCondition == 1 ) { $sql .= " and extent_management $target_mgmt_condition_clause"; } my $cur = $lda->prepare($sql) or die "em_error=prepare($sql): $DBI::errstr\n"; $cur->execute() or die "em_error=cur->execute(): $DBI::errstr\n"; my $tbsp_name; my $tbsp_contents; my $tbsp_extent_mgmt; # ------------------------------------------------------------------------------- # Retrieve all Tablespaces that meet the search criteria # ------------------------------------------------------------------------------- my @all_tbsp_names; my @all_tbsp_contents; my @all_tbsp_extent_mgmts; my $all_tbsp_count = 0; #my @fetch_row; while ( @fetch_row = $cur->fetchrow_array() ) { # count the qualifying tablespaces $all_tbsp_count++; # Retrieve Tablespace name and contents (PERMANENT, TEMPORARY, UNDO) $tbsp_name = $fetch_row[0]; push (@all_tbsp_names, $tbsp_name); push (@all_tbsp_contents, $fetch_row[1]); # tablespaces prior to 8i were all dictionary managed # databases prior to 8i were no longer supported # $tbsp_extent_mgmt = $fetch_row[2]; push (@all_tbsp_extent_mgmts, $tbsp_extent_mgmt); } warn "Data fetching terminated early by error: $DBI::errstr\n" if $DBI::err; # If no tablespaces meet the criteria, exit EMD_PERL_DEBUG("Qualifying Tablespace Count: $all_tbsp_count\n"); if ($all_tbsp_count == 0) { EMD_PERL_DEBUG("No Tablespaces To Test, exiting\n"); $lda->commit(); $lda->disconnect or warn "disconnect $DBI::errstr\n"; exit 0; } #------------------------------------------------------------------------------- # For each tablespace determine its maximum size and free space in order to # determine pctused. Also for permanent, dictionary managed tablespaces # determine if the tablespace contains any segments that are unable to extend # or are approaching their maxextents. #------------------------------------------------------------------------------- # query for determining maximum size and free space for # TEMPORARY, DICTIONARY (8i) tablespaces # my $sql_max_size_temp_dic = "select bytes/1048576, maxbytes/1048576, file_name " . "from sys.dba_data_files where tablespace_name = :1"; # new_query (8/9i) to determe the maximum size and free space for # PERMANENT (LOCAL and DICTIONARY) tablespaces. # only PERMANENT, DICTIONARY tablespace is subject to # chunk_sml_segs and max_ext_segs checking # add: ts#, blocksize, flag # add: performance improvement to fix bug 2954259 # This gtt is used for performance when accessing dba_datafiles # # DDL to create gtt: # my $ddl_gtt1 = "create global temporary table mgmt_db_file_gtt ( " . " tablespace_name varchar2(30), " . " meg number, " . " max_meg number, " . " file_name varchar2(513), " . " file_id number, " . " ts# number, " . " blocksize number, " . " flag number, " . "constraint mgmt_db_file_gtt_pk primary key (tablespace_name,file_id) " . " ) " . " on commit delete rows "; my $sql_del_gtt1 = "select 1 from mgmt_db_file_gtt where rownum < 2"; my $cur_ddl1_open = 0; my $cur_ddl1; # Prepare the SQL to see if the gtt exists my $cur_del1 = $lda->prepare($sql_del_gtt1); # If the gtt does not exist, create it #00942, 00000, "table or view does not exist" if ($DBI::errstr =~ /ORA-00942/) { $cur_ddl1_open = 1; $cur_ddl1 = $lda->prepare($ddl_gtt1) or die "em_error=prepare($ddl_gtt1): $DBI::errstr\n"; $cur_ddl1->execute() or die "em_error=cur_ddl1->execute(): $DBI::errstr\n"; } # This gtt is used for performance when accessing dba_free_space # my $ddl_gtt2 = "create global temporary table mgmt_db_size_gtt ( " . " tablespace_name varchar2(30), " . " sz number, " . " constraint mgmt_db_size_gtt_pk primary key (tablespace_name) " . " ) " . " on commit delete rows "; my $sql_del_gtt2 = "select 1 from mgmt_db_size_gtt where rownum < 2"; my $cur_ddl2_open = 0; my $cur_ddl2; # Perpare SQL to see if the gtt exists my $cur_del2 = $lda->prepare($sql_del_gtt2); # If the gtt does not exist, create it #00942, 00000, "table or view does not exist" if ($DBI::errstr =~ /ORA-00942/) { $cur_ddl2_open = 1; $cur_ddl2 = $lda->prepare($ddl_gtt2) or die "em_error=prepare($ddl_gtt2): $DBI::errstr\n"; $cur_ddl2->execute() or die "em_error=cur_ddl2->execute(): $DBI::errstr\n"; } # gtt insert statements # flag is not used in Tablespace Full metric, but used in Problem Segments # # Insert data into table mgmt_db_file_gtt. my $sql_gtt = "insert into mgmt_db_file_gtt " . " select f.tablespace_name, (f.bytes)/1048576 meg, " . " (f.maxbytes)/1048576 maxmeg, f.file_name, f.file_id, " . " ts.ts#, ts.blocksize, " . " 0 " . " from sys.ts\$ ts, sys.dba_data_files f " . " where ts.contents\$ = 0 " . " and f.tablespace_name = ts.name "; my $cur_tmp = $lda->prepare($sql_gtt) or die "em_error=prepare($sql_gtt): $DBI::errstr\n"; $cur_tmp->execute() or die "em_error=cur_tmp->execute(): $DBI::errstr\n"; my $sql_max_size = "select meg,max_meg,file_name,ts#,blocksize, flag " . " from mgmt_db_file_gtt " . " where tablespace_name =:1 "; # Insert Free Space data into table mgmt_db_size_gtt. # Query is Versioned, 9i+ versions of the db need additional information # for undo tablespaces. my $sql_gtt; if ($db_is9iOrHigher == 1) { $sql_gtt = "insert into mgmt_db_size_gtt " . "SELECT free.tablespace_name, SUM(free.sz) as sz " . "FROM " . " (SELECT tablespace_name,NVL(sum(bytes)/1048576, 0) sz " . " FROM sys.dba_free_space GROUP BY tablespace_name " . " UNION ALL " . " SELECT tablespace_name, NVL(SUM(bytes)/1048576, 0) sz " . " FROM dba_undo_extents WHERE status='EXPIRED' GROUP BY tablespace_name) free " . "GROUP BY free.tablespace_name"; } else { $sql_gtt = "insert into mgmt_db_size_gtt " . " select tablespace_name,NVL(sum(bytes)/1048576, 0) sz " . " from sys.dba_free_space group by tablespace_name "; } my $cur_tmp = $lda->prepare($sql_gtt) or die "em_error=prepare($sql_gtt): $DBI::errstr\n"; $cur_tmp->execute() or die "em_error=cur_tmp->execute(): $DBI::errstr\n"; my $sql_free_space = "select sz from mgmt_db_size_gtt " . " where tablespace_name = :1"; # query for determining maximum size and free space for locally managed # temporary tablespaces # my $sql_max_size_temp = "select bytes/1048576, maxbytes/1048576, file_name " . "from sys.dba_temp_files where tablespace_name = :1"; #my $sql_free_space_temp = "select NVL(sum(bytes_used)/1048576, 0) " # . "from v\$temp_extent_pool where tablespace_name = :1"; # new query to determine TEMPORARY tablespace usage # my $sql_tot_used_temp = "select sum(ss.used_blocks*ts.blocksize)/1048576 " ."from gv\$sort_segment ss, sys.ts\$ ts " ."where ss.tablespace_name = :1 and ss.tablespace_name = ts.name "; # declare all cursors and open cursors we know for sure we'll need # my $cur_max_size = $lda->prepare($sql_max_size) or die "em_error=prepare($sql_max_size): $DBI::errstr\n"; my $cur_free_space = $lda->prepare($sql_free_space) or die "em_error=prepare($sql_free_space): $DBI::errstr\n"; my $cur_temp_tot_open = 0; my $cur_temp_open = 0; my $cur_max_size_temp; my $cur_tot_used_temp; # cursors for temporary dictionary tbsps # my $cur_temp_dic_open = 0; my $cur_max_size_temp_dic; #------------------------------------------------------------------------------- # Loop through tablespaces #------------------------------------------------------------------------------- OUTER_LOOP: for ( my $i = 0; $i <= $#all_tbsp_names; $i++ ) { $tbsp_name = $all_tbsp_names[$i]; EMD_PERL_DEBUG("Processing Tablespace: $tbsp_name\n"); my $tbsp_contents = $all_tbsp_contents[$i]; my $tbsp_extent_mgmt = $all_tbsp_extent_mgmts[$i]; my $tbsp_status = "ONLINE, READ WRITE"; my $isLocal = 0; if ( $tbsp_extent_mgmt eq "LOCAL" ) { $isLocal = 1; } my $isTemporary = 0; if ( $tbsp_contents eq "TEMPORARY" ) { $isTemporary = 1; if ($cur_temp_tot_open == 0) { $cur_tot_used_temp = $lda->prepare($sql_tot_used_temp) or die "em_error=prepare($sql_tot_used_temp): $DBI::errstr\n"; $cur_temp_tot_open = 1; } } my $isLocalTempTbsp = 0; if ( $isLocal == 1 && $isTemporary == 1 ) { $isLocalTempTbsp = 1; if ($cur_temp_open == 0) { $cur_max_size_temp = $lda->prepare($sql_max_size_temp) or die "em_error=prepare($sql_max_size_temp): $DBI::errstr\n"; $cur_temp_open = 1; } } if ( $isLocalTempTbsp == 1 ) { $cur = $cur_max_size_temp; } elsif ( $isLocal != 1 && $isTemporary == 1) { # temporary and dictionary # if ($cur_temp_dic_open == 0) { $cur_max_size_temp_dic = $lda->prepare($sql_max_size_temp_dic) or die "em_error=prepare($sql_max_size_temp_dic): $DBI::errstr\n"; $cur_temp_dic_open = 1; } $cur = $cur_max_size_temp_dic; } else { # new_query: all permanent tbsp # $cur = $cur_max_size; } $cur->bind_param(1, $tbsp_name) or warn "cur->bind_param(1, $tbsp_name): $DBI::errstr\n"; $cur->execute() or warn "cur->execute(): $DBI::errstr\n"; my $tbsp_tot_used_mbytes = 0.0; my $tbsp_cur_free_mbytes = 0.0; my $tbsp_tot_free_mbytes = 0.0; my $tbsp_cur_mbytes = 0; my $tbsp_max_mbytes = 0; my $tbsp_used_pct = 0; my $tbsp_free_mbytes = 0; # new_query: ts#, blocksize, flag # my $tbsp_blocksize = -1; # bind 3 from result sql_max_size my $ts_numb = -1; # bind 5 from result sql_max_size my $flag_value = ""; # EXT_size if not locally system # loop through the tablespace's datafiles determining the tablespace's maximum # size as well as the additional free space that would be available in the # tablespace should a datafile autoextend # while ( @fetch_row = $cur->fetchrow_array() ) { my $df_cur_mbytes = $fetch_row[0]; # current size of datafile my $df_max_mbytes = $fetch_row[1]; # maximum size of datafile my $df_file_name = $fetch_row[2]; # file name # new_query: fetch ts#, blocksize, flag # permanent only # if ($isLocalTempTbsp != 1 && $isTemporary != 1) { $ts_numb = $fetch_row[3]; $tbsp_blocksize = $fetch_row[4]; $flag_value = $fetch_row[5]; } my $df_auto_free_mbytes = 0; # amount of additional available free space my $df_auto_free_kbytes = 0; # should the datafile autoextend if ( $df_cur_mbytes > $df_max_mbytes ) { # if current size is larger than maximum size change maximum size to # be current size. This can happen for one of two reasons: # 1. autoextension is not enabled # 2. if someone resizes an existing datafile to be smaller # than what is currently allocated. # $df_max_mbytes = $df_cur_mbytes; $df_auto_free_kbytes = 0; $df_auto_free_mbytes = 0; } else { # if maximum size is larger than current size then autoextension # is enabled, so determine how much free space is available on # the disk when determining how large the datafile could become # my $avail_kbytes = 0; my $avail_mbytes = 0; my $dir = dirname ($df_file_name); my @diskusage = get_disk_usage ($dir); if ( $diskusage[0] == -1 ) { print "em_error=Couldn't get disk usage for disk $dir\n"; next OUTER_LOOP; } else { $avail_kbytes = $diskusage[2]; $avail_mbytes = ($avail_kbytes / 1024); } if ( $df_max_mbytes > $avail_mbytes + $df_cur_mbytes) { # the maximum size is larger than what is available on the disk # so reduce the maximum size to be the amount of free space # on the disk plus the amount that is currently allocated to # the datafile # # also keep track of the amount of additional free space # that would be available should the datafile autoextend to # this maximum size # $df_max_mbytes = $avail_mbytes + $df_cur_mbytes; $df_auto_free_mbytes = $avail_mbytes; $df_auto_free_kbytes = $avail_kbytes; } else { # there's enough free space on the disk to satisfy the # datafile's maximum size so there's nothing to do other then # keeping track of the amount of additional free space # that would be available should the datafile autoextend to # this maximum size # $df_auto_free_mbytes = $df_max_mbytes - $df_cur_mbytes; $df_auto_free_kbytes = ($df_auto_free_mbytes * 1024); } } warn "$DBI::errstr\n" if $DBI::err; # keep track of tablespace's current size, maximum size and # amount of additional free space that would be added if the # datafile autoextended # $tbsp_cur_mbytes += $df_cur_mbytes; $tbsp_max_mbytes += $df_max_mbytes; $tbsp_tot_free_mbytes += $df_auto_free_mbytes; EMD_PERL_DEBUG(" Processing Datafile: $df_file_name Current Size: $df_cur_mbytes Max Size: $df_max_mbytes Free Space Available for Auto Extend: $df_auto_free_mbytes\n"); } # for the tablespace determine the total amount of free space currently # existing in the tablespace as well as the largest free chunk # my $free_space_cur; if ( $isTemporary == 1 ) { $free_space_cur = $cur_tot_used_temp; } else { $free_space_cur = $cur_free_space; } $free_space_cur->bind_param(1, $tbsp_name) or warn "free_space_cur->bind_param(1, $tbsp_name): $DBI::errstr\n"; $free_space_cur->execute() or warn "free_space_cur->execute(): $DBI::errstr\n"; @fetch_row = $free_space_cur->fetchrow_array(); if ( $isTemporary == 1 ) { $tbsp_tot_used_mbytes = $fetch_row[0]; } else { $tbsp_cur_free_mbytes = $fetch_row[0]; $tbsp_tot_free_mbytes = $tbsp_tot_free_mbytes + $tbsp_cur_free_mbytes; $tbsp_tot_used_mbytes = $tbsp_max_mbytes - $tbsp_tot_free_mbytes; } $tbsp_cur_mbytes = sprintf("%3.2f", $tbsp_cur_mbytes); $tbsp_max_mbytes = sprintf("%3.2f", $tbsp_max_mbytes); $tbsp_tot_used_mbytes = sprintf("%3.2f", $tbsp_tot_used_mbytes); # EMD_PERL_DEBUG(" tbsp_cur_mbytes: $tbsp_cur_mbytes"); # EMD_PERL_DEBUG(" tbsp_max_mbytes: $tbsp_max_mbytes"); # EMD_PERL_DEBUG(" tbsp_tot_used_mbytes: $tbsp_tot_used_mbytes"); # determine the percent used for the tablespace # if ( $tbsp_max_mbytes == 0 ) { if ($tbsp_tot_used_mbytes == 0) { $tbsp_used_pct = sprintf ("%3.2f", 0); $tbsp_free_mbytes = sprintf ("%3.2f", 0); } else { EMD_PERL_ERROR ("Couldn't get maximum size for tablspace $tbsp_name."); } } else { $tbsp_used_pct = sprintf ("%3.2f", $tbsp_tot_used_mbytes / $tbsp_max_mbytes * 100); $tbsp_free_mbytes = sprintf ("%3.2f", $tbsp_max_mbytes - $tbsp_tot_used_mbytes); } EMD_PERL_DEBUG(" Finished Tablespace: $tbsp_name Total Used(MB): $tbsp_tot_used_mbytes Max Size(MB): $tbsp_max_mbytes Space Used(%): $tbsp_used_pct Free Space(MB): $tbsp_free_mbytes\n"); # end of calculation for tbsp_used_pct # # TESTING - un-comment for testing #print "\nTablespace: $tbsp_name\n"; #print " Size(MB): $tbsp_max_mbytes Used(%): $tbsp_used_pct\n"; #print " Used(MB): $tbsp_tot_used_mbytes Free(MB): $tbsp_free_mbytes\n"; # Return Values - this is where data is returned to the metrics print "em_result=$tbsp_name|$tbsp_used_pct|$tbsp_free_mbytes\n"; next OUTER_LOOP; } # Force a commit to ensure that all rows get removed. $lda->commit(); $lda->disconnect or warn "disconnect $DBI::errstr\n"; exit 0;