jueves, 14 de mayo de 2015

Oracle Database Hot Topics: Operación a corazón abierto: Como cambiar el NLS_CHARACTERSET en un ODA

By Ronald Vargas Quesada, ACED

Si haz adquirido un ODA en los últimos días o estas por hacerlo, te recomiendo que antes de que tengas que hacer este procedimiento, pongas especial cuidado a la hora de crear la base de datos en el proceso de instalación en el Oracle Database Appliance, en definir de forma correcta los parámetros de regionalización, que te permita manejar de forma correcta, el set de caracteres requeridos en tu base de datos.

Toma en cuenta, que el despliegue del ODA, se basa en plantillas ( templates ), que no están tropicalizados. Si procedes a hacer la instalación tipo NEXT/NEXT ( a lo windows ), sin darte cuenta, estarás configurando el set de caracteres de la base de datos a UTF8, o sea unicode.


Qué importancia tiene esto.?
Existe un par de notas que explican ampliamente las implicaciones de la escogencia del set de caracteres:
  • 788156.1 - AL32UTF8 / UTF8 (Unicode) Database Character Set Implications 
  • 260192.1 - Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode)
A la hora de empezar a trabajar con tu base de datos, podrás ver que no tienes problema a la hora de ingresar registros con caracteres especiales como "ñ", tildes, o quizás requieras ingresar información en un idioma como el mandarín o japones.
Pero a la hora de hacer la consultas con estos datos, los caracteres que se visualizan en pantalla aparecen con signos especiales. Esto se debe a que haz configurado de forma inapropiada el set de caracteres de la base de datos.

A continuación encontrarás un procedimiento que podrás utilizar, para realizar una operación a corazón abierto en tu base de datos. Como lo he dicho anteriormente, este proceso es delicado y requiere tomar todas las precauciones del caso, sino, podrías quedarte sin la base de datos.

Déjame tu comentario de que te parece la nota y que tipo de artículos te gustaría estar leyendo más a menudo por acá.

Pasos para ejecutar el cambio del valor del parámetro. 

IMPORTANTE
  1. Leer el contenido completo de la guía.
  2. Recomendable y más que recomendable obligatorio realizar un full respaldo antes de continuar con este proceso. El proceso podría dejarlo sin base de datos.
Objetivo: Actualizar la tabla sys.prop$ del diccionario de la base de datos en donde se almacena el set de caracteres de la base de datos.

Validar el valor de los parámetros en la base de datos actuales.

SQL> column VALUE format a10
SQL> column PARAMETER format a30
SQL> select * from v$nls_parameters;

 SQL> /

PARAMETER                      VALUE                              CON_ID
------------------------------ ------------------------------ ----------
NLS_LANGUAGE                   AMERICAN                                0
NLS_TERRITORY                  AMERICA                                 0
NLS_CURRENCY                   $                                       0
NLS_ISO_CURRENCY               AMERICA                                 0
NLS_NUMERIC_CHARACTERS         .,                                      0
NLS_CALENDAR                   GREGORIAN                               0
NLS_DATE_FORMAT                DD-MON-RR                               0
NLS_DATE_LANGUAGE              AMERICAN                                0
NLS_CHARACTERSET               US7ASCII                                0
NLS_SORT                       BINARY                                  0
NLS_TIME_FORMAT                HH.MI.SSXFF AM                          0
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM                0
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                      0
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR            0
NLS_DUAL_CURRENCY              $                                       0
NLS_NCHAR_CHARACTERSET         AL3UTF8                                 0
NLS_COMP                       BINARY                                  0
NLS_LENGTH_SEMANTICS           BYTE                                    0
NLS_NCHAR_CONV_EXCP      FALSE                                   0

Realización de respaldo de la base de datos, previo a la operación.

. . exported "SYSMAN"."MGMT_TOPO_PAGE_OBJ_POS"               0 KB       0 rows
. . exported "SYSMAN"."MGMT_TOPO_PAGE_PREF"                  0 KB       0 rows
. . exported "SYSMAN"."MGMT_UPDATE_COLL_CREDS_DATA"          0 KB       0 rows
. . exported "SYSMAN"."MGMT_UPDATE_CREDENTIALS_DATA"         0 KB       0 rows
. . exported "SYSMAN"."MGMT_UPDATE_OPERATIONS"               0 KB       0 rows
. . exported "SYSMAN"."MGMT_UPDATE_OPERATIONS_DATA"          0 KB       0 rows
. . exported "SYSMAN"."MGMT_UPDATE_OPERATIONS_DETAILS"       0 KB       0 rows
. . exported "SYSMAN"."MGMT_UPDATE_PDP_DATA"                 0 KB       0 rows
. . exported "SYSMAN"."MGMT_UPDATE_PDP_DATA_COPY"            0 KB       0 rows
. . exported "SYSMAN"."MGMT_UPDATE_PDP_DATA_MAP"             0 KB       0 rows
. . exported "SYSMAN"."MGMT_UPDATE_PROPERTIES_DATA"          0 KB       0 rows
. . exported "SYSMAN"."MGMT_UPDATE_TEMPLATE_DATA_MAP"        0 KB       0 rows
. . exported "SYSMAN"."MGMT_UPDATE_THRESHOLDS_DATA"          0 KB       0 rows
. . exported "SYSMAN"."MGMT_URL_CACHE"                       0 KB       0 rows
. . exported "SYSMAN"."MGMT_URL_PROXY"                       0 KB       0 rows
. . exported "SYSMAN"."MGMT_USER_PREFERENCES"                0 KB       0 rows
. . exported "SYSMAN"."MGMT_USER_SESSION"                    0 KB       0 rows
. . exported "SYSMAN"."MGMT_VIEW_USER_CREDENTIALS"           0 KB       0 rows
. . exported "SYSMAN"."MGMT_VIOLATIONS"                      0 KB       0 rows
. . exported "SYSMAN"."MGMT_VIOLATION_CONTEXT"               0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_02 is:
  /u01/app/oracle/fast_recovery_area/datastore/expmigracion/change_character_backup_instoda.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_02" successfully completed at Thu May 14 04:58:40 2015 elapsed 0 00:20:21

[oracle@odadb1 expmigracion]$ ls -al
total 65535436
drwxr-xr-x 2 oracle oinstall        8192 May 14 04:38 .
drwxrwxr-x 6 oracle oinstall        4096 May  2 16:07 ..
-rw-r----- 1 oracle asmadmin 62790074368 May 14 04:58 change_character_backup_instoda.dmp
-rw-r--r-- 1 oracle asmadmin      120956 May 14 04:58 exp.log
-rw-r----- 1 oracle oinstall  4311517889 May  2 19:12 expdat.dmp.gz
-rw-r--r-- 1 oracle asmadmin      254455 May  3 00:46 imp.log

[oracle@odadb1 expmigracion]$ df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/xvda2            57192344  12632188  41654884  24% /
/dev/xvda1              470844     44718    401815  11% /boot
/dev/xvdb1            96120004  28828532  62408764  32% /u01
tmpfs                 32922988   1241096  31681892   4% /dev/shm
/dev/asm/acfsvol-86   52428800    182096  52246704   1% /cloudfs
/dev/asm/datafsvol-86
                       5242880     88496   5154384   2% /odadatafs
/dev/asm/vmstorage-18
                     419430400 367079932  52350468  88% /u01/app/sharedrepo/vmstorage
/dev/asm/datastore-18
                     1128267776  88222908 1040044868   8% /u02/app/oracle/oradata/datastore
/dev/asm/datastore-434
                      65011712  17115428  47896284  27% /u01/app/oracle/oradata/datastore
/dev/asm/datastore-86
                     1501560832 172496680 1329064152  12% /u01/app/oracle/fast_recovery_area/datastore

[oracle@odadb1 expmigracion]$ pwd
/u01/app/oracle/fast_recovery_area/datastore/expmigracion

[oracle@odadb1 expmigracion]$ ls -la
total 65535436
drwxr-xr-x 2 oracle oinstall        8192 May 14 04:38 .
drwxrwxr-x 6 oracle oinstall        4096 May  2 16:07 ..
-rw-r----- 1 oracle asmadmin 62790074368 May 14 04:58 change_character_backup_instoda.dmp
-rw-r--r-- 1 oracle asmadmin      120956 May 14 04:58 exp.log
-rw-r----- 1 oracle oinstall  4311517889 May  2 19:12 expdat.dmp.gz
-rw-r--r-- 1 oracle asmadmin      254455 May  3 00:46 imp.log

[oracle@odadb1 expmigracion]$ sqlplus sys/password as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 14 05:00:16 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> alter system set cluster_database=FALSE scope=spfile;

System altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

[oracle@odadb1 expmigracion]$ exit
logout
[root@odadb1 ~]# su - grid
[grid@odadb1 ~]$ srvctl stop database -d instoda
[grid@odadb1 ~]$ exit
logout

[root@odadb1 ~]# su - oracle
[oracle@odadb1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-MAY-2015 05:04:16

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                15-APR-2015 22:54:43
Uptime                    28 days 6 hr. 9 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/odadb1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.103.253.20)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.103.253.22)(PORT=1521)))
Services Summary...
Service "+APX" has 1 instance(s).
  Instance "+APX1", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@odadb1 ~]$ ORACLE_SID=instoda1
[oracle@odadb1 ~]$ export ORACLE_SID
[oracle@odadb1 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 14 05:05:18 2015

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

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2.5770E+10 bytes
Fixed Size                  2938792 bytes
Variable Size            3422554200 bytes
Database Buffers         2.2213E+10 bytes
Redo Buffers              131276800 bytes
Database mounted.
SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000

SQL> alter system set job_queue_processes=0;

System altered.

SQL> alter database open;

Database altered.

SQL> alter database character set WE8MSWIN1252;
alter database character set WE8MSWIN1252
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set


SQL> update sys.props$ set value$='WE8MSWIN1252' where name='NLS_CHARACTERSET';

1 row updated.

SQL> commit;

Commit complete.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2.5770E+10 bytes
Fixed Size                  2938792 bytes
Variable Size            3422554200 bytes
Database Buffers         2.2213E+10 bytes
Redo Buffers              131276800 bytes
Database mounted.
SQL> alter system enable restricted session;

System altered.

SQL> alter database open;

Database altered.

SQL> alter database character set WE8MSWIN1252;

Database altered.

SQL> alter system set job_queue_processes=1000;

System altered.

SQL> alter system set cluster_database=true scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options

[oracle@odadb1 ~]$ exit
logout
[root@odadb1 ~]# su - grid
[grid@odadb1 ~]$ srvctl start database -d instoda
[grid@odadb1 ~]$
[grid@odadb1 ~]$ exit
logout

[root@odadb1 ~]# su - oracle

[oracle@odadb1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-MAY-2015 05:14:33

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                15-APR-2015 22:54:43
Uptime                    28 days 6 hr. 19 min. 49 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/odadb1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.103.253.20)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.103.253.22)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=odadb1.lab.local)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0.2/dbhome_1/admin/instoda/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+APX" has 1 instance(s).
  Instance "+APX1", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "instoda" has 1 instance(s).
  Instance "instoda1", status READY, has 1 handler(s) for this service...
Service "instodaXDB" has 1 instance(s).
  Instance "instoda1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@odadb1 ~]$ ORACLE_SID=instoda1
[oracle@odadb1 ~]$ export ORACLE_SID
[oracle@odadb1 ~]$ sqlplus sys/password as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 14 05:15:17 2015

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> column PARAMETER format a40
SQL> column value format a20
SQL> select * from v$nls_parameters;

PARAMETER                                VALUE                    CON_ID
---------------------------------------- -------------------- ----------
NLS_LANGUAGE                             AMERICAN                      0
NLS_TERRITORY                            AMERICA                       0
NLS_CURRENCY                             $                             0
NLS_ISO_CURRENCY                         AMERICA                       0
NLS_NUMERIC_CHARACTERS                   .,                            0
NLS_CALENDAR                             GREGORIAN                     0
NLS_DATE_FORMAT                          DD-MON-RR                     0
NLS_DATE_LANGUAGE                        AMERICAN                      0
NLS_CHARACTERSET                         WE8MSWIN1252                  0
NLS_SORT                                 BINARY                        0
NLS_TIME_FORMAT                          HH.MI.SSXFF AM                0

PARAMETER                                VALUE                    CON_ID
---------------------------------------- -------------------- ----------
NLS_TIMESTAMP_FORMAT                     DD-MON-RR HH.MI.SSXF          0
                                         F AM

NLS_TIME_TZ_FORMAT                       HH.MI.SSXFF AM TZR            0
NLS_TIMESTAMP_TZ_FORMAT                  DD-MON-RR HH.MI.SSXF          0
                                         F AM TZR

NLS_DUAL_CURRENCY                        $                             0
NLS_NCHAR_CHARACTERSET                   AL16UTF16                     0
NLS_COMP                                 BINARY                        0
NLS_LENGTH_SEMANTICS                     BYTE                          0

PARAMETER                                VALUE                    CON_ID
---------------------------------------- -------------------- ----------
NLS_NCHAR_CONV_EXCP                      FALSE                         0

19 rows selected.

SQL>
[oracle@odadb1 ~]$ . .bash_profile
[oracle@odadb1 ~]$ env|grep NLS
NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
[oracle@odadb1 ~]$

[oracle@odadb1 ~]$ more /etc/hosts
# Hostfile created by Rapid Grid installer

# Following added by OneCommand
127.0.0.1    localhost.localdomain localhost

# PUBLIC HOSTNAMES

# PRIVATE HOSTNAMES
192.168.16.27    odadb1-priv0.lab.local odadb1-priv0
192.168.16.28    odadb2-priv0.lab.local odadb2-priv0

# VIP HOSTNAMES
10.103.253.22    odadb1-vip.lab.local odadb1-vip
10.103.253.23    odadb1-vip.lab.local odadb1-vip

# NET(0-3) HOSTNAMES
10.103.253.20    odadb1.lab.local odadb1
10.103.253.21    odadb1.lab.local odadb1

# Below are SCAN IP addresses for reference.
# SCAN_IPS=(10.103.253.24 10.103.253.25)
# ASR entry
141.146.1.169   transport.oracle.com
[oracle@odadb1 ~]$ ssh odadb1
[oracle@odadb1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-MAY-2015 05:20:09

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                11-MAR-2015 14:18:35
Uptime                    63 days 15 hr. 1 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/odadb1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.103.253.21)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.103.253.23)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=odadb1.lab.local)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0.2/dbhome_1/admin/instoda/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+APX" has 1 instance(s).
  Instance "+APX2", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "-MGMTDBXDB" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "_mgmtdb" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "instoda" has 1 instance(s).
  Instance "instoda2", status READY, has 1 handler(s) for this service...
Service "instodaXDB" has 1 instance(s).
  Instance "instoda2", status READY, has 1 handler(s) for this service...
Service "ersdb_c" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@odadb1 ~]$ vi .bash_profile
[oracle@odadb1 ~]$ . .bash_profile

[oracle@odadb1 ~]$ env|grep NLS
NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
[oracle@odadb1 ~]$

[oracle@odadb1 ~]$ exit
logout
Connection to odadb1 closed.
[oracle@odadb1 ~]$ sqlplus sys/password as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 14 05:22:41 2015

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> create table t1(nombre varchar2(20), apellido varchar2(20));

Table created.

SQL> insert into t1 values('Fabián','Nuñez');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

NOMBRE               APELLIDO
-------------------- --------------------
Fabián               Nuñez

SQL> drop table t1;

Table dropped.

SQL>




No hay comentarios:

Publicar un comentario en la entrada

Te agradezco tus comentarios. Te esperamos de vuelta.