rman hanging, temp tablespace issues
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: rman hanging, temp tablespace issues

  1. #1
    Join Date
    Oct 2008
    Posts
    69

    rman hanging, temp tablespace issues

    Hi There, we appear to be having trouble with one of our rman scripts for backing up archive logs , but only on one server.
    This script runs fine against other 10g databases on Solaris 10 but hangs for an hour or so near the end on one of our systems.

    Here is script
    Code:
    CMD_STR="
    ORACLE_HOME=$ORACLE_HOME
    export ORACLE_HOME
    ORACLE_SID=$ORACLE_SID
    export ORACLE_SID
    $RMAN target $TARGET_CONNECT_STR catalog $RMAN_CONNECT_STRING msglog $RMAN_LOG_F
    ILE append << EOF
    RUN {
    # backup all archive logs
    ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
    ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
    ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE';
    ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE';
    ALLOCATE CHANNEL ch04 TYPE 'SBT_TAPE';
    sql 'alter system archive log current';
    BACKUP
       FORMAT 'al_%s_%p_%t'
       ARCHIVELOG ALL DELETE INPUT;
    RELEASE CHANNEL ch00;
    RELEASE CHANNEL ch01;
    RELEASE CHANNEL ch02;
    RELEASE CHANNEL ch03;
    RELEASE CHANNEL ch04;
    #
    #
    ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
    BACKUP
        # recommended format
        FORMAT 'cntrl_%s_%p_%t'
        CURRENT CONTROLFILE;
    RELEASE CHANNEL ch00;
    }
    EOF
    "
    # Initiate the command string
    
    if [ "$CUSER" = "root" ]
    then
        su - $ORACLE_USER -c "$CMD_STR" >> $RMAN_LOG_FILE
        RSTAT=$?
    else
        /usr/bin/sh -c "$CMD_STR" >> $RMAN_LOG_FILE
        RSTAT=$?
    fi
    
    # ---------------------------------------------------------------------------
    # Log the completion of this script.
    # ---------------------------------------------------------------------------
    
    if [ "$RSTAT" = "0" ]
    then
        LOGMSG="ended successfully"
    else
        LOGMSG="ended in error"
    fi
    
    echo >> $RMAN_LOG_FILE
    echo Script $0 >> $RMAN_LOG_FILE
    echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
    echo >> $RMAN_LOG_FILE
    
    exit $RSTAT

    The job is hanging for an hour or more at this point
    Code:
    ...
    piece handle=al_26714_1_698324476 tag=TAG20090923T110114 comment=API Version 2.0,MMS Version 5.0.0.0
    channel ch00: backup set complete, elapsed time: 00:01:55
    channel ch00: deleting archive log(s)
    archive log filename=/ihs/dpp/live/live06/IHSLIVEarch1_37639_547808991.arc recid=50882 stamp=698279756
    archive log filename=/ihs/dpp/live/live06/IHSLIVEarch1_37640_547808991.arc recid=50884 stamp=698283753
    archive log filename=/ihs/dpp/live/live06/IHSLIVEarch1_37641_547808991.arc recid=50886 stamp=698283913
    archive log filename=/ihs/dpp/live/live06/IHSLIVEarch1_37642_547808991.arc recid=50888 stamp=698283985
    Finished backup at 23-SEP-09
    
    Starting Control File and SPFILE Autobackup at 23-SEP-09
    piece handle=c-2544602172-20090923-03 comment=API Version 2.0,MMS Version 5.0.0.0
    Finished Control File and SPFILE Autobackup at 23-SEP-09
    So the next part should be to release the channels which it just sits at for ages. If i look on Netbackup (which these jobs are run through) it says it is Connecting.

    On other systems job completes in couple of minutes but this one system it just sits there for an hour plus before completing. I have noticed that if i clear out the Temp tablespace then this rman job utilizes all available, i haven't noticed that with the other systems.
    I recreated and had 2gig with max set to 12GB. Within minutes of running this job rman was using all of the TEMP space.
    Surely that isn't normal? Especially as it is only an archive log rman job.

    The only difference between this and other comparable systems is the trouble one is 10.2.0.3.0 and others are 10.2.0.4.0.

    Anyone have any clues as to why the job stalls for so loing and uses so much TEMP tablespace?

    Cheers
    Phil

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    You could try to perform the following:

    Code:
    SQL> exec dbms_stats.gather_fixed_objects_stats;

  3. #3
    Join Date
    Oct 2008
    Posts
    69
    Quote Originally Posted by ebrian View Post
    You could try to perform the following:

    Code:
    SQL> exec dbms_stats.gather_fixed_objects_stats;
    Hi,
    I have had a look at this command. It seems recommended to do after upgrade to 10g once you have reached typical workload and only ever do once (unless significant change in circumstances (hardware, software, workload)

    This wasn't done after upgrade (some 9 months ago). We did do dbms_gather_database_stats though.

    Is it safe to run dbms_stats.gather_fixed_objects_stats so late on after the upgrade?

  4. #4
    Join Date
    Apr 2006
    Posts
    377
    Running gather_fixed_objects_stats again wouldn't necessarily hurt anything. In fact, with 10g it is recommended that you do analyze the fixed objects.

  5. #5
    Join Date
    Oct 2008
    Posts
    69
    Quote Originally Posted by ebrian View Post
    Running gather_fixed_objects_stats again wouldn't necessarily hurt anything. In fact, with 10g it is recommended that you do analyze the fixed objects.
    First off, thanks so much for recommending this course of action. Script now runs in 7 minutes compared to 90 mins. I am no longer getting temp tablespace issues either.

    Can you explain to me what this has done to resolve the problems i was having?
    Cheers
    Phil

  6. #6
    Join Date
    Apr 2006
    Posts
    377
    You are welcome. Glad it helped !

    Running the procedure essentially provided Oracle with better statistics on the underlying X$ tables. The new statistics enabled Oracle to produce more efficient execution plans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width