FAQ

On Mon, Feb 9, 2009 at 8:47 PM, wrote:

Well, I'm sad to hear no SQL*Plus in 11g, (so you probably should get
better with dbms_metadata).
*C:\Documents and Settings\nlitchfield>sqlplus /nolog*
*SQL*Plus: Release 11.1.0.6.0 - Production on Mon Feb 9 21:11:14 2009*
*Copyright (c) 1982, 2007, Oracle. All rights reserved.*
*SQL>*

**
*I suspect you may possibly mean no sqlplusw.exe, but that isn't a great
loss.
*

Search Discussions

  • Dennis Williams at Feb 9, 2009 at 9:15 pm
    Jared,

    dbms_metadata is a very good suggestion that I hadn't considered. Since this
    small task is nearly complete, I won't use dbms_metadata this time, but will
    definitely keep it in mind in the future.

    Dennis
  • Joel.Patterson_at_crowley.com at Feb 10, 2009 at 7:49 pm
    Well, I hope I can do everything I now can via SQL Developer.



    Sqlplusw.exe is what I am referring two, and I seem to have gotten used
    to it. Like a linesize of 2000. Having scripts call scripts and
    spool and do os commands etc.



    Joel Patterson
    Database Administrator
    904 727-2546

    From: Niall Litchfield
    Sent: Monday, February 09, 2009 4:13 PM
    To: Patterson, Joel
    Cc: JSweetser_at_icat.com; oracledba.williams_at_gmail.com;
    oracle-l@freelists.org
    Subject: Re: SQL*Plus Question



    On Mon, Feb 9, 2009 at 8:47 PM, wrote:

    Well, I'm sad to hear no SQL*Plus in 11g, (so you probably should get
    better with dbms_metadata).





    C:\Documents and Settings\nlitchfield>sqlplus /nolog

    SQL*Plus: Release 11.1.0.6.0 - Production on Mon Feb 9 21:11:14 2009

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    SQL>



    I suspect you may possibly mean no sqlplusw.exe, but that isn't a great
    loss.
  • Stephane Faroult at Feb 10, 2009 at 8:02 pm
    May I suggest taking a look here?
    http://www.oracle.com/technology/tech/oci/instantclient/index.html

    I find it easier to press return than look for the right button to click.

    HTH

    S Faroult

    Joel.Patterson_at_crowley.com wrote:
    Well, I hope I can do everything I now can via SQL Developer.



    Sqlplusw.exe is what I am referring two, and I seem to have gotten
    used to it. Like a linesize of 2000. Having scripts call scripts
    and spool and do os commands etc.



    Joel Patterson
    Database Administrator
    904 727-2546

    ------------------------------------------------------------------------
    --
    http://www.freelists.org/webpage/oracle-l
  • Anonymous at Feb 10, 2009 at 8:02 pm
    All,



    I'm sure I saw this answer on the list before but just can't remember
    it.



    Does the Rman catalog store the tape volume name that it is writing a
    backup to?



    Oracle 10.2 and Tivoli Storage Manager.

    I am trying to restore from a prior backup. The Rman catalog has the
    backup cataloged. But the Rman run fails and says:



    RMAN-06023: no backup or copy of datafile 22 found to restore

    RMAN-06023: no backup or copy of datafile 2 found to restore

    RMAN-06023: no backup or copy of datafile 1 found to restore



    I was able to restore from 2 days sooner (1/29 worked; 1/27 does not).
    I'm thinking that the tape may be offline or off site.

    Does this make sense?

    Thanks

    Tom



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    Joel.Patterson_at_crowley.com
    Sent: Tuesday, February 10, 2009 2:49 PM
    To: niall.litchfield_at_gmail.com
    Cc: JSweetser_at_icat.com; oracledba.williams_at_gmail.com;
    oracle-l@freelists.org
    Subject: RE: SQL*Plus Question



    Well, I hope I can do everything I now can via SQL Developer.



    Sqlplusw.exe is what I am referring two, and I seem to have gotten used
    to it. Like a linesize of 2000. Having scripts call scripts and
    spool and do os commands etc.



    Joel Patterson
    Database Administrator
    904 727-2546

    From: Niall Litchfield
    Sent: Monday, February 09, 2009 4:13 PM
    To: Patterson, Joel
    Cc: JSweetser_at_icat.com; oracledba.williams_at_gmail.com;
    oracle-l@freelists.org
    Subject: Re: SQL*Plus Question



    On Mon, Feb 9, 2009 at 8:47 PM, wrote:

    Well, I'm sad to hear no SQL*Plus in 11g, (so you probably should get
    better with dbms_metadata).





    C:\Documents and Settings\nlitchfield>sqlplus /nolog

    SQL*Plus: Release 11.1.0.6.0 - Production on Mon Feb 9 21:11:14 2009

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    SQL>



    I suspect you may possibly mean no sqlplusw.exe, but that isn't a great
    loss.
  • D'Hooge Freek at Feb 10, 2009 at 8:18 pm
    Thomas,

    There is indeed a media column, that can hold the tape name(s) on which the backup piece is stored.
    But I'm not sure that every rman media layer is capable of passing this information to rman.
    Following query can be used in 10gR2 to get an overview of the backups and the media used.
    It uses the session_ke and session_stamp from v$rman_backup_job_details to group the different backup sets that belong to the same backup job together

    set linesize 200

    column bjob format a15
    column recid format 99999
    column start_time format a20
    column handle format a55
    column mb format 9G999G999D99
    column media format a15

    break on bjob skip 1 on recid on backup_type on start_time

    compute sum of mb on bjob

    with backup_set_details
    as
    ( select set_count, set_stamp, session_recid, session_stamp
    from v$backup_set_details
    group by set_count, set_stamp, set_count, session_recid, session_stamp
    )
    select rbjd.session_key ||',' || rbjd.session_stamp bjob, bs.recid recid, bs.backup_type,

    to_char(bs.start_time, 'DD/MM/YYYY HH24:MI:SS') start_time,
    bp.piece#, bp.bytes/1024/1024 mb, bp.compressed, bp.handle, bp.media

    from v$backup_set bs, v$backup_piece bp, backup_set_details bsd, v$rman_backup_job_details rbjd
    where bs.set_stamp = bp.set_stamp

    and bs.set_count = bp.set_count
    and bs.set_stamp = bsd.set_stamp
    and bs.set_count = bsd.set_count
    and bsd.session_recid = rbjd.session_recid
    and bsd.session_stamp = rbjd.session_stamp
    and bp.status != 'D'

    order by bs.start_time, bs.recid, bp.piece#;

    regards,

    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge_at_uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer

    From: oracle-l-bounce_at_freelists.org On Behalf Of Mercadante, Thomas F (LABOR)
    Sent: dinsdag 10 februari 2009 21:02
    To: oracle-l@freelists.org
    Subject: Does the Rman Catalog hold tape volume names?

    All,

    I'm sure I saw this answer on the list before but just can't remember it.

    Does the Rman catalog store the tape volume name that it is writing a backup to?

    Oracle 10.2 and Tivoli Storage Manager.

    I am trying to restore from a prior backup. The Rman catalog has the backup cataloged. But the Rman run fails and says:

    RMAN-06023: no backup or copy of datafile 22 found to restore
    RMAN-06023: no backup or copy of datafile 2 found to restore
    RMAN-06023: no backup or copy of datafile 1 found to restore

    I was able to restore from 2 days sooner (1/29 worked; 1/27 does not). I'm thinking that the tape may be offline or off site.

    Does this make sense?

    Thanks

    Tom
  • Anonymous at Feb 10, 2009 at 8:27 pm
    Freek,

    Thank You! I'll let you know if this works. My SA is away from his
    desk right now. When he gets back we'll take a look.

    Thanks again Stranger.

    Tom

    -----Original Message-----
    From: D'Hooge Freek
    Sent: Tuesday, February 10, 2009 3:19 PM
    To: Mercadante, Thomas F (LABOR); oracle-l@freelists.org
    Subject: RE: Does the Rman Catalog hold tape volume names?

    Thomas,

    There is indeed a media column, that can hold the tape name(s) on which
    the backup piece is stored.
    But I'm not sure that every rman media layer is capable of passing this
    information to rman.
    Following query can be used in 10gR2 to get an overview of the backups
    and the media used.
    It uses the session_ke and session_stamp from v$rman_backup_job_details
    to group the different backup sets that belong to the same backup job
    together

    set linesize 200

    column bjob format a15
    column recid format 99999
    column start_time format a20
    column handle format a55
    column mb format 9G999G999D99
    column media format a15

    break on bjob skip 1 on recid on backup_type on start_time

    compute sum of mb on bjob

    with backup_set_details
    as
    ( select set_count, set_stamp, session_recid, session_stamp
    from v$backup_set_details
    group by set_count, set_stamp, set_count, session_recid, session_stamp
    )
    select rbjd.session_key ||',' || rbjd.session_stamp bjob, bs.recid
    recid, bs.backup_type,

    to_char(bs.start_time, 'DD/MM/YYYY HH24:MI:SS') start_time,
    bp.piece#, bp.bytes/1024/1024 mb, bp.compressed, bp.handle,

    bp.media
    from v$backup_set bs, v$backup_piece bp, backup_set_details bsd,
    v$rman_backup_job_details rbjd
    where bs.set_stamp = bp.set_stamp

    and bs.set_count = bp.set_count
    and bs.set_stamp = bsd.set_stamp
    and bs.set_count = bsd.set_count
    and bsd.session_recid = rbjd.session_recid
    and bsd.session_stamp = rbjd.session_stamp
    and bp.status != 'D'

    order by bs.start_time, bs.recid, bp.piece#;

    regards,

    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge_at_uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Mercadante, Thomas F
    (LABOR)

    Sent: dinsdag 10 februari 2009 21:02
    To: oracle-l@freelists.org
    Subject: Does the Rman Catalog hold tape volume names?

    All,

    I'm sure I saw this answer on the list before but just can't remember
    it.

    Does the Rman catalog store the tape volume name that it is writing a
    backup to?

    Oracle 10.2 and Tivoli Storage Manager.

    I am trying to restore from a prior backup. The Rman catalog has the
    backup cataloged. But the Rman run fails and says:

    RMAN-06023: no backup or copy of datafile 22 found to restore
    RMAN-06023: no backup or copy of datafile 2 found to restore
    RMAN-06023: no backup or copy of datafile 1 found to restore

    I was able to restore from 2 days sooner (1/29 worked; 1/27 does not).
    I'm thinking that the tape may be offline or off site.

    Does this make sense?

    Thanks

    Tom

    --
    http://www.freelists.org/webpage/oracle-l
  • Allen, Brandon at Feb 10, 2009 at 9:01 pm
    Just beware - the information from the media column will be useless if your backup is going to a disk staging area before it goes to tape, and it also won't tell you if your backup spanned multiple tapes, or if it was migrated later to a different tape from the original by your media management software. It's really just there for looks - it can't be relied on.

    What is the retention on your backups? Maybe it's only 14 days and 1/29 is still within that policy, but 1/27 is not?

    If you were on Netbackup, I'd recommend using the bplist and/or bpclimagelist commands to search the Netbackup catalog directly for your backups, but I don't know what the equivalent is for TSM.

    Good luck,
    Brandon

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
  • Jared Still at Feb 10, 2009 at 11:09 pm

    On Tue, Feb 10, 2009 at 1:01 PM, Allen, Brandon wrote:

    Just beware - the information from the media column will be useless if your
    backup is going to a disk staging area before it goes to tape, and it also
    won't tell you if your backup spanned multiple tapes, or if it was migrated
    later to a different tape from the original by your media management
    software. It's really just there for looks - it can't be relied on.
    I'm not familiar with Tivoli, but have a little experience with NetBackup.

    If the tapes are vaulted, (copied to another tape that is taken offsite)
    in NetBackup, then RMAN no longer has the correct tape label.

    Retrieving a tape from off-site storage requires using a netbackup utility
    to query the tape catalog based on the date to which we want to restore
    a database, and then request those tapes back from storage.

    RMAN has the correct tape label up until the time the tape is vaulted.

    After that point, RMAN is useless for retrieving tapes from storage, as the
    NBU MML does not update RMAN with the new labels.

    Once the tapes are back in place, no problem, as both the RMAN and NBU
    catalogs can correlate based on backup piece name.

    And if you've obsoleted those old backups and cleaned them out of the
    RMAN catalog: well, I don't know how to work around that scenario.

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
  • Anonymous at Feb 10, 2009 at 9:05 pm
    Brandon,

    Thanks for the info. I'm aware of disk and tape backups. I'm talking
    about tape backups.

    Still waiting for my SA to verify that the numbers reported in the media
    column are actually tape numbers.

    Thanks

    Tom

    -----Original Message-----
    From: Allen, Brandon
    Sent: Tuesday, February 10, 2009 4:02 PM
    To: Mercadante, Thomas F (LABOR); D'Hooge Freek; oracle-l@freelists.org
    Subject: RE: Does the Rman Catalog hold tape volume names?

    Just beware - the information from the media column will be useless if
    your backup is going to a disk staging area before it goes to tape, and
    it also won't tell you if your backup spanned multiple tapes, or if it
    was migrated later to a different tape from the original by your media
    management software. It's really just there for looks - it can't be
    relied on.

    What is the retention on your backups? Maybe it's only 14 days and 1/29
    is still within that policy, but 1/27 is not?

    If you were on Netbackup, I'd recommend using the bplist and/or
    bpclimagelist commands to search the Netbackup catalog directly for your
    backups, but I don't know what the equivalent is for TSM.

    Good luck,
    Brandon

    Privileged/Confidential Information may be contained in this message or
    attachments hereto. Please advise immediately if you or your employer do
    not consent to Internet email for messages of this kind. Opinions,
    conclusions and other information in this message that do not relate to
    the official business of this company shall be understood as neither
    given nor endorsed by it.

    --
    http://www.freelists.org/webpage/oracle-l
  • Allen, Brandon at Feb 10, 2009 at 9:17 pm
    I'm not sure if we're on the same page - I know you're talking about "tape" backups, but in real life, what you might think of as a "tape" backup could very likely go to a disk staging unit used by your media management software (TSM in your case, Netbackup in mine). As far as Oracle/RMAN is concerned this is a "tape" backup through the sbt_tape device type, but really it's sitting on disk until your media management software gets around to moving it off to tape (which may even be never). This is a very common practice to improve the performance of backups & restores due to the limited speed & capacity of tapes. If this is the case in your environment, then you won't get a tape label in your RMAN media column - you'll just get some mostly useless identifier for the disk path that your media management software used for staging the backup.

    Regards,
    Brandon

    -----Original Message-----
    From: Mercadante, Thomas F (LABOR)

    Thanks for the info. I'm aware of disk and tape backups. I'm talking
    about tape backups.

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
    --
    http://www.freelists.org/webpage/oracle-l
  • Kevin Lidh at Feb 10, 2009 at 9:33 pm
    When I worked at an organization that backed up directly to tape, we ran
    into issues where a backup piece would span more than one tape (maybe it
    started towards the end of one). In that case, the catalog was only
    aware of the first tape ID. Our SA had to run some NetBackup script to
    get the whole tape list and ensure it was available.
    On Tue, 2009-02-10 at 16:05 -0500, Mercadante, Thomas F (LABOR) wrote:
    Brandon,

    Thanks for the info. I'm aware of disk and tape backups. I'm talking
    about tape backups.

    Still waiting for my SA to verify that the numbers reported in the media
    column are actually tape numbers.

    Thanks

    Tom
    --
    http://www.freelists.org/webpage/oracle-l
  • Kevin Lidh at Feb 11, 2009 at 3:37 am
    Kevin,

    When I used the script in combination with legato networker, I noticed that the media column had sometimes more then 1 media id in it (separated by a comma).

    I have quickly checked with a netbackup configuration, but I don't have a case in which a backup piece is split over 2 tapes.
    Do you remember which version of netbackup was used?

    regards,

    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    e-mail: freek.dhooge_at_uptime.be
    tel. +32 (0)3 451 23 82
    http://www.uptime.be
    disclaimer

    From: Kevin Lidh [kevin.lidh_at_gmail.com]
    Sent: 10 February 2009 22:33
    To: Thomas.Mercadante_at_labor.state.ny.us
    Cc: Allen, Brandon; D'Hooge Freek; oracle-l@freelists.org
    Subject: RE: Does the Rman Catalog hold tape volume names?

    When I worked at an organization that backed up directly to tape, we ran
    into issues where a backup piece would span more than one tape (maybe it
    started towards the end of one). In that case, the catalog was only
    aware of the first tape ID. Our SA had to run some NetBackup script to
    get the whole tape list and ensure it was available.
    On Tue, 2009-02-10 at 16:05 -0500, Mercadante, Thomas F (LABOR) wrote:
    Brandon,

    Thanks for the info. I'm aware of disk and tape backups. I'm talking
    about tape backups.

    Still waiting for my SA to verify that the numbers reported in the media
    column are actually tape numbers.

    Thanks

    Tom
    --
    http://www.freelists.org/webpage/oracle-l
  • David Barbour at Feb 11, 2009 at 3:54 pm
    Currently, I'm using RMAN with AX/TSM. I can categorically state the number
    in the media column IS NOT the tape number. I've actually run through this
    with Tivoli Support.

    Not only is there the issue of disk-to-tape with respect to TSM, there's
    also the issue of Storage Pool Migration and/or Reclamation. The tape your
    backup is initially sent to may not be (probably won't be) the tape from
    which it is retrieved.

    To find the backup, you need to query the TSM database.

    You say RMAN has the backup cataloged. Get the handle from your recovery
    catalog table bp. If you have rights in TSM you can run this, otherwise,
    it's going to be up to your SA:

    select volume_name from contents where file_name = ''
    On Tue, Feb 10, 2009 at 10:37 PM, Kevin Lidh wrote:

    Kevin,

    When I used the script in combination with legato networker, I noticed that
    the media column had sometimes more then 1 media id in it (separated by a
    comma).

    I have quickly checked with a netbackup configuration, but I don't have a
    case in which a backup piece is split over 2 tapes.
    Do you remember which version of netbackup was used?

    regards,

    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    e-mail: freek.dhooge_at_uptime.be
    tel. +32 (0)3 451 23 82
    http://www.uptime.be
    disclaimer
    ________________________________________
    From: Kevin Lidh [kevin.lidh_at_gmail.com]
    Sent: 10 February 2009 22:33
    To: Thomas.Mercadante_at_labor.state.ny.us
    Cc: Allen, Brandon; D'Hooge Freek; oracle-l@freelists.org
    Subject: RE: Does the Rman Catalog hold tape volume names?

    When I worked at an organization that backed up directly to tape, we ran
    into issues where a backup piece would span more than one tape (maybe it
    started towards the end of one). In that case, the catalog was only
    aware of the first tape ID. Our SA had to run some NetBackup script to
    get the whole tape list and ensure it was available.
    On Tue, 2009-02-10 at 16:05 -0500, Mercadante, Thomas F (LABOR) wrote:
    Brandon,

    Thanks for the info. I'm aware of disk and tape backups. I'm talking
    about tape backups.

    Still waiting for my SA to verify that the numbers reported in the media
    column are actually tape numbers.

    Thanks

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Anonymous at Feb 11, 2009 at 4:40 pm
    David,



    That is what we've discovered here also. The number stored in the
    "Media" column is not related to any tapes in the tape system.



    Thanks for the reply.

    Tom



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of David Barbour
    Sent: Wednesday, February 11, 2009 10:54 AM
    To: kevin.lidh_at_gmail.com
    Cc: Mercadante, Thomas F (LABOR); Allen, Brandon; oracle-l@freelists.org
    Subject: Re: Does the Rman Catalog hold tape volume names?



    Currently, I'm using RMAN with AX/TSM. I can categorically state the
    number in the media column IS NOT the tape number. I've actually run
    through this with Tivoli Support.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 9, '09 at 9:12p
activeFeb 11, '09 at 4:40p
posts15
users10
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase