Long running background job anaysis

1) First you need to identify the job that is long running and identify details like job class, work process that is executing the job

How to identify long running background jobs:

2) Click on the job to view the display job screen. In the screen, click on job log to understand what is being performed by the job currently. This may give details like job is currently extracting some data packages or processing data packages etc

3) Identify the executing server and process id of the job from the step 1 and goto transaction SM50 of the respective executing server to view more details about the background job running.

Figure out the status of the job like On Hold or running from the process overview. If the job is On Hold, find out the reason for On Hold by examine the "Reason" column of SM50 transaction. Reason for On Hold could be due to CPIC/RFC/DEBUG/ENQ/PRIV/UPD etc.

Double click on the reason column for detailed information on the same and troubleshoot accordingly. If reason is RFC, check out which RFC it is referring to and cross check whether destination system is up or not and any other problems with that system.

    If it is ENQ, check out any lock issues like lock overflow etc
    If it is PRIV, check out for memory bottlenecks
    If it is UPD, check out whether any update issues
    If it is CPIC, check out for any network , gateway, message server and other communication problems


4) After performing step3, if you figure out job is not on Hold and it is in running state, then examine report column to identify what report/program is being executed by the job. Once you got the report/program details, figure whether it sap program or custom program and take actions accordingly.

5) Also examine Action and table columns in SM50 transaction of respective executing server to identify what is the action( roll in/roll out /Sequential read/Physical read/insert/update/delete etc)  being carried out by the job currently and what is the table on which action is being carried out.

If it is sequential read, figure out the cost of that sequential etc and consider for indexing etc. If it is physical read, check out whether there are too many swaps and consider resizing buffers accordingly. If you observed delay is due to high roll in/roll out, identify reasons for the same and tune buffer/memory parameters accordingly.

6) Once you get the table details on which action is being carried out, figure out       

        How many records are existing in the table ?
        Is this taking long time due to volume of records ?
        Are there proper indexes on the table ?(If no proper index, consider index creation  by taking help of DBA )
        Is the table having upto date statistics ? (If statistics are out of date,

              consider updating statistics of that table)


7) Consider debugging the process in SM50 ( Program/Session -> Program ->   Debugging ) to figure out the issue

8) Using ST05 or ST12, a trace can be taken for background job to figure out where exactly time is being consumed and to identify various cpu/memory bottlenecks or any buffer issues.

9) STAT/STAD transaction can be used to figure out what is the reason for high response time and actions can be taken accordingly

10) By taking help of ABAP er, even ABAP run time analysis can be done using SE30 transaction

By following the above steps, you can pin point the issue and take actions accordingly to minimize runtime of long running background jobs.

BRTOOLS-Backup issues

Ending a Backup After an Instance Failure or SHUTDOWN ABORT:

The following situations can cause a tablespace backup to fail and be incomplete:

The backup completed, but you did not run the ALTER TABLESPACE ... END BACKUP statement.
An instance failure or SHUTDOWN ABORT interrupted the backup.

Whenever crash recovery is required, if a datafile is in backup mode when an attempt is made to open it, then the database will not open the database until either a recovery command is issued, or the datafile is taken out of backup mode.

For example, the database may display a message such as the following at startup:

ORA-01113: file 12 needs media recovery
ORA-01110: data file 12: '/oracle/dbs/tbs_41.f'

If the database indicates that the datafiles for multiple tablespaces require media recovery because you forgot to end the online backups for these tablespaces, then so long as the database is mounted, running the ALTER DATABASE END BACKUP statement takes all the datafiles out of backup mode simultaneously.

In high availability situations, and in situations when no DBA is monitoring the database, the requirement for user intervention is intolerable. Hence, you can write a crash recovery script that does the following:

1. Mounts the database
2. Runs the ALTER DATABASE END BACKUP statement
3. Runs ALTER DATABASE OPEN, allowing the system to come up automatically

An automated crash recovery script containing ALTER DATABASE END BACKUP is especially useful in the following situations:

All nodes in an Oracle Real Application Clusters (RAC) configuration fail.
One node fails in a cold fail over cluster (that is, a cluster that is not a RAC configuration in which the secondary node must mount and recover the database when the first node fails).

Alternatively, you can take the following manual measures after the system fails with tablespaces in backup mode:

Recover the database and avoid issuing END BACKUP statements altogether.
Mount the database, then run ALTER TABLESPACE ... END BACKUP for each tablespace still in backup mode.

Ending Backup Mode with the ALTER DATABASE END BACKUP Statement

You can run the ALTER DATABASE END BACKUP statement when you have multiple tablespaces still in backup mode. The primary purpose of this command is to allow a crash recovery script to restart a failed system without DBA intervention. You can also perform the following procedure manually.

To take tablespaces out of backup mode simultaneously:

1. Mount but do not open the database. For example, enter:

SQL> STARTUP MOUNT

2. If performing this procedure manually (that is, not as part of a crash recovery script), query the V$BACKUP view to list the datafiles of the tablespaces that were being backed up before the database was restarted:

SQL>  SELECT * FROM V$BACKUP WHERE STATUS = 'ACTIVE';
FILE#      STATUS             CHANGE#    TIME   
---------- ------------------ ---------- ---------
        12 ACTIVE                  20863 25-NOV-02
        13 ACTIVE                  20863 25-NOV-02
        20 ACTIVE                  20863 25-NOV-02
3 rows selected.

3. Issue the ALTER DATABASE END BACKUP statement to take all datafiles currently in backup mode out of backup mode. For example, enter:

SQL> ALTER DATABASE END BACKUP;

You can use this statement only when the database is mounted but not open. If the database is open, use ALTER TABLESPACE ... END BACKUP or ALTER DATABASE DATAFILE ... END BACKUP for each affected tablespace or datafile.



Caution:

Do not use ALTER DATABASE END BACKUP if you have restored any of the affected files from a backup.




Ending Backup Mode with the SQL*Plus RECOVER Command

The ALTER DATABASE END BACKUP statement is not the only way to respond to a failed online backup: you can also run the SQL*Plus RECOVER command. This method is useful when you are not sure whether someone has restored a backup, because if someone has indeed restored a backup, then the RECOVER command brings the backup up to date. Only run the ALTER DATABASE END BACKUP or ALTERTABLESPACE ... END BACKUP statement if you are sure that the files are current.



Note:

The RECOVER command method is slow because the database must scan redo generated from the beginning of the online backup.




To take tablespaces out of backup mode with the RECOVER command:

1. Mount the database. For example, enter:

SQL> STARTUP MOUNT

2. Recover the database as normal. For example, enter:

SQL> RECOVER DATABASE

3. Use the V$BACKUP view to confirm that there are no active datafiles:

SQL>  SELECT * FROM V$BACKUP WHERE STATUS = 'ACTIVE';
FILE#      STATUS             CHANGE#    TIME   
---------- ------------------ ---------- ---------
0 rows selected.

To generate Public/Private SSH Key

ssh-keygen  using this command we can generate two keys, one public (the one you will share to the server) and the other one is private
and you shall not share with anyone.

Once the public key is copied in the ~/.ssh/authorized_keys in the server, you can login without being prompt for password.


1) As per below screen can see a prompt asking you for the location to save the key. The default location is the .ssh folder in your Home directory. You can just press “Enter” to accept the default setting.

2)Below screen is to passphrase to unlock the private key so that no one can access your remote server even if they got hold of your private key. The passphrase is optional. To leave it blank, just press “Enter”.

3)Public and private SSH key should now be generated. Open the file manager and navigate to the .ssh directory. You should see two files: id_rsa and id_rsa.pub.

4)Upload the id_rsa.pub file to the home folder of your remote host. Connect to your remote host via SSH and use the following command to move the public key to the correct location.

cat id_rsa.pub >> ~/.ssh/authorized_keys
rm id_rsa.pub

5) Remote host, open the SSH config file:

 /etc/ssh/sshd_config

6) Scroll down the config file and make sure the following attributes are set correctly.

RSAAuthentication yes
PubkeyAuthentication yes
PasswordAuthentication no

7) Finally, restart the SSH server in the remote host

sudo /etc/init.d/ssh reload

8)Now we can connect to remote host with the following command:

ssh -i /path-to-private-key username@remote-host-ip-address

# private key
openssl genrsa -out privkey.pem 2048

# public key
openssl rsa -in privkey.pem -pubout -out pubkey.pem


#To remove the pass phrase on an RSA private key:
 openssl rsa -in key.pem -out keyout.pem

#To encrypt a private key using triple DES:
 openssl rsa -in key.pem -des3 -out keyout.pem

#To convert a private key from PEM to DER format:
 openssl rsa -in key.pem -outform DER -out keyout.der

#To print out the components of a private key to standard output:
 openssl rsa -in key.pem -text -noout

#To just output the public part of a private key:
 openssl rsa -in key.pem -pubout -out pubkey.pem

#Output the public part of a private key in RSAPublicKey format:
 openssl rsa -in key.pem -RSAPublicKey_out -out pubkey.pem