Hi Friends,
Hope all of you doing well, here I have one more live scenario (Mainly for those who are new in DBA world and wanted to know what kind of issue came in PRD).
Yesterday I was doing one Database refresh activity and I came across this issue where while recovering database it was asking for archive file which was missing.
generally in this case, if you don’t have archive file and any how you want to open database i.e incomplete recovery then please follow the below steps.
While doing incomplete recovery we first find the which is current log file in DB.
Then try to apply those log file when it is asking for specific archive file.
In this scenario I try to apply the log file which was having status “CURRENT” but not luck.
Then I apply alternatively other log file as well to try luck and how wonder it took one of log file and recovery went successfully.
Luckily I copied those logs for all of you guys and , please find same one as below.
SQL> @control_CRP.sql.orig_noz ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started.
Total System Global Area 1.3095E+10 bytes Fixed Size 2231848 bytes Variable Size 4362076632 bytes Database Buffers 8724152320 bytes Redo Buffers 6447104 bytes
Control file created.
SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140
Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogB/log_g18m1.dbf ORA-00310: archived log contains sequence 145139; sequence 145140 required ORA-00334: archived log: ‘/oracle/CRQ/origlogB/log_g18m1.dbf’
SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140
Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogA/log_g17m1.dbf ORA-00310: archived log contains sequence 145138; sequence 145140 required ORA-00334: archived log: ‘/oracle/CRQ/origlogA/log_g17m1.dbf’
SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140
Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogB/log_g16m1.dbf ORA-00310: archived log contains sequence 145137; sequence 145140 required ORA-00334: archived log: ‘/oracle/CRQ/origlogB/log_g16m1.dbf’
SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140
Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogA/log_g15m1.dbf ORA-00310: archived log contains sequence 145136; sequence 145140 required ORA-00334: archived log: ‘/oracle/CRQ/origlogA/log_g15m1.dbf’
SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140
Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogB/log_g14m1.dbf Log applied. Media recovery complete. SQL> alter database open resetlogs;
Database altered.
SQL> =============================================================================== Please find the log status during this recovery from other session…. FYI…:)
SQL> select GROUP#,BYTES/1024/1024,MEMBERS,STATUS from v$log;
GROUP# BYTES/1024/1024 MEMBERS STATUS ———- ————— ———- —————- 1 50 2 INACTIVE 2 50 2 INACTIVE 3 50 2 INACTIVE 4 50 2 INACTIVE 5 50 2 INACTIVE 6 50 2 INACTIVE 7 50 2 INACTIVE 8 50 2 CURRENT
8 rows selected.
SQL> col MEMBER for a40 SQL> col TYPE for a12 SQL> select GROUP#,MEMBER,TYPE from v$logfile;
GROUP# MEMBER TYPE ———- —————————————- ———— 8 /oracle/CRQ/origlogB/log_g18m1.dbf ONLINE 8 /oracle/CRQ/mirrlogB/log_g18m2.dbf ONLINE 7 /oracle/CRQ/origlogA/log_g17m1.dbf ONLINE 7 /oracle/CRQ/mirrlogA/log_g17m2.dbf ONLINE 6 /oracle/CRQ/origlogB/log_g16m1.dbf ONLINE 6 /oracle/CRQ/mirrlogB/log_g16m2.dbf ONLINE 5 /oracle/CRQ/origlogA/log_g15m1.dbf ONLINE 5 /oracle/CRQ/mirrlogA/log_g15m2.dbf ONLINE 4 /oracle/CRQ/origlogB/log_g14m1.dbf ONLINE 4 /oracle/CRQ/mirrlogB/log_g14m2.dbf ONLINE 3 /oracle/CRQ/origlogA/log_g13m1.dbf ONLINE
GROUP# MEMBER TYPE ———- —————————————- ———— 3 /oracle/CRQ/mirrlogA/log_g13m2.dbf ONLINE 2 /oracle/CRQ/origlogB/log_g12m1.dbf ONLINE 2 /oracle/CRQ/mirrlogB/log_g12m2.dbf ONLINE 1 /oracle/CRQ/origlogA/log_g11m1.dbf ONLINE 1 /oracle/CRQ/mirrlogA/log_g11m2.dbf ONLINE
16 rows selected.
SQL>
Hope this article helped to you. I am expecting your suggestions/feedback.
It will help to motivate me to write more articles….!!!!
Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
“Key for success, always fight even knowing your defeat is certain….!!!!
samadhandba 