Updates

    Here are the simple steps to migrate the default Database Character Set:

    Database Version: Oracle 10g Database R2 (10.2.0.3)
    Source Character Set: AL32UTF8
    Target Character Set: WE8ISO8859P1

    Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

    Take a full database backup:

    Sometimes, 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. The database can be reconstructed later using the backup taken before this action, if something goes wrong.

    Note: We have taken the cold backup of the database and also performed the full database export using Datapump utility (expdp/impdp). It’s up to you, how and what type of full backup you prefer for your database i.e. Cold/Hot Backup/RMAN. So, you should recreate the database using this backup when the conversion fails.

    Start up the database:
    SQL> startup;
    ORACLE instance started.

    Total System Global Area 2097152000 bytes
    Fixed Size 2074120 bytes
    Variable Size 771754488 bytes
    Database Buffers 1308622848 bytes
    Redo Buffers 14700544 bytes
    Database mounted.
    Database opened.

    Run the Database Character Set Scanner (CSSCAN) utility.
    This CSSCAN Utility executable is under the $ORACLE_HOME/bin directory.

    $ csscan \"sys/password@mydb as sysdba\" full=y
    Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Sat Sep 13 15:58:28 2008
    Copyright (c) 1982, 2005, Oracle. All rights reserved.
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    Current database character set is AL32UTF8.
    Enter new database character set name: > WE8ISO8859P1Enter array fetch buffer size: 1024000 >
    Enter number of scan processes to utilize(1..32): 1 > 32
    Enumerating tables to scan...
    . process 1 scanning SYS.SOURCE$[AAAABIAABAAAAIRAAA]
    . process 2 scanning SYS.SOURCE$[AAAABIAABAAAHCJAAA]
    . process 3 scanning SYS.ATTRIBUTE$[AAAAC1AABAAAAURAAA]
    …………………………………………………………………………….
    …………………………………………………………………………….
    …………………………………………………………………………….

    Creating Database Scan Summary Report...
    Creating Individual Exception Report...
    Scanner terminated successfully.
    $

    For any help on CSSCAN utility i.e. keyword, prompt, and description, use the following command.

    $ csscan help=y
    Run the CSALTER script.

    Once the Database Character Set Scanning has completed successfully, the database must be opened in restricted mode, because no normal user should allow to access the database during this task is being performed. So you can run the CSALTER script as the SYS user. The location of the CSALTER Script is "$ORACLE_HOME/rdbms/admin/csalter.plb".

    Shut Down the Database
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

    Start Up the Database in Restricted Mode.
    SQL> startup restrict;
    ORACLE instance started.

    Total System Global Area 2097152000 bytes
    Fixed Size 2074120 bytes
    Variable Size 771754488 bytes
    Database Buffers 1308622848 bytes
    Redo Buffers 14700544 bytes
    Database mounted.
    Database opened.

    Run the csalter.plb script

    SQL> @?/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 validility...
    begin converting system objects
    12 rows in table SYS.WRI$_ADV_RATIONALE are converted
    1 row in table MDSYS.SDO_GEOR_XMLSCHEMA_TABLE is converted
    80 rows in table SYS.METASTYLESHEET are converted
    17 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
    79 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
    4 rows in table SYS.RULE$ are converted
    78 rows in table MDSYS.SDO_STYLES_TABLE are converted
    10576 rows in table SYS.WRH$_SQL_PLAN are converted
    21 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted
    19 rows in table SYS.WRI$_ADV_ACTIONS are converted
    4 rows in table MDSYS.SDO_XML_SCHEMAS are converted
    2308 rows in table MDSYS.SDO_COORD_OP_PARAM_VALS are converted
    578 rows in table SYS.WRI$_ADV_OBJECTS are converted
    789 rows in table SYS.WRH$_SQLTEXT are converted

    PL/SQL procedure successfully completed.

    Alter the database character set...
    CSALTER operation completed, please restart database

    PL/SQL procedure successfully completed.

    0 rows deleted.

    Function dropped.

    Function dropped.

    Procedure dropped.

    Shut Down the Database

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

    Start Up the Database
    SQL> startup;
    ORACLE instance started.

    Total System Global Area 2097152000 bytes
    Fixed Size 2074120 bytes
    Variable Size 771754488 bytes
    Database Buffers 1308622848 bytes
    Redo Buffers 14700544 bytes
    Database mounted.
    Database opened.
    SQL>

    Make sure the changes:

    SQL> SELECT * FROM nls_database_parameters;

    0 comments:

    Post a Comment

    Copyright © ORACLE ONLINE DBA
    Developed By Pavan Yennampelli