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 👍!
