Oracle迁移:Linux->Windows

• 1.Windows安装同版本数据库软件
• 2.Windows使用oradim创建实例
• 3.Windows创建密码文件
• 4.Linux创建pfile，正常关闭数据库
• 5.Linux拷贝所有文件到Windows
• 6.Windows修改pfile，指定控制文件路径
• 7.Windows备份控制文件，重建控制文件(noresetlog)
• 8.Windows后续配置

1.Windows安装同版本数据库软件

首先在MOS上确定Oracle Database 11.2.0.4.0 已在 Microsoft Windows x64 (64-bit) 2003 上通过认证。然后在MOS上下载11.2.0.4的安装介质

p13390677_112040_MSWIN-x86-64_1of7.zip p13390677_112040_MSWIN-x86-64_2of7.zip

2.Windows使用oradim创建实例

在Windows系统上，Oracle连接到实例需要此步骤，本案例创建实例vas，sqlplus登录到vas实例的具体过程：

C:/Documents and Settings/Administrator>oradim -NEW -SID vas 实例已创建。  C:/Documents and Settings/Administrator>set ORACLE_SID=vas  C:/Documents and Settings/Administrator>echo %ORACLE_SID% vas  C:/Documents and Settings/Administrator>sqlplus / as sysdba  SQL*Plus: Release 11.2.0.4.0 Production on 星期日 2月 14 12:38:48 2016  Copyright (c) 1982, 2013, Oracle.  All rights reserved.  已连接到空闲例程。  SQL>

关于oradim命令，具体参考官档：Database Administrator's Guide -> 2 Creating and Configuring an Oracle Database -> Creating a Database with the CREATE DATABASE Statement部分的步骤5：

步骤14，修改为自动启动

我们还可以直接通过oradim -h看到oradim命令的说明帮助：

C:/Documents and Settings/Administrator>oradim -h DIM-00002: 指定的命令无效。 输入以下命令之一: 通过指定以下选项创建实例:      -NEW -SID sid | -SRVC srvc | -ASMSID sid | -ASMSRVC srvc [-SYSPWD pass]  [-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]  [-SHUTMODE normal|immediate|abort] [-TIMEOUT secs] [-RUNAS osusr/ospass] 通过指定以下选项编辑实例:      -EDIT -SID sid | -ASMSID sid [-SYSPWD pass]  [-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]  [-SHUTMODE normal|immediate|abort] [-SHUTTYPE srvc|inst] [-RUNAS osusr/ospass] 通过指定以下选项删除实例:      -DELETE -SID sid | -ASMSID sid | -SRVC srvc | -ASMSRVC srvc 通过指定以下选项启动服务和实例:      -STARTUP -SID sid | -ASMSID sid [-SYSPWD pass]  [-STARTTYPE srvc|inst|srvc,inst] [-PFILE filename | -SPFILE] 通过指定以下选项关闭服务和实例:      -SHUTDOWN -SID sid | -ASMSID sid [-SYSPWD pass]  [-SHUTTYPE srvc|inst|srvc,inst] [-SHUTMODE normal|immediate|abort]  通过指定以下参数查询帮助: -? | -h | -help

oradim命令几个常用的参数：

--创建实例vas oradim -NEW -SID vas  --删除实例vas oradim -DELETE -SID vas  --编辑实例vas oradim - EDIT -SID vas -STARTMODE auto -SRVCSTART system

3.Windows创建密码文件

--创建密码文件 orapwd file=F:/app/Administrator/product/11.2.0/dbhome_1/database/PWDvas.ora password=oracle

4.Linux创建pfile，正常关闭数据库

--Linux创建pfile SQL> create pfile='/tmp/pfile20160214.ora' from spfile;  File created.  --正常关闭数据库 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.

5.Linux拷贝所有文件到Windows

查询数据库所有控制文件、数据文件、重做日志文件、临时文件(可选)信息：

select name from v$controlfile union all select name from v$datafile union all select member from v$logfile union all select name from v$tempfile;

6.Windows修改pfile，指定控制文件路径

--启动数据库到nomount状态 startup nomount pfile='%ORACLE_HOME%/database/pfilevas.ora'  --更改数据库到mount状态 alter database mount;  --根据pfile文件，创建spfile文件 create spfile='spfilevas.ora' from pfile='%ORACLE_HOME%/database/pfilevas.ora'; 

7.Windows备份控制文件，重建控制文件(noresetlog)

7.1 Windows备份控制文件

SQL> alter database backup controlfile to trace;  --生成的跟踪文件默认路径为user_dump_dest参数指向的目录下。

7.2 选取noresetlog部分重建控制文件

--选取noresetlog部分重建控制文件 --     Set #1. NORESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- Additional logs may be required for media recovery of offline -- Use this only if the current versions of all online logs are -- available. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: --  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "VAS" NORESETLOGS  NOARCHIVELOG     MAXLOGFILES 16     MAXLOGMEMBERS 3     MAXDATAFILES 100     MAXINSTANCES 8     MAXLOGHISTORY 292 LOGFILE   GROUP 1 (     '/oradata/VAS/onlinelog/o1_mf_1_c1cm3pwr_.log',     '/opt/app/oracle11/fast_recovery_area/VAS/onlinelog/o1_mf_1_c1cm3rn6_.log'   ) SIZE 1024M BLOCKSIZE 512,   GROUP 2 (     '/oradata/VAS/onlinelog/o1_mf_2_c1cm5nfs_.log',     '/opt/app/oracle11/fast_recovery_area/VAS/onlinelog/o1_mf_2_c1cm5p2b_.log'   ) SIZE 1024M BLOCKSIZE 512,   GROUP 3 (     '/oradata/VAS/onlinelog/o1_mf_3_c1cm7l3v_.log',     '/opt/app/oracle11/fast_recovery_area/VAS/onlinelog/o1_mf_3_c1cm7mnh_.log'   ) SIZE 1024M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE   '/oradata/VAS/datafile/o1_mf_system_c1cm0o2x_.dbf',   '/oradata/VAS/datafile/o1_mf_sysaux_c1cm0o5j_.dbf',   '/oradata/VAS/datafile/o1_mf_undotbs1_c1cm0o7x_.dbf',   '/oradata/VAS/datafile/o1_mf_users_c1cm0obx_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_dcdl_c1cndg32_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_d_cr_c1cnhxj3_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_icdl_c1cnj30n_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_d_cd_c1cnj8dk_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_d_cr_c1cnjg2t_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_d_ne_c1cnjmdh_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_d_pe_c1cnjrjv_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_d_rn_c1cnjy27_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_d_wo_c1cnk3lg_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_i_cd_c1cnk999_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_i_cr_c1cnkhct_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_i_ne_c1cnknpc_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_i_pe_c1cnksy1_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_i_rn_c1cnkz2m_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_i_wo_c1cnl4ny_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_cssp_c5qdmmgl_.dbf',   '/oradata/VAS/datafile/o1_mf_dbs_d_ba_c5qdmtcz_.dbf' CHARACTER SET ZHS16GBK ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE 'F:/APP/ADMINISTRATOR/FAST_RECOVERY_AREA/VAS/ARCHIVELOG/2016_02_14/O1_MF_1_1_%U_.ARC'; -- ALTER DATABASE REGISTER LOGFILE 'F:/APP/ADMINISTRATOR/FAST_RECOVERY_AREA/VAS/ARCHIVELOG/2016_02_14/O1_MF_1_1_%U_.ARC'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE -- Database can now be opened normally. ALTER DATABASE OPEN; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/VAS/datafile/o1_mf_temp_c1cm9jph_.tmp' REUSE; -- End of tempfile additions.

7.3 正常关闭数据库

SQL> shutdown immediate;

7.4 重建控制文件

把7.2部分中的内容根据实际情况修改如下：

--选取noresetlog部分重建控制文件(修改过) --     Set #1. NORESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- Additional logs may be required for media recovery of offline -- Use this only if the current versions of all online logs are -- available. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: --  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "VAS" NORESETLOGS  NOARCHIVELOG     MAXLOGFILES 16     MAXLOGMEMBERS 3     MAXDATAFILES 100     MAXINSTANCES 8     MAXLOGHISTORY 292 LOGFILE   GROUP 1 (     'F:/oradata/VAS/onlinelog/o1_mf_1_c1cm3pwr_.log'   ) SIZE 1024M BLOCKSIZE 512,   GROUP 2 (     'F:/oradata/VAS/onlinelog/o1_mf_2_c1cm5nfs_.log'   ) SIZE 1024M BLOCKSIZE 512,   GROUP 3 (     'F:/oradata/VAS/onlinelog/o1_mf_3_c1cm7l3v_.log'   ) SIZE 1024M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE   'F:/oradata/VAS/datafile/o1_mf_system_c1cm0o2x_.dbf',   'F:/oradata/VAS/datafile/o1_mf_sysaux_c1cm0o5j_.dbf',   'F:/oradata/VAS/datafile/o1_mf_undotbs1_c1cm0o7x_.dbf',   'F:/oradata/VAS/datafile/o1_mf_users_c1cm0obx_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_dcdl_c1cndg32_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_d_cr_c1cnhxj3_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_icdl_c1cnj30n_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_d_cd_c1cnj8dk_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_d_cr_c1cnjg2t_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_d_ne_c1cnjmdh_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_d_pe_c1cnjrjv_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_d_rn_c1cnjy27_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_d_wo_c1cnk3lg_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_i_cd_c1cnk999_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_i_cr_c1cnkhct_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_i_ne_c1cnknpc_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_i_pe_c1cnksy1_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_i_rn_c1cnkz2m_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_i_wo_c1cnl4ny_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_cssp_c5qdmmgl_.dbf',   'F:/oradata/VAS/datafile/o1_mf_dbs_d_ba_c5qdmtcz_.dbf' CHARACTER SET ZHS16GBK ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE 'F:/APP/ADMINISTRATOR/FAST_RECOVERY_AREA/VAS/ARCHIVELOG/2016_02_14/O1_MF_1_1_%U_.ARC'; -- ALTER DATABASE REGISTER LOGFILE 'F:/APP/ADMINISTRATOR/FAST_RECOVERY_AREA/VAS/ARCHIVELOG/2016_02_14/O1_MF_1_1_%U_.ARC'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE -- Database can now be opened normally. ALTER DATABASE OPEN; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE 'F:/oradata/VAS/datafile/o1_mf_temp_c1cm9jph_.tmp' REUSE; -- End of tempfile additions.

至此已经在Windows上成功打开数据库。

8.Windows后续配置

8.1 监听配置

在%ORACLE_HOME%/NETWORK/ADMIN/listener.ora加上以下内容

SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (SID_NAME = CLRExtProc)       (ORACLE_HOME = F:/app/Administrator/product/11.2.0/dbhome_1)       (PROGRAM = extproc)       (ENVS = "EXTPROC_DLLS=ONLY:F:/app/Administrator/product/11.2.0/dbhome_1/bin/oraclr11.dll")     )     (SID_DESC =       (GLOBAL_DBNAME = vas)       (ORACLE_HOME = F:/app/Administrator/product/11.2.0/dbhome_1)       (SID_NAME = vas)     )   )

然后启动监听

lsnrctl start

8.2 环境变量

ORACLE_SID=vas

8.3 Oracle服务自动启动

oradim -EDIT -SID vas -STARTMODE auto -SRVCSTART system

8.4 数据文件关闭自动扩展(选做)

根据空间具体情况而定。

至此，此次oracle数据库从linux平台迁移到windows平台的迁移需求就全部完成了。