ORA-16766: Redo Apply is stopped

When I tried to check the status of my standby database, It has been reported as Redo Apply has been stopped. I tried to start it manually but that doesn’t help me.

Error message in the alert log


Errors in file /oracle/BHU/saptrace/diag/rdbms/BHU_b/BHU_1/trace/BHU_1_pr00_20307.trc:
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/oracle/BHU/112_64/dbs/UNNAMED00007'
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/oracle/BHU/112_64/dbs/UNNAMED00007'

(or)

you can view the message in the DG broker

bhuora01[BHU_1]>dgmgrl

DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys

Password:

Connected.

DGMGRL> show configuration;

Configuration - DG_BHU

  Protection Mode: MaxAvailability

  Databases:

    BHU_A - Primary database

    BHU_B - Physical standby database

      Error: ORA-16766: Redo Apply is stopped

Fast-Start Failover: DISABLED

Configuration Status:

ERROR

DGMGRL> show database verbose 'BHU_B';

Database - BHU_B

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       (unknown)

  Real Time Query: OFF

  Instance(s):

    BHU_1

    BHU_2 (apply instance)

  Database Error(s):

    ORA-16766: Redo Apply is stopped

  Properties:

    DGConnectIdentifier             = 'BHU_B_DG'

    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           = 'MANUAL'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '10'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = ''

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    SidName(*)

    StaticConnectIdentifier(*)

    StandbyArchiveLocation(*)

    AlternateLocation(*)

    LogArchiveTrace(*)

    LogArchiveFormat(*)

    TopWaitEvents(*)

    (*) - Please check specific instance for the property value

Database Status:

ERROR

DGMGRL>

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

STATUS    COUNT(1)

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

ONLINE           5

RECOVER          1

SYSTEM           1

SQL> select name from v$datafile where status != 'ONLINE';

NAME

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

+BHU_DATA1/BHU_b/datafile/system.259.764182789

/oracle/BHU/112_64/dbs/UNNAMED00007

SQL> select name,status from v$datafile where status != 'ONLINE';

NAME                                                                                     STATUS

-------

+BHU_DATA1/BHU_b/datafile/system.259.764182789     SYSTEM

/oracle/BHU/112_64/dbs/UNNAMED00007              RECOVER

POSSIBLE CAUSE FOR THIS ISSUE

1)      In my case, STANDBY_FILE_MANAGEMENT has been set as MANUAL. So it was creating the new datafiles in the $ORACLE_HOME/dbs folder. so recover process couldnt apply the logs

To find the files which are created in the UNKNOWN directory

select FNNAM,FNONM from x$kccfn where FNFNO=<Datafile_number>;

Note: you can get this value of datafile from alert_SID.log

A)    first you need to create the datafile in a proper position and start the recover

Ex: For moving file from $ORACLE_HOME to correct ASM DISK GROUP

SQL> alter database create datafile '/oracle/BHU/112_64/dbs/UNNAMED00007' as '+BHU_DATA1';

Database altered.

One more situation


SQL> alter database create datafile '/oracle/BHU/112_74/dbs/UNNAMED00007' as '+BHU_B_SYSTEM';
alter database create datafile '/oracle/BHU/112_74/dbs/UNNAMED00007' as '+BHU_B_SYSTEM'
*
ERROR at line 1:
ORA-01137: specified size of file 7 (12800 blocks) is less than original size
of 4194177 blocks
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/oracle/BHU/112_74/dbs/UNNAMED00007'

you might have the UNNAMED file in the $ORACLE_HOME/dbs location itself. so you have to use the size option. you need to identify the datafile size and create like below

SQL> alter database create datafile '/oracle/BHU/112_64/dbs/UNNAMED00007' as '+BHU_B_SYSTEM' size 34358689792;

Database altered.

B)     Try to change the parameter standby_file_management=AUTO

2)      You have to change the standbyfile management across all the instance in the cluster database and you have to change it in the broker configuration also.

3)      standby database recovery process might be stopped or it might killed by some other process, so you need to start it on the apply instance

To find the apply instance on RAC environment

DISPLAY DB CONFIGURATION DETAILS

DGMGRL> show database 'BHU_B';

Database - BHU_B

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: OFF

  Instance(s):

    BHU_1 (apply instance)

    BHU_2

Database Status:

SUCCESS

DGMGRL> exit

Login in to the Apply instance, stop and start the recover process.

bhuora01[BHU_1]>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Oct 19 13:41:22 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

-- STOPING RECOVERY PROCESS

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  CANCEL;

Database altered.

-- STARTING RECOVERY PROCESS

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

Go to the apply instance and check whether the apply process is started or not

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

I hope this helps to solve your issue, please provide your feedback. Happy learning!!!!!