{"id":2003,"date":"2019-02-25T21:57:43","date_gmt":"2019-02-25T20:57:43","guid":{"rendered":"http:\/\/clemente.pamplona.name\/dba\/?p=2003"},"modified":"2019-05-05T20:15:15","modified_gmt":"2019-05-05T18:15:15","slug":"limpiando-el-flashback-de-la-fra","status":"publish","type":"post","link":"http:\/\/clemente.pamplona.name\/dba\/limpiando-el-flashback-de-la-fra\/","title":{"rendered":"Limpiando el FLASHBACK de la FRA"},"content":{"rendered":"

Hoy vamos a ver una de esas entradas que, nos puede volver algo locos, pero cuya solucion no dejan de ser asombrosamente sencilla.<\/p>\n

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.
\nEsto se agrava aun mas en el caso de que tengamos el Flashback activado.
\nVeamos un caso practico , aqui tenemos una base de datos donde toda la ocupacion esta en el Flashback<\/p>\n

\r\n+FRA\/SIDTEST\/ subdirectories size\r\nSubdir             Used MB       Mirror MB\r\n------             -------       ---------\r\nARCHIVELOG\/           306             306\r\nCHANGETRACKING\/        12              12\r\nCONTROLFILE\/\r\nFLASHBACK\/          163251          163251\r\nONLINELOG\/\r\n     ------         -------       ---------\r\nTotal                163569          163569\r\n<\/pre>\n
SI llevamoS a cabo algunos chequeos sobre la FRA tenemos que <\/p>\n
\r\n\r\nSQL> select NAME,FLASHBACK_ON from v$database;\r\nNAME                                                        FLASHBACK_ON\r\n------------------------------------------------------------ ------------------------------------------------------\r\nSIDTEST                                                      YES\r\n\r\nSQL> archive log list\r\nDatabase log mode         Archive Mode\r\nAutomatic archival             Enabled\r\nArchive destination            USE_DB_RECOVERY_FILE_DEST\r\nOldest online log sequence     284244\r\nNext log sequence to archive   284246\r\nCurrent log sequence           284246\r\n\r\nshow parameter undo_retention\r\nNAME                                 TYPE                              VALUE\r\n------------------------------------ --------------------------------- ------------------------------\r\nundo_retention                       integer                           900\r\n\r\nSQL> show parameter DB_FLASHBACK_RETENTION_TARGET\r\nNAME                                 TYPE                              VALUE\r\n------------------------------------ --------------------------------- ------------------------------\r\ndb_flashback_retention_target        integer                           1440\r\n\r\nSQL> show parameter  db_recovery_file_dest\r\nNAME                                 TYPE                              VALUE\r\n------------------------------------ --------------------------------- ------------------------------\r\ndb_recovery_file_dest                string                            +FRA\r\ndb_recovery_file_dest_size           big integer                       600G\r\n\r\n\r\nSQL> show parameter  db_recovery_file_dest_size\r\n------------------------------------ --------------------------------- ------------------------------\r\ndb_recovery_file_dest_size           big integer                       600G\r\n\r\n\r\nSQL> select  name,flashback_on   from   v$tablespace where flashback_on!='YES';\r\nno rows selected\r\n\r\nSQL> SELECT ESTIMATED_FLASHBACK_SIZE\/1024\/1024\/1024 Gb  FROM V$FLASHBACK_DATABASE_LOG;\r\n        GB\r\n----------\r\n 61.307373\r\n\r\nSQL> SELECT FLASHBACK_SIZE\/1024\/1024\/1024 Gb  FROM V$FLASHBACK_DATABASE_LOG;\r\n        GB\r\n----------\r\n159.225609\r\n<\/pre>\n
El problema  que tenemos, es que queremos liberar esos 160 Gb, pero no podemos.<\/p>\n
Como hacerlo <\/h1>\n
Hay un truco muy sucio, pero efectivo, es  modificar el parametro del tama\u00f1o del db_recovery_file_dest_size<\/p>\n
En el momento en que seteamos el db_recovery_file_dest_size por debajo de laocupacion actual, la propia base de datos vacia ficheros <\/p>\n
\r\n select sum(FLASHBACK_SIZE)\/1024\/1024\/1024\r\n from V$FLASHBACK_DATABASE_LOG;\r\nSUM(FLASHBACK_SIZE)\/1024\/1024\/1024\r\n----------------------------------\r\n                        159.225609\r\n\r\nSQL>  alter system set db_recovery_file_dest_size=50G;\r\nSystem altered.\r\n\r\nSQL> alter system set db_recovery_file_dest_size=600G;\r\nSystem altered.\r\n\r\nselect sum(FLASHBACK_SIZE)\/1024\/1024\/1024\r\n from V$FLASHBACK_DATABASE_LOG;\r\nSUM(FLASHBACK_SIZE)\/1024\/1024\/1024\r\n----------------------------------\r\n                        48.3398438\r\n<\/pre>\n
Veamos que ha asado en el alert.log <\/p>\n
\nALTER SYSTEM SET db_recovery_file_dest_size='50G' SCOPE=BOTH;
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_22.12053.988373613
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_23.1360.988373647
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_24.12976.988373689
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_25.3952.988373743
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_26.3865.988373799
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_27.12048.988374121
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_28.12304.988374251
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_29.12489.988374301
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_30.1388.988374391
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_31.2666.988374467
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_32.3815.988374789
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_44.4188.988555917
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_45.3063.988555949
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_46.14572.988555983
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_47.12737.988556005
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_48.2546.988556023
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_49.4396.988556049
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_50.2232.988556075
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_51.11446.988556097
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_52.13247.988556125
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_53.13228.988556147
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_60.13017.1003940269
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_54.1819.988556173
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_61.4863.1004080645
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_62.11394.1004080677
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_63.11263.1004083037
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_33.13079.988374861
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_34.11600.988374963
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_35.11804.988377507
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_36.4013.988377931
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_37.3242.988378457
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_59.11364.1003507407
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_38.4922.988378951
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_1.13038.988205331
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_2.1565.988205339
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_64.10813.1004102837
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_3.1899.988209247
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_4.11864.988209287
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_5.1311.988209309
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_6.1715.988209341
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_65.9603.1004103287
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_7.11990.988209441
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_8.3989.988209567
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_9.12500.988209599
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_10.6424.988209633
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_55.1243.989342719
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_11.13330.988209659
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_12.12103.988209683
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_56.3201.989352547
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_66.8648.1004135213
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_67.1132.1004135251
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_68.5686.1004135281
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_69.10834.1004135313
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_70.11789.1004135357
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_71.11145.1004135395
\nDeleted Oracle managed file +FRA\/SIDTEST\/flashback\/log_72.13516.1004135445
\nALTER SYSTEM SET db_recovery_file_dest_size='600G' SCOPE=BOTH;
\n<\/PRE><\/p>\n
cOMO PODEIS VER.. SUCIO PERO EFECTIVO <\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Sigue leyendo →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[565,60,567,921,42],"tags":[151,950],"class_list":["post-2003","post","type-post","status-publish","format-standard","hentry","category-11g-en","category-12c","category-12c-en","category-18c","category-asm","tag-flashback","tag-fra"],"_links":{"self":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/2003","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/comments?post=2003"}],"version-history":[{"count":5,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/2003\/revisions"}],"predecessor-version":[{"id":2008,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/2003\/revisions\/2008"}],"wp:attachment":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/media?parent=2003"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/categories?post=2003"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/tags?post=2003"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}