ORA-10562 on physical standby

UPDATE: (11/30)  Looks as if the corruption began just after our datacenter cutover to our dark fiber run down to Norman (standby site).  Let’s see what the network guys come up with.

Redo log corruption occuring on the DR site on our physical standby. This is either occuring in flight (and we have no firewalls in place between our primary and standby site) or it’s occuring on the standby storage tier. The reason I say this is because I fixed the issue by re-applying the archive logs from the standby site after figuring out which redo logs in which thread occured before the last good SCN stamp on the physical standby (which can be found in the alert log as seen below).

Below are the notes from yesterdays exercise…

Seriously with the block corruption on PRDFT??????  just in….. hot off the press in the alert log on the DR1 instance:

Tue Nov 27 13:39:42 2012 Errors in file /opt/app/oracle/diag/rdbms/prdftdr/PRDFTDR1/trace/PRDFTDR1_pr05_19715.trc  (incident=81053): ORA-00600: internal error code, arguments: [kdourp_inorder2], [97], [97], [44], [36], [], [], [], [], [], [], [] Incident details in: /opt/app/oracle/diag/rdbms/prdftdr/PRDFTDR1/incident/incdir_81053/PRDFTDR1_pr05_19715_i81053.trc

Tue Nov 27 13:39:44 2012

Dumping diagnostic data in directory=[cdmp_20121127133944], requested by (instance=1, osid=19715 (PR05)), summary=[incident=81053]. Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details.

Tue Nov 27 13:39:46 2012 Sweep [inc][81053]: completed Sweep [inc2][81053]: completed

Tue Nov 27 13:40:39 2012

Slave exiting with ORA-10562 exception Errors in file /opt/app/oracle/diag/rdbms/prdftdr/PRDFTDR1/trace/PRDFTDR1_pr05_19715.trc: ORA-10562: Error occurred while applying redo to data block (file# 28, block# 4272) ORA-10564: tablespace BIWORK ORA-01110: data file 28: ‘+FSDATA/prdftdr/datafile/biwork.dbf’ ORA-10561: block type ‘TRANSACTION MANAGED DATA BLOCK’, data object# 6384973 ORA-00600: internal error code, arguments: [kdourp_inorder2], [97], [97], [44], [36], [], [], [], [], [], [], []

Tue Nov 27 13:40:39 2012 Recovery Slave PR05 previously exited with exception 10562

Tue Nov 27 13:40:40 2012 MRP0: Background Media Recovery terminated with error 448 Errors in file /opt/app/oracle/diag/rdbms/prdftdr/PRDFTDR1/trace/PRDFTDR1_pr00_19535.trc: ORA-00448: normal completion of background process

Tue Nov 27 13:40:40 2012 Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovered data files to a consistent state at change 21053777458 MRP0: Background Media Recovery process shutdown (PRDFTDR1)

on PRDFT (primary db):

select thread#, sequence#, name, first_change#, next_change# from gv$archived_log where first_change# > 21052000000 order by 4;

4  101524 +FSFLASH/prdft90/archivelog/2012_11_27/thread_4_seq_101524.2890.800458783  21053772886  21053782984

 

on PRDFTDR1 (standby db):

select thread#, sequence#, name, first_change#, next_change#, applied from gv$archived_log where first_change# > 21052000000 order by 4;

4  101521 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101521.6239.800457357  21053513127  21053534956 YES   4  101521 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101521.6239.800457357  21053513127  21053534956 YES   4  101521 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101521.6239.800457357  21053513127  21053534956 YES   4  101521 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101521.6239.800457357  21053513127  21053534956 YES   1  75523 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_1_seq_75523.4107.800458781  21053513767  21053779287 NO   1  75523 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_1_seq_75523.4107.800458781  21053513767  21053779287 NO   1  75523 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_1_seq_75523.4107.800458781  21053513767  21053779287 NO   1  75523 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_1_seq_75523.4107.800458781  21053513767  21053779287 NO   2  85943 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_2_seq_85943.4164.800458783  21053513855  21053781439 NO   2  85943 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_2_seq_85943.4164.800458783  21053513855  21053781439 NO   2  85943 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_2_seq_85943.4164.800458783  21053513855  21053781439 NO   2  85943 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_2_seq_85943.4164.800458783  21053513855  21053781439 NO   3  84413 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_3_seq_84413.5043.800458783  21053513860  21053781376 NO   3  84413 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_3_seq_84413.5043.800458783  21053513860  21053781376 NO   3  84413 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_3_seq_84413.5043.800458783  21053513860  21053781376 NO   3  84413 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_3_seq_84413.5043.800458783  21053513860  21053781376 NO   4  101522 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101522.1870.800458541  21053534956  21053712763 YES   4  101522 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101522.1870.800458541  21053534956  21053712763 YES   4  101522 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101522.1870.800458541  21053534956  21053712763 YES   4  101522 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101522.1870.800458541  21053534956  21053712763 YES   4  101523 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101523.282.800458781  21053712763  21053772886 YES   4  101523 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101523.282.800458781  21053712763  21053772886 YES   4  101523 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101523.282.800458781  21053712763  21053772886 YES   4  101523 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101523.282.800458781  21053712763  21053772886 YES   4  101524 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101524.5722.800458783  21053772886  21053782984 NO   4  101524 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101524.5722.800458783  21053772886  21053782984 NO   4  101524 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101524.5722.800458783  21053772886  21053782984 NO   4  101524 +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101524.5722.800458783  21053772886  21053782984 NO

 

after grabbing the 4 need arch logs from asm and scp’ing over to DR /tmp….

SQL> alter database register or replace logfile ‘/tmp/thread_1_seq_75523.2888.800458781’; Database altered. SQL> alter database register or replace logfile ‘/tmp/thread_2_seq_85943.2891.800458783’; Database altered. SQL> alter database register or replace logfile ‘/tmp/thread_3_seq_84413.2889.800458783’; Database altered. SQL> alter database register or replace logfile ‘/tmp/thread_4_seq_101524.2890.800458783’; Database altered.

after restarting the MRP, the corrupt block has been fixed… from the alert log on the standby you can see where the logs are being applied past the last good logfile with SCN > 21053777458

Tue Nov 27 15:54:35 2012 alter database register or replace logfile ‘/tmp/thread_1_seq_75523.2888.800458781’ There are 1 logfiles specified.

ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: alter database register or replace logfile ‘/tmp/thread_1_seq_75523.2888.800458781’ alter database register or replace logfile ‘/tmp/thread_2_seq_85943.2891.800458783’ There are 1 logfiles specified.

ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: alter database register or replace logfile ‘/tmp/thread_2_seq_85943.2891.800458783’

Tue Nov 27 15:54:53 2012 alter database register or replace logfile ‘/tmp/thread_3_seq_84413.2889.800458783’ There are 1 logfiles specified.

ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: alter database register or replace logfile ‘/tmp/thread_3_seq_84413.2889.800458783’ alter database register or replace logfile ‘/tmp/thread_4_seq_101524.2890.800458783’ There are 1 logfiles specified.

ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: alter database register or replace logfile ‘/tmp/thread_4_seq_101524.2890.800458783’

Tue Nov 27 15:56:34 2012 alter database recover managed standby database disconnect Attempt to start background Managed Standby Recovery process (PRDFTDR1)

Tue Nov 27 15:56:34 2012 MRP0 started with pid=46, OS id=22434 MRP0: Background Managed Standby Recovery process started (PRDFTDR1) started logmerger process

Tue Nov 27 15:56:39 2012 Managed Standby Recovery not using Real Time Apply Parallel Media Recovery started with 8 slaves Waiting for all non-current ORLs to be archived… All non-current ORLs have been archived.

Tue Nov 27 15:56:44 2012 Media Recovery Log /tmp/thread_4_seq_101524.2890.800458783 Media Recovery Log /tmp/thread_1_seq_75523.2888.800458781 Media Recovery Log /tmp/thread_2_seq_85943.2891.800458783 Media Recovery Log /tmp/thread_3_seq_84413.2889.800458783

Tue Nov 27 15:56:44 2012 Completed: alter database recover managed standby database disconnect Media Recovery Log +FSFLASH/prdftdr/archivelog/2012_11_27/thread_1_seq_75524.2475.800458813 Media Recovery Log +FSFLASH/prdftdr/archivelog/2012_11_27/thread_3_seq_84414.4918.800458813 Media Recovery Log +FSFLASH/prdftdr/archivelog/2012_11_27/thread_2_seq_85944.3819.800458813 Media Recovery Log +FSFLASH/prdftdr/archivelog/2012_11_27/thread_4_seq_101525.2507.800458801

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s