四.升级 Instance 从10.2.0.5 到11.2.0.3
官方文档:
这个步骤11g的升级步骤相同,参考:
4.1 用resetlogs upgrade 模式open db
SQL> alter database open resetlogs upgrade;
Database altered.
4.2 upgrade模式下运行脚本:catupgrd.sql
SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
…
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>/*****************************************************************************/
SQL> /* Step 10 - SHUTDOWN THEDATABASE..!!!!!
SQL> */
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 currentsqlplus session at end of catupgrd.sql.
SQL> REM This forces user to start a newsqlplus session in order
SQL> REM to connect to the upgraded db.
SQL> exit
Disconnected from Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
注意:
1.我们备份之前的一个操作,必须先utlu112i.sql脚本, 然后执行这个脚本,否则就会出现如下错误。
SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
*
ERROR at line 1:
ORA-01722: invalid number
2.执行这个脚本会产生大量的归档文件,50M的online redo log,切换时间在2分钟左右,所以要增加onlineredo group,从而保证足够的切换时间,不能让其出现Checkpoint not complete。
在脚本运行期间还可能会调用之前的归档文件,所以也不要删除之前的归档。
3.这个脚本运行了整个2个小时,之前直接从11.2.0.1升级到11.2.0.3运行了80分钟。而在Oracle10g下,运行该脚本在40分钟左右。
导致执行占用这么长时间的原因如下:
1. 使用的是虚拟机
2. 之前没有注意到归档的问题,后面才看到checkpoint not complete,导致等待时间。
3. 运行脚本之前忘记修改java_pool_size 和 shared_pool_size. 增加这2个值,可以减少运行时间。
SQL>alter system setjava_pool_size=512M;
SQL>alter system set shared_pool_size=800M;
The upgrade scriptcreates and alters certain data dictionary tables. It also upgrades orinstallsthe following database components in the new Oracle Database11g Release 2(11.2) database:
(1) OracleDatabase Catalog Views
(2) OracleDatabase Packages andTypes
(3) JServerJAVA Virtual Machine
(4) OracleDatabase Java Packages
(5) OracleXDK
(6) OracleReal ApplicationClusters
(7) OracleWorkspace Manager
(8) OracleMultimedia
(9) OracleXML Database
(10) OLAP AnalyticWorkspace
(11) Oracle OLAP API
(12) OLAP Catalog
(13) Oracle Text
(14) Spatial
(15) Oracle DataMining
(16) Oracle LabelSecurity
(17) MessagingGateway
(18) OracleExpression Filter
(19) Oracle RulesManager
(20) OracleEnterprise Manager Repository
(21) Oracle DatabaseVault
(22) OracleApplication Express
4.3 执行utlu112s.sql脚本
这个脚本显示升级过程的一个摘要。不需要在upgrade 模式下。
SQL> @?/rdbms/admin/utlu112s.sql.
Oracle Database 11.2 Post-Upgrade StatusTool 03-02-2012 10:12:32
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. ORA-01187: cannot read from file because it failed verification tests
. ORA-01110: data file 201:"/u02/app/oracle/oradata/anqing/temp01.dbf"
. ORA-00942: table or view does not exist
. ORA-00942: table or view does not exist
. ORA-01187: cannot read from file because it failed verification tests
. ORA-01110: data file 201:"/u02/app/oracle/oradata/anqing/temp01.dbf"
. ORA-06512: at "SYS.DBMS_REPORT", line 841
. ORA-06512: at "SYS.PRVT_REPORT_TAGS", line 25
. ORA-06512: at line 1
. ORA-01187: cannot read from file because it failed verification tests
. ORA-01110: data file 201:"/u02/app/oracle/oradata/anqing/temp01.dbf"
. ORA-06512: at "SYS.DBMS_REPORT", line 841
. ORA-06512: at "SYS.PRVT_REPORT_REGISTRY", line 33
. ORA-06512: at line 1
. ORA-01187: cannot read from file because it failed verification tests
. ORA-01110: data file 201:"/u02/app/oracle/oradata/anqing/temp01.dbf"
. ORA-06512: at line 8
. ORA-01187: cannot read from file because it failed verification tests
. ORA-01110: data file 201:"/u02/app/oracle/oradata/anqing/temp01.dbf"
. ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2435
. ORA-06512: at line 1
. INVALID 11.2.0.3.0 00:28:07
JServer JAVA Virtual Machine
. VALID 11.2.0.3.0 00:19:25
Oracle Workspace Manager
. VALID 11.2.0.3.0 00:01:04
OLAP Analytic Workspace
. ORA-01187: cannot read from file because it failed verification tests
. ORA-01110: data file 201:"/u02/app/oracle/oradata/anqing/temp01.dbf"
. ORA-06512: at "SYS.DBMS_LOB", line 724
. ORA-06512: at "SYS.DBMS_AW_BUILD", line 72
. ORA-06512: at line 1
. INVALID 11.2.0.3.0 00:00:28
OLAP Catalog
. VALID 11.2.0.3.0 00:01:49
Oracle OLAP API
. VALID 11.2.0.3.0 00:01:00
Oracle Enterprise Manager
. VALID 11.2.0.3.0 00:15:51
Oracle XDK
. VALID 11.2.0.3.0 00:03:52
Oracle Text
. VALID 11.2.0.3.0 00:01:26
Oracle XML Database
. INVALID 11.2.0.3.0 00:19:46
Oracle Database Java Packages
. VALID 11.2.0.3.0 00:00:45
Oracle Multimedia
. INVALID 11.2.0.3.0 00:07:18
Spatial
. VALID 11.2.0.3.0 00:09:31
Oracle Expression Filter
. VALID 11.2.0.3.0 00:00:23
Oracle Rules Manager
. VALID 11.2.0.3.0 00:00:19
Gathering Statistics
. 00:05:02
Total Upgrade Time: 01:56:22
PL/SQL procedure successfully completed.
这里列举了整个upgrade 脚本及每个组件的执行时间。总共用时2个小时。 但是这里报了一个与temp 表空间相关的错误。 因为我在升级过程中对这个表空间进行了offline 操作,这里可能有影响,这里攒不研究这个问题。