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