Unable to Connect Oracle Database Server from Oracle Client - Change Oracle Database Character Set

Unable to Connect Oracle Database Server from Oracle Client - Change Oracle Database Character Set

Applies to:

            Oracle Enterprise Linux – Version: 5

            Oracle EBS R12

           

Description:

I have installed a new VISION instance on Oracle Linux 5. All is going well, all configuration and setup completed successfully. All services of Database and Application started successfully. Application is also working fine from client side.

I have a problem to connect database from client side. TNSNAMES.ORA file also configured on client side but unable to connect SQLPLUS.

SQLPLUS session going to hang/close when trying to connect:

CMD> Sqlplus apps/apps@VIS

Solution:

After some research I found that the character set of Database is default to “AL16UTF16” which create the problem to connect the database from a windows client.

So that to fix the problem, change the character set from “AL16UTF16” to “WE8MSWIN1252”

To fix the problem do the following steps:

1.    Check the current Character Set of Database execute the following query from sysdba

SQL> select value$ from sys.props$ where name = ‘NLS_CHARACTERSET’;

In my case above query return “AL16UTF16”

2.    To change the Database character set do the following queries:

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter system enable restricted session;

SQL> alter system set JOB_QUEUE_PROCESSES=0;

SQL> alter system set AQ_TM_PROCESSES=0;

SQL> alter database open;

SQL> alter database CHARACTER SET INTERNAL_USE WE8MSWIN1252;

SQL> shutdown;

SQL> startup restrict;

SQL> shutdown;

SQL> startup;

SQL> alter system set JOB_QUEUE_PROCESSES=1000;

SQL> alter system set AQ_TM_PROCESSES=1;

3.    Re-start the Database and Listener

4.    Now able to connect Oracle Database from Windows Client

Your comments, especially which will help us improve the functionality, will be greatly appreciatedJ