This discussion is archived
3 Replies Latest reply: Mar 15, 2013 10:12 PM by TSharma-Oracle RSS

ORA-01403: no data found on LOGICAL STANDBY database

Santosh Pradhan Explorer
Currently Being Moderated
Hi ,

Logical Standby issue :
Oracle 10.2.0.2 enterprise edition .

M Working on LOGICAL Standby since 1 yrs but still i havent got this ......................................
I m getting countinuously no data foud errror on logical standby database .

I found the table causing the proble(db_logstdby_events) and skipped that table and instanciated table using bwlow package:

exec dbms_logstdby.instantiate_table (.......................................

but when i start apply process on logical standby it again give no data found for new table :

Even i tried to instantiate the table using EXPORT/IMPORT during down time but the same facing same problem .

As much as i known abt the error that is :
table1
id
10
20
30


Now if sql apply process on logical standby tries to performe the update transaction(for example) as belows

update table1 set id=100 where id=50;

above query will not be completed cos it will never find the values 50 which is not in table .Thts why this error comming ..


Now my worry is ... no users dare to change/make such changes on Logical standby .So if there is no changes in tables then sqll apply should get all the values to be needded for an update ......

watingggg guyssss/......
  • 1. Re: ORA-01403: no data found on LOGICAL STANDBY database
    mseberg Guru
    Currently Being Moderated
    Hello;

    How do you know the standby database is consistent with the primary?

    Is there any chance a table(s) have been created on the Standby before they were created on the Primary? ( If yes check into DBMS_LOGSTDBY.INSTANTIATE_TABLE )

    Or skip and re-instantiate the table.

    Example
    EXEC DBMS_LOGSTDBY.SKIP('DML','EMPLOYEES','%');
    
    EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE  ('YOUR_USER', 'EMPLOYEES', 'YOUR_DBLINK');
    Best Regards

    mseberg
  • 2. Re: ORA-01403: no data found on LOGICAL STANDBY database
    Santosh Pradhan Explorer
    Currently Being Moderated
    Thanx for support ,,


    I m damm sure that table(s) are not created on standby before primary database .

    Now i performed table instanciation on logical and staring apply process but again its giving me no data found for again new tables ...


    Til now it have given me 11 tables list for no data found ,,, something is wronggggggggggggg

    Waiting.......
  • 3. Re: ORA-01403: no data found on LOGICAL STANDBY database
    TSharma-Oracle Guru
    Currently Being Moderated
    Troubleshooting ORA-1403 errors with Flashback Transaction

    In the event that the SQL Apply engine errors out with an ORA-1403, it may be possible to utilize flashback transaction on the standby database to reconstruct the missing data. This is reliant upon the undo_retention parameter specified on the standby database instance.

    ORA-1403: No Data Found

    Under normal circumstances the ORA-1403 error should not be seen in a Logical Standby environment. The error occurs when data in a SQL Apply managed table is modified directly on the standby database, and then the same data is modified on the primary database.

    When the modified data is updated on the primary database and received by the SQL Apply engine, the SQL Apply engine verifies the original version of the data is present on the standby database before updating the record. When this verification fails, an ORA-1403: No Data Found error is thrown by Oracle Data Guard: SQL Apply.

    The initial error

    When the SQL Apply engine verification fails, the error thrown by the SQL Apply engine is reported in the alert log of the logical standby database as well as a record being inserted into the DBA_LOGSTDBY_EVENTS view. The information in the alert log is truncated, while the error is reported in it's entirety in the database view.

    LOGSTDBY stmt: update "SCOTT"."MASTER"
    set
    "NAME" = 'john'
    where
    "PK" = 1 and
    "NAME" = 'andrew' and
    ROWID = 'AAAAAAAAEAAAAAPAAA'
    LOGSTDBY status: ORA-01403: no data found
    LOGSTDBY PID 1006, oracle@staco03 (P004)
    LOGSTDBY XID 0x0006.00e.00000417, Thread 1, RBA 0x02dd.00002221.10

    The Investigation

    The first step is to analyze the historical data of the table that threw the error. This can be achieved using the VERSIONS clause of the SELECT statement.

    SQL> select versions_xid
    , versions_startscn
    , versions_endscn
    , versions_operation
    , pk
    , name
    from scott.master
    versions between scn minvalue and maxvalue
    where pk = 1
    order by nvl(versions_startscn,0);

    VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN V PK NAME
    ---------------- ----------------- --------------- - --- -------
    03001900EE070000 3492279 3492290 I 1 andrew
    02000D00E4070000 3492290 D 1 andrew

    Depending upon the amount of undo retention that the database is configured to retain (undo_retention) and the activity on the table, the information returned might be extensive and the versions between syntax might need to be changed to restrict the amount of information returned.

    From the information returned, it can be seen that the record was first inserted at scn 3492279 and then was deleted at scn 3492290 as part of transaction ID 02000D00E4070000. Using the transaction ID, the database should be queried to find the scope of the transaction. This is achieved by querying the flashback_transaction_query view.

    SQL> select operation
    , undo_sql
    from flashback_transaction_query
    where xid = hextoraw('02000D00E4070000');

    OPERATION UNDO_SQL
    ---------- ------------------------------------------------
    DELETE insert into "SCOTT"."MASTER"("PK","NAME") values
    ('1','andrew');
    BEGIN

    Note that there is always one row returned representing the start of the transaction. In this transaction, only one row was deleted in the master table. The undo_sql column when executed will restore the original data into the table.

    SQL> insert into "SCOTT"."MASTER"("PK","NAME") values ('1','andrew');
    SQL> commit;

    The SQL Apply engine may now be restarted and the transaction will be applied to the standby database.

    SQL> alter database start logical standby apply;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points