SAP on Oracle: FAQ. Questions and Answers

SAP on Oracle: FAQ. Questions and Answers

In this post you can find the answers for below questions.

1. On which component can I open an SAP message if there are problems with the Oracle database?

The following components are available:

BC-DB-ORA: Oracle

BC-DB-ORA-DBA: BR*TOOLS for the database administration

BC-DB-ORA-CCM: Problems with CCMS functions in the Oracle area

BC-DB-ORA-INS: Installation of the Oracle database

BC-DB-ORA-RAC: Oracle Real Application Cluster

BC-DB-ORA-SYS: DBI and DBSL Oracle functions

BW-SYS-DB-ORA: Oracle problems in the BW area

2. Where can I find the Oracle alert Log?

You can find the alert log in the SAP environment in the following directory:

10g or lower: <SAPDATA_HOME>/saptrace/background

11g or higher: <SAPDATA_HOME>/saptrace/diag/rdbms/<sid>/<sid>/trace              The file name is alert_<sid>. log, <sid>alrt.log or similar.

3. Why are some Oracle errors not logged in the alert log?

Oracle only logs critical errors in the alert log. Most of the Oracle error codes are not logged (unfortunately this may also include error codes that are genuinely critical). It is therefore normal for you to find errors in the Oracle application that are not displayed in the Alert Log.

4. How can I prevent the alert log from becoming very large?

SAP Note 786032 describes how you can ensure that the size of the alert log remains within its limits by regularly renaming the file.

5. How do I obtain an explanation of Oracle error codes?

You can use the OERR tool on UNIX to obtain more exact information about a <domain>-<code> error code. For the ORA-01555 error, you can call the tool as follows:

oerr ora 1555 Alternatively, the Oracle online documentation contains a complete overview of all error messages including explanations (for example, in the 9i documentation under “Error messages”).

6. How I can determine which SQL statement triggered an Oracle error?

You can generally look in the corresponding log or trace file to find out which SQL statement has triggered an error. If this is not possible in your particular case, you can activate a more precise logging process in Oracle for a specific error code (for example, tracing for ORA-00942 -> <errorcode> = 942):

ALTER SYSTEM SET EVENTS = ‘<errorcode> trace name

errorstack forever, level 1′; If an SQL statement now triggers an error, Oracle creates a detailed trace file in the user trace directory. This file includes, among other things, the triggering SQL statement.

7. What are the parameter recommendations for Oracle?

              Refer to SAP Note 124361 and the subordinate notes referenced there, depending on the product you use (for example, SAP Note 180605 for Business Warehouse). For Oracle 10g, refer to SAP Note 830576. For Oracle 11g, SAP Note 1431798 is relevant. For Oracle 12c, see SAP Note 1888485.

8. Where can I find the Oracle parameter file (init<sid>.ora or spfile<sid>.ora)?

              You can find the parameter file init<sid>.ora under


WINDOWS: <oracle_home>\database If you use a SPFILE with Oracle 9i or higher, you can also find spfile<sid>.ora in this directory.

9. Why are changes that I made in init<sid>.ora not implemented?

If changes in init<sid>.ora are not implemented by Oracle, check the following points:

  1. a) To activate the changes, you must restart Oracle.
  2. b) Do not use an SPFILE. Otherwise, the changes must be executed in the SPFILE instead of in init<sid>.ora (see Note 596423).
  3. c) Parameters must not be defined twice. In this regard, check to see whether there are IFILE entries that include additional parameter files. If a parameter is defined in these files, this setting may overwrite changes to the parameter of the same name in init<sid>.ora. It is a good idea to copy all parameters to init<sid>.ora and delete the IFILE entries.
  4. d) If several event entries are specified, these must be found in consecutive rows of init<sid>.ora.

10. Which combinations of Oracle, R/3 and operating system are permitted?

Depending on the R/3 kernel release, the supported combinations are described in the following SAP Notes:

Note 23875 (3.x)

Note 85838 (4.0x/4.5x)

Note 156548 (4.6x)

Note 407314 (6.x)

11. Where can I find the Oracle installation or upgrade CDs?

To install the Oracle software, use only the Oracle RDBMS CDs delivered by SAP. CDs obtained directly from Oracle cannot be used. For example, you can order the RDBMS CDs by opening a message on component XX-SER-SWFL-SHIP. It is important that you correctly specify the database release and operating system.

12. Where can I find information about the Oracle upgrade?

The sequence of an Oracle upgrade is described in the Oracle upgrade guides for the relevant Oracle target release and operating system (these guides are available on SAP Service Marketplace under the quick link “instguides”):

-> Other Documentation

-> Database Upgrades

-> Oracle              These guides contain all the essential notes for the upgrade. The Oracle upgrade is not integrtaed into the upgrade of other components like R/3, but can always be performed separately. SAP Note 551542 contains more information about upgrades.

13. Why is a short dump written during an offline backup when the R/3 system is running?

The current R/3 system cannot know that BRBACKUP stops the database during an offline backup. Therefore, the first work process that loses the connection to the database writes a short dump. All work processes then go into reconnect mode until the database is available again. Consequently, it is normal that one (or more) short dumps are written during an offline backup, because the database cannot be accessed. For more information about the reconnect mechanism, see Note 98051.

14. Can I use backup/restore to copy an Oracle database to a system with a different software level?

If possible, the source and target software should be identical as far as the release, patch set and bug fixes are concerned. However, under the following restrictions, you can also make a copy on another software level:

Switch to the identical target release with a different patch set status or different bug fixes:

Since changes to the Oracle ABAP Dictionary are sometimes carried out within the framework of patch sets and bug fixes, the patch set and bug fix status should be identical when you change to an identical target release. Otherwise, strange errors can occur if the Oracle ABAP already contains a change but the relevant patch has not yet been installed in the target system (for example, ORA-00947 by SMON, if the umonmods.sql change from Note 692214 is already active but the patch is still missing in the target system). Patches that do not make any changes to the Oracle ABAP are referred to as rolling patches and can, in accordance with Note 306408, be identified by means of “opatch query -is_rolling”. For all non-rolling patches, the relevant change scripts must be used to implement the ABAP changes manually. If the target system contains fewer patches and fixes, it is very difficult to carry out the “downgrade” of non-rolling patches that is required as a result.

Due to the likelihood of errors occurring, we advise you not to use these procedures.

Switch from 32-bit to 64-bit and vice versa if you have identical source and target releases

After the copy, you must execute utlirp.sql (or utlip.sql and utlrp.sql) on the target system, as described in SAP Note 192822.

Switch from Release 8.x to a more recent Release 8.y

After the copy, you must execute the relevant upgrade script from $ORACLE_HOME/rdbms/admin on the target system. The name of the script is derived from the Oracle source release, which corresponds to the third, fifth and seventh characters of the name (for example, u0800060.sql for 8.0.6 source release or u0801050.sql for 8.1.5 source release).

Oracle release upgrade (for example: 10g –> 11g)

After the copy, you must execute all of the post-upgrade steps that are described in the guide for upgrading to Oracle 9.x.

Switch to a newer patch set of the same release

After the copy, you have to carry out all of the steps that have to be carried out when you install the patch set.

Several points can also be connected (for example, a change from 8.0.6 (32-bit) to 8.1.7 (64-bit)). The only essential step is that postprocessing is carried out fully.

15. Between which operating systems are homogeneous system copies and the structure of a standby database scenario possible?

Homogeneous system copies may only be carried out between systems with the same operating system. Standby databases must also run on the same operating system as the primary database. Differences within an operating system are generally allowed:


Different releases (for example, AIX 4.3 versus AIX 5.2); exception: You cannot combine HP-UX 10.20 and HP-UX 11.0 or higher.

              On the basis of transportable tablespaces, you can also copy between different operating systems (see SAP Note 1003028). Changing from 32-bit to 64-bit or vice versa is only permitted on the basis of an offline backup; using an online backup for this purpose is not supported. Oracle supports the 32-bit primary database with the 64-bit standby database and vice versa (My Oracle Support (MOS) documents 414043.1 and 413484.1).

16. How can I adjust the new tablespace and user names when backup/restore is used to carry out a system copy?

If the new database layout is used with the user SAP<sid> (instead of SAPR3) and tablespace names with an integrated SID, these names remain unchanged after backup/restore is used to carry out a system copy. If, for example, you copy a system with the SID “PRD” to a system with the SID “DEV”, users and tablespaces in DEV also confusingly have “PRD” instead of “DEV” in the name. This scenario is normal, poses no technical problem and can at the most lead to confusion in the administration area. One possible way to resolve this is to define an SID-dependent schema ID, as described in SAP Note 617444.

17. Can I perform transports between the databases of different releases?

Since the TP and R3TRANS transport tools work with a database-independent data format, you can perform transports between databases with different Oracle releases.

18. Which Oracle releases are currently supported?

SAP Note 1174136 contains information about which Oracle releases are supported and when support ends. For the release of Oracle 11g, refer to SAP Note 1398634.

19. Where can I find information about Oracle patch sets and bug fixes?

A bug fix (also known as an interim patch as of Oracle 9i) is a correction provided by Oracle (depending on the patch set used) to eliminate a particular problem. A patch set is a collection of corrections provided as a unit. The current patch set version is logged in the fourth digit of the Oracle release specification (for example, Oracle -> patch set 3 installed for Oracle 9.2.0). Bug fixes always depend on the current patch set version. If a bug fix exists for a patch set level, the next highest patch set generally already contains this fix. As of Oracle 10g, SAP provides “SAP Bundle Patches” at regular intervals that contain alle currently available and important bug fixes. SAP Note 1175996 describes how to determine the current patch level.

20. Which Oracle patches should generally be imported?

We recommend that you always use the latest patch set. However, since bug fixes may have negative side effects, you should not import them without making absolutely sure that they are essential.

21. How does changing the system time affect Oracle (for example, when daylight saving time is switched to standard time and vice versa)?

Internally, Oracle works with System Change Numbers (SCN) and not with real timestamps. Therefore, a system time change does not have any impact on Oracle, which means that – from a database point of view – you do not need to stop Oracle to carry out a time change. However, you should bear the following restrictions in mind:

If a time interval occurs twice as the result of a time change, the system cannot determine at which of the two identical points of time it should stop when you perform a point-in-time recovery with an end time in the duplicate interval. However, in this scenario, you can carry out the recovery with a target SCN instead of a target time.

If you carry out an RMAN backup during a time change, so that the start time of the backup is after the end time, subsequent “list backup” commands may terminate with errors such as ORA-01455 because the RMAN function cannot handle this constellation. You can solve this problem by recreating the control files that contain the backup information.

If you do not convert the system time on WINDOWS both on the database server and on the domain controller, the KERBEROS authentication with SQLPLUS or SVRMGRL can fail because the time variance is too large, and the problems described in Notes 614036 and 620540 occur.

If you convert the time zone itself, the conversion may affect columns of the type “TIMESTAMP WITH LOCAL TIME ZONE” or ” TIMESTAMP WITH TIME ZONE”. These columns are not used in the SAP environment, but may appear in the Oracle ABAP as of Oracle 10g. In this case, you may need to make certain changes when you change the time zone. Refer to Note 1019133 for more information about the “Daylight Saving Time” change in the USA in 2007.

22. How can I access remote Oracle databases from the R/3 system?

In addition to standard solutions such as database links, you can – as of R/3 4.0B – define a secondary database connection (as described in SAP Notes 323151 and 339092) to access remote Oracle databases. This is a good solution because you do not need to make any changes at Oracle level (except for adjustments to tnsnames.ora) and the connection is set up in the same way as the primary database connection.

23. How can I access the Oracle database of the R/3 system from an external system?

Generally, you can access the Oracle database of your R/3 system from an external system in various ways (Net8, database links, ODBC, and so on). However, you should bear in mind that license agreements often do not cover using the R/3 database in this way. For more information, see Note 581312. SAP Support is not responsible for setting up these connections.

24. Can I open Oracle in read-only mode?

Yes – for more information, refer to SAP Note 817253.

25. What maximum sizes are permitted for data files?

Refer to SAP Note 129439.

26. Can I use Oracle functions that are not used in the SAP standard system?

SAP Note 105047 discusses the extent to which you are permitted to use functions such as Oracle Enterprise Manager (OEM), Recovery Manager (RMAN), partitioning, Index Organized Tables (IOTs), Connection Manager, Oracle names, advanced security or Oracle Data Guard. Refer also to SAP Note 740897. For Oracle 10g, see SAP Note 828268.

27. What factors determine the space occupancy of an object at database level?

The space occupancy of an object (such as a table or index) at database level is mainly determined by the quantity of data. However, in the following cases, space occupancy may be very different despite a comparable dataset:

Fragmentation due to data deletions or unfavorable PCTFREE/PCTUSED values (Note 821687)

Use of different tablespace types (dictionary-managed vs. locally-managed)

Use of different extent sizes (INITIAL, NEXT)

Using different index types (B* tree versus bitmap)

28. How can I determine how the database is occupied on R/3 components (such as clients, company codes, and plants)?

              SAP Note 118823 contains reports that can be used to estimate the size of individual clients. You can use the command Space_Clients_RealData_CommandGenerator from SAP Note 1438410 for the same purpose. No such reports exist for other groupings like company codes, plants or sales organisations. Therefore, an evaluation of these sizes is not possible in the standard system.

29. Why is the database still occupied to the same extent after extensive data archiving?

If data is deleted from the database, the blocks of the affected objects are cleared, but the amount of space the affected objects occupy is not reduced. To make the space that is no longer occupied available for general use again, you must reorganize the objects concerned. For this, refer to SAP Notes 572060 and 541538. Refer also to SAP Note 821687, which contains detailed information about space occupancy and fragmentation.

30. How I can access R/3 pool and cluster tables at Oracle level?

Unlike transparent tables, the pool and cluster tables defined in the R/3 system do not have a direct equivalent at Oracle level. Instead, their data is stored in relevant table pools or table clusters. Only these pools and clusters have an equivalent at database level. The logic used to extract the data of the pool and cluster tables from the table pools or table clusters is integrated in the R/3 kernel. You do not have the option to copy this logic at Oracle level. Therefore, you can still only access R/3 pool and cluster tables with R/3 tools. Refer also to SAP Note 581312 which states that direct access to SAP data via non-SAP interfaces is permitted only on a restricted basis.

31. Why do the Oracle processes run under the <sid>adm user on my system?

The UNIX PS command displays the real user by default, not the actual user. Therefore, it is not a problem if the displayed user for the Oracle processes is <sid>adm. The only important thing here is that the Oracle executable has the correct authorizations, as described in Note 583861. For more information, see Note 206916.

32. How can I access the Oracle metalink?

Refer to SAP Note 758563.

33. Which CSI number can I use?

If you have acquired the Oracle license from SAP, you do not need a CSI number. You receive Oracle support from SAP and you can also access Oracle metalink as described above.

34. What must I do if I cannot use transaction SE16 to make recommended changes in configuration tables?

For various reasons, it may be necessary to change data in configuration tables (such as DBDIFF, TAORA, or IAORA). Due to system and client changeability, this is not possible, especially in live environments. However, you can proceed in one of the following ways:

Change the system changeability (SE06) and/or the client changeability (SCC4) temporarily, so that you can use SE16 to carry out a change.

Check if there is an SM30 maintenance dialog that you can use to maintain the table.

Use database tools (for example, SQLPLUS) to execute the change directly. Caution: Misuse can lead to data loss.

35. Why does the sequence of the online redo logs change sporadically?

If the next online redo log is still archived while another redo log is available for overwriting again, this redo log becomes the next redo log. This changes the sequence of the online redo logs. This behavior is desired, because the alternative would be an “archiver stuck” – at least for a short time. This scenario can occur only if several archiver processes run in parallel and these proceses no longer comply with the archiving of the redo logs. To avoid this problem, check if the archiver performance can be optimized (I/O tuning). Furthermore, an “archiver stuck” scenario must be avoided due to a completely full archive file system. The problem can also not occur, if the number of archiver processes is limited to 1 by LOG_ARCHIVE_MAX_PROCESSES and you have referred to SAP Note 445226.

36. What common misconceptions are there about the Oracle environment?

There are several widespread misconceptions about the Oracle database (for example, ONLINE operations do not set any locks or the CBO always selects the access path with the most favorable costs). For more information, see Note 825653.

37. Can the data of the Oracle database be encrypted?

At SAP level, there is no global option for encrypting data in the database. Instead, sensitive data is stored in SAP pool or cluster tables, so that the contents cannot be read meaningfully at operating system levelnicht sinnvoll. At Oracle level, data can be encrypted within the framework of the Advanced Security Option (see SAP Note 973450) or Transparent Data Encryption (see SAP Note 974876). In individual particularly sensitive areas, SAP provides additional encryption mechanisms – for example, credit card encryption in R/3 systems (see SAP Note 766703) and payment card data encryption (see SAP Note 858200). Effective protection of data in the database can be achieved by a restrictive authorization concept at SAP level and by security measures in the network and operating system areas.

38. How can database accesses be logged?

              If you want to log specific database operations or accesses to specific tables, you can do this on the SAP side by using trace mechanisms like SQL audit or logging of table changes, as described in SAP Notes 139418 and 115224. On the Oracle side, you can use parameters like AUDIT_TRAIL or AUDIT_SYS_OPERATIONS, which you configure by AUDIT commands and deactivate by NOAUDIT commands. For more detailed information, see the Oracle documentation.

39. What are the disadvantages of using a large database?

At first glance, a large database, which may increase significantly in size, does not have any major negative effects on productive use. As long as the hardware dimensions are sufficient, there are few performance disadvantages. The main problem with a large database is the increasingly difficult administrative handling. Consider the following points in relation to this:

Backup and restore runtimes can increase by a significant amount, which – in serious cases – can lead to an unnecessarily long downtime. In addition to this, an online backup that is running can negatively affect the production operation, which is why online backups should also run for as short a time as possible. Note 842240 contains information about optimizing the backup and restore processes for large databases.

The runtimes of operations on the database (such as consistency checks, database checks, reorganizations, index rebuilds or statistics creation) are relatively high and guarantee an increased system load over a longer period of time. See also Note 806554 for possible optimization measures.

Operations in the SAP system (such as archiving sessions, system copies or client operations) also take a long time if the dataset is large.

For the above reasons, it is a good idea to use a comprehensive archiving concept to limit database growth right from the start.

40. What size should data files be?

It is not possible to give a simple answer to this question. Generally, the size of the data files has no effect on activities in the database. However, you should bear in mind the following points:

Ensure that the DB_FILES Oracle parameter is set high enough. Otherwise, new data files cannot be created once this limit is reached.

The smaller datafiles are, the more quickly they can be individually restored during a backup.

The smaller the data files are, and therefore the more data files there are, the longer BEGIN BACKUP operations are likely to take in online backups. For more information, see Note 875477.

Data files that are too large aggravate performance problems that are caused by inode locking, since parallel processes may become serialized on the data file inode. See Note 793113.

On occasion, size restrictions may prevent the system from using data files that exceed a certain size (often 2GB). See Note 553854 for more information.

When there is a large number of data files and, at the same time, OS resources are not adequately configured, critical errors such as “file table overflow” can occur, which – at worst – may even cause the database to crash (see, for example, Note 9748). Therefore, depending on the number of data files selected, it is important to also adjust the relevant operating system parameters.

In general, it seems a good idea to restrict data file sizes to between 2 GB and 32 GB.

41. How can I delete a data file that was created by mistake?

If a data file was created with the wrong size or in the incorrect directory, you can use RESIZE or RENAME to correct the error in most cases. If you want to drop the data file again instead, you have the following options:

Up to and including Oracle 9i, a created data file can only be deleted during a tablespace reorganization. There are no other realistic options.

As of Oracle 10g, an empty data file can also be dropped with the following command:

ALTER TABLESPACE <tsp_name> DROP DATAFILE ‘<path_and_name>’;

If there are still extents in the data file, this command fails with ORA-03262. In this case, the affected segments must first be relocated so that the extents are released.

42. What are OCI_7 and OCI_8?

Log files and tool outputs contain information relating to OCI_7 or OCI_8, such as “Now I’m connected to ORACLE using OCI_7 API”. OCI_7 and OCI_8 are programming interfaces, and each provides a dedicated command record for client programs such as the SAP kernels. All SAP kernels up to and including 4.6D (including _EXT and _COM kernels) use OCI_7, while OCI_8 is used as of 6.20. The version does not depend on the Oracle client and the Oracle server release you use.

43. Can I also administrate the database with non-SAP tools?

SAP recommends you use BR*TOOLS to administrate the Oracle database (see SAP Note 651812), but you can also use non-SAP tools to do so. Note the following restrictions:

SAP does not provide any support for problems related to non-SAP tools. This also includes Oracle tools such as Enterprise Manager (see Note 386413).

In the case of certain activities, such as reorganizations, SAP tools also change entries in the ABAP DDIC. When you use external tools, these changes should be made in the same way. For more information, see Note 154193.

Generally, you should only use the functions of the external tools if you know exactly how they work and what consequences they have. For example, a parallel processing level of tables or indexes, which is continuously increased within the framework of reorganizations or rebuilds, can have serious effects on database accesses and performance.

44. Can I use directory structures that differ from the SAP standard?

SAP only allows you to use the directory structure and naming conventions described in SAP Note 27428. Variations can result in problems with the SAP tools and when analyzing error scenarios and are therefore not permitted. However, you can (if absolutely necessary) define the sapdata directories, or the subdirectories that it contains, as symbolic links to other directories (for example, /oracle/<sid>/sapdata1/stabd_1 -> /oradata/stabd_1).

45. How can I determine the cause of changes to table data?

If it is not clear why entries in tables disappear or change, technically, the simplest way to investigate the cause is to display the Oracle Shared Cursor Cache (see SAP Note 766349). There you can display and analyze all accesses to certain tables simply. If INSERT, UPDATE or DELETE accesses occur, you can use the ABAP source code button to determine at what point these statements are triggered. Other approaches to cause analysis like auditing or triggers are also feasible, but are unnecessary in most case.

46. Why do online and offline redo logs differ in size?

SAP Note 998675 contains information about possible causes as to why the size differs between offline and online redo logs.

47. Can Oracle files be stored on NFS?

Data files, redo logs and control files are not permitted on standard NFS file systems. Only special, trusted NFS variants, such as NetApp-Filer, are allowed. See Notes 834343 and 999524 for more information.

48. How can I determine the Oracle internal demand for resources?

You can monitor resources, such as the number of processes, sessions, segments or locks, as follows:

V$RESOURCE_LIMIT contains information about the current demand, about the peak demand and about the maximum possible value:





Note that the entry for MAX_ROLLBACK_SEGMENT is not important if you use Automatic Undo Management.

As of Oracle Release 10g, DBA_HIGH_WATER_MARK_STATISTICS contains information about current demand and about peak demand for resources:









49. How can I determine when the Oracle database should be started, and with which version it should be started?

As of Oracle 10g, you can take the history of the database start from the DBA_HIST_DATABASE_INSTANCE view:







STARTUP_TIME; In earlier Oracle versions, you can take this information from the alert log, in which all of the starts are logged.

50. How can I determine which fixes are installed?

You can use “opatch lsinventory” to display the bug fixes that are currently implemented on the operating system. As of BRCONNECT 7.00 (20), you can also use BRCONNECT to display this information:

brconnect -F lsinv              As of Basis Release 7.10 and as of Release 7.00 Support Package 11, Release 6.40 Support Package 20 and Release 6.20 Support Package 62, you can also use the report RSORAPATCHINFO, which provides the same information on the SAP. As of Oracle 10g, there is the view V$SYSTEM_FIX_CONTROL, which at least displays the implemented fixes.

51. How can I determine at what times the database was upgraded?

As of Oracle 10g, you can use DBA_REGISTRY_HISTORY to determine at what times the database was upgraded and downgraded and at what times patch sets were applied and CPUs were carried out (SAP Note 1017936).

52. Where can I find an overview of the tasks of the background processes?

At database level, you can get a good overview of the tasks of the background processes of the table X$MESSAGES:








53. What does ASFU mean?

ASFU means “Application Specific Full Use” and is the name for Oracle licenses that are purchased from SAP. Therefore, each customer who licenses the Oracle database via SAP has an ASFU license.

54. Where can I find more information about Oracle?

You can find a very comprehensive overview of Oracle functions in the Oracle online documentation. You can find information about Oracle bugs and frequent errors under the components BC-DB-ORA*and BW-SYS-DB-ORA in SAP Note Administration. For information about bugs or technical problems, go to You can find detailed information about the SAP tools used in the Oracle environment, for example BRBACKUP, BRARCHIVE, BRCONNECT, BRRESTORE, BRTOOLS, BRSPACE and BRGUI and other documents about current topics under

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *