Oracle 10.2.0.5 RMAN遷移并升級11.2.0.4一例

一、環境介紹

1. 源數據庫環境

操作系統版本: OEL 5.4 x64
數據庫版本  : 10.2.0.5 x64
數據庫sid名 : orcl

Oracle 10g 10.2.0.5（64bit）安裝目錄如下：

數據庫軟件：/u01/app/oracle/product/10.2.0/db_1
數據庫文件：/u01/app/oracle/oradata/orcl 
歸檔目錄：/u01/archivelog
RMAN目錄：/backup/dbbak/orabak

背景：一個老oracle10g數據庫,RMAN 50G, 不大，因服務器壞一塊盤，過保，計劃遷移數據庫并升級版本。

說明：源庫要做一些基礎環境的檢查，表空間，用戶名，數據文件以及是否有需要介質恢復的文件。
sqlplus / as sysdba > dbstatus.log <<EOF
select instance_name, status from v\$instance;
select dbid, open_mode from v\$database;
select file_name from dba_data_files;
select file_name from dba_temp_files;
select member from v\$logfile;
show parameter control
show parameter pfile
EOF

[oracle@db ~]$ cat dbstatus.log

SQL*Plus: Release 10.2.0.5.0 - Production on чǚ? 9? 18 14:40:57 2017

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
INSTANCE_NAME     STATUS
---------------- ------------
orcl         OPEN

SQL>
      DBID OPEN_MODE
---------- ----------
1226188361 READ WRITE

SQL>
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/tjoa
/u01/app/oracle/oradata/orcl/trswcm_data01.dbf
/u01/app/oracle/oradata/orcl/trswcmvideo_data01.dbf
/u01/app/oracle/oradata/orcl/wtt.dbf
/u01/app/oracle/oradata/orcl/user02.dbf
/u01/app/oracle/oradata/orcl/jtbzoa
/u01/app/oracle/oradata/orcl/ELINK.dbf
/u01/app/oracle/oradata/orcl/USER03.dbf
/u01/app/oracle/oradata/orcl/user04.dbf
/u01/app/oracle/oradata/orcl/users05.dbf

14 rows selected.

SQL>
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01.dbf
/u01/app/oracle/oradata/orcl/trswcm_temp01.dbf
/u01/app/oracle/oradata/orcl/trswcmvideo_temp01.dbf

SQL>
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log

SQL>
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time         integer     7
control_files                 string     /u01/app/oracle/oradata/orcl/c
                         ontrol01.ctl, /u01/app/oracle/
                         oradata/orcl/control02.ctl, /u
                         01/app/oracle/oradata/orcl/con
                         trol03.ctl
SQL>
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string     /u01/app/oracle/product/10.2.0
                         /db_1/dbs/spfileorcl.ora
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2. 目標數據庫環境

操作系統版本: RHEL 6.5 x64
數據庫版本  : 11.2.0.4 x64
數據庫sid名 : orcl

原來有數據庫，采用DBCA將數據庫刪除掉。
Oracle 11g 11.2.0.4（64bit）安裝目錄如下（未創建數據庫）

數據庫軟件：/u01/app/oracle/product/11.2.0/db_1
數據庫文件：/u01/app/oracle/oradata/orcl 
歸檔目錄：/u01/archivelog
RMAN目錄：/backup/dbbak/orabak

3. 升級思路

1）Oracle 10.2.0.2 以后才可以升級到11g 11.2.0.4。
2）創建Oracle 11.2.0.4數據庫相關目錄。
3) 在Oracle 10.2.0.5x64數據庫上執行Oracle 11.2.0.4 x64數據庫utlu112i.sql腳本。
4）備份Oracle 10.2.0.5x64數據庫，并傳到11g數據庫服務器上。
5）還原10g數據庫到11g庫上，并升級。

二、RMAN備份源10g數據庫

1. 在Oracle 10g庫上執行utlu112i.sql腳本

道先需要將11g $ORACLE_HOME/rdbms/admin下的utlu112i.sql腳本傳到10g的$ORACLE_HOME/rdbms/admin目錄下并執行。
并在10g上執行，這個腳本可以檢查升級前的一些信息，必須要執行，否則在恢復時會出現錯誤。

scp $ORACLE_HOME/rdbms/admin/utlu112i.sql 192.168.0.96:/home/oracle/ 

SQL> spool upgrade.info
SQL> @/home/oracle/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 09-18-2017 14:26:54
Script Version: 11.2.0.4.0 Build: 007
.
**********************************************************************
Database:
**********************************************************************
--> name:          ORCL
--> version:       10.2.0.5.0
--> compatible:    10.2.0.3.0
--> blocksize:     8192
--> platform:      Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 1480 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 837 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 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:
-- No update parameter changes are required.
.
**********************************************************************
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
--> 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.5.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 FKSOA has 3 INVALID objects.
.... USER ZWPORTAL has 1 INVALID objects.
.... USER XCJ has 1 INVALID objects.
.... USER XCJOANEW has 1 INVALID objects.
.... USER EDTEST has 1 INVALID objects.
.... USER NYTOA has 1 INVALID objects.
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.
WARNING: --> JOB_QUEUE_PROCESS value must be updated
.... Your current setting of "10" is too low.

.... Starting with Oracle Database 11g Release 2 (11.2), setting
.... JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and
.... DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES
.... to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were
.... unaffected and would still run. This parameter must be updated to
.... a value greater than 16  (default value is 1000) prior to upgrade.
.... Not doing so will affect the running of utlrp.sql after the upgrade
.
**********************************************************************
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>

SQL> spool off

#執行過程中無錯誤,
#說明：應用根據執行的情況需要進行如下處理。

（1）原庫存在無效對象，需要運行utlrp.sql 腳本，重新編譯無效對象

SQL> @?/rdbms/admin/utlrp.sql;

重新執行 @/home/oracle/utlu112i.sql，查看是否重新編譯,

（2）執行PURGE DBA_RECYCLEBIN

SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.

（3）收集系統信息EXECUTE dbms_stats.gather_dictionary_stats;

在升級前收集字典統計信息，否則預升級工具( utlu102i.sql)會花費更長時間
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
 
PL/SQL procedure successfully completed.

2. 備份源數據庫

備份腳本內容

#!/bin/bash
# ScriptName:rmanbakup.sh
# Usage: backup all files in oracle user environment.
# Author: koumm
# Creation: 2017-07-31
# Version: 1.0.0

#Define variable <You may need to change the value of basedir.>
basedir=/u01/orabak
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

說明：記錄數據庫DBID(DBID=1349744318)，恢復時有用。

3. 將/u01/orabak目錄拷貝到11g數據庫服務器上。

scp -r orabak xxxx:/u01/

[root@mholddb orcl]# ll -h /backup/dbbak/orabak/
總用量 55G
-rw-r----- 1 oracle oinstall  42M 9月  18 20:07 arch_ORCL_20170918_17_1
-rw-r----- 1 oracle oinstall  13M 9月  18 20:15 arch_ORCL_20170918_18_1
-rw-r----- 1 oracle oinstall 3.0K 9月  18 20:07 arch_ORCL_20170918_19_1
-rw-r--r-- 1 oracle oinstall 6.2K 9月  18 20:12 backup_all_20170918.log
-rw-r----- 1 oracle oinstall 9.9M 9月  18 20:07 ctl_ORCL_20170918_20_1
-rw-r----- 1 oracle oinstall  20G 9月  18 20:11 full_ORCL_20170918_11_1
-rw-r----- 1 oracle oinstall 3.1G 9月  18 20:12 full_ORCL_20170918_12_1
-rw-r----- 1 oracle oinstall  18G 9月  18 20:07 full_ORCL_20170918_13_1
-rw-r----- 1 oracle oinstall  14G 9月  18 20:15 full_ORCL_20170918_14_1
-rw-r----- 1 oracle oinstall 9.9M 9月  18 20:07 full_ORCL_20170918_15_1
-rw-r----- 1 oracle oinstall  96K 9月  18 20:03 full_ORCL_20170918_16_1
-rw-r--r-- 1 oracle oinstall 1.1K 9月  18 20:21 pfile20170918.ora

三、11g數據庫恢復準備

1. 創建11g數據庫基本目錄

# su - oracle

mkdir -p /u01/app/oracle/admin/orcl/{adump,dpdump,pfile,scripts} 
mkdir -p /u01/app/oracle/oradata/orcl 
mkdir -p /u01/app/oracle/flash_recovery_area/ORCL

2. 創建規檔日志文件

mkdir -p /u01/archivelog

四、恢復數據庫

1. 恢復參數文件

說明：由于10g實例名與11g實例名與數據庫目錄一致，不需要手動創建參數文件與控制文件。

# vi pfile20170801.ora

orcl.__db_cache_size=1056964608
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__shared_pool_size=503316480
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.compatible='10.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fast_start_mttr_target=60
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/archivelog'
*.nls_territory='CHINA'
*.open_cursors=3000
*.pga_aggregate_target=848297984
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.diagnostic_dest='/u01/app/oracle'

注意下sga和pga的設定是否符合目的機器的配置
*.sga_target=629145600
*.pga_aggregate_target=25165824
刪除*.background_dump_dest和*.user_dump_dest
加上
*.diagnostic_dest='/u01/app/oracle'

修改后復制一份到$ORACLE_HOME/dbs/init$ORACLE_SID.ora

[oracle@mholddb orabak]$ cp pfile20170918.ora /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora

2. nomount啟動數據庫

[oracle@mholddb orabak]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 18 20:22:21 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora';

Oracle 實例已啟動

3. 恢復控制文件

RMAN> restore controlfile from '/backup/dbbak/orabak/ctl_ORCL_20170918_20_1';

Starting restore at 18-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=197 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/oradata/orcl/control02.ctl
output file name=/u01/app/oracle/oradata/orcl/control03.ctl
Finished restore at 18-SEP-17

RMAN>

4. 恢復數據庫

RMAN> shutdown immediate;

Oracle instance shut down

原備份日志backup_all_20170801.log中有DBID記錄。

RMAN> set DBID=1226188361

executing command: SET DBID

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1219260416 bytes

Fixed Size                     2252744 bytes
Variable Size                704643128 bytes
Database Buffers             503316480 bytes
Redo Buffers                   9048064 bytes

RMAN>

RMAN> delete noprompt expired backupset;
RMAN> crosscheck backupset;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_12_1 RECID=11 STAMP=955031770
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_11_1 RECID=12 STAMP=955031784
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_13_1 RECID=13 STAMP=955032406
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_15_1 RECID=14 STAMP=955035803
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_16_1 RECID=15 STAMP=955035849
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_14_1 RECID=16 STAMP=955035323
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/arch_ORCL_20170918_18_1 RECID=17 STAMP=955036896
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/arch_ORCL_20170918_17_1 RECID=18 STAMP=955036896
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/arch_ORCL_20170918_19_1 RECID=19 STAMP=955036900
Crosschecked 9 objects

RMAN> restore database;  

Starting restore at 18-SEP-17
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/tjoa
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/orcl/USER03.dbf
channel ORA_DISK_1: reading from backup piece /backup/dbbak/orabak/full_ORCL_20170918_12_1
channel ORA_DISK_1: piece handle=/backup/dbbak/orabak/full_ORCL_20170918_12_1 tag=TAG20170918T143606
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/orcl/jtbzoa
channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/orcl/users05.dbf
channel ORA_DISK_1: reading from backup piece /backup/dbbak/orabak/full_ORCL_20170918_11_1
channel ORA_DISK_1: piece handle=/backup/dbbak/orabak/full_ORCL_20170918_11_1 tag=TAG20170918T143606
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:16
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/orcl/wtt.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/orcl/user04.dbf
channel ORA_DISK_1: reading from backup piece /backup/dbbak/orabak/full_ORCL_20170918_13_1
channel ORA_DISK_1: piece handle=/backup/dbbak/orabak/full_ORCL_20170918_13_1 tag=TAG20170918T143606
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/trswcm_data01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/orcl/trswcmvideo_data01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/orcl/user02.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/orcl/ELINK.dbf
channel ORA_DISK_1: reading from backup piece /backup/dbbak/orabak/full_ORCL_20170918_14_1
channel ORA_DISK_1: piece handle=/backup/dbbak/orabak/full_ORCL_20170918_14_1 tag=TAG20170918T143606
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
Finished restore at 18-SEP-17
RMAN>

RMAN> list backup of archivelog all;

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
17      12.70M     DISK        00:00:02     18-SEP-17     
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: TAG20170918T160134
        Piece Name: /backup/dbbak/orabak/arch_ORCL_20170918_18_1

  List of Archived Logs in backup set 17
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    30547   1904894964 18-SEP-17 1904899341 18-SEP-17
  1    30548   1904899341 18-SEP-17 1904899351 18-SEP-17
  1    30549   1904899351 18-SEP-17 1904899359 18-SEP-17

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
18      41.45M     DISK        00:00:06     18-SEP-17     
        BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: TAG20170918T160134
        Piece Name: /backup/dbbak/orabak/arch_ORCL_20170918_17_1

  List of Archived Logs in backup set 18
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    30546   1904882200 18-SEP-17 1904894964 18-SEP-17

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
19      2.50K      DISK        00:00:01     18-SEP-17     
        BP Key: 19   Status: AVAILABLE  Compressed: NO  Tag: TAG20170918T160134
        Piece Name: /backup/dbbak/orabak/arch_ORCL_20170918_19_1

  List of Archived Logs in backup set 19
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    30550   1904899359 18-SEP-17 1904899365 18-SEP-17
  1    30551   1904899365 18-SEP-17 1904899370 18-SEP-17

查看源庫在備份完成的SCN。 

RMAN> recover database until scn 1904899370;

Starting recover at 18-SEP-17
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=30547
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=30548
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=30549
channel ORA_DISK_1: reading from backup piece /backup/dbbak/orabak/arch_ORCL_20170918_18_1
channel ORA_DISK_1: piece handle=/backup/dbbak/orabak/arch_ORCL_20170918_18_1 tag=TAG20170918T160134
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/archivelog1_30547_698407628.dbf thread=1 sequence=30547
archived log file name=/u01/archivelog1_30548_698407628.dbf thread=1 sequence=30548
archived log file name=/u01/archivelog1_30549_698407628.dbf thread=1 sequence=30549
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=30550
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=30551
channel ORA_DISK_1: reading from backup piece /backup/dbbak/orabak/arch_ORCL_20170918_19_1
channel ORA_DISK_1: piece handle=/backup/dbbak/orabak/arch_ORCL_20170918_19_1 tag=TAG20170918T160134
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/archivelog1_30550_698407628.dbf thread=1 sequence=30550
archived log file name=/u01/archivelog1_30551_698407628.dbf thread=1 sequence=30551
media recovery complete, elapsed time: 00:00:01
Finished recover at 18-SEP-17
RMAN>

5. 升級模式升級數據庫

1）升級模式resetlogs啟動數據庫

[oracle@mholddb ~]$ sqlplus /nolog
SQL> conn / as sysdba;

SQL> alter database open resetlogs upgrade;
Database altered.

SQL>

2）重建temp臨時表空間數據文件

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 100m reuse autoextend on next 10m maxsize 1000m;
Tablespace altered.

6. upgrade模式下運行腳本：catupgrd.sql

SQL> @?/rdbms/admin/catupgrd.sql

... 大約執行了25分鐘。

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

從 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 斷開

7. 執行utlu112s.sql腳本

這個腳本顯示升級過程的一個摘要。不需要在upgrade 模式下。

[oracle@mholddb ~]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 21:03:52 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             570428576 bytes
Database Buffers         1023410176 bytes
Redo Buffers                7319552 bytes
Database mounted.
Database opened.

SQL> @?/rdbms/admin/utlu112s.sql

.
Oracle Database 11.2 Post-Upgrade Status Tool           09-18-2017 21:04:53
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.4.0  00:06:07
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.4.0  00:03:40
Oracle Workspace Manager
.                                         VALID      11.2.0.4.0  00:00:19
OLAP Analytic Workspace
.                                         VALID      11.2.0.4.0  00:01:07
OLAP Catalog
.                                         VALID      11.2.0.4.0  00:00:22
Oracle OLAP API
.                                         VALID      11.2.0.4.0  00:00:12
Oracle XDK
.                                         VALID      11.2.0.4.0  00:01:11
Oracle Text
.                                         VALID      11.2.0.4.0  00:00:23
Oracle XML Database
.                                         VALID      11.2.0.4.0  00:02:06
Oracle Database Java Packages
.                                         VALID      11.2.0.4.0  00:00:06
Oracle Multimedia
.                                         VALID      11.2.0.4.0  00:01:50
Spatial
.                                         VALID      11.2.0.4.0  00:03:08
Oracle Expression Filter
.                                         VALID      11.2.0.4.0  00:00:05
Oracle Rule Manager
.                                         VALID      11.2.0.4.0  00:00:04
Final Actions
.                                                                00:00:14
Total Upgrade Time: 00:21:01

PL/SQL procedure successfully completed.

SQL>

PL/SQL 過程已成功完成。

SQL>

8. 編譯無效對象

# 查看無效對象數量

SQL> select count(*) from dba_invalid_objects;

  COUNT(*)
----------
      1275

# 編譯無效對象，如果與之前的無效對象數量一樣，也可以不用去管。

SQL> @?/rdbms/admin/utlrp.sql

執行完成。

# 查看無效對象
SQL> select count(*) from dba_invalid_objects;

未選定行

SQL> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

  COUNT(*)
----------
       0

9. 檢查數據庫狀態

（1）檢查組件狀態

SQL> select comp_name,version, status from dba_registry;

COMP_NAME                                VERSION                        STATUS
---------------------------------------- ------------------------------ ----------------------
Oracle Enterprise Manager                11.2.0.4.0                     VALID
OLAP Catalog                             11.2.0.4.0                     VALID
Spatial                                  11.2.0.4.0                     VALID
Oracle Multimedia                        11.2.0.4.0                     VALID
Oracle XML Database                      11.2.0.4.0                     VALID
Oracle Text                              11.2.0.4.0                     VALID
Oracle Data Mining                       11.2.0.4.0                     VALID
Oracle Expression Filter                 11.2.0.4.0                     VALID
Oracle Rules Manager                     11.2.0.4.0                     VALID
Oracle Workspace Manager                 11.2.0.4.0                     VALID
Oracle Database Catalog Views            11.2.0.4.0                     VALID
Oracle Database Packages and Types       11.2.0.4.0                     VALID
JServer JAVA Virtual Machine             11.2.0.4.0                     VALID
Oracle XDK                               11.2.0.4.0                     VALID
Oracle Database Java Packages            11.2.0.4.0                     VALID
OLAP Analytic Workspace                  11.2.0.4.0                     VALID
Oracle OLAP API                          11.2.0.4.0                     VALID

（2）檢查補丁狀態

set line 150
col ACTION_TIME for a30
col ACTION for a15
col NAMESPACE for a8
col VERSION for a10
col BUNDLE_SERIES for a5
col COMMENTS for a30
select * from dba_registry_history;
 
ACTION_TIME                    ACTION          NAMESPAC VERSION            ID COMMENTS
------------------------------ --------------- -------- ---------- ---------- ------------------------------
25-APR-12 09.56.48.615853 AM   VIEW RECOMPILE                         8289601 view recompilation
25-APR-12 09.56.48.651566 AM   UPGRADE         SERVER   10.2.0.5.0            Upgraded from 10.2.0.4.0
18-SEP-17 09.00.00.659377 PM   VIEW INVALIDATE                        8289601 view invalidation
18-SEP-17 09.00.14.866796 PM   UPGRADE         SERVER   11.2.0.4.0            Upgraded from 10.2.0.5.0

SQL>

10，其它生成參數文件

通過pfile創建spfile文件。

SQL> create spfile from pfile;

File created.

SQL>

11，生成密碼文件

$ orapwd file='/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl' password=oracle entries=10 force=y

12，修改compatible參數：

SQL> ALTER SYSTEM SET COMPATIBLE = '11.2.0' SCOPE=SPFILE;

13, Timezone數據庫層面的升級，下面是手動執行情況，還有腳本升級方式，暫略。

注意：該步驟是否執行是和Step 6中的檢查結果相關的，只有當Timezone的版本小于14時，才需要執行該步驟。
主要參考：Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]

SQL> SELECT version FROM v$timezone_file;

根據當前timezone的版本，又分三種情況：
1）等于14：這已經是11g需要的版本了，所以升級前后都不需要做任何事，這種情況很罕見。
2）高于14：升級前，必須得給11g軟件打上該timezone版本的DST補丁，這種情況也很罕見。
3）低于14：大多數都是這種情況，在升級前不需要在11g軟件層面打補丁，在升級后需要再數據庫層面將Timezone升級至14，具體看后面的步驟

1）Timezone升級前的準備工作：

先檢查一下當前的timezone版本：

conn / as sysdba
SELECT version FROM v$timezone_file;
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

10.2.0.4、5 timezone是4 一個典型的輸出是：

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         4
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

然后開始準備工作：

alter session set "_with_subquery"=materialize;
exec DBMS_DST.BEGIN_PREPARE(14);

接著檢查準備狀態：

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;

一個典型的輸出是：
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION         4
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              PREPARE

執行腳本：
-- truncate logging tables if they exist.
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

-- log affected data
set serveroutput on
 BEGIN
 DBMS_DST.FIND_AFFECTED_TABLES
 (affected_tables => 'sys.dst$affected_tables',
 log_errors => TRUE,
 log_errors_table => 'sys.dst$error_table');
 END;
 /

下面的語句都不能有返回結果：
SELECT * FROM sys.dst$affected_tables;
SELECT * FROM sys.dst$error_table;
SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';
SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';
SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

執行腳本：
-- end prepare window, the rows above will stay in those tables.
EXEC DBMS_DST.END_PREPARE;

-- check if this is ended
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;

一個典型的輸出是：

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         4
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

2）真正開始升級Timezone

conn / as sysdba
shutdown immediate;
startup upgrade;
set serveroutput on
purge dba_recyclebin;

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
alter session set "_with_subquery"=materialize;
EXEC DBMS_DST.BEGIN_UPGRADE(14);
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

一個典型的輸出是：
PROPERTY_NAME                  VALUE
 ------------------------------ ------------------------------
 DST_PRIMARY_TT_VERSION         14
 DST_SECONDARY_TT_VERSION       4
 DST_UPGRADE_STATE              UPGRADE

下面這條語句應該沒有返回結果：
SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

no rows selected

重啟數據庫：
shutdown immediate
startup

升級相關的table：執行腳本：

alter session set "_with_subquery"=materialize;
set serveroutput on
VAR numfail number
BEGIN
 DBMS_DST.UPGRADE_DATABASE(:numfail,
 parallel => TRUE,
 log_errors => TRUE,
 log_errors_table => 'SYS.DST$ERROR_TABLE',
 log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
 error_on_overlap_time => FALSE,
 error_on_nonexisting_time => FALSE);
 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/

Failures:0

PL/SQL procedure successfully completed.

如果沒有錯誤，則結束升級：
VAR fail number
 BEGIN
 DBMS_DST.END_UPGRADE(:fail);
 DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
 END;
 /

An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.

最后一次檢查：

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;

典型輸出是：

PROPERTY_NAME                  VALUE
 ------------------------------ ------------------------------
 DST_PRIMARY_TT_VERSION         14
 DST_SECONDARY_TT_VERSION       0
 DST_UPGRADE_STATE              NONE

SELECT * FROM v$timezone_file;
FILENAME                VERSION
-------------------- ----------
timezlrg_14.dat              14

14. 如果在升級過程中遇到問題，可以重新執行升級腳本（參數）

---------------------------------------------------------

1）Shut down the database as follows:

SQL>SHUTDOWN IMMEDIATE

2）Restart thedatabasein UPGRADE mode:

SQL> STARTUPUPGRADE

3）Rerun catupgrd.sql: --升級DB

SQL>@catupgrd.sql

4）Rerun utlu112s.sql: --報告升級的過程的摘要

SQL> @utlu112s.sql

5）遷移10g的Baseline 到11g

SQL> @?/rdbms/admin/catuppst.sql

6）編譯無效對象：

SQL> @?/rdbms/admin/utlrp.sql

注意：升級之后執行utluiobj.sql  列出失效對象的對比信息。
SQL> @?/rdbms/admin/utluiobj.sql;