oracle手动升级

oracle手动升级有两种方式，一种在安装oracle软件之后，安装升级包，然后创建数据库；另一种在oracle数据库创建以后，进行升级。

本文主要介绍oracle数据库创建后进行的数据库升级(10.2.0.1-10.2.0.4)，代码如下：

SQL> --查看当前数据库版本
SQL> select * from v$version;

SQL> --给用户scott解锁
SQL> alter user scott account unlock;

SQL> --查看表空间
SQL> select * from dba_tablespaces;

SQL> --查看数据文件存放位置
SQL> select * from dba_data_files;

SQL> --创建测试表空间
SQL> create tablespace ts_upgrade datafile '/u02/oradata/data/orcl/ts_upgrade01.dbf' size 5m autoextend on maxsize 30g;

SQL> --创建用户
SQL> create user u_upgrade identified by "123456" default tablespace ts_upgrade temporary tablespace temp;

SQL> --为用户赋权
SQL> grant connect,resource to u_upgrade;

SQL> --创建测试表
SQL> create table u_upgrade.dept as select * from scott.dept;

SQL> select * from u_upgrade.dept;

[oracle@feegle ~]$ #停止em
[oracle@feegle ~]$ emctl stop dbconsole

[oracle@feegle ~]$ #停止sql*plus
[oracle@feegle ~]$ isqlplusctl stop

[oracle@feegle ~]$ #停止监听
[oracle@feegle ~]$ lsnrctl stop
[oracle@feegle ~]$ #安装升级包（p6810189_10204_Linux-x86-64.zip）
[oracle@feegle ~]$ #手动升级数据库
[oracle@feegle ~]$ sqlplus /nolog
SQL> conn /as sysdba
SQL> --以升级模式启动数据库
SQL> startup upgrade;

SQL> --重建数据字典（建议关闭数据库归档）
SQL> spool upgrade.log

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

#####重建数据字典部分截图#####
Oracle Database 10.2 Upgrade Status Utility           06-15-2012 18:27:56
.
Component                                Status         Version  HH:MM:SS
Oracle Database Server                    VALID      10.2.0.4.0  00:12:04
JServer JAVA Virtual Machine              VALID      10.2.0.4.0  00:04:58
Oracle XDK                                VALID      10.2.0.4.0  00:00:32
Oracle Database Java Packages             VALID      10.2.0.4.0  00:00:28
Oracle Text                               VALID      10.2.0.4.0  00:00:25
Oracle XML Database                       VALID      10.2.0.4.0  00:02:25
Oracle Workspace Manager                  VALID      10.2.0.4.3  00:00:43
Oracle Data Mining                        VALID      10.2.0.4.0  00:00:21
OLAP Analytic Workspace                   VALID      10.2.0.4.0  00:00:23
OLAP Catalog                              VALID      10.2.0.4.0  00:01:02
Oracle OLAP API                           VALID      10.2.0.4.0  00:00:53
Oracle interMedia                         VALID      10.2.0.4.0  00:03:52
Spatial                                   VALID      10.2.0.4.0  00:01:37
Oracle Expression Filter                  VALID      10.2.0.4.0  00:00:12
Oracle Enterprise Manager                 VALID      10.2.0.4.0  00:01:18
Oracle Rule Manager                       VALID      10.2.0.4.0  00:00:10
.
Total Upgrade Time: 00:31:30
DOC>#######################################################################
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>#######################################################################
DOC>#
SQL>
#####重建数据字典部分截图#####

SQL> spool off

SQL> --关闭数据库
SQL> shutdown immediate;
SQL> --启动数据库
SQL> startup;

SQL> --编译无效对象
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

#####编译无效对象部分截图#####
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-06-15 18:45:01
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
-------------------
                  0
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
---------------------------
                          0
SQL>
#####编译无效对象部分截图#####

SQL> 验证升级
SQL> --查看组件状态
SQL> --status=VALID
SQL> select comp_name, version, status from sys.dba_registry;

SQL> --查看表空间状态
SQL> select tablespace_name,status from dba_tablespaces;

SQL> --查看用户
SQL> select username from dba_users;

SQL> --查看用户对象
SQL> select object_name from dba_objects where owner='SCOTT';
SQL> select object_name from dba_objects where owner='U_UPGRADE';
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
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


[oracle@feegle ~]$ #启动监听
[oracle@feegle ~]$ lsnrctl start
[oracle@feegle ~]$ #启动EM
[oracle@feegle ~]$ emctl start dbconsole
[oracle@feegle ~]$ #启动SQL*PLUS
[oracle@feegle ~]$ isqlplusctl start