10.2.0.4 --->11.2.0.3.6 升级步骤

############################################
## 升级准备期 ##
############################################
本部分工作在主库和备库上分别执行
1,确认需要升级数据库的版本，直接升级到11.r2对数据库版本有要求，分别为9.2.0.8 or higher，10.2.0.2 or higher，11.1.0.6 or higher
$ sqlplus / as sysdba
SQL> select * from v$version;

2.上传安装软件

3.准备VNC
下载RealVNC，并安装
--在服务器上
# chkconfig --list vncserver     ――查看vncserver是否开启
# chkconfig --level 2345 vncserver on     ――启动vncserver
# vncserver :n     ――自动开启一个vnc服务,n为空闲端口号
# vncpasswd  ――设置登录密码
# export DISPLAY='localhost:1.0'
# xhost +
--在客户端
登陆VNC viewer，输入ip地址和端口号
--其他常用命令如下
# vi .vnc/xstartup ――清空内容，加入gnome-session&
# vncserver –kill :1 ――KILL掉刚才生成的一个VNC服务
# vncserver  ――重新开启VNC服务

4.安装11g软件
> 取消勾选“I wish to receive security updates via My Oracle Support”
> 选择“Install database software only”
> 选择“Single instance database installation”
> Selected Languages选择“English”
> 选择“Enterprise Edition(4.29GB)”
> 注意下Oracle Base的路径（一般没问题），Software Location修改为“/u01/app/oracle/product/11.2.0/db”
> OSDBA Group选择DBA，OSOPER Group选择oinstall
> 检查完环境后，点击“Fix & Check Again”根据提示用root执行脚本来修改系统参数
> 根据提示去补充rpm包
--挂OS镜像去补rpm
--# mount -o loop -t iso9660 mount -o loop -t iso9660 xxxxxx.iso /mnt
--不依赖强制安装
--# rpm -Uvh glibc-2.3.4-2.41.x86_64.rpm --force --nodeps
> 检查参数，点击“Finish”开始安装

5.安装grid软件(如果使用asm的话，这个部分是必须的)
> 选择“Install Oracle Grid Infrastructure software only”
> Selected Languages选择“English”
> Oracle ASM DBA(OSDBA FOR ASM)Group dba Oracle ASM Administrator(OSASM)Group Oinstall
> 注意下Oracle Base的路径/u01/app/oracle，Software Location修改为“/u01/app/oracle/product/11.2.0.3/grid”
> 检查完环境后，点击“Fix & Check Again”根据提示用root执行脚本来修改系统参数
> 根据提示去补充rpm包
--挂OS镜像去补rpm
--# mount -o loop -t iso9660 mount -o loop -t iso9660 xxxxxx.iso /mnt
--不依赖强制安装
--# rpm -Uvh glibc-2.3.4-2.41.x86_64.rpm --force --nodeps
> 检查参数，点击“install”开始安装
> 根据提示使用root执行root.sh
> gi软件安装完毕

6.编辑两个新bash_profile
# su - oracle
$ cd ~
$ cp .bash_profile bash_profile_11203
$ vi bash_profile_11203
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=billdb
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db
export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/db/bin:/bin:PATH
export TMP=/u01/tmp
export TMPDIR=/u01/tmp
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export LC_TYPE=en_US.UTF-8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export SQLPATH=/home/oracle/sqlpath_dba
alias sql='sqlplus / as sysdba'
alias jdb='export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db;export ORACLE_SID=myora'
alias jgrid='export ORACLE_HOME=/u01/app/11.2.0/grid;export ORACLE_SID=+ASM'
alias jlsnrctl='ORACLE_OLD=$ORACLE_HOME; export ORACLE_HOME=/u01/app/11.2.0/grid;lsnrctl status;ORACLE_HOME=$ORACLE_OLD;ORACLE_OLD='
alias bdump='cd /u02/app/oracle/diag/rdbms/logdb/logdb1/trace'

$ vi bash_profile_asm
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=+ASM
export ORACLE_HOME=/u01/app/11.2.0/grid
export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/11.2.0/grid/bin:/bin
export TMP=/u01/tmp
export TMPDIR=/u01/tmp
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export LC_TYPE=en_US.UTF-8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export SQLPATH=/home/oracle/sqlpath_dba
alias sql='sqlplus / as sysasm'
alias jdb='export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db;export ORACLE_SID=myora'
alias jgrid='export ORACLE_HOME=/u01/app/11.2.0/grid;export ORACLE_SID=+ASM'
alias jlsnrctl='ORACLE_OLD=$ORACLE_HOME; export ORACLE_HOME=/u01/app/11.2.0/grid;lsnrctl status;ORACLE_HOME=$ORACLE_OLD;ORACLE_OLD='
alias bdump='cd /u02/app/oracle/diag/rdbms/logdb/logdb1/trace'

7.拷贝10g的spfile和orapw文件到11g的目录
--确保该spfile中显式地设置了compitable参数为10.2.0，以便在升级失败情况下可以回滚。否则，升级失败无法回滚
10:44:14 sys@warehous> create pfile from spfile;

File created.

Elapsed: 00:00:00.03
10:44:30 sys@warehous> exit
$ cp /u01/app/oracle/product/10.2.0/db/dbs/initwarehouse.ora /u01/app/oracle/product/11.2.0/db/dbs/
$ cp /u01/app/oracle/product/10.2.0/db/dbs/orapwwarehouse /u01/app/oracle/product/11.2.0/db/dbs/
vi /u01/app/oracle/product/11.2.0/db/dbs/initwarehouse.ora
删除在11g中过期的参数
user_dump_dest
background_dump_dest
core_dump_dest
添加
diagnostic_dest='/u02/app/oracle'
LOG_ARCHIVE_DEST_STATE_3=DEFER --此归档目标为逻辑备库，防止启动的时候忘记关闭逻辑备库归档。

$ cp /u01/app/oracle/product/10.2.0/db/dbs/init+ASM.ora /u01/app/11.2.0/grid/dbs/
$ cp /u01/app/oracle/product/10.2.0/db/dbs/orapw+ASM /u01/app/11.2.0/grid/dbs/
cd /u01/app/11.2.0/grid/dbs/
vi init+ASM.ora
添加
diagnostic_dest='/u02/app/oracle'
asm_diskgroups='DATA'

8.拷贝10g的listener.ora和tnsnames.ora到11g的目录
$ cp /u01/app/oracle/product/10.2.0/db/network/admin/listener.ora /u01/app/11.2.0/grid/network/admin/
$ cp /u01/app/oracle/product/10.2.0/db/network/admin/tnsnames.ora /u01/app/11.2.0/grid/network/admin/
$ vi /u01/app/11.2.0/grid/network/admin/listener.ora
--修改ORACLE_HOME路径

############################################
##          升级窗口期前2周内             ##
############################################
9.部署新的统计信息收集脚本
--该表数据库统计信息收集方式，主要是组合分区的子分区需要收集，11g优化器计算成本的计算方式更细，但默认的统计信息收集方式是不收集子分区统计信息的。
BEGIN
   dbms_stats.gather_database_stats(
   method_opt=>'for all indexed columns',
   estimate_percent => 5,
   granularity=>'ALL',
   options=>'GATHER STALE',
   degree=>5,
   cascade=>TRUE
   );
END;
/

BEGIN
   dbms_stats.gather_database_stats(
   method_opt=>'for all indexed columns',
   estimate_percent => 5,
   granularity=>'ALL',
   options=>'GATHER EMPTY',
   degree=>5,
   cascade=>TRUE
   );
END;
/
10.清空回收站
purge dba_recyclebin;
############################################
##          升级窗口期前10小时内          ##
############################################
本部分工作分别在主库上和逻辑备库上分别执行
10.搜集统计信息，缩短升级时间
SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS;

############################################
## 升级窗口期前40分钟 ##
############################################
(主库和逻辑备库11-15，16在所有服务器上执行)
11.备份v$sql_plan和dba_hist_sql_plan保存10g环境下sql的执行计划
SQL> create table sys.sql_plan_history as select * from v$sql_plan;
SQL> create table sys.dba_hist_sql_plan_history as select * from dba_hist_sql_plan;

12.在10g下,保存状态为invalid的对象：
SQL> spool invalid_object_10g.txt
set pagesize500
set linesize 132
column object_name format a30
select object_name, owner, object_type from dba_objects where status='INVALID' order by owner,object_type;
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type;
SQL> spool off

13.在10g下,备份文件信息
SQL> spool files_10g.txt
col member for a50
col name for a50
col file_name for a50
select name from v$controlfile;
select file_name from dba_data_files;
select group#, member from v$logfile;
SQL> spool off

14.在10g下，执行11g的升级检查脚本
SQL> spool upgrade_info.log
SQL> @$ORACLE_HOME/../../11.2.0/db/rdbms/admin/utlu112i.sql
SQL> spool off
--仔细检查信息

15.检查有没有备份恢复进程、分布式事务
--应该没有记录才是对的
SQL> select * from v$recover_file;
SQL> select * from v$backup where status != 'NOT ACTIVE';
SQL> select * from dba_2pc_pending;

16.停用所有的crontab
$ crontab -l
--注释掉预计在时间窗口内的

############################################
## 升级窗口期 ##
############################################

17.开启screen
$ screen -S upgrade

18.检查主库的保护模式
11:10:57 sys@myora>select PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
如果有必要，修改保护模式
SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

19.停止所有连接数据库的应用,确认主备库没有连接
SQL>select se.USERNAME,se.MACHINE from v$session se where username is not null;

20.停止逻辑备库日志传输
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=DEFER SCOPE=BOTH;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

21.停止逻辑备库apply
SQL>ALTER DATABASE STOP LOGICAL STANDBY APPLY;
22.在10g下，关闭listener
$ lsnrctl stop

23.关闭主库和主库ASM
SQL> alter system checkpoint;
SQL> shutdown immediate
SQL> exit
$export ORACLE_SID=+ASM
SQL> shutdown immediate
SQL>exit

24.关闭备库和ASM(包含物理备库和逻辑备库)
SQL> shutdown immediate
SQL> exit
$export ORACLE_SID=+ASM
SQL> shutdown immediate
SQL>exit

25.升级主库css(root用户)
#/u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/roothas.pl
Using configuration parameter file: /u01/app/oracle/product/11.2.0.3/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
CSS appears healthy
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
Shutdown has begun. The daemons should exit soon.
CRS-4664: Node billdb successfully pinned.
Adding Clusterware entries to inittab

26.升级备库css(root用户，包含物理备库和逻辑备库)
#/u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/roothas.pl
Using configuration parameter file: /u01/app/oracle/product/11.2.0.3/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
CSS appears healthy
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
Shutdown has begun. The daemons should exit soon.
CRS-4664: Node billdb successfully pinned.
Adding Clusterware entries to inittab

27.分别启动主备库asm(注意，asm需要注册)
$srvctl add asm -p /u01/app/11.2.0/grid/dbs/spfile+ASM.ora -d 'ASMDISK*'
source bash_profile_asm
$ sqlplus "/as sysasm"

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 3 15:50:18 2012

Connected to an idle instance.

15:50:18 idle>startup
ASM instance started

Total System Global Area 321507328 bytes
Fixed Size                  2228024 bytes
Variable Size             294113480 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
15:50:29 idle>create spfile from pfile;
15:50:30 idle>shutdown immediate

$cd /u01/app/11.2.0/grid/dbs/
$ll
$crs_stat -t
$srvctl start asm
$ps -ef|grep asm_(检查asm进程)

28.启动主库和物理备库listener
$lsnrctl start

29.执行如下命令：
$ cp .bash_profile bash_profile_10204
$ cp bash_profile_11203 .bash_profile
$ source .bash_profile
$ env

30.在11g环境下启动备库到apply状态
$source bash_profile_11203
SQL>startup mount;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

31.升级主库和逻辑备库(31-34步操作在主库被逻辑备库上可同时进行)
运行升级包
--耗时30-40分钟，虚拟机更长，约2小时。数据字典升级完成后升级脚本会自动关闭数据库
$ sqlplus / as sysdba
$ startup upgrade
$ spool upgrade_11203.log
$ @?/rdbms/admin/catupgrd.sql
$ spool off

32.查看升级结果、执行一些未在升级包中的操作、编译无效对象
$ sqlplus / as sysdba
SQL> startup
SQL> @?/rdbms/admin/utlu112s.sql
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql

33.重新启动数据库
SQL> shutdown immediate
SQL> startup

34.查看11g下状态的无效对象
--与10g之下比较，如果有不一致，分析原因并解决
SQL> spool invalid_object_11g.txt
set pagesize500
set linesize 132
column object_name format a30
select object_name, owner, object_type from dba_objects where status='INVALID' order by owner,object_type;
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type;
SQL> spool off

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.6.52)(PORT=1530))))

35.启动逻辑备库listener
$ lsnrctl start

36.在升级后的逻辑备库上，开启apply
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

37.在主库上打开逻辑备库的日志传输
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

38.测试一下数据库能否正常连接
测试dataguard是否正常

############################################
## 升级PSU ##
############################################
PSU升级(2-3在每个服务器上分别执行)
1关闭数据库
关闭主库
$source /home/oracle/bash_profile_11203
$sql
sql>shutdown immediate;
source /home/oracle/bash_profile_asm
$sqlplus "/as sysasm"
sql>shutdown immediate
关闭逻辑库
$source /home/oracle/bash_profile_11203
$sql
sql>alter database stop logical standby apply;
sql>shutdown immedite
关闭物理库
$source /home/oracle/bash_profile_11203
$sql
sql>alter database recover managed standby database cancel;
sql>shutdown immediate;

2. 安装前的检查
原 OPatch 版本为 11.2.0.3(Patch 6880880)以上，./opatch version 显示什么版本
$ ./opatch version
Invoking OPatch 11.2.0.1.7
OPatch Version: 11.2.0.1.7
OPatch succeeded.
校验 Oracle Inventory，包括 gi home 和 db home
$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME
3. 安装的准备工作
备份 GRID_HOME 和 DB_HOME 的 ORACLE_HOME/OPatch
GI 的 ORACLE_HOME
# cd $ORACLE_HOME
# rm -fr $ORACLE_HOME/OPatch
DB 的$ORACLE_HOME
$ jdb
$ cd $ORACLE_HOME
$ tar zcvf OPatch_11.2.0.1.7.tar.gz OPatch
$ rm -fr $ORACLE_HOME/OPatch
解压缩 Opatch
GI 的 ORACLE_HOME

# unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME
# chown -R oracle:oinstall $ORACLE_HOME/OPatch
DB 的 ORACLE_HOME
$ source /home/oracle/bash_profile_11203
$ unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME
查看版本
$ ./opatch version
OPatch Version: 11.2.0.3.4
OPatch succeeded.
如果没有配置 OCM，按照下面的步骤执行：
$ source source /home/oracle/bash_profile_asm
$ cd $ORACLE_HOME/OPatch/ocm/bin/
$ ./emocmrsp
检查
$ source source /home/oracle/bash_profile_asm
$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME
$ source /home/oracle/bash_profile_11203
$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME
4. 安装
必须用 root 身份安装补丁(不需要停止 GI)，建议停止 DB
# cd $GRID_HOME/OPatch
#./opatch auto /u01/soft/p16083653_112030_Linux-x86-64-11020306-gi -oh /u01/app/11.2.0/grid -ocmrf /u01/app/11.2.0/grid/OPatch/ocm/bin/ocm.rsp

#./opatch auto /u01/soft/p16083653_112030_Linux-x86-64-11020306-gi -oh /u01/app/oracle/product/11.2.0/db -ocmrf /u01/app/11.2.0/grid/OPatch/ocm/bin/ocm.rsp

5.升级
升级逻辑库
#/u01/app/11.2.0/grid/bin/crs_start ora.cssd
$ source source /home/oracle/bash_profile_asm
$sqlplus "/as sysasm"
sql>startup
sql>exit
$ source /home/oracle/bash_profile_11203
$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus /as sysdba
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> select * from dba_registry_history;
sql>alter database start logical standby apply immediate;
sql>exit
lsnrctl start
另外开一个crt，使用tail 查看alter日志

升级主库及物理备库
启动物理备库
#/u01/app/11.2.0/grid/bin/crs_start ora.cssd
$ source source /home/oracle/bash_profile_asm
$sqlplus "/as sysasm"
sql>startup
sql>exit
$ source /home/oracle/bash_profile_11203
sql>start mount
sql>alter database recover managed standby database using current logfile disconnect from session;
sql>exit
lsnrctl start
另外开一个crt，使用tail 查看alter日志

启动主库
#/u01/app/11.2.0/grid/bin/crs_start ora.cssd
$ source source /home/oracle/bash_profile_asm
$sqlplus "/as sysasm"
sql>startup
sql>exit
$ source /home/oracle/bash_profile_11203
$lsnrctl start
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus /as sysdba
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> select * from dba_registry_history;

检查dataguard是否正常

开启停掉的crontab
$ crontab -e

禁用sql自动调优
--禁用
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/