Tuesday, February 14, 2012

Known database hang situations or errors in sap

1.  Log on to the databas
 a) WINDOWS: DLL problems
 b) HP-UX 11 (64-bit): Missing HP-UX Support Packages

2.  Starting the database:
 a) HP-UX: Startup hangs when moving to the NOMOUNT phase;
 b) SOLARIS, RELIANT: Startup hangs when moving to the MOUNT phase
 c) UNIX: Startup hangs when moving to the OPEN phase
 d) WINDOWS, Oracle 9i: The database takes a very long time to start

3.  Runtime:
 a) Changes are no longer possible, log file switch is not possible
 b) Oracle or higher:The database cannot be accessed, the CPU load is 100%.
 c) NT/W2K with AWE: Database can no longer be accessed, high CPU utilization

4.  Stopping the database:
a) NT/W2K: "shutdown normal" hangs;
 b) W2K, Oracle 8.0.6: "shutdown immediate"/"shutdown normally" hangs
c) "shutdown immediate" hangs;
d) "shutdown immediate"/"shutdown normally" hangs
e) "shutdown immediate"/"shutdown normally" hangs
 f) SOLARIS, RELIANT: "shutdown immediate"/"shutdown normally" hangs
 g) NT, Oracle 8.0: Shutdown hangsYou can use the ORADEBUG Oracle tool to analyze a hang situation asdescribed in note 613872.

 1.  Log on to the database
 a) If work processes do not start because they hang when you log on to the database, an incorrect Oracle DLL can be responsible for this.
b) Tools such as SAPDBA or the BR*Tools hang when you log onto the database - check the HP-UX Support Packages as described in note  431881.

2.  Starting the database:
a) If Async I/O is used on HP-UX, Oracle may hang with "startup  nomount".The following error message is displayed after two minutes:
  ORA-00445: background process "PMON" did not start after 120seconds
 This problem is due to a missing MLOCK privilege for the dba and  oper group.First use "getprivgrp dba" and "getprivgrp oper" to check whether this privilege exists.If it does not exist, you can assign it as a root temporarily by using "setprivgrp dba  MLOCK"/"setprivgrp oper MLOCK" and permanently by creating the /etc/privgroup file with the following contents:
              dba MLOCK
              oper MLOCK
b) If Oracle hangs on SOLARIS or RELIANT when moving to the MOUNT phase ("alter database mount" is the last entry in the Alert  log), the bug described in note 329741 is probably the trigger,  if the system was not rebooted 24, 8 or 248 days before.
c) If Oracle hangs without error message when moving from MOUNT to the OPEN status, the problem may be caused by data files that,incorrectly, contain an S bit in the access authorizations. Check the authorizations for the data files.If an "S" appears anywhere(for example, "rw-r-S---"), reset it using chmod (for example,"chmod 640 <file>" for "rw-r-----").

 d) Refer to note 656809 and make sure that the background_dump_dest parameter is set on an existing directory (SAP standard:<drive>:\oracle\<sid>\saptrace\background).

 3.  Runtime:

  a) In addition to the archiver stuck problem (note 391) and a"Checkpoint not complete" error (note 79341), which is easily identified by the entries in the alert log, the database hang situation may also be caused by the log_archive_start parameter: if the parameter is set to "false" although the archive log mode is activated, the archiver process does not run and save the online redo logs.The log writer then refuses to perform a log switch if it encounters one of the (unsaved) online redo logs. Set"log_archive_start = true" and restart the database to correct this problem.If you do not want to restart the database, perform an "archive log start" in svrmgrl.
  b) Refer to note 515080 and check if the shared_pool_size is sufficiently dimensioned. Note 514758 contains relevant bug fixes. Notes 505246 and 507254 contain other instances of the same bug.

c) Using AWE and defining an AWE_WINDOW_MEMORY that is too small may cause the database to hang. For more information, refer to note  603041.

4.  Stopping the database:

a) Refer to note 445275 and check if the Oracle Intelligent Agent is still running.

b) The Oracle 8.0.6/W2K combination is not supported because, among other things, it does not allow a proper shutdown of the system (see notes 156548, 407314). Switch to a supported environment. In this context, the alert log frequently contains a messag  "Waiting for detached processes to terminate".
c) A "shutdown immediate" first rolls back all active transactions before stopping the database. If a long-running transaction with low Commit frequency was active beforehand, the roll back process  takes an equally long time (approximately the time since the last Commit).This may give the impression that the shutdown is  hanging. If you cannot perform any maintenance until the rollback is completed, you must stop the database with "shutdown abort". After restarting, the system continues the rollback in the   background.

 d) Check in accordance with note 183842 to see if PSAPTEMP is set to "PERMANENT". Alternatively, you can also create PSAPTEMP as an LMTS (note 214995).If you create PSAPTEMP as a Dictionary Managed Table space with TEMPORARY contents, SMON must clean up for a long  time during the shutdown. The alert log contains the message"Waiting for smon to disable tx recovery".

e) When you use table monitoring, the shutdown may hang due to  access to MON_MODS$. For more information, refer to notes 604176 and 528527.

f) If you are no longer able to stop the database on SOLARIS or  RELIANT in the normal way, the bug described in note 329741 may  be responsible if the system was not rebooted 24, 8 or 248 days  before.

g) Refer to note 128726 and implement one of the suggested workarounds or change to a more current Oracle release. There are also similar problems with Oracle and this  context, the alert log frequently contains a message "waiting for  detached processes to terminate".

1.  Physical database limits
Database files
maximum per tablespace : on most operating systems 1022 maximum per database 65533 total, further limited by the db_files init<SID>.ora parameter Database file size:  see note 129439 for OS-specifics

Control Files:
  number: at least 1 more on separate disk strongly recommended maximum size: 20000 x db_block_size (8k) = 160MB

Redolog Files:
maximum number: soft limited by the MAXLOGFILES control file parameter maximum size:   OS-limited , usually 2GB maximum number of log files per group: unlimited

2.  SGA-Size:
32-bit platforms -see note 123366
64-bit platforms: 64 GB

3.  Logical Database limits:
Table spaces:
  maximum number: 64000

maximum size of an extent: 2GB (initial and next extent) default value max extents: as set for the default of the table space   maximum value for max extents: unlimited

number per table: unlimited

Partitions :
maximum number of columns in partition key : 16 columns maximum number of partitions allowed per table or index: 63999

Rollback segments:
maximum per database: no limit


per table: maximum 1000 columns
 per index: maximum 32 columns per bitmapped index: maximum 30 columns

maximum per column: unlimited

column limits:
VARCHAR2:     maximum size 4000 bytes NUMBER(p,s):  maximum p can range from 1 to 38. s from -84 to 127
 LONG:         maximum 2GB
DATE:         Jan 1st 4712 BC to Dec 31 9999 AD
RAW:          maximum 2000 bytes
CLOB:         maximum 4GB
NCLOB:        maximum 4GB
BLOB:         maximum 4GB
BFILE:        maximum 4GB

4.  Programming limits:
SQL statement length: 64k maximum
SUBQUERIES: maximum levels of subqueries: unlimited in the FROM clause   255 sub queries in the WHERE clause
GROUP BY clause: the group by expression and all non-distinct aggregate functions (AVG, SUM...) must fit within a single database block (8k)

5.  Resources defined in init<SID>.ora
db_writer_processes: 10
io_slaves : 15
sessions: 32000
parallel_max_servers: unlimitedse check with note 180430 for your kernel-specific note.



Post a Comment