SAP NW Newbie

Today I learned that an IO bottleneck in Oracle can be reduced significantly if the database is reorganized. We had a problem with a badly designed database file layout. SAP application, Oracle data files and the redo logs were all configured to be stored on a single disk. A higher than normal load on SAP system caused the entire system to crawl. Quickly checking a few transactions, which included ST06, showed that the response time of disks was very high, more than 500 ms. Before planning to redistribute the data files and redo logs away to a new disk, someone wanted to try a DB reorganization. When you reorganize the database, the fragmentation of data is reduced. So a read requires fewer blocks to be read and thus reduces the load on the disk. After the DB was reorged, problem was gone!

Here are some important Oracle parameters and information in the context of use with SAP applications. BACKGROUND_DUMP_DEST Path for alert log and background trace files COMPATIBLE Defines the Oracle version whose features can be used to the greatest extent As a rule, it must not be reset to an earlier release (see Note SAP 598470). A value with three parts (such as 10.2.0) rather than five parts (such as 10.2.0.2.0) is recommended to avoid changing the parameter as part of a patch set installation. If an ORA-00201 error occurs when you try to convert the value with five parts 10.2.0.2.0 to 10.2.0, you can leave the value 10.2.0.2.0 (independent of the patch set used). CONTROL_FILES Path and name of the control files that are used CONTROL_FILE_RECORD_KEEP_TIME Defines how many days historic data is retained in the control files Historic data is required by RMAN, for example. May cause control files to increase in size (see Note 904490) CORE_DUMP_DEST Path ...

To check the index storage quality call the transaction DB02 > Detailed Analysis > (enter the index name) > Detailed Analysis > Analyze Index > Storage Quality. If the quality is less than 50%, the index needs a reorg .

Let us say you have a table (myemployeetable) containing employee names, IDs etc and you are writing a script carrying a variable meant to hold the total number of employees. You can get the total count of employees by running a sql file and assigning the output to this variable. This is however not that straightforward. A sql command outputs a lot of information that has to be excluded when the result value is assigned to the variable. First, you don't want to print column names, so turn the heading off. set heading off Next, in the output, you do not want messages like "x records selected". To do this, turn feedback off. set feedback off Suppress all headers, page breaks, titles etc by setting the page size to 0. set pagesize 0 If you are using a SQL variable, you have to suppress how a variable is substituted before being sent to execution. This is done by turning verify off. set verify off The output of sql command shows "SQL>", this can ...

You may want to change the redo log file size or it's location online (in case you are facing space issues on the existing disk drive). Before changing the redo logs, take a full backup. Drop the first redo log by using the SQL command alter database drop logfile '<path to the redo log file>'; Now create it with your preferred location or size using the command alter database add logfile '<preferred path and name of the log file>' size <preferred size>M; Repeat this on the rest of the redo logs. You will encounter ORA1515 error at the point when you are deleting a redo log file that is currently in use. You can wait or skip the redo log file for now and proceed later with this redo log file. You may also force log switch to let you change the current log file using the command alter system switch logfile;

You can find out all the indexes that are corrupted by matching up v$database_block_corruption view. Let's begin with formatting the output: set pagesize 50 linesize 170 col segment_name format a30 col partition_name format a30 Now the following SQL will list out the corrupt indexes: select distinct file#,segment_name,segment_type, TABLESPACE_NAME, partition_name from dba_extents a,v$database_block_corruption b where a.file_id=b.file# and a.BLOCK_ID <= b.BLOCK# and a.BLOCK_ID + a.BLOCKS  >= b.BLOCK#;

SAP refresh requires database to be restored and recovered on the target system. One of the most important steps in Oracle DB restore/recovery is the control file creation on the target system as the file locations and SID of the database change. Here are the steps to create control file: Generate the control file trace on the source system: 1. Ensure that the source DB is on open or mounted mode by running the following command select open_mode from v$database; The output should be MOUNTED or READ WRITE 2. Write the control file to trace by running the following command alter database backup controlfile to trace; 3. Find out where the trace is written by running the following show parameter dump; The location is most likely  /oracle/<SID>/saptrace/diag/rdbms/<sid>/<SID>/trace for SAP-Oracle database. Check the latest trace file. 4. Open the file and copy the section resembling the following as a new file ex: createcontrolfile.sql by r...

Extended Statistics is an attempt to fix one of the flaws in CBO--values of different columns are not correlated. Let us take an example of two columns in a table. One of the column contain department code and the other contains employee name. Let us assume that there are 10 departments and 3000 employees in our example. In a real life scenario, all the employees do not belong to all the departments, but CBO assumes that is the case and hence it assumes that there are 3000*10 = 30000 combinations of employee name and department code that exist. In reality, it can be between 3000 and 30000 (assuming employee belongs to at least one department and can clock for multiple departments). The CBO is not intelligent to know these relations and this assumption can have serious performance impact on join operations. In order to calculate better statistics, we can use extended statistics from Oracle 11g onwards . SAP has provided these statistics for AUSP, BKPF, MSEG and HRP1001 tables as ...

One of the most important aspects of understanding the Oracle system architecture is understanding how instance memory is divided. We will look at an overview of the memory areas which have relevance to database usage with an SAP application as SAP does not make use of all the memory areas available. There are two broad memory areas Memory shared by all the processes - System Global Area in Oracle Memory assigned to exactly one process - Program Global Area in Oracle System Global Area [SGA] Database Buffer Cache Also known as Buffer Pool or Data buffer or " Cache " This portion of SGA holds copies of the data blocks from the datafiles. The SQL operations on data objects are first performed on these blocks and then transferred to the datafiles by the DB Writer processes. The Buffer pool is further divided into the following parts Free Buffer - free space Pinned Buffer - holds data that is currently being accessed Dirty Buffer -  holds data that is mod...

We had a task to write a script to check if certain environment variables, which should not be set, were set. This task was straightforward with C shell: if $?GARBAGEENV then echo "Unset GARBAGEENV on `hostname`" endif With K shell, we did not find anything built-in. So we used something innovative. if test "isenvset${GARBAGEENV}" != "isenvset"; then echo "Unset GARBAGEENV on `hostname`" fi If GARBAGEENV was set to N, isenvset${GARBAGEENV} would be evaluated as  isenvsetN (which is not equal to isenvset). If it was not set isenvset${GARBAGEENV} would be evaluated as isenvset.

You can look up the explanation of Oracle error code on the database server itself using  oerr utility on UNIX OS. Here is an example explanation of ORA-1555 error. sapadm> oerr ora 1555 01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small" // *Cause: rollback records needed by a reader for consistent read are //         overwritten by other writers // *Action: If in Automatic Undo Management mode, increase undo_retention //          setting. Otherwise, use larger rollback segments Pretty handy if you are working for a third-class company that blocks google search.

Enterprises using multiple ERP systems look for a a job schedulers that can work with SAP and other ERP or DW vendors alike. Third party tools are not advisable if the Enterprise runs purely on SAP ERP. They increase the TCO, increase delays in problem correction, create high dependencies among technologies, and increase chances of miscommunication. If varied technologies are being used, investing in a third party scheduler is useful to have a centralized scheduler. Here are some popular schedulers: Control-M by BMC Cronacle by Redwood ORSYP by Dollar Universe Tidal Enterprise Scheduler by Cisco Tivoli Workload Scheduler (Maestro) by IBM  UC4 Global by UC4

Fixing ORA-00059 maximum number of DB_FILES exceeded The number of datafiles per database is limited by the parameter DB_FILES. You can check the current limit using the following SQL command: show parameter db_files; You can check the curent number of datafiles the following SQL command: select count(*), max(file#) from v$datafile; When the curent number of datafiles equals DB_FILES, the next attempt to add a datafile will error out with ora error code ORA-00059. The limit can be increased (for example to 300) using the following SQL command. The changed requires a restart of database. alter system set db_files=300 scope=spfile;

SAP Buffers and Synchronization In a distributed architecture, the database and instances can be on separate servers. Therefore the access to database from the application instances would have to resort to inter server communication over the network. If the data is not changed very frequently and not too big, caching it at the application's memory can improve the access speed. Apart from improving the access speed, caching will prevent the load on the database. By reducing the database load, the need to add more CPU (and memory) can be avoided, which in turn reduces the licensing cost. SAP buffers are a part of the application memory that conceive this concept. Buffer Synchronization SAP buffers are local to each instance. When a change is made, the application instance where the transaction ran can be made aware of the change easily. However, it is very important to ensure that the changes are communicated to other application instances to ensure validity of the buffered in...

If you are using Oracle database with GRC, you can set Row-prefetch value for Oracle Risk Analysis to tune the performance of Risk Analysis. The parameter uses the row-prefetching feature of Oracle JDBC driver to fetch multiple rows at a time (instead of the default one row) reducing the round trips to be made to the database layer. The extra rows are stored in the buffers and used by the later queries which pick one of the rows that are already placed in the buffer. To set the number of rows to prefetch, navigate to Configuration --> Risk Analysis (left pane) --> Performance Tuning --> Row-prefetch value for Oracle Risk Analysis. A value of 10 is a good starting point.

When you try to connect to Oracle database and see " connected to an idle instance " as you enter sqlplus, there are chances that you have set incorrect file permissions. Check that $ORACLE_HOME/bin has "drwxr-xr-x" permisson The file "oracle" in $ORACLE_HOME/bin should have non-zero size and permission "-rwsr-s--x" If the permission differs, set it correctly using the following command chmod 6751 oracle

This is a thumb-rule for creating indexes in Oracle: For small tables (< 100 MB) The creation of index on such tables usually takes a few minutes. Therefore, when you are creating the index pick a time of day where a lock on the table (transactions will be slow due to lock on the table) for a few minutes is acceptable. For medium tables (100 MB - 1 GB) Choose a week-end or a time frame where no user will be running a report that will be using this table. For large tables (>1 GB) The index creation on these tables will be very time consuming and you may have to do this as a downtime. You will have to use DB specific options to speed up index creation. For example in Oracle, you can use PARALLEL DEGREE while creating it. a. Create the index in the DEV system using SE11. b. Take note of the exact name of the index. c. Create the index at Oracle level in QA with the same name that was used in DEV using the following syntax: create index <index name> on <table ...

There seems to be a bug with Oracle database that causes execution of two or more aggregate functions in an SQL statement to be time consuming. Example of two aggregate functions in same query: SELECT MIN(XYZ) MAX (XYZ) FROM TABLE_XYZ; SAP XI has one such statement in the report SXMS_PF_REORG (reorganization of adapter data). As a work around, SAP has split the two aggregate functions in one statement to run as two separate statements. Implement SAP note 1540734 to implement SAP's workaround if you are facing performance issues with regard to this report.

You can now let SAP support personnel access your database through SAP service connection. Ensure that you maintain permit access to the database server and listener port in sap router table. Call service.sap.com/access-support Click on Maintain connection Choose the system to which you wish to open the connection Click on the JDBC/ODBC Connection type entry and provide the port number used by the listener Now go to Open/Close connection and click on JDBC/ODBC Connection to open the connection like any other SAP service connection

Disaster Recovery is the process, policies and procedures of restoring operations critical to the resumption of business, including regaining access to data (records, hardware, software, etc.), communications (incoming, outgoing) workspace, and other business processes after a natural or human-induced disaster. Here are some common aspects of designing a Disaster Recovery environment for a business application: 1. DR Server The server-that you intend to use in case of disaster-should contain the same version/patch of the Operating System as the primary server. You have to ensure that the disk allocation is similar to that of primary. You need not allocate identical server resources on the DR site, but you may need them when you intend to run the DR site as primary in the event of a disaster. 2. Standby Database Most of the database vendors provide you with an option to install and maintain a standby database, which keeps itself cloned with the primary database. Ex: ...