Oracle: Shutdown Immediate hangs

So, I Issued a shutdown immediate today, and it sat there for almost 2 hours. As per Oracle documentation, its waiting for all the active transaction to finish...

My database has more than 30 schemas, and I probably have too many inactive connections, and hence the long wait.
 
All I could see in Alert log was "Active call for process xxxx user 'SYSTEM' program 'ORACLE.EXE (SHAD)'"

After 2 hours waiting, I gave up and started a second session and issued a Shutdown Abort.



Though this is not a bug, I was not sure how long I had to wait for all the active transactions to complete.

So, If you are planning to shutdown Immediate, make sure to query v$session for any active session, and notify the users and kill all "INACTIVE" connections.

ORA-01591: lock held by in-doubt distributed transaction

Yesterday one of my users got this error, and this is how I resolved it.

ORA-01591: lock held by in-doubt distributed transaction 10.xx.xxxxx

To read more about Distributed Transaction errors go here

Following table shows the pending transactions, You can either commit or rollback them to resolve.

select *
from DBA_2PC_PENDING
where state='prepared';

To force rollback all, generate the statements, and run them all

select 'rollback force '''||local_tran_id||''';'  sql_stmt
from DBA_2PC_PENDING
where state='prepared';

To force Commit all, generate the statements, and run them all

select 'commit force '''||local_tran_id||''';'  sql_stmt
from DBA_2PC_PENDING
where state='prepared';

ORA-00439: feature not enabled: Deferred Segment Creation on datapump import

Recently I had to downgrade my 11.2.0.1 64bit Enterprise Edition to 11.2.0.1 64 bit Standard Edition.

Since there is no easy way to downgrade, I took an export of all the schema's in data pump format.

When I tried to import the dump into the standard edition, impdp reported the following error on few tables

"ORA-00439: feature not enabled: Deferred Segment Creation on datapump import"

Looks like "Deferred Segment Creation" is available only in Enterprise edition and not in Standard.

There are 2 workarounds

 

Workaround 1

1) Let the import fail, and get the SQL for all the failing tables from the log file. Drop the schema, recreate an empty schema, and create those tables using the SQL above.

2) Add table_exists_Action=append to your parfile

3) Import the dump again

 

Workaround 2

1) This is probably an easier option, Add  Version parameter to your parfile, and specify a lower version.

2) In my case, I added version=11.1, and it worked for me