源库:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
备库:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
在备份之前需要执行一下utlu112i.sql脚本,如果不执行,跟新数据字典会报下面这个错误
从备库拷这个脚本到源库的ORACLE_HOME\rdbms\admin下
SQL> @?/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 04-07-2016 10:25:24
.
**********************************************************************
Database:
**********************************************************************
--> name: PROD1
--> version: 10.2.0.5.0
--> compatible: 10.2.0.5.0
--> blocksize: 8192
--> platform: Microsoft Windows x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 696 MB
.... AUTOEXTEND additional space required: 246 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 449 MB
.... AUTOEXTEND additional space required: 419 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 465 MB
.... AUTOEXTEND additional space required: 215 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 78 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 672 MB
WARNING: --> "java_pool_size" needs to be increased to at least 128 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
--> core_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 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.5.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... CTXSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER PUBLIC has 1 INVALID objects.
.... USER SYS has 2 INVALID objects.
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer to the Upgrade Guide for instructions to configure Network ACLs.
.... USER ORACLE_OCM has dependent objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING: --> log_archive_format must be updated.
.... As of 10.1, log_archive_format requires a %r format qualifier
.... be present in its format string. Your current setting is:
.... log_archive_format='ARC%S_%R.%T'.
.... Archive Logging is currently ON, and failure to add the %r to the
.... format string will prevent the upgraded database from starting up.
WARNING:--> recycle bin in use.
.... Your recycle bin turned on.
.... It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command: PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
.
PL/SQL 过程已成功完成。
做个rman全备:
SQL> ho rman target /
恢复管理器: Release 10.2.0.5.0 - Production on 星期四 4月 7 10:30:13 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
连接到目标数据库: PROD1 (DBID=2129729023)
RMAN> backup database plus archivelog;
启动 backup 于 07-4月 -16
当前日志已存档
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=143 devtype=DISK
通道 ORA_DISK_1: 正在启动存档日志备份集
通道 ORA_DISK_1: 正在指定备份集中的存档日志
输入存档日志线程 =1 序列 =2 记录 ID=1 时间戳=908390071
输入存档日志线程 =1 序列 =3 记录 ID=2 时间戳=908475584
输入存档日志线程 =1 序列 =4 记录 ID=3 时间戳=908487756
输入存档日志线程 =1 序列 =5 记录 ID=4 时间戳=908488374
输入存档日志线程 =1 序列 =6 记录 ID=5 时间戳=908488425
输入存档日志线程 =1 序列 =7 记录 ID=6 时间戳=908489234
输入存档日志线程 =1 序列 =8 记录 ID=7 时间戳=908489243
输入存档日志线程 =1 序列 =9 记录 ID=8 时间戳=908533823
通道 ORA_DISK_1: 正在启动段 1 于 07-4月 -16
通道 ORA_DISK_1: 已完成段 1 于 07-4月 -16
段句柄=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PROD1\BACKUPSET\2016_04_07\O
1_MF_ANNNN_TAG20160407T103023_CJCKL0T1_.BKP 标记=TAG20160407T103023 注释=NONE
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:04
完成 backup 于 07-4月 -16
启动 backup 于 07-4月 -16
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集中的数据文件
输入数据文件 fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\SYSTEM01.DBF
输入数据文件 fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\SYSAUX01.DBF
输入数据文件 fno=00005 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\EXAMPLE01.DBF
输入数据文件 fno=00006 name=C:\ORACLE\CHENG01.DBF
输入数据文件 fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\UNDOTBS01.DBF
输入数据文件 fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\USERS01.DBF
通道 ORA_DISK_1: 正在启动段 1 于 07-4月 -16
通道 ORA_DISK_1: 已完成段 1 于 07-4月 -16
段句柄=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PROD1\BACKUPSET\2016_04_07\O
1_MF_NNNDF_TAG20160407T103027_CJCKL424_.BKP 标记=TAG20160407T103027 注释=NONE
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:46
完成 backup 于 07-4月 -16
启动 backup 于 07-4月 -16
当前日志已存档
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 正在启动存档日志备份集
通道 ORA_DISK_1: 正在指定备份集中的存档日志
输入存档日志线程 =1 序列 =10 记录 ID=9 时间戳=908533873
通道 ORA_DISK_1: 正在启动段 1 于 07-4月 -16
通道 ORA_DISK_1: 已完成段 1 于 07-4月 -16
段句柄=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PROD1\BACKUPSET\2016_04_07\O
1_MF_ANNNN_TAG20160407T103113_CJCKML8H_.BKP 标记=TAG20160407T103113 注释=NONE
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:02
完成 backup 于 07-4月 -16
启动 Control File and SPFILE Autobackup 于 07-4月 -16
段 handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PROD1\AUTOBACKUP\2016_04_
07\O1_MF_S_908533875_CJCKMMDD_.BKP comment=NONE
完成 Control File and SPFILE Autobackup 于 07-4月 -16
RMAN> exit
Microsoft Windows [版本 6.0.6002]
版权所有 (C) 2006 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>set ORACLE_SID=PROD1
C:\Users\Administrator>oradiM -new -sid PROD1
实例已创建。
C:\Users\Administrator>rman target /
恢复管理器: Release 11.2.0.1.0 - Production on 星期四 4月 7 11:52:12 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
已连接到目标数据库 (未启动)
RMAN> startup nomount;
启动失败: ORA-01078: failure in processing system parameters
LRM-00109: ???????????????? 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABA
SE\INITPROD1.ORA'
在没有参数文件的情况下启动 Oracle 实例以检索 spfile
Oracle 实例已启动
系统全局区域总计 158662656 字节
Fixed Size 2173840 字节
Variable Size 88081520 字节
Database Buffers 62914560 字节
Redo Buffers 5492736 字节
RMAN> restore spfile from 'C:\oracle\product\10.2.0\flash_recovery_area\PROD1\AUTOBACKUP\2016_04_07\O1_MF_S_908533875_CJCKMMDD_.BKP';
启动 restore 于 07-4月 -16
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=18 设备类型=DISK
通道 ORA_DISK_1: 正在从 AUTOBACKUP C:\oracle\product\10.2.0\flash_recovery_area\
PROD1\AUTOBACKUP\2016_04_07\O1_MF_S_908533875_CJCKMMDD_.BKP 还原 spfile
通道 ORA_DISK_1: 从 AUTOBACKUP 还原 SPFILE 已完成
完成 restore 于 07-4月 -16
RMAN> startup force nomount;
Oracle 实例已启动
系统全局区域总计 609624064 字节
Fixed Size 2178176 字节
Variable Size 176161664 字节
Database Buffers 427819008 字节
Redo Buffers 3465216 字节
RMAN> restore controlfile from 'C:\oracle\product\10.2.0\flash_recovery_area\PROD1\AUTOBACKUP\2016_04_07\O1_MF_S_908533875_CJCKMMDD_.BKP';
启动 restore 于 07-4月 -16
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 正在还原控制文件
通道 ORA_DISK_1: 还原完成, 用时: 00:00:01
输出文件名=C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\CONTROL01.CTL
输出文件名=C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\CONTROL02.CTL
输出文件名=C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\CONTROL03.CTL
完成 restore 于 07-4月 -16
RMAN> alter database mount;
数据库已装载
释放的通道: ORA_DISK_1
RMAN> restore database;
启动 restore 于 07-4月 -16
使用通道 ORA_DISK_1
正在略过数据文件 6; 已还原到文件 C:\ORACLE\CHENG01.DBF
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00001 还原到 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\SYSTEM01.DBF
通道 ORA_DISK_1: 将数据文件 00002 还原到 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\UNDOTBS01.DBF
通道 ORA_DISK_1: 将数据文件 00003 还原到 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\SYSAUX01.DBF
通道 ORA_DISK_1: 将数据文件 00004 还原到 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\USERS01.DBF
通道 ORA_DISK_1: 将数据文件 00005 还原到 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\EXAMPLE01.DBF
通道 ORA_DISK_1: 正在读取备份片段 C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PROD1\BACKUPSET\2016_04_07\O1_MF_NNNDF_TAG20160407T103027_CJCKL424_.BKP
通道 ORA_DISK_1: 段句柄 = C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PROD1\BACKUPSET\2016_04_07\O1_MF_NNNDF_TAG20160407T103027_CJCKL424_.BKP 标记 = TAG20160407T103027
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:46
完成 restore 于 07-4月 -16
RMAN> recover database;
启动 recover 于 07-4月 -16
使用通道 ORA_DISK_1
未处理数据文件 6, 因为文件是只读的
正在开始介质的恢复
线程 1 序列 10 的归档日志已作为文件 C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA
\PROD1\ARCHIVELOG\2016_04_07\O1_MF_1_10_CJCKMK42_.ARC 存在于磁盘上
归档日志文件名=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PROD1\ARCHIVELOG\201
6_04_07\O1_MF_1_10_CJCKMK42_.ARC 线程=1 序列=10
无法找到归档日志
归档日志线程=1 序列=11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: recover 命令 (在 04/07/2016 12:13:43 上) 失败
RMAN-06054: 介质恢复正在请求未知的线程 1 序列 11 的归档日志以及起始 SCN 694939
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: alter db 命令 (在 04/07/2016 12:14:04 上) 失败
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: alter db 命令 (在 04/07/2016 12:14:26 上) 失败
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
进程 ID: 2268
会话 ID: 1 序列号: 9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-03114: 未连接到 ORACLE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: alter db 命令 (在 04/07/2016 12:14:26 上) 失败
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
进程 ID: 2268
会话 ID: 1 序列号: 9
C:\oracle\product\10.2.0\flash_recovery_area\PROD1\AUTOBACKUP\2016_04_07>sqlplus/ as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 4月 7 12:15:42 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
已连接到空闲例程。
SQL> startup upgrade;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE 例程已经启动。
Total System Global Area 609624064 bytes
Fixed Size 2178176 bytes
Variable Size 176161664 bytes
Database Buffers 427819008 bytes
Redo Buffers 3465216 bytes
数据库装载完毕。
数据库已经打开。
SQL> @?/rdbms/admin/catupgrd.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 4月 7 12:46:17 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup force;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE 例程已经启动。
Total System Global Area 609624064 bytes
Fixed Size 2178176 bytes
Variable Size 218104704 bytes
Database Buffers 385875968 bytes
Redo Buffers 3465216 bytes
数据库装载完毕。
ORA-03113: 通信通道的文件结尾
进程 ID: 2956
会话 ID: 1 序列号: 5
SQL> startup mount;
ORA-24324: 未初始化服务句柄
ORA-01041: 内部错误, hostdef 扩展名不存在
SQL> shutdown abort;
ORA-24324: 未初始化服务句柄
ORA-01041: 内部错误, hostdef 扩展名不存在
SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断 开
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 4月 7 12:47:27 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
已连接到空闲例程。
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE 例程已经启动。
Total System Global Area 609624064 bytes
Fixed Size 2178176 bytes
Variable Size 218104704 bytes
Database Buffers 385875968 bytes
Redo Buffers 3465216 bytes
数据库装载完毕。
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\SYSTEM01.DBF'
SQL> ho rman target /
恢复管理器: Release 11.2.0.1.0 - Production on 星期四 4月 7 12:50:07 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
已连接到目标数据库: PROD1 (DBID=2129729023, 未打开)
RMAN> restore datafile 1;
启动 restore 于 07-4月 -16
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=20 设备类型=DISK
数据文件 1 已经还原到文件 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\SYSTEM01.DBF 中
没有完成还原; 所有文件均为只读或脱机文件或者已经还原
完成 restore 于 07-4月 -16
RMAN> recover database;
启动 recover 于 07-4月 -16
使用通道 ORA_DISK_1
未处理数据文件 6, 因为文件是只读的
正在开始介质的恢复
线程 1 序列 36 的归档日志已作为文件 C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\REDO0
3.LOG 存在于磁盘上
归档日志文件名=C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\REDO03.LOG 线程=1 序列=36
介质恢复完成, 用时: 00:00:00
完成 recover 于 07-4月 -16
RMAN> exit
恢复管理器完成。
SQL> alter database open resetlogs;
数据库已更改。
这里要删除2个参数,出现错误信息提示‘ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance’,发现
SQL> create pfile='C:\pfile.ora' from spfile;
文件已创建。
SQL> exit
将pfile用记事本打开,删除background_dump_dest和user_dump_dest2个参数;
从Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 4月 7 13:42:23 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> create spfile from pfile='C:\pfile.ora';
文件已创建。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 609624064 bytes
Fixed Size 2178176 bytes
Variable Size 222299008 bytes
Database Buffers 381681664 bytes
Redo Buffers 3465216 bytes
数据库装载完毕。
数据库已经打开。
SQL>
--END--
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30606744/viewspace-2076740/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30606744/viewspace-2076740/