Oracle RAC业务bug导致部分数据丢失处理
问题描述：业务部门在10月26日发现某张基础表中丢失部分数据，系为9月份录入系统的基础数据丢失
Oracle RAC环境做的RMAN备份，10月18日做过expdp数据泵备份，丢失数据表名为T_GL_BALANCE，初始化表t_gl_balance_inibak
1. 查询数据操作记录
SQL>select t.SQL_TEXT, t.FIRST_LOAD_TIME from v$sqlarea t where t.FIRST_LOAD_TIME like ‘2019-10-%‘ order by t.FIRST_LOAD_TIME desc;
过滤出对T_GL_BALANCE数据表删除操作记录如下：
DELETE FROM T_GL_BALANCE WHERE (((FYEAR = :FNEXTYEAR AND FPERIOD = :FNEXTPERIOD) AND FACCOUNTBOOKID = :FACCOUNTBOOKID) AND FADJUSTPERIOD = 0) 2019-10-26/11:10:48
DELETE FROM T_GL_BALANCE WHERE (((FYEAR = :FYEAR AND FPERIOD = :FPERIOD) AND FACCOUNTBOOKID = :FACCOUNTBOOKID) AND FADJUSTPERIOD = 0) 2019-10-26/11:07:18
delete T_GL_BALANCE where T_GL_BALANCE.FACCOUNTBOOKID = :FACCOUNTBOOKID and T_GL_BALANCE.FACCOUNTID = :FACCOUNTID and T_GL_BALANCE.FYEAR = :FYEAR and T_GL_BALANCE.FPERIOD = :FPERIOD and T_GL_BALANCE.FCURRENCYID = :FCURRENCYID and T_GL_BALANCE.FDETAILID = :FDETAILID and T_GL_BALANCE.FADJUSTPERIOD = :FADJUSTPERIOD 2019-10-26/10:05:58
DELETE FROM T_GL_BALANCE WHERE EXISTS (SELECT 1 FROM TMP13ACDA3FF7C211E980B40050569 T1 WHERE ((((((t1.FAccountBookId = T_GL_BALANCE.FAccountBookId AND t1.FAccountId = T_GL_BALANCE.FAccountId) AND t1.FDetailId = T_GL_BALANCE.FDetailId) AND t1.FCurrencyId = T_GL_BALANCE.FCurrencyId) AND t1.FYear = T_GL_BALANCE.FYear) AND t1.FPeriod = T_GL_BALANCE.FPeriod) AND t1.FAdjustPeriod = T_GL_BALANCE.FAdjustPeriod)) 2019-10-26/15:25:20
DELETE FROM T_GL_BALANCE WHERE EXISTS (SELECT 1 FROM TMP9D482DDAF7C111E980B40050569 T1 WHERE ((((((t1.FAccountBookId = T_GL_BALANCE.FAccountBookId AND t1.FAccountId = T_GL_BALANCE.FAccountId) AND t1.FDetailId = T_GL_BALANCE.FDetailId) AND t1.FCurrencyId = T_GL_BALANCE.FCurrencyId) AND t1.FYear = T_GL_BALANCE.FYear) AND t1.FPeriod = T_GL_BALANCE.FPeriod) AND t1.FAdjustPeriod = T_GL_BALANCE.FAdjustPeriod)) 2019-10-26/15:22:03
DELETE FROM T_GL_BALANCE WHERE EXISTS (SELECT 1 FROM TMPF5CD11B1F7C011E980B40050569 T1 WHERE ((((((t1.FAccountBookId = T_GL_BALANCE.FAccountBookId AND t1.FAccountId = T_GL_BALANCE.FAccountId) AND t1.FDetailId = T_GL_BALANCE.FDetailId) AND t1.FCurrencyId = T_GL_BALANCE.FCurrencyId) AND t1.FYear = T_GL_BALANCE.FYear) AND t1.FPeriod = T_GL_BALANCE.FPeriod) AND t1.FAdjustPeriod = T_GL_BALANCE.FAdjustPeriod)) 2019-10-26/15:17:21
DELETE FROM T_GL_BALANCE WHERE EXISTS (SELECT 1 FROM TMP1D0C04A3F7B711E980B40050569 T1 WHERE ((((((t1.FAccountBookId = T_GL_BALANCE.FAccountBookId AND t1.FAccountId = T_GL_BALANCE.FAccountId) AND t1.FDetailId = T_GL_BALANCE.FDetailId) AND t1.FCurrencyId = T_GL_BALANCE.FCurrencyId) AND t1.FYear = T_GL_BALANCE.FYear) AND t1.FPeriod = T_GL_BALANCE.FPeriod) AND t1.FAdjustPeriod = T_GL_BALANCE.FAdjustPeriod)) 2019-10-26/14:06:52
DELETE FROM T_GL_BALANCE WHERE EXISTS (SELECT 1 FROM TMP7AAD91ECF7B311E980B40050569 T1 WHERE ((((((t1.FAccountBookId = T_GL_BALANCE.FAccountBookId AND t1.FAccountId = T_GL_BALANCE.FAccountId) AND t1.FDetailId = T_GL_BALANCE.FDetailId) AND t1.FCurrencyId = T_GL_BALANCE.FCurrencyId) AND t1.FYear = T_GL_BALANCE.FYear) AND t1.FPeriod = T_GL_BALANCE.FPeriod) AND t1.FAdjustPeriod = T_GL_BALANCE.FAdjustPeriod)) 2019-10-26/13:40:51
DELETE FROM T_GL_BALANCE WHERE EXISTS (SELECT 1 FROM TMP774CBE64F7B111E980B40050569 T1 WHERE ((((((t1.FAccountBookId = T_GL_BALANCE.FAccountBookId AND t1.FAccountId = T_GL_BALANCE.FAccountId) AND t1.FDetailId = T_GL_BALANCE.FDetailId) AND t1.FCurrencyId = T_GL_BALANCE.FCurrencyId) AND t1.FYear = T_GL_BALANCE.FYear) AND t1.FPeriod = T_GL_BALANCE.FPeriod) AND t1.FAdjustPeriod = T_GL_BALANCE.FAdjustPeriod)) 2019-10-26/13:26:26
DELETE FROM T_GL_BALANCE WHERE EXISTS (SELECT 1 FROM TMP0487A93FF7B111E980B40050569 T1 WHERE ((((((t1.FAccountBookId = T_GL_BALANCE.FAccountBookId AND t1.FAccountId = T_GL_BALANCE.FAccountId) AND t1.FDetailId = T_GL_BALANCE.FDetailId) AND t1.FCurrencyId = T_GL_BALANCE.FCurrencyId) AND t1.FYear = T_GL_BALANCE.FYear) AND t1.FPeriod = T_GL_BALANCE.FPeriod) AND t1.FAdjustPeriod = T_GL_BALANCE.FAdjustPeriod)) 2019-10-26/13:23:14
DELETE FROM T_GL_BALANCE WHERE EXISTS (SELECT 1 FROM TMPF586BB71F7B011E980B40050569 T1 WHERE ((((((t1.FAccountBookId = T_GL_BALANCE.FAccountBookId AND t1.FAccountId = T_GL_BALANCE.FAccountId) AND t1.FDetailId = T_GL_BALANCE.FDetailId) AND t1.FCurrencyId = T_GL_BALANCE.FCurrencyId) AND t1.FYear = T_GL_BALANCE.FYear) AND t1.FPeriod = T_GL_BALANCE.FPeriod) AND t1.FAdjustPeriod = T_GL_BALANCE.FAdjustPeriod)) 2019-10-26/13:22:49
DELETE FROM T_GL_BALANCE WHERE EXISTS (SELECT 1 FROM TMP7C479968F7B011E980B40050569 T1 WHERE ((((((t1.FAccountBookId = T_GL_BALANCE.FAccountBookId AND t1.FAccountId = T_GL_BALANCE.FAccountId) AND t1.FDetailId = T_GL_BALANCE.FDetailId) AND t1.FCurrencyId = T_GL_BALANCE.FCurrencyId) AND t1.FYear = T_GL_BALANCE.FYear) AND t1.FPeriod = T_GL_BALANCE.FPeriod) AND t1.FAdjustPeriod = T_GL_BALANCE.FAdjustPeriod)) 2019-10-26/13:19:28
DELETE FROM T_GL_BALANCE WHERE EXISTS (SELECT 1 FROM TMP6C2D55C5F7B011E980B40050569 T1 WHERE ((((((t1.FAccountBookId = T_GL_BALANCE.FAccountBookId AND t1.FAccountId = T_GL_BALANCE.FAccountId) AND t1.FDetailId = T_GL_BALANCE.FDetailId) AND t1.FCurrencyId = T_GL_BALANCE.FCurrencyId) AND t1.FYear = T_GL_BALANCE.FYear) AND t1.FPeriod = T_GL_BALANCE.FPeriod) AND t1.FAdjustPeriod = T_GL_BALANCE.FAdjustPeriod)) 2019-10-26/13:18:58
DELETE FROM T_GL_BALANCE WHERE EXISTS (SELECT 1 FROM TMP0DF8AC70F7B711E980B40050569 T1 WHERE ((((((t1.FAccountBookId = T_GL_BALANCE.FAccountBookId AND t1.FAccountId = T_GL_BALANCE.FAccountId) AND t1.FDetailId = T_GL_BALANCE.FDetailId) AND t1.FCurrencyId = T_GL_BALANCE.FCurrencyId) AND t1.FYear = T_GL_BALANCE.FYear) AND t1.FPeriod = T_GL_BALANCE.FPeriod) AND t1.FAdjustPeriod = T_GL_BALANCE.FAdjustPeriod)) 2019-10-26/14:06:26
DELETE FROM T_GL_BALANCE WHERE EXISTS (SELECT 1 FROM TMPB5F9D60EF7B511E980B40050569 T1 WHERE ((((((t1.FAccountBookId = T_GL_BALANCE.FAccountBookId AND t1.FAccountId = T_GL_BALANCE.FAccountId) AND t1.FDetailId = T_GL_BALANCE.FDetailId) AND t1.FCurrencyId = T_GL_BALANCE.FCurrencyId) AND t1.FYear = T_GL_BALANCE.FYear) AND t1.FPeriod = T_GL_BALANCE.FPeriod) AND t1.FAdjustPeriod = T_GL_BALANCE.FAdjustPeriod)) 2019-10-26/13:56:49
DELETE FROM T_GL_BALANCE WHERE EXISTS (SELECT 1 FROM TMPA1CD916AF7B511E980B40050569 T1 WHERE ((((((t1.FAccountBookId = T_GL_BALANCE.FAccountBookId AND t1.FAccountId = T_GL_BALANCE.FAccountId) AND t1.FDetailId = T_GL_BALANCE.FDetailId) AND t1.FCurrencyId = T_GL_BALANCE.FCurrencyId) AND t1.FYear = T_GL_BALANCE.FYear) AND t1.FPeriod = T_GL_BALANCE.FPeriod) AND t1.FAdjustPeriod = T_GL_BALANCE.FAdjustPeriod)) 2019-10-26/13:56:15
DELETE FROM T_GL_BALANCE WHERE (FAccountBookId = :FAccountBookId AND (((((FAccountId IN (102520)) AND (((FYear * 100) + FPeriod) BETWEEN :FMin AND :FMax)) AND (FCurrencyId = :FCurrencyId OR FCurrencyId = 0)) AND (FDetailId >= 0)) AND EXISTS (SELECT 1 FROM T_GL_BALANCE T1 WHERE ((T_GL_BALANCE.FDetailId = t1.FDetailId AND t1.FAccountBookId = :FBookId) AND (((t1.FAccountId IN (102520)) AND (((t1.FYear * 100) + t1.FPeriod) BETWEEN :FYearMin AND :FYearMax)) AND t1.FCurrencyId = :FCyId))))) 2019-10-26/14:06:51
DELETE FROM T_GL_BALANCE WHERE (FAccountBookId = :FAccountBookId AND (((((FAccountId IN (102509)) AND (((FYear * 100) + FPeriod) BETWEEN :FMin AND :FMax)) AND (FCurrencyId = :FCurrencyId OR FCurrencyId = 0)) AND (FDetailId >= 0)) AND EXISTS (SELECT 1 FROM T_GL_BALANCE T1 WHERE ((T_GL_BALANCE.FDetailId = t1.FDetailId AND t1.FAccountBookId = :FBookId) AND (((t1.FAccountId IN (102509)) AND (((t1.FYear * 100) + t1.FPeriod) BETWEEN :FYearMin AND :FYearMax)) AND t1.FCurrencyId = :FCyId))))) 2019-10-26/13:56:14
DELETE FROM T_GL_BALANCE WHERE (FAccountBookId = :FAccountBookId AND (((((FAccountId IN (4123)) AND (((FYear * 100) + FPeriod) BETWEEN :FMin AND :FMax)) AND (FCurrencyId = :FCurrencyId OR FCurrencyId = 0)) AND (FDetailId >= 0)) AND EXISTS (SELECT 1 FROM T_GL_BALANCE T1 WHERE ((T_GL_BALANCE.FDetailId = t1.FDetailId AND t1.FAccountBookId = :FBookId) AND (((t1.FAccountId IN (4123)) AND (((t1.FYear * 100) + t1.FPeriod) BETWEEN :FYearMin AND :FYearMax)) AND t1.FCurrencyId = :FCyId))))) 2019-10-26/10:47:49
DELETE FROM T_GL_BALANCE WHERE (FAccountBookId = :FAccountBookId AND (((((FAccountId IN (4002)) AND (((FYear * 100) + FPeriod) BETWEEN :FMin AND :FMax)) AND (FCurrencyId = :FCurrencyId OR FCurrencyId = 0)) AND (FDetailId >= 0)) AND EXISTS (SELECT 1 FROM T_GL_BALANCE T1 WHERE ((T_GL_BALANCE.FDetailId = t1.FDetailId AND t1.FAccountBookId = :FBookId) AND (((t1.FAccountId IN (4002)) AND (((t1.FYear * 100) + t1.FPeriod) BETWEEN :FYearMin AND :FYearMax)) AND t1.FCurrencyId = :FCyId))))) 2019-10-26/10:05:42
DELETE FROM T_GL_BALANCE WHERE (FAccountBookId = :FAccountBookId AND (((((FAccountId IN (103033)) AND (((FYear * 100) + FPeriod) BETWEEN :FMin AND :FMax)) AND (FCurrencyId = :FCurrencyId OR FCurrencyId = 0)) AND (FDetailId >= 0)) AND EXISTS (SELECT 1 FROM T_GL_BALANCE T1 WHERE ((T_GL_BALANCE.FDetailId = t1.FDetailId AND t1.FAccountBookId = :FBookId) AND (((t1.FAccountId IN (103033)) AND (((t1.FYear * 100) + t1.FPeriod) BETWEEN :FYearMin AND :FYearMax)) AND t1.FCurrencyId = :FCyId))))) 2019-10-25/11:34:36
DELETE FROM T_GL_BALANCE WHERE (FAccountBookId = :FAccountBookId AND (((((FAccountId IN (4007)) AND (((FYear * 100) + FPeriod) BETWEEN :FMin AND :FMax)) AND (FCurrencyId = :FCurrencyId OR FCurrencyId = 0)) AND (FDetailId >= 0)) AND EXISTS (SELECT 1 FROM T_GL_BALANCE T1 WHERE ((T_GL_BALANCE.FDetailId = t1.FDetailId AND t1.FAccountBookId = :FBookId) AND (((t1.FAccountId IN (4007)) AND (((t1.FYear * 100) + t1.FPeriod) BETWEEN :FYearMin AND :FYearMax)) AND t1.FCurrencyId = :FCyId))))) 2019-10-25/10:54:58
DELETE FROM T_GL_BALANCE WHERE (FAccountBookId = :FAccountBookId AND (((((FAccountId IN (104653)) AND (((FYear * 100) + FPeriod) BETWEEN :FMin AND :FMax)) AND (FCurrencyId = :FCurrencyId OR FCurrencyId = 0)) AND (FDetailId >= 0)) AND EXISTS (SELECT 1 FROM T_GL_BALANCE T1 WHERE ((T_GL_BALANCE.FDetailId = t1.FDetailId AND t1.FAccountBookId = :FBookId) AND (((t1.FAccountId IN (104653)) AND (((t1.FYear * 100) + t1.FPeriod) BETWEEN :FYearMin AND :FYearMax)) AND t1.FCurrencyId = :FCyId)))))
推断数据丢失发生在10月25、26日这两天
2. expdp数据泵备份还原至测试库
impdp user/:1521/kdtest directory=dump dumpfile=dump191018.dump logfile=dump191018.log
3. 将T_GL_BALANCE表测试库导出，然后导入至正式库
测试库导出
expdp user/:1521/kdtest tables=t_gl_balance_inibak directory=dump dumpfile=dump19102801.dump logfile=dump19102801.log
正式库导入
impdp user/:1521/实例名 directory=dump dumpfile=dump19102801.dump logfile=dump19102801.log remap_table=t_gl_balance_inibak:tmp_t_gl_balance_inibak cluster=n;
remap_table设置表名重命名，以下为拷贝参数设置介绍：
REMAP_DATA 指定数据转换函数,例如REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO
REMAP_DATAFILE 在所有DDL语句中重新定义数据文件引用。
REMAP_SCHEMA 将一个方案中的对象加载到另一个方案。
REMAP_TABLE 表名重新映射到另一个表,例如 REMAP_TABLE=EMP.EMPNO:REMAPPKG.EMPNO。
REMAP_TABLESPACE 将表空间对象重新映射到另一个表空间。
REUSE_DATAFILES 如果表空间已存在, 则将其初始化 (N)。
REMAP_SCHEMA参数
众所周知：IMP工具的FROMUSER和TOUSER参数可以实现将一个用户的的数据迁移到另外一个用户。
impdp数据泵使用REMAP_SCHEMA参数来实现不同用户之间的数据迁移；
语法：
REMAP_SCHEMA<span>=source_schema:target_schema
</span>
<span> impdp orcldev<span>/oracle DIRECTORY<span>=backup_path <span>DUMPFILE<span>=oracldev<span>.dmp REMAP_SCHEMA<span>=orcldev:orcltwo
</span></span></span></span></span></span></span>
<span><span><span><span><span><span><span> 与REMAP_SCHEMA类似的参数选项，如REMAP_TABLESPACE将源表空间的所有对象导入目标表空间。</span></span></span></span></span></span></span>
REMAP_TABLE参数
将源表数据映射到不同的目标表中
impdp orcldev<span>/oracle DIRECTORY<span>=backup_path <span>dumpfile<span>=oracldev<span>.dmp remap_table<span>=TAB_TEST:TEST_TB
</span></span></span></span></span></span>
<span><span><span><span><span><span>数据导入到TEST_TB表中，但是该表的索引等信息并没有相应的创建，需要手工初始化。</span></span></span></span></span></span>
REMAP_DATAFILE参数
语法：REMAP_DATAFILE<span>=source_datafile:target_datafile
</span>
<span>Oracle_Online:
</span>
<span> Remapping datafiles <span>is useful <span>when you move <span>databases <span>between platforms that have different <span>file naming conventions<span>. The source_datafile <span>and target_datafile names should <span>be exactly <span>as you want them <span>to appear <span>in the SQL statements <span>where they are referenced<span>. Oracle recommends that you enclose datafile names <span>in quotation marks <span>to eliminate ambiguity <span>on platforms <span>for which <span>a colon <span>is <span>a valid <span>file specification character<span>.</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
4. 查找丢失数据，导入正式库表T_GL_BALANCE中
SQL> alter table tmp_t_gl_balance_inibak drop column FYEARPERIOD; --删除自动生成的唯一字段
SQL> insert into t_gl_balance select * from tmp_t_gl_balance_inibak where faccountbookid=151508; --插入查询结果时少字段
SQL> alter table tmp_t_gl_balance_inibak add FYEARPERIOD NUMBER; --将删除字段加回来
SQL> insert into t_gl_balance select * from tmp_t_gl_balance_inibak where faccountbookid=151508; --插入查询结果时报错
ORA-11523: Message 11523 not found; product=RDBMS; facility=ORA google没有找到对应报错
直接在数据库服务器上操作：
sqlplus user//实例名
SQL> insert into t_gl_balance select * from tmp_t_gl_balance_inibak where faccountbookid=151508;
ORA-54013: INSERT operation disallowed on virtual columns 报错已经很明显了，通过指定列名来进行插入
SQL> insert into t_gl_balance(FACCOUNTBOOKID,FYEAR,FPERIOD,FACCOUNTID,FDETAILID,FCURRENCYID,FBEGINBALANCEFOR,FBEGINBALANCE,FDEBITFOR,FDEBIT,FCREDITFOR,FCREDIT,FYTDDEBITFOR,FYTDDEBIT,FYTDCREDITFOR,FYTDCREDIT,FENDBALANCEFOR,FENDBALANCE,FADJUSTPERIOD) select FACCOUNTBOOKID,FYEAR,FPERIOD,FACCOUNTID,FDETAILID,FCURRENCYID,FBEGINBALANCEFOR,FBEGINBALANCE,FDEBITFOR,FDEBIT,FCREDITFOR,FCREDIT,FYTDDEBITFOR,FYTDDEBIT,FYTDCREDITFOR,FYTDCREDIT,FENDBALANCEFOR,FENDBALANCE,FADJUSTPERIOD from tmp_t_gl_balance_inibak where faccountbookid=151508;
插入成功，将其他丢失数据批量恢复
5. 问题原因及问题出现的操作和时间节点
原因：业务系统BUG，反过账操作会将前一期数据清除
SQL>select to_char(FDATETIME,‘YYYY-MM-DD HH24:mi:ss‘),FDESCRIPTION,FCLIENTIP from t_bas_operatelog where fdescription like ‘%过账%‘ and to_char(FDATETIME,‘YYYY-MM-DD‘) in (‘2019-10-25‘,‘2019-10-26‘);
SQL>select to_char(FDATETIME,‘YYYY-MM-DD HH24:mi:ss‘),FDESCRIPTION,FCLIENTIP from t_bas_operatelogbk where fdescription like ‘%过账%‘ and to_char(FDATETIME,‘YYYY-MM-DD‘) in (‘2019-10-25‘,‘2019-10-26‘);
通过日志追溯到了时间及具体操作等，接下来就是让业务系统厂家打补丁了。