单实例升级: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