Hi,
We have a 11.2.04 database with the following footprint
SQL> select sum(bytes)/1024/1024/1024 from dba_segments;
SUM(BYTES)/1024/1024/1024
-------------------------
331.31
SQL> select sum(bytes)/1024/1024/1024 from dba_Data_files;
SUM(BYTES)/1024/1024/1024
-------------------------
369.84
Here are the RMAN settings..
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TESTDB are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_testdb1.f'; # default
We made a level 0 RMAN backup as below...(this is the first ever backup we made on this database)
run
{
backup incremental level 0 as compressed backupset database;
delete archivelog until time 'sysdate -5';
backup archivelog all;
delete noprompt obsolete;
}
The backup completed successfully and after the backup,we queried the FRA view to see space usage
select * from v$flash_recovery_area_usage
/
NAME SPACE LIMIT (GB) SPACE AVAILABLE (GB) PERCENT_FULL
-------------- ---------------- -------------------- ------------
+RECO 1,800.00 1,762.01 2.10
So according to above, the backup took about only 38 GB
Running another query confirms that the backup took about 41 min and the size was 38 GB
Select min(Start_time) BS,max(completion_time) BE,
sum(bytes/1024/1024/1024) BSS, ((max(completion_time) - min(Start_time))*24*60*60/60) TIM
from v$backup_piece
where
handle is not null
/
Backup started at Backup ended at Backup Size in GB Backup Elapsed Time - In Minutes
------------------ ------------------ ----------------- --------------------------------
29-JUN-17 07:18:24 29-JUN-17 07:59:24 37.99 41.00
One of our question is that with the database size of appx 350 GB .. is it possible that with basic compression, the backups could be compressed upto 10% of the database foot print?
Also, when we query the v$backup_piece, we see that the under HANDLE there are like 4 values of appx 70 GB each but no handle (null handle)
what could that be?
SQL> select stamp,tag,start_time,completion_time,handle,bytes/1024/1024/1024 GB from v$backup_piece order by 1,2;
STAMP TAG START_TIME COMPLETION_TIME HANDLE GB
---------- -------------------- ------------------ ------------------ -------------------------------------------------------------------------------- --------
947920704 TAG20170629T071823 29-jun-17 07:18:24 29-jun-17 07:36:48 +RECO/testdb/backupset/2017_06_29/nnndn0_tag20170629t071823_0.466.947920705 7.10
947920704 TAG20170629T071823 29-jun-17 07:18:24 29-jun-17 07:50:17 +RECO/testdb/backupset/2017_06_29/nnndn0_tag20170629t071823_0.464.947920705 12.92
947920704 TAG20170629T071823 29-jun-17 07:18:24 29-jun-17 07:38:29 +RECO/testdb/backupset/2017_06_29/nnndn0_tag20170629t071823_0.465.947920705 7.92
947920707 TAG20170629T071823 29-jun-17 07:18:27 29-jun-17 07:37:08 +RECO/testdb/backupset/2017_06_29/nnndn0_tag20170629t071823_0.467.947920707 7.23
947921811 TAG20170629T071823 29-jun-17 07:36:51 29-jun-17 07:36:51 +RECO/testdb/backupset/2017_06_29/ncnnn0_tag20170629t071823_0.468.947921811 .00
947921812 TAG20170629T071823 29-jun-17 07:36:52 29-jun-17 07:36:52 +RECO/testdb/backupset/2017_06_29/nnsnn0_tag20170629t071823_0.469.947921813 .00
947922637 TAG20170629T075033 29-jun-17 07:50:37 29-jun-17 07:59:29 63.89
947922638 TAG20170629T075033 29-jun-17 07:50:38 29-jun-17 07:58:57 69.86
947922638 TAG20170629T075033 29-jun-17 07:50:38 29-jun-17 07:59:00 69.68
947922638 TAG20170629T075033 29-jun-17 07:50:38 29-jun-17 07:59:21 69.87
947923144 TAG20170629T075033 29-jun-17 07:59:04 29-jun-17 07:59:24 +RECO/testdb/backupset/2017_06_29/annnf0_tag20170629t075033_0.476.947923145 2.81
11 rows selected.
And when we run the following query
select
-- SESSION_KEY,
INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs,
output_device_type dev,
input_bytes_display inbytes,
output_bytes_display outbytes
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key
/
INPUT_TYPE STATUS START_TIME END_TIME HRS DEV INBYTES OUTBYTES
------------- --------- --------------- --------------- ------- ----- ---------- ----------
DB INCR COMPLETED 06/29/17 07:18 06/29/17 07:59 .69 DISK 607.49G 311.29G
it shows us the input_bytes to be 607 GB and the backup output size to be 311 GB.
Now if we add up the bytes with handle=null from the v$backup_piece, it does comes out to be 311 GB as well
Can someone please clarify this for us
1. What does 607.49 GB and 311.29 GB represents from the query against V$RMAN_BACKUP_JOB_DETAILS
2. Is the actual backup size 311 GB OR 38 GB?
3. What does the TAG reprsents that has the handle as NULL?
Thanks in advance for your guidance and support