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

1. 156 5th Avenue Penthouse New York, NY 10010 P: 646.452.4100 www.gridapp.com Strategies & Tools for Centralizing and Automating Database Management Delivered by: Matthew Zito, Chief Scientist

2. 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 Today’s Agenda

3. 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 The database landscape is changing…

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

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

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 (doesn’t 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 “C”s • Standardized Oracle installation • Response files • Technical tools • Templated DBCA • Templates • Response files • Administration • Scripts • Schema & Templates

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

14. 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 Automation Concepts – continued

15. Code ORACLE_HOME ASM & Clusterware Home-grown scripts Content Schema PL/SQL Configuration Initialization parameters Secondary application config (Data Guard, etc.) ASM layouts The Three “C”s of Configuration Management

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

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

18. 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" Standardized Oracle Installation P

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

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

21. 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 Database Creation P

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

23. 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 Automated Administration P

24. 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 Summary P

25. 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 Conclusion P

26. Q&A Matthew Zito – mzito@gridapp.com