Monday Feb 16, 2015

Oracle Database Support News - your monthly guide to Database support information

Oracle Database Support News - your monthly guide to Database support information

Want to keep up-to-date with the latest Database Support News as well as accessing interesting technical Database articles?

Check out the monthly Oracle Premier Support - Oracle Database Support News (Document 230.1) for content such as
  • Feature Article: discussing a technical topic, best practice, new support tool or new features in a release
  • Database Technology Updates: providing up-to-date information about the current database releases such as release schedule, known Issues, relevant My Oracle Support documents as well as release information about other Database Technology related products.
  • Support Tips: including Top 10 documents, upcoming advisor webcasts, My Oracle Support updates and new or updated My Oracle Support documents.

Use Oracle Database Support News Archive (Document 1284265.1) to look up previous versions or find a specific feature articles.

Don't delay and bookmark Database Support News today so you always access the latest edition - February 2015:

  • ORA-00445 issues explained
  • Oracle Database Technology Updates 
    • Oracle Database 12c
    • Oracle Database 11g Release 2
    • Oracle TimesTen In-Memory Database
    • ODAC 12c Release 3
    • NuGet Release: ODP.NET, Managed Driver
    • Oracle Rdb Product Family on OpenVMS
  • Support Tips
    • Database Support Blog
    • My Oracle Support updates
    • My Oracle Support Accreditation Series - Oracle Database
    • IBM zSeries Oracle SIG Conference
    • Upcoming webcasts
    • Other Updates
  • Patch Updates


Thursday Jan 29, 2015

Oracle Resource Manager and DBMS_RESOURCE_MANAGER

The Oracle Database Resource Manager (the Resource Manager) is an infrastructure that provides granular control of database resources allocated to users, applications, and services. The Oracle Database Resource Manager (RM) enables you to manage multiple workloads that are contending for system and database resources.

Resources are allocated to users according to a resource plan specified by the database administrator. The plan specifies how the resources are to be distributed among resource consumer groups, which are user sessions grouped by resource requirements. A resource plan directive associates a resource consumer group with a plan and specifies how resources are to be allocated to the group. DBRM (database resource manager) process is responsible for setting resource plans and other resource manager related tasks.

Oracle Documentation on Oracle Database Resource Manager 

For 12cR1 - Oracle® Database Administrator's Guide 12c Release 1 (12.1) - Part Number E17636-17

Managing Resources with Oracle Database Resource Manager

http://docs.oracle.com/database/121/ADMIN/dbrm.htm#ADMIN027

DBMS_RESOURCE_MANAGER - Reference

http://docs.oracle.com/database/121/ARPLS/d_resmgr.htm

Using Oracle Resource Manager on pluggable databases (PDBs) in a multitenant container database (CDB)

Sqlplus: http://docs.oracle.com/database/121/ADMIN/cdb_dbrm.htm#ADMIN13774

EM: http://docs.oracle.com/database/121/ADMIN/cdb_dbrm_em.htm#ADMIN14129

For 11gR2 - Oracle® Database Administrator's Guide 11g Release 2 (11.2) - Part Number E25494-02

Managing Resources with Oracle Database Resource Manager

http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN027

For 11gR1 - Oracle® Database Administrator's Guide 11g Release 1 (11.1) - Part Number B28310-04

Managing Resource Allocation with Oracle Database Resource Manager

http://docs.oracle.com/cd/B28359_01/server.111/b28310/dbrm.htm#i1010776

DBMS_RESOURCE_MANAGER - Reference

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_resmgr.htm

Please refer the Note 1119407.1 for the Video Tutorial on Resource Manager, which includes both Concepts and Lab practices

Note 1119407.1   Resource Manager Training (11.2 features included) [Video] 

Setup and Configuration

The DBMS_RESOURCE_MANAGER package is to be used to setup and configure resourcemanager. The DBMS_RESOURCE_MANAGER package maintains plans, consumer groups, and plan directives. It also provides semantics so that you may group together changes to the plan schema.

Resource Manager Administration Privileges

You must have the system privilege ADMINISTER_RESOURCE_MANAGER to administer the Resource Manager. This privilege (with the ADMIN option) is granted to database administrators through the DBA role. GRANT_SYSTEM_PRIVILEGE and REVOKE_SYSTEM_PRIVILEGE procedures of DBMS_RESOURCE_MANAGER_PRIVS PL/SQL package are used to grant & revoke this.

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.

begin
dbms_resource_manager_privs.grant_system_privilege(
grantee_name => 'DBADMIN',
admin_option => false);
end;

In order to assign users to resource consumer groups other than default, the users must have been granted the system privilege grant_switch_consumer_group in order to be able to switch to that consumer group. GRANT_SWITCH_CONSUMER_GROUP and REVOKE_SWITCH_CONSUMER_GROUP procedures of DBMS_RESOURCE_MANAGER_PRIVS PL/SQL package are used to grant & revoke this.

begin
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'PUBLIC',
consumer_group => `GROUP_NAME');
end;

Resources Managed by the Resource Manager

There are many types of resources, which are managed by the RM like CPU, Idle time, Undo pool, etc. Distribute CPU is the widely used RM feature. Complete list of resources managed by the RM is listed in the documentation below

http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN11851

Elements of the Resource Manager

As explained in the concepts section, Resources are allocated to users according to a resource plan, distributed among resource consumer groups and resource plan directive used to specify how resources are to be allocated to the group. So The elements of the Resource Manager are Resource consumer group, Resource plan, Resource plan directive. These elements are stored in tables in the data dictionary. So you can view information about them with Resource manager related data dictionary views.

Enabling the Database Resource Manager

You can enable the Database Resource Manager by setting the RESOURCE_MANAGER_PLAN initialization parameter.
This parameter specifies the top plan, identifying the plan schema to be used for this instance. If no
plan is specified with this parameter, the Database Resource Manager is not activated. The following
example activates the Database Resource Manager and assigns the top plan as mydb_plan.

RESOURCE_MANAGER_PLAN = mydb_plan

You can also activate or deactivate the Database Resource Manager, or change the current top plan, using
the ALTER SYSTEM statement. In this example, the top plan is specified as mydb_plan.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = mydb_plan;

An error message is returned if the specified plan does not exist in the data dictionary.
To deactivate the Database Resource Manager, issue the following statement:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

Note 223572.1     Resource Manager Setup and Configuration for the Database 

A database can have multiple plans defined, but only one can be active at a given time per instance. Different instance of a RAC can activate different plans, if required.

Getting Started with Resource Manager

If you use Resource Manager and have up to eight consumer groups and needed only CPU resource allocation to control then simple resource plan is adequate, using the DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN procedure. When your situation calls for a more complex resource plan, you must create the plan, with its directives and consumer groups, in a staging area called the pending area, and then validate the plan before storing it in the data dictionary (Complex Resource Plan). Resource plans can be associated with scheduler windows or jobs, So Integration with Oracle Database Scheduler (DBMS_SCHEDULER) is possible. Resource plans do not impose a hard limit, meaning if there is no other session is taking resource allocated, then this session can take all the available resource and not just the limit allocated. Please refer Note 1119407.1 for the Video Tutorials on Resource Manager, which contains both Concepts and Lab practices

Note 1119407.1   Resource Manager Training (11.2 features included) [Video] 

Create Simple Resource Plan using CREATE_SIMPLE_PLAN procedure

You can specify up to eight consumer groups with this procedure. The only resource allocation method supported is CPU. The plan uses the EMPHASIS CPU allocation policy (the default) and each consumer group uses the ROUND_ROBIN scheduling policy (also the default). 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 => 'mydb_plan',
  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%

Each consumer group specified in the plan is allocated its CPU percentage at level 2. Also implicitly included in the plan are SYS_GROUP (a system-defined group that is the initial consumer group for the users SYS and SYSTEM) and OTHER_GROUPS. The SYS_GROUP consumer group is allocated 100% of the CPU at level 1, and OTHER_GROUPS is allocated 100% of the CPU at level 3. Refer Oracle Documentation for "Creating a Simple Resource Plan" mentioned below

http://docs.oracle.com/cd/B28359_01/server.111/b28310/dbrm002.htm#ADMIN11861

Create Complex Resource Plan using CREATE_PLAN procedure

A complex resource plan is any resource plan that is not created with the CREATE_SIMPLE_PLAN procedure. Note that any resource plan created by the Simple Resource Plan method using CREATE_SIMPLE_PLAN can also be created by the Complex Resource Plan (this) method, also creating resource plan with this method give more flexibility and can have more granularity. When your situation calls for a more complex resource plan, you must create the plan, with its directives and consumer groups, in a staging area called the pending area, and then validate the plan before storing it in the data dictionary. You can create sub-plans (plan within plans) for more granular resource management. If a consumer-group does not use its CPU-percentage, the remainder falls through to the next level, and the administrator can define explicit what to do with it. Up to eight levels can be specified.

Here is a general guideline of the steps in implementing a Database Resource Manager plan:

1. Create resource plans
2. Create resource consumer groups
3. Create resource plan directives
4. Grant switch privilege for resource consumer groups to users or roles
5. Assign users to resource consumer groups
6. Specify a plan to be used by the instance

In addition to the above steps, we need to create these elements in a staging area called the pending area, so the below steps are related to the pending area, which can be repeated each time to create, validate and submit this pending area.

i. Create pending area
ii. Validate Pending area
iii. Submit pending area

Following example assign 80% of the CPU-resources to sessions in the OLTP-group, and 10% to ADHOC- and remaining 10% to BATCH-group each. assuming we have created 3 users, ADHOC, BATCH, and OLTP and assigned a consumer group for each of them. The CPU-resources are allocated across all consumer-groups by the percentages given to each group. Specifying a zero percentage for a consumer-group means that it should not get any processing resources at that level, given a CPU consumption of 100%. This can force sessions for that consumer-group to wait forever in an overloaded system. It does not mean that users belonging to OTHER_GROUPS (having 0%) never are able to run, but for a fully loaded system, sessions for OTHER_GROUPS have to wait for the others to finish work. If this is the only one active session running, it is of course allowed to utilize all available CPU resources up to 100%. But if another session is started in another group, Resource Manager will balance CPU resources according to the plan directives.

---Create pending area for plan, consumer group and directives

begin
 dbms_resource_manager.create_pending_area();
end;

---1. Create resource plans

begin
 dbms_resource_manager.create_plan(
  plan => 'MYDB_PLAN',
  comment => 'Resource plan/method for Single level sample');
end;

---2. Create resource consumer groups

begin
 dbms_resource_manager.create_consumer_group(
  consumer_group => 'OLTP_Group',
  comment => 'Resource consumer group/method for online users sessions');
 dbms_resource_manager.create_consumer_group(
  consumer_group => 'BATCH_Group',
  comment => 'Resource consumer group/method for users sessions who run batch jobs');
 dbms_resource_manager.create_consumer_group(
  consumer_group => 'ADHOC_Group',
  comment => 'Resource consumer group/method for users sessions who execute Ad-Hoc Queries');
end;

---3. Create resource plan directives

begin
 dbms_resource_manager.create_plan_directive(
   plan => 'mydb_plan',
   group_or_subplan => 'OLTP_Group',
   comment => 'Online day users sessions at level 1',
   cpu_p1 => 80,
   parallel_degree_limit_p1 => 0);
 dbms_resource_manager.create_plan_directive(
   plan => 'mydb_plan',
   group_or_subplan => 'BATCH_Group',
   comment => 'batch day users sessions at level 1',
   cpu_p1 => 10,
   parallel_degree_limit_p1 => 10);
 dbms_resource_manager.create_plan_directive(
   plan => 'mydb_plan',
   group_or_subplan => 'ADHOC_Group',
   comment => 'ADHOC day users sessions at level 1',
   cpu_p1 => 10,
   parallel_degree_limit_p1 => 5);
 dbms_resource_manager.create_plan_directive(
   plan => 'mydb_plan',
   group_or_subplan => 'OTHER_GROUPS',
   comment => 'OTHER_GROUPS day users sessions at level 1',
   cpu_p1 => 0,
   parallel_degree_limit_p1 => 0);
end;

---Validate Pending area for plan, consumer group and directives

begin
 dbms_resource_manager.validate_pending_area();
end;

---submit pending area for plan, consumer group and directives

begin
 dbms_resource_manager.submit_pending_area();
end;

---Next, we have to grant privileges in order to assign consumer groups to users:
---We need to again create a pending area, till we submit the pending area

---Create pending area for privilages, roles and assign users

begin
 dbms_resource_manager.create_pending_area();
end;

---4. Grant switch privilege for resource consumer groups to users or roles

begin
 dbms_resource_manager_privs.grant_switch_consumer_group(
   grantee_name => 'ADHOC',
   consumer_group => 'ADHOC_Group',
   grant_option => FALSE);
 dbms_resource_manager_privs.grant_switch_consumer_group(
   grantee_name => 'OLTP',
   consumer_group => 'OLTP_Group',
   grant_option => FALSE);
 dbms_resource_manager_privs.grant_switch_consumer_group(
   grantee_name => 'BATCH',
   consumer_group => 'BATCH_Group',
   grant_option => FALSE);
end;

---5. Assign users to resource consumer groups

begin
 dbms_resource_manager.set_initial_consumer_group(
  user => 'ADHOC',
  consumer_group => 'ADHOC_Group');
 dbms_resource_manager.set_initial_consumer_group(
  user => 'OLTP',
  consumer_group => 'OLTP_Group');
 dbms_resource_manager.set_initial_consumer_group(
  user => 'BATCH',
  consumer_group => 'BATCH_Group');
end;

---Validate Pending area for privilages, roles and assign users

begin
 dbms_resource_manager.validate_pending_area();
end;

---submit pending area for privilages, roles and assign users

begin
 dbms_resource_manager.submit_pending_area();
end;

6. Specify a plan to be used by the instance

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = MYDB_PLAN; 

Refer Oracle Documentation for "Creating a Complex Resource Plan" mentioned below and the Note 106948.1

http://docs.oracle.com/cd/B28359_01/server.111/b28310/dbrm003.htm#ADMIN11862

Checking Configuration and Monitoring Resource Manager 

We can use dynamic performance views to help us monitor the results of our Oracle Database Resource Manager 

Resource Plans:

select plan, cpu_method, status from dba_rsrc_plans order by 1;

PLAN          CPU_METHOD  STATUS
------------- ----------- ---------
MYDB_PLAN     EMPHASIS    ACTIVE
1 row selected. 

Resource Consumer Groups:

select consumer_group,cpu_method, status from dba_rsrc_consumer_groups order by 1;

CONSUMER_GROUP CPU_METHOD STATUS
------------------------------ ------------------------------ ------------------------------
ADHOC_GROUP ROUND-ROBIN ACTIVE
BATCH_GROUP ROUND-ROBIN ACTIVE
DEFAULT_CONSUMER_GROUP ROUND-ROBIN ACTIVE
OLTP_GROUP ROUND-ROBIN ACTIVE
OTHER_GROUPS ROUND-ROBIN ACTIVE
5 rows selected. 

Resource Plan Directives:

select plan, group_or_subplan, type, cpu_p1, cpu_p2, cpu_p3, cpu_p4, status
from dba_rsrc_plan_directives order by 1,2,3,4,5,6;

PLAN GROUP_OR_SUBPLAN TYPE CPU_P1 CPU_P2 CPU_P3 CPU_P4 STATUS
---------------------- -------------------- -------------- ---------- ---------- ---------- ---------- -------
MYDB_PLAN ADHOC_GROUP CONSUMER_GROUP 10 0 0 0 ACTIVE
MYDB_PLAN BATCH_GROUP CONSUMER_GROUP 10 0 0 0 ACTIVE
MYDB_PLAN OLTP_GROUP CONSUMER_GROUP 80 0 0 0 ACTIVE
MYDB_PLAN OTHER_GROUPS CONSUMER_GROUP 0 0 0 0 ACTIVE
4 rows selected. 

Resource consumer group privileges:

select * from dba_rsrc_consumer_group_privs;

GRANTEE GRANTED_GROUP GRA INI
------------------------------ ------------------------------ --- ---
BATCH BATCH_GROUP NO YES
OLTP OLTP_GROUP NO YES
PUBLIC DEFAULT_CONSUMER_GROUP YES YES
ADHOC ADHOC_GROUP NO YES
4 rows selected. 

These above users have the privilege to switch to a consumer group other than default.

Default consumer groups for users:

select username, initial_rsrc_consumer_group from dba_users;

USERNAME INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
SYS DEFAULT_CONSUMER_GROUP
SYSTEM DEFAULT_CONSUMER_GROUP
OLTP OLTP_GROUP
ADHOC ADHOC_GROUP
OUTLN DEFAULT_CONSUMER_GROUP
DBSNMP DEFAULT_CONSUMER_GROUP
BATCH BATCH_GROUP
7 rows selected. 

Monitor CPU Usage and Waits by Consumer Group:

In this example, the server has 16 CPUs which means that a maximum of 16 sessions can run at any time. Refer Note 1338988.1

select to_char(m.begin_time, 'HH:MI') time, m.consumer_group_name, m.cpu_consumed_time / 60000 avg_running_sessions, m.cpu_wait_time / 60000 avg_waiting_sessions, d.mgmt_p1*(select value from v$parameter where name = 'cpu_count')/100 allocation from v$rsrcmgrmetric_history m, dba_rsrc_plan_directives d, v$rsrc_plan p where m.consumer_group_name = d.group_or_subplan and p.name = d.plan order by m.begin_time, m.consumer_group_name;

TIME NAME AVG_RUNNING AVG_WAITING ALLOCATION 

----- ------------------ ----------- ----------- ----------
13:34 ADHOC_GROUP 1.76 8.4 .8
13:34 BATCH_GROUP 2.88 .4 2.4
13:34 ETL_GROUP 0 0 2.4
13:34 INTERACTIVE_GROUP 10.4 6.8 5.6
13:34 OTHER_GROUPS .32 .08 .8
13:34 SYS_GROUP 0 0 4

 The Below Articles gives more Scripts/Tips for Monitoring Oracle Resource Manager 

Note 1338988.1  Scripts and Tips for Monitoring CPU Resource Manager

Note 106948.1     Database Resource Manager samples includes scripts and quires to check/monitor

Note 240877.1    Script to monitor PX limits from Resource Manager for active sessions

Note 428325.1    How to Find the Sessions Killed by MAX_IDLE_BLOCKER_TIME Plan Directive

Monitoring Oracle Database Resource Manager

http://docs.oracle.com/cd/B28359_01/server.111/b28310/dbrm009.htm#ADMIN11906

Resource Manager Data Dictionary Views

http://docs.oracle.com/cd/B28359_01/server.111/b28310/dbrm011.htm#ADMIN11916

Cleaning the Resource Manager Plan

In order to delete the current plan, first it has to be `switched off' or another plan has to be selected as current.

alter system set resource_manager_plan=''; 

---Create pending area for delete plan and consumer group

begin
dbms_resource_manager.create_pending_area();
end;

--Delete plan (assuming we have created 2 plans - MYDB_PLAN, ANY_OTHER_CREATED_PLAN)

begin
 dbms_resource_manager.delete_plan(
   plan => 'MYDB_PLAN');
 dbms_resource_manager.delete_plan(
   plan => 'ANY_OTHER_CREATED_PLAN');
end;

--Delete consumer group

begin
 dbms_resource_manager.delete_consumer_group(
   consumer_group => 'OLTP_GROUP');
 dbms_resource_manager.delete_consumer_group(
   consumer_group => 'BATCH_GROUP');
 dbms_resource_manager.delete_consumer_group(
   consumer_group => 'ADHOC_GROUP');
end;

---Validate Pending area for delete plan and consumer group

begin
 dbms_resource_manager.validate_pending_area();
end;

---submit pending area for delete plan and consumer group

begin
 dbms_resource_manager.submit_pending_area();
end;

--Clearing only the Pending Area, if you dont want to validate and submit

BEGIN
 DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;

Applications of Resource Manager (Additional Examples/Samples)

Resource Manager can be used in various ways to control and distribute the resources in Oracle Database Environment. The below Articles provides more samples/examples using resource manager for the database for different scenarios.      

Note 106948.1     Database Resource Manager samples

Note 471265.1     Example: How to control CPU Resources using the Resource Manager and Scheduler

Note 1266650.1   How To Switch Users Between Resource Plans Based On Schedule Window

Note 1168574.1   How to Force RESOURCE_MANAGER_PLAN with the Scheduler window boundaries

Note 971991.1  How To Incorporate Pre-Defined jobs into your Resource Manager Plan

Note 277419.1     Prevention of long-running queries with RM MAX_EST_EXEC_TIME Plan Directive

Note 1359043.1   Configuring Parallel Statement Queuing to Manage Mixed Priority Workloads in a DataWarehouse

Note 470764.1     How to Control Resources used by a Statisics Job using the Resource Manager

Note 848666.1     How to Limit max number of concurrently active sessions with Resource Manager

Note 1321099.1   How to limit the Parallel Degree for PX Queries with Resource Manager

Note 1345429.1   Parallel Statement Queuing in 11.2.0.2

Note 1269321.1   Automatic Degree of Parallelism in 11.2.0.2

Note 1388634.1   Reverse Engineering a Resource Manager Plan From Within the Database

Note 1674897.1   How to Generate Resource Manager Configuration DDLs Using Dbms_metadata Packages

Note 987436.1     How to Identify the Session that Started the Resource Manager Pending Area

Note 398985.1     How to set Resource Manager to Disconnect Idle Users after a Predetermined time?

Note 233651.1     Why Resource Manager Is Not Distributing CPU As Per Resource Plan

Note 1557657.1   Using Resource Manager to Detect and Kill Idle Sessions    

Note 786346.1     Resource Manager and Sql Tuning Advisory DEFAULT_MAINTENANCE_PLAN

Note 1296086.1   Using PL/SQL To Execute SQL or Stored Procedure/Function Calls for a Set of Users

Note 727062.1     Configuring and using Calibrate I/O

Note 1393405.1   How To Delete Calibrate I/O In The Instance

Note 884082.1     Resource Manager Enhancements in Oracle Database 11g

Note 451889.1     Logical Standby and Resource Manager

Note 1339769.1   Master Note for Oracle Database Resource Manager for EXADATA

Note 1358709.1   Configuring Resource Manager for Mixed Workloads in a Database

Note 1362445.1   Configuring and Monitoring Instance Caging

Oracle Database Resource Manager Examples

http://docs.oracle.com/cd/B28359_01/server.111/b28310/dbrm006.htm#ADMIN11012

12c: Using Oracle Resource Manager on pluggable databases (PDBs) in a multitenant container database (CDB)

Oracle Resource Manager can be used to allocate resources to pluggable databases (PDBs) in a multitenant container database (CDB).
In a non-CDB, you can use Resource Manager to manage multiple workloads that are contending for system and database resources. However, in a CDB, you can have multiple workloads within multiple PDBs competing for system and CDB resources.

Please refer the Oracle Documentation Below:

Using Oracle Resource Manager for PDBs with SQL*Plus

http://docs.oracle.com/database/121/ADMIN/cdb_dbrm.htm#ADMIN13774

Using Oracle Resource Manager for PDBs with Cloud Control

http://docs.oracle.com/database/121/ADMIN/cdb_dbrm_em.htm#ADMIN14129

Oracle Resource Manager and Operating System (OS) Resource manager

The Oracle Database Resource Manager solves many resource allocation problems that an operating system does not manage well like Excessive overhead, Inefficient scheduling, Inappropriate allocation of resources, Inability to manage database-specific resources.

The Resource Manager helps overcome these problems by giving the database more control over allocation of hardware resources and enabling you to prioritize work within the database. You can classify sessions into groups based on session attributes, and then allocate resources to these groups to optimize hardware utilization. Refer the Note 1102753.1 mentioned below, if you want to enable Oracle Resource Manager and an Operating System Resource Manager together on one system

Note 1102753.1    Operating System Resource Manager and Oracle Resource Manager support 

Oracle Database Services and Resource Manager

Database Resource Manager can map services to consumer groups. Therefore, you can automatically manage the priority of one service relative to others. You can use consumer groups to define relative priority in terms of either ratios or resource consumption. Services are integrated with the Resource Manager, which enables you to restrict the resources that are used by the users who connect to an instance by using a service. The Resource Manager enables you to map a consumer group to a service so that users who connect to an instance using that service are members of the specified consumer group.

Troubleshooting/Known Issues

The following Troubleshooting Guide (TSG) can assist you for investigating the knownissues and will help you to fix those.

Note 975455.1  Troubleshooting Guide: Oracle Resource Manager  

Recommended Patches

The following Notes will list out the recommended patches for Oracle Database Resource Manager, so that you can use RM without issues.

Note 1340184.1   Recommended Patches for Managing Runaway Queries with RM (Supercedes Note 1208133.1

Note 1339803.1   Recommended Patches for CPU Resource Manager (Supersedes Note 1207483.1)

Note 1340172.1   Recommended Patches for Instance Caging (Supersedes Note 1208064.1)

Note 1340178.1   Recommended Patches for Resource Manager Utilization Limits(Supercedes Note 1208104.1

Note 1340180.1   Recommended Patches for Parallel Statement Queuing

Note 1340181.1   Recommended Patches for Exadata I/O Resource Manager 

Sunday Oct 12, 2014

ORAchk New Version 12.1.0.2.1

The new ORAchk release 12.1.0.2.1 is now available to download.

Easier to stay up to date with the most current version of ORAchk

ORAchk release versioning now aligns with and follows the same format used by the Oracle 12c Database Patch Set Updates (PSUs); this version is 12.1.0.2.1, the next will be 12.1.0.2.2.
It’s also now even easier to update ORAchk across multiple machines.
  1. Download the latest release of orachk.zip into a shared network staging directory
  2. Set the environment variable $RAT_UPGRADE_LOC to the staging directory
  3. The next time ORAchk is run it will prompt you to auto update

New ORAchk Support for Windows

ORAchk is now supported on Windows when run within a Cygwin environment. Instructions for configuring Cygwin can be found from Document 1268927.2. ORAchk now includes hundreds of database and application checks which will run on Windows. There are even more Windows specific checks in the pipeline.

Easier execution for environments with role separation

You no longer need to have different users execute different ORAchk profiles to workaround your company’s implementation of role separation. ORAchk can now be run once as root to execute all checks. Prior to executing checks that do not require root access, ORAchk will switch user to the lower level accounts.

Faster execution time

When running against multiple databases, ORAchk can now run database checks in parallel meaning it takes a fraction of the time to complete execution. Parallel database execution is now the default. It can be turned off, if you prefer to run checks serially.

Enhanced comparison of result output

Quickly find out what has changed on your system between two ORAchk runs. When ORAchk is run with the –diff command it will now not only compare check results but collection data too. Quickly compare and understand differences in kernel parameters or database initialization parameters.

Over 70 new EBS checks

ORAchk support for EBS has been enriched and broadened, with even more checks for Oracle Payables (R12) and Oracle Workflow and now with release 12.1.0.2.1 introduces new support for Oracle Order Management (R12) and Oracle Process Manufacturing (R12).

For more details and to download the latest release of ORAchk see Document 1268927.2

Monday Jan 20, 2014

Master Note: Database Performance Overview (Doc ID 402983.1)

Purpose
Questions and Answers
Pro-Active Problem Avoidance and Diagnostic Collection
Performance Information Centres
Troubleshooting Guides
Common Problem Topics
Slow Database Performance
Concurrency Issues
Database Hangs/Spins
Session Hangs/Spins
Locking Issues
Deadlock
Pre-Upgrade Planning
Post-Upgrade Performance Issue Resolution
Debugging Waits for Various Events
Library Cache/Mutex Contention/Cursor Type Events:
Other Types:
Common Causes of Performance issues
Cursor Sharing/High Version Counts for Cursors
High CPU usage
Issues With waits for 'log file sync'
WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!
Buffer Busy/Cache Buffers Chains Latch waits
SYSAUX Issues
Performance Diagnostics References
General Diagnostics Overview
AWR/Statspack
10046 Trace
Systemstates/Hanganalyze
Errorstacks for Performance Issues
PStack
PL/SQL Profiler
OS Watcher
LTOM
Trace/Result Interpretation
Performance (and other) Webcasts
Performance and Scalability White Papers and Documentation
Interacting With Performance Support
Community: Database Tuning
References

Applies to:

Oracle Database - Standard Edition - Version 7.0.16.0 and later
Oracle Database - Personal Edition - Version 7.1.4.0 and later
Oracle Database - Enterprise Edition - Version 6.0.0.0 and later
Enterprise Manager for Oracle Database - Version 8.1.7.4 and later
Information in this document applies to any platform.

Purpose

This Document provides an overview reference to content that pertains to the resolution of Performance issues affecting the whole database.

For the Database Performance Frequently Asked Questions FAQ,information regarding Performance Related Features or  issues involving individual SQL statements, see:

Document 1360119.1 FAQ: Database Performance Frequently Asked Questions
Document 1361401.1 Where to Find Information About Performance Related Features

Document 199083.1 Master Note: SQL Query Performance Overview
Document 1516494.2 Information Center: SQL Query Performance Overview
Document 398838.1 FAQ: SQL Query Performance - Frequently Asked Questions

Questions and Answers

Pro-Active Problem Avoidance and Diagnostic Collection

Although some problems may be unforeseen, in many cases problems may be avoidable if signs are detected early enough. Additionally, if an issue does occur, it is no use collecting information about that issue after the event. For information on suggested preparations to avoid issues and in case diagnostics are required, see:

Document 1482811.1 Best Practices: Proactively Avoiding Database and Query Performance Issues
Document 1477599.1 Best Practices Around Data Collection For Performance Issues

Performance Information Centres

Performance Information Centres provide access to various aspects of performance issues:

Document 1512292.2 Information Center: Database Performance Overview
Document 1516494.2 Information Center: SQL Query Performance Overview

Troubleshooting Guides

There are a number of troubleshooting guides that can help resolve various issues:

Document 1543445.2 Troubleshooting Assistant: Troubleshooting Performance Issues
Document 1542678.2 Troubleshooting Assistant: SQL Performance Issues

Document 1528847.1 Troubleshooting: Avoiding and Resolving Database Performance Related Issues After Upgrade
Document 60.1 Troubleshooting Assistant: Oracle Database ORA-00060 Errors on Single Instance (Non-RAC) Diagnosing Using Deadlock Graphs in ORA-00060 Trace Files

Common Problem Topics

  • Slow Database Performance

    If the database is performing slowly see the following:

    Document 1362329.1 How To Investigate Slow Database Performance Issues
  • Concurrency Issues

    If you have problems with the concurrency of transactions, initially treat this as a "Slow Database Performance" issue and refer to the following:

    Document 1362329.1 How To Investigate Slow Database Performance Issues
  • Database Hangs/Spins

    If the database encounters what looks like a hang or a spin conditions refer to the following:

    Document 452358.1 How to Collect Diagnostics for Database Hanging Issues
    Document 68738.1 No Response from the Server, Does it Hang or Spin?

    Document 392037.1 Database Hangs. Sessions wait for 'resmgr:cpu quantum'
  • Session Hangs/Spins

    If an individual session appears to hang or spin conditions refer to the relevant sections in the following articles:

    Document 1362329.1 How To Investigate Slow Database Performance Issues
    Document 352648.1 How to Diagnose high CPU usage problems
  • Locking Issues

    If sessions encounter locking conflicts refer to the following:

    Document 1392319.1 Master Note: Locks, Enqueues and Deadlocks
    Document 15476.1 FAQ about Detecting and Resolving Locking Conflicts

    Document 62354.1 TX Transaction locks - Example wait scenarios
    Document 33453.1 REFERENTIAL INTEGRITY AND LOCKING
    • Deadlock

      ORA-60 is an application error which usually occurs because a consistent locking strategy has not been followed throughout an application.

      Please refer to the following articles for more information:

      Document 1509919.1 Master Note for Database Error ORA-00060
      Document 18251.1 OERR: ORA 60 "deadlock detected while waiting for resource"
      Document 62365.1 What to do with "ORA-60 Deadlock Detected" Errors

      Please ensure that you are encountering none of the conditions in these articles.
  • Pre-Upgrade Planning

    For upgrades to 11g, there is a useful webcast entitled "11G Upgrade Best Practices" :
    This recorded seminar provides an overview of tips and best practices for upgrading your database to 11G. It starts with general Challenges and Support Best Practices, but moves on to performance specifics such as AWR and STATSPACK, SQL Plan Management, and Real Application Testing.

    This webcast can be found here:


    For advice on planning your upgrade see:

    Document 785351.1 Oracle 11gR2 Upgrade Companion
    Document 601807.1 Oracle 11gR1 Upgrade Companion
    Document 466181.1 10g Upgrade Companion

    Document 465787.1 Managing CBO Stats during an upgrade to 10g or 11g

    For more general upgrade related information see:

    Document 1392633.1 Things to Consider before upgrading to 11.2.0.3 to avoid performance or wrong results
    Document 1320966.1 Things to Consider before upgrading to 11.2.0.2 to avoid performance or wrong results

    Document 1464274.1 Master Note for Real Application Testing Option
    Document 762540.1 Consolidated Reference List Of Notes For Migration / Upgrade Service Requests
  • Post-Upgrade Performance Issue Resolution

    To troubleshoot performance issues encountered after upgrading a database see:
    Document 1528847.1 Troubleshooting: Avoiding and Resolving Database Performance Related Issues After Upgrade
  • Debugging Waits for Various Events

    There are numerous articles to help you debug issues with wait event contention. The most common of these being:

    Library Cache/Mutex Contention/Cursor Type Events:

    Document 1356828.1 FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events
    Document 1377998.1 Troubleshooting: Waits for Mutex Type Events
    Document 1349387.1 Troubleshooting 'cursor: pin S wait on X' waits
    Document 1357946.1 Troubleshooting 'library cache: mutex X' waits.

    Other Types:

    Document 1376916.1 Troubleshooting: "Log File Sync" Waits

    Document 1342917.1 Troubleshooting 'latch: cache buffers chains' Wait Contention
    Document 34405.1 WAITEVENT: "buffer busy waits" Reference Note

    Document 223117.1 Troubleshooting I/O-related waits

    Document 1415999.1 Troubleshooting: Virtual Circuit Waits
  • Common Causes of Performance issues

    • Cursor Sharing/High Version Counts for Cursors

      The failure to share cursors effectively can have a highly detrimental affect on a database. For example, having hundreds of versions of the same cursor is likely to be wasting space, is unlikely to promote good performance and, in extreme cases, can cause contention issues.
      For issues linked to Cursor related waits see:

      Document 1356828.1 FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events

      Also, see the following articles for more information and help with troubleshooting issues:

      Document 296377.1Troubleshooting: High Version Count Issues
      Document 438755.1 High SQL Version Counts - Script to determine reason(s)

      Document 62143.1 Understanding and Tuning the Shared Pool
    • High CPU usage

      In cases where CPU usage of individual sessions or the whole database is high, refer to the following:

      Document 352648.1 How to Diagnose High CPU Usage Problems to the Module Level
      Document 273646.1 How to diagnose the high CPU utilization of ORACLE.EXE in Windows environment

      Note that High CPU usage can be caused by many factors including contention for cursors (see above), inefficient SQL and may be associated with excessive buffer reads etc.
    • Issues With waits for 'log file sync'

      If your system is waiting for the redo log files to synchronise with the disk, the following articles can help:

      Document 1376916.1 Troubleshooting: "Log File Sync" Waits

      Document 34592.1 WAITEVENT: "log file sync" Reference Note

      Document 857576.1 How to Minimise Waits for 'Log File Sync'?
      Document 1278149.1 High Log File Sync Wait Events, LGWR Posting Slow Write Times, Low IO Waits

      Document 1064487.1 Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql)

  • WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!

    This Issue occurs when the database detects that a waiter has waited for a resource for longer than a particular threshold. The message "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" appears in the alert log and trace and systemstates are dumped.

    Typically this is caused by two (or more) incompatible operations being run simltaneously. See:

    Document 278316.1 Troubleshooting: "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!"
  • Buffer Busy/Cache Buffers Chains Latch waits

    Buffer Busy waits occur when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy".

    Cache Buffers Chains Latch waits are caused by contention where multiple sessions waiting to read the same block.

    Typical solutions are to look for SQL that accesses the blocks in question and determine if the repeated reads are necessary.

    Document 1342917.1 Troubleshooting 'latch: cache buffers chains' Wait Contention

    Document 34405.1 WAITEVENT: "buffer busy waits" Reference Note
    Document 155971.1 Resolving Intense and "Random" Buffer Busy Wait Performance Problems:
    Document 163424.1 How To Identify a Hot Block Within The Database Buffer Cache

  • SYSAUX Issues

    SYSAUX is a mandatory tablespace in 10g which stores all auxiliary database metadata related to various product options and features. Of particular interest to Performance is the storage of Automatic Workload Repository (AWR) data:

    Document 552880.1 General Guidelines for SYSAUX Space Issues

    Document 1055547.1 SYSAUX Grows Because Optimizer Stats History is Not Purged
    Document 387914.1 WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged

    Document 554831.1 How to Control the Set of Top SQLs Captured During AWR Snapshot Generation
    Document 1292724.1 SYSAUX Tablespace Grows Rapidly

Performance Diagnostics References

  • Errorstacks for Performance Issues

    Document 1364257.1 How to Collect Errorstacks for use in Diagnosing Performance Issues
  • PStack

    Document 70609.1  How To Display Information About Processes on SUN Solaris
  • PL/SQL Profiler

    Document 243755.1 Implementing and Using the PL/SQL Profiler
  • OS Watcher

    OS Watcher (OSW) is a collection of UNIX shell scripts intended to collect and archive operating system and network metrics to aid support in diagnosing performance issues.

    Document 301137.1 OS Watcher User Guide
  • LTOM

    The Lite Onboard Monitor (LTOM) is a java program designed as a proactive, real-time diagnostic platform and provides real-time automatic problem detection and data collection.

    Document 352363.1 LTOM - The On-Board Monitor User Guide
    Document 461050.1 The LTOM Graph (LTOMg) User Guide
    Document 461228.1 The LTOM Graph FAQ

    Document 461052.1 LTOM System Profiler - Sample Output
    Document 977975.1 Diagnose LTOM Connection Problems

  • Trace/Result Interpretation

    The following articles can help with the interpretation of various diagnostics:

    Document 390374.1 - Oracle Performance Diagnostic Guide (OPDG)
    Document 39817.1 Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output

    http://www.oracle.com/technetwork/database/focus-areas/performance/statspack-opm4-134117.pdf

Performance (and other) Webcasts

A series of Performance (and General Datbase topic) related webcasts, including topics such as SQLHC, SQLTXPLAIN and OSWBB, can be found here:

Document 1456176.1 Oracle Database Advisor Webcast Archives

Performance and Scalability White Papers and Documentation

Performance and Scalability White Papers:

http://www.oracle.com/technetwork/database/focus-areas/performance/index.html

For White papers concerning Real Application Testing (RAT) and SQL Performance Analyzer (SPA) see:

Document 1546337.1 Real Application Testing (RAT): Recommended White Papers

Links to the main Tuning and Performance documentation:

Document 1195363.1 Database Performance and SQL Tuning Documentation on OTN

Interacting With Performance Support

Document 210014.1 How to Log a Good Performance Service Request
Document 166650.1 Working Effectively With Global Customer Support

Community: Database Tuning

A community has been set up for My Oracle Support (MOS) users moderated by Oracle. The goal of this community is to exchange database related Tuning knowledge and concepts. The community can be found via the following article:

Document 1383594.1 Collaborate With MOS Database Tuning Community Members



References

NOTE:1320966.1 - Things to Consider Before Upgrading to 11.2.0.2 to Avoid Poor Performance or Wrong Results
NOTE:461228.1 - The LTOM Graph FAQ
NOTE:175006.1 - Steps to generate HANGANALYZE trace files (9i and below)
NOTE:18251.1 - OERR: ORA 60 "deadlock detected while waiting for resource"
NOTE:452358.1 - How to Collect Diagnostics for Database Hanging Issues
NOTE:199083.1 - * Master Note: SQL Query Performance Overview
NOTE:223117.1 - Troubleshooting I/O-related waits
NOTE:68738.1 - No Response from the Server, Does it Hang or Spin?

NOTE:387914.1 - WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged
NOTE:390374.1 - Oracle Performance Diagnostic Guide (OPDG)
NOTE:392037.1 - Database 'Hangs'. Sessions Wait for 'resmgr:cpu quantum'
NOTE:166650.1 - Working Effectively With Support Best Practices
NOTE:461050.1 - The LTOM Graph (LTOMg) User Guide
NOTE:1357946.1 - Troubleshooting 'library cache: mutex X' waits.
NOTE:352648.1 - How to Diagnose High CPU Usage Problems to the Module Level
NOTE:554831.1 - How to Control the Set of Top SQLs Captured During AWR Snapshot Generation
NOTE:398838.1 - FAQ: SQL Query Performance - Frequently Asked Questions
NOTE:465787.1 - How to: Manage CBO Statistics During an Upgrade from 10g or 9i into 11g
NOTE:376442.1 - * How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
NOTE:39817.1 - Interpreting Raw SQL_TRACE output
NOTE:62365.1 - Troubleshooting "ORA-00060 Deadlock Detected" Errors
NOTE:785351.1 - Oracle 11gR2 Upgrade Companion
NOTE:857576.1 - How to Minimise Waits for 'Log File Sync'
NOTE:94224.1 - FAQ- Statspack Complete Reference
NOTE:70609.1 - How To Display Information About Processes on SUN Solaris
NOTE:977975.1 - Diagnose LTOM Connection Problems
NOTE:1055547.1 - SYSAUX Grows Because Optimizer Stats History is Not Purged
NOTE:34592.1 - WAITEVENT: "log file sync" Reference Note
NOTE:352363.1 - LTOM - The On-Board Monitor User Guide
NOTE:601807.1 - Oracle 11gR1 Upgrade Companion
NOTE:210014.1 - How to Log a Good Performance Service Request
NOTE:1377446.1 - * Troubleshooting Performance Issues
NOTE:243755.1 - Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data
NOTE:1528847.1 - Troubleshooting: Avoiding and Resolving Database Performance Related Issues After Upgrade
NOTE:1392633.1 - Things to Consider Before Upgrading to 11.2.0.3 to Avoid Poor Performance or Wrong Results
NOTE:466181.1 - Oracle 10g Upgrade Companion
NOTE:1361401.1 - Where to Find Information About Performance Related Features
NOTE:1362329.1 - How To Investigate Slow or Hanging Database Performance Issues
NOTE:1363422.1 - Automatic Workload Repository (AWR) Reports - Start Point
NOTE:1364257.1 - How to Collect Errorstacks for use in Diagnosing Performance Issues.
NOTE:163424.1 - How To Identify a Hot Block Within The Database Buffer Cache.
NOTE:1360119.1 - * FAQ: Database Performance Frequently Asked Questions
NOTE:552880.1 - General Guidelines for SYSAUX Space Issues
NOTE:1278149.1 - Intermittent Long 'log file sync' Waits, LGWR Posting Long Write Times, I/O Portion of Wait Minimal

NOTE:1195363.1 - Database Performance and SQL Tuning Documentation on OTN
NOTE:121779.1 - Taking Systemstate Dumps when You cannot Connect to Oracle
NOTE:62143.1 - Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
NOTE:1415999.1 - Troubleshooting: Virtual Circuit Waits

NOTE:1064487.1 - Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql)
NOTE:438452.1 - Performance Tools Quick Reference Guide
NOTE:438755.1 - High SQL Version Counts - Script to determine reason(s)
NOTE:461052.1 - LTOM System Profiler - Sample Output
NOTE:278316.1 - Troubleshooting: "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! "
NOTE:15476.1 - FAQ: Detecting and Resolving Locking Conflicts and Ora-00060 errors
NOTE:1392319.1 - Master Note: Locks, Enqueues and Deadlocks (ORA-00060)
NOTE:155971.1 - Resolving Intense and "Random" Buffer Busy Wait Performance Problems
NOTE:1376916.1 - Troubleshooting: "log file sync" Waits
NOTE:1292724.1 - Suggestions if your SYSAUX Tablespace grows rapidly or too large
NOTE:62354.1 - Waits for 'Enq: Tx - Row Lock Contention' - Wait Scenario Examples
NOTE:1377998.1 - Troubleshooting: Waits for Mutex Type Events
NOTE:1383594.1 - Collaborate with MOS Database Tuning Community Members
NOTE:762540.1 - Consolidated Reference List Of Notes For Migration / Upgrade Service Requests
NOTE:1356828.1 - FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events
NOTE:1509919.1 - Master Note for Database Error ORA-00060 "deadlock detected while waiting for resource"

NOTE:781198.1 - Diagnostics for Database Performance Issues
NOTE:273646.1 - How to Diagnose High CPU Utilization of ORACLE.EXE in Windows Environment
NOTE:1342917.1 - Troubleshooting 'latch: cache buffers chains' Wait Contention
NOTE:1349387.1 - Troubleshooting 'cursor: pin S wait on X' waits.
NOTE:296377.1 - Troubleshooting: High Version Count Issues
NOTE:301137.1 - OSWatcher Black Box (Includes: [Video])
NOTE:33453.1 - Locking and Referential Integrity

NOTE:34405.1 - WAITEVENT: "buffer busy waits" Reference Note

Sunday Jul 04, 2010

Oracle Gateway Master Note (Doc ID 1083703.1)

Master Note for Oracle Gateway Products

1. Concepts and Availability

Oracle Gateway products are based on Heterogeneous Services and allow access to non-Oracle databases from Oracle products.
Heterogeneous Services provides the generic technology for connecting to non-Oracle systems. As an integrated component of the database, Heterogeneous Services can exploit features of the database, such as the powerful SQL parsing and distributed optimization capabilities.
Heterogeneous Services extend the Oracle SQL engine to recognize the SQL and procedural capabilities of the remote non-Oracle system and the mappings required to obtain necessary data dictionary information.

There are dedicated Gateways for the following non-Oracle data sources -
- Microsoft SQL*Server - Database Gateway for SQL*Server (DG4Msql)
- Sybase - Database Gateway for Sybase (DG4Sybase)
- Informix - Database Gateway for Informix (DG4Ifmx)
- IBM DB2 - Database Gateway for DRDA (DG4DRDA)
- Teradata - Database Gateway for Teradata (DG4Teradata)
- Websphere MQ - Database Gateway for Websphere MQ (DG4MQ)
- Remote online transaction processors (OLTPs) - Database Gateway for APPC (DG4APPC)
- IMS - Database Gateway for IMS (DG4IMS)
- VSAM - Database Gateway for VSAM (Dg4VSAM)
- Adabas - Database Gateway for Adabas (DG4Adabas)

Non-Oracle datasources for which a dedicated gateway is not available can be accessed by using the following -

Database Gateway for ODBC (DG4ODBC)

which uses third party ODBC drivers to make the connection to non-Oracle data sources such as as MySQL, Progress, Ingres.
It can also be used to access non-Oracle databases for which a dedicated gateway is available.
Further details are available in this note -

Note.233876.1 Options for Connecting to Foreign Data Stores and Non-Oracle Databases - For example - DB2, SQL*Server, Sybase, Informix, Teradata, MySQL

There are differences in the functionality and licensing of the Database Gateway for ODBC and the other gateways which are discussed in these notes -
Note.252364.1 Functional Differences Between Generic Connectivity and Database Gateways
Note.232482.1 Gateway and Generic Connectivity Licensing Considerations

The following note has information about the desupport of earlier gateway versions -

Note.549796.1 Desupport of Oracle Transparent Gateways
Note.353723.1 Oracle Transparent Gateway for iWay
Note.353728.1 Oracle Transparent Gateway for INGRES
Note.353725.1 Oracle Transparent Gateway for Rdb
Note.353729.1 Oracle Transparent Gateway for RMS
Note.417250.1 Oracle Transparent Gateway for INFORMIX
Note.417253.1 Oracle Transparent Gateway for SYBASE
Note.417254.1 Oracle Transparent Gateway for MS SQL Server
Note.417251.1 Oracle Transparent Gateway for TERADATA
Note.420436.1 Oracle Transparent Gateway for DRDA - SNA Support desupport
Note.559947.1 Oracle Access Manager for AS/400
Note.559948.1 Oracle Transparent Gateway for DB2/400

Oracle's Software Error Correction Support policy document is available in this note -

Note.209768.1 Database, FMW, EM Grid Control, and OCS Software Error Correction Support Policy

Oracle's Lifetime Support Policy document is available here:

http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf

For information about the Gateway support policy refer to the section -

Oracle Gateway Release's

The certification matrix for the 11.1 and 11.2 Gateways is now available on OTN from the following URL -

http://www.oracle.com/technetwork/database/gateways/index.html

then -

Database Gateways Certification Matrix (PDF)
Mainframe Database Gateways Certification Matrix (PDF)
Legacy Database Gateways Certification Matrix (PDF)

Please note that the Legacy Gateways -
DG4IMS
DG4VSAM
DG4Adabas

can no longer be ordered. They will still be available on install media but will be decommissioned.

2. Downloading Gateway Products

Before trying to download a Gateway please refer to the certification matrix to make sure it is available for your platform and version.

Oracle Gateway products can be downloaded from the following -

1. Oracle Technology Network - OTN -

http://www.oracle.com/technology/software/products/database/index.html

Choose the relevant platform and version then the 'See All' option.
Under that there will be an option to download the Gateway products, for example -

Oracle Database Gateways 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit)

Please note that only 11.2 versions of the Gateway products are available on Windows 64-bit.  The earlier versions are not available on that platform.

2. Oracle Software Delivery Cloud


http://edelivery.oracle.com/

- go through the Terms & Restriction proces
- on the next screen ' Media Pack Search' screen choose -
 Select a Product Pack - Oracle database
- then choose your platform
- choose the 'Oracle database' media pack for the version you need - 11.1 or 11.2
- on the next screen there will be a gateway media pack to download.

The following note has links to a video showing how to download gateways -

Note.1279746.1 Where Can I Find And Download the Latest Gateways Available From Oracle?

3. My Oracle Support - 11.2.0.2 and 11.2.0.3

The 11.2.0.2  and 11.2.0.3 versions of the gateways are included in the Oracle Database patch sets 10098816 and 10404530.
Starting with the 11.2.0.2 patch set, Oracle Database patch sets are full installations of the Oracle Database software. This means that you do not need to install a 11.2.0.1 release of a gateway  before installing the 11.2.0.2 or 11.2.0.3 versions. The later versions can be installed in completely separate ORACLE_HOME directories from any existing installs.
To download the latest 11.2.0.3 gateways -

- logon to My Oracle Support
- go to Patches & Updates section
- search for patch number 10404530 and choose your platform
- click on the patch number
- for most platforms for the gateways you only need to download -
p10404530_112030_platform_5of7.zip
for example -
p10404530_112030_Linux-x86-64_5of7.zip
but for Windows 32-bit this is -
p10404530_112030_WINNT_4of6.zip



To confirm which file is needed for the Gateways review the 'README' file which details which download files contain which software.

The 11.2.0.3 patch is also available for some platforms and can be found as patch 10404530 on My Oracle Support.

3. Installation and Configuration

The installation and configuration for each gateway is described in the documentation.
This is available from -

http://www.oracle.com/technology/documentation/index.html

Click on the version required - 11.1 or 11.2  - then 'View Library'
The Gateway documentation is available from the 'Information Integration' option under the 'Heterogeneous Connectivity' heading.

The following note gives an overview of the Gateway install process -

Note.1351618.1 Installation Overview For Oracle Database Gateway Products


There are also notes available in My Oracle Support (MOS) to help with the install and configuration -

- logon to MOS -

http://support.oracle.com

- Knowledge tab
- Oracle Database Products
- Oracle Database
- Gateways
- then choose the relevant gateway

Examples are -

Note.437374.1 How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) Release 11 on Linux x86 32bit post install
Note.466267.1 How to Configure DG4MSQL (Database Gateway for MS SQL Server) on Windows 32bit post install
Note.562509.1 How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install
Note.1086365.1
How to Configure DG4MSQL (Database Gateway for MS SQL Server) on a 64bit Windows post install
Note.466228.1 How to Configure DG4ODBC on Linux x86 32bit to Connect to Non-Oracle Databases post install
Note.561033.1 How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX) to Connect to Non-Oracle Databases Post Install
Note.466225.1 How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit to Connect to Non-Oracle Databases Post Install
Note.1266572.1 How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems to Connect to Non-Oracle Databases Post Install
Note.945879.1 How to Setup DG4DRDA (Oracle Database Gateway for DRDA) Release 11.2 on Unix (Linux, AIX, HP-UX Itanium and Solaris) to Connect to DB2
Note.1086359.1 How to Setup DG4DRDA (Oracle Database Gateway for DRDA) on Windows x86 (32bit and 64bit) to Connect to DB2
Note.437689.1 How to Setup DG4IFMX (Oracle Database Gateway for Informix) Release 11 on Linux
Note.437696.1 How to Setup DG4SYBS (Oracle Database Gateway for Sybase) Release 11 on Linux
Note.554402.1 How to Setup DG4TERA (Oracle Database Gateway for TeraData) on Windows 32bit
Note.823534.1 How to Setup DG4Tera (Oracle Database Gateway for TeraData) on 64bit Unix
Note.437680.1How to Setup DG4Tera (Oracle Database Gateway for TeraData) Release 11 on Linux
Note.467947.1Setting up Legacy Gateways DG4ADABAS, DG4VSAM, DG4IMS)
Note.564299.1 How to Install DG4MQ on Linux

but new notes are continually being added.

The following notes have details of a configuration utility for the relevant gateway -

Note.1274157.1 Gateway Configuration Utility for Database Gateway For SQL Server
Note.1274143.1 Gateway Configuration Utility for Database Gateway for ODBC - DG4ODBC - to Connect to Non-Oracle Databases For Example - DB2, SQL*Server, Sybase, Informix, MySQL
Note.1286444.1 Gateway Configuration Utility for Database Gateway for DRDA - DG4DRDA - to Connect to DB2
Note.1286435.1 Gateway Configuration Utility for Database Gateway for Sybase



4. Troubleshooting

The following note details some of the common errors and solutions for Gateway issues -

Note.234517.1 How to Resolve Common Errors Encountered while using Transparent Gateways or Generic Connectivity

The following note details causes of some ORA-7445 errors -

Note.1420697.1 ORA-7445 Error Messages Using an Oracle Database Gateway (DG4DRDA, DG4IFMX, DG4MSQL, DG4ODBC, DG4SYBS, DG4TERA)


If you need to raise a service request with Oracle Support then the following information is needed to help speed up resolution -

- name and full version of the gateway being used
- platform and version where the gateway is installed
- version of the Oracle RDBMS being used to access the gateway
- name and full version of the non-Oracle data source being accessed
- configuration files and information -
gateway listener.ora
gateway init<sid>.ora
tnsnames.ora
create database link statement
- statement causing the error
- full error being received from SQLPLUS when running the problem statement
- a gateway debug trace file from running the problem statement. This is created by adding the following to the gateway init<sid..ora file -
HS_FDS_TRACE_LEVEL=debug
and running the problem statement from a new session. It is not necessary to stop and start the listener for this to take effect.
- note that for DG4DRDA 11.1 there is a different procedure for setting up debug tracing which is described in the documentation and also in these notes -
Note.221136.1 How To Trace DRDA Gateway (TG4DRDA or DG4DRDA) On Unix platforms
Note.428387.1 How To Trace The Transparent Gateway For DRDA (TG4DRDA) On Windows Platforms

Common Gateway error messages are -

ORA-28545 error diagnosed by Net8 when connecting to an agent
ORA-28500 connection from ORACLE to a non-Oracle system returned this message:
ORA-02063 preceding <str> from <name>
ORA-28511 lost RPC connection to heterogeneous remote agent using
ORA-02085 database link %s connects to %s
ORA-02062 distributed recovery received DBID <num>, expected <num >
ORA-02019 connection description for remote database not found
ORA-28513 internal error in heterogeneous remote agent
ORA-28500 connection from ORACLE to a non-Oracle system returned this message

5. Alerts

Alert notes for Gateway products are available in My Oracle Support.
To access alert notes do the following -

- logon to My Oracle Support
- choose the Knowledge tab
- In 'Knowledge Articles' click the pencil icon
- in the 'Product' option enter - gateway
- choose the gateway you want to see
- a list will be displayed if there is an alert
- there will also be a list of recently updated articles for the gateway


Who to contact for more information?


Gateway information is available from My Oracle Support (MOS) -

http://support.oracle.com

- Knowledge tab
- make sure you have enabled 'Browse Knowledge' in Customize
- in 'Find a Product by Name' enter 'gateway' - without the quotes
- then choose the relevant gateway
- continue in the 'What do you want to do' section

There is a My Oracle Support Database Gateways Community, a place to collaborate with peers in the industry and with Oracle experts which is live now at:

https://communities.oracle.com/portal/server.pt/community/database_gateways/299

There is also an Oracle Technology Network forum available at -

http://forums.oracle.com/forums/forum.jspa?forumID=63





















Friday Jun 11, 2010

ORA-4030 Troubleshooting

QUICKLINK:

Note 4030.1 OERR: ORA 4030 (Known Issues)

Note 399497.1 FAQ ORA-4030
Note 1088087.1 : ORA-4030 Diagnostic Tools [Video]

Have you observed an ORA-0430 error reported in your alert log?

ORA-4030 errors are raised when memory or resources are requested from the Operating System and the Operating System is unable to provide the memory or resources. The arguments included with the ORA-4030 are often important to narrowing down the problem.

For more specifics on the ORA-4030 error and scenarios that lead to this problem, see Note 399497.1 FAQ ORA-4030.

Looking for the best way to diagnose?

There are several available diagnostic tools (error tracing, 11g Diagnosibility, OCM, Process Memory Guides, RDA, OSW, diagnostic scripts) that collectively can prove powerful for identifying the cause of the ORA-4030.

Error Tracing

The ORA-4030 error usually occurs on the client workstation and for this reason, a trace file and alert log entry may not have been generated on the server side. It may be necessary to add additional tracing events to get initial diagnostics on the problem.


To setup tracing to trap the ORA-4030, on the server use the following in SQLPlus:

alter system set events '4030 trace name heapdump level 536870917;name errorstack level 3';

Once the error reoccurs with the event set, you can turn off tracing using the following command in SQLPlus:

alter system set events '4030 trace name context off; name context off';

NOTE: See more diagnostics information to collect in
Note 399497.1


11g Diagnosibility

Starting with Oracle Database 11g Release 1, the Diagnosability infrastructure was introduced which places traces and core files into a location controlled by the DIAGNOSTIC_DEST initialization parameter when an incident, such as an ORA-4030 occurs. For earlier versions, the trace file will be written to either USER_DUMP_DEST (if the error was caught in a user process) or BACKGROUND_DUMP_DEST (if the error was caught in a background process like PMON or SMON). The trace file may contain vital information about what led to the error condition.

 
Note 443529.1 11g Quick Steps to Package and Send Critical Error Diagnostic Information
to Support[Video] 
 
Oracle Configuration Manager (OCM)

Oracle Configuration Manager (OCM) works with My Oracle Support to enable proactive support capability that helps you organize, collect and manage your Oracle configurations.

Oracle Configuration Manager Quick Start Guide
Note 548815.1: My Oracle Support Configuration Management FAQ

Note 250434.1: BULLETIN: Learn More About My Oracle Support Configuration Manager 

General Process Memory Guides

An ORA-4030 indicates a limit has been reached with respect to the Oracle process private memory allocation. Each Operating System will handle memory allocations with Oracle slightly differently.



Solaris
Note 163763.1
Linux
Note 341782.1
IBM AIX
Notes 166491.1 and 123754.1
HP
Note 166490.1
Windows
Note 225349.1, Note 373602.1, Note 231159.1, Note 269495.1Note 762031.1
Generic
Note 169706.1

RDA

The RDA report will show more detailed information about the database and Server Configuration.

Note 414966.1 RDA Documentation Index

Download RDA -- refer to Note 314422.1 Remote Diagnostic Agent (RDA) 4 - Getting Started

OS Watcher (OSW)

This tool is designed to gather Operating System side statistics to compare with the findings from the database. This is a key tool in cases where memory usage is higher than expected on the server while not experiencing ORA-4030 errors currently.

Reference more details on setup and usage in Note 301137.1 OS Watcher User Guide

Diagnostic Scripts

Refer to Note 1088087.1 : ORA-4030 Diagnostic Tools [Video]

Common Causes/Solutions

The ORA-4030 can occur for a variety of reasons. Some common causes are:

* OS Memory limit reached such as physical memory and/or swap/virtual paging. For instance, IBM AIX can experience ORA-4030 issues related to swap scenarios. See Note 740603.1 10.2.0.4 not using large pages on AIX for more on that problem. Also reference Note 188149.1 for pointers on 10g and stack size issues.

* OS limits reached (kernel or user shell limits) that limit overall, user level or process level memory

* OS limit on PGA memory size due to SGA attach address
Reference:
Note 1028623.6 SOLARIS How to Relocate the SGA

* Oracle internal limit on functionality like PL/SQL varrays or bulk collections. ORA-4030 errors will include arguments like "pl/sql vc2" "pmucalm coll" "pmuccst: adt/re". See
Coding Pointers for pointers on application design to get around these issues

* Application design causing limits to be reached

* Bug - space leaks, heap leaks

* PL/SQL related issues
          Reference
Note 1325100.1 PLSQL Procedure Causing ORA-4030 Errors

***For reference to the content in this blog, refer to Note.1088267.1 Master Note for Diagnosing ORA-4030

ORA-4031 Troubleshooting

QUICKLINKS:

Note 4031.1 OERR: ORA 4031 (Known Issues)

Note 396940.1 Troubleshooting and Diagnosing ORA-4031 Error
Diagnostic Tool for ORA-4031

Note 1087773.1 ORA-4031 Diagnostics Tools [Video]

Have you observed an ORA-04031 error reported in your alert log?

An ORA-4031 error is raised when memory is unavailable for use or reuse in the System Global Area (SGA). The error message will indicate the memory pool getting errors and high level information about what kind of allocation failed and how much memory was unavailable. The challenge with ORA-4031 analysis is that the error and associated trace is for a "victim" of the problem. The failing code ran into the memory limitation, but in almost all cases it was not part of the root problem.

Looking for the best way to diagnose?

When an ORA-4031 error occurs, a trace file is raised and noted in the alert log if the process experiencing the error is a background process. User processes may experience errors without reports in the alert log or traces generated. The V$SHARED_POOL_RESERVED view will show reports of misses for memory over the life of the database.


Diagnostics scripts are available in
Note 430473.1 to help in analysis of the problem. There is also a training video on using and interpreting the script data Note 1087773.1.

 
11g Diagnosibility

Starting with Oracle Database 11g Release 1, the Diagnosability infrastructure was introduced which places traces and core files into a location controlled by the DIAGNOSTIC_DEST initialization parameter when an incident, such as an ORA-4031 occurs. For earlier versions, the trace file will be written to either USER_DUMP_DEST (if the error was caught in a user process) or BACKGROUND_DUMP_DEST (if the error was caught in a background process like PMON or SMON). The trace file contains vital information about what led to the error condition.

Note 443529.1 11g Quick Steps to Package and Send Critical Error Diagnostic 
Information to Support[Video]
 
Oracle Configuration Manager (OCM)
Oracle Configuration Manager (OCM) works with My Oracle Support to enable proactive 
support capability that helps you organize, collect and manage your Oracle configurations.

Oracle Configuration Manager Quick Start Guide Note 548815.1: My Oracle Support Configuration Management FAQ

Note 250434.1: BULLETIN: Learn More About My Oracle Support Configuration Manager 

Common Causes/Solutions

The ORA-4031 can occur for many different reasons. Some possible causes are:

  • SGA components too small for workload
  • Auto-tuning issues
  • Fragmentation due to application design
  • Bug/leaks in memory allocations
For more on the 4031 and how this affects the SGA, see 
Note 396940.1 Troubleshooting and Diagnosing ORA-4031 Error
 
Because of the multiple potential causes, it is important to gather enough diagnostics so that 
an appropriate solution can be identified.  However, most commonly the cause is associated 
with configuration tuning.   Ensuring that MEMORY_TARGET or SGA_TARGET are large 
enough to accommodate workload can get around many scenarios. 
 
The default trace associated with the error provides very high level information about the 
memory problem and the "victim" that ran into the issue.   The data in the default trace is 
not going to point to the root cause of the problem.
 
When migrating from 9i to 10g and higher, it is necessary to increase the size of the Shared 
Pool due to changes in the basic design of the shared memory area.
 
Note 270935.1 Shared pool sizing in 10g

NOTE: Diagnostics on the errors should be investigated as close to the time of the error(s) 
as possible.  If you must restart a database, it is not feasible to diagnose the problem 
until the database has matured and/or started seeing the problems again.
 
Note 801787.1 Common Cause for ORA-4031 in 10gR2, Excess "KGH: NO ACCESS" 
Memory Allocation
 
***For reference to the content in this blog, refer to Note.1088239.1 Master 
Note for Diagnosing ORA-4031 

ORA-7445 Troubleshooting

[Read More]

Wednesday Jun 09, 2010

ORA-600 Troubleshooting

[Read More]
About

News and Troubleshooting tips for Oracle Database and Enterprise Manager

Search

Categories
Archives
« March 2015
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    
       
Today