Unfortunately (of for good reasons) Oracle never made the ASM diskgroups directly mountable into an OS - instead the tool asmcmd is given which can be used to navigate the structure and manipulate files.
Normally there is little need to tweak with files there as all files are oracle-managed by default.
Still sometimes I have to do a cleanup. most of the time someone suspects there are leftovers which do consume significant space but are not required anymore. Unfortunately asmcmd does not help me a lot finding those directories which contain files of significant space but at the same time does have files which were not touched for smoe time. So I created a little report for my help.
The report has about 70 lines, so I put it into my git as ASM_agg_dirs.sql. It contains 3 sub-selects which do the work:
all_directories to get the path of the directories,
all_directories to get the path of the directories,
byte_to_every_dir to get the size and dates for a sub-path down from the leaves and
dir_sizes_dates to aggregate the byte_to_every_dir.
All together it shows for every directory the size of all its containing files including the sub-directories. For the age of the files, the earliest and latest modify date is aggregated and propagated. Its result can be something like
FULL_ALIAS_PATH SUM_BYTES GB LVL EARLIEST_MODIFY_ LATEST_MODIFY_DA
------------------------------------------------------------- ----------- --------- --- ---------------- ----------------
+DATA_DG/ASM 6656 .00 1 2020-12-10 16:00 2020-12-10 16:00
+DATA_DG/ASM/PASSWORD 6656 .00 2 2020-12-10 16:00 2020-12-10 16:00
+DATA_DG/DB_UNKNOWN 36864 .00 1 2022-06-08 09:00 2022-06-08 09:00
+DATA_DG/DB_UNKNOWN/PASSWORD 36864 .00 2 2022-06-08 09:00 2022-06-08 09:00
+DATA_DG/my_cluster 354004480 .33 1 2020-12-10 16:00 2023-07-13 11:00
+DATA_DG/my_cluster/ASMPARAMETERFILE 3584 .00 2 2020-12-10 16:00 2020-12-10 16:00
+DATA_DG/my_cluster/OCRBACKUP 188026880 .18 2 2021-02-23 18:00 2023-07-13 11:00
+DATA_DG/my_cluster/OCRFILE 165974016 .15 2 2023-06-11 11:00 2023-06-11 11:00
+DATA_DG/DB_UNIQ_1 610545300480 568.61 1 2022-04-13 08:00 2023-07-13 14:00
+DATA_DG/DB_UNIQ_1/BC3B5B02E2AAAD7BE0532401210A1E60 492219031552 458.41 2 2023-06-11 10:00 2023-07-04 22:00
+DATA_DG/DB_UNIQ_1/BC3B5B02E2AAAD7BE0532401210A1E60/DATAFILE 491145281536 457.41 3 2023-07-04 21:00 2023-07-04 22:00
+DATA_DG/DB_UNIQ_1/BC3B5B02E2AAAD7BE0532401210A1E60/TEMPFILE 1073750016 1.00 3 2023-06-11 10:00 2023-06-11 10:00
+DATA_DG/DB_UNIQ_1/B50214B4214037B1E053C40DD10A4E69 7231012864 6.73 2 2023-06-11 10:00 2023-07-04 22:00
+DATA_DG/DB_UNIQ_1/B50214B4214037B1E053C40DD10A4E69/DATAFILE 6182428672 5.76 3 2023-07-04 22:00 2023-07-04 22:00
+DATA_DG/DB_UNIQ_1/B50214B4214037B1E053C40DD10A4E69/TEMPFILE 1048584192 .98 3 2023-06-11 10:00 2023-06-11 10:00
+DATA_DG/DB_UNIQ_1/CONTROLFILE 676773888 .63 2 2023-06-11 10:00 2023-07-12 23:00
+DATA_DG/DB_UNIQ_1/DATAFILE 31809601536 29.63 2 2023-07-04 22:00 2023-07-04 22:00
+DATA_DG/DB_UNIQ_1/DATAGUARDCONFIG 32768 .00 2 2023-07-13 14:00 2023-07-13 14:00
+DATA_DG/DB_UNIQ_1/DG 32768 .00 2 2023-07-13 14:00 2023-07-13 14:00
+DATA_DG/DB_UNIQ_1/ONLINELOG 77309429760 72.00 2 2022-12-10 08:00 2023-07-13 14:00
+DATA_DG/DB_UNIQ_1/PARAMETERFILE 35840 .00 2 2023-07-13 12:00 2023-07-13 12:00
+DATA_DG/DB_UNIQ_1/PASSWORD 5120 .00 2 2022-04-13 08:00 2022-04-13 08:00
+DATA_DG/DB_UNIQ_1/pdbseed 2
+DATA_DG/DB_UNIQ_1/pdbseed/DATAFILE 3
+DATA_DG/DB_UNIQ_1/pdb1 2
+DATA_DG/DB_UNIQ_1/pdb1/DATAFILE 3
+DATA_DG/DB_UNIQ_1/TEMPFILE 1073750016 1.00 2 2023-06-11 10:00 2023-06-11 10:00
+DATA_DG/DB_UNIQ_2 780968243712 727.33 1 2021-09-21 15:00 2023-07-13 14:00
+DATA_DG/DB_UNIQ_2/CONTROLFILE 627933184 .58 2 2023-06-11 10:00 2023-07-13 02:00
+DATA_DG/DB_UNIQ_2/C7CAD0D3231198B1E0532401210A7165 8196775936 7.63 2 2021-09-21 15:00 2023-07-04 02:00
+DATA_DG/DB_UNIQ_2/C7CAD0D3231198B1E0532401210A7165/DATAFILE 8069890048 7.52 3 2021-09-21 15:00 2023-07-04 02:00
+DATA_DG/DB_UNIQ_2/C7CAD0D3231198B1E0532401210A7165/TEMPFILE 126885888 .12 3 2023-06-11 10:00 2023-06-11 10:00
+DATA_DG/DB_UNIQ_2/C7CAF2050A22AC66E0532601210A9FF9 705188782080 656.76 2 2021-09-21 15:00 2023-07-13 02:00
+DATA_DG/DB_UNIQ_2/C7CAF2050A22AC66E0532601210A9FF9/DATAFILE 686935162880 639.76 3 2021-09-21 15:00 2023-07-13 02:00
+DATA_DG/DB_UNIQ_2/C7CAF2050A22AC66E0532601210A9FF9/TEMPFILE 18253619200 17.00 3 2023-06-11 10:00 2023-06-11 10:00
+DATA_DG/DB_UNIQ_2/DATAFILE 55903256576 52.06 2 2021-09-21 15:00 2023-07-13 02:00
+DATA_DG/DB_UNIQ_2/DATAGUARDCONFIG 24576 .00 2 2023-07-13 14:00 2023-07-13 14:00
+DATA_DG/DB_UNIQ_2/DG 24576 .00 2 2023-07-13 14:00 2023-07-13 14:00
+DATA_DG/DB_UNIQ_2/ONLINELOG 9663694848 9.00 2 2022-12-10 10:00 2023-07-13 14:00
+DATA_DG/DB_UNIQ_2/PARAMETERFILE 27648 .00 2 2023-07-13 12:00 2023-07-13 12:00
+DATA_DG/DB_UNIQ_2/PASSWORD 5120 .00 2 2021-09-21 15:00 2021-09-21 15:00
+DATA_DG/DB_UNIQ_2/pdbseed 2
+DATA_DG/DB_UNIQ_2/pdbseed/DATAFILE 3
+DATA_DG/DB_UNIQ_2/pdb1 2
+DATA_DG/DB_UNIQ_2/pdb1/DATAFILE 3
+DATA_DG/DB_UNIQ_2/TEMPFILE 1073750016 1.00 2 2023-06-11 10:00 2023-06-11 10:00
In lines 51 & 52 of the script the projection should be commented and instead the block between 55 and 59 should be active. I there only aggregate data for files last modified 90 days or older in the past.
The result provides a much more clear picture of possible savings:
+DATA_DG/DB_UNIQ_2 408085720576 380.06 1 2021-09-21 15:00 2022-12-10 10:00
+DATA_DG/DB_UNIQ_2/CONTROLFILE 2
+DATA_DG/DB_UNIQ_2/C7CAD0D3231198B1E0532401210A7165 1887461376 1.76 2 2021-09-21 15:00 2021-09-21 15:00
+DATA_DG/DB_UNIQ_2/C7CAD0D3231198B1E0532401210A7165/DATAFILE 1887461376 1.76 3 2021-09-21 15:00 2021-09-21 15:00
+DATA_DG/DB_UNIQ_2/C7CAD0D3231198B1E0532401210A7165/TEMPFILE 3
+DATA_DG/DB_UNIQ_2/C7CAF2050A22AC66E0532601210A9FF9 378750590976 352.74 2 2021-09-21 15:00 2021-09-21 15:00
+DATA_DG/DB_UNIQ_2/C7CAF2050A22AC66E0532601210A9FF9/DATAFILE 378750590976 352.74 3 2021-09-21 15:00 2021-09-21 15:00
+DATA_DG/DB_UNIQ_2/C7CAF2050A22AC66E0532601210A9FF9/TEMPFILE 3
+DATA_DG/DB_UNIQ_2/DATAFILE 25300172800 23.56 2 2021-09-21 15:00 2021-09-21 15:00
+DATA_DG/DB_UNIQ_2/DATAGUARDCONFIG 2
+DATA_DG/DB_UNIQ_2/DG 2
+DATA_DG/DB_UNIQ_2/ONLINELOG 2147487744 2.00 2 2022-12-10 10:00 2022-12-10 10:00
+DATA_DG/DB_UNIQ_2/PARAMETERFILE 2
+DATA_DG/DB_UNIQ_2/PASSWORD 5120 .00 2 2021-09-21 15:00 2021-09-21 15:00
Somewhere in the PDB with GUID C7CAF2050A22AC66E0532601210A9FF9 about 352 GB of "old" files may be hidden.
Checking the files the situation is obvious:
ls -l
Type Redund Striped Time Sys Name
DATAFILE HIGH COARSE SEP 21 2021 Y ABC_DATA.2828.1083856881
DATAFILE HIGH COARSE JUL 13 02:00:00 Y ABC_DATA.2850.1083857223
DATAFILE HIGH COARSE SEP 21 2021 Y XYZ.2829.1083856889
DATAFILE HIGH COARSE JUL 13 02:00:00 Y XYZ.2851.1083857229
DATAFILE HIGH COARSE SEP 21 2021 Y DEF.2827.1083856881
DATAFILE HIGH COARSE JUL 13 02:00:00 Y DEF.2849.1083857221
DATAFILE HIGH COARSE SEP 21 2021 Y GHI.2826.1083856879
DATAFILE HIGH COARSE JUL 13 02:00:00 Y GHI.2848.1083857221
DATAFILE HIGH COARSE SEP 21 2021 Y SYSAUX.2819.1083856867
DATAFILE HIGH COARSE JUL 13 02:00:00 Y SYSAUX.2841.1083857211
DATAFILE HIGH COARSE SEP 21 2021 Y SYSTEM.2818.1083856865
DATAFILE HIGH COARSE JUL 13 02:00:00 Y SYSTEM.2840.1083857209
DATAFILE HIGH COARSE SEP 21 2021 Y UNDOTBS1.2820.1083856871
DATAFILE HIGH COARSE JUL 13 02:00:00 Y UNDOTBS1.2842.1083857213
DATAFILE HIGH COARSE SEP 21 2021 Y UNDO_4.2821.1083856873
DATAFILE HIGH COARSE JUL 13 02:00:00 Y UNDO_4.2843.1083857215
DATAFILE HIGH COARSE SEP 21 2021 Y UNDO_5.2822.1083856873
DATAFILE HIGH COARSE JUL 13 02:00:00 Y UNDO_5.2844.1132579919
DATAFILE HIGH COARSE SEP 21 2021 Y UNDO_6.2823.1083856875
DATAFILE HIGH COARSE JUL 13 02:00:00 Y UNDO_6.2845.1132579917
DATAFILE HIGH COARSE SEP 21 2021 Y UVW.2824.1083856877
DATAFILE HIGH COARSE JUL 13 02:00:00 Y UVW.2846.1083857217
It seems in September 2021 somehow all the files were copied into the ASM, but the step was done twice for whatever reason. Unfortunately the old files were never cleared up, but now I can release some space in the diskgroup (of course firs ' crosscheck with the CDB if the datafiles are really not needed anymore).
Keine Kommentare:
Kommentar veröffentlichen