Compelling Reasons to Use Oracle9i R2 RMAN

1. Session id 36487: Compelling Reasons to Use Oracle9i R2 RMAN Wayne LintonOracle Database Administrator Shell Canada Limited

2. whoami • Wayne Linton, I.S.P. • 30+ years Shell Canada Limited • 30+ year industry, 10+ years Oracle • OCP certified from 7.3 through 9i • past president Calgary Oracle Users Group • published in Select, Oracle magazines • part time Oracle DBA instructor at Southern Alberta Institute of Technology

3. Agenda • brief introduction to server managed backup/recovery and compelling reasons to start using RMAN • for users of 3rd Party backup tools, THE compelling reason to switch to RMAN • for pre-9i R2 users of RMAN, compelling reasons to upgrade! • 9iR2 RMAN backup demo • 9iR2 RMAN block recovery demo

4. RMAN at Shell Canada Solaris 9203 (1) Solaris9203 rdbmsRMANCatalog9203 Solaris 9203 (2) sqlnet Solaris 817 (5) NT4 817 (1) AIX 817 (3) staggered backupschedules toprevent deadlocks AIX 817 (1) 816 (2) AIX 817 (1) 816 (2)

5. Backup for Recovery • one day you WILL have to recover • do you know what backups are needed ? • do you know where they are located ? • are you certain that the backups are good ? • do you have all the required archive logs ? • are your recovery skills a little rusty?

7. A Matter of Trust ? You Trust the Managementof your Data to Oracle ... Why not Trustthe Management of your Backup and Recovery to Oracle?

8. What Is RMAN? • Recovery Manager • Oracle utility to backup, restore, recover databases from Oracle 8+ • invoked from command line or Enterprise Manager GUI • is aware of Oracle database internals • tracks location of all backup files needed for recovery (in controlfile or separate catalog) • “server managed” backup and recovery

9. RMAN - Server Managed • RMAN uses Oracle-aware server processes to perform backup and recovery operations • fully integrated with database, fully supported • knows what backups to take, have been taken, and where they are located (cfile or catalog) • knows what backups are needed for recovery, and restores the required backup files to the right location automatically

10. RMAN Answers the Questions: Which backups do I need?Where are they? Can I recover?

11. #1 Fully Integrated with Database Fully Supported by Oracle • aware of internals so can optimize for performance • hot backup handling of fractured blocks • skips backup of unused / unchanged blocks • standby backups fully interchangeable with primary (can offload backup load to standby machine!) • can create clone and standby databases (duplicate command) • knows about RAC

12. #2 Supports Incremental Backups • smaller backups and reduced backup/recovery time • reads every block and checks for corruption • multi-level incremental and cumulative backup schemes possible • backups to disk (faster restore) or tape directly • can even do ‘cold’ (mounted) incremental backups!

13. Incremental Backups • Time/space depends upon amount of change archivelogs space needed changed block backup writes backuptime db block read / corruption checking % db block change

14. #3 Online Backups Simply & Efficiently • no need for begin-end backup commands • fractured (changed) blocks re-read by RMAN during online backups • no additional redo generated • multiple output streams (channels) in parallel • multi-file read, block interleaving to one backupset • current redo log switched, archived, and backed up as part of backupset automatically

15. #4 Capture and Reporting of Backup Metadata • controlfile contains relevant RMAN backup metadata (whether you use RMAN or not!) • controlfile size governed by parameter control_file_record_keep_time = #days • LIST datafile, archivelog backup information • REPORT unrecoverable, report obsolete, etc. • recommended (but optional) use RMAN Catalog to also store backup metadata for multiple databases • backup will use controlfile if catalog not reachable

16. 3rd Party Users • 3rd party tools differ little from RMAN • have nicer GUI than OEM ? • BUT cost increases with size or number of databases or servers where software used • why pay a lot of money for functionality you already have?

17. #5 RMAN is FREE!! • included in database license • comes with Legato MML software to write to tape • can purchase MML software from preferred third-party media management vendor • see http://otn.oracle.com/deploy/availability for members of the Backup Solutions Program

18. Pre-9i users of RMAN • early versions of RMAN were somewhat awkward to use • verbose scripting and messages • lacking in ‘basic’ functionality like deletion of obsolete O/S backup files • 9i and 9iR2 have significantly improved usability and functionality

19. #6 Persistent Configuration Parameters • RMAN remembers backup settings from one session to another (in controlfile, catalog), making routine backups as simple as … BACKUP DATABASE; • set once and forget about them • can configure many parameters ...

20. 8i vs 9i RMAN Backup Script run { allocate channel d1 type disk; allocate channel d2 type disk; allocate channel d3 type disk; sql "alter system switch logfile"; backup incremental level 0 format '$HOME/BACKUP/%d_%t_%s_%p.bus' filesperset 5 (database include current controlfile); sql "alter system archive log current"; backup format '$HOME/BACKUP/%d_%t_%s_%p.bus' (archivelog all delete input); backup format '$HOME/BACKUP/%d_%t_%s_%p.bus' (current controlfile); release channel d1; release channel d2; release channel d3; } backup database incremental level 0 plus archivelog delete all input;

21. #6a Configure Retention Policy • by backup redundancy count or number of days recovery window, will determine how many backups to keep for how longCONFIGURE RETENTION POLICY … • then the obsolete OS backup files are physically removed by the command: DELETE OBSOLETE;

22. #6b Auto Channel Allocation & Parallelism • channels are allocated automatically • no need to code each channel separately • number of channels allocated specified with parallelism parameter CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

23. #6c Controlfile, SPFILE Auto Backup • if set to ON, controlfile and spfile backed up together in separate backup piece with every backup CONFIGURE CONTROLFILE AUTOBACKUP ON; • controlfile and spfile always and automatically backed up with datafile #1 (system) if autobackup set to OFF • note that spfile automatically backed up, NOT pfile!

24. #6c Controlfile, SPFILE Auto Backup cont’d ... • if set to ON, controlfile and spfile backed up automatically whenever the database structure is changed • new, dropped, offlined, onlined tablespace, datafile, redo logs, renamed file, etc. • no need to invoke RMAN for this to happen • auto-backup event noted in the alert log

25. #6d Other Noteworthy Parameters • skip read-only tablespaces already backed-up withCONFIGURE BACKUP OPTIMIZATION ON; • exclude specific tablespaces with CONFIGURE EXCLUDE FOR TABLESPACE xxxxxx; • create up to 4 backup copies withCONFIGURE BACKUP COPIES TO n;

26. #7 Auto Archive and Backup Current Redo • no need to manually switch, backup current log • one copy of all archived logs backed up, current log switched, archived, and then also backed up • guaranteed to save all requisite redo for recovery BACKUP DATABASE PLUS ARCHIVELOG;

27. #8 Clearing Multiple Archivelog Destinations • pre-9i supported clearing only 1 archivelog destination • multiple archivelog destinations searched if necessary to obtain good log copy for backup • all archivelog destinations cleared after backup with command: BACKUP DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;

28. #9 Less Verbose Runtime Messages • every line of pre-9i RMAN output was prefixed with RMAN-xxxxx, plus additional less useful informational messages obscured the ‘good stuff’ • these can be optionally turned on, but are supressed by default • errors will still be prefixed with RMAN-xxxxx • scan the log for RMAN-00569

29. D E M O N S T R A T I O N RMAN Incremental Backup … in progress

30. Demonstration • show all; (persistent configuration parameters) • level 2 incremental backup: backup incremental level 2 database plus archivelog delete all input; • list backup of tablespace WAIN; • report unrecoverable; • delete obsolete;

31. #10 Block Media Recovery • no need to offline and restore large datafile when only a few blocks are corrupted • backup / validate places location of corrupted database blocks in view: v$database_block_corruption • alert log also lists corrupted file#, block# BLOCKRECOVER CORRUPTION LIST;

32. D E M O N S T R A T I O N RMAN blockrecover

33. Demonstration • report unrecoverable; • select * from v$database_block_corruption; • corrupt tablespace wain datafile • backup validate check logical tablespace wain; • select * from v$database_block _corruption; • blockrecover corruption list; • backup validate check logical tablespace wain; • select * from v$database_block _corruption;

34. #11 Restartable Backups and Restores BACKUP DATABASE NOT BACKED UP SINCE TIME … • Restore failures may leave good versions of some datafiles in the right place • restarted restore will NOT restore those files • can force them to be restored anyway with: RESTORE FORCE ...

35. #12 Staging of Archivelogs During Recovery • sometimes you don’t have enough disk space to restore all the archived redo logs required for recovery (in this example, only 100M) • new feature automatically ‘stages’ restore of archived logs as per available disk, applies them, and deletes them to make room for next set of logs RECOVER DATABASE DELETE ARCHIVELOG MAXSIZE 100M … ;

36. #13 Backing Up Backupsets • backupsets can reside on disk for faster restore • older backupsets can be backed up to tape and deleted automatically from disk to make room for new backups on disk • backupset backups are also checked for corruption BACKUP BACKUPSET … CREATED BEFORE … DELETE INPUT;

37. #14 Other Important RMAN Functions • recover to previous ‘incarnations’ if using catalog • automatic ‘tagging’ of backups • DBNEWID to change id of ‘manually’ cloned DB • OEM GUI wizard improvements • ‘automated’ TSPITR for self-contained tablespaces • DUPLICATE command to build clone and standby databases • BACKUP and RESTORE VALIDATE

38. My Personal Favourites • Configure Retention Policy (Delete Obsolete!) • Configure Controlfile Autobackup • Other Persistent Configuration Parameters!!!!! • Block Media Recovery while still Online! • Backup Database Plus ArchivelogDelete All Input • Recover Database Delete Archivelog Maxsize • Restartable Backups and Restores

39. Upon The Shoulders of ... • Tammy Bednar, Oracle Corporation Database Backup & Recovery Strategies and Best Practices - paper 213, OOW 2001 • Tammy Bednar, Oracle CorporationRich Bernat, Chevrontexaco CorporationAre You Using the Best Solution for Protecting Your Enterprise? - paper 32513, OOW 2002

40. Upon The Shoulders of ... • Dave Anderson, SkillBuilders, Inc.A Practical Introduction to Oracle9i RMANOratechs, 2003

41. Next Steps…. • Recommended sessions • 40104 RMAN 10g: Reloaded - Tammy BednarTues. 9th, 3:30 PM Moscone Room 304 • 36675 RMAN 10g & ATA Storage in Action - Bob NgWeds. 10th, 8:30 AM (not sure which room) • Recommended hands-on labs • Manage the Database Environment, Lesson 6Performing Backups and Recovering Your Database • Demogrounds • 10g RMAN (many!) New Features, new EM interface

42. Next Steps…. • Relevant web sites to visit for more information • Metalink - many technical notes, articles, papers • OTN & Oracle Documentation • Oracle9i Recovery Manager User’s Guide - A96566-01 • Oracle9i Recovery Manager Reference - A96565-01 • Oracle9i Recovery Manager Quick Reference - A96564-01 • Oracle9i RMAN Backup and Recovery,Freeman & Hart, ISBN 0-07-222662-5

43. Reminder – please complete the OracleWorld online session surveyThank you.

44. Q & Q U E S T I O N S A N S W E R S A

45. Session id 36487: Compelling Reasons to Use Oracle9i R2 RMAN Wayne LintonOracle Database Administrator Shell Canada Limited wayne.linton@shell.ca