在9i和10g还有11g的数据库中,如果我们update了props$数据字典中的多个字符集的值,造成数据库无法启动,除了用bbed modify数据块外,oracle还自带了一个工具lbuilder,可以用来恢复。
下面介绍下这个工具的使用。
数据库版本和原来的字符集:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production CORE 9.2.0.3.0 Production TNS for Linux: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production SQL> select * from nls_database_parameters; PARAMETER VALUE ------------------------------------------------------------ --------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET WE8ISO8859P1 NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 9.2.0.4.0 20 rows selected. SQL> SQL> |
下面我们将字符集update成错误的字符集:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> update props$ set value$='zhs6gbk' 2 where name='NLS_CHARACTERSET'; 1 row updated. SQL> SQL> SQL> update props$ set value$='zhs6gbk' 2 where name='NLS_NCHAR_CHARACTERSET'; 1 row updated. SQL> commit; Commit complete. SQL> |
重启数据库的时候,我们发现数据库已经启动不了了:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> SQL> startup; ORACLE instance started. Total System Global Area 370218244 bytes Fixed Size 451844 bytes Variable Size 167772160 bytes Database Buffers 201326592 bytes Redo Buffers 667648 bytes Database mounted. ORA-01092: ORACLE instance terminated. Disconnection forced |
在alertlog中可以看到关于字符集的报错:ORA-12701: CREATE DATABASE character set is not known
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
…… Undo Segment 8 Onlined Undo Segment 9 Onlined Undo Segment 10 Onlined Successfully onlined Undo Tablespace 1. Wed Jan 16 23:46:44 2008 SMON: enabling tx recovery Wed Jan 16 23:46:44 2008 Errors in file /oracle/admin/oradg/udump/oradg_ora_4550.trc: ORA-12701: CREATE DATABASE character set is not known Wed Jan 16 23:46:44 2008 Error 12701 happened during db open, shutting down database USER: terminating instance due to error 12701 Wed Jan 16 23:46:44 2008 Errors in file /oracle/admin/oradg/bdump/oradg_dbw0_4528.trc: ORA-12701: CREATE DATABASE character set is not known Instance terminated by USER, pid = 4550 ORA-1092 signalled during: ALTER DATABASE OPEN... |
此时数据库已经无法open,只能到mount状态。因此无法在用update的方式更新回正确的数据。怎么办?
oracle提供了一个lbuilder(locale builder)可以用来修改字符集,启动数据库。从而再次update回正确的props$表。lbuilder和oracle的安装类似,需要有xwindows环境运行。
选择原来能正确启动的字符集:
注:原来正确能启动的字符集,我们可以中alertlog中观察到:
1 2 3 4 5 6 7 |
…… Wed Jan 16 00:47:43 2008 SMON: enabling tx recovery Wed Jan 16 00:47:43 2008 Database Characterset is WE8ISO8859P1 <------------------注意此处!! replication_dependency_tracking turned off (no async multimaster replication found) Completed: ALTER DATABASE OPEN |
把这个字符集的名字改成update之前错误update成的名字:
update成错误的字符集,我们在alertlog中也能看到:
1 2 3 4 5 |
…… SMON: enabling tx recovery Thu Jan 17 00:25:42 2008 Updating character set in controlfile to ZHS6GBK <------------------注意此处!! replication_dependency_tracking turned off (no async multimaster replication found) |
选择$ORACLE_HOME/ocommon/nls/data:
重新启动数据库:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
[oracle@standby2 ~]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Thu Jan 17 00:25:28 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> SQL> shutdown immediate ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux Error: 2: No such file or directory SQL> SQL> SQL> SQL> startup ORACLE instance started. Total System Global Area 370218244 bytes Fixed Size 451844 bytes Variable Size 167772160 bytes Database Buffers 201326592 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> |
记得将props$表的字符集update回去:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SQL> update props$ set value$='WE8ISO8859P1' where name='NLS_CHARACTERSET'; 1 row updated. SQL> update props$ set value$='AL16UTF16' where name='NLS_NCHAR_CHARACTERSET'; 1 row updated. SQL> commit; Commit complete. SQL> SQL> SQL> SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> SQL> startup ORACLE instance started. Total System Global Area 370218244 bytes Fixed Size 451844 bytes Variable Size 167772160 bytes Database Buffers 201326592 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> SQL> |
最后在唠叨一句:没啥把握,在没有oracle官方确认的情况下,千万别在生产系统上随便update任何字典表!
2条评论
学习了,好强大!!
oracle10.2 update了props$数据字典中的多个字符集的值,造成数据库无法启动。按照这个方法。
改完后选择Generate NLB:
选择$ORACLE_HOME/ocommon/nls/data: 我的目录是/u01/app/oracle/product/10.2.0/db_1/nls/data 点击保存后报错:说 cannot save based on unicode definition.