RMAN跨版本恢復--從Oracle10.2.0.5恢復到Oracle11.2.0.4
目錄
實驗環境：
◆原端（Source）：
作業系統：RedHat 6.4 64位 資料庫：Oracle 10.2.0.5 歸檔模式
Hostname: seiang10g.comsys.com IP：10.1.1.47 SID：seiang
◆目標端（Target）：
作業系統：CentOS 7.3 64位 資料庫：Oracle 11.2.0.4 歸檔模式
Hostname: seiang11g.comsys.com IP：10.1.1.46 SID：seiang11g
一、資料庫升級路線
下圖是Oracle資料庫升級路線，從圖看到Oracle10.2.0.1不能直接升級到11gR2版本，至少需要先升級到10.2.0.2以後才可以升級到11gR2，必須是10.2.0.2以上或者是10.1.0.5版本才可以直接升級到11gR2。
二、Source端資料庫進行RMAN全備份
2.1 備份前執行utlu112i.sql指令碼
在原端資料庫全備份之前，需要執行一下utlu112i.sql 指令碼，如果不執行，那麼在升級時執行@?/rdbms/admin/catupgrd.sql指令碼時可能會報如下錯誤：
DOC> The following error is generated if the pre-upgrade tool has not been
DOC> run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:
DOC>
DOC> SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC> Action:
DOC> Shutdown database ("alter systemcheckpoint" and then "shutdown abort").
DOC> Revert to the original oracle home andstart the database.
DOC> Run pre-upgrade tool against thedatabase.
DOC> Review and take appropriate actionsbased on the pre-upgrade
DOC> output before opening the datatabase inthe new software version.
將Target端11g中$ORACLE_HOME/rdbms/admin下的utlu112i.sql指令碼copy到Source端10g的/tmp/10g_to_11g下，並在Source端上執行，該指令碼可以檢查升級前的一些資訊，如果不滿足條件，會列出。
[oracle@seiang11g admin]$ scp $ORACLE_HOME/rdbms/admin/utlu112i.sql 10.1.1.47:/tmp/10g_to_11g
The authenticity of host '10.1.1.47 (10.1.1.47)' can't be established.
RSA key fingerprint is df:75:9b:fc:03:15:b6:a5:d7:f1:6a:a3:3f:b6:23:0e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.1.1.47' (RSA) to the list of known hosts.
oracle@10.1.1.47's password:
utlu112i.sql 100% 220KB 220.3KB/s 00:00
[oracle@seiang10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jul 14 09:43:16 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
SYS@seiang>@/tmp/10g_to_11g/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 07-14-2017 09:43:44
Script Version: 11.2.0.4.0 Build: 001
.
*********************************************************************
Database:
*********************************************************************
--> name: ORADB10G
--> version: 10.2.0.5.0
--> compatible: 10.2.0.5.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: 947 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 745 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:
-- 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
--> 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.5.0 database timezone version
.... to the latest version which comes with the new release.
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;
*********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
*********************************************************************
2.2 Source端資料庫全備份
[oracle@seiang10g ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Fri Jul 14 09:52:09 2017
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORADB10G (DBID=3454554876)
using target database control file instead of recovery catalog
RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> backup database filesperset 1 format '/u01/app/oracle/DB_backup/full_%n_%T_%t_%s_%p.bak';
5> backup spfile format='/u01/app/oracle/DB_backup/spfile_%n_%U_%T.bak';
6> sql 'alter system archive log current';
7> backup archivelog all format '/u01/app/oracle/DB_backup/arch_%d_%T_%s_%p.bak' delete input;
8> backup current controlfile format '/u01/app/oracle/DB_backup/ctl_%d_%T_%s_%p.bak';
9> release channel c1;
10> release channel c2;
11> }
allocated channel: c1
channel c1: sid=142 devtype=DISK
allocated channel: c2
channel c2: sid=146 devtype=DISK
Starting backup at 14-JUL-17
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/OraDb10g/system01.dbf
channel c1: starting piece 1 at 14-JUL-17
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00003 name=/u01/app/oracle/oradata/OraDb10g/sysaux01.dbf
channel c2: starting piece 1 at 14-JUL-17
channel c2: finished piece 1 at 14-JUL-17
piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312345_16_1.bak tag=TAG20170714T095225 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:26
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/app/oracle/oradata/OraDb10g/example01.dbf
channel c2: starting piece 1 at 14-JUL-17
channel c1: finished piece 1 at 14-JUL-17
piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312345_15_1.bak tag=TAG20170714T095225 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:26
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00006 name=/u01/app/oracle/oradata/OraDb10g/seiang01.dbf
channel c1: starting piece 1 at 14-JUL-17
channel c1: finished piece 1 at 14-JUL-17
piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312371_18_1.bak tag=TAG20170714T095225 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00002 name=/u01/app/oracle/oradata/OraDb10g/undotbs01.dbf
channel c1: starting piece 1 at 14-JUL-17
channel c2: finished piece 1 at 14-JUL-17
piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312371_17_1.bak tag=TAG20170714T095225 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:02
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00004 name=/u01/app/oracle/oradata/OraDb10g/users01.dbf
channel c2: starting piece 1 at 14-JUL-17
channel c1: finished piece 1 at 14-JUL-17
piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312373_19_1.bak tag=TAG20170714T095225 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
channel c1: starting piece 1 at 14-JUL-17
channel c2: finished piece 1 at 14-JUL-17
piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312373_20_1.bak tag=TAG20170714T095225 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
channel c1: finished piece 1 at 14-JUL-17
piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312374_21_1.bak tag=TAG20170714T095225 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
including current SPFILE in backupset
channel c2: starting piece 1 at 14-JUL-17
channel c2: finished piece 1 at 14-JUL-17
piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312374_22_1.bak tag=TAG20170714T095225 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:02
Finished backup at 14-JUL-17
Starting backup at 14-JUL-17
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel c1: starting piece 1 at 14-JUL-17
channel c1: finished piece 1 at 14-JUL-17
piece handle=/u01/app/oracle/DB_backup/spfile_ORADB10G_0ns9amrp_1_1_20170714.bak tag=TAG20170714T095257 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-JUL-17
sql statement: alter system archive log current
Starting backup at 14-JUL-17
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=6 recid=5 stamp=949312379
channel c1: starting piece 1 at 14-JUL-17
channel c2: starting archive log backupset
channel c2: specifying archive log(s) in backup set
input archive log thread=1 sequence=7 recid=6 stamp=949312379
channel c2: starting piece 1 at 14-JUL-17
channel c1: finished piece 1 at 14-JUL-17
piece handle=/u01/app/oracle/DB_backup/arch_ORADB10G_20170714_24_1.bak tag=TAG20170714T095259 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
channel c1: deleting archive log(s)
archive log filename=/u01/app/oracle/arch/arch_1_949079228_6.log recid=5 stamp=949312379
channel c2: finished piece 1 at 14-JUL-17
piece handle=/u01/app/oracle/DB_backup/arch_ORADB10G_20170714_25_1.bak tag=TAG20170714T095259 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:02
channel c2: deleting archive log(s)
archive log filename=/u01/app/oracle/arch/arch_1_949079228_7.log recid=6 stamp=949312379
Finished backup at 14-JUL-17
Starting backup at 14-JUL-17
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
channel c1: starting piece 1 at 14-JUL-17
channel c1: finished piece 1 at 14-JUL-17
piece handle=/u01/app/oracle/DB_backup/ctl_ORADB10G_20170714_26_1.bak tag=TAG20170714T095302 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-JUL-17
released channel: c1
released channel: c2
檢視生成的備份片
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13 Full 173.84M DISK 00:00:21 14-JUL-17
BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312345_16_1.bak
List of Datafiles in backup set 13
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 521984 14-JUL-17 /u01/app/oracle/oradata/OraDb10g/sysaux01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14 Full 367.93M DISK 00:00:26 14-JUL-17
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312345_15_1.bak
List of Datafiles in backup set 14
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 521983 14-JUL-17 /u01/app/oracle/oradata/OraDb10g/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15 Full 160.00K DISK 00:00:00 14-JUL-17
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312371_18_1.bak
List of Datafiles in backup set 15
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 521995 14-JUL-17 /u01/app/oracle/oradata/OraDb10g/seiang01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Full 56.37M DISK 00:00:02 14-JUL-17
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312371_17_1.bak
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 521994 14-JUL-17 /u01/app/oracle/oradata/OraDb10g/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 4.61M DISK 00:00:00 14-JUL-17
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312373_19_1.bak
List of Datafiles in backup set 17
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 521996 14-JUL-17 /u01/app/oracle/oradata/OraDb10g/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 1.90M DISK 00:00:00 14-JUL-17
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312373_20_1.bak
List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 521997 14-JUL-17 /u01/app/oracle/oradata/OraDb10g/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 6.77M DISK 00:00:00 14-JUL-17
BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312374_21_1.bak
Control File Included: Ckp SCN: 521998 Ckp time: 14-JUL-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20 Full 80.00K DISK 00:00:01 14-JUL-17
BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312374_22_1.bak
SPFILE Included: Modification time: 13-JUL-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21 Full 80.00K DISK 00:00:00 14-JUL-17
BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20170714T095257
Piece Name: /u01/app/oracle/DB_backup/spfile_ORADB10G_0ns9amrp_1_1_20170714.bak
SPFILE Included: Modification time: 13-JUL-17
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
22 2.00K DISK 00:00:01 14-JUL-17
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20170714T095259
Piece Name: /u01/app/oracle/DB_backup/arch_ORADB10G_20170714_25_1.bak
List of Archived Logs in backup set 22
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 7 522013 14-JUL-17 522018 14-JUL-17
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
23 27.67M DISK 00:00:02 14-JUL-17
BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20170714T095259
Piece Name: /u01/app/oracle/DB_backup/arch_ORADB10G_20170714_24_1.bak
List of Archived Logs in backup set 23
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 486247 13-JUL-17 522013 14-JUL-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24 Full 6.77M DISK 00:00:01 14-JUL-17
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20170714T095302
Piece Name: /u01/app/oracle/DB_backup/ctl_ORADB10G_20170714_26_1.bak
Control File Included: Ckp SCN: 522029 Ckp time: 14-JUL-17
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
13 B F A DISK 14-JUL-17 1 1 NO TAG20170714T095225
14 B F A DISK 14-JUL-17 1 1 NO TAG20170714T095225
15 B F A DISK 14-JUL-17 1 1 NO TAG20170714T095225
16 B F A DISK 14-JUL-17 1 1 NO TAG20170714T095225
17 B F A DISK 14-JUL-17 1 1 NO TAG20170714T095225
18 B F A DISK 14-JUL-17 1 1 NO TAG20170714T095225
19 B F A DISK 14-JUL-17 1 1 NO TAG20170714T095225
20 B F A DISK 14-JUL-17 1 1 NO TAG20170714T095225
21 B F A DISK 14-JUL-17 1 1 NO TAG20170714T095257
22 B A A DISK 14-JUL-17 1 1 NO TAG20170714T095259
23 B A A DISK 14-JUL-17 1 1 NO TAG20170714T095259
24 B F A DISK 14-JUL-17 1 1 NO TAG20170714T095302
[oracle@seiang10g DB_backup]$ ll /u01/app/oracle/DB_backup/
total 661788
-rw-r----- 1 oracle oinstall 29019136 Jul 14 09:53 arch_ORADB10G_20170714_24_1.bak
-rw-r----- 1 oracle oinstall 2560 Jul 14 09:53 arch_ORADB10G_20170714_25_1.bak
-rw-r----- 1 oracle oinstall 7110656 Jul 14 09:53 ctl_ORADB10G_20170714_26_1.bak
-rw-r----- 1 oracle oinstall 385810432 Jul 14 09:52 full_ORADB10G_20170714_949312345_15_1.bak
-rw-r----- 1 oracle oinstall 182288384 Jul 14 09:52 full_ORADB10G_20170714_949312345_16_1.bak
-rw-r----- 1 oracle oinstall 59113472 Jul 14 09:52 full_ORADB10G_20170714_949312371_17_1.bak
-rw-r----- 1 oracle oinstall 172032 Jul 14 09:52 full_ORADB10G_20170714_949312371_18_1.bak
-rw-r----- 1 oracle oinstall 4841472 Jul 14 09:52 full_ORADB10G_20170714_949312373_19_1.bak
-rw-r----- 1 oracle oinstall 1998848 Jul 14 09:52 full_ORADB10G_20170714_949312373_20_1.bak
-rw-r----- 1 oracle oinstall 7110656 Jul 14 09:52 full_ORADB10G_20170714_949312374_21_1.bak
-rw-r----- 1 oracle oinstall 98304 Jul 14 09:52 full_ORADB10G_20170714_949312374_22_1.bak
-rw-r----- 1 oracle oinstall 98304 Jul 14 09:52 spfile_ORADB10G_0ns9amrp_1_1_20170714.bak
將RMAN的備份片從Source端/u01/app/oracle/DB_backup/路徑下copy到Target端/tmp/10g_to_11g/路徑下。
[oracle@seiang10g ~]$ scp /u01/app/oracle/DB_backup/* 10.1.1.46:/tmp/10g_to_11g/
oracle@10.1.1.46's password:
arch_ORADB10G_20170714_24_1.bak 100% 28MB 27.7MB/s 00:01
arch_ORADB10G_20170714_25_1.bak 100% 2560 2.5KB/s 00:00
ctl_ORADB10G_20170714_26_1.bak 100% 6944KB 6.8MB/s 00:00
full_ORADB10G_20170714_949312345_15_1.bak 100% 368MB 40.9MB/s 00:09
full_ORADB10G_20170714_949312345_16_1.bak 100% 174MB 34.8MB/s 00:05
full_ORADB10G_20170714_949312371_17_1.bak 100% 56MB 56.4MB/s 00:01
full_ORADB10G_20170714_949312371_18_1.bak 100% 168KB 168.0KB/s 00:00
full_ORADB10G_20170714_949312373_19_1.bak 100% 4728KB 4.6MB/s 00:00
full_ORADB10G_20170714_949312373_20_1.bak 100% 1952KB 1.9MB/s 00:00
full_ORADB10G_20170714_949312374_21_1.bak 100% 6944KB 6.8MB/s 00:00
full_ORADB10G_20170714_949312374_22_1.bak 100% 96KB 96.0KB/s 00:00
spfile_ORADB10G_0ns9amrp_1_1_20170714.bak 100% 96KB 96.0KB/s 00:00
三、Target端進行異機操作
3.1 恢復Spfile
將Source端的引數檔案initseiang.ora傳送到Target端
[oracle@seiang10g ~]$ scp /u01/app/oracle/product/10.2.0/dbhome_1/dbs/initseiang.ora 10.1.1.46:/tmp/10g_to_11g/
oracle@10.1.1.46's password:
initseiang.ora 100% 1136 1.1KB/s 00:00
在本次測試中，不採用rman恢復，因為Source端和Target端的ORACLE_HOME路徑不一樣。要實驗異機不同路徑的恢復，所以直接修改pfile引數檔案。
[oracle@seiang11g 10g_to_11g]$ cp initseiang.ora $ORACLE_HOME/dbs
[oracle@seiang11g 10g_to_11g]$ vim $ORACLE_HOME/dbs/initseiang.ora
seiang.__db_cache_size=1207959552
seiang.__java_pool_size=16777216
seiang.__large_pool_size=16777216
seiang.__shared_pool_size=352321536
seiang.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/OraDb10g/adump'
*.background_dump_dest='/u01/app/oracle/admin/OraDb10g/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/u01/app/oracle/oradata/OraDb10g/control01.ctl','/u01/app/oracle/oradata/OraDb10g/control02.ctl','/u01
/app/oracle/oradata/OraDb10g/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/OraDb10g/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='OraDb10g'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=seiangXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/app/oracle/arch'
*.log_archive_format='arch_%t_%r_%s.log'
*.open_cursors=300
*.pga_aggregate_target=686817280
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/OraDb10g/udump'
建立相關目錄
[oracle@seiang11g ~]$ mkdir -p /u01/app/oracle/admin/OraDb10g/adump
[oracle@seiang11g ~]$ mkdir -p /u01/app/oracle/admin/OraDb10g/bdump
[oracle@seiang11g ~]$ mkdir -p /u01/app/oracle/oradata/OraDb10g
[oracle@seiang11g ~]$ mkdir -p /u01/app/oracle/admin/OraDb10g/cdump
[oracle@seiang11g ~]$ mkdir -p /u01/app/oracle/admin/OraDb10g/udump
[oracle@seiang11g ~]$ mkdir -p /u01/app/oracle/flash_recovery_area
臨時修改Target端的ORACLE_SID，有原來的seiang11g修改為seiang
[oracle@seiang11g ~]$ echo $ORACLE_SID
seiang11g
[oracle@seiang11g ~]$ export ORACLE_SID=seiang
[oracle@seiang11g ~]$ echo $ORACLE_SID
seiang
[oracle@seiang11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 14 10:22:51 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@seiang>startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
SYS@seiang>
報錯資訊1：
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
原因：
因為background_dump_dest和user_dump_dest兩個引數的影響，透過查閱11g官方文件，發現background_dump_dest和user_dump_dest兩個引數在11g中廢棄了，由新引數diagnostic_dest所取代。以下是官方文件中引數說明:
解決辦法：
將background_dump_dest和user_dump_dest這兩個引數的配置資訊從引數檔案initseiang.ora中刪除，然後重新啟動就不會報錯；
3.2 恢復控制檔案
[oracle@seiang11g ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jul 14 10:57:38 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB10G (not mounted)
RMAN> restore controlfile to '/u01/app/oracle/oradata/OraDb10g/control01.ctl' from '/tmp/10_to_11g/ctl_ORADB10G_20170714_26_1.bak';
Starting restore at 14-JUL-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/14/2017 10:57:45
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
將備份片copy到/u01/app/oracle/DB_backup路徑下就可以還原成功，如下：
RMAN> restore controlfile to '/u01/app/oracle/oradata/OraDb10g/control01.ctl' from '/u01/app/oracle/DB_backup/ctl_ORADB10G_20170714_26_1.bak';
Starting restore at 14-JUL-17
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-JUL-17
[oracle@seiang11g ~]$ cp /u01/app/oracle/oradata/OraDb10g/control01.ctl /u01/app/oracle/oradata/OraDb10g/control02.ctl
[oracle@seiang11g ~]$ cp /u01/app/oracle/oradata/OraDb10g/control01.ctl /u01/app/oracle/oradata/OraDb10g/control03.ctl
SYS@seiang>alter database mount;
檢視告警日誌的資訊：
[oracle@seiang11g trace]$ tail -f alert_seiang.log
RECO started with pid=14, OS id=24329
Fri Jul 14 10:46:09 2017
MMON started with pid=15, OS id=24331
Fri Jul 14 10:46:09 2017
MMNL started with pid=16, OS id=24333
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Fri Jul 14 11:01:45 2017
No controlfile conversion
Fri Jul 14 11:24:25 2017
alter database mount
Control file expanded from 430 to 592 blocks for upgrade.
Set as converted control file due to db_unique_name mismatch
Changing di2dbun from to OraDb10g
Expanded controlfile section 32 from 10 to 31 records
The number of logical blocks in section 32 remains the same
Fri Jul 14 11:24:29 2017
Successful mount of redo thread 1, with mount id 3454814249
Database mounted in Exclusive Mode
Lost write protection disabled
Create Relation IPS_PACKAGE_UNPACK_HISTORY
Completed: alter database mount
3.3 恢復歸檔檔案
RMAN> catalog start with '/tmp/10g_to_11g/';
released channel: ORA_DISK_1
Starting implicit crosscheck backup at 14-JUL-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Crosschecked 11 objects
Finished implicit crosscheck backup at 14-JUL-17
Starting implicit crosscheck copy at 14-JUL-17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 14-JUL-17
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /tmp/10g_to_11g/
List of Files Unknown to the Database
=====================================
File Name: /tmp/10g_to_11g/arch_ORADB10G_20170714_24_1.bak
File Name: /tmp/10g_to_11g/arch_ORADB10G_20170714_25_1.bak
File Name: /tmp/10g_to_11g/ctl_ORADB10G_20170714_26_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312345_15_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312345_16_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312371_17_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312371_18_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312373_19_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312373_20_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312374_21_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312374_22_1.bak
File Name: /tmp/10g_to_11g/spfile_ORADB10G_0ns9amrp_1_1_20170714.bak
File Name: /tmp/10g_to_11g/initseiang.ora
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/10g_to_11g/arch_ORADB10G_20170714_24_1.bak
File Name: /tmp/10g_to_11g/arch_ORADB10G_20170714_25_1.bak
File Name: /tmp/10g_to_11g/ctl_ORADB10G_20170714_26_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312345_15_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312345_16_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312371_17_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312371_18_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312373_19_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312373_20_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312374_21_1.bak
File Name: /tmp/10g_to_11g/full_ORADB10G_20170714_949312374_22_1.bak
File Name: /tmp/10g_to_11g/spfile_ORADB10G_0ns9amrp_1_1_20170714.bak
List of Files Which Where Not Cataloged
=======================================
File Name: /tmp/10g_to_11g/initseiang.ora
RMAN-07517: Reason: The file header is corrupted
RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
specification does not match any archived log in the repository
RMAN> list expired backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13 Full 173.84M DISK 00:00:21 14-JUL-17
BP Key: 13 Status: EXPIRED Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312345_16_1.bak
List of Datafiles in backup set 13
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 521984 14-JUL-17 /u01/app/oracle/oradata/OraDb10g/sysaux01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14 Full 367.93M DISK 00:00:26 14-JUL-17
BP Key: 14 Status: EXPIRED Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312345_15_1.bak
List of Datafiles in backup set 14
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 521983 14-JUL-17 /u01/app/oracle/oradata/OraDb10g/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15 Full 160.00K DISK 00:00:00 14-JUL-17
BP Key: 15 Status: EXPIRED Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312371_18_1.bak
List of Datafiles in backup set 15
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 521995 14-JUL-17 /u01/app/oracle/oradata/OraDb10g/seiang01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Full 56.37M DISK 00:00:02 14-JUL-17
BP Key: 16 Status: EXPIRED Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312371_17_1.bak
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 521994 14-JUL-17 /u01/app/oracle/oradata/OraDb10g/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 4.61M DISK 00:00:00 14-JUL-17
BP Key: 17 Status: EXPIRED Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312373_19_1.bak
List of Datafiles in backup set 17
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 521996 14-JUL-17 /u01/app/oracle/oradata/OraDb10g/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 1.90M DISK 00:00:00 14-JUL-17
BP Key: 18 Status: EXPIRED Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312373_20_1.bak
List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 521997 14-JUL-17 /u01/app/oracle/oradata/OraDb10g/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 6.77M DISK 00:00:00 14-JUL-17
BP Key: 19 Status: EXPIRED Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312374_21_1.bak
Control File Included: Ckp SCN: 521998 Ckp time: 14-JUL-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20 Full 80.00K DISK 00:00:01 14-JUL-17
BP Key: 20 Status: EXPIRED Compressed: NO Tag: TAG20170714T095225
Piece Name: /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312374_22_1.bak
SPFILE Included: Modification time: 13-JUL-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21 Full 80.00K DISK 00:00:00 14-JUL-17
BP Key: 21 Status: EXPIRED Compressed: NO Tag: TAG20170714T095257
Piece Name: /u01/app/oracle/DB_backup/spfile_ORADB10G_0ns9amrp_1_1_20170714.bak
SPFILE Included: Modification time: 13-JUL-17
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
22 2.00K DISK 00:00:01 14-JUL-17
BP Key: 22 Status: EXPIRED Compressed: NO Tag: TAG20170714T095259
Piece Name: /u01/app/oracle/DB_backup/arch_ORADB10G_20170714_25_1.bak
List of Archived Logs in backup set 22
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 7 522013 14-JUL-17 522018 14-JUL-17
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
23 27.67M DISK 00:00:02 14-JUL-17
BP Key: 23 Status: EXPIRED Compressed: NO Tag: TAG20170714T095259
Piece Name: /u01/app/oracle/DB_backup/arch_ORADB10G_20170714_24_1.bak
List of Archived Logs in backup set 23
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 486247 13-JUL-17 522013 14-JUL-17
RMAN> delete expired backupset;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
13 13 1 1 EXPIRED DISK /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312345_16_1.bak
14 14 1 1 EXPIRED DISK /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312345_15_1.bak
15 15 1 1 EXPIRED DISK /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312371_18_1.bak
16 16 1 1 EXPIRED DISK /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312371_17_1.bak
17 17 1 1 EXPIRED DISK /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312373_19_1.bak
18 18 1 1 EXPIRED DISK /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312373_20_1.bak
19 19 1 1 EXPIRED DISK /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312374_21_1.bak
20 20 1 1 EXPIRED DISK /u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312374_22_1.bak
21 21 1 1 EXPIRED DISK /u01/app/oracle/DB_backup/spfile_ORADB10G_0ns9amrp_1_1_20170714.bak
22 22 1 1 EXPIRED DISK /u01/app/oracle/DB_backup/arch_ORADB10G_20170714_25_1.bak
23 23 1 1 EXPIRED DISK /u01/app/oracle/DB_backup/arch_ORADB10G_20170714_24_1.bak
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312345_16_1.bak RECID=13 STAMP=949312345
deleted backup piece
backup piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312345_15_1.bak RECID=14 STAMP=949312345
deleted backup piece
backup piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312371_18_1.bak RECID=15 STAMP=949312371
deleted backup piece
backup piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312371_17_1.bak RECID=16 STAMP=949312371
deleted backup piece
backup piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312373_19_1.bak RECID=17 STAMP=949312373
deleted backup piece
backup piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312373_20_1.bak RECID=18 STAMP=949312373
deleted backup piece
backup piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312374_21_1.bak RECID=19 STAMP=949312374
deleted backup piece
backup piece handle=/u01/app/oracle/DB_backup/full_ORADB10G_20170714_949312374_22_1.bak RECID=20 STAMP=949312375
deleted backup piece
backup piece handle=/u01/app/oracle/DB_backup/spfile_ORADB10G_0ns9amrp_1_1_20170714.bak RECID=21 STAMP=949312377
deleted backup piece
backup piece handle=/u01/app/oracle/DB_backup/arch_ORADB10G_20170714_25_1.bak RECID=22 STAMP=949312380
deleted backup piece
backup piece handle=/u01/app/oracle/DB_backup/arch_ORADB10G_20170714_24_1.bak RECID=23 STAMP=949312380
Deleted 11 EXPIRED objects
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
22 2.00K DISK 00:00:01 14-JUL-17
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20170714T095259
Piece Name: /tmp/10g_to_11g/arch_ORADB10G_20170714_25_1.bak
List of Archived Logs in backup set 22
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 7 522013 14-JUL-17 522018 14-JUL-17
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
23 27.67M DISK 00:00:02 14-JUL-17
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20170714T095259
Piece Name: /tmp/10g_to_11g/arch_ORADB10G_20170714_24_1.bak
List of Archived Logs in backup set 23
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 486247 13-JUL-17 522013 14-JUL-17
RMAN> restore archivelog sequence 6;
Starting restore at 14-JUL-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_DISK_1: reading from backup piece /tmp/10g_to_11g/arch_ORADB10G_20170714_24_1.bak
channel ORA_DISK_1: piece handle=/tmp/10g_to_11g/arch_ORADB10G_20170714_24_1.bak tag=TAG20170714T095259
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-JUL-17
RMAN> restore archivelog sequence 7;
Starting restore at 14-JUL-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece /tmp/10g_to_11g/arch_ORADB10G_20170714_25_1.bak
channel ORA_DISK_1: piece handle=/tmp/10g_to_11g/arch_ORADB10G_20170714_25_1.bak tag=TAG20170714T095259
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-JUL-17
RMAN> restore archivelog sequence between 6 and 7;
Starting restore at 14-JUL-17
using channel ORA_DISK_1
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/arch/arch_1_949079228_6.log
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/arch/arch_1_949079228_7.log
restore not done; all files read only, offline, or already restored
Finished restore at 14-JUL-17
3.4 恢復資料檔案
RMAN> run{
2> restore database;
3> switch datafile all;
4> recover database;
5> }
Starting restore at 14-JUL-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
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/OraDb10g/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/10g_to_11g/full_ORADB10G_20170714_949312345_16_1.bak
channel ORA_DISK_1: piece handle=/tmp/10g_to_11g/full_ORADB10G_20170714_949312345_16_1.bak tag=TAG20170714T095225
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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/OraDb10g/system01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/10g_to_11g/full_ORADB10G_20170714_949312345_15_1.bak
channel ORA_DISK_1: piece handle=/tmp/10g_to_11g/full_ORADB10G_20170714_949312345_15_1.bak tag=TAG20170714T095225
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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/OraDb10g/seiang01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/10g_to_11g/full_ORADB10G_20170714_949312371_18_1.bak
channel ORA_DISK_1: piece handle=/tmp/10g_to_11g/full_ORADB10G_20170714_949312371_18_1.bak tag=TAG20170714T095225
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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 00002 to /u01/app/oracle/oradata/OraDb10g/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/10g_to_11g/full_ORADB10G_20170714_949312373_19_1.bak
channel ORA_DISK_1: piece handle=/tmp/10g_to_11g/full_ORADB10G_20170714_949312373_19_1.bak tag=TAG20170714T095225
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 00004 to /u01/app/oracle/oradata/OraDb10g/users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/10g_to_11g/full_ORADB10G_20170714_949312373_20_1.bak
channel ORA_DISK_1: piece handle=/tmp/10g_to_11g/full_ORADB10G_20170714_949312373_20_1.bak tag=TAG20170714T095225
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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/OraDb10g/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/10g_to_11g/full_ORADB10G_20170714_949312371_17_1.bak
channel ORA_DISK_1: piece handle=/tmp/10g_to_11g/full_ORADB10G_20170714_949312371_17_1.bak tag=TAG20170714T095225
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 14-JUL-17
Starting recover at 14-JUL-17
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/arch/arch_1_949079228_6.log
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/arch/arch_1_949079228_7.log
archived log file name=/u01/app/oracle/arch/arch_1_949079228_6.log thread=1 sequence=6
archived log file name=/u01/app/oracle/arch/arch_1_949079228_7.log thread=1 sequence=7
media recovery complete, elapsed time: 00:00:45
Finished recover at 14-JUL-17
檢視告警日誌的資訊：
Fri Jul 14 11:52:21 2017
Full restore complete of datafile 3 /u01/app/oracle/oradata/OraDb10g/sysaux01.dbf. Elapsed time: 0:00:05
checkpoint is 521984
last deallocation scn is 417953
Fri Jul 14 11:52:21 2017
Checker run found 1 new persistent data failures
Fri Jul 14 11:52:37 2017
Full restore complete of datafile 1 /u01/app/oracle/oradata/OraDb10g/system01.dbf. Elapsed time: 0:00:14
checkpoint is 521983
last deallocation scn is 419845
Fri Jul 14 11:52:48 2017
Full restore complete of datafile 6 /u01/app/oracle/oradata/OraDb10g/seiang01.dbf. Elapsed time: 0:00:00
checkpoint is 521995
Full restore complete of datafile 2 /u01/app/oracle/oradata/OraDb10g/undotbs01.dbf. Elapsed time: 0:00:00
checkpoint is 521996
last deallocation scn is 521418
Full restore complete of datafile 4 /u01/app/oracle/oradata/OraDb10g/users01.dbf. Elapsed time: 0:00:00
checkpoint is 521997
Full restore complete of datafile 5 /u01/app/oracle/oradata/OraDb10g/example01.dbf. Elapsed time: 0:00:01
checkpoint is 521994
last deallocation scn is 399417
Fri Jul 14 11:52:55 2017
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6
alter database recover if needed
start until cancel using backup controlfile
Media Recovery Start
Serial Media Recovery started
ORA-279 signalled during: alter database recover if needed
start until cancel using backup controlfile
...
alter database recover logfile '/u01/app/oracle/arch/arch_1_949079228_6.log'
Media Recovery Log /u01/app/oracle/arch/arch_1_949079228_6.log
ORA-279 signalled during: alter database recover logfile '/u01/app/oracle/arch/arch_1_949079228_6.log'...
alter database recover logfile '/u01/app/oracle/arch/arch_1_949079228_7.log'
Media Recovery Log /u01/app/oracle/arch/arch_1_949079228_7.log
ORA-279 signalled during: alter database recover logfile '/u01/app/oracle/arch/arch_1_949079228_7.log'...
alter database recover cancel
Media Recovery Canceled
Completed: alter database recover cancel
3.5 upgrade開啟資料庫
SYS@seiang>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: 24338
Session ID: 1 Serial number: 3
[oracle@seiang11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 14 12:06:31 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@seiang>startup upgrade
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
Database opened.
檢視告警日誌中的資訊：
Completed: ALTER DATABASE MOUNT
Fri Jul 14 12:07:08 2017
ALTER DATABASE OPEN MIGRATE
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
read 0 KB redo, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 1, block 2, scn 522022
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/OraDb10g/redo01.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 1, block 3, scn 542024
0 data blocks read, 0 data blocks written, 0 redo k-bytes read
LGWR: STARTING ARCH PROCESSES
Fri Jul 14 12:07:08 2017
ARC0 started with pid=20, OS id=25798
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Fri Jul 14 12:07:09 2017
ARC1 started with pid=21, OS id=25800
Fri Jul 14 12:07:09 2017
ARC2 started with pid=22, OS id=25802
Fri Jul 14 12:07:10 2017
ARC3 started with pid=23, OS id=25804
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/OraDb10g/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Archived Log entry 9 added for thread 1 sequence 1 ID 0xcdec4829 dest 1:
[25792] Successfully onlined Undo Tablespace 1.
Undo initialization finished serial:0 start:77355594 end:77355694 diff:100 (1 seconds)
Dictionary check beginning
Errors in file /u01/app/oracle/diag/rdbms/oradb10g/seiang/trace/seiang_dbw0_25770.trc:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/OraDb10g/temp01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/oradb10g/seiang/trace/seiang_dbw0_25770.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/OraDb10g/temp01.dbf'
File 201 not verified due to error ORA-01157
Dictionary check complete
SMON: enabling tx recovery
Re-creating tempfile /u01/app/oracle/oradata/OraDb10g/temp01.dbf
Database Characterset is AL32UTF8
Updating 10.2.0.5.0 NLS parameters in sys.props$
-- adding 11.2.0.4.0 NLS parameters.
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Stopping background process MMNL
Stopping background process MMON
Starting background process MMON
Starting background process MMNL
Fri Jul 14 12:07:13 2017
MMON started with pid=15, OS id=25810
Fri Jul 14 12:07:13 2017
MMNL started with pid=16, OS id=25812
ALTER SYSTEM enable restricted session;
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Autotune of undo retention is turned off.
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY;
Resource Manager disabled during database migration: plan '' not set
ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;
ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY;
Resource Manager disabled during database migration
replication_dependency_tracking turned off (no async multimaster replication found)
XDB UNINITIALIZED: XDB$SCHEMA not accessible
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: ALTER DATABASE OPEN MIGRATE
SYS@seiang>alter tablespace temp add tempfile'/u01/app/oracle/oradata/OraDb10g/temp02.dbf' size 100m reuse autoextend on next 10m maxsize 1000m;
SYS@seiang>alters tablespace temp drop tempfile '/u01/app/oracle/oradata/OraDb10g/temp01.dbf';
3.6 執行升級指令碼catupgrd.sql
SYS@seiang> spool /tmp/upgrade.log
SYS@seiang> set echo on
SYS@seiang> @$ORACLE_HOME/rdbms/admin/catupgrd.sql;
指令碼執行的結果：
此處省略N行內容…………….
PL/SQL procedure successfully completed.
SYS@seiang>
SYS@seiang>SET SERVEROUTPUT OFF
SYS@seiang>SET VERIFY ON
SYS@seiang>commit;
Commit complete.
SYS@seiang>
SYS@seiang>shutdown immediate;（在這裡可以看到，指令碼執行結束後，會自動關閉資料庫）
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@seiang>
SYS@seiang>
SYS@seiang>
SYS@seiang>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>#
SYS@seiang>
SYS@seiang>Rem Set errorlogging off
SYS@seiang>SET ERRORLOGGING OFF;
SYS@seiang>
SYS@seiang>REM END OF CATUPGRD.SQL
SYS@seiang>
SYS@seiang>REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
SYS@seiang>REM This forces user to start a new sqlplus session in order
SYS@seiang>REM to connect to the upgraded db.
SYS@seiang>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
檢視相應的告警日誌，可以發現，在執行指令碼catupgrd.sql的時候，頻繁的進行日誌的切換，故會產生大量的歸檔日誌檔案，所以應該最好增加日誌組，保證足夠的切換時間，以減少Checkpoint not complete的出現，同時也可以減少執行指令碼所用的時間。該指令碼大概執行了50多分鐘。
告警檔案內容如下所示：
Fri Jul 14 13:15:30 2017
Archived Log entry 40 added for thread 1 sequence 32 ID 0xcdec4829 dest 1:
Fri Jul 14 13:15:49 2017
Thread 1 advanced to log sequence 34 (LGWR switch)
Current log# 1 seq# 34 mem# 0: /u01/app/oracle/oradata/OraDb10g/redo01.log
Fri Jul 14 13:15:51 2017
Archived Log entry 41 added for thread 1 sequence 33 ID 0xcdec4829 dest 1:
Fri Jul 14 13:16:10 2017
Thread 1 advanced to log sequence 35 (LGWR switch)
Current log# 2 seq# 35 mem# 0: /u01/app/oracle/oradata/OraDb10g/redo02.log
Fri Jul 14 13:16:11 2017
Archived Log entry 42 added for thread 1 sequence 34 ID 0xcdec4829 dest 1:
Fri Jul 14 13:16:31 2017
Thread 1 cannot allocate new log, sequence 36
Checkpoint not complete
Current log# 2 seq# 35 mem# 0: /u01/app/oracle/oradata/OraDb10g/redo02.log
Thread 1 advanced to log sequence 36 (LGWR switch)
Current log# 3 seq# 36 mem# 0: /u01/app/oracle/oradata/OraDb10g/redo03.log
Fri Jul 14 13:16:34 2017
Archived Log entry 43 added for thread 1 sequence 35 ID 0xcdec4829 dest 1:
Fri Jul 14 13:16:52 2017
Thread 1 advanced to log sequence 37 (LGWR switch)
Current log# 1 seq# 37 mem# 0: /u01/app/oracle/oradata/OraDb10g/redo01.log
Fri Jul 14 13:16:54 2017
Archived Log entry 44 added for thread 1 sequence 36 ID 0xcdec4829 dest 1:
Fri Jul 14 13:17:10 2017
Thread 1 cannot allocate new log, sequence 38
Checkpoint not complete
Current log# 1 seq# 37 mem# 0: /u01/app/oracle/oradata/OraDb10g/redo01.log
Thread 1 advanced to log sequence 38 (LGWR switch)
Current log# 2 seq# 38 mem# 0: /u01/app/oracle/oradata/OraDb10g/redo02.log
Fri Jul 14 13:17:14 2017
Archived Log entry 45 added for thread 1 sequence 37 ID 0xcdec4829 dest 1:
Fri Jul 14 13:17:23 2017
Thread 1 advanced to log sequence 39 (LGWR switch)
Current log# 3 seq# 39 mem# 0: /u01/app/oracle/oradata/OraDb10g/redo03.log
Fri Jul 14 13:17:25 2017
Archived Log entry 46 added for thread 1 sequence 38 ID 0xcdec4829 dest 1:
Fri Jul 14 13:18:05 2017
Thread 1 cannot allocate new log, sequence 40
Checkpoint not complete
Current log# 3 seq# 39 mem# 0: /u01/app/oracle/oradata/OraDb10g/redo03.log
Thread 1 advanced to log sequence 40 (LGWR switch)
Current log# 1 seq# 40 mem# 0: /u01/app/oracle/oradata/OraDb10g/redo01.log
特別注意：
1、我們備份之前的一個操作，必須先utlu112i.sql指令碼， 然後執行這個指令碼，否則就會出現如下錯誤：
SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
*
ERROR at line 1:
ORA-01722: invalid number
[oracle@seiang11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 14 13:51:05 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@seiang>startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
Database opened.
SYS@seiang>select instance_name,version,status from v$instance;
INSTANCE_NAME VERSION STATUS
---------------- ----------------- ------------
seiang 11.2.0.4.0 OPEN
3.7 執行指令碼utlu112s.sql
該指令碼的作用是顯示升級過程的一個摘要，不需要在upgrade模式下執行。
SYS@seiang>@?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 07-14-2017 14:03:07
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:10:24
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:07:21
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:30
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:00:20
OLAP Catalog
. VALID 11.2.0.4.0 00:00:38
Oracle OLAP API
. VALID 11.2.0.4.0 00:00:29
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:05:57
Oracle XDK
. VALID 11.2.0.4.0 00:02:26
Oracle Text
. VALID 11.2.0.4.0 00:00:33
Oracle XML Database
. VALID 11.2.0.4.0 00:03:33
Oracle Database Java Packages
. VALID 11.2.0.4.0 00:00:12
Oracle Multimedia
. VALID 11.2.0.4.0 00:02:44
Spatial
. VALID 11.2.0.4.0 00:04:23
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:10
Oracle Rules Manager
. VALID 11.2.0.4.0 00:00:07
Final Actions
. 00:01:34
Total Upgrade Time: 00:41:32
PL/SQL procedure successfully completed.
3.8 執行指令碼catuppsd.sql
這個指令碼用來遷移一些Baseline資料到11g資料庫中，不需要在upgrade模式下執行。
SYS@seiang>@?/rdbms/admin/catuppst.sql
此處省略N行內容……
SYS@seiang>ALTER SESSION SET current_schema = SYS;
Session altered.
SYS@seiang>PROMPT Updating registry...
Updating registry...
SYS@seiang>INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '11.2.0.4',
9 0,
10 'PSU',
11 'Patchset 11.2.0.2.0');
1 row created.
SYS@seiang>COMMIT;
Commit complete.
SYS@seiang>SPOOL off
SYS@seiang>SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORADB10G_APPLY_2017Jul14_14_07_09.log
3.9 編譯無效物件
SYS@seiang>select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
5964
或者執行select count(*) from dba_invalid_objects;結果是一樣的；
SYS@seiang>@?/rdbms/admin/utlrp.sql
TIMESTAMP
----------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2017-07-14 14:15:53
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-07-14 14:21:03
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.
該指令碼大約執行了不到十分鐘的時間，在執行的過程中，檢視無效物件的數量，可以發現，無效物件逐漸減少，知道減少到0，指令碼執行結束。
SYS@seiang>select count(*) from dba_invalid_objects;
COUNT(*)
----------
212
SYS@seiang>select count(*) from dba_invalid_objects;
COUNT(*)
----------
193
SYS@seiang>select owner,count(*) from dba_objects where status='INVALID' group by owner;
OWNER COUNT(*)
------------------------------ ----------
SH 2
SYS 3
SYS@seiang>select count(*) from dba_invalid_objects;
COUNT(*)
----------
0
SYS@seiang>select owner,count(*) from dba_objects where status='INVALID' group by owner;
no rows selected
3.10檢查元件狀態
SYS@seiang>select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
--------------- ---------------------------------------- ------------------------------ ----------
EM Oracle Enterprise Manager 11.2.0.4.0 VALID
AMD OLAP Catalog 11.2.0.4.0 VALID
SDO Spatial 11.2.0.4.0 VALID
ORDIM Oracle Multimedia 11.2.0.4.0 VALID
XDB Oracle XML Database 11.2.0.4.0 VALID
CONTEXT Oracle Text 11.2.0.4.0 VALID
ODM Oracle Data Mining 11.2.0.4.0 VALID
EXF Oracle Expression Filter 11.2.0.4.0 VALID
RUL Oracle Rules Manager 11.2.0.4.0 VALID
OWM Oracle Workspace Manager 11.2.0.4.0 VALID
CATALOG Oracle Database Catalog Views 11.2.0.4.0 VALID
CATPROC Oracle Database Packages and Types 11.2.0.4.0 VALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.4.0 VALID
XML Oracle XDK 11.2.0.4.0 VALID
CATJAVA Oracle Database Java Packages 11.2.0.4.0 VALID
APS OLAP Analytic Workspace 11.2.0.4.0 VALID
XOQ Oracle OLAP API 11.2.0.4.0 VALID
17 rows selected.
3.11 總結
將資料庫10g 還原到11g有兩項關鍵內容：
1、必須在Source端10g上先執行@?/rdbms/admin/utlu112i.sql指令碼，然後在透過RMAN備份，否則Restore之後的升級將失敗。
2、Oracle10g的版本必須大於10.2.0.2。
3、如果在升級過程中遇到問題，可以關閉資料庫，然後啟動到upgrade模式，重新執行升級指令碼。
參考連結：
http://blog.csdn.net/tianlesoftware/article/details/7311352#t10
http://blog.itpub.net/26736162/viewspace-1562583/
作者：SEian.G（苦練七十二變，笑對八十一難）
ITPUB：http://blog.itpub.net/31015730/
51CTO：http://seiang.blog.51cto.com/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31015730/viewspace-2142135/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【RMAN】RMAN跨版本恢復(上)
- 【RMAN】RMAN跨版本恢復(中)
- 跨版本資料庫恢復資料庫
- RMAN恢復 執行重要檔案RMAN恢復
- Rman_異地、跨平臺、跨版本的恢復總結及案例
- rman恢復到指定時間點
- rman恢復方案和oracle異機恢復Oracle
- rman 恢復機制與恢復測試
- oracle實驗記錄 （恢復-rman恢復）Oracle
- rman恢復--丟失控制檔案的恢復
- rman恢復 使用switch映像副本進行恢復
- RMAN恢復 執行不重要檔案的RMAN恢復
- rman備份恢復-rman恢復資料檔案測試
- rac到單例項的rman恢復單例
- RMAN恢復實踐
- ORACLE DG從庫 Rman備份恢復Oracle
- rman恢復資料檔案 恢復表空間
- 【管理篇備份恢復】rman恢復測試（二） 控制檔案恢復（三）
- 【管理篇備份恢復】rman恢復測試（二） 控制檔案恢復（二）
- 【管理篇備份恢復】rman恢復測試（二） 控制檔案恢復（一）
- 控制檔案恢復—從快照中恢復
- RMAN異機恢復：RAC到單例項單例
- rman恢復--丟失聯機重做日誌的恢復
- RMAN恢復案例:丟失全部資料檔案恢復
- RMAN備份恢復之控制檔案的恢復（三）
- RMAN備份恢復之控制檔案的恢復（二）
- RMAN備份恢復之控制檔案的恢復（一）
- RMAN全庫【完全恢復/不完全恢復brief version】
- Oracle RMAN恢復測試Oracle
- RMAN恢復控制檔案
- Oracle rman 各種恢復Oracle
- RMAN其他恢復主題
- Oracle RMAN異機恢復Oracle
- RMAN恢復指令碼案例指令碼
- RMAN恢復資料庫資料庫
- rman 恢復資料塊
- rman恢復資料塊
- RMAN恢復簡單操作