| Redo Log changes not recovered in RMAN recovery after restoring control file [message #486189] |
Tue, 14 December 2010 03:53  |
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
|
Senior Member |
|
|
Hello,
I am testing a recovery scenario where all datafiles are lost
Following is the position of scn and changes saved in logfiles and archivelogfiles before database crash
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
SQL>
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 22 52428800 2 YES INACTIVE 206121 14-DEC-10
2 1 23 52428800 2 NO CURRENT 206130 14-DEC-10
3 1 21 52428800 2 YES INACTIVE 205897 14-DEC-10
SQL> select current_scn,CHECKPOINT_CHANGE#,ARCHIVE_CHANGE#,CONTROLFILE_SEQUENCE#,CONTROLFILE_CHANGE#,ARCHIVELOG_CHANGE# from v$database;
CURRENT_SCN CHECKPOINT_CHANGE# ARCHIVE_CHANGE# CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# ARCHIVELOG_CHANGE#
----------- ------------------ --------------- --------------------- ------------------- ------------------
206159 206130 206127 266 206146 206130
Now after I restore the control file and queried the database after mounting but 'before database restore and recovery', following is the status:
SQL> select current_scn,CHECKPOINT_CHANGE#,ARCHIVE_CHANGE#,CONTROLFILE_SEQUENCE#,CONTROLFILE_CHANGE#,ARCHIVELOG_CHANGE# from v$database;
CURRENT_SCN CHECKPOINT_CHANGE# ARCHIVE_CHANGE# CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# ARCHIVELOG_CHANGE#
----------- ------------------ --------------- --------------------- ------------------- ------------------
0 206121 206118 233 206126 206121
The above results denote that I can restore and recover data only upto change
206120 i.e. select ARCHIVELOG_CHANGE# - 1 from v$database;
However if we see results in step 1 which denotes
Highest change saved in archived_log (archived = 'YES') = 206121
and
Highest change saved in Redo Log (archived = 'NO') = 206130
My question is during restoring of control files I have all the redolog files then why I am not getting results for following query as 206130-1 instead of 206121 - 1?
select ARCHIVELOG_CHANGE# - 1 from v$database;
This indicates that I can recover only upto change 20620 and not upto 20629
Thus changes in redo log are not useful to me
Is that because I have restored controlfile from backup?
My understanding was redo logs will be used for recovery but after recovery I will need to open the database in 'resetlogs' thus recreating redo logs (since I have restored controlfile)
Could you please suggest on this?
Thanks and Regards,
Chetanaz
|
|
|
|
|
|
| Re: Redo Log changes not recovered in RMAN recovery after restoring control file [message #486324 is a reply to message #486212] |
Tue, 14 December 2010 18:28  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
OK, as no-one else has replied, I'll have a go. First, Chetanaz, you will not get any help if you don't try to help yourself. You say Quote:The above results denote that I can restore and recover data only upto change 206120 Test it! Don't just assume that complete recovery is impossible.
Now for your mistakes.
First, you say that you were testing loss of all datafiles. OK. So why did you restore the controlfile? You should NEVER restore the controlfile unless there is no alternative. It makes life much harder. If you have a copy of the current controlfile anywhere, put it back.
Second, the information on SCNs you are giving is from a restored controlfile: it doesn't know about any changes made after it was backed up. But RMAN is intelligent enough to know that there may have been more changes made: it will check the headers of all available files (such as the online logs, which I sincerely hope you have NOT done anything to) and should therefore identify the current SCN, and recover from the online logs up to that.
|
|
|
|
| Re: Redo Log changes not recovered in RMAN recovery after restoring control file [message #486444 is a reply to message #486189] |
Wed, 15 December 2010 10:28 |
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
|
Senior Member |
|
|
Hello John
Many thanks for the reply
I agree my mistake. I should have written test for 'loss of datafile ,controlfile and logfiles'
I agree that RMAN smartly recognizes changes in redo log when available.
My question is if redo log are not available we don't get exact information on what point we will get data recovered upto
Please refer following example
The example is lengthy but I am trying to cover a complete scenario here
insert into t values(1);
commit;
alter system switch logfile; -- creates sequence 10
insert into t values(1);
commit;
alter system switch logfile; -- creates sequence 11
Now I take backup which has 4 pieces
piece 1) has archive sequence 10,11, 12
piece 2) datafiles backup
piece 3) controlfile and spfile backup
piece 4) archivelog sequence 13
after this
insert into t values(4);
commit;
alter system switch logfile; -- creates sequence 14
insert into t values(5);
commit;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 13 52428800 2 YES INACTIVE 181650 15-DEC-10
2 1 14 52428800 2 YES ACTIVE 181659 15-DEC-10
3 1 15 52428800 2 NO CURRENT 185760 15-DEC-10
SQL> select name from v$archived_log;
NAME
------------------------------------------------------------------------
/u04/oradata/db6fra/DB6/archivelog/2010_12_15/o1_mf_1_13_6jkb2tlr_.arc
/u04/oradata/db6fra/DB6/archivelog/2010_12_15/o1_mf_1_14_6jkm9tbz_.arc
SQL> select current_scn,archive_change#,ARCHIVELOG_CHANGE# from v$database;
CURRENT_SCN ARCHIVE_CHANGE# ARCHIVELOG_CHANGE#
----------- --------------- ------------------
185766 181656 185760
Thus if I delete all the files (even redo) except available archivelog files I shall get data avaialable in archive sequence 14 i.e upto scn 181659 - 1. Right?
now I delete redo files, controlfiles, datafiles
I restore the controlfile
query the database
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 13 52428800 2 NO CURRENT 181650 15-DEC-10
3 1 12 52428800 2 YES INACTIVE 181596 15-DEC-10
2 1 11 52428800 2 YES INACTIVE 181580 15-DEC-10
SQL> select current_scn,archive_change#,ARCHIVELOG_CHANGE# from v$database;
CURRENT_SCN ARCHIVE_CHANGE# ARCHIVELOG_CHANGE#
----------- --------------- ------------------
0 181646 181650
I restore and recover the database
run{
restore database;
recover database;
}
and I get the error
RMAN-06054: media recovery requesting unknown log: thread 1 seq 15 lowscn 185760
This is fine as we don't have redo logs available with us.
I again restore and recover the database upto scn 185760 -1
RMAN> run{
2> set until scn 185759;
3> restore database;
4> recover database;
5> }
and it succeeds!! Confusing!
The changes from 181659 till 185759 were in redo logs which were lost and still the database recovery succeeds 'until scn 185759'
This is confusing me.
Thanks and Regards,
Chetanaz
|
|
|
|
|
|
| Re: Redo Log changes not recovered in RMAN recovery after restoring control file [message #486515 is a reply to message #486189] |
Thu, 16 December 2010 03:03 |
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
|
Senior Member |
|
|
Hello John
I wrote
Quote:
The changes from 181659 till 185759 were in redo logs which were lost
because the redo containing the changes weren't archived till the point I deleted the redo log files
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 13 52428800 2 YES INACTIVE 181650 15-DEC-10
2 1 14 52428800 2 YES ACTIVE 181659 15-DEC-10
3 1 15 52428800 2 NO CURRENT 185760 15-DEC-10
Also the changes could not be in datafiles as the backup which I restored was taken before these changes.
Thanks and Regards,
Chetanaz
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
