Blog posts

SBWP: You do not have a sender address in the chosen communication method

While sending an email from SBWP, for example for testing if the SMTP is well configured, the below error is given:

When looking to see more details at display log book we can check the error:

As you might think this error is not generated because the user who is sending the email does not have the email address available in SU01. But this is not the case, even if this is not mentioned in the communication area of the user, the sender address will appear as USERNAME@DOMAIN.

The problem is that the domain is not defined. This can be added in SCOT -> Settings -> Default domain and add your company domain name.

After that you can retry resending the email with success from SBWP.

How to activate an index on the DB level

Some indexes exist only created in ABAP dictionary. But in some situations, if specifically indicated by SAP, these can be also activated on the database level. For example this would be one scenario, described in the note 2489084.

What are the necessary steps to activate an index at DB level?

Go to SE11 enter the name of the table of which the index belongs -> Display

Then on Indexes button -> pick the corresponding index

And you will see that the index is marked as a “no database index” and under it states “Index does not exist in database system ORACLE”

Go to change and select instead “Index on all database systems”

A transport request will have to be created for this change that will record the modifications made.

After this option is saved, the index will become inactive, go to the same SE11 transaction and activate it

Then go to SE14, enter the name of the corresponding table -> Edit -> Indexes -> Select the wanted index

You will also see here the message “Does not exist in the database”. At the processing type, select Background, as this will be a time consuming action and then the button Create database index.

You can monitor the progress of this action in SM37:

After the job is completed you can see that the index exists now also created on the DB side:

In order to pass the modification into the next systems go to SE09, release the transport and then import the TR in the target systems. No other action will be needed on the target system.

The following entries in table ‘TAORA’ do not represent existing DB containers: [TABART, DBSPACE]

During SUM upgrade in the extraction phase PREP_INIT/INIT_CNTRANS_PRE the following error was encountered:

The following entries in table ‘TAORA’ do not represent existing DB containers: [TABART, DBSPACE]

DODS PSAPDATODS

The following entries in table ‘IAORA’ do not represent existing DB containers: [TABART, DBSPACE]

DODS PSAPDATODS

 

 

 

 

 

 

 

 

 

 

From the description of the error we can draw the conclusion that the TABART DODS is assingned to the tablespace PSAPDATODS, that does not exist.
I checked at database level and all tablespaces were present, including PSAPDATODS:

sqlplus / as sysdba
select TABLESPACE_NAME from USER_TABLESPACES;
TABLESPACE_NAME
------------------------------
PSAPDAT
PSAPDAT740
PSAPDATFACT
PSAPDATODS
PSAPDATUSR
PSAPTEMP
PSAPUNDO
SYSAUX
SYSTEM

Also in SAP all of them could be visible in table TSORA:

 

 

 

 

This sounds very strange, so let’s also verify the SUM logs to check exactly what it checks:
The tool runs 3 scripts (that can be found in /usr/sap/SID/SUM/abap/control/dbs/ORA): SELTAIA.ORA, TAIORA.SAP that list the correspondents between TABART and tablespaces and SELDBS.ORA that lists the name of available tablespaces. The output of the first 2 scripts seems ok but SELDBS.LOG, the log from SELDBS.ORA does not display all the tablespaces available in the database:

># cat SELDBS.LOG

tp_exec_statement: select TABLESPACE_NAME from USER_TABLESPACES
DBS>>> PSAPDAT
DBS>>> PSAPDAT740
DBS>>> PSAPDATFACT
DBS>>> PSAPDATUSR

tp_exec_dbscript: 1 statement(s) successfully processed.

The reason why the output is different, is because SUM executes the sql statement under user schema owner, which lists a different output because in this case the database specific preparations mentioned in the SUM upgrade guide haven’t been checked.

The resource quotas of Oracle user SAP<SCHEMA-ID> or SAPSR3 have to be checked with the statement:

select * from dba_sys_privs where grantee = 'SAPSR3/SAP<SCHEMA-ID>';

As this does not exist, it has to be granted:

grant unlimited tablespace to sapsr3/SAP<SCHEMA-ID>;

After the grant was given the error was solved, SUM tool could see all the available tablespaces and jumped to the next step.

 

 

Activate SAP* user in a SAP Java stack system

If you are in the situation that you do not remember the password of any SAP user on a Java stack system you can activate the SAP* user. These are the steps that need to be followed:
The activation is done from configtool, that needs to switched to configuration editor mode:

And go to Configurations -> Cluster_config -> custom_global -> cfg -> services -> com.sap.security.core.ume.service -> Propertysheet properties

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hit the change button, as in the warning message, the system needs to be restarted while SAP* user is activated:

 

 

 

 

And here there are two configurations that we are interested in:

ume.superadmin.password – By default it is set to FALSE. In order to activate SAP* change it to TRUE
ume.superadmin.activated – If you do not remember the password of SAP* you can enter here a new one

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After the restart of the Java system you can login with SAP* user and the password set in the previous step.

Be aware that during the time SAP* is activated the login of the other users is deactivated. So after you unlock, change the password of your user, you will have to deactivate SAP* user back and of course restart the system again, so the changes would take effect.

How to export only some rows of a table using oracle data pump option

Oracle data pump (expdp/impdp) is a very known utility for exporting data from tables. But the utility has a variety of options available and it can also be used not only to export the entire table data but also to export restricted rows from a table, that are defined using a where clause for example.
Let’s say that we want to export the rows of table HRP001 that meets the following conditions:

OTYPE = Q
RSIGN = B
RELAT = 032
SCLAS = P

we create a parameter file tab_where.par that has the content:

tables=HRP1001 
#table name to be exported from
directory=tmp_dmpdirs
#oracle directory where the export file will be located
DUMPFILE=exp_HRP1001_part.dmp
#file name that contains the data exported
logfile=exp_HRP1001_part.log
#log file of the 
query=HRP1001:"where OTYPE='Q' and RSIGN='B' and RELAT='032' and SCLAS = 'P'"
#the query that defines the data required from the table
COMPRESSION=all
#compression option for the dump file

Then we execute the expdp command that calls the parameter file that we just defined:

expdp user/passwd parfile=tab_where.par

Export: Release 12.1.0.2.0 – Production on Tue Nov 14 15:43:51 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: user/******** parfile=tab_where.par
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.285 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported “HRP1001” 14.47 MB 584555 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/oracle/exp_tbls/exp_HRP1001_part.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at Tue Nov 14 15:44:07 2017 elapsed 0 00:00:16

To import the data from this table we use the impdp utility:

impdp user/passwd parfile=tab_where.par

SAP Bundle patch bug in 12.1.0.2.180417 – 201805 => ORA-03171: Recovery was terminated during query execution

My experience with the latest SAP Bundle Patches wasn’t very good. In a previous post I mentioned this problem: http://sapnotebook.com/lob-segment-growing-large-bug-sap-bundle-patch-12-1-0-2-170418-201705/.
And now there is another bug that I experienced: 24447296 – ORA-3171 in a Non-ADG Database (Doc ID 24447296.8).
This might also be happening to you if you installed one of these patches: SBP 201711, 201708 or 201802 for Oracle 12.1.0.2 and you have a UNIX environment.
The bug manifests right after installing one of these patches by filling the system with short dumps of the type ‘DBIF_RTAB_SQL_ERROR’ with short text SQL error 3171 when accessing table “VBDATA”.
And when analyzing the developer traces for the corresponding work processes, you will get something similar to this:

———————————————-
C Wed Apr 11 08:43:46 2018
C check2: OCIStmtExecute() failed with -1=OCI_ERROR
C SQL error 3171:
C ORA-03171: Recovery was terminated during query execution
C *** ERROR => Error 3171 in stmt_execute() from oci_execute_stmt(), orpc=0
[dbsloci.c 17449]
C {root-id=0050568F7B7D1ED88FAA2DC3B5D6D34C}_{conn-id=00000000000000000000000000000000}_0
C *** ERROR => ORA-03171 occurred at SQL stmt (parse error offset=0)
[dbsloci.c 17450]
C {root-id=0050568F7B7D1ED88FAA2DC3B5D6D34C}_{conn-id=00000000000000000000000000000000}_0
C Dump statement cache (after SQL error):
———————————————-

VBDATA is not the only table for which the dumps can appear, also the tables TST03, SMW3_BDOC2, BC_MSG, SXMSCLUR, IUUC_RL_DATA, SXMXCLUP, SOFFCONT1, /WATP/TMOBMRIA can be affected.
The error does not occur in all the systems that have these patches installed, this is most likely to happen in the systems where the above tables are very large and get accessed very often.
The tricky part is that the bug can manifest directly into production system, even if the patch was previously applied to DEV and QAS and no side effects appeared, because in production the load on the tables can be significantly higher.
After some time the error has been also documented by SAP in two notes, which I recommend to be read very careful and also to be checked from time to time for updates:

2633327 – ORA-03171 while accessing table in a Non Active Data Guard database (non-ADG)
2668860 – ORA-00600 [3020] with block type NGLOB: “Hash Bucket”

In the first versions of the note 2633327, it was stated that the bug would get fixed by applying some Oracle merge patch or by installing a newer bundle patch 201805.
But a newer update of the note states that you should definitely not install those mentioned patches, as you could get into even more serious problems, that can lead to an unrecoverable database!
At the moment there is not fix available and an update of the note is still waited to come.
So I would say think twice before installing any new bundle patch from Oracle as these can have a negative impact on your business jobs and can seriously affect the daily operation.

*** Update: On 15.08.2018 SAP updated the note 2633327 and several fixes are available depending your SBP installed. Read it carefully an apply the fix that matches your case.

ORA-00392: log of thread is being cleared, operation not allowed

In this post I will describe the scenario where you already did a restore followed by recovery and realized too late that the filesystem/filesystems that contain the online redologs of the database is too small. When you attempt to start the database, you will get the error that the online redologs do not have enough space to generate:

SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-19502: write error on file “/oracle/SID/mirrlogB/log_g14m2.dbf”, block
number 2861057 (block size=512)
ORA-27063: number of bytes read/written is incorrect
Solaris-AMD64 Error: 28: No space left on device
Additional information: 4294967295
Additional information: 1048576
ORA-19502: write error on file “/oracle/SID/mirrlogB/log_g14m2.dbf”, block
number 2865153 (block size=512)
ORA-27063: number of bytes read/written is incorrect
Solaris-AMD64 Error: 28: No space left on device
Additional information: 4294967295
Additional information: 1048576

These errors occur because the database is trying to write information into the redologs, in the mirrlog/origlog location, but cannot find the available space to do that.

SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-00392: log 6 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 6 thread 1: ‘/oracle/SID/origlogB/log_g16m1.dbf’
ORA-00312: online log 6 thread 1: ‘/oracle/SID/mirrlogB/log_g16m2.dbf’

Now, if you try to shutdown the database, add the neccessary storage in the mirrlog/orriglog FS, where the online redologs are located and after this retry to open the database, you will still get the error:

SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-00392: log 6 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 6 thread 1: ‘/oracle/SID/origlogB/log_g16m1.dbf’
ORA-00312: online log 6 thread 1: ‘/oracle/SID/mirrlogB/log_g16m2.dbf’

In mount state of the database, the status of the online redologs status can be checked:

SQL> select group#,status,member from v$logfile;

    GROUP# STATUS  MEMBER
---------- ------- ------------------------------------------
         6         /oracle/SID/origlogB/log_g16m1.dbf
         6         /oracle/SID/mirrlogB/log_g16m2.dbf
         5 STALE   /oracle/SID/origlogA/log_g15m1.dbf
         5 STALE   /oracle/SID/mirrlogA/log_g15m2.dbf
         4 STALE   /oracle/SID/origlogB/log_g14m1.dbf
         4 STALE   /oracle/SID/mirrlogB/log_g14m2.dbf
         3         /oracle/SID/origlogA/log_g13m1.dbf
         3         /oracle/SID/mirrlogA/log_g13m2.dbf
         2         /oracle/SID/origlogB/log_g12m1.dbf
         2         /oracle/SID/mirrlogB/log_g12m2.dbf
         1         /oracle/SID/origlogA/log_g11m1.dbf

You can see that there are some online redologs, those that could not fit anymore on the filesystem, that are in STALE status.

Clearing the redolog groups with problem will help in this case:

SQL> alter database clear logfile group 5;

Database altered.

SQL> alter database clear logfile group 4;

Database altered.

Checking the status of the redolog groups:


    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 UNUSED
         2          1 UNUSED
         6          1 CLEARING_CURRENT
         4          1 CLEARING
         5          1 CLEARING
         3          1 UNUSED

Check again:


SQL> select group#,status,member from v$logfile;

    GROUP# STATUS  MEMBER
---------- ------- ------------------------------------------
         6         /oracle/SID/origlogB/log_g16m1.dbf
         6         /oracle/SID/mirrlogB/log_g16m2.dbf
         5         /oracle/SID/origlogA/log_g15m1.dbf
         5         /oracle/SID/mirrlogA/log_g15m2.dbf
         4         /oracle/SID/origlogB/log_g14m1.dbf
         4         /oracle/SID/mirrlogB/log_g14m2.dbf
         3         /oracle/SID/origlogA/log_g13m1.dbf
         3         /oracle/SID/mirrlogA/log_g13m2.dbf
         2         /oracle/SID/origlogB/log_g12m1.dbf
         2         /oracle/SID/mirrlogB/log_g12m2.dbf
         1         /oracle/SID/origlogA/log_g11m1.dbf
         1         /oracle/SID/mirrlogA/log_g11m2.dbf

12 rows selected.

SQL> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 UNUSED
         2          1 UNUSED
         6          1 CURRENT
         4          1 UNUSED
         5          1 UNUSED
         3          1 UNUSED

Validation of deployment queue completed with error: No valid Maintenance Certificate found on AS Java with SID

While deploying any components for a Java system, in case there was no valid maintenance certificate installed, the validation of the deployment queue was only ending with status warning and it was possible to continue with deployment without being mandatory to install the certificate, like this:

 

 

 

 

 

 

 

But with the latest SUM versions, I am not sure since when exactly, but I think it is somewhere since some patch of SUM SP21 this has changed and the installation of the maintenance certificate cannot be skipped anymore and this error will show up: “Validation of deployment queue completed with error: No valid Maintenance Certificate found on AS Java with SID XXX. For more information, refer to SAP Note 1236587 … ”

 

 

 

 

 

 

So in order to pass the error a valid maintenance certificate is mandatory to be installed for the Java application server. How to do that? First generate a new certificate on SAP Support Portal:

https://launchpad.support.sap.com/ => System Operations and Maintenance -> License Keys -> Select installation number -> Select the respective SID from the list -> Select the current HW key, License type Licence Entitlement and press button Renew certificate or if there is already one that has a validity date that is not expired, press download button:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now that we have the license, we can also install it. This can be done from NWA -> Configuration -> Infrastructure -> Licenses -> Select the Maintenance certificate from the list -> Install from file button

And now we have an up to date maintenance certificate, retry the step from SUM, that should continue with success.

com.sap.engine.services.dc.cm.deploy.AllItemsAlreadyDeployedValidationException: ASJ.dpl_dc.003456 All batch items are marked as AlreadyDeployed because of Version check.

This post should be of interest for you if you want to deploy 3rd party drivers for the JDBC and JMS adapters within the com.sap.aii.adapter.lib.sda archive. I will not insist on the preparations that need to be done for this, but you can find the answer in this note: 1770304 – PI: Preparing the com.sap.aii.adapter.lib.sda for deployment. What I would like to discuss is the error that is showing up when the archive package will be deployed.
The deployment is done via SUM tool and the error appears during configuration phase:

[Error ]: The following problem has occurred during step execution: com.sap.sdt.util.diag.DiagException: Validation of deployment queue completed with error:
 com.sap.engine.services.dc.api.deploy.AllItemsAlreadyDeployedValidaionException: [ERROR CODE DPL.DCAPI.1031] AllItemsAlreadyDeployedValidationException.
 Reason: ASJ.dpl_dc.003456 All batch items are marked as AlreadyDeployed because of Version check.
 com.sap.engine.services.dc.api.deploy.AllItemsAlreadyDeployedValidaionException: [ERROR CODE DPL.DCAPI.1031] AllItemsAlreadyDeployedValidationException.
 Reason: ASJ.dpl_dc.003456 All batch items are marked as AlreadyDeployed because of Version check.
 com.sap.engine.services.dc.cm.deploy.AllItemsAlreadyDeployedValidationException: ASJ.dpl_dc.003456 All batch items are marked as AlreadyDeployed
 because of Version check.

 

 

 

 

 

 

If you already got this error, SUM tool has to be stopped. Then, SUM has to be configured to run in force-mode, this can be done by adding the two parameters:

/jspm/deployVersionRule = updateAll
/jspm/forceMode = true

in /sdt/param/jspm_config.txt file.

After the parameters were set, restart SUM and follow the same steps to deploy the drivers, this time the error should not appear anymore.
More information can be found in SAP note 1138877 – How to Deploy External Drivers JDBC/JMS Adapters, note that should be read actually before deploying such drivers. What you will not find in the note is the error that appears in case SUM is not started with this option.

SAP One Support launchpad availability

Do you often experience problems accessing SAP Portal? Yes, I know, we all like that, especially when it happens exactly when we need it the most and it usually does [sce emoji=”facepalm”/]. But maybe sometimes you are not sure where the problem resides, maybe you also have some problems with the internet connection…
I want to share that there is an official site where you can check the status of the Portal and in case there are some problems, these are announced here. If it is an unexpected issue or if it is a planned unavailability you can check here and maybe plan your activities accordingly. You also have the option to subscribe here for any updates, so you can receive automatically an email or even SMS to stay informed about any changes, which I find very convenient. Here it is:

https://status.support.sap.com/#