10g升级

在9i，无论升级/降级 数据库都是startup migrate
10g后增加了upgrade参数，升级可直接用startup upgrade，降级仍是startup migrate

 

单机升级数据库先决条件：
      
表空间：
       1、确保system表空间至少有10M空间可用；
         
col "表空间名" for a20
             col "使用百分比" for a10
             SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
                    D.TOT_GROOTTE_MB "表空间大小(M)",
                    D.TOT_GROOTTE_MB-F.TOTAL_BYTES "已使用空间(M)",
                    TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99') "使用百分比",
                    F.TOTAL_BYTES "空闲空间(M)"
             FROM (SELECT TABLESPACE_NAME,
                          ROUND(SUM(BYTES)/1024/1024) TOTAL_BYTES,
                          ROUND(MAX(BYTES)/(1024*1024),2) MAX_BYTES
                   FROM SYS.DBA_FREE_SPACE
                   GROUP BY TABLESPACE_NAME) F,
                  (SELECT DD.TABLESPACE_NAME,
                          ROUND(SUM(BYTES)/1024/1024) TOT_GROOTTE_MB
                   FROM SYS.DBA_DATA_FILES DD
                   GROUP BY DD.TABLESPACE_NAME) D
             WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME
             ORDER BY 2 DESC;

参数：
1、确保参数SHARED_POOL_SIZE 和 JAVA_POOL_SIZE至少为150M大小，保证catupgrd.sql(10g),(9i用catpatch.sql）升级脚本正常运行；
          SQL> SHOW PARAMETER SHARED_POOL_SIZE
          SQL> SHOW PARAMETER JAVA_POOL_SIZE
          SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='200M' SCOPE=spfile;
          SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='200M' SCOPE=spfile;
为加快速度，可临时调大这２个参数

1.升级前：SQL> shutdown immediate 
停止所有的进程
Stop all listener and other processes running in the Oracle home directory

2.备份软件  数据库
tar -cvf $ORACLE_BASE /orabackup/
cp *.dbf con*.ora redo*.log /orabackup/

3.升级软件
./runIstanller   --->oracle
root.sh---->root

4.更新数据字典
sql> startup upgrade
sql> spool patch.log
SQL> @?/rdbms/admin/catupgrd.sql
sql> spool off

 

5.重编译失效对象：
sql>shutdown immediate
sql>startup
SQL>@?/rdbms/admin/utlrp.sql

 

6.升级后的检测
select comp_name,version,stutas from sys.dba_registry;

检查组件的升级情况
SQL>select * from UTL_RECOMP_ERRORS;

7.Restart the database:
SQL> SHUTDOWN
SQL> STARTUP

8.If you are using the Oracle Recovery Manager catalog, enter the following command:
$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;

 

回退：
SQL> STARTUP DOWNGRADE

SQL> SPOOL downgrade.log

SQL> @catdwgrd.sql（10.2.10运行的是这个，而10.1降级用的是d92000.sql，即dold_release.sql）

Sql>spool off

Sql>shutdown immediate