configuring Csscan in 10g and 11g (Database Character Set Scanner)_745809.1
Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner) (Doc ID 745809.1)
In this Document
Applies to:Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.4 [Release 10.1 to 11.2]Oracle Database - Standard Edition - Version 10.1.0.2 to 11.2.0.4 [Release 10.1 to 11.2] Information in this document applies to any platform. Goal
Installing Csscan (Database Character Set Scanner) for 10g and 11g and checking that it works properly. Solution1) What is Csscan (Database Character Set Scanner) ?
Csscan is a SCAN tool that allows you to see the impact of a database character set change or assist you to correct a incorrect database NLS_CHARACTERSET setup. 2) What versions of Csscan exist for 10g and 11g?
There are functionality enhancements who give a higher csscan version number:
For installing Csscan in 8i and 9i please see Note 458122.1 Installing and configuring Csscan in 8i and 9i (Database Character Set Scanner).
Unlike for 8i and 9i there are no Csscan downloads on Oracle.com for 10g and up. 3)Installing CsscanCsscan stores internal data in the database when running, the schema for the csscan tables is called CSMIG and is created using the csminst.sql script found in $ORACLE_HOME/rdbms/admin.
In general it's adviced to run Csscan from the database home using a local connection.
Note that it's possible to run Csscan from a client, but this client needs to be the same base version as the database home. (= Use a 10.2 client for 10.2 database, use 11.1 client for 11.1 database etc) When running csscan from a client make sure to run the Csminst.sql from the client home and not from the database home, this is to avoid problems when the client is a higer (or lower) patchset than the database. 3)A) Installing Csscan in Oracle RDBMS 10.1.0.x , 10.2.0.x and 11.1.0.x
For 11.1.0.6 please do install the patch for Bug 6460895 CSSCAN IS JUST HAGNING FROM LAST 60 + HRS IN 11GR1 UPGRADED DATABASE.
The 11.1.0.6 (and 10.2.0.3) Patch 6460895 is available for some platforms. Apply this patch manually, see Note 737155.1 Not Be Able To Patch 6460895 Error oracle.rdbms.csmig. Run csminst.sql AFTER applying this patch as the patch has an updated csminst.sql. The fix is included in the 11.1.0.7 (or higher) and 10.2.0.4 (or higher) patchset. A workaround if the 11.1.0.6 patch is not available for your platform is to install a 11.1.0.6 client and then apply 11.1.0.7 (patch 6890831) on this client. After this run the csminst.sql and csscan from the 11.1.0.7 client . 10.2 is less impacted but going to 10.2.0.4 may also speed up the initialisation of Csscan.
The default tablespace is SYSTEM, if you plan to run csscan against a big database then we advice to create a separate tablespace and change the $ORACLE_HOME/rdbms/admin/csminst.sql script to use this tablespace.
alter user csmig default tablespace SYSTEM
/
The amount of space needed depends mainly on the amount of exceptions found ( convertible and/or lossy data) and is impossible to predict upfront. It's useful to use on the first run of csscan a tablespace that is limited in size to avoid any disk space issues.
grant READ on directory log_file_dir to system
/ grant READ on directory data_file_dir to system /
These grants are not needed, this is removed from Csminst.sql in 11.2 and 10.2.0.5. You can safely remove these lines or revoke the grants if needed. Csscan also does not use those directory's, so if you have an "ORA-22930 directory does not exist" running Csminst.sql this can be ignored.
set oracle_sid=
sqlplus /nolog SQL> conn / as sysdba SQL> set TERMOUT ON SQL> set ECHO ON SQL> spool csminst.log SQL> @?/rdbms/admin/csminst.sql The password for the CSMIG user will be asked when running the csminst.sql script, this can be anything and we suggest to lock the CSMIG account . The actual csmig login is not used by Csscan.
SQL> conn / as sysdba
SQL> alter user csmig account lock; Check the csminst.log for errors. 3)B) Installing Csscan in Oracle RDBMS 11.2.0.x
The default tablespace is SYSTEM, if you plan to run csscan against a big database then we advice to create a separate tablespace and change the $ORACLE_HOME/rdbms/admin/csminst.sql script to use this tablespace.
alter user csmig default tablespace SYSTEM quota unlimited on SYSTEM
/
The amount of space needed depends mainly on the amount of exceptions found ( convertible and/or lossy data) and is impossible to predict upfront. It's useful to use on the first run of csscan a tablespace that is limited in size to avoid any disk space issues.
set oracle_sid=
sqlplus /nolog SQL> conn / as sysdba SQL> set TERMOUT ON SQL> set ECHO ON SQL> spool csminst.log SQL> -- note the drop user SQL> drop user csmig cascade; SQL> @?/rdbms/admin/csminst.sql Check the csminst.log for errors. The Csmig user is by default locked. 4) Checking that Csscan is working properly:
Set the oracle_sid and then run Csscan with this one table scan "test" run:
$ csscan TABLE='(SYS.SQL_VERSION$)' FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000
On windows platforms:
C:\>csscan TABLE=(SYS.SQL_VERSION$) FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000
If the user is in the OS dba group then enter "/ as sysdba" (without the qoutes) as username at the prompt, hit enter at the password prompt. Other wise provide the sysdba password at the password prompt.
D:\>csscan TABLE=(SYS.SQL_VERSION$) FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000
Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Tue Oct 28 14:58:09 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. Username: / as sysdba Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, Oracle Label Security, OLAP and Data Mining options Enumerating table to scan... . process 1 scanning SYS.SQL_VERSION$[AAAAIDAABAAAA/xAAA] Creating Database Scan Summary Report... Creating Individual Exception Report... Scanner terminated successfully.
In Csscan documentation and help screens you see often to connect with Csscan as "system".
Please use "/ as sysdba" instead, do not use "system" as this will lead to errors. 5) Once Csscan is working properly.
To know what the output of Csscan means or information on the different options/parameters of csscan please see Note 444701.1 Csscan output explained. 6) Things good to know:
* Csscan will NOT update any user data, it's a SCAN tool. 7) What to do if I want to use a later version of Csscan but I cannot upgrade my database?
If you want to use the same Csscan version but need to use a higher patchset then there is no problem using a client with the same base release and patch this client to the patchset release (ex: using an 10.2.0.4 client against an 10.2.0.2 database). Make sure you run the Csminst.sql from the client and not from the database home. 8) Known issues:
In general: when running csscan using OS background jobs please make sure the same settings and environment is used as when running it in a foreground session.
* the Csminst.sql file is doing two grants to SYSTEM
grant READ on directory log_file_dir to system
/ grant READ on directory data_file_dir to system /
* Note 762911.1 Csscan Shows Inconsistent Results for Lossy CLob Data . Fixed in 10.2.0.5 and 11.2.0.1
* Bug 9948524 CSSCAN DOES NOT REPORT INVALID CODEPOINTS FOR MSWIN AND ISO8859 CHARSETS
Data Dictionary Tables:
USER.TABLE Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- XDB.XDB$COMPLEX_TYPE 0 0 1 -------------------------------------------------- ---------------- ---------------- ---------------- * When XDB is in use one may also see ORA-22337: the type of accessed object has been evolved CSS-00144: failed to scan table XDB.XDB$SIMPLE_TYPE ORA-22337: the type of accessed object has been evolved CSS-00144: failed to scan table XDB.XDB$CONFIG
Both tables XDB.XDB$SIMPLE_TYPE and XDB.XDB$CONFIG do not contain text data which are important for changing the database characterset.
CSS-00115: invalid character set name
then
ORA-01775: looping chain of synonyms
CSS-00142: failed to get table information CSS-00140: failed to scan table (tid=1, oid=35515) or ORA-01775: looping chain of synonyms ORA-24374: define not done before fetch or execute and fetch CSS-00166: failed to report convertible data distribution
then check the tablespace used for the csmig user/objects (default tablespace is SYSTEM) , most likely this is running out of space.
ORA-22925: operation would exceed maximum size allowed for a LOB value
Apply patch 5879179 for bug 5879179 ORA-22925 from CSSCAN when reading CLOB data
Creating Database Scan Summary Report...
ORA-1455: converting column overflows integer datatype failed to report overall summary failed to create scan report Scanner terminated successfully.
This is bug 4414296 fixed in 9.2.0.8, 10.1.0.5, 10.2.0.1
Enumerating table to scan...
. ORA-1455: converting column overflows integer datatype . ORA-1455: converting column overflows integer datatype CSS-00157: failed to retrieve list of tables to scan CSS-00120: failed to enumerate tables to scan . Scanner terminated unsuccessfully
then there is a/are table(s) with more than 2^31-1 blocks
ORA-01031: insufficient privileges
failed to scan table SYS. combined with ORA-01455: converting column overflows integer datatype failed to get table information failed to scan table (tid=6, oid=393924)
then you are most likely using a non-sysdba connection for a FULL=Y scan.
ORA-01455: converting column overflows integer datatype
failed to get table information failed to scan table (tid=6, oid=393924)
Then most likely the table is dropped during the csscan run: Note 245285.1 CSSCAN fails with error "failed to scan table"
CSS-00117 failed to clear previous scan log
Failed to delete all rows from CSM$* tables. This is an internal error. Contact Oracle Customer Support.
Then the fastest way to correct this is to simply re-run Csminst.sql.
Enumerating tables to scan...
ORA-00942: table or view does not exist CSS-00152: failed to enumerate all tables CSS-00120: failed to enumerate tables to scan
Then the wrong cminst.sql was runned. For example using csscan from an 10.2.0.4 client but the Csminst.sql that was used is the one from the 10.2.0.1 database oracle home. Please run the Csminst.sql from the actual csscan home.
. process 2 scanning SCOTT.TEST[AAFbYKAAAAAAASCAAA]
ORA-00913: too many values CSS-08888: failed to enumerate indexes to rebuild
Then and older csmig schema is installed in 11.2. For example the Csminst.sql that was used (in the past) is the one from the 11.2.0.1 and the current version is 11.2.0.3. Do a "drop user csmig cascade;" and run the Csminst.sql from the current database home.
. process 3 scanning XDB.SC_PT[AAADjfAAEAAAByIAAA]
ORA-30967: operation directly on the Path Table is disallowed CSS-00144: failed to scan table XDB.SC_PT
this can be ignored - this is Bug 8728894, fixed in 11.2.0.2
Enumerating table to scan...
. process 1 scanning SYS.SQL_VERSION$[AAAAHXAABAAAAQrAAA] ORA-00904: "CNVTYPE": invalid identifier CSS-08888: failed to update conversion type Scanner terminated unsuccessfully.
Then this database was upgraded to 11g and the CSMIG user was not dropped before the 11g Csminst.sql was runned. Follow step 3)B) in this note.
process 1 scanning
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17252115/viewspace-1345529/,如需轉載,請註明出處,否則將追究法律責任。
請登入後發表評論
登入
全部評論
最新文章
|
相關文章
- character_set_database的意義Database
- Unknown initial character set index ‘255‘ received from server. Initial client character set can beIndexServerclient
- AL32UTF8 / UTF8 (Unicode) Database Character Set Implications [ID 788156.1]UnicodeDatabase
- Indexing Mixed-Character Set ColumnsIndex
- 【Mysql】character-set-server引數MySqlServer
- Upgrading from Oracle Database 10g to 11gOracleDatabase
- 【轉】修改Oracle字符集(character set)Oracle
- 啟動PL/SQL Developer 報字元編碼不一致錯誤,Database character setSQLDeveloper字元Database
- mysql:Variable 'character_set_client' can't be set to the value of 'NULL'解決MySqlclientNull
- Oracle 10中修改字符集(character set)Oracle
- Variable 'character_set_client' can't be set to the value of Null的解決方法clientNull
- Oracle Database 10g/11g補丁(Patchset)下載地址OracleDatabase
- [mysql] 批量匯入提示: Variable ‘character_set_client’ can’t be set to the value of ‘NULLMySqlclientNull
- mysql關於字符集character set的總結MySql
- rhel4 mysql5.5 字符集_character setMySql
- 關於create database語句在10g,11g中的不同Database
- MySQL字符集和校對規則(character set & collation)MySql
- mysqlbinlog: unknown variable 'default-character-set=utf8'MySql
- Configuring Solaris IP Multipathing (IPMP) for the Oracle 10g VIPOracle 10g
- oracle 10g flashback databaseOracle 10gDatabase
- Oracle Database 9i/10g/11g程式設計藝術 筆記OracleDatabase程式設計筆記
- 透過Database Link/IMPDP,同步10G、11G資料庫失敗Database資料庫
- MySQL:簡單記錄character_set_server影響引數MySqlServer
- B19306_01 閱讀筆記(character set)筆記
- Configuring non-raw Multipath Devices for Oracle Clusterware 11gdevOracle
- 11g 新特性—— Active Database Duplication for A standby databaseDatabase
- List of Oracle Database Patch Set Updates (PSU)OracleDatabase
- No need to set a listener password in ORACLE 10GOracle 10g
- Scheduler in Oracle Database 10g(轉)OracleDatabase
- Rename Tablespace in Oracle database 10gOracleDatabase
- Oracle 11G Duplicate DatabaseOracleDatabase
- 關於MySQL中的8個 character_set 變數說明MySql變數
- Qt #pragma execution_character_set("utf-8") 執行字符集QT
- Csscan&LcsscanCSS
- Oracle工具——csscanOracleCSS
- oracle exp&imp之IMP-00037: Character set marker unknownOracle
- Tablespace Management Enhancements in Oracle Database 10gOracleDatabase
- Oracle 10g新增DROP DATABASE命令Oracle 10gDatabase