ORACLE 11.2.0.1升級到11.2.0.3

Oracle  11.2.0.1升級到11.2.0.3

最近聽了李光老師的關於oracle的升級公開課，深有感悟，之前一直想自己測試的，沒有下定決心，這幾天自己在虛擬機器上測試了一下，測試的過程如下，當然這個只是一些基本的步驟，實際的生產環境我想比這個複雜的多了，但是不用急，慢慢來，循序漸進吧。。。

由於blog的文件結構沒有辦法顯示，所以這裡我擷取我的文件結構圖：

資料庫情況

單例項非ASM儲存
ORACLE_SID ： orcl
ORACLE_HOME: /u01/app/oracle/product/11.2.0/dbhome_1

資料庫原始狀態

[oracle@rhel6 tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 26 16:44:37 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test as select * from user_objects;

Table created.

SQL> select count(1) from test;

COUNT(1)

----------

30804

SQL>set linesize 150

SQL>set pagesize 9999

SQL> col comp_name format a40

SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

COMP_NAME VERSION STATUS

---------------------------------------- ------------------------------ -----------

OWB 11.2.0.1.0 VALID

Oracle Application Express 3.2.1.00.10 VALID

Oracle Enterprise Manager 11.2.0.1.0 VALID

OLAP Catalog 11.2.0.1.0 VALID

Spatial 11.2.0.1.0 VALID

Oracle Multimedia 11.2.0.1.0 VALID

Oracle XML Database 11.2.0.1.0 VALID

Oracle Text 11.2.0.1.0 VALID

Oracle Expression Filter 11.2.0.1.0 VALID

Oracle Rules Manager 11.2.0.1.0 VALID

Oracle Workspace Manager 11.2.0.1.0 VALID

Oracle Database Catalog Views 11.2.0.1.0 VALID

Oracle Database Packages and Types 11.2.0.1.0 VALID

JServer JAVA Virtual Machine 11.2.0.1.0 VALID

Oracle XDK 11.2.0.1.0 VALID

Oracle Database Java Packages 11.2.0.1.0 VALID

OLAP Analytic Workspace 11.2.0.1.0 VALID

Oracle OLAP API 11.2.0.1.0 VALID

18 rows selected.

資料庫保持執行狀態：

解壓命令：

unzip p10404530_112030_Linux-x86-64_1of7.zip -d /tmp && unzip p10404530_112030_Linux-x86-64_2of7.zip -d /tmp

升級Oracle database 軟體

開始安裝：

執行到76%的時候彈出如下視窗：

[root@rhel6 ~]# /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh

Performing root user operation for Oracle 11g

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/app/oracle/product/11.2.0.4/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The contents of "dbhome" have not changed. No need to overwrite.

The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]:

The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]:

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

[root@rhel6 ~]#

升級Instance

升級Instance 有兩種方法，第一種是使用dbua工具，第二種是手工執行指令碼。

使用DBUA 工具

操作介面：

該步驟可以在告警日誌中檢視相關資訊：

Sun Sep 28 16:16:31 2014

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch

Autotune of undo retention is turned on.

IMODE=BR

ILAT =167

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1

System name: Linux

Node name: rhel6.5

Release: 2.6.32-431.el6.x86_64

Version: #1 SMP Sun Nov 10 22:19:54 EST 2013

Machine: x86_64

VM name: VMWare Version: 6

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileorcl.ora

System parameters with non-default values:

processes = 1000

shared_pool_size = 252M

java_pool_size = 252M

memory_target = 1G

control_files = "/u01/app/oracle/oradata/orcl/control01.ctl"

control_files = "/u01/app/oracle/oradata/orcl/control02.ctl"

db_block_size = 8192

compatible = "11.2.0.0.0"

undo_tablespace = "UNDOTBS1"

recyclebin = "OFF"

remote_login_passwordfile= "EXCLUSIVE"

db_domain = ""

dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"

local_listener = "LISTENER_ORCL"

audit_file_dest = "/u01/app/oracle/admin/orcl/adump"

audit_trail = "DB"

db_name = "orcl"

open_cursors = 300

diagnostic_dest = "/u01/app/oracle"

Sun Sep 28 16:16:38 2014

PMON started with pid=2, OS id=26426

Sun Sep 28 16:16:38 2014

PSP0 started with pid=3, OS id=26428

Sun Sep 28 16:16:39 2014

VKTM started with pid=4, OS id=26430 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Sun Sep 28 16:16:39 2014

GEN0 started with pid=5, OS id=26434

Sun Sep 28 16:16:39 2014

DIAG started with pid=6, OS id=26436

Sun Sep 28 16:16:39 2014

DBRM started with pid=7, OS id=26438

Sun Sep 28 16:16:39 2014

DIA0 started with pid=8, OS id=26440

Sun Sep 28 16:16:39 2014

MMAN started with pid=9, OS id=26442

Sun Sep 28 16:16:39 2014

DBW0 started with pid=10, OS id=26444

Sun Sep 28 16:16:39 2014

LGWR started with pid=11, OS id=26446

Sun Sep 28 16:16:39 2014

CKPT started with pid=12, OS id=26448

Sun Sep 28 16:16:39 2014

SMON started with pid=13, OS id=26450

Sun Sep 28 16:16:39 2014

RECO started with pid=14, OS id=26452

Sun Sep 28 16:16:39 2014

MMON started with pid=15, OS id=26454

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

Sun Sep 28 16:16:39 2014

MMNL started with pid=16, OS id=26456

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /u01/app/oracle

Sun Sep 28 16:16:58 2014

ALTER DATABASE MOUNT

Sun Sep 28 16:17:03 2014

Successful mount of redo thread 1, with mount id 1387228411

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

Sun Sep 28 16:17:06 2014

Shutting down instance (immediate)

Shutting down instance: further logons disabled

Stopping background process MMNL

Stopping background process MMON

License high water mark = 1

All dispatchers and shared servers shutdown

ALTER DATABASE CLOSE NORMAL

ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

ALTER DATABASE DISMOUNT

Shutting down archive processes

Archiving is disabled

Completed: ALTER DATABASE DISMOUNT

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Sun Sep 28 16:17:10 2014

Stopping background process VKTM

Sun Sep 28 16:17:12 2014

Instance shutdown complete

Sun Sep 28 16:17:14 2014

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch

Autotune of undo retention is turned on.

IMODE=BR

ILAT =167

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1

System name: Linux

Node name: rhel6.5

Release: 2.6.32-431.el6.x86_64

Version: #1 SMP Sun Nov 10 22:19:54 EST 2013

Machine: x86_64

VM name: VMWare Version: 6

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileorcl.ora

System parameters with non-default values:

processes = 1000

shared_pool_size = 252M

java_pool_size = 252M

memory_target = 1G

control_files = "/u01/app/oracle/oradata/orcl/control01.ctl"

control_files = "/u01/app/oracle/oradata/orcl/control02.ctl"

db_block_size = 8192

compatible = "11.2.0.0.0"

undo_tablespace = "UNDOTBS1"

recyclebin = "OFF"

remote_login_passwordfile= "EXCLUSIVE"

db_domain = ""

dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"

local_listener = "LISTENER_ORCL"

audit_file_dest = "/u01/app/oracle/admin/orcl/adump"

audit_trail = "DB"

db_name = "orcl"

open_cursors = 300

diagnostic_dest = "/u01/app/oracle"

Sun Sep 28 16:17:17 2014

PMON started with pid=2, OS id=26627

Sun Sep 28 16:17:17 2014

PSP0 started with pid=3, OS id=26629

Sun Sep 28 16:17:18 2014

VKTM started with pid=4, OS id=26631 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Sun Sep 28 16:17:18 2014

GEN0 started with pid=5, OS id=26635

Sun Sep 28 16:17:18 2014

DIAG started with pid=6, OS id=26637

Sun Sep 28 16:17:18 2014

DBRM started with pid=7, OS id=26639

Sun Sep 28 16:17:18 2014

DIA0 started with pid=8, OS id=26641

Sun Sep 28 16:17:18 2014

MMAN started with pid=9, OS id=26643

Sun Sep 28 16:17:18 2014

DBW0 started with pid=10, OS id=26645

Sun Sep 28 16:17:18 2014

LGWR started with pid=11, OS id=26647

Sun Sep 28 16:17:18 2014

CKPT started with pid=12, OS id=26649

Sun Sep 28 16:17:18 2014

SMON started with pid=13, OS id=26651

Sun Sep 28 16:17:18 2014

RECO started with pid=14, OS id=26653

Sun Sep 28 16:17:18 2014

MMON started with pid=15, OS id=26655

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

Sun Sep 28 16:17:18 2014

MMNL started with pid=16, OS id=26657

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /u01/app/oracle

Sun Sep 28 16:17:25 2014

ALTER DATABASE MOUNT

Sun Sep 28 16:17:29 2014

Successful mount of redo thread 1, with mount id 1387239701

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

Sun Sep 28 16:17:30 2014

ALTER DATABASE OPEN MIGRATE

Thread 1 opened at log sequence 3

Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Sun Sep 28 16:17:31 2014

SMON: enabling cache recovery

[26688] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:38185014 end:38186274 diff:1260 (12 seconds)

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is WE8MSWIN1252

Updating 11.2.0.1.0 NLS parameters in sys.props$

-- adding 11.2.0.3.0 NLS parameters.

Stopping background process MMNL

Stopping background process MMON

Starting background process MMON

Starting background process MMNL

Sun Sep 28 16:17:40 2014

MMON started with pid=15, OS id=26690

ALTER SYSTEM enable restricted session;

Sun Sep 28 16:17:40 2014

MMNL started with pid=16, OS id=26692

ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;

Autotune of undo retention is turned off.

ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;

ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;

ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;

ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY;

Resource Manager disabled during database migration: plan '' not set

ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;

ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY;

Resource Manager disabled during database migration

Sun Sep 28 16:17:41 2014

replication_dependency_tracking turned off (no async multimaster replication found)

Completed: ALTER DATABASE OPEN MIGRATE

Sun Sep 28 16:17:44 2014

Starting background process CJQ0

Sun Sep 28 16:17:45 2014

CJQ0 started with pid=20, OS id=26694

點選YES，後

後邊有報告，完成即可。

同樣，如果升級過程中出現問題，我們可以關閉資料庫後，重新執行dbua 來重新升級上次失敗的元件，如下圖：

手動升級
1. 第一步 utlu112i.sql

安裝最新的 11.2 RDBMS 軟體之後，用以前的 ORACLE_HOME 中啟動 11.2.0.1例項，對正在執行的以前的例項執行 11.2.0.4 的$ORACLE_HOME/rdbms/admin/utlu112i.sql 指令碼，並將輸出 spool 至一個檔案。

[oracle@rhel6 database]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 26 17:27:46 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool /home/oracle/utlu112i_log.log

SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 09-26-2014 17:28:37

Script Version: 11.2.0.3.0 Build: 001

.

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

Database:

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

--> name: ORCL

--> version: 11.2.0.1.0

--> compatible: 11.2.0.0.0

--> blocksize: 8192

--> platform: Linux x86 64-bit

--> timezone file: V11

.

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

Tablespaces: [make adjustments in the current environment]

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

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 683 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 438 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 400 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 60 MB

.

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

Flashback: OFF

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

Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]

Note: Pre-upgrade tool was run on a lower version 64-bit database.

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

--> If Target Oracle is 32-Bit, refer here for Update Parameters:

WARNING: --> "sga_target" needs to be increased to at least 412 MB

.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:

WARNING: --> "sga_target" needs to be increased to at least 596 MB

.

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

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]

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

-- No obsolete parameters found. No changes are required

.

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

Components: [The following database components will be upgraded or installed]

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

--> Oracle Catalog Views [upgrade] VALID

--> Oracle Packages and Types [upgrade] VALID

--> JServer JAVA Virtual Machine [upgrade] VALID

--> Oracle XDK for Java [upgrade] VALID

--> Oracle Workspace Manager [upgrade] VALID

--> OLAP Analytic Workspace [upgrade] VALID

--> OLAP Catalog [upgrade] VALID

--> EM Repository [upgrade] VALID

--> Oracle Text [upgrade] VALID

--> Oracle XML Database [upgrade] VALID

--> Oracle Java Packages [upgrade] VALID

--> Oracle interMedia [upgrade] VALID

--> Spatial [upgrade] VALID

--> Expression Filter [upgrade] VALID

--> Rule Manager [upgrade] VALID

--> Oracle Application Express [upgrade] VALID

... APEX will only be upgraded if the version of APEX in

... the target Oracle home is higher than the current one.

--> Oracle OLAP API [upgrade] VALID

.

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

Miscellaneous Warnings

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

WARNING: --> 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 11.2.0.1.0 database timezone version

.... to the latest version which comes with the new release.

WARNING: --> Your recycle bin is turned on and currently contains no objects.

.... Because it is REQUIRED that the recycle bin be empty prior to upgrading

.... and your recycle bin is turned on, you may need to execute the command:

PURGE DBA_RECYCLEBIN

.... prior to executing your upgrade to confirm the recycle bin is empty.

WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.

.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.

.... USER APEX_030200 has dependent objects.

.

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

Recommendations

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

Oracle recommends gathering dictionary statistics prior to

upgrading the database.

To gather dictionary statistics execute the following command

while connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats;

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

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.

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

SQL> spool off

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel6 database]$

第二步 DBUPGDIAG.SQL

[oracle@rhel6 share-D]$ cd /tmp/

[oracle@rhel6 tmp]$ ll dbupgdiag.sql

-rwxr-xr-x. 1 oracle oinstall 23931 Sep 26 17:43 dbupgdiag.sql

[oracle@rhel6 tmp]$ pwd

/tmp

[oracle@rhel6 tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 26 17:44:31 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/tmp/dbupgdiag.sql

Enter location for Spooled output:

Enter value for 1: /home/oracle

26_Sep_2014_0544 .log

orcl_

*** Start of LogFile ***

Oracle Database Upgrade Diagnostic Utility 09-26-2014 17:44:54

===============

Hostname

===============

rhel6.5

===============

Database Name

===============

ORCL

===============

Database Uptime

===============

16:01 26-SEP-14

=================

Database Wordsize

=================

This is a 64-bit database

================

Software Version

================

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

=============

Compatibility

=============

Compatibility is set as 11.2.0.0.0

================

Archive Log Mode

================

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence 1

Current log sequence 2

================

Auditing Check

================

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest string /u01/app/oracle/admin/orcl/adu

mp

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string DB

================

Cluster Check

================

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cluster_database boolean FALSE

cluster_database_instances integer 1

DOC>################################################################

DOC>

DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before

DOC> upgrading the database

DOC>

DOC>################################################################

DOC>#

===========================================

Tablespace and the owner of the aud$ table

===========================================

OWNER TABLESPACE_NAME

------------ ------------------------------

SYS SYSTEM

============================================================================

count of records in the sys.aud$ table where dbid is null- Standard Auditing

============================================================================

0

============================================================================================

count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed

============================================================================================

select count(*) from system.aud$ where dbid is null

*

ERROR at line 1:

ORA-00942: table or view does not exist

=============================================================================

count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing

=============================================================================

0

==========================================

Oracle Label Security is installed or not

==========================================

Oracle Label Security is NOT installed at database level

================

Number of AQ Records in Message Queue Tables

================

SYS - ALERT_QT - 0

SYS - AQ$_MEM_MC - 0

SYS - AQ_EVENT_TABLE - 0

SYS - AQ_PROP_TABLE - 0

SYS - KUPC$DATAPUMP_QUETAB - 0

SYS - SCHEDULER$_EVENT_QTAB - 0

SYS - SCHEDULER$_REMDB_JOBQTAB - 0

SYS - SCHEDULER_FILEWATCHER_QT - 0

SYS - SYS$SERVICE_METRICS_TAB - 0

SYSMAN - MGMT_LOADER_QTABLE - 0

SYSMAN - MGMT_NOTIFY_INPUT_QTABLE - 0

SYSMAN - MGMT_NOTIFY_QTABLE - 0

SYSMAN - MGMT_PAF_MSG_QTABLE_1 - 0

SYSMAN - MGMT_PAF_MSG_QTABLE_2 - 0

SYSMAN - MGMT_TASK_QTABLE - 27

SYSTEM - DEF$_AQCALL - 0

SYSTEM - DEF$_AQERROR - 0

WMSYS - WM$EVENT_QUEUE_TABLE - 0

================

Time Zone version

================

11

================

Local Listener

================

LISTENER_ORCL

================

Default and Temporary Tablespaces By User

================

USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE

---------------------------- ---------------------- ----------------------

SYS TEMP SYSTEM

SYSTEM TEMP SYSTEM

OUTLN TEMP SYSTEM

MGMT_VIEW TEMP SYSTEM

FLOWS_FILES TEMP SYSAUX

MDSYS TEMP SYSAUX

ORDSYS TEMP SYSAUX

EXFSYS TEMP SYSAUX

DBSNMP TEMP SYSAUX

WMSYS TEMP SYSAUX

APPQOSSYS TEMP SYSAUX

APEX_030200 TEMP SYSAUX

OWBSYS_AUDIT TEMP SYSAUX

ORDDATA TEMP SYSAUX

CTXSYS TEMP SYSAUX

ANONYMOUS TEMP SYSAUX

SYSMAN TEMP SYSAUX

XDB TEMP SYSAUX

ORDPLUGINS TEMP SYSAUX

OWBSYS TEMP SYSAUX

SI_INFORMTN_SCHEMA TEMP SYSAUX

OLAPSYS TEMP SYSAUX

SCOTT TEMP USERS

ORACLE_OCM TEMP USERS

XS$NULL TEMP USERS

MDDATA TEMP USERS

DIP TEMP USERS

APEX_PUBLIC_USER TEMP USERS

SPATIAL_CSW_ADMIN_USR TEMP USERS

SPATIAL_WFS_ADMIN_USR TEMP USERS

================

Component Status

================

Comp ID Component Status Version Org_Version Prv_Version

------- ---------------------------------- --------- -------------- -------------- --------------

AMD OLAP Catalog VALID 11.2.0.1.0

APEX Oracle Application Express VALID 3.2.1.00.10

APS OLAP Analytic Workspace VALID 11.2.0.1.0

CATALOG Oracle Database Catalog Views VALID 11.2.0.1.0

CATJAVA Oracle Database Java Packages VALID 11.2.0.1.0

CATPROC Oracle Database Packages and Types VALID 11.2.0.1.0

CONTEXT Oracle Text VALID 11.2.0.1.0

EM Oracle Enterprise Manager VALID 11.2.0.1.0

EXF Oracle Expression Filter VALID 11.2.0.1.0

JAVAVM JServer JAVA Virtual Machine VALID 11.2.0.1.0

ORDIM Oracle Multimedia VALID 11.2.0.1.0

OWB OWB VALID 11.2.0.1.0

OWM Oracle Workspace Manager VALID 11.2.0.1.0

RUL Oracle Rules Manager VALID 11.2.0.1.0

SDO Spatial VALID 11.2.0.1.0

XDB Oracle XML Database VALID 11.2.0.1.0

XML Oracle XDK VALID 11.2.0.1.0

XOQ Oracle OLAP API VALID 11.2.0.1.0

======================================================

List of Invalid Database Objects Owned by SYS / SYSTEM

======================================================

Number of Invalid Objects

------------------------------------------------------------------

There are no Invalid Objects

DOC>################################################################

DOC>

DOC> If there are no Invalid objects below will result in zero rows.

DOC>

DOC>################################################################

DOC>#

no rows selected

================================

List of Invalid Database Objects

================================

Number of Invalid Objects

------------------------------------------------------------------

There are no Invalid Objects

DOC>################################################################

DOC>

DOC> If there are no Invalid objects below will result in zero rows.

DOC>

DOC>################################################################

DOC>#

no rows selected

======================================================

Count of Invalids by Schema

======================================================

==============================================================

Identifying whether a database was created as 32-bit or 64-bit

==============================================================

DOC>###########################################################################

DOC>

DOC> Result referencing the string 'B023' ==> Database was created as 32-bit

DOC> Result referencing the string 'B047' ==> Database was created as 64-bit

DOC> When String results in 'B023' and when upgrading database to 10.2.0.3.0

DOC> (64-bit) , For known issue refer below articles

DOC>

DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While

DOC> Upgrading Or Patching Databases To 10.2.0.3

DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and

DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6

DOC>

DOC>###########################################################################

DOC>#

Metadata Initial DB Creation Info

-------- -----------------------------------

B047 Database was created as 64-bit

===================================================

Number of Duplicate Objects Owned by SYS and SYSTEM

===================================================

Counting duplicate objects ....

COUNT(1)

----------

4

=========================================

Duplicate Objects Owned by SYS and SYSTEM

=========================================

Querying duplicate objects ....

OBJECT_NAME OBJECT_TYPE SUBOBJECT_NAME OBJECT_ID

---------------------------------------- ---------------------------------------- ------------------------------ ----------

AQ$_SCHEDULES TABLE 5612

AQ$_SCHEDULES_PRIMARY INDEX 5613

DBMS_REPCAT_AUTH PACKAGE 8435

DBMS_REPCAT_AUTH PACKAGE BODY 11896

DOC>

DOC>################################################################################

DOC>

DOC> If any objects found please follow below article.

DOC> Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema

DOC> Read the Exceptions carefully before taking actions.

DOC>

DOC>################################################################################

DOC>#

========================

Password protected roles

========================

DOC>

DOC>################################################################################

DOC>

DOC> In version 11.2 password protected roles are no longer enabled by default so if

DOC> an application relies on such roles being enabled by default and no action is

DOC> performed to allow the user to enter the password with the set role command, it

DOC> is recommended to remove the password from those roles (to allow for existing

DOC> privileges to remain available). For more information see:

DOC>

DOC> Note 745407.1 : What Roles Can Be Set as Default for a User?

DOC>

DOC>################################################################################

DOC>#

Querying for password protected roles ....

Password protected Role Assigned by default to user

------------------------------ ------------------------------

OWB$CLIENT OWBSYS

SPATIAL_CSW_ADMIN SPATIAL_CSW_ADMIN_USR

================

JVM Verification

================

================================================

Checking Existence of Java-Based Users and Roles

================================================

DOC>

DOC>################################################################################

DOC>

DOC> There should not be any Java Based users for database version 9.0.1 and above.

DOC> If any users found, it is faulty JVM.

DOC>

DOC>################################################################################

DOC>#

User Existence

---------------------------

No Java Based Users

DOC>

DOC>###############################################################

DOC>

DOC> Healthy JVM Should contain Six Roles.

DOC> If there are more or less than six role, JVM is inconsistent.

DOC>

DOC>###############################################################

DOC>#

Role

------------------------------

There are 6 JAVA related roles

Roles

ROLE

------------------------------

JAVA_DEPLOY

JAVAUSERPRIV

JAVAIDPRIV

JAVASYSPRIV

JAVADEBUGPRIV

JAVA_ADMIN

=========================================

List of Invalid Java Objects owned by SYS

=========================================

There are no SYS owned invalid JAVA objects

DOC>

DOC>#################################################################

DOC>

DOC> Check the status of the main JVM interface packages DBMS_JAVA

DOC> and INITJVMAUX and make sure it is VALID.

DOC>

DOC> If there are no Invalid objects below will result in zero rows.

DOC>

DOC>#################################################################

DOC>#

no rows selected

DOC>

DOC>#################################################################

DOC>

DOC> If the JAVAVM component is not installed in the database (for

DOC> example, after creating the database with custom scripts), the

DOC> next query will report the following error:

DOC>

DOC> select dbms_java.longname('foo') "JAVAVM TESTING" from dual

DOC> *

DOC> ERROR at line 1:

DOC> ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier

DOC>

DOC> If the JAVAVM component is installed, the query should succeed

DOC> with 'foo' as result.

DOC>

DOC>#################################################################

DOC>#

JAVAVM TESTING

---------------

foo

===================================

Oracle Multimedia/InterMedia status

===================================

.

Oracle Multimedia/interMedia is installed and listed with the following version: 11.2.0.1.0 and status: VALID

.

Checking for installed Database Schemas...

ORDSYS user exists.

ORDPLUGINS user exists.

MDSYS user exists.

SI_INFORMTN_SCHEMA user exists.

ORDDATA user exists.

.

Checking for Prerequisite Components...

JAVAVM installed and listed as valid

XDK installed and listed as valid

XDB installed and listed as valid

Validating Oracle Multimedia/interMedia...(no output if component status is valid)

PL/SQL procedure successfully completed.

*** End of LogFile ***

Upload db_upg_diag_orcl_26_Sep_2014_0544.log from "/home/oracle" directory

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlrp.sql

第三步備份

禁用所有批處理和 cron 作業，然後執行資料庫的完整備份。

第四步乾淨的關閉資料庫

[oracle@rhel6 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 28 09:11:46 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel6 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 28 09:16:52 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel6 ~]

第五步配置目標 11.2.0.3 ORACLE_HOME

拷貝$ORACLE_HOME/dbs 至新目錄 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
拷貝$ORACLE_HOME/network/admin 至新目錄 /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/

[oracle@rhel6 ~]$ cd $ORACLE_HOME/dbs

[oracle@rhel6 dbs]$ ll

total 3580

-rw-r-----. 1 oracle oinstall 3635200 Jul 20 17:50 arch1_32_852154753.dbf

-rw-rw----. 1 oracle oinstall 1544 Sep 26 17:14 hc_DBUA0.dat

-rw-rw----. 1 oracle oinstall 1544 Sep 28 09:17 hc_orcl.dat

-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora

-rw-r-----. 1 oracle oinstall 982 Jul 21 14:28 initorclasm.ora

-rw-r-----. 1 oracle oinstall 24 Jul 20 17:46 lkORCL

-rw-r-----. 1 oracle oinstall 1536 Sep 26 16:00 orapworcl

-rw-r-----. 1 oracle oinstall 2560 Sep 28 09:10 spfileorcl.ora

[oracle@rhel6 dbs]$ pwd

/u01/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@rhel6 dbs]$ ll /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/

total 4

-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora

[oracle@rhel6 dbs]$ cp * /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/

[oracle@rhel6 dbs]$ ll /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/

total 3580

-rw-r-----. 1 oracle oinstall 3635200 Sep 28 09:31 arch1_32_852154753.dbf

-rw-r-----. 1 oracle oinstall 1544 Sep 28 09:31 hc_DBUA0.dat

-rw-r-----. 1 oracle oinstall 1544 Sep 28 09:31 hc_orcl.dat

-rw-r--r--. 1 oracle oinstall 2851 Sep 28 09:31 init.ora

-rw-r-----. 1 oracle oinstall 982 Sep 28 09:31 initorclasm.ora

-rw-r-----. 1 oracle oinstall 24 Sep 28 09:31 lkORCL

-rw-r-----. 1 oracle oinstall 1536 Sep 28 09:31 orapworcl

-rw-r-----. 1 oracle oinstall 2560 Sep 28 09:31 spfileorcl.ora

[oracle@rhel6 dbs]$ cd ..

[oracle@rhel6 dbhome_1]$ cd network/admin/

[oracle@rhel6 admin]$ ll

total 24

-rw-r--r--. 1 oracle oinstall 497 Jul 20 17:38 listener.ora

-rw-r--r--. 1 oracle oinstall 981 Jul 20 17:02 listener.ora_bk

drwxr-xr-x. 2 oracle oinstall 4096 May 18 17:01 samples

-rw-r--r--. 1 oracle oinstall 187 May 7 2007 shrept.lst

-rw-r--r--. 1 oracle oinstall 203 Jul 19 22:33 sqlnet.ora

-rw-r--r--. 1 oracle oinstall 1318 Sep 26 16:01 tnsnames.ora

[oracle@rhel6 admin]$ cp -R * /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/

[oracle@rhel6 admin]$

export EDITOR=vi

export ORACLE_SID=orcl

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/:/usr/lib

export PATH=$ORACLE_HOME/bin:$PATH

export TNS_ADMIN=$ORACLE_HOME/network/admin

[oracle@rhel6 ~]$ vi .bash_profile

[oracle@rhel6 ~]$ source .bash_profile

[oracle@rhel6 ~]$

第六步開始升級

sqlplus " / as sysdba "SQL> spool /tmp/upgrade.logSQL> startup upgradeSQL> set echo onSQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql;SQL> spool offSQL> Shutdown immediate

catupgrd.sql 該指令碼花費時間較長，大約30分鐘

DOC>#######################################################################

DOC>

DOC> The above sql script is the final step of the upgrade. Please

DOC> review any errors in the spool log file. If there are any errors in

DOC> the spool file, consult the Oracle Database Upgrade Guide for

DOC> troubleshooting recommendations.

DOC>

DOC> Next restart for normal operation, and then run utlrp.sql to

DOC> recompile any invalid application objects.

DOC>

DOC> If the source database had an older time zone version prior to

DOC> upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade

DOC> TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped

DOC> with Oracle.

DOC>

DOC>#######################################################################

DOC>#

SQL>

SQL> Rem Set errorlogging off

SQL> SET ERRORLOGGING OFF;

SQL>

SQL> REM END OF CATUPGRD.SQL

SQL>

SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.

SQL> REM This forces user to start a new sqlplus session in order

SQL> REM to connect to the upgraded db.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

日誌壓縮後仍然很大，這裡就不貼出來了。。。。

驗證

[oracle@rhel6 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 28 10:56:16 2014

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2235208 bytes

Variable Size 973079736 bytes

Database Buffers 88080384 bytes

Redo Buffers 5541888 bytes

Database mounted.

Database opened.

SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112s.sql

.

Oracle Database 11.2 Post-Upgrade Status Tool 09-28-2014 10:59:11

.

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

.

Oracle Server

. VALID 11.2.0.3.0 00:14:09

JServer JAVA Virtual Machine

. VALID 11.2.0.3.0 00:05:55

Oracle Workspace Manager

. VALID 11.2.0.3.0 00:00:44

OLAP Analytic Workspace

. VALID 11.2.0.3.0 00:00:51

OLAP Catalog

. VALID 11.2.0.3.0 00:01:09

Oracle OLAP API

. VALID 11.2.0.3.0 00:00:41

Oracle Enterprise Manager

. VALID 11.2.0.3.0 00:03:35

Oracle XDK

. VALID 11.2.0.3.0 00:00:41

Oracle Text

. VALID 11.2.0.3.0 00:00:39

Oracle XML Database

. VALID 11.2.0.3.0 00:03:05

Oracle Database Java Packages

. VALID 11.2.0.3.0 00:00:30

Oracle Multimedia

. VALID 11.2.0.3.0 00:04:49

Spatial

. VALID 11.2.0.3.0 00:02:22

Oracle Expression Filter

. VALID 11.2.0.3.0 00:00:14

Oracle Rules Manager

. VALID 11.2.0.3.0 00:00:12

Oracle Application Express

. VALID 3.2.1.00.10

Gathering Statistics

. 00:03:09

Total Upgrade Time: 00:42:58

PL/SQL procedure successfully completed.

SQL>

About Me

.............................................................................................................................................

● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群號：230161599（滿）、618766405

● 微信群：可加我微信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友（646634621），註明新增緣由

● 於 2014-09-01 09:00 ~ 2014-09-31 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

.............................................................................................................................................

● 小麥苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。

小麥苗的微信公眾號小麥苗的DBA寶典QQ群1 小麥苗的DBA寶典QQ群2 小麥苗的微店

.............................................................................................................................................