标签：10.2 windows 0.4 11.2 -- orcl SQL oracle
文档课题：windows 2003 oracle 10.2.0.4升级迁移到linux 11.2.0.4
源端：windows 2003 32位 + oracle 10.2.0.4 32位 + 双实例
目标端：centos 7.9 64位 + oracle 11.2.0.4 64位
应用场景：最近遇到客户有几套oracle 10.2.0.4 32位的数据库在windows 2003 32位上，升级11g是迟早的事.以下便是模拟迁移升级过程.
1、源库信息
C:\Documents and Settings\Administrator>lsnrctl status
LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 29-10月-2022 16:49:06
Copyright (c) 1991, 2007, Oracle. All rights reserved.
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.133.204)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for 32-bit Windows: Version 10.2.0.4.0 - Production
启动日期 29-10月-2022 16:19:09
正常运行时间 0 天 0 小时 30 分 1 秒
跟踪级别 off
安全性 ON: Local OS Authentication
SNMP OFF
监听程序参数文件 C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
监听程序日志文件 C:\oracle\product\10.2.0\db_1\network\log\listener.log
监听端点概要...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.204)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
服务摘要..
服务 "PLSExtProc" 包含 1 个例程。
例程 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
服务 "orcl" 包含 1 个例程。
例程 "orcl", 状态 READY, 包含此服务的 1 个处理程序...
服务 "orclXDB" 包含 1 个例程。
例程 "orcl", 状态 READY, 包含此服务的 1 个处理程序...
服务 "orcl_XPT" 包含 1 个例程。
例程 "orcl", 状态 READY, 包含此服务的 1 个处理程序...
服务 "simdb" 包含 1 个例程。
例程 "simdb", 状态 READY, 包含此服务的 1 个处理程序...
服务 "simdbXDB" 包含 1 个例程。
例程 "simdb", 状态 READY, 包含此服务的 1 个处理程序...
服务 "simdb_XPT" 包含 1 个例程。
例程 "simdb", 状态 READY, 包含此服务的 1 个处理程序...
命令执行成功
说明：此处已在源库创建两个实例，环境尽量跟生产库一致.
先迁移orcl库，迁移前产生一些数据以作测试.
SQL> select name from v$database;
NAME
---------
ORCL
SQL> create user leo identified by leo;
用户已创建。
SQL> grant connect,resource,unlimited tablespace to leo;
授权成功。
SQL> conn leo/leo;
已连接。
SQL> create table test (id number);
表已创建。
SQL> begin
2 for i in 1..1000 loop
3 insert into test (id) values(i);
4 end loop;
5 end;
6 /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> select count(*) from test;
COUNT(*)
----------
1000
2、迁移概念
本次迁移直接使用冷备方式,即停止数据库后拷贝数据文件、控制文件、redo文件到目标库.
冷备迁移优点：
a、整个过程消耗的时间主要为数据文件的拷贝和数据字典的升级.
b、数据一致性高,冷备恢复不需要进行数据校验,对象校验等工作.
c、零风险,迁移失败也不会对原库产生任何影响.
注意事项：
a、windows-->linux的迁移只支持一致性备份恢复,不能使用redo进行recover,否则报错,类似于:
ORA-10561: block type ‘TRANSACTION MANAGED DATA BLOCK’, data object# 94252
大致含义就是数据文件和日志文件不一致.
b、一致性备份(即冷备),指干净的关闭数据库(shutdown immediate),然后可以将数据库打开到mount进行rman backup,或直接拷贝所有文件.
c、在迁移拷贝数据文件之前必须执行pre-upgrade脚本.脚本位于11g ORALCE_HOME/rdbms/admin/utlu112i.sql，在原10g环境下先运行此脚本,否则在upgrade的时候会报错.
d、源端不拷贝临时文件,所以目标端到时需要先手工创建临时文件.
e、需要重建控制文件.
f、恢复时必须将目标端数据库以upgrade模式打开.直接打开会造成数据需要先进行恢复,那么就需要重新再迁移
说明：以上非常重要,关系迁移成败.
3、目标端搭建11g
在目标端的linux服务器上搭建oracle 11.2.0.4环境,需安装数据库软件、监听、实例.安装完成实例后停止实例,然后删除数据文件、控制文件、redo文件.
目标端安装实例是为了创建spfile,密码文件,udmp目录等,此处目标端数据库的安装不做介绍，读者可翻阅笔者此前的博文.
以下为目标端实例的处理过程.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[root@liujun orcl]# pwd
/u01/app/oracle/oradata/orcl
[root@liujun orcl]# ll
total 117008004
-rw-r-----. 1 oracle oinstall 12533760 Oct 29 18:22 control01.ctl
-rw-r-----. 1 oracle oinstall 52429312 Oct 29 10:34 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 Oct 29 18:22 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 Oct 29 18:05 redo03.log
-rw-r-----. 1 oracle oinstall 660611072 Oct 29 18:22 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 33827069952 Oct 29 18:22 system01.dbf
-rw-r----- 1 oracle oinstall 8480890880 Oct 29 18:22 system02.dbf
-rw-r-----. 1 oracle oinstall 34358697984 Oct 29 18:05 temp01.dbf
-rw-r----- 1 oracle oinstall 10647248896 Oct 29 18:05 temp02.dbf
-rw-r-----. 1 oracle oinstall 32207020032 Oct 29 18:22 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Oct 29 18:22 users01.dbf
[root@liujun orcl]# mkdir bak
[root@liujun orcl]# mv *.dbf bak
[root@liujun orcl]# mv *.ctl bak
[root@liujun orcl]# mv *.log bak
[root@liujun orcl]# ll
total 165852
drwxr-xr-x 2 root root 142 Oct 29 18:24 bak
[oracle@liujun orcl]$ pwd
/u01/app/oracle/flash_recovery_area/orcl
[oracle@liujun orcl]$ mkdir bak
[oracle@liujun orcl]$ ll
total 12240
drwxr-xr-x 2 oracle oinstall 6 Oct 29 18:45 bak
-rw-r-----. 1 oracle oinstall 12533760 Oct 29 18:22 control02.ctl
[oracle@liujun orcl]$ mv control02.ctl bak
4、迁移
4.1、执行pre-upgrade脚本
Pre-upgrade脚本位于Linux 11.2.04下的$ORACLE_HOME/rdbms/admin/utlu112i.sql，将此文件拷贝到源端.
说明：utlu112i.sql脚本不仅会检查是否满足升级环境，而且还会在数据库中插入一些重要信息，如时区等等.
sftp> cd /u01/app/oracle/product/11.2.0.4/db_1/rdbms/admin
sftp> lcd C:\Users\Administrator\Desktop\soft
sftp> get utlu112i.sql
然后将utlu112i.sql 拷贝到虚拟机中的windows 2003源端C盘.
SQL> conn / as sysdba
已连接。
SQL> spool utlusql.log
SQL> @C:\utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 10-29-2022 18:30:11
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL
--> version: 10.2.0.4.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
--> platform: Microsoft Windows IA (32-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 996 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 719 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 32-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:
WARNING: --> "sga_target" needs to be increased to at least 596 MB
.
**********************************************************************
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
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [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: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING: --> Your recycle bin contains 14 object(s).
.... It is REQUIRED that the recycle bin is empty prior to upgrading
.... your database. The command:
PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
.
**********************************************************************
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> spool off;
说明：仔细检查上面警告，此处的警告可以忽略.
4.2、源端拷贝文件
查源端库文件信息.
SQL> select name from v$datafile;
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
SQL> select name from v$controlfile;
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
SQL> select member from v$logfile;
MEMBER
-------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
关闭windows 10g源端数据库，注意必须为shutdown immediate干净地关闭数据库.
SQL> shutdown immediate;
然后将数据文件、控制文件、redo文件拷贝到linux下的相应的目录.
sftp> lcd E:\
sftp> cd /u01/app/oracle/oradata/orcl
sftp> put *.dbf
sftp> put CONTROL01.CTL
sftp> put *.LOG
[oracle@liujun orcl]$ ll /u01/app/oracle/oradata/orcl
total 1548060
drwxr-xr-x 2 root root 163 Oct 29 18:44 bak
-rw-r--r-- 1 root root 7061504 Oct 29 18:34 CONTROL01.CTL
-rw-r-----. 1 root root 52429312 Oct 29 10:34 REDO01.LOG
-rw-r-----. 1 root root 52429312 Oct 29 18:22 REDO2.LOG
-rw-r-----. 1 root root 52429312 Oct 29 18:05 REDO03.LOG
-rw-r--r-- 1 root root 251666432 Oct 29 18:34 SYSAUX01.DBF
-rw-r--r-- 1 root root 524296192 Oct 29 18:34 SYSTEM01.DBF
-rw-r--r-- 1 root root 639639552 Oct 29 18:34 UNDOTBS01.DBF
-rw-r--r-- 1 root root 5251072 Oct 29 18:34 USERS01.DBF
sftp> cd /u01/app/oracle/flash_recovery_area/orcl
sftp> put CONTROL02.CTL
[oracle@liujun orcl]$ pwd
/u01/app/oracle/flash_recovery_area/orcl
[oracle@liujun orcl]$ ll
total 6896
drwxr-xr-x 2 oracle oinstall 27 Oct 29 18:45 bak
-rw-r--r-- 1 root root 7061504 Oct 29 18:34 CONTROL02.CTL
4.3、目标端文件处理
源端windows下的控制文件名为大写,参数文件中的控制文件名为小写,所以需要将控制文件名修改为小写.
[oracle@liujun orcl]$ pwd
/u01/app/oracle/oradata/orcl
[root@liujun orcl]# chown oracle:oinstall *.DBF
[root@liujun orcl]# chown oracle:oinstall CONTROL01.CTL
[root@liujun orcl]# chown oracle:oinstall *.LOG
[oracle@liujun orcl]$ mv CONTROL01.CTL control01.ctl
[root@liujun orcl]# ll
total 1548060
drwxr-xr-x 2 root root 163 Oct 29 18:44 bak
-rw-r--r-- 1 oracle oinstall 7061504 Oct 29 18:34 control01.ctl
-rw-r-----. 1 oracle oinstall 52429312 Oct 29 10:34 REDO01.LOG
-rw-r-----. 1 oracle oinstall 52429312 Oct 29 18:22 REDO2.LOG
-rw-r-----. 1 oracle oinstall 52429312 Oct 29 18:05 REDO03.LOG
-rw-r--r-- 1 oracle oinstall 251666432 Oct 29 18:34 SYSAUX01.DBF
-rw-r--r-- 1 oracle oinstall 524296192 Oct 29 18:34 SYSTEM01.DBF
-rw-r--r-- 1 oracle oinstall 639639552 Oct 29 18:34 UNDOTBS01.DBF
-rw-r--r-- 1 oracle oinstall 5251072 Oct 29 18:34 USERS01.DBF
[root@liujun orcl]# pwd
/u01/app/oracle/flash_recovery_area/orcl
[root@liujun orcl]# chown oracle:oinstall CONTROL02.CTL
[oracle@liujun orcl]$ mv CONTROL02.CTL control02.ctl
[root@liujun orcl]# ll
total 6896
drwxr-xr-x 2 oracle oinstall 27 Oct 29 18:45 bak
-rw-r--r-- 1 oracle oinstall 7061504 Oct 29 18:34 control02.ctl
4.4、目标端重建控制文件
目标端重建控制文件，将数据库打开到mount
[oracle@liujun orcl]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 29 20:57:07 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1586708480 bytes
Fixed Size 2253624 bytes
Variable Size 922750152 bytes
Database Buffers 654311424 bytes
Redo Buffers 7393280 bytes
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/orcl/contr
ol02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> alter database mount;
Database altered.
修改文件路径
此时控制文件中的路径还是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
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
8 rows selected.
SQL> alter database rename file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF' to '/u01/app/oracle/oradata/orcl/SYSTEM01.DBF';
alter database rename file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF' to '/u01/app/oracle/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
"C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF"
异常原因：linux文件名和windows命名不匹配造成,此处采用重建控制文件来修改文件目录.
SQL> alter database backup controlfile to trace;
Database altered.
找到trace文件orcl_ora_23217.trc，手工修改文件路径.
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/REDO01.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/orcl/REDO02.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/orcl/REDO03.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/SYSTEM01.DBF',
'/u01/app/oracle/oradata/orcl/UNDOTBS01.DBF',
'/u01/app/oracle/oradata/orcl/SYSAUX01.DBF',
'/u01/app/oracle/oradata/orcl/USERS01.DBF'
CHARACTER SET ZHS16GBK
;
注意：
a、指定noresetlogs.
b、控制文件的创建语句中并没有tempfile文件，此后还需要添加tempfile文件.
目标库停止实例，并且删除控制文件，然后手工创建控制文件.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
[root@liujun orcl]# pwd
/u01/app/oracle/oradata/orcl
[root@liujun orcl]# rm -f control01.ctl
[root@liujun orcl]# pwd
/u01/app/oracle/flash_recovery_area/orcl
[root@liujun orcl]# rm -f control02.ctl
[oracle@liujun orcl]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 29 21:42:17 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1586708480 bytes
Fixed Size 2253624 bytes
Variable Size 922750152 bytes
Database Buffers 654311424 bytes
Redo Buffers 7393280 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/orcl/REDO01.LOG' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/oradata/orcl/REDO02.LOG' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/oradata/orcl/REDO03.LOG' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/orcl/SYSTEM01.DBF',
14 '/u01/app/oracle/oradata/orcl/UNDOTBS01.DBF',
15 '/u01/app/oracle/oradata/orcl/SYSAUX01.DBF',
16 '/u01/app/oracle/oradata/orcl/USERS01.DBF'
17 CHARACTER SET ZHS16GBK
18 ;
Control file created.
说明：控制文件创建成功.
4.5、建临时文件
目标端重建临时文件，将数据库打开到upgrade模式.
SQL> alter database open upgrade;
Database altered.
注意:此处一定要以upgrade模式打开,直接打开虽然会报错提示打开到upgrade模式，但经测试发现报错之后就没法再打开到upgrade模式,提示需进行recover,如此以上所有工作都需重新再执行.
创建临时文件
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 100m autoextend on;
Tablespace altered.
5、升级数据字典
开始进行数据字典的升级，执行$ORACLE_HOME/rdbms/admin/catupgrd.sql脚本.
SQL> @?/rdbms/admin/catupgrd.sql;
……(省略若干)
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
说明：时间23:27----23:50,此为测试环境所消耗的时间.升级速度和数据库对象数量以及服务器性能有关，一般半小时左右，生产环境最好预留多一点时间.到此数据字典就升级完成.
6、验证版本
查看数据库版本.
[oracle@liujun orcl]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 29 23:51:12 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1586708480 bytes
Fixed Size 2253624 bytes
Variable Size 1023413448 bytes
Database Buffers 553648128 bytes
Redo Buffers 7393280 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> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 10-30-2022 11:31:43
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:06:25
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:04:19
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:15
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:00:08
OLAP Catalog
. VALID 11.2.0.4.0 00:00:19
Oracle OLAP API
. VALID 11.2.0.4.0 00:00:11
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:03:09
Oracle XDK
. VALID 11.2.0.4.0 00:00:56
Oracle Text
. VALID 11.2.0.4.0 00:00:19
Oracle XML Database
. VALID 11.2.0.4.0 00:02:09
Oracle Database Java Packages
. VALID 11.2.0.4.0 00:00:08
Oracle Multimedia
. VALID 11.2.0.4.0 00:01:44
Spatial
. VALID 11.2.0.4.0 00:02:08
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:05
Oracle Rule Manager
. VALID 11.2.0.4.0 00:00:04
Final Actions
. 00:00:23
Total Upgrade Time: 00:22:49
PL/SQL procedure successfully completed.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select comp_id,comp_name,version,status from dba_registry
COMP_ID COMP_NAME VERSION STATUS
--------------- ---------------------------------------- -------------------- ---------------
EM Oracle Enterprise Manager 11.2.0.4.0 VALID
AMD OLAP Catalog 11.2.0.4.0 VALID
SDO Spatial 11.2.0.4.0 VALID
ORDIM Oracle Multimedia 11.2.0.4.0 VALID
XDB Oracle XML Database 11.2.0.4.0 VALID
CONTEXT Oracle Text 11.2.0.4.0 VALID
ODM Oracle Data Mining 11.2.0.4.0 VALID
EXF Oracle Expression Filter 11.2.0.4.0 VALID
RUL Oracle Rule Manager 11.2.0.4.0 VALID
OWM Oracle Workspace Manager 11.2.0.4.0 VALID
CATALOG Oracle Database Catalog Views 11.2.0.4.0 VALID
COMP_ID COMP_NAME VERSION STATUS
--------------- ---------------------------------------- -------------------- ---------------
CATPROC Oracle Database Packages and Types 11.2.0.4.0 VALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.4.0 VALID
XML Oracle XDK 11.2.0.4.0 VALID
CATJAVA Oracle Database Java Packages 11.2.0.4.0 VALID
APS OLAP Analytic Workspace 11.2.0.4.0 VALID
XOQ Oracle OLAP API 11.2.0.4.0 VALID
17 rows selected.
SQL> col action for a20
SQL> col namespace for a15
SQL> col comments for a30
SQL> col action_time for a30
SQL> col version for a11
SQL> select * from dba_registry_history
ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
------------------------------ -------------------- --------------- ----------- ---------- ------------------------------
29-OCT-22 03.45.13.280000 PM UPGRADE SERVER 10.2.0.4.0 Upgraded from 10.2.0.1.0
29-OCT-22 11.49.52.227658 PM VIEW INVALIDATE 8289601 view invalidation
29-OCT-22 11.50.12.642822 PM UPGRADE SERVER 11.2.0.4.0 Upgraded from 10.2.0.4.0
7、编译无效对象
查看无效对象.
SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type;
OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------- ----------
CTXSYS INDEXTYPE 2
CTXSYS OPERATOR 3
CTXSYS PACKAGE 3
CTXSYS PACKAGE BODY 11
CTXSYS PROCEDURE 1
CTXSYS TYPE 1
CTXSYS TYPE BODY 1
CTXSYS VIEW 53
DBSNMP PACKAGE 1
DBSNMP PACKAGE BODY 3
DBSNMP VIEW 6
OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------- ----------
DMSYS FUNCTION 12
DMSYS LIBRARY 6
DMSYS PACKAGE 27
DMSYS PACKAGE BODY 23
DMSYS TYPE 94
DMSYS TYPE BODY 12
EXFSYS FUNCTION 1
EXFSYS PACKAGE BODY 13
EXFSYS TYPE BODY 2
EXFSYS VIEW 35
MDSYS FUNCTION 2
OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------- ----------
MDSYS PACKAGE BODY 17
MDSYS TRIGGER 6
MDSYS TYPE BODY 1
MDSYS VIEW 26
OLAPSYS PACKAGE BODY 30
OLAPSYS VIEW 261
ORACLE_OCM PACKAGE BODY 1
ORDDATA VIEW 1
ORDPLUGINS PACKAGE BODY 1
ORDSYS PACKAGE BODY 5
ORDSYS VIEW 1
OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------- ----------
PUBLIC SYNONYM 2015
SI_INFORMTN_SCHEMA SYNONYM 2
SYS EVALUATION CONTEXT 2
SYS FUNCTION 6
SYS JAVA CLASS 24
SYS LIBRARY 2
SYS PACKAGE 26
SYS PACKAGE BODY 232
SYS PROCEDURE 30
SYS QUEUE 2
SYS RULE SET 3
OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------- ----------
SYS TRIGGER 2
SYS TYPE 16
SYS TYPE BODY 16
SYS VIEW 2267
SYSMAN PACKAGE BODY 52
SYSMAN TRIGGER 11
SYSMAN VIEW 392
SYSTEM FUNCTION 1
SYSTEM SYNONYM 7
SYSTEM VIEW 10
WMSYS EVALUATION CONTEXT 1
OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------- ----------
WMSYS PACKAGE BODY 16
WMSYS PROCEDURE 3
WMSYS RULE SET 2
WMSYS VIEW 86
XDB FUNCTION 1
XDB OPERATOR 2
XDB PACKAGE 1
XDB PACKAGE BODY 7
XDB TRIGGER 1
XDB TYPE 3
XDB VIEW 3
66 rows selected.
运行utlrp.sql编译无效对象.
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2022-10-30 00:01:20
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2022-10-30 00:02:57
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
再次查看无效对象，发现无效对象消失.
SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type;
no rows selected
SQL> @?/rdbms/admin/catuppst
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2022-10-30 00:04:04
PL/SQL procedure successfully completed.
This script will migrate the Baseline data on a pre-11g database
to the 11g database.
Move BL Data "SYS"."WRH$_FILESTATXS" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SQLSTAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SYSTEM_EVENT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_WAITSTAT" (0 rows in 0 seconds)
8、验证数据
目标库验证此前在源库创建的用户以及表.
SQL> conn leo/leo;
Connected.
SQL> select count(*) from test;
COUNT(*)
----------
1000
说明：成功查询.
9、总结
建议迁移升级前,将所有步骤的脚本提前准备好,特别是提前准备好控制文件的创建脚本,这样停机时间基本就是拷贝数据文件和升级字典，拷贝文件和网速相关.在有限的停机时间内,该方案是最简单有效的迁移方案.
参考网址：
http://www.askmac.cn/archives/rename-gabled-code-datafile-name.html
https://blog.csdn.net/su377486/article/details/108431507
标签：10.2,
windows,
0.4,
11.2,
--,
orcl,
SQL,
oracle
From： https://blog.51cto.com/u_12991611/5807500