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 

Thursday Nov 13, 2014

EM Express 12c Database Administration Page FAQ

EM Express is a database management tool that focuses primarily on performance management and monitoring.  However, it does include basic administration functions to assist the database administrator. When users first log into EM Express, they see the home page which displays information about the database, as well as performance data, such as resource usage, average active sessions, and monitored SQL statements.

The three main categories of administrative tasks are:

1- Configuration

The four main features available in the Configuration menu are Initialization Parameters, Memory Configuration, Database Feature Usage and Database Properties.

Database Administrators can view and set initialization parameters for one or more instances of the database.  They can also determine how memory has been configured, and in particular, the amount of memory allocated to the SGA and PGA, the top processes consuming memory, and whether or not any of the automatic memory management features are in use.  In addition, memory advisor data is also displayed on the page.  To see an example of the Memory page, click here.


2- Storage

As part of Storage administration, DBAs can manage redo logs, archive logs, and tablespaces (view space consumption, create and/or alter tablespaces).  In addition, DBAs are also given some visibility into the control file contents.  DBAs can also view the Undo Advisor, as well as undo statistics, such as the undo generation rate and undo space usage.  To see an example of the Undo Page, click here.

3- Security

DBAs can use the features available in the Security menu to manage users, roles and profiles by creating, altering or granting and revoking privileges from them.

 

Frequently Asked Questions

How can I see Advisor Data on the Memory Page?

The advisor data visible at the top right panel of the Memory page is dependent on the memory configuration settings that are in use. 
If Automatic Memory Management is used, then only the Memory Advisor is displayed on the page. 
If Automatic Shared Memory Management is used, then the SGA Advisor, along with the PGA Advisor is displayed.
If Manual Shared Memory Management is used, then the Buffer Cache Advisor, along with the PGA Advisor is displayed

I'm trying to perform a task that I used to do in DB Control.  Where is it in EM Express?

EM Express is not a direct replacement for DB Control.  EM Express is built inside the database server and there are limitations as to what it can do. EM Express is also focused on providing performance management and monitoring functionality, and is not meant to be an all around database administration tool.

Oracle Database 12c also introduces Oracle Enterprise Manager Cloud Control (Cloud Control). Cloud Control provides you with complete monitoring across the Oracle technology stack and non-Oracle components. You must install Cloud Control separately after installing Oracle Database 12c , No additional license required .

Enterprise Manager Cloud Control supports Oracle Database 12c targets, including multitenant container databases (CDBs), pluggable databases (PDBs), non-CDBs, Oracle Real Application Clusters (Oracle RAC) databases, and Oracle Automatic Storage Management (Oracle ASM) databases.

Note:
Enterprise Manager Database Control is no longer available in Oracle Database 12c. You can use Enterprise Manager Cloud Control 12c or Enterprise Manager Database Express 12c to manage your Oracle Database 12c databases.

Why do I see a "Warning" icon on the Memory Page?

The warning icon is displayed if the memory configuration is different across the instances in your RAC cluster.  For example, this may be displayed if one instance uses Automatic Memory Management, while the other instance uses Manual Shared Memory Management.

In the Memory Page, why are the sizes in the Configuration region different from the Allocation region?

The configuration section shows the parameters that have been specified by the user, while the allocation section shows the amount of memory that has been allocated.  For example, if the system is using sga_target, the user can still specify minimum sizes for the different pools, and these sizes will be displayed in the Configuration section.  However, the allocation section will show what the system has allocated for the pools.

For an example, click here.  shared_pool_size=512M in the Configuration section, yet the Allocation breakdown shows 1G has been allocated.

In the Memory Page, why does sga_target in the Configuration region show a different value from the Current Setting in the SGA Advisor region?

This can happen on RAC.  The Configuration region shows the aggregate across all instances in the RAC target.  However, when showing the SGA Advisor data, if the sga_target is the same on all instances, it shows the advisor data based on the sga_target setting of the instances, with the estimated percentage of time taking into account the time saved over all instances.

For an example of a 2-node RAC environment with sga_target=2000M on each node, click here.  The page shows sga_target=3.9G, but the Current Setting in SGA Advisor shows 2G.

I'm on a RAC system, and the Home Page shows "Host CPU".  Which host is it showing?

This shows cluster CPU utilization.  If you have a symmetric cluster, this is the average of CPU utilization across all the nodes.  If you have an asymmetric cluster, then this shows the utilization over the entire cluster, e.g. sum of CPU used / sum of available CPU.

Similarly, the other charts in the Resources region, such as Active Sessions and Memory, also show aggregated values over the entire cluster.  For an example, click here.

To see the details per instance, click on the RAC link in the Status region, from the live UI.

Why is the Storage menu missing the Tablespaces entry in a CDB?

Tablespaces are local to a PDB, so the Tablespaces entry is not available at the root container.  To view and manage tablespaces or datafiles on a PDB, go to the EM Express configured on that PDB.

Can I manage and create Common Users with EM Express?

Yes, Common Users can be created and managed from the root container.
At the PDB container, you can only create and manage local users.

Why is the whole Performance tab missing?

This Might Be Database Standard Edition which does not support Performance Feature/Option ,
You can double check by :
SQL> select * from v$version;

To interpret the output, please check: How To Find Which Oracle Database Edition Is Currently Running? (Doc ID 417573.1)
Please check  Feature Availability for Oracle Database Editions Documentation Link for

Why are tabs missing from the Performance Hub?

At the PDB container, only the Activity and Monitored SQL tabs are visible because these are the only tabs that display PDB-specific data.
At the root container, all tabs are available in the Performance Hub.

Can I see the activity breakdown by PDB?

Yes, at the root container, you can see the Average Active Sessions breakdown by container using the Home Page and the Performance Hub (Summary tab or Activity tab).
This data is taken from ASH, not metrics, so you might see a discrepancy when comparing average active sessions per container against a different breakdown.

Why do I see PDB$SEED when viewing Activity breakdown by PDB?

Although PDB$SEED is just a seed database, when queries are issued against CDB_* views, the server internally switches to all containers, including PDB$SEED, to retrieve data.  If there are frequent queries against CDB_* views, this could result in activity captured against PDB$SEED.

Can I plug and unplug PDBs with EM Express?

No, EM Express does not provide full CDB management capabilities in DB 12.1.0.1.0.

How can I tell if I'm connected to the root container or to a PDB?

You can get this information from the Status region in the home page.  If you are at the root container, the type will show "CDB (# of PDBs)", and if you are at the PDB, the region will include the Container Name to which you're connected. In addition, the blue panel at the top of the page will have an icon on the top left that indicates if the database is a CDB.  The database name will be displayed after the icon:

CDB


PDB


How do I log into EM Express?

EM Express can be accessed thorugh the URL to https://<hostname>:<port>/em
A login page will be displayed, and the user can log in with any valid DB credential or use the following query while connected to CDB or PDB to generate the URL :
For HTTPS :
SQL> SELECT 'https://'||SYS_CONTEXT('USERENV','SERVER_HOST')||'.'||SYS_CONTEXT('USERENV','DB_DOMAIN')||':'||dbms_xdb_config.gethttpsport()||'/em/' from dual;
SQL> SELECT 'https://'||SYS_CONTEXT('USERENV','SERVER_HOST')||':'||dbms_xdb_config.gethttpsport()||'/em/' from dual;

For HTTP :
SQL> SELECT 'http://'||SYS_CONTEXT('USERENV','SERVER_HOST')||'.'||SYS_CONTEXT('USERENV','DB_DOMAIN')||':'||dbms_xdb_config.gethttpport()||'/em/' from dual;
SQL> SELECT 'http://'||SYS_CONTEXT('USERENV','SERVER_HOST')||':'||dbms_xdb_config.gethttpport()||'/em/' from dual;

How can I find the port on which EM Express is configured?

There are two ways to find the HTTP/HTTPS port for EM Express:

lsnrctl status | grep HTTP


(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=xxx.us.oracle.com)(PORT=5500))(Security=(my_wallet_directory=/u01/oracle/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW)) --> this is For HTTPS

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.us.oracle.com)(PORT=8080))(Presentation=HTTP)(Session=RAW)) --> this is For HTTP


or from SQL*Plus:

SQL> select dbms_xdb_config.gethttpsport() from dual;
SQL> select dbms_xdb_config.gethttpport() from dual;

How can I give users read-only access to EM Express?

To grant users read-only access so that they can view the UI but not make any changes, grant them the EM_EXPRESS_BASIC role as follows: SQL> grant EM_EXPRESS_BASIC to <user>;  

Does EM Express have support for RAC?

Yes.  EM Express is a database management tool that is automatically RAC-aware.  When connected to a RAC system, the information displayed is for the entire database, i.e. for all instances.  For example, the average active session values is aggregated across all instances.  In addition, an extra Instances tab is displayed in the Performance Region on the DB Home Page to show the distribution of average active sessions across instances.

When using EM Express on RAC, how can I see instance-specific information?

Although EM Express is RAC-aware in general, some pages will allow you to view instance-specific information as well.  There are two ways to get to this instance-specific information.

The first way is using the Status Region in the Home Page.  You can click the "RAC - <n> instance(s) up" link and it will take you to a page with the instance details.  You can now select an instance and navigate to the Performance Hub, the Instance Home Page, or the Memory Page for the selected instance.

The second way is through the Performance Hub.  If you click the RAC tab, you will see a list of instances.  From this list, you can now select an instance and navigate to the Performance Hub, the Instance Home Page, or the Memory Page for the selected instance.

For Undo Details, the information is always instance-specific.  When you navigate to the Undo Management page using the Storage menu, you are taken to a page that lists all the instances and the undo summary information for each instance.  You can now select an instance and navigate to the Undo Details Page for the selected instance.

EM Express seems slow when using HTTPS.  Why?

Some browsers disable caching if you are using SSL and have a self-signed certificate.  This is known to occur on Chrome and Safari.  To avoid this issue, use a CA certificate when using HTTPS or use a browser that supports caching when using self-signed certificates (Firefox or IE).

What is the recommended screen resolution for EM Express?

Although the EM Express UI dynamically resizes based on the screen real-estate available, it works best with resolutions of at least 1280x1024 or 1600x900.

Can I run EM Express in Safari?

Yes, you can run EM Express in Safari on MacOS.  You can also run EM Express in Safari 5.0.* on Windows, but Safari 5.1.* on Windows is known to have an issue with input text fields that prevents users from entering their username and password on the login page

How can I replace the default wallet/certificate?

The XDB wallet is stored in the $ORACLE_BASE/admin/<db_name>/xdb_wallet directory .  If ORACLE_BASE is not defined, the XDB wallet is stored in $ORACLE_HOME/admin/<db_name>/xdb_wallet, where <db_name> is the unique database name.

What should I do if the default XDB wallet expires?

If the wallet expires, you can create a new wallet with an existing SSL certificate and replace the existing wallet with the new one.  Alternatively, you can use dbms_xdb.installDefaultWallet() to replace the existing wallet with a new Oracle wallet that has a self-signed certificate.

Why am I getting a "This Connection is untrusted" message when I try to log into EM Express?  Is this a security concern?

The browser displays this message if you are using a self-signed certificate.  You can either create an exception for it, or you can replace the default wallet/certificate with a trusted certificate.  See the documentation for more information.  

Is EM Express secure?

Yes, EM Express uses SSL (HTTPS) out of the box.

Do I need any database privileges to use EM Express?

In order to use EM Express, a database user needs to have been granted the EM_EXPRESS_BASIC or EM_EXPRESS_ALL role.  The DBA role includes both the EM_EXPRESS_BASIC and the EM_EXPRESS_ALL roles.

EM_EXPRESS_BASIC grants a user read-only privileges, so that the user can view pages but not perform any actions.  EM_EXPRESS_ALL grants a user all privileges required to perform any action in EM Express.

Do I need licenses or packs to use EM Express?

You can use the basic administration features offered by EM Express with no additional licenses.  However, to use the performance features, you will need the Oracle Diagnostics Pack.  For the Performance Hub, you will need the Diagnostics Pack and for the SQL Monitor and SQL Tuning Advisor features, you will need the Tuning Pack.

Depending on what packs you own, you should set the control_management_pack_access init.ora parameter on your system.  For example, if you have the Diagnostics Pack, but not the Tuning Pack, you should set the control_management_pack_access parameter=DIAGNOSTIC.

Is EM Express supported on Oracle Database Standard Edition?  What about Database XE?

Yes, EM Express is supported on both Standard Edition (SE) and Express Edition (XE).  However, features that require the Diagnostics and Tuning Packs will not be available on SE and XE.  For example, the Performance Menu won't be available on SE and XE because the features require the Diagnostics or Tuning Packs.  Similarly, certain regions of the Home Page, such as the SQL Monitor List, will not be shown on SE and XE because they require the Tuning Pack.

How to configure EM Express on a CDB/PDB?

Users can configure EM Express both at the root and the PDB containers, with each container using a different HTTP/HTTPS port.  When connected to the root container, the information displayed is for the entire database including all PDBs.  When connected to a PDB, the information displayed is restricted to data for the PDB  Simply login to the container and set the HTTPS/HTTP port. 

By default, DBCA picks a free port from the 5500 to 5599 range to use as the EM Express port.

If you want a particular port to be used as the EM Express port, specify that port using the DBEXPRESS_HTTPS_PORT operating system environment variable prior to starting OUI or DBCA.

To start EM Express, use the EM Express URL provided by DBCA when DBCA configured your database, there are two ways to find the HTTP/HTTPS port for EM Express:  

lsnrctl status | grep HTTP

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=xxx.us.oracle.com)(PORT=5500))(Security=(my_wallet_directory=/u01/oracle/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW)) --> this is For HTTPS

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.us.oracle.com)(PORT=8080))(Presentation=HTTP)(Session=RAW)) --> this is For HTTP

or from SQL*Plus:
SQL> select dbms_xdb_config.gethttpsport() from dual;
SQL> select dbms_xdb_config.gethttpport() from dual;


or use the following query while connected to CDB or PDB to generate the URL :
For HTTPS:
SQL> SELECT 'https://'||SYS_CONTEXT('USERENV','SERVER_HOST')||'.'||SYS_CONTEXT('USERENV','DB_DOMAIN')||':'||dbms_xdb_config.gethttpsport()||'/em/' from dual;
SQL> SELECT 'https://'||SYS_CONTEXT('USERENV','SERVER_HOST')||':'||dbms_xdb_config.gethttpsport()||'/em/' from dual;

For HTTP :
SQL> SELECT 'http://'||SYS_CONTEXT('USERENV','SERVER_HOST')||'.'||SYS_CONTEXT('USERENV','DB_DOMAIN')||':'||dbms_xdb_config.gethttpport()||'/em/' from dual;
SQL> SELECT 'http://'||SYS_CONTEXT('USERENV','SERVER_HOST')||':'||dbms_xdb_config.gethttpport()||'/em/' from dual;


When EM Express prompts you for your username and password, log in as a user with DBA privilege (such as SYS).
Note: The first time you enter the URL for EM Express in your web browser, your browser may display warning messages.

EM Express is a servlet built on top of Oracle XML DB. The Oracle XML DB default wallet has a self-signed certificate, and some existing browsers consider self-signed certificates as untrusted because they are not signed by a trusted CA (certificate authority). However, the self-signed certificate is still secure, as it ensures that the traffic is encrypted between the Oracle XML DB server and the client (browser).

Therefore, enter a security exception for the EM Express URL in your web browser.

To manually configure the HTTP/HTTPS port for EM Express:
1- If the listener is running on a nonstandard port (for example, not 1521), then the init.ora file for the database you want to manage using EM Express must contain a local_listener entry so that the HTTP/HTTPS port can register with the correct listener. The local_listener entry references a TNSNAMES entry that points to the correct listener. For example:
    local_listener=inst1

where inst1 is a TNSNAMES entry defined in tnsnames.ora that points to the listener. For example:
    inst1= (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)(PORT=1234))
    (CONNECT_DATA=(SERVICE_NAME=service_name)(SERVER=DEDICATED)))

2- Enable the TCP dispatcher by adding the following entry to the init.ora file for the database you want to manage using EM Express:
    dispatchers="(PROTOCOL=TCP)(SERVICE=<sid>XDB)"

    For example, if the database SID is ORCL, then the entry would be:
    dispatchers="(PROTOCOL=TCP)(SERVICE=ORCLXDB)"

3- Use the PL/SQL procedure DBMS_XDB_CONFIG.SETHTTPSPORT to set the HTTPS port for EM Express. This will update the HTTPS port in the xdbconfig.xml file in the Oracle XML DB Repository. You must connect as SYS / AS SYSDBA to run the procedure. For example:

SQL> connect sys/<password>@<container> as sysdba
SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500);
or use the PL/SQL procedure DBMS_XDB_CONFIG.SETHTTPPORT to set the HTTP port for EM Express. This will update the HTTPS port in the xdbconfig.xml file in the Oracle XML DB Repository. You must connect as SYS / AS SYSDBA to run the procedure. For example:

SQL> connect sys/<password>@<container> as sysdba
SQL> exec DBMS_XDB_CONFIG.SETHTTPPORT(2200);

Sunday Sep 14, 2014

Introducing Oracle Support Engineer Initiated Chat for Existing Service Requests

My Oracle Support now offers a chat feature that enables Oracle Support engineers to contact you instantly online to discuss an open Service Request (SR). Oracle Support engineers may use this feature on an open SR to provide a status update, ask clarifying questions, share detailed commands and troubleshooting information, or confirm your issue is resolved. This feature is available to all My Oracle Support Customer Portal users and is another step towards leveraging preferred social technologies in our Support solutions.

Attend the webcast September 23, 2014 and September 24, 2014 and learn more at

Thursday Aug 07, 2014

Master Note for ORA-1555 Errors

ORA-1555 Errors

Concepts/Definitions

The ORA-1555 errors can happen when a query is unable to access enough undo to build
a copy of the data at the time the query started. Committed "versions" of blocks are
maintained along with newer uncommitted "versions" of those blocks so that queries can
access data as it existed in the database at the time of the query. These are referred to as
"consistent read" blocks and are maintained using Oracle undo management.

See Document 40689.1 - ORA-1555 "Snapshot too old" - Detailed Explanation for more about
these errors.

Diagnosing

Due to space limitations, it is not always feasible to keep undo blocks on hand for the life of the instance. Oracle Automatic Undo Management (AUM) helps to manage the time frame that undo blocks are stored. The time frame is the "retention" time for those blocks.

There are several ways to investigate the ORA-1555 error. In most cases, the error is a legitimate problem with getting to an undo block that has been overwritten due to the undo "retention" period having passed.

AUM will automatically tune up and down the "retention period, but often space limitations or configuration of the undo tablespace will throttle back continuous increases to the "retention" period.

The error message is reported in the user session and often is not captured in the alert log. The user could see a message like

Using rollback segment functionality:
ORA-1555: snapshot too old (rollback segment too small)

or

Using AUM:
ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$" too small

If the error is captured in the alert.log, you would see something like

Tue May 26 16:16:57 2009
ORA-01555 caused by SQL statement below (SQL ID: 54yn3n36w24ft, Query Duration=922 sec, SCN: 0x0007.8a55f4e3)

Initial Investigation

Rollback Segments:

With Oracle 10g and later versions of Oracle, you can still use a Rollback Segments configuration. ORA-1555 errors in that environment still follow older guidelines as described in

Document 10579.1 - How many Rollback Segments to Have
Document 107085.1 - Tuning Rollback Segments
Document 69464.1 - Rollback Segment Configuration & Tips

Automatic Undo Management:

The database will be self tuning for undo when using Automatic Undo Management. This does not eliminate ORA-1555 completely, but does minimize ORA-1555 as long as there is adequate space in the undo tablespace and workloads tend to follow repeatable patterns. In some cases with periodic changes to workload (large data updates particularly with LOB data) the self tuning of undo can become aggressive and lead to undo issues.

Document 461480.1 - FAQ Automatic Undo Management (AUM) / System Managed Undo (SMU)
Document 135053.1 - How to Create a Database with Automatic Undo Management
Document 268870.1 - How to Shrink the datafile of Undo Tablespace
Document 231776.1 - How to switch a Database from Automatic Undo Management (AUM) back to using Rollback Segments
Document 396863.1 - How to Keep All UNDO Segments from Being Offlined in Oracle 10g - Fast Ramp-Up

LOB Issues:

Out-of-row LOB undo is maintained in the LOB segment. So the UNDO tablespace and undo retention is not associated with most LOB ORA-1555 issues. Instead the LOB column is created using either PCT_VERSION or RETENTION to manage how much space within blocks or time transpires before the LOB undo is overwritten. In environments with high updates, deletes on rows including LOBs, the chances of ORA-1555 on LOB undo is very high.

PCT_VERSION and RETENTION are not auto-tuned. To "tune" those configuration settings, you must change the values for PCT_VERSION or RETENTION. Changes to UNDO_RETENTION does not change LOB retention time frames.

Document 162345.1 - LOBS - Storage, Read-consistency and Rollback
Document 386341.1 - How to determine the actual size of the LOB segments and how to free the deleted/unused space above/below the HWM
Document 563470.1 'Lob retention not changing when undo_retention is changed
Document 422826.1 How to identify LOB Segment Use PCTVERSION or RETENTION from Data Dictionary

Error Tracing

Undo error tracing can be done for normal undo operations using the following events:

NOTE: Normal undo operations will be indicated in the error message in that the error message includes a segment name like

'¦. name "_SYSSMU1$" too small

If the error doesn't show a segment name

'¦ name "" too small

the problem is often related to LOB undo
If using pfile:

event="10442 trace name context forever, level 10"

If using spfile:

Alter system set events '10442 trace name context forever, level 10';

Reproduce the ORA-1555 error and upload the trace file to Oracle Support.

LOB undo error tracing is more difficult. Set additional tracing events as follows:

Start Session 1
Alter session set events '10046 trace name context forever, level 12';
Reproduce the error
Exit Session 1

Start Session 2
Alter session set events '10051 trace name context forever, level 1';
Reproduce the error
Exit Session 2

Start Session
Alter session set events '1555 trace name errorstack forever, level 3';
Reproduce the error
Exit Session 3

Additional resources to review:

Document 846079.1 - LOBs and ORA-1555 troubleshooting
Document 253131.1 - Concurrent Writes May Corrupt LOB Segment When Using Auto Segment Space Management
Document 467872.1 - TROUBLESHOOTING GUIDE (TSG) - ORA-1555

V$UNDOSTAT Analysis

The V$UNDOSTAT view holds undo statistics for 10 minute intervals. This view
represents statistics across instances, thus each begin time, end time, and
statistics value will be a unique interval per instance.

This does not track undo related to LOB

Document 262066.1 - How To Size UNDO Tablespace For Automatic Undo Management
Document 1112363.1 - When Does Undo Used Space Become Available?
Document 240746.1 - 10g NEW FEATURE on AUTOMATIC UNDO RETENTION

Diagnostics Scripts

Refer to Document 746173.1 : Common Diagnostic Scripts for AUM problems
and Document 877613.1 : AUM Common Analysis/Diagnostic Scripts

Common Causes/Solutions

Using Rollback Segments functionality:

* Problem happening on SYSTEM tablespace that still uses old Rollback Segment functionality even when configured for Automatic Undo Management (AUM).

* There are not enough rollback segments to manage the undo needed for long running queries.

* Rollback Segments are too small and undo is overwritten before long running queries complete.

Reference:

Document 69464.1 - Rollback Segment Configuration & Tips
Document 10630.1 - ORA-1555: 'Snapshot too old' - Overview
Document 862469.1 - ORA-604 & ORA-1555 Rollback Segment 0 with Name "System" Too Small

Using Automatic Undo Management (AUM):

* TUNED_UNDORETENTION in V$UNDOSTAT around the time of the error is lower than the QUERY DURATION indicated in the error message. This is a legitimate ORA-1555 and if queries are going to run for very long time frames, UNDO_RETENTION may need to be larger. Auto-tuned retention may not be able to keep up with the undo workload and staying within space limitations on the UNDO tablespace.

* LOB updates and/or deletes are frequent and a higher PCT_VERSION is required to provide enough space in the LOB Segment to accommodate the LOB undo. RETENTION on LOBs that are updated or deleted frequently can run into problems holding UNDO long enough for queries.

* QUERY DURATION shown in the error message is 30+ years and therefore, no amount of undo will satisfy the consistent read blocks.

Document 750195.1 - ORA-1555 Shows Unrealistic Query Duration (billions of seconds)

* QUERY DURATION shown in the error message is 0. NOTE: This has been filed as a bug on many release levels and has been very difficult to narrow down to a specific problem.

Document 761128.1 - ORA-1555 Error when Query Duration as 0 Seconds

* QUERY DURATION is lower than TUNED_UNDRETENTION. Undo header information can sometimes get overwritten or you could be seeing a bug.

* TUNED_UNDORETENTION stays very high and UNDO tablepsace continues to grow continuously or getting space errors.

Document 1112431.1 - Undo Remains Unexpired When Using Non-autoextensible Datafiles for Undo Tablespace.


* How to find the complete SQL statement caused ORA-1555 :

        If the Database was not restarted after the error ORA-1555 , so the Statement can be obtained from :

        select SQL_TEXT from SQL_TEXT where SQL_ID='<sql id from the error message>';

        If the Database was restarted after the error ORA-1555 and an AWR snapshot was gathered before the restart , so the Statement can be obtained from :

        select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID='<sql id from the error message>';

Additional Reference:

Database Administration Community

Wednesday Mar 19, 2014

Master Note: Troubleshooting Oracle Scheduler

To BottomTo Bottom



Troubleshooting Steps

Concepts

Oracle provides a collection of functions and procedures in the DBMS_SCHEDULER package to help simplify management tasks, as well as providing a rich set of functionality for complex scheduling needs.  Collectively, these functions are called the Scheduler, and they are callable from any PL/SQL program.
Prior to Oracle release 10g similar limited functionality was available with DBMS_JOB package.
The Scheduler lets database administrators and application developers control when and where various tasks take place in an enterprise environment. For example, database administrators can schedule and monitor database maintenance jobs such as backups or nightly data warehousing loads and extracts.

Starting from 12c :

The coordinator looks at the root database and all the child PDBs and selects jobs based on the job priority, the job scheduled start time, and the availability of resources to run the job. The latter criterion depends on the consumer group of the job and the resource plan currently in effect. The coordinator makes no attempt to be fair to every PDB. The only way to ensure that jobs from a PDB are not starved is to allocate enough resources to it.

Known Issues Involving Oracle Scheduler

Problems with the Scheduler can be caused by such things as versions, incorrect setups and/or incorrect usage. The steps that follow will help to clarify where the problem might be and provides information that can be collected to help in diagnosing the issue. Please note that for Oracle Jobs, it is important to distinguish between the usage of the conventional Job queue (DBMS_JOB) and the usage of the Scheduler (DBMS_SCHEDULER). DBMS_JOB is mainly used in Oracle version 9i and lower and has been replaced by the Scheduler, which was introduced in Oracle version 10G. This document will focus mainly on Scheduler (DBMS_SCHEDULER) related issues.
Note: It is still possible to use DBMS_JOB in Oracle version 10.

For a thorough list of known issues with the Oracle Scheduler, please refer to:
NOTE 1311355.1 Known Issues And Bugs Using the Scheduler

Diagnosing Scheduler Issues

Firstly, to identify the affected job the following can be used:
<Note 744645.1> How to find the job name if a scheduled job fails with ORA-12012

Building a Testcase

In the area of the Scheduler, a testcase can often help in solving the issue or finding a solution because there are so many ways to build a job structure. Unexpected behavior or errors may be located in the user commands executed by the job and jobs can execute different types of commands, like PLSQL-programs, procedures, external shell scripts and more. Depending on the problem it is useful to test the successful execution of these commands outside the jobs first to confirm if the problem is in fact located in the job usage. Problems occurring after changes in a job setup or with a newly created job setup often benefits showing the behavior using a small testcase where the issue can be reproduced. A guideline for setting up a simple testcase is provided in the following:
Note 807159.1 Sample Code and Select Statements to Build A simple Testcase for Scheduler / Jobs

According to the situation, combine the SQL commands to build as simple a job structure as possible with only the objects that are needed. Replacing large and complex job setups and minimizing the job structure to only the relevant parts of the problem is important to developing a simple testcase where the behavior / problem can be shown in an easy way and can actually help in finding any possible workarounds.

Generic Tracing

When the problem in the job structure cannot be found in an easy manner it is often recommended to activate sql tracing by setting the event 10046. To produce a valuable result, set timed_statistics to true and max_dump_file_size to unlimited before executing the job.
For example:

SQL> alter session set max_dump_file_size=unlimited;
SQL> alter session set timed_statistics = true;
SQL> alter session set events '10046 trace name context forever, level 4';
SQL> BEGIN DBMS_SCHEDULER.run_job (job_name => 'runtest_job',
                                   use_current_session => TRUE);
end;
/

Please note that by setting the event in a parameter file, each process would have produced a trace. In this case, optimal tracing is set at the session level, where the problem can be reproduced and then tracing disabled. Ensure that only matching traces are uploaded.
Details on how to diagnose the 10046 trace can be found in Note 376442.1 and Note 39817.1

For the Scheduler, additional tracing has been introduced with setting the attribute logging_level for the jobs and using an event to dump further diagnostics for the job coordinator and the job slaves:

Limited Availability Note 461304.1 How to activate tracing for the scheduler[This section is not visible to customers.]

Automatic Scheduling Issues

Common problems with Scheduler are that jobs do not start at the expected time or won't start automatically.
As a first step, confirm that the job runs successfully with an explicit run (dbms_job.run or dbms_scheduler.run_job). This will exclude a configuration problem of the job itself. Once an explicit execution of the affected job is successful, then look at diagnosing the automatic scheduling process:

JOB_QUEUE_PROCESSES

Until Oracle Database version 11.1 , the setting of the parameter JOB_QUEUE_PROCESSES is not mandatory for the Scheduler. The Job Coordinator starts job slaves on demand, to work on the job queue table. This means setting JOB_QUEUE_PROCESSES to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were unaffected and would still run.
Starting with Oracle Database 11g Release 2 (11.2), setting JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and DBMS_JOB jobs not to run. This has been implemented to provide a clean method of stopping all jobs.

Window Issues

Note 742683.1 Scheduled Job That Works Does Not Start In Window

Purging Diagnostics

Note 443364.1 How To Purge DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_WINDOW_LOG
Note 749440.1 Dbms_scheduler.Purge Not Removing Entries from dba_scheduler_job_run_details  

Schedule Issues

Note 312547.1 Advanced use of DBMS_JOB.SUBMIT 'interval' Parameter
Note 428872.1 Scheduled Jobs Do Not Run After A Re-Start With Repeat_interval => Null.

Issues with Statistic Jobs (10g)

Note 430636.1 GATHER_STATS_JOB Is Not Running
Note 377143.1 How to check what automatic statistics collection is scheduled on 10g
Note 803191.1 Checklist To Diagnose Issues Related To Automatic Run Of Job GATHER_STATS_JOB 

Issues with 11g Autotasks

Note 865933.1 DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS Procedure Requires SYSDBA Privilege

Helpful Articles / Examples for Scheduler

Note 807159.1 Sample Code and Select Statements to Build A simple Testcase for Scheduler / Jobs
Note 727499.1 Sample how to create a job running chains
Note 1300444.1  How to create a event based chained job
Note 404238.1 How to Disable an Entry from DBMS_SCHEDULER
Note 873691.1 How to Submit a DBMS_SCHEDULER Job to Run Starting with the Nth Day of a Month
Note 461432.1 How To Exclude A Certain Window In A Schedule For A Day Using DBMS_SCHEDULER Package
Note 270256.1 How to create a Job using new 10g Scheduling feature.
Note 1431664.1 - How to build up an event based job - an example
Note 1074141.1 - How to install and setup the Scheduler Agent (schagent) so can run Remote External Jobs

Further Diagnostics

If you were not able to resolve the issue with the details provided in this or referenced documents, please raise a Service Request for further assistance from Oracle Support. Please provide any relevant tracing and/or a testcase as well as the following information:
Check the columns STATE, ENABLED, NEXT_RUN_DATE from DBA_SCHEDULER_JOBS.
Collect the information from DBA_SCHEDULER_JOB_LOG

set ver off
set term off
set page 0
set markup html on spool on
spool scheduler.html

--The proceding lines needs to be un commented when executing aganist 12c Database for a CDB (pluggable instance )
--alter session set container='<pdp name>'
--/

alter session set NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SS AM TZR'
/
alter session set NLS_DATE_FORMAT='DD-MON-RR HH.MI.SS AM'
/
select WINDOW_NAME,WINDOW_PRIORITY,ENABLED,RESOURCE_PLAN,NEXT_START_DATE,
DURATION from DBA_SCHEDULER_WINDOWS
/
select LOG_DATE,WINDOW_NAME,REQ_START_DATE,ACTUAL_START_DATE,WINDOW_DURATION,
ACTUAL_DURATION,ADDITIONAL_INFO from DBA_SCHEDULER_WINDOW_DETAILS
where rownum<20
order by LOG_DATE
/
select LOG_DATE,STATUS,WINDOW_NAME, OPERATION from DBA_SCHEDULER_WINDOW_LOG
order by LOG_DATE desc
/
select WINDOW_GROUP_NAME, ENABLED, NEXT_START_DATE from DBA_SCHEDULER_WINDOW_GROUPS
/
select LOG_DATE, OWNER,JOB_NAME, STATUS,ERROR#,REQ_START_DATE,ACTUAL_START_DATE,
ADDITIONAL_INFO from DBA_SCHEDULER_JOB_RUN_DETAILS
where JOB_NAME in ('')
order by LOG_DATE
/
show parameter job_queue
/
spool off
set markup html off spool off

Tuesday Feb 04, 2014

Master Note for Database Startup/Shutdown (Doc ID 1270450.1)

Concepts

During Startup, the instance is started first (nomount stage) which then mounts the database by accessing the control files specified in the CONTROL_FILES parameter. The next step will be to open the database which includes opening the datafiles and redo log files. Applying the redo (rollforward) and the rollback of uncommitted data (rollback) also happens if the previous shutdown was not a clean one (shutdown normal or immediate).

Similarly the database and instance undergoes multiple phases during shutdown. First the database is closed ie, the datafiles and the redolog files are closed. Then the database will be dismounted  to disassociate it from the instance. After a database is unmounted, Oracle Database closes the control files of the database. The next step will be to shutdown the instance by removing the SGA from the memory and terminating the background processes.

There are various shutdown modes such are normal, immediate,transactional and abort. When a database is closed as part of a SHUTDOWN with any option other than ABORT, Oracle Database writes data in the SGA to the data files and online redo log files. If a SHUTDOWN ABORT or abnormal termination occurs, then the instance of an open database closes and shuts down the database instantaneously. Oracle Database does not write data in the buffers of the SGA to the data files and redo log files. The subsequent reopening of the database requires instance recovery, which Oracle Database performs automatically.

Database startup and shutdown are powerful administrative options that are restricted to users with administrator privileges.

For details you can refer Note 1505155.1 - Master Note: Overview of Database Startup and Shutdown

Common Issues During Database Startup

This Section list out the common issues encountered during startup. The error can be reported in any of the above mentioned phases like nomount,mount or open. Since transaction recovery continues even after opening the database, you may observe issues like hang, high CPU usage etc, even after the database is open. The documents mentioned in the below section can be specific to platform or database versions.

Startup upgrade/migrate or setting _system_trig_enabled = FALSE will disable startup triggers to rule out startup trigger problems.

12c Startup/Shutdown

Startup and shutdown of the container database is the same as it has always been for regular instances. 

For PDBs

Also Refer to the following documents for more information on 12c Startup and Shutdown: 
  • Note 1582453.1 When to use startup / shutdown database and alter database command in 12c

ORA-27102 Errors

ORA-27102 errors normally occurs due to memory issues.The Common causes could be due to Semaphore Kernel misconfigurations,Memory related Ulimit settings,RAM or swap configurations.


ORA-27300 Errors

These errors are generally reported when the Operating System called for error or when there was a connection killed or a network interconnection failures or an OS configuration issue.The error ORA-27300 will also be accompanied by ora-27301 and ora-27302

Note 579365.1 Troubleshooting ORA-27300 ORA-27301 ORA-27302 errors

ORA-64 Errors

This error could occur when the database init.ora parameter calling for more resources than the Operating System is configured to provide.The parameters could be PROCESSES,DB_BLOCK_SIZE,SGA and more.

Note 1457812.1 ORA-00064 Error Reported After Increasing Processes Parameter value


ORA-27123 Errors

Note 115753.1 UNIX: Resolving the ORA-27123 error

ORA-1081 Errors

This error could occur when we try to startup an instance that is already running or if the shared segments/semaphores already exist.

Note 1010214.6 ORA-1081: Starting Instance
Note 18657.1 OERR: ORA 1081 cannot start already-running Oracle - shut it down first

NFS Related Issues

Note 8418190.8 Bug 8418190 - Direct NFS warnings during database startup
Note 236794.1 NFS Locking Problems Encountered During Database Startup
Note 971406.1 DATABASE STARTUP HANGS AT MOUNTING CONTROLFILE WHEN DNFS IS ENABLED
Note 1430654.1 Database Startup Failed with "Direct NFS: please check that oradism is setuid"
Note 430920.1 NetApp: Using 'nolock' NFS Mount Option with non-RAC Systems Results in Database Corruption

Transaction Recovery Slowness

There could be slowness in the database during the open phase when the database is busy performing transaction recovery.

Note 1494886.1 Database Transaction Recovery
Note 414242.1 Database Hangs Because SMON Is Taking 100% CPU Doing Transaction Recovery
Note 12934890.8 Bug 12934890 - Startup hangs waiting for row cache lock due to open transaction against UNDO$

ORA-704 Errors

This is a general error reported at startup when there is some problem during processing of bootstrap information.There should be an accompanying error/s. Plug the ORA=704 and the other errors it reports into the Oracle Support search engine for a solution.

ORA-9968 Errors

There are some client shadow processes hanging. Although the lk< SID> file is deleted the hanging processes still have a lock on the open file handle. This prevents the database to startup although a new lk<SID> file can be created successfully. An oracle process (background or shadow process) that exists while the instance is not started (crashed or not cleanly stopped) can have a lock on a file while this file is actually removed from the system. This is because on UNIX there is still a lock on the open file handle.

Note 160395.1 Database Startup Fails with ORA-1102 and ORA-9968
Note 1488147.1 Instance Startup Raises Error ORA-09968: unable to lock file (Doc ID 1488147.1)

ORA-12547 Errors

The error ORA-12547 indicates that the communication channel has been broken. It's most often thrown because the other end of the process went away unexpectedly.

Note 1307075.1 Oracle Database Fails to Start with Error ORA-12547
Note 381566.1 connect / as sysdba Fails with Ora-12547 And Tns-12514
Note 744512.1 Ora-12547: Tns:Lost Contact Creating Database After Clean Installation

ORA-1157 Errors 

The background process was not able to find one of the datafiles.The database will prohibit access to this file but other files will be unaffected.However, the first instance to open the database will need to access all online datafiles.Accompanying messages from the operating system will describe why the file was not found.

Note 184327.1 ORA-1157 Troubleshooting

Common Errors / Issues During Database Shutdown

The most common issue observed while bringing down the database is shutdown immediate hang. The main reasons for Shutdown immediate hang is:
- processes still continue to be connected to the database and do not terminate.
- SMON is cleaning temp segments or performing delayed block cleanouts.
- Uncommitted transactions are being rolled back.

The below section provides the consolidated list of known issues during shutdown. The documents mentioned in the below section can be specific to platform or database versions.

Transaction Recovery

Note 375935.1 What To Do and Not To Do When 'shutdown immediate' Hangs
Note 1076161.6 Shutdown Normal or Shutdown Immediate Hangs. SMON disabling TX Recovery
Note 414242.1 Database Hangs Because SMON Is Taking 100% CPU Doing Transaction Recovery
Note 100054.1 Transaction Rollback after a failed operation or during Database Shutdown

ORA-24324 Errors

Note 794293.1 ORA-24324 During Shutdown

DISM

Note 1001248.1 On Solaris 9 Systems, Oracle Shutdown May Hang If Utilizing Dynamic Initmate Shared Memory (DISM) 

Issues specific to automatic shutdown and startup

This section is specific to the automatic shutdown and startup that can be configured with the dbora / dbshut / dbstart scripts.

Automatic Startup Failure

The key to diagnosing automatic startup failures is to determine where startup fails. This can be done via the following steps:

  1. Determine if instance starts manually as Oracle software owner.
  2. Determine if instance starts via dbstart command run as Oracle software owner.
  3. Determine if instance starts when root runs following dbstart command:
    su - $ORA_OWNER -c $ORA_HOME/bin/dbstart

    where $ORA_OWNER is set to Oracle software owner.
  4. Determine if instance starts when running as root the OS script which calls dbstart, ie "/etc/init.d/dbora start". NOTE: Running via sh -x command will show each command as it is run from script to better see what is going on.
  1. #> sh -x /etc/init.d/dbora start

Automatic Shutdown Failure

As with automatic startup, the key to diagnosing automatic shutdown failures is to determine where shutdown fails. This can be done via following steps:

  1. Determine if instance stops manually as Oracle software owner.
  2. Determine if instance stops via dbshut command run as Oracle software owner.
  3. Determine if instance stops when root runs command
    su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
    where $ORA_OWNER is set to Oracle software owner.
  4. Determine if instance stops when running as root the OS script which calls dbshut, ie "/etc/init.d/dbora stop". NOTE: Running via sh -x command will show each command as it is run from script to better see what is going on.
    #> sh -x /etc/init.d/dbora stop

Further Diagnostics

If you were not able to resolve the issue with the details provided in this document, please raise a Service Request for further assistance from Oracle Support. The diagnostic information required to troubleshoot the issue is listed in the below document:

Troubleshooting Database Startup/Shutdown Problems (Doc ID 851057.1).

References

NOTE:1505155.1 - Master Note: Overview of Database Startup and Shutdown

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

Monday Oct 07, 2013

Master Note For Database and Client Certification

For most current information refer:

Master" Note For Database and Client Certification [ID 1298096.1]

In this Document
  Purpose
  Scope and Application
  Master Note For Database and Client Certification
     Main Note
     General
     Using My Oracle Support Effectively


Applies to:

Information in this document applies to any platform. 

Purpose

This Master Note is intended to provide an index and references to the most frequently used My Oracle Support notes with respect to Oracle Certify and Oracle Database Server/Client.

Scope and Application

This note is applicable to all levels of expertise.

Master Note For Database and Client Certification

Main Note

Note 1295603.1 Locate Database Server Certification Information on My Oracle Support

General

Note 763087.5 Tips for Finding Certifications in Classic Certify

Note 161818.1 Oracle Database (RDBMS) Releases Support Status Summary

Note 207303.1 Client / Server / Interoperability Support Between Different Oracle Versions

Note 77627.1 Oracle Database Server product support Matrix for Windows 2000

Note 1062972.1 Locate Oracle Database Server Certification Information for Microsoft Windows Platforms

Note 824935.1 Where To Find Oracle Patchset/Software for Specific Server/ Hardware Model?

Note 942852.1 Oracle VM and VMWare Certification for Oracle Products

Note 750215.1 Which OS File Systems Are Certified For Single Instance Oracle Databases?

Note 403202.1 Certification of Zeta File System (Zfs) On Solaris 10 for Oracle RDBMS

Note 1075717.1 Installing 32-bit RDBMS Client software on x86_64 Linux.

Note 870253.1 32-bit Client Install on 64-bit Windows Platform

Note 753601.1 SUPPORT FOR ORACLE DATABASE ON WPARS UNDER AIX 6.1

Note 1194734.1 Where do I find that on My Oracle Support (MOS) [Video]

Note 971464.1 FAQ - 11gR2 requires Solaris 10 update 6 or greater

 

Using My Oracle Support Effectively

Note 374370.1 New Customers Start Here

Note 868955.1 My Oracle Support Health Checks Catalog

Note 166650.1 Working Effectively With Global Customer Support

Note 199389.1 Escalating Service Requests with Oracle Support Services


 

Thursday Jul 14, 2011

Master Note for Diagnosibility

Master Note for Diagnosibility (Oracle Database)

This Master Note is intended to provide a summary, index and references on topics around diagnosibility.
This  includes:
 - What is diagnosibility ? 
 - What's new with the 11g diagnosibility concept?
 - How to gather diagnostic data for different releases? 
 - How to upload diagnostic files to Oracle support? 
 - How to verify uploads? 
 - Known Problems

 This Master Note is subdivided into categories to allow for easy access and reference to notes that are applicable to your area of interest, within diagnosibility.  

What is Diagnosibilty?

Actually diagnosibility has different meanings:
Fault diagnosibility captures diagnostic data, such as dump files or core dump files, on the OCCI client when a problem occurs. 

In pre 11g databases diagnosibilty based on single files like alert.log and process tracefiles.

With 11g 'Diagnosibility' was introduced as a feature that simplifies collecting the diagnostic information and sending this infornation to support. Diagnosibilty covers the whole process from data collection, package creation,  package upload to ORACLE and package investigation at ORACLE support. The overall view is called Diagnosibilty Framework (DFW)
RDBMS diagnostic data has been reorganized and are stored in a common directory structure named ADR (Automatic Directory Repository)  ADR contains different diagnostic files of xml format in a database-like structure.

Getting started

<<Note 1292665.1>> - Oracle Database Support Newsletter - February, 2011

Packaging

1. Package types

Packages can be categorized as following:

Description base type DB version  file format upload target
standard OS package

Y

all

tar, gz, zip

GTCR
RDA (Remote Diagnostic Agent)

Y

all

zip

ADR Repository
IPS (11g Incident Packaging Service)

Y

11.1 
and higher

zip

ADR Repository
IPS + RDA (bundle)

N

11.2.0.2
and higher

zip

ADR Repository
OCM (Oracle Configuration Manager)

Y

8.1.7
and higher

ocmconfig.jar

Configuration repository
OCM + RDA (bundle)

N

8.1.7
and higher

zip

Configuration Repository &
ADR Repository

 

2. Incident Packaging Services (IPS)  

There are 2 options to create IPS packages:

2.1 Commandline Interface (ADRCI)

For IPS package creation in general and use of command line utility (ADRCI) refer to:

<<Note 443529.1>> - 11g Quick Steps to Package and Send Critical Error Diagnostic Information to Support [Video]

2.2. GUI (SWB)

For use of Support Workbench - the graphical user interface refer to:

<<Note 1091653.1>> - 11g Quick Steps - How to create an IPS package using Support Workbench [Video]

Upload Diagnostic Data

Diagnostic files and packages can be uploaded to ORACLE using My ORACLE Support (MOS) the graphical customer user interface. This requires a Service Request (SR). The upload should not be interrupted or stopped. Once completed you will receive a completion message and SR becomes automatically updated with different upload status details. These messages can be used by support or customers to check upload status.

Verify Uploads in MOS

In order to examine upload messages, open your SR in MOS and verify following settings:

set [Filter By] to <-- Show all Entries -->
uncheck [Hide System Messages] checkbox

Package Processing - Step 1 (virus scan)

Each incoming package or file will be processed by virus scan first. After completion you will see a message like:

Virus Scan successfully completed for RDA.RDA_dwhprod.zip.
If you see this message, this will indicate that your file was successfully uploaded and received by ORACLE.

Package Processing - Step 2 (package specific actions)

After Virus Scan package and file processing depends on package type. Different types have different processing. The following will summarize processing of base package types. Package bundles will be splited into base components and processed accordingly.

a) Standalone RDA packages will be moved and extracted to Global ADR Repository. Completion tag:

Auto generated message by RDA FUP :RDA.RDA_BBDD1.zip has been uploaded to the ADR Repository.


b) Standalone IPS packages will be moved to Global ADR Repository, extracted and processed by ADRCI. Completion tag:

Diagnostic file ORA600_20100526101903_COM_1.zip was successfully processed at Oracle by 11g Diagnostics


c) Configurations will be moved to Configuration Repository. Completion tag:

 Auto generated message by RDA-OCM FUP :ocmconfig.jar has been uploaded to Configuration repository.


d) Anything else - different from RDA, IPS or configurations - goes to Global Testcase Repository (GTCR)

Health Monitor

<<Note 466920.1>> - 11g New Feature: Health monitor
<<Note 951022.1 >> - How To Purge or Delete Health Monitor Checks After DBMS_HM.RUN_CHECK ?

Diagnostic Internals

<<Note 422893.1>> - 11g Understanding Automatic Diagnostic Repository
<<Note 750982.1>> - TRM Trace Files Getting Generated in 11g Database

FAQs

<<Note: Note 453125.1>> - 11g Diagnosability Frequently Asked Questions


Troubleshooting 

ADR - Purging and Retension

<<Note 564269.1>> - Retention Policy for ADR
<<Note 751082.1>> - Adrci Purge Does Not Clear the Text-formatted Alert.Log located in the Trace Folder
<<Note 975448.1>> - WHICH FILES ARE PART OF SHORTP_POLICY AND LONGP_POLICY IN ADR?


Tips and Tricks

1. How to create an incident for testing?
Attention: Don't use this in production systems !

<<Note 390293.1>> - Introduction to 600/7445 Internal Error Analysis


Diagnostic Tools

<<Note 559339.1>> - Diagnostic Tools Catalog
<<Note 153788.1>>  - ORA-600/ORA-7445 Error Look-up Tool





Sunday Jul 03, 2011

Master Note for Partitioning [ID 1312352.1]

++++++++++++++++++++++++++++++++++++++++++++++++++++
The complete and the most recent version of this article can be viewed
from My Oracle Support Knowledge Section.

Master Note for Partitioning [ID 1312352.1]
++++++++++++++++++++++++++++++++++++++++++++++++++++

In this Document
Purpose
Scope and Application
Master Note for Partitioning
Concepts/Definitions Section
How-To Section
Troubleshooting Section
Known issues
Additional Resources

References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Database Products > Oracle Database > Data Warehousing
Information in this document applies to any platform.

Purpose

This article is intended to give you a single reference point and to assist in quickly finding various information about partitioning.

Scope and Application

The document will cover the following topics:

Concepts/Definitions
How-To Section
Troubleshooting Section
Known issues
Additional Resources

Master Note for Partitioning

Concepts/Definitions Section

Overview of Partitioning
Partitioning enables you to decompose very large tables and indexes into smaller and more manageable pieces called partitions. Each partition is an independent object with its own name and optionally its own storage characteristics.

Partitioning offers these advantages:
  • Increased availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.
  • Easier administration of schema objects reducing the impact of scheduled downtime for maintenance operations.
  • Reduced contention for shared resources in OLTP systems
  • Enhanced query performance: Often the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partition pruning) can provide order-of-magnitude gains in performance.

Relevant Links

Overview of Partitioning:

Oracle® Database Concepts 11g Release 2 (11.2) Part Number E16508-05 Chapter 4
Oracle® Database VLDB and Partitioning Guide 11g Release 2 (11.2) Part Number E16541-08

Relevant articles regarding partitions creation and maintenance:

Document   69715.1 Creating & Adding Table and Index Partitions
Document 165303.1  Examples about Insert into Range Partitioned Tables
Document 164874.1 Example of Script to Create a Range Partition Table
Document 166652.1 Example of Script to Maintain Range Partitioned Table

Document 149116.1 Oracle9i Partitioning Enhancements, LIST Partitioning
Document 209368.1 Range List Partitioning - Oracle 9.2 Enhancement

Document 276158.1 Partitioning Enhancements in Oracle 10g

Relevant articles regarding latest features:

Document 452447.1 11g Partitioning Enhancements
Document 785462.1 11g New Features:System Partitioning
Document 805976.1 11g New Features:INTERVAL PARTITIONING
Document 943567.1 11g new feature: Extended Composite Partitioning (Overview, Example and Use)
Document 761251.1 Oracle 11G Reference Partitioning examples
Document 466352.1 11g Feature: Interval Partitioning Example
Document 757754.1 Interval Partitioning By Week

Relevant articles regarding partition pruning:

Document 179518.1 Partition Pruning and Joins
Document 166118.1 Partition Pruning/Elimination (This article provide a very detailed example of how to identify which partitions/subpartitions were accessed during an execution of a statement using event 10128)

How-To Section

How to Partition a Non-partitioned Table

Document 1070693.6 How to Partition a Non-partitioned Table
Document 472449.1   How To Partition Existing Table Using DBMS_Redefinition

'How to' relevant articles for partitioning types

Document 854332.1 How To Introduce Interval Parititioning into a Range Partitioned Table
Document 165701.1 How to Implement Hash Partitioning on IOT Tables in 9i & Above
Document 1266993.1 How To Use Multicolumn Partitioning Keys

Document 74181.1 Partitioning Tables with User-Defined Types and LOBs

Document 1304370.1 How to use Partition Names for ref partitioning tables when Split is used
Document 854332.1 How To Introduce Interval Partitioning into a Range Partitioned Table

Document 846405.1 How To Change the Partition Column Of A Partitioned Table Using DBMS_Redefinition

'How to' relevant articles for partitioned indexes:

Document 69374.1 Partitioned Indexes: Global, Local, Prefixed and Non-Prefixed
Document 74224.1 How to Create Primary Key Partitioned Indexes 
Document 795854.1 How To Update Both Global and Local Indexes when Moving Table Partition?

'How to' relevant articles for statistics collection when partitions are used:

Document 175258.1 How to Compute Statistics on Partitioned Tables and Indexes
Document 237538.1 How to Move from ANALYZE to DBMS_STATS on Partitioned Tables - Some Examples
Document 111990.1 ORA-14508: ANALYZE PARTITION TABLE VALIDATE STRUCTURE CASCADE
Document 1050294.1 STALE_STATS OF SUB PARTITION INDEX REPORTED INCORRECTLY IN DBA_TAB_STATISTICS
Document 1302628.1 Collect statistics for a large partitioned table takes a lot when incremental is used
Document 1319225.1 Collect incremental statistics for a large partitioned table in versions 10.2 and 11

Troubleshooting Section

Relevant bulletin articles for partitioning related issues:

Document 209070.1 Partition Pruning based on Joins to Partitioning Criteria Stored in Dimension Tables

Document 378138.1 What to check when the fast split partitioning does not appear as working?
Document 232628.1 Fast Split partitioning in 9iR2


Relevant articles for troubleshooting import slow issues with partitioning

Document 1073195.1 Data Pump Import (Impdp) slow when importing partitioned table 
Document 752904.1 DataPump Export of Partitioned Table is Very Slow and Apparently Hangs 
Document 1224663.1 IMPDP Raises ORA-39001, ORA-39203 When Importing A Partition Of A Partitioned Table Over A Network Link


Others
Document 1281826.1 What Types of Partitioning Are Eligible for Partition Change Tracking (PCT) Fast Refresh?

Known issues


Document 165599.1 Top Partitioned Tables Issues
Document 199623.1 Top Issues Encountered Regarding Split Partition
Document 166215.1 Top Partition Performance Issues
Document 372357.1 Fast Split Partitioning Not Ocurring When It Was Expected
Document 272312.1 How to Recreate a Table Partition After Having Dropped the Datafile?
Document 959116.1 Interval Partitioning Does Not Inherit Logging
Document 198120.1 Exchange Partitions - Common Problems

Document 1077819.1 PARTITIONED TABLE: CHANGE IN BUFFER_POOL FOR A PARTITION NEEDS A REBOUNCE

Relevant article to handle various errors:

Document 887659.1 Getting ORA-01410 for Partitioned Tables Without Any Apparent DDL On The Partitions.
Document 1081230.1 INTERVAL RANGE Partition Giving ORA-14400
Document 389804.1 Range Partition Splitting Fails with ORA-01882
Document 405922.1 ORA-07445 [evaopn2] Query With Bit Mapped Indexes And Partitioning
Document 1084542.1 Ora-600 Internal Error Code, Arguments: [Kkedsamp: Bad Partitioning Info.], [1290], [663],
Document 1050966.1 ORA-14652 When Using Reference Partitioning
Document 784989.1 ORA-600 [kdblGetRDBA] During Create Table with Compression and Partitioning
Document 790630.1 ORA-600 [Kkpamdgspam1] When Joining Partitioned Tables at Different Partitioning Levels
Document 727306.1 Ora-14074 When Trying To Add Partition

Dictionary issues introduced with partitioning

Document 1289275.1 OBJ$-PARTOBJ$-TABPART$ mismatch - Dictionary Inconsistency reported for Interval Range Partition Tables

Relevant article to handle size for partitioning

Document 729149.1 Table/Index (partition) Growth Is Far More Than Expected

Install partitioning option


Document 434743.1 Can The Partitioning Option Be Deinstalled When System Partitioned Objects Are Used
Document 430239.1 How To De-install Partitioning Option From Enterprise Edition?

Additional Resources

Community: Database DataWarehousing

This community has been set up for My Oracle Support (MOS) users, and is being moderated by Oracle. The goal of this community is to exchange database related Data Warehousing knowledge and concepts including Partitioning.

White Papers

Partitioning with Oracle Database 11g Release 2 (September 2009)
Partitioning in Oracle Database 11g (June 2007) 

Partitioning White papers are also accessible via Note 1329441.1

References

NOTE:1329441.1 - White Papers for Data Warehousing Components in the Oracle Database

Monday May 16, 2011

Troubleshooting ORA-1555 (Doc ID 1307334.1)

This article is intended to assist in finding tips and techniques to assist with finding solutions to ORA-1555 errors. The document will cover the following topics:

Concepts/Definitions
Diagnosing
Common Causes/Solutions

Concepts/Definitions

The ORA-1555 errors can happen when a query is unable to access enough undo to build
a copy of the data at the time the query started. Committed “versions” of blocks are
maintained along with newer uncommitted “versions” of those blocks so that queries can
access data as it existed in the database at the time of the query. These are referred to as
“consistent read” blocks and are maintained using Oracle undo management.

See Note 40689.1 - ORA-1555 "Snapshot too old" - Detailed Explanation for more about
these errors.

Diagnosing

Due to space limitations, it is not always feasible to keep undo blocks on hand for the life of the instance. Oracle Automatic Undo Management (AUM) helps to manage the time frame that undo blocks are stored. The time frame is the “retention” time for those blocks.

There are several ways to investigate the ORA-1555 error. In most cases, the error is a legitimate problem with getting to an undo block that has been overwritten due to the undo “retention” period having passed.

AUM will automatically tune up and down the “retention” period, but often space limitations or configuration of the undo tablespace will throttle back continuous increases to the “retention” period.

The error message is reported in the user session and often is not captured in the alert log. The user could see a message like

Using rollback segment functionality:
ORA-1555: snapshot too old (rollback segment too small)

or

Using AUM:
ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$" too small

If the error is captured in the alert.log, you would see something like

Tue May 26 16:16:57 2009
ORA-01555 caused by SQL statement below (SQL ID: 54yn3n36w24ft, Query Duration=922 sec, SCN: 0x0007.8a55f4e3)

Initial Investigation

Rollback Segments:
With Oracle 10g and later versions of Oracle, you can still use a Rollback Segments configuration. ORA-1555 errors in that environment still follow older guidelines as described in

Note 10579.1 - How many Rollback Segments to Have
Note 107085.1 - Tuning Rollback Segments
Note 69464.1 - Rollback Segment Configuration & Tips
Automatic Undo Management:
The database will be self tuning for undo when using Automatic Undo Management. This does not eliminate ORA-1555 completely, but does minimize ORA-1555 as long as there is adequate space in the undo tablespace and workloads tend to follow repeatable patterns. In some cases with periodic changes to workload (large data updates particularly with LOB data) the self tuning of undo can become aggressive and lead to undo issues.

Note 461480.1 - FAQ Automatic Undo Management (AUM) / System Managed Undo (SMU)
Note 135053.1 -How to Create a Database with Automatic Undo Management
Note 268870.1 - How to Shrink the datafile of Undo Tablespace
Note 231776.1 - How to switch a Database from Automatic Undo Management (AUM) back to using Rollback Segments
Note 296863.1 - How to Keep All UNDO Segments from Being Offlined in Oracle 10g - Fast Ramp-Up
LOB Issues:
Out-of-row LOB undo is maintained in the LOB segment. So the UNDO tablespace and undo retention is not associated with most LOB ORA-1555 issues. Instead the LOB column is created using either PCT_VERSION or RETENTION to manage how much space within blocks or time transpires before the LOB undo is overwritten. In environments with high updates, deletes on rows including LOBs, the chances of ORA-1555 on LOB undo is very high.

PCT_VERSION and RETENTION are not auto-tuned. To “tune” those configuration settings, you must change the values for PCT_VERSION or RETENTION. Changes to UNDO_RETENTION does not change LOB retention time frames.

Note 162345.1 - LOBS - Storage, Read-consistency and Rollback
Note 386341.1 - How to determine the actual size of the LOB segments and how to free the deleted/unused space above/below the HWM
Note 563470.1 – Lob retention not changing when undo_retention is changed
Note 422826.1 – How to identify LOB Segment Use PCTVERSION or RETENTION from Data Dictionary

Error Tracing

Undo error tracing can be done for normal undo operations using the following events:

NOTE: Normal undo operations will be indicated in the error message in that the error message includes a segment name like

…. name "_SYSSMU1$" too small

If the error doesn’t show a segment name

… name "" too small

the problem is often related to LOB undo
If using pfile:

event="10442 trace name context forever, level 10"

If using spfile:

Alter system set events '10442 trace name context forever, level 10';

Reproduce the ORA-1555 error and upload the trace file to Oracle Support.

LOB undo error tracing is more difficult. Set additional tracing events as follows:

Start Session 1
Alter session set events '10046 trace name context forever, level 12';
Reproduce the error
Exit Session 1

Start Session 2
Alter session set events '10051 trace name context forever, level 1';
Reproduce the error
Exit Session 2

Start Session
Alter session set events '1555 trace name errorstack forever, level 3';
Reproduce the error
Exit Session 3

Additional resources to review:
Note 846079.1 – LOBs and ORA-1555 troubleshooting
Note 253131.1 –Concurrent Writes May Corrupt LOB Segment When Using Auto Segment Space Management
Note 467872.1 – TROUBLESHOOTING GUIDE (TSG) – ORA-1555

V$UNDOSTAT Analysis

The V$UNDOSTAT view holds undo statistics for 10 minute intervals. This view
represents statistics across instances, thus each begin time, end time, and
statistics value will be a unique interval per instance.

This does not track undo related to LOB
Note 262066.1 – How To Size UNDO Tablespace For Automatic Undo Management
Note 1112363.1 – When Does Undo Used Space Become Available?
Note 240746.1 – 10g NEW FEATURE on AUTOMATIC UNDO RETENTION

Diagnostics Scripts

Refer to Note 746173.1 : Common Diagnostic Scripts for AUM problems
and Note 877613.1 : AUM Common Analysis/Diagnostic Scripts

Common Causes/Solutions

Using Rollback Segments functionality:

* Problem happening on SYSTEM tablespace that still uses old Rollback Segment functionality even when configured for Automatic Undo Management (AUM).

* There are not enough rollback segments to manage the undo needed for long running queries.

* Rollback Segments are too small and undo is overwritten before long running queries complete.
Reference:
Note 69464.1 – Rollback Segment Configuration & Tips
Note 10630.1 – ORA-1555: “Snapshot too old” – Overview
Note 862469.1 – ORA-604 & ORA-1555 Rollback Segment 0 with Name “System” Too Small

Using Automatic Undo Management (AUM):

* TUNED_UNDORETENTION in V$UNDOSTAT around the time of the error is lower than the QUERY DURATION indicated in the error message. This is a legitimate ORA-1555 and if queries are going to run for very long time frames, UNDO_RETENTION may need to be larger. Auto-tuned retention may not be able to keep up with the undo workload and staying within space limitations on the UNDO tablespace.

* LOB updates and/or deletes are frequent and a higher PCT_VERSION is required to provide enough space in the LOB Segment to accommodate the LOB undo. RETENTION on LOBs that are updated or deleted frequently can run into problems holding UNDO long enough for queries.

* QUERY DURATION shown in the error message is 30+ years and therefore, no amount of undo will satisfy the consistent read blocks.

Note 750195.1 – ORA-1555 Shows Unrealistic Query Duration (billions of seconds)

* QUERY DURATION shown in the error message is 0. NOTE: This has been filed as a bug on many release levels and has been very difficult to narrow down to a specific problem.

Note 761128.1 – ORA-1555 Error when Query Duration as 0 Seconds

* QUERY DURATION is lower than TUNED_UNDRETENTION. Undo header information can sometimes get overwritten or you could be seeing a bug.

* TUNED_UNDORETENTION stays very high and UNDO tablepsace continues to grow continuously or getting space errors.

Note 1112431.1 – Undo Remains Unexpired When Using Non-autoextensible Datafiles for Undo Tablespace.

Additional Reference:

Database Administration Community

Tuesday May 10, 2011

Oracle Database Machine and Exadata Storage Server Information Center

Direct link to: Oracle Database Machine and Exadata Storage Server Information Center (Doc ID 1306791.1)

Purpose: The purpose of this bulletin is to have a consolidated site for Database Machine and Exadata Storage Server, to cover support, training and proactive information for customers. Scope and Application: This document is intended for Database Machine and Exadata Storage Server customers. It provides time critical information and highlights related to Database Machine and Exadata Storage Server support and services.

Wednesday Feb 23, 2011

Master Note For Database and Client Certification

For most current information refer:

Master" Note For Database and Client Certification [ID 1298096.1]

In this Document
  Purpose
  Scope and Application
  Master Note For Database and Client Certification
     Main Note
     General
     Using My Oracle Support Effectively


Applies to:

Information in this document applies to any platform.

Purpose

This Master Note is intended to provide an index and references to the most frequently used My Oracle Support notes with respect to Oracle Certify and Oracle Database Server/Client.

Scope and Application

This note is applicable to all levels of expertise.

Master Note For Database and Client Certification

Main Note

Note 1295603.1 Locate Database Server Certification Information on My Oracle Support

General

Note 763087.5 Tips for Finding Certifications in Classic Certify

Note 161818.1 Oracle Database (RDBMS) Releases Support Status Summary

Note 207303.1 Client / Server / Interoperability Support Between Different Oracle Versions

Note 77627.1 Oracle Database Server product support Matrix for Windows 2000

Note 1062972.1 Locate Oracle Database Server Certification Information for Microsoft Windows Platforms

Note 824935.1 Where To Find Oracle Patchset/Software for Specific Server/ Hardware Model?

Note 942852.1 Oracle VM and VMWare Certification for Oracle Products

Note 750215.1 Which OS File Systems Are Certified For Single Instance Oracle Databases?

Note 403202.1 Certification of Zeta File System (Zfs) On Solaris 10 for Oracle RDBMS

Note 1075717.1 Installing 32-bit RDBMS Client software on x86_64 Linux.

Note 870253.1 32-bit Client Install on 64-bit Windows Platform

Note 753601.1 SUPPORT FOR ORACLE DATABASE ON WPARS UNDER AIX 6.1

Note 1194734.1 Where do I find that on My Oracle Support (MOS) [Video]

Note 971464.1 FAQ - 11gR2 requires Solaris 10 update 6 or greater

 

Using My Oracle Support Effectively

Note 374370.1 New Customers Start Here

Note 868955.1 My Oracle Support Health Checks Catalog

Note 166650.1 Working Effectively With Global Customer Support

Note 199389.1 Escalating Service Requests with Oracle Support Services


 

About

News and Troubleshooting tips for Oracle Database and Enterprise Manager

Search

Categories
Archives
« May 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