一、环境介绍
1. 源数据库环境
操作系统版本: OEL 5.4 x64
数据库版本 : 10.2.0.5 x64
数据库sid名 : orcl
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 <<EOF
select instance_name, status from v\$instance;
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 <You may need to change the value of basedir.>
basedir=/u01/orabak
date=`date +%Y%m%d`
#Create pfile
sqlplus / as sysdba <<EOF
create pfile='$basedir/pfile$date.ora' from spfile;
EOF
#RMAN BACKUP
rman target / log=$basedir/backup_all_$date.log <<EOF
run{
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 1904899370 18-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;
本文出自 “koumm的linux技术博客” 博客，请务必保留此出处http://koumm.blog.51cto.com/703525/1966487