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/[email protected] 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/[email protected]/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