Home » RDBMS Server » Backup & Recovery » Redo Log changes not recovered in RMAN recovery after restoring control file (Oracle 10g R2 on Linux)
Redo Log changes not recovered in RMAN recovery after restoring control file [message #486189] Tue, 14 December 2010 03:53 Go to next message
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 #486212 is a reply to message #486189] Tue, 14 December 2010 05:19 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hello

Do you want me to rephrase the contents in the post in case it is not much clear?

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 Go to previous messageGo to next message
John Watson
Messages: 8501
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 Go to previous messageGo to next message
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 #486471 is a reply to message #486444] Wed, 15 December 2010 16:20 Go to previous messageGo to next message
John Watson
Messages: 8501
Registered: January 2010
Location: Global Village
Senior Member
Quote:
The changes from 181659 till 185759 were in redo logs which were lost
No they weren't.
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 Go to previous messageGo to next message
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

Re: Redo Log changes not recovered in RMAN recovery after restoring control file [message #486544 is a reply to message #486515] Thu, 16 December 2010 07:01 Go to previous messageGo to next message
John Watson
Messages: 8501
Registered: January 2010
Location: Global Village
Senior Member
Your own query shows that log sequence number 14 has changes 181659 to 185759, and it has been archived. Read your own qqeries, please.
This is a waste of time.
Re: Redo Log changes not recovered in RMAN recovery after restoring control file [message #486545 is a reply to message #486544] Thu, 16 December 2010 07:27 Go to previous message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Got it John!

Thanks for your patience!

Regards,
Chetanaz
Previous Topic: Need Help
Next Topic: Recovery from SQL
Goto Forum:
  


Current Time: Sun Mar 07 21:15:05 CST 2021