Hello All,
We have a 2-node RAC primary database with two physical standby databases (another 2-node RAC and a single instance) running on 11.2.0.2.7 over linux RHEL.
We had some troubles with the 2-node RAC working as standby. A few days ago we found that the FLASHBACK directory within our ASM structure was consuming almost the 95% of our +FLASH diskgroup. For some reason it was not cleaning up the old flashback files older than the 1440 minutes (flashbackup retention) was set to 720. We disabled and re-enabled the flashback and also reduced the retention with no positive results.
Someone fell in the temptation of dropping all the flashback files and recreate the control file as described in Metalink Note (ID 342160.1), and he failed to recreate the controlfile as described. We still cannot open our standby.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
SQL> select * from v$restore_point;
select * from v$restore_point
*
ERROR at line 1:
ORA-38701: Flashback database log 1 seq 1 thread 1:
"+FLASH/alephsb1/flashback/log_1.4992.784896269"
ORA-17503: ksfdopn:2 Failed to open file
+FLASH/alephsb1/flashback/log_1.4992.784896269
ORA-15012: ASM file '+FLASH/alephsb1/flashback/log_1.4992.784896269' does not
exist
The current flashback retention:
SQL> show parameter flashback_retention
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_flashback_retention_target integer 720
The current free space at each diskgroup and the contents of the +FLASH/ALEPHSB1/FLASHBACK directory:
+ASM1@argalephdbcont1:/u01/app/oracle/diag/rdbms/alephsb1/ALEPHSB1/trace>asmcmd lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 381479 25086 0 25086 0 N DATA1/
MOUNTED EXTERN N 512 4096 1048576 381479 369363 0 369363 0 N FLASH/
MOUNTED NORMAL N 512 4096 1048576 4900 3970 980 1495 0 Y QUORUM/
+ASM1@argalephdbcont1:/u01/app/oracle/diag/rdbms/alephsb1/ALEPHSB1/trace>asmcmd ls -lt +FLASH/ALEPHSB1/FLASHBACK
Type Redund Striped Time Sys Name
FLASHBACK UNPROT COARSE JUN 24 15:00:00 Y log_10540.10434.818553629
Any suggestion?
Regards.
Martin