【Oracle】利用数据泵迁移数据库，以迁移用户schema为例

Oracle数据泵是Oracle提供的更新、更灵活的数据导入、导出工具，数据泵可以用来导出表，表空间，用户Schema等

但也有限制，只能在服务器端进行。

一、准备工作

1.目标数据库已经安装，源数据库能正常运行，Linux安装Oracle的细节详见《【Oracle】linux下安装Oracle11g数据库》

2.查看目标数据库的版本与源数据库的版本，当目标数据的版本高于源数据库的版本时，导出需指定版本号

目标数据库的版本信息

<span class="pln">SQL</span><span class="pun">&amp;</span><span class="pln">gt</span><span class="pun">;</span><span class="pln"> select </span><span class="pun">*</span><span class="pln"> from v$version</span><span class="pun">;</span>

2.3. BANNER
4. --------------------------------------------------------------------------------
5.

<span class="typ">Oracle</span><span class="typ">Database</span><span class="lit">11g</span><span class="typ">Enterprise</span><span class="typ">Edition</span><span class="typ">Release</span><span class="lit">11.2</span><span class="pun">.</span><span class="lit">0.1</span><span class="pun">.</span><span class="lit">0</span><span class="pun">-</span><span class="typ">Production</span>

<span class="pln">PL</span><span class="pun">/</span><span class="pln">SQL </span><span class="typ">Release</span><span class="lit">11.2</span><span class="pun">.</span><span class="lit">0.1</span><span class="pun">.</span><span class="lit">0</span><span class="pun">-</span><span class="typ">Production</span>

<span class="pln">CORE </span><span class="lit">11.2</span><span class="pun">.</span><span class="lit">0.1</span><span class="pun">.</span><span class="lit">0</span><span class="typ">Production</span>

<span class="pln">TNS </span><span class="kwd">for</span><span class="typ">Linux</span><span class="pun">:</span><span class="typ">Version</span><span class="lit">11.2</span><span class="pun">.</span><span class="lit">0.1</span><span class="pun">.</span><span class="lit">0</span><span class="pun">-</span><span class="typ">Production</span>

<span class="pln">NLSRTL </span><span class="typ">Version</span><span class="lit">11.2</span><span class="pun">.</span><span class="lit">0.1</span><span class="pun">.</span><span class="lit">0</span><span class="pun">-</span><span class="typ">Production</span>

源数据库的版本信息

<span class="lit">1</span><span class="typ">Oracle</span><span class="typ">Database</span><span class="lit">11g</span><span class="typ">Enterprise</span><span class="typ">Edition</span><span class="typ">Release</span><span class="lit">11.2</span><span class="pun">.</span><span class="lit">0.1</span><span class="pun">.</span><span class="lit">0</span><span class="pun">-</span><span class="lit">64bit</span><span class="typ">Production</span>

<span class="lit">2</span><span class="pln"> PL</span><span class="pun">/</span><span class="pln">SQL </span><span class="typ">Release</span><span class="lit">11.2</span><span class="pun">.</span><span class="lit">0.1</span><span class="pun">.</span><span class="lit">0</span><span class="pun">-</span><span class="typ">Production</span>

3. 3"CORE 11.2.0.1.0 Production"
4.

<span class="lit">4</span><span class="pln"> TNS </span><span class="kwd">for</span><span class="lit">64</span><span class="pun">-</span><span class="pln">bit </span><span class="typ">Windows</span><span class="pun">:</span><span class="typ">Version</span><span class="lit">11.2</span><span class="pun">.</span><span class="lit">0.1</span><span class="pun">.</span><span class="lit">0</span><span class="pun">-</span><span class="typ">Production</span>

<span class="lit">5</span><span class="pln"> NLSRTL </span><span class="typ">Version</span><span class="lit">11.2</span><span class="pun">.</span><span class="lit">0.1</span><span class="pun">.</span><span class="lit">0</span><span class="pun">-</span><span class="typ">Production</span>

数据库版本一致，可以直接导出，导入

二、用数据泵导出数据库

1建立保存数据文件的地址

用root新建文件夹，并给oracle用户赋权，备注一，Linux赋权知识详见《【Linux】linux下权限知识整理》（一般在Linux安装Oracle时必须提前建立Oracle用户）

1. [root@localhost data]# mkdir /data/oracle/
2.

<span class="pun">[</span><span class="pln">root@localhost </span><span class="pun">/]#</span><span class="pln"> chown </span><span class="pun">-</span><span class="pln">R oracle</span><span class="pun">:</span><span class="pln">dba </span><span class="pun">/</span><span class="pln">data</span>

2.登录Oracle，采用主机认证方式

<span class="pun">[</span><span class="pln">oracle@localhost </span><span class="pun">~]</span><span class="pln">$ sqlplus </span><span class="str">'/as sysdba'</span>

2.3.

<span class="pln">SQL</span><span class="pun">*</span><span class="typ">Plus</span><span class="pun">:</span><span class="typ">Release</span><span class="lit">11.2</span><span class="pun">.</span><span class="lit">0.1</span><span class="pun">.</span><span class="lit">0</span><span class="typ">Production</span><span class="pln"> on </span><span class="typ">Wed</span><span class="typ">Mar</span><span class="lit">4</span><span class="lit">22</span><span class="pun">:</span><span class="lit">43</span><span class="pun">:</span><span class="lit">08</span><span class="lit">2015</span>

<span class="typ">Copyright</span><span class="pun">(</span><span class="pln">c</span><span class="pun">)</span><span class="lit">1982</span><span class="pun">,</span><span class="lit">2009</span><span class="pun">,</span><span class="typ">Oracle</span><span class="pun">.</span><span class="typ">All</span><span class="pln"> rights reserved</span><span class="pun">.</span>

5. Connected to:
6.

<span class="typ">Oracle</span><span class="typ">Database</span><span class="lit">11g</span><span class="typ">Enterprise</span><span class="typ">Edition</span><span class="typ">Release</span><span class="lit">11.2</span><span class="pun">.</span><span class="lit">0.1</span><span class="pun">.</span><span class="lit">0</span><span class="pun">-</span><span class="typ">Production</span>

<span class="typ">With</span><span class="pln"> the </span><span class="typ">Partitioning</span><span class="pun">,</span><span class="pln"> OLAP</span><span class="pun">,</span><span class="typ">Data</span><span class="typ">Mining</span><span class="pln"> and </span><span class="typ">Real</span><span class="typ">Application</span><span class="typ">Testing</span><span class="pln"> options</span>

8.9. SQL>;

3.在数据库服务器上创建DIRECTORY

1. create directory dir_dp as '/data/oracle';

4.查询已经创建的目录以及权限

<span class="pln">SELECT PRIVILEGE</span><span class="pun">,</span><span class="pln"> DIRECTORY_NAME</span><span class="pun">,</span><span class="pln"> DIRECTORY_PATH </span>

2.  FROM USER_TAB_PRIVS T, ALL_DIRECTORIES D 
3.

<span class="pln"> WHERE T</span><span class="pun">.</span><span class="pln">TABLE_NAME</span><span class="pun">(+)</span><span class="pun">=</span><span class="pln"> D</span><span class="pun">.</span><span class="pln">DIRECTORY_NAME </span>

<span class="pln"> ORDER BY </span><span class="lit">2</span><span class="pun">,</span><span class="lit">1</span><span class="pun">;</span>

5.按照Schema开始导出，命令如下

<span class="pln">expdp xtbg/xtbg dumpfile =2015-03-05EXP.DMP directory = dir_dp schemas=xtbg LOGFILE=xtbg_expdp.log </span>

二、将*.DMP导入到目标数据库

1.建立DIRECTORY步奏同上

2.创建表空间

1. --
2.

<span class="pun">--</span><span class="pln"> XTBG </span><span class="pun">(</span><span class="typ">Tablespace</span><span class="pun">)</span>

3. --
4. CREATE TABLESPACE XTBG DATAFILE
5.

<span class="str">'/home/oracle/app/oradata/orcl/XTBG_01.DBF'</span><span class="pln"> SIZE </span><span class="lit">138048K</span><span class="pln"> AUTOEXTEND ON NEXT </span><span class="lit">10M</span><span class="pln"> MAXSIZE </span><span class="lit">1000M</span>

6. LOGGING
7. ONLINE
8. EXTENT MANAGEMENT LOCAL AUTOALLOCATE
9. BLOCKSIZE 8K
10. SEGMENT SPACE MANAGEMENT AUTO
11. FLASHBACK ON;

3.创建用户

1. --
2.

<span class="pun">--</span><span class="pln"> XTBG </span><span class="pun">(</span><span class="typ">User</span><span class="pun">)</span>

3. --
4. CREATE USER XTBG
5.  IDENTIFIED BY xtbg
6.  DEFAULT TABLESPACE XTBG
7.  TEMPORARY TABLESPACE TEMP

4.对用户进行赋权

<span class="pln">grant dba</span><span class="pun">,</span><span class="pln">resource</span><span class="pun">,</span><span class="pln">connect to xtbg</span><span class="pun">;</span>

<span class="pln">grant read</span><span class="pun">,</span><span class="pln">write on directory dir_dp to xtbg</span><span class="pun">;</span>

5.开始导入

<span class="pln">impdp xtbg</span><span class="pun">/</span><span class="pln">xtbg directory</span><span class="pun">=</span><span class="pln">dir_dp dumpfile</span><span class="pun">=</span><span class="lit">2015</span><span class="pun">-<span style="color: #195f91;">03</span></span><span class="pun">-05</span><span class="lit">EXP</span><span class="pun">.</span><span class="pln">DMP schemas</span><span class="pun">=</span><span class="pln">xtbg</span>

版权声明：自由转载-非商用-非衍生-保持署名（创意共享3.0许可证）
引用列表：Oracle官网文档

来自为知笔记(Wiz)