Database

Difference between these two files SPFIE and PFILE?  What are their advantages? 

I will answer the second part of your question first (the difference between SPFILE and PFILE). Oracle provides two different types of parameter files that you can use, PFILE and SPFILE.

THE PFILE

Until Oracle 8i, we were using a text file called the PFILE (parameter file) for setting the database initialization parameters. This PFILE is read at instance startup time to get specific instance characteristics. The PFILE is text based, and can be edited in an editor like vi on UNIX or Notepad on Windows. Any changes that were made in PFILE would only take effect when the database is restarted

Since SAP/Oracle recommends using SPFILE over PFILE, I will provide some details on SPFILE to answer your question in more detail.

THE SPFILE

In Oracle9i, a new feature called SPFILE (server parameter file) was introduced. SPFILE is a binary file that contains the same information as the old PFILE. SPFILE permits dynamic changes without requiring you to restart that instance.

By default, if you do not specify PFILE in your STARTUP command, Oracle will use server parameter file (SPFILE). If you choose to use the traditional text initialization parameter file (PFILE), you must specify the PFILE clause when issuing the STARTUP command.

The SPFILE is different from the PFILE in that it can not be directly edited. This is because it has a header and footer that contain binary values. Since you can not change a SPFILE directly, Oracle allows you to manage the SPFILE via the ALTER SYSTEM command.

When you execute the ALTER SYSTEM command, the parameter change is validated immediately, which helps avoid errors associated with entering an incorrect parameter name or an invalid value. In addition, the ALTER SYSTEM allows you to specify whether you want to update the parameter immediately, update just the SPFILE, or both. All these features help avoid human error associated with manually updating a PFILE.

The following example shows how to change the current value of SESSIONS and store the value in the SPFILE.

SQL> ALTER SYSTEM SET SESSIONS=200 SCOPE=SPFILE; The new part in this command is the parameter SCOPE. You have the following options for this.

SCOPE = SPFILE

(For both static and dynamic parameters, changes are recorded in the spfile, and will take effect in the next restart.)

SCOPE = MEMORY

(For dynamic parameters, changes are applied in memory only. No static parameter change is allowed.)

SCOPE = BOTH

For dynamic parameters, the change is applied in both the server parameter file (SPFILE) and memory. No static parameter change is allowed.)

For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.

Now for the first part of your question. Since you are using Oracle 10G, you have to use the ALTER SYSTEM command to change the Oracle parameters as recommended by the SAP Early Watch Alert.

List of common database errors found in SM21 in SAP?

Here is a list of some common database errors found in tcode SM21 in SAP.

These are not very critical errors and can be ignored depending on situation.

Table names and error code in my case may differ from yours.

SQL statement violates database system restriction

Database error 24909 at UPD access to table TST01

Database error 1555 at FET access to table MKPF

Database error: TemSe->XRTAB(2)->128 for table TST03 key

Database error: TemSe->XRTAB(3)->1 for table TST01 key

Database error: TemSe->XRTAB(2)->128 for table TST03 key Challan No1914

Database error 1013 at COM

Database error -2 at FET

Database error 24909 at SEL access to table REPOLOAD

Database error 3114 at FET

For READ TABLE, the database table TDRG_MEMO_DET was not found

Database error 24909 at FET

Database I/O error

For READ TABLE, the database table TKKO was not found

For READ TABLE, the database table TBPA was not found

For READ TABLE, the database table TDRG_MEMO_DET was not found

Database error: TemSe->XRTAB(3)->1 for table TST01 key

DBIF_RSQL_INVALID_RSQL

Database I/O error

Database selection with invalid cursor 2

Database error 1410 at FET access to table BLPK

SAP system is full – what to do as Basis consultant?

If file systems in SAP server is full, what needs to be done?

How to avoid file system full in SAP?

How to delete unnecessary files in file systems of SAP?

How to delete core files in SAP?

How to delete trace files in SAP?

How to delete stat files manually in SAP?

What is the transaction code used to delete stat files manually in SAP?

What are various reports to be run to cleanup when file system is full?

How to prune the file systems in SAP?

How to delete old archive files in SAP?

What is the location of work and data directories in SAP?

What is the location of global directory in SAP?

Can we delete old page file and role files when the SAP system is online?

What is the sap parameter to set the trace level in SAP?

Sometimes, a basis consultant will get alerts or information from customers that the file systems are full. To avoid system issues and to increase uptime of the SAP system, in those cases, we have 2 options.

·         Delete un-necessary files in the file system

·         In case you found file system is defined as too small then increase the size of them.

In this article, am covering the option 1 mentioned above.

Let us assume that the system id(SID) of the sap system is CR1. Then the

i) Work directory in (Unix, WindowsNT operating system) is  /usr/sap/CR1/DVEBMGSnn/work     (where nn is the instance number)

ii)  data directory in (Unix, WindowsNT) is

/usr/sap/CR1/DVEBMGSnn/data    (where nn is the instance number)

iii)           Global directory (i.e. for all instances) is

/sapmnt/CR1/global (for UNIX)

\\<sapglobalhost>\sapmnt\CR1\sys\global (for Windows)

Please follow below steps to avoid this issue:

1.   Delete core files from work directory

Work directory in Unix often contains old core files which were generated due to previous program terminations. These core files need to be deleted 

2.   Delete old log files and spool files

Under global directory there will be many log files which can be deleted regularly.

Those files are

Log file type                      Naming Convention

Spool requests            nnnSPOOL (where nnn=client)

Job logs                      nnnJOBLG

Batch input logs          BI<hostname><instance-number>

Following reports can be run using SE38 or through background job for selective deletion of the files mentioned above.

 RSPO0041 - This report is used for deletion of old spool files

 RSBTCDEL – Used for deletion of old job logs

RSBDCREO - Report can be used for deletion of batch input log files and reorganization.

3.   Deletion of old ABAP/4 trace files

Some trace files with the name AT<instance number>nnnn will be in the data directory. Old files of this type can be deleted. The maximum available space for all files is defined by the parameter abap/atrasizequota. Also transaction SE30 can be used to delete the files.

4.   Deletion of old archiving files

When old data is archived, some files are written to the global directory. The naming convention of those files is Rxxmmddn (xx= application, mm= month, dd=day, n=number). For example, RMM11056

If these files are already saved on tape and these data is no longer required for productive operation, then these files can be deleted from the disk

5.   Deletion of old output requests

 Output requests are normally stored on the data directory. Naming convention will be like SP*. Under normal scenarios, they are deleted automatically when the output is completed. In case you see they are not getting deleted, you can delete the same

6.   Page file and Roll file

Under the data directory, normally page files and roll files are stored. Naming convention will be like PAGFILnn and ROLLFLnn(where nn is the instance number). Please note that page file and roll files can only be deleted when the corresponding SAP instance is offline.

Deleting these files hardly makes any sense because these files will start growing  to the allowed maximum extent  during the R/3 system start up. However in some special scenarios (during or after client copy), the required space can be retrieved by deleting these files. As mentioned earlier, please note that this can be done only when the instance is offline.

The maximum size of the roll file is given by

(rdisp/ROLL_MAXFS – rdisp/ROLL_SHM) * 8 Kbytes

A similar formula applies to the paging file also.

In some scenarios, to avoid the file system full issue, we can even move these page and roll files to a different file system by defining alternate values for R/3 parameters DIR_ROLL and DIR_PAGING

7.   Deletion of Old sort and Extract files

By the ABAP commands SORT and EXTRACT, temporary files are created. The location of these files is set using profile parameter DIR_SORT or DIR_EXTRACT.

The temporary files for sort are named S+++++++<Extension> (On Windows .dat is used as extension  and on Unix there is no extension). Similarly for Extract, E+++++++<Extension> will be the file naming convention.

These files will be automatically deleted after the execution of these SORT and EXTRACT commands. However, in some scenarios, an abrupt termination can happen and these files won’t get deleted automatically. Those old files can be deleted by an administrator.

The exact file names for these are stored in the profile parameters FN_SORT or FN_EXTRACT

8.   Deletion of trace files

Trace files are created during a new system startup and they can be deleted. They are contained in the work directory

9.   Deletion of stat file or moving to different location

Please check whether the stat file which consists the work load statistics, has not been reorganized for some time and has therefore become too large. In emergency cases, this file can be either deleted manually or can be moved to different location.

To delete the stat file manually, please proceed as follows:

Go to ST03 transaction; call the delete function on the relevant instance by choosing:

Workload -> Reorganize -> Delete seq.stat.file

To change the location of stat file, change the value of system parameter ‘stat/file’ to a new location.

10. Deletion of job logs at operating system level

In some emergency cases, where you cannot start the R/3 system at all, the measure is to delete the job logs at operating system level and run RSBTCDEL report in forced mode.

Note: How to delete job logs at operating system level will be covered in a separate article later

11. Changing the trace level

If the trace files (dev_*) in the work directory are very large (normal size 0.1 to 20Kbyte), it is likely that the trace level is set to very high value. In SAP, rdisp/TRACE parameter is used to set the trace level. Normal value for trace is 1. 

Please cross check this SAP parameter and reset to 1 if the value is maintained as 2 or higher. If this value is set to high, much more detailed trace will be collected and trace file sizes are likely to increase. Therefore recommending to decrease or set the trace level to 1.

Please refer below link to know, how to set the trace level in SAP.

However, if after carrying out all the above steps there is no improvement, then please consider to increase the file system size by requesting space to the relevant team