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;
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;
hi Ajay ,
ReplyDeleteI 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.
-------------------------------------------------------------
Hi SP ,
DeletePerform 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
ReplyDeletean excellent solution, thanks for sharing, you really save my time!
Richard Brown vdr virtual data room
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