近期应朋友邀请协助处理一起oracle数据库跨平台迁移的项目,需求为迁移centos 7.2 linux的oracle12.2数据库至windows server 2016操作系统上,并做好数据备份。虽然个人不建议数据库跑在windows下,但由于业主方懂linux系统的人员有限,故此整理文档如下,供后期参考。

1.环境检查及准备

SRE实战,互联网时代守护先锋!让网站飞一会, 阿里云优惠促销大全。
  • 检查linux系统的使用空间

  • 准备等量的windows系统环境

  • 书写rman下的0级备份脚本

  • 制定迁移计划和停机时间

由于linux系统空间已接近使用完毕且无法新增硬盘,考虑不影响正式环境的情况下,采用挂载windows共享目录的方式进行存储原有oracle数据库的相关备份文件。

#挂载共享目录脚本
mount -t cifs -o \
username=administrator,\
password=password,rw,uid=500,gid=501,\
dir_mode=0777,file_mode=0777 //windows的IP地址/共享目录 /mnt/backup/
#uid和gid分别为oracle用户的用户ID和所属组的ID号。

2.全备linux上的oracle数据库

#rman全量0备份脚本
rman log='/mnt/backup/rman/db_rman0.log' append <<EOF
connect target /;
run{
allocate channel ch1  device type disk;
allocate channel ch2  device type disk;
allocate channel ch3  device type disk;
sql 'alter system checkpoint';
sql 'alter system switch logfile';
sql 'alter system archive log current' ;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/mnt/backup/rman/ctl_bakup.ctl';
backup incremental level 0 filesperset 10 tag 'db0' format '/mnt/backup/rman/db0_%d_%T_%s' database 
include current controlfile plus archivelog filesperset 10 format '/mnt/backup/rman/arc0_%d_%T_%s';
backup format '/mnt/backup/rman/ctl_%U.%T' current controlfile;
backup spfile format '/mnt/backup/rman/spfile_%d_%s_%T';
crosscheck backup of database;      
crosscheck archivelog all;
crosscheck backup of controlfile;
release channel ch1;
release channel ch2;
release channel ch3;
}
EXIT;
EOF
#分别备份oracle数据库的控制文件、数据文件、归档日志和参数文件。

3.迁移相关备份文件至目标服务器

4.目标服务器安装oracle软件
Oracle12c跨平台迁移之linux至windows SRE实战 第1张
Oracle12c跨平台迁移之linux至windows SRE实战 第2张
Oracle12c跨平台迁移之linux至windows SRE实战 第3张
Oracle12c跨平台迁移之linux至windows SRE实战 第4张
Oracle12c跨平台迁移之linux至windows SRE实战 第5张
Oracle12c跨平台迁移之linux至windows SRE实战 第6张
Oracle12c跨平台迁移之linux至windows SRE实战 第7张
Oracle12c跨平台迁移之linux至windows SRE实战 第8张
Oracle12c跨平台迁移之linux至windows SRE实战 第9张
Oracle12c跨平台迁移之linux至windows SRE实战 第10张
Oracle12c跨平台迁移之linux至windows SRE实战 第11张

5.目标服务器创建相关目录

#新建目录
md D:\u01\app\oracle\
cd D:\u01\app\oracle\
md admin\alov2 oradata\alov2 archivelog 
cd D:\u01\app\oracle\admin\alov2
md adump bdump cdump dpdump udump pfile
#新建密码文件
orapwd file=D:\app\admin\virtual\product\12.2.0\dbhome_1\dbs\orapwalov2 password=’Shbus12#$’
#新建监听sid
oradim -new -sid alov2

注册表新增sid字符串值
Oracle12c跨平台迁移之linux至windows SRE实战 第12张

6.修改参数文件并启动数据库

#参数文件信息D:\test.ora
alov2.__data_transfer_cache_size=0
alov2.__db_cache_size=8858370048
alov2.__inmemory_ext_roarea=0
alov2.__inmemory_ext_rwarea=0
alov2.__java_pool_size=268435456
alov2.__large_pool_size=1409286144
alov2.__oracle_base='D:\app\admin\virtual'#ORACLE_BASE set from environment
alov2.__pga_aggregate_target=4429185024
alov2.__sga_target=21474836480
alov2.__shared_io_pool_size=536870912
alov2.__shared_pool_size=8053063680
alov2.__streams_pool_size=134217728
*._bloom_filter_enabled=TRUE
*._datafile_open_errors_crash_instance=FALSE
*._datafile_write_errors_crash_instance=FALSE
*._drop_stat_segment=1
*._enable_NUMA_optimization=FALSE
*._ges_direct_free_res_type='CTARAHDXBB'
*._index_partition_large_extents='FALSE'
*._keep_remote_column_size=TRUE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_ads_use_result_cache=FALSE
*._optimizer_aggr_groupby_elim=FALSE
*._optimizer_dsdir_usage_control=0
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_null_aware_antijoin=TRUE
*._optimizer_reduce_groupby_key=FALSE
*._optimizer_unnest_scalar_sq=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._report_capture_cycle_time=0
*._sql_plan_directive_mgmt_control=0
*._undo_autotune=FALSE
*._upgrade_capture_noops=FALSE
*._upgrade_optim=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='D:\u01\app\oracle\admin\alov2\adump'
*.audit_trail='NONE'
*.compatible='12.2.0'
*.control_files='D:\u01\app\oracle\oradata\alov2\control01.ctl','D:\u01\app\oracle\oradata\alov2\control02.ctl','D:\u01\app\oracle\oradata\alov2\control03.ctl'
*.db_block_size=8192
*.db_name='alov2'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='D:\app\admin\virtual'
*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=alov2XDB)'
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
*.filesystemio_options='SETALL'
*.inmemory_size=2147483648
*.log_archive_dest_1='LOCATION=D:\u01\app\oracle\archivelog\'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.parallel_force_local=TRUE
*.pga_aggregate_limit=0
*.pga_aggregate_target=4164m
*.processes=3200
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.resource_manager_plan='force:'
*.sga_max_size=21474836480
*.sga_target=21474836480
*.undo_tablespace='UNDOTBS1'

还原备份的控制文件并以mount的方式启动数据库

#配置恢复监听文件并在sql窗口下执行
startup nomount pfile='D:\test.ora';
#rman窗口下执行配置数据库的DBID
set DBID=原DBID;
#rman恢复控制文件,也可通过备份的控制文件直接拷贝恢复。
restore controlfile from 'D:\share\rman\ctl_ALOV2_5876_20191028_1';
alter database mount;

7.恢复数据库并重新定义文件目录

#rman窗口下执行
catalog start with 'D:\share\rman';
list backupset;
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
set newname for datafile 1 to 'D:\u01\app\oracle\oradata\alov2\system01.dbf';
set newname for datafile 2 to 'D:\u01\app\oracle\oradata\alov2\undotbs01.dbf';
set newname for datafile 3 to 'D:\u01\app\oracle\oradata\alov2\sysaux01.dbf';
set newname for datafile 4 to 'D:\u01\app\oracle\oradata\alov2\users01.dbf';
set newname for datafile 5 to 'D:\u01\app\oracle\oradata\alov2\TANG01.dbf';
set newname for datafile 6 to 'D:\u01\app\oracle\oradata\alov2\TANG02.dbf';
set newname for datafile 7 to 'D:\u01\app\oracle\oradata\alov2\ALODB_DATA01.dbf';
set newname for datafile 8 to 'D:\u01\app\oracle\oradata\alov2\FOLLOWUP01.dbf';
set newname for datafile 9 to 'D:\u01\app\oracle\oradata\alov2\FOLLOWUP02.dbf';
set newname for datafile 10 to 'D:\u01\app\oracle\oradata\alov2\ALODB_DATA02.dbf';
set newname for datafile 11 to 'D:\u01\app\oracle\oradata\alov2\ALODB_DATA03.dbf';
restore database;
switch datafile all;
release channel c1;
release channel c2;
release channel c3;
}
recover database preview;
list backup;
recover database until scn scnid;
alter database open resetlogs;
#sql窗口下执行创建spfile参数文件
create spfile from pfile=’D:\test.ora’;

8.配置windows下的oracle开机自启

Oracle12c跨平台迁移之linux至windows SRE实战 第13张
Oracle12c跨平台迁移之linux至windows SRE实战 第14张
Oracle12c跨平台迁移之linux至windows SRE实战 第15张
Oracle12c跨平台迁移之linux至windows SRE实战 第16张

9.配置windows下的oracle数据库的自动备份

#修复归档日志路径之清理无用备份信息:
execute sys.dbms_backup_restore.resetCfileSection(11);
#重新指定归档日志的存储路径:
catalog start with 'D:\u01\app\oracle\archivelog';
#制定备份计划:周三和周日全备其余增备
  • 0级全量备份脚本rmanbaklevel0.bat

rem ******Oracle backup start******
@echo off

::设置时间变量
set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%"

::删除一周前的备份数据
forfiles /p "E:\backup\rmandb" /m db0* -d -7 /c "cmd /c del /f @path"
forfiles /p "E:\backup\rmanlog" /m arc0* -d -7 /c "cmd /c del /f @path"

::执行备份操作
rman target / CMDFILE 'D:\app\admin\virtual\cmdfile\rmanbaklevel0.txt' LOG 'E:\backup\baklogs\rman_baklevel0_%Ymd%.log'

@echo on
rem ******Oracle backup end******
  • 0级全量备份文件rmanbaklevel0.txt

run{
allocate channel ch1  device type disk;
allocate channel ch2  device type disk;
allocate channel ch3  device type disk;
crosscheck archivelog all;
delete expired archivelog all;
sql 'alter system checkpoint';
sql 'alter system switch logfile';
sql 'alter system archive log current' ;
backup incremental level 0 filesperset 10 tag 'db0' format 'E:\backup\rmandb\db0_%d_%T_%s' database 
include current controlfile plus archivelog filesperset 10 format 'E:\backup\rmanlog\arc0_%d_%T_%s';
backup format 'E:\backup\rmanbak\ctl_%U.%T' current controlfile;
backup spfile format 'E:\backup\rmanbak\spfile_%d_%s_%T';
crosscheck backup of database;      
crosscheck archivelog all;
crosscheck backup of controlfile;
delete force noprompt archivelog all completed before 'sysdate-7';
release channel ch1;
release channel ch2;
release channel ch3;
}
exit
  • 1级增量备份脚本rmanbaklevel1.bat

rem ******Oracle backup start******
@echo off

::设置时间变量
set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%"

::删除一周前的备份数据
forfiles /p "E:\backup\rmandb" /m db1* -d -7 /c "cmd /c del /f @path"
forfiles /p "E:\backup\rmanlog" /m arc1* -d -7 /c "cmd /c del /f @path"

::执行备份操作
rman target / CMDFILE 'D:\app\admin\virtual\cmdfile\rmanbaklevel1.txt' LOG 'E:\backup\baklogs\rman_baklevel1_%Ymd%.log'

@echo on
rem ******Oracle backup end******
  • 1级增量备份文件rmanbaklevel1.txt

run{
allocate channel ch1  device type disk;
allocate channel ch2  device type disk;
allocate channel ch3  device type disk;
crosscheck archivelog all;
delete expired archivelog all;
sql 'alter system checkpoint';
sql 'alter system switch logfile';
sql 'alter system archive log current' ;
backup incremental level 1 filesperset 3 tag 'db1' format 'E:\backup\rmandb\db1_%d_%T_%s' database 
include current controlfile plus archivelog filesperset 3 format 'E:\backup\rmanlog\arc1_%d_%T_%s';
backup format 'E:\backup\rmanbak\ctl_%U.%T' current controlfile;
backup spfile format 'E:\backup\rmanbak\spfile_%d_%s_%T';
crosscheck backup of database;      
crosscheck archivelog all;
crosscheck backup of controlfile;
delete force noprompt archivelog all completed before 'sysdate-7';
release channel ch1;
release channel ch2;
release channel ch3;
}
exit

10.总结

综上,整个迁移过程执行完毕,稍微有点复杂,建议多试验几次即可,如有问题欢迎互动交流。当然还有其它的迁移方式,个人认为此种方案相对较为便捷。