前言
在最近的项目里，客户有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来做演练，升级方式选择AutoUpgrade工具。
手动脚本升级可以参考：https://www.xmmup.com/shougongqianyishujukuoracle-11-2-0-4daoxinjiqibingshengjidao12-2-0-1banben.html
环境介绍
1docker rm -f lhrora11204
2docker run -itd --name lhrora11204 -h lhrora11204 \
3 --privileged=true \
4 lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init
5
6
7docker rm -f lhrora1221
8docker run -itd --name lhrora1221 -h lhrora1221 \
9 --privileged=true \
10 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)
使用 DBUA 升级数据库到 Database 12c 版本2（12.2）的完整核对清单 (Doc ID 2364820.1、2189854.1)
手工迁移数据库Oracle 11.2.0.4到新机器并升级到12.2.0.1版本
Oracle数据库一键升级工具AutoUpgrade介绍（Doc ID 2485457.1、1577660.1）
Oracle Database AutoUpgrade allows DBAs to upgrade one or many databases without human intervention, all with one command and a single configuration file.
AutoUpgrade enables customers to upgrade one or many Oracle databases at the command-line with a single command and a single configuration file. AutoUpgrade will run the preupgrade tasks, perform automated fixups where needed, execute the database upgrade, and finish by taking care of post-upgrade tasks. It includes automatic retry and fallback, the possibility to schedule upgrades for future points in time, and the ability to set, change or remove initialization parameters as desired. It saves time and money by upgrading hundreds of databases with one command and replacing bespoke high maintenance upgrade solutions.
源端准备
1-- 1、升级JDK到1.8
2yum install -y java-1.8.0-openjdk*
3
4
5
6-- 2、创建并编辑config文件
7java -jar /home/oracle/autoupgrade.jar -create_sample_file config
8
9
10##参照生成的config文件，编写config
11cat >/tmp/config122.cfg <<"EOF"
12# Global configurations
13global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade
14
15
16# Database number 1 - Full DB/CDB upgrade
17upg1.log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade/employee # Path of the log directory for the upgrade job
18upg1.sid=LHR11G # ORACLE_SID of the source DB/CDB
19upg1.source_home=/u01/app/oracle/product/11.2.0.4/dbhome_1 # Path of the source ORACLE_HOME
20upg1.target_home=/u01/app/oracle/product/12.2.0.1/dbhome_1 # Path of the target ORACLE_HOME
21upg1.start_time=NOW # Optional. [NOW | +XhYm (X hours, Y minutes after launch) | dd/mm/yyyy hh:mm:ss]
22upg1.upgrade_node=lhrora11204 # Optional. To find out the name of your node, run the hostname utility. Default is 'localhost'
23upg1.run_utlrp=yes # Optional. Whether or not to run utlrp after upgrade
24upg1.timezone_upg=yes # Optional. Whether or not to run the timezone upgrade
25upg1.target_version=12.2 # Oracle version of the target ORACLE_HOME. Only required when the target Oracle database version is 12.2
26
27EOF
28
29
30-- 3、升级前源库进行分析检查，Notes：可以通过 lsj 命令查看当前JOB的运行情况。
31java -jar /home/oracle/autoupgrade.jar -config /tmp/config122.cfg -mode analyze
32
33
34-- 4、升级前源库执行修复脚本，Notes：可以通过 status -job 101 命令查看当前JOB的运行情况。
35-- 这个操作会替代之前很多的手工操作，例如删除EM，OLAP AMD，情况回收站等！！
36java -jar /home/oracle/autoupgrade.jar -config /tmp/config122.cfg -mode fixups
37
38
39-- 源端删除APEX
40@?/apex/apxremov.sql
41
42-- 检查是否有和SDO_GEOMETRY关联的表
43col owner format a15
44col table_name format a30
45col column_name format a30
46SELECT owner,table_name,column_name FROM dba_tab_columns WHERE data_type = 'SDO_GEOMETRY' AND owner != 'MDSYS' ORDER BY 1,2,3;
47
48-- RECOVERY_AREA 配置大点
49alter system set db_recovery_file_dest_size=10g;
50
51-- 组件状态
52col COMP_ID for a10
53col COMP_NAME for a40
54col VERSION for a15
55col status for a15
56set lines 180
57set pages 999
58select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;复制
执行过程：
1[oracle@lhrora11204 ~]$ java -jar /home/oracle/autoupgrade.jar -config /tmp/config122.cfg -mode analyze
2AutoUpgrade is not fully tested on OpenJDK 64-Bit Server VM, Oracle recommends to use Java HotSpot(TM)
3AutoUpgrade 21.3.211115 launched with default options
4Processing config file ...
5+--------------------------------+
6| Starting AutoUpgrade execution |
7+--------------------------------+
81 databases will be analyzed
9Type 'help' to list console commands
10upg> help
11exit // To close and exit
12help // Displays help
13lsj [(-r|-f|-p|-e) | -n <number>] // list jobs by status up to n elements.
14 -f Filter by finished jobs.
15 -r Filter by running jobs.
16 -e Filter by jobs with errors.
17 -p Filter by jobs being prepared.
18 -n <number> Display up to n jobs.
19lsr // Displays the restoration queue
20lsa // Displays the abort queue
21tasks // Displays the tasks running
22clear // Clears the terminal
23resume -job <number> // Restarts a previous job that was running
24status [-job <number> [-long]]// Lists all the jobs or a specific job
25restore -job <number> // Restores the database to its state prior to the upgrade
26restore all_failed // Restores all failed jobs to their previous states prior to the upgrade
27logs // Displays all the log locations
28abort -job <number> // Aborts the specified job
29h[ist] // Displays the command line history
30/[<number>] // Executes the command specified from the history. The default is the last command
31meta // Displays Internal latch count
32hwinfo // Displays additional information
33upg> Job 100 completed
34------------------- Final Summary --------------------
35Number of databases [ 1 ]
36
37Jobs finished [1]
38Jobs failed [0]
39Jobs pending [0]
40
41Please check the summary report at:
42/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
43/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
44
45[oracle@lhrora11204 ~]$ more /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
46==========================================
47 Autoupgrade Summary Report
48==========================================
49[Date] Tue Dec 07 11:21:25 CST 2021
50[Number of Jobs] 1
51==========================================
52[Job ID] 100
53==========================================
54[DB Name] LHR11G
55[Version Before Upgrade] 11.2.0.4.0
56[Version After Upgrade] 12.2
57------------------------------------------
58[Stage Name] PRECHECKS
59[Status] SUCCESS
60[Start Time] 2021-12-07 11:21:11
61[Duration] 0:00:14
62[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/prechecks
63[Detail] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/prechecks/lhr11g_preupgrade.log
64 Precheck passed and no manual intervention needed
65------------------------------------------
66
67
68[oracle@lhrora11204 ~]$ java -jar /home/oracle/autoupgrade.jar -config /tmp/config122.cfg -mode fixups
69AutoUpgrade is not fully tested on OpenJDK 64-Bit Server VM, Oracle recommends to use Java HotSpot(TM)
70AutoUpgrade 21.3.211115 launched with default options
71Processing config file ...
72+--------------------------------+
73| Starting AutoUpgrade execution |
74+--------------------------------+
751 databases will be processed
76Type 'help' to list console commands
77upg> status -job 101
78Progress
79-----------------------------------
80Start time: 21/12/07 11:26
81Elapsed (min): 0
82End time: N/A
83Last update: 2021-12-07T11:26:12.448
84Stage: PRECHECKS
85Operation: PREPARING
86Status: RUNNING
87Pending stages: 2
88Stage summary:
89 SETUP <1 min
90 PRECHECKS <1 min (IN PROGRESS)
91
92Job Logs Locations
93-----------------------------------
94Logs Base: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G
95Job logs: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/101
96Stage logs: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/101/prechecks
97TimeZone: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/temp
98
99Additional information
100-----------------------------------
101Details:
102Checks
103
104Error Details:
105None
106
107。。。。。
108
109upg> status -job 101
110Progress
111-----------------------------------
112Start time: 21/12/07 11:26
113Elapsed (min): 5
114End time: N/A
115Last update: 2021-12-07T11:31:08.339
116Stage: PREFIXUPS
117Operation: EXECUTING
118Status: RUNNING
119Pending stages: 1
120Stage summary:
121 SETUP <1 min
122 PRECHECKS <1 min
123 PREFIXUPS 4 min (IN PROGRESS)
124
125Job Logs Locations
126-----------------------------------
127Logs Base: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G
128Job logs: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/101
129Stage logs: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/101/prefixups
130TimeZone: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/temp
131
132Additional information
133-----------------------------------
134Details:
135+--------+-----------------+--------+
136|DATABASE| FIXUP| STATUS|
137+--------+-----------------+--------+
138| LHR11G| EXF_RUL_EXISTS|FINISHED|
139| LHR11G| AMD_EXISTS|FINISHED|
140| LHR11G| EM_PRESENT|FINISHED|
141| LHR11G|PRE_FIXED_OBJECTS| STARTED|
142+--------+-----------------+--------+
143
144Error Details:
145None
146
147upg> Job 101 completed
148------------------- Final Summary --------------------
149Number of databases [ 1 ]
150
151Jobs finished [1]
152Jobs failed [0]
153Jobs pending [0]
154
155Please check the summary report at:
156/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
157/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
158
159[oracle@lhrora11204 ~]$ more /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
160==========================================
161 Autoupgrade Summary Report
162==========================================
163[Date] Tue Dec 07 11:33:00 CST 2021
164[Number of Jobs] 1
165==========================================
166[Job ID] 101
167==========================================
168[DB Name] LHR11G
169[Version Before Upgrade] 11.2.0.4.0
170[Version After Upgrade] 12.2
171------------------------------------------
172[Stage Name] PRECHECKS
173[Status] SUCCESS
174[Start Time] 2021-12-07 11:26:12
175[Duration] 0:00:12
176[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/101/prechecks
177[Detail] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/101/prechecks/lhr11g_preupgrade.log
178 Precheck passed and no manual intervention needed
179------------------------------------------
180[Stage Name] PREFIXUPS
181[Status] SUCCESS
182[Start Time] 2021-12-07 11:26:25
183[Duration] 0:06:34
184[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/101/prefixups
185------------------------------------------复制
看来，源库不需要做任何操作。
源端全备
1-- 开启bct
2alter database enable block change tracking using file '/home/oracle/bct.log';
3select * from v$block_change_tracking;
4
5run
6{
7 backup INCREMENTAL LEVEL 0 as compressed backupset database format '/home/oracle/bk/%d%U.full';
8 sql 'alter system archive log current';
9 backup archivelog all format '/home/oracle/bk/%d%U.arc';
10 backup current controlfile format '/home/oracle/bk/%d%U.ctl';
11}
12复制
拷贝全备文件+密码文件+tnsnames.ora+sqlnet.ora到目标端
1scp /home/oracle/bk/* oracle@172.17.0.67:/home/oracle/bk/
2scp $ORACLE_HOME/network/admin/* oracle@172.17.0.67:/home/oracle/bk/
3scp $ORACLE_HOME/dbs/* oracle@172.17.0.67:/home/oracle/bk/复制
目标端恢复全备
1mkdir -p /u01/app/oracle/admin/LHR11G/adump
2export ORACLE_SID=LHR11G
3
4startup force nomount
5restore controlfile from '/home/oracle/bk/LHR11G0v0g6vm8_1_1.ctl';
6
7alter database mount;
8
9CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
10restore database;
11recover database;复制
注意，如果从这里重新做还原恢复操作，那么应该注意以下内容：
1、删除之前闪回恢复区中的所有文件，否则会自动注册闪回恢复区中的归档文件，会引起恢复错误！！！
源端增量备份
1create table lhr.test1207 as select * from dba_objects;
2
3run
4{
5 backup INCREMENTAL LEVEL 1 as compressed backupset database format '/home/oracle/bk/%d%U_inc.full';
6 sql 'alter system archive log current';
7 backup archivelog all format '/home/oracle/bk/%d%U_inc.arc';
8 backup current controlfile format '/home/oracle/bk/%d%U_inc.ctl';
9}
10
11
12scp /home/oracle/bk/* oracle@172.17.0.67:/home/oracle/bk/
13复制
目标端恢复增量备份
1catalog start with '/home/oracle/bk/';
2
3recover database;
4
5alter database open resetlogs;
6startup upgrade;复制
目标端PSU升级
如果不升级，会报错：
1[oracle@lhrora1221 employee]$ $ORACLE_HOME/jdk/bin/java -jar /home/oracle/autoupgrade.jar -config /tmp/config122.cfg -mode upgrade
2AutoUpgrade 21.3.211115 launched with default options
3Processing config file ...
4+--------------------------------+
5| Starting AutoUpgrade execution |
6+--------------------------------+
71 databases will be processed
8Type 'help' to list console commands
9upg>
10-------------------------------------------------
11Errors in database [LHR11G]
12Stage [DBUPGRADE]
13Operation [STOPPED]
14Status [ERROR]
15Info [
16Error: UPG-1411
17Invalid version /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/ of catctl.pl for AutoUpgrade; patch the target home
18Cause: Invalid version of catctl.pl and catctl.pm
19For further details, see the log file located at /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/autoupgrade_20211207_user.log]
20
21-------------------------------------------------
22Logs: [/u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/autoupgrade_20211207_user.log]
23-------------------------------------------------
24
25
26
272021-12-07 04:38:59.555 ERROR Invalid version /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/ of catctl.pl for AutoUpgrade; patch the target home
282021-12-07 04:38:59.567 ERROR Exception Error in Database Upgrade [LHR11G]
292021-12-07 04:38:59.621 ERROR lhr11g Return status is ERROR
302021-12-07 04:38:59.629 ERROR Dispatcher failed: AutoUpgException [UPG-1411#Invalid version /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/ of catctl.pl for AutoUpgrade; patch the target home]
31
32
332021-12-07 04:38:59.641 ERROR Dispatcher failed:
34Error: UPG-1411
35Invalid version /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/ of catctl.pl for AutoUpgrade; patch the target home
36Cause: Invalid version of catctl.pl and catctl.pm
37
38
39[oracle@lhrora1221 employee]$ $ORACLE_HOME/jdk/bin/java -jar /home/oracle/autoupgrade.jar -error_code UPG-1411
40ERROR1411.ERROR = UPG-1411
41ERROR1411.CAUSE = Invalid version of catctl.pl and catctl.pm
42复制
解决办法：打最新的PSU补丁。
最新的PSU请参考：https://www.xmmup.com/oracle-database-or-gi-update-revision-psu-spucpu-bundle-patches-patchsets-and-base-releases.html
1su - oracle
2unzip -d $ORACLE_HOME /soft/p6880880_210000_Linux-x86-64.zip
3
4cd /soft/
5unzip p33261817_122010_Linux-x86-64.zip
6cd 33261817
7$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
8
9shutdown immediate
10$ORACLE_HOME/OPatch/opatch apply
11
12chown oracle.oinstall /u01/app/oracle/product/12.2.0.1/dbhome_1/bin/extjobO
13
14$ORACLE_HOME/OPatch/opatch lspatches
15
16
17
18[oracle@lhrora1221 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
1933261817;Database Oct 2021 Release Update : 12.2.0.1.211019 (33261817)
20
21OPatch succeeded.复制
目标端升级(核心步骤)
参考：https://docs.oracle.com/en/database/oracle/oracle-database/21/upgrd/examples-of-autoupgrade-workflows.html#GUID-04742E36-180F-4BEA-B693-9F4ED6A2073D
1、执行升级命令
1cat >/tmp/config122.cfg <<"EOF"
2# Global configurations
3global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade
4global.catctl_options=-n 24
5
6# Database number 1 - Full DB/CDB upgrade
7upg1.log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade/job # Path of the log directory for the upgrade job
8upg1.sid=LHR11G # ORACLE_SID of the source DB/CDB
9upg1.source_home=/u01/app/oracle/product/11.2.0.4/dbhome_1 # Path of the source ORACLE_HOME
10upg1.target_home=/u01/app/oracle/product/12.2.0.1/dbhome_1 # Path of the target ORACLE_HOME
11upg1.start_time=NOW # Optional. [NOW | +XhYm (X hours, Y minutes after launch) | dd/mm/yyyy hh:mm:ss]
12upg1.upgrade_node=localhost # Optional. To find out the name of your node, run the hostname utility. Default is 'localhost'
13upg1.run_utlrp=yes # Optional. Whether or not to run utlrp after upgrade
14upg1.timezone_upg=yes # Optional. Whether or not to run the timezone upgrade
15upg1.target_version=12.2 # Oracle version of the target ORACLE_HOME. Only required when the target Oracle database version is 12.2
16upg1.catctl_options=-n 24
17EOF
18
19
20-- 升级的核心命令
21rm -rf /u01/app/oracle/cfgtoollogs/autoupgrade/
22$ORACLE_HOME/jdk/bin/java -jar /home/oracle/autoupgrade.jar -config /tmp/config122.cfg -mode upgrade
23
24[oracle@lhrora1221 ~]$ ps -ef|grep upgrade
25oracle 9447 6241 99 11:30 pts/12 00:01:23 /u01/app/oracle/product/12.2.0.1/dbhome_1/jdk/bin/java -jar /home/oracle/autoupgrade.jar -config /tmp/config122.cfg -mode upgrade
26oracle 9570 9447 1 11:31 pts/12 00:00:00 /u01/app/oracle/product/12.2.0.1/dbhome_1/perl/bin/perl /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/catctl.pl -A -l /u01/app/oracle/cfgtoollogs/autoupgrade/job/LHR11G/100/dbupgrade -i 20211208033059lhr11g -d /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin -n 8 catupgrd.sql
27oracle 9800 9743 0 11:31 pts/14 00:00:00 grep --color=auto upgrade
28-- 这里的-n依然为8，很奇怪
29
30
31-- （可选）增加在线Redo日志，最后升级完成后删除
32alter database add logfile '' size 50m;复制
Notes：执行升级过程中，可以通过 lsj 和 status -job 102 来检查升级情况。
1upg> lsj
2+----+-------+---------+---------+-------+--------------+--------+-----------+
3|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
4+----+-------+---------+---------+-------+--------------+--------+-----------+
5| 100| LHR11G|DBUPGRADE|EXECUTING|RUNNING|21/12/07 04:54|06:31:05|1%Upgraded |
6+----+-------+---------+---------+-------+--------------+--------+-----------+
7Total jobs 1
8
9upg> status -job 100
10Progress
11-----------------------------------
12Start time: 21/12/07 04:54
13Elapsed (min): 99
14End time: N/A
15Last update: 2021-12-07T06:31:05.708
16Stage: DBUPGRADE
17Operation: EXECUTING
18Status: RUNNING
19Pending stages: 7
20Stage summary:
21 SETUP <1 min
22 DBUPGRADE <1 min
23
24Job Logs Locations
25-----------------------------------
26Logs Base: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G
27Job logs: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100
28Stage logs: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/dbupgrade
29TimeZone: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/temp
30
31Additional information
32-----------------------------------
33Details:
34[Upgrading] is [1%] completed for [lhr11g]
35 +---------+------------+
36 |CONTAINER| PERCENTAGE|
37 +---------+------------+
38 | LHR11G|UPGRADE [1%]|
39 +---------+------------+
40
41Error Details:
42None复制
或者通过python来创建一个HTTPServer网页来监控升级情况：
1cd /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto
2nohup python -m SimpleHTTPServer 80 &复制
打开网页，输入：http://172.17.0.4:80/state.html，网页会自动刷新执行情况：
等待升级完成即可，时间大概1-2小时，跟数据库组件的个数、类型和是否含有特殊组件有关。
也可以查看告警日志、组件的状态来预估升级的进度，升级时间的长短和组件的个数相关！
1upg> lsj
2+----+-------+---------+---------+-------+--------------+--------+------------+
3|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
4+----+-------+---------+---------+-------+--------------+--------+------------+
5| 100| LHR11G|DBUPGRADE|EXECUTING|RUNNING|21/12/07 04:54|07:16:09|90%Upgraded |
6+----+-------+---------+---------+-------+--------------+--------+------------+
7Total jobs 1
8
9
10SYS@LHR11G> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
11
12COMP_ID COMP_NAME VERSION STATUS
13---------- ---------------------------------------- --------------- ---------------
14CATALOG Oracle Database Catalog Views 12.2.0.1.0 UPGRADED
15CATPROC Oracle Database Packages and Types 12.2.0.1.0 UPGRADED
16JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 UPGRADED
17XML Oracle XDK 12.2.0.1.0 UPGRADED
18CATJAVA Oracle Database Java Packages 12.2.0.1.0 UPGRADED
19APS OLAP Analytic Workspace 12.2.0.1.0 UPGRADED
20XOQ Oracle OLAP API 11.2.0.4.0 VALID
21OWM Oracle Workspace Manager 12.2.0.1.0 UPGRADED
22CONTEXT Oracle Text 12.2.0.1.0 UPGRADED
23XDB Oracle XML Database 12.2.0.1.0 UPGRADED
24ORDIM Oracle Multimedia 11.2.0.4.0 UPGRADING
25SDO Spatial 11.2.0.4.0 VALID
26APEX Oracle Application Express 3.2.1.00.12 VALID
27
2813 rows selected.复制
1upg> lsj
2+----+-------+----------+---------+-------+--------------+--------+-------------+
3|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
4+----+-------+----------+---------+-------+--------------+--------+-------------+
5| 100| LHR11G|POSTFIXUPS|EXECUTING|RUNNING|21/12/07 04:54|07:50:35|Remaining 3/4|
6+----+-------+----------+---------+-------+--------------+--------+-------------+
7Total jobs 1
8upg> Job 100 completed
9------------------- Final Summary --------------------
10Number of databases [ 1 ]
11
12Jobs finished [1]
13Jobs failed [0]
14Jobs pending [0]
15
16Please check the summary report at:
17/tmp/cfgtoollogs/upgrade/auto/status/status.html
18/tmp/cfgtoollogs/upgrade/auto/status/status.log
19
20
21[oracle@lhrora1221 ~]$ more /tmp/cfgtoollogs/upgrade/auto/status/status.log
22==========================================
23 Autoupgrade Summary Report
24==========================================
25[Date] Tue Dec 07 07:54:47 UTC 2021
26[Number of Jobs] 1
27==========================================
28[Job ID] 100
29==========================================
30[DB Name] LHR11G
31[Version Before Upgrade] 11.2.0.4.0
32[Version After Upgrade] 12.2
33------------------------------------------
34[Stage Name] DBUPGRADE
35[Status] FAILURE
36[Start Time] 2021-12-07 06:24:58
37[Duration] 1:22:34
38[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/dbupgrade
39------------------------------------------
40[Stage Name] POSTCHECKS
41[Status] SUCCESS
42[Start Time] 2021-12-07 07:47:33
43[Duration] 0:00:16
44[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/postchecks
45[Detail] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/postchecks/lhr11g_postupgrade.log
46------------------------------------------
47[Stage Name] POSTFIXUPS
48[Status] SUCCESS
49[Start Time] 2021-12-07 07:47:49
50[Duration] 0:06:57
51[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/postfixups
52------------------------------------------
53[Stage Name] SYSUPDATES
54[Status] SUCCESS
55[Start Time] 2021-12-07 07:54:46
56[Duration] 0:00:00
57[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/sysupdates
58------------------------------------------
59Upgrade Summary: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/dbupgrade/upg_summary.log复制
升级后操作
1-- 解决ORA-28040: No matching authentication protocol
2cat >> $ORACLE_HOME/network/admin/sqlnet.ora <<"EOF"
3SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
4SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
5EOF
6
7
8select count(1) from dba_objects where status='INVALID';
9
10
11alter system set compatible='12.2.0' scope=spfile;
12
13
14-- 升级timezone
15unzip DBMS_DST_scriptsV1.9.zip
16cd DBMS_DST_scriptsV1.9
17@upg_tzv_check.sql
18@upg_tzv_apply.sql
19SELECT version FROM v$timezone_file;
20
21
22
23alter system checkpoint;
24select * from v$log;
25alter database drop logfile group 7;
26
27
28col COMP_ID for a10
29col COMP_NAME for a40
30col VERSION for a15
31col status for a15
32set lines 180
33set pages 999
34select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;复制
最后组件的状态：
1SYS@LHR11G> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
2
3COMP_ID COMP_NAME VERSION STATUS
4---------- ---------------------------------------- --------------- ---------------
5CATALOG Oracle Database Catalog Views 12.2.0.1.0 VALID
6CATPROC Oracle Database Packages and Types 12.2.0.1.0 VALID
7JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 VALID
8XML Oracle XDK 12.2.0.1.0 VALID
9CATJAVA Oracle Database Java Packages 12.2.0.1.0 VALID
10APS OLAP Analytic Workspace 12.2.0.1.0 VALID
11OWM Oracle Workspace Manager 12.2.0.1.0 VALID
12CONTEXT Oracle Text 12.2.0.1.0 VALID
13XDB Oracle XML Database 12.2.0.1.0 VALID
14ORDIM Oracle Multimedia 12.2.0.1.0 VALID
15SDO Spatial 12.2.0.1.0 VALID
16XOQ Oracle OLAP API 12.2.0.1.0 VALID
17APEX Oracle Application Express 5.0.4.00.12 VALID复制
使用rman全备+增备+dbupgrade成功异机升级到12.2.0.1.
总体来说，使用AutoUpgrade自动升级比手动或dbua升级要省事的多！
总结
1、在源端删除不必要的组件，会加快升级的速度，例如APEX（Oracle Application Express）可以删掉，对于Spatial需要保证无数据，否则也很耗费时间，可以考虑删除OE用户，或删除example表空间：
1[oracle@lhrora1221 tmp]$ more /tmp/employee/LHR11G/100/dbupgrade/upg_summary.log
2
3Oracle Database 12.2 Post-Upgrade Status Tool 12-07-2021 23:06:30
4
5Component Current Version Elapsed Time
6Name Status Number HH:MM:SS
7
8Oracle Server UPGRADED 12.2.0.1.0 00:26:23
9JServer JAVA Virtual Machine UPGRADED 12.2.0.1.0 00:09:01
10Oracle Workspace Manager UPGRADED 12.2.0.1.0 00:02:54
11OLAP Analytic Workspace UPGRADED 12.2.0.1.0 00:00:46
12Oracle OLAP API UPGRADED 12.2.0.1.0 00:00:30
13Oracle XDK UPGRADED 12.2.0.1.0 00:00:51
14Oracle Text UPGRADED 12.2.0.1.0 00:01:37
15Oracle XML Database UPGRADED 12.2.0.1.0 00:04:14
16Oracle Database Java Packages UPGRADED 12.2.0.1.0 00:00:19
17Oracle Multimedia UPGRADED 12.2.0.1.0 00:07:06
18Spatial UPGRADED 12.2.0.1.0 00:38:30
19Oracle Application Express UPGRADED 5.0.4.00.12 00:34:24
20Final Actions 00:06:43
21Post Upgrade 00:07:32
22
23Total Upgrade Time: 02:21:13
24
25Database time zone version is 14. It is older than current release time
26zone version 26. Time zone upgrade is needed using the DBMS_DST package.
27
28Grand Total Upgrade Time: [0d:2h:45m:44s]
29
30
31SYS@LHR11G> col owner format a15
32SYS@LHR11G> col table_name format a30
33SYS@LHR11G> col column_name format a30
34SYS@LHR11G> SELECT owner,table_name,column_name FROM dba_tab_columns WHERE data_type = 'SDO_GEOMETRY' AND owner != 'MDSYS' ORDER BY 1,2,3;
35
36OWNER TABLE_NAME COLUMN_NAME
37--------------- ------------------------------ ------------------------------
38OE CUSTOMERS CUST_GEO_LOCATION
39OE WAREHOUSES WH_GEO_LOCATION
40
41SYS@LHR11G> drop user oe cascade;
42
43User dropped.复制
2、重新从全备开始测试时，记得删除目标库上的闪回空间中内容，否则会导致恢复出现问题。
3、重新跑升级脚本时，记得删除之前的日志目录，或先备份再删除目录。
4、查看autoupgrade的版本：
1[oracle@lhrora1221 ~]$ /u01/app/oracle/product/12.2.0.1/dbhome_1/jdk/bin/java -jar /home/oracle/autoupgrade.jar -version |grep build.version
2build.version 21.3.211115
3[oracle@lhrora1221 ~]$ /u01/app/oracle/product/12.2.0.1/dbhome_1/jdk/bin/java -jar /home/oracle/autoupgrade.jar -version
4build.hash 081e3f7
5build.version 21.3.211115
6build.date 2021/11/15 11:57:54
7build.max_target_version 21
8build.supported_target_versions 12.2,18,19,21
9build.type production复制
5、DBA自己控制并行度：
1#------------------------------------------#
2# Global Parameters
3#------------------------------------------#
4# Available catctl.pl options:
5#
6# -n Number of processes to use for parallel operations. For Replay upgrades, the number of parallel processes used for the upgrade defaults to the value of (CPU_COUNT divided by 4) . For Classic upgrades, the default for CDB$ROOT is 8.
7# -N Number of processors to use when upgrading PDBs. For Replay upgrades, the number of parallel processes used for the upgrade defaults to the value of (CPU_COUNT divided by 4) For Classic upgrades, the default is 2
8# -t Run SQL in Classic upgrade overwriting default Replay upgrade method
9# -T Takes offline user schema-based table spaces.
10# -z Turns on production debugging information for catcon.pm.
11
12global.catctl_options=-t -n 24 -N 4复制
所有参数参考：https://docs.oracle.com/en/database/oracle/oracle-database/21/upgrd/autoupgrade-utility-configuration-files.html#GUID-5C5542C0-F21E-4DBF-A878-27F5F4AF6519
但是，我配置后感觉没有起到作用，并行度依然是8，有点奇怪，可能还有啥机制吧！
1[oracle@lhrora1221 ~]$ ps -ef|grep upgrade
2oracle 9447 6241 99 11:30 pts/12 00:01:23 /u01/app/oracle/product/12.2.0.1/dbhome_1/jdk/bin/java -jar /home/oracle/autoupgrade.jar -config /tmp/config122.cfg -mode upgrade
3oracle 9570 9447 1 11:31 pts/12 00:00:00 /u01/app/oracle/product/12.2.0.1/dbhome_1/perl/bin/perl /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/catctl.pl -A -l /u01/app/oracle/cfgtoollogs/autoupgrade/job/LHR11G/100/dbupgrade -i 20211208033059lhr11g -d /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin -n 8 catupgrd.sql
4oracle 9800 9743 0 11:31 pts/14 00:00:00 grep --color=auto upgrade复制
6、升级的核心命令不要放后台执行，容易出现问题！意思就是不要用nohup和&放后台执行，问题比较多。
7、从可控角度来说，感觉AutoUpgrade没有手工执行的DBA可控性强！AutoUpgrade就把手工需要跑的内容集合在一块了，更像一个黑匣子了！
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【专家有话说第五期】在不同年龄段，DBA应该怎样规划自己的职业发展？
墨天轮编辑部
1219次阅读
2025-03-13 11:40:53
Oracle RAC ASM 磁盘组满了，无法扩容怎么在线处理？
Lucifer三思而后行
739次阅读
2025-03-17 11:33:53
Oracle+Deepseek+Dify 实现数据库数据实时分析
bicewow
652次阅读
2025-03-06 09:41:49
【ORACLE】ORACLE19C在19.13版本前的一个严重BUG-24761824
DarkAthena
543次阅读
2025-03-04 14:33:31
Oracle避坑指南｜同名表导出难题：如何精准排除指定用户下的表？
szrsu
502次阅读
2025-03-05 00:42:34
2月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
440次阅读
2025-03-13 14:38:19
Ogg23ai高手必看-MySQL Innodb Cluster跟oracle的亲密接触
曹海峰
437次阅读
2025-03-04 21:56:13
【ORACLE】char类型和sql优化器发生的“错误”反应
DarkAthena
398次阅读
2025-03-04 23:05:01
什么，oracle 主机用户被删了？原来是虚惊一场！
Lucifer三思而后行
394次阅读
2025-03-03 21:12:09
Oracle 如何修改 db_unique_name？强迫症福音！
Lucifer三思而后行
325次阅读
2025-03-12 21:27:56
