Hello Frank,
I am very sorry, for the security reason I never used your RMAN script to
run on the oracle production system.
Now the unique clue I am able to get is that I've found there has plenty of
fragmented tables from our DBaaS monitor platform, the column
actual_used_size and wasted_size brought my attention. Due of the number of
tables is so many I have to sum(wasted_space) and then use it to compare if
it has the real different size 680gb, but I don't think that it has such
huge sizes.
Best Regards
Quanwen Zhao
Pierre Labrousse <Pierre.Labrousse@xxxxxxxxxx> 于2023年11月16日周四 16:55写道：
Hey,
I'm sorry, but indeed Jonathan is right. I missed this change between 10.1
and 10.2, where Oracle no longer takes unused blocks into rman backup.
Maybe I've been working on Oracle for too long time, since V5 ;).
So for your case, another possibility is that some files are OFFLINE.
V$DATAFILE or DBA_DATA_FILES don't show offline datafile, but RMAN take
them into backupset, unless you add "SKIP OFFLINE" into your backup command.
Maybe a clue 😉
Pierre
------------------------------
*De :* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> de
la part de Quanwen Zhao <quanwenzhao@xxxxxxxxx>
*Envoyé :* jeudi 16 novembre 2023 07:21
*À :* gogala.mladen@xxxxxxxxx <gogala.mladen@xxxxxxxxx>
*Cc :* oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>
*Objet :* Re: RMAN backup size is almost bigger twice than all of
tablespace used size?
Thanks for the responses with Stefan, Pierre, Jonathan and Mladen.
Tablespace used size is calculated by the sum(bytes) of total file sizes -
sum(bytes) of dba_free_space.
The compression_ratio is 1 from v$backup_set_details.
The archived logs with rman backup is only 20 gb.
Even if no compressed my rman backup I thinks shoule be only 600gb but
actually 1280gb, so strange!
Best Regards
Quanwen Zhao
Mladen Gogala <gogala.mladen@xxxxxxxxx> 于2023年11月16日周四 09:17写道：
On 11/15/23 10:17, Quanwen Zhao wrote:
Hello Stefan,
Sorry, I forget to mention my RMAN backup is no compression and I've
checked all of the backup sets by v$backup_set_details that has a column
compressed. BTW the Archived Log has just only 20 GB, so the datafile of
rman backup is only 1300 - 20 = 1280 GB, 1280 >> 600 ?
Best Regards
Quanwen Zhao
How many archive logs did you back up? RMAN doesn't backup just data
files, if I remember correctly. Some of the databases that I am working
with use 4GB redo logs. Few hundreds of those can really mess up your
space, so that you can enjoy your ORA-00257 in the middle of the night. And
the bad news is that archive logs typically do not compress well.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217https://dbwhisperer.wordpress.com
