in Oracle

RMAN — 7 : Recovery Through RESETLOGS — how are the ArchiveLogs identified ?

When Oracle introduced “official” support for Recovery through RESETLOGS in 10g, it introduced the “%r” component of the log_archive_format parameter. (This was not present in 9.2)

Therefore, in 10.2, we religiously included “%r” in the log_archive_format parameter.

However, if you use the FRA via USE_DB_RECOVERY_FILE_DEST, the resetlogs_id is not present and Oracle uses OMF naming rules for the archivelogs.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 154
Next log sequence to archive 156
Current log sequence 156
SQL> show parameter log_archive_format

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL> select name from v$archived_log where first_time > trunc(sysdate);

NAME
--------------------------------------------------------------------------------
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_152_bwjmnzt5_.arc
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_153_bwjmo4yp_.arc
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_154_bwjmoh29_.arc
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_155_bwjmxjyb_.arc

SQL>

Thus, you can see in the above listing the archivelog filenames, the RESETLOGS_ID is *not* part of the archivelog filename. (THREAD# and SEQUENCE# do still appear)

How, then, does Oracle (or do we ??) distinguish between archivelogs from one resetlogs and another ? The archivelog sequence numbers do get reset to 1.
What matters is that RESETLOGS_ID, RESETLOGS_CHANGE# and RESETLOGS_TIME are also incorporated into V$ARCHIVED_LOG.

SQL> select * from v$database_incarnation order by incarnation#;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
1 1 13-AUG-09 0
PARENT 694825248 0 NO

2 754488 30-OCT-09 1 13-AUG-09
PARENT 701609923 1 NO

3 14082620 04-JUL-15 754488 30-OCT-09
CURRENT 884179148 2 NO


SQL> select resetlogs_id, count(*) from v$archived_log group by resetlogs_id;

RESETLOGS_ID COUNT(*)
------------ ----------
884179148 153

SQL>

My database’s current Incarnation is 3 and all the archivelogs are for this Incarnation. What if I were to do a RESETLOGS and go to a new Incarnation ?

First, I generate some more archivelogs in the current incarnation.

SQL> create table hemant.test_recovery_thru (id number);

Table created.

SQL> insert into hemant.test_recovery_thru select rownum from dual connect by level < 100001;

100000 rows created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 156
Next log sequence to archive 158
Current log sequence 158
SQL>

Then I shutdown the database and do a RESETLOGS on OPEN.

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 159
Next log sequence to archive 161
Current log sequence 161
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL>
SQL> select * from v$database_incarnation order by 1;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
1 1 13-AUG-09 0
PARENT 694825248 0 NO

2 754488 30-OCT-09 1 13-AUG-09
PARENT 701609923 1 NO

3 14082620 04-JUL-15 754488 30-OCT-09
PARENT 884179148 2 NO


INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
4 14184835 10-AUG-15 14082620 04-JUL-15
CURRENT 887387582 3 NO


SQL>

I now have a new incarnation (4) created today. I next generate a couple of archivelogs and check the archivelogs

SQL> alter system archive log current;

System altered.

SQL> alter system archivelog current;
alter system archivelog current
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


SQL> alter system archive log current;

System altered.

SQL> select resetlogs_id, sequence#
2 from v$archived_log
3 where first_time > trunc(sysdate)
4 order by 1,2;

RESETLOGS_ID SEQUENCE#
------------ ----------
884179148 152
884179148 153
884179148 154
884179148 155
884179148 156
884179148 157
884179148 158
884179148 159
884179148 160
884179148 161
887387582 1
887387582 2

12 rows selected.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL>
SQL> select resetlogs_id, name from v$archived_log where first_time > trunc(sysdate) order by first_time;

RESETLOGS_ID
------------
NAME
--------------------------------------------------------------------------------
884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_152_bwjmnzt5_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_153_bwjmo4yp_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_154_bwjmoh29_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_155_bwjmxjyb_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_156_bwjr2nmj_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_157_bwjr2s1s_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_158_bwjr9pg5_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_159_bwjrb06z_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_160_bwjrb582_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_161_bwjrfz1j_.arc

887387582
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_1_bwjrhogp_.arc

887387582
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_2_bwjrj0gf_.arc


12 rows selected.

SQL>

Notice the first 10 archivelogs are from the RESETLOGS_ID corresponding to Incarnation 3. The Thread# and SEQUENCE# do appear to be part of the file names. But NOT the RESETLOGS_ID.
The last two files now have SEQUENCE# 1 and 2 but no indication of the RESETLOGS_ID. The RESETLOGS_ID is in V$ARCHIVED_LOG, not in the actual filename.

.
.
.