sync data from db1 to db2-use exp\ftp\imp

sync data from db1 to db2 
use exp\ftp\imp 

#(1) db1 ,this is test data 
create tablespace user01 datafile '/u01/disk1/user01.dbf' size 10M; 
create user user01 identified by user01 default tablespace user01; 
grant resource,connect to user01; 

conn user01/user01 
create table t1(id int,name varchar2(20)); 
insert into t1 values(1,'aaa'); 
insert into t1 values(2,'bbb'); 
insert into t1 values(3,'ccc'); 
commit; 

#(2) exp.sh ,this is the exp script 

#!/bin/bash 
export ORACLE_BASE=/u01/app/oracle 
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1 
export ORACLE_SID=PROD 
export PATH=${PATH}:$ORACLE_HOME/bin 
export NLS_LANG='AMERICAN_AMERICA.UTF8' 

DUMPFILE=/home/oracle/dump/PROD_`date +%Y-%m-%d`.dmp 
DUMPLOG=/home/oracle/dump/PROD_`date +%Y-%m-%d`.log 

exp user01/user01 file=$DUMPFILE log=$DUMPLOG owner=user01 COMPRESS=y 

bzip2 $DUMPFILE 

#(3) install vsftpd on db1 
[root@ocmdb1 Server]# rpm -ivh vsftpd-2.0.5-16.el5.i386.rpm 
warning: vsftpd-2.0.5-16.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159 
Preparing... ########################################### [100%] 
1:vsftpd ########################################### [100%] 

#config vsftpd 

vi /etc/vsftpd/vsftpd.conf。#将vsftpd.conf文件中的主要设置如下： 

anonymous_enable=YES 
local_enable=YES 
write_enable=YES 
anon_upload_enable=YES 
anon_mkdir_write_enable=YES 
ascii_upload_enable=YES 
ascii_download_enable=YES 



#(4) db2,this is test data 
create tablespace user01 datafile '/data1/data/user01.dbf' size 10M; 
create user user01 identified by user01 default tablespace user01; 
grant resource,connect to user01; 

conn user01/user01 
create table t1(id int,name varchar2(20)); 
insert into t1 values(1,'aaa'); 
insert into t1 values(2,'bbb'); 
insert into t1 values(3,'ccc'); 
commit; 

#(5) imp.sh ,this is the imp script on db2 
#!/bin/bash 
export ORACLE_BASE=/u01/app/oracle 
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 
export ORACLE_SID=huvisc 
export PATH=${PATH}:$ORACLE_HOME/bin 
export NLS_LANG='AMERICAN_AMERICA.UTF8' 

DUMPFILE=PROD_`date +%Y-%m-%d`.dmp 
DUMPLOG=PROD_`date +%Y-%m-%d`.log 
BZFILE=PROD_`date +%Y-%m-%d`.dmp.bz2 

FILEPATH=/home/oracle/dump 


ftp -n<
open 192.168.0.128 
user oracle oracle 
binary 
cd /home/oracle/dump 
lcd /home/oracle/dump 
prompt 
mget $BZFILE 
close 
bye 
! 

if [ -f "$FILEPATH/$BZFILE" ]; then 
echo $BZFILE is ok >> /home/oracle/dump/ftp.log 

sqlplus -s "/as sysdba" <
drop user user01 cascade; 
create user user01 identified by user01 default tablespace user01; 
grant resource,connect to user01; 
quit; 
EOF 

cd $FILEPATH 
bunzip2 $BZFILE 
imp user01/user01 file=$DUMPFILE log=$DUMPLOG fromuser=user01 touser=user01; 


else 
echo $BZFILE is false >> /home/oracle/dump/ftp.log 
fi 

#(6) select data on db2 
[oracle@huvis ~]$ sqlplus user01/user01 

SQL> select * from t1; 

ID NAME 
---------- -------------------- 
1 aaa 
2 bbb 
3 ccc 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22193071/viewspace-1246878/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22193071/viewspace-1246878/