Home » Infrastructure » Unix » log file creation (win xp,oracle 10g)
log file creation [message #323435] Wed, 28 May 2008 08:29 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
I have a .sh file which contains


. $SVEN_HOME/config/configurator.cfg



sqlplus $leninlogin  @$general_weekly/ACE2_HISTORY/ACE2_History.sql



I want to add a log file to track the log of job completion?

This .sql file simply truncates a table and it will be automated in control M tool.

But please advice how to write the script for log creation and with proper message.

Thanks
Re: log file creation [message #323452 is a reply to message #323435] Wed, 28 May 2008 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
spool?

Regards
Michel
Re: log file creation [message #323472 is a reply to message #323452] Wed, 28 May 2008 09:40 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
that is correct,thanks but i wanted it from unix point of view,
something like redirecting >>$logfile

i am not sure of the syntax and logic,hence need some inputs
Re: log file creation [message #323487 is a reply to message #323472] Wed, 28 May 2008 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just try it (">>").

Better use "sqlplus -s" to avoid SQL*Plus banner.

Regards
Michel
Re: log file creation [message #323526 is a reply to message #323487] Wed, 28 May 2008 12:15 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
When you run the command interactively, do you get the output you want on the screen? If not, you may need to modify the sql script to stop it from suppressing the output.

Try something like this:

-- set termout off
set echo off
set time on
select user, global_name from global_name;
prompt Truncating table 1 now...
truncate table xyz;


Re: log file creation [message #323657 is a reply to message #323526] Thu, 29 May 2008 02:53 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Thanks for the suggestion.

I am getting this thing correctly

this is mu .sql file

PROMPT '**************************************************'
PROMPT 'cleaning the deleted lists from ACE2_DELETED_LIST '
PROMPT '**************************************************'

spool /edw/load_area/others/sven/COM_CODE/general/production/weekly/ACE2_HISTORY/deletelog.txt

truncate table ACE2_DELETED_LIST;

exit;

Now i want to put a date with timestamp,so that what ever will be stored in log file with have the date and timestamp??

Thanks
Re: log file creation [message #323661 is a reply to message #323657] Thu, 29 May 2008 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
spooling a file with filename and current timestamp

Regards
Michel

[Updated on: Thu, 29 May 2008 03:04]

Report message to a moderator

Re: log file creation [message #323686 is a reply to message #323661] Thu, 29 May 2008 04:18 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
col filename new_value filename
SELECT 'test_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
FROM dual
/
spool &filename
<do work>
spool off


Many thanks Michel

But what will
Quote:
col filename new_value filename
part of the code do??
Re: log file creation [message #323687 is a reply to message #323686] Thu, 29 May 2008 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It tells that the value of "filename" column in next queries will be put inside a SQL*Plus substitution variable named "filename" (which is used using "&filename").

Regards
Michel
Re: log file creation [message #323716 is a reply to message #323687] Thu, 29 May 2008 05:42 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
col filename new_value filename
SELECT 'test_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
FROM dual
spool &filename
begin
dbms_output.put_line('hi');
end;

spool off


How to put this for my unix script??

this is my .sh file
. $SVEN_HOME/config/configurator.cfg



sqlplus $leninlogin  @$general_weekly/ACE2_HISTORY/ACE2_History.sql


Quote:
sqlplus $leninlogin
here it connects to data base
and the .sql file goes like this

PROMPT '**************************************************'
PROMPT 'cleaning the deleted lists from ACE2_DELETED_LIST '
PROMPT '**************************************************'


truncate table ACE2_DELETED_LIST;

exit;


Now where to write the select query and then the spool one??


Please advice??
Re: log file creation [message #323731 is a reply to message #323716] Thu, 29 May 2008 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before the first prompt unless you don't want it in your spool.

Regards
Michel
Re: log file creation [message #323782 is a reply to message #323731] Thu, 29 May 2008 07:49 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Thanks Michel

It is working
but i want rather than the log file containing the truncated message it should contain an user defined message

Quote:
The clean up job is complete


and if error then as usual the error message??


Please advice


Thanks
Re: log file creation [message #323784 is a reply to message #323782] Thu, 29 May 2008 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set feedback off

Regards
Michel
Re: log file creation [message #323785 is a reply to message #323784] Thu, 29 May 2008 07:58 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
I guess i am not clear in my requirement,but if i set feedback off then

for a success i can write
a
dbms_output.put_line('job complete')

but in case of error how will it come to the log file??

Thanks
Re: log file creation [message #323786 is a reply to message #323785] Thu, 29 May 2008 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Success of the whole script or success of one statement?

Regards
Michel

[Updated on: Thu, 29 May 2008 08:00]

Report message to a moderator

Re: log file creation [message #323788 is a reply to message #323786] Thu, 29 May 2008 08:01 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
there is only one statement

truncate table ACE2_DELETED_LIST;



PROMPT '**************************************************'
PROMPT 'Generating Log File
PROMPT '**************************************************'

col filename new_value filename
SELECT 'ACE2_DELETED_LIST_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
FROM DUAL;




PROMPT '**************************************************'
PROMPT 'cleaning the deleted lists from ACE2_DELETED_LIST '
PROMPT '**************************************************'


Begin

spool &filename

truncate table ACE2_DELETED_LIST;


spool off

exit;

This is the current code where the table truncated message is comming to the log file in case of success and in case of failure the error message

hence it should have the user defined message in case of success and error message in case of error

[Updated on: Thu, 29 May 2008 08:05]

Report message to a moderator

Re: log file creation [message #323800 is a reply to message #323788] Thu, 29 May 2008 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First remove "Begin" line, then:

PROMPT '**************************************************'
PROMPT 'Generating Log File
PROMPT '**************************************************'

col filename new_value filename
SELECT 'ACE2_DELETED_LIST_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
FROM DUAL;

whenever sqlerror exit 1
set feedback off

PROMPT '**************************************************'
PROMPT 'cleaning the deleted lists from ACE2_DELETED_LIST '
PROMPT '**************************************************'

spool &filename

truncate table ACE2_DELETED_LIST;

prompt Success!

spool off

exit;

[Updated on: Thu, 29 May 2008 09:17]

Report message to a moderator

Re: log file creation [message #323878 is a reply to message #323800] Thu, 29 May 2008 22:32 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
This is what i face

Say in case of any error,Then this prompt is comming always
even if error is there

Now for testing i dropped the table and ran the
.sh file below

. $SVEN_HOME/config/configurator.cfg



sqlplus $leninlogin  @$general_weekly/ACE2_HISTORY/run_delete_list.sql


This is the sql script below

PROMPT '**************************************************'
PROMPT 'Generating Log File
PROMPT '**************************************************'

col filename new_value filename
SELECT 'ACE2_DELETED_LIST_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
FROM DUAL;




PROMPT '**************************************************'
PROMPT 'cleaning the deleted lists from ACE2_DELETED_LIST '
PROMPT '**************************************************'

set feedback off


#spool &filename



truncate table ACE2_DELETED_LIST;



prompt 'success!!'


spool off



The log file created goes below


cat ACE2_DELETED_LIST_20080530042741.log
truncate table ACE2_DELETED_LIST
               *
ERROR at line 1:
ORA-00942: table or view does not exist


'success!!'


The success should only come in case of success?

PLease advice?

Thanks
Re: log file creation [message #323880 is a reply to message #323878] Thu, 29 May 2008 23:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read CAREFULLY the script I posted and remove NO line.

Regards
Michel

[Updated on: Thu, 29 May 2008 23:20]

Report message to a moderator

Re: log file creation [message #323886 is a reply to message #323880] Thu, 29 May 2008 23:29 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Apologise for the mistake


but here are a few questions please advice

Quote:
whenever sqlerror exit 1


what will this do?

secondly when i execute the .sh file,if it is success then it shows the success prompt and the file creation as well but when it is a failure then
this error message should only reflect in the log file and just a message to us that it failed and please check the log file for details


the currect senario below??

sh deletelist.sh

SQL*Plus: Release 10.2.0.2.0 - Production on Fri May 30 05:22:36 2008

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

'**************************************************'
'Generating Log File
'**************************************************'

FILENAME
------------------------------------
ACE2_DELETED_LIST_20080530052236.log

'**************************************************'
'cleaning the deleted lists from ACE2_DELETED_LIST '
'**************************************************'
truncate table ACE2_DELETED_LIST
               *
ERROR at line 1:
ORA-00942: table or view does not exist
Re: log file creation [message #323904 is a reply to message #323886] Fri, 30 May 2008 00:31 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Kindly help
Re: log file creation [message #323914 is a reply to message #323886] Fri, 30 May 2008 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not what you wanted?

Regards
Michel
Re: log file creation [message #323920 is a reply to message #323914] Fri, 30 May 2008 01:06 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Yes this was what i wanted,Thanks
but if you can see my previous mail carefully,i wanted the additional thing which actually makes better redeability of the code


Quote:
secondly when i execute the .sh file,if it is success then it shows the success prompt and the file creation as well but when it is a failure then
this error message should only reflect in the log file(the user executing the .sh script should get just a message that it failed and please check the log file for details,and as it is happening now the error is there in the log file but the same thing is also appearing in the shell while executing the .sh file)




[Updated on: Fri, 30 May 2008 01:08]

Report message to a moderator

Re: log file creation [message #323931 is a reply to message #323920] Fri, 30 May 2008 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand but anyway, you can do it yourself now with what has been said.

Regards
Michel

[Updated on: Fri, 30 May 2008 01:26]

Report message to a moderator

Re: log file creation [message #323935 is a reply to message #323931] Fri, 30 May 2008 01:51 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Razz

Ok i will try

if any one can understand what i mean please let me know

I don't want that oracle error message in the unix shell to be displayed while executing the .sh file,in case of failure it should only redirect it to the log file and for the user it should only display "process failed and check log"

something like this

being new to unix i am not sure how to implement this in the existing file of mine and hence need some assistance

Thanks
Re: log file creation [message #323943 is a reply to message #323935] Fri, 30 May 2008 02:12 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Lastly one major problem in the solution provided by Michel

The error can also be in the oracle connection also in case of wrong username and pwd,so in that case how will this work,as this generates the log only after proper connection in oracle.

Thank god it strike me,otherwise it would have been problem in production..
Re: log file creation [message #324075 is a reply to message #323943] Fri, 30 May 2008 11:38 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
One trick is to put a final spool at the end of the sql to act as a flag file. If the sql script gets to the end, it didn't exit prematurely. You obviously remove old flag files before the next run...
whenever sqlerror exit 1
spool my_main_log.log
...
...
spool end_was_reached_ok.flg


Try this though, works fine for
1)sqlplus not found,
2)wrong user/pass,
3)oracle error in sql statement etc...
#!/usr/bin/ksh
user=scott
pass=tiger
db=devdb

t_name=xyz
log=my_log`date '+%Y%m%di'`.log

##sqlplus -s /nolog <<EOF >$log
sqlplus -s /nolog <<EOF >/dev/null
  whenever sqlerror exit 1
  connect $user/$pass@$db
  truncate table $t_name;
EOF
rv=$?

echo Retval is $rv

if [ $rv -eq 0 ]; then
  echo "Successfully truncated $t_name"
else
  echo "ERROR. Failed to truncate $t_name"
fi

exit $rv

Re: log file creation [message #324122 is a reply to message #324075] Sat, 31 May 2008 00:04 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi Andrew.

This is superb,many thanks

Here is my current code,but i am getting error.Please also advice on the doubts,thanks


. $SVEN_HOME/config/configurator.cfg

LOG_FILE=$general_weekly/ACE2_HISTORY/ACE2_DELETED_LIST_`date '+%Y%m%di'`.log
touch $LOG_FILE
sqlplus -s  <<EOF >$LOG_FILE
#sqlplus -s /nolog <<EOF >/dev/null
whenever sqlerror exit 1
connect  $leninlogin

#sqlplus sven_lenin_prod/sven_lenin_prod@edw2

truncate table ACE2_DELETED_LIST;

EOF
rv=$?

echo Retval is $rv

if [ $rv -eq 0 ]; then
echo "Successfully Truncated"
else
echo "ERROR. Failed to complete the process"
fi

exit $rv


Quote:
$SVEN_HOME/config/configurator.cfg


Here i have the connection settings in the .cfg file

Quote:
sqlplus -s <<EOF >$LOG_FILE


Is this correct and then touch log file

because i want the error or success also in the log file??

secondly in this code this is what i get as error in log file

sh  new_createwithlog.sh
Retval is 1
ERROR. Failed to complete the process
/edw/load_area/others/sven/COM_CODE/general/production/weekly/ACE2_HISTORY>ls
ACE2_DELETED_LIST_20080531i.log  Listoutput_History.sh            new_createwithlog.sh
ACE2_History.sql                 Listoutput_History_log.sh        run_delete_list.sql
ACE2_History_log.sql             deletelist.sh
/edw/load_area/others/sven/COM_CODE/general/production/weekly/ACE2_HISTORY>cat ACE2_DELETED_LIST_20080531i.log
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus



Please advice on the below basics,which will also allow me to understand it better.

what is the use of
Quote:
<<EOF,>/dev/null


what will these statements do
Quote:

rv=$?

echo Retval is $rv



Re: log file creation [message #324131 is a reply to message #324122] Sat, 31 May 2008 01:12 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
. $SVEN_HOME/config/configurator.cfg

LOG_FILE=$general_weekly/ACE2_HISTORY/ACE2_DELETED_LIST_`date '+%Y%m%d'`.log
touch $LOG_FILE
sqlplus -s /nolog<<EOF >$LOG_FILE
#sqlplus -s /nolog <<EOF >/dev/null
whenever sqlerror exit 1
connect  $leninlogin



set feedback off

truncate table ACE2_DELETED_LIST;

prompt 'Successfully Truncated'

EOF
rv=$?

echo Retval is $rv

if [ $rv -eq 0 ]; then
echo "Successfully Truncated"
else
echo "ERROR. Failed to complete the process"
fi


Hi Andrew

This is my current code.It is working fine.I suppose i am doing it rightly

But this is the only error i am getting in log file

cat ACE2_DELETED_LIST_20080531.log
SP2-0734: unknown command beginning "sqlplus -s..." - rest of line ignored.
'Successfully Truncated'


Why is this the case?
and what will sqlplus -s do here?
Re: log file creation [message #324218 is a reply to message #324131] Sat, 31 May 2008 12:45 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
There are two "EOF" tags (you can use just about any string you want). They form a "here document" structure in shell scripting. Take care not to indent the second one in KSH it must be in the first colum. Everything between the tags is basically what you'd normally have in a separate sql script.

The commented out line (#sqlplus...) falls withing the here document, so SQLplus gets it and doesn't know what to do with it. You can comment something in sql using "--" rather than "#".

/dev/null is a null sink. You redirect output that you aren't intrested in seeing to it.

No need to touch the file first - that just creates an empty file if it doesn't already exist.

If you really want a SQL log, then use all the propmts, etc that you're interested in and redirect to your log, then just cat the log when error is detected.

rv=return value. $? always contains the return code of the last statement you ran in KSH. You capture it into a variable to ensure that you don't accidentally look at $? for the wrong statement, like say an echo (that you put in for debugging)before checking the return code.

Try something like this (untested)
. $SVEN_HOME/config/configurator.cfg

LOG_FILE=$general_weekly/ACE2_HISTORY/ACE2_DELETED_LIST_`date '+%Y%m%d'`.log
touch $LOG_FILE
sqlplus -s /nolog<<EOF >$LOG_FILE
whenever sqlerror exit 1
connect  $leninlogin

set echo off
prompt Trying to truncate ACE2_DELETED_LIST now...
truncate table ACE2_DELETED_LIST;

EOF
rv=$?

#echo Retval is $rv

if [ $rv -eq 0 ]; then
  echo "Successfully Truncated"
else
  echo "ERROR. Failed to complete the process"
  cat $LOG_FILE
fi

# exit with return code, so that your scheduler knows
# if this script failed (1-255) or succeeded (0)
exit $rv


Unix return code is limited to 1 byte
Oracle return codes are almost always negative numbers (e.g. ORA-01012 value is -1012, so don't try returning "whenever sqlerror exit SQL.SQLCODE"

http://www.toadworld.com/Education/StevenFeuersteinsPLSQLExperience/TrainingandPresentations/MiscellaneousPLSQLTraining/tabid/173/Def ault.aspx#Wierd
Previous Topic: to set ORACLE_HOME & ORACLE_SID
Next Topic: Clean up the Oracle database
Goto Forum:
  


Current Time: Thu Mar 28 13:15:57 CDT 2024