10 ways to improve your rman script

37
10 Ways to Improve your RMAN Script Author: Yury Velikanov Co-author and presenter: Maris Elsins

Upload: maris-elsins

Post on 13-May-2015

4.108 views

Category:

Technology


5 download

DESCRIPTION

Author: Yury Velikanov Co-author and presenter at UKOUG Tech13 - Maris Elsins

TRANSCRIPT

Page 1: 10 ways to improve your rman script

10 Ways to Improve your RMAN ScriptAuthor: Yury VelikanovCo-author and presenter: Maris Elsins

Page 2: 10 ways to improve your rman script

© 2013 Pythian2

Why Pythian• Recognized Leader:

– Global industry-leader in remote database administration services and consulting for Oracle, Oracle Applications, MySQL and SQL Server

– Work with over 150 multinational companies such as Forbes.com, Fox Sports, Nordion and Western Union to help manage their complex IT deployments

• Expertise:– One of the world’s largest concentrations of dedicated, full-time

DBA expertise. Employ 7 Oracle ACEs/ACE Directors– Hold 7 Specializations under Oracle Platinum Partner program,

including Oracle Exadata, Oracle GoldenGate & Oracle RAC

• Global Reach & Scalability:– 24/7/365 global remote support for DBA and consulting,

systems administration, special projects or emergency response

Page 3: 10 ways to improve your rman script

© 2013 Pythian3

Maris Elsins

• 8y+ Oracle [Apps] DBA (+3y PL/SQL Developer)

• 3y at Pythian• Oracle Certified Professional (9i, 10g, 11g, 11i,

R12)

• Oracle Certified Master• Speaker (15) (UKOUG, OUGF, LVOUG, Collaborate)

• Blogger http://www.pythian.com/blog/author/elsins

• MarisElsins, Maris.Elsins, , • FAN of #BAAG

Page 4: 10 ways to improve your rman script

© 2013 Pythian4

Questions & Comments

#UKOUG_Tech13 @MarisElsins

Page 5: 10 ways to improve your rman script

© 2013 Pythian5

Page 6: 10 ways to improve your rman script

© 2013 Pythian6

The Mission

Give you 10 practical hints on RMAN script improvements

Encourage you to think on what can possibly go wrong

before it happens.

Page 7: 10 ways to improve your rman script

© 2013 Pythian7

Right Approach ... ! be skeptical !• If backups and trial recovery works it doesn’t

mean you don’t have issues – must test / document / practice recovery

• Challenge your backup procedures! – Think about what can possibly go wrong– Think now as in the middle of an emergency

recovery it may be way too late or too challenging

• Prepare all you may need for smooth recovery while working on backup procedures

Page 8: 10 ways to improve your rman script

© 2013 Pythian8

Few general thoughts …

NEVER rely on backups stored on the same physical media as the database!

Mark Brinsmead, Sr. Oracle DBA, PythianEven if your storage is the fanciest disk array (misnamed "SAN" by many) in the world, there exist failure modes in which ALL data in the disk array can be lost simultaneously. (Aside from fire or other disaster, failed firmware upgrades are the most common.) You don't really have a "backup" until the backup is written to separate physical media!

Page 9: 10 ways to improve your rman script

© 2013 Pythian9

Few general thoughts …

Avoid situations where the loss of a single piece of physical media can destroy more than one backup.

When backing up to tape, for example, if the tape capacity is much larger than your backups, consider alternating backups between multiple tape pools. ("Self-redundant" backups are of little value if you are able to lose several consecutive backups simply by damaging one tape cartridge).

If your backup and recovery procedures violate some of the base concepts - state risks clearly and sign/discuss those with business on regular basis.

Page 10: 10 ways to improve your rman script

© 2013 Pythian10

10 Improvements

Page 11: 10 ways to improve your rman script

© 2013 Pythian11

10 Improvements

Give us the perfect RMAN backup script and go away!

Page 12: 10 ways to improve your rman script

© 2013 Pythian12

#1 RMAN Log filespart of a log file ...

RMAN>Starting backup at 18-OCT-11current log archivedallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=63 device type=DISKchannel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=4 RECID=2 STAMP=764855059input archived log thread=1 sequence=5 RECID=3 STAMP=764855937...Finished backup at 18-OCT-11

Do you see any issues?

Prepare all you may need for smooth recovery while working on backup procedures

Page 13: 10 ways to improve your rman script

© 2013 Pythian13

#1 RMAN Log filespart of a log file …

RMAN> backup as compressed backupset database2> include current controlfile3> plus archivelog delete input;

Starting backup at 2011/10/18 12:30:46current log archivedallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=56 device type=DISKchannel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=8 RECID=6 STAMP=764856204input archived log thread=1 sequence=9 RECID=7 STAMP=764857848...Finished backup at 2011/10/18 12:33:54

Is this better?

Page 14: 10 ways to improve your rman script

© 2013 Pythian14

#1 RMAN Log files• Before calling RMAN

$ export NLS_DATE_FORMAT="YYYY/MM/DD HH24:MI:SS”$ export NLS_LANG="XX.XXX_XXX” (for non standard char sets)

• Before running commandsRMAN> set echo on

• Nice to have: total execution time at the end of log filec_begin_time_sec=`date +%s`... /The backup script/c_end_time_sec=`date +%s`v_total_exec_sec=`expr ${c_end_time_sec} - ${c_begin_time_sec}`echo "Script execution time is $v_total_exec_sec seconds"

Page 15: 10 ways to improve your rman script

© 2013 Pythian15

#1 RMAN Log files

• do not overwrite log file from previous backup

full_backup_${ORACLE_SID}.`date +%Y%m%d_%H%M%S`.log

Use case: a backup failed– should I re-run the backup now?– would it interfere with business activities?

Page 16: 10 ways to improve your rman script

© 2013 Pythian16

A Sample Script…

• We don’t keep backups older than 7 days• OS script deletes archived logs older than

7 days• Backups done by:crosscheck archivelog all;delete noprompt expired archivelog all;

backup database include current controlfile plus archivelog;

delete noprompt obsolete;

& NoYes!?

Page 17: 10 ways to improve your rman script

© 2013 Pythian17

#2 Do not use CROSSCHECK

• Do not use CROSSCHECK in your day to day backup scripts!

• If you do, RMAN silently ignores missing files, possibly making your recovery impossible

• CROSSCHECK should be a manual activity executed by a DBA to resolve an issue

Page 18: 10 ways to improve your rman script

© 2013 Pythian18

Another sample script…

backup database include current controlfile plus archivelog delete input;

delete noprompt obsolete;

exit;

Is this right?

Page 19: 10 ways to improve your rman script

© 2013 Pythian19

#3 Backup control file as the last stepbackup database plus archivelog delete input;

delete noprompt obsolete;

backup current controlfile;

exit;

Is this better?

Page 20: 10 ways to improve your rman script

© 2013 Pythian20

#4 Do not rely on ONE backup only• Do not rely on ONE backup only!

– You should always have a second option– REDUNDANCY 1 ???– REDUNDANCY 1 + 2 COPIES ???– Side note: REDUNDANCY X

• Also true for ARCHIVE LOGS– If you miss a single ARCHIVE LOG your recoverability

is compromised

-- ONE COPY ONLYBACKUP DATABASE ... PLUS ARCHIVELOG DELETE INPUT;

-- SEVERAL COPIESBACKUP DATABASE ...;BACKUP ARCHIVELOG ALL NOT BACKED UP {n} TIMES;

Page 21: 10 ways to improve your rman script

© 2013 Pythian21

#5 Do not delete ARCHIVE LOGS based on time only

• Deleting based on TIME – NO!DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-6/24' DEVICE TYPE DISK;

• Deleting based on TIME + COPIESDELETE NOPROMPT ARCHIVELOG ALL BACKED UP {N} TIMES TO DISK COMPLETED BEFORE ’sysdate-M';

• If you have a standby DB:CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

Page 22: 10 ways to improve your rman script

© 2013 Pythian22

#6 Use control file if catalog DB isn't available[oracle@host01 ~]$ rman target / catalog rdata/xxxRecovery Manager: Release 11.2.0.2.0 - Production on Tue Oct 18 15:15:25 2011...connected to target database: PROD1 (DBID=1973883562)RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-00554: initialization of internal recovery manager package failedRMAN-04004: error from recovery catalog database: ORA-28000: the account is locked[oracle@host01 ~]$

Page 23: 10 ways to improve your rman script

© 2013 Pythian23

#6 Use control file if catalog DB isn't available[oracle@host01 ~]$ rmanRMAN> echo set onRMAN> connect target *connected to target database: PROD1 (DBID=1973883562)RMAN> connect catalog *RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-04004: error from recovery catalog database: ORA-28000: the account is locked

RMAN> backup as compressed backupset database2> include current controlfile3> plus archivelog delete input;

Starting backup at 2011/10/18 15:22:30current log archived using target database control file instead of recovery catalog

special THX 2 @pfierens 4 discussion in tweeter

Page 24: 10 ways to improve your rman script

© 2013 Pythian24

#6 Use control file if catalog DB isn't available-- Backup partrman target / <<!backup as compressed backupset database...!

-- Catalog synchronization partrman target / <<!connect catalog rdata/xxxresync catalog;!

special THX 2 @martinberx 4 discussion in tweeter

Page 25: 10 ways to improve your rman script

© 2013 Pythian25

#7 Do not rely on RMAN stored configuration• The settings can change, especially if there

are more then 1 DBA in the team • Use controlfile autobackups.CONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/b01/rman/prod/%F’;

• Autobackup is created:– at the end of each RMAN backup – each time you make any DB files related changes

• BUT! don’t 100% rely on it.– What would happen if someone switched

AUTOBACKUP OFF?

Page 26: 10 ways to improve your rman script

© 2013 Pythian26

#7 Do not rely on RMAN stored configuration• Document current configuration in the log file

– RMAN> show all;

• If you change the configuration, restore it at the end of your script

• In your shell script:1. Capture the RMAN settings:

v_init_rman_setup=`$ORACLE_HOME/bin/rman target / <<_EOF_ 2>&1 | grep "CONFIGURE " | sed s/"# default"/""/gshow all;_EOF_`

2. Set your preconfigured settings and execute the backup script

3. Revert the settingsecho $v_init_rman_setup | $ORACLE_HOME/bin/rman target /

Page 27: 10 ways to improve your rman script

© 2013 Pythian27

#8 Backups’ consistency controlFailure Verification and Alerts

• How do you report backup failures and errors?– We don’t report at all– DBA checks logs sometimes– Backup logs are sent to a shared email address

(good!)– DBA on duty checks emails (what if no one

available/no email received?)– We check RMAN command errors code $? and

sending email

Page 28: 10 ways to improve your rman script

© 2013 Pythian28

#8 Backups’ consistency controlFailure Verification and Alerts

• I would suggest– Run log files check within backup script and

page immediately– Script all checks and use "OR" in between

– echo $?– egrep "ORA-|RMAN-" < log file >– Improve your scripts and test previous adjustments

on regular basis

– ALERT about any failure to the oncall DBA immediately

– DBA makes a judgment and takes a conscious decision

– ALERT about LONG running backups

Page 29: 10 ways to improve your rman script

© 2013 Pythian29

#8 Backups’ consistency controlFailure notifications are not enough!• How do you check if your database is safely backed

up based on your business requirements?• Make a separate check that would page you if your

backups don’t satisfy recoverability requirements

• REPORT NEED BACKUP ...– Datafiles that weren’t backed up last 24 hours!

REPORT NEED BACKUP RECOVERY WINDOW OF 1 DAYS;

– Datafiles that have less then 10 backups!REPORT NEED BACKUP REDUNDANCY 10;

– Datafiles that require more then 2 days of archived logsREPORT NEED BACKUP DAYS = 2;

– Datafiles that need backup due to unrecoverable operationsREPORT UNRECOVERABLE; May not available in all Versions!

Page 30: 10 ways to improve your rman script

© 2013 Pythian30

Manual tape backups• RMAN is used to take the backups to disk• Scripts are used to copy the backups from disk to tapes

• Use RMAN+MML whenever it’s possible – RMAN manages the tape backups– RMAN ensures sufficient number of tape backups are stored to satisfy the

retention policies– Simplified reporting and monitoring: REPORT NEED BACKUP … DEVICE TYPE

SBT;

• If you don’t use it then your backups are exposed to many issues• At best, your backups might take much more space on tapes as RMAN

is not able to delete them there based on the retention policy.• In worst case you may miss to backup some of the backup pieces, putting

the database recoverability at risk

• The next few slides discuss some issues

Page 31: 10 ways to improve your rman script

© 2013 Pythian31

#9 Ensure space on disk for 3*FULL backups Manual Tape Backups

• IF you don’t have– A smart backup software (incremental/opened files)– Sophisticated backup procedures

• THEN you need space on a file system for at least 3 FULL backups and ARCHIVED LOGS generated in between 3 backups

– If REDUNDANCY=1 then previous backup and ARCHIVED LOGS are removed after completing the backup. There is no continued REDO stream on tapes.

– If REDUNDANCY=2 then you need space for the third full backup during backup time only (as soon as third backup completed you remove the first one)

Page 32: 10 ways to improve your rman script

© 2013 Pythian32

#9 Don’t use “delete obsolete” Manual Tape Backups• Typically: tape backup retention > disk backup retention• This way you wipe out RMAN memory. There is no way RMAN

knows about backups available on tapes.• Think about recovery (if you use “delete noprompt obsolete”)

1. You need to recover a control file (possibly from offsite backups)2. Find and bring onsite all tapes involved (possibly several

iterations)3. Restore and recover (possibly restoring more ARCH backups)4. OR, you rely on logfiles to figure out which files to restore.

backup as compressed backupset database plus archivelog delete inputinclude current controlfile;delete noprompt obsolete;exit

Page 33: 10 ways to improve your rman script

© 2013 Pythian33

#9 Don’t use “delete obsolete” Manual Tape Backups• List obsolete backup files based on disk retentionreport obsolete recovery window of {DISK_RETENTION} days device type disk;

• check if files have been backed up and remove them from disk

!checking if each of reported files have been backed up to tapes & “rm” it from FS!

• Delete the information from repository based on tape retention.

delete force noprompt obsolete recovery window of {TAPE_RETENTION} days device type disk;

• When you need to recover:RUN{SET UNTIL SCN 898570;RESTORE DATABASE PREVIEW;}

Page 34: 10 ways to improve your rman script

© 2013 Pythian34

#9 NEVER keep default RETENTION POLICY• NEVER allow the RMAN RETENTION POLICY

to remain at the default or lower level than TAPE retention– other Oracle DBA can run DELETE OBSOLETE

command and wipe all catalog records out

CONFIGURE RETENTION POLICY TO REDUNDANCY 1000;CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1000 DAYS;

Page 35: 10 ways to improve your rman script

© 2013 Pythian35

#10 Don’t backup incomplete files to tapes• Make sure that your File System backup doesn’t

backup incomplete files to tapes• Number of ways to accomplish this:

– Trigger the FS backup from the same script that runs RMAN backup

– Use Hard links and 2 directories• RMAN writes to Dir1• Hard link for is created in Dir2• Files from Dir1 are removed as explained in #9• Files from Dir2 are removed by the tape backup

script• Data is removed from disk when both hard links

(Dir1 and Dir2) are removed.

Page 36: 10 ways to improve your rman script

© 2013 Pythian36

Do we have a winner?#1 RMAN Log files#2 Do not use CROSSCHECK#3 Backup control file as the last step#4 Do not rely on ONE backup only#5 Do not delete ARCHIVE LOGS based on time only#6 Use controlfile if catalog DB isn't available#7 Do not rely on RMAN stored configuration#8 Backups’ consistency control#9 Don’t use “delete obsolete” for Manual tape backups#10 Don’t backup incomplete files to tapes

Page 37: 10 ways to improve your rman script

© 2013 Pythian37

Thank you and Q&A

[email protected]

@MarisElsins

www.pythian.com/blog/author/elsins