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 ?
.
.
.
6 comments:
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 ?
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
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
Nitin,
I have shown alert.log entries in my previous post.
Hemant
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
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
Post a Comment