Managing Database Backup Retention with RMAN and Tivoli Storage Manager

1
Managing Database Backup Retention with RMAN and
Tivoli Storage Manager
Monica Penshorn
December 17, 2004
2
Disclaimer
The information provide in this document
expresses opinions and research of the author.
The author, Medtronic, and the Twin Cities
Oracle User's Group (TCOUG) RMAN SIG take no
responsibility for how the information is used or
if performing the actions contained in this
document cause any harm to your database
environment. Please always perform backup,
recovery, and retention testing in a lab
environment prior to executing in production. For
situations where a recovery of a critical
database is required please contact Oracle
Support for assistance.
3
Overview

• Keys to Successful Database Retention Management
• Storage Server Components Important to RMAN
  Backup and Retention
• Querying Backup Information from the Storage
  Server Database
• Performing RMAN Repository Maintenance

4
Keys to Successful Retention Management

• Business Area data retention needs are clearly
  defined
• FDA requirements
• Criticality of data
• Storage and Database Teams honor agreements made
  with Business Areas
• Retention policies and costs of retaining
  backups are documented and shared with
• Business Areas
• Storage server components that affect retention
  are understood by Database
• Administrators
• Storage, Unix, and Database Teams collaborate to
  deliver solid, high-performance,
• cost-effective solutions for the Business
• Duplexed archive log backups
• Onsite and Offsite backups
• Increased backup and recovery performance

5
What Does it all Mean???
Management Class
Policy Domain
Copy Group
Policy
Archive
Backup Type
Include/Exclude
Storage Pool
Filespace
6
Tivoli Storage Manager Components That Affect
Retention

• Storage Server Database
• Policy Domain
• Node
• Policy Set
• 
  Management Class
• 
  Copy Group - Backup or Archive
• Storage Pool Tape or Disk

7

• Storage Server Database Used for storing
  details about backups. The RMAN and Tivoli
  Storage Manager (TSM) backup repositories must be
  synchronized.
• Policy Domain - For grouping clients with similar
  retention requirements
• Node Nodes get registered with Tivoli Storage
  Manager which allows them to backup files to the
  Storage Server.
• Policy Set
• For grouping clients with similar retention
  requirements
• Many Policy Sets per Policy Domain, however,
  only 1 active Policy Set at a time
• Management Class
• 1 or more per Policy Set with 1 being considered
  the default Management Class
• Copy Group
• Heart of retention - where retention parameters
  are specified
• 1 Copy Group per Management Class referred to as
  "Standard"
• Types are Backup and Archive
• Tivoli Data Protection for Oracle uses Backup
  Copy Groups only

8

• Storage Server Database
• Policy Domain
• Node
• Policy Set
• 
  Management Class
• 
  Copy Group - Backup or Archive
• Storage Pool Tape or Disk

9

• Storage Server Database Used for storing
  details of backups. The RMAN and Tivoli Storage
  Manager (TSM) repositories can be synchronized
  using the RMAN crosscheck command.
• The RMAN crosscheck command determines whether a
  backup still exists on media. Running the
  crosscheck command sets the status in the RMAN
  catalog to AVAILABLE or EXPIRED depending on
  whether the backup exists on media.
• A Available Backup exist on the storage server
• X Expired Backup does not exist on the storage
  server, but is listed in RMAN catalog or
  controlfile
• Target database views to query status of backups
  when using a controlfile
• Vbackup_set
• Vdatafile_copy
• Catalog views to query status of backups when
  using a catalog
• RC_BACKUP_SET
• RC_DATAFILE_COPY

10
How to Use the Crosscheck Command export
ORACLE_SIDdb1 export TDPO_OPTFILE/cprod/rman/b
in/tdpo_db1.opt rman target / catalog
rcat_db1/ltpasswdgt_at_catalog_database RMANgt
configure default device type to sbt_tape RMANgt
allocate channel for maintenance type
sbt_tape RMANgt crosscheck backup of controlfile
database archivelog all spfile RMANgt release
channel RMANgt exit NOTE Be careful when
running the crosscheck command that you are
pointing to the correct media device where the
backups were taken. If you are pointing to the
wrong media device RMAN will assume the backups
are missing and flag the catalog entries with an
"X" for "expired". Then later you might
accidentally delete good backups, because they
are marked as expired!
11
How to Locate a Backup in TSM Database
Determine backup sequence key (bs_key) of backup
you are interested in SELECT db_key, bs_key,
db_name, file, status, completion_time
FROM rc_backup_datafile ORDER BY completion_time,
file / Check the media status (Available or
Expired) for that backup sequence SELECT db_key,
bs_key, db_id, backup_type, pieces,
status, pieces, start_time, completion_time
FROM rc_backup_set WHERE bs_key 41755 / SELECT
db_id, bs_key, handle, media, media_pool,
device_type, start_time,
completion_time, status FROM
rc_backup_piece WHERE bs_key 41755 / Note
These views may be queried if using a catalog.
Alternate views may be queried if using the
controlfile to store backup information.
12
Storage server database may be queried directly
to check for backups RC_BACKUP_PIECE.HANDLE
BACKUPS.LL_NAME (RMAN catalog)
(Tivoli Database) Piece
Name BACKUPS.LL_NAME (From RMANgt list
backuppiece) (Tivoli Database) dsmadmc
username user password tsm STORAGESVRgt
SELECT ll_name, node_name, filespace_name, state,
owner FROM backups where NODE_NAME'MSPR1_RMAN'
AND BACKUP_DATEgtts '2004-12-14 000000' AND
ll_name IN ('tfg7uo1u_1_1') gt eraseme.txt cat
eraseme.txt LL_NAME NODE_NAME
FILESPACE_NAME STATE OWNER
------- ----------- ----------------
-------------- ------ tfg7uo1u_1_1 MSPR1_RMAN
/db1_db ACTIVE_VERSION oracle
13
How Tivoli Stores Backup Objects
HL stands for High Level and LL stands for Low
Level
FILESPACE_NAME TDPO_FS in TDPO.OPT file HL_NAME
// LL_NAME df_2_4686869532_1 based on RMAN
format OBJECT_ID 39905726 A uniquely
generated value OWNER TDPO_OWNER in TDPO.OPT
file Example /db1_db/exp/epprd/exp1.dmp FILESPAC
E_NAME /db1_db HL_NAME /exp/epprd LL_NAME
exp1.dmp OBJECT_ID 39904902 OWNER oracle
From p.172 of Oracle9i RMAN Backup and Recovery
14
Storage Server GUI May Also Be Used to View
Tivoli Objects
Storagesrvr
15

• Storage Server Database
• Policy Domain
• Node
• Policy Set
• 
  Management Class
• 
  Copy Group - Backup or Archive
• Storage Pool Tape or Disk

16
Policy Domain - For grouping clients with similar
retention requirements Tivoli Storage Manager
commands to view Policy Domain Settings dsmadmc
username user password tsm STORAGESVRgt q
domain tsm STORAGESVRgt q domain
formatdetail tsm STORAGESVRgt q domain
RMAN
17

• Storage Server Database
• Policy Domain
• Node
• Policy Set
• 
  Management Class
• 
  Copy Group - Backup or Archive
• Storage Pool Tape or Disk

18
Node Nodes get registered with Tivoli Storage
Manager. This allows the client to backup files
to the Storage Server. Tivoli Storage Manager
commands to view Node settings dsmadmc username
user password tsm STORAGESVRgt q node tsm
STORAGESVRgt q node RMAN tsm STORAGESVRgt q node
RMAN formatdetail Example MSPR1_RMAN
19

• Storage Server Database
• Policy Domain
• Node
• Policy Set
• 
  Management Class
• 
  Copy Group - Backup or Archive
• Storage Pool Tape or Disk

20

• Policy Set
• For grouping clients with similar retention
  requirements
• Many Policy Sets per Policy Domain, however,
  only 1 active Policy Set at a time.
• Tivoli Storage Manager commands to view Policy
  Set settings
• dsmadmc
• username user
• password
• tsm STORAGESVRgt q policy rman
• tsm STORAGESVRgt q policy
• tsm STORAGESVRgt q policy active
• tsm STORAGESVRgt q policy formatdetail

21

• Storage Server Database
• Policy Domain
• Node
• Policy Set
• 
  Management Class
• 
  Copy Group - Backup or Archive
• Storage Pool Tape or Disk

22
Management Class 1 or more per Policy Sets with
1 being considered the default Management
Class Tivoli Storage Manager commands to view
Management Class settings dsmadmc username
user password tsm STORAGESVRgt q
mgmtclass tsm STORAGESVRgt q mgmtclass
active tsm STORAGESVRgt q mgmtclass rman tsm
STORAGESVRgt q mgmtclass rman active tsm
STORAGESVRgt q mgmtclass rman active
formatdetail Examples primary_disk secondary_
disk primary_tape secondary_tape
23

• Storage Server Database
• Policy Domain
• Node
• Policy Set
• 
  Management Class
• 
  Copy Group - Backup or Archive
• Storage Pool Tape or Disk

24

• Copy Group
• Heart of retention - where retention parameters
  are specified
• 1 Copy Group per Management Class referred to as
  "Standard"
• Types are Backup and Archive
• Tivoli Data Protection for Oracle uses Backup
  Copy Groups only
• Tivoli Storage Manager commands to view Copy
  Group settings
• dsmadmc
• username user
• password
• tsm STORAGESVRgt q copygroup active
• tsm STORAGESVRgt q copygroup
• tsm STORAGESVRgt q copygroup RMAN
• tsm STORAGESVRgt q copygroup RMAN active
• tsm STORAGESVRgt q copygroup rman active
  formatdetail

25
Copy Group tsm STORAGESVRgtq copygroup
rman active
verexists verdeleted retextra retonly Policy
Policy Mgmt Copy Versions
Versions Retain Retain Domain Set
Name Class Group Data Data
Extra Only Name Name Name
Exists Deleted Versions Version ------
--------- ----- ----- --------
-------- -------- ------- RMAN ACTIVE
DISK STANDARD 2 0 30
0 RMAN ACTIVE STANDARD STANDARD
2 0 30 0 RMAN
ACTIVE TAPE STANDARD 2 0
30 0 RMAN_TEST ACTIVE STANDARD
STANDARD 2 0 30
0 Setting Options Type Backup or Archive
(Backup only in the case of RMAN)    
Destination Storage Pool that will hold backup
objects     Retention Options Verexists,
verdeleted,retextra, retonly RMAN Backup
Copy Group Settings verexists1
verdeleted0 retextra0 retonly0
26
RMAN Backup Copy Group Option Definitions
When Tivoli Storage Manager makes a backup of a
file it refers to the new backup of that file as
the active version. The previous backup version
becomes inactive. Verexists - Maximum of
inactive backup versions to keep if an active
version of the file exists Verdeleted Maximum
of versions to keep if the file no longer exists
on the client machine Retextra Maximum of
days to keep inactive versions. The clock does
not start ticking at the time the file is backed
up. It starts ticking when the file becomes
inactive because it has reached the verexists or
verdeleted limit. Retonly - of days to keep
the final remaining inactive versions of a file
from the time they were made inactive if active
versions of the file do not exist RMAN Backup
Copy Group Settings verexists1
verdeleted0 retextra0 retonly0
AN
27
Important Tivoli Retention Tidbits

• RMAN backup pieces each have unique names,
  therefore each RMAN Tivoli backup is considered a
  new and active version
• Active versions never expire from the Tivoli
  Storage Manager database so RMAN must manage the
  deletion of the backups when they exceed their
  retention limits
• The call to Tivoli that deactivates objects is
  called dsmDeleteObj. It may be what is used by
  RMAN to remove obsolete entries.
• When an expired backup file reaches it's
  retention limit the file does not physically get
  removed from tape by Tivoli Storage Manager. An
  expiration job marks that space available for
  subsequent backups
• If the file was backed up by RMAN to disk then
  RMAN in 9i has the ability to remove the physical
  file from disk during retention maintenance

28
Perform RMAN Repository Maintenance

• 1. Set "Deleted" Flag in RMAN catalog for
  backups that reached retention limits
• RMANgt configure retention policy to recovery
  window of 90 days
• RMANgt allocate channel for maintenance device
  type sbt_tape
• RMANgt report obsolete
• RMANgt delete obsolete
• In 9i the delete command deletes the physical
  files associated with the backup sets (if
• on disk) and datafile copies, updates the
  statuses in the controlfile, and removes the
• backup entries from the optional recovery
  catalog.

29
2. Periodically Purge Deleted Backups from RMAN
and Tivoli Repositories
Connect to recovery catalog as target database
owner SQLgt _at_ORACLE_HOME/rdbms/admin/prgrmanc AL R
LH BP BS BCF BDF BCB BRL CCF CDF CCB XCF
(for 8.1) XDF (for 8.1)
30
Output of RMAN Delete Obsolete Process
export TDPO_OPTFILE/cprod/rman/bin/tdpo_db1.opt
export ORACLE_SIDdb1 rman
target / catalog rcat_db1/ltpasswdgt_at_catalog_databas
e connected to target database db1
(DBID2243743003) connected to recovery catalog
database RMANgt configure retention policy to
recovery window of 90 days new RMAN
configuration parameters are successfully
stored starting full resync of recovery
catalog RMANgt configure default device type to
sbt_tape new RMAN configuration parameters are
successfully stored starting full resync of
recovery catalog full resync complete Cont.
31
Output of RMAN Delete Obsolete Process cont.

• RMANgt allocate channel for maintenance device
  type sbt_tape
• RMANgt list backup completed before 'SYSDATE-90'
• RMANgt delete obsolete
• RMAN retention policy will be applied to the
  command
• RMAN retention policy is set to recovery window
  of 90 days
• Do you really want to delete the above objects
  (enter YES or NO)? YES
• Deleted 188 objects

32
Verify RMAN Backup Entries Have Been Deleted from
Catalog
SET LINESIZE 120 COL name FORMAT a8 COL
backup_type FORMAT a15 COL Start_Time FORMAT
a20 COL Completion_Time FORMAT a20 COL status
FORMAT a7 COL handle FORMAT a15 SELECT i.name,
DECODE (s.backup_type, 'L' ,
'ArchLogs' , 'D', 'Datafiles', s.backup_type)
backup_type, s.status,
p.handle, s.bs_key,
TO_CHAR(s.start_time, 'dd-MON-yy hh24mi')
Start_Time, TO_CHAR(s.completio
n_time, 'dd-MON-yy hh24mi') Completion_Time
FROM rc_backup_set s, rc_backup_piece p,
rc_database_incarnation i WHERE s.bs_key
p.bs_key AND i.current_incarnation
'YES' AND TRUNC(s.completion_time) lt
TRUNC(sysdate) - 91 AND
s.completion_time p.completion_time
AND s.db_id p.db_id ORDER BY
s.completion_time DESC /

33
Verify RMAN Archive Log Entries Have Been Deleted
from Catalog
SET LINESIZE 120 COL name FORMAT a8 COL
Completion_Time FORMAT a20 COL status FORMAT
a7 COL fname FORMAT a40 SELECT fname,
completion_time, status FROM al WHERE
TRUNC(completion_time) lt TRUNC(SYSDATE)-90 ORDER
BY completion_time DESC /

34
Verify RMAN Entries Have Been Deleted from
Storage Server

dsmadmc username user password tsm
STORAGESVRgt SELECT ll_name, node_name,
filespace_name, state, owner FROM backups where
NODE_NAME'MSPR1_RMAN' AND BACKUP_DATEltts
'2004-12-14 000000' gt eraseme.txt cat
eraseme.txt
35
Tivoli Storage Manager Dictionary Tables
SYSCAT.TABLES Contains information about all
tables that can be queried with the SELECT
command. SYSCAT.COLUMNS Describes the columns
in each table. SYSCAT.ENUMTYPES Defines the
valid values for each enumerated type and the
order of the values for each type.
36
Recovery Catalog Views
RC_ARCHIVED_LOG RC_BACKUP_CONTROLFILE RC_BACKUP_CO
RRUPTION RC_BACKUP_DATAFILE RC_BACKUP_PIECE RC_BAC
KUP_REDOLOG RC_BACKUP_SET RC_BACKUP_SPFILE RC_CHEC
KPOINT RC_CONTROLFILE_COPY RC_COPY_CORRUPTION RC_D
ATABASE RC_DATABASE_BLOCK_CORRUPTION RC_DATABASE_I
NCARNATION RC_DATAFILE RC_DATAFILE_COPY RC_LOG_HIS
TORY RC_OFFLINE_RANGE RC_PROXY_CONTROLFILE RC_PROX
Y_DATAFILE RC_REDO_LOG RC_REDO_THREAD RC_RESYNC RC
_RMAN_CONFIGURATION (Example SELECT FROM
rcat_db1.rc_rman_configuration) RC_STORED_SCRIPT
RC_STORED_SCRIPT_LINE RC_TABLESPACE
37
Questions?
38
Thank You!