"Translated from MoS article" Alter Database Add supplemental Log Data command hangs

Tag: Alter DATABASE add S Hang

Alter Database Add Supplemental Log Data command hangs
From:
Alter Database Add Supplemental Log Data hangs (document ID 406498.1)

Suitable for:
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 need to specify supplemental logging at the database level

This can be done in the following ways:

1. Manual execution: ALTER DATABASE ADD supplemental LOG DATA (PRIMARY KEY, UNIQUE INDEX) columns;2.implicitly run stored procedures, such as Dbms_streams_a Dm. Add_schema_rules3.if you setup Streams using a Grid Control It is automatically executed by SrcSchemaRule1 Stage Step 12.

In some cases, the step hangs (hang), and the statement alter DATABASE ADD supplemental LOG DATA is waiting for the TX lock in shared mode

The following steps can be used 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 associated with the dump, then SystemState will spend a lot of time and generate a large trace file
and kill session.

It may be easier to find the blocker and the waiting person

Reason:
The statement alter DATABASE ADD supplemental LOG DATA is waiting for the shared mode TX lock because there are 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 invalidates all DML cursors in the cursor cache, which has an effect on performance until the cache is regenerated.
In addition (besides), 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 property.

Solution:
You need to wait for the completion of all pending transactions.
In some high activity databases, there are always active transactioins, which can be done by restarting the database and manually running the SQL

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

"Translated from MoS article" Alter Database Add supplemental Log Data command hangs