expdp通过dblink来导入

create.sql:

spo create.log
rem @tip 如果必要请修改下面几行替换gedb为所需的用户名或口令
drop user gedb cascade;
rem @tip 在数据库范围配置缺省表空间，或指定用户的缺省表空间
rem alter database default tablespace users ;

CREATE USER gedb IDENTIFIED BY gegedb default tablespace users temporary tablespace temp2;
GRANT UNLIMITED TABLESPACE TO gedb;
GRANT CONNECT TO gedb;
GRANT RESOURCE TO gedb;
GRANT DBA TO gedb;

create public database link maindb connect to gedb identified by gegedb using '
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.123.121.59)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ge01)
)
)';

spo off
exit


backup.bat:
cd d:\oracle\
sqlplus / as sysdba @create.sql
impdp gedb/gegedb network_link=maindb SCHEMAS=gedb parallel=2 LOGFILE=impdp.log
exit


oracle11g和oracle10g不能进行互相导入

expdp和impdp只能在服务端导入，directory 也只能建在服务器的一面

create public database link dblink_82 connect to gedb identified by gegedb using '136.97.11.82/ge01';

sqlplus> conn / as sysdba

create or replace directory dump_test as '/home/oracle/dbbackup/exp';

grant read,write on directory dump_test to gedb;

expdp gedb/gegedb directory=dump_test network_link=dblink_82 EXCLUDE=TABLE:\"IN\(\'VEHICLEALARM\'\)\";

impdp gedb/geegdb@ge01 directory=dump_test dumpfile=expdat.dmp


dbbackup.sh:

#!/bin/bash
#As oracle user, chmod 744 /home/oracle/dbbackup/DBbackuprecover.sh
#crontab -e 35 1 * * * /home/oracle/dbbackup/DBbackuprecover.sh >/dev/null 2>&1
#@tip: modify local database's home directory
#@tip <ip> is remote database ip address

export ORACLE_HOME=/opt/oracle/10gapp
export PATH=$ORACLE_HOME/bin:$PATH:
export NLS_LANG=AMERICAN_AMERICA.zhs16gbk
export LANG=AMERICAN_AMERICA.ZHS16GBK
export ORACLE_SID=ge01

dmpfile=gedb_`date +%F`.dmp
logfile=gedb_`date +%F`.log
restoredblog=restoredb_`date +%F`.log
WORK_DIR=~/dbbackup

cd $WORK_DIR
exp USERID=gedb/gegedb@136.97.11.82/ge01 file=backup.dmp log=$logfile owner=gedb
if [ 0 -eq "$?" ]
then
dbshut
sqlplus / as sysdba @dbbackup.sql
mv ./backup.dmp ./$dmpfile
find ./*.dmp -type f -mtime +3 -exec rm {} \;
find ./*.log -type f -mtime +7 -exec rm {} \;
exit 0
else
echo "backup error,quit!" > $logfile
exit 1
fi


dbbackup.sql:
spo dbbackup.log
startup restrict
drop user gedb cascade;
CREATE USER gedb IDENTIFIED BY gegedb;
GRANT UNLIMITED TABLESPACE TO gedb;
GRANT CONNECT TO gedb;
GRANT RESOURCE TO gedb;
GRANT DBA TO gedb;
HOST imp USERID=gedb/gegedb file=backup.dmp log=restore.log fromuser=gedb touser=gedb
Alter system disable restricted session;
spo off
exit