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á.
IMPORTANTE
- Leer el contenido completo de la guía.
- 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.
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
. . 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
Te agradezco tus comentarios. Te esperamos de vuelta.