CRITICAL - unable to aquire tablespace info

[amane]

We are getting error 'CRITICAL - unable to aquire tablespace info' for tablespace monitoring for one server.

Status continuously changed from ok to 'CRITICAL - unable to aquire tablespace info'. There are around 245 tablespaces available on the database.

There is no load on DB as well even though Nagios is not able to get the data sometime.

Could you please help to resolve this issue.

[cdienger]

Try running the command from the command line with the option "-t 60" to set the timeout. Also try "-t 120". If it still doesn't work, run the command with the "-v" option to get some verbose output and PM it to me.

[amane]

Hi cdienger,

This issue is intermediate. Sometime will get the output.

Please find attached output for the command which i ran with using -v.

Command Output: -

Thu May 2 10:48:03 2019: fetchrow_array: SELECT version FROM v$instance
Thu May 2 10:48:03 2019: args: $VAR1 = [];

Thu May 2 10:48:03 2019: RESULT:
$VAR1 = [
'12.2.0.1.0'
];


Thu May 2 10:48:03 2019: fetchrow_array:
SELECT
d.platform_name,
sys_context('userenv', 'session_user'),
i.thread#,
i.parallel,
i.instance_name,
d.name,
g.global_name,
i.host_name
FROM
dual,
v$instance i,
v$database d,
global_name g

Thu May 2 10:48:03 2019: args: $VAR1 = [];

Thu May 2 10:48:03 2019: RESULT:
$VAR1 = [
'AIX-Based Systems (64-bit)',
'ESMSVC',
'2',
'YES',
'REAIRPRD2',
'REAIRPRD',
'REAIRPRD',
'orareaiprodr2'
];


Thu May 2 10:48:03 2019: fetchall_array: SELECT /*+ opt_param('optimizer_adaptive_features','false') */
a.tablespace_name "Tablespace",
b.status "Status",
b.contents "Type",
b.extent_management "Extent Mgmt",
a.bytes bytes,
a.maxbytes bytes_max,
c.bytes_free + NVL(d.bytes_expired,0) bytes_free
FROM
(
-- belegter und maximal verfuegbarer platz pro datafile
-- nach tablespacenamen zusammengefasst
-- => bytes
-- => maxbytes
SELECT
a.tablespace_name,
SUM(a.bytes) bytes,
SUM(DECODE(a.autoextensible, 'YES', a.maxbytes, 'NO', a.bytes)) maxbytes
FROM
dba_data_files a
GROUP BY
tablespace_name
) a,
sys.dba_tablespaces b,
(
-- freier platz pro tablespace
-- => bytes_free
SELECT
a.tablespace_name,
SUM(a.bytes) bytes_free
FROM
dba_free_space a
GROUP BY
tablespace_name
) c,
(
-- freier platz durch expired extents
-- speziell fuer undo tablespaces
-- => bytes_expired
SELECT
tablespace_name, bytes_expired
FROM
(
SELECT
a.tablespace_name,
SUM (a.bytes) bytes_expired,
a.status
FROM
dba_undo_extents a
GROUP BY
tablespace_name, status
)
WHERE
status = 'EXPIRED'
) d
WHERE
a.tablespace_name = c.tablespace_name (+)
AND a.tablespace_name = b.tablespace_name
AND a.tablespace_name = d.tablespace_name (+)
UNION ALL
SELECT
d.tablespace_name "Tablespace",
b.status "Status",
b.contents "Type",
b.extent_management "Extent Mgmt",
sum(a.bytes_free + a.bytes_used) bytes, -- allocated
SUM(DECODE(d.autoextensible, 'YES', d.maxbytes, 'NO', d.bytes)) bytes_max,
SUM(a.bytes_free + a.bytes_used - NVL(c.bytes_used, 0)) bytes_free
FROM
sys.v_$TEMP_SPACE_HEADER a,
sys.dba_tablespaces b,
sys.v_$Temp_extent_pool c,
dba_temp_files d
WHERE
c.file_id(+) = a.file_id
and c.tablespace_name(+) = a.tablespace_name
and d.file_id = a.file_id
and d.tablespace_name = a.tablespace_name
and b.tablespace_name = a.tablespace_name
GROUP BY
b.status,
b.contents,
b.extent_management,
d.tablespace_name
ORDER BY
1
Thu May 2 10:48:03 2019: args: $VAR1 = [];

Thu May 2 10:48:28 2019: RESULT:
$VAR1 = [];


CRITICAL - unable to aquire tablespace info
Thu May 2 10:48:28 2019: disconnecting DBD without handle

[cdienger]

Did you try increasing the timeout(with -t 60) when you ran the command that produced the provided output? It looks like it is timing out after 25 seconds.

[amane]

Yes, I tried with 120 as well.

[ssax]

Did the number of the occurrences get reduced when you made the change by adding -t 120?

Please make the change and then send me a fresh copy of your profile after you've applied configuration, you can download it from Admin > System Profile > Download Profile.

How long on average does that query take to complete when the server is under a little load? Can you have your DBAs profile that query during a high load time to see how long it takes?

[amane]

Hi Ssax,

Did the number of the occurrences get reduced when you made the change by adding -t 120?
No, it is not reducing the occurrences after making the changes. We added the -t 100 only for tablespaces monitoring on orareaiprodr2 server.

Please find attached profile copy.

Checked with DBA, as per them there is no load on database.

Thanks & Regards,
amane

[scottwilkerson]

If you run the command again from the CLI with the -t 100 AND -v does it still timeout after 25 seconds?

I'm thinking it possibly could be a setting on the Oracle DB that is timing out the request