RE: Beyond a basic Oracle EXP/IMP shell script: Error Handling & Exit Status?

From: Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Fri, 19 Aug 2005 07:51:39 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF35FB1C@EXCNYSM0A1AJ.nysemail.nyenet>

Chris,

If you log the import/export step, you can simply grep the resulting log file for ORA-. If you find one, then you have something to look at. I do the following all the time.

$ORACLE_HOME/bin/exp user/pw file=exp.dmp log=exp.log

# check for ORA errors in export log

if [ `grep EXP- exp.log | wc -l` -gt 0 ] || [ `grep ORA- exp.log | wc -l` -gt 0 ]; then

MAILSUBJ="*** ORA Errors Found in Database Export"

mailx -s "$MAILSUBJ" -c $MAIL_LIST < exp.log

exit 1

Hope this helps.

Tom

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Marquez, Chris
Sent: Thursday, August 18, 2005 5:47 PM
To: Reidy, Ron; oracle-l_at_freelists.org
Subject: RE: Beyond a basic Oracle EXP/IMP shell script: Error Handling & Exit Status?

Ron,

Thanks I appreciate the comments.
I have not looked into 'typeset' command yet (maybe if I do it will answer my next question?

I used a simple EXP shell script I have and forced the 3 possible outcomes;

     Export terminated successfully with warnings.
     Export terminated successfully without warnings.
     EXP-00000: Export terminated unsuccessfully

"Export terminated..." should produce "0" and
"Export terminated unsuccessfully" should produce "1" however " $? " is always "0" for each?

This snip shell code

exp <<EOFexp >> $log_file 2>&1 \
.......
exit
EOFexp

echo 'EXP ERROR CODE ' >> $log_file 2>&1 echo $? >> $log_file

Either "EXP-00000: Export terminated unsuccessfully" != "Export terminated unsuccessfully"
or EXP is never populating " $? " correctly?

Reference;

Doc ID:         Note:281725.1
Subject:        Database Utilities - Export and Import Exit Codes

(Errorlevel)

Chris Marquez
Oracle DBA

-----Original Message-----
From: Reidy, Ron [mailto:Ron.Reidy_at_arraybiopharma.com] Sent: Thu 8/18/2005 4:19 PM
To: Marquez, Chris; oracle-l_at_freelists.org Subject: RE: Beyond a basic Oracle EXP/IMP shell script: Error Handling & Exit Status?

Comments below ...

Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Marquez, Chris
Sent: Thursday, August 18, 2005 2:14 PM
To: oracle-l_at_freelists.org
Subject: Beyond a basic Oracle EXP/IMP shell script: Error Handling & Exit Status?

All,

My skill level: I have written my share of shell scripts...mostly basic. A few "loops" and "if" statements here and there. 90% of my shell scripts have been to do things with Oracle; EXP/IMP, SQLPLUS, RMAN, etc. Mostly starting programs and handling external files. I have never written a case statement, nor used error handling (other than standard out).
My task: I have been given the task/challenge to convert or add to existing EXP/IMP scripts error handling and return codes. I found some tutorials on the web and began playing with "trap" and "Exit status" in some basic shell scripts. I fear my biggest problem is that beyond "how to" I don't know what is possible (with shell scripts & Oracle).

OK, OK, OK...my task (specifically).
1.) I need to know (or test) if my script completes successfully and assign a value.
[Reidy, Ronald] Use the 'typeset' command to declare a variable
(typeset -i rc). At the end oif the exp/imp command assign the value of the return code (rc=$?). Test it from there.

2.) I need to know if/where my script dies/aborts/terminates in the middle and assigning a value....rolling variable?

"if step A complete then v_stats=...if step B complete then v_stats=..."

3.) I need to get a positive or negative return code from Oracle EXP/IMP utility and pass it to shell script (#2 above).
[Reidy, Ronald] exp/imp return positive codes only. 0 = OK, anything
else indicates a warning or failure

Regarding Oracle EXP/IMP; Not sure what I am asking here...what are the possibilities if any for Oracle EXP/IMP utility return codes?
[Reidy, Ronald] See above.
And is a return code completely worthless anyway?
[Reidy, Ronald] No.
Would I really need/parse/get the result "Export completed without warnings" to *know* the Oracle EXP/IMP returned successfully?
[Reidy, Ronald] Spool to a log file. Use /bin/egrep to look for the
specific lines you would like to see.

I'm all ears...and ready for code samples-scripts, docs, links and references.

Thanks,

Chris Marquez
Oracle DBA

This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Aug 19 2005 - 06:54:39 CDT