目标端的trail文件损坏或误删除，如何重新进行抽取

本实验主要用于：目标端OGG的trail文件损坏或人为误删除，在不丢失任何数据的情况下， 如何进行操作

1 重新启动目标端并关闭，查看检查点的信息：---此处是模拟trail文件被删除，复制进程读取的检查点信息
GGSCI (oracle10g1) 63> info reptb1 showch


REPLICAT   REPTB1    Last Started 2014-09-16 15:35   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:18 ago)
Log Read Checkpoint  File ./dirdat/pt000049
                     2014-09-16 15:34:43.058962  RBA 1210              ----查看复制进程读取的检查点时间


Current Checkpoint Detail:


Read Checkpoint #1


  GGS Log Trail


  Startup Checkpoint (starting position in the data source):
    Sequence #: 48
    RBA: 237667372
    Timestamp: 2014-09-15 16:09:28.991913
    Extract Trail: ./dirdat/pt


  Current Checkpoint (position of last record read in the data source):
    Sequence #: 49
    RBA: 1210
    Timestamp: 2014-09-16 15:34:43.058962
    Extract Trail: ./dirdat/pt


CSN state information:
  CRC: 9-CA-86-D3
  Latest CSN: 2130166
  Latest TXN: 2.33.820
  Latest CSN of finished TXNs: 2130166
  Completed TXNs: 2.33.820


Header:
  Version = 2
  Record Source = A
  Type = 1
  # Input Checkpoints = 1
  # Output Checkpoints = 0


File Information:
  Block Size = 2048
  Max Blocks = 100
  Record Length = 2048
  Current Offset = 0


Configuration:
  Data Source = 0
  Transaction Integrity = -1
  Task Type = 0


Database Checkpoint:
  Checkpoint table = ogg.checkpoint
  Key = 367089447 (0x15e15727)
  Create Time = 2014-09-04 21:00:29


Status:
  Start Time = 2014-09-16 15:35:12
  Last Update Time = 2014-09-16 15:36:19
  Stop Status = G
  Last Result = 400

GGSCI (oracle10g1) 64> 




2 插入数据用于验证：在重新指定从某个时间节点抽取时，数据未丢失：
SQL> conn oggtest/oggtest
Connected.
SQL> insert into employees1 select * from hr.employees;


107 rows created.


SQL> commit;


Commit complete.


SQL> 




3 确保数据传输进程未任何的延时
GGSCI (oracle10g) 32> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXTTB1      00:00:00      00:00:10    
EXTRACT     RUNNING     PUMPTB1     00:00:00      00:00:02    




GGSCI (oracle10g) 33> info pumptb1


EXTRACT    PUMPTB1   Last Started 2014-09-16 15:34   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:11 ago)
Log Read Checkpoint  File /app/oracle/goldengate/dirdat/tb000039
                     2014-09-16 15:40:53.000000  RBA 30094




GGSCI (oracle10g) 34> 




4 查看源端的trail文件的信息：
GGSCI (oracle10g) 34> info exttrail *


       Extract Trail: ./dirdat/tb
             Extract: EXTTB1
               Seqno: 39
                 RBA: 30094
           File Size: 500M
       Extract Trail: ./dirdat/pb
             Extract: EXTTB1
               Seqno: 3
                 RBA: 0
           File Size: 100M


       Extract Trail: ./dirdat/pt
             Extract: PUMPTB1
               Seqno: 49
                 RBA: 30244
           File Size: 500M




5 查看目标端复制进程读取的检查点信息：
GGSCI (oracle10g1) 65> info reptb1 showch


REPLICAT   REPTB1    Last Started 2014-09-16 15:35   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:11:19 ago)
Log Read Checkpoint  File ./dirdat/pt000049
                     2014-09-16 15:34:43.058962  RBA 1210    ---根据此时间点，重新进行抽取传输




Current Checkpoint Detail:


Read Checkpoint #1


  GGS Log Trail


  Startup Checkpoint (starting position in the data source):
    Sequence #: 48
    RBA: 237667372
    Timestamp: 2014-09-15 16:09:28.991913
    Extract Trail: ./dirdat/pt


  Current Checkpoint (position of last record read in the data source):
    Sequence #: 49
    RBA: 1210
    Timestamp: 2014-09-16 15:34:43.058962
    Extract Trail: ./dirdat/pt


CSN state information:
  CRC: 9-CA-86-D3
  Latest CSN: 2130166
  Latest TXN: 2.33.820
  Latest CSN of finished TXNs: 2130166
  Completed TXNs: 2.33.820


Header:
  Version = 2
  Record Source = A
  Type = 1
  # Input Checkpoints = 1
  # Output Checkpoints = 0


File Information:
  Block Size = 2048
  Max Blocks = 100
  Record Length = 2048
  Current Offset = 0


Configuration:
  Data Source = 0
  Transaction Integrity = -1
  Task Type = 0


Database Checkpoint:
  Checkpoint table = ogg.checkpoint
  Key = 367089447 (0x15e15727)
  Create Time = 2014-09-04 21:00:29


Status:
  Start Time = 2014-09-16 15:35:12
  Last Update Time = 2014-09-16 15:36:19
  Stop Status = G
  Last Result = 400






GGSCI (oracle10g1) 66> 




7 关闭源端的抽取进程和传输进程：
GGSCI (oracle10g) 37> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     STOPPED     EXTTB1      00:00:00      00:00:05    
EXTRACT     STOPPED     PUMPTB1     00:00:00      00:00:04    




GGSCI (oracle10g) 38> 




8 指定抽取进程重新抽取：
GGSCI (oracle10g) 38> alter exttb1,begin 2014-09-16 15:34:43.058962
EXTRACT altered.




GGSCI (oracle10g) 39> alter exttb1,etrollover


2014-09-16 15:56:01  INFO    OGG-01520  Rollover performed.  For each affected output trail of Version 10 or higher format, 
after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file; 
 it will not happen automatically.
EXTRACT altered.




GGSCI (oracle10g) 40> start exttb1


Sending START request to MANAGER ...
EXTRACT EXTTB1 starting




GGSCI (oracle10g) 41> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXTTB1      00:15:28      00:00:07    
EXTRACT     STOPPED     PUMPTB1     00:00:00      00:05:17    




GGSCI (oracle10g) 42> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXTTB1      00:00:00      00:00:01    
EXTRACT     STOPPED     PUMPTB1     00:00:00      00:05:22    




GGSCI (oracle10g) 43> sh ls -ltr ./dirdat


total 464748
-rw-rw-rw- 1 oracle oinstall      1060 Sep 14 20:34 tb000032
-rw-rw-rw- 1 oracle oinstall      1222 Sep 15 10:15 tb000033
drwxr-xr-x 2 oracle oinstall      4096 Sep 15 16:32 backup
-rw-rw-rw- 1 oracle oinstall       999 Sep 15 17:54 tb000035
-rw-rw-rw- 1 oracle oinstall      1060 Sep 15 17:56 tb000036
-rw-rw-rw- 1 oracle oinstall 237667549 Sep 15 17:56 tb000037
-rw-rw-rw- 1 oracle oinstall 237667488 Sep 16 15:34 tb000038
-rw-rw-rw- 1 oracle oinstall     30094 Sep 16 15:40 tb000039
-rw-rw-rw- 1 oracle oinstall     30033 Sep 16 15:56 tb000040    ---新生成的trail文件






8 使用logdump查看是否将刚执行的SQL抽取到：
Logdump 1 >detail on
Logdump 2 >ghdr on
Logdump 3 >open ./dirdat/tb000040
Current LogTrail is /app/oracle/goldengate/dirdat/tb000040 
Logdump 4 >count
LogTrail /app/oracle/goldengate/dirdat/tb000040 has 108 records 
Total Data Bytes             19624 
  Avg Bytes/Record             181 
Insert                         107 
Others                           1 
After Images                   107 


Average of 2 Transactions 
    Bytes/Trans .....      12404 
    Records/Trans ...         54 
    Files/Trans .....          1 
 


*FileHeader*                                       Partition 0 
Total Data Bytes               991 
  Avg Bytes/Record             991 
Others                           1 


OGGTEST.EMPLOYEES1                                 Partition 4 
Total Data Bytes             18633 
  Avg Bytes/Record             174 
Insert                         107                 ---将刚插入的数据重新又抽取到新的40这个trail文件中
After Images                   107 
Logdump 5 >


使数据传输进程回滚到下一个文件，并指定其从新的40trail文件开始传输


9 查看传输进程传输的检查点及时间
GGSCI (oracle10g) 45> info pumptb1 showch


EXTRACT    PUMPTB1   Last Started 2014-09-16 15:34   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:11:58 ago)
Log Read Checkpoint  File /app/oracle/goldengate/dirdat/tb000039
                     2014-09-16 15:40:53.000000  RBA 30094                  ----传输的时间




Current Checkpoint Detail:


Read Checkpoint #1


  GGS Log Trail


  Startup Checkpoint (starting position in the data source):
    Sequence #: 38
    RBA: 237667488
    Timestamp: 2014-09-15 16:15:16.000000
    Extract Trail: /app/oracle/goldengate/dirdat/tb


  Current Checkpoint (position of last record read in the data source):
    Sequence #: 39
    RBA: 30094
    Timestamp: 2014-09-16 15:40:53.000000                                ---数据源的时间点
    Extract Trail: /app/oracle/goldengate/dirdat/tb


Write Checkpoint #1


  GGS Log Trail


  Current Checkpoint (current write position):
    Sequence #: 49
    RBA: 30244
    Timestamp: 2014-09-16 15:51:05.796071
    Extract Trail: ./dirdat/pt


CSN state information:
  CRC: 91-8B-80-BC
  Latest CSN: 2164509
  Latest TXN: 3.4.842
  Latest CSN of finished TXNs: 2164509
  Completed TXNs: 3.4.842


Header:
  Version = 2
  Record Source = A
  Type = 1
  # Input Checkpoints = 1
  # Output Checkpoints = 1


File Information:
  Block Size = 2048
  Max Blocks = 100
  Record Length = 2048
  Current Offset = 0


Configuration:
  Data Source = 0
  Transaction Integrity = 1
  Task Type = 0


Status:
  Start Time = 2014-09-16 15:34:41
  Last Update Time = 2014-09-16 15:51:05
  Stop Status = G
  Last Result = 400




10 指定从新的trail开始抽取，并回滚到trail文件


GGSCI (oracle10g) 46> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXTTB1      00:00:00      00:00:03    
EXTRACT     STOPPED     PUMPTB1     00:00:00      00:15:06    


GGSCI (oracle10g) 5> alter pumptb1,extseqno 40,extrba 0
EXTRACT altered.




GGSCI (oracle10g) 6> start pumptb1


Sending START request to MANAGER ...
EXTRACT PUMPTB1 starting




11 到目标端查看，是否已经将要的数据传输到目标端：
Logdump 138 >detail on
Logdump 139 >ghdr on
Logdump 140 >open ./dirdat/pt000050
Current LogTrail is /app/oracle/goldengate/dirdat/pt000050 
Logdump 141 >count
LogTrail /app/oracle/goldengate/dirdat/pt000050 has 108 records 
Total Data Bytes             19670 
  Avg Bytes/Record             182 
Insert                         107 
Others                           1 
After Images                   107 


Average of 2 Transactions 
    Bytes/Trans .....      12427 
    Records/Trans ...         54 
    Files/Trans .....          1 
 


*FileHeader*                                       Partition 0 
Total Data Bytes              1037 
  Avg Bytes/Record            1037 
Others                           1 


OGGTEST.EMPLOYEES1                                 Partition 4 
Total Data Bytes             18633 
  Avg Bytes/Record             174 
Insert                         107                     ----已经传输到目标端
After Images                   107 
Logdump 142 >






12 指定复制进程，从指定的trail文件，开始读取，启动目标端：
GGSCI (oracle10g1) 71> alter reptb1,extseqno 50
REPLICAT altered.




GGSCI (oracle10g1) 72> start reptb1


Sending START request to MANAGER ...
REPLICAT REPTB1 starting


GGSCI (oracle10g1) 80> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
REPLICAT    ABENDED     REPTB1      00:00:00      00:00:43    


发现报如下错误： 1028有可能是将trail文件的头损坏了，不影响数据的复制，故可以跳过。


2014-09-16 16:34:23  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, reptb1.prm:  REPLICAT REPTB1 started.
2014-09-16 16:34:44  ERROR   OGG-01028  Oracle GoldenGate Delivery for Oracle, reptb1.prm:  Incompatible record (101) in ./dirdat/pt000050, rba 1210 (getting header).
2014-09-16 16:34:44  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, reptb1.prm:  PROCESS ABENDING.




使用logdump打开对应的文件，找到第一条记录，并指定从此记录开始读取
Logdump 142 >open ./dirdat/pt000050
LogTrail /app/oracle/goldengate/dirdat/pt000050 closed 
Current LogTrail is /app/oracle/goldengate/dirdat/pt000050 
Logdump 143 >n


2014/09/16 16:28:49.935.447 FileHeader           Len  1037 RBA 0 
Name: *FileHeader* 
 3000 01d0 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0...0...GG..TL..1...  
 0003 3200 0004 2000 0000 3300 0008 02f2 295d ff31 | ..2... ...3.....)].1  
 0857 3400 0026 0024 7572 693a 6f72 6163 6c65 3130 | .W4..&.$uri:oracle10  
 673a 3a61 7070 3a6f 7261 636c 653a 676f 6c64 656e | g::app:oracle:golden  
 6761 7465 3500 002a 3500 0026 0024 7572 693a 6f72 | gate5..*5..&.$uri:or  
 6163 6c65 3130 673a 3a61 7070 3a6f 7261 636c 653a | acle10g::app:oracle:  
 676f 6c64 656e 6761 7465 3600 0013 0011 2e2f 6469 | goldengate6....../di  
 
Logdump 144 >n
___________________________________________________________________ 
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)  
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
RecLength  :   177  (x00b1)   IO Time    : 2014/09/16 15:40:52.999.978   
IOType     :     5  (x05)     OrigNode   :   255  (xff) 
TransInd   :     .  (x00)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
AuditRBA   :        112       AuditPos   : 31156928 
Continued  :     N  (x00)     RecCount   :     1  (x01) 


2014/09/16 15:40:52.999.978 Insert               Len   177 RBA 1045 
Name: OGGTEST.EMPLOYEES1 
After  Image:                                             Partition 4   G  b   
 0000 000a 0000 0000 0000 0000 00c6 0001 000a 0000 | ....................  
 0006 446f 6e61 6c64 0002 000c 0000 0008 4f43 6f6e | ..Donald........OCon  
 6e65 6c6c 0003 000c 0000 0008 444f 434f 4e4e 454c | nell........DOCONNEL  
 0004 0010 0000 000c 3635 302e 3530 372e 3938 3333 | ........650.507.9833  
 0005 0015 0000 3139 3939 2d30 362d 3231 3a30 303a | ......1999-06-21:00:  
 3030 3a30 3000 0600 0c00 0000 0853 485f 434c 4552 | 00:00........SH_CLER  
 4b00 0700 0a00 0000 0000 0000 03f7 a000 0800 0aff | K...................  
Column     0 (x0000), Len    10 (x000a)  
Column     1 (x0001), Len    10 (x000a)  
Column     2 (x0002), Len    12 (x000c)  
Column     3 (x0003), Len    12 (x000c)  
Column     4 (x0004), Len    16 (x0010)  
Column     5 (x0005), Len    21 (x0015)  
Column     6 (x0006), Len    12 (x000c)  
Column     7 (x0007), Len    10 (x000a)  
Column     8 (x0008), Len    10 (x000a)  
Column     9 (x0009), Len    10 (x000a)  
Column    10 (x000a), Len    10 (x000a)  
   
Logdump 145 >






GGSCI (oracle10g1) 3> start reptb1


Sending START request to MANAGER ...
REPLICAT REPTB1 starting




GGSCI (oracle10g1) 4> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
REPLICAT    RUNNING     REPTB1      00:00:00      00:00:08 


GGSCI (oracle10g1) 8> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
REPLICAT    RUNNING     REPTB1      00:00:00      00:00:03    




GGSCI (oracle10g1) 9> info reptb1


REPLICAT   REPTB1    Last Started 2014-09-16 16:45   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint  File ./dirdat/pt000050
                     2014-09-16 15:40:52.992044  RBA 30079




查看对应的数据：
SQL> conn oggtest/oggtest
Connected.
SQL> select count(*) from employees1;


  COUNT(*)
----------
         0


SQL> /


  COUNT(*)
----------
       107


SQL> 


已经复制过来。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11783123/viewspace-2125613/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11783123/viewspace-2125613/