Disk disappeared with no increase in tablespace allocation
Date: Tue, 18 Jul 2017 13:54:02 -0500
Message-ID: <13a18c98336591b75cb95fafd27428b1_at_society.servebeer.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 18 2017 - 20:54:02 CEST