Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2002
    Posts
    2

    Unanswered: Unable to open Database

    Hi all,
    When I start my database, the database gets mounted but says
    "ORA-12701: CREATE DATABASE character set is not known ".

    When I logged into the database previously , I changed the NLS_CHARACTERSET and NLS_CHARACTERSET values in prop$ table to UTF8.

    How should I change the characterset?

    Thanks in advance

    Balaji

  2. #2
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Balaji,

    Please see below:

    The method uses two commands, which are documented in the Oracle8i
    National Language Support Guide:

    ALTER DATABASE [<db_name>] CHARACTER SET <new_character_set>
    ALTER DATABASE [<db_name>] NATIONAL CHARACTER SET <new_NCHAR_character_set>

    The database name is optional. The character set name should be specified
    without quotes, for example:

    ALTER DATABASE CHARACTER SET WE8ISO8859P1

    To change the database character set perform the following steps.
    Note that some of them have been erroneously omitted from the Oracle8i
    documentation:

    1. Make sure the parallel_server parameter in INIT.ORA is set to false
    or it is not set at all.

    2. Execute the following commands in Server Manager (svrmgrl):

    SVRMGR> SHUTDOWN IMMEDIATE; -- or NORMAL

    <do a full database backup>

    SVRMGR> STARTUP MOUNT;
    SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION;
    SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
    SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
    SVRMGR> ALTER DATABASE OPEN;
    SVRMGR> ALTER DATABASE CHARACTER SET <new_character_set>;
    SVRMGR> SHUTDOWN IMMEDIATE; -- OR NORMAL
    SVRMGR> STARTUP RESTRICT;

    3. Restore the parallel_server parameter in INIT.ORA, if necessary.

    4. Execute the following commands in Server Manager:

    SVRMGR> SHUTDOWN IMMEDIATE; -- OR NORMAL
    SVRMGR> STARTUP;

    The double restart is necessary because of a SGA initialization bug,
    fixed in Oracle9i.


    To change the national character set replace the ALTER DATABASE CHARACTER
    SET command with ALTER DATABASE NATIONAL CHARACTER SET. You can issue both
    commands together if you wish.

    The ALTER DATABASE [NATIONAL] CHARACTER SET command will return:

    ORA-01679: database must be mounted EXCLUSIVE and not open to activate

    - if you do not enable restricted session
    - if you startup the instance in PARALLEL/SHARED mode
    - if you do not set the number of queue processes to 0
    - if you do not set the number of AQ time manager processes to 0
    - if anybody is logged in apart from you.

    This error message is misleading. The command requires the database to be
    open but only one session, the one executing the command, is allowed.

    The above method will only work if the old character set is US7ASCII (or if
    you change the character set to itself). If the old character set is neither
    US7ASCII nor equal to the new character set, the ALTER DATABASE [NATIONAL]
    CHARACTER SET command will return:

    - in Oracle 8.1.5 and above:

    ORA-12712: new character set must be a superset of old character set

    - in Oracle 8.0.5 and 8.0.6:

    ORA-12710: new character set must be a superset of old character set

    - in Oracle 8.0.3 and 8.0.4:

    ORA-24329: invalid character set identifier

  3. #3
    Join Date
    Jan 2003
    Posts
    4
    I got the same issue after I changed oracle 8i.17's nls_nchar_characterset to an wrong characterset name.
    And I followed your steps,but blocked at here:
    SVRMGR> STARTUP MOUNT;
    SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION; -->OK
    SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; -->OK
    SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0; -->OK
    SVRMGR> ALTER DATABASE OPEN;
    ORA-12701: CREATE DATABASE character set is not known

    Why?
    Thank you for any reply
    holywen

    Originally posted by Breen
    Hi Balaji,

    Please see below:

    The method uses two commands, which are documented in the Oracle8i
    National Language Support Guide:

    ALTER DATABASE [<db_name>] CHARACTER SET <new_character_set>
    ALTER DATABASE [<db_name>] NATIONAL CHARACTER SET <new_NCHAR_character_set>

    The database name is optional. The character set name should be specified
    without quotes, for example:

    ALTER DATABASE CHARACTER SET WE8ISO8859P1

    To change the database character set perform the following steps.
    Note that some of them have been erroneously omitted from the Oracle8i
    documentation:

    1. Make sure the parallel_server parameter in INIT.ORA is set to false
    or it is not set at all.

    2. Execute the following commands in Server Manager (svrmgrl):

    SVRMGR> SHUTDOWN IMMEDIATE; -- or NORMAL

    <do a full database backup>

    SVRMGR> STARTUP MOUNT;
    SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION;
    SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
    SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
    SVRMGR> ALTER DATABASE OPEN;
    SVRMGR> ALTER DATABASE CHARACTER SET <new_character_set>;
    SVRMGR> SHUTDOWN IMMEDIATE; -- OR NORMAL
    SVRMGR> STARTUP RESTRICT;

    3. Restore the parallel_server parameter in INIT.ORA, if necessary.

    4. Execute the following commands in Server Manager:

    SVRMGR> SHUTDOWN IMMEDIATE; -- OR NORMAL
    SVRMGR> STARTUP;

    The double restart is necessary because of a SGA initialization bug,
    fixed in Oracle9i.


    To change the national character set replace the ALTER DATABASE CHARACTER
    SET command with ALTER DATABASE NATIONAL CHARACTER SET. You can issue both
    commands together if you wish.

    The ALTER DATABASE [NATIONAL] CHARACTER SET command will return:

    ORA-01679: database must be mounted EXCLUSIVE and not open to activate

    - if you do not enable restricted session
    - if you startup the instance in PARALLEL/SHARED mode
    - if you do not set the number of queue processes to 0
    - if you do not set the number of AQ time manager processes to 0
    - if anybody is logged in apart from you.

    This error message is misleading. The command requires the database to be
    open but only one session, the one executing the command, is allowed.

    The above method will only work if the old character set is US7ASCII (or if
    you change the character set to itself). If the old character set is neither
    US7ASCII nor equal to the new character set, the ALTER DATABASE [NATIONAL]
    CHARACTER SET command will return:

    - in Oracle 8.1.5 and above:

    ORA-12712: new character set must be a superset of old character set

    - in Oracle 8.0.5 and 8.0.6:

    ORA-12710: new character set must be a superset of old character set

    - in Oracle 8.0.3 and 8.0.4:

    ORA-24329: invalid character set identifier

  4. #4
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Holywen,
    Did you do a backup before you changed the character set?
    Breen.

  5. #5
    Join Date
    Jan 2003
    Posts
    4
    Hi Breen,
    I didn't do a backup before I change the character set.
    Is it possible to change the character set back?
    holywen

  6. #6
    Join Date
    Jan 2003
    Location
    beijing
    Posts
    3

    Unhappy hi

    I meet this question before,but now i resolve it

  7. #7
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Holywen,
    I had to go to my backup after I encountered trouble.
    Sorry, but maybe lixiaosi could mention how he resolved it.
    Regards,
    Breen.

  8. #8
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    First, updating sys.prop$ is NOT SUPPORTED method. You should ALWAYS use - ALTER DATABASE CHARACTER SET .... method. You also have to make sure:

    - you follow the note: 13856.1 on Metalink exactly
    - you take a backup first
    - the character set name is in uppercase
    - there are no typo errors in the character set name as they will prevent the database from starting up
    - you check that the new character set is recognized by the database; check V$NLS_VALID_VALUES

    If you cannot open the database, how can you change the character set ?? The only option is to restore from either backup or export.. Pitty but true ..

    Hope that helps,

    clio_usa - OCP - DBA


  9. #9
    Join Date
    Jan 2003
    Posts
    4
    Thanks all for your kindly help!
    I will backup my database first before any big change.

    But why the database should be opened before we change it's characterset?
    Why it will not pickup a default characterset if the current characterset is a invalid one?

    Thanks again!

  10. #10
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Easy answers:

    But why the database should be opened before we change it's characterset?
    Because, changing the CS involves changes to the data dictionary tables (sys.prop$ is one of them). As you know, when db is mounted you cannot access the data dictionary of any other table since the datafiles are offline. Before the control files headers are matched with the actuall datafile headers (means no recovery is neccessary) to changes to the datafiles is allowed. So then when this CS change is going to be recorded?? - in flat OS files. No...




    Why it will not pickup a default characterset if the current characterset is a invalid one?
    Because you as srivary01 did this:

    When I logged into the database previously , I changed the NLS_CHARACTERSET and NLS_CHARACTERSET values in prop$ table to UTF8
    Forcefully updated the sys.prop$ table to wrong value. Yes, Oracle could have written an BEFORE INSERT trigger to check the :new value against allowed values in V$NLS_VALID_VALUES table, but hey - can they warn you - IT IS NOT SUPPORTED METHOD, CHECK THE NEW CS AGAINST V$NLS_VALID_VALUES view... There is so much they can do.

    Bottom line you stuck wrong value in this table, and now your db doesn't recognize it on startup, and couldn't be opened.

    Look at the bright side of the problem: You learned what you shouldn't do, and I hope you will take at least TWO backups next time, validate them twice and export the full db just in case something goes wrong :-)))


    Hope that helps,

    clio_usa - OCP - DBA


  11. #11
    Join Date
    Jan 2003
    Posts
    4
    Thank you again for your answer!
    Actually I didn't know modify props$ is a NOT SUPPORTED METHOD.Now I know it
    And I didn't know modify the Character set is a dangerous thing,which can make the database not openable.That's a big lesson,hehe

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •