[Problem Record] logic slave database warning for long running transactions

In the afternoon, check the alter log of the slave database, and always report the following warning: the slave database is a logical slave database.

Mon Aug 5 16:29:17 2013

Warning: The following transaction makes no progress
Warning: in the last 30 seconds for the given message!
Warning: Xid =
0x0003. 025.0013b492 cscn = 9532709322703, message # = 11, slavid = 1

Knacrb: No offending session found (not ITL pressure)

For this warning, the official documentation is described as follows:

One of the primary causes for long-running transactions in a SQL apply environment is because of full table scans. Additionally, long-running transactions cocould be the result of SQL statements
Being replicated to the standby database, such as when creating or rebuilding an index.

Http://docs.oracle.com/cd/B28359_01/server.111/b28294/troubleshooting.htm

1. Not ITL (Interested Transaction list) pressure -- For details about Interested Transaction list, see Dave's blog

Identifying long-running transactions

If SQL apply is executing a single SQL statementfor a long period of time, then a warning message similar to the following is reported in the Alert Log
The SQL apply instance:

Mon Feb 17 14:40:15 2003WARNING: the following transaction makes no progressWARNING: in the last 30 seconds for the given message!WARNING: xid =0x0016.007.000017b6 cscn = 1550349, message# = 28, slavid = 1knacrb: no offending session found (not ITL pressure)

Note the following about the warning message:

• This warning is similar to the warning message returned for interested transaction list (ITL) pressure, with the exception being the last line that beginsknacrb. The final line indicates:

  • A full table scan may be occurring

  • This issue has nothing to do with interested transaction list (ITL) Pressure

• This warning message is reported only if a single statement takes more than 30 seconds to execute.

It may not be possible to determine the SQL statement being executed by the long-running statement, but the following SQL statement may help in identifying the database objects on which SQL apply is operating:

SQL> SELECT SAS.SERVER_ID  2       , SS.OWNER  3       , SS.OBJECT_NAME  4       , SS.STATISTIC_NAME  5       , SS.VALUE  6    FROM V$SEGMENT_STATISTICS SS  7       , V$LOCK L  8       , V$STREAMS_APPLY_SERVER SAS  9   WHERE SAS.SERVER_ID = &SLAVE_ID 10     AND L.SID = SAS.SID 11     AND L.TYPE = 'TM' 12     AND SS.OBJ# = L.ID1;

-- Determines which object is executing the long-running transaction.

Additionally, you can issue the following SQL statement to identify the SQL statement that has resulted in a large number of disk reads being issued per execution:

SQL> SELECT SUBSTR(SQL_TEXT,1,40)  2       , DISK_READS  3       , EXECUTIONS  4       , DISK_READS/EXECUTIONS  5       , HASH_VALUE  6       , ADDRESS  7    FROM V$SQLAREA  8   WHERE DISK_READS/GREATEST(EXECUTIONS,1) > 1  9     AND ROWNUM < 10 10   ORDER BY DISK_READS/GREATEST(EXECUTIONS,1) DESC

-- View the SQL statements that execute a large number of disk_read disk read Operations

Oracle recommends that all tables have primary key constraintsdefined, which automatically means that the column is definedNOT
NULL. For any table where a primary-key constraint cannot be defined, an index shocould be defined on an appropriate column that is definedNOT NULL.
If a suitable column does not exist on the table, then the table shocould be reviewed and, if possible, skipped by SQL apply.

-- Oracle recommends that all tables have primary key constraints, that is, This column has not null constraints. If there is no primary key, it should also be indexed on columns with not null constraints. If the table does not have a suitable column (both of the preceding two cannot meet the requirements), review the table. If possible, skip SQL apply.

The following steps describe how to skip all DML statements issued againstFTSTable onSCOTTSchema:

1. Stop SQL apply:

   SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;Database altered

2. Configure the Skip procedure forSCOTT.FTSTable for all DML transactions:

   SQL> EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'DML' , -     schema_name => 'SCOTT' , -     object_name => 'FTS');PL/SQL procedure successfully completed

3. Start SQL apply:

   SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;Database altered

2. troubleshooting ITL pressure

Interested Transaction list (ITL) pressure is reported in the alert log of the SQL apply instance. Example
A-3 shows an example of the warning messages.

Example A-3 warning messages reported for ITL pressure

Tue Apr 22 15:50:42 2003WARNING: the following transaction makes no progressWARNING: in the last 30 seconds for the given message!WARNING: xid =0x0006.005.000029fa cscn = 2152982, message# = 2, slavid = 17

Real-Time Analysis

The messages shown in example A-3 indicate that the SQL apply process (slavid)
#17 has not made any progress in the last 30 seconds. To determine the SQL statement being issued by the apply process, issue the following query:

SQL> SELECT SA.SQL_TEXT  2    FROM V$SQLAREA SA  3       , V$SESSION S  4       , V$STREAMS_APPLY_SERVER SAS  5   WHERE SAS.SERVER_ID = &SLAVEID  6     AND S.SID = SAS.SID  7   AND SA.ADDRESS = S.SQL_ADDRESSSQL_TEXT------------------------------------------------------------insert into "APP"."LOAD_TAB_1" p("PK","TEXT")values(:1,:2)

-- View the SQL statement in progress of the apply Process

An alternative method to identifying ITL pressure is to queryV$LOCKView, as shown in the following example. Any session that has a request value of 4 onTXLock, is waiting
An ITL to become available.

SQL> SELECT SID,TYPE,ID1,ID2,LMODE,REQUEST  2    FROM V$LOCK  3   WHERE TYPE = 'TX'SID        TY ID1        ID2        LMODE      REQUEST---------- -- ---------- ---------- ---------- ----------         8 TX     327688         48          6          0        10 TX     327688         48          0          4

-- View the V $ lock view to identify ITL pressure. Any session with the Tx lock with request 4 is waiting for the ITL to be available.

In this example,SID 10Is waiting forTXLock heldSID 8.

Post-incident Review

Pressure for a segment's ITL is unlikely to last for an extended period of time. in addition, ITL pressure that lasts for less than 30 seconds will not be reported in the standby databases Alert Log. therefore, to determine which objects have been subjected
To ITL pressure, issue the following statement:

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE  2    FROM V$SEGMENT_STATISTICS  3   WHERE STATISTIC_NAME = 'ITL WAITS'  4     AND VALUE > 0  5   ORDER BY VALUE

-- View all objects that have become ITL pressure

This statement reports all database segments that have had ITL pressure at some time since the instance was last started.

Note:

This SQL statement is not limited to a logical standby databases in the Data guard environment. It is applicable to any Oracle database.

Resolving ITL pressure

To increaseINITRANSInteger for a particle database object, it is necessary to first stop SQL apply.

-- Increase the table's initrans

See also:

Oracle Database SQL language reference
More information about specifyingINITRANSInteger, which it the initial number of concurrent transaction entries allocated within each data block allocated to the database object

The following example shows the necessary steps to increaseINITRANSFor Tableload_tab_1In the schemaapp.

1. Stop SQL apply:

   SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;Database altered.

2. Temporarily bypass the database GUARD:

   SQL> ALTER SESSION DISABLE GUARD;Session altered.

3. IncreaseINITRANSOn the standby database. For example:

   SQL> ALTER TABLE APP.LOAD_TAB_1 INITRANS 30;Table altered

4. Reenable the database GUARD:

   SQL> ALTER SESSION ENABLE GUARD;Session altered

5. Start SQL apply:

   SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;Database altered.

Also, consider modifying the database object on the primary database, so in the event of a switchover, the error shoshould not occur on the new standby database.