重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
Oracle10.2.0.5RMAN迁移并升级11.2.0.4一例
一、环境介绍
1. 源数据库环境
操作系统版本: OEL 5.4 x64
数据库版本 : 10.2.0.5 x64
数据库sid名 : orcl
十年的吉利网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。成都全网营销的优势是能够根据用户设备显示端的尺寸不同，自动调整吉利建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。成都创新互联从事“吉利网站设计”,“吉利网站推广”以来，每个客户项目都认真落实执行。
Oracle 10g 10.2.0.5（64bit）安装目录如下：
数据库软件：/u01/app/oracle/product/10.2.0/db_1
数据库文件：/u01/app/oracle/oradata/orcl
归档目录：/u01/archivelog
RMAN目录：/backup/dbbak/orabak
背景：一个老oracle10g数据库,RMAN 50G, 不大，因服务器坏一块盘，过保，计划迁移数据库并升级版本。
说明：源库要做一些基础环境的检查，表空间，用户名，数据文件以及是否有需要介质恢复的文件。
sqlplus / as sysdba >
dbstatus.log <
select dbid, open_mode from v\$database;
select file_name
from dba_data_files;
select file_name from dba_temp_files;
select member
from v\$logfile;
show parameter control
show parameter pfile
EOF
[oracle@db ~]$ cat dbstatus.log
SQL*Plus: Release 10.2.0.5.0 - Production on чǚ? 9? 18 14:40:57 2017
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL>
INSTANCE_NAME STATUS
----------------
------------
orcl OPEN
SQL>
DBID OPEN_MODE
---------- ----------
1226188361 READ
WRITE
SQL>
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/tjoa
/u01/app/oracle/oradata/orcl/trswcm_data01.dbf
/u01/app/oracle/oradata/orcl/trswcmvideo_data01.dbf
/u01/app/oracle/oradata/orcl/wtt.dbf
/u01/app/oracle/oradata/orcl/user02.dbf
/u01/app/oracle/oradata/orcl/jtbzoa
/u01/app/oracle/oradata/orcl/ELINK.dbf
/u01/app/oracle/oradata/orcl/USER03.dbf
/u01/app/oracle/oradata/orcl/user04.dbf
/u01/app/oracle/oradata/orcl/users05.dbf
14 rows selected.
SQL>
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01.dbf
/u01/app/oracle/oradata/orcl/trswcm_temp01.dbf
/u01/app/oracle/oradata/orcl/trswcmvideo_temp01.dbf
SQL>
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log
SQL>
NAME TYPE
VALUE
------------------------------------ -----------
------------------------------
control_file_record_keep_time
integer 7
control_files string
/u01/app/oracle/oradata/orcl/c
ontrol01.ctl,
/u01/app/oracle/
oradata/orcl/control02.ctl,
/u
01/app/oracle/oradata/orcl/con
trol03.ctl
SQL>
NAME TYPE
VALUE
------------------------------------ -----------
------------------------------
spfile string
/u01/app/oracle/product/10.2.0
/db_1/dbs/spfileorcl.ora
SQL> Disconnected from Oracle Database 10g
Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
2. 目标数据库环境
操作系统版本: RHEL 6.5 x64
数据库版本 : 11.2.0.4 x64
数据库sid名 : orcl
原来有数据库，采用DBCA将数据库删除掉。
Oracle 11g 11.2.0.4（64bit）安装目录如下（未创建数据库）
数据库软件：/u01/app/oracle/product/11.2.0/db_1
数据库文件：/u01/app/oracle/oradata/orcl
归档目录：/u01/archivelog
RMAN目录：/backup/dbbak/orabak
3. 升级思路
1）Oracle 10.2.0.2 以后才可以升级到11g 11.2.0.4。
2）创建Oracle 11.2.0.4数据库相关目录。
3)
在Oracle 10.2.0.5x64数据库上执行Oracle 11.2.0.4 x64数据库utlu112i.sql脚本。
4）备份Oracle
10.2.0.5x64数据库，并传到11g数据库服务器上。
5）还原10g数据库到11g库上，并升级。
二、RMAN备份源10g数据库
1. 在Oracle 10g库上执行utlu112i.sql脚本
道先需要将11g
$ORACLE_HOME/rdbms/admin下的utlu112i.sql脚本传到10g的$ORACLE_HOME/rdbms/admin目录下并执行。
并在10g上执行，这个脚本可以检查升级前的一些信息，必须要执行，否则在恢复时会出现错误。
scp $ORACLE_HOME/rdbms/admin/utlu112i.sql 192.168.0.96:/home/oracle/
SQL> spool upgrade.info
SQL> @/home/oracle/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 09-18-2017
14:26:54
Script Version: 11.2.0.4.0 Build:
007
.
**********************************************************************
Database:
**********************************************************************
-->
name: ORCL
--> version: 10.2.0.5.0
--> compatible:
10.2.0.3.0
--> blocksize: 8192
--> platform: Linux x86
64-bit
--> timezone file:
V4
.
**********************************************************************
Tablespaces:
[make adjustments in the current
environment]
**********************************************************************
-->
SYSTEM tablespace is adequate for the upgrade.
.... minimum required size:
1480 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
....
minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the
upgrade.
.... minimum required size: 837 MB
--> TEMP tablespace is
adequate for the upgrade.
.... minimum required size: 60
MB
.
**********************************************************************
Flashback:
OFF
**********************************************************************
**********************************************************************
Update
Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note:
Pre-upgrade tool was run on a lower version 64-bit
database.
**********************************************************************
-->
If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update
parameter changes are required.
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No
update parameter changes are
required.
.
**********************************************************************
Renamed
Parameters: [Update Oracle Database 11.2 init.ora or
spfile]
**********************************************************************
--
No renamed parameters found. No changes are
required.
.
**********************************************************************
Obsolete/Deprecated
Parameters: [Update Oracle Database 11.2 init.ora or
spfile]
**********************************************************************
-->
background_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED
replaced by "diagnostic_dest"
.
**********************************************************************
Components:
[The following database components will be upgraded or
installed]
**********************************************************************
-->
Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and
Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade]
VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle
Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace
[upgrade] VALID
--> OLAP Catalog [upgrade]
VALID
--> Oracle Text [upgrade] VALID
--> Oracle
XML Database [upgrade] VALID
--> Oracle Java Packages
[upgrade] VALID
--> Oracle interMedia [upgrade]
VALID
--> Spatial [upgrade] VALID
--> Data
Mining [upgrade] VALID
--> Expression Filter
[upgrade] VALID
--> Rule Manager [upgrade]
VALID
--> Oracle OLAP API [upgrade]
VALID
.
**********************************************************************
Miscellaneous
Warnings
**********************************************************************
WARNING:
--> Database is using a timezone file older than version 14.
.... After
the release migration, it is recommended that DBMS_DST package
.... be used
to upgrade the 10.2.0.5.0 database timezone version
.... to the latest
version which comes with the new release.
WARNING: --> Database contains
INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects
was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM
objects was written to
.... registry$nonsys_inv_objs.
.... Use
utluiobj.sql after the upgrade to identify any new invalid
.... objects due
to the upgrade.
.... USER FKSOA has 3 INVALID objects.
.... USER ZWPORTAL
has 1 INVALID objects.
.... USER XCJ has 1 INVALID objects.
.... USER
XCJOANEW has 1 INVALID objects.
.... USER EDTEST has 1 INVALID
objects.
.... USER NYTOA has 1 INVALID objects.
WARNING: --> Your
recycle bin is turned on and currently contains no objects.
.... Because it
is REQUIRED that the recycle bin be empty prior to upgrading
.... and your
recycle bin is turned on, you may need to execute the command:
PURGE
DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle
bin is empty.
WARNING: --> JOB_QUEUE_PROCESS value must be updated
....
Your current setting of "10" is too low.
.... Starting with Oracle Database 11g Release 2 (11.2), setting
....
JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and
.... DBMS_JOB jobs to
not run. Previously, setting JOB_QUEUE_PROCESSES
.... to 0 caused DBMS_JOB
jobs to not run, but DBMS_SCHEDULER jobs were
.... unaffected and would still
run. This parameter must be updated to
.... a value greater than 16 (default
value is 1000) prior to upgrade.
.... Not doing so will affect the running of
utlrp.sql after the
upgrade
.
**********************************************************************
Recommendations
**********************************************************************
Oracle
recommends gathering dictionary statistics prior to
upgrading the
database.
To gather dictionary statistics execute the following
command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
SQL>
SQL> spool off
#执行过程中无错误,
#说明：应用根据执行的情况需要进行如下处理。
（1）原库存在无效对象，需要运行utlrp.sql 脚本，重新编译无效对象
SQL> @?/rdbms/admin/utlrp.sql;
重新执行 @/home/oracle/utlu112i.sql，查看是否重新编译,
（2）执行PURGE DBA_RECYCLEBIN
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
（3）收集系统信息EXECUTE dbms_stats.gather_dictionary_stats;
在升级前收集字典统计信息，否则预升级工具( utlu102i.sql)会花费更长时间
SQL> EXECUTE
dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully
completed.
2. 备份源数据库
备份脚本内容
#!/bin/bash
# ScriptName:rmanbakup.sh
# Usage: backup all files in
oracle user environment.
# Author: koumm
# Creation: 2017-07-31
#
Version: 1.0.0
#Define variable
basedir=/u01/orabak
date=`date +%Y%m%d`
#Create pfile
sqlplus / as sysdba <
EOF
#RMAN BACKUP
rman target / log=$basedir/backup_all_$date.log
<
allocate channel c1 device type disk;
allocate channel
c2 device type disk;
backup database filesperset 4 format
'$basedir/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql
'alter system archive log current';
sql 'alter system archive log
current';
sql 'alter system archive log current';
backup archivelog all
format '$basedir/arch_%d_%T_%s_%p' delete input;
backup current controlfile
format '$basedir/ctl_%d_%T_%s_%p';
release channel c1;
release channel
c2;
}
EOF
说明：记录数据库DBID(DBID=1349744318)，恢复时有用。
3. 将/u01/orabak目录拷贝到11g数据库服务器上。
scp -r orabak xxxx:/u01/
[root@mholddb orcl]# ll -h /backup/dbbak/orabak/
总用量 55G
-rw-r----- 1
oracle oinstall 42M 9月 18 20:07 arch_ORCL_20170918_17_1
-rw-r----- 1 oracle
oinstall 13M 9月 18 20:15 arch_ORCL_20170918_18_1
-rw-r----- 1 oracle
oinstall 3.0K 9月 18 20:07 arch_ORCL_20170918_19_1
-rw-r--r-- 1 oracle
oinstall 6.2K 9月 18 20:12 backup_all_20170918.log
-rw-r----- 1 oracle
oinstall 9.9M 9月 18 20:07 ctl_ORCL_20170918_20_1
-rw-r----- 1 oracle
oinstall 20G 9月 18 20:11 full_ORCL_20170918_11_1
-rw-r----- 1 oracle
oinstall 3.1G 9月 18 20:12 full_ORCL_20170918_12_1
-rw-r----- 1 oracle
oinstall 18G 9月 18 20:07 full_ORCL_20170918_13_1
-rw-r----- 1 oracle
oinstall 14G 9月 18 20:15 full_ORCL_20170918_14_1
-rw-r----- 1 oracle
oinstall 9.9M 9月 18 20:07 full_ORCL_20170918_15_1
-rw-r----- 1 oracle
oinstall 96K 9月 18 20:03 full_ORCL_20170918_16_1
-rw-r--r-- 1 oracle
oinstall 1.1K 9月 18 20:21 pfile20170918.ora
三、11g数据库恢复准备
1. 创建11g数据库基本目录
# su - oracle
mkdir -p /u01/app/oracle/admin/orcl/{adump,dpdump,pfile,scripts}
mkdir
-p /u01/app/oracle/oradata/orcl
mkdir -p
/u01/app/oracle/flash_recovery_area/ORCL
2. 创建规档日志文件
mkdir -p /u01/archivelog
四、恢复数据库
1. 恢复参数文件
说明：由于10g实例名与11g实例名与数据库目录一致，不需要手动创建参数文件与控制文件。
# vi pfile20170801.ora
orcl.__db_cache_size=1056964608
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__shared_pool_size=503316480
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.compatible='10.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=orclXDB)'
*.fast_start_mttr_target=60
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/archivelog'
*.nls_territory='CHINA'
*.open_cursors=3000
*.pga_aggregate_target=848297984
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.diagnostic_dest='/u01/app/oracle'
注意下sga和pga的设定是否符合目的机器的配置
*.sga_target=629145600
*.pga_aggregate_target=25165824
删除*.background_dump_dest和*.user_dump_dest
加上
*.diagnostic_dest='/u01/app/oracle'
修改后复制一份到$ORACLE_HOME/dbs/init$ORACLE_SID.ora
[oracle@mholddb orabak]$ cp pfile20170918.ora /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora
2. nomount启动数据库
[oracle@mholddb orabak]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 18 20:22:21 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora';
Oracle 实例已启动
3. 恢复控制文件
RMAN> restore controlfile from '/backup/dbbak/orabak/ctl_ORCL_20170918_20_1';
Starting restore at 18-SEP-17
using target database control file instead
of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1:
SID=197 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore
complete, elapsed time: 00:00:01
output file
name=/u01/app/oracle/oradata/orcl/control01.ctl
output file
name=/u01/app/oracle/oradata/orcl/control02.ctl
output file
name=/u01/app/oracle/oradata/orcl/control03.ctl
Finished restore at
18-SEP-17
RMAN>
4. 恢复数据库
RMAN> shutdown immediate;
Oracle instance shut down
原备份日志backup_all_20170801.log中有DBID记录。
RMAN> set DBID=1226188361
executing command: SET DBID
RMAN> startup mount;
connected to target database (not started)
Oracle instance
started
database mounted
Total System Global Area 1219260416 bytes
Fixed Size 2252744 bytes
Variable Size
704643128 bytes
Database Buffers 503316480 bytes
Redo
Buffers 9048064 bytes
RMAN>
RMAN> delete noprompt expired backupset;
RMAN> crosscheck
backupset;
using channel ORA_DISK_1
crosschecked backup piece: found to be
'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_12_1
RECID=11 STAMP=955031770
crosschecked backup piece: found to be
'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_11_1
RECID=12 STAMP=955031784
crosschecked backup piece: found to be
'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_13_1
RECID=13 STAMP=955032406
crosschecked backup piece: found to be
'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_15_1
RECID=14 STAMP=955035803
crosschecked backup piece: found to be
'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_16_1
RECID=15 STAMP=955035849
crosschecked backup piece: found to be
'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/full_ORCL_20170918_14_1
RECID=16 STAMP=955035323
crosschecked backup piece: found to be
'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/arch_ORCL_20170918_18_1
RECID=17 STAMP=955036896
crosschecked backup piece: found to be
'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/arch_ORCL_20170918_17_1
RECID=18 STAMP=955036896
crosschecked backup piece: found to be
'AVAILABLE'
backup piece handle=/backup/dbbak/orabak/arch_ORCL_20170918_19_1
RECID=19 STAMP=955036900
Crosschecked 9 objects
RMAN> restore database;
Starting restore at 18-SEP-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel
ORA_DISK_1: specifying datafile(s) to restore from backup set
channel
ORA_DISK_1: restoring datafile 00005 to
/u01/app/oracle/oradata/orcl/tjoa
channel ORA_DISK_1: restoring datafile
00012 to /u01/app/oracle/oradata/orcl/USER03.dbf
channel ORA_DISK_1: reading
from backup piece /backup/dbbak/orabak/full_ORCL_20170918_12_1
channel
ORA_DISK_1: piece handle=/backup/dbbak/orabak/full_ORCL_20170918_12_1
tag=TAG20170918T143606
channel ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:01:35
channel ORA_DISK_1:
starting datafile backup set restore
channel ORA_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile
00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1:
restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel
ORA_DISK_1: restoring datafile 00010 to
/u01/app/oracle/oradata/orcl/jtbzoa
channel ORA_DISK_1: restoring datafile
00014 to /u01/app/oracle/oradata/orcl/users05.dbf
channel ORA_DISK_1: reading
from backup piece /backup/dbbak/orabak/full_ORCL_20170918_11_1
channel
ORA_DISK_1: piece handle=/backup/dbbak/orabak/full_ORCL_20170918_11_1
tag=TAG20170918T143606
channel ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:02:16
channel ORA_DISK_1:
starting datafile backup set restore
channel ORA_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile
00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1:
restoring datafile 00002 to
/u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring
datafile 00008 to /u01/app/oracle/oradata/orcl/wtt.dbf
channel ORA_DISK_1:
restoring datafile 00013 to /u01/app/oracle/oradata/orcl/user04.dbf
channel
ORA_DISK_1: reading from backup piece
/backup/dbbak/orabak/full_ORCL_20170918_13_1
channel ORA_DISK_1: piece
handle=/backup/dbbak/orabak/full_ORCL_20170918_13_1
tag=TAG20170918T143606
channel ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:01:45
channel ORA_DISK_1:
starting datafile backup set restore
channel ORA_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile
00006 to /u01/app/oracle/oradata/orcl/trswcm_data01.dbf
channel ORA_DISK_1:
restoring datafile 00007 to
/u01/app/oracle/oradata/orcl/trswcmvideo_data01.dbf
channel ORA_DISK_1:
restoring datafile 00009 to /u01/app/oracle/oradata/orcl/user02.dbf
channel
ORA_DISK_1: restoring datafile 00011 to
/u01/app/oracle/oradata/orcl/ELINK.dbf
channel ORA_DISK_1: reading from
backup piece /backup/dbbak/orabak/full_ORCL_20170918_14_1
channel ORA_DISK_1:
piece handle=/backup/dbbak/orabak/full_ORCL_20170918_14_1
tag=TAG20170918T143606
channel ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:01:56
Finished restore at
18-SEP-17
RMAN>
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
17 12.70M
DISK 00:00:02 18-SEP-17
BP Key: 17 Status:
AVAILABLE Compressed: NO Tag: TAG20170918T160134
Piece Name:
/backup/dbbak/orabak/arch_ORCL_20170918_18_1
List of Archived Logs in backup set 17
Thrd Seq Low SCN Low
Time Next SCN Next Time
---- ------- ---------- --------- ----------
---------
1 30547 1904894964 18-SEP-17 1904899341 18-SEP-17
1
30548 1904899341 18-SEP-17 1904899351 18-SEP-17
1 30549 1904899351
18-SEP-17 1904899359 18-SEP-17
BS Key Size Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
18 41.45M
DISK 00:00:06 18-SEP-17
BP Key: 18 Status:
AVAILABLE Compressed: NO Tag: TAG20170918T160134
Piece Name:
/backup/dbbak/orabak/arch_ORCL_20170918_17_1
List of Archived Logs in backup set 18
Thrd Seq Low SCN Low
Time Next SCN Next Time
---- ------- ---------- --------- ----------
---------
1 30546 1904882200 18-SEP-17 1904894964 18-SEP-17
BS Key Size Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
19 2.50K
DISK 00:00:01 18-SEP-17
BP Key: 19 Status:
AVAILABLE Compressed: NO Tag: TAG20170918T160134
Piece Name:
/backup/dbbak/orabak/arch_ORCL_20170918_19_1
List of Archived Logs in backup set 19
Thrd Seq Low SCN Low
Time Next SCN Next Time
---- ------- ---------- --------- ----------
---------
1 30550 1904899359 18-SEP-17 1904899365 18-SEP-17
1
30551 1904899365 18-SEP-17 190489937018-SEP-17
查看源库在备份完成的SCN。
RMAN> recover database until scn 1904899370;
Starting recover at 18-SEP-17
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default
destination
channel ORA_DISK_1: restoring archived log
archived log
thread=1 sequence=30547
channel ORA_DISK_1: restoring archived
log
archived log thread=1 sequence=30548
channel ORA_DISK_1: restoring
archived log
archived log thread=1 sequence=30549
channel ORA_DISK_1:
reading from backup piece
/backup/dbbak/orabak/arch_ORCL_20170918_18_1
channel ORA_DISK_1: piece
handle=/backup/dbbak/orabak/arch_ORCL_20170918_18_1
tag=TAG20170918T160134
channel ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file
name=/u01/archivelog1_30547_698407628.dbf thread=1 sequence=30547
archived
log file name=/u01/archivelog1_30548_698407628.dbf thread=1
sequence=30548
archived log file name=/u01/archivelog1_30549_698407628.dbf
thread=1 sequence=30549
channel ORA_DISK_1: starting archived log restore to
default destination
channel ORA_DISK_1: restoring archived log
archived
log thread=1 sequence=30550
channel ORA_DISK_1: restoring archived
log
archived log thread=1 sequence=30551
channel ORA_DISK_1: reading from
backup piece /backup/dbbak/orabak/arch_ORCL_20170918_19_1
channel ORA_DISK_1:
piece handle=/backup/dbbak/orabak/arch_ORCL_20170918_19_1
tag=TAG20170918T160134
channel ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file
name=/u01/archivelog1_30550_698407628.dbf thread=1 sequence=30550
archived
log file name=/u01/archivelog1_30551_698407628.dbf thread=1
sequence=30551
media recovery complete, elapsed time: 00:00:01
Finished
recover at 18-SEP-17
RMAN>
5. 升级模式升级数据库
1）升级模式resetlogs启动数据库
[oracle@mholddb ~]$ sqlplus /nolog
SQL> conn / as sysdba;
SQL> alter database open resetlogs upgrade;
Database altered.
SQL>
2）重建temp临时表空间数据文件
SQL> alter tablespace temp add tempfile
'/u01/app/oracle/oradata/orcl/temp02.dbf' size 100m reuse autoextend on next 10m
maxsize 1000m;
Tablespace altered.
6. upgrade模式下运行脚本：catupgrd.sql
SQL> @?/rdbms/admin/catupgrd.sql
... 大约执行了25分钟。
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> REM END OF CATUPGRD.SQL
SQL>
SQL> REM bug 12337546 -
Exit current sqlplus session at end of catupgrd.sql.
SQL>
REM This forces user to start a new sqlplus session in
order
SQL> REM to connect to the upgraded db.
SQL>
exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options 断开
7. 执行utlu112s.sql脚本
这个脚本显示升级过程的一个摘要。不需要在upgrade 模式下。
[oracle@mholddb ~]$ sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 21:03:52 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size
2253664 bytes
Variable Size 570428576 bytes
Database
Buffers 1023410176 bytes
Redo Buffers 7319552
bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 09-18-2017
21:04:53
.
Component Current
Version Elapsed Time
Name Status
Number HH:MM:SS
.
Oracle
Server
. VALID 11.2.0.4.0
00:06:07
JServer JAVA Virtual
Machine
. VALID 11.2.0.4.0
00:03:40
Oracle Workspace
Manager
. VALID 11.2.0.4.0
00:00:19
OLAP Analytic Workspace
.
VALID 11.2.0.4.0 00:01:07
OLAP
Catalog
. VALID 11.2.0.4.0
00:00:22
Oracle OLAP API
.
VALID 11.2.0.4.0 00:00:12
Oracle
XDK
. VALID 11.2.0.4.0
00:01:11
Oracle Text
. VALID
11.2.0.4.0 00:00:23
Oracle XML
Database
. VALID 11.2.0.4.0
00:02:06
Oracle Database Java
Packages
. VALID 11.2.0.4.0
00:00:06
Oracle Multimedia
.
VALID 11.2.0.4.0
00:01:50
Spatial
. VALID
11.2.0.4.0 00:03:08
Oracle Expression
Filter
. VALID 11.2.0.4.0
00:00:05
Oracle Rule Manager
.
VALID 11.2.0.4.0 00:00:04
Final
Actions
.
00:00:14
Total Upgrade Time: 00:21:01
PL/SQL procedure successfully completed.
SQL>
PL/SQL 过程已成功完成。
SQL>
8. 编译无效对象
# 查看无效对象数量
SQL> select count(*) from dba_invalid_objects;
COUNT(*)
----------
1275
# 编译无效对象，如果与之前的无效对象数量一样，也可以不用去管。
SQL> @?/rdbms/admin/utlrp.sql
执行完成。
# 查看无效对象
SQL> select count(*) from dba_invalid_objects;
未选定行
SQL> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
COUNT(*)
----------
0
9. 检查数据库状态
（1）检查组件状态
SQL> select comp_name,version, status from dba_registry;
COMP_NAME VERSION
STATUS
----------------------------------------
------------------------------ ----------------------
Oracle Enterprise
Manager 11.2.0.4.0 VALID
OLAP
Catalog 11.2.0.4.0
VALID
Spatial 11.2.0.4.0
VALID
Oracle Multimedia 11.2.0.4.0
VALID
Oracle XML Database 11.2.0.4.0
VALID
Oracle Text 11.2.0.4.0
VALID
Oracle Data Mining 11.2.0.4.0
VALID
Oracle Expression Filter 11.2.0.4.0
VALID
Oracle Rules Manager 11.2.0.4.0
VALID
Oracle Workspace Manager 11.2.0.4.0
VALID
Oracle Database Catalog Views 11.2.0.4.0
VALID
Oracle Database Packages and Types 11.2.0.4.0
VALID
JServer JAVA Virtual Machine 11.2.0.4.0
VALID
Oracle XDK 11.2.0.4.0
VALID
Oracle Database Java Packages 11.2.0.4.0
VALID
OLAP Analytic Workspace 11.2.0.4.0
VALID
Oracle OLAP API 11.2.0.4.0
VALID
（2）检查补丁状态
set line 150
col ACTION_TIME for a30
col ACTION for a15
col
NAMESPACE for a8
col VERSION for a10
col BUNDLE_SERIES for a5
col
COMMENTS for a30
select * from
dba_registry_history;
ACTION_TIME ACTION
NAMESPAC VERSION ID COMMENTS
------------------------------
--------------- -------- ---------- ----------
------------------------------
25-APR-12 09.56.48.615853 AM VIEW
RECOMPILE 8289601 view recompilation
25-APR-12
09.56.48.651566 AM UPGRADE SERVER 10.2.0.5.0 Upgraded
from 10.2.0.4.0
18-SEP-17 09.00.00.659377 PM VIEW
INVALIDATE 8289601 view invalidation
18-SEP-17
09.00.14.866796 PM UPGRADE SERVER 11.2.0.4.0 Upgraded
from 10.2.0.5.0
SQL>
10，其它生成参数文件
通过pfile创建spfile文件。
SQL> create spfile from pfile;
File created.
SQL>
11，生成密码文件
$ orapwd file='/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl' password=oracle entries=10 force=y
12，修改compatible参数：
SQL> ALTER SYSTEM SET COMPATIBLE = '11.2.0' SCOPE=SPFILE;
13, Timezone数据库层面的升级，下面是手动执行情况，还有脚本升级方式，暂略。
注意：该步骤是否执行是和Step 6中的检查结果相关的，只有当Timezone的版本小于14时，才需要执行该步骤。
主要参考：Updating
the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST [ID
977512.1]
SQL> SELECT version FROM v$timezone_file;
根据当前timezone的版本，又分三种情况：
1）等于14：这已经是11g需要的版本了，所以升级前后都不需要做任何事，这种情况很罕见。
2）高于14：升级前，必须得给11g软件打上该timezone版本的DST补丁，这种情况也很罕见。
3）低于14：大多数都是这种情况，在升级前不需要在11g软件层面打补丁，在升级后需要再数据库层面将Timezone升级至14，具体看后面的步骤
1）Timezone升级前的准备工作：
先检查一下当前的timezone版本：
conn / as sysdba
SELECT version FROM v$timezone_file;
SELECT
PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
10.2.0.4、5 timezone是4 一个典型的输出是：
PROPERTY_NAME VALUE
------------------------------
------------------------------
DST_PRIMARY_TT_VERSION
4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
然后开始准备工作：
alter session set "_with_subquery"=materialize;
exec
DBMS_DST.BEGIN_PREPARE(14);
接着检查准备状态：
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM
DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY
PROPERTY_NAME;
一个典型的输出是：
PROPERTY_NAME
VALUE
------------------------------
------------------------------------------------------------
DST_PRIMARY_TT_VERSION
4
DST_SECONDARY_TT_VERSION 14
DST_UPGRADE_STATE
PREPARE
执行脚本：
-- truncate logging tables if they exist.
TRUNCATE TABLE
SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE
TABLE sys.dst$error_table;
-- log affected data
set serveroutput
on
BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables =>
'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table =>
'sys.dst$error_table');
END;
/
下面的语句都不能有返回结果：
SELECT * FROM sys.dst$affected_tables;
SELECT * FROM
sys.dst$error_table;
SELECT * FROM sys.dst$error_table where ERROR_NUMBER=
'1883';
SELECT * FROM sys.dst$error_table where ERROR_NUMBER=
'1878';
SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in
('1878','1883');
执行脚本：
-- end prepare window, the rows above will stay in those
tables.
EXEC DBMS_DST.END_PREPARE;
-- check if this is ended
SELECT PROPERTY_NAME, SUBSTR(property_value, 1,
30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE
'DST_%'
ORDER BY PROPERTY_NAME;
一个典型的输出是：
PROPERTY_NAME VALUE
------------------------------
------------------------------
DST_PRIMARY_TT_VERSION
4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
2）真正开始升级Timezone
conn / as sysdba
shutdown immediate;
startup upgrade;
set
serveroutput on
purge dba_recyclebin;
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE
sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
alter session
set "_with_subquery"=materialize;
EXEC DBMS_DST.BEGIN_UPGRADE(14);
SELECT
PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM
DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY
PROPERTY_NAME;
一个典型的输出是：
PROPERTY_NAME
VALUE
------------------------------
------------------------------
DST_PRIMARY_TT_VERSION
14
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE
UPGRADE
下面这条语句应该没有返回结果：
SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM
ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
no rows selected
重启数据库：
shutdown immediate
startup
升级相关的table：执行脚本：
alter session set "_with_subquery"=materialize;
set serveroutput on
VAR
numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel
=> TRUE,
log_errors => TRUE,
log_errors_table =>
'SYS.DST$ERROR_TABLE',
log_triggers_table =>
'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time =>
FALSE,
error_on_nonexisting_time =>
FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
Failures:0
PL/SQL procedure successfully completed.
如果没有错误，则结束升级：
VAR fail
number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'||
:fail);
END;
/
An upgrade window has been successfully ended.
Failures:0
PL/SQL procedure successfully completed.
最后一次检查：
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM
DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY
PROPERTY_NAME;
典型输出是：
PROPERTY_NAME VALUE
------------------------------
------------------------------
DST_PRIMARY_TT_VERSION
14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE
NONE
SELECT * FROM v$timezone_file;
FILENAME
VERSION
-------------------- ----------
timezlrg_14.dat
14
14. 如果在升级过程中遇到问题，可以重新执行升级脚本（参数）
---------------------------------------------------------
1）Shut down the database as follows:
SQL>SHUTDOWN IMMEDIATE
2）Restart thedatabasein UPGRADE mode:
SQL> STARTUPUPGRADE
3）Rerun catupgrd.sql: --升级DB
SQL>@catupgrd.sql
4）Rerun utlu112s.sql: --报告升级的过程的摘要
SQL> @utlu112s.sql
5）迁移10g的Baseline 到11g
SQL> @?/rdbms/admin/catuppst.sql
6）编译无效对象：
SQL> @?/rdbms/admin/utlrp.sql
注意：升级之后执行utluiobj.sql 列出失效对象的对比信息。
SQL>
@?/rdbms/admin/utluiobj.sql;
新闻名称：Oracle10.2.0.5RMAN迁移并升级11.2.0.4一例
转载注明：http://cqcxhl.com/article/gsjdse.html