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.
Wednesday, June 25, 2014
Tuesday, June 24, 2014
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-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';
Friday, June 13, 2014
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
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
Subscribe to:
Posts (Atom)