Oracle資料庫異機升級（10.2.0.5 --> 11.2.0.4）

不一樣的天空w發表於2017-12-31
Oracle資料庫
參考：http://www.cnblogs.com/jyzhao/p/4813324.html

架構介紹：
源庫：單例項  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/opatch2017-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/opatch2017-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 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/opatch2017-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/opatch2017-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>
到此，已完成資料庫異機升級操作!!!!
                        

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2149541/，如需轉載，請註明出處，否則將追究法律責任。

相關文章