Hey all,
11.2.0.3 on AIX 7.1, we got a disk space alert yesterday for both our
primary and open-read-only Active Data Guard physical standby mount
points (no ASM). 24GB went away on each of those over 3 minutes, so
it's seems logical that the change was on the primary, which then
replicated to the standby. The total size for each mount point did not
change. The mount point for each database contains _only_ the data
files (incl temp and undo) for its respective database, plus a ~50MB
block change tracking file. Redo, control, archive and other files live
elsewhere.
I can't find where that disk space went. Redos are archived/RMANd every
15 minutes for DR, so I log mined the one that wholly contains the
timestamp of the 24GB lost space on the primary. Just our standard DML,
with nothing out of the ordinary. No large INSERTs or blocks of INSERTs
that I can see, which would seem to be validated by there only being a
single 80MB archive log around that time.
We have EM12c, so I ran this against the repository DB:
SELECT
collection_timestamp,
key_value tablespace,
value mb_max_free,
CASE WHEN key_value = LAG(key_value) OVER (ORDER BY key_value,
collection_timestamp)
THEN value - LAG(value) OVER (ORDER BY key_value,
collection_timestamp)
ELSE NULL
END delta_mb_max_Free
FROM
sysman.mgmt$metric_details
WHERE
target_name = 'primarydb'
AND target_type = 'oracle_database'
AND target_guid = 'sqlgofastnowwiththishere'
AND collection_timestamp >= TRUNC(SYSDATE-1)
AND metric_name LIKE 'problemTbsp%'
AND column_label LIKE 'Tablespace Free Space (MB)%'
ORDER BY
key_value,
collection_timestamp;
Nothing out of the ordinary. TEMP and UNDO tablespace usage does not
even approach 50% of allocated space. All other tablespaces have mere
MB usage increases.
I have an EM12c metric extension that collects the SUM(bytes) from
DBA_SEGMENTS of the 30 largest tables in the primary application's
tablespace (largest table ~150GB). A query against that metric in the
EM12c repository shows increases in the low MB range, an order of
magnitude below the 24GB actually used.
I've also compared the output of "du -m" on the mount points with what
our monitoring software (Zabbix) says is used, and they agree.
So, _something_ increased allocation -- but what?
TIA,
Rich
