1 / 45

Oracle Database Consolidation

Oracle Database Consolidation. Noel Yuhanna. Agenda. Current environment Why Consolidate? How to Consolidate? Challenges and Guidelines Resource Manager – OS and Database Steps in using DB Resource Manager Benchmark results Recommendations How to proceed?. Finance Financial Analyzer

brock
Download Presentation

Oracle Database Consolidation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Database Consolidation Noel Yuhanna

  2. Agenda • Current environment • Why Consolidate? • How to Consolidate? • Challenges and Guidelines • Resource Manager – OS and Database • Steps in using DB Resource Manager • Benchmark results • Recommendations • How to proceed? NOCOUG – Noel Yuhanna

  3. Finance Financial Analyzer General Ledger Cash Management Treasury Purchasing Payables Receivables Fixed Assets eTravel Self-Service Expenses Self-Service Purchasing Manufacturing Engineering Bills of Material Master Scheduling/MRP Capacity Work in Process Quality Cost Management Process Manufacturing Project Manufacturing Flow Manufacturing Advanced Planning& Scheduling Financials Manufacturing • Human Resources Supply Chain Projects Human Resources Human Resources Payroll Training Administration Time Management Advanced Benefits Self-Service Human Resources Projects Project Costing Project Billing Project Time & Expense Activity Management Gateway Project Connect Project Analysis Collection Pack Supply Chain Management Order Entry Purchasing Product Configuration Supplier Scheduling Supply Chain Planning Inventory Web Suppliers Advanced Planning & Scheduling Current Environment NOCOUG – Noel Yuhanna

  4. Instances .. (all over) Development UAT Test Production Training NOCOUG – Noel Yuhanna

  5. How many Instances? • 10 … • 100… • 1000… “One database per application…” NOCOUG – Noel Yuhanna

  6. Typical server utilization.. 60% 10% 5% 20% Average utilization = 30% 50% Unused system resources ! Target = 80-90% NOCOUG – Noel Yuhanna

  7. Why Consolidate? • Reduce TCO – License, staff, consulting • Improved availability -SLAs • Higher Security • Data Sharing & visibility • Globalization • Better application performance • Centralized backup and archive • Higher ROI – higher utilization • Bottom line = reduce TCO by 20% or more NOCOUG – Noel Yuhanna

  8. Challenges… • High short-term costs • Skilled resources • Potential loss of data • Ability to architect • Testing consolidation efforts • Understanding your workload • Loss of security control • Predicting the growth • Performance issues • Central point of failure NOCOUG – Noel Yuhanna

  9. Various types of consolidation • Location Consolidation • Data center/Centralized office • Better Manageability • Server/Storage Consolidation • Reduce # of Servers • Use of SAN storage • Better performance/scalability • Application/Database Consolidation • Reduce # of Instances • Better Availability/performance/scalability NOCOUG – Noel Yuhanna

  10. Database consolidation • Single Database • Multi-Instance Payroll Payroll Financials HR HR NOCOUG – Noel Yuhanna

  11. How to consolidate? • Single Database • Separate Schema • Assign roles and responsibility • Reconfigure Database Size • Reconfigure Application New Schema NOCOUG – Noel Yuhanna

  12. How to consolidate? • Multi-Instance • Database migrates as separate Instance • Co-exists with other instances on same server • Reconfigure Application Payroll Financials New Instance Existing Instance NOCOUG – Noel Yuhanna

  13. Single database approach • Pros • Provides data sharing • Less support and higher productivity • Higher resource utilization & better SLA’s • Cons • Outage can impact multiple applications • Difficult to consolidate – application dependencies • DBA support – space management • Recommendations • Useful if applications are closely inter-related NOCOUG – Noel Yuhanna

  14. Multi Instance approach • Pros • Each instance is independent • Some applications require separate instance • Helps reduce number of servers • Oracle performs well under multi-instance env • Cons • Each instance competes for system resources • No. of databases still remain same • Cannot optimize individual server or OS • Can only provide one level of availability, recoverability etc • Recommendations • Group databases with similar SLA • Multi domain Servers – useful for consolidation NOCOUG – Noel Yuhanna

  15. Multi Instance on OS Cluster • Pros • Easy to deploy • Each instance is independent • Utilizes idle node in cluster • Provides high availability – failover capability • Cons • No. of databases still remain same • Complex environment to manage • Requires lots of testing • Recommendations • Ensure each node is not over 70% utilized NOCOUG – Noel Yuhanna

  16. Oracle Instance Oracle Instance Cluster Interconnect S G A S G A Server Server Shared Storage What is Oracle 9i RAC ? NOCOUG – Noel Yuhanna

  17. RAC Features • Availability • Failover • Scalability • No application changes • 4-6 Nodes Cluster • Manageability • No special training or tools • No partitioning necessary NOCOUG – Noel Yuhanna

  18. Consolidation using RAC • DB Shared on existing RAC environment • Utilizes nodes in cluster more efficiently • Provides high availability for multiple apps • Support larger databases HR FIN HR FIN DB NOCOUG – Noel Yuhanna

  19. Guidelines for consolidation • Understand the workload • Application dependencies – integration issues • SLA requirements for the application • Hardware/database limit – scalability • Performance expectations • Data Security • Use Resource Manager technology NOCOUG – Noel Yuhanna

  20. Resource Manager • Enables DBA/SA to allocate system resources • Scheduling mechanism to track CPU time • Policy based administration • Operating System level • HP (PRM) • Solaris (Resource Mgr) • Database level • Oracle Database resource manager NOCOUG – Noel Yuhanna

  21. OS level Resource Manager • Manage critical shared resources • CPU, Memory, Disk • Groups processes/threads into various classes • Allocate CPU time to classes • Grouping done by name, owner, process id • Supports dynamic reconfiguration • Ensures critical processes gets priority • Limitations: It does not understand DB processes NOCOUG – Noel Yuhanna

  22. Oracle DB Resource Manager • Offered since Oracle 8i • Can guarantee users minimum resources • Can switch users between groups • Limit the degree parallelism • Distribute available CPU time to users • Can specify maximum number of sessions • Prevent execution when operation takes longer • Can change resource plan dynamically NOCOUG – Noel Yuhanna

  23. Oracle DB Resource Manager • Resource plan • Specifies how resources are distributed among users • Resource consumer Group • Group user sessions • Can be changed dynamically • Resource plan directive • Assign consumer groups to resource plans NOCOUG – Noel Yuhanna

  24. Oracle DB Resource Manager • Oracle Enterprise Manager • DBMS_RESOURCE_MANAGER PL/SQL Package • PLAN: Create,update,delete,cascade • CONSUMER_GROUP: Create,update,delete • PLAN_DIRECTIVE:Create,update,delete • PENDING_AREA:Create,validate,clear,submit • SWITCH:set_initial,switch groups • System Privileges: Grant, revoke – Package/Switch NOCOUG – Noel Yuhanna

  25. Oracle 9i Resource Manager • Enhancements • Automatic consumer group switching • Maximum estimated execution time • Limit the number of concurrent users -Queue • Undo Quota – operation aborted NOCOUG – Noel Yuhanna

  26. Resource Plan Consumer Group CPU-L1 CPU-L2 ONLINE 80% 0% REPORT 0% 60% ADMIN 10% 0% BATCH 0% 40% How to plan ? NOCOUG – Noel Yuhanna

  27. Steps involved • Create resource Plans • Create resource consumer groups • Create resource plan directives • Grant privileges to users/role • Assign users to resource consumer groups • Specify a plan to be used by the database • Monitor the database environment NOCOUG – Noel Yuhanna

  28. Step 1. Create Pending Area • Create a pending area DBMS_RESOURCE_MANAGER.CREATE_PENDING.AREA; • Validate changes DBMS_RESOURCE_MANAGER.VALIDATE_PENDING.AREA; • Clear changes DBMS_RESOURCE_MANAGER.CLEAR_PENDING.AREA; • Submit changes DBMS_RESOURCE_MANAGER.SUBMIT_PENDING.AREA; NOCOUG – Noel Yuhanna

  29. Step 2. Create Resource Plans DBMS_RESOURCE_MANAGER.CREATE_PLAN( PLAN => ‘plan_name’, COMMENT => ‘Comment here’); Example: DBMS_RESOURCE_MANAGER.CREATE_PLAN( PLAN => ‘SIEBEL_PLAN’, COMMENT => ‘Plan for Siebel DB Server’); * Can update, delete a Plan. NOCOUG – Noel Yuhanna

  30. Step 3. Create Resource consumer groups DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP( CONSUMER_GROUP => ‘group_name’, COMMENT => ‘Comment here’); Example: DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP( CONSUMER_GROUP => ‘OLTP’, COMMENT => ‘Group for OLTP users’); * Can update, delete consumer groups NOCOUG – Noel Yuhanna

  31. Step 4. Resource plan directives DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( PLAN => ‘Plan name’, GROUP_OR_SUBPLAN => ‘Name of consumer group or plan’, COMMENT => ‘Comment here’, CPU_P1 => ‘Specifies CPU % at 1st level …. UPTO 8’, PARALLEL_DEGREE_LIMIT_P1 => ‘Limit on Parallelism’); Example: DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( PLAN => ‘SIEBEL_PLAN’, GROUP_OR_SUBPLAN => ‘OLTP’, COMMENT => ‘Plan directive for OLTP users’, CPU_P1 => 80, PARALLEL_DEGREE_LIMIT_P1 => 4); NOCOUG – Noel Yuhanna

  32. Step 5A. Granting privileges DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP( GRANTEE_NAME => ‘grantee name’, CONSUMER_GROUP => ‘Consumer group name’, GRANT_OPTION => ‘True/False’); Example: DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP( GRANTEE_NAME => ‘NOEL’, CONSUMER_GROUP => ‘OLTP’, GRANT_OPTION => ‘TRUE’); NOCOUG – Noel Yuhanna

  33. Step 5B. Initial setup for users DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP( USER => ‘ user name’, CONSUMER_GROUP => ‘Initial consumer group’); Example: DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP( USER => ‘NOEL’, CONSUMER_GROUP => ‘OLTP’); NOCOUG – Noel Yuhanna

  34. Step 5C. Switching sessions/users DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS( SESSION_ID => ‘session_id’, SESSION_SERIAL => ‘session serial number’, CONSUMER_GROUP => ‘group_name’); DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER( USER => ‘User name’, CONSUMER_GROUP => ‘group_name’); Example: DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER( USER => ‘NOEL’, CONSUMER_GROUP => ‘OLTP’); NOCOUG – Noel Yuhanna

  35. Step 6. Activate the Plan • Persistent (init.ora) • RESOURCE_MANAGER_PLAN = “SIEBEL_PLAN” • Dynamic • ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = “SIEBEL_PLAN”; • ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=“”; NOCOUG – Noel Yuhanna

  36. Step 7. Monitor the database • VIEWS/SYSTEM TABLES • V$RSRC_CONSUMER_GROUP • V$RSRC_PLAN • V$RSRC_CONSUMER_GROUP_CPU_MTH • V$RSRC_PLAN_CPU_MTH • V$SESSION • DBA_RSRC_CONSUMER_GROUPS • DBA_RSRC_CONSUMER_GROUP_PRIVS • DBA_RSRC_MANAGER_SYSTEM_PRIVS • DBA_RSRC_PLANS • DBA_RSRC_PLAN_DIRECTIVIES NOCOUG – Noel Yuhanna

  37. Putting all together…. EXAMPLE…… BEGIN DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'CON_PLAN', COMMENT => 'COMMENT'); DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'oltp', COMMENT => 'Resource consumer group for OLTP'); DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'batch', COMMENT => 'BATCH'); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'CON_PLAN', GROUP_OR_SUBPLAN => 'oltp', COMMENT => 'OLTP SESS', CPU_P1 => 80); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'CON_PLAN', GROUP_OR_SUBPLAN => 'batch', COMMENT => 'BATCH',CPU_P2 => 100); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'CON_PLAN', GROUP_OR_SUBPLAN => 'OTHER_GROUPS',COMMENT => 'mandatory',CPU_P3 => 100); DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); END; NOCOUG – Noel Yuhanna

  38. Database Resource Manager • Useful feature for Consolidation • Plan your resource allocation • Can dynamically change plan • Guarantees application performance • No additional cost NOCOUG – Noel Yuhanna

  39. Benchmark – Churchill Ins. • Description • Pro*C program, shell scripts • Users performed various insurance quotes • Variety of Inserts, updates, Deletes • Over 782 various SQL Statements. • SUN Server 4 Cpus • Oracle 8.1 • Resource Manager CPU usage • OLTP users 90 % • Others 10% NOCOUG – Noel Yuhanna

  40. Benchmark – Churchill Ins. Results NOCOUG – Noel Yuhanna

  41. Recommendations • Single database • Use Database Resource Manager • Use RAC for large and high available databases • Multiple Instances on Server • Use Vendor specific OS level Resource Manager • Others • Use active-active OS cluster • Multi-domain functionality – offered by H/W vendors Do NOT user OS level Resource Manager along with DB Resource Manager NOCOUG – Noel Yuhanna

  42. How to proceed? • Analysis • Qualify the Applications/databases • Obtain baseline measurements • Identify the customizations • Review the complexity of the project • Performance criteria • Identify “sleeping” servers • Impact on other projects/applications • Constraints – OS, Patches, Versions, firmware • Design • Identify the resources • Architecture layout • Identify components that will be integrated • Put a plan together NOCOUG – Noel Yuhanna

  43. How to proceed? • Test • Ensure consolidation will work • Test, Test, Test. • Perform integrated testing • Implement • Stage the consolidation • Minimize outage window – use tools • Monitor • Review the environment NOCOUG – Noel Yuhanna

  44. Database consolidation • Current trend in Industry • Too many databases/servers • Helps meet SLAs • Provides availability, manageability, security… • Globalization – central location for Info • Reduces TCO – 20% or more • Consolidate today - get ready for next growth! NOCOUG – Noel Yuhanna

  45. Q & A NOCOUG – Noel Yuhanna

More Related