Move object or schema from one
replicate group to another replicate group
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.
