Oracle 使用RMAN 将 DB 从10g 直接 Restore 到11g 示例

最新推荐文章于 2024-02-27 10:29:58 发布

转载最新推荐文章于 2024-02-27 10:29:58 发布 · 674 阅读

oracle 同时被 3 个专栏收录

84 篇文章

订阅专栏

rman

33 篇文章

订阅专栏

备份与恢复

30 篇文章

订阅专栏

随着Oracle 版本的升级，RMAN 也得到增强，在Oracle11g中的RMAN 支持不同操作系统和不同DB 之前的使用，关于RMAN 的兼容性，参考：

Oracle RMAN 兼容性及不同版本和不同平台之间使用常见问题说明

http://blog.youkuaiyun.com/tianlesoftware/article/details/7232602

这篇Blog 演示一个linux 下10g 恢复到11g中的示例。

Target 端：

OS： Oracle Linux 6.1 64位

DB： 11.2.0.3

Source 端：

OS：Oracle Linux 6.1 64位

DB： 10.2.0.5

注意：

Oracle 10.2.0.1 不能直接升级到11gR2，至少需要先升级到10.2.0.2 以后才可以升级到11gR2，关于Oracle 升级路线图参考我的Blog：

Oracle9i/10g/11g 数据库升级路线图（upgrade roadmap）

http://blog.youkuaiyun.com/tianlesoftware/article/details/6729409

简单的来说就是2个步骤：

1. 将10g 数据库还原11g上

2. 升级还原的10g数据库到11g。

具体操作步骤如下。

一．在Source 库用RMAN 备份数据库

1.1 在Source 库上执行utlu112i.sql脚本

在Source 备份之前，需要执行一下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> o 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.

DOC>

将11g $ORACLE_HOME/rdbms/admin 下的utlu112i.sql脚本copy到10g的$ORACLE_HOME/rdbms/admin 下，并在10g上执行，这个脚本可以检查升级前的一些信息。如果不满足条件，会列出。

SQL> spool upgrade_info.log

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

Oracle Database 11.2 Pre-UpgradeInformation Tool 03-01-2012 19:53:57

Script Version: 11.2.0.3.0 Build: 001

**********************************************************************

Database:

**********************************************************************

--> name: ANQING

--> 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 thecurrent environment]

**********************************************************************

--> SYSTEM tablespace is adequate forthe upgrade.

.... minimum required size: 682 MB

--> UNDOTBS1 tablespace is adequate forthe upgrade.

.... minimum required size: 400 MB

--> SYSAUX tablespace is adequate forthe upgrade.

.... minimum required size: 454 MB

--> TEMP tablespace is adequate for theupgrade.

.... minimum required size: 60 MB

**********************************************************************

Flashback: OFF

**********************************************************************

Update Parameters: [Update Oracle Database11.2 init.ora or spfile]

Note: Pre-upgrade tool was run on a lowerversion 64-bit database.

**********************************************************************

--> If Target Oracle is 32-Bit, referhere for Update Parameters:

-- No update parameter changes are required.

--> If Target Oracle is 64-Bit, referhere for Update Parameters:

WARNING: --> "sga_target"needs to be increased to at least 596 MB

**********************************************************************

Renamed Parameters: [Update Oracle Database11.2 init.ora or spfile]

**********************************************************************

-- No renamed parameters found. No changesare required.

**********************************************************************

Obsolete/Deprecated Parameters: [UpdateOracle 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 databasecomponents 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 atimezone file older than version 14.

.... After the release migration, it isrecommended that DBMS_DST package

.... be used to upgrade the 10.2.0.5.0database timezone version

.... to the latest version which comes withthe new release.

WARNING: --> EM Database ControlRepository exists in the database.

.... Direct downgrade of EM DatabaseControl is not supported. Refer to the

.... Upgrade Guide for instructions to savethe EM data prior to upgrade.

WARNING: --> Your recycle bin is turnedon and currently contains no objects.

.... Because it is REQUIRED that therecycle bin be empty prior to upgrading

.... and your recycle bin is turned on, youmay need to execute the command:

PURGE DBA_RECYCLEBIN

.... prior to executing your upgrade toconfirm the recycle bin is empty.

**********************************************************************

Recommendations

**********************************************************************

Oracle recommends gathering dictionarystatistics prior to

upgrading the database.

To gather dictionary statistics execute thefollowing command

while connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************

Oracle recommends reviewing any definedevents prior to upgrading.

To view existing non-default events executethe 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 theinit.ora or spfile.

**********************************************************************

SQL>

如果直接在11g上执行这个脚本，那么会报如下错误：

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

ERROR:

ORA-04023: Object SYS.STANDARD could not bevalidated or authorized

MOS 对这个问题的说明：

ORA-04023 On SYS.STANDARD When RunningUTLU111I.SQL or UTLU112I.SQL [ID 984511.1]

在网上搜到这么一段：

WARNING: –> Database is using an oldtimezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.

To find time zone file version on sourcedatabase (10g) run
SQL> select * from v$timezone_file;
If time zone file version is lessthan 4 then apply time zone patch 5632264 manually for 10.2.0.1database。

--之前我们说10.2.0.1 不能直接升级到11gR2，通过这段文字，这个不能升级的原因可能就是与Time zone 相关。10.2.0.5的Time zone 值是4，满足条件。对于timezone 值小于4的，要打Patch 5632264。

手头没有10.2.0.1的库，暂时不能查询，之前也遇到过一个time zone的问题。

Oracle TTSORA-39322: Cannot use transportable tablespace with different timezone version 说明

http://blog.youkuaiyun.com/tianlesoftware/article/details/7298547

1.2 备份Source库

备份脚本参考：

Linux平台下RMAN 全备和增量备份shell 脚本

http://blog.youkuaiyun.com/tianlesoftware/article/details/5740630

Nocatalog下的RMAN 增量备份shell脚本

http://blog.youkuaiyun.com/tianlesoftware/article/details/6164931

二．将Source库的备份集copy到Target库的相同位置

rac2:/u01/backup> scp * 192.168.3.201:/u01/backup

oracle@192.168.3.201's password:

anqing_lev0_0jn4qd0c_1_1_20120301 100% 361MB 12.0MB/s 00:30

anqing_lev0_0kn4qd0c_1_1_20120301 100% 174MB 10.2MB/s 00:17

anqing_spfile_0qn4qd2v_1_1_20120301 100% 96KB 96.0KB/s 00:00

arch_0nn4qd2q_1_1_20120301 100% 15MB 15.5MB/s 00:01

arch_0on4qd2q_1_1_20120301 100% 355KB 355.0KB/s 00:00

ctl_file_0pn4qd2t_1_1_20120301 100%6944KB 6.8MB/s 00:00

三．将10g备份restore 到11g库上

这个涉及RMAN 兼容性问题，参考我的blog：

Oracle RMAN 兼容性及不同版本和不同平台之间使用常见问题说明

http://blog.youkuaiyun.com/tianlesoftware/article/details/7232602

Oracle 11gR2 Upgrade Companion [ID785351.1]

restore步骤也可以参考Blog，大致相同：

Oracle 单实例从32位迁移到 64位方法（二）--使用 RMANRestore 示例

http://blog.youkuaiyun.com/tianlesoftware/article/details/7261970

1.1 还原控制文件

我们的RMAN 备份信息存储在控制文件里，所以要先还原控制文件，才可以使用RMAN, 其次，我们这里使用的是Source和target 的datafile 目录相同，所以不用修改pfile 等参数。如果不同，参考上面的迁移文档。

RMAN> shutdown immediate

database closed

database dismounted

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)

Oracle instance started

Total System Global Area 1402982400 bytes

Fixed Size 2228304 bytes

Variable Size 1124077488 bytes

Database Buffers 268435456 bytes

Redo Buffers 8241152 bytes

RMAN> restore controlfile from '/u01/backup/ctl_file_0fn4pobv_1_1_20120301';

Starting restore at 01-MAR-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete,elapsed time: 00:00:02

output filename=/u02/app/oracle/oradata/anqing/control01.ctl

output filename=/u02/app/oracle/fast_recovery_area/anqing/control02.ctl

Finished restore at 01-MAR-12

1.2 Restore 数据库

RMAN> set dbid=707730149

executing command: SET DBID

--这里要注意一个问题，就是这个dbid值，因为我们的RMAN 备份集是从其他的数据库上copy 过来的，备份集中的记录的是Source 的DBID值，所以这里我们也要指定dbid 等于Source 的DBID值。 RMAN 连接时会显示该值，通过视图也可以查看。

RMAN> alter database mount;

database mounted

--如果不指定dbid，而使用默认的target 的dbid值，那么restore时会报错，如：

RMAN> restore database;

RMAN-03002: failure of restore command at03/01/2012 16:13:44

RMAN-12010: automatic channel allocationinitialization failed

RMAN-06189: current DBID 706107904 does notmatch target mounted database (707730149)

指定Source 的dbid 之后，就可以正常的进行restore database 操作了：

RMAN> restore database;

Starting restore at 01-MAR-12

Starting implicit crosscheck backup at01-MAR-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

Crosschecked 12 objects

Finished implicit crosscheck backup at01-MAR-12

Starting implicit crosscheck copy at01-MAR-12

using channel ORA_DISK_1

Finished implicit crosscheck copy at01-MAR-12

searching for all files in the recoveryarea

cataloging files...

no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafilebackup set restore

channel ORA_DISK_1: specifying datafile(s)to restore from backup set

channel ORA_DISK_1: restoring datafile00002 to /u02/app/oracle/oradata/anqing/undotbs01.dbf

channel ORA_DISK_1: restoring datafile00003 to /u02/app/oracle/oradata/anqing/sysaux01.dbf

channel ORA_DISK_1: reading from backuppiece /u01/backup/anqing_lev0_0an4po9q_1_1_20120301

channel ORA_DISK_1: piecehandle=/u01/backup/anqing_lev0_0an4po9q_1_1_20120301 tag=ANQING_LEV0

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete,elapsed time: 00:00:15

channel ORA_DISK_1: starting datafilebackup set restore

channel ORA_DISK_1: specifying datafile(s)to restore from backup set

channel ORA_DISK_1: restoring datafile00001 to /u02/app/oracle/oradata/anqing/system01.dbf

channel ORA_DISK_1: restoring datafile00004 to /u02/app/oracle/oradata/anqing/users01.dbf

channel ORA_DISK_1: reading from backuppiece /u01/backup/anqing_lev0_09n4po9q_1_1_20120301

channel ORA_DISK_1: piecehandle=/u01/backup/anqing_lev0_09n4po9q_1_1_20120301 tag=ANQING_LEV0

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete,elapsed time: 00:00:26

Finished restore at 01-MAR-12

1.3 Recoverdatabase 操作：

如果直接进行recover 操作，那么在最后会遇到类似如下的信息：

RMAN-06054:media recoveryrequesting unknown archived log for thread 1 with sequence 7 and starting SCNof 832585

这里是提醒恢复到一个未知的scn号。在alter database mount之后，通过set until scn或者set until time命令设置恢复到的scn号或时间。就可以避免这个错误。

所以在执行recover 之前，我们指定一个sequence：

去source 库查看：

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u02/archivelog

Oldest online log sequence 7

Next log sequence to archive 9

Current log sequence 9

这里当前是9，所以我们recover 到9，在Target 库进行recover 操作：

RMAN> run {

2> set until sequence 9;

3> recover database;

4> }

executing command: SET until clause

Starting recover at 01-MAR-12

using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived logrestore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=7

channel ORA_DISK_1: reading from backuppiece /u01/backup/arch_0nn4qd2q_1_1_20120301

channel ORA_DISK_1: piecehandle=/u01/backup/arch_0nn4qd2q_1_1_20120301 tag=ARC_BAK

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete,elapsed time: 00:00:01

archived log filename=/u02/archivelog/1_7_776788394.arc thread=1 sequence=7

channel ORA_DISK_1: starting archived logrestore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=8

channel ORA_DISK_1: reading from backuppiece /u01/backup/arch_0on4qd2q_1_1_20120301

channel ORA_DISK_1: piecehandle=/u01/backup/arch_0on4qd2q_1_1_20120301 tag=ARC_BAK

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete,elapsed time: 00:00:01

archived log filename=/u02/archivelog/1_8_776788394.arc thread=1 sequence=8

media recovery complete, elapsed time:00:00:02

Finished recover at 01-MAR-12

注意这里不open db。

1.4 重建TEMP 表空间

--这一步很重要，做恢复，迁移，重建控制文件，都需要做这一步操作。

SQL> alter tablespace temp add tempfile'/u02/app/oracle/oradata/anqing/temp01.dbf' size 100m reuse autoextend on next10m maxsize 1000m;

也可以通过中转TMP 表空间进行替换，具体参考：

Oracle Temp 临时表空间

http://blog.youkuaiyun.com/tianlesoftware/article/details/4697417

四．升级 Instance 从10.2.0.5 到11.2.0.3

官方文档：

http://docs.oracle.com/cd/E11882_01/server.112/e17222/upgrade.htm#CACGGHJC

这个步骤11g的升级步骤相同，参考：

Oracle11.2.0.1 升级到11.2.0.3 示例

http://blog.youkuaiyun.com/tianlesoftware/article/details/6833591

4.1 用resetlogs upgrade 模式open db

SQL> alter database open resetlogs upgrade;

Database altered.

4.2 upgrade模式下运行脚本：catupgrd.sql

SQL> spool upgrade.log

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

…

PL/SQL procedure successfully completed.

SQL>

SQL>/*****************************************************************************/

SQL> /* Step 10 - SHUTDOWN THEDATABASE..!!!!!

SQL> */

SQL>/*****************************************************************************/

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

--脚本执行完毕后，自动关闭了数据库

SQL>

SQL> 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>#

SQL>

SQL> Rem Set errorlogging off

SQL> SET ERRORLOGGING OFF;

SQL>

SQL> REM END OF CATUPGRD.SQL

SQL>

SQL> REM bug 12337546 - Exit currentsqlplus session at end of catupgrd.sql.

SQL> REM This forces user to start a newsqlplus session in order

SQL> REM to connect to the upgraded db.

SQL> exit

Disconnected from Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

注意：

1.我们备份之前的一个操作，必须先utlu112i.sql脚本，然后执行这个脚本，否则就会出现如下错误。

SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')

ERROR at line 1:

ORA-01722: invalid number

2.执行这个脚本会产生大量的归档文件，50M的online redo log，切换时间在2分钟左右，所以要增加onlineredo group，从而保证足够的切换时间，不能让其出现Checkpoint not complete。

在脚本运行期间还可能会调用之前的归档文件，所以也不要删除之前的归档。

3.这个脚本运行了整个2个小时，之前直接从11.2.0.1升级到11.2.0.3运行了80分钟。而在Oracle10g下，运行该脚本在40分钟左右。

导致执行占用这么长时间的原因如下：

1. 使用的是虚拟机

2. 之前没有注意到归档的问题，后面才看到checkpoint not complete，导致等待时间。

3. 运行脚本之前忘记修改java_pool_size 和 shared_pool_size. 增加这2个值，可以减少运行时间。

SQL>alter system setjava_pool_size=512M;
SQL>alter system set shared_pool_size=800M;

The upgrade scriptcreates and alters certain data dictionary tables. It also upgrades orinstallsthe following database components in the new Oracle Database11g Release 2(11.2) database:

（1） OracleDatabase Catalog Views

（2） OracleDatabase Packages andTypes

（3） JServerJAVA Virtual Machine

（4） OracleDatabase Java Packages

（5） OracleXDK

（6） OracleReal ApplicationClusters

（7） OracleWorkspace Manager

（8） OracleMultimedia

（9） OracleXML Database

（10） OLAP AnalyticWorkspace

（11） Oracle OLAP API

（12） OLAP Catalog

（13） Oracle Text

（14） Spatial

（15） Oracle DataMining

（16） Oracle LabelSecurity

（17） MessagingGateway

（18） OracleExpression Filter

（19） Oracle RulesManager

（20） OracleEnterprise Manager Repository

（21） Oracle DatabaseVault

（22） OracleApplication Express

4.3 执行utlu112s.sql脚本

这个脚本显示升级过程的一个摘要。不需要在upgrade 模式下。

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

Oracle Database 11.2 Post-Upgrade StatusTool 03-02-2012 10:12:32

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

Oracle Server

. ORA-01187: cannot read from file because it failed verification tests

. ORA-01110: data file 201:"/u02/app/oracle/oradata/anqing/temp01.dbf"

. ORA-00942: table or view does not exist

. ORA-01187: cannot read from file because it failed verification tests

. ORA-01110: data file 201:"/u02/app/oracle/oradata/anqing/temp01.dbf"

. ORA-06512: at "SYS.DBMS_REPORT", line 841

. ORA-06512: at "SYS.PRVT_REPORT_TAGS", line 25

. ORA-06512: at line 1

. ORA-01187: cannot read from file because it failed verification tests

. ORA-01110: data file 201:"/u02/app/oracle/oradata/anqing/temp01.dbf"

. ORA-06512: at "SYS.DBMS_REPORT", line 841

. ORA-06512: at "SYS.PRVT_REPORT_REGISTRY", line 33

. ORA-06512: at line 1

. ORA-01187: cannot read from file because it failed verification tests

. ORA-01110: data file 201:"/u02/app/oracle/oradata/anqing/temp01.dbf"

. ORA-06512: at line 8

. ORA-01187: cannot read from file because it failed verification tests

. ORA-01110: data file 201:"/u02/app/oracle/oradata/anqing/temp01.dbf"

. ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2435

. ORA-06512: at line 1

. INVALID 11.2.0.3.0 00:28:07

JServer JAVA Virtual Machine

. VALID 11.2.0.3.0 00:19:25

Oracle Workspace Manager

. VALID 11.2.0.3.0 00:01:04

OLAP Analytic Workspace

. ORA-01187: cannot read from file because it failed verification tests

. ORA-01110: data file 201:"/u02/app/oracle/oradata/anqing/temp01.dbf"

. ORA-06512: at "SYS.DBMS_LOB", line 724

. ORA-06512: at "SYS.DBMS_AW_BUILD", line 72

. ORA-06512: at line 1

. INVALID 11.2.0.3.0 00:00:28

OLAP Catalog

. VALID 11.2.0.3.0 00:01:49

Oracle OLAP API

. VALID 11.2.0.3.0 00:01:00

Oracle Enterprise Manager

. VALID 11.2.0.3.0 00:15:51

Oracle XDK

. VALID 11.2.0.3.0 00:03:52

Oracle Text

. VALID 11.2.0.3.0 00:01:26

Oracle XML Database

. INVALID 11.2.0.3.0 00:19:46

Oracle Database Java Packages

. VALID 11.2.0.3.0 00:00:45

Oracle Multimedia

. INVALID 11.2.0.3.0 00:07:18

Spatial

. VALID 11.2.0.3.0 00:09:31

Oracle Expression Filter

. VALID 11.2.0.3.0 00:00:23

Oracle Rules Manager

. VALID 11.2.0.3.0 00:00:19

Gathering Statistics

. 00:05:02

Total Upgrade Time: 01:56:22

PL/SQL procedure successfully completed.

这里列举了整个upgrade 脚本及每个组件的执行时间。总共用时2个小时。但是这里报了一个与temp 表空间相关的错误。因为我在升级过程中对这个表空间进行了offline 操作，这里可能有影响，这里攒不研究这个问题。

4.4 执行catuppst.sql脚本，不需要在upgrade 模式下执行

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

TIMESTAMP

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

COMP_TIMESTAMP POSTUP_BGN 2012-03-0123:25:22

PL/SQL procedure successfully completed.

This script will migrate the Baseline dataon a pre-11g database to the 11g database.

--这个脚本用来迁移一些Baseline 数据到11g数据库中。

…..

这个脚本运行2分钟左右。

4.5 编译无效对象

SQL> select count(*) from dba_invalid_objects;

COUNT(*)

----------

5875

sql> select distinct object_name from dba_invalid_objects;

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

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2012-03-02 09:36:56

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 statusIN (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(*) FROMUTL_RECOMP_COMPILED;

--这2个SQL 可以查看脚本执行的状态。

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 FROMdba_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>#

该脚本运行需要花30+分种。都很费时间啊。

SQL> select count(*) fromdba_invalid_objects;

COUNT(*)

----------

脚本执行完毕之后还有67个无效对象。使用如下SQL 查看了一下：

sql> select distinct object_name from dba_invalid_objects;

都是和OLAP 组件相关，关于该组件，这里不讨论。

4.6 检查组件状态

SQL> col comp_name for a40

SQL> set wrap off

SQL> select comp_name,version, statusfrom dba_registry;

COMP_NAME VERSION STATUS

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

Oracle Enterprise Manager 11.2.0.3.0 VALID

OLAP Catalog 11.2.0.3.0 VALID

Spatial 11.2.0.3.0 VALID

Oracle Multimedia 11.2.0.3.0 INVALID

Oracle XML Database 11.2.0.3.0 INVALID

Oracle Text 11.2.0.3.0 VALID

Oracle Data Mining 11.2.0.3.0 VALID

Oracle Expression Filter 11.2.0.3.0 VALID

Oracle Rules Manager 11.2.0.3.0 VALID

Oracle Workspace Manager 11.2.0.3.0 VALID

Oracle Database Catalog Views 11.2.0.3.0 VALID

COMP_NAME VERSION STATUS

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

Oracle Database Packages and Types 11.2.0.3.0 INVALID

JServer JAVA Virtual Machine 11.2.0.3.0 VALID

Oracle XDK 11.2.0.3.0 VALID

Oracle Database Java Packages 11.2.0.3.0 VALID

OLAP Analytic Workspace 11.2.0.3.0 INVALID

Oracle OLAP API 11.2.0.3.0 VALID

17 rows selected.

到这里，我们需要重建OLAP. 这里不在演示，在之前的Blog里有详细的重建OLAP 组建的过程，参考如下Blog的第12小节：

Oracle 单实例从32位迁移到 64位方法（一）--直接 copydatafiles

http://blog.youkuaiyun.com/tianlesoftware/article/details/7258654

MOS 有篇文档提供了重建OLAP组件的方法：

How To Remove Or To Reinstall TheOLAPOption To 10g And 11g [ID 332351.1]

至此，restore 过程结束，但是还有一些组件是无效的，需要重建，这里就不整理，DBA 本就是一个拼人品的工作，这次Dave人品稍稍差了一点，虽然这里有一些无效组件，但不影响DB的使用，而且OLAP 本身就是一个可选的组件，只不过在安装DB时默认安装上了，其实如果用不到OLAP，这个组件都可以不装。

关于DB 组件的重建，会在近期整理出相关的Blog。

4.7 如果在升级过程中遇到问题，可以重新执行升级脚本，步骤如下：

1.Shut down the database as follows:

SQL>SHUTDOWNIMMEDIATE

2.Restart thedatabasein UPGRADE mode:

SQL> STARTUPUPGRADE

3.Rerun catupgrd.sql: --升级DB

SQL> SPOOLupgrade.log

SQL>@catupgrd.sql

Note:

You canrerunthe catupgrd.sql script as many times as necessary. The firsttimeyou run the script, there should be no error messages returned. If yourerunthe script, then the ORA-00001 messageisdisplayed. You can safely ignore this message.

--脚本执行结束会自动shutdown immediate db，所以下面的操作在normal 状态执行即可。

4.Rerun utlu112s.sql: --报告升级的过程的摘要

SQL>@utlu112s.sql

5.迁移10g的Baseline 到11g

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

6.编译无效对象：

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