Redo is inconsistent with data block ORA-00600: internal error code, arguments: [3020]

posted on 09 Feb 2018 by in ORA-00600

Redo is inconsistent with data block {ORA-00600: internal error code, arguments: [3020]}

Is it a bug or a feture? That's the question!

In this article, we will show how to copy and restore datafiles from Primary to Standby Database on ASM using RMAN.

"Same,same, but different!" It is the same day as yesterday, but not exactly.

Today, when I logged in and start working, I got an alert for my standby database that Standby Database is not in sync with the Primary Database.

I logged and checked the configuration:

DGMGRL> show configuration;

Configuration - orcldgconf

Protection Mode: MaxPerformance
Databases:
orclprd - Primary database
orclsby - Physical standby database
Error: ORA-16766: Redo Apply is stopped

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

DGMGRL> show database verbose 'orclsby';

Database - orclsby

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 5 hours 22 minutes 5 seconds (computed 0 seconds ago)
Apply Rate: 1.87 MByte/s
Real Time Query: OFF
Instance(s):
ORCLSBY_1

Database Error(s):
ORA-16766: Redo Apply is stopped

First thing that come to my mind was to start the apply.

Of course it didn't work and the below error showed again:

.....
Mon Jan 08 10:13:35 2018
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (ORCLSBY_1)
Mon Jan 08 10:13:35 2018
MRP0 started with pid=76, OS id=3131
MRP0: Background Managed Standby Recovery process started (ORCLSBY_1)
Mon Jan 08 10:13:35 2018
RFS[20]: Selected log 6 for thread 1 sequence 124608 dbid -775285828 branch 947517497
Mon Jan 08 10:13:35 2018
Archived Log entry 221642 added for thread 1 sequence 124607 ID 0xd23a0099 dest 1:
started logmerger process
Mon Jan 08 10:13:40 2018
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 16 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Mon Jan 08 10:13:41 2018
Media Recovery Log +FRA1/orclsby/archivelog/2018_01_07/thread_2_seq_96516.13646.964814675
Media Recovery Log +FRA1/orclsby/archivelog/2018_01_07/thread_1_seq_124126.10737.964814603
Mon Jan 08 10:13:45 2018
RFS[2]: Selected log 8 for thread 2 sequence 96837 dbid -775285828 branch 947517497
Mon Jan 08 10:13:46 2018
Archived Log entry 221643 added for thread 2 sequence 96836 ID 0xd23a0099 dest 1:
Mon Jan 08 10:13:53 2018
Errors in file /u01/app/oracle/diag/rdbms/orclsby/ORCLSBY_1/trace/ORCLSBY_1_pr0e_3200.trc:
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '+REDO1/orclsby/onlinelog/group_1.404.947512911'
ORA-10567: Redo is inconsistent with data block (file# 28, block# 226858, file offset is 1858420736 bytes)
ORA-10564: tablespace TEST
ORA-01110: data file 28: '+DATA1/orclsby/datafile/test.817.947510801'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 269069
...
Recovery is interrupted!

In order to avoid recreating the standby database you can accomplish the following steps:

1. On primary database backup the datafile that is shown as inconsistent in the aler log of the standby database:

rman target /

RMAN> run{
allocate channel d1 device type disk;
BACKUP AS COPY DATAFILE 28 FORMAT '+FRA1';
release channel d1;
}

using target database control file instead of recovery catalog
allocated channel: d1
channel d1: SID=1284 instance=ORCLPRD_1 device type=DISK

Starting backup at 08-JAN-18
channel d1: starting datafile copy
input datafile file number=00028 name=+DATA1/orclprd/datafile/test.758.947496671
output file name=+FRA1/orclprd/datafile/test.7961.964865921 tag=TAG20180108T101840 RECID=260 STAMP=964865957
channel d1: datafile copy complete, elapsed time: 00:00:45
Finished backup at 08-JAN-18

released channel: d1

RMAN>

2. Next step is to copy the backuped datafile to the standby database. I am copying it from ASM to another ASM with scp.

Btw, you must check that both ASM instances(source and destination) that are registered in the local listener.

In case of error, you should setup debug mode in order to find out the exact error.

You should trigger following export command { export DBI_TRACE=1 } in order to activate debug mode and then repeat the below steps again:

In most cases it is related to the sys password.

asmcmd cp --port 1542 sys/password@xxx.xx.xxx.xx.+ASM1:+FRA1/orclprd/datafile/test.7961.964865921 +FRA1/test
copying xxx.xx.xxx.xx:+FRA1/orclprd/datafile/test.7961.964865921 -> +FRA1/test

It is creating alias "test" in +FRA1 which is poinging to +FAR1/ASM/DATAFILE - location of the copied datafile

3. After it is copied, we have to catalog it:

RMAN> catalog start with '+FRA1/ASM/DATAFILE';

using target database control file instead of recovery catalog
searching for all files that match the pattern +FRA1/ASM/DATAFILE

List of Files Unknown to the Database
=====================================
File Name: +FRA1/ASM/DATAFILE/test.20517.964865991

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

List of Cataloged Files
=======================
File Name: +FRA1/ASM/DATAFILE/test.20517.964865991

RMAN>

4. Restore the datafile on the standby database.

* Have in mind that if your standby database is opne read only, furst you have to shut it down, startup in mount state and then restore.

Keep in mind that in case of RAC Standby Database, you should modify database service from read only to mount in order to avoid automatic startup and avoid issues with the restore.

RMAN> restore datafile 149;

Starting restore at 08-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 instance=ORCLSBY_1 device type=DISK

channel ORA_DISK_1: restoring datafile 00028
input datafile copy RECID=444 STAMP=964866229 file name=+FRA1/asm/datafile/test.20517.964865991
destination for restore of datafile 00028: +DATA1/orclsby/datafile/test.817.947510801
channel ORA_DISK_1: copied datafile copy of datafile 000149
output file name=+DATA1/orclsby/datafile/test.817.947510801 RECID=0 STAMP=0
Finished restore at 08-JAN-18

RMAN>

5. Now start the apply:

DGMGRL> edit database 'orclsby' set state='apply-on';

In the alter log we can check that after restoring the datafile - all archivelogs applied and database is in sync.

Restore of datafile copy +FRA1/asm/datafile/test.20517.964865991 complete to datafile 28 +DATA1/orclsby/datafile/test.817.947510801
checkpoint is 12165580499351
Mon Jan 08 10:24:52 2018
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (ORCLSBY_1)
Mon Jan 08 10:24:52 2018
MRP0 started with pid=81, OS id=14438
MRP0: Background Managed Standby Recovery process started (ORCLSBY_1)started logmerger process
Mon Jan 08 10:24:57 2018
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 16 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Mon Jan 08 10:24:58 2018
Media Recovery Log +FRA1/orclsby/archivelog/2018_01_07/thread_2_seq_96516.13646.964814675
Media Recovery Log +FRA1/orclsby/archivelog/2018_01_07/thread_1_seq_124126.10737.964814603
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Media Recovery Log +FRA1/orclsby/archivelog/2018_01_07/thread_1_seq_124127.18888.964814907
Media Recovery Log +FRA1/orclsby/archivelog/2018_01_07/thread_2_seq_96517.20417.964814851
Media Recovery Log +FRA1/orclsby/archivelog/2018_01_07/thread_2_seq_96518.4045.964815043

Check with the below SQL that all the archivelogs are applied:

SQL> select al.thread#,
2 al.last_rec "Last Recd",
la.last_app "Last Applied"
from
(select thread#, max(sequence#) last_rec
from v$archived_log
group by thread#) al,
(select thread#, max(sequence#) last_app
from v$archived_log
where applied='YES' and registrar='RFS'
group by thread#) la
where al.thread#=la.thread#
and al.thread# != 0
order by al.thread#
3 4 5 6 7 8 9 10 11 12 13 14 15 /

THREAD# Last Recd Last Applied
---------- ---------- ------------
1 124615 124582
296855 96796

SQL> /

THREAD# Last Recd Last Applied
---------- ---------- ------------
1 124620 124620
296861 96860

Now ... checking againg the configuration:

DGMGRL> show configuration

Configuration - orcldgconf

Protection Mode: MaxPerformance
Databases:
orclprd - Primary database
orclsby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database verbose 'orclsby';

Database - orclsby

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 1.20 MByte/s
Real Time Query: OFF
Instance(s):
ORCLSBY_1

One more thing that you should not forget is to modify again Oracle RAC database service from mount to read only startup, if you performed this change before restoring the datafiles.

#enjoy :)

1 comment

Comment from: kkovachki Member

Hello Polya,
Thanks a lot for your post, its very useful!!!!

Form is loading...