Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 192,398
  • Archives

  • ŗ•ź

  • Categories

  • Subscribe

  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Posts Tagged ‘character set’

CSSCAN & CSALTER: Character Set Change Utility.

Posted by FatDBA on August 21, 2012

—————————–
CSSCAN & CSALTER
—————————–
The character set migration utility schema is installed by running the “$ORACLE_HOME/rdbms/admin/csminst.sql” script in SQL*Plus as the SYS user. Once the schema is present, the character set scanner should work normally.

[oracle@localhost admin]$ csscan
Character Set Scanner v2.1 : Release 10.2.0.0.0 – Production on Tue Aug 21 09:43:59 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username: sys as sysdba

Password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

(1)Full database, (2)User, (3)Table, (4)Column: 1 > 2

Current database character set is JA16SJIS.

Enter new database character set name: > WE8ISO8859P1

Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..32): 1 > 32

Enter user name to scan: > larry tonchar
CSS-00124: user larry tonchar not found

Enter user name to scan: > larry

Enumerating tables to scan…

. process 2 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAHAAAAMJAAA]
. process 1 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAHAAAAAJAAA]
. process 3 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAIxAAA]
. process 3 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAAWJAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAATpAAA]
. process 4 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAASpAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAAMBAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAKZAAA]
. process 5 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAJxAAA]
. process 4 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAAWpAAA]
. process 6 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAIJAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAK5AAA]
. process 7 scanning LARRY.SYS_EXPORT_SCHEMA_01[AAANSoAAEAAAAGJAAA]
. process 6 scanning LARRY.SYS_EXPORT_SCHEMA_01[AAANSoAAEAAAAHxAAA]
. process 8 scanning LARRY.PLAN_TABLE[AAANieAAEAAAAXBAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAHAAAAPJAAA]
. process 4 scanning LARRY.DEPT1[AAANQuAAEAAAAIBAAA]
. process 9 scanning LARRY.ETR[AAAM/uAAEAAAAGBAAA]
. process 7 scanning LARRY.SALARY[AAAM75AAEAAAAHpAAA]
. process 4 scanning LARRY.EMP1[AAAM9RAAEAAAAH5AAA]
. process 5 scanning LARRY.PERSON[AAAM/xAAEAAAAGhAAA]

Creating Database Scan Summary Report…

Creating Individual Exception Report…

Scanner terminated successfully.
[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Aug 21 09:47:54 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

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

SQL> startup restrict
ORACLE instance started.

Total System Global Area  306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             113247704 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>

SQL> @csalter.plb
4 rows created.

Function created.

Function created.

Procedure created.

This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)? Y
old¬†¬† 6:¬†¬†¬†¬† if (UPPER(‘&conf’) <> ‘Y’) then
new¬†¬† 6:¬†¬†¬†¬† if (UPPER(‘Y’) <> ‘Y’) then
Checking data validility…
Exceptional data found in scanner result

PL/SQL procedure successfully completed.

Checking or Converting phrase finished successfully
Database (national) character set will be altered
CSALTER finished successfully.

PL/SQL procedure successfully completed.

4 rows deleted.

Function dropped.

Function dropped.

Procedure dropped.

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

Total System Global Area  306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             113247704 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>

Posted in Advanced | Tagged: | Leave a Comment »

Character Set Change.

Posted by FatDBA on August 17, 2012

SQL> SELECT * FROM nls_database_parameters WHERE parameter = ‘NLS_CHARACTERSET’;

PARAMETER                      VALUE
—————————— —————————————-
NLS_CHARACTERSET               WE8ISO8859P1

SQL> SELECT * FROM nls_database_parameters WHERE parameter = ‘NLS_CHARACTERSET’;
PARAMETER                      VALUE
—————————— —————————————-
NLS_CHARACTERSET               WE8ISO8859P1

Modify profile and add environmental  variable РNLS_LANG
[root@localhost ~]# vi /etc/profile
NLS_LANG=’AMERICAN_AMERICA.we8iso8859p1′; export NLS_LANG

[oracle@localhost ~]$ echo $NLS_LANG
AMERICAN_AMERICA.we8iso8859p1

To change the database character set, perform the following steps:

  1. Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
  1. Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
  1. Complete the following statements:
  1. STARTUP MOUNT;
  2. ALTER SYSTEM ENABLE RESTRICTED SESSION;
  3. ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
  4. ALTER SYSTEM SET AQ_TM_PROCESSES=0;
  5. ALTER DATABASE OPEN;
  6. ALTER DATABASE CHARACTER SET new_character_set;

SHUTDOWN IMMEDIATE; — or SHUTDOWN NORMAL;

STARTUP;

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
System altered.

SQL> ALTER DATABASE OPEN;
Database altered.

SQL> ALTER DATABASE CHARACTER SET WE8ISO8895P1;
ALTER DATABASE CHARACTER SET WE8ISO8895P1
*
ERROR at line 1:
ORA-12715: invalid character set specified

If getting below provided error while change perform following steps to fix:
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

Resolution:
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE JA16SJIS;
Database altered.

SQL> SELECT * FROM nls_database_parameters WHERE parameter = ‘NLS_CHARACTERSET’;

PARAMETER
——————————
VALUE
——————————————————————————–
NLS_CHARACTERSET
JA16SJIS
—– DONE—-

Posted in Advanced | Tagged: | Leave a Comment »

 
%d bloggers like this: