How to delete the incarnations of an Oracle Database

In this article, I will talk about database incarnation and how to clean the old and obsoletes ones. It's not intended to show you how to clean the incarnations on the recovery catalog, but from the database itself! Let's start.

Sometimes, after performing several database recoveries or "open resetlogs", you can end up with so many incarnations like the example below:

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------------ ----------------- --------- ----------------------- --------- ------- ------------ ------------------ --------------------------
           1                 1 21-FEB-15                       0           PARENT     872244120                  0 NO
           2            234372 21-FEB-15                       1 21-FEB-15 PARENT     872245610                  1 NO
           3            234654 21-FEB-15                  234372 21-FEB-15 PARENT     872245666                  2 NO
           4            234936 21-FEB-15                  234654 21-FEB-15 PARENT     872245693                  3 NO
           5            235216 21-FEB-15                  234936 21-FEB-15 PARENT     872245720                  4 NO
           6            235456 21-FEB-15                  235216 21-FEB-15 PARENT     872245740                  5 NO
           7            235801 21-FEB-15                  235456 21-FEB-15 PARENT     872245759                  6 NO
           8            236041 21-FEB-15                  235801 21-FEB-15 PARENT     872245779                  7 NO
           9            236396 21-FEB-15                  236041 21-FEB-15 CURRENT    872245797                  8 YES

9 rows selected.

Another way to list the incarnations is through RMAN:

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL     1400024856       PARENT  1          21-FEB-15
2       2       ORCL     1400024856       PARENT  234372     21-FEB-15
3       3       ORCL     1400024856       PARENT  234654     21-FEB-15
4       4       ORCL     1400024856       PARENT  234936     21-FEB-15
5       5       ORCL     1400024856       PARENT  235216     21-FEB-15
6       6       ORCL     1400024856       PARENT  235456     21-FEB-15
7       7       ORCL     1400024856       PARENT  235801     21-FEB-15
8       8       ORCL     1400024856       PARENT  236041     21-FEB-15
9       9       ORCL     1400024856       CURRENT 236396     21-FEB-15

Having a lot incarnations is not really a problem, but if you are addicted for cleaning and organization like me, sometimes is good to put all this mess in trash.

So, where is this incarnation information kept? Answer: In the control file. So it's not so easy to delete or remove them, as it would be if it was inside any dictionary table.

In 11g Release 2, if you query the v$controlfile_record_section you will have the result like the below:

SQL> select rownum,t.* from v$controlfile_record_section t;

    ROWNUM TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------- ---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
         1 DATABASE                             316             1            1           0          0          0
         2 CKPT PROGRESS                       8180             4            0           0          0          0
         3 REDO THREAD                          256             1            1           0          0          0
         4 REDO LOG                              72             5            3           0          0         27
         5 DATAFILE                             520           100            4           0          0       2853
         6 FILENAME                             524          2275           11           0          0          0
         7 TABLESPACE                            68           100            5           0          0          5
         8 TEMPORARY FILENAME                    56           100            1           0          0          1
         9 RMAN CONFIGURATION                  1108            50            3           0          0          3
        10 LOG HISTORY                           56           292           16           1         16         16
        11 OFFLINE RANGE                        200           163            0           0          0          0
        12 ARCHIVED LOG                         584           280           39           1         39         39
        13 BACKUP SET                            40           409            6           1          6          6
        14 BACKUP PIECE                         736           200            6           1          6          6
        15 BACKUP DATAFILE                      200           245            7           1          7          7
        16 BACKUP REDOLOG                        76           215            2           1          2          2
        17 DATAFILE COPY                        736           200            0           0          0          0
        18 BACKUP CORRUPTION                     44           371            0           0          0          0
        19 COPY CORRUPTION                       40           409            0           0          0          0
        20 DELETED OBJECT                        20           818           24           1         24         24
        21 PROXY COPY                           928           211            0           0          0          0
        22 BACKUP SPFILE                        124           131            0           0          0          0
        23 DATABASE INCARNATION                  56           292            9           1          9          9
        24 FLASHBACK LOG                         84          2048            2           0          0          0
        25 RECOVERY DESTINATION                 180             1            1           0          0          0
        26 INSTANCE SPACE RESERVATION            28          1055            1           0          0          0
        27 REMOVABLE RECOVERY FILES              32          1000            5           0          0          0
        28 RMAN STATUS                          116           141           74           1         74         74
        29 THREAD INSTANCE NAME MAPPING          80             1            1           0          0          0
        30 MTTR                                 100             1            1           0          0          0
        31 DATAFILE HISTORY                     568            57            0           0          0          0
        32 STANDBY DATABASE MATRIX              400            31           31           0          0          0
        33 GUARANTEED RESTORE POINT             212          2048            0           0          0          0
        34 RESTORE POINT                        212          2083            0           0          0          0
        35 DATABASE BLOCK CORRUPTION             80          8384            0           0          0          0
        36 ACM OPERATION                        104            64            6           0          0          0
        37 FOREIGN ARCHIVED LOG                 604          1002            2           1          2          2

37 rows selected.

SQL>

The Incarnation information is in the line 23.

If you view the code DBMS_BACKUP_RESTORE, you can check that there is a constant binary_integer called RTYP_INCARNATION with that same value, 23. This means that the Incarnation information is inside SECTION 23 in control file (remember, here we are using 11gR2).

So, why I'm talking about this number 23? Because the first way I tried to clean was running the resetcfilesection passing the section 23 as parameter. But do not do that!

If you clean all the section 23 running the unsupported procedure "sys.dbms_backup_restore.resetcfilesection(23);", you will indeed clean all the incarnations, but also the current one. This will give you a lot of ORA-00600 and will crash your database (believe in me, I tested it).

So, the only way to clean it (if you are not an oracle engineer and don't know how to hex edit and dump the control file), is recreating the control file. Unfortunately, you will need to bring down your DB to use that approach.

First of all, make a backup of your current control file (both binary and to trace):

SQL> alter database backup controlfile to trace as '/tmp/control.txt';

Database altered.

SQL> alter database backup controlfile to '/tmp/control.ctl';

Database altered.

After that, shutdown and put your database in nomount mode:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  627732480 bytes
Fixed Size                  1346756 bytes
Variable Size             373293884 bytes
Database Buffers          247463936 bytes
Redo Buffers                5627904 bytes
SQL>

Now, get the contents of your trace control file and recreate your control file using the NORESETLOGS case:

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
2        MAXLOGFILES 5
3        MAXLOGMEMBERS 5
4        MAXDATAFILES 100
5        MAXINSTANCES 1
6        MAXLOGHISTORY 292
7    LOGFILE
8      GROUP 1 '/u01/app/oracle/orcl/oradata/redo01a.log'  SIZE 100M BLOCKSIZE 512,
9      GROUP 2 '/u01/app/oracle/orcl/oradata/redo02a.log'  SIZE 100M BLOCKSIZE 512,
10     GROUP 3 '/u01/app/oracle/orcl/oradata/redo03a.log'  SIZE 100M BLOCKSIZE 512
11   -- STANDBY LOGFILE
12   DATAFILE
13     '/u01/app/oracle/orcl/oradata/system01.dbf',
14     '/u01/app/oracle/orcl/oradata/sysaux01.dbf',
15     '/u01/app/oracle/orcl/oradata/undotbs01.dbf',
16     '/u01/app/oracle/orcl/oradata/users01.dbf'
17   CHARACTER SET AL32UTF8
18   ; 

Control file created.

SQL>

Good. Note that no recovery is needed as you shutdown your DB gracefully:

SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

So finally, lets open it again:

SQL> alter database open;

Database altered.

SQL>

Don't forget to also recreate any temporary tablespace datafile, set up the default RMAN configurations or to re-catalog any other information that is kept in control file.

Checking now the incarnations, we can see that we have only the current one:

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------------ ----------------- --------- ----------------------- --------- ------- ------------ ------------------ --------------------------
           1            236396 21-FEB-15                  236041 21-FEB-15 CURRENT    872245797                  0 NO

SQL>

Good, now you have your database cleaner.

Have you enjoyed? Please leave a comment or give a 👍!

Leave a Reply

Your email address will not be published.