Following up on my previous post "Understanding RESETLOGS"
I begin with SEQUENCE#8 and SEQUENCE#9 as ArchivedLogs :
I begin with SEQUENCE#8 and SEQUENCE#9 as ArchivedLogs :
[oracle@linux64 2012_02_13]$ pwd /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13 [oracle@linux64 2012_02_13]$ ls -l total 4104 -rw-rw---- 1 oracle oracle 4196864 Feb 13 23:39 o1_mf_1_8_7mlcok0g_.arc [oracle@linux64 2012_02_13]$ sqlplus '/ as sysdba' SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 13 23:41:59 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter system archive log current; System altered. SQL> !ls -ltr total 4516 -rw-rw---- 1 oracle oracle 4196864 Feb 13 23:39 o1_mf_1_8_7mlcok0g_.arc -rw-rw---- 1 oracle oracle 416768 Feb 13 23:42 o1_mf_1_9_7mlctdf6_.arc SQL>
Then I run an INCOMPLETE RECOVERy -- with the Online Redo Logs *still* present. I find the SEQUENCE#8 and SEQUENCE#9 are re-archived !!
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 281021528 bytes
Database Buffers 25165824 bytes
Redo Buffers 6336512 bytes
Database mounted.
SQL> recover database using backup controlfile;
ORA-00279: change 5120649 generated at 02/13/2012 23:44:13 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_1
0_%u_.arc
ORA-00280: change 5120649 for thread 1 is in sequence #10
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/orcl/system01.dbf'
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 5120649 generated at 02/13/2012 23:44:13 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_1
0_%u_.arc
ORA-00280: change 5120649 for thread 1 is in sequence #10
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL> !ls -l
total 9332
-rw-rw---- 1 oracle oracle 303104 Feb 13 23:46 o1_mf_1_10_7mld23vp_.arc
-rw-rw---- 1 oracle oracle 4196864 Feb 13 23:39 o1_mf_1_8_7mlcok0g_.arc
-rw-rw---- 1 oracle oracle 4196864 Feb 13 23:46 o1_mf_1_8_7mld23yr_.arc
-rw-rw---- 1 oracle oracle 416768 Feb 13 23:42 o1_mf_1_9_7mlctdf6_.arc
-rw-rw---- 1 oracle oracle 416768 Feb 13 23:46 o1_mf_1_9_7mld241b_.arc
SQL>
I query the V$ARCHIVED_LOG view :
SQL> l
1 select sequence#, resetlogs_id, to_char(first_time,'DD-MON-RR HH24:MI:SS') , to_char(completion_time,'DD-MON-RR HH24:MI:SS')
2 from v$archived_log
3 where sequence# > 7
4 and sequence# < 15
5* order by 1
SQL> /
SEQUENCE# RESETLOGS_ID TO_CHAR(FIRST_TIME,'DD-MON- TO_CHAR(COMPLETION_TIME,'DD
---------- ------------ --------------------------- ---------------------------
8 771421939 12-FEB-12 22:33:46 13-FEB-12 23:39:29
8 771421939 12-FEB-12 22:33:46 13-FEB-12 23:46:12
9 771421939 13-FEB-12 23:39:28 13-FEB-12 23:42:04
9 771421939 13-FEB-12 23:39:28 13-FEB-12 23:46:12
10 771421939 13-FEB-12 23:42:04 13-FEB-12 23:46:11
SQL>
So, both the copies of the SEQUENCE#8 and SEQUENCE#9 ArchiveLogs have the same RESETLOGS_ID. However, they were created at different times.
What information do I have about Database Incarnations ? 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 4955792 01-JAN-12 754488 30-OCT-09
PARENT 771419578 2 NO
4 4957614 01-JAN-12 4955792 01-JAN-12
PARENT 771421939 3 NO
5 5120650 13-FEB-12 4957614 01-JAN-12
CURRENT 775179971 4 NO
SQL>
SQL> alter system archive log current;
System altered.
SQL> select sequence#, resetlogs_id, to_char(first_time,'DD-MON-RR HH24:MI:SS') , to_char(completion_time,'DD-MON-RR HH24:MI:SS')
2 from v$archived_log
3 where sequence# > 7
4 and sequence# < 15
5 order by 1;
SEQUENCE# RESETLOGS_ID TO_CHAR(FIRST_TIME,'DD-MON- TO_CHAR(COMPLETION_TIME,'DD
---------- ------------ --------------------------- ---------------------------
8 771421939 12-FEB-12 22:33:46 13-FEB-12 23:39:29
8 771421939 12-FEB-12 22:33:46 13-FEB-12 23:46:12
9 771421939 13-FEB-12 23:39:28 13-FEB-12 23:42:04
9 771421939 13-FEB-12 23:39:28 13-FEB-12 23:46:12
10 771421939 13-FEB-12 23:42:04 13-FEB-12 23:46:11
SQL>
SQL> l
1 select sequence#, resetlogs_id, to_char(first_time,'DD-MON-RR HH24:MI:SS') , to_char(completion_time,'DD-MON-RR HH24:MI:SS')
2 from v$archived_log
3 where sequence# > 0
4 and sequence# < 4
5* order by 1,2
SQL> /
SEQUENCE# RESETLOGS_ID TO_CHAR(FIRST_TIME,'DD-MON- TO_CHAR(COMPLETION_TIME,'DD
---------- ------------ --------------------------- ---------------------------
1 771419578 01-JAN-12 11:12:58 01-JAN-12 11:50:00
1 771419578 01-JAN-12 11:12:58 01-JAN-12 11:52:19
1 771419578 01-JAN-12 11:12:58 01-JAN-12 11:25:14
1 771419578 01-JAN-12 11:12:58 01-JAN-12 11:48:44
1 771421939 01-JAN-12 11:52:19 14-JAN-12 23:48:56
1 775179971 13-FEB-12 23:46:11 13-FEB-12 23:57:14
2 771419578 01-JAN-12 11:25:13 01-JAN-12 11:52:19
2 771419578 01-JAN-12 11:25:13 01-JAN-12 11:26:55
2 771419578 01-JAN-12 11:25:13 01-JAN-12 11:50:00
2 771419578 01-JAN-12 11:25:13 01-JAN-12 11:48:44
2 771421939 14-JAN-12 23:48:55 15-JAN-12 22:47:38
3 771419578 01-JAN-12 11:26:55 01-JAN-12 11:52:19
3 771419578 01-JAN-12 11:26:55 01-JAN-12 11:48:44
3 771419578 01-JAN-12 11:26:55 01-JAN-12 11:50:00
3 771421939 15-JAN-12 22:47:38 15-JAN-12 23:14:40
15 rows selected.
SQL>
Now I can see the SEQUENCE#1 file for RESETLOGS_ID 775179971 having been archived today, 13-Feb-12 at 23:57. SEQUENCE#1 now has 6 entries ! Bottom Line : If you are testing Backup and Recovery scenarios and/or are frequently doing RESETLOGS on the same database, be very particular when viewing information about ArchivedLogs. Most DBAs only query for SEQUENCE# in V$ARCHIVED_LOG.
Today's Question : Why did I issue a RECOVER DATABASE ... 4 times ? What is the difference between the different commands ?
.
.
.