Unix command to sort biggest size file in the directory

$du -sk * | sort -n

How to empty a Unix file system with cat /dev/null command dev null

First take the backup of existing file :

$cp dev_rd.log /sapcd/XVPdev


then empty the file

$cat /dev/null > dev_rd.log

still it was giving error : File exists.

It meant that noclobber option is set so gave command

$cat /dev/null >! dev_rd.log

and it worked

Removing semaphores shared memory keys with ipcrm

Sometimes we are not able to remove the shared memory keys with cleanipc 52 remove command, we can find the culprit this way :

ipcs | grep crdadm | awk '{printf("ipcrm -s %s\n", $2);}'

or if we already have the error like that

Dec 8 10:52:08 root@globiz63 logpipe[16010]: (start_app crd0v 52 crdadm 1): OsKey: 25238 0x00006296 Semaphore Key: 38 remove failed **** - errno = 1 (Not owner)


you may find the cultprit by

## ipcs | grep 0x00006296

it will give the ownere as well

now log in as the owner and give the command

## ipcrm -s 687 (687 is the semaphore ID)

it should go now.

Downloading from SAP no options for spreadsheet to choose different formats

As per our understanding, you need the below dialog box again to select any other format than Excel (in MHTML format) that you have choosen with "Always Use Selected format" checked:


Please follow the below link in order to do that :

http://help.sap.com/saphelp_nw70/helpdata/en/45/6e983a53ad487fe10000000a114a6b/content.htm

We tried on our end and this is working for us.

In our test scanario when we clicked on spreadsheet option it is not showing the other formats and directly taking to the path where to save the file:

In order to get the options again, we chose one of the line items that we need to export :

Now right click on the selected line item and select spreadsheet :

It will ask again for the selection screen with "Always Use selected format" option selected:

Please uncheck the option "Always Use Selected Format" and take the export.

Now next time onwards, it will always show the dialog box showing the formats in which you need to take the exports.

XVP Messaging adapter hung for FTP TIMOUT

There was a scenario where the Rail Cargo messages stuck in the XVP system and every time we need to take a system restart in order to enable the server to process the messages again.

We checked the communication channel there also the FTP timout was 60 sec. Obviously, the messages stuck due to the FTP Timout but after that the messaging server was not able to process any of the message.

Root cause and work around suggested by David from XI team to set the value of parameter messaging.system.queueParallelism.maxReceivers to 2 for SAP XI AF Messaging and SAP XI AF Core adapters in visual admin.

We checked the value of the same was 2 in server0 but was 0 in server1. Change raised to correct the same in server1

For more information on messaging parameters follow below link:

http://help.sap.com/saphelp_nwpi71/helpdata/en/59/25ee41c334c717e10000000a155106/content.htm

Relevant material :
messaging.system.queueParallelism.maxReceivers
Value type: Integer

Default: 0 (disabled)

This property specifies the maximum number of parallel worker threads per queue, based on the receiver fields of the processed messages. A value of 2, for example, will limit the amount of parallel worker threads for one receiver channel instance to 2, allowing the remaining configured worker threads to process messages for other receiver channel instances of the same adapter type.

DB13 update stats will fail with error

In this scenario update stats was never successful and failed with error :

BR0301E SQL error -2132 at location thr_db_connect-3, SQL statement:
'ENABLE THREADS'

we checked the brtools permissions in /sapmnt/BIP/exe and they were wrong

SAP Note 113747 - Permissions for DBA tools BR*Tools and SAPDBA provides permissions for brtools, they are as follows :

brarchive, brbackup, and brconnect have permission 4775:
-rwsrwxr-x ora sapsys ...
These can be started by the ora or the adm OS user.

brrestore, brrecover, brspace, brtools, and sapdba have permission 755:
-rwxr-xr-x adm sapsys ...
These should only be started by the ora OS user.

Missing Oracle client on Application Server

The Scenrio is that one of the application servers of PEQ is not starting. When we checked the database connection it is showing return code 12 with R3trans -d command.

trans.log shows that the version of oracle client can't be ascertained.

we checked the env with env command

and it shows that variable SHLIB_PATH contains value

SHLIB_PATH=/usr/sap/PEQ/SYS/exe/run:/oracle/PEQ/client/11x_64/instantclient

Here in this environment the oracle client is maintained for each SID separately but we verified the physical path of oracle client it is present in /oracle/client/11x_64/instantclient


so we temperarily changed the env to physical path and it worked

$setenv SHLIB_PATH /usr/sap/PEQ/SYS/exe/run:/oracle/client/11x_64/instantclient

$R3trans -d
This is R3trans version 6.22 (release 720 - 02.05.11 - 15:56:00).
unicode enabled version
R3trans finished (0000).

How to unlock any SAP user from Oracle level

First check the Schema user Id from the below command.


$select username, user_id, account_status from dba_users;

normally the schema user id is sapsr3 or sapr3 (in old versions) or sap(sid)

first display the user using below sql:


$select bname, mandt, UFLAG from sapsr3.usr02 where bname='JSINGH' and mandt=300;

The above query will display the UFLAG value for user JSINGH.
UFLAG value 64 means locked due by administrator and 128 means locked due to incorrect logon attempts. Unlocked user will have UFLAG value set to 0.

Now we will set the UFLAG value of user 'JSINGH' to 0 with below sql command:


$update sapsr3.usr02 set UFLAG=64 where bname='JSINGH' and mandt=300;

$commit;

Now try to login at SAP level, your user has been unlocked :)

File Transfer to OS level from within SAP

You may transfer file to OS level from within SAP using TCODe CG3Z and CG3Y where in CG3Z is used to transfer file from SAP to OS level and CG3Y is used to transfer file from OS level to SAP (Your own desktop)

Maximum number of Datafiles in Oracle

Oracle supports maximum 1023 number of data files to be added for extending table space.

Support Pack implementation in Dialog Mode

SP implementation in Dialog Mode will never time out. It doesn't depend upon the parameter rdisp/max_wprun_time.Even if it runs for 15 hours or more continuously.

start JSPM with changed EPS location

You may start JSPM with different EPS/in directory than default. There are two methods

1. You may set the parameter DIR_EPS_ROOT in DEFAULT.PFL and point the same to the directory you want.

2. You may start the JSPM with parameter :

./go '-config=/JSPM/inbox=/usr/sap/trans_rep/SPR7/P3'

The JSPM session will start with the current parameter only. It will point to the path given in above parameter and will upload the patches from the same path.

R3trans used for System Refresh activity

R3tans command can be very handy for System Refresh as we can take export of tables relevant to RFCs, Partner profiles, tRFC ports and distribution models :

command for export

R3trans -w exp.log AE1_export.ctl

example of AE1_export.ctl


export
client=100
file='expAE1100_tables.dmp'
select * from edipoa
select * from edipod
select * from ediport
select * from edp12
select * from edp13
select * from edp21
select * from edpp1
select * from filename
select * from filetext
select * from kcdrm
select * from kcdrs
select * from kcdsm
select * from t462a
select * from tbd00
select * from tbd00t
select * from tbd05
select * from tbd06
select * from tbd30
select * from tbda2
select * from tbdlt
select * from tede2
select * from vari
select * from varid
select * from varit
select * from TBLSYSDEST
select * from TVARVC
select * from CIF_IMOD
select * from TBLSYSDEST
select * from EDPPV
select * from EDPVW
select * from TBDBR
select * from TBDBRF
select * from edpar
select * from edpi1
select * from edmat
select * from edmms
select * from edpo1
select * from edpo3
select * from ediphone
select * from EDPST
select * from EDIPO2
select * from EDIPOACODPAG
select * from EDIPOF
select * from EDIPOI
select * from EDIPOX
select * from EDIPOXH
select * from EDIPOXU

export
file='expAE1_tables.dmp'
select * from crep
select * from crep_http
select * from crepdescr
select * from dbsnp
select * from devaccess
select * from e070l
select * from edbas
select * from edbast
select * from edcim
select * from edcimt
select * from edimsg
select * from edimsgt
select * from edma
select * from edmsg
select * from edview
select * from edviewt
select * from filenameci
select * from filetextci
select * from moni
select * from ndisystems
select * from osmon
select * from pahi
select * from prgn_cust
select * from rslogsysmap
select * from rzllitab
select * from ssf_pse_h
select * from ssf_pse_d
select * from t000
select * from t002
select * from t002t
select * from tbdls
select * from tbdlst
select * from tdevc
select * from tedst
select * from tedtt
select * from toaar
select * from tpfet
select * from tpfht
select * from edipo
select * from crep_rfc
select * from btcomset
select * from STRUSTCERT
select * from STRUSTSSL
select * from STRUSTSSLT
select * from tmsqlastwl
select * from tmsqnotes
select * from tmsqwl
select * from tmsqwlf
select * from HTTPURLLOC
select * from rspor_t_portal 
select * from EDPO_QUEUE



export
file='expAE1RFC_tables.dmp'
select * from RFC2SOAPS
select * from RFCADPTATTR
select * from RFCADPTRCVLOG
select * from RFCADPTSNDLOG
select * from RFCATTRIB
select * from RFCCHECK
select * from RFCCMC
select * from RFCDES
select * from RFCDESSECU
select * from RFCGO
select * from RFCLIST
select * from RFCSTXTAB
select * from RFCSYSACL
select * from RFCTRUST
select * from RFCTSTLOG
select * from RFCTSTTAB
select * from RFCTYPE
select * from RFCTA
select * from RFCDOC


After system refresh, we need to import the above exported tables. While importing we need to reverse the process :

command

R3trans -w imp.log AE1_import.ctl


example of AE1_import.ctl

remove
client=100
select * from edipoa
select * from edipod
select * from ediport
select * from edp12
select * from edp13
select * from edp21
select * from edpp1
select * from filename
select * from filetext
select * from kcdrm
select * from kcdrs
select * from kcdsm
select * from t462a
select * from tbd00
select * from tbd00t
select * from tbd05
select * from tbd06
select * from tbd30
select * from tbda2
select * from tbdlt
select * from tede2
select * from vari
select * from varid
select * from varit
select * from TBLSYSDEST
select * from TVARVC
select * from CIF_IMOD
select * from TBLSYSDEST
select * from EDPPV
select * from EDPVW
select * from TBDBR
select * from TBDBRF
select * from edpar
select * from edpi1
select * from edmat
select * from edmms
select * from edpo1
select * from edpo3
select * from ediphone
select * from EDPST
select * from EDIPO2
select * from EDIPOACODPAG
select * from EDIPOF
select * from EDIPOI
select * from EDIPOX
select * from EDIPOXH
select * from EDIPOXU

import
client=100
file='expAE1100_tables.dmp'

remove
select * from crep
select * from crep_http
select * from crepdescr
select * from dbsnp
select * from devaccess
select * from e070l
select * from edbas
select * from edbast
select * from edcim
select * from edcimt
select * from edimsg
select * from edimsgt
select * from edma
select * from edmsg
select * from edview
select * from edviewt
select * from filenameci
select * from filetextci
select * from moni
select * from ndisystems
select * from osmon
select * from pahi
select * from prgn_cust
select * from rslogsysmap
select * from rzllitab
select * from ssf_pse_h
select * from ssf_pse_d
select * from t000
select * from t002
select * from t002t
select * from tbdls
select * from tbdlst
select * from tdevc
select * from tedst
select * from tedtt
select * from toaar
select * from tpfet
select * from tpfht
select * from edipo
select * from crep_rfc
select * from btcomset
select * from STRUSTCERT
select * from STRUSTSSL
select * from STRUSTSSLT
select * from tmsqlastwl
select * from tmsqnotes
select * from tmsqwl
select * from tmsqwlf
select * from HTTPURLLOC
select * from rspor_t_portal 
select * from EDPO_QUEUE

import
file='expAE1_tables.dmp'


remove
select * from RFC2SOAPS
select * from RFCADPTATTR
select * from RFCADPTRCVLOG
select * from RFCADPTSNDLOG
select * from RFCATTRIB
select * from RFCCHECK
select * from RFCCMC
select * from RFCDES
select * from RFCDESSECU
select * from RFCGO
select * from RFCLIST
select * from RFCSTXTAB
select * from RFCSYSACL
select * from RFCTRUST
select * from RFCTSTLOG
select * from RFCTSTTAB
select * from RFCTYPE
select * from RFCTA
select * from RFCDOC

import
file='expAE1RFC_tables.dmp'

Table export import with R3trans directly from OS level

For exporting the tables:

command :

R3trans -w exp.log exp.ctl

example of exp.ctl:

export

client = 200

file = '/usr/sap/trans/data/export.dat'

select * from COEP

select * from BSIS
select * from ACCTCR
select * from COEP
select * from BALDAT
select * from MLIT
select * from MLPP
select * from MSEG
select * from MSTA
select * from NAST

select * from ONR0

select * from S033

select * from S700
select * from FAGLFLEXA
select * from CKIS
select * from KEKO




For importing the same table(s)

command :

R3trans -w imp.log imp.ctl

example of imp.ctl file :

import

file = '/usr/sap/trans/data/export.dat'

client = 200

Problems faced in Language Installation

There are 3 major parts of Language Installation

1) First Import the Language Package in 000 SMLT

2) Import the language part of all old Support packages in 000 client

3) Supplement the language in each client

We majorly faced the problem in step 2, where first we searched the location for all old support packages with

find . | grep "KGPHD*"

command

Secondly, as we were using A port, we started the import of language part of support package and logged off, this happens only in Dialog :) so the R3trans will work as the control is with SMLT import language part of support packages. This is not the case when we import the language "Import package" because it will show that the control has been transferred to R3trans in Action log.

Another issue we faced, there was import issue return code (16) while doing Import of language part of support package SAPK-603DFINSAPAPPL, actually this support package didn't exist in the system, this is a sub support package, we can find this using SE16 table PAT13:

the solution was in below link :

http://forums.sdn.sap.com/thread.jspa?threadID=1881224

quoting the solution:

"please check table PAT13 in SE16; you'll see that these packages are listed in column SEC_NAME. Please refer to the main package that it belongs to on the PATCH column - this is the main package file that you should look for in SAP MarketPlace and unpack in EPS/in. Then you can repeat the process, it'll find the missing Meta-Commandfiles and continue w/o problems.

By the way, please check note #1508122, it might be that you have the incorrect language package installed on your system. This note explain in details how to check and install the correct language package on your system."

Use find command in Unix to find the required files

During language installation, one of the step is "Importing of Language part of support packages after language installation".

It requires all the old support packages to be present in EPS/in (default) directory. But there were many support packages missing. Downloading was not the option as it required approval from Solution Manager and we don't have access for the same.

So, the only option left was to search where is the location of the missing support packages not present in /usr/sap/trans/EPS/in directory. Actually we were looking for CAR files.

The command that came handy was

find . | grep "KGPHD*"

we were in location /usr/sap/trans/EPS and we gave this command

ultimately we were able to locate the missing support packages in directory /usr/sap/trans/EPS/languages_stack_01_17

How to find job name from process ID (BI System Slow Analysis)

We used this scenario in term of BI system being slow :

Analysis steps :

1) Go to DB01 and find if there are any exclusive locks i.e. in HOLD _WAIT status

2) If yes find out the process ID and application server name of the locks in HOLD status.

3) Log on to the application server on which the process in Hold status is found and goto SM66

4) Find out the process ID against BTC and double click

5) Click on Job Information, it will give you the job name, you may find this on another application server as well but you need to enable below settings :

"For background processes, additional information is available for the background job that is currently running. You can only display this information, if you are logged onto the instance where the job is running, or if you choose Settings and deselect Display only abbreviated information, avoid RFC. In any case, the job must still be running".

For more information on this please goto below link:


http://help.sap.com/saphelp_nw70/helpdata/en/c4/3a6a6a505211d189550000e829fbbd/content.htm

6) Once you have found the job, you may give it to BI guys to relate the same to the Process Chain and kill the job / cancel the process chain.

Kernel upgrade procedure - Traditional Method

(Please note that this is not the recommended method as this involved exchanging of kernel directories which may omit many other dependant files. The best way to avoid any inconsistency is to overwrite the old kernel directory by extracting new kernel binaries in the same directory).

Step I : Make directory /sapmnt/(SID)/exenew with full permissions

Step II: Extract the following files in the newly created directory

SAPEXE
SAPEXEDB
DBATL

Step III: Change premissions for these three type of files in exenew

$Chown ora(SID):dba br*
$Chown root:sapsys saposcol
$Chown root:sapsys icmbnd

*all other files should be (sid)adm:sapsys


Step IV: Stop sap and saposcol

(sid)adm : stopsap
saposcol -k

Step V: Interchange the kernel directories

$ mv sapmnt/(SID)/exe exeold
$ mv sapmnt/(SID)/exenew exe

Step VI : Execute the following script as a root user

cd /sapmnt/(SID)/exe
./saproot.sh (SID)

Step VII: Start SAP

(sid)adm : startsap



Step VIII: run the following scripts as oracle user

su - ora(sid)
cd /sapmnt/(sid)/exe

now run the scripts from here only

sqlplus /nolog @sapdba_role (SID)
sqlplus /nolog @sapconn_role (SID)

after executing the scripts check the log files

Step IX: Log in as (sid)adm user and check R3trans

$R3trans -d

It should give return code (0000)


Now login in the system goto System -> status and check the kernel patch level.

Kernel Upgrade Procedure - Simplest

Steps :- for 701 systems
1. cd /usr/sap/common/exe
2. ./OperInfo.ksh down "SR 2298151 Kernel Upgrade"
3. vis_stopsap r3
4. Login with adm
5. saposcol –k
6. cd /sapmnt/
7. cp -R exe exe
8. cd exe
9. cp -R /usr/sap/trans/SPR5/KERNEL_701_69/R5_Kernel_Extracted/* .


Steps :- For 700 Systems(*M2 and *S1 systems)
1. cd /usr/sap/common/exe
2. ./OperInfo.ksh down "CRQ 92360 Kernel Upgrade"
3. vis_stopsap r3
4. Login with adm
5. saposcol –k
6. cd /sapmnt/
7. cp -R exe exe
8. cd exe
9. cp -R /usr/sap/trans/SPR5/S1KRNL/R5_700_Kernel/* .

Steps for changing the kernel files permission to root (in all systems)
1. login as root user
2. cd /sapmnt//exe
3. ./saproot.sh

Steps:- Checking new kernel (in all systems)
1. Login as adm
2. cd /sapmnt//exe
3. disp+work (Take Screenshot of this Screen)


Please check and validate backint permission
1.cd /opt/networker/bin
2. ls -l | grep backint and check permission of listed files
lrwxr-x--- root sys backint --> backint_hpux11_ia64_LGTsc19983_LGTsc28795_3.0.2
-rwsr-xr-x root sys backint_hpux11_ia64_LGTsc19983_LGTsc28795_3.0.2

3. if sticky bit is missing for backint_hpux11_ia64_LGTsc19983_LGTsc28795_3.0.2 run below command with root access

chmod 4755 backint_hpux11_ia64_LGTsc19983_LGTsc28795_3.0.2

Mass rename script in Unix (HP-UX)

(#!/bin/ksh) - remove the brackets in real scenario

cd /oracle/GE1/saparch
for file in *arch*; do

newfile=`echo $file | sed 's/ZE1/GE1/'`
mv $file $newfile

done

Mass copy script in HP-UX Unix

(#! /bin/ksh) - remove brackets in real scenario

cd /usr/sap/ZE1/EXT/M100/D2DWHP/out/MDT

for file in *ZORDERHIS*;do

mv $file /usr/sap/trans_rep/IBM_Transfer/DE0198_interface_files_MM

echo $file 'moved'

done

How to change some character in mass in vi editor

:%s/^M//g

In above example %s means search for character ^M and replace it with nothing / g for globally

Or if you need to change the same with some other character say ^M with ^S then

:%s/^M/^S/g

Remedy query to find out last resolved requests

Log on to the Remedy tool, Choose resolved in the category after giving till Solution Manager in Assignee :


click on Advance and fill in the below query

'Last Resolved Date ' >= "22/05/2011 00:00:00" AND 'Last Name*+' != "HP_OVO_Ratingen"

Here you may give in any date from that date onwards all the Resolved Incidents in Solution Manager group excluding the Alerts ('Last Name*+' != "HP_OVO_Ratinge")

NWDI Change Management Service - Transports

This one is related to Track management. You may import / Assemble SCs (containing many DCs) in your track.

First thing to log on to the NWDI CMS

http://avggstah.dc-ratingen.de:51000/devinf/main

then choose the Change Management Service

Choose the track that you may want to do the changes and import / Assemble as per requirement

Parallel processes for Client Copy

Option for selecting the number of Parallel Process for Client Copy is available in SCC9, you may go to manu option Goto ---) Parallel Processes.

Here you will get options :

Maximum Number of Processes : 35

Logon/Server Group : parallel_generators

Command to Rebuild Indexes

We used the following command to rebuild the indexes in ZE1 System :

brspace -f idrebuild -i (index_list)

For eg :

brspace -f idrebuild -i /COCKPIT/THDRV~DEL

behind the scenes, it is using the below sql command :

sql) alter index "SAPEVO", "/COCKPIT/THDRV~DEL" rebuild online;

in generic terms

alter index "(schemaname)", "(indexname)" rebuild online;

Temp file movement in Oracle database

ZB1 System was frequently facing ORA-01114

ORA-01114:
IO error writing block to file string (block # string)

Cause: The device on which the file resides is probably offline. If the file is a temporary file, then it is also possible that the device has run out of space. This could happen because disk space of temporary files is not necessarily allocated at file creation time.

and ORA-12805: parallel query server died unexpectedly, errors frequently. So it was decided to move temp files to separate mount point. It included moving temp files from different locations to separate mount point only dedicated for temp files:

Please find the steps for the same :

Check whether listner is down Or Down Listener First

Take Name and open mode of database
SQL ) Select name,open_mode from v$database;

Take screen shot of location + status of tempfile

SQL ) set pagesize 0
SQL ) spool filetemp.txt
SQL ) select TS#,NAME,STATUS from v$tempfile;
SQL ) spool off

Now shut down the database and copy the files to new location

sqlplus / as sysdba
shutdown immediate

Note :: During shutdown of the database it hung for about 45-50 minutes, checked alert file nothing was happening, killed the ora_lgwr process with "kill -9" command then started the database with "startup" and then "shutdown immediate" it was smooth then started copying the files.

copy files from existing location to new mount point :

cp -pr /oracle/ZB1/sapdata1/temp_1/temp.data1 /oracle/ZB1/sapdata_temp1/temp_1 cp -pr /oracle/ZB1/sapdata8/temp_2/temp.data2 oracle/ZB1/sapdata_temp1/temp_2
cp -pr /oracle/ZB1/sapdata8/temp_3/temp.data3 oracle/ZB1/sapdata_temp1/temp_3


SQL ) startup mount

SQL ) alter database rename file '/oracle/ZB1/sapdata1/temp_1/temp.data1' to '/oracle/ZB1/sapdata_temp1/temp_1/temp.data1';
SQL ) alter database rename file '/oracle/ZB1/sapdata8/temp_2/temp.data2' to '/oracle/ZB1/sapdata_temp1/temp_2/temp.data2';
SQL ) alter database rename file '/oracle/ZB1/sapdata8/temp_3/temp.data3' to '/oracle/ZB1/sapdata_temp1/temp_3/temp.data3';


SQL ) alter database open;


Check the current status of updated temp files
SQL ) select TS#,NAME,STATUS from v$tempfile;

Query to ascertain database size

select (select sum(bytes)/1024/1024 from dba_data_files)+ (select sum(bytes)/1024/1024 from dba_temp_files) "Size in MB" from dual;

Table Reorg - Pre-requisites

1. No backup should be running in the system

select * from v$backup.

2. No archiving job should be running in the system, check from SM37


3. The size of oraarch (saparch) - should be free at least to the size of Table to be reorganized. Normally 30-40 GB free

4. Size of Table space PSAPSR3 should by 120% of the size of table to be reorganized.

5. Run the stats and check the table size before and after Reorg.

Table Reorg - How to do - commands

1. archive destination (sufficient space) - 20GB - 22GB (screen shot)
bdf | grep saparch (SID)


2. Tablespace PSAPEVO - object - SPACE should be same as table size - (20%). (screen shot)

select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name
, sum(bytes_used + bytes_free)/1024/1024 used_mb
from v$temp_space_header group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name union all
select tablespace_name,sum(bytes_free)/1024/1024 free_mb from v$temp_space_header group by tablespace_name ) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4;

3. Collect table statistics

brconnect -c -u / -f stats -t /BIC/B0000032000 -f collect -p 2

4. Checking TOP 20 Fregmented tables.

SELECT * FROM
(SELECT
SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,
NUM_ROWS,
AVG_ROW_LEN ROWLEN,
BLOCKS,
ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,
ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *
(1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,
ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE /
100) -
(AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"
FROM DBA_TABLES
WHERE
NUM_ROWS IS NOT NULL AND
OWNER LIKE 'SAP%' AND
PARTITIONED = 'NO' AND Table_name in ('/BIC/B0000034000
','/BIC/B0000582000',
',
(IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)
ORDER BY 7 DESC)
WHERE ROWNUM <=20;

5. Getting Table & DB size

select sum(bytes)/1024/1024/1024 from dba_segments;

select sum(bytes)/1024/1024/1024 from dba_segments where segment_name=();

Transaction to check ZR2 (Recruitment) System connectivity to TREX (Search Engine) // SRMO

SRMO - It will show the RFC connection from ZR2 to TREX is working or not.

E071 // Details about the transports done in the system

To check the status of transports, we can there is one table E071 where we can check the details of transports imported in the system.

Tuning Oracle DB Parameters - Steps to follow

First we can check the existing parmaters with following commands:

$show sga

$show parameter shared_pool;

$show parameter sga_max_size;

$show parameter db_cache_size;

Then take backup for pfile and spfile (initSID.ora and spfileSID.ora)

/oracle/SID/102_64/dbs

$create pfile from spfile

then edit pfile with the parameters that are recommended

start database with changed pfile

$startup pfile='/oracle/SID/102_64/dbs/initSID.ora'

check the parameters

if they are correct then

$create spfile from pfile

then shutdown and restart the database

$shutdown immediate
$startup

//Issue faced
while restarting database listener not found

$tnsping SID

gave error for resolving name

resolution

setenv TNS_ADMIN /oracle/SID/102_64/network/admin

later on added the same in .dbenv.csh and .dbenv_avggstqh.csh to make the same permanent.


//we can dynamically set the parameters in spfile online but this is not recommended as Oracle needs to adjust the parameters as well.

$alter system set shared_pool_size=400M scope=spfile;

How to find out UID and GID for HP-UX users

HP-UX command

$id oraeb3

$id dn1adm

HP-UX command for looking at memory with Graphics

glance | gpm

Excellent Unix Commands for finding and replacing in vi and file system

If you want to find something

$ find . | xargs grep "string"

It will find all the files recursively and then display the strings


vi commands (advanced)

Go to / Replace functionality can be used in vi... like this

:%s/stringtobereplaced/stringwithwhichtoreplace/g

(Esc + colen (:) + percentage (%) s /string1/string2/g

for eg:

:%s/avggstdh/avggstrh/g

will replace all the occurences of avggstdh with avggstrh.

BRCONNECT command for collecting stats for all tables

brconnect -c -u / -f stats -t all -f collect -p 4

Listener Issues - TNS_ADMIN parameter

If you face listener Issues make sure that TNS_ADMIN parameter is set in your DB environment


setenv TNS_ADMIN /oracle/SID/102_64/network/admin

also you may use


tnsping SID to test (It should be running) (Your listener should be up)

We faced the situation when we were using dbua for upgrading oracle. DBUA uses tnsping, also tnsnames.ora should be set correctly

Setting parameters in Oracle - New way

We may set the parameters in oracle with Alter command as well. We need not set the parameter in pfile and then create pfile. Easy way

How to free MDM Port

Our MDM Server uses port 20005 and if it is busy it wont start, it means some other application is waiting for the port.

we find out by this command

netstat -a | grep 20005

if it gives FINWAIT_1 then it is OK it will get free in some time, but if it gives FINWAIT_2 then we need to free the same manually

After analysis we found the destination host where three EP server are hosted EP1, EP2 and EP3

we logged on to each one and found which process is looking keeping busy port 20005, with below command

lsof -i | 20005

on EP3 we found the process

killed the process by kill -9 pid

Now the port became free :)

Host Movement - Profile Dependencies ABAP

Following profiles need to be changed for ABAP Host Movement and for Java apart from profile changes there need to be changes in configtool as per SAP Note 757692.