这期内容当中小编将会给大家带来有关Oracle数据库异机升级是怎样的，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。
架构介绍：
源库：单实例 ip:192.168.56.11 ORCLE_SID:orcl db_nme:orcl 版本：10.2.0.5.0
os: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.net/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 version
OPatch Version: 11.2.0.3.4
OPatch succeeded.
[oracle@testdb OPatch]$
上传opatch包及psu包如下：
[oracle@testdb ~]$ ls
database 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 version
OPatch Version: 11.2.0.3.12
OPatch succeeded.
[oracle@testdb OPatch]$
应用psu:
[oracle@testdb ~]$ ls
database 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]$ ls
p24732075_112040_Linux-x86-64.zip
[oracle@testdb soft]$
[oracle@testdb soft]$ unzip p24732075_112040_Linux-x86-64.zip
省略…………………………..
[oracle@testdb soft]$ ls
24732075 p24732075_112040_Linux-x86-64.zip PatchSearch.xml
[oracle@testdb soft]$
[oracle@testdb 24732075]$ ls
17478514 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.12
Copyright (c) 2017, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-27_21-51-47PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
[oracle@testdb 24732075]$
开始应用补丁：
[oracle@testdb 24732075]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2017, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-27_21-54-33PM_1.log
Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 17478514 18031668 18522509 19121551 19769489 20299013 20760982 21352635 21948347 22502456 23054359 24006111 24732075
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle 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]: y
Please 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]
y
User Responded with: Y
Backing up files…
Applying sub-patch '17478514' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
Patching 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.log
OPatch succeeded.
[oracle@testdb 24732075]$
[oracle@testdb OPatch]$ ./opatch lspatches
24732075;Database Patch Set Update : 11.2.0.4.170418 (24732075)
OPatch succeeded.
[oracle@testdb OPatch]$
三、 目标库机环境变量检查
[oracle@testdb ~]$ cat .bash_profile
# .bash_profile
export ORACLE_SID=kill
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export NLS_LANG="american_america.ZHS16GBK"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:Mi:SS"
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export 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/adump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump
mkdir -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/oracle
The authenticity of host '192.168.56.11 (192.168.56.11)' can't 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)? yes
Warning: Permanently added '192.168.56.11' (RSA) to the list of known hosts.
oracle@192.168.56.11's password:
utlu112i.sql 100% 220KB 220.5KB/s 00:00
[oracle@testdb admin]$
5.2 源库机执行utlu112i.sql进行升级前检查
[oracle@rhel ~]$ ls
patch soft utlu112i.sql
[oracle@rhel ~]$
[oracle@rhel ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Nov 23 16:32:27 2017
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> spool upgrade.info
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 11-23-2017 16:32:53
Script 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 to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
SQL> spool off
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@rhel ~]$
[oracle@rhel ~]$ ls
patch soft upgrade.info utlu112i.sql
[oracle@rhel ~]$
5.3 根据检查结果调整源库机数据库
5.3.1源库创建pfile并传到目标库：
SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u01/app/oracle/product/10.2.0
.1/db_1/dbs/spfileorcl.ora
SQL>
SQL>
SQL> create pfile from spfile;
File created.
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With 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/oracle
The authenticity of host '192.168.56.28 (192.168.56.28)' can't 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)? yes
Warning: Permanently added '192.168.56.28' (RSA) to the list of known hosts.
oracle@192.168.56.28's 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=478150656
kill.__java_pool_size=4194304
kill.__large_pool_size=4194304
kill.__shared_pool_size=109051904
kill.__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]$ ls
initkill.ora init.ora
[oracle@testdb dbs]$
5.3.3 源库执行PURGE DBA_RECYCLEBIN
SQL> 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 PROCEDURE
SQL> drop procedure calling;
SQL>
注意：升级之后执行utluiobj.sql
Use 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/bak
date=`date +%Y%m%d`
#Create pfile
sqlplus / as sysdba <<EOF
create pfile='$basedir/pfile$date.ora' from spfile;
EOF
#RMAN BACKUP
rman target / log=$basedir/backup_all_$date.log <<EOF
run{
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 -lrt
total 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.11's password:
arch_ORCL_20171019_5_1 100% 36MB 18.0MB/s 00:02
arch_ORCL_20171019_6_1 100% 4096 4.0KB/s 00:00
arch_ORCL_20171019_7_1 100% 2560 2.5KB/s 00:00
backup_all_20171019.log 100% 5042 4.9KB/s 00:00
ctl_ORCL_20171019_8_1 100% 6944KB 6.8MB/s 00:00
full_ORCL_20171019_1_1 100% 692MB 22.3MB/s 00:31
full_ORCL_20171019_2_1 100% 438MB 15.1MB/s 00:29
full_ORCL_20171019_3_1 100% 6944KB 6.8MB/s 00:01
full_ORCL_20171019_4_1 100% 96KB 96.0KB/s 00:00
pfile20171019.ora 100% 995 1.0KB/s 00:00
[oracle@testdb bak]$ ls -lrt
total 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]$ ls
initkill.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 sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 28 02:02:13 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora'
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2255792 bytes
Variable Size 230687824 bytes
Database Buffers 385875968 bytes
Redo Buffers 3330048 bytes
SQL>
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 2017
Copyright (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:21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/kill/control01.ctl
output file name=/u01/app/oracle/oradata/kill/control02.ctl
output file name=/u01/app/oracle/oradata/kill/control03.ctl
Finished restore at 2017-12-31 13:46:23
RMAN> alter database mount;
RMAN> crosscheck backupset;
RMAN> crosscheck backupset;
Starting implicit crosscheck backup at 2017-12-31 13:46:59
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 2017-12-31 13:46:59
Starting implicit crosscheck copy at 2017-12-31 13:46:59
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2017-12-31 13:47:00
searching for all files in the recovery area
cataloging files…
no files cataloged
using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/bak/full_ORCL_20171019_2_1 RECID=1 STAMP=957803210
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/bak/full_ORCL_20171019_3_1 RECID=2 STAMP=957803260
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/bak/full_ORCL_20171019_4_1 RECID=3 STAMP=957803264
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/bak/full_ORCL_20171019_1_1 RECID=4 STAMP=957803210
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/bak/arch_ORCL_20171019_6_1 RECID=5 STAMP=957803311
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/bak/arch_ORCL_20171019_5_1 RECID=6 STAMP=957803311
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/bak/arch_ORCL_20171019_7_1 RECID=7 STAMP=957803314
Crosschecked 7 objects
RMAN>
注意，如果备份集过期，则先注册，再删除，如下：示例
一般是两台主机的备份目录不一样，控制文件记录的备份集路径找不到对应的备份集，状态为'EXPIRED'，此时应该删除这些过期的备份集，catalog新的备份集，再次确认备份集有效性。
rman target / > catalog.log <<EOF
catalog 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;
EOF
7.4 RMAN恢复数据库
7.4.1 restore数据文件
如果数据文件存放目录已经更改，需要重命名还原。
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;
}
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;
> }
> EOF
RMAN>
RMAN> [oracle@testdb 11gbak]$
[oracle@testdb 11gbak]$
[oracle@testdb 11gbak]$ tail -200f restore.log
Recovery Manager: Release 11.2.0.4.0 – Production on Thu Dec 28 02:34:24 2017
Copyright (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 catalog
allocated channel: c1
channel c1: SID=21 device type=DISK
allocated channel: c2
channel c2: SID=24 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2017-12-31 13:52:59
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/kill/system01.dbf
channel c1: restoring datafile 00005 to /u01/app/oracle/oradata/kill/example01.dbf
channel c1: reading from backup piece /home/oracle/bak/full_ORCL_20171019_2_1
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00002 to /u01/app/oracle/oradata/kill/undotbs01.dbf
channel c2: restoring datafile 00003 to /u01/app/oracle/oradata/kill/sysaux01.dbf
channel c2: restoring datafile 00004 to /u01/app/oracle/oradata/kill/users01.dbf
channel c2: reading from backup piece /home/oracle/bak/full_ORCL_20171019_1_1
channel c1: piece handle=/home/oracle/bak/full_ORCL_20171019_2_1 tag=TAG20171019T162649
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:55
channel c2: piece handle=/home/oracle/bak/full_ORCL_20171019_1_1 tag=TAG20171019T162649
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:56
Finished restore at 2017-12-31 13:53:56
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/example01.dbf
released channel: c1
released channel: c2
RMAN>
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:19
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=38
channel ORA_DISK_1: reading from backup piece /home/oracle/bak/arch_ORCL_20171019_5_1
channel ORA_DISK_1: piece handle=/home/oracle/bak/arch_ORCL_20171019_5_1 tag=TAG20171019T162831
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_38_f4jyy9kb_.arc thread=1 sequence=38
channel 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=964187722
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=39
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=40
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=41
channel ORA_DISK_1: reading from backup piece /home/oracle/bak/arch_ORCL_20171019_6_1
channel ORA_DISK_1: piece handle=/home/oracle/bak/arch_ORCL_20171019_6_1 tag=TAG20171019T162831
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_39_f4jyyf2v_.arc thread=1 sequence=39
channel 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=964187725
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_40_f4jyyf1t_.arc thread=1 sequence=40
channel 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=964187725
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_41_f4jyyf3q_.arc thread=1 sequence=41
channel 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=964187725
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=42
channel ORA_DISK_1: reading from backup piece /home/oracle/bak/arch_ORCL_20171019_7_1
channel ORA_DISK_1: piece handle=/home/oracle/bak/arch_ORCL_20171019_7_1 tag=TAG20171019T162831
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_42_f4jyyg9r_.arc thread=1 sequence=42
channel 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=964187726
unable to find archived log
archived log thread=1 sequence=43
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/31/2017 13:55:28
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 43 and starting SCN of 1015325
RMAN>
7.4.2.2 recover database until scn xxxxxxx;
注意：这个scn根据上一步的日志信息获取。
RMAN> recover database until scn 1015325;
Starting recover at 2017-12-31 13:55:53
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-12-31 13:55:54
RMAN>
7.4.2.3 修改日志文件路径
注意：可以用UE列编辑模式快速处理下新的redo文件名字(即redoXXX.log改为对应的实际值)
new_dest=/u01/app/oracle/oradata/kill
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 ~]$ 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 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> 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 Production
With 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.log
7.4.3 修改临时文件路径
注意：同样处理下新的temp文件名字(即tempXXX.dbf改为对应的实际值)。
new_dest=/u01/app/oracle/oradata/kill
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]$ 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 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> 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 Production
With 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.dbf
SQL>
7.4 打开数据库(resetlogs)
注意：这里恢复完成不要直接尝试打开数据库。因为我们是要升级！需要open upgrade（在下一个步骤说明）。
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 6097
Session ID: 22 Serial number: 63
SQL>
八、目标库机升级数据库
–先用pfile启动到mount下：
SQL> startup mount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora'
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2255792 bytes
Variable Size 230687824 bytes
Database Buffers 385875968 bytes
Redo Buffers 3330048 bytes
Database mounted.
SQL>
8.1 为预防升级测试过程中默认的归档空间不够，这里升级先把库开启为非归档模式。
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL>
SQL> alter database noarchivelog;
Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 1
SQL>
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 recovery
SQL> alter database open upgrade;
Database altered.
SQL>
8.3 执行升级脚本
注意此步骤升级时间可能较长；
SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
记录省略……………………
…………………………….
SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON
SQL> commit;
Commit complete.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above sql script is the final step of the upgrade. Please
DOC> review any errors in the spool log file. If there are any errors in
DOC> the spool file, consult the Oracle Database Upgrade Guide for
DOC> troubleshooting recommendations.
DOC>
DOC> Next restart for normal operation, and then run utlrp.sql to
DOC> recompile any invalid application objects.
DOC>
DOC> If the source database had an older time zone version prior to
DOC> upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade
DOC> TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
DOC> with Oracle.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> REM END OF CATUPGRD.SQL
SQL>
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 order
SQL> REM to connect to the upgraded db.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@testdb dbs]$
[oracle@testdb dbs]$
–脚本执行完毕后，自动关闭了数据库,经过以上操作，便可以打开数据库了。
九、 目标库升级后操作
正常启库：
[oracle@testdb dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 15:23:03 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2255792 bytes
Variable Size 230687824 bytes
Database Buffers 385875968 bytes
Redo Buffers 3330048 bytes
Database 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> startup
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2255792 bytes
Variable Size 230687824 bytes
Database Buffers 385875968 bytes
Redo Buffers 3330048 bytes
Database mounted.
Database opened.
SQL>
SQL> set lines 200
SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———————- ——————————
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilekill.ora
SQL>
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.sql
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
—————————————————————————
COMP_TIMESTAMP UTLRP_BGN 2017-12-31 15:33:44
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> 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 number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> 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 parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
——————————————————————
COMP_TIMESTAMP UTLRP_END 2017-12-31 15:35:35
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
——————-
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
—————————
0
Function 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(*)
———-
1
SQL> col owner for a10
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_invalid_objects;
OWNER OBJECT_NAME OBJECT_TYPE STATUS
———- ————————- ————————————– ————–
SH FWEEK_PSCAT_SALES_MV MATERIALIZED VIEW INVALID
SQL>
–源库查询：
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 VALID
SH FWEEK_PSCAT_SALES_MV MATERIALIZED VIEW VALID
SQL>
–目标库查询：
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 INVALID
SH FWEEK_PSCAT_SALES_MV TABLE VALID
SQL>
–目标库重新编译：
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 VALID
SH 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.sql
utluiobj.sql
[oracle@testdb admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 16:15:49 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @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 invalid
prior 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.sql
SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 12-31-2017 15:39:37
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:12:16
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:09:05
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:35
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:01:16
OLAP Catalog
. VALID 11.2.0.4.0 00:00:47
Oracle OLAP API
. VALID 11.2.0.4.0 00:00:33
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:07:43
Oracle XDK
. VALID 11.2.0.4.0 00:01:31
Oracle Text
. VALID 11.2.0.4.0 00:00:50
Oracle XML Database
. VALID 11.2.0.4.0 00:04:39
Oracle Database Java Packages
. VALID 11.2.0.4.0 00:00:16
Oracle Multimedia
. VALID 11.2.0.4.0 00:04:04
Spatial
. VALID 11.2.0.4.0 00:04:47
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:13
Oracle Rule Manager
. VALID 11.2.0.4.0 00:00:10
Final Actions
. 00:00:26
Total Upgrade Time: 00:49:24
PL/SQL procedure successfully completed.
SQL>
9.6 数据库层面应用PSU
cd $ORACLE_HOME/rdbms/admin/
SQL> @catbundle.sql psu apply
[oracle@testdb ~]$ cd $ORACLE_HOME/rdbms/admin/
[oracle@testdb admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 15:40:51 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @catbundle.sql psu apply
省略……………………
……………………….
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCL_APPLY_2017Dec31_15_41_00.log
SQL>
–检查opatch补丁相关信息：
[oracle@testdb dbhome_1]$ cd $ORACLE_HOME/OPatch
[oracle@testdb OPatch]$ ./opatch lspatches
24732075;Database Patch Set Update : 11.2.0.4.170418 (24732075)
OPatch succeeded.
[oracle@testdb OPatch]$
[oracle@testdb OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2017, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-31_15-44-55PM_1.log
Lsinventory 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: testdb
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch 24732075 : applied on Sun Dec 31 13:10:55 CST 2017
Unique Patch ID: 21176096
Patch description: "Database Patch Set Update : 11.2.0.4.170418 (24732075)"
Created on 22 Feb 2017, 21:40:49 hrs PST8PDT
Sub-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, 17311728
17284817, 17441661, 24560906, 16992075, 17446237, 14015842, 19972569
21756677, 17375354, 20925795, 21538558, 17449815, 19463897, 13866822
17235750, 17982555, 17478514, 18317531, 14338435, 18235390, 20803583
13944971, 20142975, 17811789, 16929165, 18704244, 20506706, 17546973
20334344, 14054676, 17088068, 17346091, 18264060, 17343514, 21538567
19680952, 18471685, 19211724, 13951456, 21847223, 16315398, 18744139
16850630, 23177648, 19049453, 18673304, 17883081, 19915271, 18641419
18262334, 17006183, 16065166, 18277454, 16833527, 10136473, 18051556
17865671, 17852463, 18554871, 17853498, 18334586, 17551709, 17588480
19827973, 17344412, 17842825, 18828868, 17025461, 11883252, 13609098
17239687, 17602269, 19197175, 18316692, 22195457, 17313525, 12611721
19544839, 18964939, 17600719, 18191164, 19393542, 17571306, 20777150
18482502, 19466309, 22243719, 17040527, 17165204, 18098207, 16785708
17465741, 17174582, 16180763, 12982566, 16777840, 19463893, 22195465
16875449, 12816846, 22148226, 17237521, 6599380, 19358317, 25505394
17811438, 17811447, 17945983, 21983325, 18762750, 16912439, 17184721
18061914, 17282229, 18331850, 18202441, 17082359, 18723434, 21972320
19554106, 25505371, 14034426, 18339044, 19458377, 17752995, 20448824
17891943, 17258090, 17767676, 16668584, 18384391, 17040764, 17381384
15913355, 18356166, 14084247, 20596234, 20506715, 21756661, 13853126
18203837, 14245531, 16043574, 21756699, 22195441, 17848897, 17877323
21453153, 17468141, 20861693, 17786518, 17912217, 17037130, 16956380
18155762, 17478145, 17394950, 18641461, 18189036, 18619917, 17027426
21352646, 16268425, 24476274, 22195492, 19584068, 18436307, 22507210
17265217, 17634921, 13498382, 21526048, 19258504, 20004087, 17443671
22195485, 18000422, 22321756, 20004021, 17571039, 21067387, 22905130
16344544, 18009564, 14354737, 21286665, 18135678, 18614015, 20441797
18362222, 17835048, 16472716, 17936109, 17050888, 14010183, 17325413
18747196, 17761775, 16721594, 17082983, 20067212, 21179898, 17302277
18084625, 15990359, 24842886, 18203835, 17297939, 17811456, 22380919
16731148, 21168487, 14133975, 13829543, 17215560, 17694209, 17385178
18091059, 8322815, 17586955, 17201159, 17655634, 18331812, 19730508
18868646, 17648596, 16220077, 16069901, 17348614, 17393915, 17274537
17957017, 18096714, 17308789, 18436647, 14285317, 19289642, 14764829
17622427, 18328509, 16943711, 22195477, 14368995, 22502493, 17346671
18996843, 17783588, 21343838, 16618694, 17672719, 18856999, 18783224
17851160, 17546761, 17798953, 18273830, 22092979, 16596890, 19972566
16384983, 17726838, 22296366, 17360606, 22321741, 13645875, 18199537
16542886, 21787056, 17889549, 14565184, 17071721, 17610798, 20299015
21343897, 22893153, 20657441, 17397545, 18230522, 16360112, 19769489
12905058, 18641451, 12747740, 18430495, 17016369, 17042658, 14602788
17551063, 19972568, 21517440, 18508861, 19788842, 14657740, 17332800
13837378, 19972564, 17186905, 18315328, 19699191, 17437634, 22353199
18093615, 19006849, 19013183, 17296856, 18674024, 17232014, 16855292
17762296, 14692762, 21051840, 17705023, 22507234, 19121551, 21330264
19854503, 21868720, 19309466, 18681862, 20558005, 18554763, 17390160
18456514, 16306373, 13955826, 18139690, 17501491, 17752121, 21668627
17299889, 17889583, 18673325, 19721304, 18293054, 17242746, 17951233
18094246, 17649265, 19615136, 17011832, 16870214, 17477958, 18522509
20631274, 16091637, 17323222, 16595641, 16524926, 18228645, 18282562
17596908, 18031668, 17156148, 16494615, 22683225, 17545847, 25093656
17655240, 24528741, 17614134, 13558557, 17341326, 17891946, 17716305
22657942, 18440095, 16392068, 19271443, 21351877, 18092127, 17614227
18440047, 16903536, 14106803, 18973907, 18673342, 25505382, 19032867
17389192, 17612828, 16194160, 17006570, 25369547, 25505407, 17721717
17390431, 17570240, 16863422, 18325460, 19727057, 16422541, 19972570
17267114, 18244962, 21538485, 18765602, 18203838, 16198143, 17246576
14829250, 17835627, 18247991, 14458214, 21051862, 16692232, 17786278
17227277, 24476265, 16042673, 16314254, 16228604, 16837842, 17393683
23536835, 17787259, 20331945, 20074391, 15861775, 16399083, 18018515
22683212, 18260550, 21051858, 17080436, 16613964, 17036973, 16579084
24433711, 18384537, 18280813, 20296213, 16901385, 15979965, 23330124
18441944, 16450169, 9756271, 17892268, 11733603, 16285691, 17587063
21343775, 18180390, 16538760, 18193833, 21387964, 21051833, 17238511
17824637, 16571443, 18306996, 14852021, 17853456, 18674047, 12364061
24411921, 22195448
——————————————————————————–
OPatch succeeded.
[oracle@testdb OPatch]$
–查询dba_registry_history:
DBA_REGISTRY_HISTORY:
DBA_REGISTRY_HISTORY provides information about upgrades, downgrades, and critical patch updates that have been performed on the database.
SQL> col ACTION_TIME for a30
SQL> col ACTION for a20
SQL> col NAMESPACE for a15
SQL> col VERSION for a15
SQL> col BUNDLE_SERIES for a15
SQL> col COMMENTS for a50
SQL> select * from dba_registry_history;
ACTION_TIME ACTION NAMESPACE VERSION ID BUNDLE_SERIES COMMENTS
—————————— ——————– ————— ————— ———- ————— —————————-
19-OCT-17 02.48.11.467752 PM VIEW RECOMPILE 8289601 view recompilation
19-OCT-17 02.48.11.862564 PM UPGRADE SERVER 10.2.0.5.0 Upgraded from 10.2.0.1.0
31-DEC-17 02.55.55.820329 PM VIEW INVALIDATE 8289601 view invalidation
31-DEC-17 02.56.22.289206 PM UPGRADE SERVER 11.2.0.4.0 Upgraded from 10.2.0.5.0
31-DEC-17 03.41.37.543270 PM APPLY SERVER 11.2.0.4 170418 PSU PSU 11.2.0.4.170418
SQL>
到此，已完成数据库异机升级操作!
上述就是小编为大家分享的Oracle数据库异机升级是怎样的了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。
原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/203861.html