Linux x86-64上手动升级Oracle10204到 10205_oracle 10205 linux-x86-64-CSDN博客

Linux x86-64上手动升级Oracle10204到 10205

1、查看系统信息

[root@SXDS18 software]# uname -a

Linux SXDS18 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

[root@SXDS18 software]#

2、查看主机名

[root@SXDS18 software]# hostname

SXDS18

[root@SXDS18 software]#

3、关闭应用后关闭数据库

[root@SXDS18 software]# su - oracle

[oracle@SXDS18 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on 星期四 4月 19 17:38:26 2012

SQL> conn /as sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

4、关闭监听

[oracle@SXDS18 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 19-4月 -2012 17:44:27

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SXDS18)(PORT=1521)))

The command completed successfully

[oracle@SXDS18 ~]$

5、查看后台数据库进程是否还存在

[oracle@SXDS18 ~]$ ps -ef | grep ora

root 4240 4214 0 Mar06 ? 00:17:25 hald-addon-storage: polling /dev/hda

root 21773 21676 0 16:13 pts/4 00:00:00 su - oracle

oracle 21774 21773 0 16:13 pts/4 00:00:00 -bash

root 22766 22719 0 17:38 pts/2 00:00:00 su - oracle

oracle 22767 22766 0 17:38 pts/2 00:00:00 -bash

oracle 22881 22767 0 17:47 pts/2 00:00:00 ps -ef

oracle 22882 22767 0 17:47 pts/2 00:00:00 grep ora

oracle 25309 1 0 Mar14 ? 00:00:00 /usr/libexec/bonobo-activation-server --ac-activate --ior-output-fd=16

[oracle@SXDS18 ~]$

6、使用tar命令冷备份数据库（软件和数据库文件）

主要备份，oracle主目录和数据文件所在目录

[root@SXDS18 software]# tar -zcvf /software/oradate_gxxt19.tar.gz /oracle/product/oradata/gxxt19

[root@SXDS18 software]# tar -zcvf /software/db10g.tar.gz /oracle/product/db10g

7、上传并解压升级补丁文件

p8202632_10205_Linux-x86-64.zip

8、安装补丁

进入解压后的目录执行

[oracle@SXDS18 Disk1]$ ./runInstaller

Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, redhat-6, UnitedLinux-1.0, asianux-1, asianux-2, asianux-3, enterprise-4, enterprise-5 or SuSE-11

Passed

All installer requirements met.

运行root脚本

[root@SXDS18 ~]# /oracle/product/db10g/root.sh

Running Oracle 10g root.sh script...

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /oracle/product/db10g

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]: y

Copying dbhome to /usr/local/bin ...

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

[n]: y

Copying oraenv to /usr/local/bin ...

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

[n]: y

Copying coraenv to /usr/local/bin ...

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

Database Configuration Assistant when a database is created

HFinished running generic part of root.sh script.

Now product-specific root actions will be performed.

[root@SXDS18 ~]#

9、以升级模式启动数据库

[oracle@SXDS18 admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on ??? 4? 19 20:29:13 2012

SQL> conn /as sysdba

Connected to an idle instance.

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 1828716544 bytes

Fixed Size 2097080 bytes

Variable Size 1157627976 bytes

Database Buffers 654311424 bytes

Redo Buffers 14680064 bytes

Database mounted.

Database opened.

SQL>

10、执行升级后的脚本

SQL> spool /software/catupgrd.txt

SQL> @catupgrd.sql

。。。。。。

TIMESTAMP

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

COMP_TIMESTAMP UPGRD_END 2012-04-19 21:05:56

Oracle Database 10.2 Upgrade Status Utility 04-19-2012 21:05:56

Component Status Version HH:MM:SS

Oracle Database Server VALID 10.2.0.5.0 00:16:08

JServer JAVA Virtual Machine VALID 10.2.0.5.0 00:03:11

Oracle XDK VALID 10.2.0.5.0 00:00:37

Oracle Database Java Packages VALID 10.2.0.5.0 00:00:33

Oracle Text VALID 10.2.0.5.0 00:00:21

Oracle XML Database VALID 10.2.0.5.0 00:01:55

Oracle Workspace Manager VALID 10.2.0.5.0 00:00:30

Oracle Data Mining VALID 10.2.0.5.0 00:00:20

OLAP Analytic Workspace VALID 10.2.0.5.0 00:00:18

OLAP Catalog VALID 10.2.0.5.0 00:01:09

Oracle OLAP API VALID 10.2.0.5.0 00:00:55

Oracle interMedia VALID 10.2.0.5.0 00:02:14

Spatial VALID 10.2.0.5.0 00:02:01

Oracle Expression Filter VALID 10.2.0.5.0 00:00:19

Oracle Enterprise Manager VALID 10.2.0.5.0 00:00:58

Oracle Rule Manager VALID 10.2.0.5.0 00:00:09

Total Upgrade Time: 00:32:54

DOC>#######################################################################

DOC>

DOC> The above PL/SQL lists the SERVER components in the upgraded

DOC> database, along with their current version and status.

DOC>

DOC> Please review the status and version columns and look for

DOC> any errors in the spool log file. If there are errors in the spool

DOC> file, or any components are not VALID or not the current version,

DOC> consult the Oracle Database Upgrade Guide for troubleshooting

DOC> recommendations.

DOC>

DOC> Next shutdown immediate, restart for normal operation, and then

DOC> run utlrp.sql to recompile any invalid application objects.

DOC>

DOC>#######################################################################

DOC>#

SQL>

SQL> spool off

11、关闭数据库并使用正常模式打开数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1828716544 bytes

Fixed Size 2097080 bytes

Variable Size 1191182408 bytes

Database Buffers 620756992 bytes

Redo Buffers 14680064 bytes

Database mounted.

Database opened.

SQL>

12、执行编译无效对象的脚本

SQL> @utlrp.sql

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2012-04-19 21:14:14

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC> objects in the database. Recompilation time is proportional to the

DOC> number of invalid objects in the database, so this command may take

DOC> a long time to execute on a database with a large number of invalid

DOC> objects.

DOC>

DOC> Use the following queries to track recompilation progress:

DOC>

DOC> 1. Query returning the number of invalid objects remaining. This

DOC> number should decrease with time.

DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC> 2. Query returning the number of objects compiled so far. This number

DOC> should increase with time.

DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC> This script automatically chooses serial or parallel recompilation

DOC> based on the number of CPUs available (parameter cpu_count) multiplied

DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC> On RAC, this number is added across all RAC nodes.

DOC>

DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC> recompilation. Jobs are created without instance affinity so that they

DOC> can migrate across RAC nodes. Use the following queries to verify

DOC> whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC> 1. Query showing jobs created by UTL_RECOMP

DOC> SELECT job_name FROM dba_scheduler_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC> 2. Query showing UTL_RECOMP jobs that are running

DOC> SELECT job_name FROM dba_scheduler_running_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

TIMESTAMP

COMP_TIMESTAMP UTLRP_END 2012-04-19 21:14:29

DOC> The following query reports the number of objects that have compiled

DOC> with errors (objects that compile with errors have status set to 3 in

DOC> obj$). If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERRORS

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

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

ERRORS DURING RECOMPILATION

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

SQL>

这里注意到有30个对象存在错误，我们通过dba_objects试图可以查看到是哪些对象错误

SQL> select owner,object_name,object_type from dba_objects where status <> 'VALID';

OWNER OBJECT_NAME OBJECT_TYPE

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

SXDS SP_AS_UPGRADE PROCEDURE

SXDS SP_DB_EDIT_REPORT_RESERVE PROCEDURE

SXDS SP_FA_RPT_MO_GATHER_ALL_DATA PROCEDURE

SXDS SP_FA_RPT_MO_YMQKTJB PROCEDURE

SXDS SP_FA_RPT_MO_YMQKTJB_UP PROCEDURE

SXDS SP_FA_RPT_NBYB_CARD PROCEDURE

SXDS TR_GL_BANK_CTRAST_BEFOREINSERT TRIGGER

SXDS TRIG_FASC_COMPANY TRIGGER

SXDS SP_IMP30_SEQUENCE PROCEDURE

SXDS SP_LP_BASICDATA_COLLECT PROCEDURE

SXDS SP_V51TOV52_ALL PROCEDURE

SXDS SP_V51TOV52_UPDATE_PROJECT PROCEDURE

SXDS UPDATEGLJZFA PROCEDURE

SXDS INTEGRITYPACKAGE PACKAGE

SXDSOLD SP_IMP30_SEQUENCE PROCEDURE

SXDSOLD SP_GL_VOUCHERPOST PROCEDURE

UFSXDS SP_FA_RPT_MO_YMQKTJB PROCEDURE

UFSXDS SP_FA_RPT_MO_YMQKTJB_UP PROCEDURE

UFSXDS SP_FA_RPT_NBYB_CARD PROCEDURE

UFSXDS SP_IMP30_SEQUENCE PROCEDURE

UFSXDS SP_LP_BASICDATA_COLLECT PROCEDURE

UFSXDS SP_V51TOV52_ALL PROCEDURE

UFSXDS SP_V51TOV52_UPDATE_PROJECT PROCEDURE

UFSXDS UPDATEGLJZFA PROCEDURE

UFSXDS SP_AS_UPGRADE PROCEDURE

UFSXDS SP_DB_EDIT_REPORT_RESERVE PROCEDURE

UFSXDS SP_FA_RPT_MO_GATHER_ALL_DATA PROCEDURE

UFSXDS INTEGRITYPACKAGE PACKAGE

UFSXDS TR_GL_BANK_CTRAST_BEFOREINSERT TRIGGER

UFSXDS TRIG_FASC_COMPANY TRIGGER

SQL> select count(1) from dba_objects where status <> 'VALID';

COUNT(1)

----------

SQL>

可以看到报错的对象不属于系统用户，是应用用户下的，这个不影响也不在升级的范围内。忽略过。

13、重启数据库完成升级

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1828716544 bytes

Fixed Size 2097080 bytes

Variable Size 1191182408 bytes

Database Buffers 620756992 bytes

Redo Buffers 14680064 bytes

Database mounted.

Database opened.

SQL>

14、验证组件升级成功

SQL> set linesize 200

SQL> col comp_name for a30

SQL> col version for a30

SQL> col status for a30

SQL> set linesize 200

SQL> select comp_name,version,status from dba_registry;

COMP_NAME VERSION STATUS

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

Spatial 10.2.0.5.0 VALID

Oracle interMedia 10.2.0.5.0 VALID

OLAP Catalog 10.2.0.5.0 VALID

Oracle Enterprise Manager 10.2.0.5.0 VALID

Oracle XML Database 10.2.0.5.0 VALID

Oracle Text 10.2.0.5.0 VALID

Oracle Expression Filter 10.2.0.5.0 VALID

Oracle Rule Manager 10.2.0.5.0 VALID

Oracle Workspace Manager 10.2.0.5.0 VALID

Oracle Data Mining 10.2.0.5.0 VALID

Oracle Database Catalog Views 10.2.0.5.0 VALID

Oracle Database Packages and T 10.2.0.5.0 VALID

ypes

JServer JAVA Virtual Machine 10.2.0.5.0 VALID

Oracle XDK 10.2.0.5.0 VALID

Oracle Database Java Packages 10.2.0.5.0 VALID

OLAP Analytic Workspace 10.2.0.5.0 VALID

Oracle OLAP API 10.2.0.5.0 VALID

SQL>

15、退出SQLPLUS，重新登陆查看版本

[oracle@SXDS18 admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on 星期四 4月 19 21:22:09 2012

SQL> conn /as sysdba

Connected.

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

SQL>

至此，数据库升级完成。

From:http://blog.csdn.net/snowfox326043/article/details/7479118