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

Wednesday Nov 17, 2010

Oracle Support Master Note for Troubleshooting Managed Distributed Transactions (Doc ID 100664.1)

[Read More]

Oracle Support Master Note for Troubleshooting Streams Apply Errors ORA-1403, ORA-26787 or ORA-26786 (Doc ID 265201.1)

[Read More]

Oracle Support Master Note for Troubleshooting Streams Capture 'WAITING For REDO' or INITIALIZING (Doc ID 313279.1)

[Read More]

Oracle Support Master Note for Streams Setup Scripts (Doc ID 789445.1)

[Read More]

Oracle Support Master Note for Streams Performance Recommendations (Doc ID 335516.1)

[Read More]

Sunday Jul 04, 2010

Oracle Gateway Master Note (Doc ID 1083703.1)

Master Note for Oracle Gateway Products

1. Concepts and Availability

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

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

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

Database Gateway for ODBC (DG4ODBC)

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

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

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

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

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

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

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

Oracle's Lifetime Support Policy document is available here:

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

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

Oracle Gateway Release's

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

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

then -

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

Please note that the Legacy Gateways -
DG4IMS
DG4VSAM
DG4Adabas

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

2. Downloading Gateway Products

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

Oracle Gateway products can be downloaded from the following -

1. Oracle Technology Network - OTN -

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

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

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

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

2. Oracle Software Delivery Cloud


http://edelivery.oracle.com/

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

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

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

3. My Oracle Support - 11.2.0.2 and 11.2.0.3

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

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



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

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

3. Installation and Configuration

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

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

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

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

Note.1351618.1 Installation Overview For Oracle Database Gateway Products


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

- logon to MOS -

http://support.oracle.com

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

Examples are -

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

but new notes are continually being added.

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

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



4. Troubleshooting

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

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

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

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


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

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

Common Gateway error messages are -

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

5. Alerts

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

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


Who to contact for more information?


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

http://support.oracle.com

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

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

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

There is also an Oracle Technology Network forum available at -

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





















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