FAQ
How long does it take to do a backup spfile command to disk?
We had problems recently on a couple of servers where the RMAN backup was never finishing and indeed was timing out. Tracing just showed a controlfile enqueue (IIRC).
However the issue was corrected by identifying a couple of rogue processes which were consuming excessive cpu.

The simple test for us was rman target / followed by backup spfile;

HTH

John

Jhdba.wordpress.com

From: oracle-l-bounce_at_freelists.org On Behalf Of rama.ari_at_accenture.com
Sent: 05 February 2009 05:43
To: oracle-l@freelists.org
Subject: RMAN Restore Archivelog Files -- Very Slow

Thanks for reading this email & Thanks in Advance for your help.

We are facing performance issue when trying to restore archive log files using RMAN catalog.

Environment:
Oracle Database: 10g (10.2.0.3) RAC
Database Size: ~350 GB
Archive Log Files per day: ~30 - 45
Archive Log File Size: 200MB
NetBackup: 6.5
Backups: RMAN to VTL (Weekly Full, Daily Incremental)

We restored 350 GB of database using RMAN catalog and it took around 6 Hrs which is very good. Most of the datafile sizes are around 30GB and some of the files are couple of GB.

When we try to restore archive log (200MB) files using RMAN Catalog and it is taking approximately 15 Minutes for each file. If we bypass RMAN catalog by using backup information from controlfile, it takes approximately 2.5 Minutes to restore each archive log file. After working with backup team, we found out that approximately 12 Minutes time was spent for the hand shake between RMAN Catalog and Netbackup catalog.

Did anyone of you have seen similar situation and what might be the problem? Have you done any kind of database maintenance work for RMAN repository database to improve performance?

Thanks,
Rama

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.

Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.

If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email.
If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.

Wm Morrison Supermarkets PLC accepts no liability or responsibility for anything said in the email or its attachments and gives no warranty as to accuracy. It is the policy of Wm Morrison Supermarkets PLC not to enter into any contractual or other obligations by email.

Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility,
and it is the responsibility of recipients to carry out their own virus checks.

Search Discussions

  • Jared Still at Feb 5, 2009 at 5:32 pm
    You may want to search the Oracle-L archives at freelists.org for
    RMAN performance problems.

    This has appeared fairly frequently of late.

    One of the posts lists a MetaLink note that is actually a master list
    pointing to other notes, all relating to RMAN performance problems.

    BTW, the 'fix' for many RMAN performance problems:

    SQL 'ALTER SESSION SET OPTIMIZER_MODE=RULE'

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    On Wed, Feb 4, 2009 at 9:42 PM, wrote:

    Thanks for reading this email & Thanks in Advance for your help.



    We are facing performance issue when trying to restore archive log files
    using RMAN catalog.



    Environment:

    Oracle Database: 10g (10.2.0.3) RAC

    Database Size: ~350 GB

    Archive Log Files per day: ~30 – 45

    Archive Log File Size: 200MB

    NetBackup: 6.5

    Backups: RMAN to VTL (Weekly Full, Daily Incremental)



    We restored 350 GB of database using RMAN catalog and it took around 6 Hrs
    which is very good. Most of the datafile sizes are around 30GB and some of
    the files are couple of GB.



    When we try to restore archive log (200MB) files using RMAN Catalog and it
    is taking approximately 15 Minutes for each file. If we bypass RMAN catalog
    by using backup information from controlfile, it takes approximately 2.5
    Minutes to restore each archive log file. After working with backup team, we
    found out that approximately 12 Minutes time was spent for the hand shake
    between RMAN Catalog and Netbackup catalog.



    Did anyone of you have seen similar situation and what might be the
    problem? Have you done any kind of database maintenance work for RMAN
    repository database to improve performance?



    Thanks,

    *Rama*



    This message is for the designated recipient only and may contain
    privileged, proprietary, or otherwise private information. If you have
    received it in error, please notify the sender immediately and delete the
    original. Any other use of the email by you is prohibited.
    --
    http://www.freelists.org/webpage/oracle-l
  • Crisler, Jon at Feb 6, 2009 at 11:41 pm
    Another common fix for RMAN performance problems is to run execute
    dbms_stats.gather_fixed_object_stats



    This helps for RMAN performance problems, Export performance problems
    and some Data Pump performance problems. The cause is poor execution
    plans when using the CBO.



    I think if you are looking at OEM or AWR, you wind up seeing a lot of
    SQL statements with excessive cpu and high CBO cost that reference views
    v$hist_head and/or v$obj ( I am saying this from memory). Setting
    optimizer_mode=RULE addresses the same issue.



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jared Still
    Sent: Thursday, February 05, 2009 12:33 PM
    To: rama.ari_at_accenture.com
    Cc: oracle-l@freelists.org
    Subject: Re: RMAN Restore Archivelog Files -- Very Slow



    You may want to search the Oracle-L archives at freelists.org for
    RMAN performance problems.

    This has appeared fairly frequently of late.

    One of the posts lists a MetaLink note that is actually a master list
    pointing to other notes, all relating to RMAN performance problems.

    BTW, the 'fix' for many RMAN performance problems:

    SQL 'ALTER SESSION SET OPTIMIZER_MODE=RULE'

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    On Wed, Feb 4, 2009 at 9:42 PM, wrote:

    Thanks for reading this email & Thanks in Advance for your help.



    We are facing performance issue when trying to restore archive log files
    using RMAN catalog.



    Environment:

    Oracle Database: 10g (10.2.0.3) RAC

    Database Size: ~350 GB

    Archive Log Files per day: ~30 - 45

    Archive Log File Size: 200MB

    NetBackup: 6.5

    Backups: RMAN to VTL (Weekly Full, Daily Incremental)



    We restored 350 GB of database using RMAN catalog and it took around 6
    Hrs which is very good. Most of the datafile sizes are around 30GB and
    some of the files are couple of GB.



    When we try to restore archive log (200MB) files using RMAN Catalog and
    it is taking approximately 15 Minutes for each file. If we bypass RMAN
    catalog by using backup information from controlfile, it takes
    approximately 2.5 Minutes to restore each archive log file. After
    working with backup team, we found out that approximately 12 Minutes
    time was spent for the hand shake between RMAN Catalog and Netbackup
    catalog.



    Did anyone of you have seen similar situation and what might be the
    problem? Have you done any kind of database maintenance work for RMAN
    repository database to improve performance?



    Thanks,

    Rama



    This message is for the designated recipient only and may contain
    privileged, proprietary, or otherwise private information. If you have
    received it in error, please notify the sender immediately and delete
    the original. Any other use of the email by you is prohibited.
  • Rama.ari_at_accenture.com at Feb 16, 2009 at 4:49 am
    Jared

    We have tried your suggestions plus rest of the team suggestions but there is no luck yet.
    Symantec is recommending using (database format 'bk_%U_%t') for backups so that it guarantees uniqueness in generated file names with timestamp. Without this, there will be performance degradation as the NetBackup catalog grows. We still have to test it.

    Thanks,
    Rama

    From: Jared Still
    Sent: Thursday, February 05, 2009 12:33 PM
    To: Ari, Rama
    Cc: oracle-l@freelists.org
    Subject: Re: RMAN Restore Archivelog Files -- Very Slow

    You may want to search the Oracle-L archives at freelists.org<http://freelists.org> for
    RMAN performance problems.

    This has appeared fairly frequently of late.

    One of the posts lists a MetaLink note that is actually a master list
    pointing to other notes, all relating to RMAN performance problems.

    BTW, the 'fix' for many RMAN performance problems:

    SQL 'ALTER SESSION SET OPTIMIZER_MODE=RULE'

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    On Wed, Feb 4, 2009 at 9:42 PM, > wrote:

    Thanks for reading this email & Thanks in Advance for your help.

    We are facing performance issue when trying to restore archive log files using RMAN catalog.

    Environment:

    Oracle Database: 10g (10.2.0.3) RAC

    Database Size: ~350 GB

    Archive Log Files per day: ~30 - 45

    Archive Log File Size: 200MB

    NetBackup: 6.5

    Backups: RMAN to VTL (Weekly Full, Daily Incremental)

    We restored 350 GB of database using RMAN catalog and it took around 6 Hrs which is very good. Most of the datafile sizes are around 30GB and some of the files are couple of GB.

    When we try to restore archive log (200MB) files using RMAN Catalog and it is taking approximately 15 Minutes for each file. If we bypass RMAN catalog by using backup information from controlfile, it takes approximately 2.5 Minutes to restore each archive log file. After working with backup team, we found out that approximately 12 Minutes time was spent for the hand shake between RMAN Catalog and Netbackup catalog.

    Did anyone of you have seen similar situation and what might be the problem? Have you done any kind of database maintenance work for RMAN repository database to improve performance?

    Thanks,

    Rama

    This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.

    No virus found in this incoming message.
    Checked by AVG - www.avg.com
    Version: 8.0.233 / Virus Database: 270.10.18/1936 - Release Date: 02/05/09 11:34:00

    This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.
  • Stefan Knecht at Feb 7, 2009 at 10:04 am
    You can see where RMAN is spending its time by enabling debug mode as such:

    run {
    debug on;
    restore archivelog .....;
    }

    Have you ever performed maintenance jobs (crosscheck, delete expired, delete
    obsolete) ?

    Stefan

    Stefan P Knecht
    CEO & Founder
    s_at_10046.ch

    10046 Consulting GmbH
    Schwarzackerstrasse 29
    CH-8304 Wallisellen
    Switzerland

    Phone +41-(0)8400-10046
    Cell +41 (0) 79 571 36 27
    info_at_10046.ch
    http://www.10046.ch
    On Thu, Feb 5, 2009 at 6:42 AM, wrote:

    Thanks for reading this email & Thanks in Advance for your help.



    We are facing performance issue when trying to restore archive log files
    using RMAN catalog.



    Environment:

    Oracle Database: 10g (10.2.0.3) RAC

    Database Size: ~350 GB

    Archive Log Files per day: ~30 � 45

    Archive Log File Size: 200MB

    NetBackup: 6.5

    Backups: RMAN to VTL (Weekly Full, Daily Incremental)



    We restored 350 GB of database using RMAN catalog and it took around 6 Hrs
    which is very good. Most of the datafile sizes are around 30GB and some of
    the files are couple of GB.



    When we try to restore archive log (200MB) files using RMAN Catalog and it
    is taking approximately 15 Minutes for each file. If we bypass RMAN catalog
    by using backup information from controlfile, it takes approximately 2.5
    Minutes to restore each archive log file. After working with backup team, we
    found out that approximately 12 Minutes time was spent for the hand shake
    between RMAN Catalog and Netbackup catalog.



    Did anyone of you have seen similar situation and what might be the
    problem? Have you done any kind of database maintenance work for RMAN
    repository database to improve performance?



    Thanks,

    *Rama*



    This message is for the designated recipient only and may contain
    privileged, proprietary, or otherwise private information. If you have
    received it in error, please notify the sender immediately and delete the
    original. Any other use of the email by you is prohibited.
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 5, '09 at 2:37p
activeFeb 16, '09 at 4:49a
posts5
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase