oracle 升级(from 10.2.0.1 to 10.2.0.4 linux 32bit 单实例)

单实例升级：from 10.2.0.1 to 10.2.0.4 (linux 32bit 单实例)

 

Oracle升级

环境：centos5.6

Oracle :    10.2.0.1

目标：from 10.2.0.1 to 10.2.0.4

 

总结：

1、升级前关闭oracle与相关服务

2、system,undo,shared_pool大小

3、慎用startup downgrade

 

1、设置密码文件

[ora@dg-pp ~]$ orapwd file=/opt/oracle/product/10.2.0/db_1/dbs/orapwtest password=oracle force=y

2、正常关闭oracle

sys@TEST> shutdown immediate;

3、重新relink oracle

[ora@dg-pp ~]$ cd $ORACLE_HOME/rdbms/lib

[ora@dg-pp lib]$ make -f ins_rdbms.mk dv_off

make: *** No rule to make target `dv_off'.  Stop.

[ora@dg-pp lib]$ cd $ORACLE_HOME/bin

[ora@dg-pp bin]$ relink oracle

chmod 755 /opt/oracle/product/10.2.0/db_1/bin

mv -f /opt/oracle/product/10.2.0/db_1/bin/oracle /opt/oracle/product/10.2.0/db_1/bin/oracleO

mv /opt/oracle/product/10.2.0/db_1/rdbms/lib/oracle /opt/oracle/product/10.2.0/db_1/bin/oracle

chmod 6751 /opt/oracle/product/10.2.0/db_1/bin/oracle

 

4、开启数据库

test> startup

5、设置环境变量

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORACLE_SID=test

export PATH=$ORACLE_HOME/bin:$PATH

 

6、关闭oracle数据库和oracle相关服务

[ora@dg-pp ~]$ emctl stop dbconsole

 

TZ set to PRC

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 

Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.

http://dg-pp:5500/em/console/aboutApplication

Stopping Oracle Enterprise Manager 10g Database Control ...

 ...  Stopped.

 

[ora@dg-pp ~]$ isqlplusctl stop

iSQL*Plus 10.2.0.1.0

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

iSQL*Plus instance on port 5560 is not running ...

 

[ora@dg-pp ~]$ lsnrctl stop

sys@TEST> shutdown immediate;

 

7、解压升级补丁

[ora@dg-pp opt]$ unzip p6810189_10204_Linux-x86-64.zip

 

8、图形安装

图形省略

[root@dg-pp ~]# /opt/oracle/product/10.2.0/db_1/root.sh

Running Oracle10 root.sh script...

 

The following environment variables are set as:

    ORACLE_OWNER= ora

    ORACLE_HOME=  /opt/oracle/product/10.2.0/db_1

 

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n)

[n]:

The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)

[n]:

The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)

[n]:

 

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

 

 

9、启动listener

[ora@dg-pp opt]$ lsnrctl start

Services Summary...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

 

10、dbua升级

图形省略

 

11、检测错误

[ora@dg-pp opt]$ cd $ORACLE_HOME/install

 

Do you wish to continue (y/n) [n]: y

Spooling the error log /tmp/changePerm_err.log...

12、

SQL> conn /as sysdba

Connected.

SQL> shutdown immediate;

SQL> startup

 

13、

SQL> DROP FUNCTION DVSYS.REALM_SDML_AUTHORIZED ;

DROP FUNCTION DVSYS.REALM_SDML_AUTHORIZED

*

ERROR at line 1:

ORA-01435: user does not exist

SQL> DROP PROCEDURE DVSYS.SYNCHRONIZE_POLICY_FOR_OBJECT;

DROP PROCEDURE DVSYS.SYNCHRONIZE_POLICY_FOR_OBJECT

*

ERROR at line 1:

ORA-01435: user does not exist

SQL> DECLARE

  2                CURSOR stmt IS

  3                         SELECT u.name, o.name, r.pname

  4                             FROM user$ u, obj$ o, rls$ r

  5                         WHERE u.user# = o.owner#

  6                             AND r.obj# = o.obj#

  7                             AND bitand(r.stmt_type,65536) > 0;

  8  

  9                         object_schema VARCHAR2(32) := NULL;

 10                         object_name VARCHAR2(32) := NULL;

 11                         policy_name VARCHAR2(32) := NULL;

 12  

 13                         BEGIN

 14                          OPEN stmt;

 15                            LOOP

 16                                 FETCH stmt INTO object_schema,

 17                                                 object_name,

 18                                                 policy_name;

 19                                 EXIT WHEN stmt%NOTFOUND;

 20                                 DBMS_RLS.DROP_POLICY(

 21                                 '"'||object_schema||'"',

 22                                 '"'||object_name||'"',

 23                                 '"'||policy_name||'"');

 24                            END LOOP;

 25                          CLOSE stmt;

 26                         END;

 27                        /

 

PL/SQL procedure successfully completed.

 

14、

SQL> shutdown immediate;

[ora@dg-pp install]$ cd $ORACLE_HOME/rdbms/lib

[ora@dg-pp lib]$ make -f ins_rdbms.mk dv_on

[ora@dg-pp lib]$ relink oracle

SQL> startup

 

15、重新编译对象

SQL> shutdown immediate;

SQL> startup

SQL> @?/rdbms/admin/utlrp.sql

 

 

16 、升级回退（慎用如果你不想回退到升级前）

SQL> startup downgrade;

SQL> spool downgrade.out

SQL> @?/rdbms/admin/catdwgrd.sql

SQL> spool off

 

17、修改兼容性参数

SQL> alter system set compatible='10.2.0.4.0' scope=spfile;

 

18、升级检测

SQL>select comp_name,version,status from sys.dba_registry;

检查组件的升级情况

SQL>select * from utl_recomp_errors;

SQL> select * from v$version;

 

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

PL/SQL Release 10.2.0.4.0 - Production

CORE    10.2.0.4.0      Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production