Transcript Sharing experience on RMAN backups ... Sydney Oracle Meetup #14 Yury Velikanov Member of Sydney’s Oracle Community.

Sharing experience on RMAN backups ...
Sydney Oracle Meetup #14
Yury Velikanov
Member of Sydney’s Oracle Community
General Guidelines & Concepts
• Typical backup flow
• Nightly/Weekly/Monthly (depends on the DB size)
{rARCHs + CTL} + {FULL DB BACKUP + ARCH + CTL}
• Each 4h/2h/1h/15min (depends on ARHC volumes)
{rARCHs + CTL}
• To reduce backup’s volumes (size)
• Some Nightly/Weekly might be replaced by
{INCLEMENTAL}
©The Pythian Group
Company Confidential
RMAN: Minimal set of commands
Nightly/Weekly/Monthly
backup archivelog delete input;
archivelog delete input;
backup database;
database plus archivelog;
backup current controlfile;
backup archivelog delete input;
Each 4h/2h/1h/15min
restore controlfile;
backup
restorearchivelog
database; delete input;
backup
recoverdatabase;
database;
backup
current controlfile;
alter database
open;
©The Pythian Group
Company Confidential
Starting an RMAN session & more
set NLS_DATE_FORMAT=YYYY/MM/DD HH24:MI:SS
rman target /
list backup;
show all; -- could be used to restore cfg
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT =
'C:\oracle\backup\df0_%d_%s_%t';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP
TYPE TO COMPRESSED BACKUPSET;
©The Pythian Group
Company Confidential
RMAN: Advanced set of commands
BACKUP ${v_compress} ${v_files_per_set} ${v_max_set_size} ARCHIVELOG ALL
NOT BACKED UP ${v_num_arch_bkps} TIMES FORMAT '${v_backup_archlog_format}'
${v_del_input};
BACKUP ${v_compress} ${v_files_per_set} ${v_max_set_size} ARCHIVELOG FROM
SEQUENCE ${v_init_sequence_to_start_with} FORMAT
'${v_backup_archlog_format}' ${v_del_input};"
DELETE NOPROMPT ARCHIVELOG ALL BACKED UP $v_del_arch_copies TIMES TO DISK
COMPLETED BEFORE 'TRUNC(SYSDATE-1/(${p_del_arch_oldr_h}*24))';
BACKUP ${v_incremental} ${v_compress} ${v_files_per_set} ${v_max_set_size}
DATABASE FORMAT '${v_backup_database_format}';
BACKUP ${v_compress} CURRENT CONTROLFILE
'${v_backup_controlfile_format}';
FORMAT
BACKUP ${v_compress} SPFILE FORMAT '${v_backup_spfile_format}';"
©The Pythian Group
Company Confidential
Implementation Constraints
• Are we integrating backups with existing company wise
solution? Or backup infrastructure dedicated to Oracle DB
backups?
• File system or Tape backups
• Retention policy management (RMAN or MML)
• How many Oracle DBs in the organization?
• RMAN Repository
• Do we have Oracle RMAN integrated MML solution?
• File system or Tape backup
• Do we have Enterprise Edition or Standard Edition
• ...
©The Pythian Group
Company Confidential
6
RMAN Catalog DB consideration
• Use Control file only
•
•
•
•
•
IMHO Good choice for most of the cases
Why to manage an additional database?
Default option starting from 9i?10G?
Organizations where retention policy managed by MML
Database count is low
• Why use Control file & DB Repository?
•
•
•
•
•
•
Just to have a fun and keep us busy :)
If your organization have many Oracle Databases
If you use OEM databases and it treated as critical resource
If you need to lock some of your backups
If you write a backup script test connection to catalog DB
There might be a strange synchronization issues
©The Pythian Group
Company Confidential
Backups Performance Optimization
•
Empty block skipping (8i)
•
•
•
Compressed backups (10g)
•
•
•
•
Stores 1 block for 4 empty block
Applicable to blocks above High Water Mark only
Might improve performance 2-4 times if MML is slower then CPU
Doesn’t improve performance if CPU are slower then MML
Advanced Compression in 11g (additional licence)
Parallel backups (8i)
•
•
•
©The Pythian Group
Improves performance significantly if enough IO resources
Might be very powerful in conjunction with compression
Might kill overall system performance as tend to use all resources
available
Company Confidential
Backups Performance Optimization
•
Incremental backups (9i)
• Improve speed of backups if MML is a a performance
bottleneck
•
Block change tracking (10g)
• Increase a speed of incremental backups dramatically
• Introduce a slight overhead on regular operations
•
Read Only tablespaces
•
•
CONFIGURE BACKUP OPTIMIZATION ON;
Standby database
©The Pythian Group
Company Confidential
Recovery Performance Optimization
• If you use tape backups:
• Use as small backup sets as reasonable
• Depending on MML implementation: one file restore from
a backup set (e.g. control file) reads entire backup set
• Image copy updatable by incremental backups
• Automatic or Manual standby database
• Manual in case of Standard Edition
©The Pythian Group
Company Confidential
Real World Case 1 (Zipping bck sets)
• Constraints
• Company wide backup policy/procedures in use
• Each server has NFS mounted directory where backups are stored
• Each night centralized backup solution backups files in the directory and remove
those straight after backup is completed (excluding *.tmp files only)
• “Wrong” Solution
• RMAN fromat => *.tmp files
• Zip scripts triggered after backup, compressing and removing .tmp
• log_archive_dest => NFS; backup script => backup and remove ARCHs
• “My” Solution :)
• RMAN makes backups in *.rman.tmp files
• As soon as backup completed "mv *.rman.tmp *.rman“
• Uncatalog / Catalog backup sets
©The Pythian Group
Company Confidential
Real World Case 2 (Could we restore?)
• Description
• Accidentally found that Media Management Layer software
metadata stored on the server where backup has been taken
• Metadata RMAN Handle map to MML handles
• It wouldn’t be possible to restore backups on the other server or if
the original server lost complicity
• Solution & Take Away
• Test recovery on other server than backups were taken from
• Keep MML metadata & log files as part of you backups
©The Pythian Group
Company Confidential
Real World Case 3 (Extreme Restore)
• Description
• 3-d party financials application
• Application’s module used to manage Oracle structures &
parameters
• An administrator decided to change PCTUSER parameter for one
of the main transactions table
• He used (as instructed) the application’s module
• Instead of 1-2 seconds the operation took 10 -15 min
• The administrator interrupted the operation
• Application’s module cleaned up source and target structures
• It appears that last valid backup was taken 6 months ago
• Solution & Take Away
• Oracle Logminer used to restore the table
• Use your imagination and be creative
©The Pythian Group
Company Confidential
Archivelogs backup management
•
For strange reasons some DBAs tend to leave ARCHs for some time
on a DB server file system (after backup)
•
In some organizations ARCHs’ backup are separated from data files
backups
•
•
•
IMHO: Relative ARCHs should be part of data files backups and
stored using the same policy
•
•
Data files by RMAN
ARCHs backed up and removed by OS scripts
ARCHs regular backups should be suspended before Data Files backup
starts
Some one might say that ARCHs should be stored in more secure
place than data file backups. You may chouse to store those twice :)
©The Pythian Group
Company Confidential
Practical advice
• Don’t change backup set location if possible
• Often ZIP backup sets
• RMAN Log files should be part of backup
• Record Begin/End SCN’s and the log file
• Record Timing information
• Spool DB and MML version information
• May spool file current file structure
• spool “show all;” command output in the beginning
• Specify data/arch/ctl/sp as part of backup set name
©The Pythian Group
Company Confidential
Open Discussion ...
• Media Management Layer integration
• Retention policy management
• Flash recovery area
• …
©The Pythian Group
Company Confidential
Thank you