使用Data Pump数据库系统切割迁移方案

OS:centos 7.5
Oracle11204 RAC

一、数据库迁移基本流程

1.新的数据库环境的准备，参数与源库相同
2.expdp导出数据，传输dump文件到新数据库环境
3.impdp导入新数据库环境
4.检查数据库完整性
5.新数据库环境启动监听对外服务
6.修改应用连接信息，启动应用验证

二、数据库迁移实施

查看源库与新库组件是否一致
创建与源库相同的服务名：

 srvctl add service -d '数据库名' -s '新的服务名' -r '首选实例名' -a '可用实例名' 

三、前期准备工作

-----查看数据库字符集
SELECT * FROM nls_database_parameters;
-----查看profile
set line 1300
SELECT * FROM dba_profiles;
-----查看temp
SELECT * FROM v$tempfile;
-----查看数据库有哪些表空间

set linesize 300 pagesize 2000
col File_2Name format a60
col TableSpace_Name format a33
col Status format a20
col Auto format a20
col Size_MB format a20
col Used_MB format a20
col Useds format a20

SELECT a.tablespace_name "TableSpace_Name",
       a.file_Name "File_Name",
       a.status "Status",
       a.autoextensible "Auto",
       TO_CHAR(NVL(a.bytes/1024/1024,0),'99G999G990D900') "Size_MB",
       TO_CHAR(NVL(f.bytes/1024/1024,0),'99G999G990D900') "Free_Size_MB"
  FROM dba_data_files a,
  (SELECT file_id,
          sum(bytes) bytes
     FROM dba_free_space group by File_id) f
   WHERE a.file_id=f.file_id(+)
   ORDER BY a.tablespace_name,a.file_id;
-----查看DBA_ROLE_PIVS
SELECT * FROM dba_role_privs WHERE grantee IN ('业务用户名');
-----查看DBA_SYS_PRIVS
SELECT * FROM dba_sys_privs WHERE grantee IN ('业务用户名') ORDER BY grantee;
-----查看ROLE
SELECT role FROM dba_roles;
------查看OBJECTS
SELECT owner,
       object_type,
       count(*)
   FROM dba_objects
  WHERE owner IN ('业务用户名')
 GROUP BY owner,object_type;
-----查看INVAILD OBJECT
SELECT owner,
       object_type,
       object_name,
       status
   FROM dba_objects
  WHERE status = 'INVALID'
  AND owner IN ('业务用户名');
-----查看DISABLED TRIGGER
SELECT owner,
       trigger_name,
       table_name,
       status
   FROM dba_triggers
  WHERE status = 'ENABLED'
  AND owner in ('业务用户名')
-----查看User default tablespace&default temporary talbespace
SELECT username,
       default_tablespace,
       temporary_tablespace
   FROM dba_users
  WHERE username IN ('业务用户名');
-----查看是否开启审计
show parameter audit;
-----查看public 同义词
SELECT owner,
       table_owner
   FROM dba_synonyms
  WHERE table_owner IN ('业务用户名')
-----查看public dblink
SELECT owner,
       db_link,
       username,
       host
   FROM all_db_links

四、tnsnames.ora检查

检查$ORACLE_HOME/network/admin/tnsname.ora是否有重复

五、数据库迁移实施步骤

导出数据

-----锁业务用户
alter user '业务用户名' account lock;
create directory '目录对象名' as '操作系统目录';
expdp \"/ as sysdba\" parfile=test.sql
##test.sql内容如下
directory='目录对象名'
parallel=6
logfile='日志文件名'
dumpfile=test%U.oraexpdp
schemas='业务用户名'
cluster=n
exclude=table:"in('table_name01','table_name02')"
exclude=index,statistics
-----之后要查看导出日志有没有问题

传输dump文件到新库环境
在新库创建表空间

-----以用户表空间
CREATE TABLESPACE '业务表空间名'  DATAFILE size 30767M AUTOEXTEND OFF;
ALTER TABLESPACE '业务表空间名' ADD DATAFILE SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE '业务表空间名' ADD DATAFILE SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE '业务表空间名' ADD DATAFILE SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE '业务表空间名' ADD DATAFILE SIZE 30767M AUTOEXTEND OFF;

新库环境要关闭归档
准备impdp

-----新库创建目录对象
chown -R oracle:oinstall '目录路径'
create directory '目录对象名' as '目录路径';
impdp \"/ as sysdba \" directory='目录对象' schemas='业务用户名' logfile='日志文件名' dumpfile=test%U.oraexpdp cluster=n remap_tablespace='原表空间名字':'新表空间名字' parallel=6

临时表创建

源库：
-----临时表脚本导出：
-----导出过程在源库生成/可以直接copy输出结果到目标端执行，执行前确保导出完成
set long 9999 linesize 300 pagesize9999 longc9999
SELECT dbms_metadata.get_ddl('TABLE',table_name,owner)||';'
  FROM dba_tables t 
 WHERE t.temporary='Y'
 AND owner IN ('业务用户名');
------新库执行导入【导出完成后才能执行/脚本内容需要编辑修改】
sqlplus / as sysdba

序列重建

------新库生成删除脚本并执行：
set line 200 pagesize 500
spool drop_sequence.sql
SELECT 'DROP SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME ||';'
  FROM dba_sequences
 WHERE sequence_owner IN ('业务用户名')
 AND cache_size<>0 
 AND max_value!=cache_size
UNION ALL
SELECT 'DROP SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||';'
  FROM dba_sequences
 WHERE sequence_owner IN ('业务用户名')
 AND cache_size=0 
 AND max_value!=cache_size
ORDER BY 1;
spool off
-----目标数据库执行【sqlplus / as sysdba】【导入完成后才能执行/脚本内容需要编辑修改】
@drop_sequence.sql

-----重建sequence：（源库执行）
spool recreate_sequence.sql
SELECT 'CREATE SEQUENCE '||
        SEQUENCE_OWNER||
        '.'||
        SEQUENCE_NAME||
        ' MINVALUE '||
        to_char(MIN_VALUE)||
        ' MAXVALUE '||
        to_char(MAX_VALUE)||
        ' INCREMENT BY '||
        to_char(INCREMENT_BY)||
        ' START WITH '||
        to_char(LAST_NUMBER+(INCREMENT_BY*CACHE_SIZE)*2)||
        ' CACHE '||
        CACHE_SIZE||
      CASE WHEN ORDER_FLAG='N' 
       THEN ' NOORDER ' 
       ELSE ' ORDER ' 
       END ||
      CASE WHEN CYCLE_FLAG='N' 
       THEN ' NOCYCLE;' 
       ELSE ' CYCLE;' 
       END SQL 
     FROM dba_SEQUENCEs 
    WHERE SEQUENCE_OWNER in ('业务用户名')
    AND CACHE_SIZE<>0
UNION ALLL
SELECT 'CREATE SEQUENCE '||
        SEQUENCE_OWNER||
        '.'||
        SEQUENCE_NAME||
        ' MINVALUE '||
        to_char(MIN_VALUE)||
        ' MAXVALUE '||
        to_char(MAX_VALUE)||
        ' INCREMENT BY '||
        to_char(INCREMENT_BY)||
        ' START WITH '||
        to_char(LAST_NUMBER+1)||
        ' NOCACHE '||
     CASE WHEN ORDER_FLAG='N' 
      THEN ' NOORDER ' 
      ELSE ' ORDER ' 
      END ||
     CASE WHEN CYCLE_FLAG='N' 
      THEN ' NOCYCLE;' 
      ELSE ' CYCLE;' 
      END SQL 
    FROM dba_SEQUENCEs 
   WHERE SEQUENCE_OWNER in ('业务用户名') 
   AND CACHE_SIZE=0
ORDER BY 1;
spool off;

新库执行@create_sequence.sql

六、数据完整性检查

-----无效对象编译
@$ORACLE_HOME/rdbms/admin/utlrp.sql
-----是否有新增失效对象
SELECT owner,
       object_type,
       object_name,
       status
     FROM dba_objects
    WHERE status = 'INVAILD'
    AND owner in ('业务用户名')
-----无效对象种类数量对比
SELECT owner,
       object_type,
       count(*)
     FROM dba_objects
    WHERE status = 'INVAILD'
    AND owner in ('业务用户名')
   GROUP BY owner,object_type
   ORDER BY owner,object_type
-----对比profile
SELECT * FROM dba_profiles;
-----对比DBA_ROLE_PRIVS
SELECT * FROM dba_role_privs WHERE grantee IN ('业务用户名');
-----对比DBA_SYS_PRIVS
SELECT * FROM dba_sys_privs WHERE grantee IN ('业务用户名');
-----查看表空间对象
SELECT username,
       default_tablespace,
       temporary_tablespace
     FROM dba_users
    WHERE username IN ('业务用户名');

七、启动应用

研发同事支持

八、回退方案

打开源数据库，启动监听