Wednesday Mar 19, 2014

Master Note: Troubleshooting Oracle Scheduler

In this Document

Purpose
Troubleshooting Steps
  Concepts
  Known Issues Involving Oracle Scheduler
  Diagnosing Scheduler Issues
  Building a Testcase
  Generic Tracing
  Automatic Scheduling Issues
  JOB_QUEUE_PROCESSES
  Window Issues
  Purging Diagnostics
  Schedule Issues
  Issues with Statistic Jobs (10g)
  Issues with 11g Autotasks
  Helpful Articles / Examples for Scheduler
  Further Diagnostics
References

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

Purpose

This document is intended to assist Database Administrators resolve issues encountered using the Oracle Scheduler (DBMS_SCHEDULER) system.

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

@ Common:
@ Unpublished Note 459189.1 How to stop all dbms_scheduler jobs[This section is not visible to customers.]

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

References

NOTE:461304.1 - How to Activate Tracing for the Scheduler[This section is not visible to customers.]

NOTE:1311355.1 - Known Issues And Bugs Using the Scheduler
NOTE:1485539.1 - Master Note: Overview of Oracle Scheduler
NOTE:823091.1 - DataPump Export Scheduled Job With Many Chains, Rules and Programs

Tuesday Feb 04, 2014

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

Purpose
Scope
Details
  Concepts
  Common Issues During Database Startup
  Startup Slow / Hang
  ORA-27102 Errors
  ORA-27300 Errors
  ORA-64 Errors
  ORA-27123 Errors
 
  ORA-1081 Errors
  NFS Related Issues
 
  ORA-600 /ORA-7445 Errors 
 
  Transaction Recovery Slowness
  ORA-704 Errors
  ORA-9968 Errors
  ORA-12547 Errors
  ORA-27125 Errors
  ORA-1157 Errors 
  Other Issues
  Common Errors / Issues During Database Shutdown
  Shutdown Slow / Hang
  ORA-1031 Errors
  Transaction Recovery
  ORA-600 / ORA-7445 Errors
  ORA-24324 Errors
  DISM
  Memory Related Errors
  Other Issues
  Issues specific to automatic shutdown and startup
  Automatic Startup Failure
  Automatic Shutdown Failure
  Further Diagnostics
References

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

Purpose

 This article is intended to provide details on Database Startup and Shutdown along with the various issues that can be encountered during this activity. Most of the known issues in this topic is consolidated here.

Scope

 This document is intended to assist Database Administrators to understand the concept and resolve issues encountered during shutdown and startup.

Details

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.

 

Startup Slow / Hang

Startup can hang in any of the stages like nomount,mount or open stage. Following are some of the known issues reported so far:

 

Note 1367724.1 Startup Hangs at Mount Stage after AIO is Enabled on Linux
Note 429390.1 Database Startup Takes Longer time After Upgrade To 10.x
Note 552019.1 New Install and Creation of a 10gR2 database Hangs at Startup Nomount
Note 344933.1 DB Startup Can Hang if USE_INDIRECT_DATA_BUFFERS=TRUE and AWE_WINDOW_MEMORY Is Set Too High
Note 838451.1 Startup Hangs When "Processes" Parameter is Higher Than 10800
Note 1076092.1 Instance Startup Hangs After Creating New Undo Tablespace and Switching Between Old and New
Note 1476526.1 Database STARTUP NOMOUNT very slow after migrate to T4-4 Servers SPARC 64-Bit
Note 1475621.1 Oracle Database Startup And SGA Value Change Takes A Very Long Time To Complete

 

ORA-27102 Errors

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

 

Note 274092.1 LOCK_SGA on Windows fails with ORA-27102
Note 219752.1 ORA-27102 OSD-00034 Starting Database on Windows 2000
Note 390547.1 ORA-27102 Cannot Startup Instance via sqlplus
Note 1060677.6 ORA-27100 ORA-27102 Trying to Start 8.0.4 Database
Note 399895.1 Database Startup On Solaris 10 Fails With Ora-27102 Out Of Memory Error
Note 467707.1 ORA-27102: Out Of Memory on Oracle 10g Solaris 10 (x86-64)
Note 7272646.8 Bug 7272646 - Linux-x86_64: ORA-27103 on startup when MEMORY_TARGET > 3g
Note 790205.1 UNABLE TO START INSTANCE WITH LARGE SGA ORA-27102 SVR4 ERROR: 22: INVALID ARGUMENT
Note 263537.1 ORA-27102 out of memory When Trying To Start Database With SGA> 4G
Note 842881.1 ORA-27102, OSD-00031 Unable To Extend Memory_max_target And Memory_target Past 2GB
Note 1449714.1 Startup Fails With ORA-27102 After Upgrade From 10.2.0.3 To 10.2.0.4
Note 461519.1 ORA-27102 Database Will Not Start With SHMMAX Set To 8589934592 (8GB)
Note 351930.1 Receiving ORA-27102 For 32-Bit Oracle While Allocating Sga_max_size Greater Than 4gb
Note 1351705.1 Startup Fails with ORA-27102 and 'SVR4 Error 28: No space left on device'
Note 1292225.1 ORA-27102 OSD-00025 O/S-Error: (OS 1453) When Lock_sga is Set to True
Note 401077.1 Ora-27102: Out Of Memory: Linux Error: 12: Cannot Allocate Memory with LOCK_SGA=TRUE
Note 577898.1 ORA-27102 Received At Startup When LOCK_SGA Is Set Although Enough Memory Is Available
Note 301830.1 Upon startup of Linux database get ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device
Note 859898.1 The ORA-27102 error is generated on Solaris 10 having apparently correct settings of kernel parameters
Note 779861.1 ORA-27102: Out Of Memory And SVR4 Error: 22: Invalid argument During Startup On Solaris10 Server With Multiple DB Instances

 

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 560309.1 Database Cannot Start Due to Lack of Memory
Note 579365.1 Troubleshooting ORA-27300 ORA-27301 ORA-27302 errors
Note 314179.1 Instance Startup Fails With Error ORA-27154,ORA-27300,ORA-27301,ORA-27302
Note 812115.1 Startup Fails With ORA-27300: Os System Dependent Operation:Fork Failed With Status:17
Note 814896.1 Startup Fails With ORA-27300: OS system dependent operation:IPC init failed with status: 65
Note 949468.1 Database Startup Fails with ORA-27300: OS system dependent operation:semget failed with status: 28

 

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 470742.1 ORA-00064 Starting Database With Dispatchers
Note 556258.1 ORA-64 if Db_keep_cache_size is Set to 70 Gigs
Note 283980.1 ORA-00064: object is too large to allocate on this O/S
Note 1232463.1 ORA-00064: Object Is Too Large To Allocate On This O/S
Note 4466378.8 Bug 4466378 - ORA-64 does not report the caller description
Note 179301.1 Instance Startup Fails With ORA-00064 After Increasing Processes
Note 1457812.1 ORA-00064 Error Reported After Increasing Processes Parameter value
Note 18255.1 OERR: ORA 64 object is too large to allocate on this O/S <num, num>
Note 310838.1 Instance Startup failed with ORA-00064 when processes parameter set to High Value
Note 886312.1 Database startup can fail with ORA-00064 Errors with huge sga_target of over 40Gig
Note 1328620.1 ASM Instance Is Not Coming Up ORA-00064 (1,4468736,Kfchl Array) Kfchl Array
Note 815954.1 ORA-64 when starting ASM instance after changing db_cache_size and shared_pool_size
Note 7659217.8 Bug 7659217 - ORA-64 attempting to startup with a large SGA / buffer cache size
Note 386855.1 'startup migrate' failed with ORA-64 while upgrading to 10.2.0.2 with DBUA

 

ORA-27123 Errors

Note 167250.1 ORA-27123 When Connecting As Non Oracle User
Note 207743.1 Getting ORA-27123 when trying to startup Oracle
Note 115753.1 UNIX: Resolving the ORA-27123 error
Note 307323.1 Ora-27123 When Creating New Database
Note 167250.1 ORA-27123 When Connecting As Non Oracle User
Note 207743.1 Getting ORA-27123 when trying to startup Oracle
Note 61912.1 OERR: ORA-27123 unable to attach to shared memory segment
Note 250966.1 ORA-27123 During Startup, Immediately after a Shutdown
Note 872532.1 ORA-27123 on RHEL5 (PAE) 32bit when SGA larger than 2Gb
Note 552633.1 Starting the Database with SGA_TARGET set Fails with ORA-27123
Note 437582.1 Export Fails With EXP-00056 ORA-01034 ORA-27123 EXP-00005
Note 733974.1 ORA-27123 During Startup Nomount in 11G on AIX, Failure in SHMAT()
Note 369262.1 Startup with Maximum SGA Fails With Ora-27123 Unable To Attach Sga
Note 735187.1 Cannot Create Database With DBCA - Startup Nomount Gives ORA-27123
Note 1268668.1 Oracle Database 10g R2 Version 10.2.0.3 - Receiving Ora-27123 Errors
Note 390766.1 ora-27123 on Solaris 10 with larger than 1.6 - 1.7Gb SGA
Note 384262.1 ORA-01034, ORA-27123, HP-UX Error 22 Connecting Via Oracle Net
Note 149070.1 VMS: Connections fail with ORA-1034 and ORA-27123 errors
Note 453930.1 Connecting to the database fails with ORA-12547, Ora-600 [Ksmlsge1], ORA-27123, Error 13
Note 207797.1 ORA-1034 ORA-27123 SVR4 Error: 13: Permission denied when other then Oracle user
Note 356957.1 OpenVMS: Client Connections Report ORA-1034, ORA-27123, %SYSTEM-W-REGISFULL
Note 1401726.1 ORA-27123 When Starting Instance With No Setting Of SGA_TARGET Or SGA_MAX_SIZE
Note 730107.1 Getting ORA-27123 & not able to run Oracle when logging to server by a user other than that installed Oracle although it belongs to the same group

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

ORA-600 /ORA-7445 Errors 

Note 435436.1 ORA-00600: [kccpb_sanity_check_2] During Instance Startup
Note 101589.1 Startup database returns ORA-00600 [ktpridestroy2]
Note 405602.1 ORA-600 [16305] While Starting Up the Database
Note 466596.1 Core Dumps In skgfqio() - Database Startup Hangs/Spins
Note 336447.1 Startup Database Produces ORA-00600: [Keltnfy-Ldminit]
Note 847786.1 Can Not Open Database After Shutdown get ORA-7445 [kewa_dump_time_diff]
Note 779071.1 Unable to Start Instance Due to ORA-600 [skgmhash] after a clean shutdown
Note 549000.1 ORA-600 [6006] ORA-600 [6856] During Startup Instance, Followed by Termination by SMON
Note 453775.1 Database Startup Fails With ORA-7445 [INVALID PERMISSIONS FOR MAPPED OBJECT] After Creation of User LBACSYS

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.

Note 18494.1 OERR: ORA 704 "bootstrap process failure"
Note 560417.1 Recovery Through Upgrade returns ORA-1092 on Open
Note 1349722.1 Ora-00704,Ora-39700: Database Must Be Opened With Upgrade Option
Note 435337.1 Unable To Open Database Before/After Upgrade - ORA-00704 ORA-39700 ORA-01092
Note 1383179.1 Unavailable Bootstrap Object ACCESS$ Causes ORA-704 ORA-604 ORA-942 When Opening Database
Note 1345417.1 After failed upgrade, startup from a restored backup fails on ORA-00704 and ORA-39700

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 467251.1 ORA-09968, ORA-01102 When Starting a Database
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-27125 Errors

Note 1067569.6 HP-UX: ORA-27125: NOT OWNER TRYING TO LOCK THE SGA IN MEMORY
Note 199068.1 OpenVMS: Instance startup fails with ORA-27125 and %SYSTEM-F-VA_IN_USE
Note 121983.1 Starting Database Fails on Solaris with ORA-27126 or ORA-27125 Using LOCK_SGA
Note 577428.1 OpenVMS: Following an Oracle RELINK, Database Instance Startup fails with ORA-27125 or ORA-7217

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
Note 1035992.1 Oracle Troubleshooting
Note 212053.1 ORA-1157/ORA-1110 Trying To Open The Database
Note 145194.1 ORA-1157 ORA-1110 ORA-27086 Starting up Database
Note 444151.1 ORA-01157 on Database Startup After Dropping an Alias
Note 301635.1 ORA-01157, ORA-01110, ORA-27046 Starting A Restored Database
Note 429912.1 ORA-01157 ORA-01110 ORA-27086 after crash prevents database from opening
Note 256835.1 Database Startup Fails With ORA-1110, ORA-1157, ORA-27092 Trying Startup From 'at' or 'cron' on HP-UX

Other Issues

Note 1113864.1 MBIND: Cannot Allocate Memory On Startup
Note 578536.1 MBIND: CANNOT ALLOCATE MEMORY ON STARTUP
Note 6795133.8 Bug 6795133 - Startup delayed by QMNC queries
Note 301072.1 Dbstart Fails With Ora-01031 When Called From User Root
Note 1286665.1 ORA-00371: Not Enough Shared Pool Memory signalled on Startup
Note 779356.1 Database not starting up with errors ORA-01092 ORA-24324 ORA-01041
Note 1176443.1 ORA-4031 During Startup Nomount using RMAN without parameter file (PFILE)
Note 839789.1 ORA-12853 / ORA-4031 or ORA-4030 on Instance Startup With increased SGA size

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.

Shutdown Slow / Hang

Note 1197314.1 Shutdown Normal Hung On ORA_J00# Process
Note 309230.1 Database Doesn't Shutdown Immediate During Server Boot
Note 1039389.6 Alert Log: Shutdown Waiting for Active Calls to Complete
Note 305666.1 Shutdown is Cancelled With ORA-1013 After Waiting for an Hour
Note 1194229.1 Database shutdown immediate Hangs: Startup can also hang
Note 1183213.1 Shutdown Normal or Immediate Hang Waiting for MMON process
Note 428688.1 Bug 5057695: Shutdown Immediate Very Slow To Close Database
Note 416658.1 Shutdown Immediate Hangs / Active Processes Prevent Shutdown
Note 437876.1 Database Does Not Shutdown Cleanly When Oracle Service Is Restarted
Note 304414.1 Shutdown hangs in 9i with: SHUTDOWN: waiting for logins to complete
Note 332177.1 Database Shutdown Immediate Takes Forever, Can Only Do Shutdown Abort
Note 13440516.8 Bug 13440516 - Index skip scan cannot be interrupted - can block shutdown
Note 12879056.8 Bug 12879056 - Index skip scan cannot be interrupted - can block shutdown

ORA-1031 Errors

Note 309059.1 Oradim Command Fails to Shutdown Database(s) with ORA-01031 under 9.2.0.6
Note 846679.1 Ora-1031 Error Stopping Database Or Permission Denied Error Running Lsnrctl

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-600 / ORA-7445 Errors

Note 604067.1 Ora-600[3708] On Database Shutdown
Note 455181.1 ORA-00600[17302] During Shutdown Immediate
Note 470362.1 ORA-07445 With kpogup At Database Shutdown
Note 1135453.1 Database Hung On Shutdown After ORA-600 [KGHFRE3] Error
Note 359563.1 ORA-00600: Internal Error Code, Arguments: [17302], [2] During Shutdown
Note 435926.1 Shutdown Database Erroring ORA-600 [Librarycachenotemptyonclose], []
Note 8519322.8 Bug 8519322 - ORA-600 [17148] / ORA-600 [730] on database shutdown
Note 1326908.1 Ora-00600: [3708], ORA-600 [2103] When Database shutdown on IBM:Linux on System Z

ORA-24324 Errors

Note 794293.1 ORA-24324 During Shutdown
Note 1168554.1 Ora-24324 And Ora-1041 Errors Trying To Startup Or Shutdown The Database

DISM

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

 

Memory Related Errors

Note 1319253.1 "ERROR: SGA memory leak detected" message in alert.log on database shutdown

 

Other Issues

Note 1017085.102 ORA-01122, ORA-01210, ORA-01110: On Database Shutdown
Note 429603.1 ORA-29702 During Automatic Shutdown of Database using ASM
Note 1022414.6 ORA-01033 DATABASE INITIALIZATION OR SHUTDOWN IN PROGRESS
Note 784754.1 11g - Receiving Ora-27167 Error On Database Startup or Shutdown
Note 419651.1 Event 10621 and Event 10626/10629 Causes Shutdown Immediate to Hang
Note 118228.1 ALERT: Hang During Startup/Shutdown on Unix When System Uptime > 248 Days
Note 343031.1 How to deal with an ORA-01033 'Oracle startup or shutdown in progress' error
Note 18302.1 OERR: ORA 106 cannot startup/shutdown database when connected to a dispatcher
Note 763932.1 Shutdown Error In EM: Execution Failed Due To Binary Missing Or Permission Issues
Note 10194190.8 Bug 10194190 - Solaris: Process spin / ASM and DB crash if RAC instance up for > 248 days
Note 1001248.1 On Solaris 9 Systems, Oracle Shutdown May Hang If Utilizing Dynamic Initmate Shared Memory (DISM)
Note 760968.1 Database Startup, Shutdown Or New Connections Hang With Truss Showing OS Failing Semtimedop Call With Err#11 EAGAIN

 

 

Issues specific to automatic shutdown and startup

This 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 informations required to troubleshoot the issue is listed in the below document:

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

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

Thursday Jan 09, 2014

Oracle Database 12c Release 1 (12.1.0.1 ) availability and information

Oracle Database 12c Release 1 (12.1.0.1 ) is available for download  on

  • Microsoft Windows (X64)
  • Linux x86-64
  • Solaris SPARC 64-bit
  • Solaris x86-64
  • HP-UX Itanium
  • IBM AIX on Power Systems (64-bit)
  • IBM Linux on System z

Additional essential information and details are available via 

You may also want to review  Oracle Database Support News (Document 230.1) to keep up-to-date with the latest Database Support information.

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 depents 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


 

Monday Jan 17, 2011

Master Note for Generic Data Warehousing

[Read More]
About

News and Troubleshooting tips for Oracle Database and Enterprise Manager

Search

Categories
Archives
« April 2014
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
   
       
Today