cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

BRSPACE Log being created under incorrect location

Go to solution
Former Member

on ‎09-07-2014 8:47 PM

0 Kudos

I have written a shell script to re-org couple of tables. following is the script:

#!/usr/bin/ksh

#

# BW Tables re-org and index compression

#

ORACLE_SID=TST; export ORACLE_SID

ORACLE_HOME=/oracle/TST/112_64; export ORACLE_HOME

PATH=/usr/sbin:/oracle/TST/admin/bin:/oracle/TST/112_64/bin:.:/oracle/TST:/usr/sap/TST/SYS/exe/run:/usr/bin:.:/usr/ccs/bin:/usr/ucb

LD_LIBRARY_PATH=/usr/sap/TST/SYS/exe/run:/oracle/TST/112_64/lib; export LD_LIBRARY_PATH

CURDATE=`date +"%Y-%m-%d-%H:%M"`

LOGFILE=/oracle/$ORACLE_SID/admin/log/tables_reorg.log; export LOGFILE

EMAIL_FILE=/oracle/$ORACLE_SID/admin/log/tables_reorg_email.txt; export EMAIL_FILE

echo "Table Reorg Start Time:`date +"%Y-%m-%d-%H:%M"` " > $EMAIL_FILE

echo " "  >> $EMAIL_FILE

brspace -c force -u / -f tbreorg -t BALHDR,RSBATCHDATA > $LOGFILE

echo "Table Reorg End Time: `date +"%Y-%m-%d-%H:%M"`" >> $EMAIL_FILE

echo " "  >> $EMAIL_FILE

echo "############ SUCCESS ############"  >> $EMAIL_FILE

grep "reorganized successfully" /oracle/$ORACLE_SID/admin/log/tables_reorg.log >> $EMAIL_FILE

echo " "  >> $EMAIL_FILE

echo "############ SKIPPED ############"  >> $EMAIL_FILE

grep "will be skipped" /oracle/$ORACLE_SID/admin/log/tables_reorg.log >> $EMAIL_FILE

echo " "  >> $EMAIL_FILE

echo "############ OVERALL ERRORS ############"  >> $EMAIL_FILE

grep "error" /oracle/$ORACLE_SID/admin/log/tables_reorg.log >> $EMAIL_FILE

echo " "  >> $EMAIL_FILE

cat $EMAIL_FILE  | /usr/bin/mailx -s "$ORACLE_SID Table Re-organisation Report " myemail@email.com

exit

We are required to login to the server using our personal ID and then "sudo su - oratst" to connect to oratst. When I login like this and execute the above script it works fine. Below is the output:

BR1001I BRSPACE 7.20 (38)
BR1002I Start of BRSPACE processing: seortudn.tbr 2014-09-07 15.35.15
BR0484I BRSPACE log file: /oracle/TST/sapreorg/seortudn.tbr

BR0280I BRSPACE time stamp: 2014-09-07 15.35.16
BR1009I Name of database instance: TST
BR1010I BRSPACE action ID: seortudn
BR1011I BRSPACE function ID: tbr
BR1012I BRSPACE function: tbreorg
BR0134I Unattended mode with 'force' active - no operator confirmation allowed

BR0280I BRSPACE time stamp: 2014-09-07 15.35.18
BR1039I Selecting information about tables and indexes...
BR0285I This action can take several seconds/minutes - be patient...

BR0280I BRSPACE time stamp: 2014-09-07 15.35.33
BR0814I Number of tables/partitions in schema of owner SAPR3: 33913/7699
BR0836I Number of tables treated as info cubes for owner SAPR3: 168

BR0280I BRSPACE time stamp: 2014-09-07 15.35.58
BR0815I Number of indexes/partitions in schema of owner SAPR3: 62579/64258

BR0280I BRSPACE time stamp: 2014-09-07 15.35.58
BR0660I List display 352 # no selection possible
-------------------------------------------------------------------------------
List of tables for reorganization

  Pos.  Owner    Table                Pt. DgPk.     Rows   Used[KB]  Data[KB:%]

    1 - SAPR3    BALHDR               NO   1      752120    228272    193906:85
    2 - SAPR3    RSBATCHDATA          NO   1         295       136       122:90

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0280I BRSPACE time stamp: 2014-09-07 15.35.58
BR0134I Unattended mode with 'force' active - continuing processing with default reply 'cont'

BR0280I BRSPACE time stamp: 2014-09-07 15.35.58
BR0657I Input menu 353 # please enter/check input values
-------------------------------------------------------------------------------
Options for reorganization of tables: SAPR3.BALHDR,... (2 tables)

1 * Reorganization action (action) ............ [reorg]
2 - Reorganization mode (mode) ................ [online]
3 - Create DDL statements (ddl) ............... [yes]
4 ~ New destination tablespace (newts) ........ []
5 ~ Separate index tablespace (indts) ......... []
6 - Parallel threads (parallel) ............... [1]
7 ~ Table/index parallel degree (degree) ...... []
8 ~ Category of initial extent size (initial) . []
9 ~ Sort by fields of index (sortind) ......... []
10 # Index for IOT conversion (iotind) ......... [FIRST]
11 - Compression action (compress) ............. [none]
12 # LOB compression degree (lobcompr) ......... [medium]
13 # Index compression method (indcompr) ....... [ora_proc]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0280I BRSPACE time stamp: 2014-09-07 15.35.58
BR0134I Unattended mode with 'force' active - continuing processing with default reply 'cont'

BR0280I BRSPACE time stamp: 2014-09-07 15.35.58
BR1108I Checking tables for reorganization...

BR0280I BRSPACE time stamp: 2014-09-07 15.35.59
BR1112I Number of tables selected/skipped for reorganization: 2/0

BR0370I Directory /oracle/TST/sapreorg/seortudn created

BR0280I BRSPACE time stamp: 2014-09-07 15.35.59
BR1101I Starting 'online' table reorganization...
BR0280I BRSPACE time stamp: 2014-09-07 15.35.59
BR1124I Starting 'online' reorganization of table SAPR3.BALHDR ...
BR0280I BRSPACE time stamp: 2014-09-07 15.36.53
BR1105I Table SAPR3.BALHDR reorganized successfully

BR0280I BRSPACE time stamp: 2014-09-07 15.36.53
BR1141I 1 of 2 tables processed - 752120 of 752415 rows done
BR0204I Percentage done: 98.67%, estimated end time: 15:36
BR0001I *************************************************_
BR0280I BRSPACE time stamp: 2014-09-07 15.36.53
BR1124I Starting 'online' reorganization of table SAPR3.RSBATCHDATA ...
BR0280I BRSPACE time stamp: 2014-09-07 15.37.05
BR1105I Table SAPR3.RSBATCHDATA reorganized successfully

BR0280I BRSPACE time stamp: 2014-09-07 15.37.05
BR1141I 2 of 2 tables processed - 752415 of 752415 rows done
BR0204I Percentage done: 100.00%, estimated end time: 15:37
BR0001I **************************************************

BR0280I BRSPACE time stamp: 2014-09-07 15.37.05
BR1102I Number of tables reorganized successfully: 2

BR0280I BRSPACE time stamp: 2014-09-07 15.37.05
BR1022I Number of tables processed: 2
BR1003I BRSPACE function 'tbreorg' completed

BR1008I End of BRSPACE processing: seortudn.tbr 2014-09-07 15.37.05
BR0280I BRSPACE time stamp: 2014-09-07 15.37.06
BR1005I BRSPACE completed successfully

When I schedule it in the crontab of oratst  directly it is trying to create the logfile under $ORACLE_HOME/sapreorg and fails since that folder does not exist. Please see below

BR1001I BRSPACE 7.20 (38)
BR0252E Function fopen() failed for '/oracle/TST/112_64/sapreorg/seortuxs.tbr' at location main-5
BR0253E errno 2: No such file or directory
BR0121E Processing of log file /oracle/TST/112_64/sapreorg/seortuxs.tbr failed

BR0700E Fatal errors occurred - terminating processing...

BR1008I End of BRSPACE processing: seortuxs.tbr 2014-09-07 15.44.00
BR0280I BRSPACE time stamp: 2014-09-07 15.44.00
BR1007I BRSPACE terminated with errors

My question is, why is trying to create log under $ORACLE_HOME/reorg? Where is is picking up this location from?

Any advice from experts?

Thanks in advance.

Show replies
Show replies
Answer

Accepted Solutions (1)

Accepted Solutions (1)

Reagan
Advisor
Advisor
‎09-07-2014 9:49 PM
0 Kudos

Can you supply the environment variables of the oratst user please ?

What is the value for SAPDATA_HOME ?

Show replies
Show replies
Former Member
‎09-07-2014 10:01 PM
0 Kudos

Hello Reagan,

Below is the env of oratst:

HOME=/oracle/TST

LOGNAME=oratst

PATH=/usr/bin:

SHELL=/usr/bin/sh

TZ=US/Eastern

When I sudo su - oratst and do env, this is what I see:

HOME=/oracle/TST

PATH=/usr/sbin:/usr/local/bin:/oracle/TST/112_64:/oracle/TST/112_64/OPatch:/oracle/TST/112_64/admin/bin:/oracle/TST/112_64/bin:/usr/sbin:/usr/local/bin:/oracle/TST/112_64:/oracle/TST/112_64/OPatch:/oracle/TST/112_64/admin/bin:/oracle/TST:/usr/sap/TST/SYS/exe/run:/usr/bin:.:/tools/ca/3.1/services/bin:/tools/ca/3.1/services/tools:/tools/ca/3.1/agents/bin:/opt/EMCpower/bin:/etc/emc/bin:/etc:/usr/ccs/bin:/usr/ucb

LOGNAME=oraTST

HZ=

TERM=xterm

SHELL=/usr/bin/csh

MAIL=/var/mail/oraTST

TZ=US/Eastern

PWD=/tmp

USER=oraTST

AGENTWORKS_DIR=/tools/ca/3.1

CASHCOMP=/opt/CA/SharedComponents

CALIB=/opt/CA/SharedComponents/lib

LD_LIBRARY_PATH=/opt/CA/SharedComponents/lib:/etc/emc/rsa/cst/lib:/usr/sap/TST/SYS/exe/run

LIC_ECHO=echo

SAPSYSTEMNAME=TST

DIR_LIBRARY=/usr/sap/TST/SYS/exe/run

THREAD=NOPS

dbms_type=ORA

dbs_ora_tsname=TST

ORACLE_BASE=/oracle

ORACLE_PSRV=TST

ORACLE_SID=TST

ORACLE_HOME=/oracle/TST/112_64

ORA_NLS10=/oracle/TST/112_64/nls/data

NLS_LANG=AMERICAN_AMERICA.WE8DEC

SAPDATA_HOME=/oracle/TST

EDITOR=vi

Reagan
Advisor
Advisor
‎09-07-2014 10:19 PM
0 Kudos

Hello Pravy



LOGNAME=oraTST


USER=oraTST

Is this the right username ? Bear in mind that on Unix names are case sensitive.

Check the value for SAPREORG in the initTST.sap file located at /oracle/TST/112_64/dbs

SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA)

If there is no reference to that then I would create a symbolic link for the time being.

ln -s /oracle/TST/sapreorg /oracle/TST/112_64/sapreorg

Regards

RB

Former Member
‎09-07-2014 10:34 PM
0 Kudos

Reagen,

It was a typo while pasting it on this site.

On the server its lower case:

LOGNAME=oratst

USER=oratst.

But you were right on the money with SAPDATA_HOME. I set this in the script and it now works like a charm from the crontab. The issue is now resolved.

Appreciate your time and help.

Answers (0)

Ask a Question