156 5th Avenue

1
Strategies Tools for Centralizing and
Automating Database Management
Delivered by Matthew Zito, Chief Scientist

• 156 5th Avenue
• Penthouse
• New York, NY 10010
• P 646.452.4100
• www.gridapp.com

2
Todays Agenda

• Introduction
• The Changing Database Landscape
• Automation within the database
• Automation Across databases
• Automation and configuration management
• The Three Cs
• Response files, templates, etc.
• Code and script management
• QA

3
The database landscape is changing

• More functional requirements
• Different types of content
• More federation/mobility
• More complicated infrastructure underpinnings
• Virtual Machines
• Clustering
• Replication
• Grid, Grid, Grid
• Fewer big boxes, more little boxes

4
which leads to-

• Higher levels of required skill for DBA
• Cant just know SQL anymore
• Compliance work w/auditors to define controls
• Storage, clustering all increase DBA skillset
  requirements
• Overall greater complexity
• More servers more complexity
• More databases more complexity
• More data movement more complexity

5
So whats to be done?

• DBAs cant be generalists anymore
• Focus on development
• Focus on new technology deployment
• Create process
• Less worrying about whats happening to systems
• More effective delegation
• Reduce manual interaction with databases
• Tools
• Scripts
• Let software do the work

6
Automation is the answer

• Automation within a database
• Self-managing
• Self-healing
• Automation across databases
• Policies
• Automated deployment
• Schema management

7
Internal Automation

• Storage
• ASM
• Tablespace management
• Operational
• Automatic SGA management
• Automated Failover
• Performance
• ADDM
• Client listener load balancing

8
Storage Automation

• ASM
• Reduces storage management complexity
• Automatically rebalances data on available disks
• Encourages standardization of disk devices
• Forget fine-tuning storage - ASM is good enough
• Tablespace management
• Oracle Managed Files
• Standard functionality with some improvements

9
Operational Automation

• Automatic SGA Management
• Set an overall guideline for memory utilization
• SGA_TARGET
• Oracle tunes the components within that
• Reduces tweaking of memory
• Failover
• RAC configure TAF/connect string
• connections automatically reconnect to surviving
  node(s)
• Fast-start failover
• automatic failover from primary to standby
  database
• Compensates for stress-induced human error
• reduces downtime

10
Performance Automation

• ADDM
• Automatic analysis of AWR reports
• Provides coarse-grained recommendations
• Not fine-grained tuning, but saves time
• Load Balancing - RAC
• Listener redirects inbound connections to the
  least-loaded node (doesnt really work that well)

11
Internal Automation Summary

• Focused on either very simple or very complicated
  problems
• I need to extend this tablespace by 10
• How do I distribute connections across RAC
  nodes
• Oracle is committed to reducing DB complexity
• Increasingly automated features
• Reduced tunable parameters
• Will all internal management of databases be
  automated?
• Forrester thinks yes
• But then again.

12
Inter-database Automation Summary

• Concepts
• Automation Process
• The three Cs
• Standardized Oracle installation
• Response files
• Technical tools
• Templated DBCA
• Templates
• Response files
• Administration
• Scripts
• Schema Templates

13
Automation Concepts

• Standardization
• Reduce complexity
• Reduce ramp-up time for new DBAs
• Reduce deployment time for new databases and
  applications
• Repeatability
• Write once, run anywhere
• Inspire greater confidence in process
• Get more sleep

14
Automation Concepts continued

• Centralization
• Single Source of Truth for configuration data
• Always be current
• Enhanced auditing and understanding
• Process
• Automation does not remove the need for process
• Process should be implemented in automation
• Defined processes reduce downtime

15
The Three Cs of Configuration Management

• Code
• ORACLE_HOME
• ASM Clusterware
• Home-grown scripts
• Content
• Schema
• PL/SQL
• Configuration
• Initialization parameters
• Secondary application config (Data Guard, etc.)
• ASM layouts

16
Standardized Oracle Installation

• Response Files
• Provide a way to reliably install Oracle in an
  identical configuration
• Allows you to effectively define standards for
  how and where Oracle should be installed
• Saves time just click and go
• Basics
• Two types of automated installations
• Silent mode wont ask any questions
• Suppressed mode uses a response file and
  prompts for missing parameters
• Response files are in the format namevalue

17
Standardized Oracle Installation
P

• Process
• Create an oraInst.loc file
• Call the OUI with a response file
• Response files can be created by hand or through
  the OUI
• ./runInstaller record destinationFile
  /path/to/somefile
• Start the OUI with the response file
• runInstaller -silent -noconfig -responseFile
  responsefilename
• Post-install, you can run other config assistants
  by hand, or use response files for those as well

18
Standardized Oracle Installation
P

• Tips Tricks
• Test, test, test it may take time to develop
  good response files for your organization
• Try to limit the number of response files in use
  to keep things simple
• Its possible to build a response file that is
  complete with the exception of certain
  parameters, which can be supplied on the command
  line runInstaller silent "ORACLE_HOME_NAMEOraD
  BHome1"

19
Automated Database Creation
P

• DBCA
• Oracles DBCA has two different automated
  components templates and response files
• Templates a bundle that describes the content
  and configuration of the database it includes
  initial schemas datafiles, init parameters,
  etc.
• Response files describes the physical
  characteristics of the database SID, datafile
  layout, etc.
• The DBCA can create a new database or clone an
  existing one

20
Automated Database Creation
P

• Templates
• Oracle defines three standard templates
• General Purpose
• Transaction Processing
• Data Warehouse
• Two types of templates
• Seed a template that contains pre-created data
  files, redo logs. Etc.
• Non-seed a template that doesnt hold any
  physical structure, just definitions and
  configuration data
• Creating a new template
• Uses the Manage Templates component of the DBCA
• Create a template from an existing database (seed
  or non-seed)
• Customize an existing template

21
Automated Database Creation
P

• DBCA Response Files
• Same format as the Oracle binary installer
• Defines
• Datafile redo log locations
• SID
• Node list (in a RAC environment)
• Overrides template init.ora parameters
• Does not define schema
• Manual Database Creation
• Uses SQL scripts to create the instance
• Most reliable, reproducible method
• Not as simple to customize

22
Automated Administration
P

• Scripting
• Write scripts generically to encourage reuse
• Scripts pull variables in from config files one
  per system or database
• Automatically parse out oratab, etc.
• Invest the time in building administration
  toolkits
• Standardize on one language
• All executions log results to some central
  location
• Use scripts to automatically install scripts
  post-database installation

23
Automated Administration
P

• Source Code Management
• Deploy a centralized SCM system across the
  database environment
• Check everything in
• When you install a database, check out the
  response file, tree of administrative scripts,
  database creation code, and crontab file
• Create tags for major code releases and tie them
  back to change control
• Upgrading administrative scripts becomes as easy
  as doing a tree update

24
Summary
P

• Automation across the database environment helps
• Reduce database deployment time
• Create a consistent set of databases
• Guarantee stability
• Centralizing configuration and code helps
• Ensure databases are always created with the
  correct/latest version of their config
• Move changes smoothly from dev-gtQA-gtprod
• Build clone/duplicate copies of databases based
  on their configurations at any point in time

25
Conclusion
P

• The increasing complexity of database
  environments are encouraging DBAs to automate
• Self-managing capabilities in the database reduce
  complexity and will continue to evolve
• Across databases, the focus is on policy and
  standardization

26

• QA

Matthew Zito mzito_at_gridapp.com