Recompiling invalid objects

When applying patches or making changes to a database I often invalidate many objects with one change. As long as the object requires no changes, the following SQL*Plus script will recompile any object that is invalid:

set feedback off

      set verify off

      set echo off

      set pagesize 0

      set heading off

      spool compile.sql

      select  'alter ' ||

             decode(object_type, 'PACKAGE BODY', 'package', object_type) ||

             ' ' ||

             object_name||

             ' compile' ||

             decode(object_type, 'PACKAGE BODY', ' body;', ';')

      from   dba_objects

      where  status = 'INVALID'

      /

      spool off

      set feedback on

      set verify on

      set heading on

      set pagesize 40

      @compile

   

Locating duplicate rows when trying to create UNIQUE INDEX

Presume you are trying to create a UNIQUE index on a column and get the following error:

SQL> desc emp

    Name                            Null?    Type

    ------------------------------- -------- ----

    EMPNO                                    NUMBER(15)

    NAME                                     VARCHAR2(20)

    DEPTNO                                   NUMBER(15)

   SQL> CREATE UNIQUE INDEX emp_i ON emp (empno);

   CREATE UNIQUE INDEX emp_i ON emp (empno)

                             *

   ERROR at line 1:

   ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Above error is encountered as Oralce couldn't create a UNIQUE INDEX on the table as it found duplicate records while trying to create the index. You know have to find the duplicate values. Below methods will be useful for locating duplicate rows in a table.

    Method #1 uses a self-join query. Oracle tables Consistently have one guaranteed unique column, the rowid column. Use of the MIN and MAX functions against the rowid column can easily display the duplicate rows.

    Method #2 uses a GROUP BY / HAVING query on the table.

    Method #3 uses the "exceptions into" clause of the alter table command. We need to first create the EXCEPTIONS table by running the script "utlexcpt.sql" inorder to use the "exceptions into" clause . On NT, this script is in your ORACLE_HOME/rdbms(73/80)/admin directory: On UNIX, it is in $ORACLE_HOME/rdbms/admin directory.

    Method #1 : Using a self join

    SELECT

          rowid

        , empno

        , name

      FROM emp a

      WHERE a.rowid > (SELECT MIN(b.rowid)

                       FROM emp b

                       WHERE b.empno = a.empno);

      ROWID                   EMPNO NAME

      ------------------ ---------- --------------------

      0000059F.0001.0001        100 Jeff

    Method #2 : Using a GROUP BY

    SELECT 

          COUNT(*)

        , empno

      FROM emp

      GROUP BY empno

      HAVING COUNT(*) > 1;

        COUNT(*)      EMPNO

      ---------- ----------

               2        100

    Method #3 : Using the "into exceptions" clause

    ALTER TABLE emp

      ADD CONSTRAINT emp_i UNIQUE (empno)

      EXCEPTIONS INTO exceptions;

      alter table emp

      *

      ERROR at line 1:

      ORA-02299: cannot enable (OPS$ORACLE.EMP_I) - duplicate keys found

      -- NOW JOIN THE exceptions TABLE TO THE emp TABLE BY rowid

      SELECT 

          a.empno

        , a.name

      FROM 

          emp a

        , exceptions b

      WHERE a.rowid = b.row_id;

           EMPNO NAME

      ---------- --------------------

             100 Mark

             100 Jeff

How to create background job server group

(Like for Logon group:SMLG,For RFC logon group:RZ12,Spool server group:SPAD)similarly we can create backgroung job server group:SM61

Tcode…..>SM61

Steps:-

1>.Create a background server group using the instances which are having strong resource (CPU/RAM)

2>.Schedule the business critical jobs during the Month-End in that background server group.

Advantage:-

1>.Business critical jobs will run smoothly without any resource issue.

2>.Good for background job capacity planning by defining the background job server group

Disadvantages of other Methods:-

1>.Sometimes If we don’t define the target server then a business critical background job may fail if it goes to a weak resource server.

2>.If we define the target server manually and if there are many business critical jobs to define the target server then  that is tedious task to find out and keep the track which server is not loaded heavily.

Might help the team for OLTP production systems for background job administration.

Adapter Engine cannot be found in Integration Directory

"Adapter Engine can't be found in Integration Directory". We can find this error in integration directory and that we can resolve this issue using the subsequent steps.


To check the above error, attend integration directory click on menu Environment -> Cache Notification,


OR


While creating the channel for any adapter in integration directory, adapter engine won't show.


Reason for getting this error :


While doing the XI post-installation steps, the technical system and business system weren't created in SLD for XI server.


OR


The technical system and business system weren't created properly in SLD for XI server.


Proposed solution:


Technical system and business system to be recreated for XI server.


Steps to create technical system.


Step-1: Go to tcode : RZ70



Step-2: Check whether the hostname & gateway service entered correctly or not. If not enter the right hostname & gateway service than save.



Step-3: Click on "Proposal" button and install the default settings.



Step-4: Click on "Activate the current configuration" in Application toolbar.



Step-5: Click on "Start data collection" in Application toolbar and click yes.



Now go to SLD main screen, check whether the technical system created or not.



Step-1: Click on technical landscape in SLD home page.



Step-2: Choose the technical system type - Web AS ABAP.



Step-3: Find out the technical system created with the name “ on server name” .



Steps to create business system.



Step-1: Go to SLD main screen, click on business landscape and click on new business system.



Step-2: Enter the name of business system.



Step-3: Choose the technical system type "Web AS ABAP" and select the parameters technical system, client (ex: XID on iginsapep , 100).



Step-4: Click on the "Next" button and again "Next".



Step-5: Choose the business system role- Integration Server.



Enter the pipeline URL - http://:/sap/xi/engine?type=entry



Here hostname is server name, HTTPPort: 80.







Finally clicking the "Finish" button, it will create the business system in SLD for XI server.



Go to SLD main screen.

Check the technical system type: Exchange Infrastructure, in Application System column for integration server it should appear " on hostname" (ex: on iginsapep) . The below screen shot will appear after creating the technical system and business system in SLD.

To avoid other inconsistency problems in cache update or RWB, please implement the below oss note.

To appear " on hostname" in Application System column for other XI components Adapter Engine, Integration Directory, Integration Repository and Runtime Workbench/RWB. Refer the OSS note: 764176 and find the below attachment after implementing oss note.

Important Note-1: Add the merchandise “SAP Exchange Infrastructure 3.0” and every one the SWCVs to the above technical system and business system.

Important Note-2:  while creating the business system, if we face the error message “Integration Server already defined there's already Integration Server defined for the chosen technical system . Please select  Application system role.”, then delete the technical system and recreate the technical system using RZ70 and recreate the business system supported the technical system.

There are two ways to see the adapter engine status in Integration Directory.

Clear the integration directory cache  , Environment -> Clear SLD Data Cache.

First: Test the cache notification in ID , Environment menu -> Cache Notification

It shouldn't show any error message 'Adapter Engine not found in Integration Directory'.

Second: Check whether the adapter engine is showing in channel level for any adapter either in sender or receiver.

How to delete a scheduled job in SAP

To delete a job:

Go to Transaction SM37. choose a job (or jobs) from the Select Background Jobs window. In the Job Overview, mark the job or jobs you would like to delete by checking the box to the left of the job name. Choose Job --> Delete.

Deleting Jobs That Have Dependent Jobs:

If you delete a job that has got to be processed before another job are often started, the dependent job can not be started. The system will let os know of any such existing dependent, or successor, jobs. You'll then got to either reschedule or delete the dependent job.

If you are trying to release employment whose predecessor job was deleted, the system sets the status of the job to Planned. To start this job, you want to release it and specify the beginning conditions.

How to copy tables to another system using R3trans

If you would like copy some tables to a different system, you'll use R3trans. The steps are the following:

Create in the system source the file: export.ctl

Export

File = 'export.dat'

Client = 201

Select * from table

And within the system target create the file: import.ctl

Import

File = 'export.dat'

Client = 301

Then execute in the source systems:

R3trans export.ctl

This creates a file named export.dat, then copy this file to target system and execute within the target system:

R3trans import.ctl

This imports the table to the target system.PD.

You can check the SAP Note No. 1942

Configuring the Unix SAP Internet Mail Gateway

Configuring SAP 4.6x Internet mail Gateway

We can confidure email communication setup in SAP system to send and receive mails from different sources.This article helps us understand the way to integrate SAPOffice with an external email-system. it is essentail that Internet email configuration is in place and running succesfully before this configuration. External email system receives e-mail from SAP.

You can configure inbound and outbound forwarding. Outbound flow forwards SAP message (eg: update termination) via UNIX sendmail to the intended recepient. Inbound accepts a message from sendmail and places it within the users SAPOffice inbox. Many companies prefer to configure outbound only.

Configuring outbound forwarding

SAP configuration

1. Using transaction SM59, create an RFC destination for outbound email 

 RFC Destination : SAP_INTERNET_GATEWAY

 Connection Type : T

 Description : SAP internet mail gateway

  If ondemand gateway daemon invocation is required, click on 'Explicit Host'.

 Program : /sapmnt/SID/exe/mlunxsnd

 Target Host : provide hostname details of the central instance.

 Click 'Test Connection' and you should see successful message.

2. Shared Office Settings select appropriate option : transaction SO16-> Send -> Settings or

 directly via transaction SCOT- SAPconnect Administration

 Nodes - Create

 Node : IMAIL

 Description : SAP internet mail gateway

 RFC Destination : SAP_INTERNET_GATEWAY

 Tick : Internet

 Address Area : *

 Tick : All formats

 Dev. type : Choose an appropriate Printer Device

 Set further address type : N

 Maximum waiting time for repeat send attempt procedure : Blank or as per the requirement

 Tick : Node is ready for use

 Setting

 - Default Domain : .com

 - Conversion rule : Needed if the communication device only support single format.

 e.g. if your email system only support RAW (ASCII text format) format 

 Format To Format Ranking Function module

 ALI RAW 1 SX_OBJECT_CONVERT_ALI_RAW (convert APAP List)

 Refer SNote 171698 for details.

UNIX configuration

1 .. cd /sapmnt/SID/exe

2 ..csh

 ..mlsomadm mailgw.ini

 System Name [C11] :

 Client [000] :

 Username : MAILADM

 Password : MAILADM

 Language : E

 Load Balancing :

 Hostname : 

 System number : 

 Gateway hostname: 

 Gateway Service : 

 Use SAP Router :

 Trace level :

 Sendmail Command [/usr/lib/sendmail -i -f]:

 Codepage [ISO-8859-1] :

 Trace Level (Outbound) [0] :

 Update file sapmailsid.cfg? [Y]

Testing whether your configuration is successful

1. Logon to SAP

2. Execute transaction SO01

3. Write a message and send it to 

4. If mail is not received in your internet mailbox, go back and review steps 1-12

Schedule the SAP Internet Mail Gateway Jobs to start out every 5 minutes

1. Create

2. Position your cursor at INT

3. choose schedule and provide the date and time

4. choose Schedule periodically and tick Minutes and enter 5

5. choose Create and you are done

6. Click Show Scheduling to check

Problems that you might encounter :-

If you have created a node in SCOT and it tests out well but receive the reply.

"cannot process message in node, parameters can't be converted".

Make sure the RFC connection is functioning , which SapConnect has been installed on the Unix Server or the Microsoft Exchange Server. Originally, I had a similar error, and located that nothing had been installed on Unix or Exchange, to support SapConnect.

In SCOT (View -> System Status), the mail persist in the Intransit column.

Check you sendmail.cf files (e.g.Sun Solaris /etc/mail/sendmail.cf). Try with sendmail command to send a test file at the OS level. You must be ready to send mail at theUnix level before you'll send mail at the SAP level.

Another way of connecting to the SAPOffice without setting up the SAPconnect is to use the

How to add default printer to user in SAP system

We can assign the printer as default printer to particular user in SAP.
This blog shows the procedure on the way to add it.

1.SU01 screen

Click on change icon

2.Users Menu -à defaults

Here we've to feature a printer thereto particular user

Under spool control there's a column with output device.

Here we have to add the printer to the user

And check output immediately

SAP SYSTEM : 

CLIENT : 

User ID : XXXXXX

Then click on save button

The below screen shows that we have no authorizations to user changes.

How SAPOSCOL works

SAPOSCOL a tiny program developed by means of SAP as daemon so that SAP R/3 instance can comunicate with its OS host. By the use of this SAPOSCOL program, SAP R/3 can talk and can get a lot of info about OS inclusive of CPU usage, memory degree usage, swap, disk activity, network information, monitored process, system configuration, client - server connectivity, etc. Hmm….we can get it without directly communicating with OS. Just use SAPGUI from your workstation.

Usually, SAPOSCOL program had to be started earlier than your SAP R/3 instance so that it can grant your R/3 occasion with OS information. To begin SAPOSCOL program, your simply entered your OS console/terminal and use ora[SID] (if you’re the use of Oracle) or SAPservice[SID] if you’re the usage of MS SQL.

Just type : #saposcol

If SAPOSCOL process had been running, it will exhibit you the records and status.

To end SAPOSCOL, simply type : #saposcol -k

To review SAPOSCOL information, simply type : #saposcol -s

SAPOSCOL program is very necessary to SAP R/3 instance. It can delivered info to SAP tcode such as RZ20, ST03, ST06, etc.

If your SAPOSCOL program did’nt run then you’ll want to run it manually. Usually, Basis overlook about its permission (if you’re working in UNIX environment) and claimed “Hey, my SAPOSCOL didn’t run ? help…”

Your first step is take a look at its permission on OS level (no t required to restart your SAP R/3 system nor your database server).

Hope this tutorial can assist you.

Change The Variants or SAP Printers of Scheduled Background Jobs

How to regulate the multiple step application variant?

Go to tcode SM37

- At the Job Overview screen, place your cursor at the scheduled jobs you prefer to modified (the jobs status should nonetheless be Released)

- Click Job -> Change

- Click the Steps button -> right here you can move, add or delete addition program steps to be run

- Double click on the program name you prefer to change

- Click the Print specs button if you favor to change the SAP printers

Hardware Migration

Prerequisites

· Download required software dump and patches from SAP  market place

· Compare filesystem area with supply source SAP system database

· Set paage memory

Actions on Target server

· Install Oracle RDBMS software

· Apply RDBMS patch to the Source DB patch level

· Install SAP NW2004 with ABAP content

· Install Java add in on ABAP

· Request and apply SAP license for new hardware

· Start J2EE on new server

· Upgrade Kernel to the Source Kernel patch level

· Take full backup of database

· Take java filesystem backup

· Bring down target SAP server alongside with DB

· Stop all the services associated to oracle and SAP

· Delete datafiles from sapdatas , origlogs and mirrlogs in target system

Actions on source server

· Request Downtime for source SAP server

· Suspend jobs the usage of SAP report btctrns1 in source server

· Bring down SAP alongside with DB in source server

· Stop all services associated to Oracle and SAP in source server

· Start zipping of sapdatas, origlogs and mirrorlogs

· Zip J2EE filesystem j2EE,SCS, SYS folders.

· Copy the zipped files manually by way of Wan to target server

Actions on target server

· Unzip the zipped files and copy or overwrite them in accordance to the structure

· Change hostname in ABAP in accordance to SAP Note 8307

· Change hostname in RZ70

· Change hostname in j2ee config tool in accordance to SAP Note 757692

· Start DB,

· Delete source ops$ customers and recreate for Target server and change the password

· Start SAP and J2EE

· Re Install SAP license

· Reconfigure SMLG,RZ10,RZ04,RZ12, DB13 etc.. accordingly

· Check RFC connections and reconfigure if required

· Reconfigure STMS

· Reconfigure SLD and JCO destinations the usage of visual admin tool.

· Restart cluster for adjustments to be affected

· Check SLD and portal

· In SLD exchange hostname in profile and server settings if required

· Reschedule jobs the usage of SAP report btctrns2

· Configure backup strategy

· Take full database backup alongside with J2EE filesystem. 

Procedure for connecting R3 with PI

Below is the method to create RFC connection between ECC and PI system.
This is required throughout PI Installation or after PI system copy.

Actions to be accomplished in PI system

1.Create RFC connection for R3 the usage of T-Code sm59

2.Create a port the use of T-Code IDX1

Use R3 RFC destination

Actions to be finished In R3 server

1.create a logical system for XI system the use of BD54 T-Code

2.Create RFC destination to PI server the use of sm59 T-Code

3.create partner profile for the logical system the use of T-Code WE20

ALL About TSK files

ALL About TSK files

These TSK files are pure text files and contain an entry for every action within the form:

with = T, D, P, I, V (Table structure, Data, Primary key, Index, View)

= C, I, E (Create, Import, Export),

= xeq, ok, err

For the first time, R3load reads a TSK file completely, closes the file, renames it during a TSK.bck (for example, SAPSDIC.TSK.bck) and opens the TSK file for writing. After each execution an entry of the action is written with the corresponding status.

If all actions are processed, or if the unprocessed actions might be written back to the TSK file after an error, the TSK.bck file is deleted. During start-up, if R3load also finds a TSK.bck addition to a TSK file , it terminates immediately with a corresponding error message, as this either means an R3load process is running and is processing this request, or that a significant error has already occurred (possibly with a core dump) which will not be eliminated during the restart, but requires user intervention (for example, a full filing system , corrupt files after disk crash) When the error has been eliminated you want to merge the TSK file and therefore the TSK.bck file before or during the restart of the R3load.

There are several options available to do this:

o Manual: The TSK file contains current entries for all objects that are processed. The bck file contains entries for all objects, however, the status could also be outdated. When you merge both the files, the entries missing within the TSK file must be manually transferred from the bck file. You must then rename the bck file.

o Call R3load with the extra option '-merge_bck' (in this case, all missing entries are set to the status 'err'). This consolidates the TSK file and also the bck file and the R3load for the export or

import is started. If the R3load processes are started by the MigrationMonitor, you'll configure the extra R3load option within the *_monitor_cmd.properties file.

o As of a particular patch level in Release 6.40, the R3load contains the choice '-merge_only'. If you call the R3load with the choice '-merge_only , only the TSK file and therefore the bck file are

merged, but the export or import isn't started. You can use the choice 'R3load -h' to see whether the R3load contains the choice '-merge_only'.