随着Oracle 版本的更新，RMAN 也得到增強，在Oracle11g中的RMAN 支援不同作業系統和不同DB 之前的使用，關于RMAN 的相容性，參考：
Oracle RMAN 相容性 及 不同版本和不同平台之間使用 常見問題說明
http://www.cndba.cn/Dave/article/1034
這篇Blog 示範一個linux 下10g 恢複到11g中的示例。
Target 端：
OS： Oracle Linux 6.1 64位
DB： 11.2.0.3
Source 端：
OS：Oracle Linux 6.1 64位
DB： 10.2.0.5
注意：
Oracle 10.2.0.1 不能直接更新到11gR2，至少需要先更新到10.2.0.2 以後才可以更新到11gR2，關于Oracle 更新路線圖參考我的Blog：
Oracle9i/10g/11g 資料庫 更新路線圖（upgrade roadmap）
http://www.cndba.cn/dave/article/1363
簡單的來說就是2個步驟：
1. 将10g 資料庫 還原11g上
2. 更新還原的10g資料庫到11g。
具體操作步驟如下。
一．在Source 庫用RMAN 備份資料庫
1.1 在Source 庫上執行utlu112i.sql腳本
在Source 備份之前，需要執行一下utlu112i.sql 腳本，如果不執行，那麼在更新時執行 @?/rdbms/admin/catupgrd.sql 腳本時會報如下錯誤：
DOC> The following error is generated if the pre-upgrade tool has not been
DOC> run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:
DOC>
DOC> SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC> o Action:
DOC> Shutdown database ("alter systemcheckpoint" and then "shutdown abort").
DOC> Revert to the original oracle home andstart the database.
DOC> Run pre-upgrade tool against thedatabase.
DOC> Review and take appropriate actionsbased on the pre-upgrade
DOC> output before opening the datatabase inthe new software version.
DOC>
将11g $ORACLE_HOME/rdbms/admin 下的utlu112i.sql腳本copy到10g的$ORACLE_HOME/rdbms/admin 下，并在10g上執行，這個腳本可以檢查更新前的一些資訊。如果不滿足條件，會列出。
SQL> spool upgrade_info.log
SQL> @?/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-UpgradeInformation Tool 03-01-2012 19:53:57
Script Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: ANQING
--> version: 10.2.0.5.0
--> compatible: 10.2.0.5.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in thecurrent environment]
**********************************************************************
--> SYSTEM tablespace is adequate forthe upgrade.
.... minimum required size: 682 MB
--> UNDOTBS1 tablespace is adequate forthe upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate forthe upgrade.
.... minimum required size: 454 MB
--> TEMP tablespace is adequate for theupgrade.
.... minimum required size: 60 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lowerversion 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, referhere for Update Parameters:
-- No update parameter changes are required.
.
--> If Target Oracle is 64-Bit, referhere for Update Parameters:
WARNING: --> "sga_target"needs to be increased to at least 596 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changesare required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [UpdateOracle 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 databasecomponents 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 atimezone file older than version 14.
.... After the release migration, it isrecommended that DBMS_DST package
.... be used to upgrade the 10.2.0.5.0database timezone version
.... to the latest version which comes withthe new release.
WARNING: --> EM Database ControlRepository exists in the database.
.... Direct downgrade of EM DatabaseControl is not supported. Refer to the
.... Upgrade Guide for instructions to savethe EM data prior to upgrade.
WARNING: --> Your recycle bin is turnedon and currently contains no objects.
.... Because it is REQUIRED that therecycle bin be empty prior to upgrading
.... and your recycle bin is turned on, youmay need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade toconfirm the recycle bin is empty.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionarystatistics prior to
upgrading the database.
To gather dictionary statistics execute thefollowing command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends reviewing any definedevents prior to upgrading.
To view existing non-default events executethe 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 theinit.ora or spfile.
**********************************************************************
SQL>
如果直接在11g上執行這個腳本，那麼會報如下錯誤：
SQL> @?/rdbms/admin/utlu112i.sql
ERROR:
ORA-04023: Object SYS.STANDARD could not beval（10g) run
SQL> select * from v$timezone_file;
If time zone file version is lessthan 4 then apply time zone patch 5632264 manually for 10.2.0.1database。
--之前我們說10.2.0.1 不能直接更新到11gR2，通過這段文字，這個不能更新的原因可能就是與Time zone 相關。10.2.0.5的Time zone 值是4，滿足條件。對于timezone 值小于4的，要打Patch 5632264。
手頭沒有10.2.0.1的庫，暫時不能查詢，之前也遇到過一個time zone的問題。
Oracle TTSORA-39322: Cannot use transportable tablespace with different timezone version 說明
1.2 備份Source庫
備份腳本參考：
Linux平台下RMAN 全備 和 增量備份shell 腳本
http://www.cndba.cn/Dave/article/1060
Nocatalog下的RMAN 增量備份shell腳本
http://www.cndba.cn/Dave/article/1476
二． 将Source庫的備份集copy到Target庫的相同位置
rac2:/u01/backup> scp * 192.168.3.201:/u01/backup
[email protected]'s password:
anqing_lev0_0jn4qd0c_1_1_20120301 100% 361MB 12.0MB/s 00:30
anqing_lev0_0kn4qd0c_1_1_20120301 100% 174MB 10.2MB/s 00:17
anqing_spfile_0qn4qd2v_1_1_20120301 100% 96KB 96.0KB/s 00:00
arch_0nn4qd2q_1_1_20120301 100% 15MB 15.5MB/s 00:01
arch_0on4qd2q_1_1_20120301 100% 355KB 355.0KB/s 00:00
ctl_file_0pn4qd2t_1_1_20120301 100%6944KB 6.8MB/s 00:00
三．将10g備份restore 到11g庫上
這個涉及RMAN 相容性問題，參考我的blog：
Oracle RMAN 相容性 及 不同版本和不同平台之間使用 常見問題說明
http://www.cndba.cn/Dave/article/1034
Oracle 11gR2 Upgrade Companion [ID785351.1]
restore步驟也可以參考Blog，大緻相同：
Oracle 單執行個體 從32位 遷移到 64位 方法（二）--使用 RMANRestore 示例
http://www.cndba.cn/Dave/article/1337
1.1 還原控制檔案
我們的RMAN 備份資訊存儲在控制檔案裡，是以要先還原控制檔案，才可以使用RMAN, 其次，我們這裡使用的是Source和target 的datafile 目錄相同，是以不用修改pfile 等參數。 如果不同，參考上面的遷移文檔。
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 1402982400 bytes
Fixed Size 2228304 bytes
Variable Size 1124077488 bytes
Database Buffers 268435456 bytes
Redo Buffers 8241152 bytes
RMAN> restore controlfile from '/u01/backup/ctl_file_0fn4pobv_1_1_20120301';
Starting restore at 01-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete,elapsed time: 00:00:02
output filename=/u02/app/oracle/oradata/anqing/control01.ctl
output filename=/u02/app/oracle/fast_recovery_area/anqing/control02.ctl
Finished restore at 01-MAR-12
1.2 Restore 資料庫
RMAN> set dbid=707730149
executing command: SET DBID
--這裡要注意一個問題，就是這個dbid值，因為我們的RMAN 備份集是從其他的資料庫上copy 過來的，備份集中的記錄的是Source 的DBID值，是以這裡我們也要指定dbid 等于Source 的DBID值。 RMAN 連接配接時會顯示該值，通過視圖也可以檢視。
RMAN> alter database mount;
database mounted
--如果不指定dbid，而使用預設的target 的dbid值，那麼restore時會報錯，如：
RMAN> restore database;
RMAN-03002: failure of restore command at03/01/2012 16:13:44
RMAN-12010: automatic channel allocationinitialization failed
RMAN-06189: current DBID 706107904 does notmatch target mounted database (707730149)
指定Source 的dbid 之後，就可以正常的進行restore database 操作了：
RMAN> restore database;
Starting restore at 01-MAR-12
Starting implicit crosscheck backup at01-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at01-MAR-12
Starting implicit crosscheck copy at01-MAR-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at01-MAR-12
searching for all files in the recoveryarea
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafilebackup set restore
channel ORA_DISK_1: specifying datafile(s)to restore from backup set
channel ORA_DISK_1: restoring datafile00002 to /u02/app/oracle/oradata/anqing/undotbs01.dbf
channel ORA_DISK_1: restoring datafile00003 to /u02/app/oracle/oradata/anqing/sysaux01.dbf
channel ORA_DISK_1: reading from backuppiece /u01/backup/anqing_lev0_0an4po9q_1_1_20120301
channel ORA_DISK_1: piecehandle=/u01/backup/anqing_lev0_0an4po9q_1_1_20120301 tag=ANQING_LEV0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete,elapsed time: 00:00:15
channel ORA_DISK_1: starting datafilebackup set restore
channel ORA_DISK_1: specifying datafile(s)to restore from backup set
channel ORA_DISK_1: restoring datafile00001 to /u02/app/oracle/oradata/anqing/system01.dbf
channel ORA_DISK_1: restoring datafile00004 to /u02/app/oracle/oradata/anqing/users01.dbf
channel ORA_DISK_1: reading from backuppiece /u01/backup/anqing_lev0_09n4po9q_1_1_20120301
channel ORA_DISK_1: piecehandle=/u01/backup/anqing_lev0_09n4po9q_1_1_20120301 tag=ANQING_LEV0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete,elapsed time: 00:00:26
Finished restore at 01-MAR-12
1.3 Recoverdatabase 操作：
如果直接進行recover 操作，那麼在最後會遇到類似如下的資訊：
RMAN-06054:media recoveryrequesting unknown archived log for thread 1 with sequence 7 and starting SCNof 832585
這裡是提醒恢複到一個未知的scn号。在alter database mount之後，通過set until scn或者set until time指令設定恢複到的scn号或時間。就可以避免這個錯誤。
是以在執行recover 之前，我們指定一個sequence：
去source 庫檢視：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/archivelog
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9
這裡目前是9，是以我們recover 到9，在Target 庫進行recover 操作：
RMAN> run {
2> set until sequence 9;
3> recover database;
4> }
executing command: SET until clause
Starting recover at 01-MAR-12
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived logrestore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: reading from backuppiece /u01/backup/arch_0nn4qd2q_1_1_20120301
channel ORA_DISK_1: piecehandle=/u01/backup/arch_0nn4qd2q_1_1_20120301 tag=ARC_BAK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete,elapsed time: 00:00:01
archived log filename=/u02/archivelog/1_7_776788394.arc thread=1 sequence=7
channel ORA_DISK_1: starting archived logrestore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_1: reading from backuppiece /u01/backup/arch_0on4qd2q_1_1_20120301
channel ORA_DISK_1: piecehandle=/u01/backup/arch_0on4qd2q_1_1_20120301 tag=ARC_BAK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete,elapsed time: 00:00:01
archived log filename=/u02/archivelog/1_8_776788394.arc thread=1 sequence=8
media recovery complete, elapsed time:00:00:02
Finished recover at 01-MAR-12
注意這裡不open db。
1.4 重建TEMP 表空間
--這一步很重要，做恢複，遷移，重建控制檔案，都需要做這一步操作。
SQL> alter tablespace temp add tempfile'/u02/app/oracle/oradata/anqing/temp01.dbf' size 100m reuse autoextend on next10m maxsize 1000m;
也可以通過中轉TMP 表空間進行替換，具體參考：
Oracle Temp 臨時表空間
四．更新 Instance 從10.2.0.5 到11.2.0.3
官方文檔：
http://docs.oracle.com/cd/E11882_01/server.112/e17222/upgrade.htm#CACGGHJC
這個步驟11g的更新步驟相同，參考：
Oracle11.2.0.1 更新到11.2.0.3 示例
http://www.cndba.cn/Dave/article/1354
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 操作，這裡可能有影響，這裡攢不研究這個問題。
4.4 執行catuppst.sql腳本，不需要在upgrade 模式下執行
SQL> @?/rdbms/admin/catuppst.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2012-03-0123:25:22
PL/SQL procedure successfully completed.
This script will migrate the Baseline dataon a pre-11g database to the 11g database.
--這個腳本用來遷移一些Baseline 資料到11g資料庫中。
…..
這個腳本運作2分鐘左右。
4.5 編譯無效對象
SQL> select count(*) from dba_invalid_objects;
COUNT(*)
----------
5875
sql> select distinct object_name from dba_invalid_objects;
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2012-03-02 09:36:56
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 statusIN (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(*) FROMUTL_RECOMP_COMPILED;
--這2個SQL 可以檢視腳本執行的狀态。
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 FROMdba_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>#
該腳本運作需要花30+分種。 都很費時間啊。
SQL> select count(*) fromdba_invalid_objects;
COUNT(*)
----------
67
腳本執行完畢之後還有67個無效對象。使用如下SQL 檢視了一下：
sql> select distinct object_name from dba_invalid_objects;
都是和OLAP 元件相關，關于該元件，這裡不讨論。
4.6 檢查元件狀态
SQL> col comp_name for a40
SQL> set wrap off
SQL> select comp_name,version, statusfrom dba_registry;
COMP_NAME VERSION STATUS
---------------------------------------------------------------------- --------
Oracle Enterprise Manager 11.2.0.3.0 VALID
OLAP Catalog 11.2.0.3.0 VALID
Spatial 11.2.0.3.0 VALID
Oracle Multimedia 11.2.0.3.0 INVALID
Oracle XML Database 11.2.0.3.0 INVALID
Oracle Text 11.2.0.3.0 VALID
Oracle Data Mining 11.2.0.3.0 VALID
Oracle Expression Filter 11.2.0.3.0 VALID
Oracle Rules Manager 11.2.0.3.0 VALID
Oracle Workspace Manager 11.2.0.3.0 VALID
Oracle Database Catalog Views 11.2.0.3.0 VALID
COMP_NAME VERSION STATUS
---------------------------------------------------------------------- --------
Oracle Database Packages and Types 11.2.0.3.0 INVALID
JServer JAVA Virtual Machine 11.2.0.3.0 VALID
Oracle XDK 11.2.0.3.0 VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
OLAP Analytic Workspace 11.2.0.3.0 INVALID
Oracle OLAP API 11.2.0.3.0 VALID
17 rows selected.
到這裡，我們需要重建OLAP. 這裡不在示範， 在之前的Blog裡有詳細的重建OLAP 組建的過程，參考如下Blog的第12小節：
Oracle 單執行個體 從32位 遷移到 64位 方法（一）--直接 copydatafiles
http://www.cndba.cn/Dave/article/1338
MOS 有篇文檔提供了重建OLAP元件的方法：
How To Remove Or To Reinstall TheOLAPOption To 10g And 11g [ID 332351.1]
至此，restore 過程結束，但是還有一些元件是無效的，需要重建，這裡就不整理，DBA 本就是一個拼人品的工作，這次Dave人品稍稍差了一點，雖然這裡有一些無效元件，但不影響DB的使用， 而且OLAP 本身就是一個可選的元件，隻不過在安裝DB時預設安裝上了，其實如果用不到OLAP，這個元件都可以不裝。
關于DB 元件的重建，會在近期整理出相關的Blog。
4.7 如果在更新過程中遇到問題，可以重新執行更新腳本，步驟如下：
1.Shut down the database as follows:
SQL>SHUTDOWNIMMEDIATE
2.Restart thedatabasein UPGRADE mode:
SQL> STARTUPUPGRADE
3.Rerun catupgrd.sql: --更新DB
SQL> SPOOLupgrade.log
SQL>@catupgrd.sql
Note:
You canrerunthe catupgrd.sql script as many times as necessary. The firsttimeyou run the script, there should be no error messages returned. If yourerunthe script, then the ORA-00001 messageisdisplayed. You can safely ignore this message.
--腳本執行結束會自動shutdown immediate db，是以下面的操作在normal 狀态執行即可。
4.Rerun utlu112s.sql: --報告更新的過程的摘要
SQL>@utlu112s.sql
5.遷移10g的Baseline 到11g
SQL> @?/rdbms/admin/catuppst.sql
6.編譯無效對象：
SQL> @?/rdbms/admin/utlrp.sql
相關的一個問題：
![Oracle的基本操作[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)