分类目录归档：数据库

诡异dblink问题解决

发表于 2011 年 8 月 26 日由惜分飞

一、诡异dblink起源
今天开发拿过来一条sql，说有诡异现象，sql如下

INSERT INTO TAB_CS_CALLLOG
select c.user_logon,/*工号*/c.user_name,/*姓名*/a.call_id,/*通话id*/
      a.caller_nbr,/*主叫号码*/a.called_nbr,/*被叫号码*/d.start_time,/*呼入时间*/
      b.call_time,/*接听时间*/b.end_time,/*结束时间*/b.call_dura,/*时长*/
      to_number(b.call_time-d.start_time)*24*3600,/*等待时长*/
      decode(c.user_logon,null,0,1),/*类型*/
case
        when substr (a.CALLED_NBR,7, 2) = '00'
          then '广东移动'
        when substr (a.CALLED_NBR,7, 2) = '01'
          then '浙江移动'
        when substr (a.CALLED_NBR,7, 2) = '02'
          then '福建'
        when substr (a.CALLED_NBR,7, 2) = '03'
          then '四川'
        when substr (a.CALLED_NBR,7, 2) = '04'
          then '河南'
        when substr (a.CALLED_NBR,7, 2) = '05'
          then '湖北'
        when substr (a.CALLED_NBR,7, 2) = '06'
          then '北京CSIP'
        when substr (a.CALLED_NBR,7, 2) = '07'
          then '陕西'
        when substr (a.CALLED_NBR,7, 2) = '08'
          then '吉林'
        when substr (a.CALLED_NBR,7, 2) = '09'
          then '江西'
        when substr (a.CALLED_NBR,7, 2) = '10'
          then '宁夏'
        when substr (a.CALLED_NBR,7, 2) = '11'
          then '太原'
        when substr (a.CALLED_NBR,7, 2) = '12'
          then '江苏移动'
        when substr (a.CALLED_NBR,7, 2) = '13'
          then 'e掌管'
        when substr (a.CALLED_NBR,7, 2) = '15'
          then 'e多商'
        when substr (a.CALLED_NBR,7, 2) = '18'
          then '江苏联通'
      end 区域
from cscnew.a@cs a,cscnew.b@cs b,cscnew.c@cs c,cscnew.d@cs d
where a.call_serial=b.call_serial(+)
and b.call_serial=d.call_serial(+)
and b.user_id=c.user_id(+)
and substr(a.CALLED_NBR,1, 6) = '951654'
and  length (a.CALLED_NBR) = 15
and b.fail_reason is null
and a.end_time>=to_date('20110822000000','yyyymmddhh24miss')
and a.end_time<to_date('20110823000000','yyyymmddhh24miss');

然后我进行了测试，证实了她所说的诡异：
1、直接执行select语句需要1.7S左右，但是加上insert inot后，执行时间需要6分钟
2、直接select结果集为602条，加上insert into后，结果集为598条（少4条），如果直接执行select，除掉and b.fail_reason is null限制条件也刚好602条

二、查询相关资料，得到dblink的一些解释
1、dblink执行有两种方式，一种是在远处数据库执行完，然后结果返回，另一种是把远程的表下载到本来，然后执行
2、如果把远程的表下载到本地，空值或者null可能会发生变化（怀疑是空值转化为null，未证实）

三、根据这些解释，进行猜想
1、只执行select的时候，应该是在远程执行完，传输结果回来；而执行insert into的时候，是把远程的表全部下载到本地，然后执行出结果，而数据量本身比较大，所以比较慢
2、在把表从远程下载到本地的过程中，fail_reason 列的null值可以发生了变化，或者空值变为了null，所以数据多了4条

四、事实证明猜想
1、查询远程表大小，发现a表50m，b表400m，c表10m，d表100m左右，传输过来需要一定的时间
2、既然猜测是由于要把表传输到本地而导致这样的结果产生，那么处理方法就是让程序在远程计算出结果，然后传输到本地，查询了一些资料，上面说insert into会导致driving_site提示无效，那么我想到一个用视图的办法解决这个问题：在目标端建立一个关于本查询中无参数的视图，然后在本地通过dblink调用视图，这样总该先在远程执行出结果传输到本地了吧。
2.1）建立目标端视图

create or replace view v_tab
select c.user_logon,/*工号*/c.user_name,/*姓名*/a.call_id,/*通话id*/
      a.caller_nbr,/*主叫号码*/a.called_nbr,/*被叫号码*/d.start_time,/*呼入时间*/
      b.call_time,/*接听时间*/b.end_time,/*结束时间*/b.call_dura,/*时长*/
      to_number(b.call_time-d.start_time)*24*3600,/*等待时长*/
      decode(c.user_logon,null,0,1),/*类型*/
case
        when substr (a.CALLED_NBR,7, 2) = '00'
          then '广东移动'
        when substr (a.CALLED_NBR,7, 2) = '01'
          then '浙江移动'
        when substr (a.CALLED_NBR,7, 2) = '02'
          then '福建'
        when substr (a.CALLED_NBR,7, 2) = '03'
          then '四川'
        when substr (a.CALLED_NBR,7, 2) = '04'
          then '河南'
        when substr (a.CALLED_NBR,7, 2) = '05'
          then '湖北'
        when substr (a.CALLED_NBR,7, 2) = '06'
          then '北京CSIP'
        when substr (a.CALLED_NBR,7, 2) = '07'
          then '陕西'
        when substr (a.CALLED_NBR,7, 2) = '08'
          then '吉林'
        when substr (a.CALLED_NBR,7, 2) = '09'
          then '江西'
        when substr (a.CALLED_NBR,7, 2) = '10'
          then '宁夏'
        when substr (a.CALLED_NBR,7, 2) = '11'
          then '太原'
        when substr (a.CALLED_NBR,7, 2) = '12'
          then '江苏移动'
        when substr (a.CALLED_NBR,7, 2) = '13'
          then 'e掌管'
        when substr (a.CALLED_NBR,7, 2) = '15'
          then 'e多商'
        when substr (a.CALLED_NBR,7, 2) = '18'
          then '江苏联通'
      end 区域
from cscnew.a a,cscnew.b b,cscnew.c c,cscnew.d d
where a.call_serial=b.call_serial(+)
and b.call_serial=d.call_serial(+)
and b.user_id=c.user_id(+)
and substr(a.CALLED_NBR,1, 6) = '951654'
and  length (a.CALLED_NBR) = 15
and b.fail_reason is null

2.2）本地调用远程视图

INSERT INTO TAB_CS_CALLLOG
select * from v_tab@cs a where 
a.end_time>=to_date('20110822000000','yyyymmddhh24miss')
and a.end_time<to_date('20110823000000','yyyymmddhh24miss');

2.3）执行结果2.3S完成数据插入，而且条数也是598条，证明我的猜想是正确的，更重要的是解决了今天这个让人疑惑的问题

发表在 Oracle | 一条评论

undo异常处理步骤（10g）

发表于 2011 年 8 月 25 日由惜分飞

1、启动数据库，发现错误
startup

2、查看是否是undo文件损坏引起
select a.ts#,a.name,b.name from v$datafile a,v$tablespace b where a.ts#=b.ts#;

3、损坏undo离线
alter database datafile n offline drop;

4、打开数据库
alter database open;

5、查看当前undo表空间，并确认是否是当前undo损坏，如果不是当前undo，直接执行8，否则继续
show parameter undo_tablespace;

6、创建新undo表空间
CREATE UNDO TABLESPACE UNDOTBSNEW DATAFILE
‘/opt/oracle/oradata/xifenfei/UNDOTBS01.dbf
SIZE 50M autoextend on next 10m maxsize 30G;

7、修改默认undo为新创建表空间
alter system set undo_tablespace=’UNDOTBSNEW’;

8、查询回滚段信息，为下面删除损坏undo的回滚段做到心中有底
select segment_name,status from dba_rollback_segs;

9、删除损坏undo，如果报ORA-01548错误，继续下面操作，否则跳到14
drop tablespace UNDOTBSOLD including contents and datafiles;

10、创建pfile文件
create pfile=’/tmp/pfile’ from spfile;

11、修改pfile文件，添加隐含参数
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,
_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

12、使用pfile启动数据库
startup pfile=’/tmp/pfile’

13、删除损坏undo
drop tablespace UNDOTBSOLD including contents and datafiles;

14、查看回滚段状态
select segment_name,status from dba_rollback_segs;

15、如果有损坏表空间回滚段还存在，手工删除
drop rollback segment “_SYSSMUx$”;

16、重启数据库
shutdown immediate
startup

发表在 Oracle备份恢复 | 6 条评论

当前联机日志损坏恢复

发表于 2011 年 8 月 24 日由惜分飞

环境模拟
删除状态为active的联机日志，然后强行关闭数据库

处理过程
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1260720 bytes
Variable Size 142607184 bytes
Database Buffers 16777216 bytes
Redo Buffers 7127040 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

Alert.log 日志中错误
Wed Aug 24 00:26:33 2011
Errors in file /u01/admin/xienfei/udump/xff_ora_9186.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

xff_ora_9186.trc文件中错误
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

查询当前日志组状态
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;

GROUP# STATUS MEMBER
———- —————- ———————————————
1 CURRENT /u01/oradata/xienfei/redo01.log
3 INACTIVE /u01/oradata/xienfei/redo03.log
2 ACTIVE /u01/oradata/xienfei/redo02.log

尝试删除redo日志
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance xff (thread 1) – cannot drop
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’

发现是当前日志不能被删除，尝试切换日志
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

在数据库未打开状态，不能切换日志，只能尝试清空日志
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance xff (thread 1)
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’

因为数据库恢复需要使用，不能被清空，尝试不完成恢复
SQL> recover database until cancel;
ORA-00279: change 1272687 generated at 08/24/2011 00:20:05 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_27_756841839.arc
ORA-00280: change 1272687 for thread 1 is in sequence #27

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1272903 generated at 08/24/2011 00:25:17 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_28_756841839.arc
ORA-00280: change 1272903 for thread 1 is in sequence #28
ORA-00278: log file ‘/u01/archive/1_27_756841839.arc’ no longer needed for this
recovery

ORA-00308: cannot open archived log ‘/u01/archive/1_28_756841839.arc’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u01/oradata/xienfei/system01.dbf’

已经提示数据不一致，尝试着打开数据库
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u01/oradata/xienfei/system01.dbf’

创建pfile文件，添加隐含参数，使之不进行检查点一致性校验
SQL> create pfile=’/tmp/pfile’ from spfile;

File created.

修改pfile ，添加以下参数
*._allow_resetlogs_corruption=TRUE
*._allow_error_simulation=TRUE

SQL> shutdown abort
ORACLE instance shut down.

使用pfile打开数据库
SQL> startup pfile=’/tmp/pfile’
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1260720 bytes
Variable Size 150995792 bytes
Database Buffers 8388608 bytes
Redo Buffers 7127040 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database

发现flashback导致数据库不能被正常打开，尝试关闭它
SQL> alter database flashback off;

Database altered.

尝试直接open数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

根据提示，使用resetlogs打开数据库
SQL> alter database open resetlogs;

Database altered.

查询日志状态
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;

GROUP# STATUS MEMBER
———- —————- ———————————————
3 UNUSED /u01/oradata/xienfei/redo03.log
2 UNUSED /u01/oradata/xienfei/redo02.log
1 CURRENT /u01/oradata/xienfei/redo01.log

因为group1错误，而当前日志组在group 1上，所以切换日志组
SQL> alter system switch logfile;

System altered.

SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;

GROUP# STATUS MEMBER
———- —————- ———————————————
3 UNUSED /u01/oradata/xienfei/redo03.log
2 CURRENT /u01/oradata/xienfei/redo02.log
1 ACTIVE /u01/oradata/xienfei/redo01.log

SQL> alter system checkpoint;

System altered.

SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;

GROUP# STATUS MEMBER
———- —————- ———————————————
3 UNUSED /u01/oradata/xienfei/redo03.log
2 CURRENT /u01/oradata/xienfei/redo02.log
1 INACTIVE /u01/oradata/xienfei/redo01.log
删除有问题的group 1日志组
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter system switch logfile;

System altered.

添加日志组并检查是否正确
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;

GROUP# STATUS MEMBER
———- —————- ———————————————
3 CURRENT /u01/oradata/xienfei/redo03.log
2 ACTIVE /u01/oradata/xienfei/redo02.log

SQL> alter database add logfile group 1 ‘/u01/oradata/xienfei/redo01.log’ size 50m reuse;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;

GROUP# STATUS MEMBER
———- —————- ———————————————
3 ACTIVE /u01/oradata/xienfei/redo03.log
2 ACTIVE /u01/oradata/xienfei/redo02.log
1 CURRENT /u01/oradata/xienfei/redo01.log

注意：根据oracle官方建议，使用oracle隐含参数运行数据库可能存在很多不稳定因素，建议立即导出数据库数据，然后新建库，重新导入数据

发表在 Oracle备份恢复 | 一条评论

17813235971

QQ 咨询

分类目录归档：数据库

诡异dblink问题解决

undo异常处理步骤（10g）

当前联机日志损坏恢复

文章分类

最近发表

分类目录归档：数据库

诡异dblink问题解决

undo异常处理步骤（10g）

当前联机日志损坏恢复

标签云

文章分类

最近发表