as93is
asked on
Rename dmp file and log files with database name and date - then put dmp and log files in another directory
Rename dmp file and log files with database name and date - then put dmp and log files in another directory
I created the sa.dat file and sa_export.sh to export just one table every day in the bbb directory. I have to do a daily export of the ccc table at 1 a.m. This is done through a cron job.
When the daily export is done, an export dump file and an export log file are created.
When the export shell script is executed through the CRON, a log file for the export shell script and a log for the cron job are created.
So the following four files are created when the cron job runs:
1. export dump file
2. export log file
3. export shell script log file
4. cron job log file
The export of the ccc tables will be done in several different databases.
REQUIREMENTS:
1. All the above four files should be renamed with the date.
For example: dmp.date, export.log.date, exportshell.log.date, cron.log.date
2. ALL THE ABOVE FOUR FILES SHOULD ALSO BE RENAMED WITH THE DATABASE NAME ON WHICH
THE EXPORT IS DONE.
FOR EXAMPLE: dmp_DATABASENAME.dmp.DATE - should this be include the dat file?
export_DATABASENAME.log.DA TE - should this be included in the dat file?
exportshell_DATABASENAME.l og.DATE
cron_DATABASENAME.log.DATE
3. The above four files should be created in another directory yyy and not in the directory where the dat and shell scripts are there.
I given the sa.dat file, sa_export.sh file, and the cron job below:
========================== ========
sa.dat file
FILE=/yyy/111.dmp
LOG=/yyy/222.log
TABLES=ccc
ROWS=Y
CONSISTENT=Y
STATISTICS=NONE
========================== ========== ========== =
sa_export.sh
#!/bin/ksh
#Set ORACLE environment
ORACLE_HOME=???
ORACLE_SID=???
PATH=???
export ORACLE_HOME ORACLE_SID PATH
exportlogfile="/yyy/xx.log .`date +%m%d%Y`"
(
#
#Export ccc
$ORACLE_HOME/bin/exp username/password parfile=/bbb/as.dat
) > "$exportlogfile" 2>&1
========================== ========== ========== ========== ======
CRON JOB
#EXPORT ccc table every day
#00 1 * * * /bbb/sa_export.sh > /daily_cron.log
========================== ========== ========== ========== ========== ========== =====
Please let me know how to code the above REQUIREMENTS.
Thanks!!!
I created the sa.dat file and sa_export.sh to export just one table every day in the bbb directory. I have to do a daily export of the ccc table at 1 a.m. This is done through a cron job.
When the daily export is done, an export dump file and an export log file are created.
When the export shell script is executed through the CRON, a log file for the export shell script and a log for the cron job are created.
So the following four files are created when the cron job runs:
1. export dump file
2. export log file
3. export shell script log file
4. cron job log file
The export of the ccc tables will be done in several different databases.
REQUIREMENTS:
1. All the above four files should be renamed with the date.
For example: dmp.date, export.log.date, exportshell.log.date, cron.log.date
2. ALL THE ABOVE FOUR FILES SHOULD ALSO BE RENAMED WITH THE DATABASE NAME ON WHICH
THE EXPORT IS DONE.
FOR EXAMPLE: dmp_DATABASENAME.dmp.DATE - should this be include the dat file?
export_DATABASENAME.log.DA
exportshell_DATABASENAME.l
cron_DATABASENAME.log.DATE
3. The above four files should be created in another directory yyy and not in the directory where the dat and shell scripts are there.
I given the sa.dat file, sa_export.sh file, and the cron job below:
==========================
sa.dat file
FILE=/yyy/111.dmp
LOG=/yyy/222.log
TABLES=ccc
ROWS=Y
CONSISTENT=Y
STATISTICS=NONE
==========================
sa_export.sh
#!/bin/ksh
#Set ORACLE environment
ORACLE_HOME=???
ORACLE_SID=???
PATH=???
export ORACLE_HOME ORACLE_SID PATH
exportlogfile="/yyy/xx.log
(
#
#Export ccc
$ORACLE_HOME/bin/exp username/password parfile=/bbb/as.dat
) > "$exportlogfile" 2>&1
==========================
CRON JOB
#EXPORT ccc table every day
#00 1 * * * /bbb/sa_export.sh > /daily_cron.log
==========================
Please let me know how to code the above REQUIREMENTS.
Thanks!!!
I would like give this question 1000 points because it is difficult.
ASKER
I am a beginner to shell scripting.
Please give me the entire export shell script like example that I gave you.
How do I create the parfile?
Why do you say "Insert more code here"? What code should I insert? This is not clear to me.
I've a deadline for tomorrow to complete this task.
Please send me the entire export shell script and the parfile.
Thanks.
Please give me the entire export shell script like example that I gave you.
How do I create the parfile?
Why do you say "Insert more code here"? What code should I insert? This is not clear to me.
I've a deadline for tomorrow to complete this task.
Please send me the entire export shell script and the parfile.
Thanks.
ASKER
I am a beginner to shell scripting.
Please give me the entire export shell script like example that I gave you.
How do I create the parfile?
Why do you say "Insert more code here"? What code should I insert? This is not clear to me.
I've a deadline for tomorrow to complete this task.
Please send me the entire export shell script and the parfile.
What is .$$ after the parfile mean (TEMP_PARFILE=/tmp/parfile .$$)?
How will the shell script get the value for the ORACLE_SID?
rm $TEMP_PARFILE - Why are we removing the $TEMP_PARFILE ?
Thanks!
Please give me the entire export shell script like example that I gave you.
How do I create the parfile?
Why do you say "Insert more code here"? What code should I insert? This is not clear to me.
I've a deadline for tomorrow to complete this task.
Please send me the entire export shell script and the parfile.
What is .$$ after the parfile mean (TEMP_PARFILE=/tmp/parfile
How will the shell script get the value for the ORACLE_SID?
rm $TEMP_PARFILE - Why are we removing the $TEMP_PARFILE ?
Thanks!
Hi,
1. My code was only thought as an example, so it isn't complete. At the beginning, after #!/bin/ksh, add
2. The script creates the parfile. Look at the 'echo' statements, they put the values set under '# -- some variables for exp parfile' into a temporary file. I called the file '/tmp/parfile.$$' to make it unique, because the $$ carries the quasi-unique process id of your script.
3. I wrote '# Insert more code here #' to indicate that you could do more things in the script than just the export, e.g. send a message about the completion to someone or to initiate a backup, or anything else.
4. We remove the temporary parfile because it's no longer needed and will be recreated at the next run of the script.
5. Remember to set the correct values for the variables upt to '# -- the parfile itself -' as in your own script.
If you really need it, I will write the complete script for you, but I think you can do it by yourself.
Try it!
wmp
1. My code was only thought as an example, so it isn't complete. At the beginning, after #!/bin/ksh, add
from your script., so you get the variables correct. I can't guess what these values should be. PATH should point to the ORACLE binaries, the [] therein you must read as a colon, for some reason my browser doesn't accept a colon there.
#Set ORACLE environment
ORACLE_HOME=???
ORACLE_SID=???
PATH=$ORACLE_HOME/bin[]PATH
export ORACLE_HOME ORACLE_SID PATH
2. The script creates the parfile. Look at the 'echo' statements, they put the values set under '# -- some variables for exp parfile' into a temporary file. I called the file '/tmp/parfile.$$' to make it unique, because the $$ carries the quasi-unique process id of your script.
3. I wrote '# Insert more code here #' to indicate that you could do more things in the script than just the export, e.g. send a message about the completion to someone or to initiate a backup, or anything else.
4. We remove the temporary parfile because it's no longer needed and will be recreated at the next run of the script.
5. Remember to set the correct values for the variables upt to '# -- the parfile itself -' as in your own script.
If you really need it, I will write the complete script for you, but I think you can do it by yourself.
Try it!
wmp
OK, in case you don't have the time to do it by yourself,
here is the complete script.
The values in brackets [ ] are to be supplied by you.
wmp
here is the complete script.
The values in brackets [ ] are to be supplied by you.
wmp
#!/bin/ksh
# -- Set ORACLE environment -- #
ORACLE_HOME=[???]
ORACLE_SID=[???]
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME ORACLE_SID PATH
# -- your desired target directory -- #
TDIR=[/your/target/dir]
# -- some variables for exp parfile -- set as required -- #
USERID=[name/pass]
OWNER=[schema]
ROWS=Y
TABLES=Y
CONSISTENT=Y
STATISTICS=NONE
FILE=${TDIR}/${ORACLE_SID}.dmp.$(date +"%m%d%y")
LOG=${TDIR}/${ORACLE_SID}.log.$(date +"%m%d%y")
# -- the parfile itself -- #
TEMP_PARFILE=/tmp/parfile.$$
# -- export shell log -- #
EXSHLOG=${TDIR}/${ORACLE_SID}.shlog.(date +"%m%d%y")
# -- create parfile -- #
echo USERID=$USERID > $TEMP_PARFILE
echo OWNER=$OWNER >> $TEMP_PARFILE
echo ROWS=$ROWS >> $TEMP_PARFILE
echo TABLES=$TABLES >> $TEMP_PARFILE
echo CONSISTENT=$CONSISTENT >> $TEMP_PARFILE
echo STATISTICS=$STATISTICS >> $TEMP_PARFILE
echo FILE=$FILE >> $TEMP_PARFILE
echo LOG=$LOG >> $TEMP_PARFILE
# -- Run exp -- #
exp parfile=$TEMP_PARFILE > $EXSHLOG 2>&1
rm $TEMP_PARFILE
exit
Sorry,
I made a mistake (copy and paste error ...)
You should read TABLES=ccc and not TABLES=Y in line 19!
wmp
I made a mistake (copy and paste error ...)
You should read TABLES=ccc and not TABLES=Y in line 19!
wmp
ASKER
Your solution was an excellent, timely solution. The export shell script worked correctly as required when I tested it.
Two more new requirements were added today.
1.
The ORACLE environment variables (ORACLE_HOME, ORACLE_SID, PATH) should be deleted from the shell script.
The shell script should have a parameter and ORACLE_SID should be passed as value to this parameter.
2. The EXIT status should be captured through a returncode.
Please let me know how to code to meet the above requirements.
This is an urgent task.
Thanks.
Two more new requirements were added today.
1.
The ORACLE environment variables (ORACLE_HOME, ORACLE_SID, PATH) should be deleted from the shell script.
The shell script should have a parameter and ORACLE_SID should be passed as value to this parameter.
2. The EXIT status should be captured through a returncode.
Please let me know how to code to meet the above requirements.
This is an urgent task.
Thanks.
Hi,
glad I could help you.
To pass ORACLE_SID and ORACLE_HOME as arguments to your script,
run the script as [scriptname] [sid] [home] , e.g.
'sa_export.sh AS93 /oracle/ora102/AS93'
and modify the lines
(5) to 'ORACLE_SID=$1'
(6) to 'ORACLE_HOME=$2'
(note the changed sequence!)
I wouldn't modify PATH, because the PATH to Oracle binaries is nearly always
$ORACLE_HOME/bin (check it!)
If all your ORACLE_HOMEs were something like /oracle/ora102/$ORACLE_SID , you
could omit the $2 and modify line
(6) to ORACLE_HOME=/oracle/ora102 /$ORACLE_S ID
and run the script as [scriptname] [sid], e.g. 'sa_export.sh AS93'
To capture the returncode, simply add
RC=$? after line (42)
Then, you can use $RC for checking, or simply for
exit $RC
(note that cron doesn't like that and will send you a message about the unsuccessful completion. Perhaps that is what you want?)
I will attach a new version of the script
wmp
glad I could help you.
To pass ORACLE_SID and ORACLE_HOME as arguments to your script,
run the script as [scriptname] [sid] [home] , e.g.
'sa_export.sh AS93 /oracle/ora102/AS93'
and modify the lines
(5) to 'ORACLE_SID=$1'
(6) to 'ORACLE_HOME=$2'
(note the changed sequence!)
I wouldn't modify PATH, because the PATH to Oracle binaries is nearly always
$ORACLE_HOME/bin (check it!)
If all your ORACLE_HOMEs were something like /oracle/ora102/$ORACLE_SID
could omit the $2 and modify line
(6) to ORACLE_HOME=/oracle/ora102
and run the script as [scriptname] [sid], e.g. 'sa_export.sh AS93'
To capture the returncode, simply add
RC=$? after line (42)
Then, you can use $RC for checking, or simply for
exit $RC
(note that cron doesn't like that and will send you a message about the unsuccessful completion. Perhaps that is what you want?)
I will attach a new version of the script
wmp
#!/bin/ksh
# -- Set ORACLE environment by calling the script as [scriptname] [sid] -- #
ORACLE_SID=$1
ORACLE_HOME=/oracle/ora102/$ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME ORACLE_SID PATH
# -- your desired target directory -- #
TDIR=[/your/target/dir]
# -- some variables for exp parfile -- set as required -- #
USERID=[name/pass]
OWNER=[schema]
ROWS=Y
TABLES=Y
CONSISTENT=Y
STATISTICS=NONE
FILE=${TDIR}/${ORACLE_SID}.dmp.$(date +"%m%d%y")
LOG=${TDIR}/${ORACLE_SID}.log.$(date +"%m%d%y")
# -- the parfile itself -- #
TEMP_PARFILE=/tmp/parfile.$$
# -- export shell log -- #
EXSHLOG=${TDIR}/${ORACLE_SID}.shlog.(date +"%m%d%y")
# -- create parfile -- #
echo USERID=$USERID > $TEMP_PARFILE
echo OWNER=$OWNER >> $TEMP_PARFILE
echo ROWS=$ROWS >> $TEMP_PARFILE
echo TABLES=$TABLES >> $TEMP_PARFILE
echo CONSISTENT=$CONSISTENT >> $TEMP_PARFILE
echo STATISTICS=$STATISTICS >> $TEMP_PARFILE
echo FILE=$FILE >> $TEMP_PARFILE
echo LOG=$LOG >> $TEMP_PARFILE
# -- Run exp -- #
exp parfile=$TEMP_PARFILE > $EXSHLOG 2>&1
RC=$?
rm $TEMP_PARFILE
# -- check returncode and mail the log to Mr as93is if greater zero -- #
[ $RC -gt 0 ] && mailx -s "$0 $1 at $(date) unsuccessful!" as93is < $EXSHLOG
# -- exit 0 so cron doesn't complain -- #
exit 0
ASKER
Hi,
Thank you for your modified code.
I am a very new beginner to shell scripting and I have a strict deadline to meet.
My manager/client want the following:
1. I should pass only one parameter to the EXPORT shell script, that is ORACLE_SID.
2.
I should get a returncode to know the success/failure. I do not need to send any mail but the code is useful.
So just line 44 is enough to get the returncode. Am I right?
3. This is a very important requirement.
I should run this script through cron passing/getting/using the database/ORACLE_SID.
I don't know how to code the cron job.
I would like you to give me the code for the cron job, that is, run the export shell script through the cron passing the ORACLE_SID.
It could be something like:
getopts :d ???????????????????????
4.
There will be no one sitting at the terminal to run the script passing the ORACLE_SID.
The script will run through CRON using the ORACLE_SID.
5. I will follow your suggestion and just say exit 0 so that cron doesn't complain.
Please send me the code for the above including code for the cron job.
Thanks.
PS
I also have another requirement and I will send it you.
Please look at my question "Combine shell scripts into one shell script and run combined shell script passing a parameter through CRON".
I posted the question last night in Experts.
Again, I need help with the code and today is the deadline.
Thanks.
Thank you for your modified code.
I am a very new beginner to shell scripting and I have a strict deadline to meet.
My manager/client want the following:
1. I should pass only one parameter to the EXPORT shell script, that is ORACLE_SID.
2.
I should get a returncode to know the success/failure. I do not need to send any mail but the code is useful.
So just line 44 is enough to get the returncode. Am I right?
3. This is a very important requirement.
I should run this script through cron passing/getting/using the database/ORACLE_SID.
I don't know how to code the cron job.
I would like you to give me the code for the cron job, that is, run the export shell script through the cron passing the ORACLE_SID.
It could be something like:
getopts :d ???????????????????????
4.
There will be no one sitting at the terminal to run the script passing the ORACLE_SID.
The script will run through CRON using the ORACLE_SID.
5. I will follow your suggestion and just say exit 0 so that cron doesn't complain.
Please send me the code for the above including code for the cron job.
Thanks.
PS
I also have another requirement and I will send it you.
Please look at my question "Combine shell scripts into one shell script and run combined shell script passing a parameter through CRON".
I posted the question last night in Experts.
Again, I need help with the code and today is the deadline.
Thanks.
Hi,
1. My code above does already contain that.
Remember: if you only pass the SID, you must take care to have your ORACLE_HOME set correctly. This is easy if it takes the form '/constant/SID' (as assumed in my code above), where 'constant' is a never changing path for all your SIDs. Please post some samples of several ORACLE_HOMEs if you are in doubt.
2. Yes. In line 44 the variable $RC gets filled with the returncode of 'exp'. You can do with it whatever you want.
3. You don't need getopts for parsing one single parameter. As you see in my code, we use $1 which represents the first commandline parameter of the script. ksh fills this variable automatically.
4. The cron command is simple:
00 1 * * * /bbb/sa_export.sh mySID >/dev/null 2>&1
which means 'Run the shell script /bbb/sa_export.sh with parameter mySID each and every day at 1:00 a.m. and throw a possible console output away'
wmp
ASKER
Hi,
Here is my environment:
The OS is AIX. There are several LPARs. Each LPAR has multiple databases.
The export.sh will be run against each database on each LPAR.
The requirements are as follows:
ORACL_HOME in our environment is as follows:
ORACLE_HOME=/111/oracle/pr oduct/1020 /db_1 This is LPAR 111
ORACLE_HOME=/222/oracle/pr oduct/1020 /db_1 This is LPAR 222
Down the road, 1020 will change to 1120.
So we don't want to hard code the ORACLE_HOME which has the LPAR number (111, 222, etc) and 1020 in ORACLE_HOME.
PATH in our environment is as follows:
PATH=/111/X01/scripts:/111 /oracle/pr oduct/1020 /db_1/bin: ---- This is for LPAR 111 AND HOME 1020
PATH=/222/X01/scripts:/111 /oracle/pr oduct/1020 /db_1/bin: ----- This is for LPAR 222 AND HOME 1020
So we don't want to hard code the LPAR number and home.
SO WE WANT TO PASS ONLY ORACLE_SID AS PARAMETER TO THE EXPORT SHELL SCRIPT.
THE EXPORT.SH WILL BE RUN AGAINST EACH DATABASE ON EACH LPAR THROUGH CRON.
SO THE CRON JOB CODE HAS TO BE DIFFERENT WHEN THE EXPORT SHELL SCRIPT IS RUN AGAINST MULTIPLE DATABASES ON EACH LPAR:
MAY BE THE CRON JOB CODE COULD BE LIKE WHAT I MENTIONED BEFORE:
The code for the cron job, that is, run the export shell script through the cron passing the ORACLE_SID.
It could be something like:
getopts :d ???????????????????????
Please send me the code for the above.
IS THERE A PHONE NUMBER THAT I COULD REACH YOU? THIS TASK HAS TO BE COMPLETED TODAY.
Thanks.
Here is my environment:
The OS is AIX. There are several LPARs. Each LPAR has multiple databases.
The export.sh will be run against each database on each LPAR.
The requirements are as follows:
ORACL_HOME in our environment is as follows:
ORACLE_HOME=/111/oracle/pr
ORACLE_HOME=/222/oracle/pr
Down the road, 1020 will change to 1120.
So we don't want to hard code the ORACLE_HOME which has the LPAR number (111, 222, etc) and 1020 in ORACLE_HOME.
PATH in our environment is as follows:
PATH=/111/X01/scripts:/111
PATH=/222/X01/scripts:/111
So we don't want to hard code the LPAR number and home.
SO WE WANT TO PASS ONLY ORACLE_SID AS PARAMETER TO THE EXPORT SHELL SCRIPT.
THE EXPORT.SH WILL BE RUN AGAINST EACH DATABASE ON EACH LPAR THROUGH CRON.
SO THE CRON JOB CODE HAS TO BE DIFFERENT WHEN THE EXPORT SHELL SCRIPT IS RUN AGAINST MULTIPLE DATABASES ON EACH LPAR:
MAY BE THE CRON JOB CODE COULD BE LIKE WHAT I MENTIONED BEFORE:
The code for the cron job, that is, run the export shell script through the cron passing the ORACLE_SID.
It could be something like:
getopts :d ???????????????????????
Please send me the code for the above.
IS THERE A PHONE NUMBER THAT I COULD REACH YOU? THIS TASK HAS TO BE COMPLETED TODAY.
Thanks.
Hi,
I assume you have different operating system users per database.
These users have in their login shell environment the correct variables for Oracle, namely $ORACLE_HOME, $ORACLE_SID already set. So you can do the following trick -
Use root's crontab and put there
00 1 * * * su - [dbuser] -c "/bbb/sa_export.sh" >/dev/null 2>&1
Of course you'll have to omit the ORACLE_HOME=... and ORACLE_SID=... statements, as this is contained in [dbuser]'s environment.
Note that you don't even need to pass the SID, but you will have to create one crontab entry per existing [dbuser].
Giving away a telephone number is not compliant with EE rules.
wmp
I assume you have different operating system users per database.
These users have in their login shell environment the correct variables for Oracle, namely $ORACLE_HOME, $ORACLE_SID already set. So you can do the following trick -
Use root's crontab and put there
00 1 * * * su - [dbuser] -c "/bbb/sa_export.sh" >/dev/null 2>&1
Of course you'll have to omit the ORACLE_HOME=... and ORACLE_SID=... statements, as this is contained in [dbuser]'s environment.
Note that you don't even need to pass the SID, but you will have to create one crontab entry per existing [dbuser].
Giving away a telephone number is not compliant with EE rules.
wmp
ASKER
I am a little confused.
Please give me the revised code for the export.sh script and crontab code.
Thanks.
Please give me the revised code for the export.sh script and crontab code.
Thanks.
I don't know the names of your db-opsys-users, so I use [dbuser-nnn-n]
root's crontab on lpar 111
# run export.sh for dbuser-111-1, who is the dba-user of db_1 on lpar 111
# and has all the environment for db_1 on lpar 111 already set
00 1 * * * su - dbuser-111-1 -c "/bbb/sa_export.sh" >/dev/null 2>&1
# run export.sh for dbuser-111-2, who is the dba-user of db_2 on lpar 111
# and has all the environment for db_2 on lpar 111 already set
00 1 * * * su - dbuser-111-2 -c "/bbb/sa_export.sh" >/dev/null 2>&1
etc.
root's crontab on lpar 222
# run export.sh for dbuser-222-1, who is the dba-user of db_1 on lpar 222
# and has all the environment for db_1 on lpar 222 already set
00 1 * * * su - dbuser-222-1 -c "/bbb/sa_export.sh" >/dev/null 2>&1
# run export.sh for dbuser-222-2, who is the dba-user of db_2 on lpar 222
# and has all the environment for db_2 on lpar 222 already set
00 1 * * * su - dbuser-222-2 -c "/bbb/sa_export.sh" >/dev/null 2>&1
etc.
New script attached
root's crontab on lpar 111
# run export.sh for dbuser-111-1, who is the dba-user of db_1 on lpar 111
# and has all the environment for db_1 on lpar 111 already set
00 1 * * * su - dbuser-111-1 -c "/bbb/sa_export.sh" >/dev/null 2>&1
# run export.sh for dbuser-111-2, who is the dba-user of db_2 on lpar 111
# and has all the environment for db_2 on lpar 111 already set
00 1 * * * su - dbuser-111-2 -c "/bbb/sa_export.sh" >/dev/null 2>&1
etc.
root's crontab on lpar 222
# run export.sh for dbuser-222-1, who is the dba-user of db_1 on lpar 222
# and has all the environment for db_1 on lpar 222 already set
00 1 * * * su - dbuser-222-1 -c "/bbb/sa_export.sh" >/dev/null 2>&1
# run export.sh for dbuser-222-2, who is the dba-user of db_2 on lpar 222
# and has all the environment for db_2 on lpar 222 already set
00 1 * * * su - dbuser-222-2 -c "/bbb/sa_export.sh" >/dev/null 2>&1
etc.
New script attached
#!/bin/ksh
# -- ORACLE variables are already contained in the user environment -- #
# -- your desired target directory -- #
TDIR=[/your/target/dir]
# -- some variables for exp parfile -- set as required -- #
USERID=[name/pass]
OWNER=[schema]
ROWS=Y
TABLES=Y
CONSISTENT=Y
STATISTICS=NONE
FILE=${TDIR}/${ORACLE_SID}.dmp.$(date +"%m%d%y")
LOG=${TDIR}/${ORACLE_SID}.log.$(date +"%m%d%y")
# -- the parfile itself -- #
TEMP_PARFILE=/tmp/parfile.$$
# -- export shell log -- #
EXSHLOG=${TDIR}/${ORACLE_SID}.shlog.(date +"%m%d%y")
# -- create parfile -- #
echo USERID=$USERID > $TEMP_PARFILE
echo OWNER=$OWNER >> $TEMP_PARFILE
echo ROWS=$ROWS >> $TEMP_PARFILE
echo TABLES=$TABLES >> $TEMP_PARFILE
echo CONSISTENT=$CONSISTENT >> $TEMP_PARFILE
echo STATISTICS=$STATISTICS >> $TEMP_PARFILE
echo FILE=$FILE >> $TEMP_PARFILE
echo LOG=$LOG >> $TEMP_PARFILE
# -- Run exp -- #
exp parfile=$TEMP_PARFILE > $EXSHLOG 2>&1
RC=$?
rm $TEMP_PARFILE
# -- check returncode and mail the log to Mr as93is if greater zero -- #
[ $RC -gt 0 ] && mailx -s "$0 $1 at $(date) unsuccessful!" as93is < $EXSHLOG
# -- exit 0 so cron doesn't complain -- #
exit 0
ASKER
I have given below the FINAL code for the export.sh script AS I UNDERSTAND IT -- PASSING PARAMETER AND WITH RETURNCODE
I modified it a little for the date when I tested it yesterday.
export.sh
#!/bin/ksh
# -- Set ORACLE environment by calling the script as [scriptname] [sid] -- #
ORACLE_SID=$1
DATE=`date +"%m%d%y`
# -- your desired target directory -- #
TDIR=[/your/target/dir]
# -- some variables for exp parfile -- set as required -- #
USERID=[name/pass]
OWNER=[schema]
ROWS=Y
TABLES=Y
STATISTICS=NONE
FILE=${TDIR}/${ORACLE_SID} _$DATE.dmp
LOG=${TDIR}/${ORACLE_SID}_ $DATE.log
# -- the parfile itself -- #
TEMP_PARFILE=/tmp/parfile. $$
# -- export shell log -- #
expshlog=${TDIR}/${ORACLE_ SID}_$DATE .expshlog
# -- create parfile -- #
echo USERID=$USERID > $TEMP_PARFILE
echo OWNER=$OWNER >> $TEMP_PARFILE
echo ROWS=$ROWS >> $TEMP_PARFILE
echo TABLES=$TABLES >> $TEMP_PARFILE
echo STATISTICS=$STATISTICS >> $TEMP_PARFILE
echo FILE=$FILE >> $TEMP_PARFILE
echo LOG=$LOG >> $TEMP_PARFILE
# -- Run exp -- #
exp parfile=$TEMP_PARFILE > $expshlog 2>&1
RC=$?
rm $TEMP_PARFILE
exit 0
************************** ********** ********** ********** ********** ********** ********** *********
FINAL CODE FOR CRON JOB TO RUN EXPORT SHELL SCRIPT
00 1 * * * su - dbuser-111-1 -c "/bbb/sa_export.sh" >/dev/null 2>&1
What does su do in the cron job command?
What is -c in the cron job command?
Why is "/bbb/sa_export.sh" in double quotes?
PLEASE GO TRHOUGH THE ABOVE TWO CODES AND CORRECT IT IF NECESSARY.
PLEASE LET ME KNOW IF THE ABOVE CODE FOR THE EXPORT.SH SCRIPT AND THE CRON JOB WILL WORK CORRECTLY TO MEET OUR REQUIREMENTS.
THANKS.
I modified it a little for the date when I tested it yesterday.
export.sh
#!/bin/ksh
# -- Set ORACLE environment by calling the script as [scriptname] [sid] -- #
ORACLE_SID=$1
DATE=`date +"%m%d%y`
# -- your desired target directory -- #
TDIR=[/your/target/dir]
# -- some variables for exp parfile -- set as required -- #
USERID=[name/pass]
OWNER=[schema]
ROWS=Y
TABLES=Y
STATISTICS=NONE
FILE=${TDIR}/${ORACLE_SID}
LOG=${TDIR}/${ORACLE_SID}_
# -- the parfile itself -- #
TEMP_PARFILE=/tmp/parfile.
# -- export shell log -- #
expshlog=${TDIR}/${ORACLE_
# -- create parfile -- #
echo USERID=$USERID > $TEMP_PARFILE
echo OWNER=$OWNER >> $TEMP_PARFILE
echo ROWS=$ROWS >> $TEMP_PARFILE
echo TABLES=$TABLES >> $TEMP_PARFILE
echo STATISTICS=$STATISTICS >> $TEMP_PARFILE
echo FILE=$FILE >> $TEMP_PARFILE
echo LOG=$LOG >> $TEMP_PARFILE
# -- Run exp -- #
exp parfile=$TEMP_PARFILE > $expshlog 2>&1
RC=$?
rm $TEMP_PARFILE
exit 0
**************************
FINAL CODE FOR CRON JOB TO RUN EXPORT SHELL SCRIPT
00 1 * * * su - dbuser-111-1 -c "/bbb/sa_export.sh" >/dev/null 2>&1
What does su do in the cron job command?
What is -c in the cron job command?
Why is "/bbb/sa_export.sh" in double quotes?
PLEASE GO TRHOUGH THE ABOVE TWO CODES AND CORRECT IT IF NECESSARY.
PLEASE LET ME KNOW IF THE ABOVE CODE FOR THE EXPORT.SH SCRIPT AND THE CRON JOB WILL WORK CORRECTLY TO MEET OUR REQUIREMENTS.
THANKS.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for your PROMPT feedback.
I cannot log in as root.
Is there any other way to verify the ORACLE_HOME and PATH of the dbuser on 111 LPAR?
I log into the LPAR, and run the cron. Is this the root's crontab?
Thanks!
I cannot log in as root.
Is there any other way to verify the ORACLE_HOME and PATH of the dbuser on 111 LPAR?
I log into the LPAR, and run the cron. Is this the root's crontab?
Thanks!
You don't run the cron, cron is run by init!
If you don't login as root, it is surely not root's crontab. You will have to ask an administrator to modify root's crontab for you.
To verify dbuser's env, login as this user and run printenv.
If you don't login as root, it is surely not root's crontab. You will have to ask an administrator to modify root's crontab for you.
To verify dbuser's env, login as this user and run printenv.
ASKER
THANK YOU VERY MUCH FOR YOUR TIMELY HELP.
ASKER
Hi woolmilkporc,
Thank you very much for your excellent, timely solution to my question. Also, you have answered all my questions.
Thanks!!!
MODERATOR:
I request you to assign woolmilkporc 2000 points for his excellent, timely solution to my question.
Thanks!!!
Thank you very much for your excellent, timely solution to my question. Also, you have answered all my questions.
Thanks!!!
MODERATOR:
I request you to assign woolmilkporc 2000 points for his excellent, timely solution to my question.
Thanks!!!
You're welcome!
Have much fun and success!
Norbert
Have much fun and success!
Norbert
you could create the parfile for your export 'on the fly' by your script so you can define names and directories there.
Sample code is attached.
Why do you need a logfile from cron? Since you direct stdout of the exp command to a file, what is left to log?
But we could find a solution for this, too.
wmp
Open in new window