升级数据库

升级数据库 停止ORACLE 9i上面的所有数据库和监听等数据库服务 SQL >conn /as sysdba SQL >shutdown immediate SQL >exit [oracle@xcj1 ~]$ lsnrctl stop[@more@]1、停止ORACLE 9i上面的所有数据库和监听等数据库服务 SQL >conn /as sysdba SQL >shutdown immediate SQL >exit [oracle@xcj1 ~]$ lsnrctl stop 2、修改环境参数 su - oracle export ORACLE_HOME=/u01/app/oracle/ora10g(原来是:=/u01/app/oracle/ora92) export ORACLE_SID=xcj(原来是PROD) 3、进入ORACLE10g的安装目录 ./runInstaller 4、不选中升级现有数据库 5、不创建新的数据库 6、在root下运行root.sh(覆盖目录，选 Y) 7、打开数据库，创建pfile,拷贝10g目录下的utlu101i.sql到9i下面运行 SQL >conn /as sysdba SQL >spool check.lst SQL >@utlu101i.sql SQL >spool off 8、关闭数据库 SQL >conn /as sysdba SQL >shutdown immediate 9、拷贝pfile和password文件到10g的$ORACLE_HOME/dbs 10、修改环境参数 [oracle@xcj1 ~]$ vi .bash_profile ORACLE_HOME=/u01/app/oracle/ora10g [oracle@xcj1 ~]$ source .bash_profile [oracle@xcj1 ~]$ exit 11、修改pfile 修改： shared_pool_size=150944944 java_pool_size=50331648 compatible='10.1.0.3.0' 删除:hash_join_enabled 12、以startup upgrade打开数据库 [oracle@xcj1 dbs]$ sqlplus /nolog SQL*Plus: Release 10.1.0.3.0 - Production on Wed Dec 19 10:04:07 2007 Copyright (c) 1982, 2004, Oracle. All rights reserved. SQL> conn /as sysdba Connected to an idle instance. SQL> startup upgrade ORACLE instance started. Total System Global Area 507510784 bytes Fixed Size 779836 bytes Variable Size 254810564 bytes Database Buffers 251658240 bytes Redo Buffers 262144 bytes Database mounted. Database opened. 13、创建SYSAUX表空间 SQL> CREATE TABLESPACE sysaux DATAFILE '/u01/app/oracle/oradata/prod/sysaux.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; Tablespace created. 14、运行升级脚本(如果在运行脚本的过程中遇到了任何出错消息，那么需要纠正出现的错误后重新运行相应的脚本。升级状态程序utlu101s.sql会给出修复失败的组件所需运行的脚本名称) SQL> spool ora10upgrade.lst SQL> @?/rdbms/admin/u0902000.sql SQL> spool off 15、运行脚本查询升级后的状态(从10.1.0.5上面拷贝过来的) SQL> @utlu101s.sql TEXT PL/SQL procedure successfully completed. Oracle Database 10.1 Upgrade Status Tool 19-DEC-2007 11:43:06 --&gt Oracle Database Catalog Views Normal successful completion --&gt Oracle Database Packages and Types Normal successful completion --&gt JServer JAVA Virtual Machine Normal successful completion --&gt Oracle XDK Normal successful completion --&gt Oracle Database Java Packages Normal successful completion --&gt Oracle XML Database Normal successful completion --&gt Oracle Workspace Manager Normal successful completion --&gt Oracle Data Mining Normal successful completion --&gt Oracle interMedia Normal successful completion --&gt Spatial Normal successful completion --&gt Oracle Text Normal successful completion --&gt Oracle Ultra Search Normal successful completion No problems detected during upgrade PL/SQL procedure successfully completed. 16、关闭实例重新启动 SQL >conn /as sysdba SQL >shutdown immediate SQL >startup 17、运行脚本重新编译失效的对象 SQL> @/u01/app/oracle/ora10g/rdbms/admin/utlrp.sql TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2007-12-19 11:48:38 PL/SQL procedure successfully completed. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2007-12-19 11:51:10 PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. 18、查询是否还有失效的对象 SQL> select count(*) from dba_objects where status='INVALID'; 2 3 COUNT(*) ---------- 0 19、查询数据文件、重做日志文件是否正确 SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/prod/xdb01.dbf /u01/app/oracle/oradata/prod/users01.dbf /u01/app/oracle/oradata/prod/undotbs01.dbf /u01/app/oracle/oradata/prod/tools01.dbf /u01/app/oracle/oradata/prod/system01.dbf /u01/app/oracle/oradata/prod/odm01.dbf /u01/app/oracle/oradata/prod/indx01.dbf /u01/app/oracle/oradata/prod/example01.dbf /u01/app/oracle/oradata/prod/drsys01.dbf /u01/app/oracle/oradata/prod/sysaux.dbf 10 rows selected. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME -------------------------------- ------------- ------------ ---------- ---------- ---------- ---------- ---------- ------ 1 1 14 104857600 1 NO INACTIVE 488999 19-DEC-07 2 1 15 104857600 1 NO INACTIVE 503529 19-DEC-07 3 1 16 104857600 1 NO CURRENT 530407 19-DEC-07 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_REC ---------- -------------- ---------------------------------------------------------------------------------------------- 3 ONLINE /u01/app/oracle/oradata/prod/redo03.log NO 2 ONLINE /u01/app/oracle/oradata/prod/redo02.log NO 1 ONLINE /u01/app/oracle/oradata/prod/redo01.log NO 20、创建ORACLE10G的监听和TNSNAMES 21、备份数据库

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9821375/viewspace-996448/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9821375/viewspace-996448/