使用DBUA升级数据库从9i到10G.doc

使用DBUA升级数据库从9i到10GOS: HP-UX 11.23# uname -aHP-UX RX3600-3 B.11.23 U ia64 1955595348 unlimited-user licenseORACLE 9.2.0.6ORACLE 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=1073741824kctune -h maxswapchunks=16384kctune -h maxuprc=3687kctune -h msgmap=4098kctune -h msgmni=4096kctune -h msgseg=32767kctune -h msgtql=4096kctune -h semmap=4098kctune -h shmmni=512kctune -h vps_ceiling=642.确认/etc/oratab文件里含有要升级的数据库的条目，在此文件的配置里，要使用9i的环境变量，dbua会自动更新该文件，如下：*:/oracle/oracle/product/9.2.0:Ngxsi:/oracle/oracle/product/9.2.0:Ndbua程序执行过程中即更新该文件：*:/oracle/oracle/product/9.2.0:Ngxsi:/oracle/oracle/product/10.2.0:N3.设置环境变量以准备执行dbua：#su oracle因为要使用10G的DBUA，所以要使ORACLE_HOME变量的为10G的目录：export ORACLE_BASE=/oracle/oracleexport ORACLE_HOME=/oracle/oracle/product/10.2.0/export PATH=$ORACLE_HOME/bin:$PATHexport 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.xmlis not a valid xml fileand 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 databaseif the SYS users temporary tablespace in the Oracle 9.2 database is dictionary managedrather 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-TEMPSQL desc dba_temp_files;NameNull?Type- - -FILE_NAMEVARCHAR2(513)FILE_IDNUMBERTABLESPACE_NAMENOT NULL VARCHAR2(30)SQL select file_name from dba_temp_files;no rows selectedSQL 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-bash-3.2$ env | grep ORAORACLE_SID=gxsiORACLE_BASE=/oracle/oracleORACLE_HOME=/oracle/oracle/product/10.2.0/-bash-3.2$ sqlplus /as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Sun May 25 21:53:09 2008Copyright (c) 1982, 2005, Oracle.All rights reserved.Connected to an idle instance.SQL startupORA-32004: obsolete and/or deprecated parameter(s) specifiedORACLE instance started.Total System Global Area 1010827264 bytesFixed Size1998744 bytesVariable Size469762152 bytesDatabase Buffers536870912 bytesRedo Buffers2195456 bytesORA-01102: cannot mount database in EXCLUSIVE modeSQLexitMore $ORACLE_BASE/admin/gxsi/bdump/Alert_gxsi.logSun May 25 21:51:06 2008sculkget: failed to lock /oracle/oracle/product/10.2.0/dbs/lkGXSI exclusivesculkget: lock held by PID: 13995-bash-3.2$ cd $ORACLE_HOME/dbs-bash-3.2$ lshc_gxsi.datinitdw.oralkGXSIinit.orainitgxsi.oraorapwgxsi-bash-3.2$ fuser lk*lkGXSI: 14023o 14001o 13997o 14013o 14005o 13995o 13989o 14019o 13999o 13991o 13993o 14007o 14003o 14009o 15844o-bash-3.2$ fuser -k lk*lkGXSI: 14023o 14001o 13997o 14013o 14005o 13995o 13989o 14019o 13999o 13991o13993o 14007o 14003o 14009o 15844o-bash-3.2$ fuser lk*lkGXSI:问题2：ORA-32004查看$ORACLE_HOME/admin/gxsi/alert_gxsi.logObsolete system parameters with specified values:hash_join_enabledEnd of obsolete system parameter listingmore $ORACLE_HOME/dbs/initgxsi.orabackground_dump_dest=/oracle/oracle/admin/gxsi/bdumpcompatible=9.2.0.0.0control_files=/oradata/gxsi/ora_control01, /oradata/gxsi/ora_control02, /oradata/gxsi/ora_control03core_dump_dest=/oracle/oracle/admin/gxsi/cdumpdb_block_size=8192db_cache_size=536870912db_domain=db_file_multiblock_read_count=16db_name=gxsifast_start_mttr_target=300#hash_join_enabled=TRUEinstance_name=gxsijava_pool_size=0large_pool_size=16777216open_cursors=300pga_aggregate_target=471859200processes=200query_rewrite_enabled=FALSEremote_login_passwordfile=EXCLUSIVEsga_max_size=1008159928shared_pool_size=419430400sort_area_size=524288star_transformation_enabled=FALSEtimed_statistics=FALSEundo_management=AUTOundo_retention=10800undo_tablespace=UNDOTBS1user_dump_dest=/oracle/oracle/admin/gxsi/udumpjob_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 hasbeen restored.It may be necessary to add files to thesetablespaces.That can be done using the SQL statement:ALTER TABLESPACE ADD TEMPFILEAlternatively, if these temporary tablespaces are no longerneeded, 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 =(ADDR