Oracle常见问题处理.doc

Oracle常见问题处理我们在管理Oracle数据库过程中，会遇到一些比较头疼的问题，如：字符集问题、移植数据库、大对象备份等。下面是一些常见问题的处理方法，或许能解决你的烦恼。字符集FAQ一、什么是oracle字符集Oracle字符集是一个字节数据的解释的符号集合,有大小之分,有相互的包容关系。ORACLE 支持国家语言的体系结构允许你使用本地化语言来存储，处理，检索数据。它使数据库工具，错误消息，排序次序，日期，时间，货币，数字，和日历自动适应本地化语言和平台。影响oracle数据库字符集最重要的参数是NLS_LANG参数。它的格式如下:NLS_LANG = language_territory.charset它有三个组成部分(语言、地域和字符集)，每个成分控制了NLS子集的特性。其中:Language 指定服务器消息的语言，territory 指定服务器的日期和数字格式，charset 指定字符集。如:AMERICAN _ AMERICA. ZHS16GBK从NLS_LANG的组成我们可以看出，真正影响数据库字符集的其实是第三部分。所以两个数据库之间的字符集只要第三部分一样就可以相互导入导出数据，前面影响的只是提示信息是中文还是英文。二、如何查询Oracle的字符集很多人都碰到过因为字符集不同而使数据导入失败的情况。这涉及三方面的字符集，一是oracel server端的字符集，二是oracle client端的字符集;三是dmp文件的字符集。在做数据导入的时候，需要这三个字符集都一致才能正确导入。数据库服务器字符集select * from nls_database_parameters，其来源于props$，是表示数据库的字符集。 客户端字符集环境select * from nls_instance_parameters,其来源于v$parameter， 表示客户端的字符集的设置，可能是参数文件，环境变量或者是注册表会话字符集环境 select * from nls_session_parameters，其来源于v$nls_parameters，表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。1、查询oracle server端的字符集有很多种方法可以查出oracle server端的字符集，比较直观的查询方法是以下这种:SQLselect userenv(language) from dual;结果类似如下:AMERICAN _ AMERICA. ZHS16GBK2、如何查询dmp文件的字符集用oracle的exp工具导出的dmp文件也包含了字符集信息，dmp文件的第2和第3个字节记录了dmp文件的字符集。如果dmp文件不大，比如只有几M或几十M，可以用UltraEdit打开(16进制方式)，看第2第3个字节的内容，如0354，然后用以下SQL查出它对应的字符集：SQL select nls_charset_name(to_number(0354,xxxx) from dual;ZHS16GBK如果dmp文件很大，比如有2G以上(这也是最常见的情况)，用文本编辑器打开很慢或者完全打不开，可以用以下命令(在unix主机上)：cat exp.dmp |od -x|head -1|awk print $2 $3|cut -c 3-6然后用上述SQL也可以得到它对应的字符集。3、查询oracle client端的字符集这个比较简单。在windows平台下，就是注册表里面相应OracleHome的NLS_LANG。还可以在dos窗口里面自己设置，比如：set nls_lang=AMERICAN_AMERICA.ZHS16GBK这样就只影响这个窗口里面的环境变量。在unix平台下，就是环境变量NLS_LANG。$echo $NLS_LANGAMERICAN_AMERICA.ZHS16GBK如果检查的结果发现server端与client端字符集不一致，请统一修改为同server端相同的字符集。三、修改oracle的字符集上文说过，oracle的字符集有互相的包容关系。如us7ascii就是zhs16gbk的子集,从us7ascii到zhs16gbk不会有数据解释上的问题,不会有数据丢失。在所有的字符集中utf8应该是最大,因为它基于unicode,双字节保存字符(也因此在存储空间上占用更多)。一旦数据库创建后，数据库的字符集理论上讲是不能改变的。因此，在设计和安装之初考虑使用哪一种字符集十分重要。根据Oracle的官方说明，字符集的转换是从子集到超集受支持,反之不行。如果两种字符集之间根本没有子集和超集的关系，那么字符集的转换是不受oracle支持的。对数据库server而言，错误的修改字符集将会导致很多不可测的后果，可能会严重影响数据库的正常运行，所以在修改之前一定要确认两种字符集是否存在子集和超集的关系。一般来说，除非万不得已，我们不建议修改oracle数据库server端的字符集。特别说明，我们最常用的两种字符集ZHS16GBK和ZHS16CGB231280之间不存在子集和超集关系，因此理论上讲这两种字符集之间的相互转换不受支持。1、修改server端字符集(不建议使用)在oracle 8之前，可以用直接修改数据字典表props$来改变数据库的字符集。但oracle8之后，至少有三张系统表记录了数据库字符集的信息，只改props$表并不完全，可能引起严重的后果。正确的修改方法如下:$sqlplus /nologSQLconn / as sysdba;若此时数据库服务器已启动，则先执行SHUTDOWN IMMEDIATE命令关闭数据库服务器，然后执行以下命令:SQLSTARTUP MOUNT;SQLALTER SYSTEM ENABLE RESTRICTED SESSION;SQLALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;SQLALTER SYSTEM SET AQ_TM_PROCESSES=0;SQLALTER DATABASE OPEN;SQLALTER DATABASE CHARACTER SET ZHS16GBK;SQLALTER DATABASE national CHARACTER SET ZHS16GBK;SQLSHUTDOWN IMMEDIATE;SQLSTARTUPStartup nomount;Alter database mount exclusive;Alter system enable restricted session;Alter system set job_queue_processes=0;Alter database open;Alter database character set zhs16cgb231280;ALTER DATABASE character set INTERNAL_USE zhs16cgb231280;# 使用INTERNAL_USE可以跳过超集的检查，ALTER DATABASE character set INTERNAL_USE2、修改dmp文件字符集上文说过，dmp文件的第2第3字节记录了字符集信息，因此直接修改dmp文件的第2第3字节的内容就可以骗过oracle的检查。这样做理论上也仅是从子集到超集可以修改，但很多情况下在没有子集和超集关系的情况下也可以修改，我们常用的一些字符集，如US7ASCII，WE8ISO8859P1，ZHS16CGB231280，ZHS16GBK基本都可以改。因为改的只是dmp文件，所以影响不大。具体的修改方法比较多，最简单的就是直接用UltraEdit修改dmp文件的第2和第3个字节。比如想将dmp文件的字符集改为ZHS16GBK，可以用以下SQL查出该种字符集对应的16进制代码:SQL select to_char(nls_charset_id(ZHS16GBK), xxxx) from dual;0354然后将dmp文件的2、3字节修改为0354即可。如果dmp文件很大，用ue无法打开，就需要用程序的方法了。网上有人用java存储过程写了转换的程序(用java存储过程的好处是通用性教好，缺点是比较麻烦)。我在windows下测试通过。但要求oracle数据库一定要安装JVM选项。有兴趣的朋友可以研究一下程序代码。字符集不同数据库移植在字符集不同的情况下，如何移植数据库？首先，建议使用中间数据库过渡。假设：原来机器（SRC）： HOST:oradb1（192.168.1.10） 数据库字符集:american_america.US7ASCII Tablespace:itemlib User:itemlib/lib中间机器（TMP）： HOST:oradb2（192.168.1.20） 数据库字符集:american_america.zhs16cgb231280 Tablespace:itemlib User:itemlib/lib目标机器（DST）： HOST:oradb（192.168.1.30） 数据库字符集:american_america.zhs16cgb231280 Tablespace:itemlib User:itemlib/lib1、在SRC上导出原数据库：exp itemlib/lib file=lib001.dmp2、在TMP上新建Tablespace（itemlib)、User(itemlib/lib)3、在TMP上用Oracle的system用户登录，并执行命令： create database character set US7ASCII; 出错不管。4、退出到Shell环境，并修改环境变量： export NLS_LANG=american_america.US7ASCII5、在TMP上导入数据库： imp itemlib/lib file=lib0001.dmp touser=itemlib6、在TMP上重启Oracle：dbshut、dbstart7、在TMP上退出Shell，重新登录8、在TMP上导出数据库： exp itemlib/lib file=lib0002.dmp direct=y9、在DST上，导入数据库： imp itemlib/lib file=lib0002.dmp touser=itemlib备份大对象Oracle数据库中的大对象，如：BLOB，缺省是无法通过exp/imp备份的，如果需要备份大对象，可以通过以下方法变为可行：执行：$ORACLE_HOME/rdbms/admin/catmetx.sql $ORACLE_HOME/rdbms/admin/utlrp.sql即可EXP和IMP大对象，如：BLOBtemp表空间满正常来说，在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段a的。但有些有侯我们则会遇到临时段没有被释放，TEMP表空间几乎满的状况，甚至是我们重启了数据库仍没有解决问题。这个问题在论坛中也常被网友问到，下面我总结一下，给出几种处理方法。法一、重启库库重启时，Smon进程会完成临时段释放,TEMP表空间的清理操作，不过很多的时侯我们的库是不允许down的,所以这种方法缺少了一点的应用机会，不过这种方法还是很好用的。法二、Metalink给出的一个方法修改一下TEMP表空间的storage参数，让Smon进程观注一下临时段，从而达到清理和TEMP表空间的目的。SQLalter tablespace temp increase 1;SQLalter tablespace temp increase 0;法三、我常用的一个方法,具体内容如下：1、 使用如下语句a查看一下认谁在用临时段SELECT username,sid,serial#,sql_address,machine,program,tablespace,segtype,contentsFROM v$session se,v$sort_usage suWHERE se.saddr=su.session_addr2、那些正在使用临时段的进程SQLAlter system kill session sid,serial#;3、把TEMP表空间回缩一下SQLAlter tablespace TEMP coalesce;法四、使用诊断事件的一种方法,也是被我认为是“杀手锏”的一种方法1、 确定TEMP表空间的ts#SQLselect ts#, name from sys.ts$ ;TS# NAME-0 SYSYEM1 RBS2 USERS3* TEMP4 TOOLS5 INDX6 DRSYS2、 执行清理操作SQLalter session set events immediate trace name DROP_SEGMENTS level 4 ;说明：temp表空间的TS# 为 3*, So TS#+ 1= 4其它：1、 出现如上问题的原因我认为可能是由于大的排序超出了TEMP表空间的空间允许范围引起的。也可能包含着其它的异常的因素。2、 观注TEMP等这些空间的状态是Dba日常职责之一，我们可以通过Toad、Object Browser等这些工具办到，也可以用如下的语句：SELECT UPPER(F.TABLESPACE_NAME) 表空间名,D.TOT_GROOTTE_MB 表空间大小(M),D.TOT_GROOTTE_MB - F.TOTAL_BYTES 已使用空间(M),TO_CHAR(ROUND(D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), 990.99) 使用比,F.TOTAL_BYTES 空闲空间(M),F.MAX_BYTES 最大块(M)FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MBFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY 4 DESC切换Temp表空间通过下面步骤可以实现切换数据库的Temp表空间：1. 创建新的TEMP TABLESPACE: create temporary tablespace temp02 tempfile /dbdata/ora9idb/temp02.dbf size 5M autoextend on maxsize 2048M;2. 设置系统新的TEMP TABLESPACE: alter database default temporary tablespace temp02;3. DROP TEMP TABLESPACE: drop tablespace temp including contents and datafiles;查看：v$tempfile dba_temp_files，以确认切换成功。切换Undo表空间通过下面步骤可以实现切换数据库的Undo表空间：1. 查看 UNDO 参数 show parameter undo 设原来的为undotbs12. 查看Rollback Seg Session 状态 select owner,segment_name,tablespace_name,status from dba_rollback_segs order by 4; 原来的undotbs1为ONLINE3. 创建新的UNDO TABLESPACE create undo tablespace undotbs2 datafile /dbdata/ora9idb/undotbs02.dbf size 500M reuse autoextend on;4. 切换 Undo Tablespace alter system set undo_tablespace=undotbs2;5. 查看Rollback Seg Session 状态 select owner,segment_name,tablespace_name,status from dba_rollback_segs order by 4; 原来的undotbs1为OFFLINE, undotbs2为ONLINE6. DROP 原来的UNDOTBS drop tablespace undotbs1 including contents and datafiles;7. 重启数据库后删除原来的undotbs01.dbf文件Oracle数据库监控以下是对Oracle数据库相关运行情况的监控，并针对特定情况进行处理的方法：、如何杀掉已经“死了”的ORACLE进程：1）查看用户session，哪些是 INACTIVE：select paddr,sid,serial#,username,status from v$session;杀死某个session: sid,serial#:alter system kill session sid,serial#;2）查看session为INACTIVE的进程ID：select pid,spid,username from v$process where addr=上面的INACTIVE的session的paddr;3）记录上面的进程号spid, 然后kill掉：kill -9 spid号、如何监控当前数据库谁在运行什么SQL语句：SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;、如何分辨某个用户是从哪台机器登陆ORACLE的：SELECT machine, terminal FROM V$SESSION;、如何查询做比较大的排序的进程：SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;、如何查询做比较大的排序的进程的SQL语句：select /*+ ORDERED */ sql_text from v$sqltext a where a.hash_value = ( select sql_hash_value from v$session b where b.sid = &sid and b.serial# = &serial) order by piece asc、如何知道使用CPU多的用户session：11是cpu used by this session select a.sid, spid, status, substr(a.program, 1, 40) prog, a.terminal, osuser, value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by value desc;、查看undo tablespace 的使用情况：select s.username,u.name from v$transaction t,v$rollstat r,v$rollname u,v$session swhere s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username;以上是一些本人的经验，不当之处，请包涵指正。