Oracle resource manager for CDB

Overview

You can create a resouce plan in CDB level or PDB level, or both. This post summarizes how to use resource manager in CDB. The CDB level resource plan controls resouce allocation between individual PDBs, and the PDB level resouce plan controls resouce allocation between consumer groups.

You have two options when creating resouce plan directives, plan directive for individual PDBs or plan directive for performance profiles.

There are three plan directive attributes you can set, shares, utilization_limit, and parallel_server_limit.

When you create resouce plan in PDB level, the following restrictions apply,

 A PDB resource plan cannot have subplans.
 A PDB resource plan can have a maximum of eight consumer groups.
 A PDB resource plan cannot have a multiple-level scheduling policy.

1. memory limits for PDBs

The following parameters control the memory limits for PDBs,
Prerequistes: NONCDB_COMPATIBLE=false, MEMORY_TARGET=0

DB_CACHE_SIZE
SHARED_POOL_SIZE
PGA_AGGREGATE_LIMIT
PGA_AGGREGATE_TARGET
SGA_MIN_SIZE
SGA_TARGET

2. I/O limits for PDBs

The following parameters cannot be set in non-CDB. They can be set in PDBs or root. The PDB level setting overrides setting in root.

MAX_IOPS
MAX_MBPS

3. the common procedures when create/update/delete a plan or plan directive

exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

4. create/update/delete CDB plan

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(
    plan => 'newcdb_plan',
    comment => 'CDB resource plan for newcdb');
  END;
/

BEGIN
  DBMS_RESOURCE_MANAGER.UPDATE_CDB_PLAN(
    plan => 'newcdb_plan',
    new_comment => 'CDB plan for PDBs in newcdb');
  END;
/

BEGIN
  DBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN(
  plan => 'newcdb_plan');
  END;
/

5. create/update/delete resource plan directive for individual PDBs

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
    plan => 'newcdb_plan',
    pluggable_database => 'salespdb',
    shares => 3,
    utilization_limit => 100,
    parallel_server_limit => 100);
  END;
/

BEGIN
  DBMS_RESOURCE_MANAGER.UPDATE_CDB_PLAN_DIRECTIVE(
    plan => 'newcdb_plan',
    pluggable_database => 'operpdb',
    new_shares => 1,
    new_utilization_limit => 10,
    new_parallel_server_limit => 20);
  END;
/

BEGIN
  DBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN_DIRECTIVE(
    plan => 'newcdb_plan',
    pluggable_database => 'operpdb');
  END;
/

6. create/update/delete resource plan directive for performance profiles

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
    plan => 'newcdb_plan',
    profile => 'gold',
    shares => 3,
    utilization_limit => 100,
    parallel_server_limit => 100);
  END;
/

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
    plan => 'newcdb_plan',
    profile => 'silver',
    shares => 2,
    utilization_limit => 40,
    parallel_server_limit => 40);
  END;
/

BEGIN
  DBMS_RESOURCE_MANAGER.UPDATE_CDB_PROFILE_DIRECTIVE(
    plan => 'newcdb_plan',
    profile => 'copper',
    new_shares => 1,
    new_utilization_limit => 10,
    new_parallel_server_limit => 20);
  END;
/

BEGIN
  DBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN_DIRECTIVE(
    plan => 'newcdb_plan',
    profile => 'operpdb');
  END;
/

7. update the default directive for PDBs

BEGIN
  DBMS_RESOURCE_MANAGER.UPDATE_CDB_DEFAULT_DIRECTIVE(
    plan => 'newcdb_plan',
    new_shares => 1,
    new_utilization_limit => 50,
    new_parallel_server_limit => 50);
END;
/

8. update the autotask directive

BEGIN
  DBMS_RESOURCE_MANAGER.UPDATE_CDB_AUTOTASK_DIRECTIVE(
    plan => 'newcdb_plan',
    new_shares => 1,
    new_utilization_limit => 75,
    new_parallel_server_limit => 75);
END;
/

9. enable/disable resource manager

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'newcdb_plan';
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'salespdb_plan';
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

10. monitoring resource manager

DBA_CDB_RSRC_PLAN_DIRECTIVES
V$RSRCPDBMETRIC
V$RSRCPDBMETRIC_HISTORY
DBA_HIST_RSRC_PDB_METRIC

tns lookup

A TNS name is a logical name that the users and applications use for connecting to the database. The name could be an alias to a service name, which is defined on a pluggable database, which is plugged into a container database. As a DBA, when you manage many databases, it could be a challenge to remember which container database and which pluggable database a TNS name is pointing to.

This script will help you to lookup a TNS name, and return the details of the database that it is pointing to: pluggable database name, container database name, service name, and host name.

pre-requsites:
You need to have a oracle client, and you to set the same password for user dbsnmp in all databases.

#!/bin/bash

function lookup
{
  if ((${#ORACLE_HOME} < 1));
  then
    export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
    export PATH=$PATH:$ORACLE_HOME/bin
  fi 
  sql="select '|' || sys_context('USERENV','DB_UNIQUE_NAME')|| '|' ||" 
  sql="${sql} sys_context('USERENV','SERVICE_NAME')|| '|' ||" 
  sql="${sql} regexp_substr(sys.database_name, '^[[:alnum:]]+') || '|' ||" 
  sql="${sql} regexp_substr(host_name, '^[[:alnum:]]+') info from v\$instance;"
  result=$(echo ${sql} | sqlplus -S dbsnmp/passwd@${db_name})
  if [[ ${result} == *"Invalid option"* ]];
  then
    echo "${db_name} cannot be reached, name is invalid or service is down"
  else
    database=$(echo ${result} | cut -d"|" -f2)
    service=$(echo ${result} | cut -d"|" -f3)
    pdb=$(echo ${result} | cut -d"|" -f4)
    host=$(echo ${result} | cut -d"|" -f5)
    if [ ${database} != ${pdb} ]; then
      echo "pluggable: ${pdb}"
    fi
    echo "database : ${database}"
    echo "service  : ${service}"
    echo "host_name: ${host}"
  fi
}

if [ $# -eq 1 ];
then
  db_name=$1
  lookup
else
  echo "usage: $0 name"
fi

Here is an exmaple,

$ tnslookup sales_dev

pluggable: SALES01
database : ORCL2
service  : SDEV.DOMAIN.COM
host_name: server01