时间戳

首页 > 技术文章 > 主库增加表空间导致DG同步失败

主库增加表空间导致DG同步失败

connected 2019-05-24 18:06 原文

主库增加表空间导致DG同步失败

问题现象：

由于主库表空间不足，同事给表空间增加数据文件，第二天收到反馈说备库未同步。

 

1、主、备查看归档序列号，发现主、备归档正常同步。

SQL>archive log list

2、在主库端查询v$archived_log视图，确认日志是否被应用

set lines 300 pages 300
col name for a20
select name,dest_id,thread#,sequence#,standby_dest,applied,registrar,completion_time from v$archived_log
where standby_dest='YES'
order by thread#,sequence#;

3、在备库查看接收到的被应用的redo，发现日志未被应用

在备库查看已经应用和正在应用的日志：
SELECT SEQUENCE#,APPLIED,TO_CHAR(COMPLETION_TIME, 'yy-mm-dd hh24:mi:ss') FROM V$ARCHIVED_LOG where APPLIED='YES' OR APPLIED like '%IN-MEMRY%'  ORDER BY SEQUENCE#; SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

4、处理方式:

现在备库中建立了对应的文件夹并赋权
将备库中STANDBY_FILE_MANAGEMENT选项设定为MANUAL
查找出对应的UNNAMED 文件
将UNNAMED文件重命名为指定需要新建的数据文件
将备库STANDBY_FILE_MANAGEMENT 恢复设置为auto
同步数据

主备库查看：
show parameter STANDBY_FILE_MANAGEMENT
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
select name from v$datafile where name like '%UNNAMED%';

主库
主库：
SQL>
col name for a50
select file#,name from v$datafile;

     FILE# NAME
---------- ----------------------------------------------------------------------------------------------------
         1 /opt/oradata/shdb/system01.dbf
         2 /opt/oradata/shdb/sysaux01.dbf
         3 /opt/oradata/shdb/undotbs01.dbf
         4 /opt/oradata/shdb/users01.dbf
         5 /opt/oradata/shdb/tbs_hank_dat01.dbf
         6 /opt/oradata/shdb/tbs_hank_idx01.dbf
         7 /opt/oradata/shdb/tbs_dock_dat01.dbf
         8 /opt/oradata/shdb/tbs_mok_dat01.dbf
         9 /opt/oradata/shdb/tbs_lod_dat01.dbf
        10 /opt/oradata/shdb/tbs_book_idx01.dbf
        11 /opt/oradata/shdb/tbs_book_part_dat01.dbf
        12 /opt/oradata/shdb/tbs_book_part_idx01.dbf
        13 /opt/oradata/shdb/system02.dbf
        14 /opt/oradata/shdb/tbs_book_idx02.dbf
        15 /opt/oradata/shdb/tbs_book_part_dat02.dbf
        16 /opt/oradata/shdb/tbs_dock_dat02.dbf
        17 /opt/oradata/shdb/tbs_book_idx03.dbf
        18 /opt/oradata/shdb/tbs_book_part_dat03.dbf
        19 /opt/oradata/shdb/tbs_book_idx04.dbf
        20 /opt/oradata/shdb/tbs_book_idx05.dbf
        21 /opt/oradata/shdb/tbs_book_part_dat04.dbf
        22 /opt/oradata/shdb/tbs_book_part_dat05.dbf
        23 /opt/oradata/shdb/tbs_dock_dat03.dbf
        24 /opt/oradata/shdb/user02.dbf

24 rows selected.

备库：

备库：

SQL> select name from v$datafile where name like '%UNNAMED%';

NAME
--------------------------------------------------
/opt/oracle/app/product/11g/dbs/UNNAMED00019

 

col name for a50
select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 /opt/oradata/shdbstd/system01.dbf
         2 /opt/oradata/shdbstd/sysaux01.dbf
         3 /opt/oradata/shdbstd/undotbs01.dbf
         4 /opt/oradata/shdbstd/users01.dbf
         5 /opt/oradata/shdbstd/tbs_hank_dat01.dbf
         6 /opt/oradata/shdbstd/tbs_hank_idx01.dbf
         7 /opt/oradata/shdbstd/tbs_dock_dat01.dbf
         8 /opt/oradata/shdbstd/tbs_mok_dat01.dbf
         9 /opt/oradata/shdbstd/tbs_lod_dat01.dbf
        10 /opt/oradata/shdbstd/tbs_book_idx01.dbf
        11 /opt/oradata/shdbstd/tbs_book_part_dat01.dbf

     FILE# NAME
---------- --------------------------------------------------
        12 /opt/oradata/shdbstd/tbs_book_part_idx01.dbf
        13 /opt/oradata/shdbstd/system02.dbf
        14 /opt/oradata/shdbstd/tbs_book_idx02.dbf
        15 /opt/oradata/shdbstd/tbs_book_part_dat02.dbf
        16 /opt/oradata/shdbstd/tbs_dock_dat02.dbf
        17 /opt/oradata/shdbstd/tbs_book_idx03.dbf
        18 /opt/oradata/shdbstd/tbs_book_part_dat03.dbf
        19 /opt/oracle/app/product/11g/dbs/UNNAMED00019

19 rows selected.

SQL>

和主库对比，进行修改

alter database create datafile  '/opt/oracle/app/product/11g/dbs/UNNAMED00019' as '/opt/oradata/shdb/tbs_book_idx04.dbf';

recover managed standby database disconnect;

继续查询备库：

SQL> select name from v$datafile;

NAME
--------------------------------------------------
/opt/oradata/shdbstd/system01.dbf
/opt/oradata/shdbstd/sysaux01.dbf
/opt/oradata/shdbstd/undotbs01.dbf
/opt/oradata/shdbstd/users01.dbf
/opt/oradata/shdbstd/tbs_hank_dat01.dbf
/opt/oradata/shdbstd/tbs_hank_idx01.dbf
/opt/oradata/shdbstd/tbs_dock_dat01.dbf
/opt/oradata/shdbstd/tbs_mok_dat01.dbf
/opt/oradata/shdbstd/tbs_lod_dat01.dbf
/opt/oradata/shdbstd/tbs_book_idx01.dbf
/opt/oradata/shdbstd/tbs_book_part_dat01.dbf

NAME
--------------------------------------------------
/opt/oradata/shdbstd/tbs_book_part_idx01.dbf
/opt/oradata/shdbstd/system02.dbf
/opt/oradata/shdbstd/tbs_book_idx02.dbf
/opt/oradata/shdbstd/tbs_book_part_dat02.dbf
/opt/oradata/shdbstd/tbs_dock_dat02.dbf
/opt/oradata/shdbstd/tbs_book_idx03.dbf
/opt/oradata/shdbstd/tbs_book_part_dat03.dbf
/opt/oradata/shdb/tbs_book_idx04.dbf
/opt/oracle/app/product/11g/dbs/UNNAMED00020

20 rows selected.
可以发现，又出现一个UNNAMED00020

 

同样的方法处理
alter database create datafile  '/opt/oracle/app/product/11g/dbs/UNNAMED00020' as '/opt/oradata/shdb/tbs_book_idx05.dbf';

recover managed standby database disconnect;

按照这种方法反复查询，知道主备数据文件数量一致，且备库不再出现UNNAMED***类似的数据文件

 

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=auto;

 

5、备库启用apply，接收来自主库的redo并apply

alter database recover managed standby database using current logfile disconnect from session;

6、取消同步

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

7、备库再次启用apply，接收来自主库的redo并apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

8、根据步骤1、2、3进行验证。

在备库查看已经应用和正在应用的日志：
SELECT SEQUENCE#,APPLIED,TO_CHAR(COMPLETION_TIME, 'yy-mm-dd hh24:mi:ss') FROM V$ARCHIVED_LOG where APPLIED='YES' OR APPLIED like '%IN-MEMORY%' ORDER BY SEQUENCE#;

 

推荐阅读