Oracle DataGuard 出现 GAP 修复

下面我们通过实验来进行演示如何修复：

一、主库切几个最新的归档，然后手工删掉，重新开启DG同步。

1、备库关闭应用日志和数据库



SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2、主库切换多次归档：



QL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

3、主库删除最近几个归档日志：



[oracle@sjz311:/home/oracle]$ su - grid

ASMCMD> ls

2022_01_24/

ASMCMD> rm -rf *

二、模拟gap产生

1、备库开启同步进程：



startup;

2、主库创建测试表

create table  dbmt.ljc6  as SELECT * FROM DBA_DATA_FILES;

2、查看gap



sqlplus / as sysdba

SQL> @gap

   THREAD#     ARCHED    APPLIED    DELETED        GAP ETIME

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

         1         78         73          1          5 20220124 11:31:29

三、gap修复
1、以备库的当前SCN号为起点，在主库上做一个增量备份
备库查询当前 scn 号：



sqlplus / as sysdba

SQL> select  to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)

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

4860280

2、确认主GAP期间是否新增数据文件：



sqlplus / as sysdba

select file# from v$datafile where creation_change# > =4860280;

3、主库根据备库scn号进行增量备份：



rman target /

run{

allocate channel c1 type disk;

allocate channel c2 type disk;

backup INCREMENTAL from scn 4860280 database format '/home/oracle/ll/incre_%U';

release channel c1;

release channel c2;

}

4、备份个控制文件

backup device type disk format '/home/oracle/ll/standby_%U.ctl' current controlfile for standby;

5、将增量备份和控制文件拷贝到备库上，主库拷贝增量备份和控制文件你至备库：



scp incre_* oracle@192.168.3.112:/home/oracle/ll/

scp standby*.ctl oracle@192.168.3.112:/home/oracle/ll/

6.使用新的控制文件将备库启动到mount状态，备库关闭数据库实例，开启至nomount状态：



sqlplus / as sysdba

shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>SQL> startup nomount

ORACLE instance started.

Total System Global Area 2516581456 bytes

Fixed Size                  8899664 bytes

Variable Size             536870912 bytes

Database Buffers         1962934272 bytes

Redo Buffers                7876608 bytes

SQL> SQL> SQL> SQL> SQL> SQL>

SQL>

7、备库恢复新的控制文件：



rman target /

RMAN> restore controlfile from '/home/oracle/ll/standby_170k3cef_39_1_1.ctl';

Starting restore at 2022-01-24 13:59:01

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/u01/app/oracle/oradata/control01.ctl

output file name=/u01/app/oracle/oradata/control02.ctl

Finished restore at 2022-01-24 13:59:04

8、备库开启到mount状态：



RMAN> sql 'alter database mount';

sql statement: alter database mount

released channel: ORA_DISK_1

9、备库rman注册增量备份文件：



rman target /

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

using target database control file instead of recovery catalog

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

List of Files Unknown to the Database

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

File Name: /home/oracle/ll/standby_170k3cef_39_1_1.ctl

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/ll/standby_170k3cef_39_1_1.ctl

10、备库开启恢复增量备份



RMAN> recover database noredo;

Starting recover at 2022-01-24 14:00:44

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=72 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 00003: /u01/app/oracle/oradata/datafile/sysaux.258.1084219663

destination for restore of datafile 00007: /u01/app/oracle/oradata/datafile/users.260.1084219691

channel ORA_DISK_1: reading from backup piece /home/oracle/ll/incre_150k3cb1_37_1_1

channel ORA_DISK_1: piece handle=/home/oracle/ll/incre_150k3cb1_37_1_1 tag=TAG20220124T135129

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

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: /u01/app/oracle/oradata/datafile/system.257.1084219591

destination for restore of datafile 00004: /u01/app/oracle/oradata/datafile/undotbs1.259.1084219689

channel ORA_DISK_1: reading from backup piece /home/oracle/ll/incre_140k3cb1_36_1_1

channel ORA_DISK_1: piece handle=/home/oracle/ll/incre_140k3cb1_36_1_1 tag=TAG20220124T135129

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished recover at 2022-01-24 14:00:54

11、开启备库的恢复进程

sqlplus / as sysdba

RMAN> alter database open read only;

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

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

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

RMAN-03002: failure of sql statement command at 01/24/2022 14:02:20

ORA-10458: standby database requires recovery

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/u01/app/oracle/oradata/datafile/system.257.1084219591'

12、备库开启日志同步进程



SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

13、备库的alert

Standby redo logs should be configured for real time apply. Real time apply will be ignored.

2022-01-24T14:06:53.317790+08:00

ORA-1153 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION...

2022-01-24T14:07:12.686677+08:00

.... (PID:27930): Standby crash recovery needs the archive log for T-1.S-79 to continue

.... (PID:27930): Please verify that primary database is transporting redo logs to the standby database

.... (PID:27930): Wait timeout: T-1.S-79

2022-01-24T14:07:12.686911+08:00

Standby Crash Recovery aborted due to error 16016.

2022-01-24T14:07:12.687014+08:00

Errors in file /u01/app/oracle/diag/rdbms/stdsjz/stdsjz/trace/stdsjz_ora_27930.trc:

ORA-16016: archived log for thread 1 sequence# 79 unavailable                                       <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Recovery interrupted!

Stopping change tracking

2022-01-24T14:07:12.726174+08:00

Completed Standby Crash Recovery.

Signalling error 1152 for datafile 1!

2022-01-24T14:07:12.739578+08:00

Errors in file /u01/app/oracle/diag/rdbms/stdsjz/stdsjz/trace/stdsjz_ora_27930.trc:

ORA-10458: standby database requires recovery

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/u01/app/oracle/oradata/datafile/system.257.1084219591'

ORA-10458 signalled during: ALTER DATABASE OPEN...

2022-01-24T14:07:12.898840+08:00

Errors in file /u01/app/oracle/diag/rdbms/stdsjz/stdsjz/trace/stdsjz_mz00_28011.trc:

ORA-01110: data file 1: '/u01/app/oracle/oradata/datafile/system.257.1084219591'

2022-01-24T14:07:13.129881+08:00

Errors in file /u01/app/oracle/diag/rdbms/stdsjz/stdsjz/trace/stdsjz_mz00_28011.trc:

ORA-01110: data file 3: '/u01/app/oracle/oradata/datafile/sysaux.258.1084219663'

2022-01-24T14:07:13.221381+08:00

Errors in file /u01/app/oracle/diag/rdbms/stdsjz/stdsjz/trace/stdsjz_mz00_28011.trc:

ORA-01110: data file 4: '/u01/app/oracle/oradata/datafile/undotbs1.259.1084219689'

2022-01-24T14:07:13.309981+08:00

Errors in file /u01/app/oracle/diag/rdbms/stdsjz/stdsjz/trace/stdsjz_mz00_28011.trc:

ORA-01110: data file 7: '/u01/app/oracle/oradata/datafile/users.260.1084219691'

2022-01-24T14:08:46.866877+08:00

14、查看主库的归档



SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            +ARCH

Oldest online log sequence     77

Next log sequence to archive   79                      《《《《《《《《

Current log sequence           79

15、主库切个归档日志并断开备库应用

主：

SQL> alter system switch logfile;

备：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

16、备库read only并查询测试表



SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT * FROM  dbmt.ljc6 ;

FILE_NAME

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

   FILE_ID TABLESPACE_NAME                                                   BYTES     BLOCKS STATUS             RELATIVE_FNO AUTOEX   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS  LOST_WRITE_PRO

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

+DATA/SJZ/DATAFILE/system.257.1084219591

         1 SYSTEM                                                       1069547520     130560 AVAILABLE                     1 YES    3.4360E+10    4194302         1280 1068498944      130432 SYSTEM         OFF

+DATA/SJZ/DATAFILE/sysaux.258.1084219663

         3 SYSAUX                                                        975175680     119040 AVAILABLE                     3 YES    3.4360E+10    4194302         1280  974127104      118912 ONLINE         OFF

+DATA/SJZ/DATAFILE/users.260.1084219691

         7 USERS                                                          17039360       2080 AVAILABLE                     7 YES    3.4360E+10    4194302          160   15990784        1952 ONLINE         OFF

+DATA/SJZ/DATAFILE/undotbs1.259.1084219689

         4 UNDOTBS1                                                      361758720      44160 AVAILABLE                     4 YES    3.4360E+10    4194302          640  360710144       44032 ONLINE         OFF

最后：



SQL>     alter database recover managed standby database parallel 3 disconnect;

Database altered.

巴特西

Oracle DataGuard 出现 GAP 修复

最新文章

热门文章