An interesting and free source of SAP trainings you can find on OpenSAP at https://open.sap.com/courses. The subjects covered are various and are useful for administrators, application consultants, developers, technology consultants, etc… You can use these to extend your knowledge and also to get familiar with the newest technologies, so I really recommend to have a look on them, for sure you will find something interesting.
For attending any courses on the site, you only need to create an account, no S-user account is necessarily required.
Each course contains also an exam that you can optionally take. If this is successfully passed you will also receive a record of achievement certificate, but this exam can only be taken while the course is still current, while if you just participate at the course, you will receive a confirmation of participation certificate. It is true that the certificates do not have much of a value, but still it is a good confirmation that you understood the content of the course presented and also motivates you to go through it.
There is also an application for Android available that you can use to access the courses, you can download it from here:
As I am sure you noticed, lately the Support Portal from SAP or newer the Launchpad has changed so many times. Probably for many of you it is still not clear where to find stuff you need, so I thought it would be a good idea to share the location for most important areas a Basis admin would need. This page will be changed with new items regularly, so keep an eye on it from time to time ;).
Sometimes the Portal might experience problems and might not be available, other times there are some planned activities that require downtime and the portal cannot be accessed. There is one link where you can check the service availability of the site, so you can access this link if you experience any problems, or more convenient, you can also subscribe to this page so you are notified:
If you want to find any information related to a product starting from KBAs, blog articles, forum questions, guides, archives to use for install/upgrade you can do a search for it here:
Path: One Support Launchpad -> Software Downloads -> SUPPORT PACKAGES & PATCHES -> By Category -> Additional components -> UPGRADE TOOLS -> SL TOOLSET -> SL TOOLSET 1.0 -> ENTRY BY COMPONENT -> SOFTWARE UPDATE MANAGER (SUM) -> SOFTWARE UPDATE MANAGER 1.0 or SOFTWARE UPDATE MANAGER 2.0 -> SUPPORT PACKAGES AND PATCHES -> Choose your package depending on your platform
SWPM
Path: One Support Launchpad -> Software Downloads -> SUPPORT PACKAGES & PATCHES -> By Category -> Additional components -> UPGRADE TOOLS -> SL TOOLSET -> SL TOOLSET 1.0 -> ENTRY BY COMPONENT –
> SOFTWARE PROVISIONING MANAGER -> SOFTWARE PROVISIONING MGR 1.0 -> Choose the required SWPM tool
SPAM/SAINT package
Path: One Support Launchpad -> Software Downloads -> SUPPORT PACKAGES & PATCHES -> By Category -> Additional components -> UPGRADE TOOLS -> SL TOOLSET -> SL TOOLSET 1.0 -> Choose your needed packages depending on your SAP release
LMSERVICE
Path: One Support Launchpad -> Software Downloads -> SUPPORT PACKAGES & PATCHES -> SAP TECHNOLOGY COMPONENTS- >SAP SOLUTION MANAGER -> SAP SOLUTION MANAGER 7.1 -> ENTRY BY COMPONENT -> SOLUTION MANAGER JAVA STACK -> SOLMANDIAG 710
Generate developer keys
Direct link: https://launchpad.support.sap.com/#/sscr/developers/my One Support Launchpad -> Software Change Registration On premise -> Developers, Objects
SAP Bundle Patch
Path: One Support Launchpad -> Software Downloads -> Databases -> Database and Database Patches -> Oracle -> Oracle Patch and Software Downloads -> select your Oracle release -> select your OS type from the drop down list
IGS
Path: One Support Launchpad -> Software Downloads -> SUPPORT PACKAGES & PATCHES -> SAP Frontend Components -> SAP IGS -> select your release and here you also have SAP IGS HELPER APPLICATIONS
Guide Finder
Path: https://help.sap.com/viewer/index => at search all box choose product hierarchy and choose the product for which you would like to have the guide
(For Basis probably the most useful will be the category: SAP NetWeaver)
One example of direct link for Netweaver 740 => https://help.sap.com/viewer/p/SAP_NETWEAVER_740
SAP Cryptographic library
Path: One Support Launchpad -> Software Downloads -> SUPPORT PACKAGES & PATCHES -> By Category =>
SAP CRYPTOGRAPHIC SOFTWARE => SAPCRYPTOLIB
Kernel
Path: One Support Launchpad -> Software Downloads -> SUPPORT PACKAGES & PATCHES -> By Category => MISCELLANEOUS COMPONENTS => SAP KERNEL => Choose the needed kernel by Unicode, version, OS
B2B components for PI
Path: One Support Launchpad -> Software Downloads -> SUPPORT PACKAGES & PATCHES -> By Category => SAP NETWEAVER AND COMPLEMENTARY PRODUCTS => PI B2B ADDON 1.0 => REQUIRED COMPONENTS OF OTHER PRODUCT VERSION => JAVA COMPONENTS REQUIRES => PI SFTP PGP ADDON 1.0 – JAVA COMPONENTS => choose PGP MODULE 1.0 (for PIB2BPGP) and
SFTP ADAPTER 1.0 for PIB2BSFTP
It looks like the SAP Bundle Patch 12.1.0.2.170418 – 201705 contains some bugs. At least for me, this was noticed in a Solaris Intel environment on a Solution Manger 7.1 and a PI 7.4 system.
After a week or so since the installation, the database was growing unusually fast. Checking the top growth segments in the Solman database, the object responsible for this was the LOB segment corresponding to the column LAST_TEST in table MES_DB_AGGREGATE. In the productive system this was growing with more than 20GB/per day. But let’s see in case you also encounter such rapid growth of the database how can you track what is causing this and since when?
As a first step I would propose to check with the help of tcode ST04 or DBACOCKPIT the top growth of segments via: Space -> Segments -> Overview. Here you can check the top growing segments per day/week/month:
Also here at the tab Top sizes you can check the objects that occupy the most space.
To see how did the growth of a particular segment developed lately, you can check its history growth by going to Space -> Segments -> Detailed Analysis -> Fill in the segment name -> OK
Here you can have a look on how this segment grew on days/weeks/months:
The quickest workaround was to reorganize the responsible segment, in order not to get an overflow of the database.
The reason for this growth was caused by the abnormal behavior of the Oracle SMCO process that makes some LOB segments to extend indefinitely, bug that comes together with the instalation of the bundle patch. To summarize in a few words SMCO (Space Management Coordinator) performs proactive space allocation and space reclamation and coordinates the following space management tasks: tablespace-level space pre-allocation, securefile lob segment pre-extension, temporary segment space reclamation, securefile lob segment space reclamation. There is a parameter that enables/disables the SMCO background process: _ENABLE_SPACE_PREALLOCATION. By default this feature is turned on, so the value is by default set to 3, that means that space preallocation is active for all auto extensible datafiles and securefile segments. Another possible value is 1 and this means that pace preallocation is done only for the datafiles. If you want to quickly get rid of this problem, a temporary solution would be to completely disable this feature and set the parameter to 0:
ALTER SYSTEM SET “_ENABLE_SPACE_PREALLOCATION” = 0;
The good news is that the parameter is dynamic, so no restart is required in order for this to get activated.
A strange behavior of LOB segments growing too large I experienced also on a PI system 7.4 for the LOB segments corresponding to tables BC_MSG and SXMSCLUP.
For more information about LOB segments, you can check out my other blog post: Introduction to LOBs
If you are planning to upgrade your Solution Manager to 7.2 release, you have to plan first the update in your Maintenance Planner tool. During this process there is one error that I encountered and that might be popping up for all Solution Mangers that have the add-on TDMS 4.0 installed.
The validation of the software packages in MP will fail with the error:
DMIS CONTENT 2011_1_700 is required to upgrade to DMIS CONTENT 2011_1_731 by SAP BASIS 7.31 – SAPK-731BHINSAPBASIS during the queue check
If you check out for more details provided in the logs you will find the following:
It seems like for SAP TDMS 4.0, the product transition cannot be calculated automatically by MP, so it has to be added as a manual step by you in the MP.
You can find a solution from SAP for this in the note 2367414. But unfortunately, the solution provided by SAP is not 100% clear or complete and an additional step has to be followed.
To be more clear lets review what are the steps that have to be selected in order to get rid of the above error.
First, begin a fresh transaction for the upgrade in MP:
Select Plan a Maintenance:
Select your desired target that is Solution Manager 7.2 and Confirm selection
Then select Install or Maintain an Add-on:
Select SAP Landscape Transformation 2, select SHC for ABA 731 and the desired support package level for this and confirm the selection:
Then what it is very important and it is not mentioned in the note, continue to choose again the Install and Maintain Add-on for selecting also the add-on SAP TDMS 4.0 => TDMS for ABA 7.31 and the desired support package level and confirm this selection as well:
Then continue with the selection as normal, in MP and the validation should go successfully now.
By the way, if you are wandering, the order that you select the two additional add-ons does not matter.
But in case you also have the add-on DMIS EXT 2007_1_700 installed you might get as bonus an additional error for it too: DMIS EXT 2007_1_700 is required to upgrade to DMIS EXT 2007_1_731 by SAP BASIS 7.31 – SAPK-731BHINSAPBASIS
So in this case, you will also have to select in MP, additionally from what we have talked about at the Install and Maintain an Add-on section also the SAP TDMS EXT BPL 3.0 the TDMS for ABA 7.31 and the wanted SP level. This error is explained more in the note 2417816.
For more details, please consult the offcial SAP documenation:
Basically, the alerts are being generated because of some wrong setting in the auto-reaction method that is launched in the background, when job SAP_CCMS_MONI_BATCH_DP is running. But it is not necessary that the background job is canceled to get this error message.
The steps to find why this errors appear and solve them consist on these steps:
1. check RZ21 -> Method definitions -> display overview
2. Search for all methods with the keyword “mail”
3. For every of the existing methods that contains this word, check:
3.1. At the “Control” tab, ensure that the option “Periodically in dialog process” is set at the “Execute method” section.
3.2. At the “Parameters” tab, ensure that the SENDER option is correctly set in UPPER CASE letters, with a valid user that exists on client 000 and finally with an email address assigned.
This means that the steps above should be checked every time we delete a user from client 000, to make sure that there is no user assigned to any mail related auto-reaction method. The sudden appearance of this error could be cause by deleting such user.
The EM upgrade is cannot be done by upgrading the agent on top of the old version. The upgrade consists on moving the EM to a backup folder and do a fresh installation of the wanted version and then adjust the configuration to the already existent one.
Here are the steps that should be followed for an upgrade of EM on Solution Manager SPS 12 or higher. In my case my source version was EM 9.1.5 and the target one 9.7 and the environment Solaris Intel.
There are three packages that have to be downloaded:
1. The WILY INTROSCOPE ENTPR MGR 9.7 that can be found in this location:
https://launchpad.support.sap.com/ => Software Downloads => Support packages and patches => By Category => SAP Technology Components => SAP Solution Manager => SAP Solution Manager 7.1 => Entry by component => WILY INTROSCOPE => WILY INTROSCOPE ENTPR MGR 9.7 => Introscope Enterprise Manager 9.7 for SAP SP01
It is very important, that you choose the package for your respective OS type. But in case you do not find your OS from the dropdown list, you have to choose the OS independent version. But there is a catch, the OS independent package does not have a bundled Java VM, like the others do. So in this case you have to, either have the corresponding JAVA version required for your EM already available on the machine, either you download the SAPJVM in order to have the required Java VM while performing the installation.
In such case you can download the SAPJVM from:
https://launchpad.support.sap.com => Software Downloads => Support packages and patches => By Category => SAP NETWEAVER AND COMPLEMENTARY PRODUCTS => SAP NETWEAVER => SAP NETWEAVER 7.4 =>
ENTRY BY COMPONENT => AS JAVA EXTENSIONS => SAPJVM6
2. Wily Introscope enterprise management modules:
https://launchpad.support.sap.com/ => Software Downloads => Support packages and patches => By Category => SAP Technology Components => SAP Solution Manager => SAP Solution Manager 7.1 => Entry by component => WILY INTROSCOPE => WILY INTRO EM MGMT MODULES 9.7 => WILYISMM01P_3-80001009.ZIP (Introscope9.7customizationSP01 forSAP(Management Modules))
After the files have been uploaded to server, create special directory where to store the files and from where you can start the upgrade/installation from:
mkdir EM_upg_dir
ls
WILYISEM01_0-80001135.zip
WILYISMM01P_3-80001009.zip
eula.txt
osgiPackages.v9.7.1.16.unix.tar
unzip the files:
unzip WILYISEM01_0-80001135.zi
tar -xvf osgiPackages.v9.7.1.16.unix.tar
ls
@LongLink ca-eula.txt osgiPackages.v9.7.1.16.unix.tar unsigned
WILYISEM01_0-80001135.zip ca-eula.zh_CN.txt installer_console.log osgiPackagesID.txt version.txt
WILYISMM01P_3-80001009.zip ca-eula.zh_TW.txt introscope9.7.1.16otherUnixSAP.jar server
ca-eula.ja.txt eula.txt lgpl.txt signed
ca-eula.ko.txt installer.properties lib source
Prepare the files for the installation:
1. eula.txt
accept the agreemant by changing: LGPL=accept
2. ca-eula.txt
accept the agreemant by changing: CA-EULA=accept
3. installer.properties => check what the current parameters are for EM and make the changes in the file:
– the command, if you took the OS dependent package:
cd EM_upg_dir
/java -classpath ./introscope9.7.1.16otherUnixSAP.jar install
Silent installer completed with status of: SUCCESS. For more details, consult install logs at: /usr/sap/ccms/wilyintroscope/install. Please wait for the command prompt to return...
– the command if you have the OS dependent package:
in file /config/IntroscopeEnterpriseManager.properties. If you want to keep the already collected metrics move these folders from the old to the new EM.
# The directory that the EM will use for data storage
introscope.enterprisemanager.smartstor.directory=data
introscope.enterprisemanager.smartstor.directory.archive=data/archive
– The Introscope traces are stored in a folder that is configured with properties: # The directory that the EM will use for trace storage
introscope.enterprisemanager.transactionevents.storage.dir=traces
If you want to keep the already collected traces move this folders from the old to the new EM.
– Manually transfer any custom management modules (located in /config/modules) from the old to the new location. But do not overwrite newly installed standard SAP modules with older versions.
– Manually transfer any customization made in the old version of EM. These customization can be found in the files:
IntroscopeEnterpriseManager.lax or EMService.conf /config/IntroscopeEnterpriseManager.properties /config/em-jetty-config.xml /config/users.xml /config/users-sap.xml /config/domains.xml, /config/apm-events-thresholds-config.xml /config/loadbalancing.xml
Do not replace the new configuration files with the old copies since you then may loose any newly introduced properties! Instead, copy and paste any customization from your old files.
– Copy all certificate files used for single sign-on from Solution Manager. You find them in /sap/TrustedCerts.
– For monitoring capabilities of SAP Solution Manager copy file <EM_HOME>/config/agentclusters.xml from the old to the new location.
– Also for monitoring capabilities of SAP Solution Manager copy files /sap/SID.e2emai.properties (see SAP note 1751225).
– If you have a full Introscope license copy this too from folder /license.
– If Emergency Monitoring feature available starting with Introscope version 9.7 is activated copy files /sap/emon/*.
6. EM_HOME/ has to be given write access for SAP group sapsys from default 755 to 775
7. Start EM:
./EMCtrl.sh start
./EMCtrl.sh start: Starting Enterprise Manager...
(Please wait a few minutes for initialization to complete.)
Please check log file for more details.
8. check status
./EMCtrl.sh status
./EMCtrl.sh status: Enterprise Manager (pid XXXX) is running
8. In Solution Manager Basic Configuration Step 3.1, (Configure CA Introscope) Reload the installation. It will detect the new version.
9. After installing a new EM you have to prepare the EM for pushing monitoring metrics to Solution Manager running activity “Push DPC Configuration to CA Introscope” again.
ISagent upgrade
After the upgrade of EM, also the ISagent can be upgraded.
You can check the version of ISagent from system information from Java start page:
You can see that there are two ISagent versions are deployed on Solman, which can be a little bit confusing. But this is not only correct, but also the recommended scenario from SAP.
The IS agent version deployed on the managed system when performing the managed system configuration is dependent of the java version of the managed system and the IS agent version deployed on the Solman.
For Java 1.4 the IS agent 8.X will be deployed. If there is no ISAgent_MIN_J5 deployed on the Solman the IS agent 8.X is also deployed for systems running on Java 1.5 and 1.6. Although this scenario works, it is recommended to use IS agent 9.X for java systems that are running Java 1.5 or higher.
If both IS agent 8.X and IS agent 9.1.X (ISAgent_MIN_J5) are deployed on the Solman as in our case, then the managed systems running on Java 1.5 or higher the IS agent 9.X is deployed. This is the scenario recommended by SAP.
It is possible to have and IS Agent version 8.X deployed in parallel to the Solman IS agent 9.X, but it is NOT possible to have multiple IS agent 9.X versions deployed on the Solman Java stack.
The IS agent version needs to be lower or equal to the EM that it is connected to, that is also why we did first the EM upgrade.
The package that needed for the upgrade can be downloaded from: https://launchpad.support.sap.com/ => Software Downloads => Support packages and patches => SAP Technology Components => SAP SOlution Manager => SAP Solution Manager 7.1 => Entry by component => Solution Manager Java Stack => WILY INTRO AGT 9.7 MIN J5VIASM => Patch for WILY INTRO AGT 9.7 MIN J5VIASM SP01
First enable the Maintenance Mode in the Agent Administration, deploy IS agent with the help of SUM tool. The upgrade will require a short downtime, as Java stack will be restarted during the process. After the successful deployment, disable the Maintenance Mode in the Agent Administration.
If you have worked with Introscope EM, probably you have also faced some sort of a problem related its performance. For example, I had the situation that the EM would randomly stop itself, apparently for no reason. The version on which I had this error was 9.5.
First, I checked the logs and I could find this error in the em.log file:
In my case, I increased it from 2 Gb to 4 Gb and everything got solved out after that.
Unfortunately things do not go that smooth all the time, so what happens when EM does not start with a certain amount of configured memory? For example for version 9.7, I did the exact changes and EM would not start anymore after this, generating the following errors:
./EMCtrl.sh stop: Enterprise Manager stopped
Invalid initial heap size: -Xms4096m
The specified size exceeds the maximum representable size.
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.
Or another error:
./EMCtrl.sh status: Enterprise Manager stopped
Invalid maximum heap size: -Xmx4096m
The specified size exceeds the maximum representable size.
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.
Before making any changes to the java heap memory, I recommend that it is tested how much memory can be allocated to your JVM, especially that each EM runs with different version of JVM.
The command that you can test this is :
where NNNN is the amount the momory that you want to do the test with.
In case the value is accepted/correct it will return you default java environment info, otherwise it will throw in an error. You can test both initial and maximum heap size or just one of them.
Here are a few examples, so you can make an idea:
/usr/jdk/instances/jdk1.7.0_79/jre/bin/java -Xms3072m -Xmx3072m -version
java version "1.7.0_79"
Java(TM) SE Runtime Environment (build 1.7.0_79-b15)
Java HotSpot(TM) Server VM (build 24.79-b02, mixed mode)
/usr/jdk/instances/jdk1.7.0_79/jre/bin/java -Xms4096m -Xmx4096m -version
Invalid initial heap size: -Xms4096m
The specified size exceeds the maximum representable size.
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.
Now that we started to work on configuration files of EM, let’s see what are its most important directories and related files.
Main directories:
EM_HOME directory => by default, the home directory of EM, depending on the version, could be for 8.X /usr/sap/ccms/wilyintroscope and for 9.X and 10.X /usr/sap/ccms/apmintroscope. We can find here the configuration file:
Introscope_Enterprise_Manager.lax => the heap memory size can be modified from here, as we saw earlier
logs => contains the logs of EM where you can find quickly information about the agent state:
IntroscopeEnterpriseManager.log => you can find here information about the EM and Java VM version, in the format below:
3/10/15 05:55:20.231 PM CET [INFO] [main] [Manager] Introscope Enterprise Manager Release 9.5.6.0 (Build 956044)
3/10/15 05:55:20.232 PM CET [INFO] [main] [Manager] Using Java VM version "SAP Java Server VM 1.6.0_91" from SAP AG
3/10/15 05:55:20.232 PM CET [INFO] [main] [Manager] Using Introscope installation at: /usr/sap/ccms/wilyintroscope/.
3/10/15 05:55:20.232 PM CET [INFO] [main] [Manager] CA Wily Introscope(R) Version 9.5
But also information about any problems with the stop/start is present here.
em.log => the most important and readable errors can be found here
perflog.txt => more detailed information related to the performance and sizing are permanently written here. After the restart a new file is written and the old information is kept in perflog.txt.previous
bin => directory where you can find the stop/start script
config => various configurations and customizations can be done from here:
IntroscopeEnterpriseManager.properties=> location of different directories and also many other configurations can be set in the file
apm-events-thresholds-config.xml=> different thresholds can be set, for example limits the number of agent connections
agentclusters.xml => file used for monitoring capabilities
license => license for EM
sap => contains SAP connection related files:
SolMan_SID.e2emai.properties => IS Push configuration file used to push Introscope Metrics to Solution Manager Monitoring and Alerting Infrastructure (MAI)
TrustedCerts => certificate files used for single sign-on from SolutionManager
I started to dig down for any documentation related to EM performance and I was surprised in a good way.
There is a very nice tool from SAP named EMPerflogAnalyzer that will analyze the EM performance based only on the the perflog.txt file.
Here is how the tool looks like:
The checks that are done concern the following topics:
Number of Metrics
Number of Agents
Java Heap Space
Harvest Duration
Smartstor Duration (collected metrics)
For each point you get a stoplight color depending on whether you have a serious problem (red), just a warning (yellow) or if the configuration is correct (green). Also more details are available for each topic within the tool, guiding you on how to solve the problem.
There is an example on how a result of the tool analysis looked in my case:
The tool can be downloaded from the SAP note 2156320.
Today SAP announced that there are some problems with the sapxpg binary due to incompatibility in kernel 749.
This affects both SAP_BASIS releases 740 SP >=18 and 750 SP >=8.
SAPXPG is a kernel binary that is used for controlling external programs or the external commands within the same system or in a remote system. The trace for SAPXPG is dev_cp.
There could be two symptoms as per SAP note 2443381:
“1. You use the Unicode sapxpg and the asynchronous execution of external commands no longer works.”
=> in this case for:
– kernel 745, sapxpg_uc has to be patched to PL >=416
– kernel 749, sapxpg has to be patched to PL >=210
“2. You have SAP_BASIS 750 or 740 and SAP Kernel 749, and there are problems with the execution of external commands that have non-ASCII charactes in the parameter string or in their output. These characters are not transferred correctly.”
=> in this case for both 745 and 749 kernels the note 2443381 must be implemented.
But as there are also some other minor problems detected with SAPXPG, it would be best if you update directly to sapxpg_uc PL 510 for kernel 745 and sapxpg PL 310 for kernel 749.
In order to find out the version of sapxpg you can execute:
./sapxpg -VERSION
I recommend that you subscribe to the SAP news page in order to be up to date to the latest kernel related announcements: https://wiki.scn.sap.com/wiki/display/SI/SAP+Kernel%3A+Important+News
More information can be found in notes:
2443381 – Problems during execution of external commands as of SAP_BASIS 740
2479738 – Connection test for sapxpg or sapxpg_uc fails
What exactly is a LOB? LOB is the acronym for Large OBject. A table can have a column of the type LOB, this column holds unstructured data like documents, images, spreadsheets, xml files … When you create a LOB column for a table, you are actually creating a separate LOB segment. And when creating an LOB segment, also a LOB index is created together with it, that helps to find the data of the LOB, which is stored in different places in a tablespace.
This is how the LOB segments look like:
SYS_LOBXXXXXXXXXXCXXXXX$$, where XXXX is a hexadecimal number
And the index name of the LOB segement:
SYS_ILXXXXXXXXXXXCXXXXX$$, where XXXX is a hexadecimal number.
The hexadecimal numbers for both the LOB segment and the LOB index are the same.
There are several types of LOBs that can be found in the Oracle database:
BLOB: Binary LOBs
CLOB: Character LOBs
NCLOB: National Character LOBs
BFILE: Binary File
But in ABAP dictionary the datatype represented by LOBs are:
RATRING BLOB
STRING CLOB
LOBs can be stored as basicfiles or securefiles. Basicfiles are the old way of storing these segments, the recommended way of storing LOB segments is now securefile. The main reasons for this would be performance, additional features and security. So it is really recommended in case you still have LOB segments stored as basicfiles, to convert them into securefiles. This can be done easily, as a reorganization option via brtools.
It is possible to check the LOB segments that are not stored as secure LOBs via the command:
SELECT DISTINCT TC.TABLE_NAME,
TE.SECUREFILE
FROM
DBA_TAB_COLUMNS TC, DBA_TABLES T, DBA_LOBS TE
WHERE TC.TABLE_NAME = T.TABLE_NAME and TC.TABLE_NAME = TE.TABLE_NAME and TE.TABLE_NAME = T.TABLE_NAME
and TC.OWNER LIKE 'SAP%' and
(TC.DATA_TYPE = 'BLOB' OR TC.DATA_TYPE = 'CLOB') and SECUREFILE = 'NO';
These segments can be reorganized as secure LOBs with brtools via this path:
In Oracle 11 and 12 there is also a parameter name db_securefile that specifies how should a LOB segment be created by default.
These are the possible values according to Oracle documentation:
” NEVER
Any LOBs that are specified as SecureFiles are created as BasicFiles LOBs. All SecureFiles-specific storage options and features (for example, compress, encrypt, deduplicate) will cause an exception. The BasicFiles LOB defaults will be used for storage options not specified.
PERMITTED
LOBs are allowed to be created as SecureFiles.
PREFERRED
All LOBs are created as SecureFiles unless BASICFILE is explicitly specified in the LOB storage clause or the tablespace is a Manual Segment Space Management tablespace. When PREFERRED is set, cases where BASICFILE would otherwise be inherited from the partition or column level LOB storage are ignored; the LOBs will be created as SecureFiles instead.
ALWAYS
Attempts to create all LOBs as SecureFiles LOBs but creates any LOBs not in an Automatic Segment Space Managed (ASSM) tablespace as BasicFiles LOBs, unless SECUREFILE is explicitly specified. Any BasicFiles LOB storage options that are specified will be ignored and the SecureFiles LOB defaults will be used for all storage options not specified.
IGNORE
The SECUREFILE keyword and all SecureFiles options are ignored “
But SAP mentions in the note 1426979 for Oracle 11g only these options: permitted, never, always and ignore and clearly states that for SAP no other value is allowed, except “permitted”, that is also the default value. This means that a new created LOB, that has no specific options added under Oracle 11g, will be created by default as basicfile and this can be converted to securefile anytime.
But what about Oracle 12c? The problem is that SAP has not updated this information for 12c, but from what I know, is that the new default for 12c is the value PREFFERED which means that LOBs are now created as securefiles directly, if there is nothing else specifically mentioned in the creation clause and it is also the only accepted value by SAP for Oracle 12c.
From my experience I had a lot of situations when LOB segments increased very large and had to be reorganized. I recommend that these are regularly checked and reorganized when feasible. Here are some commands that will for sure help during the process. And remember, do not panic if after the reorganization you will not find that LOB segment in the database, this is recreated during the reorganization with another name.
How to check out the LOBs from a table and the mapped columns:
select SEGMENT_NAME,owner,table_name,column_name from dba_lobs where TABLE_NAME='table_name';
Vice versa you can also check what table belongs to a lob segment:
select owner, table_name, column_name FROM dba_lobs WHERE segment_name = 'lob_segment';
How to check the size of the LOB:
select sum(bytes)/1024/1024 from dba_extents where segment_name='XXXXXXX';
How to get an approximation of how much really does a LOB segment occupy and check if it fragmented:
563359 – Performance optimization for tables with LOB columns
2384534 – LOB conversion and table compression with BRSPACE 7.40 for Oracle 12c
2142497 – What to do for large lobsegment on Oracle database?
500340 – FAQ: LOBS
1426979 – Oracle 11g: SecureFiles – The new way to store LOB data
For some technical reasons, recently I had to rollback the bundle patch Oracle Database SAP Bundle Patch 12.1.0.2.170418 – 201705. I followed exactly the steps from the guide, but when trying to start the database back, after the deinstallation, I got this error:
SQL> startup
ORACLE instance started.
Total System Global Area 1.4596E+10 bytes
Fixed Size 6952656 bytes
Variable Size 4362078512 bytes
Database Buffers 1.0201E+10 bytes
Redo Buffers 26599424 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x3BD887D18
Process ID: 21912
Session ID: 301 Serial number: 50635
The solution to this problem can be found in the note 2392622. To summarize it, the self deadlock will be solved by adding the fix control ‘9550277:ON’.
Note for more information:
2392622 – ORA-01092 and ORA-04024 after SBP deinstallation when trying to startup database to execute rmsbp
