下面是应群
里一位网友的要求，把10.2.0.4升级到11.2.0.3的笔记发出来，这里只写了升级与降级时跑脚本的步骤，更多相关文档请参考MOS文件。
如何从数据库 11.2 降级至之前的版本（包括 11.2.0.4-11.2.0.1） (Doc ID 1525777.1) complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
其实数据库的升级步骤是很简单的，只是在升级前的测试与升级后业务的支持比较复杂，比如：升级后面SQL性能的变化等等。
1，运行utlu112i脚本,用于检查10.2.0.4升级到11.2.0.3需要注意或者修改的地方
特别是需要注意的是在源旧的环境中执行需要升级到的版本下的utlu112i.sql脚本，并注意观察一些警告与错误信息
www.htz.pw > @/tmp/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 10-03-2014 13:01:41
Script Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL1024
--> version: 10.2.0.4.0
--> compatible: 10.2.0.3.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 726 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 468 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:
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: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER SCOTT has 1 INVALID objects.
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 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 removing all hidden parameters prior to upgrading.
To view existing hidden parameters execute the following command
while connected AS SYSDBA:
SELECT name,description from SYS.V$PARAMETER WHERE name
LIKE '\_%' ESCAPE '\'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
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.
**********************************************************************
这里有一些警告的东西，有部分参数在11G环境也不受支持，所以更改一下。关闭数据库
www.htz.pw > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
www.htz.pw > exit
2，切换环境到11.2.0.3
这里需要将参数文件，tnsnames.ora文件，listener.ora文件，密码文件复制到新环境中，如果两个环境的属主不一致的时候，我们需要注意权限
[oracle@www.htz.pw oradata]$cp orcl1024 /soft -R [oracle@www.htz.pw oradata]$cp /oracle/app/oracle/product/10.2.0/db_1024/dbs/spfileorcl1024.ora /tmp [oracle@www.htz.pw oradata]$source ~/.1123 [oracle@www.htz.pw oradata]$cp /tmp/spfileorcl1024.ora $ORACLE_HOME/dbs [oracle@www.htz.pw oradata]$export ORACLE_SID=orcl1024
3，运行catupgrd脚本
这里需要注意的是catupgrd.sql脚本运行完后，会自动的关闭数据库。
oracle@www.htz.pw oradata]$sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 3 13:26:54 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. www.htz.pw > startup upgrade; ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 597098496 bytes Fixed Size 2230672 bytes Variable Size 125830768 bytes Database Buffers 465567744 bytes Redo Buffers 3469312 bytes Database mounted. Database opened. www.htz.pw > spool /tmp/upgrade.txt www.htz.pw > @?rdbms/admin/catupgrd.sql SP2-0310: unable to open file "/oracle/app/oracle/product/11.2.0/db_1123rdbms/admin/catupgrd.sql" www.htz.pw > @?/rdbms/admin/catupgrd.sql 这里需要注意，整个升级过程中大概需要30到60分钟。 www.htz.pw > www.htz.pw > www.htz.pw > /*****************************************************************************/ www.htz.pw > /* Step 10 - SHUTDOWN THE DATABASE..!!!!! www.htz.pw > */ www.htz.pw > /*****************************************************************************/ www.htz.pw > shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. www.htz.pw > www.htz.pw > www.htz.pw > www.htz.pw > 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># www.htz.pw > www.htz.pw > Rem Set errorlogging off www.htz.pw > SET ERRORLOGGING OFF; www.htz.pw > www.htz.pw > REM END OF CATUPGRD.SQL www.htz.pw > www.htz.pw > REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql. www.htz.pw > REM This forces user to start a new sqlplus session in order www.htz.pw > REM to connect to the upgraded db. www.htz.pw > exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
分析升级日志是否有错误输出，在upgrade.txt中搜索error/ora-开头的报错信息，这里不贴了。
4，升级后的其它的脚本运行
www.htz.pw > startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 597098496 bytes Fixed Size 2230672 bytes Variable Size 327157360 bytes Database Buffers 264241152 bytes Redo Buffers 3469312 bytes Database mounted. Database opened. www.htz.pw > @?/rdbms/admin/utlu112s.sql . Oracle Database 11.2 Post-Upgrade Status Tool 10-03-2014 14:26:14 . Component Current Version Elapsed Time Name Status Number HH:MM:SS . Oracle Server . VALID 11.2.0.3.0 00:10:24 JServer JAVA Virtual Machine . VALID 11.2.0.3.0 00:09:25 Oracle Workspace Manager . VALID 11.2.0.3.0 00:00:31 OLAP Analytic Workspace . VALID 11.2.0.3.0 00:00:42 OLAP Catalog . VALID 11.2.0.3.0 00:00:38 Oracle OLAP API . VALID 11.2.0.3.0 00:00:22 Oracle Enterprise Manager . VALID 11.2.0.3.0 00:06:47 Oracle XDK . VALID 11.2.0.3.0 00:02:08 Oracle Text . VALID 11.2.0.3.0 00:00:36 Oracle XML Database . VALID 11.2.0.3.0 00:06:51 Oracle Database Java Packages . VALID 11.2.0.3.0 00:00:14 Oracle Multimedia . VALID 11.2.0.3.0 00:03:16 Spatial . VALID 11.2.0.3.0 00:05:10 Oracle Expression Filter . VALID 11.2.0.3.0 00:00:09 Oracle Rules Manager . VALID 11.2.0.3.0 00:00:09 Gathering Statistics . 00:02:03 Total Upgrade Time: 00:49:35 PL/SQL procedure successfully completed. 所有组件都是有效的，升级成功 www.htz.pw > @?/rdbms/admin/catuppst.sql www.htz.pw > @?/rdbms/admin/utlrp.sql 时间取决与无效对象的个数
大概升级过程结束。
5，降级到原版本
在降级的时候，需要注意的是数据库兼容参数的配置。
www.htz.pw > shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. www.htz.pw > startup downgrade; ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 597098496 bytes Fixed Size 2230672 bytes Variable Size 339740272 bytes Database Buffers 251658240 bytes Redo Buffers 3469312 bytes Database mounted. Database opened. www.htz.pw > spool /tmp/downgrade.txt www.htz.pw > @?/rdbms/admin/catdwgrd.sql PL/SQL procedure successfully completed. Function created. Function dropped. DOC>############################################################################# DOC>############################################################################# DOC> DOC> If the below PL/SQL block raises an ORA-20001 error, then DOC> de-initialize the DBMS_AUDIT_MGMT package using DOC> declare DOC> RetVal BOOLEAN; DOC> begin DOC> RetVal := DBMS_AUDIT_MGMT.is_cleanup_initialized DOC> (DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD); DOC> if RetVal = TRUE then DOC> DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION DOC> (DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 'SYSTEM'); DOC> DOC> DBMS_AUDIT_MGMT.DEINIT_CLEANUP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD); DOC> end if; DOC> DOC> RetVal := DBMS_AUDIT_MGMT.is_cleanup_initialized DOC> (DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD); DOC> if RetVal = TRUE then DOC> DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION DOC> (DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, 'SYSTEM'); DOC> DOC> DBMS_AUDIT_MGMT.DEINIT_CLEANUP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD); DOC> end if; DOC> end; DOC> / DOC> DOC>####################################################################### DOC>####################################################################### DOC># PL/SQL procedure successfully completed. DOC>############################################################################# DOC>############################################################################# DOC> DOC> If the below PL/SQL block raises an ORA-20000 error, then DOC> consult the 11g Upgrade Guide for instructions for downgrading DOC> the EM Database Control Repository. DOC> DOC> Drop the SYSMAN user prior to downgrade using DOC> DROP USER SYSMAN CASCADE; DOC> DOC>####################################################################### DOC>####################################################################### DOC># BEGIN * ERROR at line 1: ORA-20000: Direct downgrade of EM Database Control not supported. ORA-06512: at line 6 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 这里手动删除EM的配置信息 www.htz.pw > @?/sysman/admin/emdrep/sql/core/latest/admin/admin_drop_users.sql SYSMAN old 22: FROM &EM_REPOS_USER..MGMT_CREATED_USERS new 22: FROM SYSMAN.MGMT_CREATED_USERS PL/SQL procedure successfully completed. www.htz.pw > DEFINE EM_REPOS_USER=SYSMAN @?/sysman/admin/emdrep/sql/core/latest/admin/admin_drop_repos_user.sql SYSMAN www.htz.pw > DEFINE EM_REPOS_USER=SYSMAN @?/sysman/admin/emdrep/sql/core/latest/admin/admin_drop_synonyms.sql www.htz.pw > DROP USER sysman CASCADE; User dropped. www.htz.pw > spool /tmp/down.txt www.htz.pw > @?/rdbms/admin/catdwgrd.sql www.htz.pw > COMMIT; Commit complete. www.htz.pw > SELECT 'COMP_TIMESTAMP DWGRD_END ' || 2 TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS ') || 3 TO_CHAR(SYSTIMESTAMP,'J SSSSS ') 4 AS timestamp FROM DUAL; TIMESTAMP ----------------------------------------------------------- COMP_TIMESTAMP DWGRD_END 2014-10-03 16:56:36 2456934 60996 1 row selected. www.htz.pw > www.htz.pw > Rem *********************************************************************** www.htz.pw > Rem END catdwgrd.sql www.htz.pw > Rem *********************************************************************** www.htz.pw > [oracle@www.htz.pw tmp]$grep "END STAGE" down.txt www.htz.pw > Rem END STAGE 1: Remove current release XML Classes and packages www.htz.pw > Rem END STAGE 2: downgrade installed components to previous release www.htz.pw > Rem END STAGE 3: downgrade actions always performed www.htz.pw > Rem END STAGE 1: downgrade from the current release www.htz.pw > Rem END STAGE 2: downgrade dictionary from current release 11.1.0 www.htz.pw > Rem END STAGE 1: downgrade from the current release www.htz.pw > Rem END STAGE 2: downgrade dictionary from current release 10.2.0 www.htz.pw > Rem END STAGE 1: downgrade from the current release www.htz.pw > Rem END STAGE 2: downgrade dictionary from current release to 11.1.0 www.htz.pw > Rem END STAGE 1: downgrade from the current release to 11g www.htz.pw > Rem END STAGE 2: downgrade dictionary from current release to 10.2 www.htz.pw > Rem END STAGE 4: downgrade dictionary to specified release www.htz.pw > shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. www.htz.pw > exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 切换到10.2.0.4的环境中 [oracle@www.htz.pw sql]$source ~/.1024 [oracle@www.htz.pw sql]$sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 3 17:01:57 2014 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to an idle instance. www.htz.pw > startup upgrade; ORACLE instance started. Total System Global Area 599785472 bytes Fixed Size 2085776 bytes Variable Size 125832304 bytes Database Buffers 465567744 bytes Redo Buffers 6299648 bytes Database mounted. Database opened. www.htz.pw > spool /tmp/reload.txt www.htz.pw > @?/rdbms/admin/catrelod.sql 大概20分钟手 www.htz.pw > www.htz.pw > Rem ======================================================================= www.htz.pw > Rem Display new versions and status www.htz.pw > Rem ======================================================================= www.htz.pw > www.htz.pw > column comp_name format a35 www.htz.pw > SELECT comp_name, status, substr(version,1,10) as version 2 from dba_server_registry order by modified; COMP_NAME STATUS VERSION ----------------------------------- ---------------------- -------------------- Oracle Database Packages and Types VALID 10.2.0.4.0 Oracle Database Catalog Views VALID 10.2.0.4.0 JServer JAVA Virtual Machine VALID 10.2.0.4.0 Oracle XDK VALID 10.2.0.4.0 Oracle Database Java Packages VALID 10.2.0.4.0 Oracle Text VALID 10.2.0.4.0 Oracle XML Database VALID 10.2.0.4.0 Oracle Workspace Manager VALID 10.2.0.4.3 Oracle Data Mining VALID 10.2.0.4.0 OLAP Analytic Workspace VALID 10.2.0.4.0 OLAP Catalog VALID 10.2.0.4.0 Oracle OLAP API VALID 10.2.0.4.0 Oracle interMedia VALID 10.2.0.4.0 Spatial INVALID 10.2.0.4.0 Oracle Expression Filter INVALID 10.2.0.4.0 Oracle Rule Manager INVALID 10.2.0.4.0 16 rows selected. www.htz.pw > www.htz.pw > DOC DOC>####################################################################### DOC>####################################################################### DOC> DOC> The above query lists the SERVER components now loaded in the DOC> database, along with their current version and status. DOC> DOC> Please review the status and version columns and look for DOC> any errors in the spool log file. If there are errors in the spool DOC> file, or any components are not VALID or not the correct 10.1.0 DOC> patch version, consult the downgrade chapter of the current release DOC> Database Upgrade book. DOC> DOC> Next shutdown immediate, restart for normal operation, and then DOC> run utlrp.sql to recompile any invalid application objects. DOC> DOC>####################################################################### DOC>####################################################################### DOC># www.htz.pw > www.htz.pw > Rem ******************************************************************* www.htz.pw > Rem END catrelod.sql www.htz.pw > Rem ******************************************************************* [oracle@www.htz.pw tmp]$grep '^www.htz.pw' reload.txt|grep -E 'ORA-|ERROR' www.htz.pw > startup force; ORACLE instance started. Total System Global Area 599785472 bytes Fixed Size 2085776 bytes Variable Size 197135472 bytes Database Buffers 394264576 bytes Redo Buffers 6299648 bytes Database mounted. Database opened. www.htz.pw > @?/rdbms/admin/utlrp.sql www.htz.pw > @db_version.sql www.htz.pw > www.htz.pw > set echo off Session altered. BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production COMP_ID COMP_NAME SCHEMA VERSION STATUS --------------- -------------------------------------------------- --------------- ---------- ---------------------- SDO Spatial MDSYS 10.2.0.4.0 VALID ORDIM Oracle interMedia ORDSYS 10.2.0.4.0 VALID AMD OLAP Catalog OLAPSYS 10.2.0.4.0 VALID XDB Oracle XML Database XDB 10.2.0.4.0 VALID CONTEXT Oracle Text CTXSYS 10.2.0.4.0 VALID EXF Oracle Expression Filter EXFSYS 10.2.0.4.0 VALID RUL Oracle Rule Manager EXFSYS 10.2.0.4.0 VALID OWM Oracle Workspace Manager WMSYS 10.2.0.4.3 VALID ODM Oracle Data Mining DMSYS 10.2.0.4.0 VALID CATALOG Oracle Database Catalog Views SYS 10.2.0.4.0 VALID CATPROC Oracle Database Packages and Types SYS 10.2.0.4.0 VALID JAVAVM JServer JAVA Virtual Machine SYS 10.2.0.4.0 VALID XML Oracle XDK SYS 10.2.0.4.0 VALID CATJAVA Oracle Database Java Packages SYS 10.2.0.4.0 VALID APS OLAP Analytic Workspace SYS 10.2.0.4.0 VALID XOQ Oracle OLAP API SYS 10.2.0.4.0 VALID 16 rows selected. ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS ------------------------------ -------------------- ---------- ---------- ---------- -------------------------------------------------- 2014-10-03 14:18:46.635571 VIEW INVALIDATE 8289601 view invalidation 2014-10-03 14:18:53.520704 UPGRADE SERVER 11.2.0.3.0 Upgraded from 10.2.0.4.0 2014-10-03 14:27:44.552560 APPLY SERVER 11.2.0.3 0 Patchset 11.2.0.2.0 2014-10-03 16:56:36.864938 DOWNGRADE 2014-10-03 17:18:58.584234 RELOAD SERVER 10.2.0.4.0 Reloaded after downgrade from 10.2.0
10.2.0.4升级到11.2.0.3(单机)：等您坐沙发呢！