Oracle National Character set change
Oracle National Character set change
Changing Character Set or National Character Set are vary rare activity which DBA need to perform. In This blog, We will discuss about changing of National Character Set.
We would like to change NLS_NCHAR_CHARACTERSET parameters for our database:
current value - AL16UTF16
New required value - AL32UTF8
Below are the Steps followed to change the Database National Character Set:
For Oracle 9i Database:
1) export all the user N-data (this may included 'SYSMAN' data)
select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB') and OWNER not in ('SYS','SYSTEM', 'XDB');
2)drop/truncate all the user N-data
-- If you do not drop all N-data then you will run into
-- ORA-12717: Cannot issue ALTER DATABASE NATIONAL CHARACTER SET when NCLOB, NCHAR or NVARCHAR2 data exists
3) run csscan to check if everything is o
csscan FULL=Y TONCHAR=UTF8 LOG=check CAPTURE=Y ARRAY=1000000 PROCESS=2
always run csscan with / as sydba. Do NOT specify TOCHAR only specify TONCHAR !
3.1)csscan will ask:
Current database character set is WE8MSWIN1252 <- this is the current NLS_CHARACTERSET
Enter new database character set name: > <-*just hit enter here*, do NOT fill anything in
4)check that you see in the check.txt file this:
All character type data in the data dictionary remain the same in the new character set
All character type application data remain the same in the new character set
5) then you can do a ALTER DATABASE NATIONAL CHARACTER SET UTF8;
Shutdown the listener and any application that connects locally to the database.
There should be only ONE connection the database during the WHOLE time and that's the sqlplus session where you do the change.
5.1) Make sure the PARALLEL_SERVER (8i) and CLUSTER_DATABASE parameter are set to false or it is not set at all. When using RAC you will need to start the database in single instance with CLUSTER_DATABASE = FALSE
conn / as sysdba
sho parameter CLUSTER_DATABASE
sho parameter PARALLEL_SERVER
5.2) Execute the following commands in sqlplus connected as "/ AS SYSDBA":
SPOOL Nswitch.log
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE NATIONAL CHARACTER SET UTF8;
SHUTDOWN IMMEDIATE;
5.3) Restore the parallel_server parameter in INIT.ORA, if necessary.
6) import the user N-data again
For Oracle 10g ,11g & 12c Onwards Databases;
1)export any user N-data (this may included 'SYSMAN' data)
select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB') and OWNER not in ('SYS','SYSTEM', 'XDB');
In an database with APEX/HTMLDB you might also see some APEX tables, ignore them for the time being - they are normally empty , if they are populated they will be listed as "convertible" in the csscan run later on
2) drop/truncate any user N-data
3)Truncate these 2 xdb tables if they exist and give 7 rows
SQL>select LOCALNAME from XDB.XDB$QNAME_ID;
SQL>select NMSPCURI from XDB.XDB$NMSPC_ID;
SQL>truncate table XDB.XDB$QNAME_ID
SQL>truncate table XDB.XDB$NMSPC_ID
if the exist and have more rows log a SR.
4) run csscan to check if everything is ok
csscan FULL=Y TONCHAR=UTF8 LOG=check CAPTURE=Y ARRAY=1000000 PROCESS=2
always run csscan with / as sydba. Do NOT specify TOCHAR only specify TONCHAR !
4.1)csscan will ask:
Current database character set is WE8MSWIN1252. <- this is the current NLS_CHARACTERSET
Enter new database character set name: > <-*just hit enter here*, do NOT fill anything in
4.2)check that you see in the check.txt file this:
All character type data in the data dictionary remain the same in the new character set
All character type application data remain the same in the new character set or use this select, it should give no results:
conn / AS sysdba
set serveroutput on
DECLARE
countstar NUMBER;
stmt VARCHAR2(1000);
BEGIN
FOR rec in
( select distinct c.owner, c.table_name from dba_tab_cols c, dba_objects o
where c.data_type in ('NCHAR','NVARCHAR2', 'NCLOB')
and c.owner=o.owner
and c.table_name = o.object_name
and o.object_type = 'TABLE'
order by c.owner, c.table_name)
LOOP
stmt := 'select count(*) from "' || rec.owner || '"."' || rec.table_name || '"' ;
execute immediate stmt into countstar;
If countstar >0 then
DBMS_OUTPUT.PUT_LINE(rec.owner||'.'|| rec.table_name|| ' - count * is : ' || countstar );
end if;
END LOOP;
END;
/
5)after that run csalter (10g And 11g )
Shutdown the listener and any application that connects locally to the database.
There should be only ONE connection the database during the WHOLE time and that's the sqlplus session where you do the change.
Then you do in sqlplus connected as "/ AS SYSDBA":
-- Make sure the parallel_server and CLUSTER_DATABASE parameter are set
-- to false or it is not set at all.
-- If you are using RAC you will need to start the database in single instance
-- with CLUSTER_DATABASE = FALSE
sho parameter CLUSTER_DATABASE
sho parameter PARALLEL_SERVER
-- check if you are using spfile
sho parameter pfile
-- if this "spfile" then you are using spfile
-- in that case note the
sho parameter job_queue_processes
sho parameter aq_tm_processes
-- (this is Bug 6005344 fixed in 11g )
-- then do
PURGE DBA_RECYCLEBIN
/
shutdown immediate
startup restrict
SPOOL Nswitch.log
@?\rdbms\admin\csalter.plb
-- if you are using spfile then you need to also
-- ALTER SYSTEM SET job_queue_processes=<original value> SCOPE=BOTH;
-- ALTER SYSTEM SET aq_tm_processes=<original value> SCOPE=BOTH;
shutdown
startup
For 12c and above:
Please use DMU to change National character set for database.
NOTE: DMU does not check for any loss of data during the conversion.So please run cancan to check any possible data loss.
6) after this update the XDB tables again with these inserts:
(these inserts can also be found in the $ORACLE_HOME/rdbms/admin/catxdbtm.sql script)
insert into xdb.xdb$nmspc_id values ('http://www.w3.org/XML/1998/namespace', HEXTORAW('01'));
insert into xdb.xdb$nmspc_id values ('http://www.w3.org/XML/2000/xmlns', HEXTORAW('02'));
insert into xdb.xdb$nmspc_id values ('http://www.w3.org/2001/XMLSchema-instance', HEXTORAW('03'));
insert into xdb.xdb$nmspc_id values ('http://www.w3.org/2001/XMLSchema', HEXTORAW('04'));
insert into xdb.xdb$nmspc_id values ('http://xmlns.oracle.com/2004/csx', HEXTORAW('05'));
insert into xdb.xdb$nmspc_id values ('http://xmlns.oracle.com/xdb', HEXTORAW('06'));
insert into xdb.xdb$nmspc_id values ('http://xmlns.oracle.com/xdb/nonamespace', HEXTORAW('07'));
insert into xdb.xdb$qname_id values (HEXTORAW('01'), 'space', HEXTORAW('01'), HEXTORAW('10'));
insert into xdb.xdb$qname_id values (HEXTORAW('01'), 'lang', HEXTORAW('01'), HEXTORAW('11'));
insert into xdb.xdb$qname_id values (HEXTORAW('03'), 'type', HEXTORAW('01'), HEXTORAW('12'));
insert into xdb.xdb$qname_id values (HEXTORAW('03'), 'nil', HEXTORAW('01'), HEXTORAW('13'));
insert into xdb.xdb$qname_id values (HEXTORAW('03'), 'schemaLocation', HEXTORAW('01'), HEXTORAW('14'));
insert into xdb.xdb$qname_id values (HEXTORAW('03'), 'noNamespaceSchemaLocation', HEXTORAW('01'), HEXTORAW('15'));
insert into xdb.xdb$qname_id values (HEXTORAW('02'), 'xmlns', HEXTORAW('01'), HEXTORAW('16'));
commit;
7)import any user N-data you exported
Do NOT use the UTLNCHAR.SQL or N_SWITCH.SQL script.
Using this a to try to go from UTF8 to AL16UTF16 (or inverse) will corrupt existing NCHAR data !!!!!!
It is to be used only in specific conditions when upgrading from 8i to 9i
You can learn more about oracle database administration:
Pro Oracle GoldenGate for the DBA
Oracle Security Tools Installation and Configuration
=============================================================
Check our other blog for Changing Character Set of the database.
Comments
Post a Comment
Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !