Symptom
Receiving POP email error on send and receive - POP mailbox is locked. Cannot receive new messages.
Cause
A mail client is attempting to access a locked POP account.
Solution
Close the mail client and reboot. Wait at least 10 minutes before opening the mail client again. (The reboot may not be needed but ensures that the application thoroughly quits.) If this does not resolve the issue please contact the Technology Support Center.
Additional Info
When a mail client connects to the POP server it locks the POP account. When the mail client has completed its tasks it issues a QUIT command to the POP server which unlocks the account. If no QUIT signal is issued and the connection remains idle for 10 minutes, the account is unlocked automatically. If mutliple computers are trying to access the POP account at the same time, this could cause the mailbox to be locked and inaccessible. Also, if the refresh rate for send/receive is more frequent than 10 minutes and a message hangs for some reason, then the server will remain locked for 10 minutes. Likewise if a client accesses the mailbox and the receive is cancelled prior to completion, the server may remain locked. The server will unlock the account automatically after 10 minutes and at that time the mail client should be able to reconnect.
Wednesday, January 27, 2016
ORA-00204 ORA-00202 ORA-27070 OSD-04006
recreate pfile
remove corrupted controlfile
and startup using pfile
Getting Error Running Sql ID ORA-01722 Invalid Number
SYMPTOMS
In Oracle Balance & Control, you get the following error when running a SQL ID:
ORA-01722: invalid number
As a result, the expected data is not updated by the SQL ID.
CAUSE
Null values exist in columns accessed by the code in the SQL ID.
SOLUTION
Review all columns accessed by the code in the SQL ID for null values. Update NULL values to zero or another appropriate number value.
Example:
select count(*) from mortgages where origination_fee is NULL;
update mortgages set origination_fee=0 where origination_fee is NULL;
1. We should try to avoid implicit conversion.
2. We should compare with same datatypes.
3. While comparing different datatypes, we should first do explicit conversion.
4. We should not store numbers in varchar2 fields.
5. We should convert the expression to number before comparing it with number.
In Oracle Balance & Control, you get the following error when running a SQL ID:
ORA-01722: invalid number
As a result, the expected data is not updated by the SQL ID.
CAUSE
Null values exist in columns accessed by the code in the SQL ID.
SOLUTION
Review all columns accessed by the code in the SQL ID for null values. Update NULL values to zero or another appropriate number value.
Example:
select count(*) from mortgages where origination_fee is NULL;
update mortgages set origination_fee=0 where origination_fee is NULL;
1. We should try to avoid implicit conversion.
2. We should compare with same datatypes.
3. While comparing different datatypes, we should first do explicit conversion.
4. We should not store numbers in varchar2 fields.
5. We should convert the expression to number before comparing it with number.
ORA-02055 ORA-20014 ORA-06502
declare
lncount number;
begin
-- Call the procedure
sp_impcodweb(oresult => :oresult,
imuact => :imuact,
infirmnumber => :infirmnumber,
icfilename => :icfilename,
icfiledirectory => :icfiledirectory,
icsrcfile => :icsrcfile,
icfiletime => :icfiletime,
icoutfile => :icoutfile);
dbms_output.put_line(:oresult);
Select Count(*)
into lncount
From Tbltempcoddetails
Where Nfirmnumber = :infirmnumber
And Ninstructiontype = 904
And Ntransactiontype = -99;
dbms_output.put_line(lncount);
Merge Into Tbl904offmarket T1
Using (Select Nfirmnumber,
Cclientboid,
Dtransactiondate,
Nnsdldpmtransactionno,
Cisincode,
Ctranstatus
From Tbltempcoddetails
Where Nfirmnumber = :infirmnumber
And Ninstructiontype = 904
And Ntransactiontype = -99) t
On (T1.Nfirmnumber = t.Nfirmnumber And T1.Cclientboid = t.Cclientboid And T1.Dexecutiondate = t.Dtransactiondate
And T1.Cisincode = t.Cisincode And To_Number(Trim(T1.Cinstructionreferenceno)) = t.Nnsdldpmtransactionno)
When Matched Then
Update Set T1.Ctranstatus = t.Ctranstatus;
Dbms_Output.Put_Line(To_Char(Sql%Rowcount) || ' rows merged. Sudi');
end;
/
Error invalid number at line -- Merge Into Tbl904offmarket T1
1) solution
select t1.Cinstructionreferenceno from Tbl904offmarket t1 WHERE REGEXP_LIKE(t1.Cinstructionreferenceno, '[[:alpha:]]')
select to_number(t1.Cinstructionreferenceno0 from Tbl904offmarket t1 WHERE REGEXP_LIKE(t1.Cinstructionreferenceno, '[[:alpha:]]')
2)
CREATE OR REPLACE FUNCTION IS_NUMBER (p_input IN VARCHAR2) RETURN NUMBER
AS
BEGIN
RETURN TO_NUMBER (p_input);
EXCEPTION
WHEN OTHERS THEN RETURN NULL;
END IS_NUMBER;
/
CREATE OR REPLACE FUNCTION IS_NUMBER (p_input IN VARCHAR2) RETURN NUMBER
AS
BEGIN
RETURN TO_NUMBER (p_input);
EXCEPTION
WHEN OTHERS THEN RETURN 0;
END IS_NUMBER;
/
lncount number;
begin
-- Call the procedure
sp_impcodweb(oresult => :oresult,
imuact => :imuact,
infirmnumber => :infirmnumber,
icfilename => :icfilename,
icfiledirectory => :icfiledirectory,
icsrcfile => :icsrcfile,
icfiletime => :icfiletime,
icoutfile => :icoutfile);
dbms_output.put_line(:oresult);
Select Count(*)
into lncount
From Tbltempcoddetails
Where Nfirmnumber = :infirmnumber
And Ninstructiontype = 904
And Ntransactiontype = -99;
dbms_output.put_line(lncount);
Merge Into Tbl904offmarket T1
Using (Select Nfirmnumber,
Cclientboid,
Dtransactiondate,
Nnsdldpmtransactionno,
Cisincode,
Ctranstatus
From Tbltempcoddetails
Where Nfirmnumber = :infirmnumber
And Ninstructiontype = 904
And Ntransactiontype = -99) t
On (T1.Nfirmnumber = t.Nfirmnumber And T1.Cclientboid = t.Cclientboid And T1.Dexecutiondate = t.Dtransactiondate
And T1.Cisincode = t.Cisincode And To_Number(Trim(T1.Cinstructionreferenceno)) = t.Nnsdldpmtransactionno)
When Matched Then
Update Set T1.Ctranstatus = t.Ctranstatus;
Dbms_Output.Put_Line(To_Char(Sql%Rowcount) || ' rows merged. Sudi');
end;
/
Error invalid number at line -- Merge Into Tbl904offmarket T1
1) solution
select t1.Cinstructionreferenceno from Tbl904offmarket t1 WHERE REGEXP_LIKE(t1.Cinstructionreferenceno, '[[:alpha:]]')
select to_number(t1.Cinstructionreferenceno0 from Tbl904offmarket t1 WHERE REGEXP_LIKE(t1.Cinstructionreferenceno, '[[:alpha:]]')
2)
CREATE OR REPLACE FUNCTION IS_NUMBER (p_input IN VARCHAR2) RETURN NUMBER
AS
BEGIN
RETURN TO_NUMBER (p_input);
EXCEPTION
WHEN OTHERS THEN RETURN NULL;
END IS_NUMBER;
/
CREATE OR REPLACE FUNCTION IS_NUMBER (p_input IN VARCHAR2) RETURN NUMBER
AS
BEGIN
RETURN TO_NUMBER (p_input);
EXCEPTION
WHEN OTHERS THEN RETURN 0;
END IS_NUMBER;
/
procedure must be declared
in application ldbo. is missing before procedure
role is missing for that procedure
Alternative: if you create public synonym for that it will work
Windows 7 Disk is not accessible. Access is denied.
Resolution
In order to resolve this issue, go to the Windows Explorer and follow the below mentioned steps.
Right-click on the inaccessible hard drive.
Click Properties.
Go to the Security tab, and then click Advanced.
Click ‘Edit’ by going to the Owner tab.
Modify the ownership of the desired account.
Alternatively, you can also try the following option, if you are not able to access any file or folder on a Windows 7 drive.
Right-click on the inaccessible file or folder.
Click Properties.
Select the Security tab.
Click your name Under Group or user name. This would show you the permissions you have to access the file and folder.
You can also try the following method
Change the drive letter for inaccessible hard drive.
Run the following command: chkdsk /r I:
Try to boot into the safe mode and access the HDD.
Try accessing the HDD in Windows 7 Ultimate and Home Premium.
You will be able to access the drive, as chkdsk command would have deleted the files that were causing the problem.
In order to resolve this issue, go to the Windows Explorer and follow the below mentioned steps.
Right-click on the inaccessible hard drive.
Click Properties.
Go to the Security tab, and then click Advanced.
Click ‘Edit’ by going to the Owner tab.
Modify the ownership of the desired account.
Alternatively, you can also try the following option, if you are not able to access any file or folder on a Windows 7 drive.
Right-click on the inaccessible file or folder.
Click Properties.
Select the Security tab.
Click your name Under Group or user name. This would show you the permissions you have to access the file and folder.
You can also try the following method
Change the drive letter for inaccessible hard drive.
Run the following command: chkdsk /r I:
Try to boot into the safe mode and access the HDD.
Try accessing the HDD in Windows 7 Ultimate and Home Premium.
You will be able to access the drive, as chkdsk command would have deleted the files that were causing the problem.
ORA-29264 unknown or unsupported URL scheme
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-29264: unknown or unsupported URL scheme
Error: ORA-29264 (ORA-29264)
Text: unknown or unsupported URL scheme
---------------------------------------------------------------------------
Cause: The URL scheme was unknown or unsupported.
Action: Check the URL to make sure that the scheme is valid and
supported.
Please remove any leading or trailing characters (space, enter etc) in the profile's value.
The code works fine when
i use
http://www.***.com/image?imageid=1234 worked
but when i use
http://www.finance.***.com/image?imageid=1234
I fails with the specified error.
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-29264: unknown or unsupported URL scheme
Error: ORA-29264 (ORA-29264)
Text: unknown or unsupported URL scheme
---------------------------------------------------------------------------
Cause: The URL scheme was unknown or unsupported.
Action: Check the URL to make sure that the scheme is valid and
supported.
Please remove any leading or trailing characters (space, enter etc) in the profile's value.
The code works fine when
i use
http://www.***.com/image?imageid=1234 worked
but when i use
http://www.finance.***.com/image?imageid=1234
I fails with the specified error.
ORA-29273 ORA-06512 ORA-29259
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-29259: end-of-input reached
ORA-06512: at line 1
Error: ORA-29259 (ORA-29259)
Text: end-of-input reached
---------------------------------------------------------------------------
Cause: The end of the input was reached.
Action: If the end of the input is reached prematurely, check if the
input source terminates prematurely. Otherwise, close the
connection to the input.
Please remove any leading or trailing characters (space, enter etc) in the value.
The issue was with proxy setting.
Once the UTL_HTTP.SET_PROXY was set appropriately, I no longer get the end-of-input error.
URL is not properly set
CAUSE
The site that is accessed via utl_http requires mutual authentication.
SOLUTION
This is not possible in 10g . This was implemented in 11g only.
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'LD_AD_IE_SPAN_NSEF_BPL-4' ORDER BY 2 DESC;
SELECT UTL_HTTP.REQUEST ('http://172.168.1.104') FROM DUAL;
SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.domains('172.168.1.104'));
select utl_http.request('https://marc.charpentier.com/ws/services/Payment?wsdl',NULL,'file:E:\wallet','******') from dual;
recreate ACL privileges
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-29259: end-of-input reached
ORA-06512: at line 1
Error: ORA-29259 (ORA-29259)
Text: end-of-input reached
---------------------------------------------------------------------------
Cause: The end of the input was reached.
Action: If the end of the input is reached prematurely, check if the
input source terminates prematurely. Otherwise, close the
connection to the input.
Please remove any leading or trailing characters (space, enter etc) in the value.
The issue was with proxy setting.
Once the UTL_HTTP.SET_PROXY was set appropriately, I no longer get the end-of-input error.
URL is not properly set
CAUSE
The site that is accessed via utl_http requires mutual authentication.
SOLUTION
This is not possible in 10g . This was implemented in 11g only.
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'LD_AD_IE_SPAN_NSEF_BPL-4' ORDER BY 2 DESC;
SELECT UTL_HTTP.REQUEST ('http://172.168.1.104') FROM DUAL;
SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.domains('172.168.1.104'));
select utl_http.request('https://marc.charpentier.com/ws/services/Payment?wsdl',NULL,'file:E:\wallet','******') from dual;
recreate ACL privileges
TNSaddress already in use ora12542
1)
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
Determines the time that must elapse before TCP can release a closed connection and reuse its resources. This interval between closure and release is known as the TIME_WAIT state or 2MSL state. During this time, the connection can be reopened at much less cost to the client and server than establishing a new connection.Value Name: MaxUserPort
Type: REG_DWORD
Value: 65534
2)
TcpTimedWaitDelay
Value Type: REG_DWORD
Value: 30
3)
netsh interface tcp show global
netsh int tcp set global autotuninglevel=disabled
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
Determines the time that must elapse before TCP can release a closed connection and reuse its resources. This interval between closure and release is known as the TIME_WAIT state or 2MSL state. During this time, the connection can be reopened at much less cost to the client and server than establishing a new connection.Value Name: MaxUserPort
Type: REG_DWORD
Value: 65534
2)
TcpTimedWaitDelay
Value Type: REG_DWORD
Value: 30
3)
netsh interface tcp show global
netsh int tcp set global autotuninglevel=disabled
ORA-08104 this index object 4145783 is being online built or rebuilt
*
ERROR at line 1:
ORA-20000: this index object "LDBO"."IDXCONTRACTBILL" is being online built or
rebuilt
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1
SQL>
SQL>
SQL>
SQL> E
this index object is being online built or rebuilt
Cause:
A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is on going when in fact it is not.
Solution:
The dbms_repair.online_index_clean function has been created to cleanup online index rebuilds. More details about the function on metalink – Session Was Killed During The Rebuild Of Index ORA-08104 [ID 375856.1]
Run the following to resolve:
declare
isclean boolean;
begin
isclean :=false;
while isclean=false
loop
isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
dbms_lock.sleep(10);
end loop;
end;
/
select min(object_id) from dba_objects where object_name = 'IDXCONTRACTBILL'
392745
declare
ret boolean;
begin
ret:=dbms_repair.ONLINE_INDEX_CLEAN(392745);
end;
/
exit
drop index LDBO.IDXCONTRACTBILL;
CREATE INDEX LDBO.IDXCONTRACTBILL ON LDBO.CONTRACTDETAILS (FIRMNUMBER,NFINANCIALYEAR,CONTRACT);
ALTER INDEX LDBO.IDXCONTRACTBILL REBUILD ONLINE;
ALTER INDEX LDBO.IDXCONTRACTINDEX REBUILD ONLINE;
exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'LDBO' , tabname => 'CONTRACTDETAILS',cascade => true, estimate_percent => 100,method_opt=>'for all columns size 254', granularity => 'ALL', degree => DBMS_STATS.DEFAULT_DEGREE, no_invalidate => false, force=> TRUE );
ALTER INDEX LDBO.IDXCONTRACTBILL REBUILD ONLINE pctfree 20;
ERROR at line 1:
ORA-20000: this index object "LDBO"."IDXCONTRACTBILL" is being online built or
rebuilt
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1
SQL>
SQL>
SQL>
SQL> E
this index object is being online built or rebuilt
Cause:
A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is on going when in fact it is not.
Solution:
The dbms_repair.online_index_clean function has been created to cleanup online index rebuilds. More details about the function on metalink – Session Was Killed During The Rebuild Of Index ORA-08104 [ID 375856.1]
Run the following to resolve:
declare
isclean boolean;
begin
isclean :=false;
while isclean=false
loop
isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
dbms_lock.sleep(10);
end loop;
end;
/
select min(object_id) from dba_objects where object_name = 'IDXCONTRACTBILL'
392745
declare
ret boolean;
begin
ret:=dbms_repair.ONLINE_INDEX_CLEAN(392745);
end;
/
exit
drop index LDBO.IDXCONTRACTBILL;
CREATE INDEX LDBO.IDXCONTRACTBILL ON LDBO.CONTRACTDETAILS (FIRMNUMBER,NFINANCIALYEAR,CONTRACT);
ALTER INDEX LDBO.IDXCONTRACTBILL REBUILD ONLINE;
ALTER INDEX LDBO.IDXCONTRACTINDEX REBUILD ONLINE;
exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'LDBO' , tabname => 'CONTRACTDETAILS',cascade => true, estimate_percent => 100,method_opt=>'for all columns size 254', granularity => 'ALL', degree => DBMS_STATS.DEFAULT_DEGREE, no_invalidate => false, force=> TRUE );
ALTER INDEX LDBO.IDXCONTRACTBILL REBUILD ONLINE pctfree 20;
TNS-12560 TNS protocol adapter error opiodr aborting process unknown ospid
Fatal NI connect error 12560, connecting to:
(LOCAL=NO)
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.3.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.3.0 - Production
Time: 31-JAN-2015 15:52:27
Tracing not turned on.
Tns error struct:
ns main err code: 12560
TNS-12560: TNS:protocol adapter error
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
opiodr aborting process unknown ospid (11852) as a result of ORA-609
The Solution
Change the user account that the listener is running as to the same as the one that the database service runs as, restart the listener and this should resolve the issue.
dim-00019 create service error
set ORACLE_SID=orcl
oradim -new -sid ORCL -startmode manual -spfile
Enter password for Oracle service user: <PASSWORD IS ENTERED>
DIM-00019: create service error
O/S-Error: (OS 87) The parameter is incorrect.
You said "Even after deleting the service"
How have you been deleting the service ?
Check out if your service still exists at the registry level
1. regedit
2. go towards HKEY_LOCAL_MACHINE > SYSTEM > CURRENT CONTROL SET > SERVICES > and check the Oracle services.
If your service is still present > delete your service at this level
As a last resort you might want to reboot your server (if possible)
C:\>oradim -delete -sid cs62
C:\>oradim -new -sid cs62
C:\>set oracle_sid=cs62
C:\>sqlplus /nolog
oradim -new -sid orcl -intpwd orcl -startmode manual -pfile 'F:\oracle10g\pr
oduct\10.2.0\db_1\database\initorcl.ora'
Instance created.
DIM-00019: create service error
O/S-Error: (OS 2) The system cannot find the file specified.
Solution of the Problem
On linux environment you don't need these things. But in windows environment you need to do a lot of things more than restoring files. Whenever a windows OS gets corrupted you loose the Oracle Universal Installer repository and the regedit entries along with oracle home structure. So to solve the problem it is better to install new oracle software in your windows machine without any database and then create oracle instance and service using oradim.
oradim -new -sid ORCL -startmode manual -spfile
Enter password for Oracle service user: <PASSWORD IS ENTERED>
DIM-00019: create service error
O/S-Error: (OS 87) The parameter is incorrect.
You said "Even after deleting the service"
How have you been deleting the service ?
Check out if your service still exists at the registry level
1. regedit
2. go towards HKEY_LOCAL_MACHINE > SYSTEM > CURRENT CONTROL SET > SERVICES > and check the Oracle services.
If your service is still present > delete your service at this level
As a last resort you might want to reboot your server (if possible)
C:\>oradim -delete -sid cs62
C:\>oradim -new -sid cs62
C:\>set oracle_sid=cs62
C:\>sqlplus /nolog
oradim -new -sid orcl -intpwd orcl -startmode manual -pfile 'F:\oracle10g\pr
oduct\10.2.0\db_1\database\initorcl.ora'
Instance created.
DIM-00019: create service error
O/S-Error: (OS 2) The system cannot find the file specified.
Solution of the Problem
On linux environment you don't need these things. But in windows environment you need to do a lot of things more than restoring files. Whenever a windows OS gets corrupted you loose the Oracle Universal Installer repository and the regedit entries along with oracle home structure. So to solve the problem it is better to install new oracle software in your windows machine without any database and then create oracle instance and service using oradim.
LD Error building key for index
Error building key for index "c:\users\suprim~1.moh\appdata\local\temp\00004ftl001q.cdx" tag "Clrisk".
cursor_sharing should be exact
cursor_sharing should be exact
ORA 01950 no privileges on tablespace
Saved
Record Not Saved to DataBase ORA-01950: no privileges on tablespace 'USR'
ORA-06512: at "LDBO.SP_SCHEDULERSETUP", line 735
sb17271112
E:\ldoutput
SYS@FWS1213> grant unlimited tablespace to ldbo;
Grant succeeded.
SYS@FWS1213> ALTER USER LDBO QUOTA UNLIMITED on USR;
User altered.
SYS@FWS1213>
SYS@FWS1213> ALTER USER LDBO QUOTA UNLIMITED on INDX;
ALTER USER LDBO QUOTA UNLIMITED on USR;
ALTER USER LDBO QUOTA UNLIMITED on INDX;
grant unlimited tablespace TO LDBO;
Select Tablespace_Name, Username, Bytes / 1024 / 1024/ 1024 "Used GB", Max_Bytes / 1024 / 1024/ 1024 As "Max GB" from dba_ts_quotas;
select file_name,tablespace_name,bytes/1024/1024 "Size MB",status,autoextensible,maxbytes/1024/1024 "MaxSize MB",Increment_By from dba_data_files;
Record Not Saved to DataBase ORA-01950: no privileges on tablespace 'USR'
ORA-06512: at "LDBO.SP_SCHEDULERSETUP", line 735
sb17271112
E:\ldoutput
SYS@FWS1213> grant unlimited tablespace to ldbo;
Grant succeeded.
SYS@FWS1213> ALTER USER LDBO QUOTA UNLIMITED on USR;
User altered.
SYS@FWS1213>
SYS@FWS1213> ALTER USER LDBO QUOTA UNLIMITED on INDX;
ALTER USER LDBO QUOTA UNLIMITED on USR;
ALTER USER LDBO QUOTA UNLIMITED on INDX;
grant unlimited tablespace TO LDBO;
Select Tablespace_Name, Username, Bytes / 1024 / 1024/ 1024 "Used GB", Max_Bytes / 1024 / 1024/ 1024 As "Max GB" from dba_ts_quotas;
select file_name,tablespace_name,bytes/1024/1024 "Size MB",status,autoextensible,maxbytes/1024/1024 "MaxSize MB",Increment_By from dba_data_files;
TROUBLESHOOTING ORA-03135 connection lost contact from ODP.NET connections with connection pooling enabled
SYMPTOMS
ODP.NET applications that have connection pooling enabled (which is the default) may intermittently experience the following error:
ORA-03135: connection lost contact
CAUSE
The nature of connection pooling is that there are frequently long lived idle connections, which may end up being disconnected by 3rd party software such as firewalls and load balancers.
Typically the exception occurs when trying to use a connection via a DataReader, DataAdapter, OracleCommand, etc, rather than during the OracleConnection::Open call.
For ease of troubleshooting, often the behavior can be reproduced from a SQLPlus session if left idle long enough.
SOLUTION
The ideal solution is to address the issue in the environment to prevent the disconnect from occurring, however several workarounds can be employed:
1) Simply catch the exception, and retry the operation.
2) Use the ODP.NET connection string parameter "validate connection=true" By default, it is set to false, which means that no checking of the connection is done when it is retrieved from the pool as a result of a OracleConnection::Open call. Setting it to true will cause ODP.NET to verify that the connection is still good by making a database round trip. If a problem with the connection is found, it is removed from the pool, and another connection is tried internally.
Note that while this typically alleviates ora-3135, it does have performance implications as every con.Open call will result in a round trip to the database. The overhead may or may not be significant depending upon application performance, so testing should be done in your environment.
3) Enable Dead Connection Detection on the database, which will result in a probe packet being sent from database to client periodically, which will usually prevent the firewall or load balancer from seeing the connection as idle.
Note 151972.1 Dead Connection Detection (DCD) Explained
4) Connection pooling can be disabled entirely, but this will likely have a much larger performance impact. To disable connection pooling, add "pooling=false" to the connection string.
ODP.NET applications that have connection pooling enabled (which is the default) may intermittently experience the following error:
ORA-03135: connection lost contact
CAUSE
The nature of connection pooling is that there are frequently long lived idle connections, which may end up being disconnected by 3rd party software such as firewalls and load balancers.
Typically the exception occurs when trying to use a connection via a DataReader, DataAdapter, OracleCommand, etc, rather than during the OracleConnection::Open call.
For ease of troubleshooting, often the behavior can be reproduced from a SQLPlus session if left idle long enough.
SOLUTION
The ideal solution is to address the issue in the environment to prevent the disconnect from occurring, however several workarounds can be employed:
1) Simply catch the exception, and retry the operation.
2) Use the ODP.NET connection string parameter "validate connection=true" By default, it is set to false, which means that no checking of the connection is done when it is retrieved from the pool as a result of a OracleConnection::Open call. Setting it to true will cause ODP.NET to verify that the connection is still good by making a database round trip. If a problem with the connection is found, it is removed from the pool, and another connection is tried internally.
Note that while this typically alleviates ora-3135, it does have performance implications as every con.Open call will result in a round trip to the database. The overhead may or may not be significant depending upon application performance, so testing should be done in your environment.
3) Enable Dead Connection Detection on the database, which will result in a probe packet being sent from database to client periodically, which will usually prevent the firewall or load balancer from seeing the connection as idle.
Note 151972.1 Dead Connection Detection (DCD) Explained
4) Connection pooling can be disabled entirely, but this will likely have a much larger performance impact. To disable connection pooling, add "pooling=false" to the connection string.
Table statistics being locked after import export
Table statistics get locked when exporting only the table structures with DataPump. This situation is identified as an issue that occurs with Oracle 10.2. Using DataPump data is not exported or imported if the option CONTENT = METADATA_ONLY is set.
To resolve this there are two options listed on My Oracle Support.
1. After the import unlock the statistics for tables using the command:
execute DBMS_STATS.UNLOCK_TABLE_STATS('owner','table_name');
NOTE the statistics can also be unlocked at the schema level.
2. Do not import table statistics using the option EXCLUDE=TABLE_STATISTICS.
To resolve this there are two options listed on My Oracle Support.
1. After the import unlock the statistics for tables using the command:
execute DBMS_STATS.UNLOCK_TABLE_STATS('owner','table_name');
NOTE the statistics can also be unlocked at the schema level.
2. Do not import table statistics using the option EXCLUDE=TABLE_STATISTICS.
ORA-08103 object no longer exists
check alert log
block corruption
analyze validate structure
Doc ID 8103.1
You can try to drop and rebuild the index(es) and see whether it solves the problem.
UNDO_RETENTION
ORA-08103 object no longer exists
Cause: The object has been deleted by another user since the operation began. Or a prior incomplete recovery restored the database to a point in time during the deletion of the object
Action: Remove references to the object or delete the object if this is the result of an incomplete recovery.
http://www.dba-oracle.com/t_ora_08103_object_no_longer_exists.htm
There are several likely reasons for this error, and you should always check your alert log for details. This error often requires opening a service request with MOSC:
0: A software bug (see list below).
1: You are not signed-on as the table owner.
2: Database corruption of a header block.
3: Accidental delete of the target table. (check the recyclebin)
4: Data file I/O error (check alert log)
5: corruption in UNDO log (drop and re-create)
6. An index is disabled or is offline.
Possible solution include:
1: Set db_file_multiblock_read_count to 1.
2: Run dbv on all data files.
3: Run catalog.sql, catproc.sql and utlrp.sql to ensure no data dictionary corruption.
4: Purge recyclebin & dba_recyclebin.
5: When the ORA-08103 is on an index operation, place the index online or make index usable
select index_name , status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
IDX_CUST UNUSABLE
alter index idx_cust rebuild online;
Subscribe to:
Posts (Atom)