A preliminary discussion on character set problem (vii)----internal operations on character set changes

The original question link:

Http://www.eygle.com/special/NLS_CHARACTER_SET_07.htm

As we mentioned earlier, changing the character set by modifying the props$ is an extremely dangerous way to Oracle7, and should be avoided as much as possible.

We also know that changing the character set through alter DATABASE CHARACTER set is safe and reliable, but with strict subsets and superset constraints, we rarely can actually
Use this method.


There is actually another way for Oracle to change the character set.

If you've noticed, in Oracle's Alert<sid>.log file, you might have seen this log message:


ALTER DATABASE character Set Internal_convert zhs16gbkupdating character set in Controlfile to ZHS16GBK SYS. snap$ (rel_query)-CLOB representation altered SYS. Metastylesheet (STYLESHEET)-CLOB representation altered SYS. external_tab$ (Param_clob)-CLOB representation altered XDB. Xdb$resource (sys_nc00027$)-CLOB representation altered ODM. ODM_PMML_DTD (DTD)-CLOB representation altered OE. Warehouses (sys_nc00003$)-CLOB representation altered PM. Online_media (sys_nc00042$)-CLOB representation altered PM. Online_media (sys_nc00062$)-CLOB representation altered PM. Online_media (Product_text)-CLOB representation altered PM. Online_media (sys_nc00080$)-CLOB representation altered PM. Print_media (Ad_sourcetext)-CLOB representation altered PM. Print_media (Ad_finaltext)-CLOB representation Alteredcompleted:alter database Character Set Internal_convert ZHS1



In this case, we see an important, Oracle command that is not publicly available:

ALTER DATABASE character Set Internal_convert/internal_use ZHS16GBK



This command is when you choose to use a typical way to create a seed database, Oracle will change the character set of the current seed database to the desired character according to the character set you choose
Set, that's what this command does.

When using this command, Oracle skips all subsets and superset checks and casts between arbitrary character sets, so you must be very careful when using this command, you must
Be aware of the risks associated with this operation.
What we've said before is still valid, and you can use Csscan to scan the entire database, if there is no serious data corruption between the converted character sets, or you can use a valid
The way you change it, you can use it to convert.
Let's take a look at the specific procedures and Oracle's internal operations:

This is the record information in the Alert.log file:

Tue Oct 19 16:26:30 2004

Database Characterset is ZHS16GBK

Replication_dependency_tracking turned off (no async multimaster replication found)

Completed:alter DATABASE OPEN

Tue Oct 19 16:27:07 2004

ALTER DATABASE character Set Internal_use zhs16cgb231280

Updating character set in Controlfile to zhs16cgb231280

Tue Oct 19 16:27:15 2004

Thread 1 advanced to log sequence 118

Current log# 2 seq# 118 mem# 0:/opt/oracle/oradata/primary/redo02.log

Tue Oct 19 16:27:15 2004

Arc0:evaluating Archive log 3 thread 1 sequence 117

Arc0:beginning to archive log 3 thread 1 sequence 117

Creating archive Destination log_archive_dest_1: '/opt/oracle/oradata/primary/archive/1_117.dbf '

arc0:completed Archiving log 3 thread 1 sequence 117

Tue Oct 19 16:27:20 2004

Completed:alter database Character Set Internal_use zhs16cgb231280

Shutting down Instance:further logons disabled

Shutting down instance (immediate)

License High Water mark = 1

Tue Oct 19 16:29:06 2004

ALTER DATABASE Close NORMAL

...

...


Format the 10046 trace file and get the following information (summary):


Alter session SET Events ' 10046 Trace name context forever,level ' ALTER DATABASE character set Internal_use zhs16cgb2312 80call count CPU Elapsed disk query current rows------------------------------------------------------------------- ----Parse 1 0.00 0.00 0 0 0 0Execute 1 4.88 6.04 910 16825 18099 0Fetch 0 0.00 0.00 0 0 0 0---------------------------- -------------------------------------------Total 2 4.88 6.04 910 16825 18099 0Misses in library cache during Ptimizer goal:chooseparsing user id:syselapsed times include waiting on following events:event in times Max. Wait total waited----------------------------------------waited----------------------control file sequential read 4 0 0.00 Control File Parallel write 2 0.05 0.08 log file Sync 2 0.08 0.08 sql*net message to Client 1 0.00 0.00 Sql*net M Essage from client 1 18.06 18.06******************************************************************************** ... update col$ Set charsetid =: 1 where charsetform =: 2....update argument$ Set charsetid =: 1 Where charsetform =: 2....update collection$ Set Charsetid =: 1 WHERE Charsetform =: 2....update attribute$ Set charsetid =: 1 Where charsetform =: 2....update parameter$ set Charsetid =: 1 Where charsetform =: 2....update result$ Set charsetid =: 1 Where charsetform =: 2....update partcol$ Set spare1 =: 1 WHERE Charsetform =: 2....update subpartcol$ Set spare1 =: 1 Where charsetform =: 2....update props$ Set value$ =: 1 whe Re name =: 2....update "SYS". " kotad$ "Set sys_nc_rowinfo$ =: 1 Where sys_nc_oid$ =: 2....update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=: 5,order$=:6, cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1....update kopm$ Set metadata =: 1, length =: 2 where Name= ' Db_fdo ' .... ALTER DATABASE Close NORMAL


The logs generated here you can download here (for reference):

Http://www.eygle.com/special/primary_ora_13730.zip
Http://www.eygle.com/special/primary_ora_13730.tkf.log

We see that this process is exactly the same as the internal process of the previous alter DATABASE CHARACTER set operation, meaning that the help provided by Internal_use is to make
Oracle databases bypass the checksum of subsets and superset.
This approach is useful in some ways, such as testing; apply to the product environment everyone should be extra careful, except you, no one will be responsible for the consequences:


Conclusion (we may say it again):

For DBAs, there is a very important principle: do not put your database in a dangerous situation!

This requires us to do an effective backup before doing anything that might change the structure of the database, and many DBAs have learned the hard way out of the backup operation.





The author of this article:
Eygle,oracle technology concern, from China's largest Oracle technology forum Itpub.
Www.eygle.com is the author's personal site. You can contact the author by Guoqiang.Gai@gmail.com. Welcome technical discussions and exchange of links.

Original source:

Http://www.eygle.com/special/NLS_CHARACTER_SET_07.htm