cancel
Showing results for 
Search instead for 
Did you mean: 

Error: ORA-16766: Redo Apply is stopped

Former Member
0 Kudos

Hello friends,

I am new to dataguard concept and I am facing the above issue on a system .

Please find the details below.

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

DGMGRL> show configuration

Configuration - SID

  Protection Mode: MaxAvailability

  Databases:

    SID_HOST1 - Primary database

    SID_HOST2 - Physical standby database

      Error: ORA-16766: Redo Apply is stopped

Fast-Start Failover: DISABLED

Configuration Status:

ERROR

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

DGMGRL> show database SID_STDBYHOST

Database - SID_STDBYHOST

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds (computed 0 seconds ago)

  Apply Lag:       11 hours 6 minutes 15 seconds (computed 0 seconds ago)

  Apply Rate:      18.35 MByte/s

  Real Time Query: OFF

  Instance(s): SID

 

  Database Error(s):

    ORA-16766: Redo Apply is stopped

Database Status:

ERROR

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

DGMGRL> show database verbose SID_STDBYHOST

Database - SID_HOSTSTDBY

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds (computed 0 seconds ago)

  Apply Lag:       11 hours 8 minutes 22 seconds (computed 0 seconds ago)

  Apply Rate:      18.35 MByte/s

  Real Time Query: OFF

  Instance(s)

    SID

  Database Error(s):

    ORA-16766: Redo Apply is stopped

  Properties:

    DGConnectIdentifier             = 'sid_stdbyhost'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'SYNC'

    DelayMins                       = '0'

    Binding                         = 'optional'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '14'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = 'sid_primaryhost'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    ApplyLagThreshold               = '0'

    TransportLagThreshold           = '0'

    TransportDisconnectedThreshold  = '30'

    SidName                         = 'SID'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=STDBYHOST)(PORT=1519))(CONNECT_DATA=(SERVICE_NAME=SID_HOST_DGMGRL)(INSTANCE_NAME=SID)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = '/oracle/SID/oraarch/SIDarch'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    TopWaitEvents                   = '(monitor)'

Database Status:

ERROR

pLEASE FIND OTHER DETAILS in the screenshots attached.

Please help me in making thye DB's consistent.

As per this blog , I found out that

alter database recover managed standby database disconnect from session;

could help me.

Please get back to me.

Thanks and regards,

Uday.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

first, you must check the alert log. (alert_<SID>.log from primary and standby)


it could be new datafile cannot be created or an archivelog not copied to the destination.



if it is just an archivelog not copied, start sql command on standby :

select sequence#,applied from v$archived_log order by sequence#;


copy the missing archivelog (make sure to have same owner and group access setting to these files)


and start those commands :

>rman target /

>CATALOG START WITH '/oracle/<SID>/oraarch/';


accept and quit.


and restart apply


>dgmgrl

DGMGRL> connect sys@<standby>;

  1. Connected.

DGMGRL> edit database '<standby>' set state='APPLY-OFF';

  1. Succeeded.

DGMGRL> edit database '<standby>' set state='APPLY-ON';

  1. Succeeded.


check alert log in parallel.



Regards,

Rodolphe ALT

http://moncv.altr-consulting.com



Former Member
0 Kudos

Hello Rudolph,

Thanks for the reply.

1.) As shown below the status now is :

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

Protection Mode: MaxAvailability

  Databases:

    SID_PRIMARYHOST - Primary database

      Error: ORA-16810: multiple errors or warnings detected for the database

    SID_SECHOST - Physical standby database

      Warning: ORA-16792: configurable property value is inconsistent with database setting

Fast-Start Failover: DISABLED

Configuration Status:

ERROR

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

2.) Also, the command

select sequence#,applied from v$archived_log order by sequence#;


PRIMARY gave many rows and there are quite a few with status NO which means they are not applied.

In standby , the command gave lesser sequence numbers ALL with YES status.

There is a file with RECOVER status which in STANDBY BUT NOT IN PRIMARY.

-------------standby

SQL> SELECT DISTINCT STATUS,count(1) from v$datafile group by status;

STATUS    COUNT(1)

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

ONLINE         442

RECOVER          1

SYSTEM           1

-----------------PRIMARY

SQL> SELECT DISTINCT STATUS,count(1)from v$datafile group by status;

STATUS    COUNT(1)

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

ONLINE         445

SYSTEM           1

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

I came to know that this warning "ORA-16792: configurable property value is inconsistent with database setting" goes with restart of the broker.


--> alter system set dg_broker_start=false scope=both;


--> alter system set dg_broker_start=true scope=both;


Please help me in solving , more importantly UNDERSTANDING WAHT'S GOING ON.

Thanks in advance,

Uday

Former Member
0 Kudos

JUST TO ADD , Also the oraarch folder in standby is 100% full.

We had primary oraarch increasing rapidly also in the morning and we had to take a manual archive backup of the files.

Former Member
0 Kudos

Ok, now we will try to repair the issue about recover datafile,

please start this command :

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME 

FROM   V$DATAFILE_HEADER 

WHERE  RECOVER = 'YES'  OR     (RECOVER IS NULL AND ERROR IS NOT NULL);

and could you please check the alert log on both systems.

some errors from these alert log can help me to understand major problem.

Former Member
0 Kudos

Hello Rudolph,

Unfortunately  the alert log file on both primary and standby are not up to date , i mean, the last log in the files on both hosts are dated 27th july.

Plesae find the output of the command below.

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

SQL> SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME

FROM   V$DATAFILE_HEADER

WHERE  RECOVER = 'YES'  OR     (RECOVER IS NULL AND ERROR IS NOT NULL);  2    3

     FILE# STATUS

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

ERROR                                                             REC

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

TABLESPACE_NAME

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

NAME

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

       220 ONLINE

FILE MISSING

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

Thanks again.

Uday.

Former Member
0 Kudos

Just to add , the last update in the alert log file for 27th july is when someone in our team enabled  FSFO --> primary went down --> SAP did not come up due to problems with HA config problems.

----------------standby

Sat Jun 27 18:40:00 2015

Shutting down instance (immediate)

Shutting down instance: further logons disabled

Stopping background process MMNL

Stopping background process MMON

License high water mark = 2

ALTER DATABASE CLOSE NORMAL

ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

ALTER DATABASE DISMOUNT

Shutting down archive processes

Archiving is disabled

Completed: ALTER DATABASE DISMOUNT

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Sat Jun 27 18:40:03 2015

Stopping background process VKTM

Sat Jun 27 18:40:06 2015

Instance shutdown complete

-----------------------------primary

Completed: ALTER DATABASE DISMOUNT

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Sat Jun 27 18:18:43 2015

Stopping background process VKTM

ARCH: Archival disabled due to shutdown: 1089

Shutting down archive processes

Archiving is disabled

Sat Jun 27 18:18:46 2015

Instance shutdown complete

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

Regards,

Uday

Former Member
0 Kudos

1.

Then on the Primary find that file:

SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=220;

Back on the Standby find the file:

SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=220;

2.

from oracle trc files, search keyword "ORA-01110" or "UNNAMED".

The issue is probably STANDBY_FILE_MANAGEMENT related, but could be a disk space or incorrect path issue too.

3.

from alert.log, I understand oracle db primary and standby are shutdown.

right ?

if oracle db is open, these alert log cannot be right.

Former Member
0 Kudos

Hello Rudolph,

---------------------------------------------------------------------------------------------------------------------standby

SQL> SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=220;

     FILE#

----------

NAME

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

       220

/oracle/SID/112_64/dbs/UNNAMED00220

--------------------------------------------------------------------------------------------------------------------PRIMARY

SQL> SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=220;

     FILE#

----------

NAME

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

       220

/oracle/SID/sapdata28/sr3740_1/sr3740.data1

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

--> The primary database is up now - so is SAP ; We had issue on 27th july when system went down because FSFO was enabled and SAP couldnt start on standby due to Heartbeat issues (FSFO was in status DISABLED before that)

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

-------------------------------Log from drc_SID.log

08/04/2015 18:21:55

Error: The actual protection level 'RESYNCHRONIZATION' is different from the configured protection mode 'MAXIMUM AVAILABILITY'.

Found unresolvable gap to database SID_STANDBY.

Data Guard Broker Status Summary:

  Type                        Name                             Severity  Status

  Configuration              SID                              Warning  ORA-16607

  Primary Database           SID_PRIMARY                 Error  ORA-16810

  Physical Standby Database   SID_STANDBY              Warning  ORA-16792

08/04/2015 18:22:01

Error: The actual protection level 'RESYNCHRONIZATION' is different from the configured protection mode 'MAXIMUM AVAILABILITY'.

Found unresolvable gap to database SID_STANDBY.

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

ALSO these are some lines from LGWR TRACE FILE.

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

ORA-16198: LGWR received timedout error from KSR

ERROR: Received error on receiving channel message 16198

Error 16198 detaching RFS from standby instance at host 'sid_standby'

*** 2015-08-03 17:53:07.737 7160 krsu.c

Making upidhs request to NSS2 (ocis 0x7ff09fa25a88). Begin time is <08/03/2015 17:52:37> and NET_TIMEOUT <30> seconds

NetServer pid:20168

*** 2015-08-03 17:53:07.737 4329 krsh.c

LGWR: Error 16198 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'sid_standby'

krsl_reap_io: ignoring detach error 16198

*** 2015-08-03 17:53:07.737 2917 krsi.c

krsi_dst_fail: dest:2 err:16198 force:0 blast:1

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

Plesae find the alert log attached.

Uday

Former Member
0 Kudos

ok, thanks.

now I can identify the reason of recover status file.

you cannot continue to apply archivelog to standby if this issue is not solved.

as wrote previously :


The issue is probably STANDBY_FILE_MANAGEMENT related, but could be a disk space or incorrect path issue too.



from oracle trc files, you must have the explication about this unamed file. Do you know if oracle parameter "standby_file_management" was set to AUTO ?

(it is better)



to solve this issue :

make sure the filesystem have freespace avalaible to standby.

create manually folder with ora<sid> user :

mkdir -p /oracle/SID/sapdata28/sr3740_1/

DGMGRL> edit database <standby> set state='APPLY-OFF';

SQL> select name from v$datafile where name like '%AMED%';

SQL> alter system set standby_file_management='MANUAL';

SQL> alter database create datafile '/oracle/SID/112_64/dbs/UNNAMED00220' as '/oracle/SID/sapdata28/sr3740_1/sr3740.data1';

SQL> alter system set standby_file_management='AUTO';

DGMGRL> edit database <standby> set state='APPLY-ON';

DGMGRL> show configuration;

check alert log to follow the next activity.

and next to enable FSFO, try to follow this tuto.

Regards,

Rodolphe



Former Member
0 Kudos

Hello Rudolph,

As shown below, the parameter is AUTO now.

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

ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

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

The oraarch is filled up completely ; rest are ok.

I will get back to you with the output in some time.

Thanks Rudolphe.

Former Member
0 Kudos

I will check the trc files again.

Answers (0)