Exadata interview

 

Exadata interview

Getting ready for a Exadata interview?

Make sure to refresh your knowledge by reviewing this list of Exadata Interview Questions.

What environment is a good fit for Exadata?

Exadata was originally designed for the warehouse environment. Later it was enhanced for use in the OLTP databases as well.

What are the advantages of Exadata?

The Exadata cluster allows for consistent performance while allowing for increased throughput. As load increases on the cluster the performance remains consistent by utilizing inter-instance and intra-instance parallelism.

It should not be expected that just moving to Exadata will improve performance. In most cases it will especially if the current database host is overloaded.

What is the secret behind Exadata’s higher throughput?

Exadata ships less data through the pipes between the storage and the database nodes and other nodes in the RAC cluster.

Also it’s ability to do massive parallelism by running parallel processes across all the nodes in the cluster provides it much higher level of throughput.

It also has much bigger pipes in the cluster using Infiniband interconnect for inter-instance data block transfers as high as 5X of fiberchannel networks.

What are the different Exadata configurations?

The Exadata Appliance configuration comes as a Full Rack, Half Rack, Quarter Rack or 1/8th rack.

The Full Rack X2-2 has 6 CPU’s per node with Intel Xeon 5670 processors and a total of 8 Database Server nodes. These servers have 96GB of memory on each node. A total of 14 Storage server cells communicate with the storage and push the requested data from the storage to the compute nodes.

The Half Rack has exactly half the capacity. It has 6 CPU’s per node with core Intel Xeon 5670 processors and a total of 4 Database Server nodes. It has 96GB of memory per database server node with a total of 7 Storage server cells.The Quarter Rack exactly half the capacity. It has 6 CPU’s per node with core Intel Xeon 5670 processors and a total of 2 Database Server nodes. It has 96GB of memory per database server node with a total of 3Storage server cells.

The Exadata is also available in the 1/8th Rack configuration.

What are the key Hardware components?

·         DB Server

·         Storage Server Cells

·         High Speed Infiniband Switch

·         Cisco Switch

What are the Key Software Features?

·         Smart Scan,

·         Smart Flash Cache

·         Storage Index

·         Exadata Hybrid Columnar Compression (EHCC)

·         IORM (I/O Resource Manager)

What is a Cell and Grid Disk?

Cell and Grid Disk are a logical component of the physical Exadata storage. A cell or Exadata Storage server cell is a combination of Disk Drives put together to store user data. Each Cell Disk corresponds to a LUN (Logical Unit) which has been formatted by the Exadata Storage Server Software. Typically, each cell has 12 disk drives mapped to it.

Grid Disks are created on top of Cell Disks and are presented to Oracle ASM as ASM disks. Space is allocated in chunks from the outer tracks of the Cell disk and moving inwards. One can have multiple Grid Disks per Cell disk.

What is IORM?

IORM stands for I/O Resource Manager.

It manages the I/O demand based on the configuration, with the amount of resources available. It ensures that none of the I/O cells become oversubscribed with the I/O requests. This is achieved by managing the incoming requests at a consumer group level.

Using IORM, you can divide the I/O bandwidth between multiple databases.

To implement IORM resource groups, consumers and plans need to be created first.

What is hybrid columnar compression?

Hybrid Columnar compression, also called HCC, is a feature of Exadata which is used for compressing data at column level for a table.

It creates compression data units which consist of logical grouping of columns values typically having several data blocks in it. Each data block has data from columns for multiple rows.

This logarithm has the potential to reduce the storage used by the data and reduce disk I/O enhancing performance for the queries.

The different types of HCC compression include:

·  Query Low

·  Query High

·  Archive High

·  Archive Low

What is Flash cache?

Four 96G PCIe flash memory cards are present on each Exadata Storage Server cell which provide very fast access to the data stored on it.

This is further achieved by also provides mechanism to reduces data access latency by retrieving data from memory rather than having to access data from disk. A total flash storage of 384GB per cell is available on the Exadata appliance.

What is Smart Scan?

It is a feature of the Exadata Software which enhances the database performance many times over. It processes queries in an intelligent way, retrieving specific rows rather than the complete blocks.

It applies filtering criteria at the storage level based on the selection criteria specified in the query.

It also performs column projection which is a process of sending only required columns for the query back to the database host/instance.

What are the Parallelism instance parameter used in Exadata?

The parameter PARALLEL_FORCE_LOCAL can be specified at the session level for a particular job.

How do you Test performance of Exadata?

You can use the “calibrate” commands at the cellcli command line.

What are the ways to migrate onto Exadata?

Depending on the downtime allowed there are several options:

·         Oracle DataGuard

·         Traditional Export/Import

·         Tablespace transportation

·         Goldengate Replication after a data restore onto Exadata.

What types of operations does Exadata “offload”?

Some of the operations that are offloaded from the database host to the cell servers are:

·         Predicate filtering

·         Column project filtering

·         Join processing

·         Backups

What is cellcli?

This is the command line utility used to managed the cell storage.

How do you create obtain info on the Celldisks?

At the cellcli command line you can issue the “list celldisk” command.

How would you create a grid disk?

At the cellcli command you would need to issue the “create grididsk all ..” command.

What are the cellinit.ora and the cellip.ora files used for?

These files have the hostnames and the ip address of all the nodes in the cluster. They are used to run commands on remote database and cellserver nodes from a local host.

Example:cat /etc/oracle/cell/network-config/cellinit.oraipaddress1=192.168.47.21/24

$ cat /etc/oracle/cell/network-config/cellip.oracell=”192.168.47.21:5042″cell=”192.168.47.22:5042″cell=”192.168.47.23:5042″

What operating systems does Exadata support?

Exadata has traditionally run Oracle Linux OS. Recently, Solaris has also been made available on this engineered system.

To prepare for your Oracle DBA interview here are some additional questions focusing on other database areas.

What is Exadata?

exadata is pre-configured combination of hardware and software which provides a platform to run the Oracle Database.

What is flash cache and how it works?

The flash cache is a hardware component configured in the exadata storage cell server which delivers high performance in read and write operations.

Primary task of smart flash cache is to hold frequently accessed data in flash cache so next time if same data required than physical read can be avoided by reading the data from flash cache.

What are the types of EHCC?

Query Low

Query High

Archive High

Archive Low

What is the purpose of spine switch?

Spine switch is used to connect or add more Exadata machine in the cluster

What is ASR?

ASR is the tool to manage the Oracle hardware. Full form of ASR is Auto Service Request.

Whenever any hardware fault occurs ASR automatically raise SR in Oracle Support and send notification to respective customer.

What is the difference between cellcli and dcli?

Cellcli can be used on respective cell storage only.

DCLi (Distributed command Line Utility) – DCLI can be used to replicate command on multipla storage as well as DB servers.

What is the difference between wright-through and write-back flashcache mode?

writethrough –> Falshcache will be used only for reading purpose

writeback –> Flashcache will be used for both reading and writing

Exadata sizing configuation

Full Rack

Half Rack

Quater Rack

1/8th Rack

What are the steps to create DBFS?

Create Directory

Create Tablespace on database which you are going to use for DBFS

Create user for DBFS

Grant required privileges to created user

Now connect to database with created user

Create dbfs filesystem by invoking dbfs_create_filesystem_advanced

Mount file system by starting dbfs_client

What is the difference between DBRM and IORM?

DBRM is the feature of database while IORM is the feature of storage server software.

What is smart flash cache?

Flash cache is the PCIe (Peripheral Components Interconnect Express) card which is plugged into the back end of the storage cell.

How smart scan works?

If any query executes on database server than database server sends the extents and metadata to the storage cell.

Smart scan will scan data blocks to identify relevant rows and columns.

Once data identified by smart scan, it will return to database with only appropriate rows

and columns.

Once DB server gets the data, it will assemble returned data into result set.

This operation will save the bandwidth as well CPUs and memory cost on database server

as whole sql processing happens on storage server.

Get through the interview bar with our selected interview questions for Oracle Exadata enthusiasts

What are the pre-requisites to configure ASR?

Access to My Oracle Support

Internet connectivity using HTTPS

Network connectivity from ASR server to Exadata components

Which MOS ID I should refer for latest patch update?

MOS 888828.1

Which tool is used to generate initial configuration files based on customer’s data?

OEDA (Oracle Exadata Deployment Assistance)

What are the unique features of Exadata?

Smart Scan (Cell Offload)

Flash cache

EHCC (Exadata Hybrid Columnar Compression)

IORM (IO Resource Manager)

Storage Index

Which all networks available in Exadata?

Infiniband Network

ILOM and Management Network

Client/Public Network

What are the Exadata Health check tools available?

Exacheck

sundiagtest

oswatcher

OEM 12c

What is client or public network in exadata?

Client or public network is used to established connectivity between database and application.

What are the steps involved for initial Exadata configuration?

Initial network preparation

Configure Exadata servers

Configure Exadata software

Configure database hosts to use Exadata

Configure ASM and database instances

Configure ASM disk group for Exadata

What is iDB protocol?

iDB stands for intelligent database protocol. It is a network based protocol which is responsible to

communicate between storage cell and database server.

What is LIBCELL?

Libcell stands for Library Cell which is linked with Oracle kernel. It allows oracle kernel to talk with the storage server via network based instead of operating system reads and writes.

Which packaged is used by compression adviser utility?

DBMS_COMPRESSION package

What is the primary goal of storage index?

Storage indexes are a feature unique to the Exadata Database Machine whose primary goal is to reduce the amount of I/O required to service I/O requests for Exadata Smart Scan.

What is smart scan offloading?

Offloading and Smart Scan are two terms that are used somewhat interchangeably. Exadata Smart

Scan offloads processing of queries from the database server to the storage server.

Processors on the Exadata Storage Server process the data on behalf of the database SQL query. Only the data requested in the query is returned to the database server.

What is checkip and what the use of it?

Checkip is the OS level script which contains IP address and hostname which will be used by Exadata in configuration phase. It checks network readiness like proper DNS configuration, it also checks there is no IP duplication in the network by pinging it which not supposed to ping initially.

Which script is used to reclaim the disk space of unused operating system?

For Linux: reclaimdisks.sh

For Solaris: reclaimdisks.pl

What should be ASM space allocation if backup performed internally?

40% storage space allocation for DATA disk group

60% storage space allocation for RECO disk group

How database server communicates to storage cell?

Database server communicates with storage cell through infiniband network.

Can I have multiple celldisk for one grid disk?

No. Celldisk can have multiple griddisk but griddisk cannot have multiple celldisk

How many FMods available on each flash card?

Four FMods (Flash Modules) are available on each flash card.

Which processes are used by storage software?

Cellsrv – Cell Server

MS- management server

RS – Restart Server

List the steps for replacing the damaged physical flash disk.

Identify damaged flash disk

Power off the cell

Replace flash card

Power on the cell

Verify and confirm new flash card

What is smart flash log?

Smart flash log is a temporary storage area on Exadata smart flash cache to store redoes log data.

Which parameter is used to enable and disable the smart scan?

cell_offload_processing

How to check infiniband topology?

We can verify infiniband switch topology by executing verify-topology script from one of our database server.

Can we use HCC on non-exadata environment?

No, HCC is only available data stored on Exadata storage server.

What is resource plan?

It is collection of plan directives that determines how database resources are to be allocated.

What is DBFS?

DBFS stands for Database File system which can be built on ASM disk group using database tablespace.

What are the major steps involved for cell server patching?

Check and note down existing configuration of cell

Clean up any previous patchmgr utility

Verify that the cells meet prerequisite checks

Patch cell server using patchmgr

Validation updated cell

What is the purpose of infiniband spine switch?

Spine switch is used to connect multiple exadata database machines.

What is OEM?

OEM is Oracle Enterprise Manager which is centralized tool to monitor and administer systems as well software.

What is offload block filtering?

Exadata storage server filters out the blocks that are not required for the incremental backup in progress so only the blocks that are required for the backup are sent to the database.

Which command is used to monitor BCT?

SQL>select filename,status, bytes from v$block_change_tracking;

How to add memory into database server?

Power off database server

Add memory expansion into server

Power on the ser

Out Of Place (OOP) Patching: Oracle 19c RAC (19.7.0) to Oracle 19c RAC (19.9.0) using 2-Step Method with Rollback Option

 

Out Of Place (OOP) Patching: Oracle 19c RAC (19.7.0) to Oracle 19c RAC (19.9.0) using 2-Step Method with Rollback Option

https://yvrk1973.blogspot.com/2020/10/out-of-place-oop-patching-oracle-19c.html?m=1

Thank you Y V Ravi Kumar

Oracle Data Guard Fast-Start Failover using Observer in Failover Scenario

 

Oracle Data Guard Fast-Start Failover using Observer in Failover Scenario

https://yvrk1973.blogspot.com/2020/11/oracle-data-guard-fast-start-failover.html?m=1

Patching Exadata

 

How To Patch An Exadata

https://blog.pythian.com/patch-exadata-part-1-introduction-prerequisites/

https://blog.pythian.com/patch-exadata-part-2-cells-ib-db-servers/

https://blog.pythian.com/patch-exadata-part-3-grid-database-oh-patching/

https://blog.pythian.com/patch-exadata-part-4-rollback-procedure/

https://blog.pythian.com/patch-exadata-part-5-troubleshooting/

https://blog.pythian.com/patch-exadata-part-6-timing/

Upgrading and Patching Exadata to 18c and 19c

https://hiteshgondalia.wordpress.com/2020/09/08/upgrading-and-patching-exadata-to-18c-and-19c/

Shutdown and Startup Exadata

https://hiteshgondalia.wordpress.com/2020/09/09/shutdown-and-startup-exadata/

Below are the some of the good article related to this topics.

• https://oracleracdba1.wordpress.com/2013/08/14/steps-to-shut-down-or-reboot-an-exadata-storage-cell-without-affecting-asm/
• http://www.oracle-ckpt.com/shut-down-or-reboot-an-exadata-storage-cell-without-affecting-asm-for-maintenance/
• https://dba010.com/2017/10/23/restart-exadata-storage-cell-service-without-affecting-asm/

https://hiteshgondalia.wordpress.com/2020/09/16/exadata-storage-expansion/

 

Configure the Resource Manager

Ref: Thanks 

https://hiteshgondalia.wordpress.com/configure-the-resource-manager/#b1

Configure the Resource Manager

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] Setup the Resource Manager for 12c Non CDB.
[2] Test RM for elapsed time limit exceededM
[3] Test RM for parallel server limit
[4] Test RM for IO Limits (SWITCH_IO_MEGABYTES)
[5] Test RM for Active Sessions Limit(active_sess_pool_p1)
[6] Test RM for Undo Limit (undo_pool)
[7] Automate active respective plan on their schedule window.

So Resource Manager for 12c is also pretty the same as Resource Manager 
for 11g for a database plus the CDB Resource Plans, where you can control 
how many "shares" and the limitations for each PDB will have in a 
multi-container environment.

In 12c now you can also set the resource plan directive PARALLEL_STMT_CRITICAL 
to BYPASS_QUEUE for a high-priority consumer group so that parallel statements 
from the consumer group bypass the parallel statement queue.

So the most important here is you to know how to create all the Resource Manager 
objects (Plan, Consumer Groups, Directives, etc) using both SQL*Plus and Cloud Control 
(preferable because it's faster).

In the official documentation available during the exam, on section 27 and 44 
specified below, there are good examples that you may copy, adapt and paste to 
save several minuted during the exam.

Path to Documentation:
Administrator’s Guide -> 27 Managing Resources with Oracle Database Resource Manager
Administrator’s Guide -> 44 Using Oracle Resource Manager for PDBs with SQL*Plus
Administrator’s Guide -> 45 Using Oracle Resource Manager for PDBs with Cloud Control


The Database Resource Manager helps you manage how resources such as CPU 
and Parallel Queuing are allocated among pluggable databases or user sessions.

#Container Database
CDB Resource Plans : Define Resource Plans for Consolidated Databases 
which contain directives that specify how resources are allocated to 
pluggable databases.

#Pluggable Database

Consumer Groups 
-> Define Consumer Groups, which are user sessions grouped together based 
   on resource processing requirements.

Consumer Group Mappings 
-> Define consumer group mapping rules, which are used to map user sessions 
   to consumer groups.

Plans 
-> Define Resource Plans for a single database, which contain directives 
   that specify how resources are allocated to Consumer Groups.

Performance Statistics
-> Monitor the statistics for the currently enabled Resource Plan.

44 Using Oracle Resource Manager for PDBs with SQL*Plus
44.8.1 Viewing CDB Resource Plans

COLUMN PLAN FORMAT A30
COLUMN STATUS FORMAT A10
COLUMN COMMENTS FORMAT A35

SELECT PLAN, STATUS, COMMENTS FROM DBA_CDB_RSRC_PLANS ORDER BY PLAN;

PLAN STATUS COMMENTS
------------------------------ ---------- -----------------------------------
DEFAULT_CDB_PLAN Default CDB plan
DEFAULT_MAINTENANCE_PLAN Default CDB maintenance plan
ORA$INTERNAL_CDB_PLAN Internal CDB plan
ORA$QOS_CDB_PLAN QOS CDB plan


44.8.2 Viewing CDB Resource Plan Directives

COLUMN PLAN HEADING 'Plan' FORMAT A26
COLUMN PLUGGABLE_DATABASE HEADING 'Pluggable|Database' FORMAT A25
COLUMN SHARES HEADING 'Shares' FORMAT 999
COLUMN UTILIZATION_LIMIT HEADING 'Utilization|Limit' FORMAT 999
COLUMN PARALLEL_SERVER_LIMIT HEADING 'Parallel|Server|Limit' FORMAT 999

SELECT PLAN,
PLUGGABLE_DATABASE,
SHARES,
UTILIZATION_LIMIT,
PARALLEL_SERVER_LIMIT
FROM DBA_CDB_RSRC_PLAN_DIRECTIVES
ORDER BY PLAN;

Parallel
Pluggable Utilization Server
Plan Database Shares Limit Limit
-------------------------- ------------------------- ------ ----------- --------
DEFAULT_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
DEFAULT_CDB_PLAN ORA$AUTOTASK 90 100
DEFAULT_MAINTENANCE_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
DEFAULT_MAINTENANCE_PLAN ORA$AUTOTASK 90 100
ORA$INTERNAL_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE
ORA$INTERNAL_CDB_PLAN ORA$AUTOTASK
ORA$QOS_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
ORA$QOS_CDB_PLAN ORA$AUTOTASK 90 100

======================
Path to Documentation:
======================
Documentation
Oracle Resource Manager to allocate resources in a non-CDB.
27 Managing Resources with Oracle Database Resource Manager
27.1.3 About Resource Manager Administration Privileges

BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(
GRANTEE_NAME => 'HR',
PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
ADMIN_OPTION => FALSE);
END;
/

You can revoke this privilege using the REVOKE_SYSTEM_PRVILEGE procedure.

Note:
The ADMINISTER_RESOURCE_MANAGER system privilege can only be granted or 
revoked using the DBMS_RESOURCE_MANAGER_PRIVS package. It cannot be granted 
or revoked through the SQL GRANT or REVOKE statements.

27.2.3.2 Creating Consumer Group Mapping Rules

For example, the following PL/SQL block causes user SCOTT to map to the 
DEV_GROUP consumer group every time that he logs in:

BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING 
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'SCOTT', 'DEV_GROUP');
END;
/

27.2.6.2 Granting the Switch Privilege

The following example grants user SCOTT the privilege to switch to 
consumer group OLTP.

BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
GRANTEE_NAME => 'SCOTT',
CONSUMER_GROUP => 'OLTP',
GRANT_OPTION => TRUE);
END;
/
User SCOTT is also granted permission to grant switch privileges 
for OLTP to others.

27.2.6.3 Revoking Switch Privileges

The following example revokes user SCOTT's privilege to switch to 
consumer group OLTP.

BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP (
REVOKEE_NAME => 'SCOTT', 
CONSUMER_GROUP => 'OLTP');
END;
/

27.3 The Types of Resources Managed by the Resource Manager
27.3.1 CPU

To manage CPU resources, Resource Manager allocates resources among 
consumer groups and redistributes CPU resources that were allocated 
but were not used. You can also set a limit on the amount of CPU resources 
that can be allocated to a particular consumer group.

27.3.1.1 Management Attributes(MGMT_Pn where n is 1..8)
27.3.1.2 Utilization Limit (UTILIZATION_LIMIT )

27.3.2 Exadata I/O
-> Management attributes enable you to specify CPU resource allocation 
for Exadata I/O.(IORM)

27.3.3 Parallel Execution Servers
-> Resource Manager can manage usage of the available parallel execution 
servers for a database.

27.3.3.1 Degree of Parallelism Limit (PARALLEL_DEGREE_LIMIT_P1 )
27.3.3.2 Parallel Server Limit (PARALLEL_SERVER_LIMIT )
27.3.3.3 Parallel Queue Timeout (PARALLEL_QUEUE_TIMEOUT )

27.3.4 Runaway Queries
-> Runaway sessions and calls can adversely impact overall performance if 
they are not managed properly. Resource Manager can take action when a 
session or call consumes more than a specified amount of CPU, physical I/O, 
logical I/O, or elapsed time. 

-> Resource Manager can either switch the session or call to a consumer 
group that is allocated a small amount of CPU or terminate the session 
or call.

27.3.4.1 Automatic Consumer Group Switching
27.3.4.2 Canceling SQL and Terminating Sessions
27.3.4.3 Execution Time Limit

27.3.5 Active Session Pool with Queuing
-> You can control the maximum number of concurrently active sessions 
allowed within a consumer group. This maximum defines the active session pool.

27.3.6 Undo Pool
-> You can specify an undo pool for each consumer group. 
An undo pool controls the total amount of undo for uncommitted transactions 
that can be generated by a consumer group.

27.3.7 Idle Time Limit
-> You can specify an amount of time that a session can be idle, after which 
it is terminated. You can also specify a more stringent idle time limit that 
applies to sessions that are idle and blocking other sessions.

27.4 Creating a Simple Resource Plan

Example: Creating a Simple Plan with the CREATE_SIMPLE_PLAN Procedure

The following PL/SQL block creates a simple resource plan with two user-specified consumer groups:

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'SIMPLE_PLAN1',
CONSUMER_GROUP1 => 'MYGROUP1', GROUP1_PERCENT => 80,
CONSUMER_GROUP2 => 'MYGROUP2', GROUP2_PERCENT => 20);
END;
/

Executing the preceding statements creates the following plan:

Consumer Group Level 1 Level 2 Level 3

SYS_GROUP      100%
MYGROUP1                80%
MYGROUP2                20%
OTHER_GROUPS                    100%

27.5 Creating a Complex Resource Plan

Step 1: Create a pending area.
27.5.1 About the Pending Area

27.5.2 Creating a Pending Area

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
END;
/


Step 2: Create, modify, or delete consumer groups.

27.5.3 Creating Resource Consumer Groups

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
CONSUMER_GROUP => 'OLTP',
COMMENT => 'OLTP applications');
END;
/


MGMT_MTH
The resource allocation method for distributing CPU among sessions in the consumer group. 
The default is 'ROUND-ROBIN', which uses a round-robin scheduler to ensure that sessions are 
fairly executed. 
'RUN-TO-COMPLETION' specifies that long-running sessions are scheduled ahead of other sessions. 
This setting helps long-running sessions (such as batch processes) complete sooner.


Step 3: Map sessions to consumer groups.

27.5.4 Mapping Sessions to Consumer Groups

BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( 
ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER, 
VALUE => 'OE', 
CONSUMER_GROUP => 'OLTP');
END; 
/ 



Step 4: Create the resource plan.

27.5.5 Creating a Resource Plan

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
PLAN => 'DAYTIME',
COMMENT => 'More resources for OLTP applications');
END;
/

Step 5: Create resource plan directives.

27.5.6 Creating Resource Plan Directives

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'DAYTIME',
GROUP_OR_SUBPLAN => 'OLTP',
COMMENT => 'OLTP group',
MGMT_P1 => 75);
END;
/

This directive assigns 75% of CPU resources to the OLTP consumer group at level 1.

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'DAYTIME', 
GROUP_OR_SUBPLAN => 'REPORTING',
COMMENT => 'Reporting group',
MGMT_P1 => 15,
PARALLEL_DEGREE_LIMIT_P1 => 8,
ACTIVE_SESS_POOL_P1 => 4);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'DAYTIME', 
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'This one is required',
MGMT_P1 => 10);
END;
/

In this plan, consumer group REPORTING has a maximum degree of parallelism of 8 for any operation, 
while none of the other consumer groups are limited in their degree of parallelism. In addition, 
the REPORTING group has a maximum of 4 concurrently active sessions.

Step 6: Validate the pending area.

BEGIN
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
END;
/

Step 7: Submit the pending area.

BEGIN
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/


Note: You can clear the pending area at any time using the CLEAR_PENDING_AREA procedure.
(27.5.9 Clearing the Pending Area)

BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/


27.6 Enabling Oracle Database Resource Manager and Switching Plans


ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'mydb_plan';


Note:
Note that by default a set of automated maintenance tasks run during maintenance windows, 
which are predefined Scheduler windows that are members of the MAINTENANCE_WINDOW_GROUP 
window group and which specify the DEFAULT_MAINTENANCE_PLAN resource plan. Thus, the 
Resource Manager activates by default during maintenance windows. You can modify these 
maintenance windows to use a different resource plan, if desired.

27.7.5 An Oracle-Supplied Mixed Workload Plan

Oracle Database includes a predefined resource plan, MIXED_WORKLOAD_PLAN, that prioritizes interactive 
operations over batch operations, and includes the required subplans and consumer groups recommended by 
Oracle.


27.11 Monitoring Oracle Database Resource Manager
V$RSRC_PLAN
V$RSRC_CONSUMER_GROUP
V$RSRC_SESSION_INFO
V$RSRC_PLAN_HISTORY
V$RSRC_CONS_GROUP_HISTORY
V$RSRCMGRMETRIC
V$RSRCMGRMETRIC_HISTORY

=====================================================================
Multitenant : Resource Manager with Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)
=====================================================================

In a multitennent environment Resource Manager performs two separate tasks. At the CDB level it controls 
the resources allocated to each PDB, allowing you to prioritise some PDBs over others.

At the PDB level it controls the resources allocated to each session connected to the PDB, allowing you 
to prioritise some sessions over others, just as it does in pre-12c instances.

Container Database (CDB)
-> The following sections describe how resource manager can be used to control the resource usage 
between pluggable databases (PDBs). Resource manager does not currently have the ability to control 
memory usage between PDBs.


Create CDB Resource Plan

A CDB resource plan is made up of CDB resource plan directives. The plan directives allocate shares, 
which define the proportion of the CDB resources available to the PDB, and specific utilization percentages, 
that give a finer level of control. CDB resource plans are managed using the DBMS_RESOURCE_MANAGER package.

Each plan directive is made up of the following elements:

pluggable_database : The PDB the directive relates to.
shares : The proportion of the CDB resources available to the PDB.
utilization_limit : The percentage of the CDBs available CPU that is available to the PDB.
parallel_server_limit : The percentage of the CDBs available parallel servers (PARALLEL_SERVERS_TARGET initialization parameter) that are available to the PDB.


PDBs without a specific plan directive use the default PDB directive.

[1] Setup the Resource Manager for 12c Non CDB.

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- --------------------
db_name string orcl
SQL>
SQL> CREATE USER usr_day_1 identified by oracle;

User created.

SQL> r
1* CREATE USER usr_day_2 identified by oracle

User created.

SQL> CREATE USER usr_night_2 identified by oracle;

User created.

SQL> r
1* CREATE USER usr_night_1 identified by oracle

User created.

SQL> grant connect,resource to usr_day_1,usr_day_2,usr_night_1,usr_night_2;

Grant succeeded.

SQL> alter user &username default tablespace users;
Enter value for username: usr_day_1
old 1: alter user &username default tablespace users
new 1: alter user usr_day_1 default tablespace users

User altered.

SQL> /
Enter value for username: usr_day_2
old 1: alter user &username default tablespace users
new 1: alter user usr_day_2 default tablespace users

User altered.

SQL> /
Enter value for username: usr_night_1
old 1: alter user &username default tablespace users
new 1: alter user usr_night_1 default tablespace users

User altered.

SQL> /
Enter value for username: usr_night_2
old 1: alter user &username default tablespace users
new 1: alter user usr_night_2 default tablespace users

User altered.

--Create Plan "DAY_PLAN" (Created from OEM)
DECLARE
spfileValue VARCHAR2(1000);
execText VARCHAR2(1000);
scopeValue VARCHAR2(30) := 'MEMORY';
planName VARCHAR2(100) :='DAY_PLAN';
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( plan => 'DAY_PLAN', comment => 'Testing Day Plan',max_iops => NULL,max_mbps => NULL );
dbms_resource_manager.create_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_DAY',
comment => '',
switch_elapsed_time => 6000,
max_utilization_limit => 80,
mgmt_p1 => 80, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => 16 ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => 100 ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => 'CONS_GROUP_NIGHT',
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => 120,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

);
dbms_resource_manager.create_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_NIGHT',
comment => '',
switch_elapsed_time => 60,
max_utilization_limit => 10,
mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => 2 ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => 10 ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => 'KILL_SESSION',
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => 60,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

);
dbms_resource_manager.create_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'OTHER_GROUPS',
comment => '',
switch_elapsed_time => NULL,
max_utilization_limit => 10,
mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => 2 ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => NULL ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => NULL,
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => 60,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

);
dbms_resource_manager.submit_pending_area();
END;
/

--> Active the plan from OEM.

SQL> show parameter resource

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
resource_manager_cpu_allocation integer 1
resource_manager_plan string DAY_PLAN
SQL>


BEGIN
dbms_resource_manager.set_initial_consumer_group(
user => 'USR_DAY_1',
consumer_group => 'CONS_GROUP_DAY'
);

dbms_resource_manager.set_initial_consumer_group(
user => 'USR_DAY_2',
consumer_group => 'CONS_GROUP_DAY'
);


dbms_resource_manager.set_initial_consumer_group(
user => 'USR_NIGHT_1',
consumer_group => 'CONS_GROUP_NIGHT'
);

dbms_resource_manager.set_initial_consumer_group(
user => 'USR_NIGHT_2',
consumer_group => 'CONS_GROUP_NIGHT'
);
END;
/

--Create Night Plan (From OEM) 
DECLARE
spfileValue VARCHAR2(1000);
execText VARCHAR2(1000);
scopeValue VARCHAR2(30) := 'MEMORY';
planName VARCHAR2(100) :='NIGHT_PLAN';
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( plan => 'NIGHT_PLAN', comment => 'Testing Night Plan',max_iops => NULL,max_mbps => NULL );
dbms_resource_manager.create_plan_directive(
plan => 'NIGHT_PLAN',
group_or_subplan => 'CONS_GROUP_DAY',
comment => '',
switch_elapsed_time => 60,
max_utilization_limit => 10,
mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => 2 ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => 10 ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => 'KILL_SESSION',
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => 60,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

);
dbms_resource_manager.create_plan_directive(
plan => 'NIGHT_PLAN',
group_or_subplan => 'CONS_GROUP_NIGHT',
comment => '',
switch_elapsed_time => 6000,
max_utilization_limit => 80,
mgmt_p1 => 80, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => 16 ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => 100 ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => 'CONS_GROUP_DAY',
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => 120,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

);
dbms_resource_manager.create_plan_directive(
plan => 'NIGHT_PLAN',
group_or_subplan => 'OTHER_GROUPS',
comment => '',
switch_elapsed_time => NULL,
max_utilization_limit => 10,
mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => 2 ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => NULL ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => NULL,
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => 60,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

);
dbms_resource_manager.submit_pending_area();
END;
/

--HOL
As per OCM11g book modified the existing plan.

[2] Test RM for elapsed time limit exceeded


BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_DAY',
new_comment => '',
new_active_sess_pool_p1 => 20,
new_undo_pool => NULL
);
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_NIGHT',
new_comment => '',
new_active_sess_pool_p1 => 2,
new_undo_pool => 10240,
new_parallel_degree_limit_p1=>2
);
dbms_resource_manager.submit_pending_area();
END;
/

-bash-4.1$ sqlplus usr_night_1/oracle

SQL> SELECT COUNT(*) FROM TEST;

COUNT(*)
----------
92101

SQL> SELECT * FROM TEST; 
ERROR:
ORA-56736: elapsed time limit exceeded - session terminated


Sun Sep 22 16:02:01 2019
Process 11735 killed by Resource Manager SWITCH_ELAPSED_TIME directive for consumer group CONS_GROUP_NIGHT
opiodr aborting process unknown ospid (11735) as a result of ORA-56736
Sun Sep 22 16:04:36 2019

[3] Test RM for parallel server limit

12c having some changes in parallel behavior default value of parallel_degree_policy is AUTO so sometime it will not use parallel. Reference

We will revert back the parallel behavior same as 11g.
alter system set parallel_degree_policy=LIMITED;


BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_NIGHT',
new_parallel_degree_limit_p1=>2
);
dbms_resource_manager.submit_pending_area();
END;
/

select name,pqs_completed,pq_servers_used from V$RSRC_CONSUMER_GROUP where name ='CONS_GROUP_NIGHT';
NAME PQS_COMPLETED PQ_SERVERS_USED
-------------------------------- ------------- ---------------
CONS_GROUP_NIGHT 0 0


sqlplus usr_night_1/oracle@orcl

select table_name,degree from user_tables;
TABLE_NAME   DEGREE
----------------------------------------
TEST         5

select count(1) from test a,test b where a.object_id = b.object_id;

COUNT(1)
----------
368400

select * from table(dbms_xplan.display_cursor);

NAME PQS_COMPLETED PQ_SERVERS_USED
-------------------------------- ------------- ---------------
CONS_GROUP_NIGHT 1 4


BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_NIGHT',
new_parallel_degree_limit_p1=>1
);
dbms_resource_manager.submit_pending_area();
END;
/

NAME             PQS_COMPLETED PQ_SERVERS_USED
-------------------------------- ------------- ---------------
CONS_GROUP_NIGHT 1             0

[4] Test RM for IO Limits (SWITCH_IO_MEGABYTES)

SQL> select count(1) from test a,test b
where a.object_id = b.object_id;
2 where a.object_id = b.object_id
*
ERROR at line 2:
ORA-12805: parallel query server died unexpectedly

SQL> select name,pqs_completed,pq_servers_used from V$RSRC_CONSUMER_GROUP where name ='CONS_GROUP_NIGHT';

NAME PQS_COMPLETED PQ_SERVERS_USED
-------------------------------- ------------- ---------------
CONS_GROUP_NIGHT 1 4


Sun Sep 22 16:34:30 2019
Process 10363 killed by Resource Manager SWITCH_IO_MEGABYTES directive for consumer group CONS_GROUP_NIGHT
opidrv aborting process P003 ospid (10363) as a result of ORA-603
Sun Sep 22 16:34:30 2019
Process 10361 killed by Resource Manager SWITCH_IO_MEGABYTES directive for consumer group CONS_GROUP_NIGHT
opidrv aborting process P002 ospid (10361) as a result of ORA-603
Sun Sep 22 16:34:47 2019
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m001_12770.trc:
ORA-28365: wallet is not open

[5] Test RM for Active Sessions Limit (active_sess_pool_p1)

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_DAY',
new_active_sess_pool_p1 => 3);
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_NIGHT',
new_comment => '',
new_active_sess_pool_p1 => 2,
new_undo_pool => 10240,
new_parallel_degree_limit_p1=>2
);
dbms_resource_manager.submit_pending_area();
END;
/

select name,ACTIVE_SESSIONS,QUEUE_LENGTH,REQUESTS,EXECUTION_WAITERS from V$RSRC_CONSUMER_GROUP;


NAME ACTIVE_SESSIONS QUEUE_LENGTH REQUESTS EXECUTION_WAITERS
-----------------------------------------------------------------------------------------------------
CONS_GROUP_DAY 0 0 0 0
CONS_GROUP_NIGHT 2 1 8 1
OTHER_GROUPS 1 0 22 0
_ORACLE_BACKGROUND_GROUP_ 37 0 44 0


Terminal-1 (Active Session1 if it running query.)
sqlplus usr_night_1/oracle@orcl

Terminal-2 (Active Session1 if it running query.)
sqlplus usr_night_1/oracle@orcl


Terminal-3 (Waiting/Hanging)
sqlplus usr_night_1/oracle@orcl

# Also refer this column ACTIVE_SESSION_LIMIT_HIT

[6] Test RM for Undo Limit (undo_pool)

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_DAY',
new_active_sess_pool_p1 => 3);
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_NIGHT',
new_comment => '',
new_active_sess_pool_p1 => 2,
new_undo_pool => 10240,
new_parallel_degree_limit_p1=>2
);
dbms_resource_manager.submit_pending_area();
END;
/

select name,ACTIVE_SESSIONS,CURRENT_UNDO_CONSUMPTION,UNDO_LIMIT_HIT from V$RSRC_CONSUMER_GROUP;

NAME ACTIVE_SESSIONS CURRENT_UNDO_CONSUMPTION UNDO_LIMIT_HIT
-----------------------------------------------------------------------------------------------
CONS_GROUP_DAY 0 0 0
CONS_GROUP_NIGHT 0 0 0
OTHER_GROUPS 1 0 0
_ORACLE_BACKGROUND_GROUP_ 31 0 0

UPDATE TEST
SET LAST_DDL_TIME=SYSDATE,CREATED=CURRENT_TIMESTAMP where rownum <10000;

9999 rows updated.

commit;

UPDATE TEST
SET LAST_DDL_TIME=SYSDATE,CREATED=CURRENT_TIMESTAMP where rownum <100000;
UPDATE TEST
*
ERROR at line 1:
ORA-30027: Undo quota violation - failed to get 340 (bytes)

SQL> !oerr ora 30027
30027, 00000, "Undo quota violation - failed to get %s (bytes)"
// *Cause: the amount of undo assigned to the consumer group of this
// session has been exceeded.
// *Action: ask DBA to increase undo quota, or wait until other
// transactions to commit before proceeding.


select name,ACTIVE_SESSIONS,CURRENT_UNDO_CONSUMPTION,UNDO_LIMIT_HIT from V$RSRC_CONSUMER_GROUP;

NAME ACTIVE_SESSIONS CURRENT_UNDO_CONSUMPTION UNDO_LIMIT_HIT
------------------------------------------------------------------------------------------------
CONS_GROUP_DAY 0 0 0
CONS_GROUP_NIGHT 0 0 1
OTHER_GROUPS 1 0 0
_ORACLE_BACKGROUND_GROUP_ 31 0 0


Mon Sep 23 10:54:34 2019
KTUDECRUSTAT: undo quota discrepancy: xcb=9aa86480, xcbusz=10240080, ResMgr=10239396
Mon Sep 23 10:54:36 2019

[7] Automate active respective plan on their schedule window.

--CLI
BEGIN
DBMS_SCHEDULER.create_window (
window_name => 'day_window1',
resource_plan => 'DAY_PLAN',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=8; byminute=0; bysecond=0;',
end_date => NULL,
duration => INTERVAL '10' HOUR,
window_priority => 'HIGH',
comments => 'Day time processing window.');

DBMS_SCHEDULER.create_window (
window_name => 'night_window1',
resource_plan => 'NIGHT_PLAN',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=18; byminute=0; bysecond=0;',
end_date => NULL,
duration => INTERVAL '14' HOUR,
window_priority => 'HIGH',
comments => 'Night time processing window.');

DBMS_SCHEDULER.create_window_group (
group_name => 'processing_window_group',
window_list => 'day_window1, night_window1',
comments => '24 hour processing window group');
END;
/

--Using OEM

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name=>'"DAY_WINDOW1"',
resource_plan=>'DAY_PLAN',
start_date=>systimestamp at time zone 'Asia/Calcutta',
duration=>numtodsinterval(720, 'minute'),
repeat_interval=>'FREQ=DAILY;BYHOUR=7',
end_date=>null,
window_priority=>'HIGH',
comments=>'Day time processing window.');
END;

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name=>'"NIGHT_WINDOW1"',
resource_plan=>'NIGHT_PLAN',
start_date=>systimestamp at time zone 'Asia/Calcutta',
duration=>numtodsinterval(720, 'minute'),
repeat_interval=>'FREQ=DAILY;BYHOUR=19',
end_date=>null,
window_priority=>'HIGH',
comments=>'Night time processing window.');
END;

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW_GROUP(
group_name=>'"PROCESSING_WINDOW_GROUP1"',
window_list=>'"DAY_WINDOW1","NIGHT_WINDOW1"');
END;
/

======================
Path to Documentation: 
======================
Administrator’s Guide 
(Oracle Resource Manager to allocate resources in a non-CDB)
-> 27 Managing Resources with Oracle Database Resource Manager        ---->27.1.3 About Resource Manager Administration Privileges
-> 44 Using Oracle Resource Manager for PDBs with SQL*Plus Administrator’s Guide 
-> 45 Using Oracle Resource Manager for PDBs with Cloud Contro