分类目录归档：Oracle备份恢复

ORA-607/ORA-600[4194]不一定是重大灾难

发表于 2012 年 9 月 1 日 由 惜分飞

以前解决过ORA-607/ORA-600[4194]和模拟过ORA-607/ORA-600[4194]错误,所以固定思维任务ORA-607/ORA-600[4194]可能就是重大灾难,通过这个案例来说明ORA-607/ORA-600[4194]可能也就是一个常规的不能再常规的错误:有一网友数据库因意外关闭电源导致启动过程出现ORA-00607/ORA-00600[4194]/ORA-00600[4097]的错误,使得数据库启动失败.

SMON: enabling tx recovery
Fri Aug 31 23:14:08 2012
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=15619
Fri Aug 31 23:14:10 2012
Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc:
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Fri Aug 31 23:14:12 2012
Non-fatal internal error happenned while SMON was doing logging scn->time mapping.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Fri Aug 31 23:14:12 2012
Completed: alter database open
Fri Aug 31 23:14:14 2012
Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc:
ORA-01595: error freeing extent (2) of rollback segment (4))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [48], [34], [], [], [], [], []
Fri Aug 31 23:29:41 2012
Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [17], [10], [], [], [], [], []
Fri Aug 31 23:29:43 2012
Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc:
ORA-00600: internal error code, arguments: [4194], [48], [34], [], [], [], [], []
Fri Aug 31 23:29:44 2012
Errors in file /u01/oradata/orcl/bdump/orcl_pmon_15577.trc:
ORA-00474: SMON process terminated with error
Fri Aug 31 23:29:44 2012
PMON: terminating instance due to error 474
Instance terminated by PMON, pid = 15577

通过alert日志可以定位到SMON_SCN_TIME表或者其回滚操作可能异常,结合alert和trace分析,发现这次错误的操作主要sql语句为:

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4194], [48], [34], [], [], [], [], []
Current SQL statement for this session:
UPDATE SYS.COL_USAGE$
   SET EQUALITY_PREDS    = EQUALITY_PREDS +
                           DECODE(BITAND(:FLAG, 1), 0, 0, 1),
       EQUIJOIN_PREDS    = EQUIJOIN_PREDS +
                           DECODE(BITAND(:FLAG, 2), 0, 0, 1),
       NONEQUIJOIN_PREDS = NONEQUIJOIN_PREDS +
                           DECODE(BITAND(:FLAG, 4), 0, 0, 1),
       RANGE_PREDS       = RANGE_PREDS + DECODE(BITAND(:FLAG, 8), 0, 0, 1),
       LIKE_PREDS        = LIKE_PREDS + DECODE(BITAND(:FLAG, 16), 0, 0, 1),
       NULL_PREDS        = NULL_PREDS + DECODE(BITAND(:FLAG, 32), 0, 0, 1),
       TIMESTAMP         = :TIME
 WHERE OBJ# = :OBJN
   AND INTCOL# = :COLN

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4194], [17], [10], [], [], [], [], []
Current SQL statement for this session:
UPDATE SYS.MON_MODS$
   SET INSERTS       = INSERTS + :INS,
       UPDATES       = UPDATES + :UPD,
       DELETES       = DELETES + :DEL,
       FLAGS        =
       (DECODE(BITAND(FLAGS, :FLAG), :FLAG, FLAGS, FLAGS + :FLAG)),
       DROP_SEGMENTS = DROP_SEGMENTS + :DROPSEG,
       TIMESTAMP     = :TIME
 WHERE OBJ# = :OBJN

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Current SQL statement for this session:
INSERT INTO SMON_SCN_TIME
  (THREAD,
   TIME_MP,
   TIME_DP,
   SCN,
   SCN_WRP,
   SCN_BAS,
   NUM_MAPPINGS,
   TIM_SCN_MAP)
VALUES
  (0, :1, :2, :3, :4, :5, :6, :7)

这里主要涉及到对oracle的三张表的操作
COL_USAGE$:主要是在收集统计信息的时候作为是否需要收集列直方图信息参考
MON_MODS$:Oracle主要利用该表来记录那些表的数据发生改变,方便收集统计信息
SMON_SCN_TIME:记录SCN和TIME的对应关系
通过这里的分析可以确定这三张表中的数据对于数据库来说不是致命的基表信息,在数据库运行过程中可以清理掉这些信息,最多就是因为数据库性能的下降或者SCN和TIME互转功能不完善.

解决思路
完整的undo异常处理顺序
1.从alert中可以看出来数据库是在open之后由于SMON回滚到上述几条sql异常导致数据库down,所以可以尝试使用system回滚段启动数据库,看看是否可以屏蔽相关问题
2.如果方法1不可行,那使用event屏蔽smon对回滚段的相关操作,使得数据库正常启动
3.如果由于存在特殊事务,event无法屏蔽,尝试使用隐含参数处理该问题
4.如果隐含参数尚无法解决给问题考虑使用bbed
5.如果bbed不能解决,那只能选择dul或者其类似工具处理
这个案例中我们明确的看到是因为上面的三条sql回滚异常出现问题导致,对于这样的问题,经过测试使用方法1和2都能够顺利解决问题(open库之后需要重建undo,删除有问题undo表空间,修改参数[可能包括event],切换undo表空间).因为遇到几次ORA-607/ORA-600[4194]是因为system rollback损坏导致,所以这次开始也认为是一次比较复杂的恢复,最后证明这次是一种非常常规的恢复.对于ORACLE的数据库恢复有经验可能会比较快的定位问题,但是如果按照固定的思路去想可能会让自己走进死胡同.

发表在 Oracle备份恢复 | 标签为 , , | 评论关闭

何种情况下imp的fromuser/touser改变tablespace失效

发表于 2012 年 8 月 30 日 由 惜分飞

exp/imp是大家在数据库迁移中最常见的工具,但是该工具对于表空间的转换不是很智能(最少没有datapump方便),使得很多人在导入数据的时候,吃够了表空间不存在的苦.这里有个细节:fromuser和touser在哪些情况下会失效.这里通过试验,简单证明了对于常见的lob对象和分区表对象的时候fromuser和touser修改表空间会失效.
exp/imp支持表空间变化

--创建测试用户
SQL> create user chf identified by xifenfei;  

User created.

SQL> grant dba to chf;

Grant succeeded.

SQL> conn chf/xifenfei
Connected.

--创建测试对象
SQL> create table t_xifenfei01 tablespace users
  2  as
  3  select * from dba_objects;

Table created.

SQL> create index in_t_xifenfei01 on t_xifenfei01(object_id) tablespace xifenfei;

Index created.

SQL> create table t_xifenfei02 tablespace xifenfei
  2  as
  3  select * from dba_objects;

Table created.

SQL> create index in_t_xifenfei02 on t_xifenfei02(object_id) tablespace users;

Index created.

--查询测试对象分布表空间情况
SQL> select OWNER,table_name,TABLESPACE_NAME from dba_tables where table_name like 'T_XIFENFEI%';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
CHF                            T_XIFENFEI01                   USERS
CHF                            T_XIFENFEI02                   XIFENFEI

SQL> SELECT OWNER,INDEX_NAME,TABLESPACE_NAME FROM DBA_INDEXES WHERE INDEX_NAME LIKE 'IN_T_XIFENFEI%';

OWNER                          INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
CHF                            IN_T_XIFENFEI01                XIFENFEI
CHF                            IN_T_XIFENFEI02                USERS

--导出测试对象
[oracle@xifenfei ~]$ exp chf/xifenfei tables=t_xifenfei01,t_xifenfei02 file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log

Export: Release 10.2.0.4.0 - Production on Thu Dec 15 07:33:27 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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                   T_XIFENFEI01      50053 rows exported
. . exporting table                   T_XIFENFEI02      50055 rows exported
Export terminated successfully without warnings.

--为了试验证实,离线该表涉及表空间
SQL> alter tablespace xifenfei read only;

Tablespace altered.

SQL> alter tablespace users read only;

Tablespace altered.

--创建新用户
SQL> create user chf1 identified by xifenfei;

User created.

SQL> grant dba to chf1;

Grant succeeded.

--创建新表空间
SQL> create tablespace xifenfei1 datafile '/u01/oracle/oradata/XFF/xifenfei02.dbf' size 10m autoextend on 
  2  next 10m maxsize 10g;

Tablespace created.

SQL> alter user chf1 default tablespace xifenfei1;

User altered.

--两个测试用户分别默认表空间
SQL> select username,default_tablespace from dba_users where username like 'CHF%';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
CHF                            USERS
CHF1                           XIFENFEI1

--导入测试数据
[oracle@xifenfei ~]$ imp chf1/xifenfei fromuser=chf touser=chf1 file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log

Import: Release 10.2.0.4.0 - Production on Thu Dec 15 07:37:54 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by CHF, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF's objects into CHF1
. . importing table                 "T_XIFENFEI01"      50053 rows imported
. . importing table                 "T_XIFENFEI02"      50055 rows imported
Import terminated successfully without warnings.

--查询导入结果
SQL> SELECT OWNER,INDEX_NAME,TABLESPACE_NAME FROM DBA_INDEXES WHERE INDEX_NAME LIKE 'IN_T_XIFENFEI%' 
  2  and owner='CHF1';

OWNER                          INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
CHF1                           IN_T_XIFENFEI01                XIFENFEI1
CHF1                           IN_T_XIFENFEI02                XIFENFEI1

SQL> select OWNER,table_name,TABLESPACE_NAME from dba_tables where table_name like 'T_XIFENFEI%'
  2  AND OWNER='CHF1';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
CHF1                           T_XIFENFEI01                   XIFENFEI1
CHF1                           T_XIFENFEI02                   XIFENFEI1

通过这里的试验证明:对于无lob对象的普通表和普通index使用fromuser和touser可以实现表空间完美变化

含LOB对象测试

--read write相关表空间
SQL> alter tablespace users read write;

Tablespace altered.

SQL> alter tablespace xifenfei read write;

Tablespace altered.

SQL> create tablespace xifenfei2 datafile '/u01/oracle/oradata/XFF/xifenfei03.dbf' size 10m;

Tablespace created.

SQL> conn chf/xifenfei
Connected.

--创建表,lob分别属于不同空间(数据导入到另外表空间)
SQL> create table t_lob 
  2  (id number,clob1 clob,blob1 blob) tablespace users 
  3   LOB ("CLOB1") STORE AS (   TABLESPACE xifenfei)
  4   LOB ("BLOB1") STORE AS (   TABLESPACE xifenfei1 );

Table created.

SQL> select table_name,COLUMN_NAME,TABLESPACE_NAME from user_lobs;

TABLE_NAME                     COLUMN_NAME     TABLESPACE_NAME
------------------------------ --------------- ------------------------------
T_LOB                          CLOB1           XIFENFEI
T_LOB                          BLOB1           XIFENFEI1

SQL> select tablespace_name from user_tables where table_name='T_LOB';

TABLESPACE_NAME
------------------------------
USERS

--创建表和lob属于一个表空间(数据导入到另外表空间)
SQL>  create table t_lob_n
  2  (id number,clob1 clob) tablespace users;

Table created.

SQL> select segment_name,segment_type,tablespace_name  from user_segments where SEGMENT_NAME not like '%XIFENFEI%';

SEGMENT_NAME                                                                      SEGMENT_TYPE       TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ----------------
SYS_IL0000051858C00002$$                                                          LOBINDEX           USERS
SYS_LOB0000051858C00002$$                                                         LOBSEGMENT         USERS
T_LOB_N                                                                           TABLE              USERS
T_LOB                                                                             TABLE              USERS
SYS_IL0000051851C00002$$                                                          LOBINDEX           XIFENFEI
SYS_LOB0000051851C00002$$                                                         LOBSEGMENT         XIFENFEI
SYS_IL0000051851C00003$$                                                          LOBINDEX           XIFENFEI1
SYS_LOB0000051851C00003$$                                                         LOBSEGMENT         XIFENFEI1

--表和lob不同表空间(数据导入到lob对应表空间)
SQL>  create table t_lob2
  2  (id number,clob1 clob) tablespace users 
  3  LOB ("CLOB1") STORE AS (   TABLESPACE xifenfei2);

Table created.

SQL> select table_name,COLUMN_NAME,TABLESPACE_NAME from user_lobs;

TABLE_NAME                     COLUMN_NAME     TABLESPACE_NAME
------------------------------ --------------- ------------------------------
T_LOB_N                        CLOB1           USERS
T_LOB                          CLOB1           XIFENFEI
T_LOB                          BLOB1           XIFENFEI1
T_LOB2                         CLOB1           XIFENFEI2

SQL>  select segment_name,segment_type,tablespace_name  from user_segments where SEGMENT_NAME not like '%XIFENFEI%';

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
T_LOB2                         TABLE              USERS
SYS_IL0000051858C00002$$       LOBINDEX           USERS
SYS_LOB0000051858C00002$$      LOBSEGMENT         USERS
T_LOB_N                        TABLE              USERS
T_LOB                          TABLE              USERS
SYS_IL0000051851C00002$$       LOBINDEX           XIFENFEI
SYS_LOB0000051851C00002$$      LOBSEGMENT         XIFENFEI
SYS_IL0000051851C00003$$       LOBINDEX           XIFENFEI1
SYS_LOB0000051851C00003$$      LOBSEGMENT         XIFENFEI1
SYS_IL0000051863C00002$$       LOBINDEX           XIFENFEI2
SYS_LOB0000051863C00002$$      LOBSEGMENT         XIFENFEI2

11 rows selected.

SQL> select table_name,COLUMN_NAME,TABLESPACE_NAME,SEGMENT_NAME from user_lobs;

TABLE_NAME                     COLUMN_NAME     TABLESPACE_NAME                SEGMENT_NAME
------------------------------ --------------- ------------------------------ ------------------------------
T_LOB                          BLOB1           XIFENFEI1                      SYS_LOB0000051851C00003$$
T_LOB                          CLOB1           XIFENFEI                       SYS_LOB0000051851C00002$$
T_LOB_N                        CLOB1           USERS                          SYS_LOB0000051858C00002$$
T_LOB2                         CLOB1           XIFENFEI2                      SYS_LOB0000051863C00002$$
--得到在默认情况下LOBINDEX和LOBSEGMENT在同一个表空间

--导出三种情况下lob表
[oracle@xifenfei ~]$ exp chf/xifenfei tables=t_lob_n file=/tmp/lob1.dmp log=/tmp/xifenfei.log indexes=y

Export: Release 10.2.0.4.0 - Production on Thu Dec 15 08:57:38 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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                        T_LOB_N          0 rows exported
Export terminated successfully without warnings.

[oracle@xifenfei ~]$ exp chf/xifenfei tables=t_lob file=/tmp/lob.dmp log=/tmp/xifenfei.log indexes=y

Export: Release 10.2.0.4.0 - Production on Thu Dec 15 08:31:25 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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                          T_LOB          0 rows exported
Export terminated successfully without warnings.

[oracle@xifenfei ~]$ exp chf/xifenfei tables=t_lob2 file=/tmp/lob2.dmp log=/tmp/xifenfei.log

Export: Release 10.2.0.4.0 - Production on Thu Dec 15 16:23:18 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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                         T_LOB2          0 rows exported
Export terminated successfully without warnings.

--修改default tablespace 和 read only相关表空间
SQL> alter user chf1 default tablespace xifenfei2;

User altered.

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace xifenfei read only;

Tablespace altered.

SQL> alter tablespace xifenfei1 read only;

Tablespace altered.

--导入lob表
[oracle@xifenfei ~]$ imp chf1/xifenfei tables=t_lob_n file=/tmp/lob1.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1

Import: Release 10.2.0.4.0 - Production on Thu Dec 15 08:58:12 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by CHF, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF's objects into CHF1
IMP-00017: following statement failed with ORACLE error 1647:
 "CREATE TABLE "T_LOB_N" ("ID" NUMBER, "CLOB1" CLOB)  PCTFREE 10 PCTUSED 40 I"
 "NITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 "
 "BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("CLOB1") ST"
 "ORE AS  (TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCA"
 "CHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POO"
 "L DEFAULT))"
IMP-00003: ORACLE error 1647 encountered
ORA-01647: tablespace 'USERS' is read only, cannot allocate space in it
Import terminated successfully with warnings.
--使用fromuser和touser并未修改table segment初始化参数

[oracle@xifenfei ~]$ imp chf1/xifenfei tables=t_lob file=/tmp/lob.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1

Import: Release 10.2.0.4.0 - Production on Thu Dec 15 08:35:05 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by CHF, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF's objects into CHF1
IMP-00017: following statement failed with ORACLE error 1647:
 "CREATE TABLE "T_LOB" ("ID" NUMBER, "CLOB1" CLOB, "BLOB1" BLOB)  PCTFREE 10 "
 "PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELI"
 "ST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB "
 "("CLOB1") STORE AS  (TABLESPACE "XIFENFEI" ENABLE STORAGE IN ROW CHUNK 8192"
 " RETENTION NOCACHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROU"
 "PS 1 BUFFER_POOL DEFAULT)) LOB ("BLOB1") STORE AS  (TABLESPACE "XIFENFEI1" "
 "ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING  STORAGE(INITIAL"
 " 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 1647 encountered
ORA-01647: tablespace 'USERS' is read only, cannot allocate space in it
Import terminated successfully with warnings.
--结论同上

[oracle@xifenfei ~]$ imp chf1/xifenfei tables=t_lob2 file=/tmp/lob2.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1

Import: Release 10.2.0.4.0 - Production on Thu Dec 15 16:24:03 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by CHF, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF's objects into CHF1
IMP-00017: following statement failed with ORACLE error 1647:
 "CREATE TABLE "T_LOB2" ("ID" NUMBER, "CLOB1" CLOB)  PCTFREE 10 PCTUSED 40 IN"
 "ITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 B"
 "UFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("CLOB1") STO"
 "RE AS  (TABLESPACE "XIFENFEI2" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION N"
 "OCACHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_"
 "POOL DEFAULT))"
IMP-00003: ORACLE error 1647 encountered
ORA-01647: tablespace 'USERS' is read only, cannot allocate space in it
Import terminated successfully with warnings.
--结论也同上

通过三种不同情况的table segment 和lob segment的分别表空间和导入表空间测试情况,可以判断出来在使用exp/imp迁移数据时候,如果遇到含lob字段表,不能通过fromuser和touser来实现修改,就算lob的表空间存在,或者lob和table segment是同一个表空间,而table segment的表空间不存在,依然会报错,导入不成功.

分区表测试

--read write 相关表空间
SQL> alter tablespace users read write;

Tablespace altered.

SQL> alter tablespace xifenfei read write;

Tablespace altered.

SQL> alter tablespace xifenfei1 read write;

Tablespace altered.

--创建分区表
SQL> conn chf/xifenfei
Connected.
SQL> create table tab_par
  2  (
  3    F_KJND VARCHAR2(4) default ' ' not null,
  4    F_CODE VARCHAR2(30) default ' ' not null,
  5    F_KMBH VARCHAR2(30) default ' ' not null,
  6    F_BKBH VARCHAR2(30) default ' ' not null,
  7    UNIT_ID VARCHAR2(30) 
  8  )
  9  partition by range (F_KJND)
 10  (partition TABL_NAME_PT_2009 values less than ('2010')tablespace users,
 11  partition TABL_NAME_PT_2010 values less than ('2011')tablespace xifenfei,
 12  partition TABL_NAME_PT_MAX values less than (MAXVALUE)  tablespace xifenfei1
 13  );

Table created.

--查询分区分布
SQL> select PARTITION_NAME,TABLESPACE_NAME from ALL_TAB_PARTITIONS where TABLE_NAME='TAB_PAR';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
TABL_NAME_PT_2009              USERS
TABL_NAME_PT_2010              XIFENFEI
TABL_NAME_PT_MAX               XIFENFEI1

--导出分区表
[oracle@xifenfei ~]$ exp chf/xifenfei tables=tab_par file=/tmp/tab_par.dmp log=/tmp/xifenfei.log

Export: Release 10.2.0.4.0 - Production on Thu Dec 15 18:33:19 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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                        TAB_PAR
. . exporting partition              TABL_NAME_PT_2009          0 rows exported
. . exporting partition              TABL_NAME_PT_2010          0 rows exported
. . exporting partition               TABL_NAME_PT_MAX          0 rows exported
Export terminated successfully without warnings.

--导入分区表
[oracle@xifenfei ~]$ imp chf1/xifenfei tables=tab_par file=/tmp/tab_par.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1

Import: Release 10.2.0.4.0 - Production on Thu Dec 15 18:33:52 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by CHF, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF's objects into CHF1
. . importing partition  "TAB_PAR":"TABL_NAME_PT_2009"          0 rows imported
. . importing partition  "TAB_PAR":"TABL_NAME_PT_2010"          0 rows imported
. . importing partition   "TAB_PAR":"TABL_NAME_PT_MAX"          0 rows imported
Import terminated successfully without warnings.
--导入成功

--查看导入进入表空间
SQL> select PARTITION_NAME,TABLESPACE_NAME from ALL_TAB_PARTITIONS where TABLE_NAME='TAB_PAR' and TABLE_OWNER='CHF1';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
TABL_NAME_PT_2009              USERS
TABL_NAME_PT_2010              XIFENFEI
TABL_NAME_PT_MAX               XIFENFEI1
--发现还是进入和以前相同的表空间,fromuser和touser未生效

SQL> DROP TABLE CHF1.TAB_PAR PURGE;

Table dropped.

--read only相关表空间测试
SQL> ALTER TABLESPACE USERS READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE XIFENFEI READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE XIFENFEI1 READ ONLY;

Tablespace altered.

--再次导入
[oracle@xifenfei ~]$ imp chf1/xifenfei tables=tab_par file=/tmp/tab_par.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1

Import: Release 10.2.0.4.0 - Production on Thu Dec 15 18:36:38 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by CHF, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF's objects into CHF1
IMP-00017: following statement failed with ORACLE error 1647:
 "CREATE TABLE "TAB_PAR" ("F_KJND" VARCHAR2(4) NOT NULL ENABLE, "F_CODE" VARC"
 "HAR2(30) NOT NULL ENABLE, "F_KMBH" VARCHAR2(30) NOT NULL ENABLE, "F_BKBH" V"
 "ARCHAR2(30) NOT NULL ENABLE, "UNIT_ID" VARCHAR2(30))  PCTFREE 10 PCTUSED 40"
 " INITRANS 1 MAXTRANS 255 TABLESPACE "USERS" LOGGING PARTITION BY RANGE ("F_"
 "KJND" )  (PARTITION "TABL_NAME_PT_2009" VALUES LESS THAN ('2010')  PCTFREE "
 "10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FRE"
 "ELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS, "
 "PARTITION "TABL_NAME_PT_2010" VALUES LESS THAN ('2011')  PCTFREE 10 PCTUSED"
 " 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROU"
 "PS 1 BUFFER_POOL DEFAULT) TABLESPACE "XIFENFEI" LOGGING NOCOMPRESS, PARTITI"
 "ON "TABL_NAME_PT_MAX" VALUES LESS THAN (MAXVALUE)  PCTFREE 10 PCTUSED 40 IN"
 "ITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 B"
 "UFFER_POOL DEFAULT) TABLESPACE "XIFENFEI1" LOGGING NOCOMPRESS )"
IMP-00003: ORACLE error 1647 encountered
ORA-01647: tablespace 'USERS' is read only, cannot allocate space in it
Import terminated successfully with warnings.
--进步一证明分区表在导入的时候fromuser和touser未能改变其对应表空间

通过对分区表的测试,证明exp/imp在操作分区表的时候fromuser和touser也不能实现表空间的转换

在使用imp和exp实现数据迁移的时候,遇到我们常见的lob和分区表时候fromuser和touser修改表空间会失效,数据还是会导入到原对象锁对应的表空间,所以在处理含这些对象的数据迁移时,一般方法有:1.创建好这些对象所属表空间;2.先导出来这些对象对应的创建脚本,创建好这些对象,然后使用IGNORE=Y导入

发表在 逻辑备份/恢复 | 评论关闭

通过odu验证rman backup对于truncate对象备份处理

发表于 2012 年 8 月 27 日 由 惜分飞

rman backup 对于truncate和drop等相关操作的extent到底是怎么处理的,这里通过rman backup 结合odu证明出来,在较新版本的rman中,rman backup 并未完全的备份这些被认为不需要的extent.
创建模拟环境

SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create tablespace xifenfei datafile '/u01/oracle/oradata/XFF/xifenfei01.dbf' 
   2 size 10m autoextend on maxsize 10g;

Tablespace created.

SQL> conn chf/xifenfei
Connected.

SQL> create table t_xifenfei tablespace xifenfei
  2  as
  3  select * from dba_objects;

Table created.

SQL> insert into t_xifenfei
  2  select * from dba_objects;

50055 rows created.

SQL> commit;

Commit complete.

SQL> select BYTES from dba_free_space where TABLESPACE_NAME='XIFENFEI';

     BYTES
----------
    983040

SQL> select BYTES from v$datafile where name='/u01/oracle/oradata/XFF/xifenfei01.dbf';

     BYTES
----------
  12582912

SQL> select 12582912-983040 from dual;

12582912-983040
---------------
       11599872

SQL> select object_id,data_object_id from dba_objects where object_name='T_XIFENFEI';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     51833          51833

--这里我们得到信息有:
--1.dataobj#=51833
--2.使用数据文件空间为:11599872

rman备份no truncate table 数据文件

[oracle@xifenfei ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Dec 15 06:00:05 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: XFF (DBID=3440302261)

RMAN> backup tablespace xifenfei format '/u01/oracle/oradata/tmp/no_truncate_xifenfei';

Starting backup at 15-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/oracle/oradata/XFF/xifenfei01.dbf
channel ORA_DISK_1: starting piece 1 at 15-DEC-11
channel ORA_DISK_1: finished piece 1 at 15-DEC-11
piece handle=/u01/oracle/oradata/tmp/no_truncate_xifenfei tag=TAG20111215T060343 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-DEC-11

truncate table 操作

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 15 06:03:58 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> truncate table chf.t_xifenfei;

Table truncated.

rman备份truncate table 数据文件

RMAN> backup tablespace xifenfei format '/u01/oracle/oradata/tmp/truncate_xifenfei';

Starting backup at 15-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/oracle/oradata/XFF/xifenfei01.dbf
channel ORA_DISK_1: starting piece 1 at 15-DEC-11
channel ORA_DISK_1: finished piece 1 at 15-DEC-11
piece handle=/u01/oracle/oradata/tmp/truncate_xifenfei tag=TAG20111215T060445 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-DEC-11

[root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/*_xifenfei
-rw-r----- 1 oracle oinstall 11640832 Dec 15 06:03 /u01/oracle/oradata/tmp/no_truncate_xifenfei
-rw-r----- 1 oracle oinstall   630784 Dec 15 06:04 /u01/oracle/oradata/tmp/truncate_xifenfei

odu挖rman备份前数据文件

ODU> unload dict
CLUSTER C_USER# file_no: 1 block_no: 89
TABLE OBJ$ file_no: 1 block_no: 121
CLUSTER C_OBJ# file_no: 1 block_no: 25
CLUSTER C_OBJ# file_no: 1 block_no: 25
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found TABPART$'s obj# 266
found TABPART$'s dataobj#:266,ts#:0,file#:1,block#:2121,tab#:0
found INDPART$'s obj# 271
found INDPART$'s dataobj#:271,ts#:0,file#:1,block#:2161,tab#:0
found TABSUBPART$'s obj# 278
found TABSUBPART$'s dataobj#:278,ts#:0,file#:1,block#:2217,tab#:0
found INDSUBPART$'s obj# 283
found INDSUBPART$'s dataobj#:283,ts#:0,file#:1,block#:2257,tab#:0
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found LOB$'s obj# 151
found LOB$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:6
found LOBFRAG$'s obj# 299
found LOBFRAG$'s dataobj#:299,ts#:0,file#:1,block#:2393,tab#:0

ODU> scan extent tablespace 6

scan extent start: 2011-12-15 06:12:28
scanning extent...
scanning extent finished.
scan extent completed: 2011-12-15 06:12:28

ODU> unload table chf.t_xifenfei object 51833 

Unloading table: T_XIFENFEI,object ID: 51833
Unloading segment,storage(Obj#=51833 DataObj#=51833 TS#=6 File#=5 Block#=11 Cluster=0)
100110 rows unloaded
--这里可以看到odu全部找到被truncate掉的记录条数

使用rman 备份后数据文件

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@xifenfei odu]$ rm /u01/oracle/oradata/XFF/xifenfei01.dbf
[oracle@xifenfei odu]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Dec 15 06:14:00 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     318767104 bytes

Fixed Size                     1267236 bytes
Variable Size                104860124 bytes
Database Buffers             205520896 bytes
Redo Buffers                   7118848 bytes

RMAN> restore datafile 5;

Starting restore at 15-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /u01/oracle/oradata/XFF/xifenfei01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/truncate_xifenfei
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oracle/oradata/tmp/truncate_xifenfei tag=TAG20111215T060445
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-DEC-11

odu挖rman还原后数据文件

ODU> scan extent tablespace 6

scan extent start: 2011-12-15 06:14:43
scanning extent...
scanning extent finished.
scan extent completed: 2011-12-15 06:14:43

ODU>  unload table chf.t_xifenfei object 51833 

Unloading table: T_XIFENFEI,object ID: 51833
Unloading segment,storage(Obj#=51833 DataObj#=51833 TS#=6 File#=5 Block#=11 Cluster=0)
4774 rows unloaded
--odu只找到极少数数据4774/100110

通过odu挖rman备份前和备份后的数据文件,得知rman backup备份的过程,对绝大多数truncate的表的原始数据未正常备份(为什么是绝大多数,我无法给出解释),这里也可以看出rman backup并非是真正意义上的完全物理上复制(和rman copy还是有区别,copy不能完全被取代)

发表在 rman备份/恢复 | 2 条评论