RE: RMAN backup size is almost bigger twice than all of tablespace used size?

From: Pierre Labrousse <Pierre.Labrousse_at_digora.com>
Date: Wed, 15 Nov 2023 15:58:08 +0000
Message-ID: <PR1P264MB18245548FA16DCE98BE1B6A4F6B1A_at_PR1P264MB1824.FRAP264.PROD.OUTLOOK.COM>

It depends how your colleague does its delete.

If it is a partition table (suppose partition by range of date) and he does "alter table drop old partitions", you are in the case : blocks formatted and not used. But if he does a "DELETE FROM ... WHERE col < date", blocks remain attached to the segment (no change of High Water Mark) and are therefore always counted in the blocks column of DBA_SEGMENTS (I suppose your query to check space used is SELECT TABLESPACE_NAME, SUM(BLOCKS) from DBA_SEGMENTS GROUP BY TABLESPACE_NAME). If he also launch a ALTER TABLE ... SHRINK SPACE, the High Water Mark is now updated and blocks are available for other extents, but blocks are taken by RMAN, because they have been formatted one time. (Sorry for my poor english, hope so I'm clear in my explanation 😉).

Pierre LABROUSSE
Consultant DBA ORACLE (OCM 10g/11g/12c) Mobile +33 (0)7 56 05 27 38
pierre.labrousse<https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com%2520%250d>_at_<https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com%2520%250d>digora.co<https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com%2520%250d>m<https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com>

De : Quanwen Zhao <quanwenzhao_at_gmail.com> Envoyé : mercredi 15 novembre 2023 16:25 À : Pierre Labrousse <Pierre.Labrousse_at_digora.com> Cc : ORACLE-L <oracle-l_at_freelists.org> Objet : Re: RMAN backup size is almost bigger twice than all of tablespace used size?

Yes, the app developer colleague always deleted the table's history data interval by some day to make his business tablespace used-ratio being lower than 75%.

The previous behaviour caused this issue?

Best Regards
Quanwen Zhao

Pierre Labrousse <Pierre.Labrousse_at_digora.com<mailto:Pierre.Labrousse_at_digora.com>> 于2023年11月15日周三 23:17写道： Hey,

Rman only takes into account formatted blocks and not blocks never used, so if you drop or truncate a table its blocks are considered formatted and take into backupset even blocks are empty, this generally explains the size difference

Best.

De : oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> de la part de Quanwen Zhao <quanwenzhao_at_gmail.com<mailto:quanwenzhao_at_gmail.com>> Envoyé : mercredi 15 novembre 2023 15:14 À : ORACLE-L <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Objet : RMAN backup size is almost bigger twice than all of tablespace used size?

Hello everyone!

Recenty I've notice that there has oracle product system with 19.13, RMAN backup size is almost bigger twice than all of tablespace used size?

Such as, all of tablespace used size is 600 GB and total size is 2500 GB, RMAN backup size (generated backup sets) is 1300 GB.

Generally speaking, RMAN backup size is almost equal to all of tablespace used size.

Firstly I suspect oracle recyclebin has occupied the massive data but the parameter recyclebin is off.

Now I have to infer business tablespace has produced plenty of table fragments or HWM (High Water Mask) or discontinuous free data blocks.

How to troubleshoot it for me? Any suggestion would be very appreciated!

Best Regards
Quanwen Zhao

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 15 2023 - 16:58:08 CET

This message: [ Message body ]
Next message: Pierre Labrousse: "RE: RMAN dup issue"
Previous message: Amit Saroha: "Re: Hint is not used in SQL"
Maybe in reply to: Quanwen Zhao: "RMAN backup size is almost bigger twice than all of tablespace used size?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message