ODP.NET: ORA-03111: Break received on communication channel

I am using Oracle Managed dlls (Oracle.ManagedDataAccess.dll) in program to export a data set into XML and lately I've been getting  "ORA-03111: Break received on communication channel" errors.

I was obviously looking into network and TNS to figure out where the issue is, but it seems ODP.Net is masking the exception thrown from my program.

Actual issue was due to dirty characters in a string, and escaping them to XML was failing.

so, If you get this error, start from your SQL. Its probably a SQL error, but masked to something totally different.

Orace 12c: Enterprise Manager URL

I installed 12c on a Windows server, and it did not create a shortcut for EM URL.

Enterprise Manager (EM) URL is in following format

http://<servername>:<portname>/em
or
https://<servername>:<portname>/em

You can figure out the port name using following SQL

Run as Sys

select dbms_xdb.getHttpPort() http_port , dbms_xdb_config.getHttpsPort() https_port
from dual

Once you know the port number, you can easily access EM

impdp hangs

I had to restore a 20GB dump on AWS, and impdp was hanging at the index step for almost 3 hours

and i couldn't verify what it was doing.



Session browser said its waiting with "wait for unread message on broadcast channel"



dba_datapump_jobs status said "Executing"



 dba_objects was good too



So, to get the status of the import process, I had to query v$session_longops. Note the "SOFAR", "TOTALWORKS"  and "Messages" column. For my import session they kept changing giving the impression that its not frozen




Here are the queries...

-- to get the list of import/export running
select * from dba_datapump_jobs;

-- to check the status
select j.owner_name,j.job_name,j.state,o.object_type,o.status, o.timestamp
from dba_datapump_jobs j, dba_objects o
where j.owner_name = o.owner
and j.job_name = o.object_name;

-- to check what its doing
select opname, target, sofar, totalwork,units, elapsed_seconds, message
from v$session_longops
where sofar != totalwork
order by start_time desc;

Oracle: How to use Constants defined in a package in SQL Statement

I have an Oracle package with 100+ Constants defined.  I wanted to use those constants in a SQL, and couldn't find any easy option.

I Found this solution in StackOverflow from "Björn", which is brilliant. I just changed the return to Varchar2 instead of number, so it will work for all data types

----------------------------------------------------------------------------------------------------------------
Create or Replace Function get_constant (i_constant IN Varchar2)  RETURN Varchar2 deterministic AS
   output Varchar2(4000);
Begin

   execute immediate 'begin :output := '||i_constant||'; end;' using out output;   
   Return output;

End;
/
----------------------------------------------------------------------------------------------------------------

Create or Replace Package My_Constants as

number_constant constant int := 9;
varchar2_constant constant varchar2(200) :='Hello There';
Date_Constant constant date := sysdate;
end;
/
select get_constant('My_Constants.number_constant'),
       get_constant('My_Constants.varchar2_constant'),
       get_constant('My_Constants.Date_Constant')
     from dual;

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