Search My Oracle Blog

Custom Search

10 August, 2015

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.

.
.
.



1 comment:

Foued said...

Hemant, nice article as usual. Thank you,
Foued

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com