Hoy vamos a ver una de esas entradas que, nos puede volver algo locos, pero cuya solucion no dejan de ser asombrosamente sencilla.
Cuando tenemos servidores con un diskgroup de FRA compartido, podemos tener el caso de que la ocupacion de la FRA de una de ellas ponga en peligro la disponibilidad e todas ellas.
Esto se agrava aun mas en el caso de que tengamos el Flashback activado.
Veamos un caso practico , aqui tenemos una base de datos donde toda la ocupacion esta en el Flashback
+FRA/SIDTEST/ subdirectories size
Subdir Used MB Mirror MB
------ ------- ---------
ARCHIVELOG/ 306 306
CHANGETRACKING/ 12 12
CONTROLFILE/
FLASHBACK/ 163251 163251
ONLINELOG/
------ ------- ---------
Total 163569 163569
SI llevamoS a cabo algunos chequeos sobre la FRA tenemos que
SQL> select NAME,FLASHBACK_ON from v$database;
NAME FLASHBACK_ON
------------------------------------------------------------ ------------------------------------------------------
SIDTEST YES
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 284244
Next log sequence to archive 284246
Current log sequence 284246
show parameter undo_retention
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
undo_retention integer 900
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_flashback_retention_target integer 1440
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 600G
SQL> show parameter db_recovery_file_dest_size
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest_size big integer 600G
SQL> select name,flashback_on from v$tablespace where flashback_on!='YES';
no rows selected
SQL> SELECT ESTIMATED_FLASHBACK_SIZE/1024/1024/1024 Gb FROM V$FLASHBACK_DATABASE_LOG;
GB
----------
61.307373
SQL> SELECT FLASHBACK_SIZE/1024/1024/1024 Gb FROM V$FLASHBACK_DATABASE_LOG;
GB
----------
159.225609
El problema que tenemos, es que queremos liberar esos 160 Gb, pero no podemos.
Como hacerlo
Hay un truco muy sucio, pero efectivo, es modificar el parametro del tamaño del db_recovery_file_dest_size
En el momento en que seteamos el db_recovery_file_dest_size por debajo de laocupacion actual, la propia base de datos vacia ficheros
select sum(FLASHBACK_SIZE)/1024/1024/1024
from V$FLASHBACK_DATABASE_LOG;
SUM(FLASHBACK_SIZE)/1024/1024/1024
----------------------------------
159.225609
SQL> alter system set db_recovery_file_dest_size=50G;
System altered.
SQL> alter system set db_recovery_file_dest_size=600G;
System altered.
select sum(FLASHBACK_SIZE)/1024/1024/1024
from V$FLASHBACK_DATABASE_LOG;
SUM(FLASHBACK_SIZE)/1024/1024/1024
----------------------------------
48.3398438
Veamos que ha asado en el alert.log
ALTER SYSTEM SET db_recovery_file_dest_size='50G' SCOPE=BOTH;
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_22.12053.988373613
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_23.1360.988373647
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_24.12976.988373689
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_25.3952.988373743
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_26.3865.988373799
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_27.12048.988374121
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_28.12304.988374251
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_29.12489.988374301
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_30.1388.988374391
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_31.2666.988374467
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_32.3815.988374789
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_44.4188.988555917
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_45.3063.988555949
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_46.14572.988555983
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_47.12737.988556005
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_48.2546.988556023
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_49.4396.988556049
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_50.2232.988556075
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_51.11446.988556097
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_52.13247.988556125
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_53.13228.988556147
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_60.13017.1003940269
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_54.1819.988556173
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_61.4863.1004080645
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_62.11394.1004080677
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_63.11263.1004083037
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_33.13079.988374861
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_34.11600.988374963
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_35.11804.988377507
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_36.4013.988377931
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_37.3242.988378457
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_59.11364.1003507407
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_38.4922.988378951
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_1.13038.988205331
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_2.1565.988205339
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_64.10813.1004102837
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_3.1899.988209247
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_4.11864.988209287
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_5.1311.988209309
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_6.1715.988209341
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_65.9603.1004103287
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_7.11990.988209441
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_8.3989.988209567
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_9.12500.988209599
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_10.6424.988209633
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_55.1243.989342719
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_11.13330.988209659
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_12.12103.988209683
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_56.3201.989352547
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_66.8648.1004135213
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_67.1132.1004135251
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_68.5686.1004135281
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_69.10834.1004135313
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_70.11789.1004135357
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_71.11145.1004135395
Deleted Oracle managed file +FRA/SIDTEST/flashback/log_72.13516.1004135445
ALTER SYSTEM SET db_recovery_file_dest_size='600G' SCOPE=BOTH;
cOMO PODEIS VER.. SUCIO PERO EFECTIVO