开启辅助访问

Bo's Oracle Station

Bo's Oracle Station»社区 › 培训进行时（结课后图标变灰，但是欢迎继续发帖！） › 12017-12c管理类OCP认证班第二期 › 升级数据库的过程
返回列表 发新帖
查看: 1911|回复: 0

升级数据库的过程

[复制链接]
botang

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2020-12-10 21:08:25 | 显示全部楼层 |阅读模式
11.2.0.3上：
  1. [oracle@classroom ~]$ . oraenv
  2. ORACLE_SID = [orcl] ? rcat
  3. The Oracle base remains unchanged with value /u01/app/oracle
  4. [oracle@classroom ~]$ sqlplus /nolog

  6. SQL*Plus: Release 11.2.0.3.0 Production on 星期四 12月 10 21:00:30 2020

  8. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  10. SQL> conn / as sysdba
  11. Connected to an idle instance.
  12. SQL> startup
  13. ORACLE instance started.

  15. Total System Global Area 1603411968 bytes
  16. Fixed Size                    2228784 bytes
  17. Variable Size                  503320016 bytes
  18. Database Buffers         1090519040 bytes
  19. Redo Buffers                    7344128 bytes
  20. Database mounted.
  21. Database opened.
  22. SQL> @/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/preupgrd.sql






  29. Loading Pre-Upgrade Package...


  32. ***************************************************************************
  33. Executing Pre-Upgrade Checks in RCAT...
  34. ***************************************************************************


  37.       ************************************************************

  39.                    ====>> ERRORS FOUND for RCAT <<====

  41. The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
  42.                     prior to attempting your upgrade.
  43.             Failure to do so will result in a failed upgrade.


  46. 1) Check Tag:          PURGE_RECYCLEBIN
  47.     Check Summary: Check that recycle bin is empty prior to upgrade
  48.     Fixup Summary:
  49.      "The recycle bin will be purged."

  51.             You MUST resolve the above error prior to upgrade

  53.       ************************************************************

  55.       ************************************************************

  57.                ====>> PRE-UPGRADE RESULTS for RCAT <<====

  59. ACTIONS REQUIRED:

  61. 1. Review results of the pre-upgrade checks:
  62. /u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade.log

  64. 2. Execute in the SOURCE environment BEFORE upgrade:
  65. /u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade_fixups.sql

  67. 3. Execute in the NEW environment AFTER upgrade:
  68. /u01/app/oracle/cfgtoollogs/rcat/preupgrade/postupgrade_fixups.sql

  70.       ************************************************************

  72. ***************************************************************************
  73. Pre-Upgrade Checks in RCAT Completed.
  74. ***************************************************************************

  76. ***************************************************************************
  77. ***************************************************************************
  78. SQL>
复制代码
  1. SQL> @/u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade_fixups.sql
  2. Pre-Upgrade Fixup Script Generated on 2020-12-10 21:07:56  Version: 12.1.0.2 Build: 006
  3. Beginning Pre-Upgrade Fixups...
  4. Executing in container RCAT

  6. **********************************************************************
  7. Check Tag:     EM_PRESENT
  8. Check Summary: Check if Enterprise Manager is present
  9. Fix Summary:   Execute emremove.sql prior to upgrade.
  10. **********************************************************************
  11. Fixup Returned Information:
  12. WARNING: --> Enterprise Manager Database Control repository found in the database

  14.      In Oracle Database 12c, Database Control is removed during
  15.      the upgrade. To save time during the Upgrade, this action
  16.      can be done prior to upgrading using the following steps after
  17.      copying rdbms/admin/emremove.sql from the new Oracle home
  18.    - Stop EM Database Control:
  19.     [        DISCUZ_CODE_2        ]gt; emctl stop dbconsole

  21.    - Connect to the Database using the SYS account AS SYSDBA:

  23.    SET ECHO ON;
  24.    SET SERVEROUTPUT ON;
  25.    @emremove.sql
  26.      Without the set echo and serveroutput commands you will not
  27.      be able to follow the progress of the script.
  28. **********************************************************************


  31. **********************************************************************
  32. Check Tag:     AMD_EXISTS
  33. Check Summary: Check to see if AMD is present in the database
  34. Fix Summary:   Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
  35. **********************************************************************
  36. Fixup Returned Information:
  37. INFORMATION: --> OLAP Catalog(AMD) exists in database

  39.      Starting with Oracle Database 12c, OLAP Catalog component is desupported.
  40.      If you are not using the OLAP Catalog component and want
  41.      to remove it, then execute the
  42.      ORACLE_HOME/olap/admin/catnoamd.sql script before or
  43.      after the upgrade.
  44. **********************************************************************


  47. **********************************************************************
  48. Check Tag:     PURGE_RECYCLEBIN
  49. Check Summary: Check that recycle bin is empty prior to upgrade
  50. Fix Summary:   The recycle bin will be purged.
  51. **********************************************************************
  52. Fixup Succeeded
  53. **********************************************************************


  56. **********************************************************************
  57. Check Tag:     APEX_UPGRADE_MSG
  58. Check Summary: Check that APEX will need to be upgraded.
  59. Fix Summary:   Oracle Application Express can be manually upgraded prior to database upgrade.
  60. **********************************************************************
  61. Fixup Returned Information:
  62. INFORMATION: --> Oracle Application Express (APEX) can be
  63.      manually upgraded prior to database upgrade

  65.      APEX is currently at version 3.2.1.00.10 and will need to be
  66.      upgraded to APEX version 4.2.5 in the new release.
  67.      Note 1: To reduce database upgrade time, APEX can be manually
  68.              upgraded outside of and prior to database upgrade.
  69.      Note 2: See MOS Note 1088970.1 for information on APEX
  70.              installation upgrades.
  71. **********************************************************************


  74. **********************************************************************
  75.                       [Pre-Upgrade Recommendations]
  76. **********************************************************************

  78.                         *****************************************
  79.                         ********* Dictionary Statistics *********
  80.                         *****************************************

  82. Please gather dictionary statistics 24 hours prior to
  83. upgrading the database.
  84. To gather dictionary statistics execute the following command
  85. while connected as SYSDBA:
  86.     EXECUTE dbms_stats.gather_dictionary_stats;

  88. ^^^ MANUAL ACTION SUGGESTED ^^^


  91.            **************************************************
  92.                 ************* Fixup Summary ************

  94. 1 fixup routine was successful.
  95. 3 fixup routines returned INFORMATIONAL text that should be reviewed.

  97. **************** Pre-Upgrade Fixup Script Complete *********************
  98. SQL>
复制代码
  1. SQL> shutdown immediate
  2. Database closed.
  3. Database dismounted.
  4. ORACLE instance shut down.
  5. SQL>
复制代码


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

  1. #Backup file is  /u01/app/oracle/product/12.1.0/grid/srvm/admin/oratab.bak.classroom line added by Agent
  2. #



  6. # This file is used by ORACLE utilities.  It is created by root.sh
  7. # and updated by either Database Configuration Assistant while creating
  8. # a database or ASM Configuration Assistant while creating ASM instance.

  10. # A colon, ':', is used as the field terminator.  A new line terminates
  11. # the entry.  Lines beginning with a pound sign, '#', are comments.
  12. #
  13. # Entries are of the form:
  14. #   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
  15. #
  16. # The first and second fields are the system identifier and home
  17. # directory of the database respectively.  The third field indicates
  18. # to the dbstart utility that the database should , "Y", or should not,
  19. # "N", be brought up at system boot time.
  20. #
  21. # Multiple entries with the same $ORACLE_SID are not allowed.
  22. #
  23. #
  24. em12rep:/u01/app/oracle/product/12.1.0/dbhome_1:N               # line added by Agent
  25. orcl2:/u01/app/oracle/product/12.1.0/dbhome_1:N         # line added by Agent
  26. orcl:/u01/app/oracle/product/12.1.0/dbhome_2:N          # line added by Agent
  27. cdb1:/u01/app/oracle/product/12.1.0/dbhome_1:N          # line added by Agent
  28. rcat:/u01/app/oracle/product/12.1.0/dbhome_1:N          # line added by Agent
  29. +ASM:/u01/app/oracle/product/12.1.0/grid:N              # line added by Agen                                                                              
复制代码
  1. [oracle@classroom ~]$ . oraenv
  2. ORACLE_SID = [rcat] ? rcat
  3. The Oracle base remains unchanged with value /u01/app/oracle
  4. [oracle@classroom ~]$ which sqlplus
  5. /u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus
  6. [oracle@classroom ~]$ sqlplus /nolog

  8. SQL*Plus: Release 12.1.0.2.0 Production on 星期四 12月 10 21:17:28 2020

  10. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  12. SQL> conn / as sysdba
  13. Connected to an idle instance.
  14. SQL> startup upgrade
  15. ORA-01078: failure in processing system parameters
  16. LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initrcat.ora'
  17. SQL>
复制代码
  1. [oracle@classroom ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilercat.ora  /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilercat.ora
  2. [oracle@classroom ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwrcat /u01/app/oracle/product/12.1.0/dbhome_1/dbs/
  3. [oracle@classroom ~]$
复制代码
  1. [oracle@classroom ~]$ sqlplus /nolog

  3. SQL*Plus: Release 12.1.0.2.0 Production on 星期四 12月 10 21:24:59 2020

  5. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  7. SQL> conn / as sysdba
  8. Connected to an idle instance.
  9. SQL> startup upgrade
  10. ORACLE instance started.

  12. Total System Global Area 1610612736 bytes
  13. Fixed Size                    2924928 bytes
  14. Variable Size                  671092352 bytes
  15. Database Buffers          922746880 bytes
  16. Redo Buffers                   13848576 bytes
  17. Database mounted.
  18. Database opened.
  19. SQL> @?/rdbms/admin/catupgrd.sql
  20. DOC>######################################################################
  21. DOC>######################################################################
  22. DOC>                                     NOTE
  23. DOC>
  24. DOC>        The catupgrd.sql is being deprecated in the 12.1 release of the
  25. DOC>        Oracle Database.  Customers are encouraged to use catctl.pl as
  26. DOC>        the replacement for catupgrd.sql when upgrading the database dictionary.
  27. DOC>
  28. DOC>                        cd $ORACLE_HOME/rdbms/admin
  29. DOC>                        $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
  30. DOC>
  31. DOC>        Refer to the Oracle Database Upgrade Guide for more information.
  32. DOC>
  33. DOC>        This database upgrade procedure must be called with the following
  34. DOC>        argument when invoking from the SQL prompt:
  35. DOC>
  36. DOC>                        @catupgrd.sql PARALLEL=NO
  37. DOC>
  38. DOC>######################################################################
  39. DOC>######################################################################
  40. DOC>#
  41. old   2: WHERE        UPPER('&&1') = 'PARALLEL=NO' OR
  42. new   2: WHERE        UPPER('') = 'PARALLEL=NO' OR
  43. old   3:        UPPER('&&1') = 'PARALLEL=YES'
  44. new   3:        UPPER('') = 'PARALLEL=YES'
  45. SELECT (to_number(count(*)))/(to_number(count(*))) FROM DUAL
  46.                             *
  47. ERROR at line 1:
  48. ORA-01476: 除数为 0


  51. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  52. With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
  53. and Unified Auditing options
  54. [oracle@classroom ~]$
复制代码
  1. [oracle@classroom admin]$ pwd
  2. /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin
  3. [oracle@classroom admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
复制代码
  1. [oracle@classroom ~]$ sqlplus /nolog

  3. SQL*Plus: Release 12.1.0.2.0 Production on 星期四 12月 10 21:24:59 2020

  5. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  7. SQL> conn / as sysdba
  8. Connected to an idle instance.
  9. SQL> startup upgrade
  10. ORACLE instance started.

  12. Total System Global Area 1610612736 bytes
  13. Fixed Size                    2924928 bytes
  14. Variable Size                  671092352 bytes
  15. Database Buffers          922746880 bytes
  16. Redo Buffers                   13848576 bytes
  17. Database mounted.
  18. Database opened.
  19. SQL> @?/rdbms/admin/catupgrd.sql
  20. DOC>######################################################################
  21. DOC>######################################################################
  22. DOC>                                     NOTE
  23. DOC>
  24. DOC>        The catupgrd.sql is being deprecated in the 12.1 release of the
  25. DOC>        Oracle Database.  Customers are encouraged to use catctl.pl as
  26. DOC>        the replacement for catupgrd.sql when upgrading the database dictionary.
  27. DOC>
  28. DOC>                        cd $ORACLE_HOME/rdbms/admin
  29. DOC>                        $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
  30. DOC>
  31. DOC>        Refer to the Oracle Database Upgrade Guide for more information.
  32. DOC>
  33. DOC>        This database upgrade procedure must be called with the following
  34. DOC>        argument when invoking from the SQL prompt:
  35. DOC>
  36. DOC>                        @catupgrd.sql PARALLEL=NO
  37. DOC>
  38. DOC>######################################################################
  39. DOC>######################################################################
  40. DOC>#
  41. old   2: WHERE        UPPER('&&1') = 'PARALLEL=NO' OR
  42. new   2: WHERE        UPPER('') = 'PARALLEL=NO' OR
  43. old   3:        UPPER('&&1') = 'PARALLEL=YES'
  44. new   3:        UPPER('') = 'PARALLEL=YES'
  45. SELECT (to_number(count(*)))/(to_number(count(*))) FROM DUAL
  46.                             *
  47. ERROR at line 1:
  48. ORA-01476: 除数为 0


  51. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  52. With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
  53. and Unified Auditing options
  54. [oracle@classroom ~]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 catupgrd.sql
  55. Can't open perl script "catctl.pl": (null)
  56. [oracle@classroom ~]$ /u01/app/oracle/product/12.1.0/
  57. agent/      dbhome_1/   dbhome_2/   gc_inst/    grid/       middleware/ swlib/      
  58. [oracle@classroom ~]$ cd /u01/app/oracle/product/
  59. [oracle@classroom product]$ cd 12.1.0/
  60. [oracle@classroom 12.1.0]$ cd dbhome_1/rdbms/admin/
  61. [oracle@classroom admin]$ pwd
  62. /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin
  63. [oracle@classroom admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql

  65. Argument list for [catctl.pl]
  66. SQL Process Count     n = 4
  67. SQL PDB Process Count N = 0
  68. Input Directory       d = 0
  69. Phase Logging Table   t = 0
  70. Log Dir               l = 0
  71. Script                s = 0
  72. Serial Run            S = 0
  73. Upgrade Mode active   M = 0
  74. Start Phase           p = 0
  75. End Phase             P = 0
  76. Log Id                i = 0
  77. Run in                c = 0
  78. Do not run in         C = 0
  79. Echo OFF              e = 1
  80. No Post Upgrade       x = 0
  81. Reverse Order         r = 0
  82. Open Mode Normal      o = 0
  83. Debug catcon.pm       z = 0
  84. Debug catctl.pl       Z = 0
  85. Display Phases        y = 0
  86. Child Process         I = 0

  88. catctl.pl version: 12.1.0.2.0
  89. Oracle Base           = /u01/app/oracle

  91. Analyzing file catupgrd.sql
  92. Log files in /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin
  93. catcon: ALL catcon-related output will be written to catupgrd_catcon_28605.lst
  94. catcon: See catupgrd*.log files for output generated by scripts
  95. catcon: See catupgrd_*.lst files for spool files, if any
  96. Number of Cpus        = 4
  97. SQL Process Count     = 4

  99. ------------------------------------------------------
  100. Phases [0-73]
  101. Serial   Phase #: 0 Files: 1








  110.     Time: 263s  
  111. Serial   Phase #: 1 Files: 5     Time: 153s  
  112. Restart  Phase #: 2 Files: 1     Time: 0s   
  113. Parallel Phase #: 3 Files: 18    Time: 37s   
  114. Restart  Phase #: 4 Files: 1     Time: 0s   
  115. Serial   Phase #: 5 Files: 5     Time: 64s   
  116. Serial   Phase #: 6 Files: 1     Time: 32s   
  117. Serial   Phase #: 7 Files: 4     Time: 18s   
  118. Restart  Phase #: 8 Files: 1     Time: 3s   
  119. Parallel Phase #: 9 Files: 62    Time: 112s  
  120. Restart  Phase #:10 Files: 1     Time: 0s   
  121. Serial   Phase #:11 Files: 1     Time: 49s   
  122. Restart  Phase #:12 Files: 1     Time: 1s   
  123. Parallel Phase #:13 Files: 91    Time: 13s   
  124. Restart  Phase #:14 Files: 1     Time: 0s   
  125. Parallel Phase #:15 Files: 111   Time: 36s   
  126. Restart  Phase #:16 Files: 1     Time: 0s   
  127. Serial   Phase #:17 Files: 3     Time: 2s   
  128. Restart  Phase #:18 Files: 1     Time: 1s   
  129. Parallel Phase #:19 Files: 32    Time: 46s   
  130. Restart  Phase #:20 Files: 1     Time: 0s   
  131. Serial   Phase #:21 Files: 3     Time: 15s   
  132. Restart  Phase #:22 Files: 1     Time: 2s   
  133. Parallel Phase #:23 Files: 23    Time: 135s  
  134. Restart  Phase #:24 Files: 1     Time: 0s   
  135. Parallel Phase #:25 Files: 11    Time: 72s   
  136. Restart  Phase #:26 Files: 1     Time: 0s   
  137. Serial   Phase #:27 Files: 1     Time: 1s   
  138. Restart  Phase #:28 Files: 1     Time: 1s   
  139. Serial   Phase #:30 Files: 1     Time: 0s   
  140. Serial   Phase #:31 Files: 257   Time: 42s   
  141. Serial   Phase #:32 Files: 1     Time: 0s   
  142. Restart  Phase #:33 Files: 1     Time: 1s   
  143. Serial   Phase #:34 Files: 1     Time: 7s   
  144. Restart  Phase #:35 Files: 1     Time: 1s   
  145. Restart  Phase #:36 Files: 1     Time: 0s   
  146. Serial   Phase #:37 Files: 4     Time: 79s   
  147. Restart  Phase #:38 Files: 1     Time: 1s   
  148. Parallel Phase #:39 Files: 13    Time: 86s   
  149. Restart  Phase #:40 Files: 1     Time: 0s   
  150. Parallel Phase #:41 Files: 10    Time: 12s   
  151. Restart  Phase #:42 Files: 1     Time: 0s   
  152. Serial   Phase #:43 Files: 1     Time: 15s   
  153. Restart  Phase #:44 Files: 1     Time: 1s   
  154. Serial   Phase #:45 Files: 1     Time: 7s   
  155. Serial   Phase #:46 Files: 1     Time: 1s   
  156. Restart  Phase #:47 Files: 1     Time: 2s   
  157. Serial   Phase #:48 Files: 1     Time: 293s  
  158. Restart  Phase #:49 Files: 1     Time: 2s   
  159. Serial   Phase #:50 Files: 1     Time: 68s   
  160. Restart  Phase #:51 Files: 1     Time: 1s   
  161. Serial   Phase #:52 Files: 1     Time: 49s   
  162. Restart  Phase #:53 Files: 1     Time: 1s   
  163. Serial   Phase #:54 Files: 1     Time: 286s  
  164. Restart  Phase #:55 Files: 1     Time: 2s   
  165. Serial   Phase #:56 Files: 1     Time: 103s  
  166. Restart  Phase #:57 Files: 1     Time: 1s   
  167. Serial   Phase #:58 Files: 1     Time: 161s  
  168. Restart  Phase #:59 Files: 1     Time: 1s   
  169. Serial   Phase #:60 Files: 1     Time: 406s  
  170. Restart  Phase #:61 Files: 1     Time: 1s   
  171. Serial   Phase #:62 Files: 1     Time: 2215s
  172. Restart  Phase #:63 Files: 1     Time: 1s   
  173. Serial   Phase #:64 Files: 1     Time: 1s   
  174. Serial   Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > catupgrd_datapatch_upgrade.log 2> catupgrd_datapatch_upgrade.err
  175. returned from sqlpatch
  176.     Time: 41s   
  177. Serial   Phase #:66 Files: 1     Time: 95s   
  178. Serial   Phase #:68 Files: 1     Time: 0s   
  179. Serial   Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > catupgrd_datapatch_normal.log 2> catupgrd_datapatch_normal.err
  180. returned from sqlpatch
  181.     Time: 109s  
  182. Serial   Phase #:70 Files: 1     Time: 220s  
  183. Serial   Phase #:71 Files: 1     Time: 0s   
  184. Serial   Phase #:72 Files: 1     Time: 0s   
  185. Serial   Phase #:73 Files: 1     Time: 58s   

  187. Grand Total Time: 5439s

  189. LOG FILES: (catupgrd*.log)

  191. Upgrade Summary Report Located in:
  192. /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/rcat/upgrade/upg_summary.log

  194. Grand Total Upgrade Time:    [0d:1h:30m:39s]
  195. [oracle@classroom admin]$
  196. [oracle@classroom admin]$
  197. [oracle@classroom admin]$
  198. [oracle@classroom admin]$
  199. [oracle@classroom admin]$
  200. [oracle@classroom admin]$
  201. [oracle@classroom admin]$
  202. [oracle@classroom admin]$
  203. [oracle@classroom admin]$
  204. [oracle@classroom admin]$
复制代码
  1. /u01/app/oracle/cfgtoollogs/rcat/preupgrade/postupgrade_fixups.sql
复制代码

---------------------------------------------------------------
  1. SQL> @?/rdbms/admin/utlu121s.sql

  3. PL/SQL procedure successfully completed.


  6. PL/SQL procedure successfully completed.





  12. CATCTL REPORT = /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/rcat/upgrade/upg_summary.log

  14. PL/SQL procedure successfully completed.






  21. Oracle Database 12.1 Post-Upgrade Status Tool                12-13-2020 15:11:11

  23. Component                                Current         Version  Elapsed Time
  24. Name                                        Status                Number         HH:MM:SS

  26. Oracle Server                               UPGRADED      12.1.0.2.0  00:21:48
  27. JServer JAVA Virtual Machine                  VALID      12.1.0.2.0  00:04:52
  28. Oracle Workspace Manager                  VALID      12.1.0.2.0  00:01:23
  29. OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:00:47
  30. OLAP Catalog                             OPTION OFF      11.2.0.3.0  00:00:00
  31. Oracle OLAP API                           VALID      12.1.0.2.0  00:00:25
  32. Oracle XDK                                  VALID      12.1.0.2.0  00:01:07
  33. Oracle Text                                  VALID      12.1.0.2.0  00:01:40
  34. Oracle XML Database                          VALID      12.1.0.2.0  00:03:04
  35. Oracle Database Java Packages                  VALID      12.1.0.2.0  00:00:18
  36. Oracle Multimedia                          VALID      12.1.0.2.0  00:02:40
  37. Spatial                                UPGRADED      12.1.0.2.0  00:06:44
  38. Oracle Application Express                  VALID     4.2.5.00.08  00:36:01
  39. Final Actions                                                         00:02:04
  40. Post Upgrade                                                         00:03:27

  42. Total Upgrade Time: 01:27:14

  44. PL/SQL procedure successfully completed.

  46. SQL>
  47. SQL> --
  48. SQL> -- Update Summary Table with con_name and endtime.
  49. SQL> --
  50. SQL> UPDATE sys.registry$upg_summary SET reportname = :ReportName,
  51.   2                                       con_name = SYS_CONTEXT('USERENV','CON_NAME'),
  52.   3                                       endtime  = SYSDATE
  53.   4              WHERE con_id = -1;

  55. 1 row updated.

  57. SQL> commit;

  59. Commit complete.

  61. SQL>
复制代码
  1. SQL> @?/rdbms/admin/catuppst.sql
  2. SQL> Rem
  3. SQL> Rem $Header: rdbms/admin/catuppst.sql /st_rdbms_12.1/1 2014/06/11 20:58:01 surman Exp $
  4. SQL> Rem
  5. SQL> Rem catuppst.sql
  6. SQL> Rem
  7. SQL> Rem Copyright (c) 2006, 2014, Oracle and/or its affiliates.
  8. SQL> Rem All rights reserved.
  9. SQL> Rem
  10. SQL> Rem    NAME
  11. SQL> Rem         catuppst.sql - CATalog UPgrade PoST-upgrade actions
  12. SQL> Rem
  13. SQL> Rem    DESCRIPTION
  14. SQL> Rem         This post-upgrade script performs remaining upgrade actions that
  15. SQL> Rem         do not require that the database be open in UPGRADE mode.
  16. SQL> Rem         Automatically apply the latest PSU.
  17. SQL> Rem
  18. SQL> Rem    NOTES
  19. SQL> Rem         You must be connected AS SYSDBA to run this script.
  20. SQL> Rem
  21. SQL> Rem    MODIFIED   (MM/DD/YY)
  22. SQL> Rem    surman        06/05/14 - Backport surman_bug-17277459 from main
  23. SQL> Rem    surman        05/19/14 - 17277459: Remove call to catbundle
  24. SQL> Rem    jerrede        01/17/14 - Fix Bug 18071399 Add Post Upgrade Report Time
  25. SQL> Rem    surman        05/31/13 - 16790144: Use @@
  26. SQL> Rem    cmlim        05/15/13 - bug 16816410: add table name to errorlogging
  27. SQL> Rem                           syntax
  28. SQL> Rem    surman        03/19/13 - 16094163: Add catbundleapply.sql
  29. SQL> Rem    cmlim        03/01/13 - bug 16306200: remove the workaround (added in
  30. SQL> Rem                           txn in bug 16085743) that re-updated
  31. SQL> Rem                           oracle-supplied bit in views owned by SYS after
  32. SQL> Rem                           bootstrap.  Workaround not needed once the shared
  33. SQL> Rem                           pool is flushed in catuposb.sql (bug 16306200).
  34. SQL> Rem    jerrede        01/14/13 - XbranchMerge jerrede_bug-16097914 from
  35. SQL> Rem                           st_rdbms_12.1.0.1
  36. SQL> Rem    jerrede        01/11/13 - Move Removal of EXF/RUL to upgrade.
  37. SQL> Rem                           LogMiner/Standyby can not deal with removing
  38. SQL> Rem                           a component outside of upgrade.
  39. SQL> Rem    sjanardh        01/10/13 - XbranchMerge maba_bug-14615619 from main
  40. SQL> Rem    jerrede        12/19/12 - Bug#16025279 Add Event for Not Removing EXF/RUL
  41. SQL> Rem                           Upgrade Components
  42. SQL> Rem    surman        12/10/12 - XbranchMerge surman_bug-12876907 from main
  43. SQL> Rem    maba        11/26/12 - fixed bug 14615619
  44. SQL> Rem    jerrede        11/05/12 - Add Exadata Bundle support
  45. SQL> Rem    cmlim        10/27/12 - bug 14258301 : gather fixed obj stats if none of
  46. SQL> Rem                           the fixed object tables have had stats collected
  47. SQL> Rem    mfallen        09/20/12 - bug 14390165: check if AWR data needs update
  48. SQL> Rem    jerrede        10/23/12 - Unset _ORACLE_SCRIPT
  49. SQL> Rem    jerrede        10/23/12 - Add Session Info
  50. SQL> Rem    maba        09/13/12 - added create dequeue log for bug 14278722
  51. SQL> Rem    jerrede        06/26/12 - Set event to optionally update required stats
  52. SQL> Rem                           during upgrade
  53. SQL> Rem    rpang        05/21/12 - Add network ACL migration status check
  54. SQL> Rem    traney        05/09/12 - lrg 6949943: mask ORA-942s
  55. SQL> Rem    jerrede        04/17/12 - Moved Mandatory Changes to catrequired.sql
  56. SQL> Rem    traney        04/04/12 - lrg 6762280: drop DBMS_DDL_INTERNAL_LIB
  57. SQL> Rem    traney        03/12/12 - bug 13719175: move post-utlmmig stats here
  58. SQL> Rem    cdilling        12/13/11 - drop SYSMAN schema - removal of EM component for
  59. SQL> Rem                           upgrade to 12.1
  60. SQL> Rem    aramappa        06/22/11 - Always run olstrig.sql when OLS installed in DB
  61. SQL> Rem    xbarr        04/28/11 - move DMSYS removal code to odmu112.sql
  62. SQL> Rem    xbarr        10/25/10 - run dmsysrem.sql to drop DMSYS schema if exists
  63. SQL> Rem    cdilling        07/21/10 - add call to catbundle.sql for bug 9925339
  64. SQL> Rem    srtata        12/16/08 - run olstrig.sql when upgrading from prior to 10.2
  65. SQL> Rem    srtata        10/15/08 - put back olstrig.sql as we found it cannot be run
  66. SQL> Rem                           as part of upgrade
  67. SQL> Rem    srtata        02/26/08 - move olstrig.sql to olsdbmig.sql
  68. SQL> Rem    ushaft        02/05/07 - post upgrade for ADDM tasks.
  69. SQL> Rem    cdilling        12/06/06 - add support for error logging
  70. SQL> Rem    rburns        11/10/06 - post upgrade actions
  71. SQL> Rem    rburns        11/10/06 - Created
  72. SQL> Rem
  73. SQL>
  74. SQL> Rem =====================================================================
  75. SQL> Rem Call Common session settings
  76. SQL> Rem =====================================================================
  77. SQL> @@catpses.sql
  78. SQL> Rem
  79. SQL> Rem $Header: rdbms/admin/catpses.sql /main/3 2014/02/20 12:45:39 surman Exp $
  80. SQL> Rem
  81. SQL> Rem catpses.sql
  82. SQL> Rem
  83. SQL> Rem Copyright (c) 2006, 2014, Oracle and/or its affiliates.
  84. SQL> Rem All rights reserved.
  85. SQL> Rem
  86. SQL> Rem    NAME
  87. SQL> Rem         catpses.sql - CATalog and CATProc SESsion script
  88. SQL> Rem
  89. SQL> Rem    DESCRIPTION
  90. SQL> Rem         This script initializes the session for running catalog
  91. SQL> Rem         and/or catproc scripts
  92. SQL> Rem
  93. SQL> Rem    NOTES
  94. SQL> Rem         It is used as the session script for parallel processes
  95. SQL> Rem         when catalog.sql and/or catproc.sql is run using multiprocesses
  96. SQL> Rem
  97. SQL> Rem BEGIN SQL_FILE_METADATA
  98. SQL> Rem SQL_SOURCE_FILE: rdbms/admin/catpses.sql
  99. SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/catpses.sql
  100. SQL> Rem SQL_PHASE: CATPSES
  101. SQL> Rem SQL_STARTUP_MODE: NORMAL
  102. SQL> Rem SQL_IGNORABLE_ERRORS: NONE
  103. SQL> Rem SQL_CALLING_FILE: rdbms/admin/cdstrt.sql
  104. SQL> Rem END SQL_FILE_METADATA
  105. SQL> Rem
  106. SQL> Rem    MODIFIED   (MM/DD/YY)
  107. SQL> Rem    surman        12/29/13 - 13922626: Update SQL metadata
  108. SQL> Rem    jerrede        05/08/12 - Added session info for CDB.
  109. SQL> Rem    rburns        10/23/06 - add session script
  110. SQL> Rem    rburns        10/23/06 - Created
  111. SQL> Rem
  112. SQL>
  113. SQL> @@?/rdbms/admin/sqlsessstart.sql
  114. SQL> Rem
  115. SQL> Rem $Header: rdbms/admin/sqlsessstart.sql /main/1 2013/03/13 13:08:33 surman Exp $
  116. SQL> Rem
  117. SQL> Rem sqlsessstart.sql
  118. SQL> Rem
  119. SQL> Rem Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.
  120. SQL> Rem
  121. SQL> Rem    NAME
  122. SQL> Rem         sqlsessstart.sql - SQL session start
  123. SQL> Rem
  124. SQL> Rem    DESCRIPTION
  125. SQL> Rem         Any commands which should be run at the start of all oracle
  126. SQL> Rem         supplied scripts.
  127. SQL> Rem
  128. SQL> Rem    NOTES
  129. SQL> Rem         See sqlsessend.sql for the corresponding end script.
  130. SQL> Rem
  131. SQL> Rem    MODIFIED   (MM/DD/YY)
  132. SQL> Rem    surman        03/08/13 - 16462837: Common start and end scripts
  133. SQL> Rem    surman        03/08/13 - Created
  134. SQL> Rem
  135. SQL>
  136. SQL> alter session set "_ORACLE_SCRIPT" = true;

  138. Session altered.

  140. SQL>
  141. SQL> Rem =====================================================================
  142. SQL> Rem Assure CHAR semantics are not used in the dictionary
  143. SQL> Rem =====================================================================
  144. SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

  146. Session altered.

  148. SQL>
  149. SQL> Rem =====================================================================
  150. SQL> Rem Needed for Consolidated databases
  151. SQL> Rem =====================================================================
  152. SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=true;

  154. Session altered.

  156. SQL>
  157. SQL>
  158. SQL>
  159. SQL> @?/rdbms/admin/sqlsessend.sql
  160. SQL> Rem
  161. SQL> Rem $Header: rdbms/admin/sqlsessend.sql /main/1 2013/03/13 13:08:33 surman Exp $
  162. SQL> Rem
  163. SQL> Rem sqlsessend.sql
  164. SQL> Rem
  165. SQL> Rem Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.
  166. SQL> Rem
  167. SQL> Rem    NAME
  168. SQL> Rem         sqlsessend.sql - SQL session end
  169. SQL> Rem
  170. SQL> Rem    DESCRIPTION
  171. SQL> Rem         Any commands which should be run at the end of all oracle
  172. SQL> Rem         supplied scripts.
  173. SQL> Rem
  174. SQL> Rem    NOTES
  175. SQL> Rem         See sqlsessstart.sql for the corresponding start script.
  176. SQL> Rem
  177. SQL> Rem    MODIFIED   (MM/DD/YY)
  178. SQL> Rem    surman        03/08/13 - 16462837: Common start and end scripts
  179. SQL> Rem    surman        03/08/13 - Created
  180. SQL> Rem
  181. SQL>
  182. SQL> alter session set "_ORACLE_SCRIPT" = false;

  184. Session altered.

  186. SQL>
  187. SQL>
  188. SQL>
  189. SQL> Rem *********************************************************************
  190. SQL> Rem BEGIN catuppst.sql
  191. SQL> Rem *********************************************************************
  192. SQL> Rem Set identifier to POSTUP for errorlogging
  193. SQL>
  194. SQL> SET ERRORLOGGING ON TABLE SYS.REGISTRY$ERROR IDENTIFIER 'POSTUP';
  195. SQL>
  196. SQL> SELECT dbms_registry_sys.time_stamp('POSTUP_BGN') as timestamp from dual;

  198. TIMESTAMP
  199. --------------------------------------------------------------------------------
  200. COMP_TIMESTAMP POSTUP_BGN 2020-12-13 15:13:34

  202. SQL>
  203. SQL> Rem =======================================================================
  204. SQL> Rem  Run Post Upgrade Operations
  205. SQL> Rem =======================================================================
  206. SQL>
  207. SQL> @@catrequired.sql
  208. SQL> Rem
  209. SQL> Rem $Header: rdbms/admin/catrequired.sql /main/1 2012/07/19 11:27:56 jerrede Exp $
  210. SQL> Rem
  211. SQL> Rem catrequired.sql
  212. SQL> Rem
  213. SQL> Rem Copyright (c) 2006, 2012, Oracle and/or its affiliates.
  214. SQL> Rem All rights reserved.
  215. SQL> Rem
  216. SQL> Rem    NAME
  217. SQL> Rem         catrequired.sql - Catalog Mandatory Upgrade Script
  218. SQL> Rem
  219. SQL> Rem    DESCRIPTION
  220. SQL> Rem         This catalog script is a place holder
  221. SQL> Rem         for other things that may be added in the future.
  222. SQL> Rem         Right now it only calls catrequtlmg.sql.
  223. SQL> Rem
  224. SQL> Rem    NOTES
  225. SQL> Rem         You must be connected AS SYSDBA to run this script.
  226. SQL> Rem
  227. SQL> Rem    MODIFIED   (MM/DD/YY)
  228. SQL> Rem          jerrede        04/17/12 - Created
  229. SQL> Rem
  230. SQL>
  231. SQL>
  232. SQL> Rem *********************************************************************
  233. SQL> Rem BEGIN catrequired.sql
  234. SQL> Rem *********************************************************************
  235. SQL>
  236. SQL> Rem
  237. SQL> Rem Display Start TimeStamp
  238. SQL> Rem
  239. SQL> SELECT sys.dbms_registry_sys.time_stamp('catreq_bgn') as timestamp from dual;

  241. TIMESTAMP
  242. --------------------------------------------------------------------------------
  243. COMP_TIMESTAMP CATREQ_BGN 2020-12-13 15:13:34

  245. SQL>
  246. SQL>
  247. SQL> Rem
  248. SQL> Rem Post-utlmmig statistics gathering
  249. SQL> Rem
  250. SQL> @@catrequtlmg.sql
  251. SQL> Rem
  252. SQL> Rem $Header: rdbms/admin/catrequtlmg.sql /main/2 2013/01/09 14:23:01 jerrede Exp $
  253. SQL> Rem
  254. SQL> Rem catrequtlmg.sql
  255. SQL> Rem
  256. SQL> Rem Copyright (c) 2006, 2013, Oracle and/or its affiliates.
  257. SQL> Rem All rights reserved.
  258. SQL> Rem
  259. SQL> Rem    NAME
  260. SQL> Rem         catrequtlmg.sql - Catalog Mandatory Upgrade Script
  261. SQL> Rem
  262. SQL> Rem    DESCRIPTION
  263. SQL> Rem         This catalog script can run from utlmmig.sql or catuppst.sql.
  264. SQL> Rem         The event _utlmmig_table_stats_gathering determines where it
  265. SQL> Rem         is run.  If TRUE (the default) it is run from utlmmig.sql, if
  266. SQL> Rem         FALSE it will be run from catuppst.sql. This script gathers
  267. SQL> Rem         statistics on migration stats that are recreated after an
  268. SQL> Rem         upgrade occurs.
  269. SQL> Rem
  270. SQL> Rem    NOTES
  271. SQL> Rem         You must be connected AS SYSDBA to run this script.
  272. SQL> Rem
  273. SQL> Rem    MODIFIED   (MM/DD/YY)
  274. SQL> Rem    jerrede        12/20/12 - Turn off set serveroutput
  275. SQL> Rem    jerrede        04/17/12 - Moved from catuppst.sql
  276. SQL> Rem                           which was written by Tom Raney.
  277. SQL> Rem
  278. SQL>
  279. SQL>
  280. SQL> Rem *********************************************************************
  281. SQL> Rem BEGIN catrequtlmg.sql
  282. SQL> Rem *********************************************************************
  283. SQL>
  284. SQL> Rem =======================================================================
  285. SQL> Rem Statistics gathering
  286. SQL> Rem =======================================================================
  287. SQL> -- DBMS_STATS now depends on DBMS_UTILITY which may have gotten invalidated
  288. SQL> -- by some preceeding DDL statement, so package state needs to be cleared to
  289. SQL> -- avoid ORA-04068, reset_package causes set serveroutput on to not work.
  290. SQL>
  291. SQL> execute dbms_session.reset_package;

  293. PL/SQL procedure successfully completed.

  295. SQL> set serveroutput on;
  296. SQL>
  297. SQL> declare
  298.   2  
  299.   3    n_SampleSize                NUMBER           := DBMS_STATS.AUTO_SAMPLE_SIZE;
  300.   4    c_UPGSAMPLESIZE CONSTANT NUMBER           := 100;
  301.   5    c_TRACEEVENT CONSTANT VARCHAR2(30)  := '_utlmmig_table_stats_gathering';
  302.   6    c_POSTUPGRADE CONSTANT VARCHAR2(19) := 'CATREQ_POST_UPGRADE';
  303.   7    c_BOOTERR CONSTANT VARCHAR2(23)           := 'BOOTSTRAP_UPGRADE_ERROR';
  304.   8    c_MIGTABLE CONSTANT VARCHAR2(4)           := '$MIG';
  305.   9    c_POSTUPGTABLE CONSTANT VARCHAR2(1) := '



  309. ;
  310. 10    s_TableName VARCHAR2(4)                   := c_MIGTABLE;
  311. 11    b_InUtlMig BOOLEAN := sys.dbms_registry_sys.select_props_data(c_BOOTERR);
  312. 12    b_UpgradeMode BOOLEAN := sys.dbms_registry.is_in_upgrade_mode();
  313. 13    b_StatEvt  BOOLEAN := sys.dbms_registry.is_trace_event_set(c_TRACEEVENT);
  314. 14    b_SelProps BOOLEAN := sys.dbms_registry_sys.select_props_data(c_POSTUPGRADE);
  315. 15    b_Props BOOLEAN := TRUE;
  316. 16  
  317. 17  begin
  318. 18  
  319. 19    --
  320. 20    -- Debug Info
  321. 21    --
  322. 22    IF (b_StatEvt) THEN
  323. 23           sys.dbms_output.put_line('catrequtlmg: b_StatEvt     = TRUE');
  324. 24    ELSE
  325. 25           sys.dbms_output.put_line('catrequtlmg: b_StatEvt     = FALSE');
  326. 26    END IF;
  327. 27  
  328. 28    IF (b_SelProps) THEN
  329. 29           sys.dbms_output.put_line('catrequtlmg: b_SelProps    = TRUE');
  330. 30    ELSE
  331. 31           sys.dbms_output.put_line('catrequtlmg: b_SelProps    = FALSE');
  332. 32    END IF;
  333. 33  
  334. 34    IF (b_UpgradeMode) THEN
  335. 35           sys.dbms_output.put_line('catrequtlmg: b_UpgradeMode = TRUE');
  336. 36    ELSE
  337. 37           sys.dbms_output.put_line('catrequtlmg: b_UpgradeMode = FALSE');
  338. 38    END IF;
  339. 39  
  340. 40    IF (b_InUtlMig) THEN
  341. 41           sys.dbms_output.put_line('catrequtlmg: b_InUtlMig    = TRUE');
  342. 42    ELSE
  343. 43           sys.dbms_output.put_line('catrequtlmg: b_InUtlMig    = FALSE');
  344. 44           s_TableName := c_POSTUPGTABLE;
  345. 45    END IF;
  346. 46  
  347. 47    --
  348. 48    -- b_StatEvt = FALSE indicates don't collect stats
  349. 49    --              in upgrade mode.
  350. 50    --
  351. 51    -- Don't do the migration stats in UPGRADE mode.
  352. 52    -- Stats will run no matter what mode we are in
  353. 53    -- if post upgrade data is found in sys.props$.
  354. 54    --
  355. 55    IF (b_StatEvt = FALSE AND b_SelProps = FALSE) THEN
  356. 56  
  357. 57           --
  358. 58           -- In Upgrade Mode Only
  359. 59           --
  360. 60           IF (b_UpgradeMode) THEN
  361. 61  
  362. 62             --
  363. 63             -- Set sys.props$ table indicating that it
  364. 64             -- needs to be run in the post upgrade script.
  365. 65             --
  366. 66             b_Props := sys.dbms_registry_sys.insert_props_data(c_POSTUPGRADE,
  367. 67                      'Run Migration Stats',
  368. 68                      'Startup database in normal mode and run catuppst.sql');
  369. 69             IF (b_Props) THEN
  370. 70               sys.dbms_output.put_line('catrequtlmg: insert_props_data: Success');
  371. 71             ELSE
  372. 72               sys.dbms_output.put_line('catrequtlmg: insert_props_data: Failure');
  373. 73             END IF;
  374. 74  
  375. 75           END IF;
  376. 76  
  377. 77           RETURN;
  378. 78  
  379. 79    END IF;
  380. 80  
  381. 81    --
  382. 82    -- b_StatEvt = TRUE indicates collect stats
  383. 83    --              in upgrade mode.
  384. 84    --
  385. 85    -- Don't do the migration stats in NORMAL mode.
  386. 86    -- Stats will run no matter what mode we are in
  387. 87    -- if post upgrade data is found in sys.props$.
  388. 88    --
  389. 89    IF (b_StatEvt = TRUE AND b_SelProps = FALSE AND b_UpgradeMode = FALSE) THEN
  390. 90  
  391. 91             RETURN;
  392. 92  
  393. 93    END IF;
  394. 94  
  395. 95    --
  396. 96    -- Updating migration stats in post upgrade. Write an entry to
  397. 97    -- sys.props$ table to indicate that stat collection has started.
  398. 98    -- If this entry is present then this routine has failed.
  399. 99    --
  400. 100    IF (b_SelProps) THEN
  401. 101  
  402. 102           b_Props := sys.dbms_registry_sys.update_props_data(c_POSTUPGRADE,
  403. 103                                                'Started Migration Stats');
  404. 104           IF (b_Props) THEN
  405. 105             sys.dbms_output.put_line('catrequtlmg: update_props_data: Success');
  406. 106           ELSE
  407. 107             sys.dbms_output.put_line('catrequtlmg: update_props_data: Failure');
  408. 108           END IF;
  409. 109  
  410. 110    END IF;
  411. 111  
  412. 112    --
  413. 113    -- If we are in upgrade mode turn auto sample size off
  414. 114    -- results in a bug when we try and do a recomp in
  415. 115    -- utlrp.
  416. 116    --
  417. 117    IF (b_UpgradeMode) THEN
  418. 118           n_SampleSize := c_UPGSAMPLESIZE;
  419. 119    END IF;
  420. 120  
  421. 121    --
  422. 122    -- Delete Stats
  423. 123    --
  424. 124    sys.dbms_output.put_line('catrequtlmg: Deleting table stats');
  425. 125    sys.dbms_stats.delete_table_stats('SYS', 'OBJ' || s_TableName);
  426. 126    sys.dbms_stats.delete_table_stats('SYS', 'USER' || s_TableName);
  427. 127    sys.dbms_stats.delete_table_stats('SYS', 'COL' || s_TableName);
  428. 128    sys.dbms_stats.delete_table_stats('SYS', 'CLU' || s_TableName);
  429. 129    sys.dbms_stats.delete_table_stats('SYS', 'CON' || s_TableName);
  430. 130    sys.dbms_stats.delete_table_stats('SYS', 'TAB' || s_TableName);
  431. 131    sys.dbms_stats.delete_table_stats('SYS', 'IND' || s_TableName);
  432. 132    sys.dbms_stats.delete_table_stats('SYS', 'ICOL' || s_TableName);
  433. 133    sys.dbms_stats.delete_table_stats('SYS', 'LOB' || s_TableName);
  434. 134    sys.dbms_stats.delete_table_stats('SYS', 'COLTYPE' || s_TableName);
  435. 135    sys.dbms_stats.delete_table_stats('SYS', 'SUBCOLTYPE' || s_TableName);
  436. 136    sys.dbms_stats.delete_table_stats('SYS', 'NTAB' || s_TableName);
  437. 137    sys.dbms_stats.delete_table_stats('SYS', 'REFCON' || s_TableName);
  438. 138    sys.dbms_stats.delete_table_stats('SYS', 'OPQTYPE' || s_TableName);
  439. 139    sys.dbms_stats.delete_table_stats('SYS', 'ICOLDEP' || s_TableName);
  440. 140    sys.dbms_stats.delete_table_stats('SYS', 'TSQ' || s_TableName);
  441. 141    sys.dbms_stats.delete_table_stats('SYS', 'VIEWTRCOL' || s_TableName);
  442. 142    sys.dbms_stats.delete_table_stats('SYS', 'ATTRCOL' || s_TableName);
  443. 143    sys.dbms_stats.delete_table_stats('SYS', 'TYPE_MISC' || s_TableName);
  444. 144    sys.dbms_stats.delete_table_stats('SYS', 'LIBRARY' || s_TableName);
  445. 145    sys.dbms_stats.delete_table_stats('SYS', 'ASSEMBLY' || s_TableName);
  446. 146  
  447. 147    --
  448. 148    -- Gather Stats
  449. 149    --
  450. 150    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats OBJ' ||
  451. 151                                  s_TableName);
  452. 152    sys.dbms_stats.gather_table_stats('SYS', 'OBJ' || s_TableName,
  453. 153                                       estimate_percent => n_SampleSize,
  454. 154                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  455. 155    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats USER' ||
  456. 156                                  s_TableName);
  457. 157    sys.dbms_stats.gather_table_stats('SYS', 'USER' || s_TableName,
  458. 158                                       estimate_percent => n_SampleSize,
  459. 159                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  460. 160    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats COL' ||
  461. 161                                  s_TableName);
  462. 162    sys.dbms_stats.gather_table_stats('SYS', 'COL' || s_TableName,
  463. 163                                       estimate_percent => n_SampleSize,
  464. 164                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  465. 165    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats CLU' ||
  466. 166                                   s_TableName);
  467. 167    sys.dbms_stats.gather_table_stats('SYS', 'CLU' || s_TableName,
  468. 168                                       estimate_percent => n_SampleSize,
  469. 169                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  470. 170    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats CON' ||
  471. 171                                   s_TableName);
  472. 172    sys.dbms_stats.gather_table_stats('SYS', 'CON' || s_TableName,
  473. 173                                       estimate_percent => n_SampleSize,
  474. 174                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  475. 175    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats TAB' ||
  476. 176                                  s_TableName);
  477. 177    sys.dbms_stats.gather_table_stats('SYS', 'TAB' || s_TableName,
  478. 178                                       estimate_percent => n_SampleSize,
  479. 179                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  480. 180    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats IND' ||
  481. 181                                  s_TableName);
  482. 182    sys.dbms_stats.gather_table_stats('SYS', 'IND' || s_TableName,
  483. 183                                       estimate_percent => n_SampleSize,
  484. 184                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  485. 185    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ICOL' ||
  486. 186                                  s_TableName);
  487. 187    sys.dbms_stats.gather_table_stats('SYS', 'ICOL' || s_TableName,
  488. 188                                       estimate_percent => n_SampleSize,
  489. 189                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  490. 190    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats LOB' ||
  491. 191                                   s_TableName);
  492. 192    sys.dbms_stats.gather_table_stats('SYS', 'LOB' || s_TableName,
  493. 193                                       estimate_percent => n_SampleSize,
  494. 194                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  495. 195    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats COLTYPE' ||
  496. 196                                  s_TableName);
  497. 197    sys.dbms_stats.gather_table_stats('SYS', 'COLTYPE' || s_TableName,
  498. 198                                       estimate_percent => n_SampleSize,
  499. 199                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  500. 200    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats SUBCOLTYPE' ||
  501. 201                                  s_TableName);
  502. 202    sys.dbms_stats.gather_table_stats('SYS', 'SUBCOLTYPE' || s_TableName,
  503. 203                                       estimate_percent => n_SampleSize,
  504. 204                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  505. 205    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats NTAB' ||
  506. 206                                  s_TableName);
  507. 207    sys.dbms_stats.gather_table_stats('SYS', 'NTAB' || s_TableName,
  508. 208                                       estimate_percent => n_SampleSize,
  509. 209                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  510. 210    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats REFCON' ||
  511. 211                                  s_TableName);
  512. 212    sys.dbms_stats.gather_table_stats('SYS', 'REFCON' || s_TableName,
  513. 213                                       estimate_percent => n_SampleSize,
  514. 214                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  515. 215    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats OPQTYPE' ||
  516. 216                                  s_TableName);
  517. 217    sys.dbms_stats.gather_table_stats('SYS', 'OPQTYPE' || s_TableName,
  518. 218                                       estimate_percent => n_SampleSize,
  519. 219                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  520. 220    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ICOLDEP' ||
  521. 221                                  s_TableName);
  522. 222    sys.dbms_stats.gather_table_stats('SYS', 'ICOLDEP' || s_TableName,
  523. 223                                       estimate_percent => n_SampleSize,
  524. 224                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  525. 225    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats TSQ' ||
  526. 226                                  s_TableName);
  527. 227    sys.dbms_stats.gather_table_stats('SYS', 'TSQ' || s_TableName,
  528. 228                                       estimate_percent => n_SampleSize,
  529. 229                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  530. 230    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats VIEWTRCOL' ||
  531. 231                                  s_TableName);
  532. 232    sys.dbms_stats.gather_table_stats('SYS', 'VIEWTRCOL' || s_TableName,
  533. 233                                       estimate_percent => n_SampleSize,
  534. 234                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  535. 235    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ATTRCOL' ||
  536. 236                                  s_TableName);
  537. 237    sys.dbms_stats.gather_table_stats('SYS', 'ATTRCOL' || s_TableName,
  538. 238                                       estimate_percent => n_SampleSize,
  539. 239                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  540. 240    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats TYPE_MISC' ||
  541. 241                                  s_TableName);
  542. 242    sys.dbms_stats.gather_table_stats('SYS', 'TYPE_MISC' || s_TableName,
  543. 243                                       estimate_percent => n_SampleSize,
  544. 244                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  545. 245    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats LIBRARY' ||
  546. 246                                  s_TableName);
  547. 247    sys.dbms_stats.gather_table_stats('SYS', 'LIBRARY' || s_TableName,
  548. 248                                       estimate_percent => n_SampleSize,
  549. 249                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  550. 250    sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ASSEMBLY' ||
  551. 251                                  s_TableName);
  552. 252    sys.dbms_stats.gather_table_stats('SYS', 'ASSEMBLY' || s_TableName,
  553. 253                                       estimate_percent => n_SampleSize,
  554. 254                                       method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  555. 255  
  556. 256  
  557. 257    --
  558. 258    -- Delete any previous entry that may have been stored in
  559. 259    -- sys.props$ table.
  560. 260    --
  561. 261    b_Props := sys.dbms_registry_sys.delete_props_data(c_POSTUPGRADE);
  562. 262    IF (b_Props) THEN
  563. 263           sys.dbms_output.put_line('catrequtlmg: delete_props_data: Success');
  564. 264    ELSE
  565. 265           sys.dbms_output.put_line('catrequtlmg: delete_props_data: No Props Data');
  566. 266    END IF;
  567. 267  
  568. 268  end;
  569. 269  /
  570. catrequtlmg: b_StatEvt           = TRUE
  571. catrequtlmg: b_SelProps    = FALSE
  572. catrequtlmg: b_UpgradeMode = FALSE
  573. catrequtlmg: b_InUtlMig    = FALSE

  575. PL/SQL procedure successfully completed.

  577. SQL>
  578. SQL> --
  579. SQL> -- Set serveroutput off
  580. SQL> --
  581. SQL> set serveroutput off;
  582. SQL>
  583. SQL> --
  584. SQL> -- Reset Package to be on the safe side for the
  585. SQL> -- case where we are running in catuppst.sql
  586. SQL> --
  587. SQL> execute dbms_session.reset_package;

  589. PL/SQL procedure successfully completed.

  591. SQL>
  592. SQL>
  593. SQL> Rem *********************************************************************
  594. SQL> Rem END catrequtlmg.sql
  595. SQL> Rem *********************************************************************
  596. SQL>
  597. SQL>
  598. SQL>
  599. SQL> Rem
  600. SQL> Rem Display End TimeStamp
  601. SQL> Rem
  602. SQL> SELECT sys.dbms_registry_sys.time_stamp('catreq_end') as timestamp from dual;

  604. TIMESTAMP
  605. --------------------------------------------------------------------------------
  606. COMP_TIMESTAMP CATREQ_END 2020-12-13 15:13:34

  608. SQL>
  609. SQL> Rem *********************************************************************
  610. SQL> Rem END catrequired.sql
  611. SQL> Rem *********************************************************************
  612. SQL>
  613. SQL>
  614. SQL> --
  615. SQL> -- These were created in utlmmig.sql but could not be dropped until now.
  616. SQL> -- Suppress "does not exist" errors.
  617. SQL> --
  618. SQL> set serveroutput on;
  619. SQL> begin
  620.   2    sys.dbms_output.put_line('catuppst: Dropping library DBMS_DDL_INTERNAL_LIB');
  621.   3    execute immediate 'drop library DBMS_DDL_INTERNAL_LIB';
  622.   4  exception
  623.   5    when others then
  624.   6    if sqlcode = -4043 then
  625.   7             null;
  626.   8    end if;
  627.   9  end;
  628. 10  /
  629. catuppst: Dropping library DBMS_DDL_INTERNAL_LIB

  631. PL/SQL procedure successfully completed.

  633. SQL>
  634. SQL> begin
  635.   2    sys.dbms_output.put_line('catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG');
  636.   3    execute immediate 'drop view "_CURRENT_EDITION_OBJ_MIG"';
  637.   4  exception
  638.   5    when others then
  639.   6           if sqlcode = -942 then
  640.   7             null;
  641.   8           end if;
  642.   9  end;
  643. 10  /
  644. catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG

  646. PL/SQL procedure successfully completed.

  648. SQL>
  649. SQL> begin
  650.   2    sys.dbms_output.put_line('catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG');
  651.   3    execute immediate 'drop view "_ACTUAL_EDITION_OBJ_MIG"';
  652.   4  exception
  653.   5    when others then
  654.   6           if sqlcode = -942 then
  655.   7             null;
  656.   8           end if;
  657.   9  end;
  658. 10  /
  659. catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG

  661. PL/SQL procedure successfully completed.

  663. SQL>
  664. SQL> set serveroutput off;
  665. SQL>
  666. SQL>
  667. SQL> Rem =======================================================================
  668. SQL> Rem Upgrade AWR Baseline information
  669. SQL> Rem =======================================================================
  670. SQL>
  671. SQL> @@awrblmig.sql
  672. SQL> Rem
  673. SQL> Rem $Header: awrblmig.sql 16-nov-2006.14:53:14 rburns Exp $
  674. SQL> Rem
  675. SQL> Rem awrblmig.sql
  676. SQL> Rem
  677. SQL> Rem Copyright (c) 2006, Oracle. All rights reserved.
  678. SQL> Rem
  679. SQL> Rem    NAME
  680. SQL> Rem         awrblmig.sql - AWR Baseline Migrate
  681. SQL> Rem
  682. SQL> Rem    DESCRIPTION
  683. SQL> Rem         This script is used to migrate the AWR Baseline data from
  684. SQL> Rem         the renamed BL tables back to the base tables.  This script is
  685. SQL> Rem         needed because the way the baselines are stored have been changed
  686. SQL> Rem         in 11g.  This script will
  687. SQL> Rem
  688. SQL> Rem    NOTES
  689. SQL> Rem         Run this script if you have AWR Baselines prior to the 11g release
  690. SQL> Rem         and have upgraded to the 11g release.
  691. SQL> Rem
  692. SQL> Rem    MODIFIED   (MM/DD/YY)
  693. SQL> Rem    rburns        11/16/06 - modify set statements
  694. SQL> Rem    mlfeng        06/18/06 - Script to migrate the AWR Baseline data
  695. SQL> Rem    mlfeng        06/18/06 - Created
  696. SQL> Rem
  697. SQL>
  698. SQL> set serveroutput on;
  699. SQL> exec dbms_output.enable(100000);

  701. PL/SQL procedure successfully completed.

  703. SQL>
  704. SQL> prompt

  706. SQL> prompt This script will migrate the Baseline data on a pre-11g database
  707. This script will migrate the Baseline data on a pre-11g database
  708. SQL> prompt to the 11g database.
  709. to the 11g database.
  710. SQL> prompt

  712. SQL>
  713. SQL> begin
  714.   2    dbms_swrf_internal.baseline_migrate(migrate_tables => TRUE,
  715.   3                                             drop_tables          => TRUE);
  716.   4  end;
  717.   5  /
  718. ...                                          ...
  719. ... Completed Moving the Baseline Data          ...
  720. ...                                          ...
  721. ... If there are no Move BL Data messages ...
  722. ... above, then there are no renamed          ...
  723. ... baseline tables in the system.          ...
  724. ...                                          ...
  725. ...                                          ...
  726. ... Completed the Dropping of the          ...
  727. ... Renamed Baseline Tables                  ...
  728. ...                                          ...
  729. ... If there are no Drop Table messages   ...
  730. ... above, then there are no renamed          ...
  731. ... baseline tables in the system.          ...
  732. ...                                          ...

  734. PL/SQL procedure successfully completed.

  736. SQL>
  737. SQL> set serveroutput off
  738. SQL>
  739. SQL> Rem =======================================================================
  740. SQL> Rem AWR inactive DBIDs check
  741. SQL> Rem =======================================================================
  742. SQL>
  743. SQL> set serveroutput on
  744. SQL>
  745. SQL> declare
  746.   2    cnt number;
  747.   3  begin
  748.   4    select count(*) into cnt
  749.   5           from sys.wrm$_wr_control
  750.   6          where bitand(status_flag, 4) = 4;
  751.   7    if (cnt > 0) then
  752.   8           dbms_output.put_line(
  753.   9             'AWR has data for ' || cnt || ' different DBID(s) that need updating.');
  754. 10           dbms_output.put_line(
  755. 11             'These are either imported AWR snapshots, or snapshots from before');
  756. 12           dbms_output.put_line(
  757. 13             'changing the DBID of the database. You will not be able to import');
  758. 14           dbms_output.put_line(
  759. 15             'additional data for those DBIDs until they are updated.');
  760. 16           dbms_output.put_line(
  761. 17             'Please use the rdbms/admin/awrupd12.sql script to update data for ' ||
  762. 18             'those DBIDs after the database has been upgraded.');
  763. 19    end if;
  764. 20  end;
  765. 21  /

  767. PL/SQL procedure successfully completed.

  769. SQL>
  770. SQL> set serveroutput off
  771. SQL>
  772. SQL> Rem =======================================================================
  773. SQL> Rem Upgrade ADDM task metadata
  774. SQL> Rem =======================================================================
  775. SQL>
  776. SQL> @@addmtmig.sql
  777. SQL> Rem
  778. SQL> Rem $Header: addmtmig.sql 26-feb-2007.04:29:23 sburanaw Exp $
  779. SQL> Rem
  780. SQL> Rem addmtmig.sql
  781. SQL> Rem
  782. SQL> Rem Copyright (c) 2007, Oracle. All rights reserved.
  783. SQL> Rem
  784. SQL> Rem    NAME
  785. SQL> Rem         addmtmig.sql - ADDM Task Migration
  786. SQL> Rem
  787. SQL> Rem    DESCRIPTION
  788. SQL> Rem         Post upgrade script for 11.1 release to fill new ADDM task
  789. SQL> Rem         metadata tables with derived information.
  790. SQL> Rem
  791. SQL> Rem    NOTES
  792. SQL> Rem         If this script is not included in post-upgrade, EM performance
  793. SQL> Rem         pages will not be able to display 10g ADDM tasks properly.
  794. SQL> Rem         There is no other effect on the database.
  795. SQL> Rem
  796. SQL> Rem    MODIFIED   (MM/DD/YY)
  797. SQL> Rem    sburanaw        02/23/07 - fix microsecond for wri$_adv_addm_task
  798. SQL> Rem    ushaft        02/05/07 - Created
  799. SQL> Rem
  800. SQL>
  801. SQL> Rem
  802. SQL> Rem Find the tasks that need to be upgraded.
  803. SQL> Rem
  804. SQL>
  805. SQL> insert into wri$_adv_addm_tasks
  806.   2  (TASK_ID,
  807.   3   DBID,
  808.   4   BEGIN_SNAP_ID,
  809.   5   END_SNAP_ID,
  810.   6   REQUESTED_ANALYSIS,
  811.   7   ACTUAL_ANALYSIS,
  812.   8   DATABASE_TIME)
  813.   9  select t.task_id,
  814. 10              to_number(p_dbid.parameter_value),
  815. 11              to_number(p_bid.parameter_value),
  816. 12              to_number(p_eid.parameter_value),
  817. 13              'INSTANCE', 'INSTANCE',
  818. 14              to_number(p_dbt.parameter_value)
  819. 15  from   dba_advisor_tasks t,
  820. 16              dba_advisor_parameters p_dbid,
  821. 17              dba_advisor_parameters p_bid,
  822. 18              dba_advisor_parameters p_eid,
  823. 19              dba_advisor_parameters p_dbt
  824. 20  where  t.advisor_name = 'ADDM'
  825. 21    and  t.status = 'COMPLETED'
  826. 22    and  p_dbid.task_id = t.task_id
  827. 23    and  p_dbid.parameter_name = 'DB_ID'
  828. 24    and  p_bid.task_id = t.task_id
  829. 25    and  p_bid.parameter_name = 'START_SNAPSHOT'
  830. 26    and  p_eid.task_id = t.task_id
  831. 27    and  p_eid.parameter_name = 'END_SNAPSHOT'
  832. 28    and  p_dbt.task_id = t.task_id
  833. 29    and  p_dbt.parameter_name = 'DB_ELAPSED_TIME'
  834. 30    and  t.task_id not in (select task_id from wri$_adv_addm_tasks);

  836. 0 rows created.

  838. SQL>
  839. SQL> commit;

  841. Commit complete.

  843. SQL>
  844. SQL> Rem
  845. SQL> Rem Add the estimated analysis version.
  846. SQL> Rem
  847. SQL>
  848. SQL> CREATE GLOBAL TEMPORARY TABLE addm$dbv (
  849.   2    exec_from         timestamp(3),
  850.   3    exec_to              timestamp(3),
  851.   4    db_version         varchar2(17)
  852.   5  )
  853.   6  ON COMMIT DELETE ROWS;

  855. Table created.

  857. SQL>
  858. SQL> insert into addm$dbv
  859.   2  select min(startup_time), null, version
  860.   3  from   wrm$_database_instance
  861.   4  where  dbid = (select dbid from v$database)
  862.   5  group by version;

  864. 2 rows created.

  866. SQL>
  867. SQL> update addm$dbv a
  868.   2  set    a.exec_from = (select cast(min(execution_end) as timestamp)
  869.   3                             from   dba_advisor_tasks)
  870.   4  where  a.exec_from =
  871.   5              (select min(exec_from)
  872.   6               from   addm$dbv);

  874. 1 row updated.

  876. SQL>
  877. SQL> update addm$dbv a
  878.   2  set    a.exec_to =
  879.   3             (select nvl(min(exec_from), cast(sysdate as timestamp))
  880.   4              from   addm$dbv b
  881.   5              where  b.exec_from > a.exec_from);

  883. 2 rows updated.

  885. SQL>
  886. SQL> update wri$_adv_addm_tasks t
  887.   2  set    t.analysis_version =
  888.   3              (select min(v.db_version)
  889.   4               from addm$dbv v, dba_advisor_tasks a
  890.   5               where  a.task_id = t.task_id
  891.   6                 and  cast(a.execution_end as timestamp) > v.exec_from
  892.   7                 and  cast(a.execution_end as timestamp) <= v.exec_to)
  893.   8  where  t.analysis_version IS NULL;

  895. 0 rows updated.

  897. SQL>
  898. SQL> drop table addm$dbv;

  900. Table dropped.

  902. SQL>
  903. SQL> commit;

  905. Commit complete.

  907. SQL>
  908. SQL>
  909. SQL> Rem
  910. SQL> Rem Add the database name, database version, snapshot end time.
  911. SQL> Rem
  912. SQL>
  913. SQL> update wri$_adv_addm_tasks t
  914.   2  set    (t.dbname, t.dbversion, t.end_time) =
  915.   3              (select min(d.db_name), min(d.version), min(s.end_interval_time)
  916.   4               from   wrm$_snapshot s, wrm$_database_instance d
  917.   5               where  s.dbid = t.dbid
  918.   6                 and  s.snap_id = t.end_snap_id
  919.   7                 and  d.dbid = t.dbid
  920.   8                 and  d.instance_number = s.instance_number
  921.   9                 and  d.startup_time = s.startup_time
  922. 10               )
  923. 11  where  t.analysis_version like '10.%'
  924. 12    and  t.dbname IS NULL
  925. 13    and  t.dbversion IS NULL
  926. 14    and  t.end_time IS NULL;

  928. 0 rows updated.

  930. SQL>
  931. SQL> commit;

  933. Commit complete.

  935. SQL>
  936. SQL> Rem
  937. SQL> Rem Add the snapshot begin time.
  938. SQL> Rem
  939. SQL>
  940. SQL> update wri$_adv_addm_tasks t
  941.   2  set    t.begin_time =
  942.   3              (select min(s.end_interval_time)
  943.   4               from   wrm$_snapshot s
  944.   5               where  s.dbid = t.dbid
  945.   6                 and  s.snap_id = t.begin_snap_id
  946.   7               )
  947.   8  where  t.analysis_version like '10.%'
  948.   9    and  t.begin_time IS NULL;

  950. 0 rows updated.

  952. SQL>
  953. SQL> commit;

  955. Commit complete.

  957. SQL>
  958. SQL> Rem
  959. SQL> Rem Add the active sessions
  960. SQL> Rem
  961. SQL>
  962. SQL> update wri$_adv_addm_tasks t
  963.   2  set    t.active_sessions =
  964.   3                 t.database_time /
  965.   4                  (extract(day          from t.end_time - t.begin_time) *24*60*60*1000000
  966.   5                   + extract(hour   from t.end_time - t.begin_time)    *60*60*1000000
  967.   6                   + extract(minute from t.end_time - t.begin_time)        *60*1000000
  968.   7                   + extract(second from t.end_time - t.begin_time)           *1000000 )
  969.   8  where  t.active_sessions IS NULL
  970.   9    and  t.begin_time IS NOT NULL
  971. 10    and  t.end_time IS NOT NULL
  972. 11    and  t.database_time IS NOT NULL
  973. 12    and  t.end_time > t.begin_time;

  975. 0 rows updated.

  977. SQL>
  978. SQL> commit;

  980. Commit complete.

  982. SQL>
  983. SQL>
  984. SQL> Rem
  985. SQL> Rem Add a row for each task into the wri$_adv_addm_inst table
  986. SQL> Rem
  987. SQL>
  988. SQL> insert into wri$_adv_addm_inst i
  989.   2  (TASK_ID,
  990.   3   INSTANCE_NUMBER,
  991.   4   INSTANCE_NAME,
  992.   5   HOST_NAME,
  993.   6   STATUS,
  994.   7   DATABASE_TIME,
  995.   8   ACTIVE_SESSIONS,
  996.   9   PERC_ACTIVE_SESS,
  997. 10   LOCAL_TASK_ID)
  998. 11  select t.task_id,
  999. 12              to_number(p.parameter_value),
  1000. 13              d.instance_name,
  1001. 14              d.host_name,
  1002. 15              'ANALYZED',
  1003. 16              t.database_time,
  1004. 17              t.active_sessions,
  1005. 18              100,
  1006. 19              t.task_id
  1007. 20  from   wri$_adv_addm_tasks t, dba_advisor_parameters p,
  1008. 21              wrm$_snapshot s, wrm$_database_instance d
  1009. 22  where  t.task_id not in (select task_id from wri$_adv_addm_inst)
  1010. 23    and  t.analysis_version like '10.%'
  1011. 24    and  t.actual_analysis = 'INSTANCE'
  1012. 25    and  p.task_id = t.task_id
  1013. 26    and  p.parameter_name = 'INSTANCE'
  1014. 27    and  p.parameter_value IS NOT NULL
  1015. 28    and  p.parameter_value <> 'UNUSED'
  1016. 29    and  s.dbid = t.dbid
  1017. 30    and  s.snap_id = t.end_snap_id
  1018. 31    and  s.instance_number =
  1019. 32              to_number(decode(p.parameter_value, 'UNUSED', NULL, p.parameter_value))
  1020. 33    and  d.dbid = t.dbid
  1021. 34    and  d.instance_number = s.instance_number
  1022. 35    and  d.startup_time = s.startup_time;

  1024. 0 rows created.

  1026. SQL>
  1027. SQL> commit;

  1029. Commit complete.

  1031. SQL>
  1032. SQL>
  1033. SQL> Rem
  1034. SQL> Rem Add a row for each the findings
  1035. SQL> Rem
  1036. SQL>
  1037. SQL> insert into wri$_adv_addm_fdg
  1038.   2  (TASK_ID,
  1039.   3   FINDING_ID,
  1040.   4   DATABASE_TIME,
  1041.   5   ACTIVE_SESSIONS,
  1042.   6   PERC_ACTIVE_SESS,
  1043.   7   IS_AGGREGATE
  1044.   8  )
  1045.   9  select t.task_id,
  1046. 10              a.finding_id,
  1047. 11              a.impact,
  1048. 12              (a.impact * t.active_sessions) / t.database_time,
  1049. 13              (a.impact * 100) / t.database_time,
  1050. 14              'N'
  1051. 15  from   wri$_adv_addm_tasks t, dba_advisor_findings a
  1052. 16  where  (t.task_id, a.finding_id) not in
  1053. 17                  (select task_id, finding_id from wri$_adv_addm_fdg)
  1054. 18    and  t.analysis_version like '10.%'
  1055. 19    and  t.actual_analysis = 'INSTANCE'
  1056. 20    and  t.task_id = a.task_id
  1057. 21    and  a.type in ('PROBLEM', 'SYMPTOM')
  1058. 22    and  t.database_time > 0;

  1060. 0 rows created.

  1062. SQL>
  1063. SQL> commit;

  1065. Commit complete.

  1067. SQL>
  1068. SQL> Rem =======================================================================
  1069. SQL> Rem If OLS in the database, run olstrig.sql to updated OLS policies
  1070. SQL> Rem =======================================================================
  1071. SQL>
  1072. SQL> COLUMN :ols_name NEW_VALUE ols_file NOPRINT;
  1073. SQL> VARIABLE ols_name VARCHAR2(30)
  1074. SQL> DECLARE
  1075.   2  BEGIN
  1076.   3          IF dbms_registry.is_loaded('OLS') IS NOT NULL THEN
  1077.   4             :ols_name := '@olstrig.sql';   -- OLS installed in DB
  1078.   5          ELSE
  1079.   6             :ols_name := dbms_registry.nothing_script;        -- No OLS
  1080.   7          END IF;
  1081.   8  END;
  1082.   9  /

  1084. PL/SQL procedure successfully completed.

  1086. SQL> SELECT :ols_name FROM DUAL;




  1091. SQL> @&ols_file
  1092. SQL> Rem $Header: rdbms/admin/nothing.sql /st_rdbms_12.1/1 2014/06/03 11:24:49 aketkar Exp $
  1093. SQL> Rem
  1094. SQL> Rem
  1095. SQL> Rem BEGIN SQL_FILE_METADATA
  1096. SQL> Rem SQL_SOURCE_FILE: rdbms/admin/nothing.sql
  1097. SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/nothing.sql
  1098. SQL> Rem SQL_PHASE: NOTHING
  1099. SQL> Rem SQL_STARTUP_MODE: NORMAL
  1100. SQL> Rem SQL_IGNORABLE_ERRORS: NONE
  1101. SQL> Rem SQL_CALLING_FILE: NONE
  1102. SQL> Rem END SQL_FILE_METADATA
  1103. SQL> Rem
  1104. SQL>
  1105. SQL>
  1106. SQL> Rem =======================================================================
  1107. SQL> Rem If EM in the database, run @emremove.sql to remove EM schema
  1108. SQL> Rem =======================================================================
  1109. SQL>
  1110. SQL> COLUMN :em_name NEW_VALUE em_file NOPRINT;
  1111. SQL> VARIABLE em_name VARCHAR2(30)
  1112. SQL> DECLARE
  1113.   2  BEGIN
  1114.   3          IF dbms_registry.is_loaded('EM') IS NOT NULL THEN
  1115.   4             :em_name := '@emremove.sql';   -- EM exists in DB
  1116.   5          ELSE
  1117.   6             :em_name := dbms_registry.nothing_script;   -- No EM
  1118.   7          END IF;
  1119.   8  END;
  1120.   9  /

  1122. PL/SQL procedure successfully completed.

  1124. SQL> SELECT :em_name FROM DUAL;




  1129. SQL> @&em_file
  1130. SQL> Rem $Header: rdbms/admin/nothing.sql /st_rdbms_12.1/1 2014/06/03 11:24:49 aketkar Exp $
  1131. SQL> Rem
  1132. SQL> Rem
  1133. SQL> Rem BEGIN SQL_FILE_METADATA
  1134. SQL> Rem SQL_SOURCE_FILE: rdbms/admin/nothing.sql
  1135. SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/nothing.sql
  1136. SQL> Rem SQL_PHASE: NOTHING
  1137. SQL> Rem SQL_STARTUP_MODE: NORMAL
  1138. SQL> Rem SQL_IGNORABLE_ERRORS: NONE
  1139. SQL> Rem SQL_CALLING_FILE: NONE
  1140. SQL> Rem END SQL_FILE_METADATA
  1141. SQL> Rem
  1142. SQL>
  1143. SQL> Rem =======================================================================
  1144. SQL> Rem Network ACL migration status check
  1145. SQL> Rem =======================================================================
  1146. SQL>
  1147. SQL> set serveroutput on
  1148. SQL>
  1149. SQL> declare
  1150.   2    cnt number;
  1151.   3  begin
  1152.   4    select count(*) into cnt from dba_xs_acls
  1153.   5          where owner = 'SYS' and name like 'PRE_12_1_NETWORK_ACL_%';
  1154.   6    if (cnt > 0) then
  1155.   7           dbms_output.put_line(
  1156.   8             cnt || ' network access control lists (ACL) from releases earlier than');
  1157.   9           dbms_output.put_line(
  1158. 10             'Oracle Database 12.1 are not migrated. Please refer to the "Oracle');
  1159. 11           dbms_output.put_line(
  1160. 12             'Database Upgrade Guide" section titled "Configure Fine-Grained Access');
  1161. 13           dbms_output.put_line(
  1162. 14             'to External Network Services After Upgrading Oracle Database" for');
  1163. 15           dbms_output.put_line(
  1164. 16             'more information.');
  1165. 17    end if;
  1166. 18  end;
  1167. 19  /

  1169. PL/SQL procedure successfully completed.

  1171. SQL>
  1172. SQL> set serveroutput off
  1173. SQL>
  1174. SQL> Rem =======================================================================
  1175. SQL> Rem AQ post upgrade code start
  1176. SQL> Rem =======================================================================
  1177. SQL> set serveroutput on
  1178. SQL> -- create dequeue logs and recreate base views for old multiconsumer qtables
  1179. SQL> declare
  1180.   2    cursor qt_cur is select schema, name, flags from system.aq$_queue_tables;
  1181.   3  begin
  1182.   4    -- only execute the script if version is in 11.1 or 10.2
  1183.   5    for qt in qt_cur loop
  1184.   6           if (sys.dbms_aqadm_sys.mcq_8_1(qt.flags)) then
  1185.   7             begin
  1186.   8               if not sys.dbms_aqadm_sys.object_exists(qt.schema, 'AQ$_' || qt.name ||
  1187.   9                '_L', 'TABLE') then
  1188. 10                 sys.dbms_prvtaqim.create_dequeue_log(qt.schema, qt.name, qt.flags);
  1189. 11                 sys.dbms_prvtaqim.create_base_view(qt.schema, qt.name, qt.flags);
  1190. 12               end if;
  1191. 13             exception
  1192. 14               when others then
  1193. 15                 dbms_system.ksdwrt(dbms_system.alert_file,
  1194. 16                                'catuppst.sql: create dequeue log or recreate base' ||
  1195. 17                                ' view failed for ' || qt.schema || '.' || qt.name || ', error msg ' || SQLERRM);
  1196. 18             end;
  1197. 19           end if;
  1198. 20           -- validate queues in post upgrade only for scheduler queue
  1199. 21           if (qt.name = 'SCHEDULER$_EVENT_QTAB') then
  1200. 22             sys.dbms_aqadm_sys.validate_qtab_queues(qt.schema, qt.name);
  1201. 23             dbms_aqadm.start_queue(queue_name => 'scheduler$_event_queue');
  1202. 24           end if;
  1203. 25    end loop;
  1204. 26  end;
  1205. 27  /

  1207. PL/SQL procedure successfully completed.

  1209. SQL> set serveroutput off
  1210. SQL> Rem =======================================================================
  1211. SQL> Rem AQ post upgrade code end
  1212. SQL> Rem =======================================================================
  1213. SQL>
  1214. SQL>
  1215. SQL> Rem =======================================================================
  1216. SQL> Rem Bug 14258301 - Gather fixed objects stats IF NONE of the fixed object
  1217. SQL> Rem tables has stats
  1218. SQL> Rem =======================================================================
  1219. SQL>
  1220. SQL> set serveroutput on
  1221. SQL> declare
  1222.   2    has_stats_cnt  number := 0;  -- # of fixed object tables that have stats
  1223.   3  begin
  1224.   4    -- find # of fixed object tables that have had stats collected
  1225.   5    execute immediate
  1226.   6           'select count(*) ' ||
  1227.   7           'from sys.dba_tab_statistics ' ||
  1228.   8           ' where owner = ''SYS'' and table_name like ''X$%'' ' ||
  1229.   9           '         and last_analyzed is not null'
  1230. 10           into has_stats_cnt;
  1231. 11  
  1232. 12    -- if none of the fixed obj tables have had stats collected
  1233. 13    -- then gather fixed objects stats
  1234. 14    -- else do nothing
  1235. 15    if (has_stats_cnt = 0) then
  1236. 16           sys.dbms_output.put_line('catuppst: Gathering fixed objects stats now...');
  1237. 17           sys.dbms_stats.gather_fixed_objects_stats;
  1238. 18           sys.dbms_output.put_line('catuppst: Gathering fixed objects stats done.');
  1239. 19    end if;
  1240. 20  end;
  1241. 21  /

  1243. PL/SQL procedure successfully completed.

  1245. SQL> set serveroutput off
  1246. SQL>
  1247. SQL> Rem =======================================================================
  1248. SQL> Rem Gather Fixed Objects Stats end
  1249. SQL> Rem =======================================================================
  1250. SQL>
  1251. SQL>
  1252. SQL> Rem =======================================================================
  1253. SQL> Rem Signal 'end' of catuppst.sql before catbundle.sql is executed
  1254. SQL> Rem =======================================================================
  1255. SQL> SELECT dbms_registry_sys.time_stamp('POSTUP_END') as timestamp from dual;

  1257. TIMESTAMP
  1258. --------------------------------------------------------------------------------
  1259. COMP_TIMESTAMP POSTUP_END 2020-12-13 15:13:36

  1261. SQL>
  1262. SQL>
  1263. SQL> Rem Set errorlogging off
  1264. SQL> SET ERRORLOGGING OFF;
  1265. SQL>
  1266. SQL> Rem
  1267. SQL> Rem Set _ORACLE_SCRIPT to false
  1268. SQL> Rem
  1269. SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=false;

  1271. Session altered.

  1273. SQL>
  1274. SQL>
  1275. SQL> Rem *********************************************************************
  1276. SQL> Rem END catuppst.sql
  1277. SQL> Rem *********************************************************************
  1278. SQL>
  1279. SQL>
复制代码
  1. SQL> @/u01/app/oracle/cfgtoollogs/rcat/preupgrade/postupgrade_fixups.sql
  2. SQL> REM Post Upgrade Script Generated on: 2020-12-10 21:07:56
  3. SQL> REM Generated by Version: 12.1.0.2 Build: 006
  4. SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 750;
  5. Post Upgrade Fixup Script Generated on 2020-12-10 21:07:56  Version: 12.1.0.2 Build: 006
  6. Beginning Post-Upgrade Fixups...

  8. **********************************************************************
  9. Check Tag:     OLD_TIME_ZONES_EXIST
  10. Check Summary: Check for use of older timezone data file
  11. Fix Summary:   Update the timezone using the DBMS_DST package after upgrade is complete.
  12. **********************************************************************
  13. Fixup Returned Information:
  14. INFORMATION: --> Older Timezone in use

  16.      Database is using a time zone file older than version 18.
  17.      After the upgrade, it is recommended that DBMS_DST package
  18.      be used to upgrade the 12.1.0.2.0 database time zone version
  19.      to the latest version which comes with the new release.
  20.      Please refer to My Oracle Support note number 977512.1 for details.
  21. **********************************************************************


  24. **********************************************************************
  25. Check Tag:     NOT_UPG_BY_STD_UPGRD
  26. Check Summary: Identify existing components that will NOT be upgraded
  27. Fix Summary:   This fixup does not perform any action.
  28. **********************************************************************
  29. Fixup Returned Information:
  30. This fixup does not perform any action.  
  31. If you want to upgrade those other components, you must do so manually.
  32. **********************************************************************


  35. **********************************************************************
  36.                      [Post-Upgrade Recommendations]
  37. **********************************************************************

  39.                         *****************************************
  40.                         ******** Fixed Object Statistics ********
  41.                         *****************************************

  43. Please create stats on fixed objects two weeks
  44. after the upgrade using the command:
  45.    EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

  47. ^^^ MANUAL ACTION SUGGESTED ^^^


  50.            **************************************************
  51.                 ************* Fixup Summary ************

  53. 2 fixup routines generated INFORMATIONAL messages that should be reviewed.

  55. *************** Post Upgrade Fixup Script Complete ********************

  57. PL/SQL procedure successfully completed.

复制代码


  1. SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
复制代码


回复

使用道具 举报

返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-4-29 05:01 , Processed in 0.042698 second(s), 24 queries .

快速回复 返回顶部 返回列表