3 Replies Latest reply on Mar 16, 2013 5:12 AM by TSharma-Oracle

    ORA-01403: no data found on LOGICAL STANDBY database

    Santosh Pradhan
      Hi ,

      Logical Standby issue :
      Oracle 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 :

      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

          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.

          Best Regards

          • 2. Re: ORA-01403: no data found on LOGICAL STANDBY database
            Santosh Pradhan
            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

            1 person found this helpful
            • 3. Re: ORA-01403: no data found on LOGICAL STANDBY database
              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"
              "NAME" = 'john'
              "PK" = 1 and
              "NAME" = 'andrew' and
              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);

              ---------------- ----------------- --------------- - --- -------
              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

              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;