11GR2 DATAGUARD环境下的DATABASE升级（11.2.0.2升级到11.2.0.3）

menny

今天主要测试是11GR2 PHYSICAL STANDBY DATABAES环境下面的数据库升级，从11.2.0.2升级到11.2.0.3。在DG环境下面的升级方法有很多.如：
1，升级主库，主库完成后再RECOVER备库。
2，把PHYSICAL转临时的逻辑备库，升级逻辑备库后，切换，再RECOVER原主库。
    这里我采用的是第1种方式，这个在10G中也是最常用的升级方法。不过我这里的主库，不是正式运行的主库，而是在升级前做了一次切换，把备库变成主备的，是为了确保原主库在升级失败后，可以快速的还原（使用flashback方法），减少停机时机。在整个升级过程中，备库是一直对外提供读的服务。
    本次只是测试环境测试，千万不能用于生产环境。因为有很多地方没有考虑周到的，比如在切换的时候没有考虑是否有大事务这些在运行，升级前的无效对象检查等。
测试环境：OS RHEL 5.6 X86_64 ,DB 11.2.0.2 11.2.0.3
升级步骤
1.安装11.2.0.3的软件。
2.RMAN备份原主库。
3.在备库创建restore point。
4.正常关闭主库，启动到mount，创建restore point.
5.open数据库，切换到physical standby。
6.备库切换成主库，创建restore point。
7.关闭主库，停监听。
8.修改主库上的ORACLE_HOME与相关的文件。
9.启动数据库，启动监听，运行升级脚本。
10.关闭备库与停监听
11.修改备库的ORACLE_HOME与相关文件
12.启动到mount与recover数据库
13.升级完成后，做切换测试。

下面是详细的操作步骤
1.安装11.2.0.3的软件。
      这里安装就不写了，主要使用的CLONE方式安装数据库，没有使用relink all方式。
2.RMAN 备份原主库。
   我这里只是测试步骤，所以没有做RMAN的备份。在生产数据库一定记得做RMAN备份，数据重于一切。
3.在备库创建restore point
   为什么在这里就要创建一个restore point呢？主要是预防主备使用了flashback到原来的restore point后，备库也使用flashback到这个restore point，那样，整个DG环境都不用重新搭建。
4 正常关闭主库，启动到mount，创建restore point.

• 4.1 正常关闭主库
• SQL> set lines 100
• #查看数据库的状态。
• #注意这里我启用了flashback
• SQL>  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
• #关闭数据库
• SQL> shutdown immediate;
• Database closed.
• Database dismounted.
• ORACLE instance shut down.
  

• 4.2 启动到mount
• SQL> startup mount;
• 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.
  

• 4.3 创建restore point
• SQL> create restore point update_standby_test_1 guarantee flashback database;  
•   
• Restore point created.  
  

5. open数据库，切换到physical standby。

• 5.1 open数据库
• SQL> alter database open;
• Database altered.
• 5.2 切换到physical standby
• SQL> alter database commit to switchover to physical standby with session shutdown;
• Database altered.
• 5.3 关闭数据库
• SQL> shutdown immediate;
• ORA-01092: ORACLE instance terminated. Disconnection forced
• 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
• 5.4 启动数据库到open read only
• [oracle@11gdg ~]$ sqlplus / as sysdba
• SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 10 22:44:45 2012
• Copyright (c) 1982, 2011, Oracle.  All rights reserved.
• Connected to an idle instance.
• 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.
• Database opened.
• 5.5 查询数据库的状态
• SQL> set lines 100
• SQL> 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 ONLY            ARCHIVELOG   TO PRIMARY           PHYSICAL STANDBY YES
• 已经是physical standby，一切正常
  

6 备库切换成主库，创建restore point。

• 6.1 查看备库的状态
• SQL> set lines 100
• SQL> 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 ONLY WITH APPLY ARCHIVELOG   TO PRIMARY           PHYSICAL STANDBY YES
• 6.2 创建restore point
• SQL> alter database commit to switchover to primary with session shutdown;
• Database altered.
• 6.3 切换成主库
• SQL> alter database commit to switchover to primary with session shutdown;
• Database altered.
• 6.4 查看切换后的状态
• SQL> select open_mode,log_mode,switchover_status,database_role,flashback_on from v$database;
• OPEN_MODE            LOG_MODE     SWITCHOVER_STATUS    DATABASE_ROLE    FLASHBACK_ON
• -------------------- ------------ -------------------- ---------------- ------------------
• MOUNTED              ARCHIVELOG   NOT ALLOWED          PRIMARY          YES
• 6.4  
  

7 关闭主库，停监听

• 7.1 修改参数
• #这里配置成defer是为了不让arch日志传到备库去。
• #因为如果在flashback的时候，传这些日志就浪费了。
• SQL> alter system set log_archive_dest_state_2=defer scope=spfile;
• System altered.
• 7.2 关闭数据库
• SQL> shutdown abort;
• ORACLE instance shut down.
• 7.3 停监听
• [oracle@11g ~]$ lsnrctl stop
• LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 11-SEP-2012 05:27:22
• Copyright (c) 1991, 2010, Oracle.  All rights reserved.
• Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.30)(PORT=1521)))
• The command completed successfully
• [oracle@11g ~]$ ps -ef|grep lsn
• oracle    3853  2734  0 05:27 pts/0    00:00:00 grep lsn
  

8 修改ORACLE_HOME与相关的文件

• 8.1 修改oracle_home
• [oracle@11g ~]$ 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
• 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/htz/htz/trace
• [oracle@11g ~]$ . !$
• 8.2 CP相关的文件
• [oracle@11g ~]$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/* /u01/app/oracle/product/11.2.0/db_2/dbs/
• [oracle@11g ~]$ cp -r /u01/app/oracle/product/11.2.0/db_1/network/admin/*  /u01/app/oracle/product/11.2.0/db_2/network/admin/
• 8.3 修改listener.ora文件  
• [oracle@11g ~]$ cd $ORACLE_HOME
• [oracle@11g db_2]$ cd network/admin
• [oracle@11g admin]$ cat listener.ora
• SID_LIST_LISTENER =
•   (SID_LIST =
•     (SID_DESC =
•      (GLOBAL_DBNAME = htz)
•      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_2)
•      (SID_NAME = htz)
•     )
•    )
• LISTENER =
•   (DESCRIPTION =
•     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
•   )  
• 8.4 修改/etc/oratab
• [oracle@11g admin]$ tail -1 /etc/oratab
  htz:/u01/app/oracle/product/11.2.0/db_1:N
  

9 启动数据库，启动监听，运行升级脚本。

• 9.1 启动监听
• [oracle@11g ~]$ lsnrctl start
• LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-SEP-2012 05:30:07
• 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/11g/listener/alert/log.xml
• Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.30)(PORT=1521)))
• Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.30)(PORT=1521)))
• STATUS of the LISTENER
• ------------------------
• Alias                     LISTENER
• Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
• Start Date                11-SEP-2012 05:30:07
• 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/11g/listener/alert/log.xml
• Listening Endpoints Summary...
•   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.30)(PORT=1521)))
• Services Summary...
• Service "htz" has 1 instance(s).
•   Instance "htz", status UNKNOWN, has 1 handler(s) for this service...
• The command completed successfully
• 9.2 启动数据库upgrade
• [oracle@11g ~]$ sqlplus / as sysdba
• SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 11 05:37:57 2012
• Copyright (c) 1982, 2011, Oracle.  All rights reserved.
• Connected to an idle instance.
• SQL> startup upgrade;
• ORACLE instance started.
• Total System Global Area  730714112 bytes
• Fixed Size                  2231952 bytes
• Variable Size             658506096 bytes
• Database Buffers           67108864 bytes
• Redo Buffers                2867200 bytes
• Database mounted.
• Database opened.
  

• 9.3 在升级前执行dbupgdiag脚本。
• 这个脚本可以检查很多的东西，如无效对象，用户，组件等。
• SQL> @/home/oracle/rs/sql/dbupgdiag.sql
• Enter location for Spooled output:
• Enter value for 1: /tmp/
• 这里我不CP输出内容，等升级成功后我们还得再执行一次这个脚本，那里会贴出输出内容。
• 9.4 在升级前执行utlu112i.sql
• 在升级一直执行一下这个脚本。在11G中安装数据库时默认就会有utlu11*.sql脚本，在以前的版本中
• 是没有这些脚本，要自己到metalink去下载的。如果是从10G到11G这种升级时，我们需要在10G的
• ORACLE_HOME执行一次这个脚本。
• SQL> @?/rdbms/admin/utlu112i.sql
  Oracle Database 11.2 Pre-Upgrade Information Tool 09-11-2012 05:41:57
  Script Version: 11.2.0.3.0 Build: 001
  .
  **********************************************************************
  Database:
  **********************************************************************
  --> name: HTZ
  --> version: 11.2.0.2.0
  --> compatible: 11.2.0.0.0
  --> blocksize: 8192
  --> platform: Linux x86 64-bit
  --> timezone file: V14
  .
  **********************************************************************
  Tablespaces: [make adjustments in the current environment]
  **********************************************************************
  --> SYSTEM tablespace is adequate for the upgrade.
  .... minimum required size: 710 MB
  --> SYSAUX tablespace is adequate for the upgrade.
  .... minimum required size: 485 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: ON
  **********************************************************************
  FlashbackInfo:
  --> name: /u01/app/oracle/fast_recovery_area
  --> limit: 4032 MB
  --> used: 63 MB
  --> size: 4032 MB
  --> reclaim: 23.4375 MB
  --> files: 8
  WARNING: --> Flashback Recovery Area Set. Please ensure adequate disk space in recover
  y areas before performing an upgrade.
  .
  **********************************************************************
  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: --> "memory_target" needs to be increased to at least 436 MB
  .
  
  --> If Target Oracle is 64-Bit, refer here for Update Parameters:
  WARNING: --> "memory_target" needs to be increased to at least 620 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 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 PUBLIC has 8 INVALID objects.
  .... USER CTXSYS has 1 INVALID objects.
  .... USER SYS has 11 INVALID objects.
  WARNING: --> Sync standby database prior to upgrade.
  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 removing all hidden parameters prior to upgrading.
  
  To view existing hidden parameters execute the following command
  while connected AS SYSDBA:
  
  SELECT name,description from SYS.V$PARAMETER WHERE name
  LIKE '\_%' ESCAPE '\'
  
  Changes will need to be made in the init.ora or spfile.
  
  **********************************************************************
  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.
  
  **********************************************************************
• 9.5 执行catupgrd.sql升级脚本
• 执行完后会正常的关闭数据库
• SQL> spool /tmp/upgrade1.log
• SQL> @?/rdbms/admin/catupgrd.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
• #检查/tmp/upgrade1.log文件的内容，查看是否有报错。如果有错误，我们
• #可以再次执行catupgrd.sql这个脚本，执行再多次都可以。如果还不成功，
• #我们可以通过DBUA这个来执行，我有一次就是执行catupgrd.sql执行了3次
• #还是有部分组件升级失败，但是又没有报错，最后通过dbua来升级成功
• 9.6 正常启动数据库
• [oracle@11g ~]$ sqlplus / as sysdba
• SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 11 07:09:11 2012
• Copyright (c) 1982, 2011, Oracle. All rights reserved.
• Connected to an idle instance.
• SQL> startup
• ORACLE instance started.
• Total System Global Area 730714112 bytes
• Fixed Size 2231952 bytes
• Variable Size 721420656 bytes
• Database Buffers 4194304 bytes
• Redo Buffers 2867200 bytes
• Database mounted.
• Database opened.
• #查看数据库的状态
• SQL> select open_mode,log_mode,switchover_status,database_role,flashback_on from v$database;
• SQL>
• SQL> set lines 100
• SQL> 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 NOT ALLOWED PRIMARY YES
• 9.7 查看组件是否升级成功
• SQL> @?/rdbms/admin/utlu112s.sql
• .
• Oracle Database 11.2 Post-Upgrade Status Tool 09-11-2012 07:12:17
• .
• Component Current Version Elapsed Time
• Name Status Number HH:MM:SS
• .
• Oracle Server
• . VALID 11.2.0.3.0 00:16:26
• JServer JAVA Virtual Machine
• . VALID 11.2.0.3.0 00:03:01
• Oracle Workspace Manager
• . VALID 11.2.0.3.0 00:00:49
• OLAP Analytic Workspace
• . VALID 11.2.0.3.0 00:01:26
• OLAP Catalog
• . VALID 11.2.0.3.0 00:00:59
• Oracle OLAP API
• . VALID 11.2.0.3.0 00:01:01
• Oracle Enterprise Manager
• . VALID 11.2.0.3.0 00:03:50
• Oracle XDK
• . VALID 11.2.0.3.0 00:00:00
• Oracle Text
• . VALID 11.2.0.3.0 00:01:13
• Oracle XML Database
• . VALID 11.2.0.3.0 00:04:30
• Oracle Database Java Packages
• . VALID 11.2.0.3.0 00:00:34
• Oracle Multimedia
• . VALID 11.2.0.3.0 00:10:49
• Spatial
• . VALID 11.2.0.3.0 00:03:44
• Oracle Expression Filter
• . VALID 11.2.0.3.0 00:00:14
• Oracle Rules Manager
• . VALID 11.2.0.3.0 00:00:13
• Oracle Application Express
• . VALID 3.2.1.00.12
• Gathering Statistics
• . 00:02:39
• Total Upgrade Time: 00:51:40
• PL/SQL procedure successfully completed.
• 全部升级成功。这里也可以查看每个部件升级时所用的时间。我这里一共用了1个小时。
• 9.8 查看升级后的无效对象
• 由于我这里的无效对象太多，就不全部贴出了
• SQL> @?/rdbms/admin/utluiobj.sql
• .
• Oracle Database 11.1 Post-Upgrade Invalid Objects Tool 09-11-2012 07:13:39
• .
• This tool lists post-upgrade invalid objects that were not invalid
• prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).
• .
• Owner Object Name Object Type
• .
• SYS ALL$OLAP2_AWS VIEW
• SYS ALL_ALL_TABLES VIEW
• SYS ALL_APPLY VIEW
• SYS ALL_APPLY_CHANGE_HANDLERS VIEW
• SYS ALL_APPLY_CONFLICT_COLUMNS VIEW
• SYS ALL_APPLY_DML_CONF_COLUMNS VIEW
• SYS ALL_APPLY_DML_CONF_HANDLERS VIEW
• SYS ALL_APPLY_DML_HANDLERS VIEW
• SYS ALL_APPLY_ENQUEUE VIEW
• SYS ALL_APPLY_ERROR VIEW
• SYS ALL_APPLY_ERROR_MESSAGES VIEW
• SYS ALL_APPLY_EXECUTE VIEW
• SYS ALL_APPLY_HANDLE_COLLISIONS VIEW
• [oracle@11g tmp]$ wc -l utluiobj.log
• 6124 utluiobj.log
• 9.9 执行catuppst.sql脚本
• SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql;
• TIMESTAMP
• ----------------------------------------------------------------------------------------------------
• COMP_TIMESTAMP POSTUP_BGN 2012-09-11 07:15:55
• PL/SQL procedure successfully completed.
• ...........................................
• 9.10 编译无效对象
• SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql;
• TIMESTAMP
• ------------------------------------------------------------------------------------------------------------------------------------------------------
• COMP_TIMESTAMP UTLRP_BGN 2012-09-11 07:29:36
• 1 row selected.
• DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
• DOC> objects in the database. Recompilation time is proportional to the
• DOC> number of invalid objects in the database, so this command may take
• DOC> a long time to execute on a database with a large number of invalid
• DOC> objects.
• DOC>
• DOC> Use the following queries to track recompilation progress:
• DOC>
• DOC> 1. Query returning the number of invalid objects remaining. This
• DOC> number should decrease with time.
• DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
• DOC>
• DOC> 2. Query returning the number of objects compiled so far. This number
• DOC> should increase with time.
• DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
• DOC>
• DOC> This script automatically chooses serial or parallel recompilation
• DOC> based on the number of CPUs available (parameter cpu_count) multiplied
• DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
• DOC> On RAC, this number is added across all RAC nodes.
• DOC>
• DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
• DOC> recompilation. Jobs are created without instance affinity so that they
• DOC> can migrate across RAC nodes. Use the following queries to verify
• DOC> whether UTL_RECOMP jobs are being created and run correctly:
• DOC>
• DOC> 1. Query showing jobs created by UTL_RECOMP
• DOC> SELECT job_name FROM dba_scheduler_jobs
• DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
• DOC>
• DOC> 2. Query showing UTL_RECOMP jobs that are running
• DOC> SELECT job_name FROM dba_scheduler_running_jobs
• DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
• DOC>#
• PL/SQL procedure successfully completed.
• TIMESTAMP
• ------------------------------------------------------------------------------------------------------------------------------------------------------
• COMP_TIMESTAMP UTLRP_END 2012-09-11 07:37:26
• 1 row selected.
• DOC> The following query reports the number of objects that have compiled
• DOC> with errors (objects that compile with errors have status set to 3 in
• DOC> obj$). If the number is higher than expected, please examine the error
• DOC> messages reported with each object (using SHOW ERRORS) to see if they
• DOC> point to system misconfiguration or resource constraints that must be
• DOC> fixed before attempting to recompile these objects.
• DOC>#
• OBJECTS WITH ERRORS
• -------------------
• 0
• 1 row selected.
• DOC> The following query reports the number of errors caught during
• DOC> recompilation. If this number is non-zero, please query the error
• DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
• DOC> are due to misconfiguration or resource constraints that must be
• DOC> fixed before objects can compile successfully.
• DOC>#
• ERRORS DURING RECOMPILATION
• ---------------------------
• 0
• 1 row selected.
• Function created.
• PL/SQL procedure successfully completed.
• Function dropped.
• PL/SQL procedure successfully completed.
• 到这里主库的升级就完成了。 修改log_archive_dest_state_2参数为enable
  
  
  • SQL> alter system set log_archive_dest_state_2=enable;
  • System altered.
  • 从备库的alert日志中我们可以看到下面的输出
  • Mon Sep 10 10:00:53 2012
  • Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/arch/htz/
  • ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;
  • Mon Sep 10 10:04:12 2012
  • Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/arch/htz/
  • ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;
  • ALTER SYSTEM SET log_archive_dest_2='service="htz"','LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="htz" net_timeout=30','valid_for=(all_logfiles,primary_role)' SCOPE=BOTH;
  • Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/arch/htz/
  • ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
  • ALTER SYSTEM ARCHIVE LOG
  • Mon Sep 10 10:09:24 2012
  • Primary database is in MAXIMUM AVAILABILITY mode
  • Changing standby controlfile to RESYNCHRONIZATION level
  • Standby controlfile consistent with primary
  • RFS[4]: Assigned to RFS process 3958
  • RFS[4]: Selected log 4 for thread 1 sequence 85 dbid 1848107928 branch 793318533
  • Mon Sep 10 10:09:27 2012
  • RFS[5]: Assigned to RFS process 3962
  • RFS[5]: Selected log 5 for thread 1 sequence 84 dbid 1848107928 branch 793318533
  • Mon Sep 10 10:09:30 2012
  • Archived Log entry 96 added for thread 1 sequence 84 ID 0x6e2ccafb dest 1:
  • RFS[5]: Opened log for thread 1 sequence 34 dbid 1848107928 branch 793318533
  • Archived Log entry 97 added for thread 1 sequence 34 rlc 793318533 ID 0x6e2ccafb dest 2:
  • Mon Sep 10 10:09:31 2012
  • RFS[6]: Assigned to RFS process 3966
  • RFS[6]: Opened log for thread 1 sequence 33 dbid 1848107928 branch 793318533
  • Archived Log entry 98 added for thread 1 sequence 33 rlc 793318533 ID 0x6e2ccafb dest 2:
  • Mon Sep 10 10:09:32 2012
  • RFS[7]: Assigned to RFS process 3970
  • RFS[7]: Opened log for thread 1 sequence 35 dbid 1848107928 branch 793318533
  • RFS[6]: Opened log for thread 1 sequence 37 dbid 1848107928 branch 793318533
  • RFS[5]: Opened log for thread 1 sequence 36 dbid 1848107928 branch 793318533
  • Archived Log entry 99 added for thread 1 sequence 35 rlc 793318533 ID 0x6e2ccafb dest 2:
  • Archived Log entry 100 added for thread 1 sequence 37 rlc 793318533 ID 0x6e2ccafb dest 2:
  • RFS[7]: Opened log for thread 1 sequence 38 dbid 1848107928 branch 793318533
  • Mon Sep 10 10:09:39 2012
  • Archived Log entry 101 added for thread 1 sequence 36 rlc 793318533 ID 0x6e2ccafb dest 2:
  • Archived Log entry 102 added for thread 1 sequence 38 rlc 793318533 ID 0x6e2ccafb dest 2:
  • RFS[6]: Opened log for thread 1 sequence 39 dbid 1848107928 branch 793318533
  • RFS[7]: Opened log for thread 1 sequence 41 dbid 1848107928 branch 793318533
  • RFS[5]: Opened log for thread 1 sequence 40 dbid 1848107928 branch 793318533
  • Mon Sep 10 10:09:46 2012
  • Archived Log entry 103 added for thread 1 sequence 41 rlc 793318533 ID 0x6e2ccafb dest 2:
  • Mon Sep 10 10:09:46 2012
  • Archived Log entry 104 added for thread 1 sequence 39 rlc 793318533 ID 0x6e2ccafb dest 2:
  • 升级完成后我们再次执行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
    • ================
    • 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
    • ================
    •         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>################################################################
    • 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                                                                         5701
    • AQ$_SCHEDULES_PRIMARY                    INDEX                                                                         5702
    • DBMS_REPCAT_AUTH                         PACKAGE                                                                       8568
    • DBMS_REPCAT_AUTH                         PACKAGE BODY                                                                 12144
    • 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
    • ================
    • 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
    •                             *** 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)))
    • Connecting to (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...
    • The command completed successfully
      
    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
    • Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    • Connected to:
    • Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    • With the Partitioning, OLAP, Data Mining and Real Application Testing options
    • 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;
    • ORA-01092: ORACLE instance terminated. Disconnection forced
    • 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
    • With the Partitioning, OLAP, Data Mining and Real Application Testing options
    • [oracle@11g ~]$ sqlplus  / as sysdba
    • SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 11 18:26:13 2012
    • Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    • Connected to an idle instance.
    •      
    • > startup
    • ORACLE instance started.
    • Total System Global Area  313159680 bytes
    • Fixed Size                  2227944 bytes
    • Variable Size             239075608 bytes
    • Database Buffers           67108864 bytes
    • Redo Buffers                4747264 bytes
    • Database mounted.
    • Database opened.
    • > set lines 100
    • > 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 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;
    • OPEN_MODE            LOG_MODE     SWITCHOVER_STATUS    DATABASE_ROLE    FLASHBACK_ON
    • -------------------- ------------ -------------------- ---------------- ------------------
    • READ ONLY WITH APPLY ARCHIVELOG   TO PRIMARY           PHYSICAL STANDBY YES
    • SQL> alter database commit to switchover to primary with session shutdown;
    • Database altered.
    • SQL> select open_mode,log_mode,switchover_status,database_role,flashback_on from v$database;
    • OPEN_MODE            LOG_MODE     SWITCHOVER_STATUS    DATABASE_ROLE    FLASHBACK_ON
    • -------------------- ------------ -------------------- ---------------- ------------------
    • MOUNTED              ARCHIVELOG   NOT ALLOWED          PRIMARY          YES
    • SQL> alter database open;
    • Database altered.
    • SQL> 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
      
    整个升级过程已经完成。
    后面将会介绍临时的logical standby database升级方式。
    
    
  
  
  

outlook

这是什么东东啊

liyeho

做爱做的事，交配交的人。

hongmeigui22027

路过，支持一下啦

dew

月经不仅仅是女人的痛苦，也是男人的痛苦。

4591566

谢谢楼主，共同发展

lchl0388

如果有一双眼睛陪我一同哭泣，就值得我为生命受苦。