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

How to flash the database back to a guaranteed restore point

After a week of testing their new release, my customer wanted their database reset to the state it was previously in.
Earlier this week, I created a restore point of type GUARANTEED FLASHBACK.

Here is how I restored a database using flashback database technology. It was, as expected, very quick. Only a few seconds to flash back 14 GB of changes.

Connect to Recovery Manager:

oracle@myserver:[TESTDB01]# rman target / catalog uid/pwd@rmancat

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 20 16:06:44 2014

connected to target database: TESTDB01 (DBID=411134280, not open)
connected to recovery catalog database

RMAN> list restore point all;

SCN              RSP Time  Type          Time         Name
---------------- --------- ----------   ---------     ----
153050263689                GUARANTEED  17-NOV-14     AKSEPT_TEST_START

Start the flashback operation:

RMAN> FLASHBACK DATABASE TO RESTORE POINT AKSEPT_TEST_START;
Starting flashback at 20-NOV-14
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=585 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 6.3.0.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=683 device type=DISK

starting media recovery

archived log for thread 1 with sequence 4651 is already on disk as file /u04/fast_recovery_area/TESTDB01/archivelog/2014_11_17/o1_mf_1_4651_b6m91zog_.arc
media recovery complete, elapsed time: 00:00:01
Finished flashback at 20-NOV-14 

RMAN> ALTER DATABASE OPEN RESETLOGS;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> exit

-- all done --

From alert.log:

Completed: ALTER DATABASE   MOUNT
Thu Nov 20 16:10:37 2014
alter database recover datafile list clear
Completed: alter database recover datafile list clear
RMAN flashback database to before scn 153050263690 in incarnation 2
Flashback Restore Start
Thu Nov 20 16:12:11 2014
Flashback Restore Complete
Flashback Media Recovery Start
started logmerger process
Parallel Media Recovery started with 8 slaves
Thu Nov 20 16:12:22 2014
Flashback Media Recovery Log /u04/fast_recovery_area/TESTDB01/archivelog/2014_11_17/o1_mf_1_4651_b6m91zog_.arc
Thu Nov 20 16:12:22 2014
Incomplete Recovery applied until change 153050263690 time 11/17/2014 07:53:33
Flashback Media Recovery Complete
Completed: RMAN flashback database to before scn 153050263690 in incarnation 2
Thu Nov 20 16:13:29 2014
alter database open resetlogs
RESETLOGS after incomplete recovery UNTIL CHANGE 153050263690
Archived Log entry 4827 added for thread 1 sequence 4826 ID 0x18811648 dest 1:
Archived Log entry 4828 added for thread 1 sequence 4823 ID 0x18811648 dest 1:
Archived Log entry 4829 added for thread 1 sequence 4822 ID 0x18811648 dest 1:
Archived Log entry 4830 added for thread 1 sequence 4827 ID 0x18811648 dest 1:
Archived Log entry 4831 added for thread 1 sequence 4824 ID 0x18811648 dest 1:
Archived Log entry 4832 added for thread 1 sequence 4825 ID 0x18811648 dest 1:
Clearing online redo logfile 1 /u03/oradata/TESTDB01/redo01.log
Clearing online log 1 of thread 1 sequence number 4826
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u03/oradata/TESTDB01/redo02.log
Clearing online log 2 of thread 1 sequence number 4823
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u03/oradata/TESTDB01/redo03.log
Clearing online log 3 of thread 1 sequence number 4822
Thu Nov 20 16:13:41 2014
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 /u03/oradata/TESTDB01/redo04.log
Clearing online log 4 of thread 1 sequence number 4827
Clearing online redo logfile 4 complete
Clearing online redo logfile 5 /u03/oradata/TESTDB01/redo05.log
Clearing online log 5 of thread 1 sequence number 4824
Clearing online redo logfile 5 complete
Clearing online redo logfile 6 /u03/oradata/TESTDB01/redo06.log
Clearing online log 6 of thread 1 sequence number 4825
Thu Nov 20 16:13:52 2014
Clearing online redo logfile 6 complete
Resetting resetlogs activation ID 411113032 (0x18811648)
Online log /u03/oradata/TESTDB01/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u03/oradata/TESTDB01/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u03/oradata/TESTDB01/redo03.log: Thread 1 Group 3 was previously cleared
Online log /u03/oradata/TESTDB01/redo04.log: Thread 1 Group 4 was previously cleared
Online log /u03/oradata/TESTDB01/redo05.log: Thread 1 Group 5 was previously cleared
Online log /u03/oradata/TESTDB01/redo06.log: Thread 1 Group 6 was previously cleared
Thu Nov 20 16:13:52 2014
Setting recovery target incarnation to 3

How to check the database's flashback settings

I normally use the following script to gather the most important facts about my flashback settings and readyness for a potential flashback database operation:

alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';
set linesize 300
set trimspool on
col name format a35
col time format a35
col guaranteed format a10
col "oldest flback SCN" format 9999999999999
col SCN format 9999999999999
set pagesize 200
spool chk_flashb.log
prompt =================================================
Prompt give me the
prompt * estimated flashback log size
prompt * retention target
prompt * current accumulated size of all flashback logs
prompt =================================================
select estimated_flashback_size/1024/1024 "Estimated Flbacklog Size mb",
       retention_target/60                "Hours of flback logs",
       flashback_size/1024/1024           "Current Flbacklog Size mb"
from v$flashback_database_log
/

prompt ===============================================
Prompt How far back can the database be flashed back?
prompt ===============================================
select oldest_flashback_scn  "oldest flback SCN",
       oldest_flashback_time "oldest flback time"
from v$flashback_database_log
/

prompt =================================================
prompt show the restore points created the last 2 weeks
prompt =================================================
SELECT NAME,
       SCN,
       TIME,
       DATABASE_INCARNATION#,
       GUARANTEE_FLASHBACK_DATABASE "guaranteed",
       STORAGE_SIZE
FROM V$RESTORE_POINT
where time >= SYSDATE-14;
Prompt ===================================
prompt Show the flash recovery area usage
Prompt ===================================
select * from v$flash_recovery_area_usage;

Prompt ======================================
prompt I/O information for flashback logging
Prompt ======================================
select begin_time "Begin time",
        end_time "End time",
        round(flashback_data/1024/1024) "MB of flbk data written",
        round(redo_data/1024/1024)      "MB of redo data"
from   v$flashback_database_stat
order by begin_time asc;
exit