Oracle Database Consolidation

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?

3
Current Environment
4
Instances .. (all over)
Development
UAT
Test
Production
Training
5
How many Instances?

• 10
• 100
• 1000
• One database per application

6
Typical server utilization..
60
10
5
20
Average utilization 30
50
Unused system resources !
Target 80-90
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

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

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

10
Database consolidation
Payroll
Payroll
Financials
HR
HR

• Single Database
• Multi-Instance

11
How to consolidate?

• Single Database
• Separate Schema
• Assign roles and responsibility
• Reconfigure Database Size
• Reconfigure Application

New Schema
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
13
Single database approach

• Pros
• Provides data sharing
• Less support and higher productivity
• Higher resource utilization better SLAs
• Cons
• Outage can impact multiple applications
• Difficult to consolidate application
  dependencies
• DBA support space management
• Recommendations
• Useful if applications are closely inter-related

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

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

16
What is Oracle 9i RAC ?
17
RAC Features

• Availability
• Failover
• Scalability
• No application changes
• 4-6 Nodes Cluster
• Manageability
• No special training or tools
• No partitioning necessary

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

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

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

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

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

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_DIRECTIVECreate,update,delete
• PENDING_AREACreate,validate,clear,submit
• SWITCHset_initial,switch groups
• System Privileges Grant, revoke Package/Switch

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

26
Resource Plan
How to plan ?

• Consumer Group CPU-L1 CPU-L2
• ONLINE 80 0
• REPORT 0 60
• ADMIN 10 0
• BATCH 0 40

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

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

29
Step 2. Create Resource Plans

• DBMS_RESOURCE_MANAGER.CREATE_PLAN(
• PLAN gt plan_name,
• COMMENT gt Comment here)
• Example
• DBMS_RESOURCE_MANAGER.CREATE_PLAN(
• PLAN gt SIEBEL_PLAN,
• COMMENT gt Plan for Siebel DB Server)
• Can update, delete a Plan.

30
Step 3. Create Resource consumer groups

• DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
• CONSUMER_GROUP gt group_name,
• COMMENT gt Comment here)
• Example
• DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
• CONSUMER_GROUP gt OLTP,
• COMMENT gt Group for OLTP users)
• Can update, delete consumer groups

31
Step 4. Resource plan directives

• DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
• PLAN gt Plan name,
• GROUP_OR_SUBPLAN gt Name of consumer group or
  plan,
• COMMENT gt Comment here,
• CPU_P1 gt Specifies CPU at 1st level . UPTO
  8,
• PARALLEL_DEGREE_LIMIT_P1 gt Limit on
  Parallelism)
• Example
• DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
• PLAN gt SIEBEL_PLAN,
• GROUP_OR_SUBPLAN gt OLTP,
• COMMENT gt Plan directive for OLTP users,
• CPU_P1 gt 80,
• PARALLEL_DEGREE_LIMIT_P1 gt 4)

32
Step 5A. Granting privileges

• DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_
  GROUP(
• GRANTEE_NAME gt grantee name,
• CONSUMER_GROUP gt Consumer group name,
• GRANT_OPTION gt True/False)
• Example
• DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
• GRANTEE_NAME gt NOEL,
• CONSUMER_GROUP gt OLTP,
• GRANT_OPTION gt TRUE)

33
Step 5B. Initial setup for users

• DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(
• USER gt user name,
• CONSUMER_GROUP gt Initial consumer group)
• Example
• DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(
• USER gt NOEL,
• CONSUMER_GROUP gt OLTP)

34
Step 5C. Switching sessions/users

• DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SE
  SS(
• SESSION_ID gt session_id,
• SESSION_SERIAL gt session serial number,
• CONSUMER_GROUP gt group_name)
• DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_US
  ER(
• USER gt User name,
• CONSUMER_GROUP gt group_name)
• Example
• DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_US
  ER(
• USER gt NOEL,
• CONSUMER_GROUP gt OLTP)

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

36
Step 7. Monitor the database

• VIEWS/SYSTEM TABLES
• VRSRC_CONSUMER_GROUP
• VRSRC_PLAN
• VRSRC_CONSUMER_GROUP_CPU_MTH
• VRSRC_PLAN_CPU_MTH
• VSESSION
• DBA_RSRC_CONSUMER_GROUPS
• DBA_RSRC_CONSUMER_GROUP_PRIVS
• DBA_RSRC_MANAGER_SYSTEM_PRIVS
• DBA_RSRC_PLANS
• DBA_RSRC_PLAN_DIRECTIVIES

37
Putting all together.

• EXAMPLE
• BEGIN
• DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA()
• DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN gt
  'CON_PLAN',
• COMMENT gt 'COMMENT')
• DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUM
  ER_GROUP gt 'oltp',
• COMMENT gt 'Resource consumer group for OLTP')
• DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUM
  ER_GROUP gt 'batch',
• COMMENT gt 'BATCH')
• DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN
  gt 'CON_PLAN',
• GROUP_OR_SUBPLAN gt 'oltp', COMMENT gt 'OLTP
  SESS', CPU_P1 gt 80)
• DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN
  gt 'CON_PLAN',
• GROUP_OR_SUBPLAN gt 'batch', COMMENT gt
  'BATCH',CPU_P2 gt 100)
• DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN
  gt 'CON_PLAN',
• GROUP_OR_SUBPLAN gt 'OTHER_GROUPS',COMMENT gt
  'mandatory',CPU_P3 gt 100)
• DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA()
• DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA()
• END

38
Database Resource Manager

• Useful feature for Consolidation
• Plan your resource allocation
• Can dynamically change plan
• Guarantees application performance
• No additional cost

39
Benchmark Churchill Ins.

• Description
• ProC 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

40
Benchmark Churchill Ins.
Results
Concurrent users Avg. time without DRM for OLTP Avg. time with DRM for OLTP
100 OLTP 0 Others 33.92 33.48
100 OLTP 100 Others 70.86 36.39
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

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

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

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!

45

• Q A