Troubleshoot ORA-12547: TNS: lost contact

Problem Description
Whenever you connect to database as sysdba it fails with ORA-12517 like below.

$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Sun Aug 01 11:27:27 2010
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
ERROR:
ORA-12547: TNS:lost contact
Enter user-name:

Cause of the Problem
ORA-12547 indicates that the communication channel has been broken. Based on the scenarios cause and solution of the problem is discussed below.

1)Local connection as sysdba (Bequeath):
Problem
BEQ connection fails when connecting with / as sysdba

Cause
Oracle binaries have not been linked correctly

Solution
Relink the Oracle binaries by executing the following command,

$ ORACLE_HOME/bin relink all

2)Bequeath connections fail with ora-12547 and Remote connections fail with ORA-12500:

From the listener.log file it shows,

TNS-12500: TNS:listener failed to start a dedicated server process 
TNS-12547: TNS:lost contact 
TNS-12560: TNS:protocol adapter error 
TNS-00517: Lost contact 

The version of the database server is 9.2.0.2

Cause
This is oracle bug 2654576 and happened due to the processes value reaches a limit.

Solution
i) The bug is fixed in patch set 9.2.0.3 and in 10g. So upgrade oracle database is a solution.

ii) Increase the PROCESSES parameter in the init.ora to a higher value for example 2000.

iii) If increasing the PROCESSES parameter does not help then increase the value of the hidden parameter.
_attach_count_slack = 2000

3) Remote connections to the database server fail with ORA-12547
Cause 1
SQLNET.INBOUND_CONNECT_TIMEOUT and/or INBOUND_CONNECT_TIMEOUT_listener_name is set in the database server's sqlnet.ora and listener.ora. If the client fails to establish a connection and complete authentication in the time specified defined by these parameter, then the database server terminates the connection.

If this is the case and your oracle database version is 10g and higher then ORA-3136 errors is reported in the alert.log.

Cause 2
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES list does not have the IP address of the failing client.
Alternatively the TCP.EXCLUDED_NODES list contain the IP address of the failing client.

Solution 1
If problem happened due to cause 1 then SQLNET.INBOUND_CONNECT_TIMEOUT and/or INBOUND_CONNECT_TIMEOUT_listener_name to appropriate values.

Note that the database server and the listener has to be restarted for these parameters to take effect.

Solution 2
For cause 2 either add the IP address of the failing client in the TCP.INVITED_NODES list or remove it from the TCP.EXCLUDED_NODES list.

It is recommended to restart the database server and the listener for these parameters to take effect.

4) Listener fails to start with Oracle error ORA-12547
Cause
TCP.VALIDNODE_CHECKING is active on the database server and the TCP.INVITED_NODES list does not have the IP address of the failing client.
Alternatively the TCP.EXCLUDED_NODES list contain the IP address of the failing client.

Solution
Either add the IP address of the failing client in the TCP.INVITED_NODES list or remove it from the TCP.EXCLUDED_NODES list.
It is recommended to restart the database server and the listener for these parameters to take effect.

5) After changing /etc/system and rebooting system, the 10g R2 TNS Listener fails to start

$ lsnrctl start 
Starting /u01/oracle/product/10.2.0/Db_1/bin/tnslsnr: please wait... 
TNSLSNR for Solaris: Version 10.2.0.1.0 - Production 
System parameter file is /u01/oracle/product/10.2.0/Db_1/network/admin/listener.ora 
Log messages written to /u01/oracle/product/10.2.0/Db_1/network/log/listener.log 
Trace information written to /u01/oracle/product/10.2.0/Db_1/network/trace/listener.trc 
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server11)(PORT=1521))) 
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc10))) 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server11)(PORT=1521))) 
TNS-12547: TNS:lost contact 
TNS-12560: TNS:protocol adapter error 
TNS-00517: Lost contact 
Solaris Error: 131: Connection reset by peer 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=extproc10))) 
TNS-12541: TNS:no listener 
TNS-12560: TNS:protocol adapter error 
TNS-00511: No listener 
Solaris Error: 146: Connection refused 

Cause
This one is happened due to a DNS client process, 'BIND 9' not running/started on the Solaris 10 Server.

Solution
You need to start the BIND 9 DNS Client on the Solaris10 Server.

RMAN duplicate fails with RMAN-10006, ORA-03113, ORA-01092

Problem Description
While duplicating database using RMAN it fails with ORA-01092: "ORACLE instance terminated. Disconnection forced" and ora-03113 error reported.

RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10039: error encountered while polling for RPC completion on channel clone_default
RMAN-10006: error running SQL statement: select act from x$ksusex where sid=:1 and serial=:2
RMAN-10002: ORACLE error: ORA-03113: end-of-file on communication channel
RMAN-03002: failure of Duplicate Db command at 02/30/2010 01:21:18
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: 
ORA-01092: ORACLE instance terminated. Disconnection forced

If we look for the auxiliary database alert.log file then it shows that Undo Tablespace 'UNDOTBS1' does not exist or of wrong type.

Cause of the Problem
The UNDO_TABLESPACE parameter of auxiliary database is not same as target database. In the source database undo tablespace is set to UNDOTBS2 while in target database undo tablespace is set to UNDOTBS1. The undo tablespace specified in the auxiliary database parameter file does not exist.

Solution of the Problem
Make sure that the undo tablespace for auxiliary database is same as of target database.
Edit the init.ora or alter the UNDO_TABLESPACE parameter of the auxiliary instance and set
undo_tablespace = UNDOTBS2
- restart the auxiliary instance in nomount mode and repeat the duplicate database command.

RMAN duplicate database fails with RMAN-00601, RMAN-10006, ORA-00704, ORA-39700

Problem Description
Duplicating database using RMAN fails with following errors.

channel channel1: starting piece 1 at 29-JUL-2010 03:49:19
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10039: error encountered while polling for RPC completion on channel channel1
RMAN-10006: error running SQL statement: select action from gv$session where sid=:1 and serial#=:2 and inst_id=:3 
RMAN-10002: ORACLE error: ORA-3114: not connected to ORACLE 
RMAN-10041: Could not re-create polling channel context following failure. 
RMAN-10024: error setting up for rpc polling 
RMAN-10005: error opening cursor 
RMAN-10002: ORACLE error: ORA-3114: not connected to ORACLE 
RMAN-03002: failure of Duplicate Db command at 02/23/2007 11:11:50 
RMAN-03015: error occurred in stored script Memory Script 
RMAN-06136: ORACLE error from auxiliary database: ORA-1092: ORACLE instance 
terminated. Disconnection forced 
ORA-00704: bootstrap process failure 
ORA-39700: database must be opened with UPGRADE option

Cause of the Problem
The errors occurred because the Oracle versions for both the target database home and the auxiliary database home are not same.

Solution of the Problem
The solution is to upgrade the auxiliary database server binaries to match the target database server. Also if there is one off patches that exist on the target database, you also need to apply on the auxiliary database in order to make the duplication work.

ORA-14080: partition cannot be split along the specified high bound

Problem Scenarios

SQL>    CREATE TABLE USER_ACTIVITY
  2     (
  3     COL1 NUMBER NOT NULL,
  4     COL2 VARCHAR2(10) NULL,
  5     COL3 NUMBER NOT NULL
  6     )
  7     TABLESPACE USERS
  8     PARTITION BY RANGE (COL3)
  9     (
 10      PARTITION P10 VALUES LESS THAN (100),
 11      PARTITION P20 VALUES LESS THAN (200),
 12      PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
 13      );

Table created.

SQL> ALTER TABLE USER_ACTIVITY SPLIT PARTITION
  2      P_MAX AT (200)
  3      INTO
  4      (
  5      PARTITION P30,
  6      PARTITION MAXPART
  7      );
    P_MAX AT (200)
    *
ERROR at line 2:
ORA-14080: partition cannot be split along the specified high bound

Cause of the Problem
There is already a partition with a specified high bound of 200. You need to split the partition on a value higher than the next range down but lower that the next range up.

Solution of the Problem
The solution is to split the partition into appropriate bound. The following statement will split the partition at value 250. From the two, the first partition (P30) holding values 200
to 249 and the second partition (MAXPART) holding values 250 and above.

SQL> ALTER TABLE USER_ACTIVITY SPLIT PARTITION
  2      P_MAX AT (250)
  3      INTO
  4      (
  5      PARTITION P30,
  6      PARTITION MAXPART
  7      );

Table altered.

Export fails with ORA-01406: fetched column value was truncated

Problem Description
Exporting oracle 11g database using 10g exp utility fails with ORA-01406 errors.

E:\>exp exp_user/exp_user full=y file=exp_dump.dmp log=exp_dump.log

Export: Release 10.2.0.1.0 - Production on Thu Jul 22 23:01:49 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
EXP-00008: ORACLE error 1406 encountered
ORA-01406: fetched column value was truncated
EXP-00000: Export terminated unsuccessfully

By further investigation it is noticed that the statement that fails with ORA-01406 is:
SELECT GRANTEE, PRIV, WGO FROM SYS.EXU8SPV ORDER BY SEQUENCE

And the database character set is WE8MSWIN1252.

SQL> select property_value from database_properties where property_name='NLS_CHARACTERSET';

PROPERTY_VALUE
--------------------------------------------------------------------------------
WE8MSWIN1252

The same problem arises whenever you connect to 11g database using oracle 9i or oracle 10gR1 exp client utility.

Cause of the Problem
The cause of this problem has been identified as Oracle Bug 6804150 and fixed in 11g and 10.2.0.5.

It is caused by the new privilege name "ADMINISTER SQL MANAGEMENT OBJECT" added in 11g.
Since the value exceeds 30 characters, then truncation happens and the error ora-01406 is encountered.

Solution of the Problem
Solution 01:
Change the Database character set to AL32UTF8 will solve this problem.

Solution 02:
Apply Patch for BUG 6804150 if available for your platform.

Solution 03:
Install the Oracle 10.2.0.5 patchset.

Solution 04:
Use 11g exp client utility.

exp fails with EXP-00023 and expdp fails with ORA-31631, ORA-39161

Problem Description
It is needed to export/import full database. To export full database, "EXPORT FULL DATABASE" privilege is granted and to import full database "IMPORT FULL DATABASE" privilege is granted. Now while doing full database export/import, exp fails with EXP-00023 and expdp fails with
ORA-31631: privileges are required
ORA-39161: Full database jobs require privileges

In the following example the error case is demonstrated.

A user named exp_user is created and it is granted EXPORT FULL DATABASE privilege.

E:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 22 19:35:15 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user exp_user identified by exp_user;

User created.

SQL> grant create session, resource, export full database to exp_user;

Grant succeeded.

SQL> select * from dba_sys_privs where grantee='EXP_USER';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
EXP_USER                       EXPORT FULL DATABASE                     NO
EXP_USER                       CREATE SESSION                           NO
EXP_USER                       UNLIMITED TABLESPACE                     NO

SQL> select * from dba_role_privs where grantee='EXP_USER';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
EXP_USER                       RESOURCE                       NO  YES

E:\>exp exp_user/exp_user full=y file=exp_dump.dmp log=exp_dump.log

Export: Release 10.2.0.1.0 - Production on Thu Jul 22 19:43:52 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00023: must be a DBA to do Full Database or Tablespace export
(2)U(sers), or (3)T(ables): (2)U >

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user EXP_USER
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user EXP_USER
About to export EXP_USER's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export EXP_USER's tables via Conventional Path ...
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

E:\>expdp exp_user/exp_user full=y dumpfile=exp_dump.dmp logfile=exp_dump.log

Export: Release 10.2.0.1.0 - Production on Thursday, 22 July, 2010 19:51:21

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39161: Full database jobs require privileges

From the above example, we see both exp and expdp operations have failed.

Cause of the Problem
The system privileges "EXPORT FULL DATABASE" and "IMPORT FULL DATABASE" was introduced in oracle database 10gR1. But these two privileges are not currently in use by oracle. May be they will be implemented in future releases. In oracle 10g and 11g these two system privileges are not operational and hence assigning these privileges will do nothing.

The right privileges used by export/import are the roles EXP_FULL_DATABASE/ IMP_FULL_DATABASE.

Solution of the Problem
Assign correct privileges to the user. He who will do full database export operation assign him EXP_FULL_DATABASE role and he who will perform full database import operation assign him IMP_FULL_DATABASE role.

E:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 22 19:53:32 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> revoke export full database from exp_user;

Revoke succeeded.

SQL> grant exp_full_database to exp_user;

Grant succeeded.

SQL> select * from dba_sys_privs where grantee = 'EXP_USER';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
EXP_USER                       CREATE SESSION                           NO
EXP_USER                       UNLIMITED TABLESPACE                     NO

SQL> select * from dba_role_privs where grantee = 'EXP_USER';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
EXP_USER                       EXP_FULL_DATABASE              NO  YES
EXP_USER                       RESOURCE                       NO  YES

Now invoking exp and expdp with full=y went fine.

D:\>exp exp_user/exp_user full=y file=exp_dump.dmp log=exp_dump.log

Export: Release 11.1.0.6.0 - Production on Thu Jul 22 21:19:03 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
.
.
.

How to uninstall Oracle 11g

Uninstall Oracle software 11.2g
In 11.2g there is deinstall command through which you can uninstall standalone Oracle Database installations, Oracle Clusterware and Automatic Storage Management (ASM) from your server, as well as Oracle Real Application Clusters (Oracle RAC) and Oracle Database client installations. You can find out deinstall tool under $ORACLE_HOME/deinstall folder.

If you don't have deinstall tool you can download it from url http://www.oracle.com/technology/software/products/database/index.html

- Under Oracle Database 11g Release 2, click See All for the respective platform for which you want to download the Deinstallation Tool.

- The Deinstallation Tool is available for download at the end of the page.

So, in 11.2g just use,
$$ORACLE_HOME/deinstall/deinstall

Uninstall Oracle software in 11.1g
If you are running Oracle 11.1g then you have to open the Oracle Universal Installer on windows and runInstaller on unix. In windoes you can find oui under $ORACLE_HOME/oui/bin and file name is setup.exe.