Shulou(Shulou.com)06/01 Report--

First, let's look at two possible problems in OGG synchronization:

The oracle online log contains committed and uncommitted transactions, but OGG only writes committed transactions to the queue file. So what does OGG do with uncommitted transactions, especially long ones?

Some long transactions are in batch jobs and take several hours to complete, such as running batches at night. During the parsing process, OGG starts to read the online log as soon as these transactions are executed, but these transactions may last for a long time, during which the online log may be switched to the archived log, and other transactions will be executed and committed during this period. If the long transaction has not been committed and the archived log is deleted due to regular rman backups, what will OGG do?

In view of the above situation, OGG has two processing methods. The first is to use normal archiving recovery, that is, to recover all archived logs needed by OGG, probably starting from the archive where the long transaction begins, so that OGG will start parsing from the checkpoint at which the transaction begins. The second way is to use Bounded Recovery, which will be discussed below.

To put it simply, the default setting of BR (Bounded Recovery) is 4 hours, that is, every 4 hours the OGG extraction process will do a checkpoint. At the time point of each checkpoint, OGG will check the long transaction and write the status of the long transaction more than 4 hours to disk (if it does not reach 4 hours, the transaction will not be written by BR), which is saved by default in the BR directory of the OGG installation directory. At the interval of each BR, this operation continues until the transaction commits, or the transaction rolls back.

In the following example, we set the BRINTERVAL to 20 minutes:

BR BRINTERVAL 20M

The following is the official documentation description for BR:

Data is persisted on disk to recover long transactions so that the extraction process ensures capture performance (although capture delays occur only in extreme cases). If some transactions start well before this point in time when the extraction process stops, the system needs to take up a lot of log space, or these log files may not be on disk or have been deleted. Furthermore, it is unacceptable to re-start reading transactions from an early log file because other transactions in these log files have been parsed and written to the queue file.

If the state of these long transactions can be restored by persisting the data, then the round-trip read action can be eliminated. In extreme cases, if there are multiple long transactions, if each transaction requires rereading from the starting point, the capture performance of the OGG will be greatly degraded.

In this example, we set the interval for BR to 20 minutes, and then execute an insert statement without committing. At this point, the extraction process starts reading at some point in the online log, with the serial number of the online log: # 14878.

Then we switch several sets of logs, back up and delete the log file with serial number 14878. We can see that every 20 minutes, the BR checkpoint executes, and at this point, the status information and data of the long transaction are written to disk. Even if there are no corresponding archive log files on disk, the extraction process will no longer read these logs, but will recover directly from the BR data saved on disk, and if the transaction commits, OGG will directly write the data under the BR directory to the queue.

The test steps are as follows:

Execute the following INSERT statement, but not commit, to test the long transaction scenario:

SQL > insert into myobjects

Select object_id,object_name,object_type from dba_objects

75372 rows created.

Check the serial number of the currently read online log through infor ext1, which is 14878 in this test.

GGSCI 2 > info ext1

EXTRACT EXT1 Last Started 2014-06-21 18:07 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:08 ago)

Process ID 15190

Log Read Checkpoint Oracle Redo Logs

2014-06-21 18:10:21 Seqno 14878, RBA 5936128

SCN 0.9137531 (9137531)

Use SEND EXTRACT SHOWTRANS to see if any transactions are open:

GGSCI 4 > send ext1 showtrans

Sending SHOWTRANS request to EXTRACT EXT1...

Oldest redo log file necessary to restart Extract is:

Redo Log Sequence Number 14878, RBA 116752

-

XID: 10.16.1533

Items: 75372

Extract: EXT1

Redo Thread: 1

Start Time: 2014-06-21, 18, 10, 14

SCN: 0.9137521 (9137521)

Redo Seq: 14878

Redo RBA: 116752

Status: Running

INFO EXTRACT SHOWCH displays more information about the checkpoint of the extraction process, including the read point in the current transaction (log), the location where the queue file is written, and so on. In the following example, the first checkpoint is the read point at the start of the extraction process: 14861, followed by the read point of the earliest uncommitted transaction: sequence number 14878 SCN 9137521, and finally the current log read checkpoint of the extraction process, the sequence number is still 14878, but the checkpoint is 9137612, indicating that some other operations have taken place in DB after this uncommitted transaction.

GGSCI 5 > info ext1 showch

EXTRACT EXT1 Last Started 2014-06-21 18:07 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:06 ago)

Process ID 15190

Log Read Checkpoint Oracle Redo Logs

2014-06-21 18:11:41 Seqno 14878, RBA 5977088

SCN 0.9137612 (9137612)

Current Checkpoint Detail:

Read Checkpoint # 1

Oracle Redo Log

Startup Checkpoint (starting position in the data source):

Thread #: 1

Sequence #: 14861

RBA: 5918224

Timestamp: 2014-06-21 1614 4933.000000

SCN: 0.9129707 (9129707)

Redo File: / u01/app/oracle/fast_recovery_area/GGATE1/archivelog/2014_06_21/o1_mf_1_14861_9tbo7pys_.arc

Recovery Checkpoint (position of oldest unprocessed transaction in the data source):

Thread #: 1

Sequence #: 14878

RBA: 116752

Timestamp: 2014-06-21 1815 14000000

SCN: 0.9137521 (9137521)

Redo File: / u01/app/oracle/oradata/ggate1/redo03.log

Current Checkpoint (position of last record read in the data source):

Thread #: 1

Sequence #: 14878

RBA: 5977088

Timestamp: 2014-06-21 18 1115 41.000000

SCN: 0.9137612 (9137612)

Redo File: / u01/app/oracle/oradata/ggate1/redo03.log

Write Checkpoint # 1

GGS Log Trail

Current Checkpoint (current write position):

Sequence #: 3

RBA: 8130790

Timestamp: 2014-06-21 18 1115 44.414364

Extract Trail:. / dirdat/zz

Trail Type: RMTTRAIL

After about 20 minutes, we continue to use showch to see how the output differs from the previous command:

As you can see, the currently read online log sequence number has been changed to 14884 (previously 14878).

However, the restore checkpoint remains unchanged and is the same as the previous command.

GGSCI 2 > info ext1 showch

EXTRACT EXT1 Last Started 2014-06-21 18:07 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:04 ago)

Process ID 15190

Log Read Checkpoint Oracle Redo Logs

2014-06-21 18:40:34 Seqno 14884, RBA 72704

SCN 0.9139491 (9139491)

Current Checkpoint Detail:

Read Checkpoint # 1

Oracle Redo Log

Startup Checkpoint (starting position in the data source):

Thread #: 1

Sequence #: 14861

RBA: 5918224

Timestamp: 2014-06-21 1614 4933.000000

SCN: 0.9129707 (9129707)

Redo File: / u01/app/oracle/fast_recovery_area/GGATE1/archivelog/2014_06_21/o1_mf_1_14861_9tbo7pys_.arc

Recovery Checkpoint (position of oldest unprocessed transaction in the data source):

Thread #: 1

Sequence #: 14878

RBA: 116752

Timestamp: 2014-06-21 1815 14000000

SCN: 0.9137521 (9137521)

Redo File: / u01/app/oracle/oradata/ggate1/redo03.log

Current Checkpoint (position of last record read in the data source):

Thread #: 1

Sequence #: 14884

RBA: 72704

Timestamp: 2014-06-21 18-40-40-34. 000000

SCN: 0.9139491 (9139491)

Redo File: / u01/app/oracle/oradata/ggate1/redo03.log

Through the above command, we saw the information about the BR checkpoint. previously, we changed the BR interval from the default 4 hours to 20 minutes, so that every 20 minutes (in this example: 187, 1814, 27, 18), the current status information of the long transaction is written to the BR directory on disk by the extraction process.

Therefore, we see that at the 18:27 BR interval, BR persists 14881 of the online log to disk, and if there is an error or restart in extract at this time, extract no longer needs to read data from redo or archives earlier than the 14881 sequence number.

BR Previous Recovery Checkpoint:

Thread #: 0

Sequence #: 0

RBA: 0

Timestamp: 2014-06-21 18 purl 07 purl 35.982719

SCN: Not available

Redo File:

BR Begin Recovery Checkpoint:

Thread #: 0

Sequence #: 14878

RBA: 116752

Timestamp: 2014-06-21 1815 14000000

SCN: 0.9137521 (9137521)

Redo File:

BR End Recovery Checkpoint:

Thread #: 1

Sequence #: 14881

RBA: 139776

Timestamp: 2014-06-21 1815 2715 38.000000

SCN: 0.9138688 (9138688)

Redo File:

In the BR directory, we can see some files generated by the extraction process ext1:

GGSCI 4 > info ext1

EXTRACT EXT1 Last Started 2014-06-21 18:07 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:06 ago)

Process ID 15190

Log Read Checkpoint Oracle Redo Logs

2014-06-21 18:41:35 Seqno 14884, RBA 131072

SCN 0.9139583 (9139583)

GGSCI 3 > shell ls-l. / BR/EXT1

Total 20

-rw-r-- 1 oracle oinstall 65536 Jun 21 18:27 CP.EXT1.000000015

Drwxr-x- 2 oracle oinstall 4096 Jun 19 17:07 stale

At this point, what happens if we delete 14878 of the archive logs? Because the BR checkpoint has written the log sequence number 14878 containing long transactions to disk, the extract process will no longer need these old archives. To test this function, we delete 14878 after the archive backup, remember that this sequence number is the sequence number at the beginning of the long transaction and is recorded in the extraction process checkpoint log.

RMAN > backup archivelog sequence 14878 delete input

Starting backup at 21-JUN-14

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=24 device type=DISK

Channel ORA_DISK_1: starting archived log backup set

Channel ORA_DISK_1: specifying archived log (s) in backup set

Input archived log thread=1 sequence=14878 RECID=30497 STAMP=850846396

Channel ORA_DISK_1: starting piece 1 at 21-JUN-14

Channel ORA_DISK_1: finished piece 1 at 21-JUN-14

Piece handle=/u01/app/oracle/fast_recovery_area/GGATE1/backupset/2014_06_21/o1_mf_annnn_TAG20140621T234659_9tcb7msp_.bkp tag=TAG20140621T234659 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Channel ORA_DISK_1: deleting archived log (s)

Archived log file name=/u01/app/oracle/fast_recovery_area/GGATE1/archivelog/2014_06_21/o1_mf_1_14878_9tbpowlm_.arc RECID=30497 STAMP=850846396

Finished backup at 21-JUN-14

OK, let's submit the deal now.

SQL > insert into myobjects

2 select object_id,object_name,object_type from dba_objects

75372 rows created.

SQL > commit

Commit complete.

In the log report of the extraction process ext1, we can see the information of long transactions and BR checkpoints, and the redo log sequence number written by the BR checkpoint is increasing every 20 minutes, that is, the OGG extraction process writes the current log sequence number every 20 minutes, and is reflected in the OGG log report.

2014-06-21 18:17:42 WARNING OGG-01027 Long Running Transaction: XID 10.16.1533, Items 75372, Extract EXT1, Redo Thread 1, SCN 0.9137521 (9137521), Redo Seq # 14878, R

Edo RBA 116752.

2014-06-21 18:27:41 INFO OGG-01971 The previous message, 'WARNING OGG-01027', repeated 1 times.

2014-06-21 18:27:41 INFO OGG-01738 BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p23540_extr: start=SeqNo: 14878, RBA: 116752, SCN: 0.9137521 (9137521), Timest

Amp: 2014-06-21 18 RBA: 14.000000, end=SeqNo: 14881, RBA: 139776, SCN: 0.9138688 (9138688), Timestamp: June-21, 2014-06-21 18 purl 27purl 38.000000, Thread: 1.

2014-06-21 18:47:50 INFO OGG-01738 BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p23540_extr: start=SeqNo: 14885, RBA: 144912, SCN: 0.9139983 (9139983), Timest

Amp: 2014-06-21 18 purse 47.000000, Thread: 1, end=SeqNo: 14885, RBA: 145408, SCN: 0.9139983 (9139983), Timestamp: 2014-06-21 18 purl 47.000000, Thread: 1.

2014-06-21 19:07:59 INFO OGG-01738 BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p23540_extr: start=SeqNo: 14889, RBA: 176144, SCN: 0.9141399 (9141399), Timest

Amp: 2014-06-21 197 end=SeqNo 56.000000, Thread: 1, end=SeqNo: 14889, RBA: 176640, SCN: 0.9141399 (9141399), Timestamp: 2014-06-21 197V 56.000000, Thread: 1.

Finally, keep in mind that if you use the BR default of 4 hours, at least 8 hours of archived logs should be kept on the current disk to meet the requirements of any long transaction, which, of course, is often required to take longer in a real production environment. In the following illustration

T27 and T45 start before BR Nmur1 and record the status at the checkpoint BR N, while T801 starts after BR Nmurl 1. During the BR N checkpoint, because it does not meet the time requirements of BR interval, it will not be recorded on the checkpoint BR N, but on the checkpoint BR N.

Once extract is pawned within the time range of BR N and BR Number1, all information of T801 is lost. After restarting extract, the log will be parsed at the point in time starting from T801.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.