目的：
将一套10.2.0.5的UP-UNIX系统的数据，迁移到一台Windows环境下。
迁移方案：由于不同的操作系统，为了方便迁移，只是测试，使用EXP/IMP方式。
迁移流程：
一.源端导出
1）确定数据库大小
SQL>select round(sum(bytes)/1024/1024/1024,2) from dba_segments;
2)确定数据库版本，及字符集
SQL>select * from nls_database_parameters;
NLS_CHARACTERSET US7ASCII 数据库字符集
NLS_NCHAR_CHARACTERSET AL16UTF16 国家字符集
NLS_RDBMS_VERSION 10.2.0.4.0 数据库版本
3）迁移的用户数量
SQL> select 'create user '||a.username||' identified by values '''||b.password||''';' from dba_users a,user$ b where
b.name=a.username and a.username not like '%SYS%' and a.account_status='OPEN';
4）数据导出
$imp system/oracle file=/u01/dump/exp_full_20190325.dmp log=/u01/dump/exp_full_20190325.log full=y
5) Dump文件传输
选择二进制方式，使用ftp传输不要选择默认方式，否则默认转换后dump文件损坏
· ascii - 设置文件传输类型为ASCII，默认类型
· binary - 设置文件传输类型为binary（二进制传输）
ftp> binary --传输前进行修改传输类型，选择binary方式
200 Switching to Binary mode.
传输后，通过ls -lrt 等方式对比字节数量，确认无误.
IMP-00010 During Import using a Transferred File (文档 ID 157954.1)
CHANGES
Copied export dump file from Windows to Solaris.
CAUSE
Export file was copied/transferred as ASCII file instead of binary.
SOLUTION
Copy/Transfer the export dump file as binary not ASCII.
二、目标端导入前准备
1)数据库版本与源端一致。
2）创建永久、临时表空间
select tablespace_name,sum(bytes)/1024/1024/1024 g,sum(maxbytes)/1024/1024/1024 maxg from dba_data_files group by tablespace_name;
select tablespace_name,sum(bytes)/1024/1024/1024 g,sum(maxbytes)/1024/1024/1024 maxg from dba_temp_files group by tablespace_name;
3）迁移用户
3.1备份
新库，导入前，备份系统用户名称：
select username from dba_users;
3.2创建批量删除用户脚本：{导入不成功，使用脚本进行回退操作}
select 'drop user '||username||' cascade;' from dba_users where username not in('A','B');
3.2创建用户{源端查询后，将文本拷贝编辑，目标端执行即可}
SQL> select 'create user '||a.username||' identified by values '''||b.password||''';' from dba_users a,user$ b where
b.name=a.username and a.username not like '%SYS%' and a.account_status='OPEN';
对用户修改默认表空间
SQL> select 'alter user '||username||' default tablespace '||DEFAULT_TABLESPACE||';' from dba_users where ACCOUNT_STATUS='OPEN' and username not like '%SYS%';
4）用户授权【备选方案，实际操作中imp导入自动进行】
源端TEST4需要检查是否存在非默认角色
SQL> select 'grant '||GRANTED_ROLE||' TO '||GRANTEE||';' FROM DBA_ROLE_PRIVS;
SQL> select 'grant '||PRIVILEGE||' to '||GRANTEE||';' from dba_sys_privs;
5）参数修改
5.1.参数值备份
show parameter workarea_size_policy
show parameter sort_area_size
show parameter db_file_multiblock_read_count
show parameter "_sort_multiblock_read_count"
5.2.参数修改
alter system set workarea_size_policy = manual;
alter system set sort_area_size=1024000000 scope=spfile;
alter system set db_file_multiblock_read_count= 128;
6）.导入操作
imp user/pwd file=/u01/dump/exp_full_20190325.dmp log=/u01/dump/imp_full_20190325.log full=y
commit=y feedback=10000 buffer=10240000 ignore=y
7）数据对比
SQL> select object_type,count(*) from dba_objects group by object_type order by 2;
SQL> select object_type,count(*) from dba_objects where owner='SYS' group by object_type order by 2;
SQL>select round(sum(bytes)/1024/1024/1024,2) from dba_segments;
SQL> select tablespace_name,count(*) from dba_segments group by tablespace_name;
SQL> select SEGMENT_TYPE,count(*) from dba_segments where tablespace_name='USERS' group by SEGMENT_TYPE;
7)参数回退
SQL> alter system set workarea_size_policy =AUTO;
SQL> alter system set sort_area_size=65536 scope=spfile;
SQL> alter system set db_file_multiblock_read_count= 16;
8)失效对象重新编译
编译失效对象：
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql