|
11.2.0.3上:
- [oracle@classroom ~]$ . oraenv
- ORACLE_SID = [orcl] ? rcat
- The Oracle base remains unchanged with value /u01/app/oracle
- [oracle@classroom ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.3.0 Production on 星期四 12月 10 21:00:30 2020
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- SQL> conn / as sysdba
- Connected to an idle instance.
- SQL> startup
- ORACLE instance started.
- Total System Global Area 1603411968 bytes
- Fixed Size 2228784 bytes
- Variable Size 503320016 bytes
- Database Buffers 1090519040 bytes
- Redo Buffers 7344128 bytes
- Database mounted.
- Database opened.
- SQL> @/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/preupgrd.sql
- Loading Pre-Upgrade Package...
- ***************************************************************************
- Executing Pre-Upgrade Checks in RCAT...
- ***************************************************************************
- ************************************************************
- ====>> ERRORS FOUND for RCAT <<====
- The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
- prior to attempting your upgrade.
- Failure to do so will result in a failed upgrade.
- 1) Check Tag: PURGE_RECYCLEBIN
- Check Summary: Check that recycle bin is empty prior to upgrade
- Fixup Summary:
- "The recycle bin will be purged."
- You MUST resolve the above error prior to upgrade
- ************************************************************
- ************************************************************
- ====>> PRE-UPGRADE RESULTS for RCAT <<====
- ACTIONS REQUIRED:
- 1. Review results of the pre-upgrade checks:
- /u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade.log
- 2. Execute in the SOURCE environment BEFORE upgrade:
- /u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade_fixups.sql
- 3. Execute in the NEW environment AFTER upgrade:
- /u01/app/oracle/cfgtoollogs/rcat/preupgrade/postupgrade_fixups.sql
- ************************************************************
- ***************************************************************************
- Pre-Upgrade Checks in RCAT Completed.
- ***************************************************************************
- ***************************************************************************
- ***************************************************************************
- SQL>
复制代码- SQL> @/u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade_fixups.sql
- Pre-Upgrade Fixup Script Generated on 2020-12-10 21:07:56 Version: 12.1.0.2 Build: 006
- Beginning Pre-Upgrade Fixups...
- Executing in container RCAT
- **********************************************************************
- Check Tag: EM_PRESENT
- Check Summary: Check if Enterprise Manager is present
- Fix Summary: Execute emremove.sql prior to upgrade.
- **********************************************************************
- Fixup Returned Information:
- WARNING: --> Enterprise Manager Database Control repository found in the database
- In Oracle Database 12c, Database Control is removed during
- the upgrade. To save time during the Upgrade, this action
- can be done prior to upgrading using the following steps after
- copying rdbms/admin/emremove.sql from the new Oracle home
- - Stop EM Database Control:
- [ DISCUZ_CODE_2 ]gt; emctl stop dbconsole
- - Connect to the Database using the SYS account AS SYSDBA:
- SET ECHO ON;
- SET SERVEROUTPUT ON;
- @emremove.sql
- Without the set echo and serveroutput commands you will not
- be able to follow the progress of the script.
- **********************************************************************
- **********************************************************************
- Check Tag: AMD_EXISTS
- Check Summary: Check to see if AMD is present in the database
- Fix Summary: Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
- **********************************************************************
- Fixup Returned Information:
- INFORMATION: --> OLAP Catalog(AMD) exists in database
- Starting with Oracle Database 12c, OLAP Catalog component is desupported.
- If you are not using the OLAP Catalog component and want
- to remove it, then execute the
- ORACLE_HOME/olap/admin/catnoamd.sql script before or
- after the upgrade.
- **********************************************************************
- **********************************************************************
- Check Tag: PURGE_RECYCLEBIN
- Check Summary: Check that recycle bin is empty prior to upgrade
- Fix Summary: The recycle bin will be purged.
- **********************************************************************
- Fixup Succeeded
- **********************************************************************
- **********************************************************************
- Check Tag: APEX_UPGRADE_MSG
- Check Summary: Check that APEX will need to be upgraded.
- Fix Summary: Oracle Application Express can be manually upgraded prior to database upgrade.
- **********************************************************************
- Fixup Returned Information:
- INFORMATION: --> Oracle Application Express (APEX) can be
- manually upgraded prior to database upgrade
- APEX is currently at version 3.2.1.00.10 and will need to be
- upgraded to APEX version 4.2.5 in the new release.
- Note 1: To reduce database upgrade time, APEX can be manually
- upgraded outside of and prior to database upgrade.
- Note 2: See MOS Note 1088970.1 for information on APEX
- installation upgrades.
- **********************************************************************
- **********************************************************************
- [Pre-Upgrade Recommendations]
- **********************************************************************
- *****************************************
- ********* Dictionary Statistics *********
- *****************************************
- Please gather dictionary statistics 24 hours prior to
- upgrading the database.
- To gather dictionary statistics execute the following command
- while connected as SYSDBA:
- EXECUTE dbms_stats.gather_dictionary_stats;
- ^^^ MANUAL ACTION SUGGESTED ^^^
- **************************************************
- ************* Fixup Summary ************
- 1 fixup routine was successful.
- 3 fixup routines returned INFORMATIONAL text that should be reviewed.
- **************** Pre-Upgrade Fixup Script Complete *********************
- SQL>
复制代码- SQL> shutdown immediate
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL>
复制代码
-----------------------------------------------------------------------------------------------------------------------
- #Backup file is /u01/app/oracle/product/12.1.0/grid/srvm/admin/oratab.bak.classroom line added by Agent
- #
- # This file is used by ORACLE utilities. It is created by root.sh
- # and updated by either Database Configuration Assistant while creating
- # a database or ASM Configuration Assistant while creating ASM instance.
- # A colon, ':', is used as the field terminator. A new line terminates
- # the entry. Lines beginning with a pound sign, '#', are comments.
- #
- # Entries are of the form:
- # $ORACLE_SID:$ORACLE_HOME:<N|Y>:
- #
- # The first and second fields are the system identifier and home
- # directory of the database respectively. The third field indicates
- # to the dbstart utility that the database should , "Y", or should not,
- # "N", be brought up at system boot time.
- #
- # Multiple entries with the same $ORACLE_SID are not allowed.
- #
- #
- em12rep:/u01/app/oracle/product/12.1.0/dbhome_1:N # line added by Agent
- orcl2:/u01/app/oracle/product/12.1.0/dbhome_1:N # line added by Agent
- orcl:/u01/app/oracle/product/12.1.0/dbhome_2:N # line added by Agent
- cdb1:/u01/app/oracle/product/12.1.0/dbhome_1:N # line added by Agent
- rcat:/u01/app/oracle/product/12.1.0/dbhome_1:N # line added by Agent
- +ASM:/u01/app/oracle/product/12.1.0/grid:N # line added by Agen
复制代码- [oracle@classroom ~]$ . oraenv
- ORACLE_SID = [rcat] ? rcat
- The Oracle base remains unchanged with value /u01/app/oracle
- [oracle@classroom ~]$ which sqlplus
- /u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus
- [oracle@classroom ~]$ sqlplus /nolog
- SQL*Plus: Release 12.1.0.2.0 Production on 星期四 12月 10 21:17:28 2020
- Copyright (c) 1982, 2014, Oracle. All rights reserved.
- SQL> conn / as sysdba
- Connected to an idle instance.
- SQL> startup upgrade
- ORA-01078: failure in processing system parameters
- LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initrcat.ora'
- SQL>
复制代码- [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
- [oracle@classroom ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwrcat /u01/app/oracle/product/12.1.0/dbhome_1/dbs/
- [oracle@classroom ~]$
复制代码- [oracle@classroom ~]$ sqlplus /nolog
- SQL*Plus: Release 12.1.0.2.0 Production on 星期四 12月 10 21:24:59 2020
- Copyright (c) 1982, 2014, Oracle. All rights reserved.
- SQL> conn / as sysdba
- Connected to an idle instance.
- SQL> startup upgrade
- ORACLE instance started.
- Total System Global Area 1610612736 bytes
- Fixed Size 2924928 bytes
- Variable Size 671092352 bytes
- Database Buffers 922746880 bytes
- Redo Buffers 13848576 bytes
- Database mounted.
- Database opened.
- SQL> @?/rdbms/admin/catupgrd.sql
- DOC>######################################################################
- DOC>######################################################################
- DOC> NOTE
- DOC>
- DOC> The catupgrd.sql is being deprecated in the 12.1 release of the
- DOC> Oracle Database. Customers are encouraged to use catctl.pl as
- DOC> the replacement for catupgrd.sql when upgrading the database dictionary.
- DOC>
- DOC> cd $ORACLE_HOME/rdbms/admin
- DOC> $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
- DOC>
- DOC> Refer to the Oracle Database Upgrade Guide for more information.
- DOC>
- DOC> This database upgrade procedure must be called with the following
- DOC> argument when invoking from the SQL prompt:
- DOC>
- DOC> @catupgrd.sql PARALLEL=NO
- DOC>
- DOC>######################################################################
- DOC>######################################################################
- DOC>#
- old 2: WHERE UPPER('&&1') = 'PARALLEL=NO' OR
- new 2: WHERE UPPER('') = 'PARALLEL=NO' OR
- old 3: UPPER('&&1') = 'PARALLEL=YES'
- new 3: UPPER('') = 'PARALLEL=YES'
- SELECT (to_number(count(*)))/(to_number(count(*))) FROM DUAL
- *
- ERROR at line 1:
- ORA-01476: 除数为 0
- Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
- With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
- and Unified Auditing options
- [oracle@classroom ~]$
复制代码- [oracle@classroom admin]$ pwd
- /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin
- [oracle@classroom admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
复制代码- /u01/app/oracle/cfgtoollogs/rcat/preupgrade/postupgrade_fixups.sql
复制代码
---------------------------------------------------------------
- SQL> @?/rdbms/admin/utlu121s.sql
- PL/SQL procedure successfully completed.
- PL/SQL procedure successfully completed.
- CATCTL REPORT = /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/rcat/upgrade/upg_summary.log
- PL/SQL procedure successfully completed.
- Oracle Database 12.1 Post-Upgrade Status Tool 12-13-2020 15:11:11
- Component Current Version Elapsed Time
- Name Status Number HH:MM:SS
- Oracle Server UPGRADED 12.1.0.2.0 00:21:48
- JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:04:52
- Oracle Workspace Manager VALID 12.1.0.2.0 00:01:23
- OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:47
- OLAP Catalog OPTION OFF 11.2.0.3.0 00:00:00
- Oracle OLAP API VALID 12.1.0.2.0 00:00:25
- Oracle XDK VALID 12.1.0.2.0 00:01:07
- Oracle Text VALID 12.1.0.2.0 00:01:40
- Oracle XML Database VALID 12.1.0.2.0 00:03:04
- Oracle Database Java Packages VALID 12.1.0.2.0 00:00:18
- Oracle Multimedia VALID 12.1.0.2.0 00:02:40
- Spatial UPGRADED 12.1.0.2.0 00:06:44
- Oracle Application Express VALID 4.2.5.00.08 00:36:01
- Final Actions 00:02:04
- Post Upgrade 00:03:27
- Total Upgrade Time: 01:27:14
- PL/SQL procedure successfully completed.
- SQL>
- SQL> --
- SQL> -- Update Summary Table with con_name and endtime.
- SQL> --
- SQL> UPDATE sys.registry$upg_summary SET reportname = :ReportName,
- 2 con_name = SYS_CONTEXT('USERENV','CON_NAME'),
- 3 endtime = SYSDATE
- 4 WHERE con_id = -1;
- 1 row updated.
- SQL> commit;
- Commit complete.
- SQL>
复制代码- SQL> @?/rdbms/admin/catuppst.sql
- SQL> Rem
- SQL> Rem $Header: rdbms/admin/catuppst.sql /st_rdbms_12.1/1 2014/06/11 20:58:01 surman Exp $
- SQL> Rem
- SQL> Rem catuppst.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2006, 2014, Oracle and/or its affiliates.
- SQL> Rem All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem catuppst.sql - CATalog UPgrade PoST-upgrade actions
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem This post-upgrade script performs remaining upgrade actions that
- SQL> Rem do not require that the database be open in UPGRADE mode.
- SQL> Rem Automatically apply the latest PSU.
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem You must be connected AS SYSDBA to run this script.
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem surman 06/05/14 - Backport surman_bug-17277459 from main
- SQL> Rem surman 05/19/14 - 17277459: Remove call to catbundle
- SQL> Rem jerrede 01/17/14 - Fix Bug 18071399 Add Post Upgrade Report Time
- SQL> Rem surman 05/31/13 - 16790144: Use @@
- SQL> Rem cmlim 05/15/13 - bug 16816410: add table name to errorlogging
- SQL> Rem syntax
- SQL> Rem surman 03/19/13 - 16094163: Add catbundleapply.sql
- SQL> Rem cmlim 03/01/13 - bug 16306200: remove the workaround (added in
- SQL> Rem txn in bug 16085743) that re-updated
- SQL> Rem oracle-supplied bit in views owned by SYS after
- SQL> Rem bootstrap. Workaround not needed once the shared
- SQL> Rem pool is flushed in catuposb.sql (bug 16306200).
- SQL> Rem jerrede 01/14/13 - XbranchMerge jerrede_bug-16097914 from
- SQL> Rem st_rdbms_12.1.0.1
- SQL> Rem jerrede 01/11/13 - Move Removal of EXF/RUL to upgrade.
- SQL> Rem LogMiner/Standyby can not deal with removing
- SQL> Rem a component outside of upgrade.
- SQL> Rem sjanardh 01/10/13 - XbranchMerge maba_bug-14615619 from main
- SQL> Rem jerrede 12/19/12 - Bug#16025279 Add Event for Not Removing EXF/RUL
- SQL> Rem Upgrade Components
- SQL> Rem surman 12/10/12 - XbranchMerge surman_bug-12876907 from main
- SQL> Rem maba 11/26/12 - fixed bug 14615619
- SQL> Rem jerrede 11/05/12 - Add Exadata Bundle support
- SQL> Rem cmlim 10/27/12 - bug 14258301 : gather fixed obj stats if none of
- SQL> Rem the fixed object tables have had stats collected
- SQL> Rem mfallen 09/20/12 - bug 14390165: check if AWR data needs update
- SQL> Rem jerrede 10/23/12 - Unset _ORACLE_SCRIPT
- SQL> Rem jerrede 10/23/12 - Add Session Info
- SQL> Rem maba 09/13/12 - added create dequeue log for bug 14278722
- SQL> Rem jerrede 06/26/12 - Set event to optionally update required stats
- SQL> Rem during upgrade
- SQL> Rem rpang 05/21/12 - Add network ACL migration status check
- SQL> Rem traney 05/09/12 - lrg 6949943: mask ORA-942s
- SQL> Rem jerrede 04/17/12 - Moved Mandatory Changes to catrequired.sql
- SQL> Rem traney 04/04/12 - lrg 6762280: drop DBMS_DDL_INTERNAL_LIB
- SQL> Rem traney 03/12/12 - bug 13719175: move post-utlmmig stats here
- SQL> Rem cdilling 12/13/11 - drop SYSMAN schema - removal of EM component for
- SQL> Rem upgrade to 12.1
- SQL> Rem aramappa 06/22/11 - Always run olstrig.sql when OLS installed in DB
- SQL> Rem xbarr 04/28/11 - move DMSYS removal code to odmu112.sql
- SQL> Rem xbarr 10/25/10 - run dmsysrem.sql to drop DMSYS schema if exists
- SQL> Rem cdilling 07/21/10 - add call to catbundle.sql for bug 9925339
- SQL> Rem srtata 12/16/08 - run olstrig.sql when upgrading from prior to 10.2
- SQL> Rem srtata 10/15/08 - put back olstrig.sql as we found it cannot be run
- SQL> Rem as part of upgrade
- SQL> Rem srtata 02/26/08 - move olstrig.sql to olsdbmig.sql
- SQL> Rem ushaft 02/05/07 - post upgrade for ADDM tasks.
- SQL> Rem cdilling 12/06/06 - add support for error logging
- SQL> Rem rburns 11/10/06 - post upgrade actions
- SQL> Rem rburns 11/10/06 - Created
- SQL> Rem
- SQL>
- SQL> Rem =====================================================================
- SQL> Rem Call Common session settings
- SQL> Rem =====================================================================
- SQL> @@catpses.sql
- SQL> Rem
- SQL> Rem $Header: rdbms/admin/catpses.sql /main/3 2014/02/20 12:45:39 surman Exp $
- SQL> Rem
- SQL> Rem catpses.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2006, 2014, Oracle and/or its affiliates.
- SQL> Rem All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem catpses.sql - CATalog and CATProc SESsion script
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem This script initializes the session for running catalog
- SQL> Rem and/or catproc scripts
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem It is used as the session script for parallel processes
- SQL> Rem when catalog.sql and/or catproc.sql is run using multiprocesses
- SQL> Rem
- SQL> Rem BEGIN SQL_FILE_METADATA
- SQL> Rem SQL_SOURCE_FILE: rdbms/admin/catpses.sql
- SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/catpses.sql
- SQL> Rem SQL_PHASE: CATPSES
- SQL> Rem SQL_STARTUP_MODE: NORMAL
- SQL> Rem SQL_IGNORABLE_ERRORS: NONE
- SQL> Rem SQL_CALLING_FILE: rdbms/admin/cdstrt.sql
- SQL> Rem END SQL_FILE_METADATA
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem surman 12/29/13 - 13922626: Update SQL metadata
- SQL> Rem jerrede 05/08/12 - Added session info for CDB.
- SQL> Rem rburns 10/23/06 - add session script
- SQL> Rem rburns 10/23/06 - Created
- SQL> Rem
- SQL>
- SQL> @@?/rdbms/admin/sqlsessstart.sql
- SQL> Rem
- SQL> Rem $Header: rdbms/admin/sqlsessstart.sql /main/1 2013/03/13 13:08:33 surman Exp $
- SQL> Rem
- SQL> Rem sqlsessstart.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem sqlsessstart.sql - SQL session start
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem Any commands which should be run at the start of all oracle
- SQL> Rem supplied scripts.
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem See sqlsessend.sql for the corresponding end script.
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem surman 03/08/13 - 16462837: Common start and end scripts
- SQL> Rem surman 03/08/13 - Created
- SQL> Rem
- SQL>
- SQL> alter session set "_ORACLE_SCRIPT" = true;
- Session altered.
- SQL>
- SQL> Rem =====================================================================
- SQL> Rem Assure CHAR semantics are not used in the dictionary
- SQL> Rem =====================================================================
- SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
- Session altered.
- SQL>
- SQL> Rem =====================================================================
- SQL> Rem Needed for Consolidated databases
- SQL> Rem =====================================================================
- SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=true;
- Session altered.
- SQL>
- SQL>
- SQL>
- SQL> @?/rdbms/admin/sqlsessend.sql
- SQL> Rem
- SQL> Rem $Header: rdbms/admin/sqlsessend.sql /main/1 2013/03/13 13:08:33 surman Exp $
- SQL> Rem
- SQL> Rem sqlsessend.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem sqlsessend.sql - SQL session end
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem Any commands which should be run at the end of all oracle
- SQL> Rem supplied scripts.
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem See sqlsessstart.sql for the corresponding start script.
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem surman 03/08/13 - 16462837: Common start and end scripts
- SQL> Rem surman 03/08/13 - Created
- SQL> Rem
- SQL>
- SQL> alter session set "_ORACLE_SCRIPT" = false;
- Session altered.
- SQL>
- SQL>
- SQL>
- SQL> Rem *********************************************************************
- SQL> Rem BEGIN catuppst.sql
- SQL> Rem *********************************************************************
- SQL> Rem Set identifier to POSTUP for errorlogging
- SQL>
- SQL> SET ERRORLOGGING ON TABLE SYS.REGISTRY$ERROR IDENTIFIER 'POSTUP';
- SQL>
- SQL> SELECT dbms_registry_sys.time_stamp('POSTUP_BGN') as timestamp from dual;
- TIMESTAMP
- --------------------------------------------------------------------------------
- COMP_TIMESTAMP POSTUP_BGN 2020-12-13 15:13:34
- SQL>
- SQL> Rem =======================================================================
- SQL> Rem Run Post Upgrade Operations
- SQL> Rem =======================================================================
- SQL>
- SQL> @@catrequired.sql
- SQL> Rem
- SQL> Rem $Header: rdbms/admin/catrequired.sql /main/1 2012/07/19 11:27:56 jerrede Exp $
- SQL> Rem
- SQL> Rem catrequired.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2006, 2012, Oracle and/or its affiliates.
- SQL> Rem All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem catrequired.sql - Catalog Mandatory Upgrade Script
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem This catalog script is a place holder
- SQL> Rem for other things that may be added in the future.
- SQL> Rem Right now it only calls catrequtlmg.sql.
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem You must be connected AS SYSDBA to run this script.
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem jerrede 04/17/12 - Created
- SQL> Rem
- SQL>
- SQL>
- SQL> Rem *********************************************************************
- SQL> Rem BEGIN catrequired.sql
- SQL> Rem *********************************************************************
- SQL>
- SQL> Rem
- SQL> Rem Display Start TimeStamp
- SQL> Rem
- SQL> SELECT sys.dbms_registry_sys.time_stamp('catreq_bgn') as timestamp from dual;
- TIMESTAMP
- --------------------------------------------------------------------------------
- COMP_TIMESTAMP CATREQ_BGN 2020-12-13 15:13:34
- SQL>
- SQL>
- SQL> Rem
- SQL> Rem Post-utlmmig statistics gathering
- SQL> Rem
- SQL> @@catrequtlmg.sql
- SQL> Rem
- SQL> Rem $Header: rdbms/admin/catrequtlmg.sql /main/2 2013/01/09 14:23:01 jerrede Exp $
- SQL> Rem
- SQL> Rem catrequtlmg.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2006, 2013, Oracle and/or its affiliates.
- SQL> Rem All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem catrequtlmg.sql - Catalog Mandatory Upgrade Script
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem This catalog script can run from utlmmig.sql or catuppst.sql.
- SQL> Rem The event _utlmmig_table_stats_gathering determines where it
- SQL> Rem is run. If TRUE (the default) it is run from utlmmig.sql, if
- SQL> Rem FALSE it will be run from catuppst.sql. This script gathers
- SQL> Rem statistics on migration stats that are recreated after an
- SQL> Rem upgrade occurs.
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem You must be connected AS SYSDBA to run this script.
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem jerrede 12/20/12 - Turn off set serveroutput
- SQL> Rem jerrede 04/17/12 - Moved from catuppst.sql
- SQL> Rem which was written by Tom Raney.
- SQL> Rem
- SQL>
- SQL>
- SQL> Rem *********************************************************************
- SQL> Rem BEGIN catrequtlmg.sql
- SQL> Rem *********************************************************************
- SQL>
- SQL> Rem =======================================================================
- SQL> Rem Statistics gathering
- SQL> Rem =======================================================================
- SQL> -- DBMS_STATS now depends on DBMS_UTILITY which may have gotten invalidated
- SQL> -- by some preceeding DDL statement, so package state needs to be cleared to
- SQL> -- avoid ORA-04068, reset_package causes set serveroutput on to not work.
- SQL>
- SQL> execute dbms_session.reset_package;
- PL/SQL procedure successfully completed.
- SQL> set serveroutput on;
- SQL>
- SQL> declare
- 2
- 3 n_SampleSize NUMBER := DBMS_STATS.AUTO_SAMPLE_SIZE;
- 4 c_UPGSAMPLESIZE CONSTANT NUMBER := 100;
- 5 c_TRACEEVENT CONSTANT VARCHAR2(30) := '_utlmmig_table_stats_gathering';
- 6 c_POSTUPGRADE CONSTANT VARCHAR2(19) := 'CATREQ_POST_UPGRADE';
- 7 c_BOOTERR CONSTANT VARCHAR2(23) := 'BOOTSTRAP_UPGRADE_ERROR';
- 8 c_MIGTABLE CONSTANT VARCHAR2(4) := '$MIG';
- 9 c_POSTUPGTABLE CONSTANT VARCHAR2(1) := '
- ;
- 10 s_TableName VARCHAR2(4) := c_MIGTABLE;
- 11 b_InUtlMig BOOLEAN := sys.dbms_registry_sys.select_props_data(c_BOOTERR);
- 12 b_UpgradeMode BOOLEAN := sys.dbms_registry.is_in_upgrade_mode();
- 13 b_StatEvt BOOLEAN := sys.dbms_registry.is_trace_event_set(c_TRACEEVENT);
- 14 b_SelProps BOOLEAN := sys.dbms_registry_sys.select_props_data(c_POSTUPGRADE);
- 15 b_Props BOOLEAN := TRUE;
- 16
- 17 begin
- 18
- 19 --
- 20 -- Debug Info
- 21 --
- 22 IF (b_StatEvt) THEN
- 23 sys.dbms_output.put_line('catrequtlmg: b_StatEvt = TRUE');
- 24 ELSE
- 25 sys.dbms_output.put_line('catrequtlmg: b_StatEvt = FALSE');
- 26 END IF;
- 27
- 28 IF (b_SelProps) THEN
- 29 sys.dbms_output.put_line('catrequtlmg: b_SelProps = TRUE');
- 30 ELSE
- 31 sys.dbms_output.put_line('catrequtlmg: b_SelProps = FALSE');
- 32 END IF;
- 33
- 34 IF (b_UpgradeMode) THEN
- 35 sys.dbms_output.put_line('catrequtlmg: b_UpgradeMode = TRUE');
- 36 ELSE
- 37 sys.dbms_output.put_line('catrequtlmg: b_UpgradeMode = FALSE');
- 38 END IF;
- 39
- 40 IF (b_InUtlMig) THEN
- 41 sys.dbms_output.put_line('catrequtlmg: b_InUtlMig = TRUE');
- 42 ELSE
- 43 sys.dbms_output.put_line('catrequtlmg: b_InUtlMig = FALSE');
- 44 s_TableName := c_POSTUPGTABLE;
- 45 END IF;
- 46
- 47 --
- 48 -- b_StatEvt = FALSE indicates don't collect stats
- 49 -- in upgrade mode.
- 50 --
- 51 -- Don't do the migration stats in UPGRADE mode.
- 52 -- Stats will run no matter what mode we are in
- 53 -- if post upgrade data is found in sys.props$.
- 54 --
- 55 IF (b_StatEvt = FALSE AND b_SelProps = FALSE) THEN
- 56
- 57 --
- 58 -- In Upgrade Mode Only
- 59 --
- 60 IF (b_UpgradeMode) THEN
- 61
- 62 --
- 63 -- Set sys.props$ table indicating that it
- 64 -- needs to be run in the post upgrade script.
- 65 --
- 66 b_Props := sys.dbms_registry_sys.insert_props_data(c_POSTUPGRADE,
- 67 'Run Migration Stats',
- 68 'Startup database in normal mode and run catuppst.sql');
- 69 IF (b_Props) THEN
- 70 sys.dbms_output.put_line('catrequtlmg: insert_props_data: Success');
- 71 ELSE
- 72 sys.dbms_output.put_line('catrequtlmg: insert_props_data: Failure');
- 73 END IF;
- 74
- 75 END IF;
- 76
- 77 RETURN;
- 78
- 79 END IF;
- 80
- 81 --
- 82 -- b_StatEvt = TRUE indicates collect stats
- 83 -- in upgrade mode.
- 84 --
- 85 -- Don't do the migration stats in NORMAL mode.
- 86 -- Stats will run no matter what mode we are in
- 87 -- if post upgrade data is found in sys.props$.
- 88 --
- 89 IF (b_StatEvt = TRUE AND b_SelProps = FALSE AND b_UpgradeMode = FALSE) THEN
- 90
- 91 RETURN;
- 92
- 93 END IF;
- 94
- 95 --
- 96 -- Updating migration stats in post upgrade. Write an entry to
- 97 -- sys.props$ table to indicate that stat collection has started.
- 98 -- If this entry is present then this routine has failed.
- 99 --
- 100 IF (b_SelProps) THEN
- 101
- 102 b_Props := sys.dbms_registry_sys.update_props_data(c_POSTUPGRADE,
- 103 'Started Migration Stats');
- 104 IF (b_Props) THEN
- 105 sys.dbms_output.put_line('catrequtlmg: update_props_data: Success');
- 106 ELSE
- 107 sys.dbms_output.put_line('catrequtlmg: update_props_data: Failure');
- 108 END IF;
- 109
- 110 END IF;
- 111
- 112 --
- 113 -- If we are in upgrade mode turn auto sample size off
- 114 -- results in a bug when we try and do a recomp in
- 115 -- utlrp.
- 116 --
- 117 IF (b_UpgradeMode) THEN
- 118 n_SampleSize := c_UPGSAMPLESIZE;
- 119 END IF;
- 120
- 121 --
- 122 -- Delete Stats
- 123 --
- 124 sys.dbms_output.put_line('catrequtlmg: Deleting table stats');
- 125 sys.dbms_stats.delete_table_stats('SYS', 'OBJ' || s_TableName);
- 126 sys.dbms_stats.delete_table_stats('SYS', 'USER' || s_TableName);
- 127 sys.dbms_stats.delete_table_stats('SYS', 'COL' || s_TableName);
- 128 sys.dbms_stats.delete_table_stats('SYS', 'CLU' || s_TableName);
- 129 sys.dbms_stats.delete_table_stats('SYS', 'CON' || s_TableName);
- 130 sys.dbms_stats.delete_table_stats('SYS', 'TAB' || s_TableName);
- 131 sys.dbms_stats.delete_table_stats('SYS', 'IND' || s_TableName);
- 132 sys.dbms_stats.delete_table_stats('SYS', 'ICOL' || s_TableName);
- 133 sys.dbms_stats.delete_table_stats('SYS', 'LOB' || s_TableName);
- 134 sys.dbms_stats.delete_table_stats('SYS', 'COLTYPE' || s_TableName);
- 135 sys.dbms_stats.delete_table_stats('SYS', 'SUBCOLTYPE' || s_TableName);
- 136 sys.dbms_stats.delete_table_stats('SYS', 'NTAB' || s_TableName);
- 137 sys.dbms_stats.delete_table_stats('SYS', 'REFCON' || s_TableName);
- 138 sys.dbms_stats.delete_table_stats('SYS', 'OPQTYPE' || s_TableName);
- 139 sys.dbms_stats.delete_table_stats('SYS', 'ICOLDEP' || s_TableName);
- 140 sys.dbms_stats.delete_table_stats('SYS', 'TSQ' || s_TableName);
- 141 sys.dbms_stats.delete_table_stats('SYS', 'VIEWTRCOL' || s_TableName);
- 142 sys.dbms_stats.delete_table_stats('SYS', 'ATTRCOL' || s_TableName);
- 143 sys.dbms_stats.delete_table_stats('SYS', 'TYPE_MISC' || s_TableName);
- 144 sys.dbms_stats.delete_table_stats('SYS', 'LIBRARY' || s_TableName);
- 145 sys.dbms_stats.delete_table_stats('SYS', 'ASSEMBLY' || s_TableName);
- 146
- 147 --
- 148 -- Gather Stats
- 149 --
- 150 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats OBJ' ||
- 151 s_TableName);
- 152 sys.dbms_stats.gather_table_stats('SYS', 'OBJ' || s_TableName,
- 153 estimate_percent => n_SampleSize,
- 154 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 155 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats USER' ||
- 156 s_TableName);
- 157 sys.dbms_stats.gather_table_stats('SYS', 'USER' || s_TableName,
- 158 estimate_percent => n_SampleSize,
- 159 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 160 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats COL' ||
- 161 s_TableName);
- 162 sys.dbms_stats.gather_table_stats('SYS', 'COL' || s_TableName,
- 163 estimate_percent => n_SampleSize,
- 164 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 165 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats CLU' ||
- 166 s_TableName);
- 167 sys.dbms_stats.gather_table_stats('SYS', 'CLU' || s_TableName,
- 168 estimate_percent => n_SampleSize,
- 169 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 170 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats CON' ||
- 171 s_TableName);
- 172 sys.dbms_stats.gather_table_stats('SYS', 'CON' || s_TableName,
- 173 estimate_percent => n_SampleSize,
- 174 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 175 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats TAB' ||
- 176 s_TableName);
- 177 sys.dbms_stats.gather_table_stats('SYS', 'TAB' || s_TableName,
- 178 estimate_percent => n_SampleSize,
- 179 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 180 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats IND' ||
- 181 s_TableName);
- 182 sys.dbms_stats.gather_table_stats('SYS', 'IND' || s_TableName,
- 183 estimate_percent => n_SampleSize,
- 184 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 185 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ICOL' ||
- 186 s_TableName);
- 187 sys.dbms_stats.gather_table_stats('SYS', 'ICOL' || s_TableName,
- 188 estimate_percent => n_SampleSize,
- 189 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 190 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats LOB' ||
- 191 s_TableName);
- 192 sys.dbms_stats.gather_table_stats('SYS', 'LOB' || s_TableName,
- 193 estimate_percent => n_SampleSize,
- 194 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 195 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats COLTYPE' ||
- 196 s_TableName);
- 197 sys.dbms_stats.gather_table_stats('SYS', 'COLTYPE' || s_TableName,
- 198 estimate_percent => n_SampleSize,
- 199 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 200 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats SUBCOLTYPE' ||
- 201 s_TableName);
- 202 sys.dbms_stats.gather_table_stats('SYS', 'SUBCOLTYPE' || s_TableName,
- 203 estimate_percent => n_SampleSize,
- 204 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 205 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats NTAB' ||
- 206 s_TableName);
- 207 sys.dbms_stats.gather_table_stats('SYS', 'NTAB' || s_TableName,
- 208 estimate_percent => n_SampleSize,
- 209 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 210 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats REFCON' ||
- 211 s_TableName);
- 212 sys.dbms_stats.gather_table_stats('SYS', 'REFCON' || s_TableName,
- 213 estimate_percent => n_SampleSize,
- 214 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 215 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats OPQTYPE' ||
- 216 s_TableName);
- 217 sys.dbms_stats.gather_table_stats('SYS', 'OPQTYPE' || s_TableName,
- 218 estimate_percent => n_SampleSize,
- 219 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 220 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ICOLDEP' ||
- 221 s_TableName);
- 222 sys.dbms_stats.gather_table_stats('SYS', 'ICOLDEP' || s_TableName,
- 223 estimate_percent => n_SampleSize,
- 224 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 225 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats TSQ' ||
- 226 s_TableName);
- 227 sys.dbms_stats.gather_table_stats('SYS', 'TSQ' || s_TableName,
- 228 estimate_percent => n_SampleSize,
- 229 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 230 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats VIEWTRCOL' ||
- 231 s_TableName);
- 232 sys.dbms_stats.gather_table_stats('SYS', 'VIEWTRCOL' || s_TableName,
- 233 estimate_percent => n_SampleSize,
- 234 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 235 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ATTRCOL' ||
- 236 s_TableName);
- 237 sys.dbms_stats.gather_table_stats('SYS', 'ATTRCOL' || s_TableName,
- 238 estimate_percent => n_SampleSize,
- 239 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 240 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats TYPE_MISC' ||
- 241 s_TableName);
- 242 sys.dbms_stats.gather_table_stats('SYS', 'TYPE_MISC' || s_TableName,
- 243 estimate_percent => n_SampleSize,
- 244 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 245 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats LIBRARY' ||
- 246 s_TableName);
- 247 sys.dbms_stats.gather_table_stats('SYS', 'LIBRARY' || s_TableName,
- 248 estimate_percent => n_SampleSize,
- 249 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 250 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ASSEMBLY' ||
- 251 s_TableName);
- 252 sys.dbms_stats.gather_table_stats('SYS', 'ASSEMBLY' || s_TableName,
- 253 estimate_percent => n_SampleSize,
- 254 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
- 255
- 256
- 257 --
- 258 -- Delete any previous entry that may have been stored in
- 259 -- sys.props$ table.
- 260 --
- 261 b_Props := sys.dbms_registry_sys.delete_props_data(c_POSTUPGRADE);
- 262 IF (b_Props) THEN
- 263 sys.dbms_output.put_line('catrequtlmg: delete_props_data: Success');
- 264 ELSE
- 265 sys.dbms_output.put_line('catrequtlmg: delete_props_data: No Props Data');
- 266 END IF;
- 267
- 268 end;
- 269 /
- catrequtlmg: b_StatEvt = TRUE
- catrequtlmg: b_SelProps = FALSE
- catrequtlmg: b_UpgradeMode = FALSE
- catrequtlmg: b_InUtlMig = FALSE
- PL/SQL procedure successfully completed.
- SQL>
- SQL> --
- SQL> -- Set serveroutput off
- SQL> --
- SQL> set serveroutput off;
- SQL>
- SQL> --
- SQL> -- Reset Package to be on the safe side for the
- SQL> -- case where we are running in catuppst.sql
- SQL> --
- SQL> execute dbms_session.reset_package;
- PL/SQL procedure successfully completed.
- SQL>
- SQL>
- SQL> Rem *********************************************************************
- SQL> Rem END catrequtlmg.sql
- SQL> Rem *********************************************************************
- SQL>
- SQL>
- SQL>
- SQL> Rem
- SQL> Rem Display End TimeStamp
- SQL> Rem
- SQL> SELECT sys.dbms_registry_sys.time_stamp('catreq_end') as timestamp from dual;
- TIMESTAMP
- --------------------------------------------------------------------------------
- COMP_TIMESTAMP CATREQ_END 2020-12-13 15:13:34
- SQL>
- SQL> Rem *********************************************************************
- SQL> Rem END catrequired.sql
- SQL> Rem *********************************************************************
- SQL>
- SQL>
- SQL> --
- SQL> -- These were created in utlmmig.sql but could not be dropped until now.
- SQL> -- Suppress "does not exist" errors.
- SQL> --
- SQL> set serveroutput on;
- SQL> begin
- 2 sys.dbms_output.put_line('catuppst: Dropping library DBMS_DDL_INTERNAL_LIB');
- 3 execute immediate 'drop library DBMS_DDL_INTERNAL_LIB';
- 4 exception
- 5 when others then
- 6 if sqlcode = -4043 then
- 7 null;
- 8 end if;
- 9 end;
- 10 /
- catuppst: Dropping library DBMS_DDL_INTERNAL_LIB
- PL/SQL procedure successfully completed.
- SQL>
- SQL> begin
- 2 sys.dbms_output.put_line('catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG');
- 3 execute immediate 'drop view "_CURRENT_EDITION_OBJ_MIG"';
- 4 exception
- 5 when others then
- 6 if sqlcode = -942 then
- 7 null;
- 8 end if;
- 9 end;
- 10 /
- catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG
- PL/SQL procedure successfully completed.
- SQL>
- SQL> begin
- 2 sys.dbms_output.put_line('catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG');
- 3 execute immediate 'drop view "_ACTUAL_EDITION_OBJ_MIG"';
- 4 exception
- 5 when others then
- 6 if sqlcode = -942 then
- 7 null;
- 8 end if;
- 9 end;
- 10 /
- catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG
- PL/SQL procedure successfully completed.
- SQL>
- SQL> set serveroutput off;
- SQL>
- SQL>
- SQL> Rem =======================================================================
- SQL> Rem Upgrade AWR Baseline information
- SQL> Rem =======================================================================
- SQL>
- SQL> @@awrblmig.sql
- SQL> Rem
- SQL> Rem $Header: awrblmig.sql 16-nov-2006.14:53:14 rburns Exp $
- SQL> Rem
- SQL> Rem awrblmig.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2006, Oracle. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem awrblmig.sql - AWR Baseline Migrate
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem This script is used to migrate the AWR Baseline data from
- SQL> Rem the renamed BL tables back to the base tables. This script is
- SQL> Rem needed because the way the baselines are stored have been changed
- SQL> Rem in 11g. This script will
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem Run this script if you have AWR Baselines prior to the 11g release
- SQL> Rem and have upgraded to the 11g release.
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem rburns 11/16/06 - modify set statements
- SQL> Rem mlfeng 06/18/06 - Script to migrate the AWR Baseline data
- SQL> Rem mlfeng 06/18/06 - Created
- SQL> Rem
- SQL>
- SQL> set serveroutput on;
- SQL> exec dbms_output.enable(100000);
- PL/SQL procedure successfully completed.
- SQL>
- SQL> prompt
- SQL> prompt This script will migrate the Baseline data on a pre-11g database
- This script will migrate the Baseline data on a pre-11g database
- SQL> prompt to the 11g database.
- to the 11g database.
- SQL> prompt
- SQL>
- SQL> begin
- 2 dbms_swrf_internal.baseline_migrate(migrate_tables => TRUE,
- 3 drop_tables => TRUE);
- 4 end;
- 5 /
- ... ...
- ... Completed Moving the Baseline Data ...
- ... ...
- ... If there are no Move BL Data messages ...
- ... above, then there are no renamed ...
- ... baseline tables in the system. ...
- ... ...
- ... ...
- ... Completed the Dropping of the ...
- ... Renamed Baseline Tables ...
- ... ...
- ... If there are no Drop Table messages ...
- ... above, then there are no renamed ...
- ... baseline tables in the system. ...
- ... ...
- PL/SQL procedure successfully completed.
- SQL>
- SQL> set serveroutput off
- SQL>
- SQL> Rem =======================================================================
- SQL> Rem AWR inactive DBIDs check
- SQL> Rem =======================================================================
- SQL>
- SQL> set serveroutput on
- SQL>
- SQL> declare
- 2 cnt number;
- 3 begin
- 4 select count(*) into cnt
- 5 from sys.wrm$_wr_control
- 6 where bitand(status_flag, 4) = 4;
- 7 if (cnt > 0) then
- 8 dbms_output.put_line(
- 9 'AWR has data for ' || cnt || ' different DBID(s) that need updating.');
- 10 dbms_output.put_line(
- 11 'These are either imported AWR snapshots, or snapshots from before');
- 12 dbms_output.put_line(
- 13 'changing the DBID of the database. You will not be able to import');
- 14 dbms_output.put_line(
- 15 'additional data for those DBIDs until they are updated.');
- 16 dbms_output.put_line(
- 17 'Please use the rdbms/admin/awrupd12.sql script to update data for ' ||
- 18 'those DBIDs after the database has been upgraded.');
- 19 end if;
- 20 end;
- 21 /
- PL/SQL procedure successfully completed.
- SQL>
- SQL> set serveroutput off
- SQL>
- SQL> Rem =======================================================================
- SQL> Rem Upgrade ADDM task metadata
- SQL> Rem =======================================================================
- SQL>
- SQL> @@addmtmig.sql
- SQL> Rem
- SQL> Rem $Header: addmtmig.sql 26-feb-2007.04:29:23 sburanaw Exp $
- SQL> Rem
- SQL> Rem addmtmig.sql
- SQL> Rem
- SQL> Rem Copyright (c) 2007, Oracle. All rights reserved.
- SQL> Rem
- SQL> Rem NAME
- SQL> Rem addmtmig.sql - ADDM Task Migration
- SQL> Rem
- SQL> Rem DESCRIPTION
- SQL> Rem Post upgrade script for 11.1 release to fill new ADDM task
- SQL> Rem metadata tables with derived information.
- SQL> Rem
- SQL> Rem NOTES
- SQL> Rem If this script is not included in post-upgrade, EM performance
- SQL> Rem pages will not be able to display 10g ADDM tasks properly.
- SQL> Rem There is no other effect on the database.
- SQL> Rem
- SQL> Rem MODIFIED (MM/DD/YY)
- SQL> Rem sburanaw 02/23/07 - fix microsecond for wri$_adv_addm_task
- SQL> Rem ushaft 02/05/07 - Created
- SQL> Rem
- SQL>
- SQL> Rem
- SQL> Rem Find the tasks that need to be upgraded.
- SQL> Rem
- SQL>
- SQL> insert into wri$_adv_addm_tasks
- 2 (TASK_ID,
- 3 DBID,
- 4 BEGIN_SNAP_ID,
- 5 END_SNAP_ID,
- 6 REQUESTED_ANALYSIS,
- 7 ACTUAL_ANALYSIS,
- 8 DATABASE_TIME)
- 9 select t.task_id,
- 10 to_number(p_dbid.parameter_value),
- 11 to_number(p_bid.parameter_value),
- 12 to_number(p_eid.parameter_value),
- 13 'INSTANCE', 'INSTANCE',
- 14 to_number(p_dbt.parameter_value)
- 15 from dba_advisor_tasks t,
- 16 dba_advisor_parameters p_dbid,
- 17 dba_advisor_parameters p_bid,
- 18 dba_advisor_parameters p_eid,
- 19 dba_advisor_parameters p_dbt
- 20 where t.advisor_name = 'ADDM'
- 21 and t.status = 'COMPLETED'
- 22 and p_dbid.task_id = t.task_id
- 23 and p_dbid.parameter_name = 'DB_ID'
- 24 and p_bid.task_id = t.task_id
- 25 and p_bid.parameter_name = 'START_SNAPSHOT'
- 26 and p_eid.task_id = t.task_id
- 27 and p_eid.parameter_name = 'END_SNAPSHOT'
- 28 and p_dbt.task_id = t.task_id
- 29 and p_dbt.parameter_name = 'DB_ELAPSED_TIME'
- 30 and t.task_id not in (select task_id from wri$_adv_addm_tasks);
- 0 rows created.
- SQL>
- SQL> commit;
- Commit complete.
- SQL>
- SQL> Rem
- SQL> Rem Add the estimated analysis version.
- SQL> Rem
- SQL>
- SQL> CREATE GLOBAL TEMPORARY TABLE addm$dbv (
- 2 exec_from timestamp(3),
- 3 exec_to timestamp(3),
- 4 db_version varchar2(17)
- 5 )
- 6 ON COMMIT DELETE ROWS;
- Table created.
- SQL>
- SQL> insert into addm$dbv
- 2 select min(startup_time), null, version
- 3 from wrm$_database_instance
- 4 where dbid = (select dbid from v$database)
- 5 group by version;
- 2 rows created.
- SQL>
- SQL> update addm$dbv a
- 2 set a.exec_from = (select cast(min(execution_end) as timestamp)
- 3 from dba_advisor_tasks)
- 4 where a.exec_from =
- 5 (select min(exec_from)
- 6 from addm$dbv);
- 1 row updated.
- SQL>
- SQL> update addm$dbv a
- 2 set a.exec_to =
- 3 (select nvl(min(exec_from), cast(sysdate as timestamp))
- 4 from addm$dbv b
- 5 where b.exec_from > a.exec_from);
- 2 rows updated.
- SQL>
- SQL> update wri$_adv_addm_tasks t
- 2 set t.analysis_version =
- 3 (select min(v.db_version)
- 4 from addm$dbv v, dba_advisor_tasks a
- 5 where a.task_id = t.task_id
- 6 and cast(a.execution_end as timestamp) > v.exec_from
- 7 and cast(a.execution_end as timestamp) <= v.exec_to)
- 8 where t.analysis_version IS NULL;
- 0 rows updated.
- SQL>
- SQL> drop table addm$dbv;
- Table dropped.
- SQL>
- SQL> commit;
- Commit complete.
- SQL>
- SQL>
- SQL> Rem
- SQL> Rem Add the database name, database version, snapshot end time.
- SQL> Rem
- SQL>
- SQL> update wri$_adv_addm_tasks t
- 2 set (t.dbname, t.dbversion, t.end_time) =
- 3 (select min(d.db_name), min(d.version), min(s.end_interval_time)
- 4 from wrm$_snapshot s, wrm$_database_instance d
- 5 where s.dbid = t.dbid
- 6 and s.snap_id = t.end_snap_id
- 7 and d.dbid = t.dbid
- 8 and d.instance_number = s.instance_number
- 9 and d.startup_time = s.startup_time
- 10 )
- 11 where t.analysis_version like '10.%'
- 12 and t.dbname IS NULL
- 13 and t.dbversion IS NULL
- 14 and t.end_time IS NULL;
- 0 rows updated.
- SQL>
- SQL> commit;
- Commit complete.
- SQL>
- SQL> Rem
- SQL> Rem Add the snapshot begin time.
- SQL> Rem
- SQL>
- SQL> update wri$_adv_addm_tasks t
- 2 set t.begin_time =
- 3 (select min(s.end_interval_time)
- 4 from wrm$_snapshot s
- 5 where s.dbid = t.dbid
- 6 and s.snap_id = t.begin_snap_id
- 7 )
- 8 where t.analysis_version like '10.%'
- 9 and t.begin_time IS NULL;
- 0 rows updated.
- SQL>
- SQL> commit;
- Commit complete.
- SQL>
- SQL> Rem
- SQL> Rem Add the active sessions
- SQL> Rem
- SQL>
- SQL> update wri$_adv_addm_tasks t
- 2 set t.active_sessions =
- 3 t.database_time /
- 4 (extract(day from t.end_time - t.begin_time) *24*60*60*1000000
- 5 + extract(hour from t.end_time - t.begin_time) *60*60*1000000
- 6 + extract(minute from t.end_time - t.begin_time) *60*1000000
- 7 + extract(second from t.end_time - t.begin_time) *1000000 )
- 8 where t.active_sessions IS NULL
- 9 and t.begin_time IS NOT NULL
- 10 and t.end_time IS NOT NULL
- 11 and t.database_time IS NOT NULL
- 12 and t.end_time > t.begin_time;
- 0 rows updated.
- SQL>
- SQL> commit;
- Commit complete.
- SQL>
- SQL>
- SQL> Rem
- SQL> Rem Add a row for each task into the wri$_adv_addm_inst table
- SQL> Rem
- SQL>
- SQL> insert into wri$_adv_addm_inst i
- 2 (TASK_ID,
- 3 INSTANCE_NUMBER,
- 4 INSTANCE_NAME,
- 5 HOST_NAME,
- 6 STATUS,
- 7 DATABASE_TIME,
- 8 ACTIVE_SESSIONS,
- 9 PERC_ACTIVE_SESS,
- 10 LOCAL_TASK_ID)
- 11 select t.task_id,
- 12 to_number(p.parameter_value),
- 13 d.instance_name,
- 14 d.host_name,
- 15 'ANALYZED',
- 16 t.database_time,
- 17 t.active_sessions,
- 18 100,
- 19 t.task_id
- 20 from wri$_adv_addm_tasks t, dba_advisor_parameters p,
- 21 wrm$_snapshot s, wrm$_database_instance d
- 22 where t.task_id not in (select task_id from wri$_adv_addm_inst)
- 23 and t.analysis_version like '10.%'
- 24 and t.actual_analysis = 'INSTANCE'
- 25 and p.task_id = t.task_id
- 26 and p.parameter_name = 'INSTANCE'
- 27 and p.parameter_value IS NOT NULL
- 28 and p.parameter_value <> 'UNUSED'
- 29 and s.dbid = t.dbid
- 30 and s.snap_id = t.end_snap_id
- 31 and s.instance_number =
- 32 to_number(decode(p.parameter_value, 'UNUSED', NULL, p.parameter_value))
- 33 and d.dbid = t.dbid
- 34 and d.instance_number = s.instance_number
- 35 and d.startup_time = s.startup_time;
- 0 rows created.
- SQL>
- SQL> commit;
- Commit complete.
- SQL>
- SQL>
- SQL> Rem
- SQL> Rem Add a row for each the findings
- SQL> Rem
- SQL>
- SQL> insert into wri$_adv_addm_fdg
- 2 (TASK_ID,
- 3 FINDING_ID,
- 4 DATABASE_TIME,
- 5 ACTIVE_SESSIONS,
- 6 PERC_ACTIVE_SESS,
- 7 IS_AGGREGATE
- 8 )
- 9 select t.task_id,
- 10 a.finding_id,
- 11 a.impact,
- 12 (a.impact * t.active_sessions) / t.database_time,
- 13 (a.impact * 100) / t.database_time,
- 14 'N'
- 15 from wri$_adv_addm_tasks t, dba_advisor_findings a
- 16 where (t.task_id, a.finding_id) not in
- 17 (select task_id, finding_id from wri$_adv_addm_fdg)
- 18 and t.analysis_version like '10.%'
- 19 and t.actual_analysis = 'INSTANCE'
- 20 and t.task_id = a.task_id
- 21 and a.type in ('PROBLEM', 'SYMPTOM')
- 22 and t.database_time > 0;
- 0 rows created.
- SQL>
- SQL> commit;
- Commit complete.
- SQL>
- SQL> Rem =======================================================================
- SQL> Rem If OLS in the database, run olstrig.sql to updated OLS policies
- SQL> Rem =======================================================================
- SQL>
- SQL> COLUMN :ols_name NEW_VALUE ols_file NOPRINT;
- SQL> VARIABLE ols_name VARCHAR2(30)
- SQL> DECLARE
- 2 BEGIN
- 3 IF dbms_registry.is_loaded('OLS') IS NOT NULL THEN
- 4 :ols_name := '@olstrig.sql'; -- OLS installed in DB
- 5 ELSE
- 6 :ols_name := dbms_registry.nothing_script; -- No OLS
- 7 END IF;
- 8 END;
- 9 /
- PL/SQL procedure successfully completed.
- SQL> SELECT :ols_name FROM DUAL;
- SQL> @&ols_file
- SQL> Rem $Header: rdbms/admin/nothing.sql /st_rdbms_12.1/1 2014/06/03 11:24:49 aketkar Exp $
- SQL> Rem
- SQL> Rem
- SQL> Rem BEGIN SQL_FILE_METADATA
- SQL> Rem SQL_SOURCE_FILE: rdbms/admin/nothing.sql
- SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/nothing.sql
- SQL> Rem SQL_PHASE: NOTHING
- SQL> Rem SQL_STARTUP_MODE: NORMAL
- SQL> Rem SQL_IGNORABLE_ERRORS: NONE
- SQL> Rem SQL_CALLING_FILE: NONE
- SQL> Rem END SQL_FILE_METADATA
- SQL> Rem
- SQL>
- SQL>
- SQL> Rem =======================================================================
- SQL> Rem If EM in the database, run @emremove.sql to remove EM schema
- SQL> Rem =======================================================================
- SQL>
- SQL> COLUMN :em_name NEW_VALUE em_file NOPRINT;
- SQL> VARIABLE em_name VARCHAR2(30)
- SQL> DECLARE
- 2 BEGIN
- 3 IF dbms_registry.is_loaded('EM') IS NOT NULL THEN
- 4 :em_name := '@emremove.sql'; -- EM exists in DB
- 5 ELSE
- 6 :em_name := dbms_registry.nothing_script; -- No EM
- 7 END IF;
- 8 END;
- 9 /
- PL/SQL procedure successfully completed.
- SQL> SELECT :em_name FROM DUAL;
- SQL> @&em_file
- SQL> Rem $Header: rdbms/admin/nothing.sql /st_rdbms_12.1/1 2014/06/03 11:24:49 aketkar Exp $
- SQL> Rem
- SQL> Rem
- SQL> Rem BEGIN SQL_FILE_METADATA
- SQL> Rem SQL_SOURCE_FILE: rdbms/admin/nothing.sql
- SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/nothing.sql
- SQL> Rem SQL_PHASE: NOTHING
- SQL> Rem SQL_STARTUP_MODE: NORMAL
- SQL> Rem SQL_IGNORABLE_ERRORS: NONE
- SQL> Rem SQL_CALLING_FILE: NONE
- SQL> Rem END SQL_FILE_METADATA
- SQL> Rem
- SQL>
- SQL> Rem =======================================================================
- SQL> Rem Network ACL migration status check
- SQL> Rem =======================================================================
- SQL>
- SQL> set serveroutput on
- SQL>
- SQL> declare
- 2 cnt number;
- 3 begin
- 4 select count(*) into cnt from dba_xs_acls
- 5 where owner = 'SYS' and name like 'PRE_12_1_NETWORK_ACL_%';
- 6 if (cnt > 0) then
- 7 dbms_output.put_line(
- 8 cnt || ' network access control lists (ACL) from releases earlier than');
- 9 dbms_output.put_line(
- 10 'Oracle Database 12.1 are not migrated. Please refer to the "Oracle');
- 11 dbms_output.put_line(
- 12 'Database Upgrade Guide" section titled "Configure Fine-Grained Access');
- 13 dbms_output.put_line(
- 14 'to External Network Services After Upgrading Oracle Database" for');
- 15 dbms_output.put_line(
- 16 'more information.');
- 17 end if;
- 18 end;
- 19 /
- PL/SQL procedure successfully completed.
- SQL>
- SQL> set serveroutput off
- SQL>
- SQL> Rem =======================================================================
- SQL> Rem AQ post upgrade code start
- SQL> Rem =======================================================================
- SQL> set serveroutput on
- SQL> -- create dequeue logs and recreate base views for old multiconsumer qtables
- SQL> declare
- 2 cursor qt_cur is select schema, name, flags from system.aq$_queue_tables;
- 3 begin
- 4 -- only execute the script if version is in 11.1 or 10.2
- 5 for qt in qt_cur loop
- 6 if (sys.dbms_aqadm_sys.mcq_8_1(qt.flags)) then
- 7 begin
- 8 if not sys.dbms_aqadm_sys.object_exists(qt.schema, 'AQ$_' || qt.name ||
- 9 '_L', 'TABLE') then
- 10 sys.dbms_prvtaqim.create_dequeue_log(qt.schema, qt.name, qt.flags);
- 11 sys.dbms_prvtaqim.create_base_view(qt.schema, qt.name, qt.flags);
- 12 end if;
- 13 exception
- 14 when others then
- 15 dbms_system.ksdwrt(dbms_system.alert_file,
- 16 'catuppst.sql: create dequeue log or recreate base' ||
- 17 ' view failed for ' || qt.schema || '.' || qt.name || ', error msg ' || SQLERRM);
- 18 end;
- 19 end if;
- 20 -- validate queues in post upgrade only for scheduler queue
- 21 if (qt.name = 'SCHEDULER$_EVENT_QTAB') then
- 22 sys.dbms_aqadm_sys.validate_qtab_queues(qt.schema, qt.name);
- 23 dbms_aqadm.start_queue(queue_name => 'scheduler$_event_queue');
- 24 end if;
- 25 end loop;
- 26 end;
- 27 /
- PL/SQL procedure successfully completed.
- SQL> set serveroutput off
- SQL> Rem =======================================================================
- SQL> Rem AQ post upgrade code end
- SQL> Rem =======================================================================
- SQL>
- SQL>
- SQL> Rem =======================================================================
- SQL> Rem Bug 14258301 - Gather fixed objects stats IF NONE of the fixed object
- SQL> Rem tables has stats
- SQL> Rem =======================================================================
- SQL>
- SQL> set serveroutput on
- SQL> declare
- 2 has_stats_cnt number := 0; -- # of fixed object tables that have stats
- 3 begin
- 4 -- find # of fixed object tables that have had stats collected
- 5 execute immediate
- 6 'select count(*) ' ||
- 7 'from sys.dba_tab_statistics ' ||
- 8 ' where owner = ''SYS'' and table_name like ''X$%'' ' ||
- 9 ' and last_analyzed is not null'
- 10 into has_stats_cnt;
- 11
- 12 -- if none of the fixed obj tables have had stats collected
- 13 -- then gather fixed objects stats
- 14 -- else do nothing
- 15 if (has_stats_cnt = 0) then
- 16 sys.dbms_output.put_line('catuppst: Gathering fixed objects stats now...');
- 17 sys.dbms_stats.gather_fixed_objects_stats;
- 18 sys.dbms_output.put_line('catuppst: Gathering fixed objects stats done.');
- 19 end if;
- 20 end;
- 21 /
- PL/SQL procedure successfully completed.
- SQL> set serveroutput off
- SQL>
- SQL> Rem =======================================================================
- SQL> Rem Gather Fixed Objects Stats end
- SQL> Rem =======================================================================
- SQL>
- SQL>
- SQL> Rem =======================================================================
- SQL> Rem Signal 'end' of catuppst.sql before catbundle.sql is executed
- SQL> Rem =======================================================================
- SQL> SELECT dbms_registry_sys.time_stamp('POSTUP_END') as timestamp from dual;
- TIMESTAMP
- --------------------------------------------------------------------------------
- COMP_TIMESTAMP POSTUP_END 2020-12-13 15:13:36
- SQL>
- SQL>
- SQL> Rem Set errorlogging off
- SQL> SET ERRORLOGGING OFF;
- SQL>
- SQL> Rem
- SQL> Rem Set _ORACLE_SCRIPT to false
- SQL> Rem
- SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=false;
- Session altered.
- SQL>
- SQL>
- SQL> Rem *********************************************************************
- SQL> Rem END catuppst.sql
- SQL> Rem *********************************************************************
- SQL>
- SQL>
复制代码- SQL> @/u01/app/oracle/cfgtoollogs/rcat/preupgrade/postupgrade_fixups.sql
- SQL> REM Post Upgrade Script Generated on: 2020-12-10 21:07:56
- SQL> REM Generated by Version: 12.1.0.2 Build: 006
- SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 750;
- Post Upgrade Fixup Script Generated on 2020-12-10 21:07:56 Version: 12.1.0.2 Build: 006
- Beginning Post-Upgrade Fixups...
- **********************************************************************
- Check Tag: OLD_TIME_ZONES_EXIST
- Check Summary: Check for use of older timezone data file
- Fix Summary: Update the timezone using the DBMS_DST package after upgrade is complete.
- **********************************************************************
- Fixup Returned Information:
- INFORMATION: --> Older Timezone in use
- Database is using a time zone file older than version 18.
- After the upgrade, it is recommended that DBMS_DST package
- be used to upgrade the 12.1.0.2.0 database time zone version
- to the latest version which comes with the new release.
- Please refer to My Oracle Support note number 977512.1 for details.
- **********************************************************************
- **********************************************************************
- Check Tag: NOT_UPG_BY_STD_UPGRD
- Check Summary: Identify existing components that will NOT be upgraded
- Fix Summary: This fixup does not perform any action.
- **********************************************************************
- Fixup Returned Information:
- This fixup does not perform any action.
- If you want to upgrade those other components, you must do so manually.
- **********************************************************************
- **********************************************************************
- [Post-Upgrade Recommendations]
- **********************************************************************
- *****************************************
- ******** Fixed Object Statistics ********
- *****************************************
- Please create stats on fixed objects two weeks
- after the upgrade using the command:
- EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
- ^^^ MANUAL ACTION SUGGESTED ^^^
- **************************************************
- ************* Fixup Summary ************
- 2 fixup routines generated INFORMATIONAL messages that should be reviewed.
- *************** Post Upgrade Fixup Script Complete ********************
- PL/SQL procedure successfully completed.
复制代码
- SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
复制代码
|
|