Rem
Rem $Header: rdbms/admin/catupstr.sql /st_rdbms_11.2.0.1.0/1 2009/07/16 14:11:12 bmccarth Exp $
Rem
Rem catupstr.sql
Rem
Rem Copyright (c) 2006, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      catupstr.sql - CATalog UPgrade STaRt script
Rem
Rem    DESCRIPTION
Rem      This script performs the initial checks for upgrade
Rem      (open for UPGRADE, AS SYSDBA, etc.) and then runs
Rem      the "i" scripts, utlip.sql, and the "c" scripts
Rem      to complete the basic RDBMS upgrade
Rem
Rem    NOTES
Rem      Invoked from catupgrd.sql
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    cdilling    06/01/09 - check for supported upgrade versions
Rem    cdilling    05/26/09 - for PSU check only 5 digits for version
Rem    cmlim       01/16/09 - bug 7496789: update check on when DV needs to be
Rem                           relinked off
Rem    cmlim       12/19/08 - timezone_b7193417-c: rewrite timezone check
Rem    cmlim       12/12/08 - timezone_b7193417-b: if old OH has newer timezone
Rem                           version than 8, abort if new OH is not patched
Rem    rlong       09/25/08 - 
Rem    cmlim       07/24/08 - bug 7193417: support timezone file version
Rem                           changes in 11.2
Rem    awitkows    03/30/08 - DST. repl registry with props
Rem    rburns      11/11/07 - XbranchMerge rburns_bug-6446262 from
Rem                           st_rdbms_project-18813
Rem    rburns      11/08/07 - check for INVALID old versions of types
Rem    jciminsk    10/22/07 - Upgrade support for 11.2
Rem    cdilling    10/09/07 - update version to 11.2
Rem    cdilling    08/23/07 - check disabled indexes only
Rem    rburns      07/16/07 - add 11.1 patch upgrade
Rem    rburns      05/29/07 - add timezone version check
Rem    rburns      05/01/07 - reload dbms_assert
Rem    rburns      03/10/07 - add DV and OLS check
Rem    cdilling    02/19/07 - add sys.enabled$indexes table for bug 5530085
Rem    dvoss       02/19/07 - Check bootstrap migration status
Rem    rburns      10/23/06 - add session script
Rem    rburns      08/14/06 - add RDBMS identifier
Rem    cdilling    06/08/06 - add error logging table
Rem    gviswana    06/07/06 - Enable 4523571 fix 
Rem    rburns      05/22/06 - parallel upgrade 
Rem    rburns      05/22/06 - Created
Rem

Rem =====================================================================
Rem Exit immediately if there are errors in the initial checks
Rem =====================================================================

WHENEVER SQLERROR EXIT;        

DOC 
######################################################################
######################################################################
    The following statement will cause an "ORA-01722: invalid number"
    error if the user running this script is not SYS.  Disconnect
    and reconnect with AS SYSDBA.
######################################################################
######################################################################
#

SELECT TO_NUMBER('MUST_BE_AS_SYSDBA') FROM DUAL
WHERE USER != 'SYS';

DOC
######################################################################
######################################################################
    The following statement will cause an "ORA-01722: invalid number"
    error if the database server version is not correct for this script.
    Shutdown ABORT and use a different script or a different server.
######################################################################
######################################################################
#

SELECT TO_NUMBER('MUST_BE_11_2') FROM v$instance
WHERE substr(version,1,6) != '11.2.0';

DOC
#######################################################################
#######################################################################
   The following statement will cause an "ORA-01722: invalid number"
   error if the database has not been opened for UPGRADE.  

   Perform a "SHUTDOWN ABORT"  and 
   restart using UPGRADE.
#######################################################################
#######################################################################
#

SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance
WHERE status != 'OPEN MIGRATE';

DOC
#######################################################################
#######################################################################
     The following statement will cause an "ORA-01722: invalid number"
     error if the Oracle Database Vault option is TRUE.  Upgrades cannot
     be run with the Oracle Database Vault option set to TRUE since
     AS SYSDBA connections are restricted.

     Perform a "SHUTDOWN ABORT", relink the server without the Database
     Vault option (but with the OLS option), and restart the server using
     UPGRADE.

     Restriction is for:
     1) When source database has no DV installed.
     OR
     2) When source database has DV installed and DV version is prior to 11.2.

#######################################################################
#######################################################################
#

SELECT TO_NUMBER('DATA_VAULT_OPTION_ON') FROM v$option
WHERE
  (value = 'TRUE' and parameter = 'Oracle Database Vault')
  AND
  (
   (0 = (select count(*) from registry$ where cid = 'DV'))
   OR
   ((0 != (select count(*) from registry$ where cid = 'DV'))
    AND
    ((select substr(version,1,4) from registry$ where cid = 'DV')
        IN ('9.2.', '10.1', '10.2', '11.1')))
  );


DOC
#######################################################################
#######################################################################
   The following statement will cause an "ORA-01722: invalid number"
   error if Database Vault is installed in the database but the Oracle 
   Label Security option is FALSE.  To successfully upgrade Oracle 
   Database Vault, the Oracle Label Security option must be TRUE.

   Perform a "SHUTDOWN ABORT", relink the server with the OLS option
   (but without the Oracle Database Vault option) and restart the server 
   using UPGRADE.
#######################################################################
#######################################################################
#

SELECT TO_NUMBER('LABEL_SECURITY_OPTION_OFF') FROM v$option 
WHERE value = 'FALSE' and parameter =
      (SELECT 'Oracle Label Security' FROM user$ where name = 'DVSYS');

DOC
#######################################################################
#######################################################################
   The following statement will cause an "ORA-01722: invalid number"
   error if bootstrap migration is in progress and logminer clients
   require utlmmig.sql to be run next to support this redo stream.

   Run utlmmig.sql
   then (if needed) 
   restart the database using UPGRADE and
   rerun the upgrade script.
#######################################################################
#######################################################################
#

SELECT TO_NUMBER('MUST_RUN_UTLMMIG.SQL')
    FROM SYS.V$DATABASE V
    WHERE V.LOG_MODE = 'ARCHIVELOG' and
          V.SUPPLEMENTAL_LOG_DATA_MIN != 'NO' and
          exists (select 1 from sys.props$
                  where name = 'LOGMNR_BOOTSTRAP_UPGRADE_ERROR');

DOC
#######################################################################
#######################################################################
   The following statement will cause:
   - "ORA-00942: table or view does not exist" ; or
   - "ORA-00904: "TZ_VERSION": invalid identifier"" ; or
   - "ORA-01722: invalid number"
     if the pre-upgrade utility (utlu112i.sql) has not been run to:
     a) create and update registry$database table to include the current
        database timezone file version used in the old release; or
     b) do inserts into sys.props$.

     o Action:
       Shutdown ABORT and revert to the original ORACLE_HOME.  Then run
       utlu112i.sql to populate registry$database with the database timezone
       file version used by the lower version database and to populate
       sys.props$ with Day Light Saving Time (DST) properties information. 

   OR
   - An "ORA-01722: invalid number"
     if the old release uses a timezone file version newer than 8 (shipped with
     11.2) but the new release has not been patched yet.

     o Action:
       Shutdown ABORT and patch new ORACLE_HOME to the same timezone file
       version as used in the old ORACLE_HOME.


#######################################################################
#######################################################################
#

SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
   FROM sys.props$
   WHERE
     (
      (name = 'DST_PRIMARY_TT_VERSION' AND
       TO_NUMBER(value$) != (SELECT tz_version from registry$database))
      AND
      ((SELECT version from v$timezone_file) !=
       (SELECT tz_version from registry$database))
     )
     OR
     (
      (name = 'DST_PRIMARY_TT_VERSION' AND TO_NUMBER(value$) > 8)
      AND
      (0 = (select count(*) from v$timezone_file))
     );


DOC 
#######################################################################
#######################################################################
    The following statements will cause an "ORA-01722: invalid number"
    error if the SYSAUX tablespace does not exist or is not
    ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, and
    SEGMENT SPACE MANAGEMENT AUTO.
 
    The SYSAUX tablespace is used in 10.1 to consolidate data from
    a number of tablespaces that were separate in prior releases. 
    Consult the Oracle Database Upgrade Guide for sizing estimates.

    Create the SYSAUX tablespace, for example,

     create tablespace SYSAUX datafile 'sysaux01.dbf' 
         size 70M reuse 
         extent management local 
         segment space management auto 
         online;

    Then rerun the catupgrd.sql script.
#######################################################################
#######################################################################
#

SELECT TO_NUMBER('No SYSAUX tablespace') FROM dual 
WHERE 'SYSAUX' NOT IN (SELECT name from ts$);

SELECT TO_NUMBER('Not ONLINE for READ/WRITE') from ts$
WHERE name='SYSAUX' AND online$ !=1;

SELECT TO_NUMBER ('Not PERMANENT') from ts$
WHERE name='SYSAUX' AND 
      (contents$ !=0 or (contents$ = 0 AND bitand(flags, 16)= 16));

SELECT TO_NUMBER ('Not LOCAL extent management') from ts$
WHERE name='SYSAUX' AND bitmapped = 0;

SELECT TO_NUMBER ('Not AUTO segment space management') from ts$
WHERE name='SYSAUX' AND bitand(flags,32) != 32;

Rem =====================================================================
Rem Assure CHAR semantics are not used in the dictionary
Rem =====================================================================

ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

Rem =====================================================================
Rem Continue even if there are SQL errors in remainder of script
Rem =====================================================================

WHENEVER SQLERROR CONTINUE;  

Rem
Rem Bug 5530085
Rem
Rem Poplulate sys.enabled_indexes table with the list of function-based 
Rem indexes that are currently not 'disabled'. This schema/index name list 
Rem will be later used in utlrp.sql to enable indexes in the list that may 
Rem have become disabled. 
Rem
CREATE TABLE sys.enabled$indexes( schemaname, indexname, objnum )
AS select u.name, o1.name, i.obj# from user$ u, obj$ o1, obj$ o2, ind$ i
    where
        u.user# = o1.owner# and o1.type# = 1 and o1.obj# = i.obj#
       and bitand(i.property, 16)= 16 and bitand(i.flags, 1024)=0
       and i.bo# = o2.obj# and bitand(o2.flags, 2)=0;



Rem
Rem Create error logging table
Rem
CREATE TABLE sys.registry$error(username   VARCHAR(256),
                                timestamp  TIMESTAMP,
                                script     VARCHAR(1024),
                                identifier VARCHAR(256),
                                message    CLOB,
                                statement  CLOB);
                                         
DELETE FROM sys.registry$error;

set errorlogging on table sys.registry$error;
set errorlogging on identifier 'RDBMS';

commit;


Rem
Rem Pre-create log to record upgrade operations and errors
Rem

CREATE TABLE registry$log (
             cid         VARCHAR2(30),              /* component identifier */
             namespace   VARCHAR2(30),               /* component namespace */
             operation   NUMBER NOT NULL,              /* current operation */
             optime      TIMESTAMP,                  /* operation timestamp */
             errmsg      varchar2(1000)         /* ORA error message number */
             );
Rem Clear log entries if the table already exists
DELETE FROM registry$log;

Rem put timestamps into spool log and registry$log
INSERT INTO registry$log (cid, namespace, operation, optime)
       VALUES ('UPGRD_BGN','SERVER',-1,SYSTIMESTAMP);
COMMIT;
SELECT 'COMP_TIMESTAMP UPGRD__BGN ' || 
        TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS ')  || 
        TO_CHAR(SYSTIMESTAMP,'J SSSSS ')
        AS timestamp FROM DUAL;

Rem Run Session initialization script
Rem error logging table must exist
@@catupses.sql

Rem =====================================================================
Rem BEGIN STAGE 1: load dictionary changes for basic SQL processing
Rem =====================================================================

Rem run all of the "i" scripts from the earliest supported release
@@i0902000

Rem =====================================================================
Rem END STAGE 1: load dictionary changes for basic SQL processing
Rem =====================================================================

Rem =====================================================================
Rem BEGIN STAGE 2: invalidate all non-Java objects
Rem =====================================================================
 
-- This block of code sets up to run utlip.sql only if the release is
-- prior to 11.1 or there has been a platform change. 

-- It uses the existence of a 11.1 table to determine the need for 
-- utlip.sql.  MODEL$ is created in the upgrade to 11.1;
-- if it exists, then utlip.sql is not needed unless there is
-- a platform change.

DEFINE utlip_file = 'utlip.sql';
DEFINE utlip_tabcol = NULL;

COLUMN utlip_name NEW_VALUE utlip_file NOPRINT;
COLUMN utlip_tabcolumn NEW_VALUE utlip_tabcol NOPRINT;

SELECT 'nothing.sql' AS utlip_name FROM obj$ 
       WHERE name = 'MODEL$' and owner#=0;

SELECT platform_id AS utlip_tabcolumn FROM v$database;

SELECT 'SELECT platform_id FROM registry$database' AS utlip_tabcolumn FROM 
obj$ WHERE name = 'REGISTRY$DATABASE';

-- Set utlip_name if the platform identifer in v$database 
-- does not match platform identifier in registry$database
SELECT 'utlip.sql' AS utlip_name FROM v$database
    WHERE v$database.platform_id  
      NOT IN (&&utlip_tabcol);      
@@&utlip_file

-- Bug 6446262, check forINVALID old versions of types and update 
-- any with status = 6
SELECT name, subname, owner#, status FROM obj$
       WHERE type#=13 AND subname IS NOT NULL AND status > 1;
UPDATE obj$ SET status=1 
       WHERE type#=13 AND subname IS NOT NULL AND status=6;
COMMIT;
ALTER SYSTEM FLUSH SHARED_POOL;

-- Reload dbms_assert package for changed interfaces (used in "c" scripts)
@@dbmsasrt.sql
@@prvtasrt.plb

Rem =====================================================================
Rem END STAGE 2: invalidate all non-Java objects
Rem =====================================================================

Rem =====================================================================
Rem BEGIN STAGE 3: dictionary upgrade
Rem =====================================================================

WHENEVER SQLERROR EXIT

Rem Determine original release and run the appropriate script
CREATE OR REPLACE FUNCTION version_script 
RETURN VARCHAR2 IS

  p_null         char(1);
  p_version      VARCHAR2(30);
  p_prv_version  VARCHAR2(30);
  server_version VARCHAR2(30);

BEGIN

-- For 11.2, direct uppgrades are supported from 9.2.0.8, 10.1.0.5,
-- 10.2.0.2 and above, and 11.1.0.6 and above
--
  SELECT version INTO p_version FROM registry$ where cid='CATPROC';
  IF substr(p_version,1,7) = '9.2.0.8' THEN
     RETURN '0902000';
  ELSIF substr(p_version,1,8) = '10.1.0.5' THEN
     RETURN '1001000';
  ELSIF substr(p_version,1,6) = '10.2.0' AND
        substr(p_version,1,8) != '10.2.0.1' THEN
     RETURN '1002000';
  ELSIF substr(p_version,1,6) = '11.1.0' THEN
     RETURN '1101000';
  ELSIF substr(p_version,1,6) = '11.2.0' THEN -- current version
     SELECT version INTO server_version FROM v$instance;
     IF substr(p_version,1,8) != substr(server_version,1,8) THEN --- run c1101000   
        RETURN '1101000';
     ELSE -- version is the same as instance, so rerun the previous upgrade
     -- rerun upgrade of previous release 
        EXECUTE IMMEDIATE
             'SELECT prv_version FROM registry$ where cid=''CATPROC'''
        INTO p_prv_version;
        IF substr(p_prv_version,1,5) = '9.2.0' THEN
           RETURN '0902000';
        ELSIF substr(p_prv_version,1,6) = '10.1.0' THEN
           RETURN '1001000';
        ELSIF substr(p_prv_version,1,6) = '10.2.0' THEN
           RETURN '1002000';
        ELSIF substr(p_prv_version,1,6) = '11.1.0' OR
              p_prv_version IS NULL THEN  -- new database
           RETURN '1101000';
        ELSE
           RAISE_APPLICATION_ERROR(-20000,
          'Upgrade re-run not supported from version ' || p_prv_version );
        END IF;
      END IF;
  END IF;

  RAISE_APPLICATION_ERROR(-20000,
       'Upgrade not supported from version ' || p_version );

END version_script;
/

Rem get the correct script name into the "upgrade_file" variable
COLUMN file_name NEW_VALUE upgrade_file NOPRINT;
SELECT version_script AS file_name FROM DUAL;

WHENEVER SQLERROR CONTINUE

Rem run the selected "c" upgrade script
@@c&upgrade_file

Rem Remove entries from sys.duc$ - rebuilt for 11g by catalog and catproc
Rem Can cause errors on any DROP USER statements in upgrade scripts
truncate table duc$;

Rem =====================================================================
Rem END STAGE 3: dictionary upgrade
Rem =====================================================================