Link to home
Create AccountLog in
Avatar of as93is
as93is

asked on

How do I Create a CRON JOB to take a DAILY FULL EXPORT at 10.30 p.m.of the 9.2 database on Sun 5.8 on Solaris?

Create a CRON JOB to take a DAILY FULL EXPORT at 10.30 p.m.of the 9.2 database on Sun 5.8 on Solaris.
I have to take FULL EXPORT of the ORACLE 9.2 database DAILY at 10.30 p.m.
I have to automate this process on Solaris as a cron job which should include the EXP command with parameters and make it work today.  This is URGENT.
How do I set the environment? cron env and my Korn Shell env
How do I include username and password? - oracle login information for the EXP command
I am beginner with cron jobs.  What should be the cron job command for DAILY EXPORT at 10.30 p.m.?
After the export is done, I want to move the dmp file to a backup server.  How do I do this?

Here is my EXP command:

FILE=/my/expdaily.dmp
LOG=/my/expdaily.log
FULL=y
GRANTS=y
INDEXES=y
ROWS=y
CONTRAINTS=y
STATISTICS=compute

I would be thankful if you could please help me with the above.  This is for an URGENT deadline which is today (04-12-2008)

Regards,
Inthira
Avatar of Paulo Pimenta
Paulo Pimenta
Flag of Portugal image

OK!
You need to do 3 things:

1) Create a BASH file (something like the Batch file in Wondows) to execute the exp command and move the files to the new location. Since this location is on another server, that I assume to be a Windows Server, you may need to mout it first (http://www.cyberciti.biz/faq/access-windows-shares-from-linux/).

1.1) BASH file should look like this:
#!/bin/bash          
<exp command you normally use. don't forget the user/password parameter>
mv /my/expdaily.dmp <backup server location>
mv /my/expdaily.log <backup server location>

2) Edit contrab file in order to Schedule the execution of the BASH file you created on step 1. This is the tricky part, for you, I think.

2.1) Edit the crontab file in "/etc" folder.

2.2) Add a line, at the end of the file, looking like this: 30 10 * * 1-7 <command (BASH file) you create>

2.3) Save crontab.

Hope this helps. From you physical location, I believe you have the hole afternoon to implement this and run some tests.

Best of luck!

Cheers,
PP
Correction:
2.2) The line in crontab file should be: 30 22 * * 1-7 <command (BASH file) you create>
This means the command will execute at 22:30, every day (monday to sunday), no matter the day of month or the month.

Here's a better explanation to interprete "30 22 * * 1-7"
field allowed values
----- --------------
minute 0-59
hour 0-23
day of month 1-31
month 1-12 (or names, see below)
day of week 0-7 (0 or 7 is Sun, or use names)
Avatar of as93is
as93is

ASKER

Create a CRON JOB to take a DAILY FULL EXPORT at 10.30 p.m.of the 9.2 database on Sun 5.8 on Solaris.

Thank you very much for the prompt response.  As you rightly said, I still have the rest of the day to complete my task.

I still would like to have clarifications for the following:

1.  How do I set the environment? cron env and my Korn Shell env  - I am in KORN SHELL.

2.  How do I include username and password? - oracle login information for the EXP command

3.  2.2) Add a line, at the end of the file, looking like this: 30 10 * * 1-7 <command (BASH file) you create>

What  DO I INCLUDE <command (BASH file) you create> in the crontab?  Please give an example with bash file name in the above command.

Regards,
IS
1) I believe Crontab is environment independent. You open console (command line console) and edit "/etc/crontab" file, for example with vi, or you can try editing the same file through your shell, as you would do in Windows by opening Windows Explorer and editing the file with Notepad for example. What matters is that crontab does not dependent on the shell you are using.

2) Regarding this issue, there's good info here: http://www.orafaq.com/wiki/Import_Export_FAQ

3) Let's suppose you create the BASH file "/my/full_backup_daily.sh". crontab line could look like this:
30 10 * * 1-7 /my/full_back_daily.sh
Avatar of omarfarid
Please note that, to run crontab job you need to:

- set the env variables needed like PATH, ORACLE_HOME, ORACLE_SID, etc in the script you want to run as crontab job
- the script should be executable (e.g. chmod +x myscript)
- The commands or files used in the script should be referenced by full path name (e.g. /usr/bin/ls)
- you redirect any output / error messages to files (e.g. /usr/bin/ls >> /path/to/myscript.log 2>&1)

to schedule a  cron job like the one  you want to:

add the following to your crontab jobs

30 22 * * * /path/to/myscript

To add as cron tab job

- EDITOR=vi ; export EDITOR
- crontab -e

add the line above , save and exit
 to check crontab jobs

crontab -l

To export oracle you need to run the exp command with required arguments / options:

exp username/password@oracle_db parfile=export.txt

where export.txt contains:

FILE=/my/expdaily.dmp
LOG=/my/expdaily.log
FULL=y
GRANTS=y
INDEXES=y
ROWS=y
CONTRAINTS=y
STATISTICS=compute

To move the dump file to a backup server you may use:

- ftp command

or scp / sftp

Let me know which one you prefer.
Avatar of as93is

ASKER

Here is the export backup shell script, export.dat file and the crontab entry.  
Please let me know if I can run the following to do a full daily export backup using cron job:
export_full_backup_daily.sh

!/bin/ksh -x
PATH=/path
export PATH=/path
ORACLE_HOME=x
export ORACLE_HOME
ORACLE_SID=y
export ORACLE_SID

exp username/password@oracle_db parfile="/path/export.dat"

HOW DO I INCLUDE THE EXP COMMAND IN THE SHELL SCRIPT?  Will the above command run?

====================================================================
export.dat

FILE=/my/expdaily.dmp
LOG=/my/expdaily.log
FULL=y
GRANTS=y
INDEXES=y
ROWS=y
CONTRAINTS=y
STATISTICS=compute
===============================================================
CRONNTAB ENTRY

30 22 * * * /path/export_full_back_daily.sh > /dev/null 2>&1

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of omarfarid
omarfarid
Flag of United Arab Emirates image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account