Rem
Rem $Header: a0800050.sql 10-dec-98.16:48:40 ato Exp $
Rem
Rem a0800050.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      a0800050.sql - additional ANONYMOUS BLOCK dictionary upgrade.
Rem      		Upgrade Oracle RDBMS from 8.0.5 to the new release
Rem
Rem    DESCRIPTION
Rem
Rem      Additional upgrade script to be run during the migration of a
Rem      8.0.5 database to the new release.
Rem
Rem	 This script is called from u0800050.sql and a0800040.sql
Rem
Rem	 Put any anonymous block related changes here.
Rem	 Any dictionary create, alter, updates and deletes  
Rem 	 that must be performed before catalog.sql and catproc.sql go 
Rem	 in c0800050.sql
Rem
Rem      The upgrade is performed in the following stages:
Rem        STAGE 1: additional steps to upgrade from 8.0.5 to 8.1.3
Rem        STAGE 2: upgrade from 8.0.5 to the new release
Rem
Rem    RETURNS
Rem 
Rem    NOTES
Rem      You must connect internal to run this script
Rem  
Rem    MODIFIED   (MM/DD/YY)
Rem    mkamath     02/04/99 - fixing AQ propagation upgrade
Rem    mrhodes     01/05/99 - set AQ compatible to 8.0
Rem    ato         12/10/98 - fix AQ CreateQueue
Rem    rshaikh     10/15/98 - add comments advanced replication
Rem    ato         09/10/98 - rollback 3gl callouts
Rem    ncramesh    08/04/98 - change for sqlplus
Rem    rshaikh     07/21/98 - call a0801030.sql
Rem    liwong      07/21/98 - Add repcat$_repcat.flag                        
Rem    hasun       06/30/98 - Apply snapshot upgrade from old u0800050
Rem    ato         06/12/98 - recreate existing AQ objects as first class obje
Rem    rshaikh     06/17/98 -  move all anonymous block stmts from
Rem			      u0800050.sql here
Rem

Rem =========================================================================
Rem BEGIN STAGE 1: upgrade from 8.0.5 to 8.1.3
Rem =========================================================================

Rem =========================================================================
Rem AQ-related upgrade starts here
Rem =========================================================================
 
Rem 1. Converting all 8.0 queues to first class objects starts here
Rem =========================================================================

Rem Use a internal table to store the old dictionary data

CREATE TABLE system.aq$_queue_upgrade_tmp( 
        oid             RAW(16),           
	eventid	 	NUMBER NOT NULL,	
	name		VARCHAR2(30) NOT NULL,	
	table_objno	NUMBER NOT NULL,	
	usage   	NUMBER NOT NULL,   	
	enable_flag	NUMBER NOT NULL,        
	max_retries	NUMBER,			
	retry_delay	NUMBER,			
        properties      NUMBER,                 
        ret_time        NUMBER,                 
	queue_comment	VARCHAR2(2000),		
	subscribers	sys.aq$_subscribers)
/

COMMENT ON TABLE system.aq$_queue_upgrade_tmp IS
'Internal table used for upgrade/downgrade AQ queue object only. Do not delete or modify this table.'
/

Rem Copy the dictionary data into the temp table
Rem If data already exists in aq$_queue_upgrade_tmp, no new row
Rem will be inserted into this temp table.  The insert and the
Rem delete form a transaction.

INSERT INTO system.aq$_queue_upgrade_tmp
  (oid, eventid, name, table_objno, usage, enable_flag, max_retries,
   retry_delay, properties, ret_time, queue_comment, subscribers)
  SELECT oid, eventid, name, table_objno, usage, enable_flag, max_retries,
         retry_delay, properties, ret_time, queue_comment, subscribers
    FROM system.aq$_queues
    WHERE NOT EXISTS (SELECT 1 FROM system.aq$_queue_upgrade_tmp)
/

Rem Clear the dictionary table
DELETE from system.aq$_queues
/

COMMIT
/

Rem
Rem Recreate the queues using 8.1 internal C callouts
Rem 
DECLARE
  queue_nonexistent EXCEPTION;
  PRAGMA EXCEPTION_INIT(queue_nonexistent, -24010);
  CURSOR get_old_queues_defn_c IS 
    SELECT t.schema owner, q.name name, t.name table_name, q.oid oid, 
	q.usage queue_type,  q.max_retries max_retries, q.eventid eventid,
      	q.retry_delay retry_delay, q.properties properties, 
     	q.table_objno table_objno, q.ret_time ret_time, 
	q.queue_comment queue_comment, q.enable_flag enable_flag
    FROM  system.aq$_queue_upgrade_tmp q, system.aq$_queue_tables t
    WHERE q.table_objno = t.objno;
  old_queue_defn       get_old_queues_defn_c%ROWTYPE;
  dependency_tracking  BOOLEAN;
BEGIN
  FOR old_queue_defn IN get_old_queues_defn_c LOOP
    IF (bitand(old_queue_defn.properties,1) = 1) THEN
      dependency_tracking := TRUE;
    ELSE
      dependency_tracking := FALSE;
    END IF;
    --
    -- Clear the cache, if the upgrade have run before
    --
    BEGIN
      sys.dbms_aqadm_syscalls.kwqa_3gl_ForceDropQueue(
                           owner=>old_queue_defn.owner,
                           queue_name=>old_queue_defn.name);
      COMMIT;
    EXCEPTION
      WHEN queue_nonexistent THEN
        ROLLBACK;
    END;
    sys.dbms_aqadm_syscalls.kwqa_3gl_CreateQueue(
		         owner=>old_queue_defn.owner, 
		         queue_name=>old_queue_defn.name,
	                 table_name=>old_queue_defn.table_name,
                         compatible=>'8.0',
  			 queue_oid=>old_queue_defn.oid,
			 queue_type=>old_queue_defn.queue_type,
			 max_retry=>NVL(old_queue_defn.max_retries,0),
	                 retry_interval=>NVL(old_queue_defn.retry_delay,0),
		         tracking=>dependency_tracking,
			 retention=>old_queue_defn.ret_time,
	                 comment=>old_queue_defn.queue_comment);
    COMMIT;

    --
    -- Enable the queue if necessary
    --
    IF (old_queue_defn.enable_flag <> 0) THEN
      UPDATE system.aq$_queues 
        SET enable_flag = old_queue_defn.enable_flag
      WHERE name = old_queue_defn.name
        AND table_objno = old_queue_defn.table_objno;
    END IF;
    
    COMMIT;

  END LOOP;
END;
/

Rem
Rem Move subscriber information into the aq$_queues
Rem
DECLARE
  CURSOR move_subscriber_c IS
    SELECT name, table_objno, subscribers FROM system.aq$_queue_upgrade_tmp;
    move_subscriber  move_subscriber_c%ROWTYPE;
BEGIN
  FOR move_subscriber IN move_subscriber_c LOOP
    UPDATE system.aq$_queues 
      SET subscribers =  move_subscriber.subscribers
    WHERE name = move_subscriber.name
      AND table_objno = move_subscriber.table_objno;
    COMMIT;
  END LOOP;
END;
/
Rem
Rem Done converting all 8.0 queues to first-class object.
Rem 

Rem 
Rem 2. upgrade for AQ propagation scheduling starts here
Rem =========================================================================
Rem copy every schedule entry from system.aq$_schedules to sys.aq$_schedules;
INSERT into sys.aq$_schedules (oid, destination, start_time, duration, 
       next_time, latency, last_run, jobno, failures, total_time, total_msgs,
       total_bytes, total_windows, win_msgs, win_bytes, max_num_per_win,
       max_size)
SELECT oid, destination, start_time, duration, next_time, latency, 
       last_time, jobno, 0, 0, 0, 0, 0, 0, 0, 0, 0
  FROM system.aq$_schedules;
/
Rem for every schedule entry change 'what' string and interval
Rem of corresponding propagation job
Rem also set cur_start_time of schedule to next_date of job from job$
DECLARE
  CURSOR prop_job is 
    SELECT jobno
      FROM sys.aq$_schedules;
  my_next_date DATE;
BEGIN
  FOR this_job IN prop_job LOOP
    dbms_job.what(this_job.jobno,
        'next_date := sys.dbms_aqadm.aq$_propaq(job);');
    dbms_job.interval(this_job.jobno,NULL);
    SELECT next_date 
    INTO my_next_date
    FROM job$
    WHERE job = this_job.jobno;
    IF (my_next_date IS NULL) THEN
      my_next_date := SYSDATE;
    END IF;
    UPDATE sys.aq$_schedules
    SET cur_start_time = my_next_date
    WHERE jobno = this_job.jobno;
  END LOOP;
END;
/
Rem delete all schedules from system.aq$_schedules;
DELETE FROM system.aq$_schedules;
/
COMMIT;
/

Rem 
Rem upgrade for AQ propagation scheduling done
Rem 

Rem =========================================================================
Rem AQ-related upgrade ends here
Rem =========================================================================

Rem Install the packages etc.
Rem =========================================================================
Rem replication-related upgrade starts here
Rem =========================================================================

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repcatlog
 DROP CONSTRAINT repcat$_repcatlog_request
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repcatlog
 ADD CONSTRAINT repcat$_repcatlog_request
   CHECK (request IN (-1, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
                                       11, 12, 13, 14, 15))
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repobject
 DROP CONSTRAINT repcat$_repobject_type
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repobject
 ADD CONSTRAINT repcat$_repobject_type
   CHECK (type IN (-1, 1, 2, 4, 5, 7, 8, 9, 11, 12, -3, -4))
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repobject
 DROP CONSTRAINT repcat$_repobject_status
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repobject
 ADD CONSTRAINT repcat$_repobject_status
                      CHECK (status IN (0, 1, 2, 3, 4, 5))
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repcolumn
 DROP CONSTRAINT repcat$_repcolumn_uk
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repcolumn
 ADD CONSTRAINT repcat$_repcolumn_uk UNIQUE (sname, oname, type, pos)
       DEFERRABLE INITIALLY DEFERRED
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repcat ADD(flavor_id NUMBER)
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repcat ADD(flag RAW(4) default '00000000')
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repcolumn ADD(
  ctype NUMBER,
  length NUMBER,
  precision# NUMBER,
  scale NUMBER,
  null$ NUMBER,
  charsetid NUMBER,
  charsetform NUMBER
)
/ 

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_snapgroup ADD(flavor_id NUMBER)
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
UPDATE system.repcat$_repcolumn SET
  flag = utl_raw.bit_or(flag, '04')
WHERE utl_raw.bit_and(utl_raw.substr(flag, 1, 1), '01') = '01'
/
COMMIT
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
UPDATE system.repcat$_repcolumn SET
  flag = utl_raw.bit_or(flag, '08')
WHERE utl_raw.bit_and(utl_raw.substr(flag, 1, 1), '02') = '02'
/
COMMIT
/

REM
REM This causes a number of error if Advanced Replication option
REM is not installed
REM ORA- errors expected if Advanced Replication is not installed
REM PLS- errors expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
DECLARE
  user_number NUMBER;
  my_sname    VARCHAR2(30);
  my_oname    VARCHAR2(30);
  local_sname VARCHAR2(30);
  local_oname VARCHAR2(30);
  obj_type    VARCHAR2(30);
  CURSOR c_user IS SELECT DISTINCT sname FROM system.repcat$_repcolumn;
  CURSOR c_table IS SELECT DISTINCT oname FROM system.repcat$_repcolumn rc
                      WHERE rc.sname = my_sname;
  CURSOR c_info IS
    SELECT c.name, c.type#, c.length, c.precision#, c.scale, c.null$,
      c.charsetid, c.charsetform
    FROM sys.col$ c, sys.obj$ o, sys.hist_head$ h,
      sys.coltype$ ac, sys.obj$ ot, sys.user$ ut
    WHERE o.obj# = c.obj#
      AND o.owner# = user_number
      AND o.name = local_oname
      AND c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+)
      AND bitand(c.property, 32) = 0 /* not hidden column */
      AND c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
      AND ac.toid = ot.oid$(+)
      AND ot.owner# = ut.user#(+)
      AND (o.type# = 2
            AND
            NOT EXISTS (SELECT NULL
		         FROM sys.tab$ t
		         WHERE t.obj# = o.obj#
		           AND (bitand(t.property, 512) = 512 or
                                bitand(t.property, 8192) = 8192)));
  CURSOR c_verify IS SELECT sname, oname, cname
                       FROM system.repcat$_repcolumn rc
                       WHERE rc.ctype IS NULL;
BEGIN
  FOR r_user IN c_user LOOP
    BEGIN
      my_sname := r_user.sname;
      FOR r_table IN c_table LOOP
        my_oname := r_table.oname;
        -- distinguish tables and snapshots
        BEGIN
          SELECT type INTO obj_type
            FROM dba_repobject
            WHERE sname = my_sname
              AND oname = my_oname
              AND type = 'TABLE';
          local_sname := my_sname;
          local_oname := my_oname;
        EXCEPTION WHEN no_data_found THEN
          SELECT owner, name
            INTO local_sname, local_oname
            FROM sys.dba_snapshots
            WHERE master_owner = my_sname AND master = my_oname;
        END;
        SELECT u.user# INTO user_number FROM user$ u
          WHERE u.name = local_sname;
        FOR r_info IN c_info LOOP
          UPDATE system.repcat$_repcolumn rc
            SET rc.ctype = r_info.type#,
                rc.length = r_info.length,
                rc.precision# = r_info.precision#,
                rc.scale = r_info.scale,
                rc.null$ = r_info.null$,
                rc.charsetid = r_info.charsetid,
                rc.charsetform = r_info.charsetform
            WHERE rc.sname = my_sname
              and rc.oname = my_oname
              and rc.cname = r_info.name;
        END LOOP;
      END LOOP;
    EXCEPTION WHEN OTHERS THEN
      dbms_system.ksdwrt(2, '  Replication migration error ' ||
                            TO_CHAR(SQLCODE) || ': ' || SQLERRM);
      dbms_system.ksdwrt(2, '    Repcolumn error in user "' || my_sname ||
                            '"');
    END;
  END LOOP;
  FOR r_verify IN c_verify LOOP
    dbms_system.ksdwrt(2, '  Replication migration error -' ||
                          ' NULL length for column');
    dbms_system.ksdwrt(2, '    "' || r_verify.sname || '"."' ||
                           r_verify.oname || '"."' || r_verify.cname || '"');
  END LOOP;
END;
/
commit
/
CREATE TABLE system.def$_pushed_transactions
(
  source_site    VARCHAR2(128),                                -- sending site
    CONSTRAINT def$_pushed_tran_primary 
      PRIMARY KEY(source_site),
  last_tran_id   NUMBER DEFAULT 0,               -- last committed transaction
  disabled       VARCHAR2(1) DEFAULT 'F',               -- disable propagation
    CHECK (disabled IN ('T', 'F'))
)
/
comment on table SYSTEM.DEF$_PUSHED_TRANSACTIONS is
'Information about deferred transactions pushed to this site by RepAPI clients'
/
comment on column SYSTEM.DEF$_PUSHED_TRANSACTIONS.SOURCE_SITE is
'Originating database for the deferred transaction'
/
comment on column SYSTEM.DEF$_PUSHED_TRANSACTIONS.LAST_TRAN_ID is
'Last committed transaction'
/
comment on column SYSTEM.DEF$_PUSHED_TRANSACTIONS.DISABLED is
'Disable propagation'
/
Rem =========================================================================
Rem replication-related upgrade ends here
Rem =========================================================================

Rem =========================================================================
Rem END STAGE 1: upgrade from 8.0.5 to 8.1.3
Rem =========================================================================

Rem =========================================================================
Rem BEGIN STAGE 2: upgrade from 8.0.5 to the new release
Rem =========================================================================
Rem

@@a0801030

Rem =========================================================================
Rem END STAGE 2: upgrade from 8.0.5 to the new release
Rem =========================================================================

Rem *************************************************************************
Rem END a0800050.sql
Rem *************************************************************************
