RE: Getting all tapes used out of RMAN

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Tue, 25 Jul 2006 11:10:29 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E3602004FF7@CWYMSX04.Corp.Acxiom.net>

The thing is, RC_BACKUP_PIECE (and V$BACKUP_PIECE) only list the first tape used for the given piece, in the media column. Frequently I have backup pieces that span multiple tapes, but I only happen to notice that when running queries against Legato. Apparently, RMAN works with Legato to know when to continue onto multiple tapes, but doesn't record that anywhere (at least from what I can find).

For example, here's a list of tapes used for Sunday's full backup out of RMAN:

SELECT SUBSTR(CONCAT_ORDER_AGGREGATE_FNC(' ' || (media)),2) FROM (SELECT DISTINCT rbp.media

          FROM rc_backup_piece rbp, rc_database rd
         WHERE rbp.tag LIKE 'TAG20060723T%'
           AND rd.name = '<db name>'
           AND rd.db_key = rbp.db_key);

WF0033 WF0050 WF0067 WF0091 WF0133 WF0189

Yet if I grab a list of pieces used in the same backup and grep that list against all pieces on existing tapes in the library, I get a longer list:

Get a list of pieces for the backup SET LINESIZE 10000 TRIMSPOOL on TRIMOUT on COLUMN handle_string FORMAT A1000 WRAP
Shrink down the concatenated list to avoid any wrapping issues with UNIX SELECT 'PIECE_LIST="' || SUBSTR(CONCAT_ORDER_AGGREGATE_FNC('|' || handle),2) || '"' handle_string FROM (SELECT REPLACE(REPLACE(REPLACE(rbp.handle,'full_lvl0_bkup_'),'d<db name>_'),'_p1') handle FROM rc_backup_piece rbp, rc_database rd WHERE ( rbp.tag LIKE 'TAG20060723T%' AND rbp.db_key = rd.db_key) AND rd.name = '<db name>');

# Set PIECE_LIST to be list of RMAN backup pieces at UNIX level, as output
# from SQL query.

% PIECE_LIST="<output from query above>"

% mminfo -a | grep "full_lvl0_bkup" | grep -E "($PIECE_LIST)" | \

cut -d' ' -f1 | sort -u | xargs

WF0010 WF0018 WF0033 WF0050 WF0067 WF0091 WF0133 WF0189 WF0284 WF0303

Comparing the 2 lists, tapes WF0010, WF0018, WF0284, and WF0303 are not listed from the RMAN query. A check back using the "mminfo" command shows that some backup pieces extended onto those tapes.

Is it really not stored anywhere in RMAN that piece blah, blah, blah was written to WF0033 and continued onto WF0010?

Dave

Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762
<mailto:dherri_at_acxiom.com>

"When I come home from work and see those little noses pressed against the windowpane, then I know I am a success" - Paul Faulkner

From: Fuad Arshad [mailto:fuadar_at_yahoo.com] Sent: Tuesday, July 25, 2006 9:05 AM
To: Herring Dave - dherri; oracle-l
Subject: Re: Getting all tapes used out of RMAN

hereis a script thati hope might help
select d.name, p.tag, p.media,

       s.incremental_level "LEVEL",
       to_char(s.start_time, 'DD-MON-YY HH24:MI') start_time,
       s.elapsed_seconds/60 "MINUTES"

from RC_DATABASE d, RC_BACKUP_PIECE p, RC_BACKUP_SET s where d.name = <dname>

    and p.backup_type     = 'D' -- D=Database, L=Log
  and d.db_key = p.db_key
  and s.db_key = p.db_key
  and p.bs_key = s.bs_key
*************************************************************************

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank you.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 25 2006 - 11:10:29 CDT