Home » RDBMS Server » Backup & Recovery » SQL qery to find rman backup size generated per week using rman catalog database (Oracle 10g, Oracle 11)
SQL qery to find rman backup size generated per week using rman catalog database [message #648391] Mon, 22 February 2016 22:55 Go to next message
SamuelJk
Messages: 25
Registered: August 2007
Junior Member
I want to generate rman backup size report for all databases registered with our catalog database

1) Per day (FULL/incremental 0 /incremental 1)
2) Per week (FULL/incremental 0 /incremental 1)
3) Per Month (FULL/incremental 0 /incremental 1)


Note :
We have FULL, incremental level 0, Incremental level 1 backup scheduled. I want to generate sql query to generate report for each type using recovery catalog dictionary tables or views

Report message to a moderator

Re: SQL qery to find rman backup size generated per week using rman catalog database [message #648399 is a reply to message #648391] Tue, 23 February 2016 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

You know where to find the information, so what is your problem to write the query?
Post what you already tried.

Report message to a moderator

Re: SQL qery to find rman backup size generated per week using rman catalog database [message #648406 is a reply to message #648399] Tue, 23 February 2016 01:55 Go to previous messageGo to next message
SamuelJk
Messages: 25
Registered: August 2007
Junior Member
Michel Cadot wrote on Tue, 23 February 2016 01:03

You know where to find the information, so what is your problem to write the query?
Post what you already tried.


"
This is SQL we used.

select DB_NAME, INPUT_TYPE,INPUT_TYPE_WEIGHT,
STATUS,
TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,
round(ELAPSED_SECONDS/3600,2) hrs,
round(INPUT_BYTES/1024/1024/1024,2) SUM_BYTES_BACKED_IN_GB,
round(OUTPUT_BYTES/1024/1024/1024,2) SUM_BACKUP_PIECES_IN_GB,
OUTPUT_DEVICE_TYPE
FROM RC_RMAN_BACKUP_JOB_DETAILS where trunc(START_TIME) between '01-JAN-2016' AND '31-JAN-2016' and input_type != 'ARCHIVELOG'
order by db_name,input_type, START_TIME";

sample output
DB_NAME INPUT_TYPE STATUS START_TIME END_TIME HRS SUM_BYTES_BACKED_IN_GB SUM_BACKUP_PIECES_IN_GB OUTPUT_DEVICE_TYPE
XYZ DB INCR COMPLETED 01/01/16 19:00 01/01/16 19:05 0.07 0.85 0.46 SBT_TAPE
XYZ DB INCR COMPLETED 01/02/16 19:00 01/02/16 19:03 0.05 0.9 0.49 SBT_TAPE
XYZ DB INCR COMPLETED 01/02/16 19:00 01/02/16 19:03 0.05 0.9 0.49 SBT_TAPE
XYZ DB INCR COMPLETED 01/02/16 19:00 01/02/16 19:03 0.05 0.9 0.49 SBT_TAPE
XYZ DB INCR COMPLETED 01/02/16 19:00 01/02/16 19:03 0.05 0.9 0.49 SBT_TAPE
XYZ DB INCR COMPLETED 01/13/16 04:37 01/13/16 05:02 0.42 64.36 48.62 SBT_TAPE

How should I differentiate incremental level 0 and incremental 1 backup type from the above sql.

Note : INPUT TYPE is DB INCR for both INCR 0 and INCR 1. How to differentiate incremental 0 and 1

Report message to a moderator

Re: SQL qery to find rman backup size generated per week using rman catalog database [message #648409 is a reply to message #648406] Tue, 23 February 2016 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

You have not this information in JOB view you have to get it from RC_BACKUP_DATAFILE_DETAILS.

Please read How to use [code] tags and make your code easier to read.

[Updated on: Tue, 23 February 2016 02:22]

Report message to a moderator

Re: SQL qery to find rman backup size generated per week using rman catalog database [message #648431 is a reply to message #648409] Tue, 23 February 2016 06:07 Go to previous messageGo to next message
SamuelJk
Messages: 25
Registered: August 2007
Junior Member
SELECT db_name,
input_type,
input_type_weight,
status,
To_char(start_time, 'mm/dd/yy hh24:mi') start_time,
To_char(end_time, 'mm/dd/yy hh24:mi') end_time,
Round(elapsed_seconds / 3600, 2) hrs,
Round(input_bytes / 1024 / 1024 / 1024, 2) SUM_BYTES_BACKED_IN_GB,
Round(output_bytes / 1024 / 1024 / 1024, 2) SUM_BACKUP_PIECES_IN_GB,
output_device_type
FROM rc_rman_backup_job_details
WHERE Trunc(start_time) BETWEEN '01-JAN-2016' AND '31-JAN-2016'
AND input_type != 'ARCHIVELOG'
ORDER BY db_name,
input_type,
start_time

Report message to a moderator

Re: SQL qery to find rman backup size generated per week using rman catalog database [message #648432 is a reply to message #648431] Tue, 23 February 2016 06:09 Go to previous messageGo to next message
SamuelJk
Messages: 25
Registered: August 2007
Junior Member
Sample output
DB_NAME INPUT_TYPE STATUS START_TIME END_TIME HRS SUM_BYTES_BACKED_IN_GB SUM_BACKUP_PIECES_IN_GB OUTPUT_DEVICE_TYPE
XYZ DB INCR COMPLETED 01/01/16 19:00 01/01/16 19:05 0.07 0.85 0.46 SBT_TAPE
XYZ DB INCR COMPLETED 01/02/16 19:00 01/02/16 19:03 0.05 0.9 0.49 SBT_TAPE
XYZ DB INCR COMPLETED 01/02/16 19:00 01/02/16 19:03 0.05 0.9 0.49 SBT_TAPE
XYZ DB INCR COMPLETED 01/02/16 19:00 01/02/16 19:03 0.05 0.9 0.49 SBT_TAPE
XYZ DB INCR COMPLETED 01/02/16 19:00 01/02/16 19:03 0.05 0.9 0.49 SBT_TAPE
XYZ DB INCR COMPLETED 01/13/16 04:37 01/13/16 05:02 0.42 64.36 48.62 SBT_TAPE

How should I differentiate incremental level 0 and incremental 1 backup type from the above sql.

Note : INPUT TYPE is DB INCR for both INCR 0 and INCR 1. How to differentiate incremental 0 and 1

Report message to a moderator

Re: SQL qery to find rman backup size generated per week using rman catalog database [message #648435 is a reply to message #648431] Tue, 23 February 2016 06:31 Go to previous messageGo to next message
SamuelJk
Messages: 25
Registered: August 2007
Junior Member
 
SELECT db_name, 
       input_type, 
       input_type_weight, 
       status, 
       To_char(start_time, 'mm/dd/yy hh24:mi')     start_time, 
       To_char(end_time, 'mm/dd/yy hh24:mi')       end_time, 
       Round(elapsed_seconds / 3600, 2)            hrs, 
       Round(input_bytes / 1024 / 1024 / 1024, 2)  SUM_BYTES_BACKED_IN_GB, 
       Round(output_bytes / 1024 / 1024 / 1024, 2) SUM_BACKUP_PIECES_IN_GB, 
       output_device_type 
FROM   rc_rman_backup_job_details 
WHERE  Trunc(start_time) BETWEEN '01-JAN-2016' AND '31-JAN-2016' 
       AND input_type != 'ARCHIVELOG' 
ORDER  BY db_name, 
          input_type, 
          start_time

Report message to a moderator

Re: SQL qery to find rman backup size generated per week using rman catalog database [message #648436 is a reply to message #648432] Tue, 23 February 2016 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 23 February 2016 09:21

You have not this information in JOB view you have to get it from RC_BACKUP_DATAFILE_DETAILS.

Please read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: SQL qery to find rman backup size generated per week using rman catalog database [message #648437 is a reply to message #648435] Tue, 23 February 2016 06:40 Go to previous messageGo to next message
SamuelJk
Messages: 25
Registered: August 2007
Junior Member
 

SQL used

SELECT db_name, 
       input_type, 
       input_type_weight, 
       status, 
       To_char(start_time, 'mm/dd/yy hh24:mi')     start_time, 
       To_char(end_time, 'mm/dd/yy hh24:mi')       end_time, 
       Round(elapsed_seconds / 3600, 2)            hrs, 
       Round(input_bytes / 1024 / 1024 / 1024, 2)  SUM_BYTES_BACKED_IN_GB, 
       Round(output_bytes / 1024 / 1024 / 1024, 2) SUM_BACKUP_PIECES_IN_GB, 
       output_device_type 
FROM   rc_rman_backup_job_details 
WHERE  Trunc(start_time) BETWEEN '01-JAN-2016' AND '31-JAN-2016' 
       AND input_type != 'ARCHIVELOG' 
ORDER  BY db_name, 
          input_type, 
          start_time  




Sample output

 DB_NAME INPUT_TYPE STATUS START_TIME END_TIME HRS SUM_BYTES_BACKED_IN_GB SUM_BACKUP_PIECES_IN_GB OUTPUT_DEVICE_TYPE
 XYZ DB INCR COMPLETED 01/01/16 19:00 01/01/16 19:05 0.07 0.85 0.46 SBT_TAPE
 XYZ DB INCR COMPLETED 01/02/16 19:00 01/02/16 19:03 0.05 0.9 0.49 SBT_TAPE
 XYZ DB INCR COMPLETED 01/02/16 19:00 01/02/16 19:03 0.05 0.9 0.49 SBT_TAPE
 XYZ DB INCR COMPLETED 01/02/16 19:00 01/02/16 19:03 0.05 0.9 0.49 SBT_TAPE
 XYZ DB INCR COMPLETED 01/02/16 19:00 01/02/16 19:03 0.05 0.9 0.49 SBT_TAPE
 XYZ DB INCR COMPLETED 01/13/16 04:37 01/13/16 05:02 0.42 64.36 48.62 SBT_TAPE

 How should I differentiate incremental level 0 and incremental 1 backup type from the above sql.

 Note : INPUT TYPE is DB INCR for both INCR 0 and INCR 1. How to differentiate incremental 0 and 1



Report message to a moderator

Re: SQL qery to find rman backup size generated per week using rman catalog database [message #648446 is a reply to message #648437] Tue, 23 February 2016 07:52 Go to previous message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 23 February 2016 13:31
Michel Cadot wrote on Tue, 23 February 2016 09:21

You have not this information in JOB view you have to get it from RC_BACKUP_DATAFILE_DETAILS.

Please read How to use [code] tags and make your code easier to read.



And when you post the result of a query align the columns.

[Updated on: Tue, 23 February 2016 07:52]

Report message to a moderator

Previous Topic: Oracle folders to backup
Next Topic: Cross Platform migration, ORA-27048
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Dec 22 02:34:56 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.