Rem  ldappostadd.sql
Rem
Rem  This script updates chg_stat entries after a new node is added
Rem  It has three parts:
Rem     1. insert change status entries on the new node where new node is
Rem        consumer.
Rem        NOTE: change entries where new node as supplier will automatically
Rem        be created by replication servers on all nodes.
Rem     2. Copy shadow change log entries form asr_chg_log of the sponsor node
Rem        where the changes were not owned by the sponsor node (eg: yet to be
Rem        applied to the sponsor node).
Rem     3. Copy schema from the sponsor node to the new node.
Rem
Rem  Run this script while connected to the new node
Rem  NOTE: This script is reentrant.
Rem
Rem  Daniel  Shih           Nov. 5, 1998 
Rem                         Modified and merged
Rem                            a. ldapchstatupd1.sql
Rem                            b. ldapchstatupd2.sql
Rem                            c. ldapchlogcp.sql
Rem                            d. ldapscmcp.sql
Rem                         into one.
Rem                         Roger Raj. contributed much to the original scripts.
Rem                             

spool LOGS/ldappostadd.log

--set echo off
--1--accept NEW_TNS prompt 'Enter global name for new site: '
--2--accept SPONSOR_TNS prompt 'Enter global name for Sponsor site: '
--3--accept NEW_HOST prompt 'Enter host name for new site: '
--4--accept SPONSOR_HOST prompt 'Enter host name for Sponsor site: '

connect repadmin/repadmin@&&1

-- Clean up ods_chg_stat and asr_chg_log on the new node before starting.
truncate table ods.ods_chg_stat@&1;
truncate table ods.asr_chg_log;
truncate table ods.ods_chg_log;

--Clean up cn=OracleSchemaVersion entries in the new node so that
-- ldifwriter will not fail. Bug introduced in 206.
declare
   cursor  entries is
       SELECT entryid from ods.ct_dn
       where parentdn like 'cn=oracleschemaversion,%'
       OR rdn like 'cn=oracleschemaversion';
BEGIN

    FOR cur in entries LOOP
        DELETE ods.ds_attrstore where entryid = cur.entryid;
        DELETE ods.ct_objectclass where entryid = cur.entryid;
        DELETE ods.ct_orclguid where entryid = cur.entryid;
        DELETE ods.ct_cn where entryid = cur.entryid;
        DELETE ods.ct_dn where entryid = cur.entryid;
    END LOOP;
    commit;

END;
/


BEGIN
        dbms_reputil.replication_off;

        -- Copy ods_chg_stat from sponsor node to the new node
        insert into ods.ods_chg_stat select * from ods.ods_chg_stat@&&2;
        COMMIT;
        dbms_reputil.replication_on;
END;
/

declare sponsor_largest_no NUMBER;
BEGIN
        -- Insert new change status entries where the new node as consumer.
        -- Since the new node was copied from the sponsor node, it's change
        -- status entries where it's a consumer should be in line with where
        -- the sponsor node was a consumer.
        insert into ods.ods_chg_stat
                select CHG_NO,SUPPLIER,'&&3' from ods.ods_chg_stat
                        where supplier != consumer
                        and   consumer = '&&4';
        COMMIT;

        -- One exception of the above rule was when the sponsor node was
        -- supplier. In that case, the change number should be identical
        -- to the largest change number supplied by the sponsor node. 
        select NVL(max(chg_no), 0) INTO sponsor_largest_no
        from ods.ods_chg_log@&2 where
                server = '&4';
        insert into ods.ods_chg_stat values (sponsor_largest_no,
                                             '&4',
                                             '&3');

        -- Replication server will take care of the scenario where the new node
        -- is a supplier.

        COMMIT;
END;
/

BEGIN
        dbms_reputil.replication_off;

        insert into ods.asr_chg_log
                select * from ods.asr_chg_log@&2
                where server != '&4';
        COMMIT;
END;
/

BEGIN

        -- Reset schema on the new node.
        delete from ods.ds_attrstore where entryid=2;

        -- Copy schema from sponsor node to the new node.
        insert into ods.ds_attrstore
                select * from ods.ds_attrstore@&2
                where entryid=2;
        COMMIT;
        dbms_reputil.replication_on;
END;
/

spool off
exit;
