SAP SMQ2 Mass Queue Deletion

In some scenario or as per customer requirement, you need to delete all the Q from SMQ2. Manual deletion of Q is pain & it takes lot of time.

You can follow below method to truncate the table in safe method.

Table Name – TRFCQIN (SMQ2)

For Oracle

SQL>select count(*) from sapsr3.TRFCQIN;

COUNT(*)
———-
2517434

Cross verify the count from SMQ2 with Oracle Result.

SQL>truncate table SAPSR3.TRFCQIN;

From SAP

SE14 – TRFCQIN

Delete Data – Activate & Adjust Database.

Now your SMQ2 is empty.

SAP workprocesses can not connect to the database any more.

SAP Note 1519872 

Symptom

This note describes the configuration of an SAP installation with the SAP-pecific database user profile SAPUPROF.

Symptom:
SAP system does not start any more.
SAP workprocesses can not connect to the database any more.

SAP trace files contain errors similar to the following:
TRACE-INFO: 101:  [    dbsloci.,00000]  *** ERROR => CONNECT failed with sql error ‘28001’
TRACE-INFO: 102:  [     dev trc,00000]     set_ocica() -> SQL error code 28001
TRACE-INFO: 103:  [     dev trc,00000]    >oci_get_errmsg (con_hdl=0,rc=28001)
TRACE-INFO: 104:  [     dev trc,00000]     OCIErrorGet -> SQL error code: 28001
TRACE-INFO: 105:  [     dev trc,00000]     ORA-28001: the password has expired

Other symptoms:
You get ORA-28000 or ORA-28001 or ORA-28002 errors when you try to connect to the database (i.e. as database user SYSTEM).

Reason:

  • Oracle Database Release 11.2.0.X.
  • Database user profile SAPUPROF is not configured. Check this with the following query (for user SAPSR3):
    SQL> select profile from dba_users where username = ‘SAPSR3’;
  • With 11.2, passwords of database users must be changed every 180 days for security reasons.

For full solution, Please go through this note 1519872.

SAPSR3 user locked.

Recently we find an issue that while starting SAP via SAP MMC, work process stop in Ended status. We found in developer trace that it’s a database connectivity issue. In some cases connectivity from SAP to Database does not happen due to SAPSR3 user locked.

Steps to find the issue in Windows Environment.

1. Go to CMD – R3trans -d (it ended with return code 0012)

2. Read the trans.log & search if there any user lock issue found.

if YES then go to below procedure to resolved.

1. Stop the SAP process in SAP MMC.

1. go to sqlplus via “CMD – sqlplus / as sysdba

2. execute statement to check which user(SAPSR3) is locked.

select username, account_status from dba_users;

find the list of dba users with their status. if SAPSR3 user is locked then execute this below statement to unlock.

alter user SAPSR3 account unlock;

again execute to check whether it’s successful or not.

select username, account_status from dba_users;

if successful log out from SQL & execute “R3trans -d“. it finish with RC=0000.

START SAP & it will start.