ORA-01403 solution for logical standby Database

In Oracle's Data Guard environment, the logical standby database application process stops and the log shows an error as ORA-01403 not data found.

The specific information is as follows:

Mon May 21 10:39:31 2012

LOGSTDBY status: ORA-01403: no data found

LOGSTDBY Apply process P004 pid = 143 OS id = 13936 stopped

Mon May 21 10:39:32 2012

Errors in file/u01/app/oracle/admin/db/bdump/rdb_lsp0_13922.trc:

ORA-12801: error signaled in parallel query server P004

ORA-01403: no data found

LOGSTDBY Analyzer process P003 pid = 38 OS id = 13932 stopped

This error is common in the logical standby environment.

Query the failed transaction information in the dba_logstdby_events view.

The query SQL statement is as follows:

Select event_time,

Xidusn,

Xidslt,

Xidsqn,

Status,

Status_code,

Event,

'Exec dbms_logstdby.skip_transaction ('| xidusn |', '| xidslt |', '|

Xidsqn | ');' as exec_ SQL,

A .*

From dba_logstdby_events

Where event_time = (select max (event_time) from dba_logstdby_events );

 

From the event column, we can find the specific information that failed to update a table.

Solution

There are two solutions to these problems:

First, you can use the skip_transaction method to ignore this transaction.

Exec dbms_logstdby.skip_transaction (values, 12, 951001 );

However, this method has sequelae, and there will be constant troubles in the subsequent synchronization process, and problems with data not found will emerge one after another.

Second, use the skip method to ignore all operations on the table.

Alter database stop logical standby apply;

Execute dbms_logstdby.skip ('dml', 'username', 't_ ZHUSHOU_ORDER ');

Execute dbms_logstdby.skip ('schema _ ddls', 'username', 't_ ZHUSHOU_ORDER ');

Alter database start logical standby apply immediate;

Ignore the DML and SCHEMA_DDL operations of the problematic table.

The problem of stopping data replication is solved, but the table cannot be synchronized in fact.

New question: How can I add tables with skip addresses to logical standby?

To solve this new problem, we mainly rely on the DBMS_LOGSTDBY.INSTANTIATE_TABLE method.

Official Instructions on its use:

This procedure creates and populates a table in the standby database from a corresponding table in the primary database. the table requires the name of the database link (dblink) as an input parameter. if the table already exists in the logical standby database, it will be dropped and re-created based on the table definition at the primary database. this procedure only brings over the data associated with the table, and not the associated indexes and constraints.

The procedure is as follows:

1. Create a database link pointing to the master database in the logical standby Database

2. Drop the table by unskip on the logical standby database.

Stop the application log of the logical standby database before unskip

Alter database stop logical standby apply;

Execute dbms_logstdby.unskip ('dml', 'username', 't_ ZHUSHOU_ORDER ');

Execute dbms_logstdby.unskip ('schema _ ddls', 'username', 't_ ZHUSHOU_ORDER ');

3. Use INSTANTIATE_TABLE to re-instantiate a table.

EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE ('username', 't_ ZHUSHOU_ORDER ', 'db _ RAC ');

Note that the user on the source database must have SELECT_CATALOG_ROLE permission, otherwise the ORA-16276 will be reported

SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE ('username', 't_ ZHUSHOU_ORDER ', 'db _ RAC ');

 

Begin DBMS_LOGSTDBY.INSTANTIATE_TABLE ('username', 't_ ZHUSHOU_ORDER ', 'db _ RAC'); end;

 

ORA-16276: specified database link does not correspond to primary database

ORA-06512: at "SYS. DBMS_INTERNAL_LOGSTDBY", line 5361

ORA-02019: connection description for remote database not found

ORA-06512: at "SYS. DBMS_LOGSTDBY", line 636

ORA-06512: at line 2

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12