Upgrade Oracle Database from 10.2.0.1 to 11.2.0.4

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

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

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? --&gt 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

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,這個有待討論吧。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26175573/viewspace-1256574/，如需轉載，請註明出處，否則將追究法律責任。

Upgrade Oracle Database from 10.2.0.1 to 11.2.0.4

一 確定升級路線

二 環境

1 資料庫版本資訊

2 oracle 10g環境變數

3 oracle 11g 環境變數

三 首先升級10.2.0.1到10.2.0.2

四 安裝11.2.0.4 資料庫軟體

1 切換到11g 環境變數

2 執行安裝軟體工作

五 升級到11Gr2

1 切換變數

2 執行 dbua進行升級

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

相關文章

一確定升級路線

二環境

三首先升級10.2.0.1到10.2.0.2

四安裝11.2.0.4 資料庫軟體

五升級到11Gr2