Re: Odd RMAN Catalog Query Result for Full backups
- From: Stefan Koehler <contact@xxxxxxxx>
- To: mcolmenares@xxxxxxxxxxxxxxxxxxxxxx, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 3 Jul 2015 23:20:03 +0200 (CEST)
Hi Marcos,
Anyone had this happen to them or see anythign wrong with my query or
something else?
Are you using control file autobackups ("CONFIGURE CONTROLFILE AUTOBACKUP ON")?
These are recorded as "FULL" backups (and done automatically by
archive log backups for example), but you can differ these backups with help of
column RC_BACKUP_SET.CONTROLFILE_INCLUDED for example. Here is a
sample query that i have written for some RMAN catalog reporting purpose.
----------------
select RCD.NAME,
decode(RCBS.CONTROLFILE_INCLUDED,'NONE',decode(RCBS.BACKUP_TYPE,'D','FULL','I','INCR','L','ARCH'),'AUTO')
as TYPE,
RCBS.CONTROLFILE_INCLUDED, RCBS.START_TIME, RCBS.COMPLETION_TIME,
ceil(RCBS.ELAPSED_SECONDS/60) as MINUTES,
RCRS.STATUS as RMAN_JOB_STATE, RCBP.DEVICE_TYPE as DEVICE,
decode(RCBP.STATUS,'A','Available','U','Unavailable','D','Deleted','X','Expired')
as BACKUP_STATE,
ceil(RCBP.BYTES/1024/1024) as MB, RCBP.TAG, RCBP.IS_RECOVERY_DEST_FILE
as FRA,
decode(RCBS.KEEP,'YES',RCBS.KEEP_UNTIL || ' - ' ||
RCBS.KEEP_OPTIONS,NULL) as CUSTOM_RETENTION
from RC_DATABASE RCD,
RC_BACKUP_SET RCBS,
RC_BACKUP_PIECE RCBP,
RC_RMAN_STATUS RCRS
where RCD.DB_KEY = RCBS.DB_KEY
and RCBS.DB_KEY = RCBP.DB_KEY
and RCBS.BS_KEY = RCBP.BS_KEY
and RCBP.DB_KEY = RCRS.DB_KEY
and RCBP.RSR_KEY = RCRS.RSR_KEY
order by RCD.NAME, RCBS.START_TIME;
----------------
Hope this helps.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage:
http://www.soocs.de
Twitter: @OracleSK
"Marcos Colmenares H." <mcolmenares@xxxxxxxxxxxxxxxxxxxxxx> hat am 3. Juli
2015 um 20:20 geschrieben:
Good Day,
Im querying all the full backups done on a certain Database using the RMAN
Catalog DB. But i keep seeing .11G Backup sets from dates where there
shouldnt be any, .11g sets along with the date where the actual full took
place and full backups split into two sets.
[Imágenes integradas 1]
the query im using at the moment would be this one
SELECT
START_TIME,
to_char(OUTPUT_BYTES/1024/1024/1024,'99990.00') as SIZE_GB,
DECODE (STATUS, 'A', 'Available', 'U', 'Unavailable', 'D', 'Deleted', 'X',
'Expired') as STATUS,
DECODE (BACKUP_TYPE, 'D', 'FULL LVL 0', 'I', 'INCR LVL 1', 'L', 'ARCH RDO
LOG') as BACKUP_TYPE
from RC_BACKUP_SET_DETAILS where db_key = 1602437 --and db_id =3079508743
and trunc(start_TIME) between to_date('06-apr-2015','dd-mon-rrrr') and
to_date('03-jul-2015','dd-mon-rrrr')
and BACKUP_TYPE = 'D'
order by start_time
i'm lost to why this is happening, since we only have FULL backups during
the weekend. Anyone had this happen to them or see anythign wrong with my
query or something else?
Best Regards,
Marcos Colmenares H
--
//www.freelists.org/webpage/oracle-l
Other related posts:
