Question Re: dataguard error in Technology Q&A

dataguard error

Former Member — Tue, 06 Oct 2015 03:16:24 GMT

Hello ,

We have dataguard error on a system.

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

DGMGRL> show configuration

Configuration - sid

Protection Mode: MaxAvailability

Databases:

sid_primary - Primary database

Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database

sid_standby - (*) Physical standby database

Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database

Fast-Start Failover: ENABLED

Configuration Status:

ERROR

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

DGMGRL> show database sid_host

Database -

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 8 days 8 hours 44 minutes 12 seconds (computed 0 seconds ago)

Apply Lag: (unknown)

Apply Rate: (unknown)

Real Time Query: OFF

Instance(s):

SID

Database Error(s):

ORA-16766: Redo Apply is stopped

Database Warning(s):

ORA-16817: unsynchronized fast-start failover configuration

Database Status:

ERROR

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

2.) On standby :

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

THREAD# LAST_APPLIED_LOG

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

1 48210

Primary :

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

THREAD# LAST_APPLIED_LOG

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

1 49043

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

3.) on alertlog _ standby :

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

Errors in file /oracle/SID/saptrace/diag/rdbms/sid_standby/SID/trace/SID_pr09_846.trc:

ORA-00600: internal error code, arguments: [3020], [31], [234430], [130257854], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 31, block# 234430, file offset is 1920450560 bytes)

ORA-10564: tablespace PSAPUNDO

ORA-01110: data file 31: '/oracle/SID/sapdata1/undo_4/undo.data4'

ORA-10560: block type 'KTU UNDO BLOCK'

Errors in file /oracle/SID/saptrace/diag/rdbms/sid_nstandby/SID/trace/SID_pr03_834.trc (incident=16331):

ORA-00600: internal error code, arguments: [3020], [60], [686250], [252344490], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 60, block# 686250, file offset is 1326792704 bytes)

ORA-10564: tablespace PSAPUNDO

ORA-01110: data file 60: '/oracle/SID/sapdata3/undo_7/undo.data7'

ORA-10560: block type 'KTU UNDO BLOCK'

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

There are many such files not applied on primary :

SEQUENCE# APPLIED

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

47924 YES

47925 NO

47925 YES

47926 NO

47926 YES

47927 NO

47927 YES

47928 NO

47928 YES

47929 NO

47929 YES

SEQUENCE# APPLIED

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

47930 NO

47930 YES

47931 NO

47931 YES

47932 NO

47932 YES

47933 NO

47933 YES

47934 NO

47934 YES

47935 NO

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

how to proceed in making standby consistent?

Thanks,

Uday

Re: dataguard error

former_member182657 — Tue, 06 Oct 2015 03:37:50 GMT

Hi Uday,


Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database

Could you share result for query


select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024/1024 ,status from v$standby_log;

Regards,

Re: dataguard error

Former Member — Tue, 06 Oct 2015 03:55:59 GMT

Hi Gaurav,

Thanks for the reply.

Please find the output.

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

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024/1024 ,status from v$standby_log;

GROUP# THREAD# SEQUENCE# BYTES/1024/1024/1024 STATUS

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

1 1 0 .146484375 UNASSIGNED

2 1 49047 .146484375 ACTIVE

3 1 0 .146484375 UNASSIGNED

4 1 0 .146484375 UNASSIGNED

11 1 0 .146484375 UNASSIGNED

12 1 0 .146484375 UNASSIGNED

13 1 0 .146484375 UNASSIGNED

7 rows selected.

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

Thanks,

Uday

Re: dataguard error

former_member182657 — Tue, 06 Oct 2015 04:27:52 GMT

Hi Uday,

Could you try to stop the MRP in standby system with query


alter database recover managed standby database cancel ;

& can proceed to drop the standby redo logs with help of query

e.g ALTER DATABASE DROP STANDBY LOGFILE GROUP 1;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 3;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;

....... up to 13th.

After that recreate all with reuse option,e.g


alter database add standby logfile group 1 '/?.log'  size ? reuse;

Here you need to opt the location & size to create the the above said standby log files. After the procedure share the result for dgmgrl status & show configuration verbose.

Regards,

Re: dataguard error

stefan_koehler — Tue, 06 Oct 2015 10:27:38 GMT

Hi Uday,

the root cause of your issue is absolutely obvious.


ORA-00600: internal error code, arguments: [3020], [31], [234430], [130257854], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 31, block# 234430, file offset is 1920450560 bytes)

MOS ID #1265884.1 describes the reasons for this issue (and how to fix it). However it is important to understand that "in the majority of cases, standby stuck recovery errors indicate a corruption of the primary database. No errors may have been reported on the primary. The ORA-600 [3020] stuck recovery error could occur on the Standby database for several reasons including: a lost write on the Primary, a lost write on the Standby, missing redo, or logical corruption on the primary resulting in an incomplete redo chain."

Regards

Stefan