--------------------------Change Oracle DB Character Set---------------------------------------------
If you want to change from WE8MSWIN1252 to AL32UTF8. Check the NLS parameters first.
SQL> SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
---------------------------------------Changing Commands------------------------------------------------
The syntax of the ALTER DATABASE CHARACTER SET statement is as follows:
SQL> ALTER DATABASE [db_name] CHARACTER SET new_character_set;
(db_name is an optional)
For Example :
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
--------------------------------Steps to change the database character set------------------------------------------
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.
2. Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
3. Startup Oracle database
SQL> startup mount;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
If you get the error ORA-12712, please update the following table.
"ORA-12712: new character set must be a superset of old character set"
SQL> update sys.props$ set VALUE$='AL32UTF8' where NAME='NLS_CHARACTERSET';
SQL> commit;
If you get the error ORA-12721, please login as DBA user.
"ORA-12721: operation cannot execute when other sessions are active"
4. shutdown immediate; or shutdown normal;
5. startup oracle database
SQL> startup mount;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
SQL> shutdown immediate;
SQL> startup;
--------------------------------------Check the NLS parameters-------------------------------------------
SQL> SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
Thanks, this really helps
ReplyDeleteIf you execute the above change of your Character Set, you certainly destroy your database and will be unable to discover it. Very very bad idea.
ReplyDeletethat's why there is csscan and csalter...
DeleteInstead of using those instructions u can also try these steps from the video.
ReplyDeleteeasy and simple with 3 steps.
https://www.youtube.com/watch?v=zMphHE78imM
This comment has been removed by the author.
ReplyDeleteIf it's a rac environment you must first
ReplyDeleteSQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;
After turning cluster off, then execute the above steps. After changing the database character set, remember to turn back cluster dabatase to TRUE and then reset those parameters again else..
1 Database will only be started on the node where it was stopped.
2. Jobs will like expdp/impdp fail with errors like
ORA-39065: unexpected master process exception in DISPATCH
ORA-39079: unable to enqueue message DG,KUPC$S_1_20160803115525,MCP, ,1,Y
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPC$QUE_INT", line 969
ORA-25306: Cannot connect to buffered queue's owner instance
ORA-39097: Data Pump job encountered unexpected error -39079
ORA-39065: unexpected master process exception in KUPC$QUEUE_INT.PUT_STATUS
ORA-39079: unable to enqueue message DG,KUPC$S_1_20160803115525,MCP, ,1,Y
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPC$QUE_INT", line 969
ORA-25306: Cannot connect to buffered queue's owner instance
To fix this, reset the process parameter to their default in the same way they were set to 0.
SQL> alter system reset JOB_QUEUE_PROCESSES scope=spfile sid='*';
System altered.
SQL> alter system reset aq_tm_processes scope=spfile sid='*';
System altered.
expdp/impdp runs like hot knife through butter
I forgot to mention that you must bounce the database again to make those parameter reset take effect.
DeleteThanks
thanks ¡ very helpfull
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteMuy buena información entregada en esta discusión, muy clara y concisa, me gustó tu blog , muy interesante el tema del cual hay mucho por aprender
ReplyDeleteQuiero dejar esta página en la cual puedes ver productos de los retail de todo Chile,
y comparar los precios de ofertas lider con las otras tiendas