Oracle生产库迁移操作步骤（实战）_oracle数据库迁移到新服务器

迁移方案

本次迁移方案描述：

背景：机房服务器需要替换
目标：从IDC机房旧服务器移到新服务器
优化：数据库版本 11.2.0.4 
方案：使用expdp方式并行压缩导出备份，且承保表单独导出提前一天先同步到本地库，使用plsqldeveloper 工具中表对比来完成差异数据同步。
时间预估：大体应该在2个小时内完成。
方案步骤：
 一、在新服务器上安装oracle数据库，并设置好参数
 二、在新服务器上创建角色、表空间、用户、权限
 三、使用expdp/impdp实现数据内容迁移
 四、检查迁移对象是否都过来了
 五、其他改动 
1
2
3
4
5
6
7
8
9
10
11
12
13

一、安装Oracle

1.安装步骤忽略

2.设置Oracle参数

--建表时立即分配空间
alter system set deferred_segment_creation=FALSE;
--设置最大连接数
select value from v$parameter where name ='processes'; --数据库允许的最大连接数
alter system set processes = 1000 scope = spfile; --修改最大连接数

--开启归档

--开启闪回

--参数调优
方法一：关闭它
alter system set event='10949 TRACE NAME CONTEXT FOREVER' scope=spfile;
alter system set "_serial_direct_read"=false scope=both;
ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';

方法二：调高下限
alter system set "_small_table_threshold"=2621440 scope=spfile sid='*'; -- 约2G 
alter session set "_small_table_threshold"=2621440 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

二、创建表空间、创建角色、用户、并赋予权限

1.创建表空间

--创建实例表空间,可将表空间文件分散到不同磁盘
CREATE TABLESPACE dianqi LOGGING DATAFILE
  'D:\oracle\oradata\dianqi0.dbf'  size 1000M autoextend on next 640M maxsize 10000M,
  'D:\oracle\oradata\dianqi1.dbf'  size 1000M autoextend on next 640M maxsize 10000M,
  'D:\oracle\oradata\dianqi2.dbf'  size 1000M autoextend on next 640M maxsize 10000M,
  'D:\oracle\oradata\dianqi3.dbf'  size 1000M autoextend on next 640M maxsize 10000M,
  'D:\oracle\oradata\dianqi4.dbf'  size 1000M autoextend on next 640M maxsize 10000M,
  'd:\oracle\oradata\dianqi5.dbf'  size 1000M autoextend on next 640M maxsize 10000M,
  'd:\oracle\oradata\dianqi6.dbf'  size 1000M autoextend on next 640M maxsize 10000M,
  'd:\oracle\oradata\dianqi7.dbf'  size 1000M autoextend on next 640M maxsize 10000M,
  'd:\oracle\oradata\dianqi8.dbf'  size 1000M autoextend on next 640M maxsize 10000M,
  'd:\oracle\oradata\dianqi9.dbf'  size 1000M autoextend on next 640M maxsize 10000M,
  'd:\oracle\oradata\dianqi10.dbf' size 1000M autoextend on next 640M maxsize 10000M,
  'd:\oracle\oradata\dianqi11.dbf' size 1000M autoextend on next 640M  MAXSIZE UNLIMITED;


--临时表空间
CREATE TEMPORARY TABLESPACE  dianqi_temp TEMPFILE
 'D:\oracle\oradata\dianqi_temp0.dbf'   size 5000M autoextend on next 640M maxsize 10000M,
 'D:\oracle\oradata\dianqi_temp1.dbf'  size 1000M autoextend on next 640M maxsize 10000M,
 'D:\oracle\oradata\dianqi_temp2.dbf'  size 1000M autoextend on next 640M maxsize 10000M,
 'D:\oracle\oradata\dianqi_temp3.dbf'  size 1000M autoextend on next 640M maxsize 10000M,
 'D:\oracle\oradata\dianqi_temp4.dbf'  size 1000M autoextend on next 640M  MAXSIZE UNLIMITED;

   --dianqi_index
CREATE  TABLESPACE DIANQI_INDEX  LOGGING DATAFILE
 'D:\oracle\oradata\DIANQI_INDEX0.DBF'  size 1000M autoextend on next 64M maxsize 10000M,
 'D:\oracle\oradata\DIANQI_INDEX1.DBF'  size 1000M autoextend on next 64M maxsize 10000M,
 'D:\oracle\oradata\DIANQI_INDEX2.DBF'  size 1000M autoextend on next 64M maxsize 10000M,
 'D:\oracle\oradata\DIANQI_INDEX3.DBF'  size 1000M autoextend on next 64M maxsize 10000M,
 'D:\oracle\oradata\DIANQI_INDEX4.DBF'  size 1000M autoextend on next 64M maxsize 10000M,
 'D:\oracle\oradata\DIANQI_INDEX5.DBF'  size 1000M autoextend on next 64M MAXSIZE UNLIMITED;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

2.新建用户

--创建用户
create user dianqi identified by "xxx" default tablespace dianqi temporary tablespace dianqi_temp;

--设置密码永不过期 （system账号下执行）
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
1
2
3
4
5

3.新建角色

-- 一般开发使用
create role LZ;
-- Grant/Revoke object privileges 
grant select on SYS.V_$SESSION to LZ;
grant select on SYS.V_$SESSTAT to LZ;
grant select on SYS.V_$STATNAME to LZ;
-- Grant/Revoke role privileges 
grant exp_full_database to LZ;
--grant imp_full_database to LZ;

-- Grant/Revoke system privileges 
grant create job to LZ;
grant create procedure to LZ;
grant create session to LZ;
grant create synonym to LZ;
grant create sequence to LZ;
grant create table to LZ;
grant create trigger to LZ;
grant create type to LZ;
grant create view to LZ;
grant debug any procedure to LZ;
grant debug connect session to LZ;
grant create database link to LZ with admin option;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

4.授权

grant lz to dianqi;

alter user dianqi quota unlimited on users;
alter user dianqi quota unlimited on system;
alter user dianqi quota unlimited on dianqi;
alter user dianqi quota unlimited on dianqi_index;
1
2
3
4
5
6

三、备份/还原

expdp/impdp方式

--expdb方式导出

--导出前先创建目录--
select * from dba_directories;
--注意：这里只是在oracle内部创建了目录参数，服务上需要手工创建dump_dir文件夹
create or replace directory DUMP_DIR as 'e:\oracle\dump_dir';

--给目录授权
--grant read,write on directory DUMP_DIR to system； 

--按用户导出,使用并行方式导出
expdp system/xxx@lzdb directory=DUMP_DIR  dumpfile=lzdb20240429_%U.dmp logfile=lzdb20240429_expdb.log schemas=dianqi filesize=1024m compression=all parallel=4

--导出全库,使用并行方式导出
expdp system/xxx@lzdb directory=DUMP_DIR  dumpfile=full_lzdb20240429_%U.dmp logfile=full_lzdb20240429_expdb.log full=y filesize=1024m compression=all parallel=4

--impdp 导入
--按用户导入
impdp system/"""Lzhy#System.$Suning"""@lzdb schemas=dianqi directory=DUMP_DIR dumpfile =lzdb20240429_%U.dmp logfile=lzdb20240429_impdb.log parallel=4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

exp/imp方式

rman方式

四、检查

-----------------------------查看表空间使用情况-------------------------------------------
-- 查看表空间使用率（包括临时表空间）
  select * from (
  Select a.tablespace_name,
  to_char(a.bytes/1024/1024,'99,999.999') total_bytes,
  to_char(b.bytes/1024/1024,'99,999.999') free_bytes,
  to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes,
  to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' use
  from (select tablespace_name,
  sum(bytes) bytes
  from dba_data_files
  group by tablespace_name) a,
  (select tablespace_name,
  sum(bytes) bytes
  from dba_free_space
  group by tablespace_name) b
  where a.tablespace_name = b.tablespace_name
  union all
  select c.tablespace_name,
  to_char(c.bytes/1024/1024,'99,999.999') total_bytes,
  to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes,
  to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes,
  to_char(d.bytes_used*100/c.bytes,'99.99') || '%' use
  from
  (select tablespace_name,sum(bytes) bytes
  from dba_temp_files group by tablespace_name) c,
  (select tablespace_name,sum(bytes_cached) bytes_used
  from v$temp_extent_pool group by tablespace_name) d
  where c.tablespace_name = d.tablespace_name
  )
  order by tablespace_name;


--2.查看文件是否自动扩展
  select d.file_name,d.tablespace_name,d.autoextensible,d.* from dba_data_files d;
  --如果想查看临时表空间文件是否自动扩展
  select d.file_name,d.tablespace_name,d.autoextensible,d.* from dba_temp_files d;


检查迁移对象是否都过来了
--检查迁移过来的对象数量
select * from all_tab_comments t  where upper(t.owner)='dianqi';

select * from user_tables
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

五、其他

--创建闪回目录
cd d:\oracle\
mkdir flashback (生成闪回目录)
1
2
3

---------------------------------开归档再开闪回--------------------------------------
select name,current_scn,flashback_on  from v$database; --查看是否开启闪回功能
--创建闪回目录
cd d:\oracle\
mkdir flashback (生成闪回目录)

--alter system set db_recovery_file_dest='d:\oracle\flashback' scope=both;     --设置目录
alter system set db_recovery_file_dest_size=80G scope=both;                 --设置大小
alter system set db_flashback_retention_target=2880;                         --配置保留时间 1440为一天

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog; --开启归档
SQL> alter database flashback on; --开启闪回
SQL> alter database open;

select name,current_scn,flashback_on  from v$database;--查看是否开启闪回功能
select name,log_mode,open_mode from v$database; --查看是否开了归档

--归档及闪回空间使用情况
select * from v$flash_recovery_area_usage;   --查看空间占用率
select * from v$recovery_file_dest;                  --查看归档日志的存放地址;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22