OnlineTable Migration

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Online Table Migration using GoldenGate

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I would like to demonstrate a case study of table partitioning and “online migration” in a OLTP system with “near Zero Downtime”.

In such cases, GoldenGate is the best tool to achieve this goal of an on-line migration.

1)  Find out dependant objects.


First of all, We need to find out the list of dependent objects which are having child relation with the table on which we are going to perform the migration and partitioning. DBA_CONSTRAINTS view can be helpful to get the list of dependent tables.

select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from dba_constraints
where constraint_type in (‘R’,’F’) and r_constraint_name in (select constraint_name from dba_constraints where constraint_type in (‘P’,’U’) and table_name='<master_table_name>’);

In my case TT is the master table and TTQREL is the child table.

2) Create Temporary Partition table.

Extract the DDL of the master table and create the temporary master table by making necessary changes in the DDL that supports partitioning.

In my case, I have used hash partition methodology.

 --------------
 Master Table:
 --------------
 orginal: 
 CREATE TABLE TT
 (
 NID NUMBER,
 NAME VARCHAR2(10),
 SNUM VARCHAR2(40),
 CREATEDATE DATE,
 srstatus varchar(5)
 CONSTRAINT UQ_SNUM UNIQUE (SNUM) ENABLE,
 CONSTRAINT PK_NID PRIMARY KEY (NID) ENABLE
 );

 Temporary Master Table:
 CREATE TABLE TEMP_TT
 (
 NID NUMBER,
 NAME VARCHAR2(10),
 SNUM VARCHAR2(40),
 CREATEDATE DATE,
 srstatus varchar(5)
 CONSTRAINT T_UQ_SNUM UNIQUE (SNUM) ENABLE,
 CONSTRAINT T_PK_NID PRIMARY KEY (NID) ENABLE
 )
 SEGMENT CREATION IMMEDIATE PARTITION BY HASH (SNUM)
 (
 PARTITION PSNUM01 TABLESPACE DATA,
 PARTITION PSNUM02 TABLESPACE DATA,
 PARTITION PSNUM03 TABLESPACE DATA,
 PARTITION PSNUM04 TABLESPACE DATA
 );
 Note: Initially I will not create any constraints and indexes on the temp table as I want to load the data as fast as possible.

 

--------------
 Child Table
--------------
 original:
 CREATE TABLE SCOTT.TTQREL
 (
 QRID NUMBER,
 SNUM VARCHAR2(40),
 QRCODE VARCHAR2(30),
 CREATEDATE DATE,
 UPDATEDATE DATE,
 CONSTRAINT PK_QRID PRIMARY KEY (QRID) ENABLE,
 CONSTRAINT UK_QRCODE_SNUM UNIQUE (SNUM) ENABLE,
 CONSTRAINT FK_QRCODE_SNUM FOREIGN KEY (SNUM) REFERENCES SCOTT.TT (SNUM) ENABLE
 );

 temporary Child Table:
 CREATE TABLE SCOTT.TEMP_TTQREL
 (
 QRID NUMBER,
 SNUM VARCHAR2(40),
 QRCODE VARCHAR2(30),
 CREATEDATE DATE,
 UPDATEDATE DATE,
 CONSTRAINT T_PK_QRID PRIMARY KEY (QRID) ENABLE,
 CONSTRAINT T_UK_QRCODE_SNUM UNIQUE (SNUM) ENABLE,
 CONSTRAINT T_FK_QRCODE_SNUM FOREIGN KEY (SNUM) REFERENCES SCOTT.TEMP_TT (SNUM) ENABLE
 );
 Note: Initially I will not create any constraint nor indexes on the temp table as I want to load the data as fast as possible.
3) Configure and start the EXTRACT.

We need to start the extract & pump process on the source table to capture the changes that are happening in the live system.
In order to achieve this goldengate sofware has to be installed and configured already.

 ~~~~~~~~~
 EXTRACT
 ~~~~~~~~~
 DBLOGIN USERID ggs_owner, PASSWORD ggs_owner

 EXTRACT extt
 USERID ggs_owner,PASSWORD ggs_owner
 EXTTRAIL /opt/ggs/trails/ex
 TABLE SCOTT.TT;
 TABLE SCOTT.TTQREL;

 ADD TRANDATA SCOTT.TT
 ADD TRANDATA SCOTT.TTQREL

 ADD EXTRACT extt TRANLOG BEGIN NOW
 start EXTRACT extt

Note: Here the source and target databases are same.

 ~~~~~~~~~
  PUMP
 ~~~~~~~~~
 ADD EXTRACT pptt, EXTTRAILSOURCE /opt/ggs/trails/ex
 ADD RMTTRAIL /opt/ggs/trails/pp, EXTRACT pptt
 DELETE RMTTRAIL /opt/ggs/trails/pp, EXTRACT pptt

 ADD RMTTRAIL /opt/ggs/rmtrails/pp, EXTRACT pptt

 EXTRACT pptt
 USERID ggs_owner,PASSWORD ggs_owner
 RMTHOST testnode2, MGRPORT 7809
 RMTTRAIL /opt/ggs/rmtrails/pp
 TABLE scott.tt;

 start EXTRACT pptt
4) Perform initial load.

We need to load the static data into new temp table.
Before loading note down the SCN.

select to_char(dbms_flashback.get_system_change_number(),’999999999999999999999999′) from dual;

For loading, I have used below methodology.

insert /*+ append */ into temp_tt select * from tt where srstatus in ('SRS01','SRS02');
insert /*+ append */ into temp_tt select * from tt where srstatus = 'SRS05' and createddate > sysdate -365;
commit;

insert /*+ append */ into temp_ttqrel select * from ttqrel where snum in (select snum from temp_tt);
commit;
5) Create necessary indexes on temp table.

After initial load we need to create necessary indexes on the temp table so that out goldengate process can work efficiently.
Number of indexes must be same as they are present on original tables.

6)  Start the replicat.

After successfull completion of static data loading we need to load the changed data.
I have created below replicat to load the changed data.

 ~~~~~~~~~
 REPLICAT
 ~~~~~~~~~
 DBLOGIN USERID ggs_owner, PASSWORD ggs_owner

 ADD REPLICAT rtt, EXTTRAIL /opt/ggs/rmtrails/pp NODBCHECKPOINT
 EDIT PARAM rtt
 REPLICAT rtt
 USERID ggs_owner, PASSWORD ggs_owner
 ASSUMETARGETDEFS
 MAP SCOTT.TT, TARGET SCOTT.TEMP_TT;
 MAP SCOTT.TT, TARGET SCOTT.TEMP_TTQREL;
 DISCARDFILE /opt/ggs/dirrpt/rtt.dsc, APPEND, MEGABYTES 10

The most important thing is to start the replicate.
I have started the replicat at specific SCN (which I had taken before starting the static loading).

start rtt AFTERCSN <scn>

7) Swap the table names and constraints.

Bring down the application and rename the tables and indexes accordingly.

Rename Tables:
alter table tt rename to tt_org;
alter table temp_tt rename to tt;
alter table ttqrel rename to ttqrel_org;
alter table temp_ttqrel rename to ttqrel;

Rename Constraints:
alter table TT rename constraint UQ_SNUM to O_UQ_SNUM;
alter table TT rename constraint PK_NID to O_PK_NID;

alter table temp_tt rename constraint T_UQ_SNUM to UQ_SNUM;
alter table temp_tt rename constraint T_PK_NID to PK_NID;

alter table TTQREL rename constraont PK_QRID to O_PK_QRID;
alter table TTQREL rename constraont UK_QRCODE_SNUM to O_UK_QRCODE_SNUM;
alter table TTQREL rename constraont FK_QRCODE_SNUM to O_FK_QRCODE_SNUM;

alter table temp_ttQREL T_PK_QRID to PK_QRID;
alter table temp_ttQREL T_UK_QRCODE_SNUM to UK_QRCODE_SNUM;
alter table temp_ttQREL T_FK_QRCODE_SNUM to FK_QRCODE_SNUM;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s