Oracle数据库异机升级是怎样的

这期内容当中小编将会给大家带来有关Oracle数据库异机升级是怎样的，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

架构介绍：源库：单实例  ip:192.168.56.11  ORCLE_SID:orcl  db_nme:orcl  版本：10.2.0.5.0os:Red Hat Enterprise Linux Server release 4 (Tikanga)   hostname:rhel目标库：单实例  ip:192.168.56.28  ORACLE_SID:kill  版本：11.2.0.4（只安装了or源库cle软件）      os:Red Hat Enterprise Linux Server release 6.7 (Santiago)   hostname:testd目标库 异机升级步骤：一、 确认是否可以直接升级二、 目标库机安装11g软件，打好PSU补丁三、 目标库机环境变量检查四、 目标库机创建需要的目录五、 源库机原库升级前检查    5.1 从目标库机上传utlu112i.sql脚本到源库机5.2 源库机执行utlu112i.sql进行升级前检查    5.3 根据检查结果调整源库机数据库六、 源库机备份原库七、 目标库机恢复数据库    7.1 创建密码文件7.2 启动实例到nomount状态(指定pfile文件)    7.3 RMAN恢复控制文件，确定备份集有效性    7.4 RMAN恢复数据库    7.5 打开数据库(resetlogs)八、 目标库机升级数据库8.1 为预防升级测试过程中默认的归档空间不够，这里升级先把库开启为非归档模式    8.2 升级open数据库    8.3 执行升级脚本九、 目标库机升级后操作    9.1 创建spfile文件                                                    9.2 执行EXECUTE dbms_stats.gather_dictionary_stats收集数据字典统计信息    9.3 重新编译 @?/rdbms/admin/utlrp.sql                                     9.4 查看失效对象                                                          9.5 运行升级后检查脚本@?/rdbms/admin/utlu112s.sql                     9.6 数据库层面应用PSU一、 确认是否可以直接升级参考：http://blog.itpub-/31397003/viewspace-2146129/二、 目标库机安装11g软件，打好PSU补丁 升级安装至oracle软件步骤省略...................打PSU补丁如下：[oracle@testdb OPatch]$ pwd/u01/app/oracle/product/11.2.0/dbhome_1/OPatch[oracle@testdb OPatch]$ [oracle@testdb OPatch]$ ./opatch versionOPatch Version: 11.2.0.3.4OPatch succeeded.[oracle@testdb OPatch]$          上传opatch包及psu包如下：[oracle@testdb ~]$ lsdatabase  db_install_oui.rsp  p24732075_112040_Linux-x86-64.zip  p6880880_112000_Linux-x86-64.zip[oracle@testdb ~]$ pwd/home/oracle更新opatch工具：[oracle@testdb dbhome_1]$ mv OPatch OPatch.bak[oracle@testdb dbhome_1]$ [oracle@testdb ~]$ unzip p6880880_112000_Linux-x86-64.zip  -d /u01/app/oracle/product/11.2.0/dbhome_1省略.....................[oracle@testdb ~]$ cd -/u01/app/oracle/product/11.2.0/dbhome_1[oracle@testdb dbhome_1]$ cd OPatch[oracle@testdb OPatch]$ ./opatch versionOPatch Version: 11.2.0.3.12OPatch succeeded.[oracle@testdb OPatch]$ 应用psu:[oracle@testdb ~]$ lsdatabase  db_install_oui.rsp  p24732075_112040_Linux-x86-64.zip  p6880880_112000_Linux-x86-64.zip[oracle@testdb ~]$ [oracle@testdb ~]$ mkdir soft[oracle@testdb ~]$ mv p24732075_112040_Linux-x86-64.zip soft/[oracle@testdb ~]$ [oracle@testdb ~]$ cd soft/[oracle@testdb soft]$ lsp24732075_112040_Linux-x86-64.zip[oracle@testdb soft]$ [oracle@testdb soft]$ unzip p24732075_112040_Linux-x86-64.zip省略................................[oracle@testdb soft]$ ls24732075  p24732075_112040_Linux-x86-64.zip  PatchSearch.xml[oracle@testdb soft]$ [oracle@testdb 24732075]$ ls17478514  18031668  18522509  19121551  19769489  20299013  20760982  21352635  21948347  22502456  23054359  24006111  24732075  patchmd.xml  README.html  README.txt[oracle@testdb 24732075]$ 根据README.html进行打补丁：进行冲突检测：[oracle@testdb 24732075]$ pwd/home/oracle/soft/24732075[oracle@testdb 24732075]$[oracle@testdb 24732075]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./Oracle Interim Patch Installer version 11.2.0.3.12Copyright (c) 2017, Oracle Corporation.  All rights reserved.PREREQ sessionOracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1Central Inventory : /u01/app/oraInventoryfrom           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.locOPatch version    : 11.2.0.3.12OUI version       : 11.2.0.4.0Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-27_21-51-47PM_1.logInvoking prereq "checkconflictagainstohwithdetail"Prereq "checkConflictAgainstOHWithDetail" passed.OPatch succeeded.[oracle@testdb 24732075]$开始应用补丁：[oracle@testdb 24732075]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch applyOracle Interim Patch Installer version 11.2.0.3.12Copyright (c) 2017, Oracle Corporation.  All rights reserved.Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1Central Inventory : /u01/app/oraInventoryfrom           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.locOPatch version    : 11.2.0.3.12OUI version       : 11.2.0.4.0Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-27_21-54-33PM_1.logVerifying environment and performing prerequisite checks...OPatch continues with these patches:   17478514  18031668  18522509  19121551  19769489  20299013  20760982  21352635  21948347  22502456  23054359  24006111  24732075Do you want to proceed? [y|n]yUser Responded with: YAll checks passed.Provide your email address to be informed of security issues, install andinitiate Oracle Configuration Manager. Easier for you if you use your MyOracle Support Email address/User Name.Visit http://www.oracle.com/support/policies.html for details.Email address/User Name: You have not provided an email address for notification of security issues.Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  yPlease shutdown Oracle instances running out of this ORACLE_HOME on the local system.(Oracle Home = /u01/app/oracle/product/11.2.0/dbhome_1)Is the local system ready for patching? [y|n]yUser Responded with: YBacking up files...Applying sub-patch 17478514 to OH /u01/app/oracle/product/11.2.0/dbhome_1Patching component oracle.rdbms, 11.2.0.4.0...Patching component oracle.rdbms.rsf, 11.2.0.4.0...Patching component oracle.sdo, 11.2.0.4.0...Patching component oracle.sysman.agent, 10.2.0.4.5...省略...............................................................省略...............................................................Patching component oracle.ordim.server, 11.2.0.4.0...Composite patch 24732075 successfully applied.Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-27_21-54-33PM_1.logOPatch succeeded.[oracle@testdb 24732075]$  [oracle@testdb OPatch]$ ./opatch lspatches24732075;Database Patch Set Update : 11.2.0.4.170418 (24732075)OPatch succeeded.[oracle@testdb OPatch]$三、 目标库机环境变量检查 [oracle@testdb ~]$ cat .bash_profile # .bash_profileexport ORACLE_SID=killexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1export NLS_LANG="american_america.ZHS16GBK"export NLS_DATE_FORMAT="YYYY-MM-DD HH24:Mi:SS"export LD_LIBRARY_PATH=$ORACLE_HOME/libexport PATH=$ORACLE_HOME/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin[oracle@testdb ~]$ 四、目标库创建需要的目录目标库建议先创建以下目录，对于bdump,udump在11g不再需要了，这点从后面的升级前检查脚本的输出结果也可看到。mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adumpmkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdump mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdumpmkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile [oracle@testdb ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump[oracle@testdb ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdump[oracle@testdb ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump [oracle@testdb ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile五、 源库机原库升级前检查5.1 从目标库机上传utlu112i.sql脚本到源库机[oracle@testdb admin]$ cd $ORACLE_HOME/rdbms/admin/[oracle@testdb admin]$ [oracle@testdb admin]$ ls -lrt utlu112i*-rw-rw-rw-. 1 oracle oinstall 225754 Feb 23  2017 utlu112i.sql[oracle@testdb admin]$ [oracle@testdb admin]$ scp utlu112i.sql oracle@192.168.56.11:/home/oracleThe authenticity of host 192.168.56.11 (192.168.56.11) cant be established.RSA key fingerprint is ed:38:fa:9f:2a:49:b6:c6:22:7a:05:78:3e:ea:c4:28.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added 192.168.56.11 (RSA) to the list of known hosts.oracle@192.168.56.11s password: utlu112i.sql                                                                                                                              100%  220KB 220.5KB/s   00:00[oracle@testdb admin]$ 5.2 源库机执行utlu112i.sql进行升级前检查[oracle@rhel ~]$ lspatch  soft  utlu112i.sql[oracle@rhel ~]$ [oracle@rhel ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Thu Nov 23 16:32:27 2017Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> spool upgrade.info SQL> @utlu112i.sqlOracle Database 11.2 Pre-Upgrade Information Tool 11-23-2017 16:32:53Script Version: 11.2.0.4.0 Build: 007.**********************************************************************Database:**********************************************************************--> name:          ORCL--> version:       10.2.0.1.0--> compatible:    10.2.0.1.0--> blocksize:     8192--> platform:      Linux 64-bit for AMD--> timezone file: V2.**********************************************************************Tablespaces: [make adjustments in the current environment]**********************************************************************--> SYSTEM tablespace is adequate for the upgrade..... minimum required size: 1113 MB--> UNDOTBS1 tablespace is adequate for the upgrade..... minimum required size: 400 MB--> SYSAUX tablespace is adequate for the upgrade..... minimum required size: 714 MB--> TEMP tablespace is adequate for the upgrade..... minimum required size: 60 MB--> EXAMPLE tablespace is adequate for the upgrade..... minimum required size: 69 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:WARNING: --> "sga_target" needs to be increased to at least 596 MB.**********************************************************************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]  VALID--> OLAP Catalog                 [upgrade]  VALID--> EM Repository                [upgrade]  VALID--> Oracle Text                  [upgrade]  VALID--> Oracle XML Database          [upgrade]  VALID--> Oracle Java Packages         [upgrade]  VALID--> Oracle interMedia            [upgrade]  VALID--> Spatial                      [upgrade]  VALID--> Data Mining                  [upgrade]  VALID--> Expression Filter            [upgrade]  VALID--> Rule Manager                 [upgrade]  VALID--> Oracle OLAP API              [upgrade]  VALID.**********************************************************************Miscellaneous Warnings**********************************************************************WARNING: --> Database is using 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.1.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 SYS has 1 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 is turned on and currently contains no objects..... Because it is REQUIRED that the recycle bin be empty prior to upgrading.... and your recycle bin is turned on, you may need to execute the command:        PURGE DBA_RECYCLEBIN.... prior to executing your upgrade to confirm the recycle bin is empty..**********************************************************************Recommendations**********************************************************************Oracle recommends gathering dictionary statistics prior toupgrading the database.To gather dictionary statistics execute the following commandwhile connected as SYSDBA:    EXECUTE dbms_stats.gather_dictionary_stats;**********************************************************************SQL> spool offSQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options[oracle@rhel ~]$ [oracle@rhel ~]$ lspatch  soft  upgrade.info  utlu112i.sql[oracle@rhel ~]$ 5.3 根据检查结果调整源库机数据库5.3.1源库创建pfile并传到目标库：SQL> show parameter spfileNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------spfile                               string      /u01/app/oracle/product/10.2.0                                                 .1/db_1/dbs/spfileorcl.oraSQL> SQL> SQL> create pfile from spfile;File created.SQL> SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options[oracle@rhel ~]$ [oracle@rhel ~]$ cd $ORACLE_HOME/dbs[oracle@rhel dbs]$ ls inito*initorcl.ora[oracle@rhel dbs]$ scp initorcl.ora  oracle@192.168.56.28:/home/oracleThe authenticity of host 192.168.56.28 (192.168.56.28) cant be established.RSA key fingerprint is 16:8d:5a:fb:f2:58:e1:ee:4c:98:3d:76:ec:48:bb:46.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added 192.168.56.28 (RSA) to the list of known hosts.oracle@192.168.56.28s password:initorcl.ora                                                                                                                         100%  994     1.0KB/s   00:00[oracle@rhel dbs]$ 5.3.2在目标库库上进行调整pfile:注意下sga的设定是否符合目的机器的配置*.sga_target=624951296  WARNING: --> "sga_target" needs to be increased to at least 596 MB将*.background_dump_dest和*.user_dump_dest删除加上 *.diagnostic_dest=$ORACLE_BASE注：参数文件的修改不用多说，但需要注意其他各参数的值是否符合需求。[oracle@testdb ~]$ cat initorcl.ora kill.__db_cache_size=478150656kill.__java_pool_size=4194304kill.__large_pool_size=4194304kill.__shared_pool_size=109051904kill.__streams_pool_size=0*.audit_file_dest=/u01/app/oracle/admin/kill/adump#*.background_dump_dest=/u01/app/oracle/admin/kill/bdump*.compatible=10.2.0.1.0*.control_files=/u01/app/oracle/oradata/kill/control01.ctl,/u01/app/oracle/oradata/kill/control02.ctl,/u01/app/oracle/oradata/kill/control03.ctl*.core_dump_dest=/u01/app/oracle/admin/kill/cdump*.db_block_size=8192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=orcl*.diagnostic_dest=$ORACLE_BASE*.db_recovery_file_dest=/u01/app/oracle/flash_recovery_area*.db_recovery_file_dest_size=2147483648*.dispatchers=(PROTOCOL=TCP) (SERVICE=orclXDB)*.job_queue_processes=10*.open_cursors=300*.pga_aggregate_target=199229440*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.sga_target=624951296*.undo_management=AUTO*.undo_tablespace=UNDOTBS1#*.user_dump_dest=/u01/app/oracle/admin/orcl/udump[oracle@testdb ~]$ 根据参数文件在目标库上创建相应的目录；[oracle@testdb ~]$ mkdir -p /u01/app/oracle/oradata/kill/[oracle@testdb cdump]$ mkdir -p /u01/app/oracle/flash_recovery_area修改后复制一份到$ORACLE_HOME/dbs/init$ORACLE_SID.ora[oracle@testdb ~]$ cp initorcl.ora $ORACLE_HOME/dbs/init$ORACLE_SID.ora[oracle@testdb ~]$ cd $ORACLE_HOME/dbs/[oracle@testdb dbs]$ lsinitkill.ora  init.ora[oracle@testdb dbs]$5.3.3 源库执行PURGE DBA_RECYCLEBINSQL> PURGE DBA_RECYCLEBIN;   SQL> 5.3.4 源库收集系统信息EXECUTE dbms_stats.gather_dictionary_stats;在升级前收集字典统计信息，否则预升级工具( utlu102i.sql)会花费更长时间SQL> EXECUTE dbms_stats.gather_dictionary_stats;PL/SQL procedure successfully completed.5.3.5 源库运行utlrp.sql 脚本，重新编译无效对象select * from registry$nonsys_inv_objs; //非sys/system的失效对象select * from registry$sys_inv_objs;   //sys/system的失效对象SQL> @?/rdbms/admin/utlrp.sql;重新执行 @/home/oracle/utlu112i.sql，查看是否重新编译SQL> select * from registry$sys_inv_objs;OWNER      OBJECT_NAME                    OBJECT_TYP---------- ------------------------------ ----------SYS        CALLING                        PROCEDURESQL> drop procedure calling;SQL>注意：升级之后执行utluiobj.sqlUse utluiobj.sql after the upgrade to identify any new invalid六、 源库备份创建备份脚本：注意；脚本将备份归档，需开启归档；[oracle@rhel ~]$ mkdir bak[oracle@rhel ~]$ vi backup_all.sh#!/bin/bash#Define variable <You may need to change the value of basedir.>basedir=/home/oracle/bakdate=`date +%Y%m%d`#Create pfilesqlplus / as sysdba <<EOFcreate pfile=$basedir/pfile$date.ora from spfile;EOF#RMAN BACKUPrman target / log=$basedir/backup_all_$date.log <<EOFrun{allocate channel c1 device type disk;allocate channel c2 device type disk;backup database filesperset 4 format $basedir/full_%d_%T_%s_%p;sql alter system archive log current;sql alter system archive log current;sql alter system archive log current;sql alter system archive log current;backup archivelog all format $basedir/arch_%d_%T_%s_%p delete input;backup current controlfile format $basedir/ctl_%d_%T_%s_%p;release channel c1;release channel c2;}EOF~"backup_all.sh" [New] 27L, 773C written                                                                                                              [oracle@rhel ~]$ 执行脚本；[oracle@rhel ~]$ nohup sh backup_all.sh &[1] 17818[oracle@rhel ~]$ nohup: appending output to `nohup.out[oracle@rhel ~]$ [oracle@rhel ~]$ jobs[1]+  Running                 nohup sh backup_all.sh &[oracle@rhel ~]$ [oracle@rhel ~]$ jobs[1]+  Running                 nohup sh backup_all.sh &[oracle@rhel ~]$ [1]+  Done                    nohup sh backup_all.sh[oracle@rhel ~]$ [oracle@rhel ~]$  cd bak[oracle@rhel bak]$ ls -lrttotal 1231432-rw-r--r-- 1 oracle oinstall       994 Nov 23 17:21 pfile20171123.ora-rw-r----- 1 oracle oinstall 564199424 Nov 23 17:22 full_ORCL_20171123_6_1-rw-r----- 1 oracle oinstall   7110656 Nov 23 17:22 full_ORCL_20171123_7_1-rw-r----- 1 oracle oinstall     98304 Nov 23 17:22 full_ORCL_20171123_8_1-rw-r----- 1 oracle oinstall 676749312 Nov 23 17:22 full_ORCL_20171123_5_1-rw-r----- 1 oracle oinstall   4425728 Nov 23 17:23 arch_ORCL_20171123_9_1-rw-r----- 1 oracle oinstall      4608 Nov 23 17:23 arch_ORCL_20171123_10_1-rw-r----- 1 oracle oinstall      2560 Nov 23 17:23 arch_ORCL_20171123_11_1-rw-r----- 1 oracle oinstall   7110656 Nov 23 17:23 ctl_ORCL_20171123_12_1-rw-r--r-- 1 oracle oinstall      5045 Nov 23 17:23 backup_all_20171123.log[oracle@rhel bak]$ 将源库备份的文件传到目标路（目标库操作）[oracle@testdb ~]$ mkdir bak[oracle@testdb ~]$ cd bak[oracle@testdb bak]$ ls[oracle@testdb bak]$ [oracle@testdb bak]$ scp oracle@192.168.56.11:/home/oracle/bak/* .oracle@192.168.56.11s password: arch_ORCL_20171019_5_1                                                                                                               100%   36MB  18.0MB/s   00:02arch_ORCL_20171019_6_1                                                                                                               100% 4096     4.0KB/s   00:00arch_ORCL_20171019_7_1                                                                                                               100% 2560     2.5KB/s   00:00backup_all_20171019.log                                                                                                              100% 5042     4.9KB/s   00:00ctl_ORCL_20171019_8_1                                                                                                                100% 6944KB   6.8MB/s   00:00full_ORCL_20171019_1_1                                                                                                               100%  692MB  22.3MB/s   00:31full_ORCL_20171019_2_1                                                                                                               100%  438MB  15.1MB/s   00:29full_ORCL_20171019_3_1                                                                                                               100% 6944KB   6.8MB/s   00:01full_ORCL_20171019_4_1                                                                                                               100%   96KB  96.0KB/s   00:00pfile20171019.ora                                                                                                                    100%  995     1.0KB/s   00:00[oracle@testdb bak]$  ls -lrttotal 1207652-rw-r----- 1 oracle oinstall  37643264 Dec 31 13:39 arch_ORCL_20171019_5_1-rw-r----- 1 oracle oinstall      4096 Dec 31 13:39 arch_ORCL_20171019_6_1-rw-r----- 1 oracle oinstall      2560 Dec 31 13:39 arch_ORCL_20171019_7_1-rw-r--r-- 1 oracle oinstall      5042 Dec 31 13:39 backup_all_20171019.log-rw-r----- 1 oracle oinstall   7110656 Dec 31 13:39 ctl_ORCL_20171019_8_1-rw-r----- 1 oracle oinstall 725647360 Dec 31 13:39 full_ORCL_20171019_1_1-rw-r----- 1 oracle oinstall 458997760 Dec 31 13:40 full_ORCL_20171019_2_1-rw-r----- 1 oracle oinstall   7110656 Dec 31 13:40 full_ORCL_20171019_3_1-rw-r----- 1 oracle oinstall     98304 Dec 31 13:40 full_ORCL_20171019_4_1-rw-r--r-- 1 oracle oinstall       995 Dec 31 13:40 pfile20171019.ora[oracle@testdb bak]$ 七、目标库恢复数据库注意：这里恢复完成不要直接尝试打开数据库。因为我们是要升级！需要open resetlogs upgrade（在下一个步骤说明）。7.1 创建密码文件:orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle force=y entries=5[oracle@testdb admin]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle force=y entries=5[oracle@testdb admin]$ cd $ORACLE_HOME/dbs/[oracle@testdb dbs]$ lsinitkill.ora  init.ora  orapwkill[oracle@testdb dbs]$ 7.2 启动实例到nomount状态(指定pfile文件)根据之前修改传到目标库上的pfile启动数据库到nomount状态；startup nomount pfile=$ORACLE_HOME/dbs/init$ORACLE_SID.ora[oracle@testdb ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 28 02:02:13 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup nomount pfile=$ORACLE_HOME/dbs/init$ORACLE_SID.oraORACLE instance started.Total System Global Area  622149632 bytesFixed Size                  2255792 bytesVariable Size             230687824 bytesDatabase Buffers          385875968 bytesRedo Buffers                3330048 bytesSQL> 7.3 RMAN恢复控制文件，确定备份集有效性7.3.1 RMAN恢复控制文件，启动数据库到mount状态，确定备份集有效性[oracle@testdb 11gbak]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 28 02:03:42 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCL (not mounted)RMAN> restore controlfile from /home/oracle/bak/ctl_ORCL_20171019_8_1;Starting restore at 2017-12-31 13:46:21using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=20 device type=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:02output file name=/u01/app/oracle/oradata/kill/control01.ctloutput file name=/u01/app/oracle/oradata/kill/control02.ctloutput file name=/u01/app/oracle/oradata/kill/control03.ctlFinished restore at 2017-12-31 13:46:23RMAN> alter database mount;RMAN> crosscheck backupset;RMAN> crosscheck backupset;Starting implicit crosscheck backup at 2017-12-31 13:46:59allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=20 device type=DISKCrosschecked 7 objectsFinished implicit crosscheck backup at 2017-12-31 13:46:59Starting implicit crosscheck copy at 2017-12-31 13:46:59using channel ORA_DISK_1Finished implicit crosscheck copy at 2017-12-31 13:47:00searching for all files in the recovery areacataloging files...no files catalogedusing channel ORA_DISK_1crosschecked backup piece: found to be AVAILABLEbackup piece handle=/home/oracle/bak/full_ORCL_20171019_2_1 RECID=1 STAMP=957803210crosschecked backup piece: found to be AVAILABLEbackup piece handle=/home/oracle/bak/full_ORCL_20171019_3_1 RECID=2 STAMP=957803260crosschecked backup piece: found to be AVAILABLEbackup piece handle=/home/oracle/bak/full_ORCL_20171019_4_1 RECID=3 STAMP=957803264crosschecked backup piece: found to be AVAILABLEbackup piece handle=/home/oracle/bak/full_ORCL_20171019_1_1 RECID=4 STAMP=957803210crosschecked backup piece: found to be AVAILABLEbackup piece handle=/home/oracle/bak/arch_ORCL_20171019_6_1 RECID=5 STAMP=957803311crosschecked backup piece: found to be AVAILABLEbackup piece handle=/home/oracle/bak/arch_ORCL_20171019_5_1 RECID=6 STAMP=957803311crosschecked backup piece: found to be AVAILABLEbackup piece handle=/home/oracle/bak/arch_ORCL_20171019_7_1 RECID=7 STAMP=957803314Crosschecked 7 objectsRMAN> 注意，如果备份集过期，则先注册，再删除，如下：示例一般是两台主机的备份目录不一样，控制文件记录的备份集路径找不到对应的备份集，状态为EXPIRED，此时应该删除这些过期的备份集，catalog新的备份集，再次确认备份集有效性。rman target / > catalog.log <<EOFcatalog backuppiece /u01/orabak/backup/full_JINGYU_20150911_39_1;catalog backuppiece /u01/orabak/backup/full_JINGYU_20150911_40_1;catalog backuppiece /u01/orabak/backup/full_JINGYU_20150911_41_1;catalog backuppiece /u01/orabak/backup/full_JINGYU_20150911_42_1;catalog backuppiece /u01/orabak/backup/arch_JINGYU_20150911_43_1;catalog backuppiece /u01/orabak/backup/arch_JINGYU_20150911_44_1;catalog backuppiece /u01/orabak/backup/arch_JINGYU_20150911_45_1;crosscheck backupset;delete noprompt expired backupset;crosscheck backupset;EOF7.4 RMAN恢复数据库7.4.1 restore数据文件如果数据文件存放目录已经更改，需要重命名还原。rman target / log=restore.log <<EOFrun {allocate channel c1 device type disk;allocate channel c2 device type disk;set newname for datafile 1 to /u01/app/oracle/oradata/kill/system01.dbf;set newname for datafile 2 to /u01/app/oracle/oradata/kill/undotbs01.dbf;set newname for datafile 3 to /u01/app/oracle/oradata/kill/sysaux01.dbf;set newname for datafile 4 to /u01/app/oracle/oradata/kill/users01.dbf;set newname for datafile 5 to /u01/app/oracle/oradata/kill/example01.dbf;restore database;   switch datafile all;release channel c1;release channel c2;}EOF[oracle@testdb 11gbak]$ rman target / log=restore.log <<EOF> run {> allocate channel c1 device type disk;> allocate channel c2 device type disk;> set newname for datafile 1 to /u01/app/oracle/oradata/kill/system01.dbf;> set newname for datafile 2 to /u01/app/oracle/oradata/kill/undotbs01.dbf;> set newname for datafile 3 to /u01/app/oracle/oradata/kill/sysaux01.dbf;> set newname for datafile 4 to /u01/app/oracle/oradata/kill/users01.dbf;> set newname for datafile 5 to /u01/app/oracle/oradata/kill/example01.dbf;> restore database;   > switch datafile all;> release channel c1;> release channel c2;> }> EOFRMAN> RMAN> [oracle@testdb 11gbak]$ [oracle@testdb 11gbak]$  [oracle@testdb 11gbak]$ tail -200f restore.logRecovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 28 02:34:24 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCL (DBID=1485502468, not open)RMAN> using target database control file instead of recovery catalogallocated channel: c1channel c1: SID=21 device type=DISKallocated channel: c2channel c2: SID=24 device type=DISKexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting restore at 2017-12-31 13:52:59channel c1: starting datafile backup set restorechannel c1: specifying datafile(s) to restore from backup setchannel c1: restoring datafile 00001 to /u01/app/oracle/oradata/kill/system01.dbfchannel c1: restoring datafile 00005 to /u01/app/oracle/oradata/kill/example01.dbfchannel c1: reading from backup piece /home/oracle/bak/full_ORCL_20171019_2_1channel c2: starting datafile backup set restorechannel c2: specifying datafile(s) to restore from backup setchannel c2: restoring datafile 00002 to /u01/app/oracle/oradata/kill/undotbs01.dbfchannel c2: restoring datafile 00003 to /u01/app/oracle/oradata/kill/sysaux01.dbfchannel c2: restoring datafile 00004 to /u01/app/oracle/oradata/kill/users01.dbfchannel c2: reading from backup piece /home/oracle/bak/full_ORCL_20171019_1_1channel c1: piece handle=/home/oracle/bak/full_ORCL_20171019_2_1 tag=TAG20171019T162649channel c1: restored backup piece 1channel c1: restore complete, elapsed time: 00:00:55channel c2: piece handle=/home/oracle/bak/full_ORCL_20171019_1_1 tag=TAG20171019T162649channel c2: restored backup piece 1channel c2: restore complete, elapsed time: 00:00:56Finished restore at 2017-12-31 13:53:56datafile 1 switched to datafile copyinput datafile copy RECID=7 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/system01.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=8 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/undotbs01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=9 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/sysaux01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=10 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/users01.dbfdatafile 5 switched to datafile copyinput datafile copy RECID=11 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/example01.dbfreleased channel: c1released channel: c2RMAN> Recovery Manager complete.[oracle@testdb bak]$7.4.2 recover数据文件7.4.2.1 使用recover database命令恢复数据RMAN> recover database;RMAN> recover database;Starting recover at 2017-12-31 13:55:19using channel ORA_DISK_1starting media recoverychannel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=38channel ORA_DISK_1: reading from backup piece /home/oracle/bak/arch_ORCL_20171019_5_1channel ORA_DISK_1: piece handle=/home/oracle/bak/arch_ORCL_20171019_5_1 tag=TAG20171019T162831channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:03archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_38_f4jyy9kb_.arc thread=1 sequence=38channel default: deleting archived log(s)archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_38_f4jyy9kb_.arc RECID=6 STAMP=964187722channel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=39channel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=40channel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=41channel ORA_DISK_1: reading from backup piece /home/oracle/bak/arch_ORCL_20171019_6_1channel ORA_DISK_1: piece handle=/home/oracle/bak/arch_ORCL_20171019_6_1 tag=TAG20171019T162831channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_39_f4jyyf2v_.arc thread=1 sequence=39channel default: deleting archived log(s)archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_39_f4jyyf2v_.arc RECID=8 STAMP=964187725archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_40_f4jyyf1t_.arc thread=1 sequence=40channel default: deleting archived log(s)archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_40_f4jyyf1t_.arc RECID=7 STAMP=964187725archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_41_f4jyyf3q_.arc thread=1 sequence=41channel default: deleting archived log(s)archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_41_f4jyyf3q_.arc RECID=9 STAMP=964187725channel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=42channel ORA_DISK_1: reading from backup piece /home/oracle/bak/arch_ORCL_20171019_7_1channel ORA_DISK_1: piece handle=/home/oracle/bak/arch_ORCL_20171019_7_1 tag=TAG20171019T162831channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_42_f4jyyg9r_.arc thread=1 sequence=42channel default: deleting archived log(s)archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_42_f4jyyg9r_.arc RECID=10 STAMP=964187726unable to find archived logarchived log thread=1 sequence=43RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 12/31/2017 13:55:28RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 43 and starting SCN of 1015325RMAN> 7.4.2.2 recover database until scn xxxxxxx;注意：这个scn根据上一步的日志信息获取。RMAN>  recover database until scn 1015325;Starting recover at 2017-12-31 13:55:53using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 2017-12-31 13:55:54RMAN> 7.4.2.3 修改日志文件路径注意：可以用UE列编辑模式快速处理下新的redo文件名字(即redoXXX.log改为对应的实际值)new_dest=/u01/app/oracle/oradata/killsqlplus / as sysdba > logfile.log <<EOF                                                             set linesize 180 pagesize 100                                                                      select alter database rename file ||member|| to $new_dest/redoXXX.log; from v\$logfile;EOF          执行记录如下：[oracle@testdb ~]$ new_dest=/u01/app/oracle/oradata/kill[oracle@testdb ~]$ sqlplus / as sysdba > logfile.log <<EOF                                                             > set linesize 180 pagesize 100> select alter database rename file ||member|| to $new_dest/redoXXX.log; from v\$logfile;> EOF[oracle@testdb ~]$ [oracle@testdb ~]$ cat logfile.log SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 13:57:16 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> SQL> ALTERDATABASERENAMEFILE||MEMBER||TO/U01/APP/ORACLE/ORADATA/KILL/REDOXXX.LOG;-----------------------------------------------------------------------------------------------------------alter database rename file /u01/app/oracle/oradata/orcl/redo03.log to /u01/app/oracle/oradata/kill/redoXXX.log;alter database rename file /u01/app/oracle/oradata/orcl/redo02.log to /u01/app/oracle/oradata/kill/redoXXX.log;alter database rename file /u01/app/oracle/oradata/orcl/redo01.log to /u01/app/oracle/oradata/kill/redoXXX.log;SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@testdb ~]$     正式执行：   SQL> alter database rename file /u01/app/oracle/oradata/orcl/redo03.log to /u01/app/oracle/oradata/kill/redo03.log;alter database rename file /u01/app/oracle/oradata/orcl/redo02.log to /u01/app/oracle/oradata/kill/redo02.log;alter database rename file /u01/app/oracle/oradata/orcl/redo01.log to /u01/app/oracle/oradata/kill/redo01.log;Database altered.SQL> Database altered.SQL> Database altered.SQL> select member from v$logfile;                                                                                       MEMBER--------------------------------------------------------------------------------                                         /u01/app/oracle/oradata/kill/redo03.log/u01/app/oracle/oradata/kill/redo02.log                                                                                  /u01/app/oracle/oradata/kill/redo01.log7.4.3 修改临时文件路径   注意：同样处理下新的temp文件名字(即tempXXX.dbf改为对应的实际值)。new_dest=/u01/app/oracle/oradata/kill                                                                                                                                                                                 sqlplus / as sysdba > tempfile.log<<EOFset linesize 180 pagesize 100                                                                     select alter database rename file ||name|| to $new_dest/tempXXX.dbf; from v\$tempfile;EOF               --查询[oracle@testdb 11gbak]$ new_dest=/u01/app/oracle/oradata/kill[oracle@testdb 11gbak]$ sqlplus / as sysdba > tempfile.log<<EOF                                                           > set linesize 180 pagesize 100> select alter database rename file ||name|| to $new_dest/tempXXX.dbf; from v\$tempfile;> EOF[oracle@testdb 11gbak]$ cat tempfile.log SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 28 02:50:11 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> SQL> ALTERDATABASERENAMEFILE||NAME||TO/U01/APP/ORACLE/ORADATA/KILL/TEMPXXX.DBF;-------------------------------------------------------------------------------------------------alter database rename file /u01/app/oracle/oradata/orcl/temp01.dbf to /u01/app/oracle/oradata/kill/tempXXX.dbf;SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@testdb 11gbak]$执行：SQL> alter database rename file /u01/app/oracle/oradata/orcl/temp01.dbf to /u01/app/oracle/oradata/kill/temp01.dbf;Database altered.SQL> select name from v$tempfile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/kill/temp01.dbfSQL>  7.4 打开数据库(resetlogs)注意：这里恢复完成不要直接尝试打开数据库。因为我们是要升级！需要open upgrade（在下一个步骤说明）。SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00704: bootstrap process failureORA-39700: database must be opened with UPGRADE optionProcess ID: 6097Session ID: 22 Serial number: 63SQL> 八、目标库机升级数据库  --先用pfile启动到mount下：SQL> startup mount pfile=$ORACLE_HOME/dbs/init$ORACLE_SID.oraORACLE instance started.Total System Global Area  622149632 bytesFixed Size                  2255792 bytesVariable Size             230687824 bytesDatabase Buffers          385875968 bytesRedo Buffers                3330048 bytesDatabase mounted.SQL> 8.1  为预防升级测试过程中默认的归档空间不够，这里升级先把库开启为非归档模式。SQL> archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     1Next log sequence to archive   1Current log sequence           1SQL> SQL> alter database noarchivelog;Database altered.SQL>  archive log list;Database log mode              No Archive ModeAutomatic archival             DisabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     1Current log sequence           1SQL> 8.2 升级open数据库alter database open resetlogs upgrade; 用于不完全恢复alter database open upgrade;           用于完全恢复 SQL> alter database open resetlogs upgrade;alter database open resetlogs upgrade*ERROR at line 1:ORA-01139: RESETLOGS option only valid after an incomplete database recoverySQL> alter database open upgrade;Database altered.SQL> 8.3 执行升级脚本注意此步骤升级时间可能较长；SQL> spool upgrade.logSQL> @?/rdbms/admin/catupgrd.sql记录省略..........................................................SQL> SQL> SET SERVEROUTPUT OFFSQL> SET VERIFY ONSQL> commit;Commit complete.SQL> SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> SQL> SQL> SQL> DOCDOC>#######################################################################DOC>#######################################################################DOC>DOC>   The above sql script is the final step of the upgrade. PleaseDOC>   review any errors in the spool log file. If there are any errors inDOC>   the spool file, consult the Oracle Database Upgrade Guide forDOC>   troubleshooting recommendations.DOC>DOC>   Next restart for normal operation, and then run utlrp.sql toDOC>   recompile any invalid application objects.DOC>DOC>   If the source database had an older time zone version prior toDOC>   upgrade, then please run the DBMS_DST package.  DBMS_DST will upgradeDOC>   TIMESTAMP WITH TIME ZONE data to use the latest time zone file shippedDOC>   with Oracle.DOC>DOC>#######################################################################DOC>#######################################################################DOC>#SQL> SQL> Rem Set errorlogging offSQL> SET ERRORLOGGING OFF;SQL> SQL> REM END OF CATUPGRD.SQLSQL> SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.SQL> REM                This forces user to start a new sqlplus session in orderSQL> REM                to connect to the upgraded db.SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@testdb dbs]$                    [oracle@testdb dbs]$--脚本执行完毕后，自动关闭了数据库,经过以上操作，便可以打开数据库了。九、 目标库升级后操作 正常启库：[oracle@testdb dbs]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 15:23:03 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area  622149632 bytesFixed Size                  2255792 bytesVariable Size             230687824 bytesDatabase Buffers          385875968 bytesRedo Buffers                3330048 bytesDatabase mounted.Database opened.SQL>SQL> set lines 200   SQL> show parameter spfile;NAME                                 TYPE                   VALUE------------------------------------ ---------------------- ------------------------------spfile                               string                 9.1 创建spfile文件SQL> create spfile from pfile;File created.SQL> show parameter spfile;NAME                                 TYPE                   VALUE------------------------------------ ---------------------- ------------------------------spfile                               string                 SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL>  SQL> startupORACLE instance started.Total System Global Area  622149632 bytesFixed Size                  2255792 bytesVariable Size             230687824 bytesDatabase Buffers          385875968 bytesRedo Buffers                3330048 bytesDatabase mounted.Database opened.SQL> SQL> set lines 200SQL> show parameter spfileNAME                                 TYPE                   VALUE------------------------------------ ---------------------- ------------------------------spfile                               string                 /u01/app/oracle/product/11.2.0                                                            /dbhome_1/dbs/spfilekill.oraSQL> 9.2 执行EXECUTE dbms_stats.gather_dictionary_stats收集数据字典统计信息;SQL> exec dbms_stats.gather_dictionary_stats;PL/SQL procedure successfully completed.SQL>   9.3 重新编译 @?/rdbms/admin/utlrp.sqlSQL>  @?/rdbms/admin/utlrp.sqlTIMESTAMP---------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_BGN  2017-12-31 15:33:44DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalidDOC>   objects in the database. Recompilation time is proportional to theDOC>   number of invalid objects in the database, so this command may takeDOC>   a long time to execute on a database with a large number of invalidDOC>   objects.DOC>DOC>   Use the following queries to track recompilation progress:DOC>DOC>   1. Query returning the number of invalid objects remaining. ThisDOC>      number should decrease with time.DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);DOC>DOC>   2. Query returning the number of objects compiled so far. This numberDOC>      should increase with time.DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;DOC>DOC>   This script automatically chooses serial or parallel recompilationDOC>   based on the number of CPUs available (parameter cpu_count) multipliedDOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).DOC>   On RAC, this number is added across all RAC nodes.DOC>DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallelDOC>   recompilation. Jobs are created without instance affinity so that theyDOC>   can migrate across RAC nodes. Use the following queries to verifyDOC>   whether UTL_RECOMP jobs are being created and run correctly:DOC>DOC>   1. Query showing jobs created by UTL_RECOMPDOC>         SELECT job_name FROM dba_scheduler_jobsDOC>            WHERE job_name like UTL_RECOMP_SLAVE_%;DOC>DOC>   2. Query showing UTL_RECOMP jobs that are runningDOC>         SELECT job_name FROM dba_scheduler_running_jobsDOC>            WHERE job_name like UTL_RECOMP_SLAVE_%;DOC>#PL/SQL procedure successfully completed.TIMESTAMP------------------------------------------------------------------COMP_TIMESTAMP UTLRP_END  2017-12-31 15:35:35DOC> The following query reports the number of objects that have compiledDOC> with errors.DOC>DOC> If the number is higher than expected, please examine the errorDOC> messages reported with each object (using SHOW ERRORS) to see if theyDOC> point to system misconfiguration or resource constraints that must beDOC> fixed before attempting to recompile these objects.DOC>#OBJECTS WITH ERRORS-------------------                  0DOC> The following query reports the number of errors caught duringDOC> recompilation. If this number is non-zero, please query the errorDOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errorsDOC> are due to misconfiguration or resource constraints that must beDOC> fixed before objects can compile successfully.DOC>#ERRORS DURING RECOMPILATION---------------------------                          0Function created.PL/SQL procedure successfully completed.Function dropped.PL/SQL procedure successfully completed.SQL>9.4 查看失效对象SELECT count(*) FROM dba_invalid_objects;如果失效对象为升级之前的失效对象，升级过程中没有使对象失效。        SQL> SELECT count(*) FROM dba_invalid_objects;  COUNT(*)----------         1SQL> col owner for a10SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_invalid_objects;OWNER      OBJECT_NAME               OBJECT_TYPE                            STATUS---------- ------------------------- -------------------------------------- --------------SH         FWEEK_PSCAT_SALES_MV      MATERIALIZED VIEW                      INVALIDSQL>  --源库查询：SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where object_name=FWEEK_PSCAT_SALES_MV;OWNER                          OBJECT_NAME               OBJECT_TYPE         STATUS------------------------------ ------------------------- ------------------- -------SH                             FWEEK_PSCAT_SALES_MV      TABLE               VALIDSH                             FWEEK_PSCAT_SALES_MV      MATERIALIZED VIEW   VALIDSQL> --目标库查询：SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where object_name=FWEEK_PSCAT_SALES_MV;OWNER      OBJECT_NAME               OBJECT_TYPE                            STATUS---------- ------------------------- -------------------------------------- --------------SH         FWEEK_PSCAT_SALES_MV      MATERIALIZED VIEW                      INVALIDSH         FWEEK_PSCAT_SALES_MV      TABLE                                  VALIDSQL>  --目标库重新编译：QL> alter MATERIALIZED VIEW sh.FWEEK_PSCAT_SALES_MV compile;Materialized view altered.SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where object_name=FWEEK_PSCAT_SALES_MV;OWNER      OBJECT_NAME               OBJECT_TYPE                            STATUS---------- ------------------------- -------------------------------------- --------------SH         FWEEK_PSCAT_SALES_MV      MATERIALIZED VIEW                      VALIDSH         FWEEK_PSCAT_SALES_MV      TABLE                                  VALID--执行utluiobj.sql脚本列出无效对象[oracle@testdb admin]$ pwd/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin[oracle@testdb admin]$ [oracle@testdb admin]$ ls utluiobj.sqlutluiobj.sql[oracle@testdb admin]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 16:15:49 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> @utluiobj.sql.Oracle Database 11.2 Post-Upgrade Invalid Objects Tool 12-31-2017 16:16:00.This tool lists post-upgrade invalid objects that were not invalidprior to upgrade (it ignores pre-existing pre-upgrade invalid objects)..Owner                     Object Name                     Object Type.PL/SQL procedure successfully completed.SQL>   9.5 运行升级后检查脚本@?/rdbms/admin/utlu112s.sqlSQL> @?/rdbms/admin/utlu112s.sql.Oracle Database 11.2 Post-Upgrade Status Tool           12-31-2017 15:39:37.Component                               Current      Version     Elapsed TimeName                                    Status       Number      HH:MM:SS.Oracle Server.                                         VALID      11.2.0.4.0  00:12:16JServer JAVA Virtual Machine.                                         VALID      11.2.0.4.0  00:09:05Oracle Workspace Manager.                                         VALID      11.2.0.4.0  00:00:35OLAP Analytic Workspace.                                         VALID      11.2.0.4.0  00:01:16OLAP Catalog.                                         VALID      11.2.0.4.0  00:00:47Oracle OLAP API.                                         VALID      11.2.0.4.0  00:00:33Oracle Enterprise Manager.                                         VALID      11.2.0.4.0  00:07:43Oracle XDK.                                         VALID      11.2.0.4.0  00:01:31Oracle Text.                                         VALID      11.2.0.4.0  00:00:50Oracle XML Database.                                         VALID      11.2.0.4.0  00:04:39Oracle Database Java Packages.                                         VALID      11.2.0.4.0  00:00:16Oracle Multimedia.                                         VALID      11.2.0.4.0  00:04:04Spatial.                                         VALID      11.2.0.4.0  00:04:47Oracle Expression Filter.                                         VALID      11.2.0.4.0  00:00:13Oracle Rule Manager.                                         VALID      11.2.0.4.0  00:00:10Final Actions.                                                                00:00:26Total Upgrade Time: 00:49:24PL/SQL procedure successfully completed.SQL> 9.6 数据库层面应用PSUcd $ORACLE_HOME/rdbms/admin/SQL> @catbundle.sql psu apply[oracle@testdb ~]$ cd $ORACLE_HOME/rdbms/admin/[oracle@testdb admin]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 15:40:51 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> @catbundle.sql psu apply省略....................................................SQL> COMMIT;Commit complete.SQL> SPOOL offSQL> SET echo offCheck the following log file for errors:/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCL_APPLY_2017Dec31_15_41_00.logSQL>  --检查opatch补丁相关信息：[oracle@testdb dbhome_1]$ cd $ORACLE_HOME/OPatch[oracle@testdb OPatch]$ ./opatch lspatches24732075;Database Patch Set Update : 11.2.0.4.170418 (24732075)OPatch succeeded.[oracle@testdb OPatch]$[oracle@testdb OPatch]$ ./opatch lsinventoryOracle Interim Patch Installer version 11.2.0.3.12Copyright (c) 2017, Oracle Corporation.  All rights reserved.Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1Central Inventory : /u01/app/oraInventory   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.locOPatch version    : 11.2.0.3.12OUI version       : 11.2.0.4.0Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-31_15-44-55PM_1.logLsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2017-12-31_15-44-55PM.txt--------------------------------------------------------------------------------Local Machine Information::Hostname: testdbARU platform id: 226ARU platform description:: Linux x86-64Installed Top-level Products (1): Oracle Database 11g                                                  11.2.0.4.0There are 1 products installed in this Oracle Home.Interim patches (1) :Patch  24732075     : applied on Sun Dec 31 13:10:55 CST 2017Unique Patch ID:  21176096Patch description:  "Database Patch Set Update : 11.2.0.4.170418 (24732075)"   Created on 22 Feb 2017, 21:40:49 hrs PST8PDTSub-patch  24006111; "Database Patch Set Update : 11.2.0.4.161018 (24006111)"Sub-patch  23054359; "Database Patch Set Update : 11.2.0.4.160719 (23054359)"Sub-patch  22502456; "Database Patch Set Update : 11.2.0.4.160419 (22502456)"Sub-patch  21948347; "Database Patch Set Update : 11.2.0.4.160119 (21948347)"Sub-patch  21352635; "Database Patch Set Update : 11.2.0.4.8 (21352635)"Sub-patch  20760982; "Database Patch Set Update : 11.2.0.4.7 (20760982)"Sub-patch  20299013; "Database Patch Set Update : 11.2.0.4.6 (20299013)"Sub-patch  19769489; "Database Patch Set Update : 11.2.0.4.5 (19769489)"Sub-patch  19121551; "Database Patch Set Update : 11.2.0.4.4 (19121551)"Sub-patch  18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)"Sub-patch  18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"Sub-patch  17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"   Bugs fixed:17288409, 21051852, 24316947, 17811429, 17205719, 18607546, 20506699     17816865, 17922254, 23330119, 17754782, 16934803, 13364795, 1731172817284817, 17441661, 24560906, 16992075, 17446237, 14015842, 1997256921756677, 17375354, 20925795, 21538558, 17449815, 19463897, 13866822     17235750, 17982555, 17478514, 18317531, 14338435, 18235390, 2080358313944971, 20142975, 17811789, 16929165, 18704244, 20506706, 1754697320334344, 14054676, 17088068, 17346091, 18264060, 17343514, 21538567     19680952, 18471685, 19211724, 13951456, 21847223, 16315398, 1874413916850630, 23177648, 19049453, 18673304, 17883081, 19915271, 1864141918262334, 17006183, 16065166, 18277454, 16833527, 10136473, 18051556     17865671, 17852463, 18554871, 17853498, 18334586, 17551709, 1758848019827973, 17344412, 17842825, 18828868, 17025461, 11883252, 1360909817239687, 17602269, 19197175, 18316692, 22195457, 17313525, 12611721     19544839, 18964939, 17600719, 18191164, 19393542, 17571306, 2077715018482502, 19466309, 22243719, 17040527, 17165204, 18098207, 1678570817465741, 17174582, 16180763, 12982566, 16777840, 19463893, 2219546516875449, 12816846, 22148226, 17237521, 6599380, 19358317, 25505394     17811438, 17811447, 17945983, 21983325, 18762750, 16912439, 1718472118061914, 17282229, 18331850, 18202441, 17082359, 18723434, 2197232019554106, 25505371, 14034426, 18339044, 19458377, 17752995, 20448824     17891943, 17258090, 17767676, 16668584, 18384391, 17040764, 1738138415913355, 18356166, 14084247, 20596234, 20506715, 21756661, 1385312618203837, 14245531, 16043574, 21756699, 22195441, 17848897, 17877323     21453153, 17468141, 20861693, 17786518, 17912217, 17037130, 1695638018155762, 17478145, 17394950, 18641461, 18189036, 18619917, 1702742621352646, 16268425, 24476274, 22195492, 19584068, 18436307, 22507210     17265217, 17634921, 13498382, 21526048, 19258504, 20004087, 1744367122195485, 18000422, 22321756, 20004021, 17571039, 21067387, 2290513016344544, 18009564, 14354737, 21286665, 18135678, 18614015, 20441797     18362222, 17835048, 16472716, 17936109, 17050888, 14010183, 1732541318747196, 17761775, 16721594, 17082983, 20067212, 21179898, 1730227718084625, 15990359, 24842886, 18203835, 17297939, 17811456, 22380919     16731148, 21168487, 14133975, 13829543, 17215560, 17694209, 1738517818091059, 8322815, 17586955, 17201159, 17655634, 18331812, 1973050818868646, 17648596, 16220077, 16069901, 17348614, 17393915, 17274537     17957017, 18096714, 17308789, 18436647, 14285317, 19289642, 1476482917622427, 18328509, 16943711, 22195477, 14368995, 22502493, 1734667118996843, 17783588, 21343838, 16618694, 17672719, 18856999, 18783224     17851160, 17546761, 17798953, 18273830, 22092979, 16596890, 1997256616384983, 17726838, 22296366, 17360606, 22321741, 13645875, 1819953716542886, 21787056, 17889549, 14565184, 17071721, 17610798, 2029901521343897, 22893153, 20657441, 17397545, 18230522, 16360112, 19769489     12905058, 18641451, 12747740, 18430495, 17016369, 17042658, 1460278817551063, 19972568, 21517440, 18508861, 19788842, 14657740, 1733280013837378, 19972564, 17186905, 18315328, 19699191, 17437634, 22353199     18093615, 19006849, 19013183, 17296856, 18674024, 17232014, 1685529217762296, 14692762, 21051840, 17705023, 22507234, 19121551, 2133026419854503, 21868720, 19309466, 18681862, 20558005, 18554763, 17390160     18456514, 16306373, 13955826, 18139690, 17501491, 17752121, 2166862717299889, 17889583, 18673325, 19721304, 18293054, 17242746, 1795123318094246, 17649265, 19615136, 17011832, 16870214, 17477958, 18522509     20631274, 16091637, 17323222, 16595641, 16524926, 18228645, 1828256217596908, 18031668, 17156148, 16494615, 22683225, 17545847, 2509365617655240, 24528741, 17614134, 13558557, 17341326, 17891946, 17716305     22657942, 18440095, 16392068, 19271443, 21351877, 18092127, 1761422718440047, 16903536, 14106803, 18973907, 18673342, 25505382, 1903286717389192, 17612828, 16194160, 17006570, 25369547, 25505407, 17721717     17390431, 17570240, 16863422, 18325460, 19727057, 16422541, 1997257017267114, 18244962, 21538485, 18765602, 18203838, 16198143, 1724657614829250, 17835627, 18247991, 14458214, 21051862, 16692232, 17786278     17227277, 24476265, 16042673, 16314254, 16228604, 16837842, 1739368323536835, 17787259, 20331945, 20074391, 15861775, 16399083, 1801851522683212, 18260550, 21051858, 17080436, 16613964, 17036973, 16579084     24433711, 18384537, 18280813, 20296213, 16901385, 15979965, 2333012418441944, 16450169, 9756271, 17892268, 11733603, 16285691, 1758706321343775, 18180390, 16538760, 18193833, 21387964, 21051833, 17238511     17824637, 16571443, 18306996, 14852021, 17853456, 18674047, 1236406124411921, 22195448--------------------------------------------------------------------------------OPatch succeeded.[oracle@testdb OPatch]$

--查询dba_registry_history:

上述就是小编为大家分享的Oracle数据库异机升级是怎样的了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注行业资讯频道。