ORA-01156: recovery or flashback in progress may need access to files

Error:

ORA-01156: recovery or flashback in progress may need access to files

When: While adding standby redo log files in standby database

Solution:

1.Cancel the recovery 

alter database recover managed standby database cancel ;

2. Add standby redo log file 

alter database add standby logfile group 11 ('+DATA1','+DATA1') size 1G;

3. Restart the recovery

alter database recover managed standby database disconnect from session;
 





DEMO:


SQL> alter database add standby logfile group 9 ('+DATA1','+DATA1') size 1G;
alter database add standby logfile group 9 ('+DATA1','+DATA1') size 1G
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files



SQL> alter database recover managed standby database cancel ;

Database altered.

SQL> alter database add standby logfile group 9 ('+DATA1','+DATA1') size 1G;

Database altered.

SQL> alter database add standby logfile group 10 ('+DATA1','+DATA1') size 1G;

Database altered.

SQL> alter database add standby logfile group 11 ('+DATA1','+DATA1') size 1G;

Database altered.


SQL> select * from v$standby_log;
    GROUP# DBID                                        THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
         9 UNASSIGNED                                        0          0 1073741824        512          0 YES UNASSIGNED
        10 UNASSIGNED                                        0          0 1073741824        512          0 YES UNASSIGNED
        11 UNASSIGNED                                        0          0 1073741824        512          0 YES UNASSIGNED


SQL>  alter database recover managed standby database disconnect from session;

Database altered.




Note: 
+DATA1 is the ASM disk group path specified , in your case if its local/storage file system then specify the absolute path for redo log file & Ensure available free space in diskgroup or files system

Use Below to identify the free space in ASM diskgroups:

set line 900
set pages 900
col NAME for a20
col PATH for a50
select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,STATE,OS_MB,TOTAL_MB,FREE_MB,HOT_USED_MB,COLD_USED_MB,NAME,PATH ,CREATE_DATE
 from v$asm_disk;

 

3 comments:

  1. hi Ajay ,

    I have an issue regarding creating standby database .Iam new to oracle need help

    1) I have a physical standby server setup with same filesystem format as primary.
    2) My database size is 300 G no much activity is going on .So i just made database in backup mode and copied over all my datafiles todestination standby host.
    3) I did copied my control file as standby control file from primary to standby

    alter database create standby controlfile as '/oracle/CLARITY/home/cntrlCLARITY.ctl';

    Database altered.

    4) I made modifications in init.ora file and made sure that it uses new standby control file.

    5) alter database mount;

    6) alter database recover managed standby database disconnect from session;

    I see this in my alert log :


    Clearing online log 1 of thread 1 sequence number 83241
    Thu Apr 18 18:30:52 2013
    Errors in file /ebay/oracle/CLARITY/trace/bdump/clarity_mrp0_4555.trc:
    ORA-00313: open failed for members of log group 1 of thread 1
    ORA-00312: online log 1 thread 1: '/oracle/CLARITY/redo/CLARITY_redo01.log'
    ORA-27037: unable to obtain file status
    SVR4 Error: 2: No such file or directory
    Additional information: 3
    Thu Apr 18 18:30:52 2013
    Errors in file /ebay/oracle/CLARITY/trace/bdump/clarity_mrp0_4555.trc:
    ORA-19527: physical standby redo log must be renamed
    ORA-00312: online log 1 thread 1: '/oracle/CLARITY/redo/CLARITY_redo01.log'
    Clearing online redo logfile 1 complete
    Media Recovery Waiting for thread 1 sequence 83173
    Fetching gap sequence in thread 1, gap sequence 83173-83242
    FAL[client]: Error fetching gap sequence, no FAL server specified
    Thu Apr 18 18:30:53 2013
    Completed: alter database recover managed standby database disconnect from session
    Thu Apr 18 18:31:22 2013
    FAL[client]: Failed to request gap sequence
    GAP - thread 1 sequence 83173-83242
    DBID 1473224392 branch 604202760
    FAL[client]: All defined FAL servers have been attempted.
    -------------------------------------------------------------
    Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
    parameter is defined to a value that is sufficiently large
    enough to maintain adequate log switch information to resolve
    archivelog gaps.
    -------------------------------------------------------------






    ReplyDelete
    Replies
    1. Hi SP ,

      Perform or verify below ..
      1. Check if TNS entry added in prim/standby mentioning the service/alias same as mentioned in fal_server/fal_client
      2. Make sure tnsping is resolvable from both prim/stby when tried against each other
      3. verify parameter likes fal_server/fal_client,log_archive_config is set properly
      4. Also create password file in primary & copy to standby $ORACLE_HOME/dbs location (Unix) using orapwd utility
      5. Copy the redo logs in same destination on primary to resolve ORA- error mention above
      Point 1-4 should ensure that RFS/FAL able to push/pull archives across the setup

      Thanks,
      Ajay More

      Delete

  2. an excellent solution, thanks for sharing, you really save my time!
    Richard Brown vdr virtual data room

    ReplyDelete

Popular Posts