Upgrade Oracle Database from 10.2.0.1 to 11.2.0.4

一 确定升级路线

经过官方文档查看如果从10g升级到目前最新版本11.2.0.4话，10.2.0.1必须先升级到10.2.0.4。

 

Direct upgrade is not supported. Upgrade to an intermediate Oracle Database release before you can upgrade to the new Oracle Database 11g release, as follows:

·         9.0.1.3 (or earlier) -> 9.0.1.4 -> 10.2.0.4 -> 11.2

·          

但是在metalink上查看到不同的说法，metalink 上说是最小版本为10.2.0.2就可以升级到11.2了 。Doc ID 980693.1

The 11.2 Upgrade Guide list minimum version of oracle that can be upgraded directly to 11.2

  In the case of an upgrade from 10.2 to 11.2 - the minimum version is 10.2.0.2

  This customer was attempting to upgrade from 10.2.0.1

下面我从实验来查看到底是什么情况，我先把数据库打到10.2.0.2

二 环境

1 数据库版本信息

SQL> select version,status,instance_name from v$instance;

 

VERSION           STATUS       INSTANCE_NAME

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

10.2.0.1.0        OPEN         ora10g

 

SQL>

2 oracle 10g环境变量

umask 022

export ORACLE_BASE=/darin

export ORACLE_HOME=/darin/product/10.2.0

export ORACLE_SID=ora10g

export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"

export NLS_LANG=AMERICAN_AMERICA.zhs16gbk

export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin

export PATH=$ORACLE_HOME/bin:${PATH}:$HOME/bin

export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin

export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin

export ORACLE_TERM=xterm

export TNS_ADMIN=$ORACLE_HOME/network/admin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib

export CLASSPATH=$ORACLE_HOME/JRE

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib

export THREADS_FLAG=native

export TEMP=/tmp

export TMPDIR=/tmp

export PS1=`hostname`$'[$PWD]'

alias sql='sqlplus "/as sysdba"'

export DISPLAY=192.168.88.1:0.0

3 oracle 11g 环境变量

umask 022

export ORACLE_BASE=/darin

export ORACLE_HOME=/darin/product/11.2.0.4

export ORACLE_SID=ora10g

export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"

export NLS_LANG=AMERICAN_AMERICA.zhs16gbk

export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin

export PATH=$ORACLE_HOME/bin:${PATH}:$HOME/bin

export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin

export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin

export ORACLE_TERM=xterm

export TNS_ADMIN=$ORACLE_HOME/network/admin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib

export CLASSPATH=$ORACLE_HOME/JRE

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib

export THREADS_FLAG=native

export TEMP=/tmp

export TMPDIR=/tmp

export PS1=`hostname`$'[$PWD]'

alias sql='sqlplus "/as sysdba"'

export DISPLAY=192.168.88.1:0.0

三 首先升级10.2.0.1到10.2.0.2

  下载补丁包p4547817_10202_Linux-x86-64.zip，删除到服务器上解压

   执行安装补丁

arin[/darin/soft/Disk1]./runInstaller

Starting Oracle Universal Installer...

 

Checking installer requirements...

 

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

                                      Passed

 

 

All installer requirements met.

 

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-08-23_12-12-49AM. Please wait ...

 

Oracle home还是原来的

此时检查到oracle还在运行，需要手动shutdown

执行下一步

发生如下报错

我有个sqlplus 登录到oracle里面了，尝试退出sqlplus 重新retry 成功

执行root脚本

[root@darin ~]# /darin/product/10.2.0/root.sh

Running Oracle10 root.sh script...

 

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME=  /darin/product/10.2.0

 

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

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

 

启动数据库

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  281018368 bytes

Fixed Size                  2070552 bytes

Variable Size             109053928 bytes

Database Buffers          163577856 bytes

Redo Buffers                6316032 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

此时发现数据库被强制关闭掉了，查看alert发现，必须启动为upgrade模式

 

再次启动尝试

 

 

SQL> startup upgrade

ORACLE instance started.

 

Total System Global Area  281018368 bytes

Fixed Size                  2070552 bytes

Variable Size             109053928 bytes

Database Buffers          163577856 bytes

Redo Buffers                6316032 bytes

Database mounted.

Database opened.

SQL>

执行如下升级脚本

 

需要执行如下脚本

SQL> startup upgrade

SQL> spool patch.log

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

SQL> spool 

升级之后

 

TIMESTAMP

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

COMP_TIMESTAMP RUL        2014-08-23 01:20:40

DBUA_TIMESTAMP RUL        VALID       2014-08-23 01:20:40

 

 

TIMESTAMP

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

COMP_TIMESTAMP UPGRD_END  2014-08-23 01:20:41

.

Oracle Database 10.2 Upgrade Status Utility           08-23-2014 01:20:41

.

Component                                Status         Version  HH:MM:SS

Oracle Database Server                    VALID      10.2.0.2.0  00:17:03

JServer JAVA Virtual Machine              VALID      10.2.0.2.0  00:05:46

Oracle XDK                                VALID      10.2.0.2.0  00:01:24

Oracle Database Java Packages             VALID      10.2.0.2.0  00:00:56

Oracle Text                               VALID      10.2.0.2.0  00:00:38

Oracle XML Database                       VALID      10.2.0.2.0  00:01:42

Oracle Data Mining                        VALID      10.2.0.2.0  00:00:32

OLAP Analytic Workspace                   VALID      10.2.0.2.0  00:00:32

OLAP Catalog                              VALID      10.2.0.2.0  00:01:21

Oracle OLAP API                           VALID      10.2.0.2.0  00:01:19

Oracle interMedia                         VALID      10.2.0.2.0  00:06:42

Spatial                                   VALID      10.2.0.2.0  00:01:05

Oracle Expression Filter                  VALID      10.2.0.2.0  00:00:23

Oracle Enterprise Manager                 VALID      10.2.0.2.0  00:01:10

Oracle Rule Manager                       VALID      10.2.0.2.0  00:00:19

.

Total Upgrade Time: 00:40:59

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> SQL> SQL> SQL>

 

 

 

执行重新编译无效包体脚本

 

 

 

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

 

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN  2014-08-23 01:22:37

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  2014-08-23 01:24:39

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

 

 

到此我们已经完成升级从10.2.0.1 到10.2.0.2。

 

 

 

四 安装11.2.0.4 数据库软件

 1  切换到11g 环境变量

darin[/home/oracle]ls

10g.sh  11g.sh  oradiag_oracle  patch.log  trace.log

darin[/home/oracle]source  11g.sh

darin[/home/oracle]more 11g.sh

umask 022

export ORACLE_BASE=/darin

export ORACLE_HOME=/darin/product/11.2.0.4

export ORACLE_SID=ora11g

export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"

export NLS_LANG=AMERICAN_AMERICA.zhs16gbk

export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin

export PATH=$ORACLE_HOME/bin:${PATH}:$HOME/bin

export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin

export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin

export ORACLE_TERM=xterm

export TNS_ADMIN=$ORACLE_HOME/network/admin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib

export CLASSPATH=$ORACLE_HOME/JRE

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib

export THREADS_FLAG=native

export TEMP=/tmp

export TMPDIR=/tmp

export PS1=`hostname`$'[$PWD]'

alias sql='sqlplus "/as sysdba"'

export DISPLAY=192.168.88.1:0.0

darin[/home/oracle]echo $ORACLE_SID

ora11g

darin[/home/oracle]echo $ORACLE_HOME

/darin/product/11.2.0.4

 

 

现在我的环境变量是11g的变量，而且oracle sid 也是更换为ora11g

 

2  执行安装软件工作

darin[/darin/soft/database]echo $ORACLE_HOME

/darin/product/11.2.0.4

darin[/darin/soft/database]ls

install  readme.html  response  rpm  runInstaller  sshsetup  stage  welcome.html

darin[/darin/soft/database]./runInstaller

Starting Oracle Universal Installer...

执行下一步

选择忽略软件更新

选择只安装数据库软件

 
选择语言

默认企业版

这里新的版本的oracle home

继续next

这里我们发现 了oracle10g和11g 对于操作系统的环境的一些改变

例如 ip_local_port_range

10g的要求是10240  650000

 11g的要求是90000  655000

为了安装成功我需要把 参数更改一下

 

更改/etc/sysctl.conf

Sysctl –p 立刻生效一下

 

 下面是sysctl.conf 文件相关解释

# For 11g, Oracle-Validated setting for net.ipv4.ip_local_port_range is 9000 65500

# For 10g, uncomment 'net.ipv4.ip_local_port_range = 1024 65000', comment 'net.ipv4.ip_local_port_range = 9000 65500' entry and re-run sysctl -p

# net.ipv4.ip_local_port_range = 1024 65000

net.ipv4.ip_local_port_range = 9000 65500

#net.ipv4.ip_local_port_range = 1024 65000

 

 

这里是内存不够可以忽略，直接安装

 

安装完成之后 执行root.sh

[root@darin 11.2.0.4]# ./root.sh

Performing root user operation for Oracle 11g

 

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME=  /darin/product/11.2.0.4

 

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

Creating y directory...

   Copying dbhome to y ...

   Copying oraenv to y ...

   Copying coraenv to y ...

 

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 script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

[root@darin 11.2.0.4]#

结束安装。

五 升级到11Gr2

1 切换变量

darin[/home/oracle]source 11g.sh

darin[/home/oracle]echo $ORACLE_HOME

/darin/product/11.2.0.4

darin[/home/oracle]sqlplus

 

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 23 12:30:18 2014

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Enter user-name:

现在已经切换到11g环境变量上了

 

 

2 执行 dbua进行升级

 


这里选择我的10g


这里是一些建议和提示 选择yes
        

这里我选择了 backup database

    这里是由于配置企业管理器 必须要有监听 这里发现提示需要建立个监听

我们手动另开窗口netca 建立个监听

 

 

执行完成

查看11.2.0.4 的监听状态

 

darin[/home/oracle]lsnrctl status

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-AUG-2014 12:39:30

 

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

 

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

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                23-AUG-2014 12:39:10

Uptime                    0 days 0 hr. 0 min. 20 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /darin/product/11.2.0.4/network/admin/listener.ora

Listener Log File         /darin/diag/tnslsnr/darin/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=darin)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "ora10g" has 1 instance(s).

  Instance "ora10g", status READY, has 1 handler(s) for this service...

Service "ora10gXDB" has 1 instance(s).

  Instance "ora10g", status READY, has 1 handler(s) for this service...

Service "ora10g_XPT" has 1 instance(s).

  Instance "ora10g", status READY, has 1 handler(s) for this service...

The command completed successfully

darin[/home/oracle]

 

 

再次重试下一步

 

执行下一步


点击 finish

?查看日志可以看到关键部署

[Thread-60] [ 2014-08-23 12:50:58.569 CST ] [SQLEngine.setSpool:1968]  Setting Spool  = /darin/cfgtoollogs/dbua/ora10g/upgrade1/Oracle_Server.log

[Thread-60] [ 2014-08-23 12:50:58.569 CST ] [SQLEngine.setSpool:1969]  Is spool appendable? --> true

[Thread-60] [ 2014-08-23 12:50:58.570 CST ] [MigrateSidDirective.processDirective:329]  *********** CREATE NEW SID *****

[Thread-60] [ 2014-08-23 12:50:58.570 CST ] [Oratab.getTempFile:804]  Temp oratab: /tmp/oratab

[Thread-60] [ 2014-08-23 12:50:58.572 CST ] [MigrateSidDirective.processDirective:352]  Add new  sid to registry

[Thread-60] [ 2014-08-23 12:50:58.573 CST ] [MigrateSidDirective.processDirective:354]  Add SID Operation Complete

[Thread-60] [ 2014-08-23 12:50:58.573 CST ] [Database.applyMigInitParameters:2901]  Applying changes to Init.ora

[Thread-60] [ 2014-08-23 12:50:58.574 CST ] [Database.applyMigInitParameters:2845]  Deleting background_dump_dest

[Thread-60] [ 2014-08-23 12:50:58.574 CST ] [Database.applyMigInitParameters:2845]  Deleting user_dump_dest

[Thread-60] [ 2014-08-23 12:50:58.575 CST ] [MigrateSidDirective.processDirective:365]  Mig Init Params Applied

[Thread-60] [ 2014-08-23 12:50:58.576 CST ] [MigrateSidDirective.processDirective:376]  New PasswordFile: /darin/product/11.2.0.4/dbs/orapwora10g for sid: ora10g

[Thread-60] [ 2014-08-23 12:50:58.576 CST ] [MigrateSidDirective.processDirective:379]  Copy password file

[Thread-60] [ 2014-08-23 12:50:58.577 CST ] [OsUtilsBase.copyFile:1565]  OsUtilsBase.copyFile:

[Thread-60] [ 2014-08-23 12:50:58.618 CST ] [OsUtilsBase.copyFile:1613]  **write of file at destination complet

 

到这里数据库已经顺利升级完成，大约估计花费3-4小时时间，当然我的虚拟机配置很差的。

 

查看dbua 日志

COMMIT;

 

Commit complete.

 

 

--

-- Drop the temp HELP view in a PL/SQL block so that the unnecessary error

-- ORA-0942 can be suppressed when view does not exist. (7676775)

--

BEGIN

2    EXECUTE IMMEDIATE 'DROP VIEW SYSTEM.HELP_TEMP_VIEW';

3    EXCEPTION WHEN OTHERS THEN

4    IF (SQLCODE = -942) THEN

5       NULL;

6    ELSE

7       RAISE;

8    END IF;

9  END;

10  /

 

PL/SQL procedure successfully completed.

这是最后升级汇总表

 

再次在操作系统查看

 

darin[/home/oracle]sql

 

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 23 18:09:07 2014

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> select status from v$instance;

 

STATUS

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

OPEN

3 至此oracle database 10.2.0.1 到11.2.0.4升级完成。

   这里也证实了 oracle10g 升级到11Gr2 ，10g的基本版本最低位10.2.0.2.但是官方文档上说的最低的是10.2.0.4,这个有待讨论吧。