Oracle12c跨平台迁移之linux至windows | ドラゴンボールのBlog

需求为迁移centos 7.2 linux的oracle12.2数据库至windows server 2016操作系统上，并做好数据备份。虽然个人不建议数据库跑在windows下，但由于业主方懂linux系统的人员有限，故此整理文档如下，供后期参考。

1.环境检查及准备

检查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.迁移相关备份文件至目标服务器

压缩传输、直接拷贝、网络传输备份文件至目标服务器。
目标服务器安装JDK环境并下载oracle12c安装文件。

Jdk8u231下载路径：点击链接下载jdk8u231软件
Oracle12c下载路径：点击链接下载Oracle12c软件

4.目标服务器安装oracle软件

这里需要先安装数据库软件和配置监听，不需要建库。

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字符串值

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开机自启

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

总结

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