RMAN and TDPO performance testing

 

Preamble

After a first post on how to configure Tivoli Data Protector for Oracle (TDPO) let’s test it. These tests have been done on Red Hat Enterprise Linux Server release 5.5 (Tikanga) with Oracle 11.2.0.2.0 with Tivoli Data Protector for Oracle 5.4.1.0. Disks used were local disk of the server as no NAS or SAN attachment…

Database is in NOARCHIVELOG mode and testing has been done in compressed and non-compressed mode, with different channel (8 and 4) and simulating tape asynchronous I/O with BACKUP_TAPE_IO_SLAVES parameter. Oracle parameter to keep tape streaming (continuously moving) by simulating asynchronous tape I/O.

Oracle definition:
If BACKUP_TAPE_IO_SLAVES=TRUE a single tape slave per channel is used. This speeds up the backup if the bottleneck is in writing to tape; it frees the channel process to continue processing RMAN input buffers whilst the tape slave waits for IO completion.

At the end BACKUP_TAPE_IO_SLAVES did improve only a bit backup performance, in clear not much differences…

Remark:
I choose a parallelism of 4, increasing parallelism to 8 did not change backup time. Optimal degree of parallelism seems to be a complex formula with free CPU available/number of datafiles/number of free tape drives.

Server has a Gigabit LAN card i.e. it can handle around 128MB/s.

Database size is 18380 MB and is more than 95% used. I used Swingbench to load it with figures as RMAN does not backup empty, never used, blocks.

To be able to see backup/restore start and end time with hours, minutes and seconds you can change environment variable NLS_DATE_FORMAT before launching RMAN (same value as the one you would set in SQL):

[oracle@server1 ~]$ export NLS_DATE_FORMAT="dd-mon-yyyy hh24:mi:ss"

While backup/restore is running you can generate Network and CPU usage with:

[root@server1 tmp]# sar -n DEV -u -o /tmp/sar_output 5 0

And then generate a file you can use in MRTG or Excel with (on my server configured and used LAN card is eth5):

[root@server1 tmp]# sar -n DEV -u -f /tmp/sar_output | grep -e eth5 -e all | grep -v Average > /tmp/sar_output_filtered

Backup with TDPO results

Script used for backup:

#!/bin/ksh
 
# Trapping errors -------------------------------------------------------------
 
trap 'STATUS=$?;set +x;echo;echo error $STATUS at line nb $LINENO executing :`sed -n "${LINENO}p" $0`;echo;exit $STATUS' ERR
 
# OS management ---------------------------------------------------------------
 
OSTYPE=$(uname -s)
case $OSTYPE in
"AIX" ) alias bdf="/usr/bin/df -Ik"
        alias ll="/usr/bin/ls -l" ;;
"SunOS") alias bdf="/usr/bin/df -k"
         alias ll="/usr/bin/ls -l"
         alias awk="/usr/xpg4/bin/awk"
         alias grep="/usr/xpg4/bin/grep" ;;
"Linux") alias bdf="/bin/df -k"
         alias grep=egrep
         alias echo="echo -e"
         alias ll="ls -l" ;;
esac
 
# Variables -------------------------------------------------------------------
JOB_NAME=$(basename $0)
TODAY=`date +'%Y%m%d_%H%M%S'`
LOG_FILE=/tmp/${JOB_NAME}_${TODAY}_$$.log
export NLS_DATE_FORMAT="dd-mon-yyyy hh24:mi:ss"
 
# Main Here -------------------------------------------------------------------
 
JOB_START=`date +%s`
echo "\nStarting at `date +'%Y%m%d_%H%M%S'`\n"
 
rman << EOF
connect target /
connect catalog rman/rman@emrep
 
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 9 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE DEFAULT DEVICE TYPE TO sbt;
CONFIGURE CHANNEL DEVICE TYPE sbt PARMS 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
CONFIGURE DEVICE TYPE sbt PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
#CONFIGURE DEVICE TYPE sbt PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
 
shutdown immediate;
 
startup mount;
 
run
{
allocate channel sbt1 type sbt parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel sbt2 type sbt parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel sbt3 type sbt parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel sbt4 type sbt parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
 
backup database include current controlfile tag '${TODAY}';
 
release channel sbt1;
release channel sbt2;
release channel sbt3;
release channel sbt4;
}
 
alter database open;
 
EOF
 
JOB_END=`date +%s`
echo "\nEnding at `date +'%Y%m%d_%H%M%S'`"
 
echo "\nCompleted in $((($JOB_END - $JOB_START)/60)) minutes"
 
echo "\nNormal Exit"

Remark:
This backup command include current control file as well as your spfile. The compression is determined by device configuration in global preferences and hence inherited.

Backup results files (non compressed):

RMAN> list backup;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7874    Full    389.50M    SBT_TAPE    00:00:20     15-JUL-11
        BP Key: 7879   Status: AVAILABLE  Compressed: NO  Tag: 20110715_140842
        Handle: 28mhgl8q_1_1   Media: 7
  List of Datafiles in backup set 7874
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 23090331   15-JUL-11 /oracle/sys/test/sysaux01.dbf
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7875    Full    10.75M     SBT_TAPE    00:00:19     15-JUL-11
        BP Key: 7880   Status: AVAILABLE  Compressed: NO  Tag: 20110715_140842
        Handle: 29mhgl8u_1_1   Media: 7
  Control File Included: Ckp SCN: 23090331     Ckp time: 15-JUL-11
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7876    Full    256.00K    SBT_TAPE    00:00:02     15-JUL-11
        BP Key: 7881   Status: AVAILABLE  Compressed: NO  Tag: 20110715_140842
        Handle: 2amhgl9g_1_1   Media: 7
  SPFILE Included: Modification time: 15-JUL-11
  SPFILE db_unique_name: TEST
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7877    Full    7.54G      SBT_TAPE    00:04:10     15-JUL-11
        BP Key: 7882   Status: AVAILABLE  Compressed: NO  Tag: 20110715_140842
        Handle: 27mhgl8o_1_1   Media: 4
  List of Datafiles in backup set 7877
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 23090331   15-JUL-11 /oracle/sys/test/system01.dbf
  5       Full 23090331   15-JUL-11 /oracle/data01/test/users02.dbf
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7878    Full    9.09G      SBT_TAPE    00:04:20     15-JUL-11
        BP Key: 7883   Status: AVAILABLE  Compressed: NO  Tag: 20110715_140842
        Handle: 26mhgl8o_1_1   Media: 21
  List of Datafiles in backup set 7878
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  3       Full 23090331   15-JUL-11 /oracle/rbs/test/undotbs01.dbf
  4       Full 23090331   15-JUL-11 /oracle/data01/test/users01.dbf

And compressed version:

RMAN> list backup;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7916    Full    63.25M     SBT_TAPE    00:00:15     15-JUL-11
        BP Key: 7923   Status: AVAILABLE  Compressed: YES  Tag: 20110715_142850
        Handle: 2dmhgmek_1_1   Media: 10
  List of Datafiles in backup set 7916
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 23093114   15-JUL-11 /oracle/sys/test/sysaux01.dbf
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7917    Full    256.00K    SBT_TAPE    00:00:00     15-JUL-11
        BP Key: 7924   Status: AVAILABLE  Compressed: YES  Tag: 20110715_142850
        Handle: 2fmhgmf4_1_1   Media: 11
  SPFILE Included: Modification time: 15-JUL-11
  SPFILE db_unique_name: TEST
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7918    Full    512.00K    SBT_TAPE    00:00:16     15-JUL-11
        BP Key: 7925   Status: AVAILABLE  Compressed: YES  Tag: 20110715_142850
        Handle: 2emhgmel_1_1   Media: 12
  Control File Included: Ckp SCN: 23093114     Ckp time: 15-JUL-11
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7919    Full    3.31G      SBT_TAPE    00:08:08     15-JUL-11
        BP Key: 7926   Status: AVAILABLE  Compressed: YES  Tag: 20110715_142850
        Handle: 2cmhgmek_1_1   Media: 9
  List of Datafiles in backup set 7919
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 23093114   15-JUL-11 /oracle/sys/test/system01.dbf
  5       Full 23093114   15-JUL-11 /oracle/data01/test/users02.dbf
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7920    Full    4.25G      SBT_TAPE    00:10:02     15-JUL-11
        BP Key: 7927   Status: AVAILABLE  Compressed: YES  Tag: 20110715_142850
        Handle: 2bmhgmek_1_1   Media: 8
  List of Datafiles in backup set 7920
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  3       Full 23093114   15-JUL-11 /oracle/rbs/test/undotbs01.dbf
  4       Full 23093114   15-JUL-11 /oracle/data01/test/users01.dbf

If we put all in a table:

RMAN mode Backup time Backup size
Compressed 11 minutes 7805 MB
Non compressed 5 minutes 17040 MB

So clearly compression is multiplying backup time by 2 with obviously less tape usage even with default compression algorithm (other algorithm are part of Advanced compression paid option).

RMAN compressed backup, network usage:

RMAN compressed backup network usage
RMAN compressed backup network usage
  • rxbyt/s: Total number of bytes received per second.
  • txbyt/s: Total number of bytes transmitted per second.

Around 15MB/s so we are far to reach our LAN card limit…

RMAN compressed backup, CPU usage (in %):

RMAN compressed backup cpu usage
RMAN compressed backup cpu usage

I have 16 cores on my server so it means around one core !

RMAN non compressed backup, network usage:

RMAN non compressed backup network usage
RMAN non compressed backup network usage

So around 75MB/s, 5 times more network usage than compressed backup. Means LAN card is close to be 60% used, only by the backup, better not to launch multiple backup at the same time.

RMAN non compressed backup CPU usage is almost flat…

Result and transfer rate can also be seen at Oracle level with V$BACKUP_SYNC_IO and V$BACKUP_ASYNC_IO tables (if you used BACKUP_TAPE_IO_SLAVES for tape access):

SQL> SET lines 200
SQL> col filename FOR a40
SQL> SET pages 50
SQL> SELECT
     a.device_type, a.TYPE, b.status, b.mbytes_processed AS "Total MB", a.filename, ROUND(a.bytes/(1024*1024)) AS "Size MB", TO_CHAR(a.open_time,'dd-mon-yyyy hh24:mi:ss') AS open_time,
     ROUND(a.elapsed_time/100) AS "Time (s)", ROUND(a.elapsed_time/100) AS "Elapsed (s)", ROUND(a.effective_bytes_per_second/(1024*1024)) AS "MB/s"
     FROM v$backup_async_io a, v$rman_status b
     WHERE a.rman_status_recid = b.recid
     ORDER BY a.use_count;
 
DEVICE_TYPE       TYPE      STATUS                    Total MB FILENAME                                    SIZE MB OPEN_TIME                       TIME (s) Elapsed (s)       MB/s
----------------- --------- ----------------------- ---------- ---------------------------------------- ---------- ----------------------------- ---------- ----------- ----------
DISK              AGGREGATE COMPLETED                    18391                                                9310 18-jul-2011 14:52:30                 600         600         16
DISK              INPUT     COMPLETED                    18391 /oracle/data01/test/users01.dbf                8000 18-jul-2011 14:52:30                 600         600         13
DISK              INPUT     COMPLETED                    18391 /oracle/rbs/test/undotbs01.dbf                 1310 18-jul-2011 14:52:30                 160         160          8
DISK              AGGREGATE COMPLETED                    18391                                                8570 18-jul-2011 14:52:30                 483         483         18
DISK              INPUT     COMPLETED                    18391 /oracle/data01/test/users02.dbf                8000 18-jul-2011 14:52:30                 483         483         17
DISK              INPUT     COMPLETED                    18391 /oracle/sys/test/system01.dbf                   570 18-jul-2011 14:52:31                  55          55         10
DISK              AGGREGATE COMPLETED                    18391                                                 500 18-jul-2011 14:52:31                  13          13         38
DISK              INPUT     COMPLETED                    18391 /oracle/sys/test/sysaux01.dbf                   500 18-jul-2011 14:52:31                  13          13         38
DISK              AGGREGATE COMPLETED                    18391                                                  11 18-jul-2011 14:52:46                   1           1         11
DISK              INPUT     COMPLETED                    18391 /oracle/software/dbs/snapcf_test.f               11 18-jul-2011 14:52:46                   1           1         11
 
10 ROWS selected.
 
SQL> SELECT
     a.device_type, a.TYPE, b.status, b.mbytes_processed AS "Total MB", a.filename, ROUND(a.bytes/(1024*1024)) AS "Size MB", TO_CHAR(a.open_time,'dd-mon-yyyy hh24:mi:ss') AS open_time,
     ROUND(a.elapsed_time/100) AS "Time (s)", ROUND(a.elapsed_time/100) AS "Elsapsed (s)", ROUND(a.effective_bytes_per_second/(1024*1024)) AS "MB/s"
     FROM v$backup_sync_io a, v$rman_status b
     WHERE a.rman_status_recid = b.recid
     ORDER BY a.use_count;
 
DEVICE_TYPE       TYPE      STATUS                    Total MB FILENAME                                    SIZE MB OPEN_TIME                       TIME (s) Elsapsed (s)       MB/s
----------------- --------- ----------------------- ---------- ---------------------------------------- ---------- ----------------------------- ---------- ------------ ----------
SBT_TAPE          OUTPUT    COMPLETED                    18391 34mhokte_1_1                                   4346 18-jul-2011 14:52:30                 600          600          7
SBT_TAPE          OUTPUT    COMPLETED                    18391 35mhokte_1_1                                   3385 18-jul-2011 14:52:30                 483          483          7
SBT_TAPE          OUTPUT    COMPLETED                    18391 36mhokte_1_1                                     67 18-jul-2011 14:52:31                  13           13          5
DISK              AGGREGATE COMPLETED                    18391                                                   0
                  UNKNOWN   COMPLETED                    18391                                                   0
SBT_TAPE          OUTPUT    COMPLETED                    18391 38mhoktu_1_1                                      0 18-jul-2011 14:52:46                   0            0
SBT_TAPE          OUTPUT    COMPLETED                    18391 37mhoktf_1_1                                      1 18-jul-2011 14:52:46                   1            1          1
 
7 ROWS selected.

If you activated asynchronous tape I/O you get whole result in V$BACKUP_ASYNC_IO table:

SQL> SET lines 200
SQL> col filename FOR a40
SQL> SET pages 50
SQL> SELECT
     a.device_type, a.TYPE, b.status, b.mbytes_processed AS "Total MB", a.filename, ROUND(a.bytes/(1024*1024)) AS "Size MB", TO_CHAR(a.open_time,'dd-mon-yyyy hh24:mi:ss') AS open_time,
     ROUND(a.elapsed_time/100) AS "Time (s)", ROUND(a.elapsed_time/100) AS "Elapsed (s)", ROUND(a.effective_bytes_per_second/(1024*1024)) AS "MB/s"
     FROM v$backup_async_io a, v$rman_status b
     WHERE a.rman_status_recid = b.recid
     ORDER BY a.use_count;
 
DEVICE_TYPE       TYPE      STATUS                    Total MB FILENAME                                    SIZE MB OPEN_TIME                       TIME (s) Elapsed (s)       MB/s
----------------- --------- ----------------------- ---------- ---------------------------------------- ---------- ----------------------------- ---------- ----------- ----------
DISK              INPUT     COMPLETED                    18391 /oracle/rbs/test/undotbs01.dbf                 1310 18-jul-2011 15:30:05                 151         151          9
DISK              INPUT     COMPLETED                    18391 /oracle/data01/test/users01.dbf                8000 18-jul-2011 15:30:05                 574         574         14
DISK              AGGREGATE COMPLETED                    18391                                                9310 18-jul-2011 15:30:05                 574         574         16
SBT_TAPE          OUTPUT    COMPLETED                    18391 39mhon3s_1_1                                   4346 18-jul-2011 15:30:05                 575         575          8
DISK              INPUT     COMPLETED                    18391 /oracle/sys/test/system01.dbf                   570 18-jul-2011 15:30:06                  53          53         11
DISK              INPUT     COMPLETED                    18391 /oracle/data01/test/users02.dbf                8000 18-jul-2011 15:30:06                 457         457         18
DISK              AGGREGATE COMPLETED                    18391                                                8570 18-jul-2011 15:30:06                 457         457         19
SBT_TAPE          OUTPUT    COMPLETED                    18391 3amhon3s_1_1                                   3385 18-jul-2011 15:30:05                 459         459          7
DISK              AGGREGATE COMPLETED                    18391                                                 500 18-jul-2011 15:30:05                  13          13         38
DISK              INPUT     COMPLETED                    18391 /oracle/sys/test/sysaux01.dbf                   500 18-jul-2011 15:30:05                  13          13         38
SBT_TAPE          OUTPUT    COMPLETED                    18391 3bmhon3t_1_1                                     67 18-jul-2011 15:30:05                  14          14          5
SBT_TAPE          OUTPUT    COMPLETED                    18391 3dmhon4c_1_1                                      0 18-jul-2011 15:30:21                   1           1          0
DISK              AGGREGATE COMPLETED                    18391                                                  11 18-jul-2011 15:30:21                   0           0
DISK              INPUT     COMPLETED                    18391 /oracle/software/dbs/snapcf_test.f               11 18-jul-2011 15:30:21                   0           0
SBT_TAPE          OUTPUT    COMPLETED                    18391 3cmhon3t_1_1                                      1 18-jul-2011 15:30:21                   1           1          1
 
15 ROWS selected.

In my configuration write to disk are asynchronous, because both at Linux and Oracle level all is well configured. Means that Oracle FILESYSTEMIO_OPTIONS is well configured:

SQL> show parameter filesystemio_options
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      SETALL

And asynchronous I/O Linux library is installed and is working:

[root@server1 ~]# rpm -q libaio
libaio-0.3.106-5
libaio-0.3.106-5
[root@server1 ~]# cat /proc/slabinfo | grep kio
kioctx                73    156    320   12    1 : tunables   54   27    8 : slabdata     13     13      0
kiocb                  7     15    256   15    1 : tunables  120   60    8 : slabdata      1      1      0

Restore with TDPO results

Script used for restore:

#!/bin/ksh
 
# Trapping errors -------------------------------------------------------------
 
trap 'STATUS=$?;set +x;echo;echo error $STATUS at line nb $LINENO executing :`sed -n "${LINENO}p" $0`;echo;exit $STATUS' ERR
 
# OS management ---------------------------------------------------------------
 
OSTYPE=$(uname -s)
case $OSTYPE in
"AIX" ) alias bdf="/usr/bin/df -Ik"
        alias ll="/usr/bin/ls -l" ;;
"SunOS") alias bdf="/usr/bin/df -k"
         alias ll="/usr/bin/ls -l"
         alias awk="/usr/xpg4/bin/awk"
         alias grep="/usr/xpg4/bin/grep" ;;
"Linux") alias bdf="/bin/df -k"
         alias grep=egrep
         alias echo="echo -e"
         alias ll="ls -l" ;;
esac
 
# Variables -------------------------------------------------------------------
JOB_NAME=$(basename $0)
TODAY=`date +'%Y%m%d_%H%M%S'`
LOG_FILE=/tmp/${JOB_NAME}_${TODAY}_$$.log
export NLS_DATE_FORMAT="dd-mon-yyyy hh24:mi:ss"
 
# Main Here -------------------------------------------------------------------
 
JOB_START=`date +%s`
echo "\nStarting at `date +'%Y%m%d_%H%M%S'`\n"
 
rman << EOF
connect target /
connect catalog rman/rman@emrep
 
startup nomount;
 
run
{
allocate channel sbt1 type sbt parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel sbt2 type sbt parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel sbt3 type sbt parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel sbt4 type sbt parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
 
restore database;
restore controlfile;
 
release channel sbt1;
release channel sbt2;
release channel sbt3;
release channel sbt4;
}
 
alter database mount;
 
alter database open resetlogs;
EOF
 
JOB_END=`date +%s`
echo "\nEnding at `date +'%Y%m%d_%H%M%S'`"
 
echo "\nCompleted in $((($JOB_END - $JOB_START)/60)) minutes"
 
echo "\nNormal Exit"

Restore took between 11-12 minutes in compressed and non compressed mode.

RMAN compressed restore, network usage:

RMAN compressed restore network usage
RMAN compressed restore network usage
  • rxbyt/s: Total number of bytes received per second.
  • txbyt/s: Total number of bytes transmitted per second.

Around 15MB/s so we are far to reach our LAN card limit…

RMAN compressed restore, CPU usage (in %):

RMAN compressed restore cpu usage
RMAN compressed restore cpu usage

I have 16 cores on my server so it means around one core !

RMAN non compressed restore, network usage:

RMAN non compressed restore network usage
RMAN non compressed restore network usage

So around 31MB/s, 2 times more network usage than compressed backup. Means LAN card is 25% used, only by the backup so not a big deal…

RMAN non compressed restore CPU usage is almost flat…

Issues encountered

Not able to delete backup files

When deleting backup with delete command you may hit below issue:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_SBT_TAPE_1 channel at 07/13/2011 11:43:47
ORA-19509: failed to delete sequential file, handle="c-2049025103-20110713-00", parms=""
ORA-27027: sbtremove2 returned error
ORA-19511: Error received from media manager layer, error text:
   ANS1126E (RC27)   The file space cannot be deleted because this node does not have
permission to delete archived or backed up data.

Simply give your node right to delete TSM backup files:

  1. Log into the Tivoli Storage Manager server and bring up the Tivoli Storage Manager administrative command line.
  2. Enter this command to the Tivoli Storage Manager server: update node TSMclientnode1 backdel=y.

Control file backup missing

You may test multiple restore/recover and use same initial consistent (offline) backup. At a point in time when trying to restore controlfile you may have following scary message, while still seeing a controlfile backup when using LIST BACKUP command:

RMAN> restore controlfile;
 
Starting restore at 20-jul-2011 17:13:02
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1
 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/20/2011 17:13:02
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore

While, as I said, you see it:

RMAN> crosscheck backup of controlfile;
 
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=4cmhti9m_1_1 RECID=139 STAMP=756992327
Crosschecked 1 objects
 
 
RMAN> list expired backup of controlfile;
 
specification does not match any backup in the repository
 
RMAN> list backup of controlfile summary;
 
 
List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
9629    B  F  A SBT_TAPE    20-jul-2011 11:38:52 1       1       NO         20110720_113718

So before jumping to tape system error, think of database incarnation !!!:

RMAN>  LIST INCARNATION OF DATABASE "test";
 
 
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2       4       TEST     2049025103       PARENT  1          11-may-2011 17:24:31
2       4301    TEST     2049025103       PARENT  16336412   13-jul-2011 15:33:49
2       9699    TEST     2049025103       PARENT  23751040   20-jul-2011 12:07:09
2       13720   TEST     2049025103       CURRENT 23751041   20-jul-2011 17:18:37
 
RMAN> RESET DATABASE TO INCARNATION 4301;
 
RMAN> restore controlfile;
 
Starting restore at 20-jul-2011 17:50:12
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1
 
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: restoring control file
channel ORA_SBT_TAPE_1: reading from backup piece 4cmhti9m_1_1
channel ORA_SBT_TAPE_1: piece handle=4cmhti9m_1_1 tag=20110720_113718
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:07
output file name=/oracle/ctrl/test/control01.ctl
output file name=/oracle/ctrl/test/control02.ctl
Finished restore at 20-jul-2011 17:50:21

Online backup with TDPO

If you want to backup a production database then obviously you will perform an online backup and have your database running in ARCHIVELOG mode. What is strongly suggested to reduce your Recovery Time Objective (RTO) is to first backup to disk (disk are cheap isn’t it ?), ideally in Fast Recovery Area (FRA) and then put this FRA to tape for obvious safety reason.

Configure FRA for your database by setting those two initialization parameters (filesystem case but could be ASM):

SQL> show parameter db_recovery
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oracle/fast_recovery_area
db_recovery_file_dest_size           big INTEGER 30G

Then do your online backup (on disk) with something like:

#!/bin/ksh
 
# Trapping errors -------------------------------------------------------------
 
trap 'STATUS=$?;set +x;echo;echo error $STATUS at line nb $LINENO executing :`sed -n "${LINENO}p" $0`;echo;exit $STATUS' ERR
 
# OS management ---------------------------------------------------------------
 
OSTYPE=$(uname -s)
case $OSTYPE in
"AIX" ) alias bdf="/usr/bin/df -Ik"
        alias ll="/usr/bin/ls -l" ;;
"SunOS") alias bdf="/usr/bin/df -k"
         alias ll="/usr/bin/ls -l"
         alias awk="/usr/xpg4/bin/awk"
         alias grep="/usr/xpg4/bin/grep" ;;
"Linux") alias bdf="/bin/df -k"
         alias grep=egrep
         alias echo="echo -e"
         alias ll="ls -l" ;;
esac
 
# Variables -------------------------------------------------------------------
JOB_NAME=$(basename $0)
TODAY=`date +'%Y%m%d_%H%M%S'`
LOG_FILE=/tmp/${JOB_NAME}_${TODAY}_$$.log
export NLS_DATE_FORMAT="dd-mon-yyyy hh24:mi:ss"
 
# Main Here -------------------------------------------------------------------
 
JOB_START=`date +%s`
echo "\nStarting at `date +'%Y%m%d_%H%M%S'`\n"
 
rman << EOF
connect target /
connect catalog rman/rman@emrep
 
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 9 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE DEFAULT DEVICE TYPE TO disk;
CONFIGURE CHANNEL DEVICE TYPE sbt PARMS 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
CONFIGURE DEVICE TYPE sbt PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
#CONFIGURE DEVICE TYPE sbt PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE disk PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
 
run
{
allocate channel disk1 type disk;
allocate channel disk2 type disk;
allocate channel disk3 type disk;
allocate channel disk4 type disk;
 
backup database include current controlfile tag '${TODAY}';
backup archivelog all delete all input tag '${TODAY}';
 
release channel disk1;
release channel disk2;
release channel disk3;
release channel disk4;
}
 
EOF
 
JOB_END=`date +%s`
echo "\nEnding at `date +'%Y%m%d_%H%M%S'`"
 
echo "\nCompleted in $((($JOB_END - $JOB_START)/60)) minutes"
 
echo "\nNormal Exit"

Then you have something like:

RMAN> list backup;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
23075   Full    1.02M      DISK        00:00:07     06-SEP-11
        BP Key: 23081   Status: AVAILABLE  Compressed: YES  Tag: 20110906_152111
        Piece Name: /oracle/fast_recovery_area/TEST/backupset/2011_09_06/o1_mf_nnndf_20110906_152111_76d7mcwl_.bkp
  List of Datafiles in backup set 23075
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  7       Full 33217619   06-SEP-11 /oracle/data01/test/fda01.dbf
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
23076   Full    1.20M      DISK        00:00:18     06-SEP-11
        BP Key: 23082   Status: AVAILABLE  Compressed: YES  Tag: 20110906_152111
        Piece Name: /oracle/fast_recovery_area/TEST/backupset/2011_09_06/o1_mf_ncnnf_20110906_152111_76d7n3l3_.bkp
  Control File Included: Ckp SCN: 33217637     Ckp time: 06-SEP-11
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
23077   Full    80.00K     DISK        00:00:03     06-SEP-11
        BP Key: 23083   Status: AVAILABLE  Compressed: YES  Tag: 20110906_152111
        Piece Name: /oracle/fast_recovery_area/TEST/backupset/2011_09_06/o1_mf_nnsnf_20110906_152111_76d7n7m2_.bkp
  SPFILE Included: Modification time: 03-SEP-11
  SPFILE db_unique_name: TEST
.
.
.

Use a script like the following to put your FRA on tape (you may also use TO DESTINATION option):

run
{
allocate channel sbt1 type sbt parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel sbt2 type sbt parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel sbt3 type sbt parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel sbt4 type sbt parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
 
backup recovery area;
 
release channel sbt1;
release channel sbt2;
release channel sbt3;
release channel sbt4;
}

Then all FRA disk backup files are duplicated on tape:

RMAN> list backup;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size
------- ---- -- ----------
23075   Full    1.02M
  List of Datafiles in backup set 23075
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  7       Full 33217619   06-SEP-11 /oracle/data01/test/fda01.dbf
 
  Backup Set Copy #1 of backup set 23075
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:07     06-SEP-11       YES        20110906_152111
 
    List of Backup Pieces for backup set 23075 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    23081   1   AVAILABLE   /oracle/fast_recovery_area/TEST/backupset/2011_09_06/o1_mf_nnndf_20110906_152111_76d7mcwl_.bkp
 
  Backup Set Copy #2 of backup set 23075
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  SBT_TAPE    00:00:07     06-SEP-11       YES        20110906_152111
 
    List of Backup Pieces for backup set 23075 Copy #2
    BP Key  Pc# Status      Media                   Piece Name
    ------- --- ----------- ----------------------- ----------
    23644   1   AVAILABLE   7                       52mlshca_1_2
 
BS Key  Type LV Size
------- ---- -- ----------
23076   Full    1.20M
  Control File Included: Ckp SCN: 33217637     Ckp time: 06-SEP-11
 
  Backup Set Copy #1 of backup set 23076
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:18     06-SEP-11       YES        20110906_152111
 
    List of Backup Pieces for backup set 23076 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    23082   1   AVAILABLE   /oracle/fast_recovery_area/TEST/backupset/2011_09_06/o1_mf_ncnnf_20110906_152111_76d7n3l3_.bkp
 
  Backup Set Copy #2 of backup set 23076
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  SBT_TAPE    00:00:18     06-SEP-11       YES        20110906_152111
 
    List of Backup Pieces for backup set 23076 Copy #2
    BP Key  Pc# Status      Media                   Piece Name
    ------- --- ----------- ----------------------- ----------
    23643   1   AVAILABLE   4                       53mlshcj_1_2
 
BS Key  Type LV Size
------- ---- -- ----------
23077   Full    80.00K
  SPFILE Included: Modification time: 03-SEP-11
  SPFILE db_unique_name: TEST
 
  Backup Set Copy #1 of backup set 23077
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:03     06-SEP-11       YES        20110906_152111
 
    List of Backup Pieces for backup set 23077 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    23083   1   AVAILABLE   /oracle/fast_recovery_area/TEST/backupset/2011_09_06/o1_mf_nnsnf_20110906_152111_76d7n7m2_.bkp
 
  Backup Set Copy #2 of backup set 23077
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  SBT_TAPE    00:00:03     06-SEP-11       YES        20110906_152111
 
    List of Backup Pieces for backup set 23077 Copy #2
    BP Key  Pc# Status      Media                   Piece Name
    ------- --- ----------- ----------------------- ----------
    23642   1   AVAILABLE   4                       54mlshd6_1_2
.
.
.

References

  • RMAN Backup Performance [ID 360443.1]
  • RMAN Myths Dispelled: Common RMAN Performance Misconceptions [ID 134214.1]
  • RMAN Performance Troubleshooting [ID 1326686.1]
  • Known RMAN Performance Problems [ID 247611.1]
  • Advise On How To Improve Rman Performance [ID 579158.1]
  • RMAN: I/O Slaves and Memory Usage [ID 73354.1]
  • HOW TO CHECK ASYNCHRONOUS I/O ON LINUX [ID 370579.1]
  • How To Check if Asynchronous I/O is Working On Linux [ID 237299.1]
  • Backup and Recovery Scenarios [ID 94114.1]
  • RMAN — Frequently Asked Question (FAQ) [ID 469777.1]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>