Step by Step oracle database 10gR2 upgrade to 11.2.0.2
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: Step by Step oracle database 10gR2 upgrade to 11.2.0.2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
1. 安装11.2.0.2.0 software; 安装软件之前，需要先安装如下的包： -- 对HP IA64平台，安装11.2之前，需要安装如下的操作os patch： PHCO_40381 PHKL_38691 PHKL_38762 PHKL_38938 PHKL_39351 PHSS_36354 PHSS_37042 PHSS_37959 PHSS_38141 PHSS_39100 PHSS_39102 其中PHKL_39351单独是无法安装的，安装过程中提示需要安装如下 依赖相关的包： -- PHKL_39351 Patch Dependencies： 11.31： PHKL_36142 PHKL_36699 PHKL_37465 PHKL_38691 PHKL_37650 但是安装上面的包时，又需要其他的包，hp真是BT，无奈之下我只能下载如下的zip包， 同时把所有依赖patch全部下载，然后安装该zip包，即可。 hpux_11.31_11180102.zip -- 安装11.2.0.2以后，安装如下两个重要的one-off patch Bug 10368698 Bug 10419629 2. 运行dbupgdiag.sql进行检查(或运行@ utlu112i.sql) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 |
$ cd <location of the script> $ sqlplus / as sysdba sql> alter session set nls_language='American'; sql> @dbupgdiag.sql sql> exit 该脚本需要从Mos上下载。（个人感觉运行两个脚本结合一起用比较好） 该脚本运行情况如下： SQL> @ dbupgdiag.sql Enter location for Spooled output： 19_Nov_2011_0837 .log DMSBHMC_ SP2-0606： Cannot create SPOOL file "0/db_upg_diag_DMSBHMC_19_Nov_2011_0837.log" *** Start of LogFile *** Oracle Database Upgrade Diagnostic Utility 11-19-2011 20：37：11 =============== Hostname =============== dmsdbp02 =============== Database Name =============== DMSBHMC =============== Database Uptime =============== 20：03 19-NOV-11 ================= Database Wordsize ================= This is a 64-bit database ================ Software Version ================ Oracle Database 10g Release 10.2.0.4.0 - 64bit Production PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for HPUX： Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production ============= Compatibility ============= Compatibility is set as 10.2.0.3.0 ================ Archive Log Mode ================ Database log mode Archive Mode Automatic archival Enabled Archive destination /ora_arch/DMSBHMC/ Oldest online log sequence 38503 Next log sequence to archive 38505 Current log sequence 38505 ================ Auditing Check ================ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /ora_dump/DMSBHMC/adump/ audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string NONE =========================================== Tablespace and the owner of the aud$ table =========================================== OWNER TABLESPACE_NAME ------------ ------------------------------ SYS SYSTEM ============================================================================ count of records in the sys.aud$ table where dbid is null- Standard Auditing ============================================================================ 0 ============================================================================================ count of records in the system.aud$ when dbid is null，Std Auditing with OLS or DV installed ============================================================================================ select count(*) from system.aud$ where dbid is null * ERROR at line 1： ORA-00942： table or view does not exist ============================================================================= count of records in the sys.fga_log$ when dbid is null，Fine Grained Auditing ============================================================================= 0 ========================================== Oracle Label Security is installed or not ========================================== Oracle Label Security is NOT installed at database level ================ Number of AQ Records in Message Queue Tables ================ SYS - ALERT_QT - 28968 SYS - AQ$_MEM_MC - 0 SYS - AQ_EVENT_TABLE - 0 SYS - AQ_SRVNTFN_TABLE - 0 SYS - KUPC$DATAPUMP_QUETAB - 0 SYS - SCHEDULER$_EVENT_QTAB - 0 SYS - SCHEDULER$_JOBQTAB - 0 SYS - SYS$SERVICE_METRICS_TAB - 0 SYSMAN - MGMT_NOTIFY_QTABLE - 0 SYSTEM - DEF$_AQCALL - 0 SYSTEM - DEF$_AQERROR - 0 WMSYS - WM$EVENT_QUEUE_TABLE - 0 ================ Time Zone version ================ 4 ================ Local Listener ================ ================ Default and Temporary Tablespaces By User ================ USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE ---------------------------- ---------------------- ---------------------- PERFSTAT TEMP_TS STATSPACK BHMCCRM TEMP_TS DMSB_TS01 DMSB01 TEMP_TS DMSB_TS01 KCHC01 TEMP_TS USERS BMC TEMP_TS USERS BHMCIT TEMP_TS USERS SCOTT TEMP_TS USERS MDDATA TEMP_TS USERS TSMSYS TEMP_TS USERS DIP TEMP_TS USERS ORACLE_OCM TEMP_TS USERS DBSNMP TEMP_TS SYSAUX SYSMAN TEMP_TS SYSAUX EXFSYS TEMP_TS SYSAUX DMSYS TEMP_TS SYSAUX WMSYS TEMP_TS SYSAUX ORDSYS TEMP_TS SYSAUX XDB TEMP_TS SYSAUX SI_INFORMTN_SCHEMA TEMP_TS SYSAUX ORDPLUGINS TEMP_TS SYSAUX MDSYS TEMP_TS SYSAUX CTXSYS TEMP_TS SYSAUX ANONYMOUS TEMP_TS SYSAUX MGMT_VIEW TEMP_TS SYSTEM SYS TEMP_TS SYSTEM SYSTEM TEMP_TS SYSTEM OUTLN TEMP_TS SYSTEM DRS DRS_TMP DRS_SPA DRS_BACKUP TBST_BTEP TBSD_TDEF ================ Component Status ================ Comp ID Component Status Version Org_Version Prv_Version ------- ---------------------------------- --------- -------------- -------------- -------------- APS OLAP Analytic Workspace REMOVED 10.2.0.4.0 CATALOG Oracle Database Catalog Views VALID 10.2.0.4.0 CATJAVA Oracle Database Java Packages VALID 10.2.0.4.0 CATPROC Oracle Database Packages and Types VALID 10.2.0.4.0 CONTEXT Oracle Text VALID 10.2.0.4.0 EM Oracle Enterprise Manager VALID 10.2.0.4.0 EXF Oracle Expression Filter VALID 10.2.0.4.0 JAVAVM JServer JAVA Virtual Machine VALID 10.2.0.4.0 ODM Oracle Data Mining VALID 10.2.0.4.0 ORDIM Oracle interMedia VALID 10.2.0.4.0 OWM Oracle Workspace Manager VALID 10.2.0.4.3 RUL Oracle Rules Manager VALID 10.2.0.4.0 SDO Spatial VALID 10.2.0.4.0 XDB Oracle XML Database VALID 10.2.0.4.0 XML Oracle XDK VALID 10.2.0.4.0 XOQ Oracle OLAP API REMOVED 10.2.0.4.0 ====================================================== List of Invalid Database Objects Owned by SYS / SYSTEM ====================================================== Number of Invalid Objects ------------------------------------------------------------------ There are no Invalid Objects DOC>################################################################ DOC> DOC> If there are no Invalid objects below will result in zero rows. DOC> DOC>################################################################ DOC># no rows selected ================================ List of Invalid Database Objects ================================ Number of Invalid Objects ------------------------------------------------------------------ There are no Invalid Objects DOC>################################################################ DOC> DOC> If there are no Invalid objects below will result in zero rows. DOC> DOC>################################################################ DOC># no rows selected ====================================================== Count of Invalids by Schema ====================================================== ============================================================== Identifying whether a database was created as 32-bit or 64-bit ============================================================== DOC>########################################################################### DOC> DOC> Result referencing the string 'B023' ==> Database was created as 32-bit DOC> Result referencing the string 'B047' ==> Database was created as 64-bit DOC> When String results in 'B023' and when upgrading database to 10.2.0.3.0 DOC> (64-bit) ，For known issue refer below articles DOC> DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While DOC> Upgrading Or Patching Databases To 10.2.0.3 DOC> Note 579523.1 ORA-600 [22635]，ORA-600 [KOKEIIX1]，ORA-7445 [KOPESIZ] and DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6 DOC> DOC>########################################################################### DOC># Metadata Initial DB Creation Info -------- ----------------------------------- B047 Database was created as 64-bit =================================================== Number of Duplicate Objects Owned by SYS and SYSTEM =================================================== Counting duplicate objects .... COUNT(1) ---------- 4 ========================================= Duplicate Objects Owned by SYS and SYSTEM ========================================= Querying duplicate objects .... OBJECT_NAME OBJECT_TYPE ---------------------------------------- ---------------------------------------- AQ$_SCHEDULES TABLE AQ$_SCHEDULES_PRIMARY INDEX DBMS_REPCAT_AUTH PACKAGE DBMS_REPCAT_AUTH PACKAGE BODY DOC> DOC>################################################################################ DOC> DOC> If any objects found please follow below article. DOC> Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema DOC> Read the Exceptions carefully before taking actions. DOC> DOC>################################################################################ DOC># ================ JVM Verification ================ JAVAVM - Installed properly ================================================ Checking Existence of Java-Based Users and Roles ================================================ DOC> DOC>################################################################################ DOC> DOC> There should not be any Java Based users for database version 9.0.1 and above. DOC> If any users found，it is faulty JVM. DOC> DOC>################################################################################ DOC># User Existence --------------------------- No Java Based Users DOC> DOC>############################################################### DOC> DOC> Healthy JVM Should contain Six Roles. DOC> If there are more or less than six role，JVM is inconsistent. DOC> DOC>############################################################### DOC># Role ------------------------------ There are 6 JAVA related roles Roles ROLE ------------------------------ JAVAUSERPRIV JAVAIDPRIV JAVASYSPRIV JAVADEBUGPRIV JAVA_ADMIN JAVA_DEPLOY ========================================= List of Invalid Java Objects owned by SYS ========================================= There are no SYS owned invalid JAVA objects DOC> DOC>################################################################# DOC> DOC> Check the status of the main JVM interface packages DBMS_JAVA DOC> and INITJVMAUX and make sure it is VALID. DOC> If there are no Invalid objects below will result in zero rows. DOC> DOC>################################################################# DOC># no rows selected INFO： Below query should succeed with 'foo' as result. JAVAVM TESTING --------------- foo *** End of LogFile *** not spooling currently Upload db_upg_diag_DMSBHMC_19_Nov_2011_0837.log from "0" directory |
|
1 |
3. 根据 utlu112i.sql 的运行结果进行升级前的准备, 如下： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 |
SQL> @ utlu112i.sql Oracle Database 11.2 Pre-Upgrade Information Tool 11-18-2011 18：52：27 Script Version： 11.2.0.2.0 Build： 001 . ********************************************************************** Database： ********************************************************************** --> name： DMSBHMC --> version： 10.2.0.4.0 --> compatible： 10.2.0.3.0 --> blocksize： 8192 --> platform： HP-UX IA (64-bit) --> timezone file： V4 . ********************************************************************** Tablespaces： [make adjustments in the current environment] ********************************************************************** --> SYSTEM tablespace is adequate for the upgrade. .... minimum required size： 786 MB --> UNDOTBS1 tablespace is adequate for the upgrade. .... minimum required size： 7423 MB WARNING： --> SYSAUX tablespace is not large enough for the upgrade. .... currently allocated size： 1000 MB .... minimum required size： 1008 MB .... increase current size by： 8 MB .... tablespace is AUTOEXTEND ENABLED. --> TEMP_TS tablespace is adequate for the upgrade. .... minimum required size： 61 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 --> JServer JAVA Virtual Machine [upgrade] VALID --> Oracle XDK for Java [upgrade] VALID --> Oracle Workspace Manager [upgrade] VALID --> OLAP Analytic Workspace [upgrade] INVALID --> OLAP Catalog [upgrade] INVALID --> 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] INVALID . ********************************************************************** 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 PUBLIC has 1 INVALID objects. .... USER DRS has 12 INVALID objects. .... USER DMSB01 has 29 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 contains 298 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. ********************************************************************** SP2-0042： unknown command "$" - rest of line ignored. SQL> spool off [DMSBHMC] oracle@dmsdbp02：/ora_engine $ |
|
1 2 3 4 5 6 7 8 9 10 |
4. 应客户要求，停库，进行物理全冷备 -- cp 所有数据文件，controlfile, redo log(tmp除外） -- tar 包备份10g $ORACLE_HOME 5. 扩展相关的表空间 我这里扩容sysaux和system即可。 6. Remove OLAP组件 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
Execute： @?/olap/admin/catnoamd.sql; @?/olap/admin/olapidrp.plb; @?/olap/admin/catnoaps.sql; @?/olap/admin/catnoxoq.sql; @?/olap/admin/cwm2drop.sql; 然后进行check： SQL> Col comp_name FOR a35 SQL> Col STATUS FOR a15 SQL> SET LINES 150 SQL> SELECT comp_name,version,STATUS FROM dba_registry; COMP_NAME VERSION STATUS ----------------------------------- ------------------------------ --------------- Spatial 10.2.0.4.0 VALID Oracle interMedia 10.2.0.4.0 VALID Oracle Enterprise Manager 10.2.0.4.0 VALID Oracle XML Database 10.2.0.4.0 VALID Oracle Text 10.2.0.4.0 VALID Oracle Expression Filter 10.2.0.4.0 VALID Oracle Rules Manager 10.2.0.4.0 VALID Oracle Workspace Manager 10.2.0.4.3 VALID Oracle Data Mining 10.2.0.4.0 VALID Oracle Database Catalog Views 10.2.0.4.0 VALID Oracle Database Packages and Types 10.2.0.4.0 VALID JServer JAVA Virtual Machine 10.2.0.4.0 VALID Oracle XDK 10.2.0.4.0 VALID Oracle Database Java Packages 10.2.0.4.0 VALID OLAP Analytic Workspace 10.2.0.4.0 REMOVED Oracle OLAP API 10.2.0.4.0 REMOVED 16 rows selected. 针对不同版本，remove OLAP组件，请参看如下文档： Master Note for Oracle XML Database (XDB) Install / Deinstall [ID 1292089.1] |
|
1 2 3 |
7. Recomplied(or clear) invalid objects 首先运行utlrp.sql，对于无效对象，跟应用确认如下可以删除： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
drop procedure "DMSB01.UPDATE_VINORDER" ; drop procedure "DMSB01.UPDATE_ORDE_STATUS" ; drop procedure CLEAN_WSTCLEP_DUP ; drop procedure CONV_LONG_VARCHAR ; drop procedure OVEH_MIGRATION ; drop procedure GEN_CUST_ID2 ; drop procedure BILL_MIGRATION ; drop procedure GEN_BILL_NO ; drop procedure BILL_REPM_MAIGRAION ; drop procedure BILL_REPL_MIGRATION ; drop procedure GEN_CUST_ID ; drop procedure REPP_MIGRATION ; drop procedure REPM_MIGRATION ; drop procedure REPL_MIGRATION ; drop procedure CLPP_MIGRATION ; drop procedure CLOP_MIGRATION ; drop procedure CLAIM_MIGRATION ; drop procedure TEST ; drop procedure GEN_CLAIM_NO ; drop procedure BILL_REPP_MIGRATION ; drop function DRS.CCC ; drop view DRS.V_20110601_03_01_02SP_PZ ; drop view DRS.V_20110901_03_01_02SP_PZ ; drop view DRS.V_20110401_03_01_02SP_PZ ; drop view DRS.V_TJ_0901_FQHZ ; drop view DRS.V_TJ_1008_FQHZ ; drop view DRS.V_20110503_03_01_02SP_PZ ; drop view DRS.V_TJ_0802_FQHZ ; drop view DRS.V_20110704_03_01_02SP_PZ ; drop view DRS.V_20110801_03_01_02SP_PZ ; |
|
1 |
8. 清理回收站 |
|
1 2 3 4 5 6 |
-- select count(*) from dba_recyclebin; -- purge dba_recyclebin SQL> PURGE DBA_RECYCLEBIN; DBA Recyclebin purged. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
9. Check again -- select cocunt(*) from dba_objects where status='INVALID'; -- 再次运行 utlu112i.sql 10. 收集统计 运行如下脚本即可（需要从metalink下载）： check_schema_stale_stats.sql 执行情况如下： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
SQL> @ check_stale_stats.sql ------------------------------------------------------------------------------------------------------- -- CTXSYS schema contains stale statistics use the following to gather the statistics -- ------------------------------------------------------------------------------------------------------- EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('CTXSYS', OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', CASCADE=>TRUE); -- There are no stale statistics in DMSYS schema. -- There are no stale statistics in EXFSYS schema. ------------------------------------------------------------------------------------------------------- -- MDSYS schema contains stale statistics use the following to gather the statistics -- ------------------------------------------------------------------------------------------------------- EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('MDSYS', OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', CASCADE=>TRUE); -- There are no stale statistics in ORDSYS schema. ------------------------------------------------------------------------------------------------------- -- SYS schema contains stale statistics use the following to gather the statistics -- ------------------------------------------------------------------------------------------------------- EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYS', OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', CASCADE=>TRUE); ------------------------------------------------------------------------------------------------------- -- SYSMAN schema contains stale statistics use the following to gather the statistics -- ------------------------------------------------------------------------------------------------------- EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYSMAN', OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', CASCADE=>TRUE); -- There are no stale statistics in WMSYS schema. ------------------------------------------------------------------------------------------------------- -- XDB schema contains stale statistics use the following to gather the statistics -- ------------------------------------------------------------------------------------------------------- EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('XDB', OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', CASCADE=>TRUE); 根据上面结果，执行相关的sql即可。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
11. Check for TIMESTAMP WITH TIMEZONE Datatype select TZ_VERSION from registry$database; 12. Check that the National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 or AL16UTF16. select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET'; If this is UTF8 or AL16UTF16 then no action is needed. If is not UTF8 or AL16UTF16 then refer to the following article： Note 276914.1 The National Character Set in Oracle 9i and 10g. 13. Optimizer Statistics |
|
1 2 3 4 5 |
$ sqlplus "/as sysdba" SQL> @check_schema_stale_stats.sql SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; |
|
1 2 3 4 5 |
14. Disable Oracle Database Vault 15. Backing up Enterprise Manager Database Control Data 16. check 数据字典错误 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Set verify off Set space 0 Set line 120 Set heading off Set feedback off Set pages 1000 Spool analyze.sql SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;' FROM dba_clusters WHERE owner='SYS' UNION SELECT 'Analyze table "'||table_name||'" validate structure cascade;' FROM dba_tables WHERE owner='SYS' AND partitioned='NO' AND (iot_type='IOT' OR iot_type is NULL) UNION SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;' FROM dba_tables WHERE owner='SYS' AND partitioned='YES'; spool off $ sqlplus "/ as sysdba" SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql SQL> @analyze.sql |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
17. Ensure that all snapshot refreshes are successfully completed，and that replication is stopped. SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times; 18. Ensure that no files need media recovery. SQL> SELECT * FROM v$recover_file; no rows selected 19. Ensure that no files are in backup mode. SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; 20. Resolve outstanding distributed transactions prior to the upgrade. |
|
1 2 3 4 5 6 7 |
SQL> select * from dba_2pc_pending; If this returns rows you should do the following： SQL> SELECT local_tran_id FROM dba_2pc_pending; SQL> EXECUTE dbms_transaction.purge_lost_db_entry(''); SQL> COMMIT; |
|
1 |
21. To check if a standby database exists |
|
1 2 3 4 5 |
SQL> SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1) 2 FROM v$parameter 3 WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%'; no rows selected |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
22. Disable all batch and cron jobs. 23. 如果有em，那么需要备份em原数据 由于客户这里并没有配置em，故我跳过该步骤，如果存在的话，那么参看如下文档进行操作即可。 How To Save Oracle Enterprise Manager Database Control Data Before Upgrading The Single Instance Database To Other Release ? [ID 870877.1] 24. Ensure the users SYS and SYSTEM have 'SYSTEM' as their default tablespace. You must have sufficient space in the tablespace or be set to extents unlimited. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- 操作记录 SQL> SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1) 2 FROM v$parameter 3 WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%'; no rows selected SQL> SELECT username，default_tablespace 2 FROM dba_users 3 WHERE username in ('SYS','SYSTEM'); USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ SYS SYSTEM SYSTEM SYSTEM |
|
1 |
25. Ensure that if the aud$ table exists that it is in the SYS schema and in the SYSTEM tablespace. |
|
1 2 3 4 5 6 7 |
SQL> SELECT owner,tablespace_name 2 FROM dba_tables 3 WHERE table_name='AUD$'; OWNER TABLESPACE_NAME ------------------------------ ------------------------------ SYS SYSTEM |
|
1 |
26. Check whether database has any externally authenticated SSL users. |
|
1 2 3 4 5 6 |
SQL> SELECT name 2 FROM sys.user$ 3 WHERE ext_username IS NOT NULL 4 AND password = 'GLOBAL'; no rows selected |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
27. stop database,stoo listener and dbconsole. -- shutdown immediate -- lsnrctl stop -- emctl stop dbconsole 28. 修改.profile，编辑复制原spfile进行适当编辑生成一个11gR2 pfile -- 修改.profile 中$ORACLE_HOME即可（指定到11gR2 目录） 如下： export ORACLE_HOME=/ora_engine/product/11.2.0/dbhome_1 -- 修改pfile，如下： 具体内容省略。 备注：如何确认10g中那些参数在11gR2中已经废弃，那么可以参看utlu112i.sql的运行结果，并作相应修改即可。 -- 将10g 密码文件 listener.ora，tnsnames.ora拷贝到11gR2中 -- 修改/etc/oratab 29. 进行升级 当然，你可以使用dbua进行升级，我这里选择使用脚本进行： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- shutdown immediate; -- startup upgrade; SQL> set echo on SQL> SPOOL upgrade.log SQL> @ /ora_engine/product/11.2.0/dbhome_1/rdbms/admin/catupgrd.sql SQL> spool off $ sqlplus "/as sysdba" SQL> STARTUP SQL> @ /ora_engine/product/11.2.0/dbhome_1/rdbms/admin/utlu112s.sql SQL> @ /ora_engine/product/11.2.0/dbhome_1/rdbms/admin/catuppst.sql SQL> @ /ora_engine/product/11.2.0/dbhome_1/rdbms/admin/utlrp.sql |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
30. 升级完成以后，检查组件是否正常 select comp_name,version,status from sys.dba_registry 31. check indexes select owner,index_name from dba_indexes where status='UNUSABLE'; 进查，对17个状态unusable的index进行rebuild。 32. 启动listener，dbconsole，进行应用测试 33. 进行TDE加密配置 关于TDE加密实施涉及到用户信息，故省略。 注意： 当使用 alter table table_name move tablespace tbs_encrypt parallel n; 或 alter index index_name rebuild tablespace tbs_encrypt parallel n; 后记得使用 alter table/index table_name/index_name parallel 1; 修改默认值。 34. 由于是双机环境(VCS)，在备机上安装相应的os patch，然后将主机器上的11g ORACLE_HOME 直接tar包传过去解压即可，当然tar过去以后最好是relink all一下。 补充：这次实施过程较为波折，所以打算补充几句，从周5晚上开始，到今天早上9点才搞定， 其中遇到了如下几个问题，并对相关的注意事项进行说明： (1) 必须参考官方文档，对于提到的os patch包最好全部打上; (2) 安装oracle所推荐的patch如 10368698 10419629 针对类似安装升级不管是大版本升级还是 安装psu等等我们不仅要参考安装文档或reamde，而且更要关注Mos上的文档，对于提到的 相关bug，如果能下载安装，最好是升级之前打上； (3) 升级之后，可能会面临一些性能上的问题，建议参考如下文档： Things to Consider Before Upgrade to 11.2.0.2 in Relation to Database Performance [ID 1320966.1] 其中涉及到的几个patch如下： E:\software\11.2.0.2-forHPIA64\Relation to Database Performance-patch>dir *.zip 驱动器 E 中的卷是 work 卷的序列号是 B36D-13B7 E:\software\11.2.0.2-forHPIA64\Relation to Database Performance-patch 的目录 2011/11/19 14：18 177,067 p10149223_112020_HPUX-IA64.zip 2011/11/19 14：11 248,243 p10259620_112020_HPUX-IA64.zip 2011/11/19 14：14 207,794 p10269193_112020_HPUX-IA64.zip 2011/11/19 14：17 869,533 p10623249_112022_HPUX-IA64.zip 2011/11/19 14：13 189,808 p11719151_112020_HPUX-IA64.zip 2011/11/19 14：18 186,073 p12363485_112020_HPUX-IA64.zip 2011/11/19 14：12 196,735 p13004894_112020_HPUX-IA64.zip 2011/11/19 14：13 20,917 p9842771_112020_Generic.zip 8 个文件 2,096,170 字节 0 个目录 48,185,565,184 可用字节 该文档中提到了几个性能相关的bug，看来11.2.0.2问题也真的不少啊。 (4) 关于oracle TDE，不多说了，以前写过几篇相关的文章，另外大家也可以参考如下MOS文档： Quick and Dirty TDE Setup and FAQ [ID 1251597.1] |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
<div id="X" style="height:670px; width:auto;OVERFLOW-y:auto;border:blue 1px solid;margin:10px"> +++++++++ 怪事趣谈 +++++++++ 怪事1： 不过在升级过程中我还是遇到问题了，第一天晚上在运行cataupgrd.sql时，发现奇慢无比，其中通过看alert日志， 发现在更新xdb时非常慢，花了差不多2小时35分钟，通过对比上个月前的测试，实际上上次测试该步骤就6分钟左右而已。 虽然该步是进行过去了，但是在后面更新ORDIM组件时，运行到如下脚本时，session挂住了： INSERT INTO MDSYS.SDO_CS_SRS ( CS_NAME, SRID, AUTH_SRID, AUTH_NAME, WKTEXT, WKTEXT3D, CS_BOUNDS ) ( SELECT COORD_REF_SYS_NAME "CS_NAME", SRID "SRID", SRID "AUTH_SRID", INFORMATION_SOURCE "AUTH_NAME", NVL( LEGACY_WKTEXT, MDSYS.sdo_cs.internal_det_srid_wkt(srid)) "WKTEXT", sdo_cs.get_3d_wkt(srid) "WKTEXT3D", LEGACY_CS_BOUNDS "CS_BOUNDS" FROM MDSYS.SDO_COORD_REF_SYS) 通过查看10月份弄的测试库，发现SDO_COORD_REF_SYS数据量非常小，把该sql拿到测试库上运行不到30s就完成了。 第一天晚上就卡在这里，经过1个小时之后，凌晨4点，觉得进行回退，以免影响应用。 昨晚继续进行升级操作，最开始怀疑或许pfile中内存相关参数设置太小了，跑之前我sga和pga分别调整到8G,3G； 另外通过查询mos发现运行cataupgrd.sql在xdb组件更新慢的情况很可能是bug 10368698, 于是运行脚本之前我也 将该patch打上，不过最后发现仍然效果一样。 通过查看alert发现仍然花了2.5小时左右，而且仍然运行到上面的insert语句时，停止不动了，过了30分钟，客户 建议取消，放弃该方案。 迫于无奈之下，通过top可以看到目前基本上消耗集中在一个cpu上，而且消耗了99%，但是不是user，而且sys消耗， 这有些怪异。 突发奇想，想看看这个session进程目前是什么情况，于是使用oradebug对该进行操作了下，过了不到10s，居然奇迹 出现insert执行ok了，然后随后差不多30分钟完成了cataupgrd.sql脚本的运行，检查日志也没有发现任何错误。 </div> <div id="Z" style="height:200px; width:auto;OVERFLOW-y:auto;border:blue 1px solid;margin:10px"> 怪事2： 做完升级以及TDE加密，应用测试也ok以后，将11gR2 oracle home进行tar包传到备机，安装相关的os patch以后， 进行tar xvf解压，然后relink all，发现relink.log有错误，运行sqlplus报错。 看错误是找不到一些lib文件，进行对比，发现tar的包少文件，接着重新tar了一次，传到备机上解压后分布对比 主和备上的product目录(oracle home), 发现居然文件总数不一样，怪了。而且更奇怪的是，我第一次的tar包， 由于原oracle base目录空间不足，只能在临时目录解压然后mv(cp)过去，发现mv（cp）过去以后的product和以前 的product文件总数也不相同。 最后无奈之下，xxx公司的人用NBU直接备份主机上的ORACLE_HOME到备机上，然后就ok了。 不得不说，真是一个神奇的国度。 </div> |
3 Responses to “Step by Step oracle database 10gR2 upgrade to 11.2.0.2”
先顶,再收藏
步骤很详细，一线一手的文档就是详细。
Informative suggestions . For my two cents , if your company has been searching for a DD 2208 , my business filled out a sample version here http://goo.gl/pF7wJ9
Leave a Reply
You must be logged in to post a comment.