环境: A机:RHEL5.5 + Oracle B机:RHEL5.5 需求: A机10.2.0.4数据库,在B机升级到11.2.0.4,应用最新PSU补丁程序。 目录: 一、 确认是否可以直接升级 二、 B机安装11g软件,打好PSU补丁 三、 B机环境变量检查 四、 B机创建需要的目录 五、 A机原库升级前检查

六、 A机备份原库 七、 B机恢复数据库 八、 B机升级数据库

九、 B机升级后操作

一、 确认是否可以直接升级

本次需求:Oracle -> 从下图中看到,可以直接升级。

二、 B机安装11g软件,打好PSU补丁



三、 B机环境变量检查

echo $PATH

四、 B机创建需要的目录


mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdump 
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump 
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile 
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/scripts

五、 A机原库升级前检查


5.1 从B机上传utlu112i.sql脚本到A机

$ scp $ORACLE_HOME/rdbms/admin/utlu112i.sql 192.168.xx.xx:/home/oracle          

5.2 A机执行utlu112i.sql进行升级前检查

[oracle@oracle10g oracle]$ sqlplus / as sysdba
SQL*Plus: Release - Production on Fri Dec 26 06:46:46 2014
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> spool
SQL> @/home/oracle/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 08-28-2015 10:02:57
Script Version: Build: 007
--> name:          VAS
--> version:
--> compatible:
--> blocksize:     8192
--> platform:      Linux x86 64-bit
--> timezone file: V4
Tablespaces: [make adjustments in the current environment]
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 991 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 790 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
Flashback: OFF
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
-- No renamed parameters found. No changes are required.
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
--> background_dump_dest         11.1       DEPRECATED   replaced by  "diagnostic_dest"
--> user_dump_dest               11.1       DEPRECATED   replaced by  "diagnostic_dest"

Components: [The following database components will be upgraded or installed]
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Data Mining                  [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
Miscellaneous Warnings
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER CRNOPHQOA has 2 INVALID objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
.... prior to executing your upgrade to confirm the recycle bin is empty.
WARNING: --> JOB_QUEUE_PROCESS value must be updated
.... Your current setting of "10" is too low.

.... Starting with Oracle Database 11g Release 2 (11.2), setting
.... JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and
.... DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES
.... to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were
.... unaffected and would still run. This parameter must be updated to
.... a value greater than 32  (default value is 1000) prior to upgrade.
.... Not doing so will affect the running of utlrp.sql after the upgrade
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

    EXECUTE dbms_stats.gather_dictionary_stats;

SQL> spool off

5.3 根据检查结果调整A机数据库

5.3.1 表空间对比满足条件

select file_name,tablespace_name,maxbytes/1024/1024 from dba_data_files
where tablespace_name in('SYSTEM','UNDOTBS1','SYSAUX')
union all
select file_name,tablespace_name,maxbytes/1024/1024 from dba_temp_files;

5.3.2 A机创建pfile文件

SQL> create pfile='/home/oracle/orcl10.pfile' from spfile;

5.3.3 A机scp pfile文件到B机

$ scp /home/oracle/orcl10.pfile 192.168.xx.xx:/home/oracle

5.3.4 B机上编辑pfile文件

加上 *.diagnostic_dest='$ORACLE_BASE'


$ cp /home/oracle/orcl10.pfile $ORACLE_HOME/dbs/init$ORACLE_SID.ora

5.3.5 A机运行utlrp.sql 脚本,重新编译无效对象

重新编译SYS 和SYSTEM 中的无效对象

col OWNER for a10;
col OBJECT_NAME for a30;
col OBJECT_TYPE for a10;
select * from registry$nonsys_inv_objs; //非sys/system的失效对象

select * from registry$sys_inv_objs;   //sys/system的失效对象
SQL> @?/rdbms/admin/utlrp.sql;
重新执行 @/home/oracle/utlu112i.sql,查看是否重新编译,


DBA Recyclebin purged.

5.3.7 A机收集系统信息EXECUTE dbms_stats.gather_dictionary_stats;

在升级前收集字典统计信息,否则预升级工具( utlu102i.sql)会花费更长时间

SQL> EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.


六、 A机备份原库

为了不影响业务的网络带宽,可采用DCN网络进行NFS的搭建,同时不必再传送备份文件。 NFS配置参考: A机备份原库可参考:

七、 B机恢复数据库


注意:这里恢复完成不要直接尝试打开数据库。因为我们是要升级!需要open resetlogs upgrade(在下一个步骤说明)。

八、 B机升级数据库

8.1 修改恢复后的数据库为非归档模式


SQL> select status from v$instance;


SQL> alter database noarchivelog;

Database altered.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     27
Current log sequence           29

8.2 alter database open resetlogs upgrade;

SQL> alter database open resetlogs upgrade;

8.3 执行升级脚本

SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql

本次升级经验值: 测试库此步骤升级运行时间:37分钟。 生产库此步骤升级运行时间:61分钟。

九、 B机升级后操作

9.1 创建spfile文件

SQL> startup pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora'
SQL> create spfile from pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora';

9.2 执行EXECUTE dbms_stats.gather_dictionary_stats;

SQL> exec dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.

9.3 重新编译

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

9.4 查看失效对象

SQL> SELECT count(*) FROM dba_invalid_objects;


9.5 运行升级后检查脚本

SQL> @?/rdbms/admin/utlu112s.sql
Oracle Database 11.2 Post-Upgrade Status Tool           08-31-2015 16:13:55
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
Oracle Server
.                                         VALID  00:07:44
JServer JAVA Virtual Machine
.                                         VALID  00:05:02
Oracle Workspace Manager
.                                         VALID  00:00:21
OLAP Analytic Workspace
.                                         VALID  00:00:20
OLAP Catalog
.                                         VALID  00:00:30
.                                         VALID  00:00:15
Oracle Enterprise Manager
.                                         VALID  00:05:23
Oracle XDK
.                                         VALID  00:01:48
Oracle Text
.                                         VALID  00:00:30
Oracle XML Database
.                                         VALID  00:02:42
Oracle Database Java Packages
.                                         VALID  00:00:08
Oracle Multimedia
.                                         VALID  00:02:21
.                                         VALID  00:03:32
Oracle Expression Filter
.                                         VALID  00:00:06
Oracle Rules Manager
.                                         VALID  00:00:05
Final Actions
.                                                                00:00:25
Total Upgrade Time: 00:31:21

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

9.6 数据库层面应用PSU

cd $ORACLE_HOME/rdbms/admin/
SQL> @catbundle.sql psu apply


./opatch lspatches
./opatch lsinventory


SQL> select * from dba_registry_history;





0 条评论
登录 后参与评论