如何安全地升级Oracle数据库

1.说明

见过很多DBA,升级数据库,基本就是安装新的Oracle软件,然后就是执行catupgrd.sql脚本,成不成功,看天。如果脚本执行失败了,然后就是疯了一般地找原因。这其实是个很不好的升级过程,那正确的、有保证的升级过程应该是怎样的呢?

我正好碰到了几套HP-UX 8640主机比较老的环境,因为没有SCN的相关补丁,需要从9i升级到10g。
操作系统版本是HP-UX B.11.11 U PA,老古董级的机器。这些数据库都是单实例,所以升级过程相对比较简单。

Oracle升级路线如下图,当前数据库版本是9.2.0.8,满足升级到10gR2的条件,升级的目标版本是10.2.0.5.12

oracle_upgrade

2.补丁检查

HPUX v11.11 平台从Oracle 9.2.0.8升级到10.2.0.5,需要检查如下补丁包

补丁包     补丁包说明
GOLDQPK11i  需要04年12月份或以后的补丁,如果GOLDQPK11i不存在,那必须GOLDAPPS11i、GOLDBASE11i补丁包存在
PHSS_31221  HP aC++ -AA runtime libraries (aCC A.03.60)
PHNE_31097  General Release/Performance Patch
PHSS_30970  1,s700_800 11.11 ld(1) and linker tools cumulative patch  2,s700_800 11.11 ld(1) and linker tools cumulative patch for JDK 1.4.2 3,Network: s700_800 11.11 LAN product cumulative patch 4,C++/C: linker patch 
PHSS_32508  C++: HP aC++ compiler (A.03.63) upgrade patch 
PHSS_32509  C: ANSI C compiler B.11.11.12 cumulative patch
PHSS_32510  C++/C: +O4/PBO Compiler B.11.11.12 cumulative patch

其中GOLDQPK11i、PHSS31221、PHSS30970是必要补丁包

检查补丁包的命令如下:

/usr/sbin/swlist -l bundle | grep "GOLD"
/usr/sbin/swlist -l patch -a supersedes | grep -in 31221

3.升级过程

我这边的升级过程,忽略了数据库备份、软件的备份等一系列过程,这些需要自行考虑,一些老的、备查的数据库,数据量一般还很大、非归档,想要备份还很困难,这就更考量DBA的技术能力了,因为如果升级出现故障,那风险就很大了,所以这种情况,更需要严格的升级过程来保证。

3.1 执行Database Pre-Upgrade Utility

下载utlu102i_2.sql,参考How to Download and Run Oracle’s Database Pre-Upgrade Utility [ID 884522.1]

[oracle@ora9 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Jun 3 00:11:45 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> @utlu102i_2.sql

3.2 相关日志

**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
--> The existing log files are adequate. No changes are required.
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 331 MB
.... AUTOEXTEND additional space required: 81 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 391 MB
.... AUTOEXTEND additional space required: 191 MB
WARNING: --> TEMP tablespace is not large enough for the upgrade.   -> 临时表空间不足
.... currently allocated size: 16 MB
.... minimum required size: 58 MB
.... increase current size by: 42 MB
.... tablespace is NOT AUTOEXTEND ENABLED.
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "streams_pool_size" is not currently defined and needs a value of
at least 50331648
WARNING: --> "session_max_open_files" needs to be increased to at least 20
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
--> "hash_join_enabled"                             -> 废弃参数
--> "log_archive_start"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade]  VALID
--> Oracle Packages and Types[upgrade]  VALID
--> Oracle Workspace Manager [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Deprecated CONNECT role granted to some user/roles.
.... CONNECT role after upgrade has only CREATE SESSION privilege.
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 10g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
....   SYS
....   WMSYS
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
--> New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
.

PL/SQL procedure successfully completed.

utlu102i_2.sql脚本检查了升级过程必要的条件,如:

  • 表空间大小是否满足升级要求
  • 需要增加的参数与废弃的参数
  • 组件的状态
  • 其它相关警告
  • SYSAUX表空间需求

如果有组件的INVALID状态,建议全部处理成VALID状态再进行升级,有些时侯业务维护时会把一些业务对象建到SYS用户里,这些对象如果是INVALID就没有太大关系了,但哪些是业务对象、哪些是系统对象,就需要DBA自已分辨清楚了。

满足了这些要求,就可以进行后续过程了

3.3 CONNECT角色中权限的回收

在升级到10gR2后,CONNECT角色只包含CREATE SESSION权限,其它权限被回收了。
9i中CONNECT角色中包含的权限:

SQL> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='CONNECT';

GRANTEEP                       RIVILEGE
------------------------------ ----------------------------------------
CONNECT                        CREATE VIEW
CONNECT                        CREATE TABLE
CONNECT                        ALTER SESSION
CONNECT                        CREATE CLUSTER
CONNECT                        CREATE SESSION
CONNECT                        CREATE SYNONYM
CONNECT                        CREATE SEQUENCE
CONNECT                        CREATE DATABASE LINK

8 rows selected.

检查有CONNECT角色的用户:

SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER', 
'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

如果需要恢复,请参考Predefined Roles Evolution From 8i to 10gR2: CONNECT Role Change in 10gR2 (Doc ID 317258.1)

3.4 备份DBLINK创建脚本

SELECT
'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;

create table dba_db_links_20150317 as select * from dba_db_links;
create table dba_users_20150317 as select * from dba_users;
create table link$_20150317 as select * from link$;

3.5 检查NLSNCHARCHARACTERSET

从9i开始,国际字符集(NLSNCHARCHARACTERSET)被限制成只能使用UTF8和AL16UTF16。其它不被支持了。

参考Note:276914.1 “The National Character Set in Oracle 9i, 10g and 11g”

SQL> select * from nls_database_parameters where parameter ='NLS_NCHAR_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ --------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16

检查是否有字段涉及转换

select distinct OWNER, TABLE_NAME
from DBA_TAB_COLUMNS
where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB')
and OWNER not in ('SYS','SYSTEM','XDB');

3.6 收集系统用户统计信息

建议保留老的系统统计信息

$ sqlplus '/as sysdba'
SQL>spool sdict

SQL>grant analyze any to sys;

SQL>exec dbms_stats.create_stat_table('SYS','dictstattab'); 

SQL>exec dbms_stats.export_schema_stats('WMSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('MDSYS','dictstattab',statown => 'SYS'); 
SQL>exec dbms_stats.export_schema_stats('CTXSYS','dictstattab',statown => 'SYS'); 
SQL>exec dbms_stats.export_schema_stats('XDB','dictstattab',statown => 'SYS'); 
SQL>exec dbms_stats.export_schema_stats('WKSYS','dictstattab',statown => 'SYS'); 
SQL>exec dbms_stats.export_schema_stats('LBACSYS','dictstattab',statown => 'SYS'); 
SQL>exec dbms_stats.export_schema_stats('OLAPSYS','dictstattab',statown => 'SYS'); 
SQL>exec dbms_stats.export_schema_stats('DMSYS','dictstattab',statown => 'SYS'); 
SQL>exec dbms_stats.export_schema_stats('ODM','dictstattab',statown => 'SYS'); 
SQL>exec dbms_stats.export_schema_stats('ORDSYS','dictstattab',statown => 'SYS'); 
SQL>exec dbms_stats.export_schema_stats('ORDPLUGINS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SI_INFORMTN_SCHEMA','dictstattab',statown => 'SYS'); 
SQL>exec dbms_stats.export_schema_stats('OUTLN','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('DBSNMP','dictstattab',statown => 'SYS'); 
SQL>exec dbms_stats.export_schema_stats('SYSTEM','dictstattab',statown => 'SYS'); 
SQL>exec dbms_stats.export_schema_stats('SYS','dictstattab',statown => 'SYS'); 

SQL>spool off

收集统计信息

SQL>grant analyze any to sys; 

SQL>exec dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('XDB',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('WKSYS',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('LBACSYS',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('OLAPSYS',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('DMSYS',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('ODM',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('ORDSYS',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('ORDPLUGINS',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('SI_INFORMTN_SCHEMA',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 
SQL>exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER', 
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

3.7 检查失效对象

spool invalid_pre.lst
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';
spool off

如果有失效对象,执行:

$ sqlplus '/as sysdba'
SQL> @?/rdbms/admin/utlrp.sql

如果是从Oracle 9iR2升级,检查dbaregistry是否有信息,如果没有,在9.2的ORACLEHOME中执行如下脚本:

$ sqlplus '/as sysdba' 
SQL> @?/rdbms/admin/catalog.sql 
SQL> @?/rdbms/admin/catproc.sql 
SQL> @?/rdbms/admin/utlrp.sql

3.8 检查数据字典完整性

使用如下脚本生成analyze.sql

Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql

Select 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
from dba_clusters
where owner='SYS'
union
Select 'Analyze table "'||table_name||'" validate structure cascade;' 
from dba_tables
where owner='SYS' and partitioned='NO' and (iot_type='IOT' or iot_type is NULL)
union
Select 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
from dba_tables
where owner='SYS' and partitioned='YES';

spool off

注意编译一下analyze.sql,执行:

$ sqlplus '/as sysdba'
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql

3.9 确保物化视图已经全部刷新

$ sqlplus '/ as sysdba' 
SQL> select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;
或
SQL> select distinct owner, name mview, master_owner master_owner, last_refresh from dba_mview_refresh_times;

3.10 停止监听

$ lsnrctl stop

确认没有要恢复的文件

SQL> select * from v$recover_file;

确认没有数据文件处理备份模式

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

确认没有分布式未决事务

SQL> select * from dba_2pc_pending;

如果有未决事务,处理如下:

alter session set "_smu_debug_mode" = 4;
set pagesize 999
select  'commit force '''||LOCAL_TRAN_ID||''';'||chr(10)||
'exec dbms_transaction.purge_lost_db_entry('''||LOCAL_TRAN_ID||''');'||chr(10)||
'commit;'  
  from dba_2pc_pending ;

3.11 禁止批处理的JOB

3.12 确认SYS、SYSTEM用户处于自已的表空间

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

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYSTEM                         SYSTEM
SYS                            SYSTEM

如果不是,修改如下:

SQL> alter user sys default tablespace SYSTEM; 
SQL> alter user system default tablespace SYSTEM;

3.13 如果启用审计,确认aud$在SYSTEM表空间

SQL> select tablespace_name from dba_tables where table_name='AUD$';

3.24 如果存在表XDB.MIGR9202STATUS,需要drop掉

参见Note:356082.1

3.15 关闭数据库

SQL> shutdown immediate

3.16 备份数据库

3.17 安装10gR2介质

注意检查10gR2在当前操作系统环境下要求的软件包

export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=tea
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

注意保留老的SHELL环境变量

3.18 升级init.ora文件

删除废弃参数
hashjoinenabled
logarchivestart
增加参数
*.streamspoolsize=80M
*.sessionmaxopen_files=2000

3.19 复制SQL*Net文件到新环境

cp $OLDORACLEHOME/network/admin/*.ora $NEWORACLEHOME/network/admin

3.20 启动upgrade

$ sqlplus "/ as sysdba"
SQL> create spfile from pfile='/oracle/init10g.ora';
SQL> startup upgrade

3.21 创建SYSAUX表空间

CREATE TABLESPACE sysaux DATAFILE '/oracle/app/oracle/oradata/tea/sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

3.22 执行升级

SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off
SQL> @?/rdbms/admin/utlu102s.sql TEXT

3.23 检查升级后状态

SQL> set linesize 120
SQL> column comp_name format a40
SQL> select comp_name, status, version from dba_registry;

COMP_NAME                                STATUS                 VERSION
---------------------------------------- ---------------------- -------------------
Oracle Workspace Manager                 VALID                  10.2.0.5.0
Oracle Database Catalog Views            VALID                  10.2.0.5.0
Oracle Database Packages and Types       VALID                  10.2.0.5.0

3.24 重启数据库

SQL> shutdown immediate (DO NOT use "shutdown abort" !!!) 
SQL> startup restrict

3.25 重建Oracle Label Security的DML Triggers

只有存在Oracle Label Security组件是才需要执行
SQL> @olstrig.sql

3.26 重新编译

SQL> @utlrp.sql

检查失效对象

spool invalid_post.lst 
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'; 
spool off

4. 总结

经过以上严格的过程保证,Oracle数据库的升级,基本没有太大的问题,当然这个升级过程是9i升级到10g的,其它版本类似,但不完全一样。

关于紫砂壶

感悟技术人生
此条目发表在Oracle基础知识分类目录,贴了标签。将固定链接加入收藏夹。