A Hitchhikers View Of The Oracle Underground

1
A Hitchhikers View Of The Oracle Underground

• Raghav Vinjamuri

2
About This Presentation

• Purpose
• Given a vast repertoire of tools and techniques
  for Proactive Oracle Maintenance - everyone has
  their own favorite tools, keywords and ..
  buzzwords.
• This presentation provides a view for a selection
  of tools and techniques that are uncommon or in
  various stages of implementation .. in an Oracle
  DB env. ?

3

Counting down from ..

• 7

4
7 Speed Up Backups .. 1

• Question Name some options for speeding up
  backups using RMAN?

Thoughts Get faster CPU, disk .. Faster
everything. Seriously ?
5
7 - Speed Up Backups .. 2

• Thoughts
• Incremental backup strategy
• RMAN Block Change Tracking
• Block change tracking causes only the changed
  database blocks to be flagged in a special file.
• Mostly everyone uses a Incremental StrategyYet
  not many use BCT why?
• Use ASM

6
7 - Speed Up Backups .. 3

• Considerations
• Needs a Block Change Tracking file that can be
  created dynamically at any time.Even when the db
  is open.

7

• 6

8
6 Speed Up Recovery/Restore .. 1

• Question How to speed up recovery and restore?

Thoughts Get a superfast IO subsystem ?
9
6 Speed Up Recovery/Restore .. 2

• Answer
• Use Restore points and Flash Recovery Logs
• Provides a feature wherein a reset can be
  performed to the same restore points. (see
  demo).
• Vastly speeds up qa refresh times (and, mitigates
  operational errors).

10

• 5

11
5 Recover a small table .. 1

• Question How do you recover a small mostly
  static data table?
• Given that,
• - a backup all archivelogs available
• - however, no export dmp file

12
5 Recover a small table .. 2

• Thoughts
• Hmm restore to another instance.
• Ok what next?

• Option
• Use Log Miner Capability

13
5 Restore a small table .. 3

• Remember
• Take an export !! ?

14

• 4

15
4 samples of prod table for qa .. 1

• Question How to create a sample of prod table?

• Option
• Use exp with where clause

16

• 3

17
3 Audit Forensics .. 1

• Question How to track changes for a
  transaction?

Thoughts Triggers .. Ok. What else?
18
3 Audit Forensics .. 2

• Option
• Flashback Versions Query andFlashback
  Transactions Query

• Considerations
• db_flashback_retention_target defaults to 1440
  minutes

19

• 2

20
2 ORADEBUG .. 1

• Thoughts
• Useful for diagnosing performance and hanging
  issues.
• Primarily for Oracle Support
• Serves debug and tracing issues.
• Take dumps of internal structures and file
  headers

21
2 ORADEBUG .. 2

• Thoughts
• In sqlplus as sysdba attach processrun ORADEBUG
  commandsdetach process
• ORADEBUG HELP prints ORADEBUG command

22

• 1

23
1 Oracle RDA .. 1

• Oracle RDA (Remote Diagnostics Agent) is
  primarily used by Oracle Support.
• Instead of discussing everything about RDA, here
  is the RDA report generated.
• Download it from MetaLink - Doc ID 314422.1

24

• BONUS

25
BONUS Undocumented Parameter

• _fix_control
• Syntax _fix_controlltBUGNOgtON
• Or _fix_controlltBUGNOgtOFF
• Current Favorite!!

26
BONUS _fix_control .. 2

• Thoughts
• Bug Fixes need to be enabled to use this feature
• Check beforehand, if the particular bug fix is
  registered or not!
• VSYSTEM_FIX_CONTROL is a view of bug fixes that
  can be turned off. -)Currently has 229 (as in
  11gR1)

27
Presentation Roundup

• Summary
• This presentation touched upon
• Backups, and getting more from RMAN backups
• Restores, and setting restore points for quick qa
  refresh
• Restore small table using Log Miner capability
• Using Flashback Versions and Flashback
  Transactions
• ORADEBUG and rda
• Tools and Options for making everyday work
  simpler and easier!

28
Fin!

• QA
• All the best for the future!
• Thank You!