ITPub博客

首页 > 数据库 > Oracle > RMAN跨版本恢复--从Oracle10.2.0.5恢复到Oracle11.2.0.4

RMAN跨版本恢复--从Oracle10.2.0.5恢复到Oracle11.2.0.4

原创 Oracle 作者:迷倪小魏 时间:2017-07-16 19:47:44 0 删除 编辑

目录


一、数据库升级路线

二、Source端数据库进行RMAN全备份

2.1 备份前执行utlu112i.sql脚本

2.2 Source端数据库全备份

三、Target端进行异机操作

3.1 恢复Spfile

3.2 恢复控制文件

3.3 恢复归档文件

3.4 恢复数据文件

3.5 upgrade打开数据库

3.6 执行升级脚本catupgrd.sql

3.7 执行脚本utlu112s.sql

3.8 执行脚本catuppsd.sql

3.9 编译无效对象

3.10检查组件状态

3.11 总结


实验环境:

◆原端(Source):

操作系统:RedHat 6.4 64位                 数据库:Oracle 10.2.0.5 归档模式

Hostname: seiang10g.comsys.com      IP10.1.1.47     SIDseiang

◆目标端(Target):

操作系统:CentOS 7.3 64位                数据库:Oracle 11.2.0.4 归档模式

Hostname: seiang11g.comsys.com      IP10.1.1.46     SIDseiang11g



一、数据库升级路线

下图是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.


Target11g$ORACLE_HOME/rdbms/admin下的utlu112i.sql脚本copySource10g/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/路径下copyTarget/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、必须在Source10g上先执行@?/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(苦练七十二变,笑对八十一难)

ITPUBhttp://blog.itpub.net/31015730/

51CTOhttp://seiang.blog.51cto.com/



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31015730/viewspace-2142135/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2016-03-12

  • 博文量
    101
  • 访问量
    205746