Friday, June 5, 2015

Move object or schema from one replicate group to another replicate group

Move object or schema from one replicate group to another replicate group

           E0000018  --> P0000018 --> R0000018
           E0000050  --> P0000050 --> R0000050


Note
1)     We are moving one schema from the Replicat(R0000050) to Replicat(R0000018)
2)     Schema name CG_DATA
3)     Take the backup of all the param which are planned to modify during the change


#1 Add table on the new extract & Pump process

GGSCI> stop E0000018
GGSCI> stop P0000018

Add the below line in the param file(Extract & Pump)
TABLE CG_DATA.*;

GGSCI> start E0000018
GGSCI> start P0000018

Note: make sure that new schema object are seen in the extract & pump stats
To see it
GGSCI> STATS EXTRACT E0000018,TABLE CG_DATA.*
GGSCI> STATS EXTRACT P0000018,TABLE CG_DATA.*




#2 Make sure Extract & PUMP & Replicat (R0000050) lag is “0”
GGSCI> info R0000050
REPLICAT   R0000050  Last Started 2015-05-31 08:12   Status RUNNING
Description          "CG_DATA@editorial -> CG_DATA@staging"
Checkpoint Lag       00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint  File /n01/oraggsdata/dirdat/ora269a/CG_DATA/tr000025
                     2015-06-03 11:42:06.082537  RBA 1061441

Note: if the Extract & PUMP & Replicat lag are not “0”, then we have to make sure it is LAG is zero.

#3 Stop the Replicat(R0000050 & R0000018 ) and find the CSN number from replicat R0000050 trail file
A)    Stop the replicat process
GGSCI> stop R0000050
GGSCI> stop R0000018

B)     Remove the schema entire from the replicat(R0000050)
C)     You need to find the CSN number from the trail file of replicat(R0000050) using the logdump utility.
Note:
CASE#1 In our case, after we stopped there is no update to the trail file; so we have followed the below process.

/n01/oraggsbin/11.2: ./logdump
Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.28 19820451 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_150116.0606
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

Logdump 25 >open /n01/oraggsdata/dirdat/ora269a/CG_DATA/tr000025
Current LogTrail is /n01/oraggsdata/dirdat/ora269a/CG_DATA/tr000025
Logdump 26 >pos  1061441
Reading forward from RBA 1061441
Logdump 27 >detail data
Logdump 28 >ggstoken detail
Logdump 29 >sfh
Scan reached EOF
Logdump 30 >sfh prev

2015/06/03 11:37:06.085.185 GGSPKUpdate          Len   346 RBA 1060211
Name: GGS.HEARTBEAT
After  Image:                                             Partition 4   GU s  
 000f 0001 000b 0000 0007 6f72 7437 3330 6100 0000 | ..........ort730a... 
 04ff ff00 0000 0100 0b00 0000 076f 7274 3733 3061 | .............ort730a 
 0002 0004 ffff 0000 0003 001f ffff 3139 3030 2d30 | ..............1900-0 
 312d 3031 3a30 303a 3030 3a30 302e 3030 3030 3030 | 1-01:00:00:00.000000 
 3030 3000 0400 1fff ff31 3930 302d 3031 2d30 313a | 000......1900-01-01: 
 3030 3a30 303a 3030 2e30 3030 3030 3030 3030 0005 | 00:00:00.000000000.. 
 001f ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 | ....1900-01-01:00:00 
Before Image          Len    17 (x00000011)
KeyLen    15 (x0000000f)
KeyCol     1 (x0001), Len    11 (x000b) 
 0000 0007 6f72 7437 3330 61                       | ....ort730a 

After Image           Len   329 (x00000149)
Column     0 (x0000), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     1 (x0001), Len    11 (x000b) 
 0000 0007 6f72 7437 3330 61                       | ....ort730a 
Column     2 (x0002), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     3 (x0003), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     4 (x0004), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     5 (x0005), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     6 (x0006), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     7 (x0007), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     8 (x0008), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     9 (x0009), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    10 (x000a), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column    11 (x000b), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    12 (x000c), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    13 (x000d), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    14 (x000e), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    15 (x000f), Len    31 (x001f) 
 0000 3230 3135 2d30 362d 3033 3a31 313a 3337 3a31 | ..2015-06-03:11:37:1 
 312e 3033 3937 3338 3030 30                       | 1.039738000 
Column    16 (x0010), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    17 (x0011), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    18 (x0012), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    19 (x0013), Len     4 (x0004) 
 ffff 0000                                         | .... 
 
GGS tokens:
TokenID x52 'R' ORAROWID         Info x00  Length   20
 4141 4162 3055 4142 6341 4141 4676 6a41 4141 0001 | AAAb0UABcAAAFvjAAA.. 
TokenID x4c 'L' LOGCSN           Info x00  Length   13
 3231 3831 3138 3630 3139 3832 32                  | 2181186019822 
TokenID x36 '6' TRANID           Info x00  Length   12
 3231 2e32 372e 3834 3835 3530                     | 21.27.848550 
  
Logdump 31 >

CASE#2
   If the trail is getting regular update, as soon you entire the sfh, it will throw output

Logdump 31 >open /n01/oraggsdata/dirdat/ord639a/CHEM_PROC/tr000025
Current LogTrail is /n01/oraggsdata/dirdat/ord639a/CHEM_PROC/tr000025
Logdump 32 >detail data
Logdump 33 >ggstoken detail
Logdump 34 >pos 10000
Reading forward from RBA 10000
Logdump 35 >sfh

2015/05/28 13:12:10.591.876 GGSPKUpdate          Len   346 RBA 10436
Name: GGS.HEARTBEAT
After  Image:                                             Partition 4   GU s  
 000f 0001 000b 0000 0007 6f72 7437 3330 6100 0000 | ..........ort730a... 
 04ff ff00 0000 0100 0b00 0000 076f 7274 3733 3061 | .............ort730a 
 0002 0004 ffff 0000 0003 001f ffff 3139 3030 2d30 | ..............1900-0 
 312d 3031 3a30 303a 3030 3a30 302e 3030 3030 3030 | 1-01:00:00:00.000000 
 3030 3000 0400 1fff ff31 3930 302d 3031 2d30 313a | 000......1900-01-01: 
 3030 3a30 303a 3030 2e30 3030 3030 3030 3030 0005 | 00:00:00.000000000.. 
 001f ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 | ....1900-01-01:00:00 
Before Image          Len    17 (x00000011)
KeyLen    15 (x0000000f)
KeyCol     1 (x0001), Len    11 (x000b) 
 0000 0007 6f72 7437 3330 61                       | ....ort730a 

After Image           Len   329 (x00000149)
Column     0 (x0000), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     1 (x0001), Len    11 (x000b) 
 0000 0007 6f72 7437 3330 61                       | ....ort730a 
Column     2 (x0002), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     3 (x0003), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     4 (x0004), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     5 (x0005), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     6 (x0006), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     7 (x0007), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column     8 (x0008), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column     9 (x0009), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    10 (x000a), Len    31 (x001f) 
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0 
 302e 3030 3030 3030 3030 30                       | 0.000000000 
Column    11 (x000b), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    12 (x000c), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    13 (x000d), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    14 (x000e), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    15 (x000f), Len    31 (x001f) 
 0000 3230 3135 2d30 352d 3238 3a31 333a 3132 3a31 | ..2015-05-28:13:12:1 
 312e 3034 3136 3339 3030 30                       | 1.041639000 
Column    16 (x0010), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    17 (x0011), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    18 (x0012), Len     4 (x0004) 
 ffff 0000                                         | .... 
Column    19 (x0013), Len     4 (x0004) 
 ffff 0000                                         | .... 
 
GGS tokens:
TokenID x52 'R' ORAROWID         Info x00  Length   20
 4141 4162 3055 4142 6341 4141 4676 6a41 4141 0001 | AAAb0UABcAAAFvjAAA.. 
TokenID x4c 'L' LOGCSN           Info x00  Length   13
 3231 3739 3734 3138 3533 3835 32                  | 2179741853852 
TokenID x36 '6' TRANID           Info x00  Length   11
 3231 2e36 2e38 3233 3133 32                       | 21.6.823132 
  

D)    Add the schema entire to the replicat(R0000018); you need to use the CSN from the previous step

รจ SCN number is 2181186019822 as per the CASE#1;

MAP CG_DATA.*, TARGET CG_DATA.*, FILTER (@GETENV("TRANSACTION", "CSN") > 2181186019822);

E)     Start the replicat process
GGSCI> start R0000050
GGSCI> start R0000018

F)     Check the status with the below command

GGSCI> STATS EXTRACT R0000018,TABLE CG_DATA.*

#4 Post movement steps
A)    Once you are happy with the changes, you can remove the schema capture from the E0000050 & P0000050.
B)     You can leave the Replicat(R0000018) running for sometime; then we can remove the filter condition from the param file
Before modification
MAP CG_DATA.*, TARGET CG_DATA.*, FILTER (@GETENV("TRANSACTION", "CSN") > 2181186019821);
After Modification
             MAP CG_DATA.*, TARGET CG_DATA.*;
C)     You can monitor the complete process for next 2 hours.