DG日志断档解决方法

最新推荐文章于 2022-03-22 11:25:44 发布

weixin_33804990

最新推荐文章于 2022-03-22 11:25:44 发布

阅读量413

点赞数

文章标签：操作系统数据库

原文链接：http://blog.51cto.com/zhangjinbo/1924781

版权

DG日志断档了怎么办？

1、查看两个库之间的通讯，如果设置了fal_server和fal_client，那么等两个库之间的通信恢复好了以后，日志就会自动的传过去；

2、如果主库的归档日志给删除了，出现断档的情况，就可以对主库做一个基于最小scn的增量备份，然后将增量备份传到备库，使备库恢复，具体方法可以见王小俊的共享文档，也可以见下面；

--1. 查询备库的SCN

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

1820049562

-- 当查询v$datafile scn,database scn,datafile_header scn发现均不一致,这个时候不能使用CURRENT_SCN来作为scn增量备份的起点。

-- 这个时候需要根据缺少日志最小sequence#找出scn,然后作为增量备份的起点.

SQL> select a.file#,a.checkpoint_change# CTRL_SCN,b.checkpoint_change# FILE_SCN from v$datafile a,v$datafile_header b where a.file#=b.file#;

FILE# CTRL_SCN FILE_SCN

---------- ---------- ----------

1 1820049386 1820049563

2 1820049386 1820049563

3 1820049386 1820049563

4 1820049386 1820049563

5 1820049386 1820049563

6 1820049386 1820049563

15 1820049386 1820049563

7 rows selected.

SQL> select checkpoint_change#,current_scn from v$database;

CHECKPOINT_CHANGE# CURRENT_SCN

------------------ -----------

1820049385 1820049562

--本例中从33号归档开始缺失日志。

SQL> select thread#,sequence#,name,applied,first_change#,next_change# from v$archived_log where thread#=1 and sequence#=33 order by sequence#;

THREAD# SEQUENCE# NAME APPLIED FIRST_CHANGE# NEXT_CHANGE#

---------- ---------- ------------------------------------------------------------ --------- ------------- ------------

1 33 NO 1820049386 1820049389

1 33 dg_demo YES 1820049386 1820049389

--所以从 SCN=1820049385开始做增量备份。

--2. 确定主库是否添加数据文件

--确定主库在这个scn之后是否有添加数据文件，如果添加文件，需要手工在备库添加

select FILE#,name from v$datafile where CREATION_CHANGE#> =1820049385;

--3. 备库停止日志应用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

--4. 主库做增量备份,并传输到备库上。

RMAN> BACKUP INCREMENTAL FROM SCN 1820049385 DATABASE FORMAT '/opt/oracle/11gR2/rmanbak/incre_%U' tag 'INCRE_SCN';

Starting backup at 2014/08/25 10:53:17

using channel ORA_DISK_1

backup will be obsolete on date 2014/09/01 10:53:18

archived logs will not be kept or backed up

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00002 name=+DGDATA/demo/datafile/sysaux.291.853078433

input datafile file number=00001 name=+DGDATA/demo/datafile/system.290.853078487

input datafile file number=00006 name=+DGDATA/demo/datafile/undotbs2.268.853078373

input datafile file number=00003 name=+DGDATA/demo/datafile/undotbs1.293.853078397

input datafile file number=00015 name=+DGDATA/demo/datafile/wxj_tbs1.292.853078399

input datafile file number=00005 name=+DGDATA/demo/datafile/example.263.853078373

input datafile file number=00004 name=+DGDATA/demo/datafile/users.289.853078489

channel ORA_DISK_1: starting piece 1 at 2014/08/25 10:53:18

channel ORA_DISK_1: finished piece 1 at 2014/08/25 10:55:13

piece handle=/opt/oracle/11gR2/rmanbak/incre_0jpgqvku_1_1 tag=INCRE_SCN comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55

using channel ORA_DISK_1

backup will be obsolete on date 2014/09/01 10:55:15

archived logs will not be kept or backed up

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 2014/08/25 10:55:18

channel ORA_DISK_1: finished piece 1 at 2014/08/25 10:55:19

piece handle=/opt/oracle/11gR2/rmanbak/incre_0kpgqvoj_1_1 tag=INCRE_SCN comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2014/08/25 10:55:20

oracle@rac1:/opt/oracle/11gR2/rmanbak> ll

total 801808

-rw-r----- 1 oracle asmadmin 800251904 2014-08-25 10:55 incre_0jpgqvku_1_1

-rw-r----- 1 oracle asmadmin 19988480 2014-08-25 10:55 incre_0kpgqvoj_1_1

oracle@rac1:/opt/oracle/11gR2/rmanbak> scp * 192.168.52.235:/home/oracle/rmanbak

Password:

incre_0jpgqvku_1_1 100% 763MB 20.6MB/s 00:37

incre_0kpgqvoj_1_1 100% 19MB 19.1MB/s 00:00

--5. 备库上进行恢复

RMAN> catalog start with '/home/oracle/rmanbak/';

searching for all files that match the pattern /home/oracle/rmanbak/

List of Files Unknown to the Database

=====================================

File Name: /home/oracle/rmanbak/incre_0jpgqvku_1_1

File Name: /home/oracle/rmanbak/incre_0kpgqvoj_1_1

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /home/oracle/rmanbak/incre_0jpgqvku_1_1

File Name: /home/oracle/rmanbak/incre_0kpgqvoj_1_1

--开始执行恢复

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 25-AUG-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=37 device type=DISK

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /oradata/demo/datafile/system.256.839340653

destination for restore of datafile 00002: /oradata/demo/datafile/sysaux.257.839340655

destination for restore of datafile 00003: /oradata/demo/datafile/undotbs1.258.839340655

destination for restore of datafile 00004: /oradata/demo/datafile/users.259.839340655

destination for restore of datafile 00005: /oradata/demo/datafile/example.264.839340819

destination for restore of datafile 00006: /oradata/demo/datafile/undotbs2.265.839341139

destination for restore of datafile 00015: /oradata/demo/datafile/wxj_tbs1.278.842085667

channel ORA_DISK_1: reading from backup piece /home/oracle/rmanbak/incre_0jpgqvku_1_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 08/25/2014 11:02:01

ORA-19870: error while restoring backup piece /home/oracle/rmanbak/incre_0jpgqvku_1_1

ORA-19573: cannot obtain exclusive enqueue for datafile 2

--报错ORA-19870，ORA-19573，原因是实例处于open状态。

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes

Variable Size 159387560 bytes

Database Buffers 50331648 bytes

Redo Buffers 5226496 bytes

Database mounted.

--再次执行恢复

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 25-AUG-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=38 device type=DISK