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;



4 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
  3. My spouse and I love your blog and find almost all of your posts to be just what I’m looking for. Appreciating the persistence you put into your blog and the detailed information you provide. I found another one blog like you Oracle ADF.Actually I was looking for the same information on internet for Oracle Application Development Framework and came across your blog. I am impressed by the information that you have on this blog. Thanks once more for all the details.

    ReplyDelete