欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Data guard 出现gap sequence修复

程序员文章站 2022-06-01 17:27:21
...

一、出现gap sequence现象 SQLgt; alter database open;alter database open*第 1 行出现错误:今天的dataguard ,备库恢复open时

一、出现gap sequence现象

SQL> alter database open;
alter database open
*
第 1 行出现错误:
今天的dataguard ,备库恢复open时报错:

ORA-16004: 备份数据库需要恢复
ORA-01152: 文件 1 没有从过旧的备份中恢复
ORA-01110: 数据文件 1: 'C:\Oracle\PRODUCT\10.2.0\ORADATA\SHENG\SYSTEM01.DBF'

虽然archivelog是可以同步的:

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
15

在主库上看到的log:
Tue Jan 03 19:11:20 2012
FAL[server]: Fail to queue the whole FAL gap
GAP - thread 1 sequence 8-8
DBID 1778268600 branch 770765436

备库的alert 日志:
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 8-8
DBID 1778268600 branch 770765436
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.

二、修复操作
1、查询备库的scn

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
614090

目的:1)确定主库在这个scn之后是否有添加数据文件,如果添加文件,需要手工在备库添加
2)确定主库增量备份起点

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

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

未选定行

3、备库停止日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

4、主库增量备份并传输到备库上
主库进行增量备份
RMAN> backup incremental from scn 614090 database format 'C:\soft\sheng_U%' tag 'shp';

说明:主库之前必须要做过rman的全备(没有全备的库,基于scn的增量备份也能够成功)

5、备库上进行恢复
RMAN> catalog start with 'C:\soft';
RMAN> RECOVER DATABASE NOREDO;
说明:CATALOG START WITH是10g及其以后版本中才存在功能,没有该功能可以采用catalog或者复制主库的控制文件,rman备份放置和主库备份时相同目录实现。

6、主库上创建standby controlfile文件并传输到备库
RMAN> backup current controlfile for standby format 'C:\soft\sheng_ctl.bck';

7、备库恢复控制文件
RMAN> shutdown;
RMAN> STARTUP NOMOUNT;
RMAN> restore standby controlfile from 'C:\soft\SHENG_CTL.BCK';
RMAN> alter database mount;

8、清空备库日志组
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
注:如果采用了standby log模式,不需要清空,如果清空会出现
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: ‘c:\....\redo01.log’
说明:如果没有采用standby log模式,有几组需要清空几组

9、备库重设flashback
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;

10、备库重新接收并应用日志
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

----------------------
我试做上面的操作,发现由于redo位置不同,而引发错误:

发现备库的alert:
RFS[1]: Unable to open standby log 6: 313
Tue Jan 03 20:23:16 2012
Errors in file c:\oracle\product\10.2.0\admin\sheng\udump\sheng_rfs_2000.trc:
ORA-00313: open failed for members of log group 7 of thread 1
ORA-00312: online log 7 thread 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\REDO07.LOG'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 21) 设备未就绪。

检查一下log 位置,,发现有问题:

SQL> select * from v$logfile;
行将被截断


GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------
3 ONLINE D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\REDO03.LOG
2 ONLINE D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\REDO02.LOG
1 ONLINE D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\REDO01.LOG

SQL> select * from v$log;
在列 FIRST_CHANGE# 前截断 (按要求)


GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRS
---------- ---------- ---------- ---------- ---------- --- ---------------- ----
1 1 17 52428800 1 YES CLEARING 03-1
3 1 16 52428800 1 YES INACTIVE 03-1
2 1 18 52428800 1 YES CLEARING_CURRENT 03-1

下面进行修复:
其实在备库的 pfile 中加入:
log_file_name_convert = D:\oracle\product\10.2.0\oradata\sheng\, C:\oracle\product\10.2.0\oradata\sheng\

shutdown immediate
create spfile from pfile;

startup nomount;
alter database mount;

经过上面在备库的操作后,
select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\REDO03.LOG
2 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\REDO02.LOG
1 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\REDO01.LOG
4 STANDBY C:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\REDO04.LOG
5 STANDBY C:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\REDO05.LOG
6 STANDBY C:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\REDO06.LOG
7 STANDBY C:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\REDO07.LOG

已选择7行。

发现已经正确。

下面是细节:

主库:

SQL> alter system switch logfile;

系统已更改。
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
654580
alert:

Tue Jan 03 21:22:56 2012
Thread 1 advanced to log sequence 19
Current log# 3 seq# 19 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\REDO03.LOG
Tue Jan 03 21:22:57 2012
ARC1: Standby redo logfile selected for thread 1 sequence 18 for destination LOG_ARCHIVE_DEST_2


看一下备库的信息:

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
654555

RFS[1]: Successfully opened standby log 4: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\REDO04.LOG'
Tue Jan 03 21:22:54 2012
Media Recovery Log C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\00100018770765436.ARC
Media Recovery Waiting for thread 1 sequence 19

-----

到这里为止:

如果 open 出现数据库忙的情况,用一下命令停掉:

alter database recover managed standby database cancel

alter database open read only, 就可以打开数据库了。

Data guard 出现gap sequence修复