Health Check version 12 vs 13

j.dekker · February 21, 2020, 10:21am

Hi,

An other difference found, In Toad 12 a check on "list tablespaces with <10% free space remaing is different than in Toad 13. In version 12 the Health check gifs me a detail like

Tablespaces with less than 10% free space (TCORBEE.WORLD)
CORSA_IDX : 6,1% Free Space, 1214 of 19968 MB remaining
CORSA_AUDIT_DAT : 3,5% Free Space, 1464 of 41984 MB remaining
CORSA_DAT : 10% Free Space, 1377 of 13824 MB remaining
CORSA_AUDIT_IDX : 8,1% Free Space, 2318 of 28672 MB remaining

and in version 13 nothing.

In version 13 you can check the parameter by param in both version, but in version 12 only by checkbox, in this case 10% and Free space remaining measured in percent or MB. In version 13 list tablespace in ___ free space and measured in percent or MB and "new" in version 13 you can pick Ignoring or Observing. I treid both but no messages.

Gr. Jan

JohnDorlon · February 21, 2020, 1:44pm

I just tested Toad 13.2. I get data back when I set it to "ignoring autoextend". If I raise the percentage, more results show up in the tablespace list. So it seems to be working for me.

Do you have 1 or multiple data files in those tablespaces? It should work with either, but my test environment usually only has 1 file per tablespace.

Also, you said versions 12 and 13 -

there are a lot of 12's and already 3 13's. Please let me know exact version.

j.dekker · March 5, 2020, 8:31am

Hi JohnDorlon,

In the firts place, Toad for Oracle 12.1.0.22 vs 13.2.0.258 (also your version). We also discovered that there is a difference between (Toad 13) in a task by running manualy and through scheduling. Often a differend result. Running a HealthCheck task manualy gives an output for tablespace less than 10%. But the same task scheduled gives no output. Wierd. I will start now to check a database with a tablespace less then 10% and running it manualy and through a Windows scheduling. I'll report you soon.

Gr. Jan

JohnDorlon · March 5, 2020, 1:34pm

Hi Jan,

Thanks for the specifics on version number.
I tried running a health check from command line and got the same result.

When you schedule, do you miss health check results for other checks too or just the tablespace check?

-John

j.dekker · March 6, 2020, 1:50pm

Hi John,

Some info and differences:

Toad 12.1.0.22

Toad 13.2.0.258

Checks on database in both version the same

In version 13 there is now the new feature for observing/ignoring autoexetend. When i check it out for Observing there is no output. In this sample i set it to Ignoring. But one tablespace isn't show.
You asked also for tablespaces with more data_files. In this case only one data_file per tablespace.

And what you say, some times miss health check results for other checks too.

Both Checks are done manualy and not scheduled. That's my next step. I'll keep you informed.

Gr. Jan

JohnDorlon · March 6, 2020, 2:47pm

If you can reproduce the problem without scheduling, then I think we can forget about scheduling.

It sounds like the SQL may need to be changed. I'll try to reproduce this with a few other databases.

Which database version are you using?

JohnDorlon · March 6, 2020, 3:31pm

Using spool SQL, I grabbed the SQL for 12.1 and 13.3 (which is the same as 13.2) and ran them against database oracle versions 12c, 12cR2, 18c, and 19c. You can see that the SQL is quite different, but for me, the result was the same each time. Try repeating this test on your database.

--Toad 12.1

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 (+)
and nvl(fs.free_bytes,0) / df.total_bytes <= (10 / 100);

-- Toad 13.3 (ignoring auto extend)

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)
where   current_file_size_pct_free < 10;

j.dekker · March 11, 2020, 1:56pm

Hoi John,

We are using Oracle 11.2.0.4 and Oracle 12.2.0.1.

I'll try to look into it for scheduling and give you the result in a few days.

I also installed the latest version now 13.3.0.181 and will try serveral things in automation designer. I saw in the version .181 that there is still connecting first to all the databases and then run the healthcheck parameters.
In my case i installed the 13.3.0.181 next to the previous version. The task in automation designer were gone. In both version, after installing the .181 version. No problem i exported the tasks and imported them from another System. Also i saw that the connection overview for some databases the Color were not filled in any more, others still.

In this sample i filled the colors after the new installed version.

Gr. Jan

JohnDorlon · March 11, 2020, 2:07pm

Hi Jan,

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.
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?

j.dekker · March 11, 2020, 2:24pm

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

JohnDorlon · March 11, 2020, 2:30pm

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.

JohnDorlon · March 11, 2020, 2:32pm

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

j.dekker · March 11, 2020, 2:45pm

Hi John,

email send to you

gr. Jan

j.dekker · March 11, 2020, 2:50pm

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.

j.dekker · March 19, 2020, 10:27am

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 .

Gr. Jan

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

How can upload this zip file?

JohnDorlon · March 19, 2020, 4:09pm

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

j.dekker · March 20, 2020, 11:17am

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

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

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

JohnDorlon · March 20, 2020, 1:22pm

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:

Set up a Health check for only 1 database.
Uncheck everything except the tablespace free space item.
Click Main Menu -> Database -> Spool SQL -> Spool to screen
Run the health check.
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

j.dekker · March 20, 2020, 1:57pm

Hi John,

Hereby the output

output.txt (26,5 KB)

Gr. Jan

JohnDorlon · March 20, 2020, 2:06pm

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)