Oracle資料庫的升級(10gR2至11gR2)

Oracle資料庫的升級(10gR2至11gR2)

今天對Oracle資料庫升級做了大致瞭解，還是很多東西值得考慮的。

首先，升級前的準備就很多工作需要做：

1. 測試和準備， 主要是應用的做壓力測試。因為版本升級，最佳化器可能使SQL效能回退（選擇效能更差的執行計畫），可以結合database replay做壓力測試，SPA類比環境變化的影響，以及SPM對執行計畫做固定。

2. 升級的方法， 主要是圖形介面DBUA（新目錄的路徑）來完成，相對簡單；手工升級（手工執行指令碼、DBUA只不過將這些手工動作代替而已、新目錄路徑）；EXPDP（源庫路徑）/IMPDP（新庫路徑）等等。

其中，以下幾種方法可以支援滾動升級：

可以看得出，升級的方案多樣，根據不同的環境而定。

3. 升級前，務必將新庫路徑下$ORACLE_HOME/rdbms/admin/utlu112i.sql指令碼拷至源庫執行，主要是檢查當前環境（如資料表空間大小，隱含參數，警告或建議）是否合適做升級，我們可以根據警告和建議做相應調整。

以下粘出單機資料庫的升級實驗步驟，供參數，由10.2.0.5升級至11.2.0.3.

時間允許的話,將做RAC的升級並更新文章

======================================================================================================

一、源庫--備份

run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
backup AS BACKUPSET full tag 'db_full' format '/backup/db_%d_%T_%s_%U.bak' database include current controlfile;
sql 'alter system archive log current';
release channel ch4;
release channel ch3;
release channel ch2;
release channel ch1;
}

SQL> archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /arch/ora10g
Oldest online log sequence    49
Next log sequence to archive  51
Current log sequence        51

二、源庫 -- 運行指令碼utlu112i.sql,做升級前檢查
[oracle@tivoli02 backup]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 26 20:27:42 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool /tmp/upgrade_info.log
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql

列印內容如下:
================
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 01-26-2015 20:34:56
Script Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:    ORA10G
--> version:    10.2.0.5.0
--> compatible:    10.2.0.5.0
--> 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: 643 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 433 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]
**********************************************************************
--> sql_trace    10.2    DEPRECATED
--> 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
--> EM Repository  [upgrade]  VALID
--> Oracle XML Database  [upgrade]  VALID
--> Oracle Java Packages  [upgrade]  VALID
--> Oracle interMedia  [upgrade]  VALID
--> Expression Filter  [upgrade]  VALID
--> Rule Manager  [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 10.2.0.5.0 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 PUBLIC has 234 INVALID objects.
.... USER TEST has 3 INVALID objects.
.... USER SYSTEM has 2 INVALID objects.
.... USER SYSMAN has 210 INVALID objects.
.... USER SYS has 198 INVALID objects.
.... USER WMSYS has 6 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:
 PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
.
**********************************************************************
Recommendations
**********************************************************************
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;

**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.

To view existing hidden parameters execute the following command
while connected AS SYSDBA:

    SELECT name,description from SYS.V$PARAMETER WHERE name
 LIKE '\_%' ESCAPE '\'

Changes will need to be made in the init.ora or spfile.

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
  Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'

  Trace Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

**********************************************************************
Oracle recommends examining audit tables AUD$ and FGA_LOG$ before
upgrading the database.

This database has 0 rows in AUD$ and 23 rows in FGA_LOG$ that
will be updated during the database upgrade from 10.2.0.5.0.

During this upgrade, null DBIDs in AUD$ and FGA_LOG$ will be updated
with non-null values.

The upgrade downtime could be affected if there are many rows to update.
If downtime is a concern, the audit update could be done manually prior
to upgrading the database.

Please refer to My Oracle Support Note 1329590.1 titled "How to
Pre-Process SYS.AUD$ Records Pre-Upgrade From 10.1 or Later to 11.2".

**********************************************************************

<<<<<<<<<根據輸出結果,做相對就的調整...此處省略

 

三、upgrading
[oracle@tivoli02 ~]$ env | grep ORA
ORACLE_SID=ora10g
ORACLE_BASE=/u01/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@tivoli02 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 26-JAN-2015 20:41:52

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

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/tivoli02/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tivoli02)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tivoli02)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                    LISTENER
Version                  TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                26-JAN-2015 20:41:52
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level              off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File  /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File        /u01/app/oracle/diag/tnslsnr/tivoli02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tivoli02)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@tivoli02 ~]$ which lsnrctl
/u01/app/oracle/product/11.2.0/db_1/bin/lsnrctl

sqlpus / as sysdba
startup upgrade
SQL> SPOOL upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql  ---about 30 minutes
SQL> STARTUP
SQL> @?/rdbms/admin/utlu112s.sql
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> SELECT count(*) FROM dba_invalid_objects;
SQL> SELECT distinct object_name FROM dba_invalid_objects;
<<<<<<如果有失效對象，需要手工重編譯

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

Oracle效能最佳化 之 共用池