Configuring an Oracle database

This task determines whether your Oracle database is using a character set that is compatible with the ISO Latin-1 character set.

Complete this task only once, on any computer with a database client that connects to the Oracle database server.

To configure an Oracle database:

1. Check the database configuration from within an SQLPLUS session.
   1. To determine which character set is currently in use, run the following command:

      select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

   2. Depending on the output from step 1a, take the action indicated in the following table.

   Output	Description and Action
   VALUE ------------------------ WE8ISO8859P1	The current character set is compatible with the ISO Latin-1 character set. No changes are necessary. Skip to 1d.
   VALUE ------------------------ US7ASCII	The current character set is not compatible with the ISO Latin-1 character set, but can be changed. Skip to 1d.
   any other output	The current character is not compatible. Skip to 1c.

   1. To verify that character set conversion is possible without data loss, run the Oracle Scanner utility (csscan.exe in the Oracle binaries directory).

      This utility simulates the new character sets and generates a report or user objects that would be affected if you changed the character set. If the report indicates no problems, continue with the following steps. If problems are listed in the report, contact BMC Customer Support.

   2. Check the sort order:

      select * from nls_database_parameters where parameter in ('NLS_COMP','NLS_SORT');

      The output should resemble the following sample and indicate the binary sort order:

      PARAMETER VALUE
      -------------------------- -----------
      NLS_SORT BINARY
      NLS_COMP                   BINARY

      If the sort order is not binary, contact BMC Customer Support. Do not continue customizing Western European language support.

2. If your current character is set to compatible with ISO Latin-1 and uses binary sort, skip the remainder of this task. Otherwise, complete this step:
   1. Shut down all Control-M/EM and Control-M/Server components and applications that might connect to the database server.
   2. Export the data by running the command appropriate for the application (as a safety precaution):

   Application / component	Command
   Control-M/EM	UNIX: emutil -U user -P password -export -type all -file exportFile Microsoft Windows: util -U user -P password -export -type all -file exportFile
   Control-M/Server	ctm_backup_bcp

3. Log on to the SQLPLUS application with administrator privileges:

   sqlplus /nolog

   connect / as sysdba

4. Shut down the database:

   SHUTDOWN IMMEDIATE;

5. Configure the database character set:

   STARTUP MOUNT

   ALTER SYSTEM ENABLE RESTRICTED SESSION;

   ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

   ALTER SYSTEM SET AQ_TM_PROCESSES=0;

   ALTER DATABASE OPEN;

   ALTER DATABASE CHARACTER SET INTERNAL_USE WE8ISO8859P1;

6. Shut down and restart the database:

   SHUTDOWN IMMEDIATE

   STARTUP;

7. Restart all components that you shut down.