windows oracle 10.2.0.4升级迁移到linux 11.2.0.4

最新推荐文章于 2022-06-17 13:19:31 发布

久违的太阳

最新推荐文章于 2022-06-17 13:19:31 发布

阅读量808

点赞数 1

分类专栏： oracle upgrade 文章标签： oracle 迁移 windows2linux

本文链接：https://blog.csdn.net/su377486/article/details/108431507

版权

oracle upgrade 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

文章目录

1.迁移环境说明
2.迁移注意事项
3.搭建linux11g环境
4.正式迁移
5. 迁移总结

1.迁移环境说明

客户的环境为windows oracle 10.2.0.4 单实例版本,需要将数据库迁移到linux单实例下,并且升级到11.2.0.4,数据量大概为230GB.可停机时间为3小时左右.下面是有如下方案:

1.使用expdp/impdp

expdp和impdp支持异构跨平台迁移.

尝试使用expdp来导出,发现有一张大表总共月47GB,字段类型为CLOB,导出时间需要8小时左右,尝试使用exp,导出时间也需要3小时,这还只是导出的时间,再加上导入停机时间肯定不够

2.使用dsg/ogg这类方案

ogg这类逻辑同步方案的优势停机时间很短,但是需要数据校验,对象校验,性能测试等,在此环境下肯定是可以的,但是这个库并不大,而且可停机时间为3小时,因此dsg/ogg这类方案稍显复杂

3.使用rman备份恢复方案(冷备)

在这样的场景下是可以使用rman的方案的,但是需要注意的是必须使用一致性rman备份恢复.因此根据次原理,我们大致可以推断出,可以直接使用冷备的方式进行迁移.此方案有如下的优势:

停机时间不长,基本就是一个数据文件的拷贝和数据字典的升级的过程.230G数据文件拷贝需要大概40分钟,数据字典升级大概需要30分钟,在一个半小时以内肯定是可以完成所有工作
数据一致性高,因为是冷备恢复,因此不需要进行数据的校验,对象的校验等工作
零风险,迁移失败也不会对原来的系统产生任何影响,只需要继续使用原来的数据库即可

2.迁移注意事项

这里我们不使用rman的备份恢复,而是直接使用冷备的方式,即停止数据库,拷贝数据文件的方式进行迁移,下面是本次迁移需要的注意事项,非常重要,关系迁移成败:

windows->linux的迁移只支持一致性的备份恢复,不能使用redo进行recover,否则报错,类似于:

ORA-10561: block type ‘TRANSACTION MANAGED DATA BLOCK’, data object# 94252

报错大致的含义就是数据文件和日志文件不一致.

何为一致性备份,即冷备,干净的关闭(shutdown immediate),然后可以将数据库打开到mount进行rman backup,或者直接拷贝所有的文件
在迁移拷贝数据文件之前必须执行pre-upgrade脚本.
脚本位于11g ORALCE_HOME/rdbms/admin/utlu112i.sql
在原10g环境下先运行此脚本,否则在upgrade的时候会报错
我们拷贝完成之后,数据库临时文件不存在,需要先手工创建临时文件
需要重建控制文件
必须将数据库打开到upgrade模式,不能直接打开,直接打开后数据就需要进行恢复,那么就需要重新再迁

3.搭建linux11g环境

在需要迁移的linux服务器上搭建oracle 11.2.0.4环境,这里我们安装完软件,启动监听,安装完成实例,安装完成实例之后停止oracle实例,并且删除安装的数据文件.

安装实例这一步可以跳过,我们这里安装是为了让oracle帮我们创建spfile,密码文件,udmp目录等,这些就不需要我在手工处理了.

4.正式迁移

4.1 执行PRE-UPGRADE脚本

Pre-upgrade脚本位于Linux 11.2.04下的$ORACLE_HOME/rdbms/admin/utlu112i.sql

将此文件拷贝到windows10g下,执行:

SQL> @e:\utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 09-06-2020 13:48:12
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:          ORCL
--> version:       10.2.0.4.0
--> compatible:    10.2.0.3.0
--> blocksize:     8192
--> platform:      Microsoft Windows x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 525 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 232 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest         11.1       DEPRECATED   replaced by  "diagnostic_dest"
--> user_dump_dest               11.1       DEPRECATED   replaced by  "diagnostic_dest"
.

**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.4.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
        PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

    EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
  Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'

  Trace Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

**********************************************************************
SQL>

仔细检查上面有一些告警,我们这里可以忽略.

4.2 拷贝数据文件

首先关闭windows10g数据库,注意这里必须为shutdown immediate,干净的关闭数据库.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

然后将数据文件拷贝到linux下的指定目录,这里的目录就是你以后数据文件存放的目录,我这里使用ftp进行拷贝:

C:\Users\Administrator>ftp 192.168.56.46
连接到 192.168.56.46。
220 (vsFTPd 3.0.2)
用户(192.168.56.46:(none)): oracle
331 Please specify the password.
密码:
230 Login successful.
ftp> lcd E:\oradata\orcl
目前的本地目录 E:\oradata\orcl。
ftp> cd /u01/oradata/orcl
250 Directory successfully changed.
ftp> bin
200 Switching to Binary mode.
ftp> prompt
交互模式 关 。
ftp> mput *
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 Transfer complete.
ftp: 发送 7061504 字节，用时 0.05秒 150244.77千字节/秒。
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 Transfer complete.
ftp: 发送 7061504 字节，用时 0.36秒 19669.93千字节/秒。
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 Transfer complete.
ftp: 发送 7061504 字节，用时 0.36秒 19615.29千字节/秒。
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 Transfer complete.
ftp: 发送 52429312 字节，用时 0.36秒 146042.65千字节/秒。
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 Transfer complete.
ftp: 发送 52429312 字节，用时 0.38秒 139811.50千字节/秒。
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 Transfer complete.
ftp: 发送 52429312 字节，用时 0.36秒 145636.98千字节/秒。
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 Transfer complete.
ftp: 发送 125837312 字节，用时 0.89秒 141231.55千字节/秒。
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 Transfer complete.
ftp: 发送 314580992 字节，用时 2.23秒 140752.12千字节/秒。
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 Transfer complete.
ftp: 发送 20979712 字节，用时 0.42秒 49714.96千字节/秒。
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 Transfer complete.
ftp: 发送 209723392 字节，用时 1.97秒 106512.64千字节/秒。
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 Transfer complete.
ftp: 发送 5251072 字节，用时 0.05秒 111724.94千字节/秒。
ftp>

注意:ftp拷贝的时候使用bin模式

4.3 将数据库打开到mount

先将linux下数据库打开到nomount:

[oracle@bre2 orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 6 13:56:13 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1519898624 bytes
Fixed Size		    2253464 bytes
Variable Size		  369102184 bytes
Database Buffers	 1140850688 bytes
Redo Buffers		    7692288 bytes
SQL> show parameter control

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time	     integer	 7
control_files			     string	 /u01/oradata/orcl/control01.ct
						 l, /u01/oradata/orcl/control02
						 .ctl
control_management_pack_access	     string	 DIAGNOSTIC+TUNI

查看其控制文件是否和拷贝的文件一致,这里由于windows下的文件名为大写,参数文件中的名称为小写,所以需要将文件名修改为小写:

[oracle@bre2 orcl]$ mv CONTROL01.CTL control01.ctl
[oracle@bre2 orcl]$ mv CONTROL02.CTL control02.ctl

这样数据库就可以打开到mount了:

SQL> alter database mount;

Database altered.

4.4 修改文件路径

此时控制文件中的路径还是windows下的路径,这肯定是不对的:

SQL> select name from v$datafile
  2  union all
  3  select name from v$tempfile
  4  union all
  5  select member from v$logfile;

NAME
--------------------------------------------------------------------------------
E:\ORADATA\ORCL\SYSTEM01.DBF
E:\ORADATA\ORCL\UNDOTBS01.DBF
E:\ORADATA\ORCL\SYSAUX01.DBF
E:\ORADATA\ORCL\USERS01.DBF
E:\ORADATA\ORCL\TEMP01.DBF
E:\ORADATA\ORCL\REDO01.LOG
E:\ORADATA\ORCL\REDO02.LOG
E:\ORADATA\ORCL\REDO03.LOG

我们尝试使用rename语句进行修改:

SQL> alter database rename file 'E:\ORADATA\ORCL\SYSTEM01.DBF' to '/u01/oradata/orcl/SYSTEM01.DBF';
alter database rename file 'E:\ORADATA\ORCL\SYSTEM01.DBF' to '/u01/oradata/orcl/SYSTEM01.DBF'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, data file, or temporary file
"E:\ORADATA\ORCL\SYSTEM01.DBF"

这里报错了,报错的意思是E:\这个目录在控制文件中不存在,这可能是linux下文件名和windows命名不匹配造成的,这里我们只能重建控制文件来修改文件目录:

SQL> alter database backup controlfile to trace;

Database altered.

找到trace文件,手工修改文件路径:

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/orcl/REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/oradata/orcl/REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/oradata/orcl/REDO03.LOG'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/oradata/orcl/SYSTEM01.DBF',
  '/u01/oradata/orcl/UNDOTBS01.DBF',
  '/u01/oradata/orcl/SYSAUX01.DBF',
  '/u01/oradata/orcl/USERS01.DBF'
CHARACTER SET ZHS16GBK
;

注意:

1.这里指定NORESETLOGS

2.这里并没有tempfile的文件,所以后还需要添加tempfile文件

停止数据库实例,并且删除控制文件,再手工创建控制文件

[oracle@bre2 orcl]$ rm -rf control0*.ctl
[oracle@bre2 orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 6 14:21:53 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1519898624 bytes
Fixed Size		    2253464 bytes
Variable Size		  369102184 bytes
Database Buffers	 1140850688 bytes
Redo Buffers		    7692288 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/orcl/REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/  2    3    4    5    6    7    8    9  oradata/orcl/REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/oradata/orcl/REDO03.LOG'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/oradata/orcl/SYSTEM01.DBF',
  '/u01/oradata/orcl/UNDOTBS01.DBF',
  '/u01/oradata/orcl/SYSAUX01.DBF',
  '/ 10   11   12   13   14   15   16  u01/oradata/orcl/USERS01.DBF'
CHARACTER SET ZHS16GBK
;
 17   18  
Control file created.

4.5 重建临时文件

将数据库打开到upgrade模式

SQL> alter database open upgrade;

Database altered.

SQL>

注意:

这里一定要打开到upgrade模式,不能直接打开,虽然你直接打开会报错,提示让你打开到upgrade模式但是经过测试,发现报错之后就没法再打开到upgrade模式了,提示需要进行recover,这样上面所有的工作就白费了!!!因此这里不能直接打开!!!

为临时表空间创建临时文件,我这里只有一个临时表空间,如果有多个需要都进行创建:

SQL> alter tablespace temp add tempfile '/u01/oradata/orcl/TEMP01.DBF' size 100m reuse;

Tablespace altered.

4.6 数据字典升级

下面开始进行数据字典的升级,执行$ORACLE_HOME/rdbms/admin/catupgrd.sql脚本:

SQL> @?/rdbms/admin/catupgrd

......
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> 
SQL> 
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above sql script is the final step of the upgrade. Please
DOC>   review any errors in the spool log file. If there are any errors in
DOC>   the spool file, consult the Oracle Database Upgrade Guide for
DOC>   troubleshooting recommendations.
DOC>
DOC>   Next restart for normal operation, and then run utlrp.sql to
DOC>   recompile any invalid application objects.
DOC>
DOC>   If the source database had an older time zone version prior to
DOC>   upgrade, then please run the DBMS_DST package.  DBMS_DST will upgrade
DOC>   TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
DOC>   with Oracle.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> 
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL> 
SQL> REM END OF CATUPGRD.SQL
SQL> 
SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
SQL> REM                This forces user to start a new sqlplus session in order
SQL> REM                to connect to the upgraded db.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

# 查看升级结果
SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool        09-06-2020 15:04:32
.
Component                Current      Version     Elapsed Time
Name                    Status         Number     HH:MM:SS
.
Oracle Server
.                      VALID      11.2.0.4.0  00:03:14
Oracle Workspace Manager
.                      VALID      11.2.0.4.0  00:00:08
Final Actions
.                                 00:00:05
Total Upgrade Time: 00:03:28


PL/SQL procedure successfully completed.

升级速度和你数据库对象的多少和服务器性能有关,一般半小时左右,到此数据字典就升级完成了.

查看当前数据库版本:

[oracle@bre2 orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 6 14:33:35 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1519898624 bytes
Fixed Size		    2253464 bytes
Variable Size		  369102184 bytes
Database Buffers	 1140850688 bytes
Redo Buffers		    7692288 bytes
Database mounted.
Database opened.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select status from v$instance;

STATUS
------------
OPEN

4.7 编译无效对象

执行$ORACLE_HOME/rdbms/admin/utlrp.sql编译无效对象:

SQL> @?/rdbms/admin/utlrp

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2020-09-06 14:34:36
...

Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.



SQL> @?/rdbms/admin/catuppst
...

到此迁移升级流程全部完成.

5. 迁移总结

建议在做之前,将所有的步骤和脚本提前准备好,特别是修改控制文件那一部分,提前准备好创建控制文件的脚本,这样整体下来停机时间基本就是拷贝数据文件和升级字典的时间.
建议在正式升级迁移前做一次模式迁移,可以将原生产库进行rman备份恢复到一台测试库上,使用测试库上的数据库进行迁移演练
拷贝数据文件基本就是和网速相关了,像千兆网络一小时能拷贝大约350G数据,在有限的时间停机时间内,应该是最简单有效的迁移方案了,当然对于那种停机时间只能按照分钟来计算,而且数据量较大的数据库,那么首先推荐使用dsg或者ogg方案!