（轉）使用DBUA從9i到10G升級資料庫

OS: HP-UX 11.23

# uname -a
HP-UX RX3600-3 B.11.23 U ia64 1955595348 unlimited-user license

ORACLE 9.2.0.6

ORACLE 10.2.0.1

注: 不會在這裡貼圖, 所以把圖全刪了, 改用文字描述.

1. 使用和9i相同的使用者來安裝Oracle10G，安裝時選擇不同的ORACLE_HOME目錄，並且取消建庫選擇項。

在安裝Oracle10G的時，安裝程式檢查出HP-UX有不符合的條件：

Checking for PHSS_33278; found Not found. Failed <<<<

Checking for PHSS_33279; found Not found. Failed <<<<

Checking for PHSS_33277; found Not found. Failed <<<<

Checking for PHSS_33279; found Not found. Failed <<<<

Checking for maxssiz_64bit=1073741824; found maxssiz_64bit=268435456. Failed <<<<

Checking for maxswapchunks=16384; found no entry. Failed <<<<

Checking for maxuprc=3687; found maxuprc=256. Failed <<<<

Checking for msgmap=4098; found msgmap=514. Failed <<<<

Checking for msgmni=4096; found msgmni=512. Failed <<<<

Checking for msgseg=32767; found msgseg=8192. Failed <<<<

Checking for msgtql=4096; found msgtql=1024. Failed <<<<

Checking for semmap=4098; found no entry. Failed <<<<

Checking for shmmni=512; found shmmni=400. Failed <<<<

Checking for vps_ceiling=64; found vps_ceiling=16. Failed <<<<

Check complete. The overall result of this check is: Failed <<<<

Problem: The kernel parameters do not meet the minimum requirements (see above).

Recommendation: Perform. operating system specific instructions to update the kernel parameters.

上面提示的補丁沒理，只修改了要求的系統引數(不加引數則表示引數立刻生效)：

kctune -h maxssiz_64bit="1073741824"

kctune -h maxswapchunks=16384"

kctune -h maxuprc="3687"

kctune -h msgmap="4098"

kctune -h msgmni="4096"

kctune -h msgseg="32767"

kctune -h msgtql="4096"

kctune -h semmap="4098"

kctune -h shmmni="512"

kctune -h vps_ceiling="64"

2. 確認/etc/oratab檔案裡含有要升級的資料庫的條目，在此檔案的配置裡，要使用9i的環境變數，dbua會自動更新該檔案，如下：

*:/oracle/oracle/product/9.2.0:N

gxsi:/oracle/oracle/product/9.2.0:N

dbua程式執行過程中即更新該檔案：

*:/oracle/oracle/product/9.2.0:N

gxsi:/oracle/oracle/product/10.2.0:N

3. 設定環境變數以準備執行dbua ：

#su – oracle

因為要使用10G的DBUA，所以要使ORACLE_HOME變數的為10G的目錄：

export ORACLE_BASE=/oracle/oracle

export ORACLE_HOME=/oracle/oracle/product/10.2.0/

export PATH=$ORACLE_HOME/bin:$PATH

export DISPLAY=10.154.249.5:1.0

可以使用xclock來測試。

4. 執行DBUA啟動升級過程

啟動DBUA，出現DBUA的安裝介面，是一些關於DBUA升級的說明.

5. 點選next出現如下圖，選擇要升級的資料庫。如果上面第2步沒有做，則在下圖的 Available Database選擇框裡無法出現內容。

注:此處畫面顯示的是當前Oracle裡所包含的庫列表.

6. 在上圖中選擇好要升級的資料庫後，點選next下一步.

在此處出現了一個錯誤：

問題1：

For input string: ""

Upgrade configuration file

/oracle/SND/102_64/cfgtoollogs/dbua/SND/upgrade2/upgrade.xml is not a valid xml file

在另一視窗檢視該檔案，發現該檔案正是DBUA升級程式自身產生的，每執行一次則產生一個upgrade*目錄。

在網上查詢此問題，發現如下原因：

While trying to run the DBUA to upgrade an Oracle 9.2 database to 10.2 I get the error: For input string: "" Upgrade configuration file /oracle/SND/102_64/cfgtoollogs/dbua/SND/upgrade2/upgrade.xml is not a valid xml file   and the DBUA does not run. Please help as this is my first Oracle upgrade ever!     There have been reported issues when using the DBUA to upgrade to a 10.2 database if the SYS user's temporary tablespace in the Oracle 9.2 database is dictionary managed rather than locally managed. Check to see if this is the case by first querying the DBA_USERS view to determine which tablespace has been defined as temporary for SYS:   select temporary_tablespace from dba_users where username='SYS';   Then check to see whether this tablespace is locally or dictionary managed: select tablespace_name, extent_management from dba_tablespaces;   If the temporary tablespace defined for the SYS user is a dictionary managed tablespace, try creating another locally managed tablespace and assign it to be the temporary tablespace for SYS as follows: alter user sys temporary tablespace ;   Try the upgrade again using the DBUA.

根據以上描述使用如下方法解決：

根據以上說明，查詢原9i資料庫後，發現TEMP表空間無資料檔案，要修改sys和system使用者使用可本地使用的臨時表空間。 SQL> select temporary_tablespace from dba_users where username='SYS';   TEMPORARY_TABLESPACE ------------------------------------------------------------ TEMP   SQL> desc dba_temp_files; Name Null? Type ----------------------------------------- -------- ---------------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME NOT NULL VARCHAR2(30) ……   SQL> select file_name from dba_temp_files; no rows selected   SQL> create temporary tablespace temp2 tempfile '/oradata/gxsi/temp02.dbf' size 100M; Tablespace created.   SQL> alter user sys temporary tablespace temp2; User altered.   SQL> alter user system temporary tablespace temp2; User altered.

7. 然後在第5步裡點下一步，沒有再出現錯誤，DBUA繼續執行.

接下來的幾個配置畫面裡有:

SYSAUX表空間的配置;

Recompile invalid objects at the end of upgrade;

may need to backup database;

configure the database with entherprise manager;

must specify passwords for the user accounts;

Database upgrade Summary;

開始執行升級,等待中...

在更新程式升級"Upgrading Oracle Server"序升時出現錯誤:

ORA-25138: HASH_JOIN_ENABLED initialization parameter has been made obsolete.

此錯誤是在執行”Upgrading Oracle Server”步驟時出現的，因為上不了網，沒法檢視此錯誤的原因，點Ignore繼續。

查到錯誤原因了，因為在9i裡的部分引數，在10G裡已經不適用，所以系統提示此錯誤，在這裡是“HASH_JOIN_ENABLED”引數不適用，此問題待升級完成後用SQLPLUS來修改，如問題2 。

下面的2個錯誤提示也均是因為“HASH_JOIN_ENABLED”引數所致。

問題1：ORA-01102

問題2：ORA-32004

檢視$ORACLE_HOME/admin/gxsi/alert_gxsi.log

Obsolete system parameters with specified values:

hash_join_enabled

End of obsolete system parameter listing

more $ORACLE_HOME/dbs/initgxsi.ora

background_dump_dest=/oracle/oracle/admin/gxsi/bdump

compatible=9.2.0.0.0

control_files=/oradata/gxsi/ora_control01, /oradata/gxsi/ora_control02, /oradata/gxsi/ora_control03

core_dump_dest=/oracle/oracle/admin/gxsi/cdump

db_block_size=8192

db_cache_size=536870912

db_domain=""

db_file_multiblock_read_count=16

db_name=gxsi

fast_start_mttr_target=300

#hash_join_enabled=TRUE

instance_name=gxsi

java_pool_size=0

large_pool_size=16777216

open_cursors=300

pga_aggregate_target=471859200

processes=200

query_rewrite_enabled=FALSE

remote_login_passwordfile=EXCLUSIVE

sga_max_size=1008159928

shared_pool_size=419430400

sort_area_size=524288

star_transformation_enabled=FALSE

timed_statistics=FALSE

undo_management=AUTO

undo_retention=10800

undo_tablespace=UNDOTBS1

user_dump_dest=/oracle/oracle/admin/gxsi/udump

job_queue_processes=1

因為資料庫啟動用的是pfile，在庫裡無法使用ALTER SYSTEM RESET log_archive_start SCOPE=SPFILE SID='*'; 來修改，所以直接vi編輯initgxsi.ora檔案，將其中的#hash_join_enabled=TRUE引數註釋掉，然後再將資料庫shutdown immediate再startup後，問題解決！

問題3：temp表空間無資料檔案

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

WARNING: The following temporary tablespaces contain no files.

This condition can occur when a backup controlfile has

been restored. It may be necessary to add files to these

tablespaces. That can be done using the SQL statement:

ALTER TABLESPACE ADD TEMPFILE

Alternatively, if these temporary tablespaces are no longer

needed, then they can be dropped.

Empty temporary tablespace: TEMP

(SQL> drop tablespace temp後，問題解決)

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

Database Characterset is US7ASCII

問題4：listener.ora和tnsnames.ora

升級完成後，系統沒有listener.ora和tnsnames.ora，需要使用netca來建立，建立好後需要手動編輯listener.ora來新增gxsi監聽：

-bash-3.2$ more tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/oracle/product/10.2.0/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

GXSI =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.154.249.23)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = gxsi)

)

)

 

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

 

-bash-3.2$ more listener.ora

 

# listener.ora Network Configuration File: /oracle/oracle/product/10.2.0/network/admin/listener.ora

# Generated by Oracle configuration tools.

 

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /oracle/oracle/product/10.2.0)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = gxsi)

(ORACLE_HOME = /oracle/oracle/product/10.2.0)

(SID_NAME = gxsi)

)

)

 

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.154.249.23)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

 

ORA-32004: obsolete and/or deprecated parameter(s) specified

錯誤2: Performing Post Upgrade

ORA-32003: error occured processing parameter "hash_join_enabled"

ORA-01078: failure in processing system parameters

錯誤1: Performing Post Upgrade