[TOC]

ORACLE 逻辑迁移步骤

迁移用户

要迁移的用户：CEDE001,ABCDEFG01

数据对象 dba_objects 确认

1
2
3
select count(*) from dba_objects  where owner in ('CEDE001','ABCDEFG01');
select count(object_type),object_type from dba_objects where owner in ('CEDE001','ABCDEFG01') group by object_type;
select owner,object_type,count(*) from dba_objects where owner in ('CEDE001','ABCDEFG01')  group by owner,object_type order by 1,2;

检查数据库组件

1
2
col comp_name for a60
select comp_name,version, status from dba_registry;

检查表空间

目标库和源库都执行，进行比较

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT a.TABLESPACE_NAME "TableSpace Name",
       round(a.BYTES/1024/1024) "Total MB",
       round((a.BYTES-nvl(b.BYTES, 0)) /1024/1024) "Used MB ",
       nvl(round(b.BYTES/1024/1024), 0) "Free MB ",
       round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) "Pct Free",
       a.AUTO
FROM
  (SELECT TABLESPACE_NAME,
          sum(BYTES) BYTES,
          max(AUTOEXTENSIBLE) AUTO
   FROM sys.dba_data_files
   GROUP BY TABLESPACE_NAME) a,
  (SELECT TABLESPACE_NAME,
          sum(BYTES) BYTES
   FROM sys.dba_free_space
   GROUP BY TABLESPACE_NAME) b
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
ORDER BY ((a.BYTES-b.BYTES)/a.BYTES) DESC;

检查是否有 users 等系统表空间

查询是否有使用users等系统默认的表空间，如果有的话，导入的时候需要使用 remap_tablespace

1
2
3
4
5
SQL> select distinct(tablespace_name) from dba_segments where owner ='CEDE001' or owner='ABCDEFG01';
TABLESPACE_NAME
------------------------------
ABCDEFG01DATA
ABCDEFG01INDX

检查directory

1
2
3
4
5
6
7
8
col directory_name for a25
col DIRECTORY_PATH for a60
col owner for a15
select owner,directory_name,directory_path from dba_directories;

SELECT DBMS_LOB.substr(DBMS_METADATA.get_ddl('DIRECTORY',DIR.directory_name),4000,1)
FROM DBA_DIRECTORIES DIR
WHERE directory_name NOT in('DATA_PUMP_DIR','ORACLE_OCM_CONFIG_DIR');
1
2
3
4
5
6
7
8
9
10
col db_link for a30
col username for a20
col host for a30
select * from dba_db_links where owner='PUBLIC';

col owner format a15;
col db_link format a20;
col host format a20;
set heading on
select * from dba_db_links;

如存在db_link，确保准备好创建脚本

1
2
select DBMS_LOB.substr(DBMS_METADATA.GET_DDL('DB_LINK',dl.db_link,dl.owner),4000,1) FROM DBA_DB_LINKS dl;）
select DBMS_LOB.substr(DBMS_METADATA.get_ddl('DB_LINK',DL.DB_LINK,'PUBLIC'),4000,1) from dba_db_links DL where owner='PUBLIC';

检查同义词

1
SELECT * FROM dba_synonyms WHERE table_owner in ('ABCDEFG01','CEDE001');

如存在，准备生成同义词脚本

1
SELECT 'CREATE  PUBLIC SYNONYM '|| synonym_name|| ' FOR '|| table_owner|| '.'|| table_name|| ';' ROM dba_synonyms WHERE table_owner in ('ABCDEFG01','CEDE001');

检查job

1
2
3
4
5
col schema_user for a12
col interval for a30
col what for a60
select job,what,schema_user,interval from dba_jobs  order by what;
select owner,job_name from dba_scheduler_jobs;

查看角色 RESOURCE 权限

1
select Privilege from ROLE_SYS_PRIVS where ROLE=upper('RESOURCE');

新环境创建表空间

创建表空间

1
2
3
4
create tablespace CEDE001TAB  datafile '+WYDB_DATA/' size 1024M autoextend on;
create tablespace CEDE001IND  datafile '+WYDB_DATA/' size 1024M autoextend on;
create temporary tablespace CEDE001TEMP tempfile '+WYDB_DATA/' size 1024M autoextend on;
create temporary tablespace ABCDEFG01TEMP  tempfile '+WYDB_DATA/' size 1024M autoextend on;

新环境创建DIRECTORY

1
CREATE OR REPLACE DIRECTORY impdir AS '/u01/app/oracle';

检查 tnsname.ora、listener.ora、sqlnet.ora

1
2
3
4
cd $ORACLE_HOME/network/admin
cat listener.ora  
cat tnsnames.ora  
cat sqlnet.ora

删除schemas

如果已经存在的测试的同名schema
删除schema命令：

1
2
drop user CEDE001 cascade;
drop user ABCDEFG01 cascade;

起停生产库

1
2
lsnrctl stop
kill -9 `ps -ef | grep LOCAL=NO | awk '{ print $2}'`

锁定用户脚本

1
2
--ALTER USER CEDE001 ACCOUNT LOCK ;
--ALTER USER ABCDEFG01 ACCOUNT LOCK ;

集群 

1
2
3
4
srvctl stop listener
srvctl stop scan_listener
srvctl stop database -d xxdb
srvctl start database -d xxdb

导出\导入数据

查看磁盘空间，dg -g ， 磁盘空间是否足够存放dmp文件
源dir：directory=expdpdir /u01/app/oracle/dump
源主机

1
expdp expfulluser/expfulluser directory=expdpdir dumpfile=xx_$(date +%F).dmp schemas=CEDE001,ABCDEFG01  exclude=STATISTICS logfile=xx_$(date +%F)_expdp.log 
1
2
3
4
cd  /u01/app/oracle/dump
csum xx_$(date +%F)*.dmp 
---scp传输dmp文件
scp  xx_$(date +%F)*.dmp  IP 地址:/u01/app/oracle

目标主机：

1
cd /u01/app/oracle

计算比较md5值

1
csum xx_$(date +%F)*.dmp

导入数据

1
impdp expfulluser/expfulluser directory=impdir dumpfile=xx_$(date +%F).dmp  schemas=CEDE001,ABCDEFG01 logfile=xx_$(date +%F)_impdp.log

导入完成之后比较数据对象数量

迁移前后数据对象数量比较。

1
2
3
select count(*) from dba_objects  where owner in ('CEDE001','ABCDEFG01');
select count(object_type),object_type from dba_objects where owner in ('CEDE001','ABCDEFG01') group by object_type;
select owner,object_type,count(*) from dba_objects where owner in ('CEDE001','ABCDEFG01')  group by owner,object_type order by 1,2;

编译失效对象

1
exec utl_recomp.recomp_parallel(8);

统计信息收集

1
2
3
4
5
6
7
 vi gtd.sh
sqlplus / as sysdba<<_EOF
exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();
dbms_stats.gather_schema_stats(ownname=>'CEDE001',cascade=>TRUE,degree=>4,estimate_percent=>10);
exec dbms_stats.gather_schema_stats(ownname=>'ABCDEFG01',cascade=>TRUE,degree=>4,estimate_percent=>10);
_EOF
nohup sh gtd.sh &

原文作者: liups.com

原文链接: http://liups.com/posts/f5bd3d20/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议