Handling ORA-1403 ora-12801 on logical standby app

In this Document
  Purpose
  Last Review Date
  Instructions for the Reader
  Troubleshooting Details

---

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 11.2.0.1.0 - Release: 9.2 to 11.2
Information in this document applies to any platform.

Purpose

Logical standby apply may report ora-1403: No data found during apply. 
This note explains how to check the transaction that is failing and ways to fix the same problematic transaction.

Sample Alert log contents
==================

LOGMINER: Begin mining logfile: D:\ORADATA\LOGI\SREDO05.LOG
LOGSTDBY Apply process P012 started with pid=50 OS id=5300
LOGMINER: session# = 41, builder process P003 started with pid=41 OS id=4740
Tue Aug 17 09:53:08 2010
LOGSTDBY status: ORA-01403: no data found
LOGSTDBY Apply process P006 pid=44 OS id=2892 stopped
Tue Aug 17 09:53:08 2010
Errors in file c:\oracle\product\10.2.0\admin\logi\bdump\logi_lsp0_2144.trc:
ORA-12801: error signaled in parallel query server P006
ORA-01403: no data found
LOGSTDBY Apply process P006 pid=44 OS id=2892 stopped
Tue Aug 17 09:53:08 2010
Errors in file c:\oracle\product\10.2.0\admin\logi\bdump\logi_lsp0_2144.trc:
ORA-12801: error signaled in parallel query server P006
ORA-01403: no data found

Last Review Date

August 18, 2010

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

ORA-1403 is mostly caused due some of below reasons

1) Skip rule defined on DML operations earlier in Logical standby database that cause the data mismatch between PRIMARY and STANDBY database.
2) User modified the data directly in Logical standby database by setting database guard status OFF.


Solution:1
**************

1) Identify the failing sql statement using DBA_LOGSTDBY_EVENTS from logical standby database

SQL> select XIDUSN, XIDSLT, XIDSQN , status , event from dba_logstdby_events order by event_time;

XIDUSN XIDSLT XIDSQN STATUS EVENT
---------- ---------- ---------- ------------------------------ -------------------------------
5 22 1826 ORA-01403: no data found delete from "SHANKAR"."TEST1" where "ID" = 1 and 
"ENAME" = 'shankar' and ROWID = 'AAAAAAAABAAAJHaAAA'

You should not compare the ROWID of PRIMARY & STANDBY database as it will be complete different and sql apply will not check the row based on ROWID that is shown in the sql statement above.

2) Skip the failing transaction from logical standby database.

Execute the procedure dbms_lostdby.skip_transaction(XIDUSN , XIDSLT ,XIDSQN) of the failing transaction.
 

SQL> exec dbms_logstdby.skip_transaction (5,22,1826);

3) Restart the sql apply.

SQL> alter database start logical standby apply immediate;

If using broker

DGMGRL>edit database logi set state='online';

Solution:2
*************
If the data mismatch is huge that may cause repeated ora-1403 on same table then its advisable to instantiate the complete table from PRIMARY to STANDBY.

TableName : TEST1
SchemaName : SHANKAR

1. At primary, as sys user grant below roles to the user whose table(s) is being reinstantiated

SQL> grant SELECT_CATALOG_ROLE to SHANKAR;
SQL> grant EXP_FULL_DATABASE to SHANKAR;
SQL> grant IMP_FULL_DATABASE to SHANKAR;



2. As sys user, create a connected user database link at logical standby.

SQL> create public database link synctable connect to shankar identified by shankar using 'prim';

database link created.


prim - is the service name in TNSNAMES.ORA that points to primary database.

3. verify the database link from logical standby to ensure that it gets info from primary

SQL> select db_unique_name,database_role from v$database@synctable;

DB_UNIQUE_NAME DATABASE_ROLE
------------------------------ ----------------
PRIM PRIMARY



4. Stop the sql apply if it is already on. You can skip this step otherwise

SQL> select count(*) from v$logstdby; ## will result zero rows if sql apply is not running


SQL> alter database stop logical standby apply;

If using BROKER

DGMGRL> edit database logi set state='log-apply-off';

5. Remove the skip rules in LOGICAL standby database, if there is any, from the table that is being instantiated

SQL> select STATEMENT_OPT,NAME from DBA_LOGSTDBY_SKIP where OWNER='SHANKAR';
SQL> exec dbms_logstdby.unskip('SCHEMA_DDL', 'SHANKAR','TEST1');
SQL> exec dbms_logstdby.unskip('DML', 'SHANKAR','TEST1');


6. From logical standby ,start instantiating the the table as sys user

SQL> exec dbms_logstdby.instantiate_table('SHANKAR','TEST1','SYNCTABLE')

Note:- Ensure that the Schema Name, Table Name and dblink name are in Uppercase .

7. Once you are done with instantiation, resume sql apply

SQL> alter database start logical standby apply immediate;

If using BROKER

DGMGRL> edit database logi set state='online';

8.At primary, Revoke the roles granted to the application user - SHANKAR

SQL> revoke SELECT_CATALOG_ROLE from SHANKAR;
SQL> revoke EXP_FULL_DATABASE from SHANKAR;
SQL> revoke IMP_FULL_DATABASE from SHANKAR; 


Solution:3
*************

If the sql apply fails even though the data is in sync, you may need to log a service request with oracle support with the below events set in Logical standby and the issue being reproduced.

1) In logical standby, stop the sql apply

sql> alter database stop logical standby apply;


if using broker

DGMGRL> edit database logi set state='log-apply-off';

2) Set the below events in logical standby (you may backup and remove all the files in /bdump so that you can zip and upload all the files generated in /bdump to Oracle Support)

SQL> alter system set max_dump_file_size=unlimited;

SQL>alter system set events '1349 trace name context forever, level 4095';
SQL> alter system set events '16300 trace name context forever, level 15';
SQL> alter system set events '26700 trace name context forever, level 1544';
SQL>alter system set events '10308 trace name context forever, level 8';

SQL>alter system set events '1403 trace name errorstack level 3';



3) Start the sql apply

sql> alter database start logical standby apply immediate;

if using broker

DGMGRL> edit database logi set state='online';

4) Monitor the alert log to ensure issue is reproduced.

Errors in file c:\oracle\product\10.2.0\admin\logi\bdump\logi_lsp0_4532.trc:
ORA-12801: error signaled in parallel query server P006
ORA-01403: no data found

Log a service request and upload the alert log and trace files generated in /bdump folder

5) Turn off the trace events set earlier.

SQL>alter system set events '1349 trace name context off';
SQL>alter system set events '16300 trace name context off';
SQL>alter system set events '26700 trace name context off';
SQL>alter system set events '10308 trace name context off';
SQL>alter system set events '1403 trace name context off';


6. End