Use exp in Linux to back up ORACLE data tables and import scripts

Sometimes, for the synchronization of some tables in two Oracle databases, we can use the tables required by the source database exp and then execute imp import locally.

Operation logic: first, execute the drop table command locally to delete the table to be replaced from the table required by the remote database exp, note the order in which tables associated with the primary and Foreign keys are deleted. Then execute imp import. The script is as follows:

First, create the. Sh file to execute the command:

[Oracle @ erpdevdb ~] $ Cat erpupdate. Sh
Echo "------------------------------------------------------------------">/home/Oracle/erptable. Log
Echo 'date'>/home/Oracle/erptable. Log
Export oracle_base =/u01/APP/Oracle
Export ORACLE_HOME =/u01/APP/Oracle/product/10.2.0/db_1
Export oracle_sid = erpstudydb
Export Path =/home/Oracle/bin:.:/u01/APP/Oracle/product/10.2.0/db_1/bin
Export nls_lang = american_america.zhs16gbk

CD/home/Oracle

SJ = 'date' + % Y % m % d''
Exp banping/****** @ remotedb parfile =/home/Oracle/parexptbl. LST file =/home/Oracle/exptable/table $ {SJ}. dmp

Sqlplus-s "banping/*******" @/home/Oracle/droptable. SQL

IMP userid = banping/****** file =/home/Oracle/exptable/table $ {SJ}. dmp full = y commit = y

# Delete old files
Find/home/Oracle/exptable-mtime + 10-exec LS-La {}\;>>/home/Oracle/erptable. Log
Find/home/Oracle/exptable-mtime + 10-exec RM {}\;>>/home/Oracle/erptable. Log

Echo 'date'>/home/Oracle/erptable. Log
Echo "------------------------------------------------------------------">/home/Oracle/erptable. Log
CAT/home/Oracle/erptable. log | sed "s/\ n/\ r \ n/g">/home/Oracle/expimpmail.txt
Mail-s "studydb EXP-IMP log" banping@banping.com Rm/home/Oracle/erptable. Log

Droptable. SQL is a bunch of table deletion commands:

[Oracle @ erpdevdb ~] $ Cat droptable. SQL
Drop table msgtask;
Drop table RDATA;
......

Parexptbl. lst is the list of tables to be imported:

[Oracle @ erpdevdb ~] $ Cat parexptbl. lst
Tables = (msgtask, RDATA ,......)

After these files are completed, Add. Sh to the scheduled task:

[Oracle @ erpdevdb ~] $ Crontab-l
30 5 ***/home/Oracle/erpupdate. Sh>/home/Oracle/erptable. log 2> & 1

In this way, you can monitor the export and import information by email every day.