sqlplus / as sysdba
创建数据库导出用户
create user dump identified by dump;
给导出用户赋予权限
grant dba to dump;
系统中创建导出目录
mkdir /backup
chown oracle:oinstall /backup
数据库中创建对应导出目录
create directory dump as '/backup';
删除目录命令
-----drop directory dump;
替代目录命令
create or replace directory dump as '/backup';
给导出目录赋予读写权限
grant read,write on directory dump to dump;
-----目标库创建所需表空间
create temporary tablespace TEMP01 tempfile '/u02/app/oracle/oradata/datastore/orcldata/temp01.dbf' size 30G;
create tablespace TSP_ACCT datafile '/u02/app/oracle/oradata/datastore/orcldata/TSP_ACCT01.dbf' size 2G;
-----关闭归档(可选)
-----导出数据
expdp dump/dump directory=dump dumpfile=expdp_20170623_%U.dmp logfile=expfull.log full=y job_name=exp_job filesize=10G parallel=15
建议后台导出
nohup expdp dump/dump directory=dump dumpfile=expdp_20170623_%U.dmp logfile=expfull.log full=y job_name=exp_job filesize=10G parallel=15 &
***集群数据库导出时要加入cluster=N参数
***并行数量根据自己服务器负载情况选择
-----导入数据
nohup impdp dump/dump directory=dump dumpfile=expdp_20170623_%U.dmp logfile=impfull.log full=y TABLE_EXISTS_ACTION=SKIP job_name=imp_job parallel=19 &
-----开启归档、验证数据
====================================================
正确停止expdp导出任务
1.查看视图dba_datapump_jobs
select job_name,state from dba_datapump_jobs;
发现任务确认还在执行
2.正确停止expdp导出任务使用stop_job
[oracle@database ~]$
expdp \"sys/oracle as sysdba\" attach=SYS_EXPORT_SCHEMA_02
***expdp dump/dump attach=SYS_EXPORT_SCHEMA_02
Export: Release 11.2.0.4.0 - Production on Mon Jan 26 10:31:42 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Job: SYS_EXPORT_SCHEMA_02
Owner: SYS
Operation: EXPORT
Creator Privs: TRUE
GUID: 0D85DF9D2F014494E053A80013AC483D
Start Time: Monday, 26 January, 2015 10:27:03
Mode: SCHEMA
Instance: orcl
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND "sys/******** AS SYSDBA" schemas=(prod,tts_fnd,tts_cms,tts_hr,tts_ecm,tts_coa,tts_exm,tts_htm) dumpfile=tt.dmp logfile=tt.log directory=test
State: EXECUTING
Bytes Processed: 17,595,108,512
Percent Done: 81
Current Parallelism: 1
Job Error Count: 0
Dump File: /oracle/backup/tt.dmp
bytes written: 17,610,469,376
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: TTS_EXM
Object Name: EXM_PERSON_ITEMS
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 80
Total Objects: 1,400
Worker Parallelism: 1
Export> stop_job=immediate
Are you sure you wish to stop this job ([yes]/no): yes
[oracle@database ~]$
在此查看视图dba_datapump_jobs
发现SYS_EXPORT_SCHEMA_02已经是not runing状态了