Zero Downtime Upgrade Guide for Oracle

10.3 HotFix 2

Back Next

Upgrade Steps Controlled from the Active Environment

The active environment must be prepared after you complete the upgrade steps on the passive environment. Run the steps from the active environment.

During the upgrade, you must drop the source schema, re-create it from the target schema, and then import the database dump file. Do not attempt to bypass this process by applying a change list, because the schemas must be exactly the same in both databases for the replication to work. To avoid making inadvertent changes, enable Production Mode on the source and target databases. Log in to the Hub Console, select the Databases tool, select the database, and enable Production Mode. In future, if you need to apply a change list to the target database, you can disable Production Mode and apply the change list.

Before you begin, open the following repository tables and make a note of the values in the columns.

C_REPOS_ZDT_STATUS

Record the values for all columns. You need these values in step 6.

C_REPOS_DB_RELEASE

Record the values for the following columns. You need these values in step 7.

db_password, tns_name

connection_port

oracle_sid

database_host

connect_url

database_id

connection_type

proxy_ind

db_proxy_username

db_proxy_password

db_replication_username

db_replication_password

debug_ind

debug_level

debug_file_name

debug_file_pat

Infrastructure Upgrade

. Upgrade hardware and third-party software in the active environment.

Stop the application servers, close connections, and then drop and re-create the schema in the active environment.

Stop the MDM application servers at the active environment.

Close all the connections, such as SQL*Plus, TOAD, and the application server that are connected to the active environment.

Drop the schema in active environment, and then create it.

Drop schema A - using system user (sqlplus system/password@tnsname)
 SQL> drop user envA cascade;
 
Create schema A - using system user (sqlplus system/password@tnsname)
 SQL> <hub_server_install>/resources/database/custom_scripts/oracle/import/@mk_cmx_ors_user; -- supply the schema name as A

Re-create the Operational Reference Store by importing the dump file that was generated from the passive environment.

C:\> impdp <dba_username>/<dba_password>@<tns_entry_name>
     directory=<DATA_PUMP_DIR_OBJECT>
     dumpfile==<mrm_backup_envb.dmp>
     logfile=<mrm_restore_after_upgrade.log>
     content=all 
     remap_schema=<from_user>:<to_user>
     parallel=8
     job_name=<RESTORE_ENVB>

While the schema is being created, you might see the following messages:

ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal

The type already exists, and therefore it is not re-created. You can safely ignore these messages.

Remove event queues and drop tables.

Remove 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;

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.

Re-compile the packages, stored procedures, and views.

Navigate to

<MDM installation directory>/resources/database/oracle/en_US/

As the SYSTEM user, run the following commands from SQL*plus:


SQL > @update_javasp.sql  
      @compile_types.sql

Navigate to

<MDM installation directory>/resources/database/oracle/

Run the following commands from SQL*plus:


SQL > @cmx_zdt_objects_support.sql
      @cmxlb_pack.plb
      @cmx_debug_print_prc.plb
      @cmxlog_pack.plb
      @cmxlog_body.plb
      @cmxzdt_pack.plb
      @cmxzdt_body.plb

Restore the ZDT status for the active environment.

Use the values that were in the C_REPOS_ZDT_STATUS table before you dropped the schema.

delete from c_repos_zdt_status;
commit;

insert into C_REPOS_ZDT_STATUS  
(REPLICATION_TARGET_IND, ACTIVE_UPGRADE_IND, CREATOR, CREATE_DATE,GGS_HOME_PATH, REPLICAT_NUMBER, TRAIL_FILE_SIZE, DISCARD_FILE_SIZE, ACCEPTABLE_LAG_MINUTES, LAG_DETECTION_TOKEN, LOCAL_ENVIRONMENT_NAME, LOCAL_SCHEMA_NAME, LOCAL_TRAIL_PATH, PUMP_RMTHOST, PUMP_MGRPORT, REMOTE_TRAIL_PATH, REMOTE_ENVIRONMENT_NAME, REMOTE_SCHEMA_NAME , REGULAR_STREAM_ID, EVENT_QUEUE_ID, EXTRACT_PREFIX, REPLICAT_PREFIX , BATCH_DISABLED_IND, DEFAULT_TIMEOUT_MINUTES
) 
Values
(<insert a comma-separated list of the values that were in the C_REPOS_ZDT_STATUS table before you dropped the schema.>
);
commit;

Check the environment-specific settings on the active environment in the C_REPOS_DB_RELEASE table.

All the values in the table must be local and the database entries must point to the local database. If necessary, update the values to match the values that were in the C_REPOS_DB_RELEASE table before you dropped the schema.

Configure sequences on the active environment to be odd:

BEGIN
    CMXZDT.GET_ZDT_CONFIG;
    CMXZDT.CONFIGURE_SEQUENCES;
END;
/

Deploy and start event queues on the active environment, which is the new target:

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;
/

Deploy new replication baseline parameter files from the active environment to the passive environment:

BEGIN
  CMXZDT.CONFIGURE_GGS_REPLICAT(CMXZDT.zdt_local);
END;
/

Start replication replay on the active environment. Use the SCN you obtained in Upgrade Steps Controlled from the Passive Environment.

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,in_after_csn => <SCN>);
                                   
  dbms_output.put_line('out_return_code => '||out_return_code||' out_error_msg => '||substr(out_error_msg,1,250));
end;
/

MDM Upgrade

. On the new passive environment, upgrade the

Multidomain MDM

software and configure the process server.

Upgrading with Zero Downtime

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal