手工迁移数据库Oracle 11.2.0.4到新机器并升级到12.2.0.1版本
👉 本文共约1524个字，系统预计阅读时间或需6分钟。
前言
在最近的项目里，客户有2套11.2.0.4的数据库，需要迁移上云，数据量较大（一个600g，一个2T），停机时间6小时以内，还有很多其它的库需要同时切割，目前想到的迁移方案有：
1、XTTS进行全量+增量迁移。
缺点：物理迁移，在最后一次增量的时候，源端数据库需要设置为只读模式，只能读，不能写。云端数据库在最后一次增量之前都不能使用。
由于客户需要提前做POC测试，而且网络不是直达的，需要经过中转机，所以，该方案放弃。
2、使用数据泵+OGG方式。
缺点：逻辑迁移，对于特殊的列不能同步（例如long列），需要正式切割前进行大量的实验观察，否则可能造成个别数据不一致。
这个方案，我想着使用OGG的微服务会比较好，结果安装了好几个版本的OGG微服务（从12.3到21.3），兼容性都不支持11.2.0.4，无奈只能放弃！
3、进行全量+增量的rman备份恢复，最后做数据字典升级。可以使用手动脚本升级，也可以使用DBUA升级，也可以使用最新的AutoUpgrade工具升级。
缺点：数据字典升级时间稍微长点，预估1~2小时左右，但可控在切割时间内。
优点：方案成熟，变数少，可以做压缩备份，减少中间网络传输的时间。
本文我们使用方案3来做演练。
环境介绍
1 2 3 4 5 6 7 8 9 10 | docker rm -f lhrora11204 docker run -itd --name lhrora11204 -h lhrora11204 \ --privileged=true \ lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init docker rm -f lhrora1221 docker run -itd --name lhrora1221 -h lhrora1221 \ --privileged=true \ lhrbest/oracle_12cr2_ee_lhr_12.2.0.1:2.0 init |
参考文档：
手动升级到 Non-CDB Oracle Database 12c Release 2（12.2）的完整核对清单 (Doc ID 2297983.1、2173141.1)
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！使用 DBUA 升级数据库到 Database 12c 版本2（12.2）的完整核对清单 (Doc ID 2364820.1、2189854.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 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 | PURGE DBA_RECYCLEBIN; EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; scp /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/emremove.sql oracle@172.17.0.66:/tmp/ scp /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/preupgrade.jar oracle@172.17.0.66:/tmp/ -- 删除不需要的组件，减少最后升级数据字典的时间 -- 删除OLAP Catalog(OLAP AMD)，12c不再支持 @?/olap/admin/catnoamd.sql -- 删除OEM，需要从12c拷贝：$12c_ORACLE_HOME/rdbms/admin @?/rdbms/admin/emremove.sql -- 删除APEX @?/apex/apxremov.sql -- 执行下面的查询来检查是否有和SDO_GEOMETRY关联的表，如果有返回行数，那么在升级前需要往12.2的ORACLE HOME上打补丁 patch 25293022，或者直接删除相关表 col owner format a15 col table_name format a30 col column_name format a30 SELECT owner,table_name,column_name FROM dba_tab_columns WHERE data_type = 'SDO_GEOMETRY' AND owner != 'MDSYS' ORDER BY 1,2,3; col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 col status for a15 set lines 180 set pages 999 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; -- 预检并修复 $ORACLE_HOME/jdk/bin/java -jar /tmp/preupgrade.jar FILE TEXT @/u01/app/oracle/cfgtoollogs/LHR11G/preupgrade/preupgrade_fixups.sql [oracle@lhrora11204 ~]$ $ORACLE_HOME/jdk/bin/java -jar /tmp/preupgrade.jar FILE TEXT Preupgrade generated files: /u01/app/oracle/cfgtoollogs/LHR11G/preupgrade/preupgrade.log /u01/app/oracle/cfgtoollogs/LHR11G/preupgrade/preupgrade_fixups.sql /u01/app/oracle/cfgtoollogs/LHR11G/preupgrade/postupgrade_fixups.sql col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 col status for a15 set lines 180 set pages 999 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; COMP_ID COMP_NAME VERSION STATUS ---------- ---------------------------------------- --------------- --------------- OWB OWB 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 EXF Oracle Expression Filter 11.2.0.4.0 VALID RUL Oracle Rules 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 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 15 rows selected. -- 失效对象 select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type; select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ; -- 编译失效对象 @$ORACLE_HOME/rdbms/admin/utlrp.sql -- 开启bct alter database enable block change tracking using file '/home/oracle/bct.log'; select * from v$block_change_tracking; |
源端全备
1 2 3 4 5 6 7 8 | run { backup INCREMENTAL LEVEL 0 as compressed backupset database format '/home/oracle/bk/%d%U.full'; sql 'alter system archive log current'; backup archivelog all format '/home/oracle/bk/%d%U.arc'; backup current controlfile format '/home/oracle/bk/%d%U.ctl'; } |
拷贝全备文件+密码文件+tnsnames.ora+sqlnet.ora到目标端
1 2 3 4 | scp /home/oracle/bk/* oracle@172.17.0.67:/home/oracle/bk/ scp $ORACLE_HOME/network/admin/* oracle@172.17.0.67:/home/oracle/bk/ scp $ORACLE_HOME/dbs/* oracle@172.17.0.67:/home/oracle/bk/ scp /u01/app/oracle/cfgtoollogs/LHR11G/preupgrade/* oracle@172.17.0.67:/home/oracle/bk/ |
目标端恢复全备
1 2 3 4 5 6 7 8 9 10 | mkdir -p /u01/app/oracle/admin/LHR11G/adump export ORACLE_SID=LHR11G cp /home/oracle/bk/spfileLHR11G.ora $ORACLE_HOME/dbs/spfileLHR11G.ora startup force nomount restore controlfile from '/home/oracle/bk/LHR11G0a0g2qca_1_1.ctl'; alter database mount; restore database; recover database; |
源端增量备份
1 2 3 4 5 6 7 8 9 10 11 12 | create table lhr.test1206 as select * from dba_objects; run { backup INCREMENTAL LEVEL 1 as compressed backupset database format '/home/oracle/bk/%d%U_inc.full'; sql 'alter system archive log current'; backup archivelog all format '/home/oracle/bk/%d%U_inc.arc'; backup current controlfile format '/home/oracle/bk/%d%U_inc.ctl'; } scp /home/oracle/bk/* oracle@172.17.0.67:/home/oracle/bk/ |
目标端恢复增量备份
1 2 3 4 | catalog start with '/home/oracle/bk/'; recover database; |
目标端升级数据字典
准备工作
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | -- 如果是rac ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE; alter database open RESETLOGS; startup upgrade select * from v$logfile; alter database add logfile '/u01/app/oracle/oradata/LHR11G/redo04.log' size 50m; alter database add logfile '/u01/app/oracle/oradata/LHR11G/redo05.log' size 50m; alter database add logfile '/u01/app/oracle/oradata/LHR11G/redo06.log' size 50m; alter database add logfile '/u01/app/oracle/oradata/LHR11G/redo07.log' size 50m; alter database add logfile '/u01/app/oracle/oradata/LHR11G/redo08.log' size 50m; |
开始升级
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | -- 升级（核心） mkdir -p /tmp/dbupgrade_log nohup dbupgrade -n 16 -d $ORACLE_HOME/rdbms/admin -l /tmp/dbupgrade_log & 注意： 1、若升级失败，则可以先解决相关错误后，再重新跑该脚本。 2、升级过程中所有的日志文件位置：/tmp/dbupgrade_log 3、数据库告警日志：/u01/app/oracle/diag/rdbms/ora11g/ora11g/trace -- 执行Post-Upgrade Status工具，utlu122s.sql并且检查升级的日志。在新的版本下执行Post-Upgrade Status工具。这个脚本被放置在$ORACLE_HOME/rdbms/admin目录。 startup @?/rdbms/admin/utlu122s.sql -- 检查升级日志看是否脚本catuppst.sql已被执行。如果尚未执行，那么在新的ORACLE_HOME里手工执行。这个脚本被放置在$ORACLE_HOME/rdbms/admin目录。 @?/rdbms/admin/catuppst.sql -- 升级Timezone(文档 ID 1585343.1)，3分钟搞定 unzip DBMS_DST_scriptsV1.9.zip cd DBMS_DST_scriptsV1.9 @upg_tzv_check.sql @upg_tzv_apply.sql SELECT version FROM v$timezone_file; |
升级过程：
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 377 378 379 380 381 382 383 384 385 386 | [oracle@lhrora1221 ~]$ more nohup.out Argument list for [/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/catctl.pl] Run in c = 0 Do not run in C = 0 Input Directory d = /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin Echo OFF e = 1 Simulate E = 0 Forced cleanup F = 0 Log Id i = 0 Child Process I = 0 Log Dir l = /tmp/dbupgrade_log Priority List Name L = 0 Upgrade Mode active M = 0 SQL Process Count n = 16 SQL PDB Process Count N = 0 Open Mode Normal o = 0 Start Phase p = 0 End Phase P = 0 Reverse Order r = 0 AutoUpgrade Resume R = 0 Script s = 0 Serial Run S = 0 RO User Tablespaces T = 0 Display Phases y = 0 Debug catcon.pm z = 0 Debug catctl.pl Z = 0 catctl.pl VERSION: [12.2.0.1.0] STATUS: [production] BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125] /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0.1/dbhome_1] /u01/app/oracle/product/12.2.0.1/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/12.2.0.1/dbhome_1] catctlGetOrabase = [/u01/app/oracle/product/12.2.0.1/dbhome_1] Analyzing file /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/catupgrd.sql Log file directory = [/tmp/dbupgrade_log] catcon: ALL catcon-related output will be written to [/tmp/dbupgrade_log/catupgrd_catcon_3682.lst] catcon: See [/tmp/dbupgrade_log/catupgrd*.log] files for output generated by scripts catcon: See [/tmp/dbupgrade_log/catupgrd_*.lst] files for spool files, if any Number of Cpus = 16 Database Name = LHR11G DataBase Version = 11.2.0.4.0 Parallel SQL Process Count = 8 Components in [LHR11G] Installed [APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ] Not Installed [APEX DV EM MGW ODM OLS RAC WK] ------------------------------------------------------ Phases [0-115] Start Time:[2021_12_06 20:03:02] ------------------------------------------------------ *********** Executing Change Scripts *********** Serial Phase #:0 [LHR11G] Files:1 Time: 237s *************** Catalog Core SQL *************** Serial Phase #:1 [LHR11G] Files:5 Time: 165s Restart Phase #:2 [LHR11G] Files:1 Time: 0s *********** Catalog Tables and Views *********** Parallel Phase #:3 [LHR11G] Files:19 Time: 51s Restart Phase #:4 [LHR11G] Files:1 Time: 0s ************* Catalog Final Scripts ************ Serial Phase #:5 [LHR11G] Files:6 Time: 58s ***************** Catproc Start **************** Serial Phase #:6 [LHR11G] Files:1 Time: 33s ***************** Catproc Types **************** Serial Phase #:7 [LHR11G] Files:2 Time: 31s Restart Phase #:8 [LHR11G] Files:1 Time: 0s **************** Catproc Tables **************** Parallel Phase #:9 [LHR11G] Files:69 Time: 52s Restart Phase #:10 [LHR11G] Files:1 Time: 1s ************* Catproc Package Specs ************ Serial Phase #:11 [LHR11G] Files:1 Time: 117s Restart Phase #:12 [LHR11G] Files:1 Time: 0s ************** Catproc Procedures ************** Parallel Phase #:13 [LHR11G] Files:97 Time: 9s Restart Phase #:14 [LHR11G] Files:1 Time: 1s Parallel Phase #:15 [LHR11G] Files:118 Time: 29s Restart Phase #:16 [LHR11G] Files:1 Time: 0s Serial Phase #:17 [LHR11G] Files:13 Time: 6s Restart Phase #:18 [LHR11G] Files:1 Time: 0s ***************** Catproc Views **************** Parallel Phase #:19 [LHR11G] Files:33 Time: 48s Restart Phase #:20 [LHR11G] Files:1 Time: 0s Serial Phase #:21 [LHR11G] Files:3 Time: 19s Restart Phase #:22 [LHR11G] Files:1 Time: 0s Parallel Phase #:23 [LHR11G] Files:24 Time: 146s Restart Phase #:24 [LHR11G] Files:1 Time: 0s Parallel Phase #:25 [LHR11G] Files:11 Time: 73s Restart Phase #:26 [LHR11G] Files:1 Time: 0s Serial Phase #:27 [LHR11G] Files:1 Time: 0s Serial Phase #:28 [LHR11G] Files:3 Time: 8s Serial Phase #:29 [LHR11G] Files:1 Time: 0s Restart Phase #:30 [LHR11G] Files:1 Time: 0s *************** Catproc CDB Views ************** Serial Phase #:31 [LHR11G] Files:1 Time: 1s Restart Phase #:32 [LHR11G] Files:1 Time: 0s Serial Phase #:34 [LHR11G] Files:1 Time: 0s ***************** Catproc PLBs ***************** Serial Phase #:35 [LHR11G] Files:283 Time: 43s Serial Phase #:36 [LHR11G] Files:1 Time: 0s Restart Phase #:37 [LHR11G] Files:1 Time: 1s Serial Phase #:38 [LHR11G] Files:1 Time: 10s Restart Phase #:39 [LHR11G] Files:1 Time: 0s *************** Catproc DataPump *************** Serial Phase #:40 [LHR11G] Files:3 Time: 102s Restart Phase #:41 [LHR11G] Files:1 Time: 0s ****************** Catproc SQL ***************** Parallel Phase #:42 [LHR11G] Files:13 Time: 82s Restart Phase #:43 [LHR11G] Files:1 Time: 0s Parallel Phase #:44 [LHR11G] Files:12 Time: 12s Restart Phase #:45 [LHR11G] Files:1 Time: 0s Parallel Phase #:46 [LHR11G] Files:2 Time: 1s Restart Phase #:47 [LHR11G] Files:1 Time: 1s ************* Final Catproc scripts ************ Serial Phase #:48 [LHR11G] Files:1 Time: 15s Restart Phase #:49 [LHR11G] Files:1 Time: 0s ************** Final RDBMS scripts ************* Serial Phase #:50 [LHR11G] Files:1 Time: 45s ************ Upgrade Component Start *********** Serial Phase #:51 [LHR11G] Files:1 Time: 1s Restart Phase #:52 [LHR11G] Files:1 Time: 0s **************** Upgrading Java **************** Serial Phase #:53 [LHR11G] Files:1 Time: 387s Restart Phase #:54 [LHR11G] Files:1 Time: 0s ***************** Upgrading XDK **************** Serial Phase #:55 [LHR11G] Files:1 Time: 63s Restart Phase #:56 [LHR11G] Files:1 Time: 0s ********* Upgrading APS,OLS,DV,CONTEXT ********* Serial Phase #:57 [LHR11G] Files:1 Time: 142s ***************** Upgrading XDB **************** Restart Phase #:58 [LHR11G] Files:1 Time: 1s Serial Phase #:60 [LHR11G] Files:3 Time: 33s Serial Phase #:61 [LHR11G] Files:3 Time: 12s Parallel Phase #:62 [LHR11G] Files:9 Time: 6s Parallel Phase #:63 [LHR11G] Files:24 Time: 4s Serial Phase #:64 [LHR11G] Files:4 Time: 17s Serial Phase #:65 [LHR11G] Files:1 Time: 0s Serial Phase #:66 [LHR11G] Files:30 Time: 5s Serial Phase #:67 [LHR11G] Files:1 Time: 0s Parallel Phase #:68 [LHR11G] Files:6 Time: 3s Serial Phase #:69 [LHR11G] Files:2 Time: 29s Serial Phase #:70 [LHR11G] Files:3 Time: 108s Restart Phase #:71 [LHR11G] Files:1 Time: 0s ********* Upgrading CATJAVA,OWM,MGW,RAC ******** Serial Phase #:72 [LHR11G] Files:1 Time: 113s **************** Upgrading ORDIM *************** Restart Phase #:73 [LHR11G] Files:1 Time: 0s Serial Phase #:75 [LHR11G] Files:1 Time: 1s Parallel Phase #:76 [LHR11G] Files:2 Time: 63s Serial Phase #:77 [LHR11G] Files:1 Time: 74s Restart Phase #:78 [LHR11G] Files:1 Time: 1s Parallel Phase #:79 [LHR11G] Files:2 Time: 22s Serial Phase #:80 [LHR11G] Files:2 Time: 2s ***************** Upgrading SDO **************** Restart Phase #:81 [LHR11G] Files:1 Time: 0s Serial Phase #:83 [LHR11G] Files:1 Time: 45s Serial Phase #:84 [LHR11G] Files:1 Time: 2s Restart Phase #:85 [LHR11G] Files:1 Time: 1s Serial Phase #:86 [LHR11G] Files:1 Time: 39s Restart Phase #:87 [LHR11G] Files:1 Time: 1s Parallel Phase #:88 [LHR11G] Files:3 Time: 254s Restart Phase #:89 [LHR11G] Files:1 Time: 0s Serial Phase #:90 [LHR11G] Files:1 Time: 7s Restart Phase #:91 [LHR11G] Files:1 Time: 1s Serial Phase #:92 [LHR11G] Files:1 Time: 3s Restart Phase #:93 [LHR11G] Files:1 Time: 0s Parallel Phase #:94 [LHR11G] Files:4 Time: 78s Restart Phase #:95 [LHR11G] Files:1 Time: 1s Serial Phase #:96 [LHR11G] Files:1 Time: 0s Restart Phase #:97 [LHR11G] Files:1 Time: 1s Serial Phase #:98 [LHR11G] Files:2 Time: 59s Restart Phase #:99 [LHR11G] Files:1 Time: 0s Serial Phase #:100 [LHR11G] Files:1 Time: 1s Restart Phase #:101 [LHR11G] Files:1 Time: 0s *********** Upgrading Misc. ODM, OLAP ********** Serial Phase #:102 [LHR11G] Files:1 Time: 35s **************** Upgrading APEX **************** Restart Phase #:103 [LHR11G] Files:1 Time: 0s Serial Phase #:104 [LHR11G] Files:1 Time: 1s Restart Phase #:105 [LHR11G] Files:1 Time: 0s *********** Final Component scripts *********** Serial Phase #:106 [LHR11G] Files:1 Time: 1s ************* Final Upgrade scripts ************ Serial Phase #:107 [LHR11G] Files:1 Time: 93s ********** End PDB Application Upgrade ********* Serial Phase #:108 [LHR11G] Files:1 Time: 1s ******************* Migration ****************** Serial Phase #:109 [LHR11G] Files:1 Time: 77s Serial Phase #:110 [LHR11G] Files:1 Time: 0s Serial Phase #:111 [LHR11G] Files:1 Time: 159s ***************** Post Upgrade ***************** Serial Phase #:112 [LHR11G] Files:1 Time: 23s **************** Summary report **************** Serial Phase #:113 [LHR11G] Files:1 Time: 1s Serial Phase #:114 [LHR11G] Files:1 Time: 0s Serial Phase #:115 [LHR11G] Files:1 Time: 34s ------------------------------------------------------ Phases [0-115] End Time:[2021_12_06 20:59:44] ------------------------------------------------------ Grand Total Time: 3406s LOG FILES: (/tmp/dbupgrade_log/catupgrd*.log) Upgrade Summary Report Located in: /tmp/dbupgrade_log/upg_summary.log Grand Total Upgrade Time: [0d:0h:56m:46s] [oracle@lhrora1221 ~]$ more /tmp/dbupgrade_log/upg_summary.log Oracle Database 12.2 Post-Upgrade Status Tool 12-06-2021 20:59:09 Component Current Version Elapsed Time Name Status Number HH:MM:SS Oracle Server UPGRADED 12.2.0.1.0 00:23:10 JServer JAVA Virtual Machine UPGRADED 12.2.0.1.0 00:06:25 Oracle Workspace Manager UPGRADED 12.2.0.1.0 00:01:36 OLAP Analytic Workspace UPGRADED 12.2.0.1.0 00:00:58 Oracle OLAP API UPGRADED 12.2.0.1.0 00:00:18 Oracle XDK UPGRADED 12.2.0.1.0 00:01:02 Oracle Text UPGRADED 12.2.0.1.0 00:01:22 Oracle XML Database UPGRADED 12.2.0.1.0 00:03:35 Oracle Database Java Packages UPGRADED 12.2.0.1.0 00:00:15 Oracle Multimedia UPGRADED 12.2.0.1.0 00:02:41 Spatial UPGRADED 12.2.0.1.0 00:08:11 Final Actions 00:02:52 Post Upgrade 00:00:21 Total Upgrade Time: 00:53:20 Database time zone version is 14. It is older than current release time zone version 26. Time zone upgrade is needed using the DBMS_DST package. Grand Total Upgrade Time: [0d:0h:56m:46s] [oracle@lhrora1221 ~]$ ORACLE_SID=LHR11G [oracle@lhrora1221 ~]$ sas SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 7 08:32:27 2021 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SYS@LHR11G> startup ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size 8622776 bytes Variable Size 939527496 bytes Database Buffers 1191182336 bytes Redo Buffers 8151040 bytes Database mounted. Database opened. SYS@LHR11G> @?/rdbms/admin/utlu122s.sql Oracle Database 12.2 Post-Upgrade Status Tool 12-07-2021 08:33:26 Component Current Version Elapsed Time Name Status Number HH:MM:SS Oracle Server UPGRADED 12.2.0.1.0 00:23:10 JServer JAVA Virtual Machine UPGRADED 12.2.0.1.0 00:06:25 Oracle Workspace Manager UPGRADED 12.2.0.1.0 00:01:36 OLAP Analytic Workspace UPGRADED 12.2.0.1.0 00:00:58 Oracle OLAP API UPGRADED 12.2.0.1.0 00:00:18 Oracle XDK UPGRADED 12.2.0.1.0 00:01:02 Oracle Text UPGRADED 12.2.0.1.0 00:01:22 Oracle XML Database UPGRADED 12.2.0.1.0 00:03:35 Oracle Database Java Packages UPGRADED 12.2.0.1.0 00:00:15 Oracle Multimedia UPGRADED 12.2.0.1.0 00:02:41 Spatial UPGRADED 12.2.0.1.0 00:08:11 Final Actions 00:02:52 Post Upgrade 00:00:21 Total Upgrade Time: 00:53:20 Database time zone version is 14. It is older than current release time zone version 26. Time zone upgrade is needed using the DBMS_DST package. Summary Report File = /tmp/dbupgrade_log/upg_summary.log [oracle@lhrora1221 ~]$ cd DBMS_DST_scriptsV1.9 [oracle@lhrora1221 DBMS_DST_scriptsV1.9]$ sas SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 8 16:47:44 2021 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SYS@LHR11G> SYS@LHR11G> @upg_tzv_check.sql INFO: Starting with RDBMS DST update preparation. INFO: NO actual RDBMS DST update will be done by this script. INFO: If an ERROR occurs the script will EXIT sqlplus. INFO: Doing checks for known issues ... INFO: Database version is 12.2.0.1 . INFO: Database RDBMS DST version is DSTv14 . INFO: No known issues detected. INFO: Now detecting new RDBMS DST version. A prepare window has been successfully started. INFO: Newest RDBMS DST version detected is DSTv26 . INFO: Next step is checking all TSTZ data. INFO: It might take a while before any further output is seen ... A prepare window has been successfully ended. INFO: A newer RDBMS DST version than the one currently used is found. INFO: Note that NO DST update was yet done. INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update. INFO: Note that the upg_tzv_apply.sql script will INFO: restart the database 2 times WITHOUT any confirmation or prompt. SYS@LHR11G> SYS@LHR11G> @upg_tzv_apply.sql INFO: If an ERROR occurs the script will EXIT sqlplus. INFO: The database RDBMS DST version will be updated to DSTv26 . WARNING: This script will restart the database 2 times WARNING: WITHOUT asking ANY confirmation. WARNING: Hit control-c NOW if this is not intended. INFO: Restarting the database in UPGRADE mode to start the DST upgrade. Database closed. Database dismounted. ORACLE instance shut down. ORACLE instance started. Total System Global Area 2332033024 bytes Fixed Size 8623304 bytes Variable Size 771754808 bytes Database Buffers 1543503872 bytes Redo Buffers 8151040 bytes Database mounted. Database opened. INFO: Starting the RDBMS DST upgrade. INFO: Upgrading all SYS owned TSTZ data. INFO: It might take time before any further output is seen ... An upgrade window has been successfully started. INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data. Database closed. Database dismounted. ORACLE instance shut down. ORACLE instance started. Total System Global Area 2332033024 bytes Fixed Size 8623304 bytes Variable Size 771754808 bytes Database Buffers 1543503872 bytes Redo Buffers 8151040 bytes Database mounted. Database opened. INFO: Upgrading all non-SYS TSTZ data. INFO: It might take time before any further output is seen ... INFO: Do NOT start any application yet that uses TSTZ data! INFO: Next is a list of all upgraded tables: Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L" Number of failures: 0 Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S" Number of failures: 0 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L" Number of failures: 0 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S" Number of failures: 0 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L" Number of failures: 0 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S" Number of failures: 0 INFO: Total failures during update of TSTZ data: 0 . An upgrade window has been successfully ended. INFO: Your new Server RDBMS DST version is DSTv26 . INFO: The RDBMS DST update is successfully finished. INFO: Make sure to exit this sqlplus session. INFO: Do not use it for timezone related selects. SYS@LHR11G> SYS@LHR11G> SELECT version FROM v$timezone_file; VERSION ---------- 26 1 row selected. |
数据字典大概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 | -- 检查诊断升级/迁移相关的状态的Oracle数据字典。dbupgdiag.sql脚本可以收集和升级迁移诊断信息有关的数据字典的信息， -- 可以在升级的数据库上以SYS用户来执行它，关于更多信息， -- 请参考文档Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information(dbupgdiag.sql) SQL> alter session set nls_language='American'; SQL> @dbupgdiag.sql -- 编译失效对象 select count(1) from dba_objects where status='INVALID'; @$ORACLE_HOME/rdbms/admin/utlrp.sql -- 修复脚本 @postupgrade_fixups.sql select count(1) from dba_objects where status='INVALID'; select name,open_mode,version from v$database,v$instance; alter system set compatible='12.2.0' scope=spfile; alter system checkpoint; select * from v$log; alter database drop logfile group 7; col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 col status for a15 set lines 180 set pages 999 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; |
最后组件的状态：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SYS@LHR11G> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; COMP_ID COMP_NAME VERSION STATUS ---------- ---------------------------------------- --------------- --------------- CATALOG Oracle Database Catalog Views 12.2.0.1.0 VALID CATPROC Oracle Database Packages and Types 12.2.0.1.0 VALID JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 VALID XML Oracle XDK 12.2.0.1.0 VALID CATJAVA Oracle Database Java Packages 12.2.0.1.0 VALID APS OLAP Analytic Workspace 12.2.0.1.0 VALID OWM Oracle Workspace Manager 12.2.0.1.0 VALID CONTEXT Oracle Text 12.2.0.1.0 VALID XDB Oracle XML Database 12.2.0.1.0 VALID ORDIM Oracle Multimedia 12.2.0.1.0 VALID SDO Spatial 12.2.0.1.0 VALID XOQ Oracle OLAP API 12.2.0.1.0 VALID 12 rows selected. |
使用rman全备+增备+dbupgrade成功异机升级到12.2.0.1.
总结
1、如果在原Oracle 11g数据库系统中，有使用WM_CONCAT函数，那么在升级到12c后会报错：ORA-00904: "WM_CONCAT": invalid identifier，解决办法请参考：https://www.xmmup.com/ora-00904-wm_concat-invalid-identifiercuowujiejue.html
2、只有在编译失效对象完成后，dba_registry中的statu列才会变为VALID。若编译过程很慢，那您得找一下原因了，找找是否有大量存储过程，而存储过程中含有dblink连接等问题，等这些问题修复后，再编译起来就很快了。编译脚本：
1 2 3 4 5 6 | -- 编译失效对象 select count(1) from dba_objects where status='INVALID'; @$ORACLE_HOME/rdbms/admin/utlrp.sql SELECT 'ALTER '|| d.object_type|| ' ' || d.owner ||'.'|| d.object_name ||' COMPILE;' FROM dba_objects d where d.status='INVALID' ; |
文中所说的数据泵+ogg方式其实是可行的，不能使用微服务，但是可以使用传统模式，请参考 https://www.xmmup.com/shiyongogg-21-3yuanchengshishihuxiangtongbuoracle-11-2-0-4shuangzhu.html
这个升级，目标机器12C 中没有PDB，只有CDB吧？
是的，是非cdb的库，和11架构一样