11GR2 DATAGUARD环境下的DATABASE升级（11.2.0.2升级到11.2.0.3）(1)
<a target="_blank" href="http://luoping.blog.51cto.com/534596/988073">http://luoping.blog.51cto.com/534596/988073</a>
11GR2 DATAGUARD环境下的DATABASE升级（11.2.0.2升级到11.2.0.3）（2）
<a target="_blank" href="http://luoping.blog.51cto.com/534596/988108">http://luoping.blog.51cto.com/534596/988108</a>
11GR2 DATAGUARD环境下的DATABASE升级（11.2.0.2升级到11.2.0.3）（3）
<a target="_blank" href="http://luoping.blog.51cto.com/534596/988024">http://luoping.blog.51cto.com/534596/988024</a>
升级完成后我们再次执行dbupgrade脚本
SQL> @/home/oracle/rs/sql/dbupgdiag
Enter location for Spooled output:
11_Sep_2012_0741 .log
htz_
SP2-0606: Cannot create SPOOL file "0/db_upg_diag_htz_11_Sep_2012_0741.log"
*** Start of LogFile ***
Oracle Database Upgrade Diagnostic Utility 09-11-2012 07:41:41
===============
Hostname
11g
Database Name
HTZ
Database Uptime
07:28 11-SEP-12
=================
Database Wordsize
This is a 64-bit database
================
Software Version
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
=============
Compatibility
Compatibility is set as 11.2.0.0.0
Archive Log Mode
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch/htz/
Oldest online log sequence 98
Next log sequence to archive 100
Current log sequence 100
Auditing Check
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/htz/adum
p
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
Cluster Check
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>#
===========================================
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
============================================================================================
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
==========================================
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
14
Local Listener
Default and Temporary Tablespaces By User
USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE
---------------------------- ---------------------- ----------------------
SYS TEMP SYSTEM
SYSTEM TEMP SYSTEM
SCOTT TEMP USERS
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
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.3.0 11.2.0.2.0 11.2.0.2.0
APEX Oracle Application Express VALID 3.2.1.00.12
APS OLAP Analytic Workspace VALID 11.2.0.3.0 11.2.0.2.0 11.2.0.2.0
CATALOG Oracle Database Catalog Views VALID 11.2.0.3.0 11.2.0.2.0 11.2.0.2.0
CATJAVA Oracle Database Java Packages VALID 11.2.0.3.0 11.2.0.2.0 11.2.0.2.0
CATPROC Oracle Database Packages and Types VALID 11.2.0.3.0 11.2.0.2.0 11.2.0.2.0
CONTEXT Oracle Text VALID 11.2.0.3.0 11.2.0.2.0 11.2.0.2.0
EM Oracle Enterprise Manager VALID 11.2.0.3.0 11.2.0.2.0
EXF Oracle Expression Filter VALID 11.2.0.3.0 11.2.0.2.0 11.2.0.2.0
JAVAVM JServer JAVA Virtual Machine VALID 11.2.0.3.0 11.2.0.2.0 11.2.0.2.0
ORDIM Oracle Multimedia VALID 11.2.0.3.0 11.2.0.2.0 11.2.0.2.0
OWB OWB VALID 11.2.0.2.0
OWM Oracle Workspace Manager VALID 11.2.0.3.0 11.2.0.2.0
RUL Oracle Rules Manager VALID 11.2.0.3.0 11.2.0.2.0 11.2.0.2.0
SDO Spatial VALID 11.2.0.3.0 11.2.0.2.0 11.2.0.2.0
XDB Oracle XML Database VALID 11.2.0.3.0 11.2.0.2.0 11.2.0.2.0
XML Oracle XDK VALID 11.2.0.3.0 11.2.0.2.0 11.2.0.2.0
XOQ Oracle OLAP API VALID 11.2.0.3.0 11.2.0.2.0 11.2.0.2.0
======================================================
List of Invalid Database Objects Owned by SYS / SYSTEM
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC> If there are no Invalid objects below will result in zero rows.
no rows selected
================================
List of Invalid Database Objects
Count of Invalids by Schema
==============================================================
Identifying whether a database was created as 32-bit or 64-bit
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> 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
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 5701
AQ$_SCHEDULES_PRIMARY INDEX 5702
DBMS_REPCAT_AUTH PACKAGE 8568
DBMS_REPCAT_AUTH PACKAGE BODY 12144
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.
========================
Password protected roles
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> Note 745407.1 : What Roles Can Be Set as Default for a User?
Querying for password protected roles ....
Password protected Role Assigned by default to user
------------------------------ ------------------------------
OWB$CLIENT OWBSYS
JVM Verification
================================================
Checking Existence of Java-Based Users and Roles
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.
User Existence
---------------------------
No Java Based Users
DOC>###############################################################
DOC> Healthy JVM Should contain Six Roles.
DOC> If there are more or less than six role, JVM is inconsistent.
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> Check the status of the main JVM interface packages DBMS_JAVA
DOC> and INITJVMAUX and make sure it is VALID.
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> select dbms_java.longname('foo') "JAVAVM TESTING" from dual
DOC> *
DOC> ERROR at line 1:
DOC> ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
DOC> If the JAVAVM component is installed, the query should succeed
DOC> with 'foo' as result.
JAVAVM TESTING
---------------
foo
*** End of LogFile ***
到这里主库升级完成。
10 关闭备库与停监听
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@11gdg admin]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 10-SEP-2012 10:31:12
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.31)(PORT=1521)))
The command completed successfully
#修改相关的文件
[oracle@11gdg ~]$ tail -1 /etc/oratab
htz:/u01/app/oracle/product/11.2.0/db_2:N
[oracle@11gdg admin]$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/* /u01/app/oracle/product/11.2.0/db_2/dbs/
[oracle@11gdg admin]$ cp -r /u01/app/oracle/product/11.2.0/db_1/network/admin/* /u01/app/oracle/product/11.2.0/db_2/network/admin/
[oracle@11gdg ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_SID=htz
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_2
export PATH=$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/OPatch
stty erase ^h
set -o vi
export DISPLAY=192.168.100.1:0.0
export SHELL_HOME=$HOME/rs
export PATH=$PATH:$SHELL_HOME/bin:$SHELL_HOME/dbmonitor:$SHELL_HOME/asm:$SHELL_HOME/rman:$SHELL_HOME/tune:$SHELL_HOME/dump:$SHELL_HOME/event
export TRACE=/u01/app/oracle/diag/rdbms/htzb/htz/trace
[oracle@11gdg ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 10-SEP-2012 10:32:30
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_2/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_2/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/11gdg/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.31)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 10-SEP-2012 10:32:30
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_2/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/11gdg/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.31)(PORT=1521)))
Services Summary...
Service "htzb" has 1 instance(s).
Instance "htz", status UNKNOWN, has 1 handler(s) for this service...
12 启动数据库并手动recover
[oracle@11gdg ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 10 10:32:42 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
#注意如果这里直接用startup要报下面的错误 的
SQL> startup
ORACLE instance started.
Total System Global Area 313159680 bytes
Fixed Size 2227944 bytes
Variable Size 255852824 bytes
Database Buffers 50331648 bytes
Redo Buffers 4747264 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 4233
Session ID: 1 Serial number: 5
SQL>
#手动recover
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
alert日志
Completed: ALTER DATABASE RECOVER managed standby database using current logfile disconnect
Mon Sep 10 10:42:12 2012
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/htz/redo02.log
Clearing online log 2 of thread 1 sequence number 96
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/app/oracle/oradata/htz/redo03.log
Clearing online log 3 of thread 1 sequence number 95
Clearing online redo logfile 3 complete
Media Recovery Log /u01/app/oracle/arch/htz/1_28_793318533.dbf
Identified End-Of-Redo (switchover) for thread 1 sequence 28 at SCN 0x0.121e16
Resetting standby activation ID 1848137735 (0x6e285807)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Continuing
Media Recovery Log /u01/app/oracle/arch/htz/1_29_793318533.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_30_793318533.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_31_793318533.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_32_793318533.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_33_793318533.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_34_793318533.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_35_793318533.dbf
Mon Sep 10 10:42:44 2012
Media Recovery Log /u01/app/oracle/arch/htz/1_36_793318533.dbf
Mon Sep 10 10:42:58 2012
Media Recovery Log /u01/app/oracle/arch/htz/1_37_793318533.dbf
Mon Sep 10 10:43:11 2012
Media Recovery Log /u01/app/oracle/arch/htz/1_38_793318533.dbf
说明正在recover
直接alert日志中出现下面的提示Media Recovery Log /u01/app/oracle/arch/htz/1_99_793318533.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_100_793318533.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_101_793318533.dbf
Media Recovery Log /u01/app/oracle/arch/htz/1_102_793318533.dbf
Media Recovery Waiting for thread 1 sequence 103 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 103 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/htz/standby01.log
表示正在等待mem中的日志，recover完成了。
这里启动数据库open read only状态
收集陈旧的统计信息
[oracle@11gdg sql]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 11 00:20:15 2012
Connected to:
SQL> @check_stale_stats
-- There are no stale statistics in APEX_030200 schema.
-------------------------------------------------------------------------------------------------------
-- CTXSYS schema contains stale statistics use the following to gather the statistics --
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('CTXSYS',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
-- There are no stale statistics in EXFSYS schema.
-- There are no stale statistics in MDSYS schema.
-- OLAPSYS schema contains stale statistics use the following to gather the statistics --
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('OLAPSYS',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
-- ORDSYS schema contains stale statistics use the following to gather the statistics --
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('ORDSYS',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
-- There are no stale statistics in OWBSYS schema.
-- SYS schema contains stale statistics use the following to gather the statistics --
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYS',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
-- SYSMAN schema contains stale statistics use the following to gather the statistics --
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYSMAN',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
-- There are no stale statistics in WMSYS schema.
-- XDB schema contains stale statistics use the following to gather the statistics --
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('XDB',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
照着上面执行吧。
13 升级完成后做切换。
主上：
SYS@11g > set lines 100
SYS@11g > select open_mode,log_mode,switchover_status,database_role,flashback_on from v$database;
OPEN_MODE LOG_MODE SWITCHOVER_STATUS DATABASE_ROLE FLASHBACK_ON
-------------------- ------------ -------------------- ---------------- ------------------
READ WRITE ARCHIVELOG TO STANDBY PRIMARY YES
SYS@11g > alter database commit to switchover to physical standby with session shutdown;
Database altered.
SYS@11g > shutdown immediate;
SYS@11g > startup
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SYS@11g > exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
[oracle@11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 11 18:26:13 2012
> startup
Variable Size 239075608 bytes
Database Buffers 67108864 bytes
Database opened.
> set lines 100
> select open_mode,log_mode,switchover_status,database_role,flashback_on from v$database;
READ ONLY ARCHIVELOG TO PRIMARY PHYSICAL STANDBY YES
备上
SQL> set lines 100
SQL> select open_mode,log_mode,switchover_status,database_role,flashback_on from v$database;
READ ONLY WITH APPLY ARCHIVELOG TO PRIMARY PHYSICAL STANDBY YES
SQL> alter database commit to switchover to primary with session shutdown;
MOUNTED ARCHIVELOG NOT ALLOWED PRIMARY YES
SQL> alter database open;
整个升级过程已经完成。
后面将会介绍临时的logical standby database升级方式。
本文转自7343696 51CTO博客，原文链接：http://blog.51cto.com/luoping/988024，如需转载请自行联系原作者
![【MySQL数据库】数据库索引事务1.索引2.事务[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)