Database 11g

Wednesday, May 22, 2019

How to change existing Database Characterset in Oracle

Hi Guy's,
I got the task to change the database character-set to Arabic Language for an existing UAT database.

Note : - Incorrect data conversion can lead to data corruption, so perform a full backup of the database before attempting to migrate the data to a new character set.


  • Database character set migration has two stages Data Scanning & Data Conversion. Before you change the database character set.
  • We need to identify possible database character set conversion problems and truncation of data. This step is called data scanning, for example number of schema objects where the column widths need to be expanded and the extent of the data that does not exist in the target character repertoire.
  • Before using the Database Character Set Scanner, you must run the csminst.sql script to set up the necessary system tables on the database that you plan to scan.
  • The csminst.sql script needs to be run only once. The script performs the following tasks to prepare the database for scanning:
  1. Creates a user named CSMIG
  2. Assigns the necessary privileges to CSMIG
  3. Assigns the default tablespace to CSMIG
  4. Creates the Character Set Scanner system tables under CSMIG

  • First check the existing character-set of a database.
        SQL> select * from nls_database_parameters;


  • Now execute csminst.sql script,
Steps: -
SQL> @D:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\csminst.sql
User created.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
User altered.
1 row created.
1 row updated.
Table created.
drop public synonym csm$parameters
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$query
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$tables
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$columns
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$extables
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$errors
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$langid
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$charsetid
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$indexes
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$constraints
 ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$triggers
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$dictusers
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
18 rows created.
View created.
drop public synonym csmv$tables
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
View created.
drop public synonym csmv$columns
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.
View created.
drop public synonym csmv$errors
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
View created.
drop public synonym csmv$indexes
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
View created.
drop public synonym csmv$constraints
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
View created.
drop public synonym csmv$triggers
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
View created.
View created.
View created.

View created.
Grant succeeded.
Grant succeeded.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now run CSSCAN utility:

  • Using CSSCAN we can scan the data in the database and generate report that lists any issues involves in changing NLScharacter set of database.
  • When you run CSSCAN it opens 4 options of database Scan.

1. Full Database Scan:  Scanner reads and verifies the character data of all tables belonging to all users in the database including the data dictionary (such as SYS and SYSTEM users), and it reports on the effects of the simulated migration to the new database character set. It scans all schema objects including stored packages, procedures and functions, and object definitions stored as part of the data dictionary.

2. User Scan: The Database Character Set Scanner reads and verifies character data of all tables belonging to the specified user and reports on the effects on the tables of changing the character set.

3. Table Scan : The Database Character Set Scanner reads and verifies the character data of the specified tables, and reports the effects on the tables of changing the character set.

4. Column Scan : The Database Character Set Scanner reads and verifies the character data of the specified columns, and reports the effects on the tables of changing the character set.


  • I want to SCAN full database, so I selected (1) option
  • Mention New Character-set which you want.


D:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN> CSSCAN
Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Sun Apr 28 15:00:24 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: system
Password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
(1)Full database, (2)User, (3)Table, (4)Column: 1 > 1
Current database character set is WE8MSWIN1252.
Enter new database character set name: > AR8MSWIN1256
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..): 1 >
Enumerating tables to scan...

. process 1 scanning SYS.AUD$[AAASZOAABAAAAkAAAA]
. process 1 scanning SYS.AUD$[AAASZOAAKAAAACAAAA]
 process 1 scanning HMIS.AHTRNHST_B4_OPENING_FINAL_BILL[AABfSAAAEAACVAAAAA]
 process 1 scanning HMIS.AHTRNHST_B4_OPENING_FINAL_BILL[AABfSAAAHAAEMiAAAA]
 process 1 scanning HMIS.OTORDERS[AABf4YAAHAAEIiAAAA]
 process 1 scanning IDMS7.INVSRVITM[AABfsxAAGAAARAAAAA]
 process 1 scanning HMIS.OTORDERS[AABf4YAAEAACRAAAAA]
 process 1 scanning IDMS7.INVSRVITM[AABfsxAAEAACdQAAAA]
 process 1 scanning IDMS7.INVSRVITM[AABfsxAAFAACjSAAAA]
 process 1 scanning HMIS.HMIS_AUDIT_TRAIL[AABgLHAAGAAA96YAAA]
 process 1 scanning HMIS.HMIS_AUDIT_TRAIL[AABgLHAAHAAERCAAAA]
 process 1 scanning HMIS.HMIS_AUDIT_TRAIL[AABgLHAAEAACZgAAAA]
 process 1 scanning HMIS.HMIS_AUDIT_TRAIL[AABgLHAAFAACbDYAAA]
 process 1 scanning IDMS7.GLDOCENT[AABfvWAAEAAABAAAAA]
 process 1 scanning IDMS7.GLDOCENT[AABfvWAAFAAAEKAAAA]
 process 1 scanning IDMS7.ACRTEMP1[AABf22AAGAAAsOAAAA]
 process 1 scanning IDMS7.GLDOCENT[AABfvWAAGAAAlsAAAA]
 process 1 scanning HMIS.OTOCURNC_2[AABfP6AAGAAAA0AAAA]
 process 1 scanning IDMS7.ACRTEMP1[AABf22AAFAAAGwAAAA]
 process 1 scanning HMIS.SHDESCPT[AABf3fAAGAAAveAAAA]
 process 1 scanning HMIS.OTOCURNC_2[AABfP6AAFAAAAiAAAA]
 process 1 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA]
 process 1 scanning IDMS7.GLDOCENT[AABfvWAAHAAB84AAAA]
 process 1 scanning HMIS.SHDESCPT[AABf3fAAFAAAJYAAAA]
 process 1 scanning IDMS7.ACRTEMP1[AABf22AAEAAADgAAAA]
 process 1 scanning IDMS7.GLWORKFD_TMP_D[AABfuCAAEAAChQAAAA]
 process 1 scanning IDMS7.GLWORKFH[AABft+AAGAAAbiAAAA]
 process 1 scanning IDMS7.INVDTL[AABfs7AAGAAAVAAAAA]
 process 1 scanning IDMS7.INVSRVITM[AABfsxAAHAAEUyAAAA]
 process 1 scanning HMIS.SHSHTDTA_2[AABfQBAAGAAADEAAAA]
 process 1 scanning IDMS7.GLWORKFD[AABfuHAAGAAAhiAAAA]
 process 1 scanning HMIS.AMPATBIO[AABgQBAAGAAA/mAAAA]
 process 1 scanning IDMS7.GLWORKFD_TMP_D[AABfuCAAGAAAfiAAAA]
 process 1 scanning IDMS7.GLWORKFD_TMP_D[AABfuCAAFAACpSAAAA]
 process 1 scanning IDMS7.GLDOCHDR[AABfvPAAGAAAjsAAAA]
 process 1 scanning IDMS7.GL_MSTBCH[AABft0AAGAAAXSAAAA]
 process 1 scanning IDMS7.INVHDR[AABfs2AAGAAATAAAAA]
 process 1 scanning IDMS7.GLWORKFD_TMP_D[AABfuCAAHAAEWyAAAA]
 process 1 scanning IDMS7.GL_DTLBCH[AABft3AAGAAAZSAAAA]
 process 1 scanning HMIS.MJBAKBON[AABgGRAAHAAEa6AAAA]
 process 1 scanning IDMS7.CSH_TRN[AABfysAAGAAAo+AAAA]
 process 1 scanning HMIS.MJBAKBON[AABgGRAAGAAA60AAAA]
 process 1 scanning IDMS7.GLWORKFD_TMP_G[AABfuAAAGAAAdiAAAA]
 process 1 scanning IDMS7.TOTTAB1[AABfeqAAGAAAJ+AAAA]
 process 1 scanning HMIS.DRGMODLOG[AABf3sAAEAACZhQAAA]
 process 1 scanning HMIS.DRGMODLOG[AABf3sAAGAAAxuwAAA]
 process 1 scanning HMIS.DRGMODLOG[AABf3sAAFAACrSAAAA]
 process 1 scanning HMIS.DRGMODLOG[AABf3sAAHAAEYyAAAA]
 process 1 scanning HMIS.MMTRACK_BKUP[AABfS4AAEAACmSAAAA]
 process 1 scanning HMIS.MMTRACK_BKUP[AABfS4AAFAACukAAAA]
 process 1 scanning SYS.HISTGRM$[AAAAGlAABAAAAqAAAA]
 process 1 scanning HMIS.MMTRACK_BKUP[AABfS4AAGAAAHiIAAA]
 process 1 scanning IDMS7.ITH[AABfr8AAFAAADkAAAA]
 process 1 scanning HMIS.LABRESULT[AABgI6AAGAAA9aAAAA]
 process 1 scanning IDMS7.ITH[AABfr8AAGAAAPmAAAA]
 process 1 scanning HMIS.MMTRACK_BKUP[AABfS4AAHAAEd0AAAA]
 process 1 scanning HMIS.MAPPER[AABgGoAAHAAEh0AAAA]
 process 1 scanning HMIS.MMLABTEST[AABfQHAAHAAEh0QAAA]
 process 1 scanning HMIS.MAPPER[AABgGoAAEAACpioAAA]
 process 1 scanning HMIS.MAPPER[AABgGoAAGAAA9GgAAA]
 process 1 scanning HMIS.OVRLOG[AABfSsAAGAAAHgoAAA]
 process 1 scanning IDMS7.ITH[AABfr8AAHAAB8kAAAA]
 process 1 scanning HMIS.MMLABTEST[AABfQHAAFAACzkAAAA]
 process 1 scanning HMIS.OTOCUTRC[AABf4WAAGAAAy2AAAA]
 process 1 scanning IDMS7.ITH[AABfr8AAEAAAAsAAAA]
 process 1 scanning HMIS.MAPPER[AABgGoAAFAACykAAAA]
 process 1 scanning HMIS.LABRESULT[AABgI6AAFAAANqAAAA]
 process 1 scanning IDMS7.ITH_AZZA[AABfQXAAGAAAFK4AAA]
 process 1 scanning HMIS.OTOCUTRC[AABf4WAAFAAAJ+AAAA]
 process 1 scanning HMIS.OVRLOG[AABfSsAAHAAEg0AAAA]
 process 1 scanning HMIS.LABRESULT[AABgI6AAEAACoiAAAA]
 process 1 scanning SYS.WRI$_ADV_SQLT_PLANS[AAABcLAACAAAA5IAAA]
 process 1 scanning IDMS7.ITH_AZZA[AABfQXAAFAAC0kAAAA]
 process 1 scanning HMIS.LABRESULT[AABgI6AAHAAEf0AAAA]
 process 1 scanning HMIS.MMLABTEST[AABfQHAAEAACriAAAA]
 process 1 scanning HMIS.MARCVLOG[AABgGiAAFAAC2UAAAA]
 process 1 scanning HMIS.MMMEDREPORT[AABgWfAAFAACtaAAAA]
 process 1 scanning HMIS.OVRLOG[AABfSsAAEAACpiAAAA]
 process 1 scanning HMIS.MMLABTEST[AABfQHAAGAAAFEgAAA]
 process 1 scanning SYSTEM.SYS_IMPORT_FULL_01[AAA3GvAABAAAWMAAAA]
 process 1 scanning SYSTEM.SYS_IMPORT_FULL_02[AAA3HjAABAAAhbAAAA]
. process 1 scanning SYS.SQLOBJ$
. process 1 scanning SYS.SQLOBJ$DATA
. process 1 scanning SYS.CHNF$_CLAUSES
. process 1 scanning SYS.CHNF$_GROUP_FILTER_IOT
. process 1 scanning SYS.RECENT_RESOURCE_INCARNATIONS$
. process 1 scanning SYS.WRR$_REPLAY_SCN_ORDER
. process 1 scanning SYS.WRR$_REPLAY_SEQ_DATA
. process 1 scanning SYS.WRR$_WORKLOAD_ATTRIBUTES
. process 1 scanning SYS.AQ$_SCHEDULER$_EVENT_QTAB_T
. process 1 scanning SYS.AQ$_SCHEDULER$_EVENT_QTAB_H
. process 1 scanning SYS.AQ$_SCHEDULER$_EVENT_QTAB_G
. process 1 scanning SYS.AQ$_SCHEDULER$_EVENT_QTAB_I
. process 1 scanning SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_T
. process 1 scanning SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_H
. process 1 scanning SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_G
. process 1 scanning SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_I
. process 1 scanning SYS.AQ$_SCHEDULER_FILEWATCHER_QT_T
. process 1 scanning SYS.AQ$_SCHEDULER_FILEWATCHER_QT_H
. process 1 scanning SYS.AQ$_SCHEDULER_FILEWATCHER_QT_G
. process 1 scanning SYS.AQ$_SCHEDULER_FILEWATCHER_QT_I
. process 1 scanning SYS.AQ$_ALERT_QT_T
. process 1 scanning SYS.AQ$_ALERT_QT_H
. process 1 scanning SYS.AQ$_ALERT_QT_G
. process 1 scanning SYS.AQ$_ALERT_QT_I
. process 1 scanning SYS.AQ$_AQ$_MEM_MC_T
. process 1 scanning SYS.AQ$_AQ$_MEM_MC_H
. process 1 scanning SYS.AQ$_AQ$_MEM_MC_G
. process 1 scanning SYS.AQ$_AQ$_MEM_MC_I
. process 1 scanning SYS.AQ$_AQ_PROP_TABLE_T
. process 1 scanning SYS.AQ$_AQ_PROP_TABLE_H
. process 1 scanning SYS.AQ$_AQ_PROP_TABLE_G
. process 1 scanning SYS.AQ$_AQ_PROP_TABLE_I
. process 1 scanning SYS.AQ$_KUPC$DATAPUMP_QUETAB_T
. process 1 scanning SYS.AQ$_KUPC$DATAPUMP_QUETAB_H
. process 1 scanning SYS.AQ$_KUPC$DATAPUMP_QUETAB_G
. process 1 scanning SYS.AQ$_KUPC$DATAPUMP_QUETAB_I
. process 1 scanning SYS.AQ$_SYS$SERVICE_METRICS_TAB_T
. process 1 scanning SYS.AQ$_SYS$SERVICE_METRICS_TAB_H
. process 1 scanning SYS.AQ$_SYS$SERVICE_METRICS_TAB_G
. process 1 scanning SYS.AQ$_SYS$SERVICE_METRICS_TAB_I
. process 1 scanning DBSNMP.BSLN_TIMEGROUPS
. process 1 scanning EXFSYS.EXF$ATTRSET
. process 1 scanning EXFSYS.EXF$EXPRSET
. process 1 scanning EXFSYS.EXF$EXPSETPRIVS
. process 1 scanning EXFSYS.EXF$ATTRLIST
. process 1 scanning EXFSYS.EXF$ASUDFLIST
. process 1 scanning EXFSYS.EXF$IDXSECOBJ
. process 1 scanning EXFSYS.EXF$EXPSETSTATS
. process 1 scanning CTXSYS.DR$PARAMETER
. process 1 scanning CTXSYS.DR$SQE
. process 1 scanning CTXSYS.DR$THS_FPHRASE
. process 1 scanning CTXSYS.DR$SECTION_ATTRIBUTE
. process 1 scanning CTXSYS.DR$STOPWORD
. process 1 scanning CTXSYS.DR$PENDING
. process 1 scanning CTXSYS.DR$ONLINE_PENDING
. process 1 scanning CTXSYS.DR$PARALLEL
. process 1 scanning CTXSYS.DR$DBO
. process 1 scanning CTXSYS.DR$INDEX_CDI_COLUMN
. process 1 scanning CTXSYS.DR$SDATA_UPDATE
. process 1 scanning EXFSYS.RLM$ERRCODE
. process 1 scanning EXFSYS.RLM$RULESETSTCODE
. process 1 scanning EXFSYS.RLM$EVENTSTRUCT
. process 1 scanning EXFSYS.RLM$RULESET
. process 1 scanning EXFSYS.RLM$RULESETPRIVS
Creating Database Scan Summary Report...
Creating Individual Exception Report...
ORA-01455: converting column overflows integer datatype
CSS-00168: failed to report individual exceptions
CSS-00122: failed to create scan report
Scanner terminated successfully.

D:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 28 15:28:12 2019
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;
INSTANCE_NAME           STATUS
---------------------------------------------------
iectest2                                 OPEN

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

SQL> startup restrict
ORACLE instance started.
Total System Global Area 2.0911E+10 bytes
Fixed Size                  2264328 bytes
Variable Size            5905580792 bytes
Database Buffers         1.4965E+10 bytes
Redo Buffers               38170624 bytes
Database mounted.
Database opened.

SQL>  @D:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\csalter.plb

0 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 validity...
Exceptional data found in scanner result
PL/SQL procedure successfully completed.

Checking or Converting phase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.

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

 SQL> startup mount
ORACLE instance started.
Total System Global Area 2.0911E+10 bytes
Fixed Size                  2264328 bytes
Variable Size            5905580792 bytes
Database Buffers         1.4965E+10 bytes
Redo Buffers               38170624 bytes
Database mounted.

SQL> alter system enable restricted session;
System altered.
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 system set nls_length_semantics=CHAR scope=both;
System altered.

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

 SQL> startup restrict;
ORACLE instance started.
Total System Global Area 2.0911E+10 bytes
Fixed Size                  2264328 bytes
Variable Size            5905580792 bytes
Database Buffers         1.4965E+10 bytes
Redo Buffers               38170624 bytes
Database mounted.
Database opened.

SQL> alter database character set INTERNAL_USE AR8MSWIN1256;

Database altered.

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

 SQL> startup
ORACLE instance started.
Total System Global Area 2.0911E+10 bytes
Fixed Size                  2264328 bytes
Variable Size            5905580792 bytes
Database Buffers         1.4965E+10 bytes
Redo Buffers               38170624 bytes
Database mounted.
Database opened.

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
                                           NOW CHECK THE NEW CHARACTER-SET
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


SQL> select * from nls_database_parameters ORDER BY PARAMETER;

PARAMETER                                          VALUE
------------------------------ ----------------------------------------
NLS_CALENDAR                                   GREGORIAN
NLS_CHARACTERSET                         AR8MSWIN1256
NLS_COMP                                               BINARY
NLS_CSMIG_SCHEMA_VERSION            5
NLS_CURRENCY                                         $
NLS_DATE_FORMAT                       DD-MON-RR
NLS_DATE_LANGUAGE                 AMERICAN
NLS_DUAL_CURRENCY                           $
NLS_ISO_CURRENCY                      AMERICA
NLS_LANGUAGE                             AMERICAN
NLS_LENGTH_SEMANTICS                BYTE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_NCHAR_CONV_EXCP                FALSE
NLS_NUMERIC_CHARACTERS             .,
NLS_RDBMS_VERSION                      11.2.0.3.0
NLS_SORT                                              BINARY
NLS_TERRITORY                                 AMERICA
NLS_TIMESTAMP_FORMAT             DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT      DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT                          HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                   HH.MI.SSXFF AM TZR

21 rows selected.


Happy Learning…..




 


Posted by at 1 comment:
Subscribe to: Posts (Atom)