We're here to help!

Notes on Language Settings - (NLS Settings)

Follow
Description

When creating a standby database, there are a number of important factors to take into account:

  1. Operating systems should match.
  2. Oracle database software version and patch level should match.
  3. Dbvisit Standby software installed on both primary and standby must match.

Another component to keep in mind is the Language settings being used. Again the core requirement for a standby (DR) site is that it should match the primary. So when you are using a Primary system using German settings with default currency set to Euro, you need to make sure that the standby server settings match. Oracle NLS settings can get complex. It is recommended that you review the relevant Oracle Documentation. For more details please see the following Oracle Support Notes:

  • The Correct NLS_LANG in a Microsoft Windows Environment (Doc ID 179133.1)
  • The Correct NLS_LANG Setting in Unix Environments (Doc ID 264157.1)
  • Changing Or Choosing the Database Character Set ( NLS_CHARACTERSET ) (Doc ID 225912.1)
Example

Below is an example of what might happen when the wrong settings are in place. In this case the primary database server has the following environment settings with regards to NLS parameters:

NLS_DATE_FORMAT=dd/mm/yyyy:hh24:mi:ss
NLS_LANG=dutch_the netherlands.AL32UTF8

This example, from a basic test system, has the following database parameters:

audit_file_dest='/u01/app/oracle/admin/QA1/adump'
compatible='12.1.0.1.0'
control_files='/u01/app/oracle/oradata/QA1/control01.ctl'
db_block_size=8192
db_domain=''
db_name='QA1'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=3221225472
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=QA1XDB)'
log_archive_format='%t_%s_%r.dbf'
nls_calendar='GREGORIAN'
nls_comp='BINARY'
nls_currency='€'
nls_date_format='YYYY-MM-DD:HH24:MI:SS'
nls_date_language='DUTCH'
nls_dual_currency='€'
nls_iso_currency='THE NETHERLANDS'
nls_language='DUTCH'
nls_numeric_characters=',.'
nls_sort='DUTCH'
nls_territory='THE NETHERLANDS'
nls_time_format='HH24:MI:SSXFF'
nls_time_tz_format='HH24:MI:SSXFF TZR'
nls_timestamp_format='DD-MM-RR HH24:MI:SSXFF'
nls_timestamp_tz_format='DD-MM-RR HH24:MI:SSXFF TZR'
open_cursors=300
pga_aggregate_target=52428800
processes=300
remote_login_passwordfile='EXCLUSIVE'
sga_max_size=419430400
sga_target=419430400
undo_tablespace='UNDOTBS1'

But when creating a standby database using the same parameters as above, the following error is encountered:

SQL> startup nomount pfile='/usr/dbvisit/standby/tmp/dbv_QA1_csd_init.ora'
LRM-00123: invalid character 130 found in the input file 
ORA-01078: failure in processing system parameters

Further investigation found that the NLS_LANG setting on the standby server was not set. Once it was updated to match the primary server the following successful outcome resulted:

oracle@kiwi102[/usr/dbvisit/standby/tmp]: env|grep LANG
LANG=en_NZ.UTF-8
oracle@kiwi102[/usr/dbvisit/standby/tmp]: env|grep NLS
NLS_DATE_FORMAT=dd/mm/yyyy:hh24:mi:ss
oracle@kiwi102[/usr/dbvisit/standby/tmp]: export NLS_LANG="dutch_the netherlands.AL32UTF8"

oracle@kiwi102[/usr/dbvisit/standby/tmp]: sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Di Okt 20 21:49:31 2015

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

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='/usr/dbvisit/standby/tmp/dbv_QA1_csd_init.ora'
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size		          2925120 bytes
Variable Size		     268438976 bytes
Database Buffers	      142606336 bytes
Redo Buffers		        5459968 bytes
SQL>

The above is purely an example to show that if you have different settings between a primary and standby, you might get some unexpected results. It is important to make sure the primary and standby servers match as closely as possible.

Have more questions? Submit a request

Comments