Shulou(Shulou.com)05/31 Report--

This article is about how to deal with OGG ora-01403 errors. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

There is a classic error in OGG operation and maintenance-1403. The phenomenon is that the replication process abended is caused by the replication update or delete operation on the target side, because the target data cannot be found during update or delete. There are many possibilities as to why the data is not on the target side, such as artificial deletion, deletion caused by trigger is not disabled, cascading foreign key deletion is not disabled, and so on. Usually our troubleshooting method is to confirm whether the trigger, cascading foreign key deletion and job on the target side have been started. If enabled, disable it. Then check whether the source table has a primary key and whether the primary key is valid in trandata. If there is no problem with the above troubleshooting, start to do table-level initialization, data pump export and import, synchronous changes.

But sometimes we can stop messing around and "fill the gap" to make the replication process resume quickly. The ideas are as follows:

1. Locate the missing data through the target ggserr log and replcat.dsc file

two。 Manually fill the gaps in the way database link executes the insert into destination select * from source table where= (the condition confirmed in step 1) on the source side.

3. Start the replication process, and the replication process reoperates the operation that failed before abended.

Here is an experiment to demonstrate the above process.

1. Source inserts the first test data

Insert into FM_TAX_RATE_TEST (TEST_ID,COUNTRY, STATE, TAX_TYPE, TAX_RATE)

Values (1),'68, 'WT3', 0.0015)

Commit

2. Target confirms synchronization

Select * from fm_tax_rate_test

COUNTR STAT TAX_TY TAX_RATE TEST_ID

-

CN 68 WT3. 0015 1

3. Target deletes the replication record and makes a 1403 error

Delete from fm_tax_rate_test where test_id=1

Commit

4. Source performing a update operation on the first test record will cause the target replication process to be interrupted. The reason for the interruption is that the data positioned by the where statement in the update statement does not exist on the target side, because I just deleted the record manually.

Update FM_TAX_RATE_TEST set country='US' where test_id=1

Commit

At this time, the target end has been interrupted, and data changes are added in source. It is expected that the changes caused by these failures will be applied after the restart of target.

Insert into FM_TAX_RATE_TEST (TEST_ID,COUNTRY, STATE, TAX_TYPE, TAX_RATE)

Values (2),'68', 'WT3', 0.0015)

Insert into FM_TAX_RATE_TEST (TEST_ID,COUNTRY, STATE, TAX_TYPE, TAX_RATE)

Values (3 Values,'68, 'WT3', 0.0015); 2

Commit

Target replication process interrupted

GGSCI (cdbsym3) 6 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REPSYM 00:00:00 00:00:02

REPLICAT ABENDED REPSYM_T 00:10:20 00:00:01

Error message fragment in ggserr.log on target side

2015-03-31 13:50:26 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, repsym_t.prm: Aborted grouped transaction on 'OGG_TEST.FM_TAX_RATE_TEST', Database error 1403 (OCI Error ORA-01403: no data found, SQL).

2015-03-31 13:50:26 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, repsym_t.prm: Repositioning to rba 170249512 in seqno 12.

2015-03-31 13:50:26 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, repsym_t.prm: SQL error 1403 mapping OGG_TEST.FM_TAX_RATE_TEST to OGG_TEST.FM_TAX_RATE_TEST OCI Error ORA-01403: no data found, SQL.

2015-03-31 13:50:26 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, repsym_t.prm: Repositioning to rba 170249512 in seqno 12.

2015-03-31 13:50:26 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, repsym_t.prm: Error mapping from OGG_TEST.FM_TAX_RATE_TEST to OGG_TEST.FM_TAX_RATE_TEST.

2015-03-31 13:50:26 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, repsym_t.prm: PROCESS ABENDING.

The data of test_id=1 is recorded in the discard file on the target side. Failed to execute udpate

More repsym_t.dsc

Oracle GoldenGate Delivery for Oracle process started, group REPSYM_T discard file opened: 2015-03-31 13:50:25

Current time: 2015-03-31 13:50:26

Discarded record from action ABEND on error 1403

OCI Error ORA-01403: no data found, SQL

Aborting transaction on. / dirdat/yt beginning at seqno 12 rba 170249512

Error at seqno 12 rba 170249512

Problem replicating OGG_TEST.FM_TAX_RATE_TEST to OGG_TEST.FM_TAX_RATE_TEST

Record not found

Mapping problem with compressed key update record (target format)...

*

TEST_ID = 1

COUNTRY = US

STATE = 68

TAX_TYPE = WT3

TAX_RATE = .00150000

TEST_ID = 1

At this time, what is most commonly used by many operators is to export source tables according to csn consistency and reinitialize tables with inconsistent data on the target side. Use the following ways to modify the replication process parameter file and restart the replication process to track progress.

Map schema.table, target schema.table, filter (@ GETENV ("TRANSACTION", "CSN") > 9527)

If the synchronized table is large, the process can be lengthy.

If only a few pieces of data are missing and others are thought to have deleted it by mistake, does it need to be dealt with so aggressively? In fact, you can use a simple method to deal with it, create a database link in the source database, manually insert the missing data on the target side to close the vulnerability, and then start the replication process. The replication process tries the failed update statement again, and the where statement locks the data that has just been manually inserted, and the modification is successful. The replication process continues to apply source-side data changes.

5. Create a database link on the source side. Where SERVICE_NAME = data is the SID of the target database

5-1 add the string of the target-side database to tnsnames.ora

To19 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.78.2.19) (PORT = 1553))

)

(CONNECT_DATA =

(SERVICE_NAME = data)

)

)

5-2 create a database link that points to the target database, where ogg_test is the schema of the target database.

Create public databbase link to19 connect to ogg_test identified by ogg_test

5-3 manually synchronize missing statements through database link. Where the select statement is the data of the source table and insert into is the target database.

Insert into ogg_test.fm_tax_rate_test@to19 select * from ogg_test.fm_tax_rate_test where test_id=1

6. Target starts the replication process

GGSCI (cdbsym3) 4 > start repsym

Sending START request to MANAGER...

REPLICAT REPSYM starting

GGSCI (cdbsym3) 5 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REPSYM 00:00:00 00:00:00

REPLICAT RUNNING REPSYM_T 00:00:00 00:00:01

Data changes have been applied to the replication side

GGSCI (cdbsym3) 8 > stats repsym total table dbp.rb_restraints

Sending STATS request to REPLICAT REPSYM...

Start of Statistics at 2015-03-31 11:09:14.

Replicating from SYMBOLS.RB_RESTRAINTS to DBP.RB_RESTRAINTS:

* Total statistics since 2015-03-31 11:08:13 * *

Total inserts 1.00

Total updates 4.00

Total deletes 0.00

Total discards 0.00

Total operations 5.00

End of Statistics.

7. View the data changes after the replication process is started in the database

OGG_TEST@data > select * from ogg_test.fm_tax_rate_test

COUNTR STAT TAX_TY TAX_RATE TEST_ID

-

US 68 WT3. 0015 1

TW 68 WT3. 0015 2

JP 68 WT3. 0015 3

The first piece of data is the data we synchronized by hand, and the last two pieces of data are the data changes after the failure.

Note: if the data from the source table also performs delete operations before manual synchronization, it cannot be obtained by isnert into select and synchronized to the target side.

Thank you for reading! This is the end of the article on "how to deal with OGG ora-01403 errors". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.