Friday, March 13, 2020

Remember to delete archivelogs from obsolete incarnations after a flashback database operation



I have recently been supporting a customer by setting guaranteed restore points and using these to flash the database back on their signal, typically when they need to redeploy some code for their application.

It's easy to forget that every time you open the database with the RESETLOG options, you create another incarnation of your database.
Here is what my flash recovery area looked like:
cd /fra/SALESDB/archivelogs
du -sh *
300M    2020_01_30
36G     2020_02_03
248M    2020_02_04
1.5G    2020_02_05
273M    2020_02_06
284M    2020_02_07
332M    2020_02_08
3.7G    2020_02_09
510G    2020_02_11
15G     2020_02_12
1.1G    2020_02_13
386M    2020_02_14
237M    2020_02_15
3.7G    2020_02_16
14G     2020_02_17
523G    2020_02_18
1.5G    2020_02_19
208M    2020_02_20
213M    2020_02_21
239M    2020_02_22
3.6G    2020_02_23
217M    2020_02_24
293M    2020_02_25
1.5G    2020_02_26
258M    2020_02_27
261M    2020_02_28
296M    2020_02_29
3.6G    2020_03_01
507G    2020_03_02
216M    2020_03_03
13G     2020_03_04
214M    2020_03_05
211M    2020_03_06
237M    2020_03_07
3.6G    2020_03_08
288M    2020_03_09
244M    2020_03_10
1.6G    2020_03_11
16G     2020_03_12
504G    2020_03_13


The latest incarnation was created on the 12.03.2020:
RMAN> list incarnation of database;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       SALESDB 1626528189       PARENT  270012427233 30.01.2020 11:39:03
2       2       SALESDB 1626528189       PARENT  270012433324 30.01.2020 11:51:46
3       3       SALESDB 1626528189       PARENT  270020068677 11.02.2020 11:00:20
4       4       SALESDB 1626528189       PARENT  270020175869 13.02.2020 12:02:52
5       5       SALESDB 1626528189       PARENT  270020238995 18.02.2020 10:50:25
6       6       SALESDB 1626528189       PARENT  270020500540 02.03.2020 13:51:53
7       7       SALESDB 1626528189       CURRENT 270020646622 12.03.2020 14:18:33

This situation caused the flash recovery area to fill, since Oracle wasn't able to delete archivelogs from the previous incarnation.

I solved this by deleting archivelogs from before the last incarnation was created.

First, list the logs you want to delete:
rman target / nocatalog log=list_archlogs.txt'
RMAN> list archivelog all completed before "to_date('12.03.2020 14:18:33')";

When I check the file, these were all older logs, residing in folders dated before the last incarnation was created.

I then deleted them as follows:
delete archivelog all completed before "to_date('12.03.2020 14:18:33')";

The size of the flash recovery area has now dropped to 17% and the file listing of /fra/SALESDB/archivelogs now shows empty folders:
4.0K    2020_01_30
8.0K    2020_02_03
4.0K    2020_02_04
4.0K    2020_02_05
4.0K    2020_02_06
4.0K    2020_02_07
4.0K    2020_02_08
16K     2020_02_09
48K     2020_02_11
4.0K    2020_02_12
4.0K    2020_02_13
4.0K    2020_02_14
4.0K    2020_02_15
4.0K    2020_02_16
4.0K    2020_02_17
52K     2020_02_18
4.0K    2020_02_19
4.0K    2020_02_20
4.0K    2020_02_21
4.0K    2020_02_22
4.0K    2020_02_23
4.0K    2020_02_24
4.0K    2020_02_25
4.0K    2020_02_26
4.0K    2020_02_27
4.0K    2020_02_28
4.0K    2020_02_29
4.0K    2020_03_01
52K     2020_03_02
4.0K    2020_03_03
4.0K    2020_03_04
4.0K    2020_03_05
4.0K    2020_03_06
4.0K    2020_03_07
4.0K    2020_03_08
4.0K    2020_03_09
4.0K    2020_03_10
4.0K    2020_03_11
15G     2020_03_12
504G    2020_03_13

No comments:

Post a Comment