Monday, October 5, 2015

Is it possible to change the character set of the database by recreating the control file?

Question: Is it possible to change the character set of the database by recreating the controlfil?
Answer: No, this is not supported.

Doc ID 225912.1 states:

"Note also that it's NOT possible to change / adapt the NLS_CHARACTERSET by re-creating the control file.
The characterset specified in CREATE CONTROLFILE DATABASE ... RESETLOGS ....CHARACTER SET ; ( the result of an ALTER DATABASE BACKUP CONTROLFILE TO TRACE..) need to be the actual current NLS_CHARACTERSET of the database."


Question: So why does CREATE CONTROLFILE generate a CHARACTERSET clause in the first place then?
Answer: In case media recovery is required after the controlfile has been recreated.

From the documentation:
"If you specify a character set, then Oracle Database reconstructs character set information in the control file. If media recovery of the database is subsequently required, then this information will be available before the database is open, so that tablespace names can be correctly interpreted during recovery."

The documentation goes on to say

"This clause is required only if you are using a character set other than the default, which depends on your operating system."

Without having found any sources on the internet to confirm my interpretation, I will claim that this means:

* If you recreate your controlfile for a database with, say, WE8ISO8859P1 as character set, you will indeed need to specify the character in the CREATE CONTROLFILE clause.

* If you recreate the controlfile of a database using UTF8, for example AL32UT8, you can skip this clause altogether if you wish

No comments:

Post a Comment