合 使用rman备份恢复迁移Oracle 11.2.0.4到新机器并使用AutoUpgrade自动升级到12.2.0.1版本
Tags： Oracle数据迁移升级AutoUpgrade
👉 本文共约3699个字，系统预计阅读时间或需14分钟。
目录
前言
在最近的项目里，客户有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工具。
环境介绍
1 2 3 4 5 6 7 8 9 | 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)
使用 DBUA 升级数据库到 Database 12c 版本2（12.2）的完整核对清单 (Doc ID 2364820.1、2189854.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 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 | -- 1、升级JDK到1.8 yum install -y java-1.8.0-openjdk* -- 2、创建并编辑config文件 java -jar /home/oracle/autoupgrade.jar -create_sample_file config ##参照生成的config文件，编写config cat >/tmp/config122.cfg <<"EOF" # Global configurations global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade # Database number 1 - Full DB/CDB upgrade upg1.log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade/employee # Path of the log directory for the upgrade job upg1.sid=LHR11G # ORACLE_SID of the source DB/CDB upg1.source_home=/u01/app/oracle/product/11.2.0.4/dbhome_1 # Path of the source ORACLE_HOME upg1.target_home=/u01/app/oracle/product/12.2.0.1/dbhome_1 # Path of the target ORACLE_HOME upg1.start_time=NOW # Optional. [NOW | +XhYm (X hours, Y minutes after launch) | dd/mm/yyyy hh:mm:ss] upg1.upgrade_node=lhrora11204 # Optional. To find out the name of your node, run the hostname utility. Default is 'localhost' upg1.run_utlrp=yes # Optional. Whether or not to run utlrp after upgrade upg1.timezone_upg=yes # Optional. Whether or not to run the timezone upgrade upg1.target_version=12.2 # Oracle version of the target ORACLE_HOME. Only required when the target Oracle database version is 12.2 EOF -- 3、升级前源库进行分析检查，Notes：可以通过 lsj 命令查看当前JOB的运行情况。 java -jar /home/oracle/autoupgrade.jar -config /tmp/config122.cfg -mode analyze -- 4、升级前源库执行修复脚本，Notes：可以通过 status -job 101 命令查看当前JOB的运行情况。 -- 这个操作会替代之前很多的手工操作，例如删除EM，OLAP AMD，情况回收站等！！ java -jar /home/oracle/autoupgrade.jar -config /tmp/config122.cfg -mode fixups -- 源端删除APEX @?/apex/apxremov.sql -- 检查是否有和SDO_GEOMETRY关联的表 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; -- RECOVERY_AREA 配置大点 alter system set db_recovery_file_dest_size=10g; -- 组件状态 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 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 | [oracle@lhrora11204 ~]$ java -jar /home/oracle/autoupgrade.jar -config /tmp/config122.cfg -mode analyze AutoUpgrade is not fully tested on OpenJDK 64-Bit Server VM, Oracle recommends to use Java HotSpot(TM) AutoUpgrade 21.3.211115 launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be analyzed Type 'help' to list console commands upg> help exit // To close and exit help // Displays help lsj [(-r|-f|-p|-e) | -n <number>] // list jobs by status up to n elements. -f Filter by finished jobs. -r Filter by running jobs. -e Filter by jobs with errors. -p Filter by jobs being prepared. -n <number> Display up to n jobs. lsr // Displays the restoration queue lsa // Displays the abort queue tasks // Displays the tasks running clear // Clears the terminal resume -job <number> // Restarts a previous job that was running status [-job <number> [-long]]// Lists all the jobs or a specific job restore -job <number> // Restores the database to its state prior to the upgrade restore all_failed // Restores all failed jobs to their previous states prior to the upgrade logs // Displays all the log locations abort -job <number> // Aborts the specified job h[ist] // Displays the command line history /[<number>] // Executes the command specified from the history. The default is the last command meta // Displays Internal latch count hwinfo // Displays additional information upg> Job 100 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Jobs pending [0] Please check the summary report at: /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log [oracle@lhrora11204 ~]$ more /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log ========================================== Autoupgrade Summary Report ========================================== [Date] Tue Dec 07 11:21:25 CST 2021 [Number of Jobs] 1 ========================================== [Job ID] 100 ========================================== [DB Name] LHR11G [Version Before Upgrade] 11.2.0.4.0 [Version After Upgrade] 12.2 ------------------------------------------ [Stage Name] PRECHECKS [Status] SUCCESS [Start Time] 2021-12-07 11:21:11 [Duration] 0:00:14 [Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/prechecks [Detail] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/prechecks/lhr11g_preupgrade.log Precheck passed and no manual intervention needed ------------------------------------------ [oracle@lhrora11204 ~]$ java -jar /home/oracle/autoupgrade.jar -config /tmp/config122.cfg -mode fixups AutoUpgrade is not fully tested on OpenJDK 64-Bit Server VM, Oracle recommends to use Java HotSpot(TM) AutoUpgrade 21.3.211115 launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be processed Type 'help' to list console commands upg> status -job 101 Progress ----------------------------------- Start time: 21/12/07 11:26 Elapsed (min): 0 End time: N/A Last update: 2021-12-07T11:26:12.448 Stage: PRECHECKS Operation: PREPARING Status: RUNNING Pending stages: 2 Stage summary: SETUP <1 min PRECHECKS <1 min (IN PROGRESS) Job Logs Locations ----------------------------------- Logs Base: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G Job logs: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/101 Stage logs: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/101/prechecks TimeZone: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/temp Additional information ----------------------------------- Details: Checks Error Details: None 。。。。。 upg> status -job 101 Progress ----------------------------------- Start time: 21/12/07 11:26 Elapsed (min): 5 End time: N/A Last update: 2021-12-07T11:31:08.339 Stage: PREFIXUPS Operation: EXECUTING Status: RUNNING Pending stages: 1 Stage summary: SETUP <1 min PRECHECKS <1 min PREFIXUPS 4 min (IN PROGRESS) Job Logs Locations ----------------------------------- Logs Base: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G Job logs: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/101 Stage logs: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/101/prefixups TimeZone: /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/temp Additional information ----------------------------------- Details: +--------+-----------------+--------+ |DATABASE| FIXUP| STATUS| +--------+-----------------+--------+ | LHR11G| EXF_RUL_EXISTS|FINISHED| | LHR11G| AMD_EXISTS|FINISHED| | LHR11G| EM_PRESENT|FINISHED| | LHR11G|PRE_FIXED_OBJECTS| STARTED| +--------+-----------------+--------+ Error Details: None upg> Job 101 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Jobs pending [0] Please check the summary report at: /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log [oracle@lhrora11204 ~]$ more /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log ========================================== Autoupgrade Summary Report ========================================== [Date] Tue Dec 07 11:33:00 CST 2021 [Number of Jobs] 1 ========================================== [Job ID] 101 ========================================== [DB Name] LHR11G [Version Before Upgrade] 11.2.0.4.0 [Version After Upgrade] 12.2 ------------------------------------------ [Stage Name] PRECHECKS [Status] SUCCESS [Start Time] 2021-12-07 11:26:12 [Duration] 0:00:12 [Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/101/prechecks [Detail] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/101/prechecks/lhr11g_preupgrade.log Precheck passed and no manual intervention needed ------------------------------------------ [Stage Name] PREFIXUPS [Status] SUCCESS [Start Time] 2021-12-07 11:26:25 [Duration] 0:06:34 [Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/101/prefixups ------------------------------------------ |
看来，源库不需要做任何操作。
源端全备
1 2 3 4 5 6 7 8 9 10 11 12 | -- 开启bct alter database enable block change tracking using file '/home/oracle/bct.log'; select * from v$block_change_tracking; 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 | 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/ |
目标端恢复全备
1 2 3 4 5 6 7 8 9 10 11 | mkdir -p /u01/app/oracle/admin/LHR11G/adump export ORACLE_SID=LHR11G startup force nomount restore controlfile from '/home/oracle/bk/LHR11G0v0g6vm8_1_1.ctl'; alter database mount; CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET; restore database; recover database; |
注意，如果从这里重新做还原恢复操作，那么应该注意以下内容：
1、删除之前闪回恢复区中的所有文件，否则会自动注册闪回恢复区中的归档文件，会引起恢复错误！！！
源端增量备份
1 2 3 4 5 6 7 8 9 10 11 | create table lhr.test1207 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 5 6 | catalog start with '/home/oracle/bk/'; recover database; alter database open resetlogs; startup upgrade; |
目标端PSU升级
如果不升级，会报错：
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 | [oracle@lhrora1221 employee]$ $ORACLE_HOME/jdk/bin/java -jar /home/oracle/autoupgrade.jar -config /tmp/config122.cfg -mode upgrade AutoUpgrade 21.3.211115 launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be processed Type 'help' to list console commands upg> ------------------------------------------------- Errors in database [LHR11G] Stage [DBUPGRADE] Operation [STOPPED] Status [ERROR] Info [ Error: 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 Cause: Invalid version of catctl.pl and catctl.pm For further details, see the log file located at /u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/autoupgrade_20211207_user.log] ------------------------------------------------- Logs: [/u01/app/oracle/cfgtoollogs/autoupgrade/employee/LHR11G/100/autoupgrade_20211207_user.log] ------------------------------------------------- 2021-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 2021-12-07 04:38:59.567 ERROR Exception Error in Database Upgrade [LHR11G] 2021-12-07 04:38:59.621 ERROR lhr11g Return status is ERROR 2021-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] 2021-12-07 04:38:59.641 ERROR Dispatcher failed: Error: 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 Cause: Invalid version of catctl.pl and catctl.pm [oracle@lhrora1221 employee]$ $ORACLE_HOME/jdk/bin/java -jar /home/oracle/autoupgrade.jar -error_code UPG-1411 ERROR1411.ERROR = UPG-1411 ERROR1411.CAUSE = Invalid version of catctl.pl and catctl.pm |
解决办法：打最新的PSU补丁。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
最后升级脚本的时间跟数据库组件的个数、类型和是否含有特殊组件有关，另外也跟机器性能也有一定的关系，我在云主机上测试的，30分钟即可升级完毕。
这个升级，目标端12C 也是没有PDB的CDB吧？
对的