14 February, 2012

Archived Logs after RESETLOGS

Following up on my previous post "Understanding RESETLOGS"

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> 
My current Database Incarnation (5) has a RESETLOGS_ID 775179971. But the Archived Logs of SEQUENCE#8 to SEQUENCE#10 are for the previous RESETLOGS_ID. What happens if I cause another Archive ?
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 ?

.
.
.

6 comments:

Narendra said...

Hemant,

I have to say your posts scare me about how little I know... :)
I am not sure what is the purpose of 2nd and 3rd RECOVER commands (I think they are not needed but...what do I know?). I had refer to documentation and reading here, I guess your first RECOVER statement tells oracle to do a complete recovery and hence does not allow to open database as all logs (that RECOVER requested) are not applied. Your last RECOVER statement tells oracle to do an incomplete recovery and hence you are allowed to open database subsequently. Is that any relevant ?

Nitin said...

Hi Hemant,

I think with resetlogs command, the online redolog files are cleared but sometime not archived but enters in the list of V$ARCHIVE_LOG(?).
V$ARCHIVE_LOG(NAME Archived log file name. If set to NULL, the log file was cleared before it was archived.)
but with which recovery command? alert log with full details would be helpful.
is this a factor?

Regards,
Nitin

Hemant K Chitale said...

Narendra,
In the first RECOVER I do specify USING BACKUP CONTROLFILE and I do explicitly CANCEL. Yet, only the fourth RECOVER where both are specified *together* works.
A User-Managed Recovery incomplete Recovey must specify UNTIL CANCEL in the RECOVER command.

The second and third attempts fail because the controlfile is already marked as inconsistent and the DBA must explicitly include a USING BACKUP CONTROLFILE.


RMAN recovery hides some of the complexity but it is good to practice recovery from the SQLPLUS command line once in a while.

Hemant

Hemant K Chitale said...

Nitin,
I have shown alert.log entries in my previous post.

Hemant

Darryl Griffiths said...

Hi Hemant,
Nice article.
Can you clarify: "A User-Managed Recovery incomplete Recovey must specify UNTIL CANCEL in the RECOVER command.", do you mean that you must specify "UNTIL" in the RECOVER command? i.e. "UNTIL " would also work?

Thanks,

Darryl

Hemant K Chitale said...

Darryl,
If you use RMAN for the RECOVER, you would precede the RECOVER with a SET UNTIL.
If you use SQLPlus for the RECOVER, you would have UNTIL as part of the RECOVER command.


UNTIL can be SEQUENCE#, TIME or CHANGE# (SCN)

Hemant K Chitale