oracle ebs 完工倒扣,配合EBS数据库手工升级

(1)安装9.2.0.8数据库软件

注：升级之前与之后检查sys.MON_MODS_ALL$和sys.MON_MODS$里是否有重复行。这关系到11gR2升级里 一个BUG

(1.1)安装9.2.0.1 RDBMS

安装9.2.0.1 RDBMS代码并配置TNS，步骤如下：

(1.1.1)分别解压9201的4张CD到/u03/oracle/orasup/9iR2/9201下的4个目录中。

(1.1.2)检查表空间SYSTEM和RBS的剩余空间，SYSTEM至少要500MB，RBS至少要750MB。

alter tablespace rbs add datafile '/u05/oracle/PROD/proddata/rbs08.dbf' size 1000m;

alter table argument$ storage(maxextents unlimited);

alter tablespace SYSTEM default storage(maxextents unlimited);

(1.1.3)关闭所有应用和数据库、监听。

(1.1.4)运行/usr/sbin/slibclean

(1.1.5)运行rootpre.sh脚本。

(1.1.6)运行runInstaller.sh安装英文版本。

(1.1.7)重新运行runInstaller.sh安装ZHS语言包。

(1.2)安装9.2.0.8补丁

(1.2.1)运行/usr/sbin/slibclean(否则会报告libjox9.a无法被替换的错误)

(1.2.2)安装9208补丁集 4547809

注：补丁包仅有英文版本，无需选择语言。

(1.2.3)安装opatch补丁6880880(即最新的opatch)

(1.2.4)安装附加的9208补丁：5391326 、2858130

注：和EBS相关，使用opatch apply安装

(1.2.5)安装9208 CPU补丁：8534403。补丁后续步骤暂不执行。

注：直接使用opatch apply安装, Post步骤不执行。

(2)升级8.1.7.3 数据库至9.2.0.8(64bit)

注 :如果数据库有归档，升级前将归档和log_archive_dest等归档参数注释,并取消数据库归档

(2.1)升级9.2.0.8前的准备

执行以下步骤，准备将数据库8.1.7.3升级到9.2.0.8：

(2.1.1)创建$ORACLE_HOME/admin目录

$mkdir –p /u12/oracle/prod9iRDBMS/admin

$cd /u12/oracle/prod9iRDBMS/admin

$cp -rp /u01/oracle/proddb/8.1.7/admin .

(2.1.2)准备9i ORACLE HOME里的initPROD.ora文件

a. 替换ORACLE_HOME对应的路径

b. COMPATIBLE = 8.1.7

c. _system_trig_enabled = FALSE， CLUSTER_DATABASE =FALSE，

d. 注释掉以下几个参数：

event="10932 trace name context level 32768"

event="10933 trace name context level 512"

event="10943 trace name context level 16384"

optimizer_features_enable = 9.2.0 #MP

job_queue_interval # Obsoleted

optimizer_percent_parallel# Obsoleted

always_anti_join# Obsoleted

always_semi_join# Obsoleted

aq_tm_processes

job_queue_processes

e. 不要使用undo_参数

f. 检查control_file, rollback, utl_file_dir,db_files, log_archive_start参数。

g. SGA参数最小值:

Shared_pool_size: > 50MB => 150MB(最少) => 6G(推荐)

PGA_AGGREGATE_TARGET > 25M => 60MB(最少) => 2G(推荐)

LARGE_POOL_SIZE > 10M => 50MB (最少) => 100M(推荐)

Log_buffer => 10M(最少) =>50M(推荐)

Lock_sga = true

Db_cache_size=> 6GB => 24GB(推荐)

Parallel_max_servers=>8(最少) =>32(推荐)

h. 关闭执行程序的RAC选项：

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk rac_off

make -f ins_rdbms.mk ioracle

(2.1.3)备份oraInventory

(2.1.4)检查数据库用户没有crontab命令或确认crontab无影响

(2.1.5)检查ulimit设置

$ulimit -a

time(seconds) unlimited

file(blocks) unlimited

data(kbytes) unlimited

stack(kbytes) 4194304

memory(kbytes) unlimited

coredump(blocks) unlimited

nofiles(descriptors) unlimited

ulimit -c unlimited

ulimit -n unlimited

ulimit -m unlimited

(2.2)升级数据库到9.2.0.8

执行以下步骤，将数据库从8.1.7.3升级到9.2.0.8：

(2.2.1)确认以下环境变量设置正确:

export ORACLE_HOME=xxxxxx

export SHLIB_PATH=$ORACLE_HOME/lib:/usr/dt/lib:/usr/openwin/lib

export PATH=$ORACLE_HOME/Apache/perl/bin:$ORACLE_HOME/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/home/oraprod/bin:/usr/bin/X11:/sbin:.:/home/oraprod:/usr/java131/bin:.

export ADPERLPRG=$ORACLE_HOME/Apache/perl/bin/perl

export ORA_TZFILE=$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

export TNS_ADMIN=$ORACLE_HOME/network/admin/PROD

export CONTEXT_FILE=$ORACLE_HOME/appsutil/PROD.xml

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/dt/lib:/usr/openwin/lib:$ORACLE_HOME/ctx/lib

export PERL5LIB=$ORACLE_HOME/Apache/perl/lib/5.00503:$ORACLE_HOME/Apache/perl/lib/site_perl/5.005:$ORACLE_HOME/appsutil/perl

export ADJVAPRG=$ORACLE_HOME/jdk/bin/java

export LIBPATH=$ORACLE_HOME/lib:/usr/dt/lib:/usr/openwin/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/ctx/lib

(2.2.2)在9i ORACLE HOME下，以migrate模式启动数据库

SQL> STARTUP MIGRATE

(2.2.3)运行9208升级脚本

$ cd $ORACLE_HOME/rdbms/admin;sqlplus ‘/as sysdba’

SQL> SPOOL U0801070.LOG

SQL> @u0801070.sql (约运行1小时)

运行完之后，检查dba_register的内容如下：

SQL> select comp_name,status,version from dba_registry;

COMP_NAME STATUS VERSION

----------------------------------- ----------- --------------------

Oracle9i Catalog Views VALID 9.2.0.8.0

Oracle9i Packages and Types VALID 9.2.0.8.0

JServer JAVA Virtual Machine LOADED 8.1.7

Java Packages LOADED 8.1.7

Oracle XDK for Java LOADED 8.1.7

Oracle interMedia Text LOADED 8.1.7

Real Application Clusters LOADED

Oracle interMedia LOADED 8.1.6.0.0

Oracle Spatial LOADED 8.1.6.0.0

运行以下脚本继续9208升级(components upgraded)：

SQL> @cmpdbmig.sql(运行20分钟)

运行完之后，检查dba_register的内容如下：

SQL> select comp_name,status,version from dba_registry;

COMP_NAME STATUS VERSION

----------------------------------- ----------- ------------------------------

Oracle9i Catalog Views VALID 9.2.0.8.0

Oracle9i Packages and Types VALID 9.2.0.8.0

JServer JAVA Virtual Machine VALID 9.2.0.8.0

Oracle9i Java Packages VALID 9.2.0.8.0

Oracle XDK for Java UPGRADED 9.2.0.2.0

Oracle interMedia Text LOADED 8.1.7

Oracle9i Real Application Clusters INVALID 9.2.0.8.0

Oracle interMedia LOADED 8.1.6.0.0

Oracle Spatial LOADED 8.1.6.0.0 DEVELOPMENT

(2.3.4)升级TEXT组件

$cd $ORACLE_HOME/ctx/admin

$sqlplus "/as sysdba"

SQL> @s0900010.sql (运行1分钟)

SQL> conn ctxsys/ctxsys

SQL> @u0900010.sql (运行1分钟)

在运行以上升级脚本的时候报告错误ORA-01562: failed to extend rollback segment number。

发生错误的语句是修改dr$waiting表，为它添加一个字段wtg_pid：alter table dr$waiting add (wtg_pid number default 0)。手工执行如下脚本进行修复：

SQL> alter table dr$waiting add (wtg_pid number );

SQL> alter table CTXSYS.DR$WAITING modify WTG_PID default 0;

SQL> alter table dr$waiting nologging;

SQL> update /*+ parallel(4) */dr$waiting set wtg_pid = 0;

运行完之后，检查dba_register的内容如下：

SQL> select comp_name,status,version from dba_registry;

COMP_NAME STATUS VERSION

---------------------------------------- ----------- ------------------------------

Oracle9i Catalog Views VALID 9.2.0.8.0

Oracle9i Packages and Types VALID 9.2.0.8.0

JServer JAVA Virtual Machine VALID 9.2.0.8.0

Oracle9i Java Packages VALID 9.2.0.8.0

Oracle XDK for Java UPGRADED 9.2.0.2.0

Oracle interMedia Text LOADED 8.1.7

Oracle9i Real Application Clusters INVALID 9.2.0.8.0

Oracle interMedia LOADED 8.1.6.0.0

Oracle Spatial LOADED 8.1.6.0.0 DEVELOPMENT

SQL> connect / as sysdba

SQL> @?/ctx/admin/s0902000.sql(运行1分钟)

SQL> connect ctxsys/ctxsys

SQL> @?/ctx/admin/u0902000.sql (运行10分钟)

运行完之后，检查dba_register的内容如下：

SQL> select comp_name,status,version from dba_registry;

COMP_NAME STATUS VERSION

-------------------------------------------------- ----------- ------------------------------

Oracle9i Catalog Views VALID 9.2.0.8.0

Oracle9i Packages and Types VALID 9.2.0.8.0

JServer JAVA Virtual Machine VALID 9.2.0.8.0

Oracle9i Java Packages VALID 9.2.0.8.0

Oracle XDK for Java UPGRADED 9.2.0.2.0

Oracle Text VALID 9.2.0.8.0

Oracle9i Real Application Clusters INVALID 9.2.0.8.0

Oracle interMedia LOADED 8.1.6.0.0

Oracle Spatial LOADED 8.1.6.0.0 DEVELOPMENT

SQL> spool off

(2.3.5)升级数据库Spatial组件

$cd $ORACLE_HOME/javavm/install

$sqlplus '/as sysdba'

SQL >spool jvm_upg_20101126.log

SQL >@jvmsec3.sql(运行1分钟)

SQL >@jvmsec5.sql (运行时有错误，错误参考spatial.log，忽略.运行1分钟)

SQL> SHUTDOWN IMMEDIATE

修改参数compatible=9.2.0

SQL > STARTUP

SQL>SPOOL ?/md/admin/mdprivs.log

SQL>@?/md/admin/mdprivs.sql(运行1分钟)

SQL>connect mdsys/mdsys

SQL>@?/md/admin/c81Xu9X.sql(运行1分钟) (运行时有错误，错误参考日志)

运行完之后，检查dba_register的内容如下：

SQL> select comp_name,status,version from dba_registry;

COMP_NAME STATUS VERSION

-------------------------------------------------- ----------- ------------------------------

Oracle9i Catalog Views VALID 9.2.0.8.0

Oracle9i Packages and Types VALID 9.2.0.8.0

JServer JAVA Virtual Machine VALID 9.2.0.8.0

Oracle9i Java Packages VALID 9.2.0.8.0

Oracle XDK for Java UPGRADED 9.2.0.2.0

Oracle Text VALID 9.2.0.8.0

Oracle9i Real Application Clusters INVALID 9.2.0.8.0

Oracle interMedia LOADED 8.1.6.0.0

Spatial VALID 9.2.0.8.0

(2.3.6)升级数据库Intermedia组件

SQL> spool ?/ord/im/admin/imdbma.log

SQL> CONNECT /AS SYSDBA

SQL> @?/ord/im/admin/imdbma.sql(运行1分钟)

SQL>@?/ord/im/admin/u0801070.sql(运行5分钟)

运行完之后，检查dba_register的内容如下：

SQL> select comp_name,status,version from dba_registry;

COMP_NAME STATUS VERSION

-------------------------------------------------- ----------- ------------------------------

Oracle9i Catalog Views VALID 9.2.0.8.0

Oracle9i Packages and Types VALID 9.2.0.8.0

JServer JAVA Virtual Machine VALID 9.2.0.8.0

Oracle9i Java Packages VALID 9.2.0.8.0

Oracle XDK for Java UPGRADED 9.2.0.2.0

Oracle Text VALID 9.2.0.8.0

Oracle9i Real Application Clusters INVALID 9.2.0.8.0

Oracle interMedia VALID 9.2.0.8.0

Spatial VALID 9.2.0.8.0

(2.3.7)运行9208补丁的后续步骤

SQL> startup migrate

SQL> spool ?/rdbms/admin/catpatch.log

SQL> @?/rdbms/admin/catpatch.sql (运行30分钟)

多次报告ORA-01031: insufficient privileges错误，可以直接忽略(NOTE:287356.1)

运行完之后，检查dba_register的内容如下：

SQL> select comp_name,status,version from dba_registry;

COMP_NAME STATUS VERSION

----------------------------------- ----------- --------------------

Oracle9i Catalog Views VALID 9.2.0.8.0

Oracle9i Packages and Types VALID 9.2.0.8.0

JServer JAVA Virtual Machine VALID 9.2.0.8.0

Oracle9i Java Packages VALID 9.2.0.8.0

Oracle XDK for Java VALID 9.2.0.10.0

Oracle Text VALID 9.2.0.8.0

Oracle9i Real Application Clusters INVALID 9.2.0.8.0

Oracle interMedia VALID 9.2.0.8.0

(2.3.8)数据库升级之后会有如下的SYS用户无效对象，忽略(Note 361757.1)

x_$bh;

x_$kcbwbpd;

x_$kcbwds;

(2.4)执行CPU补丁的后续步骤

执行以下数据库从8.1.7.3升级到9.2.0.8的后续步骤：

(2.4.1)运行9208 CPU补丁的后续步骤。

cd $ORACLE_HOME/cpu/CPUJul2009

sqlplus '/as sysdba'

SQL> startup

SQL> spool catcpu.log

sql> @catcpu.sql (运行3分钟)

sql> commit;

运行完之后有61837个无效对象

(2.4.2)运行nchar转换脚本

SQL> shutdown immediate

sql> startup restrict;

sql> @?/rdbms/admin/utlnchar.sql(运行1分钟)

sql> @?/rdbms/admin/n_switch.sql(运行3分钟)

(2.5)转换数据库到64位(可选，如有需要的话)

(2.5.1)转换数据库到64位

cd $ORACLE_HOME/rdbms/admin

sqlplus "/ as sysdba"

SQL> shutdown immediate

SQL> STARTUP MIGRATE

SQL> SPOOL wordsize.log

SQL> select * from v$version;

SQL> @utlirp.sql (运行210分钟)

由于以上脚本会将所有的对象失效并重新以单进程进行编译,这样效率很慢,在以上脚本执行的同时,建议执行以下脚本来并行编译:

@?/rdbms/admin/utlrcmp.sql

begin

sys.utl_recomp.recomp_parallel(threads => 80,schema => 'APPS');

end;

注：该步骤时间长

注意执行的是utlirp.sql, 不是utlrp.sql

检查wordsize.log是否有错误

检查数据库是否为64bit:

SQL> select * from v$version

(3)升级9.2.0.8后续步骤

(3.1)在9i ORACLE HOME里配置listener.ora

(3.2)进行SMU转换(运行10分钟)

(3.2.1)检查当前的rollback segment

SQL> select segment_name, tablespace_name from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME

------------------------------ ------------------------------

SYSTEM SYSTEM

RBS01 RBS

RBS02 RBS

RBS03 RBS

...

RBS30 RBS

(3.2.2)检查当前的rollback表空间

SQL > select distinct tablespace_name from dba_rollback_segs;

TABLESPACE_NAME

------------------------------

RBS

SYSTEM

SQL> select file_name, tablespace_name, bytes from dba_data_files where tablespace_name = 'RBS';

FILE_NAME TABLESPACE_NAME BYTES

------------------------------ ------------------------------ ----------

/u05/oracle/proddata/rbs01.dbf RBS 1572864000

/u05/oracle/proddata/rbs02.dbf RBS 1572864000

...

/u05/oracle/proddata/rbs18.dbf RBS 1572864000

(3.2.3)将当前的rollback segment offline(system rollback segment除外)。

Set pagesize 0

Set linesize 130

select 'alter rollback segment '||segment_name||' offline;'

from dba_rollback_segs a

where a.tablespace_name = 'RBS';

利用以上脚本生成的SQL进行 rollback segment offline操作。生成的SQL如下：

alter rollback segment RBS01 offline;

alter rollback segment RBS02 offline;

...

alter rollback segment RBS30 offline;

(3.2.4)删除当前的rollback segment(system rollback segment除外)

利用以下SQL生成drop rollback segment的SQL：

select 'drop rollback segment '||segment_name||';'

from dba_rollback_segs a

where a.tablespace_name = 'RBS';

生成出来的SQL如下：

drop rollback segment RBS01;

drop rollback segment RBS02;

...

drop rollback segment RBS30;

验证已经全部drop了;

select 'drop rollback segment '||segment_name||';'

from dba_rollback_segs a

where a.tablespace_name = 'RBS';

(3.2.5)删除当前的rollback表空间

SQL>Alter tablespace RBS offline;

SQL>select owner,segment_name,segment_type,bytes from dba_segments where tablespace_name = 'RBS'

OWNER SEGMENT_NAME

------------------------------ ---------------------------------------------------------------------------------

SEGMENT_TYPE BYTES

------------------ ----------

SYS 6.36532

SPACE HEADER 81920

SQL>drop tablespace rbs;

(3.2.6)创建SMU的表空间APPS_UNDOTS(运行5分钟)

create undo tablespace APPS_UNDOTS1 datafile '/u05/oracle/PROD/proddata/rbs01.dbf' size 1500M reuse extent management local;

alter tablespace APPS_UNDOTS1 add datafile '/u05/oracle/PROD/proddata/rbs02.dbf' size 1500M reuse;

alter tablespace APPS_UNDOTS1 add datafile '/u05/oracle/PROD/proddata/rbs03.dbf' size 1500M reuse;

alter tablespace APPS_UNDOTS1 add datafile '/u05/oracle/PROD/proddata/rbs04.dbf' size 1500M reuse;

alter tablespace APPS_UNDOTS1 add datafile '/u05/oracle/PROD/proddata/rbs05.dbf' size 1500M reuse;

alter tablespace APPS_UNDOTS1 add datafile '/u05/oracle/PROD/proddata/rbs06.dbf' size 1500M reuse;

alter tablespace APPS_UNDOTS1 add datafile '/u05/oracle/PROD/proddata/rbs07.dbf' size 2000M reuse;

alter tablespace APPS_UNDOTS1 add datafile '/u05/oracle/PROD/proddata/rbs08.dbf' size 2000M reuse;

alter tablespace APPS_UNDOTS1 add datafile '/u05/oracle/PROD/proddata/rbs09.dbf' size 2000M reuse;

(3.2.7)修改数据库初始化参数文件：

添加：

undo_management = AUTO

undo_retention = 1800

undo_suppress_errors = FALSE

undo_tablespace = APPS_UNDOTS1

event="10932 trace name context level 32768"

event="10943 trace name context level 16384"

event="10933 trace name context level 512"

修改：

log_buffer = xxxx(可选)

删除：

rollback_segments = ...

(3.2.8)重新启动数据库(运行10分钟).

需要断开所有SQLPLUS连接

(3.3)执行升级到9.2.0.8的后续步骤

(本步骤的补丁放在/u03/oracle/orasup/9iR2/9208Patches下)

(3.3.1)搜集数据字典统计量(运行10分钟)

SQL> execute dbms_stats.gather_schema_stats('SYS',cascade=>TRUE,degree=>20);

(3.3.2)启动9i RDBMS Listener。

(3.3.3)检查owapatch.sql已经成功运行，否则需要参考390993.1重新运行。在8.1.7.3下，owa版本是3.0.0.0.4。

SQL> select owa_util.get_version from dual;

GET_VERSION

--------------------------------------------------------------------------------

3.0.9.8.7