Toad World® Forums

Health Check version 12 vs 13

Hi Jan,

  1. It's normal behavior of all automation designer actions to make connections first and then run. Health Check was intentionally changed to behave this way also.
  2. Regarding missing your missing action in automation designer, I wonder if Toad imported settings from different installation of Toad than what you expected. Maybe that could also explain the colors not getting imported?

Hi John,

In answering the first question: in the version 12 of Toad i saw that there is making a connection to the first database and than run the healthcheck with the parameters, then connecting to the second database and run the healthcheck with the parameters and then connecting to the third database and run the healthcheck with te parameters etc.... all in one case 155 databases. In Toad 13 i see and i also said conecting first to all the databases and then run the healthcheck with the parameters. Ok, if that the new process then i accept it and now know it.
In answering your second question, no the new installion didn't imported all settings. I found in the options, the email settings didn't imported in the new version. And i installed today the new version so i can't say if i mising things yet. I keep you informed.

ps. can i deïnstall the 13.2 safely

Gr. Jan

Oh, wow, 155 connections. Can Toad handle that many? I can see why one at a time would be preferred. In any case, it should now continue instead of stop if it can't connect to one of them.

Yes, you can safely deinstall 13.2 if you don't want it any longer.

Oh - can you zip up and send me your user files folder from 13.2?

Maybe I can reproduce the connection colors and email settings not being imported.

You can send to john.dorlon@quest.com.

Thanks

Hi John,

email send to you

gr. Jan

Hi John,

yep 155 connections at the time. We split the CDB's and PDB's already. In our case we have 153 CDB's to check and 155 PDB's and 98 stand alone DB's at different time scheduling. Why? We want to check if the databases are still good, therefore the healthcheck with those parameters.

Hi John,

After several days testing and other work i now send you the differences between the previous version HealthCheck 12 and the HealthCheck 13 (latest version) in a zip file. The mail we got with VMPC HealthCheck is the version 12 and the Toad HealthCheck is now the version 13. Also a Word doc with the Installation at our system and the parameters for the Healtcheck. On both servers the same parameters. Only on the version 13 i don't see the invalids and the tablespace %.

So i think i give you more homework :wink: .

Gr. Jan

oeps, i see i can't upload zip files. :frowning:

How can upload this zip file?

Hi Jan,

I got the zip file by email. Thank you. Scroll up a bit (to approx 13 days ago) and you'll see some SQL that I posted. This is the SQL that we use in 12.1 and 13.3 to determine tablespaces with less than X% free space. Please run both of these on the BRD02 database. They should provide the same result but perhaps they do not.

Thank you
John

Hi John,

I run those both scripts on the BRD02 and PGISGEO databases and see below

On BRD02

oracle ovm-repodb01:/nfs01/scripts/beheer_scripts/bin -- brd02
$ sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Vr Mrt 20 12:03:57 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

select df.tablespace_name, round(df.total_bytes / 1048576) total_mb,
round(nvl(fs.free_bytes,0) / 1048576) free_mb,
round(100 * nvl(fs.free_bytes,0) / df.total_bytes, 1) percent_empty
from
(Select tablespace_name, sum(bytes) total_bytes
from dba_data_files
where tablespace_name not in ('SYSTEM')
group by tablespace_name) df,
(Select tablespace_name, sum(bytes) free_bytes
from dba_free_space
where tablespace_name not in ('SYSTEM')
group by tablespace_name) fs
where df.tablespace_name = fs.tablespace_name (+)
14 and nvl(fs.free_bytes,0) / df.total_bytes <= (10 / 100);

TABLESPACE_NAME TOTAL_MB FREE_MB PERCENT_EMPTY


UNDOTBS1 3072 164 5,3

Select tablespace_name,
current_file_size_in_mb total_mb,
free_mb_in_current free_mb,
current_file_size_pct_free percent_empty
from (select files.tablespace_name,
round(files.current_bytes / 1048576) current_file_size_in_mb,
round((files.current_bytes - free.free_bytes) / 1048576) used_mb,
round(free.free_bytes/ 1048576) free_mb_in_current,
round(100 * nvl(free.free_bytes, 0) / files.current_bytes, 1) current_file_size_pct_free,
round(files.max_bytes / 1048576) max_file_size_mb,
round((files.max_bytes - (files.current_bytes - nvl(free.free_bytes, 0))) / 1048576) free_mb_in_max,
round(100 * (files.max_bytes - (files.current_bytes - nvl(free.free_bytes, 0))) / files.max_bytes, 1) max_file_size_pct_free
from (select tablespace_name,
sum(current_bytes) current_bytes,
sum(max_bytes) max_bytes
from (Select df.tablespace_name,
df.bytes current_bytes,
decode(df.autoextensible, 'YES', df.maxbytes, 'NO', df.bytes) max_bytes
from dba_data_files df
where tablespace_name not in ('SYSTEM'))
group by tablespace_name) files,
(Select tablespace_name,
sum(bytes) free_bytes
from dba_free_space
where tablespace_name not in ('SYSTEM')
group by tablespace_name) free
where free.tablespace_name (+) = files.tablespace_name)
28 where current_file_size_pct_free < 10;

TABLESPACE_NAME TOTAL_MB FREE_MB PERCENT_EMPTY


UNDOTBS1 3072 164 5,3

sys @ brd02> exit

At another database PGISGEO

oracle ovm6p:/home/oracle -- cpgisgeo
$ sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Vr Mrt 20 12:07:27 2020

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Verbonden met:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

sys @ cpgisgeo> @set_pdb
oud 1: alter session set container=&1
nieuw 1: alter session set container=PGISGEO

Sessie is gewijzigd.

select df.tablespace_name, round(df.total_bytes / 1048576) total_mb,
round(nvl(fs.free_bytes,0) / 1048576) free_mb,
round(100 * nvl(fs.free_bytes,0) / df.total_bytes, 1) percent_empty
from
(Select tablespace_name, sum(bytes) total_bytes
from dba_data_files
where tablespace_name not in ('SYSTEM')
group by tablespace_name) df,
(Select tablespace_name, sum(bytes) free_bytes
from dba_free_space
where tablespace_name not in ('SYSTEM')
12 group by tablespace_name) fs
where df.tablespace_name = fs.tablespace_name (+)
14 and nvl(fs.free_bytes,0) / df.total_bytes <= (10 / 100);

TABLESPACE_NAME TOTAL_MB FREE_MB PERCENT_EMPTY


CONVERSIE 19456 1390 7,1
BSGW 22016 1677 7,6

Select tablespace_name,
2 current_file_size_in_mb total_mb,
free_mb_in_current free_mb,
current_file_size_pct_free percent_empty
from (select files.tablespace_name,
round(files.current_bytes / 1048576) current_file_size_in_mb,
round((files.current_bytes - free.free_bytes) / 1048576) used_mb,
round(free.free_bytes/ 1048576) free_mb_in_current,
round(100 * nvl(free.free_bytes, 0) / files.current_bytes, 1) current_file_size_pct_free,
round(files.max_bytes / 1048576) max_file_size_mb,
round((files.max_bytes - (files.current_bytes - nvl(free.free_bytes, 0))) / 1048576) free_mb_in_max,
round(100 * (files.max_bytes - (files.current_bytes - nvl(free.free_bytes, 0))) / files.max_bytes, 1) max_file_size_pct_free
from (select tablespace_name,
sum(current_bytes) current_bytes,
sum(max_bytes) max_bytes
from (Select df.tablespace_name,
df.bytes current_bytes,
decode(df.autoextensible, 'YES', df.maxbytes, 'NO', df.bytes) max_bytes
19 from dba_data_files df
where tablespace_name not in ('SYSTEM'))
group by tablespace_name) files,
(Select tablespace_name,
sum(bytes) free_bytes
from dba_free_space
where tablespace_name not in ('SYSTEM')
group by tablespace_name) free
where free.tablespace_name (+) = files.tablespace_name)
28 where current_file_size_pct_free < 10;

TABLESPACE_NAME TOTAL_MB FREE_MB PERCENT_EMPTY


CONVERSIE 19456 1390 7,1
BSGW 22016 1677 7,6

sys @ cpgisgeo>

The same results in sql, but why not from Toad it self? And the BRD02 is a 11.2.0.4 db and PGISGEO a 12.2.0.1 db.

Gr. Jan

That's a good question. Can you make sure that the SQL I gave above for 13.3 is the same as the SQL that your Toad is running?

Easiest way to check is:

  1. Set up a Health check for only 1 database.
  2. Uncheck everything except the tablespace free space item.
  3. Click Main Menu -> Database -> Spool SQL -> Spool to screen
  4. Run the health check.
  5. SQL appears at the bottom of Toad.

If Result if correct, then I wonder if running the health check against 155 databases at the same time is causing some kind of memory problem which causes some results to be missed. I don't know why it would, just making a guess.

-John

Hi John,

Hereby the output

output.txt (26,5 KB)

Gr. Jan

The SQL I gave is using the "Ignoring autoextend" option.
The SQL in output.txt is using the "Observing autoextend" option.

You can see the difference in the last line of the sql. (Current_file_size_pct_free or max_file_size_pct_free)

Hi John,

I found it. In the output window on the server in the HealthCheck i see those 2 tablespaces (ignore extent on) and when Toad is sending the message to me in Outlook there is no information off those 2 tablespaces. Something is going wrong by sending the output.
I'll investigate it next week, or you have the solution.

Gr. Jan

The only thing that I can think of is - look at the health check action that is sending the email. Make sure that it is set to "Ignoring Autoextend". If that's the setting that you want.

With "Ignoring Autoextend"

  • Toad will report tablespaces as almost full, even if the tablespaces have autoextend enabled and tablespace will just grow when it runs out of space.

With "Observing Autoextend"

  • If a tablespace is almost full, but tablespace has autoextend enabled and there is still room for tablespace to grow before max size, then Toad will not include it in the report.
  • If a tablespace is almost full, and tablespace has autoextend enabled and, but max size is reached, then Toad will include it in the report.
  • If a tablespace is almost full, and tablespace has autoextend disabled, then Toad will include it in the report.

Toad 12.1 did not have the Ignoring/Observing option (it behaved as "Ignoring autoextend"), so I think this is the difference that we've been looking for.

Hi John,

I found it. First, I check de prod databases and one database has 2 tablespaces who are less than 10%. With ignore option. Now when i starting the check on the left window in Automation Designer

and run the check there is now output on those 2 tablespaces from PGISGEO. Also when i open the app in the right windows

i've got the same info, now 2 tablespaces info with less then 10%. Also the HTML output file as parameter set to healtcheckPROD.html and looking into it, the same info as i get in the mail. But............ when i open the report output and let it be run, then ....

image

and the saved HTML file also containd the wright info

image

So thats strange, isn't it?

So i hope i told you enough about this and give you some "home" work.

Gr. Jan

Thanks Jan.

It looks like what is happening is that when the health check runs without the GUI, it is not running the health checks on the differrent databases. It appears to be running them all against the same database.

I'll have this fixed soon.

Try it in the beta just released. It should be fixed.

Hi John,

Installed the BETA and it seem good now. I'll try it several days and looking if i see any differences between our older version and this beta version. I'll keep you informed.

Gr. Jan

1 Like

Hi John,

So far so good. We saw only that sometimes the task isn't run because a password isn't correct. In the execution log you can see the error. But we are missing on which database this is wrong. When running it manuay to see which database it is, it's just running fine. Strange. Our solution is to remove all the database from the check and add them again.


Is it possible to add the info from which database this is wrong, to connect to the database. I think i saw it once a time ago, that the password wasn't correct or the database wasn't running. And all this in the screen by running it manualy.

Gr. Jan

It would be nice to identify that in the log. I'll take a look.