1.在9ihome下執行檢查工具
SQL> spool aa.log
SQL> @/opt/oracle/product/11g/rdbms/admin/utlu112i.sql

-bash-3.2$ cat aa.log
SQL> @/opt/oracle/product/11g/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 03-13-2013 14:32:24
Script. Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--&gt name:          DB01
--&gt version:       9.2.0.8.0
--&gt compatible:    9.2.0.0.0
--&gt blocksize:     8192
--&gt timezone file: V1
.
**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
--&gt The existing log files are adequate. No changes are required.
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--&gt SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 442 MB
--&gt UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--&gt TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
--&gt CWMLITE tablespace is adequate for the upgrade.
.... minimum required size: 16 MB
--&gt DRSYS tablespace is adequate for the upgrade.
.... minimum required size: 29 MB
--&gt EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 24 MB
--&gt ODM tablespace is adequate for the upgrade.
.... minimum required size: 7 MB
--&gt XDB tablespace is adequate for the upgrade.
.... minimum required size: 57 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 32-bit database.
**********************************************************************
--&gt If Target Oracle is 32-Bit, refer here for Update Parameters:
WARNING: --&gt "compatible" must be set to at least 10.1.0
WARNING: --&gt "shared_pool_size" needs to be increased to at least 295 MB
WARNING: --&gt "db_cache_size" needs to be increased to at least 50331648 bytes
.

--&gt If Target Oracle is 64-Bit, refer here for Update Parameters:
WARNING: --&gt "compatible" must be set to at least 10.1.0
WARNING: --&gt "shared_pool_size" needs to be increased to at least 531 MB
WARNING: --&gt "java_pool_size" needs to be increased to at least 128 MB
WARNING: --&gt "db_cache_size" needs to be increased to at least 50331648 bytes
.
**********************************************************************
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]
**********************************************************************
--&gt hash_join_enabled            10.1       OBSOLETE
--&gt background_dump_dest         11.1       DEPRECATED   replaced by "diagnostic_dest"
--&gt user_dump_dest               11.1       DEPRECATED   replaced by "diagnostic_dest"
.

**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--&gt Oracle Catalog Views         [upgrade] VALID
--&gt Oracle Packages and Types    [upgrade] VALID
--&gt JServer JAVA Virtual Machine [upgrade] VALID
--&gt Oracle XDK for Java          [upgrade] VALID
--&gt Oracle Workspace Manager     [upgrade] VALID
--&gt OLAP Analytic Workspace      [upgrade] LOADED
--&gt OLAP Catalog                 [upgrade] VALID
--&gt Oracle Text                  [upgrade] VALID
--&gt Oracle XML Database          [upgrade] VALID
--&gt Oracle Java Packages         [upgrade] VALID
--&gt Oracle interMedia            [upgrade] VALID
--&gt Spatial                      [upgrade] LOADED
--&gt Data Mining                  [upgrade] LOADED
--&gt Oracle Ultra Search          [upgrade] VALID
--&gt Oracle OLAP API              [upgrade] LOADED
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --&gt The database has not been patched to release 9.2.0.8.0.
... Run catpatch.sql prior to upgrading.
WARNING: --&gt Passwords exist in some database links.
.... Passwords will be encrypted during the upgrade.
.... Downgrade of database links with passwords is not supported.
WARNING: --&gt Deprecated CONNECT role granted to some user/roles.
.... CONNECT role after upgrade has only CREATE SESSION privilege.
WARNING: --&gt 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 9.2.0.8.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --&gt 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 EPT has 6 INVALID objects.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following commands
while connected as SYSDBA:
    EXECUTE dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER'
             ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
             ,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
             ,cascade=>TRUE);
    EXECUTE dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER'
             ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
             ,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
             ,cascade=>TRUE);
    EXECUTE dbms_stats.gather_schema_stats('ORDPLUGINS',options=>'GATHER'
             ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
             ,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
             ,cascade=>TRUE);
    EXECUTE dbms_stats.gather_schema_stats('ORDSYS',options=>'GATHER'
             ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
             ,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
             ,cascade=>TRUE);
    EXECUTE dbms_stats.gather_schema_stats('WKSYS',options=>'GATHER'
             ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
             ,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
             ,cascade=>TRUE);
    EXECUTE dbms_stats.gather_schema_stats('XDB',options=>'GATHER'
             ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
             ,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
             ,cascade=>TRUE);
    EXECUTE dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER'
             ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
             ,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
             ,cascade=>TRUE);
    EXECUTE dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER'
             ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
             ,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
             ,cascade=>TRUE);
    EXECUTE dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER'
             ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
             ,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
             ,cascade=>TRUE);
    EXECUTE dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER'
             ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
             ,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
             ,cascade=>TRUE);
    EXECUTE dbms_stats.gather_schema_stats('SYS',options=>'GATHER'
             ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
             ,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
             ,cascade=>TRUE);

**********************************************************************
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.

**********************************************************************
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 11.2 environment]
**********************************************************************
--&gt New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
.
SQL> exit
-bash-3.2$

2.根據檢查結果進行相應修改

a.修改系統引數
SQL> alter system set compatible='10.0.1' scope=spfile;

System altered.
SQL> alter system set sga_max_size=1600m scope=spfile;

System altered.

SQL> alter system set db_cache_size=800m scope=spfile;

System altered.

SQL> alter system set shared_pool_size=500m scope=spfile;

System altered.

b.對賦予了connect角色的使用者賦予許可權
grant CREATE VIEW          to ept;
grant CREATE TABLE         to ept;
grant ALTER SESSION        to ept;
grant CREATE CLUSTER       to ept;
grant CREATE SESSION       to ept;
grant CREATE SYNONYM       to ept;
grant CREATE SEQUENCE      to ept;
grant CREATE DATABASE LINK to ept;

d.進行9208的補丁程式
SQL> spool cc.log
SQL> @/opt/oracle/product/9ir2/rdbms/admin/catpatch.sql

d.生成引數檔案,關閉資料庫
SQL>create pfile='/opt/oracle/initDB01.ora' from spfile;

去除引數
hash_join_enabled

3.從9ihome複製密碼檔案到11ghome，修改oratab
-bash-3.2$ cat oratab
#

# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form.:
# $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
# *:/opt/oracle/product/9ir2:N
*:/opt/oracle/product/9ir2:N
#DB01:/opt/oracle/product/9ir2:N
DB01:/opt/oracle/product/11g:N

4.在11g下啟動資料庫，執行catupgrd.sql
-bash-3.2$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 18 09:45:32 2013

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup upgrade pfile='/opt/oracle/initDB01.ora'
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORACLE instance started.

Total System Global Area 1673965568 bytes
Fixed Size                  1345352 bytes
Variable Size             822085816 bytes
Database Buffers          838860800 bytes
Redo Buffers               11673600 bytes
Database mounted.
Database opened.
SQL>

CREATE TABLESPACE sysaux DATAFILE '/opt/oracle/oradata/DB01/sysaux01.dbf'
         SIZE 500M REUSE
         EXTENT MANAGEMENT LOCAL
         SEGMENT SPACE MANAGEMENT AUTO
         ONLINE;
spool upgrade.sql
@/opt/oracle/product/11g/rdbms/admin/catupgrd.sql

5.修改引數檔案，丟棄兩個引數，重新啟動資料庫，執行指令碼utlu112s.sql
BACKGROUND_DUMP_DEST
USER_DUMP_DEST

SQL>startup pfile='/opt/oracle/initDB01.ora'
spool ss.log
SQL> @/opt/oracle/product/11g/rdbms/admin/utlu112s.sql

cat ss.log
有省略
.
Oracle Database 11.2 Post-Upgrade Status Tool           03-18-2013 10:23:40
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.3.0 00:08:39
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.3.0 00:01:23
Oracle Workspace Manager
.                                         VALID      11.2.0.3.0 00:00:32
OLAP Analytic Workspace
.                                         VALID      11.2.0.3.0 00:00:22
OLAP Catalog
.                                         VALID      11.2.0.3.0 00:00:45
Oracle OLAP API
.                                         VALID      11.2.0.3.0 00:00:12
Oracle XDK
.                                         VALID      11.2.0.3.0 00:01:28
Oracle Text
.                                         VALID      11.2.0.3.0 00:00:36
Oracle XML Database
.                                         VALID      11.2.0.3.0 00:02:46
Oracle Database Java Packages
.                                         VALID      11.2.0.3.0 00:00:09
Oracle Multimedia
.                                         VALID      11.2.0.3.0 00:02:13
Spatial
.                                         VALID      11.2.0.3.0 00:03:55
Gathering Statistics
.                                                                00:01:20
Total Upgrade Time: 00:24:31

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON

6.執行catuppst.sql指令碼
@/opt/oracle/product/11g/rdbms/admin/catuppst.sql

下面是部分日誌
Generating apply and rollback scripts...
Check the following file for errors:
/opt/oracle/cfgtoollogs/catbundle/catbundle_PSU_DB01_GENERATE_2013Mar18_10_28_54.log
Apply script. /opt/oracle/product/11g/rdbms/admin/catbundle_PSU_DB01_APPLY.sql
Rollback script. /opt/oracle/product/11g/rdbms/admin/catbundle_PSU_DB01_ROLLBACK.sql

PL/SQL procedure successfully completed.

Executing script. file...

SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/opt/oracle/cfgtoollogs/catbundle/catbundle_PSU_DB01_APPLY_2013Mar18_10_28_54.log

檢查兩個.log檔案都沒有錯誤資訊，指令碼的命令都成功執行

7.執行下面指令碼修復invalid物件
@/opt/oracle/product/11g/rdbms/admin/utlrp.sql

SQL> SELECT count(*) FROM dba_invalid_objects;

COUNT(*)
----------
2

SQL> SELECT distinct object_name FROM dba_invalid_objects;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
PR_DECL_ORGINFO_SYNC
PR_STEPHEN_DEAL_BLOCK

SQL>

8．對timezone進行處理
SQL> startup upgrade pfile='/opt/oracle/initDB01.ora'
SQL> Execute DBMS_DST.BEGIN_UPGRADE(14,true);

PL/SQL procedure successfully completed.

SQL> select count(*)
from sys.dst$error_table;

COUNT(*)
----------
0

SQL>

SQL> select count(*)
from sys.dst$trigger_table;

COUNT(*)
----------
0

SQL> VAR numfail number
SQL> BEGIN
2     DBMS_DST.UPGRADE_DATABASE(:numfail,
3              parallel                  => TRUE,
4              log_errors                => TRUE,
5              log_errors_table          => 'SYS.DST$ERROR_TABLE',
6              log_triggers_table        => 'SYS.DST$TRIGGER_TABLE',
7              error_on_overlap_time     => TRUE,
8              error_on_nonexisting_time => TRUE);
9 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
10 END;
11 /

PL/SQL procedure successfully completed.

SQL>

SQL> BEGIN
2 DBMS_DST.END_UPGRADE(:numfail);
3 END;
4 /

PL/SQL procedure successfully completed.

手工升級9i資料庫到11g

相關文章