Today I faced an issue of archive log gap at Standby end. A standby was created from cold backup of Production Database. While starting the recovery manager, an error was reported in alert log.

MRP0: Background Media Recovery terminated with error 600
Tue Apr 26 10:16:00 2011
Errors in file c:\dbdr\bdump\dbdr_mrp0_1432.trc:
ORA-00600: internal error code, arguments: [3020], [2], [5549], [8394157], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 5549)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 2: 'D:\DBDR\ORADATA\UNDOTBS01.DBF'
ORA-10560: block type 'KTU UNDO BLOCK'

Recovery interrupted!
Recovered data files to a consistent state at change 25738819980
Tue Apr 26 10:16:01 2011
Errors in file c:\dbdr\bdump\dbdr_mrp0_1432.trc:
ORA-00600: internal error code, arguments: [3020], [2], [5549], [8394157], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 5549)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 2: 'D:\DBDR\ORADATA\UNDOTBS01.DBF'
ORA-10560: block type 'KTU UNDO BLOCK'

I didnt find any good solution for the error. I just thought why dont I go for an incremental backup from the current scn at my standby till the production’s scn.
The following solution can be useful when there is a gap of some archive log at standby which was physically removed/delted at production end, And it was not applied to standby database.

DB => Production Database

C:\Users\Rao>set oracle_sid=db

C:\Users\Rao>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 26 10:13:05 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: sys as sydba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

10:13:41 system@ORCL>@dt

STARTTIME            SYSDATETIME          DB_NAME LOG_MODE   OPEN_MODE  DATABASE_ROLE SWITCHOVER_STATUS VERSION
-------------------- -------------------- ------- ---------- ---------- ------------- ----------------- ----------- ----------
26-04-2011 10:09:18  26-04-2011 10:14:17  DB      ARCHIVELOG READ WRITE PRIMARY       NOT ALLOWED       10.2.0.1.0

Elapsed: 00:00:00.21

Current sequence at production.

10:16:14 system@ORCL>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
314

Elapsed: 00:00:00.09

Current SCN

10:19:27 system@ORCL>SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
2.5739E+10

Elapsed: 00:00:00.03

Get the actual scn value

10:24:05 system@ORCL>SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

TO_CHAR(CURRENT_SCN)
----------------------------------------
25738824328

Elapsed: 00:00:00.03

DBDR => Standby database

Bringing standby in sync with production database.

C:\Users\Rao>set oracle_sid=dbdr

C:\Users\Rao>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 26 10:14:32 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

10:14:36 dbdr >startup nomount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1247900 bytes
Variable Size              62915940 bytes
Database Buffers          100663296 bytes
Redo Buffers                2945024 bytes
10:15:04 dbdr >alter database mount standby database;

Database altered.

Elapsed: 00:00:05.43
10:15:10 dbdr >@dt

STARTTIME            SYSDATETIME          DB_NAME LOG_MODE     OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS VERSION
-------------------- -------------------- ------- ------------ ---------- ---------------- ----------------- ---------
26-04-2011 10:15:03  26-04-2011 10:23:41  DB      ARCHIVELOG   MOUNTED    PHYSICAL STANDBY NOT ALLOWED       10.2.0.1.0

Elapsed: 00:00:00.25

10:15:26 dbdr >recover managed standby database disconnect from session;
Media recovery complete.

It shows MRP has started. But in the alert log the output shows,

ALTER DATABASE RECOVER  managed standby database disconnect from session
MRP0 started with pid=17, OS id=1432
Managed Standby Recovery not using Real Time Apply
Tue Apr 26 10:15:55 2011
Completed: ALTER DATABASE RECOVER  managed standby database disconnect from session
Tue Apr 26 10:15:55 2011
Media Recovery Log C:\DBDR\ARCHIVE_DBDR\ARC_0741355170_00311_001
Tue Apr 26 10:15:58 2011
Errors in file c:\dbdr\bdump\dbdr_mrp0_1432.trc:
ORA-00600: internal error code, arguments: [3020], [2], [5549], [8394157], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 5549)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 2: 'D:\DBDR\ORADATA\UNDOTBS01.DBF'
ORA-10560: block type 'KTU UNDO BLOCK'

MRP0: Background Media Recovery terminated with error 600
Tue Apr 26 10:16:00 2011
Errors in file c:\dbdr\bdump\dbdr_mrp0_1432.trc:
ORA-00600: internal error code, arguments: [3020], [2], [5549], [8394157], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 5549)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 2: 'D:\DBDR\ORADATA\UNDOTBS01.DBF'
ORA-10560: block type 'KTU UNDO BLOCK'

Recovery interrupted!
Recovered data files to a consistent state at change 25738819980
Tue Apr 26 10:16:01 2011
Errors in file c:\dbdr\bdump\dbdr_mrp0_1432.trc:
ORA-00600: internal error code, arguments: [3020], [2], [5549], [8394157], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 5549)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 2: 'D:\DBDR\ORADATA\UNDOTBS01.DBF'
ORA-10560: block type 'KTU UNDO BLOCK'

Tue Apr 26 10:16:01 2011
Errors in file c:\dbdr\bdump\dbdr_mrp0_1432.trc:
ORA-00600: internal error code, arguments: [3020], [2], [5549], [8394157], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 5549)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 2: 'D:\DBDR\ORADATA\UNDOTBS01.DBF'
ORA-10560: block type 'KTU UNDO BLOCK'

Even the log sequence shown was lagging at standby as compared to production which is 314.

10:15:55 dbdr >select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
--------------
311

Elapsed: 00:00:00.04

When cancelling recovery manager.

10:16:35 dbdr >recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active

In alert log,

Tue Apr 26 10:17:08 2011
ALTER DATABASE RECOVER  managed standby database cancel
Tue Apr 26 10:17:08 2011
ORA-16136 signalled during: ALTER DATABASE RECOVER  managed standby database cancel  ...
Tue Apr 26 10:17:42 2011

10:17:08 dbdr >select process from v$managed_standby;

PROCESS
---------
ARCH
ARCH
RFS

Elapsed: 00:00:00.01

Trying to recover standby database manually,

10:17:27 dbdr >recover standby database;
ORA-00279: change 25738819980 generated at 04/23/2011 16:13:24 needed for thread 1
ORA-00289: suggestion : C:\DBDR\ARCHIVE_DBDR\ARC_0741355170_00311_001
ORA-00280: change 25738819980 for thread 1 is in sequence #311

10:17:42 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\DBDR\ORADATA\SYSTEM01.DBF'

ORA-01112: media recovery not started

Checking the archive applied status at standby.

10:17:52 dbdr >SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

SEQUENCE# APP
----------- -----
311            NO
312            NO
313            NO
314            NO

Elapsed: 00:00:00.06

The current scn for the standby is,

10:19:33 dbdr >SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
2.5739E+10

Elapsed: 00:00:00.04
10:23:52 dbdr >SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

TO_CHAR(CURRENT_SCN)
----------------------------------------
25738819979

Elapsed: 00:00:00.01

RMAN Incremental backup from SCN of standby database

Now, trying out the incremental backup feature.

Taking the incremental backup of production database from the current scn (25738819979) at standby database and applying the same at standby end.

C:\Users\Rao>set oracle_sid=db

C:\Users\Rao>rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 26 11:36:31 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: DB (DBID=1505170978)

RMAN> BACKUP INCREMENTAL FROM SCN 25738819979 DATABASE FORMAT 'C:\DBDR\DBDR_%U' tag 'Archive_Gap'; Starting backup at 26-APR-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=154 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=D:\DB\ORADATA\SYSTEM01.DBF input datafile fno=00004 name=D:\DB\ORADATA\USERS01.DBF input datafile fno=00003 name=D:\DB\ORADATA\SYSAUX01.DBF input datafile fno=00005 name=D:\DB\ORADATA\NEW01.DBF input datafile fno=00002 name=D:\DB\ORADATA\UNDOTBS01.DBF channel ORA_DISK_1: starting piece 1 at 26-APR-11 channel ORA_DISK_1: finished piece 1 at 26-APR-11 piece handle=C:\DBDR\DBDR_0DMAO5OR_1_1 tag=ARCHIVE_GAP comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current control file in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 26-APR-11 channel ORA_DISK_1: finished piece 1 at 26-APR-11 piece handle=C:\DBDR\DBDR_0EMAO5QI_1_1 tag=ARCHIVE_GAP comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 26-APR-11 RMAN> exit Recovery Manager complete.

Now applying the incremental backup to standby database. Catalog the backuppiece with standby.

C:\Users\Rao>set oracle_sid=dbdr

C:\Users\Rao>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 26 11:56:17 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: DB (DBID=1505170978, not open)

RMAN> CATALOG BACKUPPIECE 'C:\DBDR\DBDR_0DMAO5OR_1_1';

using target database control file instead of recovery catalog
cataloged backuppiece
backup piece handle=C:\DBDR\DBDR_0DMAO5OR_1_1 recid=3 stamp=749476633

RMAN> CATALOG BACKUPPIECE 'C:\DBDR\DBDR_0EMAO5QI_1_1';

cataloged backuppiece
backup piece handle=C:\DBDR\DBDR_0EMAO5QI_1_1 recid=4 stamp=749476642

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 26-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: D:\DBDR\ORADATA\SYSTEM01.DBF
destination for restore of datafile 00002: D:\DBDR\ORADATA\UNDOTBS01.DBF
destination for restore of datafile 00003: D:\DBDR\ORADATA\SYSAUX01.DBF
destination for restore of datafile 00004: C:\DBDR\ORADATA\USERS01.DBF
destination for restore of datafile 00005: C:\DBDR\ORADATA\NEW01.DBF
channel ORA_DISK_1: reading from backup piece C:\DBDR\DBDR_0DMAO5OR_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\DBDR\DBDR_0DMAO5OR_1_1 tag=ARCHIVE_GAP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished recover at 26-APR-11

RMAN> DELETE BACKUP TAG 'Archive_Gap';

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
3       3       1   1   AVAILABLE   DISK        C:\DBDR\DBDR_0DMAO5OR_1_1
4       4       1   1   AVAILABLE   DISK        C:\DBDR\DBDR_0EMAO5QI_1_1

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=C:\DBDR\DBDR_0DMAO5OR_1_1 recid=3 stamp=749476633
deleted backup piece
backup piece handle=C:\DBDR\DBDR_0EMAO5QI_1_1 recid=4 stamp=749476642
Deleted 2 objects

RMAN> exit

Recovery Manager complete.

C:\Users\Rao>

Now starting the recovery manager process.

11:03:37 dbdr >ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

Elapsed: 00:00:06.18
11:03:51 dbdr >select process from v$managed_standby;

PROCESS
---------
ARCH
ARCH
RFS
MRP0

Elapsed: 00:00:00.01

Lets check the redo has been applied,

11:04:15 dbdr >SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

SEQUENCE# APP
---------- ---
311 YES
312 YES
313 YES
314 YES

Elapsed: 00:00:00.03

We can see that the standby is now in sync with the production.

Verifying the recovery manager is running,

At production,

10:24:41 system@ORCL>alter system switch logfile;

System altered.

Elapsed: 00:00:00.62
11:04:35 system@ORCL>/

System altered.

Elapsed: 00:00:00.20
11:04:36 system@ORCL>/

System altered.

Elapsed: 00:00:03.74

11:04:46 system@ORCL>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
317

Elapsed: 00:00:00.06

At standby,

11:04:19 dbdr >select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
--------------
317

Elapsed: 00:00:00.01
11:07:03 dbdr >SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

SEQUENCE# APP
---------- ---
311 YES
312 YES
313 YES
314 YES
315 YES
316 YES
317 YES

7 rows selected.

Elapsed: 00:00:00.04
11:07:06 dbdr >

Cont… Resolving missing archive log gap at Standby Database – Part 2

Ref: Using RMAN Incremental Backups to Roll Forward a Physical Standby Database – Oracle Document

http://www.dba-oracle.com/t_physical_standby_missing_log_scenario.htm

Advertisements