Handling ORA-1403 ora-12801 on logical standby apply [ID 1178284.1]
Handling ORA-1403 ora-12801 on logical standby apply [ID 1178284.1]
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.2Information in this document applies to any platform.
***Checked for relevance on 02-MAR-2012***
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, 2010Instructions 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 reasons1) 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 brokerDGMGRL>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;
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.
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
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 BROKERDGMGRL> 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 WNER='SHANKAR';
SQL> exec dbms_logstdby.unskip('SCHEMA_DDL', 'SHANKAR','TEST1');
SQL> exec dbms_logstdby.unskip('DML', 'SHANKAR','TEST1');
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 BROKERDGMGRL> 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;
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 brokerDGMGRL> 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';
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 brokerDGMGRL> 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';
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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17252115/viewspace-765442/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- PK重複導致Logical Standby Apply process stop - ORA-00001APP
- Step by Step Guide on How to Create Logical Standby [ID 738643.1]GUIIDE
- 建立 Logical Standby DatabaseDatabase
- manage logical standby databaseDatabase
- DataGuard:Logical Standby Switchover
- DataGuard:Logical Standby FailoverAI
- 監控Logical standby databaseDatabase
- Oracle DG建立Logical Standby DatabaseOracleDatabase
- 同事寫的監控Logical Standby SQL apply 程式stop的監控報警指令碼SQLAPP指令碼
- 物理Standby資料庫及邏輯Standby資料庫(Physical Standby & Logical Standby)資料庫
- 建立Oracle 11g logical standbyOracle
- Logical Standby Database的配置步驟.Database
- Oracle10g logical standby 建立Oracle
- Create Logical Standby For Oracle 10GOracle 10g
- Logical Standby的維護操作_SKIP
- Logical Standby中Job的處理
- Oracle10gR2 Logical Standby (zt)Oracle
- 建立Data guard logical standby database須知Database
- [江楓]In Memory Undo與logical standby databaseDatabase
- [zt] Logical standby維護命令手冊
- Oracle Logical Standby 維護常用命令Oracle
- Logical Standby常見問題解決方式
- oracle 10g logical standby db creationOracle 10g
- RAC環境LOGICAL STANDBY的SWITCHOVER切換
- Oracle 9i Logical Standby與Physical standby歸檔恢復區別Oracle
- logical standby DG同步錯誤問題總結
- 配置 Oracle 10g RAC primary + RAC logical standbyOracle 10g
- oracle LOGICAL standby ORA-04030: out of process memoryOracle
- Oracle 9i R2 配置 Logical StandbyOracle
- 常見Logical Standby異常處理[final]
- [zt]Logical standby同步故障的處理過程
- RAC環境LOGICAL STANDBY的FAILOVER切換AI
- oracle10g R2 logical standby switchover to primaryOracle
- oracle LOGICAL standby 日誌無法應用處理Oracle
- tempfile檔案過大問題處理 for logical standby
- Oracle Data Gurad -- Logical Standby 相關說明Oracle
- 4 Creating a Logical Standby Database 建立邏輯備庫Database
- Oracle10g Logical Standby的開啟與關閉Oracle