Reducing Costs of Environment Copies using Oracle - Recovery Manager

1
Reducing Costs of Environment Copies using Oracle
- Recovery Manager
Rich BernatChevronTexaco Corp.
2
Agenda

Who We Are
ChevronTexaco SAP Landscape
Why We Chose RMAN
How We Use RMAN
What We Gained
The ever-present Gotchas
Tuning RMAN for Performance
Q A
3
Who am I ???

• Rich Bernat
• ChevronTexaco Corp.
• Concord, CA (SF East Bay)
• Basis Admin (HR ITS)
• Oracle DBA
• Backup Recovery
• Disaster Recovery

4
ChevronTexaco Our Company

• One of the worlds largest global energy
  companies
• Active in more than 180 countries
• 53,000 employees worldwide
• 98 billion in revenue for 2002

5
Agenda

Who We Are
ChevronTexaco SAP Landscape
Why We Chose RMAN
How We Use RMAN
What We Gained
The ever-present Gotchas
Tuning RMAN for Performance
Q A
6
ChevronTexaco Landscape

• 13 HP-UX N-class or RP database servers (1-10)
  DBs per server
• 35 development, staging, or project databases
• 7.0Tb total ranging from 80Gb to 2Tb
• 6 production databases
• SAP Modules - FI, CO, HR, BW, SD
• Oracle 8.1.7.4 or 9.2.0.4 in shared
  ORACLE_HOME for DBs on each version

7
Refresh FrequencyHow are we special

• Staging environments are refreshed monthly for
  Prod Change window (or on demand)
• Project environments are refreshed on demand
• Projects create, copy, refresh, restore many
  times for different project phases
• Several refreshes can occur at in the same day
  6Tb of data movement!!!
• Customers expect us to deliver environments in a
  narrow time window.

8
Look what we used to do

• Take an offline backup (outage required)
• Restore (recover) as source DB on separate
  hardware
• Risk having 2 DBs briefly named the same
• Lots of SQL to parse rebuild control file
• Lots of SQL shell scripting logic to rename
  datafiles
• Mount point madness for sapdatas
• Had to recreate the Oracle DBID (also risky!!!)

9
Agenda

Who We Are
ChevronTexaco SAP Landscape
Why We Chose RMAN
How We Use RMAN
What We Gained
The ever-present Gotchas
Tuning RMAN for Performance
Q A
10
Why we needed to change

• Projects wanted more flexibility in refresh
  options
• Prior method required restore to separate DB
  server
• Refresh of environments was spilling into project
  time
• Required offline backup of source DB.
• Buying more hardware was too expensive

11
Why we needed to change

• Tape Capacity Tape silo reaching capacity
• Impact of backups on Production cycles
• Reduction of downtime

12
So

• Our method of backups and refreshes needed to
  change .

13
What RMAN provides...

• Ability to copy/create an instance on the same
  host!!! Huge benefit in our world!
• No outage for the source environment required!!!
• Point-In-Time recovery for projects that want an
  env from Thursday at 314 pm.!!!
• No Mountpoint madness
• No controlfile worries
• No DBID concerns
• No copy named the same SID as source.
• Delegation of work to Operation Analysts and Tech
  Team

14
RMAN Terminology

• Channel pathway for MML I/O tape or disk
• DBID Unique Oracle database Identifier
• Incarnation Version of a Database (after
  resetlogs or duplicate)
• RMAN Catalog Repository of Database backup
  activity (required for DB Duplication!)
• Duplexing data streams to multiple backup
  devices
• Multiplexing multiple i/o streams to same
  device (open files or channel)
• Backup types Whole, Incr 0, incr 1

15
RMAN Catalog

• RMAN stores data in a separate DB known as the
  RMAN catalog.
• It is exported with EACH backup and archivelog.
• Over time, export grows so keep enough space in
  temp location for export.
• These are HP OBII/DP implementation features
  your MML may vary.
• We just upgraded to HPO DataProtector 5.1 in
  Feb/04.

16
Agenda

Who We Are
ChevronTexaco SAP Landscape
Why We Chose RMAN
How We Use RMAN
What We Gained
The ever-present Gotchas
Tuning RMAN for Performance
Q A
17
An RMAN DB Backup

• rman
• connect target SRCSTRING
• connect catalog CATSTRING
• run
• allocate channel 'dev_1' type 'sbt_tape'
• parms BLKSIZE524288, ENV(OB2BARTYPEOracle8,O
  B2APPNAMESID,OB2BARLISThost_SID_incr0)'
• allocate channel 'dev_2' type 'sbt_tape
• parms 'BLKSIZE524288, ENV(OB2BARTYPEOracle8
  ,OB2APPNAMESID,OB2BARLISThost_SID_incr0)'
• set limit channel 'dev_1' maxopenfiles 4
• set limit channel 'dev_2' maxopenfiles 4
• backup incremental level 0 cumulative
  filesperset 24
• format host_SID_incr0ltSID_stgt.dbf'
  tag'1050127202'
• database
• backup format host_SID_incr0ltSID_stgt.dbf'
  current controlfile
• sql 'alter database backup controlfile to
  trace'
• resync catalog

18
And now for your archivelogs

• rman
• connect target SRCSTRING
• connect catalog CATSTRING
• run
• set duplex2
• sql 'alter system archive log current'
• allocate channel 'ch1' type 'sbt_tape'
• parms ENV(OB2BARTYPEOracle8,OB2APPNAMESID,OB
  2BARLISThost_SID_archc)'
• set limit channel 'ch1' maxopenfiles 4
• backup filesperset 24 format
  host_SID_archcltSID_stgt.dbf'
• archivelog all delete input
• resync catalog

19
Steps in a CVX Refresh

• Export SAP Security
• SM59/SMLG Settings
• Validate target filesystem size (sapdatas) SAP
  mount points
• Validate RMAN connectivity
• This all happens during the day prior to refresh
  or create. Source env is up active.

20
RMAN Processing

• Typical RMAN command for Production to QA Refresh
  
• rman
• connect target SRCSTRING
• connect catalog CATSTRING
• connect auxiliary sys/SYSPWD_at_QA1 \
• run allocate auxiliary channel ch1 type
  'sbt_tape'
• allocate auxiliary channel ch2 type
  'sbt_tape'
• allocate auxiliary channel ch3 type
  'sbt_tape'
• allocate auxiliary channel ch4 type
  'sbt_tape'
• set until time 08-16-02 153211\
• duplicate target database to QA1 \

21
Post Processing

• Configure RMAN ID
• Grant sysdba to rman
• Configure Data Protector (HP Backup Utility) for
  new SID
• Update Transport Tables
• Update Printers tables
• Delete Scheduled Jobs
• Re-create SID-ADM user and SAPUSER table

22
Post Processing (Contd)

• Validate Oracle Users (Basis SAs)
• Delete Old Security
• Import New Security
• Reconfigure Taxware
• Delete/Import SM59 SMLG entries
• Apply SAP license Very Cool! I hate typing the
  License Key
• Saplicense pinstall ifilelicense.key
• Set archivelogmode on (if needed)

23
Voila a working SAP env.

• Reset profiles and operation modes via RZ04/RZ10
• Turn over to our TMS team to apply necessary
  transports
• Released to our customers early in the morning
  (depending on security and transports)

24
Agenda

Who We Are
ChevronTexaco SAP Landscape
Why We Chose RMAN
How We Use RMAN
What We Gained
The ever-present Gotchas
Tuning RMAN for Performance
Q A
25
RMAN Gains

• Copy/Refresh to any server( including same
  server)!
• Drastically reduced errors
• predictable availability for projects
• Automates most of refresh process
• Reduce risk in rename process
• Reduced tape resource consumption using
  incremental backups
• Reduced operational impact of backup process
• RMAN is not perfect weve gone through some
  trouble but it was worth it.

26
Agenda

Who We Are
ChevronTexaco SAP Landscape
Why We Chose RMAN
How We Use RMAN
What We Gained
The ever-present Gotchas
Tuning RMAN for Performance
Q A
27
RMAN Gotchas

• ORA-4031 shared pool consumed either increase
  SHARED_POOL_SIZE or enable LARGE_POOL_SIZE
• Duplicate DBID from our old DB copies zero_dbid
  procedure and new controlfiles
• MetaLink note 174625.1
• nid new in 9i to create a new dbid
• Need to maintain your catalog identify and
  delete old DB incarnations

28
RMAN Catalog Housekeeping

• Clean your catalog by
• Removing old DBs and old incarnations
• execute dbms_rcvcat.unregisterdatabase(4624689,163
  2315282)
• Removing expired backup entries from the catalog
• change backupset 12345 crosscheck
• change backupset 12345 delete
• Clean up old control file backups

29
More RMAN Gotchas

• Controlfile captured at beginning of backup
  (w/system datafile really needs to be at end and
  with each set of archlogs.
• backup format your format here' current
  controlfile
• Need to create sapdata directory structure prior
  to Duplicate
• Need to have 1 archivelog
• "sql 'alter system archivelog current'"
  (archivelogmode)
• or
• "sql 'alter system switch logfile'"
  (noarchivelogmode)
• host 'sleep 45' long enough to allow the
  switch to complete
• ABSOLUTE_FUZZY_CHANGE vs. COMPLETION_CHANGE

30
Agenda

Who We Are
ChevronTexaco SAP Landscape
Why We Chose RMAN
How We Use RMAN
What We Gained
The ever-present Gotchas
Tuning RMAN for Performance
Q A
31
Getting the squirrels to run!

• Balance memory consumption
• (shared_pool_size or large_pool_size)
• network bandwidth - Gigabit for network devices.
• Tape device speed probably the bottleneck for
  incr0 and whole!
• Your bottleneck may vary
• Disk Subsystem - maximum read rates
• Schedule during low impact times
• Multiple devices More better!
• Turn off mml CRC for maximum throughput
• Were currently sustained 60M/sec and peak at
  80M/sec

32
Getting the squirrels to run (contd)

• Multiplex filesperset 24 lower values
  create many catalog entries. Catalog space
  management.
• disk is fast
• tape and network are slow
• MaxOpenFiles 4 Governs of files open for
  backup still allows for efficient use of
  FilesPerSet
• Channel Parallelism 1 or 2 per device (nominal
  gain on 2 channels. 3 more are not beneficial
  in incr0 or whole backup.
• of channels impacts restore speed. For faster
  restores, use 1 channel in your backups
• Channels filesperset consume your SGA memory
  MaxOpenFiles helps manage this
• I/O buffer tuning - match your stripe size

33
How is this all done??

• Automation utilizing Unix shell scripting
• Standards in DB layout file systems
• Understanding the nuances of RMAN
• In-depth knowledge about SAP tables
• Years of experience and building on ideas of
  others
• Management support to try new methods
• Like all our work it has evolved

34
New in 9i

• Most 8i syntax still works some new variations
  in 9i.
• Configuration can be retained in Catalog for a
  SID or still invoked via rman run command
• Show all
• Retention policy helps with backup maintenance
• Recovery window redundancy vs. days
• Control file autobackup its about time!!!
• CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE
  TYPE 'SBT_TAPE' TO 2 - replaces duplex
• CONFIGURE DEVICE PARALLELISM 2 auto channel
  allocation.
• DELETE FORCE NOPROPMT OBSOLETE
• execute dbms_rcvcat.unregisterdatabase(db_key,dbid
  )

35
Other RMAN Uses

• Backup Recovery (Obviously)
• Disaster Recovery!!!
• Restore Oracle binaries
• Restore RMAN catalog
• Configure MML RMAN communications
• Restore DBs!
• Used for D/R drills last 3 years

36
Acknowledgements

• Jason Singleton ChevronTexaco Corp.
• Tammy Bednar Oracle Corp.

37
Agenda

Who We Are
ChevronTexaco SAP Landscape
Why We Chose RMAN
How We Use RMAN
What We Gained
The ever-present Gotchas
Tuning RMAN for Performance
Q A
38
Thank You!

• Rich Bernat
• ChevronTexaco Corp.
• RichBernat_at_ChevronTexaco.com

39
Thank you for attending!