Monday, June 08, 2015

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA Hangs

This post demonstrates that if a database has an unfinished transaction and you try to run the above-mentioned command, it will hang. I logged into an Oracle 11 database, started a transaction but did not COMMIT it in the red session below:

PQEDPT1 /export/home/oracle > sqlplus /
 
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 14:55:54 2015
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> create table andrew
  2  as select * from dba_tables
  3  where 1=2
  4  /
 
Table created.
 
SQL> insert into andrew
  2  select * from dba_tables
  3  /
 
15251 rows created.
 
I started the blue session below and tried to run an ALTER DATABASE ADD SUPPLEMENTAL LOG DATA command but it did not appear to be doing anything:

PQEDPT1 /export/home/oracle > sqlplus / as sysdba
 
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 15:01:47 2015
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> alter database add supplemental log data
  2  /

I started the green session below and saw that the blue session was waiting for events in waitclass Other:

PQEDPT1 /export/home/oracle > sqlplus /
 
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 15:23:52 2015
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select time_waited/100
  2  from v$session_event
  3  where event = 'events in waitclass Other'
  4  and sid =
  5  (select sid from v$session where username = 'SYS')
  6  /
 
TIME_WAITED/100
---------------
        1312.97
 
SQL> /
 
TIME_WAITED/100
---------------
        1321.79
 
SQL> /
 
TIME_WAITED/100
---------------
        1330.59
 
SQL> 

This was not particularly helpful but, fortunately for me, I knew that the blue session was waiting for the red session’s transaction to finish. I returned to it and did a COMMIT:

PQEDPT1 /export/home/oracle > sqlplus /
 
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 14:55:54 2015
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> create table andrew
  2  as select * from dba_tables
  3  where 1=2
  4  /
 
Table created.
 
SQL> insert into andrew
  2  select * from dba_tables
  3  /
 
15251 rows created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL>

… and the blue session’s command completed:

PQEDPT1 /export/home/oracle > sqlplus / as sysdba
 
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 15:01:47 2015
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> alter database add supplemental log data
  2  /
 
Database altered.
 
SQL>

No comments: