从11.2.0.2 升级到11.2.0.3

11.2.0.3的升级同10g的不一样，11.2.0.3的包可以不用在11.2.0.2的基础上而直接安装

Export DISPLAY=10.3.11.206:0.0

1.      安装11.2.0.3软件 ，服务器上已安装了11.2.0.2，现在直接重新安装11.2.0.3软件,安装路劲与11.2.0.2不一样

ORACLE_BASE=/ora103/11.2.0.3

ORACLE_HOME=/ora103/11.2.0.3/db

解压：

Unzip p10404530_112030_AIX64-5L_1of7.zip

./runInstaller  -ignoresysprereqs

2.      Expdp full

Export $NLS_LANG=american_america.UTF8

Expdp system/scqmes full=y directory=dumpdir dumpfile=scqpack.dmp logfile=export_full.log  (20min)

3.      11.2.0.3软件安装结束之后，在11.2.0.3的环境下运行dbca  (30min)

选择字符集为UTF8

Export ORACLE_BASE=/ora103/11.2.0.3

Export ORACLE_HOME=/ora103/11.2.0.3/db

Export PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:/ora103/11.2.0.3/db/bin

真正升级的时候可以直接修改/ora101/.profile文件即可

4.      将源datafile备份

mv /ora101/oradata/SCQPACK  /ora101/oradata/SCQPACK_BAK

mkdir /ora101/oradata/SCQPACK

5.      实例安装并创建tablespace  (10min)

@/ora101/scripts/addFile.sql

6.      Create directory dumpfile as ‘/ora101/oradata/dumpdir’;

Grant read,write on directory dumpfile to system;

7.      Impdp full

Export $NLS_LANG=american_america.UTF8

Impdp system/scqmes full=y directory=dumpdir  dumpfile=scqpack.dmp

Logfile=impdp_full.log parallel=2

切忌：利用nohup

8.      Move scqhp table from system tablespace to hp_tb and rebuild index

select 'alter table scqhp.'|| segment_name|| ' move  tablespace HP_TB;'  from dba_segments

where tablespace_name='SYSTEM' AND owner='SCQHP' and segment_type='TABLE' ;

rebuild index:

SELECT 'alter index scqhp.'||index_name||' rebuild online compute statistics;' from dba_indexes where table_name in(select  segment_name from dba_segments

where tablespace_name='SYSTEM' AND owner='SCQHP' and segment_type='TABLE') ;

9.      Shutdown db

10.  Copy pfile to new directory ,copy tnsnames.ora listener.ora

11.  Startup ;

12.  Configure physical standby

注意：如果不设定Export $NLS_LANG，再import的时候会报错，

因为AL32UTF8是父集 而UTF8是子集的

 SQL> create tablespace SMPERP_MV datafile '/ora103/oradata/SCQTEST/smperp_mv.dbf' size 1000M autoextend on next 50M maxsize unlimited

SQL> create tablespace SMPERP_MV_INX datafile '/ora103/oradata/SCQTEST/smperp_mv_idx.dbf' size 200M autoextend on next 50M maxsize unlimited

SQL> create directory dumpdir as '/ora103/oradata/dumpdir';

SQL> grant read,write on directory dumpdir to system;

 总结：这次升级还算顺利，但中途还是出了点小故障，开始expdp之前没有设定NLS_LANG 导致重新expdp(幸好数据量小，否则得崩溃了）

下午就看起来有点不顺了，在配置physical standby的时候连连出错，

SQL> alter database open read only;

alter database open read only

*

ERROR at line 1:

ORA-10458: standby database requires recovery

ORA-01195: online backup of file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/ora103/oradata/PACKSTB/system01.dbf'

SQL> recover datafile 1;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;

ORA-00283: recovery session canceled due to errors

ORA-01666: control file is for a standby database

第一感觉是日志没有同步到standby，于是查看口令文件是否正确

查看log

Fetching gap sequence in thread 1, gap sequence 10-10

Error 12154 received logging on to the standby

FAL[client, USER]: Error 12154 connecting to SCQPACK for fetching gap sequence

Fri Dec 30 14:51:20 2011

Error 12154 received logging on to the standby

FAL[client, USER]: Error 12154 connecting to SCQPACK for fetching gap sequence

Fri Dec 30 14:51:30 2011

Error 12154 received logging on to the standby

FAL[client, USER]: Error 12154 connecting to SCQPACK for fetching gap sequence

Fri Dec 30 14:51:40 2011

Error 12154 received logging on to the standby

FAL[client, USER]: Error 12154 connecting to SCQPACK for fetching gap sequence

问题点找出，就是口令文件不对，密码不对，重新创建口令文件

创建之后又出现问题：

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 30 15:36:43 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:

ORA-01033: ORACLE initialization or shutdown in progress

Process ID: 0

Session ID: 0 Serial number: 0

Enter user-name:

ERROR:

ORA-01017: invalid username/password; logon denied

以及从primary 登陆：

scqmespack:ora101:/ora101/11.2.0.3/db/dbs >sqlplus sys/scqmes@PACKSTB as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 30 15:34:58 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:

ORA-01031: insufficient privileges

Enter user-name:

ERROR:

ORA-01017: invalid username/password; logon denied

问题还是口令文件不对，仔细查看是口令文件名字不对修改之后OK！

遇到问题的时候还是得沉下心来，淡定！！

2012年1月1日还得加班，迁移数据库！！伤不起呀！！