RMAN: Beginner to Advanced in 1 Hour Arup Nanda (c) Arup Nanda
Who am I • Oracle DBA for 16 years and counting • Speak at conferences, write articles, 4 books • Brought up the Global Database Group at Starwood Hotels, in White Plains, NY (c) Arup Nanda 2
What’s Being Covered • • RMAN Basics Merge Backups Block Change Tracking Monitoring Compression Encryption Key/Wallet Management Decisions – Image Copy or Backupset (c) Arup Nanda 3
What is it? • RMAN: Recovery MANager • Oracle Enterprise Backup Utility (EBU) with Oracle 7 – Bugs, limitations made it infeasible • RMAN – the new incarnation of EBU introduced with Oracle 8 – Bugs abound; DBAs still favored Hot Backup – Bug free, quite usable with Oracle 8 i (c) Arup Nanda 4
What was the Earlier Approach • Put the database/tablespaces/datafiles to backup mode. – ALTER DATABASE BEGIN BACKUP; – Copy/ftp the files to remote location – ALTER DATABASE END BACKUP; • The datafile headers are not updated with the newer checkpoints • The datafiles are written as usual (c) Arup Nanda 5
What was the problem? 1. Fractured block • More redo written during this period 2. Recovery will apply more redo • The recovery will take longer (c) Arup Nanda 6
RMAN • Does not need – ALTER DATABASE BEGIN BACKUP • RMAN understands the structure of the data block. So it does not: – need to stop the SCN update in the header – generate too much redo – need to apply redundant redo during recovery (c) Arup Nanda 7
RMAN Basics • Executable rman in $OH/bin • Has to connect to a target database # $ORACLE_HOME/bin/rman Recovery Manager: Release … RMAN> connect target / connected to target database: ODBA 112 (DBID=623661443) RMAN> connect target "sys/oracle as sysdba" # $ORACLE_HOME/bin/rman target=/ (c) Arup Nanda 8
Start the Backup The sequence of keywords (commands) is important. Giving wrong commands (or in wrong sequence) make the command fail and you can't edit (unlike SQL*Plus) RMAN> backup database; Semicolon is necessary to end a command Could be database, datafile, tablespace, archivelog all, etc. (c) Arup Nanda 9
Only Used Blocks Backup set Datafile 2 2 1 Backup pieces (individual files) Datafile 1 Used block Unused block Since only used blocks are backed up, the backup is less than the actual database size (c) Arup Nanda 10
Datafiles and Backupsets bp 1 df 1 bp 2 bs 1 bp 3 bs 2 bp 4 bs 3 df 2 df 3 df 4 bp 5 Datafiles Many to Many Backup Pieces (c) Arup Nanda Many to One Backup Sets A datafile can’t be split across backupsets 11
Restoring RMAN> restore database; RMAN> restore tablespace USERS; RMAN> restore datafile 5; • This recreates the datafiles from the different backup pieces RMAN exclusive! • Read and restored in parallel • Recovering (applies the incremental backups and archive logs to the restored datafiles) RMAN> recover database; (c) Arup Nanda 12
Incremental Backups • RMAN checks for used blocks, so it can check if the blocks have been backed up before • It can backup only the changed blocks • Reducing backup size, time, I/O and CPU • Level 1 – changed since Last Level 1 • Level 0 – Full backup (c) Arup Nanda 13
Incremental Types Tuesday 2 3 5 6 1 2 3 4 Monday 1 4 Initial 5 6 1 2 3 5 6 4 Full Backup (all blocks) All blocks Block 2 All blocks Block 3 Full (c) Arup Nanda Level 1 14
Incremental Levels RMAN> backup 2> database 3> ; RMAN> backup 2> incremental level 1 3> database 4> ; Can be replaced by TABLESPACE USERS, DATAFILENanda 1, etc. (c) Arup Full Database Incremental Level 0 indicates a full backup 15
Recovery Backup Sun (Full) Mon Tue Wed Thu Recovery Sun (Full) (c) Arup Nanda 16
Cumulative Incremental Differential Tuesday 2 3 5 6 1 2 3 4 Monday 1 4 Initial 5 6 1 2 3 5 Cumulative Full Backup (all blocks) Block 2 Block 3 Block 2 and Block 3 6 4 (c) Arup Nanda 17
Cumulative Default is Differential RMAN> backup 2> cumulative incremental level 1 3> tablespace users; Cumulative Incremental backups contain all the changes since the full backup; hence it requires only one incremental backup to be applied, speeding up recovery. (c) Arup Nanda 18
Channels • Channels are akin to Parallel Query slaves • Each channel is a session that RMAN uses to backup or restore • More channels, usually faster backup • During backup, each channel uses a unique backupset • Default is one channel (c) Arup Nanda 19
Output of Backup allocated channel: c 1 channel c 1: SID=128 device type=DISK A channel is a session, hence SID allocated channel: c 2 channel c 2: SID=121 device type=DISK Starting backup at 06 -MAY-09 channel c 1: starting full datafile backup set channel c 1: specifying datafile(s) in backup set input datafile number=00001 name=C: ORACLEORADATAARUPLAP 11SYSTEM 01. DBF input datafile number=00003 name=C: ORACLEORADATAARUPLAP 11UNDOTBS 01. DBF channel c 1: starting piece 1 at 06 -MAY-09 channel c 2: starting full datafile backup set channel c 2: specifying datafile(s) in backup set input datafile number=00002 name=C: ORACLEORADATAARUPLAP 11SYSAUX 01. DBF input datafile number=00004 name=C: ORACLEORADATAARUPLAP 11USERS 01. DBF channel c 2: starting piece 1 at 06 -MAY-09 Datafiles are mentioned here (c) Arup Nanda 20
Multiple Channels RMAN> run { 2> allocate channel c 1 type disk format 'c: temp%U. rman'; 3> allocate channel c 2 type disk format 'c: temp%U. rman'; 4> backup database; %U creates a unique name for 5> } the file. Run "block" is enclosed in pairs of curly braces {} (c) Arup Nanda 21
Check Defaults RMAN> show all; Shows all the default configuration items such as number of channels, etc. RMAN> configure. . . changes the items (c) Arup Nanda 22
Configure Channels RMAN> show DEVICE TYPE; RMAN configuration parameters for database with db_unique_name ARUPLAP 11 are: CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default RMAN> configure device type disk parallelism 2 backup type to backupset; . . . new RMAN configuration parameters are successfully stored RMAN> configure channel 1 device type disk format 'c: temp%U. rman'; (c) Arup Nanda 23
NOLOGGING • Nologging Operations – INSERT */+ APPEND */ INTO … – SQL*Loader DIRECT=Y – CREATE TABLE … NOLOGGING • Redo is not Generated for the Data – Metadata changes are always logged • The new information doesn't get into the redo – and hence not to arc logs – And hence not possible to recover • Incremental backs up blocks changed by NOLOGGING operations (c) Arup Nanda RMAN exclusive! 24
Block Change Tracking • Incremental Backup still goes through the files to see which blocks changed • Adds to time and performance • Enter – Block Change Tracking • To create a BCT File: ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/home/oracle/orabackup/bct_PRODB 2. dbf'; • Records which blocks have been changed (c) Arup Nanda 25
BCT, contd. • Must be available to all instances of RAC. • Size approx 10 MB per 1 TB • Test: SELECT * FROM V$BLOCK_CHANGE_TRACKING; • Ensure: select USED_CHANGE_TRACKING from v$backup_datafile; (c) Arup Nanda 26
Checking for Backups Lists the backups available RMAN> list backup of database; using target database control file instead of recovery catalog List of Backup Sets ========== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- -- --------------4 Full 273. 66 M DISK 00: 56 06 -MAY-09 BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG 20090506 T 195902 Piece Name: C: ORACLEPRODUCT11. 1DB 1DATABASE 9 KECC 46_1_1 List of Datafiles in backup set 4 File LV Type Ckp SCN Ckp Time Name ---------- ---4 Full 25170983 06 -MAY-09 C: ORACLEORADATAARUPLAP 11USERS 01. DBF (c) Arup Nanda 27
Which Files Need Backup RMAN> report need backup; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of files with less than 1 redundant backups File #bkps Name --------------------1 0 C: ORACLEORADATAARUPLAP 11SYSTEM 01. DBF 2 0 C: ORACLEORADATAARUPLAP 11SYSAUX 01. DBF 3 0 C: ORACLEORADATAARUPLAP 11UNDOTBS 01. DBF (c) Arup Nanda 28
Obsolete RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of obsolete backups and copies Type Key Completion Time Filename/Handle ------------ ----------Backup Set 1 06 -MAY-09 Backup Piece 1 06 -MAY-09 C: ORACLEPRODUCT11. 1DB 1DATABASE 4 KEBFLM_1_1 RMAN> delete obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=123 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=170 device type=DISK Deleting the following obsolete backups and copies: Type Key Completion Time Filename/Handle ------------ ----------Backup Set 1 06 -MAY-09 Backup Piece 1 06 -MAY-09 C: ORACLEPRODUCT11. 1DB 1DATABASE 4 KEBFLM_1_1 Do you really want to delete the above objects (enter YES or NO)? yes deleted backup piece handle=C: ORACLEPRODUCT11. 1DB 1DATABASE 4 KEBFLM_1_1 RECID=1 STAMP=686145221 Deleted 1 objects (c) Arup Nanda 29
SHOW, LIST or REPORT • List – lists backups, failures, etc. • Report – similar; but less often used • Show – only config items like channels (c) Arup Nanda 30
Block Media Recovery • Less likely: the entire datafile is corrupted • More likely: a specific block in a file is – Why restore the entire file when only a block needs to be recovered? ORA-01578: ORACLE data block corrupted (file # 5, block # 21) ORA-01110: data file 5: 'c: oracle. . . ' RMAN To restore DF# 5 Block# 21: exclusive! RMAN> blockrecover datafile 5 block 21; (c) Arup Nanda 31
Compression • Compresses the backupset produced backup as compressed backupset incremental level 1 tablespace users; RMAN exclusive! • Only for regular backups, not image copies • Compression happens in-line; unlike O/S tools, which first need a file then compress it. (c) Arup Nanda comp. rman 32
Validation • Automatically during backup • Checks for integrity of datafiles and archived logs – Detects corruptions before they get into the disks or tapes – Early warning of corruptions • During merge RMAN exclusive! (c) Arup Nanda 33
Image Copy • Image Copy – Copies all blocks, occupied or not. Can create copies of COPY • Regular RMAN Backup – Copies only used blocks Original Datafile Regular RMAN Image Copy (c) Arup Nanda 34
Image Copies RMAN> backup 2> database 3> ; RMAN> backup 2> as copy 3> database 4> ; Can be replaced by TABLESPACE USERS, DATAFILENanda 1, etc. (c) Arup Regular RMAN Image Copy Creates a copy for each datafile 35
Recovery Scenario Current Sun (Full) Mon Tue Wed Thu Modified Full Full Mon Tue Wed Thu (c) Arup Nanda 36
Merged Backup Database Full Backup Incr. (c) Arup Nanda 37
Merge Backup BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE; RECOVER COPY OF DATABASE WITH TAG WEEKLY; (c) Arup Nanda script r. rman c. rman 38
Image Copy • Backup is simple; RMAN does not have to read the file extensively • Restoring simpler – no “reconstruction” • Merge Possible • Compression not possible (c) Arup Nanda 39
Regular Backups • Smaller Size • May be Quicker • Compression possible When deciding between regular and image copy backups, take these factors into account. (c) Arup Nanda 40
Image Copies Sp lit Non-conventional backups – mirror-split (must use CATALOG) Mirrored Pair Backed up Via RMAN CATALOG DATAFILECOPY ‘/orabcv/dbf 1. dbf’ level 0; (c) Arup Nanda 41
Image Copies - Cases • Partitioned Tables – PART 1 -> Tablespace TS 1, datafile dbf 1 – PART 2 -> Tablespace TS 2; datafile dbf 2 • SQL> alter tablespace TS 1 read only; • Option 1: – Copy the file dbf 1 to /oraback/dbf 1 – Catalog it in RMAN: CATALOG DATAFILECOPY ‘/oraback…’ • Option 2: – RMAN> BACKUP DATAFILE 235 AS COPY; (c) Arup Nanda 42
Image Copies - More Database RMAN> BACKUP. . . AS COPY. . . Backup Location Tape RMAN> backup device type sbt copy of database; 1. Recovery from Tape to Database Directly 2. Backup to the tape is validated 3. Requires MML (c) Arup Nanda 43
Displaying Seconds • Issue on Command Line: export NLS_DATE_FORMAT="mm/dd/yy hh 24: mi: ss" (c) Arup Nanda 44
Validate Archivelogs • To validate: backup validate archivelog all; • Debug (c) Arup Nanda 45
Monitoring • V$SESSION_LONGOPS SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100, 2) "% COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK (c) Arup Nanda rman_stat. sql 46
Effectiveness select device_type "Device", type, to_char(open_time, 'mm/dd/yyyy hh 24: mi: ss') "Open Time", to_char(close_time, 'mm/dd/yyyy hh 24: mi: ss') "Close Time", elapsed_time "Ela", effective_bytes_per_second "b/sec", filename from v$backup_async_io where close_time > sysdate - 30 order by close_time desc; (c) Arup Nanda eff. sql 47
Job Details • V$RMAN_BACKUP_JOB_DETAILS records all jobs present and past • Summary: Script rep 1. sql • Compression: script rep 2. sql • Past Outputs: V$RMAN_OUTPUT rep 3. sql (c) Arup Nanda 48
I/O Effectiveness select io_count, ready, short_waits, long_waits/io_count, filename from v$backup_async_io where io_count != 0 (c) Arup Nanda async. sql 49
Resource Governor • You have limited CPUs and I/O resources – You don't want all the CPU to be taken by RMAN affecting the database perf – You can let RMAN run longer but using less CPU • Minimize Time/CPU (c) Arup Nanda 50
11 g Parallel Backup • Pre-11 g – Backups can run in parallel – But each parallel stream picks a single file • Post-11 g – Each file can be backed in parallel (c) Arup Nanda 51
Surviving Nightmares • Controlfile is missing and you want to recover • Datafile backup is missing • Two databases have the same name but your are restoring only one! • Database name as Unknown • Cloning the production database – on the same host! • How to use extract valuable information from RMAN catalog when the controlfile is missing (c) Arup Nanda 52
Transporting Tablespaces • Transportable – on the same endianness platforms, e. g. Linux to Windows • Cross-platform: RMAN> convert tablespace users 2> to platform 'HP-UX (64 -bit)' 3> format= '/home/oracle/orabackup/%N_%f'; (c) Arup Nanda tts 1. rman 53
Cloning (on the same host) • • • Check memory, space, etc. Create the new INIT. ORA Place the parameters *_convert export ORACLE_SID=CLONEDB 2 Startup nomount Check Source DB is at least mounted (c) Arup Nanda 54
Cloning Contd. • • Put connect strings in TNSNAMES. ORA Check MAX of Arc Logs al. sql Construct the script for cloning clone. rman Run it! (c) Arup Nanda 55
Surviving Nightmares (c) Arup Nanda 56
Missing Controlfile • Autobackup: – list backup of controlfile; • No backup: – strings * | grep control – Use restore controlfile from “…”. (c) Arup Nanda cont. rman 57
Missing Backup • Datafile backup is missing • Solution: – SQL> ALTER TABLESPACE … OFFLINE – RMAN> RESTORE DATAFILE …; It will create an empty file – RMAN> RECOVER DATAFILE …; It will roll forward the file – SQL> ALTER TABLESPACE … ONLINE (c) Arup Nanda 58
Catalog, yes; Controlfile: no • The controlfile is missing; but you have the catalog. • RC_DATABASE – the database • select * from RC_BACKUP_CONTROLFILE_SUMMAR Y (c) Arup Nanda 59
Same DB Name! • Two databases have the same name but your are restoring only one! • Use DB_ID RMAN> RMAN> SET DBID = <DBID of target>; RESTORE CONTROLFILE; ALTER DATABASE MOUNT; RESTORE DATABASE; RECOVER DATABASE; (c) Arup Nanda 60
UNKNOWN – the DB Name? ! • Database name as UKNOWN • Sequence: – You had a database, not registered to catalog – You recovered the database incompletely, with RESETLOGS – You registered the “reincarnated” database to the catalog. (c) Arup Nanda 61
Security • 90% of data theft at occurs from the backup; not from the database directly. • Backup is more vulnerable, because: – Backup tapes are outside the firewall – Backup tapes are less vigilantly guarded – Intrusion is often undetected – Intrusion can’t be shutdown, once started (c) Arup Nanda 62
Protecting RMAN Backup • Encrypting the selective columns – Using Transparent Data Encryption (TDE) – Using your own encryption routines • Encrypting the entire backup – Using Tape Management System encryption capabilities – Using RMAN’s native encryption – new in Oracle 10 g R 2 (c) Arup Nanda 63
Wallet Table Encryption Key SYS. ENC$ Master Key Encryption Wallet alter system set encryption wallet open authenticated by "remnant"; (c) Arup Nanda 64
RMAN TDE Setup • Make sure directory $ORACLE_BASE/admin/$ORACLE_SID/ wallet exists • Set the enc key password: key. sql • Open the wallet: wallo. sql • Close the wallet: wallc. sql (c) Arup Nanda 65
Encrypting RMAN Backups • Use the same Encryption Wallet • Transparent Mode: RMAN> configure encryption for database on; RMAN> configure encryption for tablespace users on; SQL> alter system set encryption wallet open authenticated by “top. Secret"; RMAN> backup tablespace users; (c) Arup Nanda 66
Restoring Enc. RMAN Backups RMAN> restore tablespace users; Starting restore at 21 -MAR-06 using channel ORA_DISK_1. . . messages. . . ORA-19870: error reading backup piece /flash_area/PRODB 2/BACKUPSET/2006_03_21O 1 _MF_NNNDF_TAG 20060209 T 221325_1 YR 16 QLT_. BKP ORA-19913: unable to decrypt backup ORA-28365: wallet is not open (c) Arup Nanda 67
RMAN Encryption without Wallet • To Backup: RMAN> set encryption on identified by “pooh" only; RMAN> backup tablespace users; • To Restore: RMAN> set decryption identified by "pooh"; RMAN> restore tablespace users; (c) Arup Nanda 68
RMAN Encryption – Dual Mode • During Backup: – Wallet must be open. RMAN> set encryption on identified by “pooh"; RMAN> backup tablespace users; • During Restore: Either: – Wallet is open OR – RMAN> set decryption identified by “pooh”; (c) Arup Nanda 69
Checking Encryption • Check: select tablespace_name, encrypt_in_backup from rc_datafile; e 1. sql (c) Arup Nanda 70
Shameless Plug • More features described in my book • Cookbook format: e. g. How to perform a recovery when controlfile is missing (c) Arup Nanda 71
Thank You! Questions? Contact: arup@proligence. com (c) Arup Nanda 72
Скачать презентацию RMAN Beginner to Advanced in 1 Hour Arup
f21738fb3fc38707f9b9d76944801788.ppt