Why is it then...
Russ Cannon, February 14, 2013 - 4:30 pm UTC
that sometimes we can open a database requiring media recovery by simply running "recover database" or "recover datafile #" without restoring? We may have not restored from backup, but because the database somehow "crashed" we had to perform media recovery no restore required.
Kind regards,
Russ
February 14, 2013 - 5:31 pm UTC
One reason could be because the file that was on the file system that needed recovery wasn't damaged - it just became unavailable to the database for whatever reason (while the rest of the database advanced forward). You fixed that reason, file becomes available but is in need of recovery.
This can also happen if a datafile is in "backup mode" (old old old way of doing hot backups) for example as well. The file online is in need of media recovery because it was in backup mode - but itself was "intact" - not damaged (it just needs redo applied to it - to make it consistent with the rest of the database)...
so how it works
Sunil Kumar, February 14, 2013 - 5:59 pm UTC
Dear Sir,
correct me if I am wrong in the following senario:
After mount:
1. All the datafiles in the control file has crosschecked, if a datafile is read/writeable or unavailable, media recovery error display for that file.
2. If all the files are available then it check for redo thread for instance, suppose redo thread in open state, it means instance recovery is required.
3. In case of point 2, Oracle check checkpoint SCN in the controlfile with correspondence datafile, if they are out of sync (they are !) then it start applying redo change vectors from online redolog file from last checkpoint position onwards.
Regards
Sunil Kumar
February 15, 2013 - 9:17 am UTC
1) well, if the datafile is unavailable - it will be taken offline, affecting the tablespace it is part of. when you "fix it" and try to bring it online, we'll verify that it is "caught up" to the rest of the database and if not - ask you to recover it using media recovery.
2) if we didn't shutdown normal for any reason, an instance recovery is required, yes.
3) yes
Still few doubts
A reader, February 18, 2013 - 10:25 pm UTC
Dear Sir,
In following case how recovery will work:
Database was down with shut abort and after that a datafile has been replaced from old one (from backup),
In this case how recovery process will work ?
Will first it try to perform instance recovery ? and when stuck it will go for media recovery ?
or there is a check perform for every datafile even before start any kind of recovery (e.g. instance or media) weather it is current or not ?
Regards
Sunil Kumar
February 25, 2013 - 8:04 am UTC
it'll do a check and see that the database needs more recovery than it can do and tell you to perform that recovery (or you can offline that datafile, and then just do instance recovery and open up everything else)
ops$tkyte%ORA11GR2> connect / as sysdba
Connected.
sys%ORA11GR2> shutdown abort;
ORACLE instance shut down.
sys%ORA11GR2> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
.... [ora11gr2@localhost ~]$ cp /tmp/users.dbf /home/ora11gr2/app/ora11gr2/oradata/ora11gr2/
.....
[tkyte@localhost ~]$ sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 25 09:02:21 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
idle> startup
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1346140 bytes
Variable Size 364905892 bytes
Database Buffers 163577856 bytes
Redo Buffers 5832704 bytes
Database mounted.
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4:
'/home/ora11gr2/app/ora11gr2/oradata/ora11gr2/users.dbf'
idle> recover database;
ORA-00279: change 114450695 generated at 02/25/2013 08:55:48 needed for thread
1
ORA-00289: suggestion :
/home/ora11gr2/app/ora11gr2/fast_recovery_area/ORA11GR2/archivelog/2013_02_25/o
1_mf_1_4802_8lpvmq20_.arc
ORA-00280: change 114450695 for thread 1 is in sequence #4802
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 114454065 generated at 02/25/2013 08:57:34 needed for thread
1
ORA-00289: suggestion :
/home/ora11gr2/app/ora11gr2/fast_recovery_area/ORA11GR2/archivelog/2013_02_25/o
1_mf_1_4803_8lpvmq22_.arc
ORA-00280: change 114454065 for thread 1 is in sequence #4803
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 114458088 generated at 02/25/2013 08:57:35 needed for thread
1
ORA-00289: suggestion :
/home/ora11gr2/app/ora11gr2/fast_recovery_area/ORA11GR2/archivelog/2013_02_25/o
1_mf_1_4804_8lpvmr86_.arc
ORA-00280: change 114458088 for thread 1 is in sequence #4804
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 114462802 generated at 02/25/2013 09:00:24 needed for thread
1
ORA-00289: suggestion :
/home/ora11gr2/app/ora11gr2/fast_recovery_area/ORA11GR2/archivelog/2013_02_25/o
1_mf_1_4805_8lpvmt07_.arc
ORA-00280: change 114462802 for thread 1 is in sequence #4805
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 114467093 generated at 02/25/2013 09:00:25 needed for thread
1
ORA-00289: suggestion :
/home/ora11gr2/app/ora11gr2/fast_recovery_area/ORA11GR2/archivelog/2013_02_25/o
1_mf_1_4806_8lpvmvdy_.arc
ORA-00280: change 114467093 for thread 1 is in sequence #4806
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
idle> alter database open;
Database altered.
Links are not working
saul, October 30, 2017 - 7:15 pm UTC
Hi i´m a new dba, I have 6 mounts of experience and i have a lot of questions about how instance recovery works, i came here and i saw that the links are not working
October 31, 2017 - 1:52 pm UTC
Thanks, I've updated the instance recovery link.