Tuesday, October 25, 2011

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!!!!!

3 comments:

  1. 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.


    hi Bhuvan,

    you have mentioned that we have to change it in broker configuration also, can you give me the steps to change it in broker config.

    Thanks

    ReplyDelete
  2. EDIT DATABASE 'DB_PRIMARY_NAME' SET PROPERTY 'StandbyFileManagement'='AUTO';
    EDIT DATABASE 'DB_STANDBY_NAME' SET PROPERTY 'StandbyFileManagement'='AUTO';

    ReplyDelete