Oracle数据库从Linux平台到Windows跨平台迁移

1. 场景说明

1.1.客户原环境

OS：Red Hat Enterprise Linux Server release 6.5 DB：Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production 服务器：华为RH2485 V2 存储：OceanStor S5600T

1.2.云上目标主机环境

OS：Windows Server 2008 R2 SP1 DB：Oracle 11G Release 11.2.0.3.0

1.3.客户需求

如上原环境，客户希望业务平台平滑往华为公有云上迁移； 因为某些业务层面的原因，公有云上的目标数据库的操作系统需要改为Windows 2008操作系统，故而需要将原有Linux平台下的Oracle数据库，跨平台迁移到安装在Win2008上的Oracle里；

2. 解决方案

2.1.基本迁移方案说明：

根据以上客户的需求，Oracle原有的RMAN类型的备份/恢复式的迁移，经测试后不可用，故而本次我们采用RMAN的convert database的方式来执行；

2.2.数据库迁移限制：

--使用convert database来传输数据库，有以下限制：

 ----以下部分不能被传输
   1 redo file
   2 controlfile
   3 外部表和目录
   4 密码文件

2.3.Convert方式迁移过程：

2.3.0.目标基本环境准备：

--因为目标数据库只安装了数据库软件，没有建库，故而需要先创建对应的oracle服务：

C:\>oradim -new -sid oadb
-- 实例已创建。

C:\>net start | findstr Oracle
  Oracle OADB VSS Writer Service
  OracleMTSRecoveryService
  OracleRemExecService
  OracleServiceOADB

C:\>net start OracleServiceOADB
--启动新创建的服务：

C:\>oradim -del -sid oadb
--若是有过期的服务需要删除：

2.3.1.检查原数据库支持的可传输平台：

SQL> col platform_name format a40;
SQL> select * from v$transportable_platform order by platform_id;
​
PLATFORM_ID PLATFORM_NAME         ENDIAN_FORMAT
----------- ---------------------------------------- --------------
   1 Solaris[tm] OE (32-bit)       Big
   2 Solaris[tm] OE (64-bit)       Big
   3 HP-UX (64-bit)         Big
   4 HP-UX IA (64-bit)         Big
   5 HP Tru64 UNIX         Little
   6 AIX-Based Systems (64-bit)       Big
   7 Microsoft Windows IA (32-bit)     Little
   8 Microsoft Windows IA (64-bit)     Little
   9 IBM zSeries Based Linux       Big
   10 Linux IA (32-bit)         Little
   11 Linux IA (64-bit)         Little
   12 Microsoft Windows x86 64-bit     Little
   13 Linux x86 64-bit         Little
   15 HP Open VMS          Little
   16 Apple Mac OS         Big
   17 Solaris Operating System (x86)     Little
   18 IBM Power Based Linux       Big
   19 HP IA Open VMS         Little
   20 Solaris Operating System (x86-64)     Little
   21 Apple Mac OS (x86-64)       Little
20 rows selected.

--确认源&目标数据库的编码字符序一致：

SQL> select tp.endian_format
 2 from v$transportable_platform tp,v$database d
 3 where tp.platform_name=d.platform_name;
​
ENDIAN_FORMAT
\--------------
Little

--将源数据库启动在只读模式下： (在此之前可以在数据库里创建一些测试数据，待恢复完成后确认使用）

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup open read only
ORACLE 例程已经启动。
Total System Global Area 2471931904 bytes
Fixed Size         2257712 bytes
Variable Size       553651408 bytes
Database Buffers     1895825408 bytes
Redo Buffers        20197376 bytes
数据库装载完毕。
数据库已经打开。

2.3.2.开始转换数据库

--使用RMAN脚本：

RMAN> convert database
new database oadb
skip offline
transport script '/oracle/trans/trans_script.sql'
to platform 'Microsoft Windows x86 64-bit'
format '/oracle/trans/%N.dbf' ;

--以下是命令的输出结果：

Starting conversion at source at 11-OCT-17
using channel ORA_DISK_1
​
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.XMLDIR found in the database
​
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/oracle/oradata/oadb/system01.dbf
converted datafile=/oracle/trans/SYSTEM.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/oracle/oradata/oadb/sysaux01.dbf
converted datafile=/oracle/trans/SYSAUX.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/oracle/oradata/oadb/undotbs01.dbf
converted datafile=/oracle/trans/UNDOTBS1.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/oracle/oradata/oadb/users01.dbf
converted datafile=/oracle/trans/USERS.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Edit init.ora file /oracle/trans/init_DUMMY_TBS_0_0_0.dbf.ora. This PFILE will be used to create the database on the target platform
Run SQL script /oracle/trans_script.sql on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 11-OCT-17
RMAN>

2.3.3.传输备份的数据库文件到目标服务器

利用mount命令的cifs格式，挂载目标服务器：

# mount -t cifs -o username='admin',password='admin.com' 192.168.10.52:/ftp_share /media/cifs
# cp -r /oracle/trans /media/cifs/

复制后的文件集：

init_DUMMY_TBS_0_0_0.dbf.ora
SYSAUX.dbf
SYSTEM.dbf
UNDOTBS1.dbf
USERS.dbf

2.3.4.目标服务器开始恢复

--将各文件复制到正确的位置

copy init_DUMMY_TBS_0_0_0.dbf.ora D:\app\Administrator\product\11.2.0\dbhome_1\database\initoadb.ora
copy SYSAUX.dbf D:\app\oradata\oadb\
copy SYSTEM.dbf D:\app\oradata\oadb\
copy UNDOTBS1.dbf D:\app\oradata\oadb\
copy USERS.dbf D:\app\oradata\oadb\

2.3.5.开始恢复数据库

1）修改参数文件，注意其中的路径信息要修改正确；

 control_files      = "D:\app\oradata\oadb\control01.dbf"
 db_recovery_file_dest  = "D:\app\Administrator\fast_recovery_area\oadb"
 db_recovery_file_dest_size= 4322230272
 audit_file_dest     = "D:\app\Administrator\admin\oadb\adump"
 db_name         = "OADB"
 remote_login_passwordfile= "EXCLUSIVE"
 db_domain        = ""
 processes        = 150
 sga_target        = 2483027968
 db_block_size      = 8192
 compatible        = "11.2.0.0.0"
 log_archive_format    = "%t_%s_%r.dbf"
 undo_tablespace     = "UNDOTBS1"
 audit_trail       = "OS"
 open_cursors       = 300
 pga_aggregate_target   = 825229312

2）.启动到nomount模式：

STARTUP NOMOUNT PFILE='D:\app\Administrator\product\11.2.0\dbhome_1\database\initoadb.ora'

3）.重建控制文件：

CREATE CONTROLFILE REUSE SET DATABASE "OADB" RESETLOGS ARCHIVELOG
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 8
  MAXLOGHISTORY 292
LOGFILE
 GROUP 1 'D:\app\oradata\oadb\redo01.dbf' SIZE 100M BLOCKSIZE 512,
 GROUP 2 'D:\app\oradata\oadb\redo02.dbf' SIZE 100M BLOCKSIZE 512,
 GROUP 3 'D:\app\oradata\oadb\redo03.dbf' SIZE 100M BLOCKSIZE 512
DATAFILE
 'D:\app\oradata\oadb\SYSTEM.dbf',
 'D:\app\oradata\oadb\SYSAUX.dbf',
 'D:\app\oradata\oadb\UNDOTBS1.dbf',
 'D:\app\oradata\oadb\USERS.dbf'
CHARACTER SET WE8MSWIN1252;

--注意，重建控制文件后，数据库自动切换到mount模式，需确认；

4）.打开数据库在resetlogs

ALTER DATABASE OPEN RESETLOGS;

5）.添加临时文件

ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\app\oradata\oadb\TEMP.dbf'
SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

6）.利用pfile创建spfile

SQL> create spfile from pfile;

7）.更新恢复完成的数据库

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> @@ ?/rdbms/admin/utlirp.sql
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

8）.验证数据库

从业务层面进行数据访问，访问正常；

此至，数据库跨平台迁移完全。