Hello all,
I am trying to understand the output size from the query that i am running below. My db version is 11.2.0.3(on hp-ux ia)...By total db size is 8.47gb of which 5.31 is being used. I ran a daily RMAN backup at 4am to disk with below command (passing $LEVEL as either 0 or 1)....Level 0 is on Sunday from below output (march 17, 10, 03)...i get the input size around the 8GB during the LEVEL 0 run...but when level 1 runs the input size goes around 11gb ?? Am i reading that wrong or something? There is not block change tracking file in use or anything...but below rman cmd daily(with level 0 on sunday and rest of the day level 1)....
backup incremental
level=$LEVEL cumulative database plus archivelog delete input;
SQL> ho more rman_backup_size.sql
column status format a10
column COMMAND_ID for a20
column time_taken_display format a10;
column input_bytes_display format a12;
column output_bytes_display format a12;
column output_bytes_per_sec_display format a10;
column ses_key format 9999999
column ses_recid format 9999999
column device_type format a10
column OutBytesPerSec for a13
SELECT b.session_key ses_key,
b.session_recid ses_recid,
b.session_stamp,
b.command_id,
b.input_type,
b.status,
to_char(b.start_time,'DD-MM-YY HH24:MI') "Start Time",
b.time_taken_display,
b.output_device_type device_type,
b.input_bytes_display,
b.output_bytes_display,
b.output_bytes_per_sec_display "OutBytesPerSec"
FROM v$rman_backup_job_details b
ORDER BY b.start_time asc;
SQL>
SQL> @rman_backup_size
| | SESSION| | | | |TIME |DEVICE | | |
SES_KEY|SES_RECID| STAMP|COMMAND_ID |INPUT_TYPE |STATUS |Start Time |TAKEN |TYPE |INPUT_BYTES_|OUTPUT_BYTES|OutBytesPerSe
--------|---------|------------|--------------------|-------------|----------|--------------|----------|----------|------------|------------|-------------
3310| 3310| 806212806|2013-02-01T04:00:01 |ARCHIVELOG |COMPLETED |01-02-13 04:02|00:00:11 |DISK | 4.73M | 4.74M | 441.00K
3318| 3318| 806299207|2013-02-02T04:00:01 |DB INCR |COMPLETED |02-02-13 04:00|00:02:33 |DISK | 11.25G | 4.39G | 29.41M
3326| 3326| 806385608|2013-02-03T04:00:01 |DB INCR |COMPLETED |03-02-13 04:00|00:02:34 |DISK | 7.98G | 7.74G | 51.50M
3334| 3334| 806472007|2013-02-04T04:00:01 |DB INCR |COMPLETED |04-02-13 04:00|00:02:12 |DISK | 11.28G | 3.26G | 25.26M
3342| 3342| 806558407|2013-02-05T04:00:01 |DB INCR |COMPLETED |05-02-13 04:00|00:02:15 |DISK | 11.25G | 3.46G | 26.22M
3350| 3350| 806644807|2013-02-06T04:00:01 |DB INCR |COMPLETED |06-02-13 04:00|00:02:12 |DISK | 11.28G | 3.74G | 28.99M
3358| 3358| 806731207|2013-02-07T04:00:01 |DB INCR |COMPLETED |07-02-13 04:00|00:02:15 |DISK | 11.29G | 3.97G | 30.14M
3366| 3366| 806817607|2013-02-08T04:00:01 |DB INCR |COMPLETED |08-02-13 04:00|00:02:15 |DISK | 11.28G | 4.19G | 31.77M
3374| 3374| 806904006|2013-02-09T04:00:00 |DB INCR |COMPLETED |09-02-13 04:00|00:02:13 |DISK | 11.30G | 4.44G | 34.17M
3382| 3382| 806990406|2013-02-10T04:00:00 |DB INCR |COMPLETED |10-02-13 04:00|00:02:22 |DISK | 8.08G | 7.85G | 56.61M
3390| 3390| 807076807|2013-02-11T04:00:01 |DB INCR |COMPLETED |11-02-13 04:00|00:02:16 |DISK | 11.22G | 3.21G | 24.14M
3398| 3398| 807163207|2013-02-12T04:00:01 |DB INCR |COMPLETED |12-02-13 04:00|00:02:25 |DISK | 11.23G | 3.47G | 24.50M
3406| 3406| 807249608|2013-02-13T04:00:01 |DB INCR |COMPLETED |13-02-13 04:00|00:02:25 |DISK | 11.34G | 3.81G | 26.91M
3414| 3414| 807336006|2013-02-14T04:00:01 |DB INCR |COMPLETED |14-02-13 04:00|00:02:12 |DISK | 11.30G | 4.00G | 31.07M
3422| 3422| 807422407|2013-02-15T04:00:01 |DB INCR |COMPLETED |15-02-13 04:00|00:02:15 |DISK | 11.27G | 4.20G | 31.84M
3430| 3430| 807508807|2013-02-16T04:00:01 |DB INCR |COMPLETED |16-02-13 04:00|00:02:21 |DISK | 11.24G | 4.40G | 31.93M
3438| 3438| 807595206|2013-02-17T04:00:01 |DB INCR |COMPLETED |17-02-13 04:00|00:02:31 |DISK | 8.02G | 7.79G | 52.84M
3446| 3446| 807681607|2013-02-18T04:00:01 |DB INCR |COMPLETED |18-02-13 04:00|00:02:16 |DISK | 11.29G | 3.27G | 24.63M
18 rows selected.
Elapsed: 00:00:00.22
SQL>
SQL> ho more db_size.sql
Prompt
Prompt ============================================================
Prompt |Shows sum of datafile (dose not include temp/redo/control)|
Prompt ============================================================
set timi off;
select round(sum(bytes)/1024/1024/1024,2) "Total_GB" from dba_data_files;
SELECT round(SUM(bytes)/1024/1024/1024,2) "Used_GB" FROM dba_segments;
set timi on;
SQL>
SQL> @db_size
============================================================
|Shows sum of datafile (dose not include temp/redo/control)|
============================================================
Total_GB
----------
8.47
Used_GB
----------
5.31
SQL>