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? Nov 21st 2002 NOCOUG – Noel Yuhanna Current Environment Finance Financial Analyzer General Ledger Cash Management Treasury Purchasing Payables Receivables Fixed Assets eTravel Self-Service Expenses Self-Service Purchasing Manufacturing Financials Manufacturing Engineering Bills of Material Master Scheduling/MRP Capacity Work in Process Quality Cost Management Process Manufacturing Project Manufacturing Flow Manufacturing Advanced Planning & Scheduling Human Resources Supply Chain Projects Human Resources Human Resources Payroll Training Administration Time Management Advanced Benefits Self-Service Human Resources Projects Purchasing Supplier Scheduling Inventory Advanced Planning & Scheduling Project Costing Project Billing Project Time & Expense Activity Management Gateway Project Connect Project Analysis Collection Pack Supply Chain Management Order Entry Product Configuration Supply Chain Planning Web Suppliers NOCOUG – Noel Yuhanna Nov 21st 2002 Instances .. (all over) Development UAT Test Production Training Nov 21st 2002 NOCOUG – Noel Yuhanna How many Instances?    10 … 100… 1000… “One database per application…” NOCOUG – Noel Yuhanna Nov 21st 2002 Typical server utilization.. 60% 10% 5% 20% Average utilization = 30% 50% Unused system resources ! Target = 80-90% NOCOUG – Noel Yuhanna Nov 21st 2002 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 Nov 21st 2002 NOCOUG – Noel Yuhanna 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 Nov 21st 2002 Various types of consolidation  Location Consolidation   Data center/Centralized office Better Manageability Reduce # of Servers Use of SAN storage Better performance/scalability  Server/Storage Consolidation     Application/Database Consolidation   Reduce # of Instances Better Availability/performance/scalability Nov 21st 2002 NOCOUG – Noel Yuhanna Database consolidation Payroll Payroll Financials HR HR   Single Database Multi-Instance NOCOUG – Noel Yuhanna Nov 21st 2002 How to consolidate?  Single Database     Separate Schema Assign roles and responsibility Reconfigure Database Size Reconfigure Application New Schema NOCOUG – Noel Yuhanna Nov 21st 2002 How to consolidate?  Multi-Instance    Database migrates as separate Instance Co-exists with other instances on same server Reconfigure Application Payroll New Instance Financials Existing Instance NOCOUG – Noel Yuhanna Nov 21st 2002 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 Useful if applications are closely inter-related Nov 21st 2002  Recommendations  NOCOUG – Noel Yuhanna 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 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 Group databases with similar SLA Multi domain Servers – useful for consolidation Nov 21st 2002  Cons      Recommendations   NOCOUG – Noel Yuhanna Multi Instance on OS Cluster  Pros     Easy to deploy Each instance is independent Utilizes idle node in cluster Provides high availability – failover capability No. of databases still remain same Complex environment to manage Requires lots of testing Ensure each node is not over 70% utilized Nov 21st 2002  Cons     Recommendations  NOCOUG – Noel Yuhanna What is Oracle 9i RAC ? Oracle Instance Cluster Interconnect Oracle Instance S G A Server Server S G A Shared Storage NOCOUG – Noel Yuhanna Nov 21st 2002 RAC Features  Availability  Failover No application changes 4-6 Nodes Cluster No special training or tools No partitioning necessary  Scalability    Manageability   NOCOUG – Noel Yuhanna Nov 21st 2002 Consolidation using RAC     DB Shared on existing RAC environment Utilizes nodes in cluster more efficiently Provides high availability for multiple apps Support larger databases FIN HR FIN HR DB NOCOUG – Noel Yuhanna Nov 21st 2002 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 Nov 21st 2002 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) Oracle Database resource manager  Database level  NOCOUG – Noel Yuhanna Nov 21st 2002 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 Nov 21st 2002 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 Nov 21st 2002 Oracle DB Resource Manager  Resource plan  Specifies how resources are distributed among users Group user sessions Can be changed dynamically Assign consumer groups to resource plans  Resource consumer Group    Resource plan directive  NOCOUG – Noel Yuhanna Nov 21st 2002 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 Nov 21st 2002 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 Nov 21st 2002 Resource Plan How to plan ? Consumer Group ONLINE REPORT ADMIN BATCH CPU-L1 80% 0% 10% 0% CPU-L2 0% 60% 0% 40% NOCOUG – Noel Yuhanna Nov 21st 2002 Steps involved 1. 2. 3. 4. 5. 6. 7. 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 Nov 21st 2002 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 Nov 21st 2002 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 Nov 21st 2002 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 Nov 21st 2002 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 Nov 21st 2002 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 Nov 21st 2002 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 Nov 21st 2002 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 Nov 21st 2002 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 Nov 21st 2002 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 Nov 21st 2002 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 Nov 21st 2002 Database Resource Manager      Useful feature for Consolidation Plan your resource allocation Can dynamically change plan Guarantees application performance No additional cost NOCOUG – Noel Yuhanna Nov 21st 2002 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 Nov 21st 2002 Benchmark – Churchill Ins. Results Concurrent users 100 OLTP 0 Others 100 OLTP 100 Others Avg. time without DRM for OLTP 33.92 Avg. time with DRM for OLTP 33.48 70.86 36.39 NOCOUG – Noel Yuhanna Nov 21st 2002 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 Nov 21st 2002 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 Identify the resources Architecture layout Identify components that will be integrated Put a plan together Nov 21st 2002  Design     NOCOUG – Noel Yuhanna How to proceed?  Test    Ensure consolidation will work Test, Test, Test. Perform integrated testing  Implement   Stage the consolidation Minimize outage window – use tools Review the environment  Monitor  NOCOUG – Noel Yuhanna Nov 21st 2002 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 Nov 21st 2002 Q&A NOCOUG – Noel Yuhanna Nov 21st 2002