upgrade a database from 9.2.0.8 to 10.2.0.1 by dbua

dbua升级数据库比imp/exp快多了，但是步骤也复杂多了，前面有一大堆的检查，看是否DB满足要求。使用dbua可以用图形界面也可以手工执行script，觉得手工还是保险一些，今天尝试了一下把一个数据库从9.2.0.8升级到10.2.0.1，记录如下：

环境介绍如下：

OS：CenterOS4U8 32Bit, Original DB 9.2.0.8, Target DB 10.2.0.1

参考Metalink的 Note ID 883066.1

 

1.配置测试环境，包括安装/配置9208的数据库和10.2.0.1的Oracle_Home。

 

      安装9208过程很简单，先安装数据库所需要的补丁和工具，然后设置OS的基本参数，建立DBA组和用户，开始安装Oracle9204，安装完毕后升级到9208建库。建完库后把以前备份的数据库imp进去，即为源库，弄好后再相同的用户下安装Oracle10.2.0.1的Oracle_home，不用建库。

 

     安装/建库9208过程虽然简单，也碰到了不少问题，解决方法记录如下：

Q1:Error occurred during initialization of VM
Unable to load native library: /tmp/OraInstall2011-05-30_09-03-26AM/jre/lib/i386/libjava.so: symbol __libc_wait,

version GLIBC_2.0 not defined in file libc.so.6 with link time reference

 

安装到63%时 link ins_plsql.mk error

A1:
Install compat-libcwait-2.1-1.i386.rpm
compat-oracle-rhel4-1.0-5.i386.rpm

 

Q2:dbca: line 124: <num> Segmentation fault $JRE_DIR/bin/jre -DORACLE_HOME=$OH -DJDBC_PROTOCOL=thin -mx64m -classpath $CLASSPATH

oracle.sysman.assistants.dbca.Dbca $ARGUMENTS

A2: added -native to the dbca script which seems to allow it to run without crashing

 

Q3:dbca 53% ora-29807 specified operator does not exist

A3:
Workaround;
 just ignore the error and DBCA proceeds normally. Once the database is ready,
 login as SYS and run this script manually:
 
$ORACLE_HOME/rdbms/admin/prvtxml.plb
 SQL> @?/rdbms/admin/prvtxml.plb

 

2. 源库升级前的动作，该动作都是在9208数据库上进行：

   A. 停掉与源库相关的crontab/backup/job等等

   B. 备份db link的scripts，以便于升级完毕后的检查/重建

   C.做一系列检查，对不满足条件的进行处理，其中包括加大部分表空间，增大部分SGA的内存结构，编译无效对象等等，最主要参考的是utlu102i.sql提供的信息，部分检查包括下列SQL语句:

 

  create package hout for hcheck in sys schema

  create package hcheck for hcheck in sys schema

 

  sqlplus "/ as sysdba"
  set serveroutput on
  spool hcheck.out
  execute hcheck.full
  spool off

  spool /tmp/utlu102i.out
  @10G_ORACLE_HOME/rdbms/admin/utlu102i.sql
  spool off

 

spool /tmp/regInvalid.out
set echo on
-- query registry
set lines 80 pages 100
select substr(comp_id,1,15) comp_id,
substr(comp_name,1,30) comp_name,
substr(version,1,10) version,
status
from dba_registry
order by modified;

select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status
from dba_objects
where status <> 'VALID'
order by owner, type;
spool off;
set echo off;

 

select * from v$recover_file;
select * from v$backup where status!='NOT ACTIVE';

select * from dba_2pc_pending

select username, default_tablespace from dba_users
where username in ('SYS','SYSTEM');

    

    ......等等

 

3. 升级动作，以下动作在10.2.0.1Oracle_Home下操作：

 

  A.检查环境下列变量，必须指向新的Oracle_home

  - ORACLE_HOME
- PATH
- ORA_NLS10
- ORACLE_BASE
- LD_LIBRARY_PATH
- LD_LIBRARY_PATH_64 (Solaris only)
- LIBPATH (AIX only)
- SHLIB_PATH (HPUX only)
- ORACLE_PATH

 

 B. copy orignal pfile initcompard.ora to new place $ORACLE_HOME/dbs/
      update initcompard.ora file accroding to the file utlu102i.out

 

 C. 手动执行DBUA脚本升级

sqlplus "/ as sysdba"
create spfile from pfile
startup upgrade
alter session set resumable_time=3600;
CREATE TABLESPACE sysaux DATAFILE '/mnt/9ir2/dbhome/oradata/compard/sysaux01.dbf' SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

 ALTER DATABASE DATAFILE '/mnt/9ir2/dbhome/oradata/compard/sysaux01.dbf' AUTOEXTEND ON MAXSIZE 1000M;
spool /tmp/catupgrd.out
@?/rdbms/admin/catupgrd.sql    --20minutes
spool off
shutdown immediate

 D. create new password file,command as below:

      orapwd file=orapwcompard password=manager entries=10

 

E. Compile invalid objects and check the dba_registry

 

sqlplus "/ as sysdba"
startup restrict
@?/rdbms/admin/utlu102s.sql TEXT
@?/rdbms/admin/utlrp.sql

drop view or synonym refresh this synonym in sys schema
SYS V_$KQRPD VIEW
SYS V_$KQRSD VIEW
SYS GV_$KQRPD VIEW
SYS GV_$KQRSD VIEW
PUBLIC GV$KQRSD SYNONYM
PUBLIC GV$KQRPD SYNONYM
PUBLIC V$KQRSD SYNONYM
PUBLIC V$KQRPD SYNONYM

shutdown immediate

startup

 F.补全9i中具有connect用户的权限：

     GRANT create session, create table, create view, create synonym,
      create database link, create cluster, create sequence, alter session
    TO CONNECT;

 F. cp /mnt/9ir2/dbhome/network/admin/*.ora $ORACLE_HOME/network/admin

     打开监听器,从客户端登陆，确认结果。

 

4.升级完成后

 A. 恢复crontab并修改相关脚本，包括开关DB/备份等等，使之指向新库

 B.更新oratab

 

这次升级比较简单，比如没有任何应用，也不涉及到NLS_LANG方面的操作，数据文件升级完成后还在原来的目录，如果碰到复杂的情况就要具体问题具体对待了。

附录，DB可以直接升级的版本关系列表：

8.1.7.4 -> 10.2.X.X.X
9.0.1.4 or 9.0.1.5 -> 10.2.X.X.X
9.2.0.4 or higher -> 10.2.X.X.X
10.1.0.2 or higher -> 10.2.X.X.X

The following database version will require an indirect upgrade path.

7.3.3 (or lower) -> 7.3.4 -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
7.3.4 -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
8.0.n -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
8.1.n -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X