热衷技术，热爱交流
分类： Oracle
2013-08-17 22:03:05
全库迁移和分区迁移：
一、全库迁移
全库迁移时，如果数据量比较少，可以考虑用exp/imp。全库导出实际是把数据库中的所有对象，包括用户，模式对象，角色，表空间，数据文件等全部导出。全库迁移一般需要重新建库，为了避免一些额外工作，目的库和源库的结构最好一样(包括实例名，相关路径和数据库名)。
1.导出整库：
exp "'sys/talent@localhost:1523/hexel as sysdba'" buffer=33691776 file=all_`date +%Y%m%d_%H:%M:%S`.dmp filesize=30m log=all_`date +%Y%m%d_%H:%M:%S`.log full=y
如果数据量太大，可以分为几个文件导出：
exp "'sys/talent@localhost:1523/hexel as sysdba'" buffer=35691776 file=all_`date +%Y%m%d_%H:%M:%S`_1.dmp,all_`date +%Y%m%d_%H:%M:%S`_2.dmp filesize=25m log=all_`date +%Y%m%d_%H:%M:%S`.log full=y
2.建目的库：
（1）建立相关路径：
mkdir -p $ORACLE_BASE/admin/hr/adump
mkdir -p $ORACLE_BASE/admin/hr/bdump
mkdir -p $ORACLE_BASE/admin/hr/cdump
mkdir -p $ORACLE_BASE/admin/hr/udump
mkdir -p $ORACLE_BASE/admin/hr/pfile
mkdir -p $ORACLE_BASE/oradata2/hr
mkdir -p $ORACLE_BASE/fast_recovery_area/hr
（2）生成密码文件：
orapwd file=$ORACLE_HOME/dbs/orapwhr password=talent entries=5 force=y
（3）建立参数文件：
cat $ORACLE_HOME/dbs/inithr.ora
db_name='hr'
memory_target=300M
processes = 100
audit_file_dest='/u01/admin/hr/adump'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/fast_recovery_area/hr'
db_recovery_file_dest_size=1G
diagnostic_dest='/u01'
dispatchers='(PROTOCOL=TCP) (SERVICE=hrdb)'
local_listener='(address=(protocol=tcp)(host=hexel)(port=1523))'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files='/u01/oradata2/hr/control01.ctl','/u01/fast_recovery_area/hr/control02.ctl'
service_names='hr'
（4）启动实例：
oracle[~/oracle/dbs]$ORACLE_SID=hr
oracle[~/oracle/dbs]$sqlplus / as sysdba
SYS >create spfile from pfile;
SYS >startup nomount;
（5）创建数据库：
CREATE DATABASE hr
USER SYS IDENTIFIED BY talent
USER SYSTEM IDENTIFIED BY talent
LOGFILE GROUP 1 ('/u01/oradata2/hr/redo01.log') SIZE 100M,
GROUP 2 ('/u01/oradata2/hr/redo02.log') SIZE 100M,
GROUP 3 ('/u01/oradata2/hr/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/oradata2/hr/system01.dbf' SIZE 325M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/oradata2/hr/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/oradata2/hr/users01.dbf'
SIZE 300M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/oradata2/hr/temp01.dbf'
SIZE 200M REUSE
UNDO TABLESPACE UNDOTBS1
DATAFILE '/u01/oradata2/hr/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
（6）System登录，创建数据字典：
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/sqlplus/admin/pupbld.sql
@?/rdbms/admin/catexp.sql
3.导入数据：
数据导入时，由于很多对象可能已经存在了，所有会有很多错误提示信息。为了减少错误提示，可以进行下面操作：
删除除sys,system,outln以外的所有用户；
由于目的数据库中system的对象比较多，所以可以把system的对象清除掉
使用下列语句可以查看导入时需要执行的DDL语句（hr.sh）
imp "'sys/talent@localhost:1523/hr as sysdba'" file=all_20130817_12:16:45_1.dmp,all_20130817_12:16:45_2.dmp full=y commit=y ignore=yindexfile=hr.sh log=imp_full.log
执行导入:
imp "'sys/talent@localhost:1523/hr as sysdba'" file=all_20130817_12:16:45_1.dmp,all_20130817_12:16:45_2.dmp full=y commit=y ignore=y log=imp_full.log
数据导入时，oracle会根据dmp文件建立相应的用户，角色，表空间，分区等内容。由于表空间建立时数据文件的路径是由源库决定的，所以需要建立与源库一样的路径。
二、分区和模式迁移
实际工作中，一般很少会用到全库迁移，一般只要求迁移模式对象或者分区数据就可以了。与全库迁移不同，模式对象和分区迁移不会自动在目的库建立表空间、角色、用户等相关对象，它要求目的库已经具备了相关对象。
本例，源库hr已经经过脚本初始化，hr模式具备了相关对象。hr模式对象包括用的表、索引、视图和分区，现在要迁移模式hr到目的数据库hexel。
首先查看hr模式在源库中包含的对象
分区：
HR >col TABLE_NAME for a20
HR >col PARTITION_NAME for a20
HR >col TABLEspace_NAME for a15
HR >col FILE_NAME for a40
HR >select a.TABLE_NAME,a.PARTITION_NAME,a.TABLESPACE_NAME,b.FILE_NAME
from user_tab_partitions a,dba_data_files b where a.TABLESPACE_NAME=b.TABLESPACE_NAME;;
TABLE_NAME |PARTITION_NAME |TABLESPACE_NAME|FILE_NAME
--------------------|--------------------|---------------|---------------------------------
P_TIME |P20130817 |P20130817 |/u01/oradata2/hr/p20130817.dbf
P_TIME |P20130818 |P20130818 |/u01/oradata2/hr/p20130818.dbf
P_TIME |P20130819 |P20130819 |/u01/oradata2/hr/p20130819.dbf
TIME_RANGE_SALES |SALES_1998 |HR |/u01/oradata2/hr/hr.dbf
TIME_RANGE_SALES |SALES_1999 |HR |/u01/oradata2/hr/hr.dbf
TIME_RANGE_SALES |SALES_2001 |HR |/u01/oradata2/hr/hr.dbf
TIME_RANGE_SALES |SALES_2000 |HR |/u01/oradata2/hr/hr.dbf
对象：
HR >select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OWNER='HR';
OWNER |OBJECT_NAME |OBJECT_TYPE
----------|------------------------------|-------------------
HR |EMPLOYEES_SEQ |SEQUENCE
HR |LOCATIONS_SEQ |SEQUENCE
HR |DEPARTMENTS_SEQ |SEQUENCE
HR |TEST_TYPE |TYPE
HR |COUNTRIES |TABLE
HR |COUNTRY_C_ID_PK |INDEX
HR |DEPARTMENTS |TABLE
HR |DEPT_ID_PK |INDEX
HR |EMPLOYEES |TABLE
HR |EMP_EMAIL_UK |INDEX
HR |EMP_EMP_ID_PK |INDEX
HR |JOBS |TABLE
HR |JOB_ID_PK |INDEX
HR |JOB_HISTORY |TABLE
HR |JHIST_EMP_ID_ST_DATE_PK |INDEX
HR |LOCATIONS |TABLE
HR |LOC_ID_PK |INDEX
HR |P_TIME |TABLE PARTITION
HR |P_TIME |TABLE PARTITION
HR |P_TIME |TABLE PARTITION
HR |P_TIME |TABLE
HR |REGIONS |TABLE
HR |REG_ID_PK |INDEX
HR |STUDENT |TABLE
HR |SYS_C006388 |INDEX
HR |STUDENT1 |TABLE
HR |SYS_C006390 |INDEX
HR |STUDENT2 |TABLE
HR |TEST_TABLE |TABLE
HR |SYS_C006392 |INDEX
HR |TIME_RANGE_SALES |TABLE PARTITION
HR |TIME_RANGE_SALES |TABLE PARTITION
HR |TIME_RANGE_SALES |TABLE PARTITION
HR |TIME_RANGE_SALES |TABLE PARTITION
HR |TIME_RANGE_SALES |TABLE
HR |EMP_DETAILS_VIEW |VIEW
HR |HR_PR3 |PROCEDURE
HR |HR_PR4 |PROCEDURE
导出模式对象：
oracle[~]$exp 'hr/talent123@localhost:1523/hr' file=hr_`date +%Y%m%d_%H:%M:%S`.dmp \
log=hr`date +%Y%m%d_%H:%M:%S`.log
Export: Release 11.2.0.3.0 - Production on 星期六 8月 17 20:32:46 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HR
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HR
About to export HR's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HR's tables via Conventional Path ...
. . exporting table COUNTRIES 25 rows exported
. . exporting table DEPARTMENTS 27 rows exported
. . exporting table EMPLOYEES 107 rows exported
. . exporting table JOBS 19 rows exported
. . exporting table JOB_HISTORY 10 rows exported
. . exporting table LOCATIONS 23 rows exported
. . exporting table P_TIME
. . exporting partition P20130817 1 rows exported
. . exporting partition P20130818 1 rows exported
. . exporting partition P20130819 1 rows exported
. . exporting table REGIONS 4 rows exported
. . exporting table STUDENT 4 rows exported
. . exporting table STUDENT1 4 rows exported
. . exporting table STUDENT2 4 rows exported
. . exporting table TEST_TABLE 1 rows exported
. . exporting table TIME_RANGE_SALES
. . exporting partition SALES_1998 12 rows exported
. . exporting partition SALES_1999 4 rows exported
. . exporting partition SALES_2000 0 rows exported
. . exporting partition SALES_2001 4 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
目的库hexel刚经过相同的脚本初始化的，所以比源库的内容要少一些。例如，hexel数据库中hr模式对象的表p_time没有p20130818和p20130819分区。现在要追加这些分区，而且分区要符合原来的特点：一个分区一个表空间，对应一个数据文件.
建立和源库名字一样的表空间，否则数据无法导入：
HR >create tablespace p20130818 datafile '/u01/oradata2/hx/p20130818.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;
HR >create tablespace p20130819 datafile '/u01/oradata2/hx/p20130819.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;
分区不是必须建立的，不过也可以提前建立：
HR >alter table p_time add partition p20130818 values less than (TIMESTAMP' 2013-08-19 00:00:00') tablespace p20130818;
HR >alter table p_time add partition p20130819 values less than (TIMESTAMP' 2013-08-20 00:00:00') tablespace p20130819;
开始导入数据：
oracle[~]$imp hr/talent123@localhost:1523/hexel file=hr_20130817_20:32:46.dmp ignore=y log=hr.log full=y
……………………省略………………………
Column 6 MX 0 rows imported
. . importing partition "P_TIME":"P20130817" 1 rows imported
. . importing partition "P_TIME":"P20130818" 1 rows imported
. . importing partition "P_TIME":"P20130819" 1 rows imported
