11GR2 DATAGUARD环境下的DATABASE升级(11.2.0.2升级到11.2.0.3)(1)

http://luoping.blog.51cto.com/534596/988073

11GR2 DATAGUARD环境下的DATABASE升级(11.2.0.2升级到11.2.0.3)(2)

http://luoping.blog.51cto.com/534596/988108

11GR2 DATAGUARD环境下的DATABASE升级(11.2.0.2升级到11.2.0.3)(3)

http://luoping.blog.51cto.com/534596/988024

  1. 9.3  在升级前执行dbupgdiag脚本。 
  2. 这个脚本可以检查很多的东西,如无效对象,用户,组件等。  
  3. SQL> @/home/oracle/rs/sql/dbupgdiag.sql 
  4.  
  5. Enter location for Spooled output
  6.  
  7. Enter value for 1: /tmp/ 
  8. 这里我不CP输出内容,等升级成功后我们还得再执行一次这个脚本,那里会贴出输出内容。 
  9. 9.4 在升级前执行utlu112i.sql
  10. 在升级一直执行一下这个脚本。在11G中安装数据库时默认就会有utlu11*.sql脚本,在以前的版本中
  11. 是没有这些脚本,要自己到metalink去下载的。如果是从10G到11G这种升级时,我们需要在10G的
  12. ORACLE_HOME执行一次这个脚本。
  13. 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.

    **********************************************************************
  14.  
  15. 9.5 执行catupgrd.sql升级脚本 
  16. 执行完后会正常的关闭数据库 
  17. SQL> spool /tmp/upgrade1.log 
  18. SQL> @?/rdbms/admin/catupgrd.sql; 
  19. ...................... 
  20. SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql. 
  21. SQL> REM                This forces user to start a new sqlplus session in order 
  22. SQL> REM                to connect to the upgraded db. 
  23. SQL> exit 
  24. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production 
  25. With the Partitioning, OLAP, Data Mining and Real Application Testing options 
  26.  
  27. #检查/tmp/upgrade1.log文件的内容,查看是否有报错。如果有错误,我们 
  28. #可以再次执行catupgrd.sql这个脚本,执行再多次都可以。如果还不成功, 
  29. #我们可以通过DBUA这个来执行,我有一次就是执行catupgrd.sql执行了3次 
  30. #还是有部分组件升级失败,但是又没有报错,最后通过dbua来升级成功 
  31. 9.6 正常启动数据库 
  32. [oracle@11g ~]$ sqlplus / as sysdba 
  33.  
  34. SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 11 07:09:11 2012 
  35.  
  36. Copyright (c) 1982, 2011, Oracle.  All rights reserved. 
  37.  
  38. Connected to an idle instance. 
  39.  
  40. SQL> startup 
  41. ORACLE instance started. 
  42.  
  43. Total System Global Area  730714112 bytes 
  44. Fixed Size                  2231952 bytes 
  45. Variable Size             721420656 bytes 
  46. Database Buffers            4194304 bytes 
  47. Redo Buffers                2867200 bytes 
  48. Database mounted. 
  49. Database opened. 
  50. #查看数据库的状态 
  51. SQL> select open_mode,log_mode,switchover_status,database_role,flashback_on from v$database
  52.  
  53. SQL>  
  54. SQL> set lines 100 
  55. SQL> select open_mode,log_mode,switchover_status,database_role,flashback_on from v$database
  56.  
  57. OPEN_MODE            LOG_MODE     SWITCHOVER_STATUS    DATABASE_ROLE    FLASHBACK_ON 
  58. -------------------- ------------ -------------------- ---------------- ------------------ 
  59. READ WRITE           ARCHIVELOG   NOT ALLOWED          PRIMARY          YES 
  60. 9.7 查看组件是否升级成功 
  61. SQL> @?/rdbms/admin/utlu112s.sql 
  62. Oracle Database 11.2 Post-Upgrade Status Tool           09-11-2012 07:12:17 
  63. Component                               Current      Version     Elapsed Time 
  64. Name                                    Status       Number      HH:MM:SS 
  65. Oracle Server 
  66. .                                         VALID      11.2.0.3.0  00:16:26 
  67. JServer JAVA Virtual Machine 
  68. .                                         VALID      11.2.0.3.0  00:03:01 
  69. Oracle Workspace Manager 
  70. .                                         VALID      11.2.0.3.0  00:00:49 
  71. OLAP Analytic Workspace 
  72. .                                         VALID      11.2.0.3.0  00:01:26 
  73. OLAP Catalog 
  74. .                                         VALID      11.2.0.3.0  00:00:59 
  75. Oracle OLAP API 
  76. .                                         VALID      11.2.0.3.0  00:01:01 
  77. Oracle Enterprise Manager 
  78. .                                         VALID      11.2.0.3.0  00:03:50 
  79. Oracle XDK 
  80. .                                         VALID      11.2.0.3.0  00:00:00 
  81. Oracle Text 
  82. .                                         VALID      11.2.0.3.0  00:01:13 
  83. Oracle XML Database 
  84. .                                         VALID      11.2.0.3.0  00:04:30 
  85. Oracle Database Java Packages 
  86. .                                         VALID      11.2.0.3.0  00:00:34 
  87. Oracle Multimedia 
  88. .                                         VALID      11.2.0.3.0  00:10:49 
  89. Spatial 
  90. .                                         VALID      11.2.0.3.0  00:03:44 
  91. Oracle Expression Filter 
  92. .                                         VALID      11.2.0.3.0  00:00:14 
  93. Oracle Rules Manager 
  94. .                                         VALID      11.2.0.3.0  00:00:13 
  95. Oracle Application Express 
  96. .                                         VALID     3.2.1.00.12 
  97. Gathering Statistics 
  98. .                                                                00:02:39 
  99. Total Upgrade Time: 00:51:40 
  100.  
  101. PL/SQL procedure successfully completed. 
  102.  
  103. 全部升级成功。这里也可以查看每个部件升级时所用的时间。我这里一共用了1个小时。 
  104. 9.8 查看升级后的无效对象 
  105. 由于我这里的无效对象太多,就不全部贴出了 
  106. SQL> @?/rdbms/admin/utluiobj.sql 
  107. Oracle Database 11.1 Post-Upgrade Invalid Objects Tool 09-11-2012 07:13:39 
  108. This tool lists post-upgrade invalid objects that were not invalid 
  109. prior to upgrade (it ignores pre-existing pre-upgrade invalid objects). 
  110. Owner                     Object Name                     Object Type 
  111. SYS                   ALL$OLAP2_AWS                            VIEW 
  112. SYS                  ALL_ALL_TABLES                            VIEW 
  113. SYS                       ALL_APPLY                            VIEW 
  114. SYS       ALL_APPLY_CHANGE_HANDLERS                            VIEW 
  115. SYS      ALL_APPLY_CONFLICT_COLUMNS                            VIEW 
  116. SYS      ALL_APPLY_DML_CONF_COLUMNS                            VIEW 
  117. SYS     ALL_APPLY_DML_CONF_HANDLERS                            VIEW 
  118. SYS          ALL_APPLY_DML_HANDLERS                            VIEW 
  119. SYS               ALL_APPLY_ENQUEUE                            VIEW 
  120. SYS                 ALL_APPLY_ERROR                            VIEW 
  121. SYS        ALL_APPLY_ERROR_MESSAGES                            VIEW 
  122. SYS               ALL_APPLY_EXECUTE                            VIEW 
  123. SYS     ALL_APPLY_HANDLE_COLLISIONS                            VIEW 
  124.  
  125. [oracle@11g tmp]$ wc -l utluiobj.log 
  126. 6124 utluiobj.log 
  127.  
  128. 9.9 执行catuppst.sql脚本 
  129. SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql; 
  130.  
  131. TIMESTAMP 
  132. ---------------------------------------------------------------------------------------------------- 
  133. COMP_TIMESTAMP POSTUP_BGN 2012-09-11 07:15:55 
  134.  
  135.  
  136. PL/SQL procedure successfully completed. 
  137.  
  138. ........................................... 
  139. 9.10 编译无效对象 
  140. SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql; 
  141.  
  142. TIMESTAMP 
  143. ------------------------------------------------------------------------------------------------------------------------------------------------------ 
  144. COMP_TIMESTAMP UTLRP_BGN  2012-09-11 07:29:36 
  145.  
  146. 1 row selected. 
  147.  
  148. DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid 
  149. DOC>   objects in the database. Recompilation time is proportional to the 
  150. DOC>   number of invalid objects in the database, so this command may take 
  151. DOC>   a long time to execute on a database with a large number of invalid 
  152. DOC>   objects. 
  153. DOC> 
  154. DOC>   Use the following queries to track recompilation progress: 
  155. DOC> 
  156. DOC>   1. Query returning the number of invalid objects remaining. This 
  157. DOC>      number should decrease with time
  158. DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); 
  159. DOC> 
  160. DOC>   2. Query returning the number of objects compiled so far. This number 
  161. DOC>      should increase with time
  162. DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; 
  163. DOC> 
  164. DOC>   This script automatically chooses serial or parallel recompilation 
  165. DOC>   based on the number of CPUs available (parameter cpu_count) multiplied 
  166. DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu). 
  167. DOC>   On RAC, this number is added across all RAC nodes. 
  168. DOC> 
  169. DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel 
  170. DOC>   recompilation. Jobs are created without instance affinity so that they 
  171. DOC>   can migrate across RAC nodes. Use the following queries to verify 
  172. DOC>   whether UTL_RECOMP jobs are being created and run correctly: 
  173. DOC> 
  174. DOC>   1. Query showing jobs created by UTL_RECOMP 
  175. DOC>         SELECT job_name FROM dba_scheduler_jobs 
  176. DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%'
  177. DOC> 
  178. DOC>   2. Query showing UTL_RECOMP jobs that are running 
  179. DOC>         SELECT job_name FROM dba_scheduler_running_jobs 
  180. DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%'
  181. DOC># 
  182.  
  183.  
  184.  
  185. PL/SQL procedure successfully completed. 
  186.  
  187.  
  188. TIMESTAMP 
  189. ------------------------------------------------------------------------------------------------------------------------------------------------------ 
  190. COMP_TIMESTAMP UTLRP_END  2012-09-11 07:37:26 
  191.  
  192. 1 row selected. 
  193.  
  194. DOC> The following query reports the number of objects that have compiled 
  195. DOC> with errors (objects that compile with errors have status set to 3 in 
  196. DOC> obj$). If the number is higher than expected, please examine the error 
  197. DOC> messages reported with each object (using SHOW ERRORS) to see if they 
  198. DOC> point to system misconfiguration or resource constraints that must be 
  199. DOC> fixed before attempting to recompile these objects. 
  200. DOC># 
  201.  
  202. OBJECTS WITH ERRORS 
  203. ------------------- 
  204.                   0 
  205.  
  206. 1 row selected. 
  207.  
  208. DOC> The following query reports the number of errors caught during 
  209. DOC> recompilation. If this number is non-zero, please query the error 
  210. DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors 
  211. DOC> are due to misconfiguration or resource constraints that must be 
  212. DOC> fixed before objects can compile successfully. 
  213. DOC># 
  214.  
  215. ERRORS DURING RECOMPILATION 
  216. --------------------------- 
  217.                           0 
  218.  
  219. 1 row selected. 
  220.  
  221.  
  222. Function created. 
  223.  
  224.  
  225. PL/SQL procedure successfully completed. 
  226.  
  227.  
  228. Function dropped. 
  229.  
  230.  
  231. PL/SQL procedure successfully completed. 
到这里主库的升级就完成了。

 修改log_archive_dest_state_2参数为enable

  1. SQL> alter system set log_archive_dest_state_2=enable; 
  2.  
  3. System altered. 
  4.  
  5. 从备库的alert日志中我们可以看到下面的输出 
  6. Mon Sep 10 10:00:53 2012 
  7. Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/arch/htz/ 
  8. ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH; 
  9. Mon Sep 10 10:04:12 2012 
  10. Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/arch/htz/ 
  11. ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH; 
  12. 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; 
  13. Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/arch/htz/ 
  14. ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH; 
  15. ALTER SYSTEM ARCHIVE LOG 
  16. Mon Sep 10 10:09:24 2012 
  17. Primary database is in MAXIMUM AVAILABILITY mode 
  18. Changing standby controlfile to RESYNCHRONIZATION level 
  19. Standby controlfile consistent with primary 
  20. RFS[4]: Assigned to RFS process 3958 
  21. RFS[4]: Selected log 4 for thread 1 sequence 85 dbid 1848107928 branch 793318533 
  22. Mon Sep 10 10:09:27 2012 
  23. RFS[5]: Assigned to RFS process 3962 
  24. RFS[5]: Selected log 5 for thread 1 sequence 84 dbid 1848107928 branch 793318533 
  25. Mon Sep 10 10:09:30 2012 
  26. Archived Log entry 96 added for thread 1 sequence 84 ID 0x6e2ccafb dest 1: 
  27. RFS[5]: Opened log for thread 1 sequence 34 dbid 1848107928 branch 793318533 
  28. Archived Log entry 97 added for thread 1 sequence 34 rlc 793318533 ID 0x6e2ccafb dest 2: 
  29. Mon Sep 10 10:09:31 2012 
  30. RFS[6]: Assigned to RFS process 3966 
  31. RFS[6]: Opened log for thread 1 sequence 33 dbid 1848107928 branch 793318533 
  32. Archived Log entry 98 added for thread 1 sequence 33 rlc 793318533 ID 0x6e2ccafb dest 2: 
  33. Mon Sep 10 10:09:32 2012 
  34. RFS[7]: Assigned to RFS process 3970 
  35. RFS[7]: Opened log for thread 1 sequence 35 dbid 1848107928 branch 793318533 
  36. RFS[6]: Opened log for thread 1 sequence 37 dbid 1848107928 branch 793318533 
  37. RFS[5]: Opened log for thread 1 sequence 36 dbid 1848107928 branch 793318533 
  38. Archived Log entry 99 added for thread 1 sequence 35 rlc 793318533 ID 0x6e2ccafb dest 2: 
  39. Archived Log entry 100 added for thread 1 sequence 37 rlc 793318533 ID 0x6e2ccafb dest 2: 
  40. RFS[7]: Opened log for thread 1 sequence 38 dbid 1848107928 branch 793318533 
  41. Mon Sep 10 10:09:39 2012 
  42. Archived Log entry 101 added for thread 1 sequence 36 rlc 793318533 ID 0x6e2ccafb dest 2: 
  43. Archived Log entry 102 added for thread 1 sequence 38 rlc 793318533 ID 0x6e2ccafb dest 2: 
  44. RFS[6]: Opened log for thread 1 sequence 39 dbid 1848107928 branch 793318533 
  45. RFS[7]: Opened log for thread 1 sequence 41 dbid 1848107928 branch 793318533 
  46. RFS[5]: Opened log for thread 1 sequence 40 dbid 1848107928 branch 793318533 
  47. Mon Sep 10 10:09:46 2012 
  48. Archived Log entry 103 added for thread 1 sequence 41 rlc 793318533 ID 0x6e2ccafb dest 2: 
  49. Mon Sep 10 10:09:46 2012 
  50. Archived Log entry 104 added for thread 1 sequence 39 rlc 793318533 ID 0x6e2ccafb dest 2: