VMCD.ORG » 手动升级9i-10g全步骤

某局威武，升级弄完都到凌晨6点了居然还要搭DG ,下面记录这次的升级过程（9.2.0.7-10.2.0.4）

迁移步骤：

1 首先安装10g的数据库软件,基于当时的AIX情况，由于系统是通过OS升级重新导入到P6 570上的需要重新开启AIO,数据库版本为9.2.0.7 需要再打一个bos.cifs.**补丁

2 copy 10g ORACLE_HOME/rdbms/admin 下的脚本
ORACLE_HOME/rdbms/admin/utlu102i.sql /tmp/utlu102i.sql
ORACLE_HOME/rdbms/admin/utltzuv2.sql /tmp/utltzuv2.sql

3 收集数据库信息

SQL> spool Database_Info.log
SQL> @utlu102i.sql
SQL> spool off

检查各项参数：（Database，Logfiles，Tablespaces，Update Parameters，Deprecated Parameters，Obsolete Parameters，Components，Miscellaneous Warnings，SYSAUX Tablespace

3 检查角色

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’);

由于9i的connect role具有如下角色：

SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS
WHERE GRANTEE=’CONNECT’

GRANTEE PRIVILEGE
—————————— —————————
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

10g 的connect 仅仅具有 create session 其他privilege需要手动grant

4 创建dblink 脚本 (跳过）

5 检查timestamp 数据类型

$ sqlplus ‘/as sysdba’

SQL> spool TimeZone_Info.log
SQL> @utltzuv2.sql
SQL> spool off

如果 timezone_info.log 指出timezone格式不支持如下操作：

For example, user scott has a table tztab:

create table tztab(x number primary key, y timestamp with time zone);
insert into tztab values(1, timestamp ”);

Before upgrade, you can create a table tztab_back. Note column y here is defined as VARCHAR2 to preserve the original value.

create table tztab_back(x number primary key, y varchar2(256));
insert into tztab_back select x,
to_char(y, ‘YYYY-MM-DD HH24.MI.SSXFF TZR’) from tztab;
After upgrade, you need to update the data in the table tztab using the value in tztab_back.

update tztab t set t.y = (select to_timestamp_tz(t1.y,
‘YYYY-MM-DD HH24.MI.SSXFF TZR’) from tztab_back t1 where t.x=t1.x);

6 NLS_NCHAR_CHARACTERSET 跳过（8i-10g 需要检查此项）

7 收集系统用户信息（9i没有dictionary 信息收集）
sqlplus ‘/as sysdba’

SQL>spool gdict

SQL>grant analyze any to sys;

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);

SQL>spool off

8 检查数据库对象状态

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

重新编辑invalid objects
$ sqlplus ‘/as sysdba’
SQL> @?/rdbms/admin/utlrp.sql

If you are upgrading from Oracle9iR2 (9.2), verify that the view dba_registry contains data. If the view is empty, run the following scripts from the 9.2 home:

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

9 Check for corruption in the dictionary useing the following commands in SQL*Plus connected as sys:

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

This creates a script called analyze.sql.
Now execute the following steps.

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

analyze.sql 不应该返回任何错误

10 确保所有物化视图刷新完毕,并且所有刷新停止

$ sqlplus ‘/ as sysdba’
SQL> select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;

11 停止监听

$ lsnrctl
LSNRCTL> stop

确保没有datafile 需要recover

$ sqlplus ‘/ as sysdba’
SQL> select * from v$recover_file;

12 确保数据库没有datafile在backup状态

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

13 查看是否存在分布式事务

SQL> select * from dba_2pc_pending;

14 关闭所有jobs

15 确保system sys用户的默认表空间为system

SQL> select username, default_tablespace from dba_users
where username in (‘SYS’,’SYSTEM’);
To modify use:

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

16 查看aud$是否启用

SQL> select tablespace_name from dba_tables where table_name=’AUD$’;
If the AUD$ table exists, and is in use, upgrade performance can be effected depending on the number of records in the table.

Note.979942.1 Ext/Pub Database upgrade appears to have halted at SYS.AUD$ Table

这次升级直接select count (*) from aud$ 返回值为0 直接继续下一步

17 Note down where all control files are located.

SQL> select * from v$controlfile;

18 If table XDB.MIGR9202STATUS exists in the database, drop it before upgrading the database
(to avoid the issue described in Note:356082.1)

19 Shutdown the database.

$ sqlplus ‘/as sysdba’
SQL> shutdown immediate;

20 备份数据库

21 copy pfile from 9i $ORACLE_HOME to 10g $ORACLE_HOME

Make sure the PGA_AGGREGATE_TARGET initialization parameter is set to
at least 24 MB.

Ensure that the SHARED_POOL_SIZE and the LARGE_POOL_SIZE are at least 150Mb

Make sure the JAVA_POOL_SIZE initialization parameter is set to at least 150 MB.

AQ_TM_PROCESSES=0 and JOB_QUEUE_PROCESSES=0 for the duration of the upgrade

If you have defined an UNDO tablespace, set the parameter UNDO_MANAGEMENT=AUTO

虽然在10g上通过startup upgarde oracle会自动禁止这些参数，但是这里还是提前设置
并且可以参照上面的database_info信息进行调整

22 检查剩余空间是否充足，确保archive归档空间

23 检查nls_lang-> database nls_characterset

24 cp listener* tnsnames* to 10g $ORACLE_HOME

25 创建密码文件

26 Update the oratab entry, to set the new ORACLE_HOME and disable automatic startup:

SID:ORACLE_HOME:N

27 确定环境变量注意unset TNS_ADMIN ORA_NLS33 chang to ORA_NLS10

export ORA_NLS10= /u01/finprod/finproddb/10.2/db_1/nls/data/9idata

/u01/finprod/finproddb/10.2/db_1/perl/bin/perl

oraprod(DATABASE)@nbrbwdev5:perl $ORACLE_HOME/nls/data/old/cr9idata.pl
Creating directory /u01/finprod/finproddb/10.2/db_1/nls/data/9idata …
Copying files to /u01/finprod/finproddb/10.2/db_1/nls/data/9idata…
Copy finished.

28 建立sysaux 表空间（注意本地管理自动段空间管理不然后面跑脚本会报错）
CREATE TABLESPACE sysaux DATAFILE ‘xxxx sysaux01.dbf’
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

29 开始升级
SQL> spool upgrade.log
SQL> @catupgrd.sql

30 查看组件状态

SQL> @utlu102s.sql TEXT

SQL> select comp_name, status, version from dba_registry;

31 Restart the database:

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

32 如果开启Oracle Label Security policies

SQL> @olstrig.sql

此库没有采用跳过

33 Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

If there are still objects which are not valid after running the script run the following:

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
Now compare the invalid objects in the file ‘invalid_post.lst’ with the invalid objects in the file ‘invalid_pre.lst’ you created in step 9.

NOTE: If you have upgraded from version 9.2 to version 10.2 and find that the following views are invalid, the views can be safely ignored (or dropped):

SYS.V_$KQRPD
SYS.V_$KQRSD
SYS.GV_$KQRPD
SYS.GV_$KQRSD

Invalid x_$ Objects After Upgrade [ID 361757.1]

ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA [ID 565600.1]

34 开启数据库 ,开启lsnrctl ,启用rbo 使用dbms_stats.gather_database_stats收集信息,开启rbo 修改compatible ,观察数据库性能35 修改数据库字符集由于csscan扫描转换成zhs16gbk某些lob字段可能产生乱码所以放弃csalter转换字符集方案，并且不存在超集，子集关系所以采用强制转换方式：
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE zhs16gbk;
SQL>shutdown immediate;
SQL>startup

Invalid x_$ Objects After Upgrade [ID 361757.1]
ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA [ID 565600.1]
lock-held-by-in-doubt-distributed-transaction

Mandy 13 years ago

This really is something I have to find more information about, i appreciate you for the posting.

myp2p eu rugby league 13 years ago

I wanted to tell you, I do believe there is a trouble with your Feed, it’s not at all showing right in my RSS viewer. It simply started happening yesterday, did you change some thing on the website?

admin 13 years ago

yeah , i found also , thank you