Database SOS – 第137页 – Professional Database Recovery Support@+8617813235971

记录一次oer 8102.2处理

1.alert日志

Tue Dec 20 22:09:45 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_27442.trc:
Wed Dec 21 22:10:45 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_32761.trc:
Thu Dec 22 22:11:46 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_5935.trc:
Fri Dec 23 22:12:47 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_11382.trc:

Mnnn performs manageability tasks dispatched to them by MMON. Tasks performed include taking Automatic Workload Repository snapshots and Automatic Database Diagnostic Monitor analysis.
从这个时间点来看，应该是数据库启动GATHER_STATS_JOB收集统计信息时发现这个错误。

2.bas_m000_11382.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      bas
Release:        2.6.9-78.ELsmp
Version:        #1 SMP Wed Jul 9 15:46:26 EDT 2008
Machine:        x86_64
Instance name: BAS
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 11382, image: oracle@bas (m000)
*** ACTION NAME:(Auto-Purge Slave Action) 2011-12-23 22:12:47.074
*** MODULE NAME:(MMON_SLAVE) 2011-12-23 22:12:47.074
*** SERVICE NAME:(SYS$BACKGROUND) 2011-12-23 22:12:47.074
*** SESSION ID:(5465.2033) 2011-12-23 22:12:47.074
oer 8102.2 - obj# 4152, rdba: 0x00401f7c(afn 1, blk# 8060)
kdk key 8102.2:
  ncol: 2, len: 10
  key: (10):  02 c1 0a 06 00 c0 04 dc 00 00
  mask: (4096):
 09 00 00 00 00 fb d1 c0 00 00 00 00 00 70 f8 fe bf 7f 00 00 00 cd 7d 5d 01

oer 8102.<code> - obj# <object id>, rdba: <rdba value>(afn <file#>, blk# <block#>)
kdk key 8102.2:
ncol: <number of columns in the key including the rowid>, len: <key length>
key: (<length>):<hexadecimal value>
obj#:   object_id for the affected index in dba_objects.
rdba:   relative data block address where the key is supposed to be stored in the index.
afn:     absolute file number where the affected index block is stored.
(file_id in dba_data_files, file# in v$datafile).
blk#:   Index block number where the key is supposed to be stored.

出现oer 8102.2的错误，有两种可能:1.坏块,2.表和索引数据不一致

3.找出相关对象

SQL> col object_name for a30
SQL> col owner for a10
SQL> select object_name,owner,object_type
  2  from dba_objects where object_id=4152;
OBJECT_NAME                    OWNER      OBJECT_TYPE
------------------------------ ---------- -------------------
WRI$_SEGADV_OBJLIST_IDX_TS     SYS        INDEX
SQL> select OWNER,TABLE_NAME from dba_indexes
   2 where index_name='WRI$_SEGADV_OBJLIST_IDX_TS';
OWNER      TABLE_NAME
---------- ------------------------------
SYS        WRI$_SEGADV_OBJLIST
SQL> ANALYZE TABLE sys.WRI$_SEGADV_OBJLIST VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE sys.WRI$_SEGADV_OBJLIST VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

4.分析坏块(逻辑/物理)

SQL> ANALYZE INDEX WRI$_SEGADV_OBJLIST_IDX_TS VALIDATE STRUCTURE;
Index analyzed.
SQL> ANALYZE TABLE WRI$_SEGADV_OBJLIST VALIDATE STRUCTURE;
Table analyzed.
[oracle@bas bdump]$ dbv file=/opt/app/oracle/oradata/BAS/system01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Sat Dec 24 21:14:38 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /opt/app/oracle/oradata/BAS/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 552960
Total Pages Processed (Data) : 360156
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 167596
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1961
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 23247
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 2890198330 (2750.2890198330)

检测证明，对象以及对象所属的数据文件，无坏块现象

5.分析表和index不一致

--找出index对应列
SQL> SELECT table_name , column_name from dba_ind_columns
  2  WHERE index_name='WRI$_SEGADV_OBJLIST_IDX_TS' order by table_name;
TABLE_NAME                     COLUMN_NAME
------------------------------ --------------------
WRI$_SEGADV_OBJLIST            TS_ID
--确定对应列是否允许为null
SQL> desc WRI$_SEGADV_OBJLIST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AUTO_TASKID                                        NUMBER
 TS_ID                                              NUMBER
 OBJN                                               NUMBER
 OBJD                                               NUMBER
 STATUS                                             VARCHAR2(40)
 TASK_ID                                            NUMBER
 REASON                                             VARCHAR2(40)
 REASON_VALUE                                       NUMBER
 CREATION_TIME                                      TIMESTAMP(6)
 PROC_TASKID                                        NUMBER
 END_TIME                                           TIMESTAMP(6)
 SEGMENT_OWNER                                      VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
--确认在表中对应列是否有空值
SQL> SELECT /*+ FULL(t1) */ count(TS_ID)
  2   FROM WRI$_SEGADV_OBJLIST t1
  3    WHERE t1.TS_ID IS NULL;
COUNT(TS_ID)
------------
           0
--表比index多数据
SQL> SELECT /*+ FULL(t1) */ TS_ID
  2  FROM WRI$_SEGADV_OBJLIST t1
  3  MINUS
  4  SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID
  5  FROM WRI$_SEGADV_OBJLIST t where  ts_id is not null;
no rows selected
--index中数据条数
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ count(TS_ID)
  2  FROM WRI$_SEGADV_OBJLIST t
  3  where ts_id is not null;
COUNT(TS_ID)
------------
         901
--表中数据条数
SQL> SELECT /*+ FULL(t1) */ count(TS_ID)
  2  FROM WRI$_SEGADV_OBJLIST t1 ;
COUNT(TS_ID)
------------
         937
--index中不同值数量
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */
  2  COUNT(DISTINCT TS_ID)
  3  FROM WRI$_SEGADV_OBJLIST t WHERE TS_ID IS NOT NULL;
COUNT(DISTINCTTS_ID)
--------------------
                   5
--表中不同值数量
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID
  2  FROM WRI$_SEGADV_OBJLIST t WHERE ts_id IS NOT NULL
  3  MINUS
  4  SELECT /*+ FULL(t1) */ TS_ID
  5  FROM WRI$_SEGADV_OBJLIST t1 ;
     TS_ID
----------
         4
--对比可以知道index中的唯一值比表中，这个也就解释了，为什么表中总条数多，
--但是他们两做减法的时候，记录为空
--索引表比表多数据
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID
  2  FROM WRI$_SEGADV_OBJLIST t WHERE ts_id IS NOT NULL
  3  MINUS
  4  SELECT /*+ FULL(t1) */ TS_ID
  5  FROM WRI$_SEGADV_OBJLIST t1 ;
     TS_ID
----------
         4

上面的检测证明:1.表中有索引中无的数据,2.索引中有表中不存在数据

6.解决问题

SQL> alter index WRI$_SEGADV_OBJLIST_IDX_TS rebuild online;
Index altered.
--测试index中总条数
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ count(TS_ID)
  2   FROM WRI$_SEGADV_OBJLIST t
  3    where  ts_id is not null;
COUNT(TS_ID)
------------
         937
--无多余index项(以前唯一值为4的记录已经不存在)
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID
  2      FROM WRI$_SEGADV_OBJLIST t WHERE ts_id IS NOT NULL
  3    MINUS
  4      SELECT /*+ FULL(t1) */ TS_ID
  5      FROM WRI$_SEGADV_OBJLIST t1 ;
no rows selected
--通过上述测试，证明表和index不一致问题解决

通过ROWID找回坏块数据

一.准备环境

C:\Users\XIFENFEI>sqlplus chf/xifenfei
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 12月 23 10:49:52 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create tablespace t_xff datafile 'E:\ORACLE\ORADATA\XFF\t_xff01.dbf'
  2  size 10m autoextend on next 10m maxsize 1g;
表空间已创建。
SQL> create table t_xifenfei tablespace t_xff
  2  as
  3  select * from dba_objects;
表已创建。
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     73286

二.发现坏块
使用ULtraEdit破坏数据(关闭数据库执行)

SQL> select count(*) from t_xifenfei;
select count(*) from t_xifenfei
                     *
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 13, 块号 373)
ORA-01110: 数据文件 13: 'E:\ORACLE\ORADATA\XFF\T_XFF01.DBF'

三.查询坏块相关信息

The "LOW_RID" is the lowest rowid INSIDE the corrupt block:
SELECT dbms_rowid.rowid_create(1,<DATA_OBJECT_ID>,<RFN>,<BL>,0) LOW_RID
from DUAL;
The "HI_RID" is the first rowid AFTER the corrupt block:
SELECT dbms_rowid.rowid_create(1,<DATA_OBJECT_ID>,<RFN>,<BL>+1,0) HI_RID
from DUAL;
SQL> col tablespace_name for a30
SQL> col segment_type for a5
SQL> col owner for a10
SQL> col segment_name for a20
SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2                 FROM dba_extents
  3                WHERE file_id =13
  4  AND 373 between block_id AND block_id + blocks - 1 ;
TABLESPACE_NAME                SEGME OWNER      SEGMENT_NAME
------------------------------ ----- ---------- --------------------
T_XFF                          TABLE CHF        T_XIFENFEI
SQL> SELECT data_object_id
  2            FROM dba_objects
  3   WHERE object_name = 'T_XIFENFEI'  and owner='CHF';
DATA_OBJECT_ID
--------------
         77759
--坏块的最小rowid
SQL> select dbms_rowid.rowid_create(1, 77759,13,373,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAS+/AANAAAAF1AAA
坏块的最大rowid(block+1得到)
SQL> select dbms_rowid.rowid_create(1, 77759,13,374,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAS+/AANAAAAF2AAA

四.根据rowid找回数据

SQL> SELECT /*+ ROWID(A) */ COUNT(*) FROM T_XIFENFEI A
  2  WHERE ROWID>='AAAS+/AANAAAAF2AAA';
  COUNT(*)
----------
     55858
SQL> SELECT /*+ ROWID(A) */ COUNT(*) FROM T_XIFENFEI A
  2  WHERE  ROWID<'AAAS+/AANAAAAF1AAA';
  COUNT(*)
----------
     17358
SQL> SELECT 77759-55858-17358 from dual;
77759-55858-17358
-----------------
             4543
SQL> CREATE TABLE T_XIFENFEI_BAK  TABLESPACE T_XFF
  2  AS
  3  SELECT /*+ ROWID(A) */ * FROM T_XIFENFEI A
  4  WHERE ROWID>='AAAS+/AANAAAAF2AAA';
表已创建。
SQL> INSERT INTO  T_XIFENFEI_BAK
  2  SELECT /*+ ROWID(A) */ * FROM T_XIFENFEI A
  3  WHERE  ROWID<'AAAS+/AANAAAAF1AAA';
已创建17358行。
SQL> COMMIT;
提交完成。
SQL> SELECT COUNT(*) FROM T_XIFENFEI_BAK;
  COUNT(*)
----------
     73216

五.和dbms_repair解决坏块对比

SQL> CONN / AS SYSDBA
已连接。
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');
PL/SQL 过程已成功完成。
SQL>  select skip_corrupt from dba_tables where table_name='T_XIFENFEI';
SKIP_COR
--------
ENABLED
SQL> select count(*) from chf.t_xifenfei;
  COUNT(*)
----------
     73216

通过跳过坏块和rowid功能对比可以看出，两者丢失的数据是相同的，如果有index，同样利用rowid结合index，可能会找回部分数据。当然dbms_repair也提供了类此的功能。两种方法的使用看个人的爱好与习惯。

使用bbed解决ORA-00600[2662]

一、数据库启动报ORA-00600[2662]

[oracle@node1 ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 22 14:37:00 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2],
[2164287937], [4194432], [], [], [], [], [], []
Process ID: 16829
Session ID: 96 Serial number: 3

二.alert日志错误显示

Thu Dec 22 14:37:09 2011
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
Thu Dec 22 14:37:09 2011
ARC0 started with pid=20, OS id=16831
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thu Dec 22 14:37:10 2011
ARC1 started with pid=21, OS id=16833
Thu Dec 22 14:37:10 2011
ARC2 started with pid=22, OS id=16835
Thu Dec 22 14:37:10 2011
ARC3 started with pid=23, OS id=16837
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 17
  Current log# 2 seq# 17 mem# 0: /opt/oracle/oradata/ora11g/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_16829.trc  (incident=36156):
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2], [2164287937], [4194432], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_36156/ora11g_ora_16829_i36156.trc
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_16829.trc  (incident=36157):
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2], [2164287937], [4194432], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_36157/ora11g_ora_16829_i36157.trc
Dumping diagnostic data in directory=[cdmp_20111222143713], requested by (instance=1, osid=16829), summary=[incident=36156].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Undo initialization errored: err:600 serial:0 start:176607884 end:176611234 diff:3350 (33 seconds)
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_16829.trc:
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2], [2164287937], [4194432], [], [], [], [], [], []
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_16829.trc:
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2], [2164287937], [4194432], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 16829): terminating the instance due to error 600
Instance terminated by USER, pid = 16829
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (16829) as a result of ORA-1092
Thu Dec 22 14:37:15 2011
ORA-1092 : opitsk aborting process

三.分析日志
ORA-00600[2662]主要参数说明见:ORA-00600 [2662]
这里补充说明:e表示出现异常问题的数据块的DBA,这里的4194432就是一个数据块的DBA

--通过DBA地址查询数据块和文件号
SQL> select dbms_utility.data_block_address_block(4194432) "blick",
  2    dbms_utility.data_block_address_file(4194432) "file" from dual;
     blick       file
---------- ----------
       128          1
--当前数据库SCN
SQL> select to_char(2147510731,'xxxxxxxxxxx') from dual;
TO_CHAR(2147
------------
    800069cb
--当前数据块SCN
SQL> select to_char(2164287937,'xxxxxxxxxxx') from dual;
TO_CHAR(2164
------------
    810069c1

四.bbed查看相关SCN

[oracle@node1 ora11g]$ bbed
Password:
BBED-00113: Invalid password. Please rerun utility with the correct password.
[oracle@node1 ora11g]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Dec 22 14:49:24 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename "/opt/oracle/oradata/ora11g/system01.dbf"
        FILENAME        /opt/oracle/oradata/ora11g/system01.dbf
BBED> set block 1
        BLOCK#          1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484
   struct kcvcpscn, 8 bytes                 @484
      ub4 kscnbas                           @484      0x800069c8
      ub2 kscnwrp                           @488      0x0002
   ub4 kcvcptim                             @492      0x2dedee96
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500
      struct kcvcprba, 12 bytes             @500
         ub4 kcrbaseq                       @500      0x00000011
         ub4 kcrbabno                       @504      0x0000210f
         ub2 kcrbabof                       @508      0x0010
   ub1 kcvcpetb[0]                          @512      0x02
   ub1 kcvcpetb[1]                          @513      0x00
   ub1 kcvcpetb[2]                          @514      0x00
   ub1 kcvcpetb[3]                          @515      0x00
   ub1 kcvcpetb[4]                          @516      0x00
   ub1 kcvcpetb[5]                          @517      0x00
   ub1 kcvcpetb[6]                          @518      0x00
   ub1 kcvcpetb[7]                          @519      0x00
BBED> set block 128
        BLOCK#          128
BBED> p bas_kcbh
ub4 bas_kcbh                                @8        0x810069c1
BBED> p wrp_kcbh
ub2 wrp_kcbh                                @12       0x0002

这里看到的SCN(16进制)和我们在alert日志中看到的有一定的出入原因是在数据库启动的时候，当前SCN增加了，但是因为数据库直接abort，没有写入到数据文件中。导致数据文件头部的SCN比alert中显示的稍微小一点(还有可能，系统当前的scn比system01.dbf的scn大一点)。通过对比数据块和数据文件头部的SCN也可以说明当数据块的SCN>数据块当前SCN导致ORA-00600[2662]

五.bbed修改数据块的SCN

BBED> set offset 8
        OFFSET          8
BBED> m /x c8690080
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
        MODE            Edit
BBED> m /x c8690080
BBED-00209: invalid number (c8690080)
--分开修改，曲线救国策略
BBED> m /x c869
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /opt/oracle/oradata/ora11g/system01.dbf (0)
 Block: 128              Offsets:    8 to  519           Dba:0x00000000
------------------------------------------------------------------------
 c8690081 02000104 2f8f0000 00000000 00000000 00000000 00000000 06000000
 2f000000 20100000 00000000 00000000 07000000 81004000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 06000000 00000000 00000000
 00000040 81004000 07000000 88004000 08000000 10024000 08000000 18024000
 08000000 20024000 08000000 28024000 08000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> set offset +2
        OFFSET          10
BBED> m /x 0080
 File: /opt/oracle/oradata/ora11g/system01.dbf (0)
 Block: 128              Offsets:   10 to  521           Dba:0x00000000
------------------------------------------------------------------------
 00800200 01042f8f 00000000 00000000 00000000 00000000 00000600 00002f00
 00002010 00000000 00000000 00000700 00008100 40000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000600 00000000 00000000 00000000
 00408100 40000700 00008800 40000800 00001002 40000800 00001802 40000800
 00002002 40000800 00002802 40000800 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> p tailchk
ub4 tailchk                                 @8188     0x69c10e01
BBED> set offset 8188
        OFFSET          8188
BBED> m /x 010ec869
 File: /opt/oracle/oradata/ora11g/system01.dbf (0)
 Block: 128              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 010ec869
 <32 bytes per line>
BBED> p tailchk
ub4 tailchk                                 @8188     0x69c80e01
BBED> p bas_kcbh
ub4 bas_kcbh                                @8        0x800069c8
BBED> sum apply
Check value for File 0, Block 128:
current = 0x8e2f, required = 0x8e2f
BBED> exit

六.启动数据库

[oracle@node1 ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 22 14:58:10 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
Database opened.

七.补充说明
一般遇到ORA-00600[2662]都是使用alter session set events ‘10015 trace name adjust_scn level N’;方法处理，但是有时候会遇到ORA-01031错误，那就需要请bbed帮忙处理

OS Pid: 30268 executed alter session set events '10051 trace name adjust_scn level 2'
Thu Dec 22 12:04:07 2011
Errors in file /ora101/diag/rdbms/ora11/ora11/trace/ora11_ora_30268.trc:
ORA-01031: insufficient privileges
Thu Dec 22 12:04:43 2011
Errors in file /ora101/diag/rdbms/ora11/ora11/trace/ora11_ora_846.trc:
ORA-01031: insufficient privileges

通过ZHS16GBK和AL32UTF8字符编码分析exp/imp

一、试验环境和试验准备工作
1.源端

[oracle@node1 ~]$ echo $LANG
zh_CN.gb2312
[oracle@node1 ~]$ sqlplus hr/xifenfei
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 21 14:07:24 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT userenv('language') FROM dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
SQL> create table xifenfei (name varchar2(6));
Table created.
SQL> insert into xifenfei values('惜分飞');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from xifenfei;
NAME
------
惜分飞
惜分飞
惜分飞
惜分飞

2.目标端

[oracle@node1 ~]$ echo $LANG
zh_CN.gb2312
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 15:26:18 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> SELECT userenv('language') FROM dual;
USERENV('LANGUAGE')
----------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

二、导出测试表(xifenfei)
1.ZHS16GBK编码

[oracle@node1 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ exp hr/xifenfei file=/tmp/xifenfei.dmp \
log=/tmp/xifenfei.log tables=xifenfei
Export: Release 10.2.0.5.0 - Production on Wed Dec 21 14:07:11 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                       XIFENFEI          4 rows exported
Export terminated successfully without warnings.

2.AL32UTF8编码

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@node1 ~]$ exp hr/xifenfei file=/tmp/xifenfei_new.dmp \
> log=/tmp/xifenfei_new.log tables=xifenfei
Export: Release 10.2.0.5.0 - Production on Wed Dec 21 14:38:14 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                       XIFENFEI          4 rows exported
Export terminated successfully without warnings.

3.dmp文件比较
3.1)文件大小比较

[oracle@node1 ~]$ cd /tmp
[oracle@node1 tmp]$ ll *.dmp
-rw-r--r-- 1 oracle oinstall 16384 12-21 14:07 xifenfei.dmp
-rw-r--r-- 1 oracle oinstall 16384 12-21 14:38 xifenfei_new.dmp
虽然导出客户端设置了不步的编码，但是导出的文件大小相等

3.2)文件头部比较

--xifenfei.dmp
00000000h: 03 03 54 45 58 50 4F 52 54 3A 56 31 30 2E 30 32 ; ..TEXPORT:V10.02
00000010h: 2E 30 31 0A 44 48 52 0A 52 54 41 42 4C 45 53 0A ; .01.DHR.RTABLES.
00000020h: 38 31 39 32 0A 30 0A 33 32 0A 30 0A 03 54 03 54 ; 8192.0.32.0..T.T
00000030h: 07 D0 00 01 00 00 00 00 00 00 00 00 00 11 00 20 ; .?............
00000040h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;
00000050h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;
00000060h: 20 20 20 20 20 20 20 20 20 57 65 64 20 44 65 63 ;          Wed Dec
00000070h: 20 32 31 20 31 34 3A 37 3A 31 32 20 32 30 31 31 ;  21 14:7:12 2011
00000080h: 2F 74 6D 70 2F 78 69 66 65 6E 66 65 69 2E 64 6D ; /tmp/xifenfei.dm
00000090h: 70 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; p...............
--xifenfei_new.dmp
00000000h: 03 03 69 45 58 50 4F 52 54 3A 56 31 30 2E 30 32 ; ..iEXPORT:V10.02
00000010h: 2E 30 31 0A 44 48 52 0A 52 54 41 42 4C 45 53 0A ; .01.DHR.RTABLES.
00000020h: 38 31 39 32 0A 30 0A 33 32 0A 30 0A 03 69 03 54 ; 8192.0.32.0..i.T
00000030h: 07 D0 00 01 00 00 00 00 00 00 00 00 00 15 00 20 ; .?............
00000040h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;
00000050h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;
00000060h: 20 20 20 20 20 20 20 20 57 65 64 20 44 65 63 20 ;         Wed Dec
00000070h: 32 31 20 31 34 3A 33 38 3A 31 35 20 32 30 31 31 ; 21 14:38:15 2011
00000080h: 2F 74 6D 70 2F 78 69 66 65 6E 66 65 69 5F 6E 65 ; /tmp/xifenfei_ne
00000090h: 77 2E 64 6D 70 00 00 00 00 00 00 00 00 00 00 00 ; w.dmp...........
--ZHS16GBK dmp 文件 第2,3字节为 0354
--AL32UTF8 dmp 文件 第2,3字节为 0369

3.3)比较dmp文件内容

--xifenfei.dmp
000020ceh: 2B 30 30 3A 30 30 00 00 04 00 42 59 54 45 06 00 ; +00:00....BYTE..
000020deh: 55 4E 55 53 45 44 01 00 32 0B 00 49 4E 54 45 52 ; UNUSED..2..INTER
000020eeh: 50 52 45 54 45 44 0B 00 44 49 53 41 42 4C 45 3A ; PRETED..DISABLE:
000020feh: 41 4C 4C 00 00 0A 4D 45 54 52 49 43 53 54 0A 54 ; ALL...METRICST.T
0000210eh: 41 42 4C 45 20 22 58 49 46 45 4E 46 45 49 22 0A ; ABLE "XIFENFEI".
0000211eh: 43 52 45 41 54 45 20 54 41 42 4C 45 20 22 58 49 ; CREATE TABLE "XI
0000212eh: 46 45 4E 46 45 49 22 20 28 22 4E 41 4D 45 22 20 ; FENFEI" ("NAME"
0000213eh: 56 41 52 43 48 41 52 32 28 36 29 29 20 20 50 43 ; VARCHAR2(6))  PC
0000214eh: 54 46 52 45 45 20 31 30 20 50 43 54 55 53 45 44 ; TFREE 10 PCTUSED
0000215eh: 20 34 30 20 49 4E 49 54 52 41 4E 53 20 31 20 4D ;  40 INITRANS 1 M
0000216eh: 41 58 54 52 41 4E 53 20 32 35 35 20 53 54 4F 52 ; AXTRANS 255 STOR
0000217eh: 41 47 45 28 49 4E 49 54 49 41 4C 20 36 35 35 33 ; AGE(INITIAL 6553
0000218eh: 36 20 4E 45 58 54 20 31 30 34 38 35 37 36 20 46 ; 6 NEXT 1048576 F
0000219eh: 52 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C ; REELISTS 1 FREEL
000021aeh: 49 53 54 20 47 52 4F 55 50 53 20 31 20 42 55 46 ; IST GROUPS 1 BUF
000021beh: 46 45 52 5F 50 4F 4F 4C 20 44 45 46 41 55 4C 54 ; FER_POOL DEFAULT
000021ceh: 29 20 54 41 42 4C 45 53 50 41 43 45 20 22 55 53 ; ) TABLESPACE "US
000021deh: 45 52 53 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 ; ERS" LOGGING NOC
000021eeh: 4F 4D 50 52 45 53 53 0A 49 4E 53 45 52 54 20 49 ; OMPRESS.INSERT I
000021feh: 4E 54 4F 20 22 58 49 46 45 4E 46 45 49 22 20 28 ; NTO "XIFENFEI" (
0000220eh: 22 4E 41 4D 45 22 29 20 56 41 4C 55 45 53 20 28 ; "NAME") VALUES (
0000221eh: 3A 31 29 0A 01 00 01 00 06 00 54 03 01 00 00 00 ; :1).......T.....
0000222eh: 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 06 00 CF A7 ; ....惜分飞....惜
0000223eh: B7 D6 B7 C9 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 ; 分飞....惜分飞..
0000224eh: 06 00 CF A7 B7 D6 B7 C9 00 00 FF FF 0A 4D 45 54 ; ..惜分飞...MET
0000225eh: 52 49 43 53 54 72 65 66 65 72 65 6E 74 69 61 6C ; RICSTreferential
0000226eh: 20 69 6E 74 65 67 72 69 74 79 20 63 6F 6E 73 74 ;  integrity const
0000227eh: 72 61 69 6E 74 73 0A 4D 45 54 52 49 43 45 54 20 ; raints.METRICET
0000228eh: 34 0A 4D 45 54 52 49 43 53 54 74 72 69 67 67 65 ; 4.METRICSTtrigge
0000229eh: 72 73 0A 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 ; rs.METRICET 4.ME
000022aeh: 54 52 49 43 53 54 62 69 74 6D 61 70 2C 20 66 75 ; TRICSTbitmap, fu
000022beh: 6E 63 74 69 6F 6E 61 6C 20 61 6E 64 20 65 78 74 ; nctional and ext
000022ceh: 65 6E 73 69 62 6C 65 20 69 6E 64 65 78 65 73 0A ; ensible indexes.
000022deh: 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 54 52 49 ; METRICET 4.METRI
000022eeh: 43 53 54 70 6F 73 74 74 61 62 6C 65 73 20 61 63 ; CSTposttables ac
000022feh: 74 69 6F 6E 73 0A 4D 45 54 52 49 43 45 54 20 34 ; tions.METRICET 4
0000230eh: 0A 4D 45 54 52 49 43 53 54 50 6F 73 74 2D 69 6E ; .METRICSTPost-in
0000231eh: 73 74 20 70 72 6F 63 65 64 75 72 61 6C 20 61 63 ; st procedural ac
0000232eh: 74 69 6F 6E 73 20 0A 4D 45 54 52 49 43 45 54 20 ; tions .METRICET
0000233eh: 34 0A 4D 45 54 52 49 43 45 54 47 30 0A 45 58 49 ; 4.METRICETG0.EXI
0000234eh: 54 0A 45 58 49 54 0A                            ; T.EXIT.
--xifenfei_new.dmp
000020ceh: 2B 30 30 3A 30 30 00 00 04 00 42 59 54 45 06 00 ; +00:00....BYTE..
000020deh: 55 4E 55 53 45 44 01 00 32 0B 00 49 4E 54 45 52 ; UNUSED..2..INTER
000020eeh: 50 52 45 54 45 44 0B 00 44 49 53 41 42 4C 45 3A ; PRETED..DISABLE:
000020feh: 41 4C 4C 00 00 0A 4D 45 54 52 49 43 53 54 0A 54 ; ALL...METRICST.T
0000210eh: 41 42 4C 45 20 22 58 49 46 45 4E 46 45 49 22 0A ; ABLE "XIFENFEI".
0000211eh: 43 52 45 41 54 45 20 54 41 42 4C 45 20 22 58 49 ; CREATE TABLE "XI
0000212eh: 46 45 4E 46 45 49 22 20 28 22 4E 41 4D 45 22 20 ; FENFEI" ("NAME"
0000213eh: 56 41 52 43 48 41 52 32 28 36 29 29 20 20 50 43 ; VARCHAR2(6))  PC
0000214eh: 54 46 52 45 45 20 31 30 20 50 43 54 55 53 45 44 ; TFREE 10 PCTUSED
0000215eh: 20 34 30 20 49 4E 49 54 52 41 4E 53 20 31 20 4D ;  40 INITRANS 1 M
0000216eh: 41 58 54 52 41 4E 53 20 32 35 35 20 53 54 4F 52 ; AXTRANS 255 STOR
0000217eh: 41 47 45 28 49 4E 49 54 49 41 4C 20 36 35 35 33 ; AGE(INITIAL 6553
0000218eh: 36 20 4E 45 58 54 20 31 30 34 38 35 37 36 20 46 ; 6 NEXT 1048576 F
0000219eh: 52 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C ; REELISTS 1 FREEL
000021aeh: 49 53 54 20 47 52 4F 55 50 53 20 31 20 42 55 46 ; IST GROUPS 1 BUF
000021beh: 46 45 52 5F 50 4F 4F 4C 20 44 45 46 41 55 4C 54 ; FER_POOL DEFAULT
000021ceh: 29 20 54 41 42 4C 45 53 50 41 43 45 20 22 55 53 ; ) TABLESPACE "US
000021deh: 45 52 53 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 ; ERS" LOGGING NOC
000021eeh: 4F 4D 50 52 45 53 53 0A 49 4E 53 45 52 54 20 49 ; OMPRESS.INSERT I
000021feh: 4E 54 4F 20 22 58 49 46 45 4E 46 45 49 22 20 28 ; NTO "XIFENFEI" (
0000220eh: 22 4E 41 4D 45 22 29 20 56 41 4C 55 45 53 20 28 ; "NAME") VALUES (
0000221eh: 3A 31 29 0A 01 00 01 00 06 00 54 03 01 00 00 00 ; :1).......T.....
0000222eh: 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 06 00 CF A7 ; ....惜分飞....惜
0000223eh: B7 D6 B7 C9 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 ; 分飞....惜分飞..
0000224eh: 06 00 CF A7 B7 D6 B7 C9 00 00 FF FF 0A 4D 45 54 ; ..惜分飞...MET
0000225eh: 52 49 43 53 54 72 65 66 65 72 65 6E 74 69 61 6C ; RICSTreferential
0000226eh: 20 69 6E 74 65 67 72 69 74 79 20 63 6F 6E 73 74 ;  integrity const
0000227eh: 72 61 69 6E 74 73 0A 4D 45 54 52 49 43 45 54 20 ; raints.METRICET
0000228eh: 34 0A 4D 45 54 52 49 43 53 54 74 72 69 67 67 65 ; 4.METRICSTtrigge
0000229eh: 72 73 0A 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 ; rs.METRICET 4.ME
000022aeh: 54 52 49 43 53 54 62 69 74 6D 61 70 2C 20 66 75 ; TRICSTbitmap, fu
000022beh: 6E 63 74 69 6F 6E 61 6C 20 61 6E 64 20 65 78 74 ; nctional and ext
000022ceh: 65 6E 73 69 62 6C 65 20 69 6E 64 65 78 65 73 0A ; ensible indexes.
000022deh: 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 54 52 49 ; METRICET 4.METRI
000022eeh: 43 53 54 70 6F 73 74 74 61 62 6C 65 73 20 61 63 ; CSTposttables ac
000022feh: 74 69 6F 6E 73 0A 4D 45 54 52 49 43 45 54 20 34 ; tions.METRICET 4
0000230eh: 0A 4D 45 54 52 49 43 53 54 50 6F 73 74 2D 69 6E ; .METRICSTPost-in
0000231eh: 73 74 20 70 72 6F 63 65 64 75 72 61 6C 20 61 63 ; st procedural ac
0000232eh: 74 69 6F 6E 73 20 0A 4D 45 54 52 49 43 45 54 20 ; tions .METRICET
0000233eh: 34 0A 4D 45 54 52 49 43 45 54 47 30 0A 45 58 49 ; 4.METRICETG0.EXI
0000234eh: 54 0A 45 58 49 54 0A                            ; T.EXIT.
--通过比较知道，不同客户端编码，导出的数据内容相同，
--都是一个汉字对应两个字节(说明是按照服务端编码导出)

三、导入测试表(xifenfei)
1.导入xifenfei.dmp(ZHS16GBK编码导出)
1.1)客户端编码设置ZHS16GBK

[oracle@node1 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \
 log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test
Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:16:27 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by HR, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞          0 rows imported
Import terminated successfully with warnings.

1.2)客户端编码设置为AL32UTF8

[oracle@node1 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \
log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test
Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:21:41 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by HR, not by you
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export client uses ZHS16GBK character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋         0 rows imported
Import terminated successfully with warnings.

2.导入xifenfei_new.dmp(AL32UTF8编码导出)
2.1)客户端编码设置ZHS16GBK

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei_new.dmp \
> log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test
Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:39:53 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by HR, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞          0 rows imported
Import terminated successfully with warnings.

2.2)客户端编码设置为AL32UTF8

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei_new.dmp \
> log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test
Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:41:12 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by HR, not by you
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋         0 rows imported
Import terminated successfully with warnings.

四、修改列长度，导入成功
无论源端客户端使用何种编码导出，目标端使用何种编码导入(仅限我这里说的AL32UTF8和ZHS16GBK),如果客户端编码是ZHS16GBK，验证数据的时候，可以省略掉设置编码的过程。

--导入报错后，登录数据库，修改列长度，因为目标端数据库编码是AL32UTF8，
--1个汉字占用3个字节修改列的程度满足错误提示的最大程度。
SQL> alter table xifenfei modify name varchar2(9);
Table altered.
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \
log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test IGNORE=y
Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:25:07 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by HR, not by you
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export client uses ZHS16GBK character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"          4 rows imported
Import terminated successfully without warnings.
--因为目标端客户端编码为AL32UTF8，而linux展示平台编码为gbk，所以出现乱码
--修改客户端编码后，查询乱码问题消失
[oracle@node1 ~]$ sqlplus test/xifenfei
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 14:25:14 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select * from xifenfei;
NAME
---------
鎯滃垎椋
鎯滃垎椋
鎯滃垎椋
鎯滃垎椋
SQL> exit
[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ sqlplus test/xifenfei
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 14:25:52 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select * from xifenfei;
NAME
------------------
惜分飞
惜分飞
惜分飞
惜分飞

五、试验总结
1.当源端字符编码为ZHS16GBK，目标端编码为AL32UTF8，客户端随便为其中的一种编码，迁移数据不会出现乱码，但是会出现列长度不够现象。反过来不行，因为utf8中的部分字符转换到gbk中肯定会不支持
2.设置了源端客户端编码，仅仅是导出来的dmp文件头部有编码字符标示不一样，存储数据还是按照服务端存储
3.打破神话，exp/imp导入要不乱码，导出和导入的客户端编码要一致

修改oracle数据库字符集

现在有个需求，需要数据库字符集从ZHS16GBK修改为AL32UTF8，因为他们没有子集的关系，所以在转换前，需要先检测库中的数据内容是否全库可以转换为AL32UTF8字符集，检测使用oracle提供的csscan工具实现
一、csscan使用
1.安装csscan相关数据字典

SQL> @?/rdbms/admin/csminst.sql

2.csscan使用说明

[oracle@node1 ~]$ csscan help=y
Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Wed Dec 21 12:30:42 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
You can let Scanner prompt you for parameters by entering the CSSCAN
command followed by your username/password:
  Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\"
Or, you can control how Scanner runs by entering the CSSCAN command
followed by various parameters. To specify parameters, you use keywords:
  Example:
    CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3
Keyword    Default Prompt Description
---------- ------- ------ -------------------------------------------------
USERID             yes    username/password
FULL       N       yes    scan entire database
USER               yes    owner of tables to be scanned
TABLE              yes    list of tables to scan
COLUMN             yes    list of columns to scan
EXCLUDE                   list of tables to exclude from scan
TOCHAR             yes    new database character set name
FROMCHAR                  current database character set name
TONCHAR                   new national character set name
FROMNCHAR                 current national character set name
ARRAY      1024000 yes    size of array fetch buffer
PROCESS    1       yes    number of concurrent scan process
MAXBLOCKS                 split table if block size exceed MAXBLOCKS
CAPTURE    N              capture convertible data
SUPPRESS                  maximum number of exceptions logged for each table
FEEDBACK                  report progress every N rows
BOUNDARIES                list of column size boundaries for summary report
LASTRPT    N              generate report of the last database scan
LOG        scan           base file name of report files
PARFILE                   parameter file name
PRESERVE   N              preserve existing scan results
LCSD       N       no     enable language and character set detection
LCSDDATA   LOSSY   no     define the scope of the detection
HELP       N              show help screen (this screen)
QUERY      N              select clause to scan subset of tables or columns
---------- ------- ------ -------------------------------------------------
Scanner terminated successfully.

3.使用csscan检测当前数据库

[oracle@node1 ~]$ csscan userid="'"sys/xifenfei as sysdba"'" full=y \
fromchar=ZHS16GBK tochar=AL32UTF8 log=/tmp/check.log capture=y array=1000000 process=4
Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Wed Dec 21 12:36:37 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Enumerating tables to scan...
. process 1 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA]
. process 2 scanning XFF.T1[AAATjjAAEAAAC7AAAA]
. process 3 scanning TEST.T_XFF[AAAT+6AAEAAAAIQAAA]
. process 2 scanning SYS.METHOD$[AAAAHvAABAAAA0QAAA]
. process 4 scanning SYS.ATTRIBUTE$[AAAAHvAABAAAA0QAAA]
…………………………………………
. process 4 scanning CTXSYS.DR$INDEX_CDI_COLUMN
. process 2 scanning CTXSYS.DR$SDATA_UPDATE
. process 1 scanning EXFSYS.RLM$RULESETSTCODE
. process 3 scanning EXFSYS.RLM$RULESET
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.

4.查看csscan检查日志

[oracle@node1 ~]$ ll /tmp/check.log.*
-rw-r--r-- 1 oracle oinstall  14526 12-21 12:37 /tmp/check.log.err
-rw-r--r-- 1 oracle oinstall 100235 12-21 12:37 /tmp/check.log.out
-rw-r--r-- 1 oracle oinstall   8265 12-21 12:37 /tmp/check.log.txt
--err是错误日志
--out是执行过程
--txt是执行结果汇总
[oracle@node1 tmp]$ cat /tmp/check.log.txt
Database Scan Summary Report
Time Started  : 2011-12-21 12:36:37
Time Completed: 2011-12-21 12:37:13
Process ID         Time Started       Time Completed
---------- -------------------- --------------------
         1  2011-12-21 12:36:43  2011-12-21 12:37:12
         2  2011-12-21 12:36:43  2011-12-21 12:37:12
         3  2011-12-21 12:36:43  2011-12-21 12:37:12
         4  2011-12-21 12:36:43  2011-12-21 12:37:12
---------- -------------------- --------------------
[Database Size]
Tablespace                           Used            Free           Total       Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM                            723.63M           6.38M         730.00M            .00K
SYSAUX                            686.56M         103.44M         790.00M            .00K
UNDOTBS1                           18.31M         126.69M         145.00M            .00K
TEMP                                 .00K            .00K            .00K            .00K
USERS                             138.69M          22.56M         161.25M            .00K
EXAMPLE                           310.13M          35.50M         345.63M            .00K
------------------------- --------------- --------------- --------------- ---------------
Total                           1,877.31M         294.56M       2,171.88M            .00K
[Database Scan Parameters]
Parameter                      Value
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1
Instance Name                  ora11g
Database Version               11.2.0.3.0
Scan type                      Full database
Scan CHAR data?                YES
Database character set         ZHS16GBK
FROMCHAR                       ZHS16GBK
TOCHAR                         AL32UTF8
Scan NCHAR data?               NO
Array fetch buffer size        1000000
Number of processes            4
Capture convertible data?      YES
------------------------------ ------------------------------------------------
[Scan Summary]
All character type data in the data dictionary are convertible to the new character set
All character type application data remain the same in the new character set
[Data Dictionary Conversion Summary]
Data Dictionary Tables:
Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     6,061,403              152                0                0
CHAR                             4,988                0                0                0
LONG                           252,530                0                0                0
VARRAY                          50,812                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        6,369,733              152                0                0
Total in percentage             99.998%           0.002%           0.000%           0.000%
The data dictionary can not be safely migrated using the CSALTER script
XML CSX Dictionary Tables:
Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                           711                0                0                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                              711                0                0                0
Total in percentage            100.000%           0.000%           0.000%           0.000%
[Application Data Conversion Summary]
Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     8,142,707                0                0                0
CHAR                            63,085                0                0                0
LONG                                 0                0                0                0
VARRAY                             583                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        8,206,375                0                0                0
Total in percentage            100.000%           0.000%           0.000%           0.000%
[Distribution of Convertible, Truncated and Lossy Data by Table]
Data Dictionary Tables:
USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
CTXSYS.DR$INDEX_VALUE                                            76                0                0
CTXSYS.DR$STOPWORD                                               76                0                0
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
[Distribution of Convertible, Truncated and Lossy Data by Column]
Data Dictionary Tables:
USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
CTXSYS.DR$INDEX_VALUE|IXV_VALUE                                  76                0                0
CTXSYS.DR$STOPWORD|SPW_WORD                                      76                0                0
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
[Indexes to be Rebuilt]
USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
--因为检测结果没有Truncation(截断数据)/Lossy(丢失数据)都不存在记录，所以不用查看错误日志
--如果发现不为0，需要检查err日志，然后先处理丢这些记录，然后再转换

二、修改数据库字符集

[oracle@node1 tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 12:59:55 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select value$ from props$ where name='NLS_CHARACTERSET';
VALUE$
------------------------------------------------------
ZHS16GBK
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
SQL> alter system enable restricted session;
System altered.
SQL> alter system set job_queue_processes=0 scope=memory;
System altered.
SQL> alter system set aq_tm_processes=0  scope=memory;
System altered.
SQL> alter database open;
Database altered.
SQL> alter database character set internal_use AL32UTF8;
Database altered.
SQL> shutdown  immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
Database opened.
SQL> select value$ from props$ where name='NLS_CHARACTERSET';
VALUE$
--------------------------------------------------------
AL32UTF8

DBMS_SCHEDULER常规操作

1.create job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
  job_name =>'xifenfei_job',
  job_type =>'STORED_PROCEDURE',
  job_action =>'p_schedule',
  repeat_interval =>'FREQ=DAILY; BYHOUR=18,20,22',
  enabled => true,
  comments => 'XIFENFEI');
END;
--每天18/20/22点执行p_schedule过程

2.disable job

BEGIN
    DBMS_SCHEDULER.DISABLE(name => 'xifenfei_job');
END;

3.enable job

BEGIN
    DBMS_SCHEDULER.ENABLE(name => 'xifenfei_job');
END;

4.select job

select * from USER_SCHEDULER_JOBS;

5.query logs

SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS
WHERE job_name='XIFENFEI_JOB';

6.delete logs

--ALL_SCHEDULER_JOB_RUN_DETAILS视图
CREATE OR REPLACE VIEW ALL_SCHEDULER_JOB_RUN_DETAILS
(log_id, log_date, owner, job_name, job_subname, status, error#,
req_start_date, actual_start_date, run_duration, instance_id,
session_id, slave_pid, cpu_used, credential_owner, credential_name,
destination_owner, destination, additional_info)
AS
(SELECT
     j.LOG_ID, j.LOG_DATE, e.OWNER,
     DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1)),
     DECODE(instr(e.NAME,'"'),0,NULL,substr(e.NAME,instr(e.NAME,'"')+1)),
     e.STATUS, j.ERROR#, j.REQ_START_DATE, j.START_DATE, j.RUN_DURATION,
     j.INSTANCE_ID, j.SESSION_ID, j.SLAVE_PID, j.CPU_USED,
     decode(e.credential, NULL, NULL,
        substr(e.credential, 1, instr(e.credential, '"')-1)),
     decode(e.credential, NULL, NULL,
        substr(e.credential, instr(e.credential, '"')+1,
           length(e.credential) - instr(e.credential, '"'))),
     decode(bitand(e.flags, 1), 0, NULL,
        substr(e.destination, 1, instr(e.destination, '"')-1)),
     decode(bitand(e.flags, 1), 0, e.destination,
        substr(e.destination, instr(e.destination, '"')+1,
           length(e.destination) - instr(e.destination, '"'))),
     j.ADDITIONAL_INFO
   FROM scheduler$_job_run_details j, scheduler$_event_log e
   WHERE j.log_id = e.log_id
   AND e.type# = 66 and e.dbid is null
   AND ( e.owner = SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
         or  /* user has object privileges */
            ( select jo.obj# from obj$ jo, user$ ju where
                DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1))
                = jo.name and e.owner = ju.name and jo.owner# = ju.user#
                and jo.subname is null and jo.type# = 66
            ) in
            ( select oa.obj#
                from sys.objauth$ oa
                where grantee# in ( select kzsrorol from x$kzsro )
            )
         or /* user has system privileges */
            (exists ( select null from v$enabledprivs
                       where priv_number = -265 /* CREATE ANY JOB */
                   )
             and e.owner!='SYS')
        )
  );
--从这个视图中可以发现，日志有存在SCHEDULER$_JOB_RUN_DETAILS和
--SCHEDULER$_EVENT_LOG两张表中,所以要删除日志，就需要处理这两张表
######################删除日志操作#################
--删除SYS.SCHEDULER$_JOB_RUN_DETAILS中数据
DELETE FROM SYS.SCHEDULER$_JOB_RUN_DETAILS A
 WHERE EXISTS (SELECT 1
          FROM SYS.SCHEDULER$_EVENT_LOG B
         WHERE B.NAME = 'XIFENFEI_JOB'
           AND A.LOG_ID = B.LOG_ID);
--删除SYS.SCHEDULER$_EVENT_LOG中数据
DELETE FROM SYS.SCHEDULER$_EVENT_LOG B
 WHERE B.NAME = 'XIFENFEI_JOB';
--提交
 COMMIT;
#####################################################

7.delete jobs

BEGIN
    DBMS_SCHEDULER.DROP_JOB(job_name => 'xifenfei_job');
END;

参考:http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm

绕过win中服务开启oracle数据库

在win系统中，如果oracle数据库的服务没有启动，使用sqlplus登录，会报ORA-12560错误，数据库无法启动

C:\Users\XIFENFEI&gt;sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 12月 19 20:33:27 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-12560: TNS: 协议适配器错误
请输入用户名:

绕过服务启动数据库具体操作步骤如下：

1.当前oracle数据库服务OracleServiceXFF处于关闭状态

2.右键OracleServiceXFF服务属性
从这里我们可以看到”可执行文件的路径”，然后我们直接在dos(管理员权限，或者说有权限运行oracle的用户下)，运行该命令

3.在dos中运行命令
这里提示”Press CTRL-C to exit server:”在你不想oracle被abort之前，请不要执行CTRL-C或者直接关闭该窗口

4.开启oracle数据库

至此在win服务器中绕过服务，直接启动oracle操作完成，这个多半是在因为不能正常开启服务，通过这种方式来排错。在生产环境中，请勿模仿，如出现问题，后果自负(数据库被abort)

处理fast_recovery_area无剩余空间案例

一、打开数据库报错

[oracle@node1 ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 19 15:42:04 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
SQL> alter database mount;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 19489
Session ID: 96 Serial number: 1

二、错误信息
1.alert日志内容

Mon Dec 19 15:23:29 2011
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 7 processes
Started redo scan
Completed redo scan
read 0 KB redo, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 3, block 93169, scn 12899730
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
Mem# 0: /opt/oracle/oradata/ora11g/redo03.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 3, block 93169, scn 12919731
0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Mon Dec 19 15:23:29 2011
LGWR: STARTING ARCH PROCESSES
Mon Dec 19 15:23:29 2011
ARC0 started with pid=27, OS id=19539
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Dec 19 15:23:30 2011
ARC1 started with pid=28, OS id=19548
Mon Dec 19 15:23:30 2011
ARC2 started with pid=29, OS id=19550
Mon Dec 19 15:23:30 2011
ARC3 started with pid=30, OS id=19552
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_arc2_19550.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 4322230272 字节) 已使用 100.00%, 尚有 0 字节可用。
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARC2: Error 19809 Creating archive log file to '/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_2_%u_.arc'
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_19489.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4322230272 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_1_%u_.arc'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ora11g - Archival Error
ORA-16038: 日志 2 sequence# 2 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 2 线程 1: '/opt/oracle/oradata/ora11g/redo02.log'
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_19489.trc:
ORA-16038: log 1 sequence# 1 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/ora11g/redo01.log'
USER (ospid: 19489): terminating the instance due to error 16038
Mon Dec 19 15:23:31 2011
System state dump requested by (instance=1, osid=19489), summary=[abnormal instance termination].
System State dumped to trace file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_diag_19463.trc
Dumping diagnostic data in directory=[cdmp_20111219152331], requested by (instance=1, osid=19489), summary=[abnormal instance termination].
Instance terminated by USER, pid = 19489

2.trace文件信息(ora11g_ora_19489.trc)

*** 2011-12-19 15:23:31.026 4320 krsh.c
ARCH: Error 19809 Creating archive log file to '/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_1_%u_.arc'
*** 2011-12-19 15:23:31.026 2932 krsi.c
krsi_dst_fail: dest:1 err:19809 force:0 blast:1
DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/ora11g/redo01.log'
ORA-16038: log 1 sequence# 1 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/ora11g/redo01.log'
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+461<-kjzdssdmp()+267<-kjzduptcctx()+232<-kjzdicrshnfy()+53<-ksuitm()+1332<-kcfopd()+5962<-adbdrv()+51834<-opiexe()+18384<-opiosq0()+3870<-kpooprx()
+274<-kpoal8()+829<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opidrv()+570
<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252
----- End of Abridged Call Stack Trace -----

这个错误很明显：因为数据库归档日志放置在fast_recovery_area中，而空间已满，导致联机日志sequence# 2不能被归档，数据库无法打开

三、修改db_recovery_file_dest_size,打开数据库

SQL>  Alter system set db_recovery_file_dest_size=20G scope=both;
System altered.
SQL> alter database open;
Database altered.

设置较大db_recovery_file_dest_size，先打开数据库，再解决问题(减少down机时间是dba一大准则)

四、删除历史归档日志
从sequence# 为2中很明显看出来，数据库进行了resetlogs打开，所以前面的归档日志，在原则上已经无效(不再使用原始备份集恢复)，因为数据库的控制文件中，无原归档日志信息，所以无法使用rman删除归档日志。那只能使用os命令先删除掉历史归档日志，然后再使用rman处理

[root@node1 archivelog]# find ./ -mtime +1| xargs rm -rf
[root@node1 archivelog]# ll
总计 72
drwxr-x--- 2 oracle oinstall 4096 12-18 22:35 2011_12_18
drwxr-x--- 2 oracle oinstall 4096 12-19 13:25 2011_12_19
RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_10_31/o1_mf_1_5_7bxbhkof_.arc RECID=1 STAMP=766015219
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_11_01/o1_mf_1_6_7bxw2gpo_.arc RECID=2 STAMP=766033231
……………………………………
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_105_7gc3co97_.arc RECID=132 STAMP=770306728
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_106_7gc3cv1w_.arc RECID=123 STAMP=770306728
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_107_7gc3mbpr_.arc RECID=127 STAMP=770306728
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_1_7gxtrlnq_.arc RECID=134 STAMP=770312597
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_2_7gxtrloz_.arc RECID=135 STAMP=770312597
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_3_7gxtrodg_.arc RECID=136 STAMP=770312599
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc RECID=126 STAMP=770306728
……………………………………
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_20_7gxlq29k_.arc RECID=113 STAMP=770306728
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_21_7gxl3zdm_.arc RECID=114 STAMP=770306728
Crosschecked 136 objects
RMAN> DELETE EXPIRED  archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
List of Archived Log Copies for database with db_unique_name ORA11G
……………………………………
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
……………………………………
deleted archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_16/o1_mf_1_14_7gpood3n_.arc RECID=115 STAMP=770306728
deleted archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_17/o1_mf_1_15_7gqhvvhh_.arc RECID=106 STAMP=770306727
Deleted 124 EXPIRED objects

五、补充说明
1.使用os命令删除fast_recovery_area内容后，需要使用crosscheck检测(如:archivelog all,backup等)。
2.然后使用 DELETE EXPIRED命令删除(archivelog all,backup等)
3.fast_recovery_area设置合适大小+合适的策略
4.resetlogs打开数据库后，做好备份
5.fast_recovery_area无剩余空间处理思路
5.1)如果数据库不能登录：重启至mount，增大fast_recovery_area，open数据库，然后使用rman删除历史垃圾数据(备份集,日志,闪回日志等)
5.2)如果数据库可以使用sys登录，增大fast_recovery_area(使其数据库可以正常工作)，然后使用rman处理垃圾数据

使用dbms_backup_restore包恢复数据库

Oracle提供了一个包:DBMS_BACKUP_RESTORE包是由dbmsbkrs.sql 和 prvtbkrs.plb 这两个脚本创建的.catproc.sql 脚本运行后会调用这两个包.所以是每个数据库都有的这个包是Oracle服务器和操作系统之间IO操作的接口.由恢复管理器直接调用。而且据说这两个脚本的功能是内建到Oracle的一些库文件中的.
由此可见，我们可以在数据库 nomount 情况下调用这些package ,来达到我们的恢复目的
1、启动数据库到nomount状态

[oracle@node1 ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 19 13:34:22 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes

2、恢复controlfile

SQL> DECLARE
  2  devtype varchar2(256);
  3  done boolean;
  4  BEGIN
  5  devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoreSetDatafile;
  7  sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/opt/oracle/oradata/ora11g/control01.ctl');
  8  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/ora11g_02mu1avd_1_1.rman', params=>null);
  9  sys.dbms_backup_restore.deviceDeallocate;
 10  END;
 11  /
PL/SQL procedure successfully completed.

3、恢复数据文件

SQL> DECLARE
  2  devtype varchar2(256);
  3  done boolean;
  4  BEGIN
  5  devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoreSetDatafile;
  7  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>1,toname=>'/opt/oracle/oradata/ora11g/system01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>2,toname=>'/opt/oracle/oradata/ora11g/sysaux01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>3,toname=>'/opt/oracle/oradata/ora11g/undotbs01.dbf');
  8    9   10  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>4,toname=>'/opt/oracle/oradata/ora11g/users01.dbf');
 11  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>5,toname=>'/opt/oracle/oradata/ora11g/example01.dbf');
 12  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/ora11g_01mu1aqq_1_1.rman', params=>null);
 13  sys.dbms_backup_restore.deviceDeallocate;
 14  END;
 15  /
PL/SQL procedure successfully completed.

4、启动数据库

SQL> alter database mount;
SQL> alter database recover until cancel using backup controlfile;
SQL> alter database open resetlogs;

5、特别说明
在oracle 10g及其以后版本中，因为rman中有catalog with start命令，可以实现rman备份的加载，所以不需要使用dbms_backup_restore包处理，在oracle 9i及其以前版本中，可能因为没有catalog库，控制文件中又没有了备份集信息，需要采用这种方法处理数据文件还原，然后根据实际情况，使用ALTER DATABASE REGISTER LOGFILE 添加日志，进行恢复

Linux中文件大小限制

在日常的维护中，偶尔总担心某个文件会不会因为太大(超过系统限制大小)，导致工作不正常。查找了一些资料，这里对文件大小限制的情况做个记录，以便以后做到心中有底
一、文件大小限制列表

二、查看数据块大小
1、直接查看操作磁盘(Block size)

[root@bas ~]# tune2fs -l /dev/sda2
tune2fs 1.35 (28-Feb-2004)
Filesystem volume name:   /home
…………
Block size:               4096
Fragment size:            4096
…………
Journal backup:           inode blocks

2、查看某个文件(IO Block)

[oracle@bas backup]$ stat full_back.dmp
  File: `full_back.dmp'
  Size: 429687808000    Blocks: 840054384  IO Block: 4096   regular file
Device: 812h/2066d      Inode: 13          Links: 1
Access: (0644/-rw-r--r--)  Uid: (  501/  oracle)   Gid: (  501/oinstall)
Access: 2011-12-18 02:00:25.000000000 +0800
Modify: 2011-12-18 20:49:31.000000000 +0800
Change: 2011-12-18 20:49:31.000000000 +0800