[Translated from mos] The Alter Database Add Supplemental Log Data command is suspended, mossupplemental

[Translated from mos] The Alter Database Add Supplemental Log Data command is suspended, mossupplemental

Alter Database Add Supplemental Log Data command suspended
Source:
Alter Database Add Supplemental Log Data Hangs (Document ID 406498.1)

Applicable:
Oracle Database-Enterprise Edition-Version 10.2.0.1 and later
Information in this document applies to any platform.

Symptoms:
As part of the stream replication configuration, you must specify supplemental logging at the database level

You can do this as follows:

1. manual execution: alter database add supplemental log data (primary key, unique index) COLUMNS; 2. implicitly running stored procedures, such as DBMS_STREAMS_ADM.ADD_SCHEMA_RULES3.if you setup Streams using Grid Control it is automatically executed by SrcSchemaRule1 Stage Step 12.

In some cases, this step will be suspended (hang), and the statement alter database add supplemental log data is waiting for the TX lock in shared Mode

You can follow these steps to find the problematic session:

connect / as sysdbaalter session set tracefile_identifier='SUPP';alter session set max_dump_file_size=unlimited;alter session set events '32593 errorstack(3) systemstate(266)';ALTER DATABASE add SUPPLEMENTAL LOG DATA;Upload the *SUPP* from the database trace directory.

Note: If the database has many sessions related to the dump, The systemstate will take a lot of time and generate a large trace file.
Then kill the session

It may be easier to find the blocking and waiting persons.

Cause:
Statement alter database add supplemental log data is waiting for TX lock in shared mode because of uncommitted transactions. This is the expected behavior.
When the DATABASE is open, you can issue the alter database add supplemental log data statement.
However, Oracle database sets all DML cursors In the cursor cache as invalid, which affects the performance until the cache is regenerated.
In addition, we also need to wait for all pending transactions to complete so that the redo generated after the DDL (alter database add supplemental log data) has the correct supplemental logging attribute.

Solution:
You need to wait for the completion of all pending transactions.
In some high activity databases, there are always some active transactioins. At this time, you can restart the database and manually run the SQL

STARTUP MOUNT ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER DATABASE OPEN;