Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Multidomain MDM 10.3 HotFix 1
  • All Products

Table of Contents

Search

  1. Preface
  2. Introduction to Zero Downtime
  3. Upgrading with Zero Downtime
  4. Troubleshooting

Zero Downtime Upgrade Guide for Oracle

Zero Downtime Upgrade Guide for Oracle

Back Next

Upgrade Steps Controlled from the Passive Environment

To ensure the systems are synchronized, you must have replication running from the active environment to the passive environment.
In the following procedure, every step is dependent on the previous steps. Run all the commands in order. Unless otherwise specified, ensure that each process is finished before you run the next command.
  1. MDM Upgrade
    . Stop the application server.
  2. Disable read services to the passive environment.
  3. Connect to the Operational Reference Store schema that you are upgrading.
  4. Set
    ACTIVE_UPGRADE_IND
     to
    1
     in the passive environment: 
    update C_REPOS_ZDT_STATUS set ACTIVE_UPGRADE_IND = 1;
BEGIN 
   cmxzdt.v_zdt_ind:= -1; 
END;
/ 
commit;
  5. Turn off replication replay in the passive environment: 
    DECLARE
out_error_msg VARCHAR2(32000);
out_return_code INT;
BEGIN
   cmxzdt.stop_replication_replay(out_error_message => out_error_msg, 
                                    out_return_code => out_return_code);

   dbms_output.put_line('out_return_code => '||out_return_code||' out_error_msg => '||substr(out_error_msg,1,250));
END;
/
  6. Clean up schema changes from previous upgrades.
    The following commands remove all obsolete columns and tables from the passive environment based on C_REPOS_MET_VIRTUAL_CHANGE: 
    DECLARE
   out_error_msg VARCHAR2(32000);
   out_return_code INT;
BEGIN
   cmxzdt.cleanup_obsolete_objects(out_error_message => out_error_msg, 
                                     out_return_code => out_return_code);

   dbms_output.put_line('out_return_code => '||out_return_code||' out_error_msg => '||substr(out_error_msg,1,250));
END;
/
  7. MDM Upgrade
    . Stop all Oracle GoldenGate process in the passive environment.
    If Oracle GoldenGate processes are running during the upgrade, the upgrade process cannot recompile the
    CMXZDT
     packages. In this case, the
    CMXZDT
     packages become not valid due to schema changes. 
    DECLARE
    out_error_msg   VARCHAR2(32000);
    out_return_code INT;
BEGIN
    cmxzdt.configure_ggs_event_replicat( cmxzdt.zdt_local );
    cmxzdt.stop_event_queue( out_error_msg, out_return_code );

    dbms_output.put_line('out_return_code => ' || out_return_code || ' out_error_msg => ' || SUBSTR( out_error_msg, 1, 250 ));
END;
/
  8. MDM Upgrade
    . Upgrade
    Multidomain MDM
    :
    1. Upgrade the Hub Store.
    2. Upgrade the Hub Server.
    3. Upgrade the Process Server.
    For more information about upgrading the software, see the
    Multidomain MDM Upgrade Guide
    .
    After you finish the upgrade, the application server must be running so that you can perform the remaining steps.
  9. Schema Update
    . Run the Metadata Manager command line utility to apply the changelist.
    The utility applies the changes in the changelist file to the schema. For example, you can use a changelist to add or remove columns in the base object table or to set the trust values on columns.
  10. Run a full tokenize batch job to update all the match tokens in the *_STRP tables.
    You can run the tokenize back job from the Hub Console or by using a service integration framework (SIF) API.
    Run From
    Steps
    Hub Console
    1. In the MDM Hub Console, open the Batch Viewer tool.
    2. From the Batch Viewer navigation pane, expand the base object for which you want to regenerate all the match tokens.
    3. Expand
      Generate Match Tokens
      .
    4. Select the batch job that you want to use to generate match tokens.
    5. Select
      Re-generate All Match Tokens
      .
    6. Click
      Execute Batch
      .
    API
    To run a tokenize batch job on all records, use the ExecuteBatchGenerateMatchTokens request with the
    fullRestripInd
     set to
    1
    .
    The following code sample shows an ExecuteBatchGenerateMatchTokens request to create match tokens for all records in the C_PARTY base object: 
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
         xmlns:urn="urn:siperian.api">
   <soapenv:Header/>
   <soapenv:Body>
      <urn:executeBatchGenerateMatchTokens>
         <urn:username>admin</urn:username>
         <urn:password>
            <urn:password>admin</urn:password>
            <urn:encrypted>false</urn:encrypted>
         </urn:password> 
         <urn:orsId>localhost-orclsnl-UTSOURCE</urn:orsId>
             <urn:asynchronousOptions>
            <urn:isAsynchronous>false</urn:isAsynchronous>
             <urn:jmsReplyTo></urn:jmsReplyTo>
            <urn:jmsCorrelationId></urn:jmsCorrelationId>
         </urn:asynchronousOptions>
         <urn:tableName>C_PARTY</urn:tableName>
         <urn:fullRestripInd>
    1
    </urn:fullRestripInd>
      </urn:executeBatchGenerateMatchTokens>
   </soapenv:Body>
</soapenv:Envelope>
  11. MDM Upgrade
     and
    Schema Update
    . Populate the backfill table C_REPOS_ZDT_BACKFILL_TASK to indicate that trust backfill is required on base object tables.
    Use the
    CMXZDT.add_backfill_task()
     method: 
    exec CMXZDT.add_backfill_task(
    backfill type
    , '
    base object name
    ', '
    api
    ', 
    sequence
    );
COMMIT;
    where:
    • backfill type
      is the type of backfill:
      • TRUST_BACKFILL
        .
        Recommended
        . Use when you add new trusted columns. This option runs the same processes that are run by both the
        REVALIDATE
         and
        RECALCULATE
         options.
      • REVALIDATE
        . Use when you change or add validation rules.
      • RECALCULATE
        . Use when you change trust rules.
      • TOKENIZE
        . Use when you need to run the tokenize process on dirty records, but you are unable to run batch jobs.
    • base object name
       is the table name of a base object. Run the command on all base objects tables where you want to recalculate the best version of the truth (BVT). If you are not sure which tables are affected by the schema update, run the command on all base object tables in the schema.
    • api
       specifies which API runs the backfill task.
      R
       is Read API,
      W
       is Write API, and
      B
       is both Read and Write APIs. Use
      B
      .
    • sequence
       is the order in which to run the backfill task in relationship to other tasks. If you are not sure, use
      1
       to run the backfill task first.
    For example, the following command applies trust backfill for the C_CUSTOMER base object: 
    exec CMXZDT.add_backfill_task('TRUST_BACKFILL','C_CUSTOMER','B', 1);
COMMIT;
    In the command, use the data in the repository metadata.
  12. Run the backfill batch job for each base object.
    You can run the batch backfill job from the Hub Console or by using a service integration framework (SIF) API.
    Run From
    Steps
    Hub Console
    1. In the MDM Hub Console, open the Batch Viewer tool.
    2. From the Batch Viewer navigation pane, select the base object that you want to backfill.
      If the backfill batch job does not appear in the batch viewer for the base object, select
      Batch Viewer
      Refresh
      .
    3. Run the backfill batch job.
    API
    1. Ensure the MDM Hub Server is running.
    2. Select an API.
      • To run a backfill on all base objects, use the ExecuteBatchBackfillAll API.
      • To run a backfill a specified base object, use the ExecuteBatchBackfill API.
    3. To run the backfill on all records, ensure that the
      dirtyOnlyInd
       parameter is
      false
      .
    4. Comment out the rowidObjectTable element in the request.
    The following code sample shows an ExecuteBatchBackfill request to backfill records in the C_BO_TRUST base object: 
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:siperian.api">
   <soapenv:Header/>
   <soapenv:Body>
      <urn:ExecuteBatchBackfill>
        <urn:username>
    <user name>
    </urn:username>
         <urn:password>
            <urn:password>
    <password>
    </urn:password>
         </urn:password>
         <urn:orsId>localhost-orclsnl-UTSOURCE</urn:orsId>
        <urn:asynchronousOptions>
            <urn:isAsynchronous>false</urn:isAsynchronous>
         </urn:asynchronousOptions>
         <urn:tableName>C_BO_TRUST</urn:tableName>
         
    <!--urn:rowidObjectTable>?</urn:rowidObjectTable-->
    
         <urn:dirtyOnlyInd>
    false
    </urn:dirtyOnlyInd>
      </urn:ExecuteBatchBackfill>
   </soapenv:Body>
</soapenv:Envelope>
  13. Schema Update with Data Change
    . Disable the regular Oracle GoldenGate mapping for the table C_AGREEMENT and change the mapping to table C_AGREEMENT_XREF_NEW_FROM_A.
    1. Turn off mapping for the entire C_AGREEMENT base object and all related tables by using cmxzdt.disable_bo_replication ('C_AGREEMENT'): 
      BEGIN
   cmxzdt.disable_bo_replication ('C_AGREEMENT');
commit;
END;
/
    2. Create a new mapping from C_AGREEMENT_XREF to C_AGREEMENT_XREF_NEW_FROM_A by using
      cmxzdt.remap_table_replication ('C_AGREEMENT_XREF', ' C_AGREEMENT_XREF_NEW_FROM_A'
      ). The source and target tables must be the same. The table C_AGREEMENT_XREF_NEW_FROM_A is created automatically by cmxzdt.remap_table_replication. If the table exists, the procedure fails. Run the following command: 
      BEGIN
   cmxzdt.remap_table_replication ('C_AGREEMENT_XREF', 'C_AGREEMENT_XREF_NEW_FROM_A');
commit;
END;
/
  14. Schema Update with Data Change
    . Start the reload of data to C_AGREEMENT from C_AGREEMENT_XREF. You do not need to wait for the data to reload before you proceed to the next step.
  15. If you conduct user acceptance validation, perform the following steps:
    1. Note the system change number (SCN) for later flashback.
    2. Perform user acceptance validation.
    3. Flash back to the SCN noted in substep a.
  16. MDM Upgrade
    . Start event queue processes in the passive environment: 
    DECLARE
    out_error_msg   VARCHAR2(32000);
    out_return_code INT;
BEGIN
    CMXZDT.CONFIGURE_GGS_EVENT_REPLICAT(CMXZDT.ZDT_LOCAL);
    cmxzdt.start_event_queue( out_error_msg, out_return_code );
    dbms_output.put_line('out_return_code => ' || out_return_code || ' out_error_msg => ' || SUBSTR( out_error_msg, 1, 250 ));
END;
/
  17. Process data changes from the passive environment by starting replication replay: 
    DECLARE
   out_error_msg VARCHAR2(32000);
   out_return_code INT;
BEGIN
   cmxzdt.start_replication_replay(in_env_type => cmxzdt.zdt_local,
                             out_error_message => out_error_msg,
                               out_return_code => out_return_code);

   dbms_output.put_line('out_return_code => '||out_return_code||' out_error_msg => '||substr(out_error_msg,1,250));
END;
/
  18. Detect completion of replication catch-up in the passive environment: 
    DECLARE
    out_error_msg VARCHAR2(32000);
    out_return_code INT;
BEGIN
    cmxzdt.wait_replay_catchup(in_timeout_minutes => 5,
                                out_error_message => out_error_msg,
                                  out_return_code => out_return_code);

   dbms_output.put_line('out_return_code => '||out_return_code||' out_error_msg => '||substr(out_error_msg,1,250));
END;
/
  19. Send a message from the passive environment to the active environment to disallow batch processes in the active environment: 
    DECLARE
   out_error_msg VARCHAR2(32000); 
   out_return_code INT; 
BEGIN 
   cmxzdt.disable_all_batch(in_affected_zdt_env => cmxzdt.zdt_source,
                             in_timeout_minutes => 30, 
                              out_error_message => out_error_msg, 
                                out_return_code => out_return_code); 

   dbms_output.put_line('out_return_code => '||out_return_code||'  out_error_msg => '||substr(out_error_msg,1,250)); 
END;
/
    Changes would be required to the batch scheduling and batch control script to detect this condition and not attempt new jobs. This can be controlled based on the flag on environment A in C_REPOS_ZDT_STATUS.batch_disabled_ind. Any subsequent invocation of batch jobs from the active environment will result in an error. The job scheduler should be updated accordingly.
  20. Enable writeable SIF services in the passive environment: 
    DECLARE
   out_error_msg VARCHAR2(32000);
   out_return_code INT;
BEGIN
   cmxzdt.enable_all_write_sif(in_affected_zdt_env => cmxzdt.zdt_target, 
                                in_timeout_minutes => 5,
                                 out_error_message => out_error_msg,
                                   out_return_code => out_return_code);

   dbms_output.put_line('out_return_code => '||out_return_code||' out_error_msg => '||substr(out_error_msg,1,250));
END;
/
  21. Verify that the application server is running in the passive environment, and then redirect services from the active environment to the passive environment.
    The passive environment is live for read and write services.
  22. Synchronize sequences.
    Sequences on the target system are not updated through replication. Reset the sequence numbers so that, after switching the passive and active environments, the new sequence values on the target system are higher than on the source system. 
    DECLARE 
   out_error_msg VARCHAR2(32000); 
   out_return_code INT;
BEGIN
   cmxzdt.synchronize_sequences(out_error_message => out_error_msg,
                                  out_return_code => out_return_code);

   dbms_output.put_line('out_return_code => '||out_return_code||' out_error_msg => '||substr(out_error_msg,1,250));
END;
/
  23. Disable writeable SIF services in the active environment: 
    DECLARE
   out_error_msg VARCHAR2(32000);
   out_return_code INT;
BEGIN
   cmxzdt.disable_all_write_sif(in_affected_zdt_env => cmxzdt.zdt_source,
                                 in_timeout_minutes => 5,
                                  out_error_message => out_error_msg,
                                    out_return_code => out_return_code);

   dbms_output.put_line('out_return_code => '||out_return_code||' out_error_msg => '||substr(out_error_msg,1,250));
END;
/
  24. Complete the replication processing in the passive environment: 
    DECLARE
   out_error_msg VARCHAR2(32000);
   out_return_code INT;
BEGIN
   cmxzdt.stop_replication(out_error_message => out_error_msg,
                             out_return_code => out_return_code,
                          in_timeout_minutes => 60);

   dbms_output.put_line('out_return_code => '||out_return_code||' out_error_msg => '||substr(out_error_msg,1,250));
END;
/
    This call returns a response when all data is replicated to the passive environment.
  25. Schema Update with Data Change
    . Process the delta on the C_AGREEMENT table.
    The implementation resource custom writes this to handle the data that came from the active environment after you reloaded the passive environment.
  26. MDM Upgrade
     and
    Schema Update
    . Run the backfill job on dirty records for each base object.
    Run From
    Steps
    Hub Console
    1. In the MDM Hub Console, open the Batch Viewer tool.
    2. From the Batch Viewer navigation pane, select the base object that you want to backfill.
      If the backfill batch job does not appear in the batch viewer for the base object, select
      Batch Viewer
      Refresh
      .
    3. To run the backfill on only the dirty records, select
      For dirty records only
      .
    4. Run the backfill batch job.
    API
    1. Ensure the MDM Hub Server is running.
    2. Select an API.
      • To run a backfill on all base objects, use the ExecuteBatchBackfillAll API.
      • To run a backfill a specified base object, use the ExecuteBatchBackfill API.
    3. To run the backfill on only the dirty records, set the
      dirtyOnlyInd
       parameter to
      true
      .
    4. Comment out the rowidObjectTable element in the request.
    The following code sample shows an ExecuteBatchBackfill request to backfill the dirty records in the C_BO_TRUST base object: 
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:siperian.api">
   <soapenv:Header/>
   <soapenv:Body>
      <urn:ExecuteBatchBackfill>
        <urn:username>
    <user name>
    </urn:username>
         <urn:password>
            <urn:password>
    <password>
    </urn:password>
         </urn:password>
         <urn:orsId>localhost-orclsnl-UTSOURCE</urn:orsId>
        <urn:asynchronousOptions>
            <urn:isAsynchronous>false</urn:isAsynchronous>
         </urn:asynchronousOptions>
         <urn:tableName>C_BO_TRUST</urn:tableName>
        
     <!--urn:rowidObjectTable>?</urn:rowidObjectTable-->
    
         <urn:dirtyOnlyInd>
    true
    </urn:dirtyOnlyInd>
      </urn:ExecuteBatchBackfill>
   </soapenv:Body>
</soapenv:Envelope>
  27. Remove the backfill tasks from the C_REPOS_BACKFILL_TASK table. The table must be empty so that other batch jobs can run. 
    Delete from c_repos_zdt_backfill_task;
COMMIT;
  28. MDM Upgrade
     and
    Schema Update
    . Run a tokenize batch job on dirty records for each base object.
    Run From
    Steps
    Hub Console
    1. In the MDM Hub Console, open the Batch Viewer tool.
    2. From the Batch Viewer navigation pane, expand the base object for which you want to regenerate all the match tokens.
    3. Expand
      Generate Match Tokens
      .
    4. Select the batch job that you want to use to generate match tokens.
    5. Clear
      Re-generate All Match Tokens
      .
    6. Click
      Execute Batch
      .
    API
    To run the tokenize batch job on dirty records only, use the ExecuteBatchGenerateMatchTokens request with the
    fullRestripInd
     set to
    0
    .
    The following code sample shows an ExecuteBatchGenerateMatchTokens request to create match tokens for dirty records in the C_PARTY base object: 
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
         xmlns:urn="urn:siperian.api">
   <soapenv:Header/>
   <soapenv:Body>
      <urn:executeBatchGenerateMatchTokens>
         <urn:username>admin</urn:username>
         <urn:password>
            <urn:password>admin</urn:password>
            <urn:encrypted>false</urn:encrypted>
         </urn:password> 
         <urn:orsId>localhost-orclsnl-UTSOURCE</urn:orsId>
             <urn:asynchronousOptions>
            <urn:isAsynchronous>false</urn:isAsynchronous>
             <urn:jmsReplyTo></urn:jmsReplyTo>
            <urn:jmsCorrelationId></urn:jmsCorrelationId>
         </urn:asynchronousOptions>
         <urn:tableName>C_PARTY</urn:tableName>
         <urn:fullRestripInd>
    0
    </urn:fullRestripInd>
      </urn:executeBatchGenerateMatchTokens>
   </soapenv:Body>
</soapenv:Envelope>
  29. Enable batch services in the passive environment: 
    Sample SQL:
DECLARE 
    out_error_msg VARCHAR2(32000); 
    out_return_code INT; 
BEGIN 
    cmxzdt.enable_all_batch(in_affected_zdt_env => cmxzdt.zdt_target, 
		                           in_timeout_minutes => 5,
		                            out_error_message => out_error_msg, 
		                              out_return_code => out_return_code); 

    dbms_output.put_line('out_return_code => '||out_return_code||' out_error_msg => '||substr(out_error_msg,1,250)); 
END; 
/
  30. Schema Update with Data Change
    . If any user data foreign keys changed during the update, run foreign key validation. Run the ExecuteBatchValidateFKRelationships SIF API for each base object.
    Any violations that came from the active environment after the lookup data is updated are detected.
  31. Schema Update
    . If there are violations, fix the violations.
    If the violations are not severe, you can fix the violations after you complete the upgrade. If you cannot fix some violations, contact Informatica Global Customer Support.
  32. Stop all Oracle GoldenGate processes in the passive environment and the active environment. Clean the passive environment.
    1. From the GoldenGate command line, stop all GoldenGate processes in the passive environment and remove processes and trail files: 
      GGSCI (hostname) 1> dblogin userid USER password "Password" 
GGSCI (hostname) 1> stop *
GGSCI (hostname) 1> delete * -- hit 'y' when Prompted for n/y
GGSCI (hostname) 1> DELETE TRACETABLE [SCHEMA_NAME].GGS_EVENT_TRACE 
GGSCI (hostname) 1> DELETE CHECKPOINTTABLE [SCHEMA_NAME].GGS_EVENT_CHECKPOINT
GGSCI (hostname) 1> DELETE CHECKPOINTTABLE [SCHEMA_NAME].GGS_CHECKPOINT
    2. Stop all processes in the active environment and remove processes and trail files: 
      GGSCI (hostname) 1> dblogin userid USER password "Password" 
GGSCI (hostname) 1> stop *
GGSCI (hostname) 1> delete * -- hit 'y' when Prompted for n/y
GGSCI (hostname) 1> DELETE TRACETABLE [SCHEMA_NAME].GGS_EVENT_TRACE 
GGSCI (hostname) 1> DELETE CHECKPOINTTABLE [SCHEMA_NAME].GGS_EVENT_CHECKPOINT
    3. Delete all the files related to the passive environment. Navigate to each of the following directories and remove the specified files: 
      <GoldenGate install directory>/
      dirchk
      
del *

<GoldenGate install directory>/
      dirdat
      
del enva/*
del envb/*

<GoldenGate install directory>/
      dirprm
      
del eqenv*.prm*
del pqenv*.prm*
del rqenv*.prm*
del e_env*.prm*
del p_env*.prm*
del *.def

<GoldenGate install directory>/
      dirrpt
      
del *.dsc
del e_env*.rpt
del eqenv*.rpt
del p_env*.rpt
del pqenv*.rpt
del r_env*.rpt
del rqenv*.rpt
    4. Repeat the preceding step on all the files related to the active environment.
    5. Remove the history from the ZDT event queue in the passive environment.
      Do not drop the GGS_CHECKPOINT and GGS_CHECKPOINT_LOX tables. If you drop these tables, the REPLICAT service does not start. 
      /* Repository tables for ZDT */
delete from C_REPOS_ZDT_EVENT_QUEUE;
delete from C_REPOS_ZDT_REPLICAT_EXCEPTION;
update C_REPOS_ZDT_ENV_STATE set state = NULL, state_ts = NULL, state_desc = NULL, updated_by=NULL, update_date=NULL;

/* Tables for Oracle GoldenGate */
delete from GGS_CHECKPOINT;        -- Do not drop this table
delete from GGS_CHECKPOINT_LOX;    -- Do not drop this table

drop table GGS_EVENT_CHECKPOINT cascade constraints;
drop table GGS_EVENT_CHECKPOINT_LOX cascade  constraints;
drop table GGS_EVENT_TRACE cascade  constraints;
commit;
    6. Search for %GGS% tables. Verify that the GGS_CHECKPOINT and GGS_CHECKPOINT_LOX tables exist. If you find any other tables with GGS in the table name, including TMP_GGS_* tables, you can drop these other tables.
  33. MDM Upgrade
    . Remove all temporary procedures and packages used for the backfills: 
    DECLARE
    
    PROCEDURE unregister_table(
      prowid_table VARCHAR2
    , ptable       VARCHAR2
    )
    AS
        sql_text VARCHAR2(2000);
    BEGIN
    
        debug_print(
          ' Start to unregister a table: ' || ptable || 
          ' rowid_table ' || prowid_table
        );
    
        FOR cc2 IN (
            SELECT k.rowid_key_constraint, k.key_type_str, kc.rowid_column
                 , kc.rowid_parent_column
              FROM c_repos_key_constraint k
                 , c_repos_key_constraint_col kc
                 , c_repos_column c
             WHERE k.rowid_key_constraint = kc.rowid_key_constraint
               AND kc.rowid_column = c.rowid_column
               AND c.rowid_table = prowid_table
        ) LOOP
            debug_print(
               ' Start to deprecate constraints : ' || cc2.rowid_key_constraint ||
               '  ' || cc2.rowid_column || ' rowid_table ' || prowid_table
            );
        
            sql_text := 
                'DELETE c_repos_key_constraint_col ' ||
                 'WHERE rowid_column = ''' || cc2.rowid_column || '''';
            debug_print( sql_text );
            EXECUTE IMMEDIATE sql_text;
        
            sql_text := 
                'DELETE c_repos_key_constraint ' ||
                 'WHERE rowid_key_constraint = ''' || cc2.rowid_key_constraint || '''';
            debug_print( sql_text );
            EXECUTE IMMEDIATE sql_text;
        
        END LOOP;
    
        sql_text := 
            'DELETE c_repos_column ' ||
             'WHERE rowid_table = ''' || prowid_table || '''';
        debug_print( sql_text );
        EXECUTE IMMEDIATE sql_text;
    
        sql_text := 
            'DELETE c_repos_table ' ||
             'WHERE rowid_table = ''' || prowid_table || '''';
        debug_print( sql_text );
        EXECUTE IMMEDIATE sql_text;
    
        COMMIT;
        debug_print(' unregistered table: ' || prowid_table);
    
    END unregister_table;

BEGIN
    debug_print(' ============================================= ');
    debug_print('==> Start to drop temporary ZDT objects');
    debug_print('==> ');
    FOR i IN (
        SELECT DISTINCT u.object_type t, u.object_name n
          FROM user_procedures u
         WHERE object_name IN ( 'ZDT_C_REPOS_TASK_DATA_GET_XREF'
                              , 'GENERATE_CMXZDT_TEMP'
                              , 'CMXZDT_TEMP' )
    ) LOOP
        debug_print( '==> Start to drop temporary ' || i.t || ' ' || i.n || '' );
        EXECUTE IMMEDIATE 'drop ' || i.t || ' ' || i.n || '';
    END LOOP;
    debug_print( '==> ' );
    debug_print( '==> End dropping temporary ZDT objects' );
    debug_print( ' ============================================= ' );

    debug_print( ' ============================================= ' );
    debug_print( '==> Start to unregister deprecated tables ' );
    debug_print( '==> ' );
    FOR cc IN (
        SELECT rowid_table, table_name
             , SUBSTR( table_name, LENGTH( table_name ) - 3, 4 ) AS postfix
             , SUBSTR( UPPER( table_name ), 1, LENGTH( table_name ) - 5 ) AS bo_name
                 FROM c_repos_table
                WHERE type_ind = 14
                  AND ( ( table_name LIKE '%_HUID' ) OR 
                        ( table_name LIKE '%_HFKM' ) OR
                        ( table_name LIKE '%_HMXR' )
                      )
    ) LOOP
        debug_print(
          ' Start to unregister table : ' || cc.table_name || 
          ' rowid_table ' || cc.rowid_table
        );
        unregister_table( cc.rowid_table, cc.table_name );
    END LOOP;

    debug_print( '==> ' );
    debug_print( '==> End unregistering deprecated tables ' );
    debug_print( ' ============================================= ' );

    debug_print( 'Changes are completed for all tables. ' );

END;
/
  34. Mark the passive environment as the source to set up for the replication switch: 
    update c_repos_zdt_status set REPLICATION_TARGET_IND = 0;
COMMIT;
  35. In the C_REPOS_ZDT_STATUS table, set
    ACTIVE_UPGRADE_IND
     to
    0
     in the passive environment: 
    update C_REPOS_ZDT_STATUS set ACTIVE_UPGRADE_IND = 0;
BEGIN 
    cmxzdt.v_zdt_ind:= -1; 
END;
/ 
COMMIT;
  36. Configure sequences for the passive environment to be even: 
    BEGIN
    CMXZDT.GET_ZDT_CONFIG;
    CMXZDT.CONFIGURE_SEQUENCES;
END;
/
  37. Deploy and start the event queue in the passive environment: 
    DECLARE 
   out_error_msg VARCHAR2(32000); 
   out_return_code INT;
BEGIN
   CMXZDT.CONFIGURE_GGS_EVENT_REPLICAT(CMXZDT.zdt_local);
   CMXZDT.CONFIGURE_GGS_EVENT_EXTRACT(CMXZDT.zdt_local);
   CMXZDT.START_EVENT_QUEUE(out_error_message  => out_error_msg,
                               out_return_code => out_return_code);

   dbms_output.put_line('out_return_code => '||out_return_code||' out_error_msg => '||substr(out_error_msg,1,250));
END;
/
  38. Deploy new replication baseline parameter files from the passive environment to the active environment: 
    BEGIN
    CMXZDT.CONFIGURE_GGS_EXTRACT(CMXZDT.zdt_local);
END; 
/
  39. Start extract and pump in the passive environment, which is now the source: 
    DECLARE
   out_error_msg VARCHAR2(32000);
   out_return_code INT;
BEGIN
  cmxzdt.start_replication_extract(in_env_type => cmxzdt.zdt_local,
                            out_error_message  => out_error_msg,
                               out_return_code => out_return_code);
                      
  dbms_output.put_line('out_return_code => '||out_return_code||' out_error_msg => '||substr(out_error_msg,1,250));
END; 
/
  40. Get the current SCN from the passive environment: 
    SQL-CMX_ORS_B> select current_scn from v$database;

CURRENT_SCN
-----------
    2880593
  41. Export the passive environment using Data Pump with the SCN: 
    
c:> <
    ors username
    >/<
    password
    >@<
    tns entry name
    >
    directory=<
    DATA_PUMP_DIR_OBJECT
    >
    dumpfile=<
    mrm_backup_envb
    .dmp> 
    logfile=<
    mrm_backup_after_upgrade
    .log> 
    parallel=8 
    job_name=<
    EXPORT_AFTER_UPGRADE
    >
    flashback_scn=<
    CURRENT_SCN from the previous step
    >
  42. Resume batch jobs in the passive environment.
0 COMMENTS
We’d like to hear from you!