Delivered by:Matthew Zito, Chief Scientist

156 5th AvenuePenthouse

New York, NY 10010P: 646.452.4100

www.gridapp.com

Strategies & Tools for Centralizing and Strategies & Tools for Centralizing and Automating Database ManagementAutomating Database Management

February 21, 2006February 21, 2006

Today’s Agenda

• Introduction• The Changing Database Landscape• Automation within the database• Automation Across databases• Automation and configuration

management• The Three C’s• Response files, templates, etc.• Code and script management• Q&A

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

…which leads to-

• Higher levels of required skill for DBA– Can’t 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

So what’s to be done?

• DBAs can’t be generalists anymore– Focus on development– Focus on new technology deployment

• Create process – Less worrying about what’s happening to systems– More effective delegation

• Reduce manual interaction with databases– Tools– Scripts– Let software do the work

Automation is the answer

• Automation within a database– “Self-managing”– “Self-healing”

• Automation across databases– Policies– Automated deployment– Schema management

Internal Automation

• Storage– ASM– Tablespace management

• Operational– Automatic SGA management– Automated Failover

• Performance– ADDM– Client &listener load balancing

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

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

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 (doesn’t really work that well)

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….

Inter-database Automation Summary

• Concepts– Automation & Process– The three “C”s

• Standardized Oracle installation– Response files– Technical tools

• Templated DBCA– Templates– Response files

• Administration– Scripts– Schema & Templates

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

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

The Three “C”s 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

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 – won’t ask any questions• Suppressed mode – uses a response file and prompts for

missing parameters

– Response files are in the format name=value

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

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

– It’s 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_NAME=OraDBHome1"

Automated Database Creation

P

• DBCA– Oracle’s 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

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 doesn’t 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

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

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

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

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->QA->prod– Build clone/duplicate copies of databases based on

their configurations at any point in time

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

Q&A

Matthew Zito – mzito@gridapp.com