Tag: Consolidation

Feedback of Modern Consolidated Database Environment

 

Since the launch of Oracle 12c R1 Beta Program (August 2012) at Trivadis, we have been intensively testing, engineering and implementing Multitenant architectures for our customers.

Today, we can provide our feedbacks and those of our customers!

The overall feedback related to Oracle Multitenant is very positive, customers have been able to increase flexibility and automation, improving the efficiency of the software development life cycles.

Even the Single-tenant configuration (free of charge) brings few advantages compared to the non-CDB architecture. Therefore, from a technology point of view I recommend adopting the Container Database (CDB) architecture for all Oracle databases.

 

Examples of Multitenant architectures implemented

Having defined Oracle Multitenant a technological revolution on the space of relational databases, when combined with others 12c features it becomes a game changer for flexibility, automation and velocity.

Here are listed few examples of successful architectures implemented with our customers, using Oracle Container Database (CDB):

 

  • Database consolidation without performance and stability compromise here.

 

  • Multitenant and DevOps here.

 

  • Operating Database Disaster Recovery in Multitenant environment here.

 

 

 

Adding Pluggable Databases to an existing Multitenant Data Guard environment

We all know the benefits of the Oracle Multitenant cosolidation “Many-as-One”, one container (CDB) operation for wich many Pluggable Databases (PDBs) can take benefit; for example one CDB backup protects all PDBs stored inside the container itself.

While among the DBAs the setup of Oracle Data Guard is become more than a standandard routine,  described in thousands of Internet pages and blogs (one example available here), this post explains how to add new Pluggable Databases (PDBs) to an existing Multitenant environment protected by Data Guard.

 

How to create PDBs in Oracle Multitenant environment protected by Data Guard

There are multiple scenarios of PDB creation and they differently integrate within the Data Guard architecture. The easiest way to proceed consists in creating a new Pluggable Database using the SEED PDB:

  • PDB creation from SEED
    The creation of a brand new empty pluggable database is automatically replicated to each physical standby database. No additional action is required.

 

Unfortunately, this option is not always applicable because the new PDB should be a clone of an existing one. Therefore, it is important for the DBA to understand how integrating new non-empty Pluggable Database on a Multitenant and Data Guard environment without impacting the pre-existing setup.

 

  • PDB clone 

Cloning a PDB in a Data Guard environment requires few additional steps, which changes across the different Oracle versions and when Active Data Guard option is in use. While remote cloning the PDB the option STANDBYS=NONE should be used to defer the PDB replica to the Standby container. Then it is possible to replicate and protect the newly cloned PDB with Data Guard.

A full example of how to perform those tasks is reported below.

 

Cloning a PDB via DB Link using the STANDBYS=NONE option

create pluggable database PCJORD from PCLORD@ccls01_PCLORD tempfile reuse STANDBYS=NONE;

 

Open the newly cloned PDB

alter pluggable database PCJORD open instances=all;

 

On the Standby Container Restore PDB from Primary

run{
set newname for pluggable database PCJORD to new;
restore pluggable database PCJORD from service CMJP01;
switch datafile all;
}

 

Connect to the Standby container and STOP the Apply Process

dgmgrl
connect sys/xxxxxxxx@CMJP01
edit database 'CMJS01' set state='APPLY-OFF';

 

If Active Data Guard is in use, re-start the Standby container in MOUNT

srvctl stop database -db CMJS01 

sqlplus / as sysdba
startup mount

 

Enable the PDB recovery on the Standby Container

alter session set container=PCJORD;
alter pluggable database enable recovery;

 

Connect to the Standby container and RE-START the Apply Process

dgmdrl
connect sys/xxxxxxxx@CMJP01
edit database 'CMJS01' set state='APPLY-ON';

 

If Active Data Guard is in use Open the Container in Read Only

alter database open;

 

 

 

 

Oracle Resource Manager

 

#############################################################
##    How to implement Oracle Resource Manager granting    ##
##    CONSUMER GROUPS to Clusterware services              ##
#############################################################

--Create a service for OLTP sessions
srvctl add service -d dbrac10 -s DBRAC10_OLTP -r dbrac11,dbrac12,dbrac13
srvctl start service -d dbrac10 -s DBRAC10_OLTP

--Create a service for BATCH sessions
srvctl add service -d dbrac10 -s DBRAC10_BATCH -r dbrac11,dbrac12,dbrac13
srvctl start service -d dbrac10 -s DBRAC10_BATCH


###################################################################
## Resource Plan Design:
## MGMT_P1=75% SYS_GROUP,  MGMT_P2=80% OLTP, MGMT_P2=10% BATCH, MGMT_P2=5%
## ORA$AUTOTASK_SUB_PLAN,  MGMT_P2=5% ORA$DIAGNOSTICS, MGMT_P3=70% OTHER_GROUPS           
###################################################################       

## Resource Plan Implementation:
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'REAL_TIME_PLAN', COMMENT => 'Respurce Plan for OLTP database');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'OLTP',CATEGORY => 'INTERACTIVE', COMMENT => 'OLTP sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'BATCH', CATEGORY => 'BATCH', COMMENT => 'BATCH sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'OLTP', COMMENT => 'OLTP group', MGMT_P2 => 80);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'BATCH', COMMENT => 'BATCH group',
MGMT_P3 => 70, PARALLEL_DEGREE_LIMIT_P1 => 6, ACTIVE_SESS_POOL_P1 => 4, MAX_IDLE_TIME => 240);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'SYS_GROUP', COMMENT => 'SYS group', MGMT_P1 => 70);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'OTHER group', MGMT_P4 => 50);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN',GROUP_OR_SUBPLAN => 'ORA$AUTOTASK_SUB_PLAN', COMMENT => 'ORA$AUTOTASK_SUB_PLAN group', MGMT_P3 => 20);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'ORA$DIAGNOSTICS', COMMENT => 'ORA$DIAGNOSTICS group', MGMT_P3 => 10);
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (DBMS_RESOURCE_MANAGER.SERVICE_NAME, 'DBRAC10_OLTP', 'OLTP');
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.SERVICE_NAME, 'DBRAC10_BATCH', 'BATCH');
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/

###################################################################


## Grant the Switch to the Users
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();    
dbms_resource_manager_privs.grant_switch_consumer_group ('PERF_TEST','OLTP',FALSE); 
dbms_resource_manager_privs.grant_switch_consumer_group ('PERF_TEST','BATCH',FALSE); 
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/

###################################################################       
## Enable the Resource plan with the FORCE Option to avoid the Scheduler window to
## activate a different plan during the job execution.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:REAL_TIME_PLAN';
###################################################################       

## Example of Group and Plan Deletion:

BEGIN
DBMS_RESOURCE_MANAGER.DELETE_PLAN (PLAN => 'REAL_TIME_PLAN');
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP(CONSUMER_GROUP => 'OLTP');
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP(CONSUMER_GROUP => 'BATCH');
END;
/


###################################################################
## SQL Queries for monitoring Resource Manager  utilization:
###################################################################

## Check the service name used by each session
select inst_id, username, SERVICE_NAME, count(*) from gv$session 
where SERVICE_NAME <>'SYS$BACKGROUND'
group by inst_id, username, SERVICE_NAME order by  order by 2,3,1;


## List the Active Resource Consumer Groups:
select INST_ID, NAME, ACTIVE_SESSIONS, EXECUTION_WAITERS, REQUESTS, 
CPU_WAIT_TIME, CPU_WAITS, CONSUMED_CPU_TIME, YIELDS, QUEUE_LENGTH, 
ACTIVE_SESSION_LIMIT_HIT from gV$RSRC_CONSUMER_GROUP where name in 
('SYS_GROUP','BATCH','OLTP','OTHER_GROUPS') order by 2,1;