The Blog from the DBA Classroom

By: Joel Goodman

Archive for April, 2010

Be Foxy; Try Proxy

Posted by Joel Goodman on 22/04/2010


I was speaking at the Norwegian Oracle User Group Last week, having been invited  to present talks on RMAN New Features for Oracle 11g and Partitioning New Features for Oracle 11g. The Partitioning talk had much of the typical questions and answers concerning the new partitioning methods. The RMAN session however, was somewhat unusual in that one of the most interesting discussions arising from the talk was “off topic” in that it did not concern a New 11g RMAN feature. During the RMAN talk I mentioned that the “Active Data Guard” option permits one to perform “Fast Incremental Backups” with Data Guard on a physical standby database. This initiated a discussion of how “Block Change Tracking” works in Oracle generally and additionally how Exadata Storage Cells improve the granularity by tracking at the individual block level rather than at the range of blocks level.

The point of the discussion was that the backups are made less of an overhead by:

  1. Tracking the blocks that have changed, thereby reducing the read overheads when doing backups.
  2. Offloading the entire process to a physical standby, thereby removing the entire overhead of the backup from the primary.
  3. Reducing the overheads further if using Exadata by having more granular identification of which blocks had changed.

We discussed this for a while and then I informed the 50 or so delegates that there is still the overhead of reading and writing the blocks that get backed up from the storage servers into the database server RMAN Channel processes. It was then that the subject of RMAN “Proxy Copy” came up and to my surprise few if any delegates knew about it although this has occurred in the past in some Oracle University classes. So I went a bit off topic and described how this works and then followed this up with some offline discussions after the talk ended.

  1. The database, or specific tablespaces may be backup up by having the Media Manager do it by “proxy” on behalf of RMAN
  2. RMAN requests that each file  in the database or tablespace is copied by the Media Manager
  3. The Media Manager then copies the file using whichever method it supports, such as breaking mirrors and resilvering
  4. When RMAN receives a reply that a file has been copied it sends an “info” request to the Media Manager asking for the file  name of the backup just completed
  5. RMAN then adds the information about the copy to the catalog where it is listed as a “Proxy Copy

The benefit of “Proxy Copy” is that the Media Manager may make the copy from the data files or archive logs directly to a backup location without necessarily passing the data from the storage network to the database servers and this reduces the traffic in the storage network. But balanced against this must be the elapsed time and backup volume. Lets compare Proxy Copy to incremental backups to see the pros and cons:

Incremental Backups:

  • Level Zero Incremental backups back up all formatted blocks and blocks that must be backed up for all the files covered by the backup command. This can lead to substantial network traffic on the storage network whenever the level zero backup is taken.
  • Level one Incremental backups back up only those blocks in the same range as blocks modified since the last backup.
  • Level one Incremental backups done in an Exadata Database Machine environment backup only those blocks that have changed since the last backup.
  • The degree of network traffic on a level one backup is proportional to the “delta” , ie what percentage of blocks in the database change between each backup

Proxy Copies:

  • Proxy Copies back up all the blocks of the files included in the backup command. For Database backups all the blocks are read and written as occurs when performing an RMAN Image Copy.
  • Proxy Copies however can reduce the network traffic in the storage network
  • The Media Manager products that use the Oracle System Backup to Tape (SBT) Application Programming Interface do not necessarily all support Proxy Copy as it is not mandatory for this to be included. When the RMAN channel process connects to the media manager it uses the “sbtinit” call to shake hands with the media manager and determine the capabilities provided by the vendor.
  • One may use the “sbttest” utility for example to see this and here is an example of the handshake between RMAN in 11.1.07 and the oracle.disksbt simulator supplied by Oracle which had remained static since Oracle 8i. Note that for this to work the environment variable BACKUP_DIR must be set to a directory where the simulator will write the “media” including ordinary backupsets to media and Proxy Copies.

tutor5d> sbttest sbt_test.file -trace sbtio.log -dbname tutor5d -libname oracle.disksbt -remove_before -no_remove_after -block_size 4096 -block_count 1000

The sbt function pointers are loaded from oracle.disksbt library.
— sbtinit succeeded
— sbtinit (2nd time) succeeded
sbtinit: vendor description string=WARNING: Oracle Test Disk API
sbtinit: Media manager is version 8.1.3.0
sbtinit: Media manager supports SBT API version 2.0
sbtinit: allocated sbt context area of 4160 bytes
sbtinit: proxy copy is supported
— sbtinit2 succeeded

The key part of the handshake is the second to last line showing that Proxy Copy is supported by this “Media Manager”.

To take a Proxy Copy one uses the following syntax:

backup proxy [only] [database | tablespace | datafile | datafilecopy | archivelog | controlfilecopy and some others followed by possible options. The “only” keyword means that id the media manager does not support Proxy Copy then an error is returned. Without the “only” RMAN would request an ordinary backupset in the event that Proxy Copy was not supported.

Here is an example of a proxy copy for the database:

RMAN> backup proxy only database;

Starting backup at 22-APR-10
using channel ORA_SBT_TAPE_1
current control file cannot be backed up by proxy.
SPFILE cannot be backed up by proxy.
channel ORA_SBT_TAPE_1: starting full proxy datafile backup at 22-APR-10
channel ORA_SBT_TAPE_1: specifying datafile(s) for proxy backup
input datafile file number=00001 name=/home/tutor5d/data/TUTOR5D_NLEDU02/datafile/o1_mf_system_3cxr3bkq_.dbf
proxy file handle=08lbpqs6_1_1
input datafile file number=00002 name=/home/tutor5d/data/TUTOR5D_NLEDU02/datafile/o1_mf_sysaux_3cxr3tv8_.dbf
proxy file handle=08lbpqs6_2_1
input datafile file number=00004 name=/home/tutor5d/data/TUTOR5D_NLEDU02/datafile/o1_mf_example_3cxs05ds_.dbf
proxy file handle=08lbpqs6_3_1
input datafile file number=00003 name=/home/tutor5d/data/TUTOR5D_NLEDU02/datafile/o1_mf_undo_3cxr45jp_.dbf
proxy file handle=08lbpqs6_4_1
channel ORA_SBT_TAPE_1: proxy copy complete, elapsed time: 00:00:56
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_SBT_TAPE_1: starting piece 1 at 22-APR-10
channel ORA_SBT_TAPE_1: finished piece 1 at 22-APR-10
piece handle=09lbpqtu_1_1 tag=TAG20100422T212214 comment=API Version 2.0,MMS Version 8.1.3.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-APR-10

RMAN>

To view the Proxy Copies already taken use the RMAN List Backup command as follows:

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
4       Full    7.75M      SBT_TAPE    00:00:02     22-APR-10
BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20100422T212214
Handle: 09lbpqtu_1_1   Media:
SPFILE Included: Modification time: 01-FEB-10
SPFILE db_unique_name: TUTOR5D_NLEDU02
Control File Included: Ckp SCN: 2143889      Ckp time: 22-APR-10

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
5       Full    200.50M    SBT_TAPE    00:00:15     22-APR-10
BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20100422T212721
Handle: 0albpr5q_1_1   Media:
List of Datafiles in backup set 5
File LV Type Ckp SCN    Ckp Time  Name
—- — —- ———- ——— —-
1       Full 2144034    22-APR-10 /home/tutor5d/data/TUTOR5D_NLEDU02/datafile/o1_mf_system_3cxr3bkq_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
6       Full    7.75M      SBT_TAPE    00:00:02     22-APR-10
BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20100422T212721
Handle: 0blbpr6j_1_1   Media:
SPFILE Included: Modification time: 01-FEB-10
SPFILE db_unique_name: TUTOR5D_NLEDU02
Control File Included: Ckp SCN: 2144058      Ckp time: 22-APR-10

List of Proxy Copies
====================

PC Key  File Status      Completion Time Ckp SCN    Ckp Time
——- —- ———– ————— ———- —————
10      1    AVAILABLE   22-APR-10       2143862    22-APR-10
Datafile name: /home/tutor5d/data/TUTOR5D_NLEDU02/datafile/o1_mf_system_3cxr3bkq_.dbf
Handle: 08lbpqs6_1_1   Media: 3380-3
Tag: TAG20100422T212214

11      2    AVAILABLE   22-APR-10       2143862    22-APR-10
Datafile name: /home/tutor5d/data/TUTOR5D_NLEDU02/datafile/o1_mf_sysaux_3cxr3tv8_.dbf
Handle: 08lbpqs6_2_1   Media: 3380-3
Tag: TAG20100422T212214

13      3    AVAILABLE   22-APR-10       2143862    22-APR-10
Datafile name: /home/tutor5d/data/TUTOR5D_NLEDU02/datafile/o1_mf_undo_3cxr45jp_.dbf
Handle: 08lbpqs6_4_1   Media: 3380-3
Tag: TAG20100422T212214

12      4    AVAILABLE   22-APR-10       2143862    22-APR-10
Datafile name: /home/tutor5d/data/TUTOR5D_NLEDU02/datafile/o1_mf_example_3cxs05ds_.dbf
Handle: 08lbpqs6_3_1   Media: 3380-3
Tag: TAG20100422T212214

PC Key  Thrd Seq     Status      Completion Time
——- —- ——- ———– —————
6       1    242     AVAILABLE   22-APR-10
Handle: 04lbpipf_1_1   Media: 3380-3
Tag: TAG20100422T190415

7       1    243     AVAILABLE   22-APR-10
Handle: 04lbpipf_2_1   Media: 3380-3
Tag: TAG20100422T190415

8       1    244     AVAILABLE   22-APR-10
Handle: 04lbpipf_3_1   Media: 3380-3
Tag: TAG20100422T190415

RMAN>

Notice that the Proxy Copies are listed after the backup sets and are not listed as “copies” as seen here:

RMAN> list copy of database;

RMAN>

But if an RMAN Image copy is taken then this will be listed as follows:

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;

old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
released channel: ORA_SBT_TAPE_1

RMAN> RMAN> backup as copy database;

Starting backup at 22-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=74 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/tutor5d/data/TUTOR5D_NLEDU02/datafile/o1_mf_system_3cxr3bkq_.dbf
output file name=/home/tutor5d/flash/TUTOR5D_NLEDU02/datafile/o1_mf_system_5x196vfy_.dbf tag=TAG20100422T213451 RECID=1 STAMP=717024917
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/tutor5d/data/TUTOR5D_NLEDU02/datafile/o1_mf_sysaux_3cxr3tv8_.dbf
output file name=/home/tutor5d/flash/TUTOR5D_NLEDU02/datafile/o1_mf_sysaux_5x197ypb_.dbf tag=TAG20100422T213451 RECID=2 STAMP=717024945
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/tutor5d/data/TUTOR5D_NLEDU02/datafile/o1_mf_example_3cxs05ds_.dbf
output file name=/home/tutor5d/flash/TUTOR5D_NLEDU02/datafile/o1_mf_example_5x198qvp_.dbf tag=TAG20100422T213451 RECID=3 STAMP=717024961
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/tutor5d/data/TUTOR5D_NLEDU02/datafile/o1_mf_undo_3cxr45jp_.dbf
output file name=/home/tutor5d/flash/TUTOR5D_NLEDU02/datafile/o1_mf_undo_5x19972q_.dbf tag=TAG20100422T213451 RECID=4 STAMP=717024970
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/home/tutor5d/flash/TUTOR5D_NLEDU02/controlfile/o1_mf_TAG20100422T213451_5x199g8c_.ctl tag=TAG20100422T213451 RECID=5 STAMP=717024974
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 22-APR-10
channel ORA_DISK_1: finished piece 1 at 22-APR-10
piece handle=/home/tutor5d/flash/TUTOR5D_NLEDU02/backupset/2010_04_22/o1_mf_nnsnf_TAG20100422T213451_5x199hh4_.bkp tag=TAG20100422T213451 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-APR-10

RMAN> list copy of database;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
——- —- – ————— ———- —————
1       1    A 22-APR-10       2144262    22-APR-10
Name: /home/tutor5d/flash/TUTOR5D_NLEDU02/datafile/o1_mf_system_5x196vfy_.dbf
Tag: TAG20100422T213451

2       2    A 22-APR-10       2144276    22-APR-10
Name: /home/tutor5d/flash/TUTOR5D_NLEDU02/datafile/o1_mf_sysaux_5x197ypb_.dbf
Tag: TAG20100422T213451

4       3    A 22-APR-10       2144292    22-APR-10
Name: /home/tutor5d/flash/TUTOR5D_NLEDU02/datafile/o1_mf_undo_5x19972q_.dbf
Tag: TAG20100422T213451

3       4    A 22-APR-10       2144286    22-APR-10
Name: /home/tutor5d/flash/TUTOR5D_NLEDU02/datafile/o1_mf_example_5x198qvp_.dbf
Tag: TAG20100422T213451

RMAN>

Finally one may list the Proxy Copies by using the v$proxy_datafile view in the target database as follows:

SYS@tutor5d SQL> select file#,device_type, media,tag,status,
2  start_time,completion_time
3  from v$proxy_datafile
4* order by 1

FILE#            DEVICE_TYPE       MEDIA  TAG      S     START_TIME                            COMPLETION_TIME
———- —————– —— ——————– – —————————– —————————–
1 SBT_TAPE          3380-3  TAG20100422T212214   A 22-apr-2010 21:22:15           22-apr-2010 21:23:02
1 SBT_TAPE          3380-3  TAG20100422T185703   D 22-apr-2010 18:57:06         22-apr-2010 18:58:46
1 SBT_TAPE          3380-3  TAG20100422T211624   D 22-apr-2010 21:16:25            22-apr-2010 21:16:44
2 SBT_TAPE          3380-3 TAG20100422T185703   D 22-apr-2010 18:57:06          22-apr-2010 18:58:48
2 SBT_TAPE          3380-3 TAG20100422T212214   A 22-apr-2010 21:22:15            22-apr-2010 21:23:02
2 SBT_TAPE          3380-3 TAG20100401T171026   D 01-apr-2010 17:10:26           01-apr-2010 17:10:42
3 SBT_TAPE          3380-3 TAG20100422T212214   A 22-apr-2010 21:22:15            22-apr-2010 21:23:02
3 SBT_TAPE          3380-3 TAG20100422T185703   D 22-apr-2010 18:57:06          22-apr-2010 18:58:52
4 SBT_TAPE          3380-3 TAG20100422T212214   A 22-apr-2010 21:22:15          22-apr-2010 21:23:02
4 SBT_TAPE          3380-3 TAG20100422T185703   D 22-apr-2010 18:57:06          22-apr-2010 18:58:50

10 rows selected.

Note that proxy copies of archivelogs are listed in v$proxy_archivedlog.

Further reading is available in the documentation here .

So have fun with your proxy and see if it can help you cut some storage network overheads based on your backup volume, I/O workloads and database change delta.

Joel

22/04/2010

Advertisements

Posted in Oracle | Leave a Comment »