Are You Sure You Can Recover In Any Circumstance?

1
Are You Sure You Can Recover In Any Circumstance?
Tim Boles Database Administrator Senior Staff 
2
Who Am I ?

• Tim Boles
• DBA with Lockheed Martin ISGS Civil Division
• Oracle Database Administrator Since 1998
• Experience from gigabytes terabytes databases
• timothy.s.boles_at_lmco.com
• www.lockheedmartin.com/isgs

3
Topics

• Availability Is Not Recovery
• Causes of Data Loss And Its Cost
• Basics of Backups
• Building A Backup Policy
• How To Be Sure You Can Recover

4
Availability is not recovery
5
The New Thing

• Cloud Computing
• Virtual
• RAC
• Standby Database
• 99.999 Uptime

6
When Does Availability Not Help?

• RAC Lose the underlying data files.
• ESAN Lose Power and drives dont come up.
• Virtual Disgruntled Employee Drops Schema
• COOP Site Software Bug / Virus corrupts data
• DISK MIRROR Data Corruption

7
Causes of Data Loss and its cost
8
The Experts Say
Causes of Data Loss Dr. Smith Findings Ontrack Findings
Hardware Failure 40 56
Human Error 29 26
Software Corruption 13 9
Theft 9 --------
Computer Viruses 6 4
Hardware Destruction 3 2
http//gbr.ppperdine.edu/033/dataloss.html http//
www.ontrackdatarecovery.com/understanding-data-los
s
9
Hardware Failure Recovery

• Server Failure(s)
• Drive Failure(s)
• ESAN
• Disaster Recovery Site

10
What If?

• Server Disk Failure with Oracle Software Binaries
• SAN with redo logs fail
• Mirrored Master Destruction with Administrative
  Files
• listener.ora
• tnsnames.ora
• password
• dataguard configuration
• Enterprise Manager configuration files
• RMAN repository failure

11
Human Error

• OS Commands
• Bad DDL
• Bad DML
• Compounded by Additional Mistakes
• Features Only Help When Enabled
• Whats Your Plan?

12
Software Corruption

• Customized COTS / In-House
• COTS
• Leopard OS
• Oracle BUG

http//tomkarpik.com/articles/massive-data-loss-bu
g-in-leopard/
13
How Would You Recover?

• DROP SCHEMA CASCADE
• Oracle software deletion
• Wrong data deletion
• detected immediately
• detected several hours later
• Batch Job corruption
• Software Upgrade
• Block Corruption Detected in Backup

14
Counting the Cost
Industry Sector Revenue/Hour (Millions)
Energy 2.8
Telecommunications 2.0
Manufacturing 1.6
Information Technology 1.3
Pharmaceuticals 1.1
Meta Group of Stanford, CT in October of 2000 IT
Performance Engineering Measurement Strategies
Quantifying Performance Loss.
15
Basics of a Backup
16
What The?
RMAN Backup
Rman Restore
17
RMAN Does Not Back Up

• Oracle Software Home (binaries)
• BFILES
• Password Files
• pfiles (spfiles are covered with newer versions)
• tnsnames.ora
• listener.ora
• sqlnet.ora
• /etc/oratab
• scripts (shell, sql)

18
The Basics

• Backup and Recovery Plan
• Physical Backups
• Data Storage
• data files, contol files, Archived Redo
• Support Files
• Binaries, Initialization Files, Scripts, .ora,
  password
• Logical Backups (Exports)
• Logical data structure such as tables,
  tablespaces, objects, users, data within tables

http//www.oracle.com/technology/deploy/availabili
ty/htdocs/BR_Overview.htm
19
Building a Backup policy
20
Where to Start?

• Stake Holders
• Who Cares About The Data?
• Users
• Auditor, Lawyer, Regulator
• Security System Administrators
• Who Touches The Data?
• System / Backup Administrators
• Database Administrators

21
Basic Concerns

• Size of Database (growth potential)
• Backup Window
• Space Available for Backup Storage
• Media Used
• Tools Available
• Data Retention Times
• Acceptable Mean Time To Recovery (MTTR)

22
Beyond the Basics

• Encryption
• Storage of Encryption Keys
• Access to Encryption Keys
• Design of Database
• Read-Only Tablespaces
• Tablespace Partitions
• Compression Algorithms

23
Is Your Backup Good?

• Backup Log
• Physical Check
• Logical Check
• Only good if you can recover

24
How To Be Sure You Can Recover
25
What Is Your Source?

• Memory / Experience
• Oracle Documentation / Books
• Internet Search Engines
• Co-worker
• Monitoring Tools (i.e. Oracle Enterprise Manager)
• Customized Documentation

26
Are You A Single Point Of Failure?
27
Backup And Recovery Document
28
Documentation Is Your Friend

• Good Business Sense
• Every System Is Different
• Boosts Ability to Concentrate
• Gain Experience and Knowledge
• Refine Backup / Restore Policies
• Refine Procedures

29
BR Document 20000 ft View

• Overall Backup Strategy
• Architecture Summary
• Script Listing and Description
• Procedures
• Test Documentation

30
Overall Backup Strategy

• Types of Backups And Reasons
• Physical
• Hot / Cold
• Full / Incremental
• Exports
• Full
• Schema, Table, (Transportable) Tablespace
• Tools
• Scheduling
• Notification
• Retention Policies (Time and Off-site Location)
• System Specifics

31
Architecture Summary

• Server Configuration
• Tool Integration
• Database Configuration

32
Tools and Technology Available

• Media Failure
• Restore Media from Backup
• Recover using RMAN or SQL Commands
• Full
• Partial
• Tablespace point-in-time (TSPITR)
• Time-based (PITR)
• Cancel-based
• Change-based
• Human or Software Error
• Flashback

http//www.oracle.com/technology/deploy/availabili
ty/htdocs/BR_Overview.htm
33
Flashback 9i and 10g R1

• Oracle 9i
• Flashback Query
• Oracle Database 10g R1
• Flashback Database
• Flashback Table
• Flashback Drop
• Flashback Version Query
• Flashback Transaction Query

34
Flashback 10g R2 and 11g

• Oracle Database 10g R2
• Restore Points
• Flashback Database Through Resetlogs
• Oracle Database 11g R1
• Flashback Transaction
• Flashback Data Archive
• Oracle Database 11g R2
• Flashback Data Archive tracks most DDL

http//www.oracle.com/technology/deploy/availabili
ty/htdocs/Flashback_Overview.htm
35
Cheat Sheet

• System Name
• System Description
• Server Name
• Server Description
• Lead Administrator Contact Information
• Database Information
• Name
• Oracle version/patch level
• CSI
• Important users / Password Expire Dates
• Features Enabled

36
Cheat Sheet Continued..Locations

• ORACLE_HOME
• Oracle User Home
• Administration SQL scripts
• Administration Shell scripts
• RMAN/backup scripts
• Backup Logs
• Backup Storage
• contol files
• Archive Logs

37
Script Listing and Description

• Location
• Usage
• Execution Syntax
• Parameters with Descriptions

38
Test Documentation

• Backup Procedures
• Recovery Scenarios To Test
• Document Restore Procedures

39
Media Loss

• Loss of a Control File
• Loss of a data file for a tablespace
• System, rollback segment, UNDO, user data, Index,
  read-only, partition
• Loss of Redo Log file
• Inactive Online, Current Online, Archived
• Loss of entire redo group
• Inactive Online, Current Online, Archived
• Data Block Corruption
• Physical
• Logical
• In Backup

40
Recovery of Entire Database

• Recovery with No RMAN catalog
• With / Without controfile
• With / Without redo logs
• Recovery to New Machine
• Recovery to New File System.
• Point in Time Recovery of Entire Database
• Recovery of RMAN catalog
• Creation of Standby Database
• Creation of Duplicate Database on Test System

41
More Than Just One File

• If database crashes during backup.
• If binaries are destroyed.
• If entire database server has to be replaced.
• If SAN loses multiple drives.
• If database crashes during table movement.
• If database crashes during use of Flashback
  Technology
• If Read-Only tablespace was created before last
  backup.
• If Read-only tablespace was created after last
  backup

42
User / Software Error (Flashback)

• Recovery of Dropped Schema
• Recovery of Dropped Table
• Data Corruption in Row
• Transaction Flashback
• Single
• All resulting transactions
• Software Installation Failure
• Data Corruption in entire schema
• Data Corruption in schema 5 hours old but reset
  of database needs to remain.
• Trigger or procedure is recompiled with wrong
  code

43
Visit the IOUG Booth This Week

• Located in the User Group Pavilion - Moscone
  West, 2nd Floor
• Learn why over 23,000 have joined IOUG and what
  it can do for you
• Chat with the IOUG Board of Directors
• Hear about new regional IOUG BI user communities
• Find out how to submit an abstract for
  COLLABORATE 11 IOUG Forum
• Enter for a chance to win a COLLABORATE 11
  registration
• Stock up on IOUG gear and educational materials!

44
(No Transcript)