Backup Recovery with RMAN LCG 3 D Workshop
Backup & Recovery with RMAN LCG 3 D Workshop, Bologna June 12 th, 2007 Jacek Wojcieszuk CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG
Agenda • • • Types of failures and backups in Oracle RMAN Architecture Manual vs. RMAN backups On-tape backups with RMAN Configuration RMAN backup strategies RMAN backups at Tier 0 Exemplary recovery scanarios Demo CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 2
Types of failures • Instance Failure – Usually connected with an Oracle process failure • Media Failure – Disk failure, storage array controller failure etc. • Block Corruption – Usually caused by bugs in Oracle software • Human error – In most cases accidentally deleted/updated data – Database user or DBA • Disaster – Fire, flood, earthquake, plane crash etc. CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 3
Backup options in Oracle • Physical backups – Cold (off-line) backups • Full database only • Require downtime • Do not provide flexibility for point in time recovery (Pi. TR) – Hot (on-line) backups CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it • Different types of backups: full, incr. (cumulative, differential), archivelogs • Different scopes: full database, tablespace(s) or datafile(s) • Do not require database downtime • Can be used to recover full database, single/multiple tablespace(s)/datafile(s) or a corrupted block • Database can be recovered to any point in time within assumed backup retention period LCG 3 D Workshop, Bologna, June 2007 - 4
Backup options in Oracle (2) • Logical backups – Logical copy of data in the database – Support for different backup granularity – Can be taken either with legacy Export/Import tools or with Data Pump (10 g) • Standby systems (Data Guard) – Physical and logical standby databases CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 5
How hot backups are possible? DBWR Db file LGWR Redo. Log Log archiver Db file Backup proc. Server proc. SGA Tapes or disks system Archived redo logs CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 6
RMAN architecture Target Database RMAN Client Target Database Auxiliary Database RMAN Catalog Schema Catalog Database CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 7
Types of RMAN hot backups • Copy or backupset • Full database backup • Incremental backups (in 10 g 2 levels available: 0 and 1) – Cumulative, differential – Can be used to update a copy of the database • Archivelog backups • Tablespace(s), datafile(s) backups CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Differential incremental Cumulative incremental LCG 3 D Workshop, Bologna, June 2007 - 8
Manual vs. RMAN backups • RMAN advantages: – Supports incremental backup strategies – RMAN on-line backups are not so heavy for the system as manual on-line backups – RMAN can detect corrupted blocks – RMAN automatically track database structure changes – Provides easy, automated backup, restore and recovery operations – Keeps invenotory of taken backups – Can seamlessly work with third party media managers • Disadvantage: something new to learn – RMAN concepts and command syntax sometimes are not intuitive CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 9
On-tape backups with RMAN • RMAN allows to take on-disk backups out of the box – Flash recovery area, if configured, further simplifies such backups – On disk backups are interesting but usually not sufficient for a disaster recovery • On-disk backups can be manually sent to tapes – Recovery can be very troublesome • RMAN can seamlessly work with third party Media Managers – Media Manager Library (MML) is required – Different configuration tasks for different MMLs CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 10
On-tape backups with RMAN (2) • Many vendors of Media Management software provide MMLs • Most popular are: – Tivoli Storage Manager – Veritas Net. Backup – EMC Net. Worker • Oracle Secure Backup – Very new and probably not yet reliable enough CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 11
RMAN Configuration • RMAN can be preconfigured – Configuration is stored in the control file and in the recovery catalog (if used) – Can facilitate backup automation • Most useful settings: Setting Default Recommended Controlfile autobackup off on Retention policy to redundancy 1 to recovery window of 31 days Device type disk parallelism 1. . . disk|sbt prallelism 2. . . Default device type to disk to sbt Backup optimization off Channel device type none sbt parms=‘. . . ’ Maxsetsize unlimited depends on your database size CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 12
RMAN Configuration (2) • Example: configure RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS; configure DEFAULT DEVICE TYPE TO 'sbt'; configure DEVICE TYPE 'sbt' PARALLELISM 2; configure CHANNEL DEVICE TYPE ‘sbt’ parms='ENV= (TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo. opt)'; configure DEVICE TYPE DISK PARALLELISM 2; configure MAXSETSIZE TO 200 G; configure archivelog backup copies for device type 'sbt' to 1; configure controlfile autobackup on; • The SHOW ALL command lists all RMAN configuration settings • To clear a given settings append CLEAR at the end of the CONFIGURE command CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 13
RMAN backup strategies • RMAN allows many types of backups • It possible to build own backup strategy that suits given database best • There also Oracle recommended backup strategies: – Incremental backup strategy: level 0 backups + level 1 backups (cumulative and/or differential) and archivelog backups inbetween. – Incrementally updated database copy: 1 backup as copy of the whole database + incremental backups used to update the copy + archived redo logs. CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 14
Backup strategy used at Tier 0 • Both Oracle-recommended strategies implemented for all production systems • Incremental backup strategy: – Backups go to tapes – Weekly or biweekly level 0 backups (depending on the DB size) – A level 1 cumulative backup inbetween – Daily incremental level 1 differential backups – Archivelog backup every 30 minutes • Incrementally updated DB copy strategy: – daily incremental differential backups applied with 2 days of delay – Copies, incremental backups and archived redo logs stored in the Flash Recovery Area CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 15
Backup operations at Tier 0 • Central machine to schedule and run all the backups • Central RMAN catalog exported on regular basis • Examples of RMAN commands being used: run { crosscheck archivelog all; backup force tag ‘some_tag_0 T' incremental level 0 check logical database force format '%d_%T_%U_lvl 0 T' plus archivelog format '%d_%T_%U_lvl 0 Tarch'; delete noprompt force archivelog all completed before „sysdate - 2"; } CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it run { crosscheck archivelog all; backup tag ‘some_tag_1 D' incremental level 1 database format '%d_%T_%U_lvl 1 D' plus archivelog format '%d_%T_%U_lvl 1 Darch'; delete noprompt force archivelog all completed before ‘sysdate – 2’; } LCG 3 D Workshop, Bologna, June 2007 - 16
Backup operations at Tier 0 (2) run { backup tag ‘some_tag_AR' archivelog all format '%d_%T_%U_arch'; delete noprompt force archivelog all completed before ‘sysdate - 2’; } run { backup maxsetsize 2047 G tag ' DB_Copy_tag' device type disk incremental level 1 for recover of copy with tag ‘DB_Copy_tag’ database; recover device type disk copy of database with tag DB_Copy_tag until time ‘sysdate -2 ‘; delete force noprompt backupset device type disk tag DB_Copy_tag completed before ‘sysdate -2’; } CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 17
Complete database recovery • Needed when: – All datafiles are lost or the SYSTEM tablespace datafiles are lost – At least one member of each redo log group survived • Requires: – Control file recovery (if it’s lost) – Datafile restore from a backup – Database recovery using incremental backups and/or archived redo logs and online redo logs startup mount run { allocate channel c 1 device type disk|sbt; allocate channel c 2 device type disk|sbt; restore database check readonly; recover database; alter database open; } CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 18
Database point-in-time recovery • Needed when: – all datafiles are lost – All copies of the current control file are lost – Or all online redo log group members are lost • The most typical recovery in case of systems implementing SAME approach • If done after a disaster it has to be preceded by: – Hardware configuration – OS and Oracle software installation – Re-creation or restore from non-RMAN backup of listener. ora, tnsnames. ora and other important configuration files – ASM instance and diskgroup configuration (if needed) – MML installation and configuration –. . . • Requires – Spfile restore – Controlfile restore – Datafiles restore and recovery CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 19
set dbid=xxxxxxx startup nomount; run { allocate channel c 1 device type disk|sbt; restore spfile to ‘some_location’ from autobackup; recover database; alter database open resetlogs; } shutdown immediate; startup nomount; run { allocate channel c 1 device type disk|sbt; restore controlfile from autobackup; alter database mount; } CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it run { set until time = "to_date('24 -AUG-2006 00: 00', 'dd-mon-yyyy hh 24: mi: ss')"; allocate channel c 1 device type disk|sbt; allocate channel c 2 device type disk|sbt; restore database check readonly; recover database; alter database open resetlogs; } LCG 3 D Workshop, Bologna, June 2007 - 20
Tablespace point-in-time recovery • Needed – Mainly to address a human error • Oracle makes efforts to automate it – Can be done with few clicks in OEM • Requires – Point in time recovery of the whole database (an auxiliary instance is created) – Export/import of selected tablespaces schemas or objects CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 21
Block media recovery • Needed when: – Database reports either single or multi block corruption • Can be done with an open database • Only the table(s) containing corrupted blocks are not available • Database corruptions can be discovered with RMAN backup validate database command • Corrupted blocks can be found in V$DATABASE_BLOCK_CORRUPTION ORA-01578: ORACLE data block corrupted (file # 19, block # 44) ORA-01110: data file 19: ‘d: oracleoradatamydb_maintbs_01. dbf’ run { allocate channel c 1 device type disk|sbt; blockrecover datafile 19 block 44; } CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 22
Single/multiple datafile/tablespace recovery • Needed when – Single/multiple tablespaces or datafiles have been lost – SYSTEM tablespace is intact – Controlfiles and online redo logs are intact • Requires – To put offline datafiles and tablespaces being recovered – The database can be open and available to users run { sql „alter tablespace users offline”; allocate channel c 1 device type disk|sbt; restore tablespace users; recover tablespace users; sql „alter tablespace users online”; } CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 23
Demo CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 24
Hands on preparation • https: //twiki. cern. ch/twiki/bin/view/PSSGroup /Hands. On. Exercises • Please start a full backup of your database, if possible use a tape system CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 25
More info • Oracle Database 10 g RMAN Backup & Recovery (by Mathew Hart and Robert G. Freeman) • Oracle Documentation – Backup and Recovery Basics – Backup and Recovery Advanced User's Guide – Backup and Recovery Reference CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it LCG 3 D Workshop, Bologna, June 2007 - 26
- Slides: 26