Wednesday , February 21 2018
Home / Oracle DBA / How to Recovery Redo log file

How to Recovery Redo log file

–> Open database in mount stage:

SQL> shut abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 627732480 bytes
Fixed Size 1338336 bytes
Variable Size 545260576 bytes
Database Buffers 75497472 bytes
Redo Buffers 5636096 bytes
SQL> alter database mount;

Database altered.

–> Identify to which group the deleted redo file was associated:

SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;

GROUP# STATUS
———- ——-
MEMBER
——————————————————————————–
3
/u01/app/oracle/oradata/TrainingDatabase/redo03.log

2
/u01/app/oracle/oradata/TrainingDatabase/redo02.log

1
/u01/app/oracle/oradata/TrainingDatabase/redo01.log

–> check group status:

SQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED
FROM V$LOG; 2

GROUP# MEMBERS STATUS ARC
———- ———- —————- —
1 1 CURRENT NO
3 1 INACTIVE YES
2 1 UNUSED YES

SQL> ALTER DATABASE OPEN RESETLOGS;

ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

 

SQL> SQL>
SQL> select a.first_change#, a.status, b.member from v$log a, v$logfile b where a.group#=b.group# and a.status=’current’;

no rows selected

SQL> select a.first_change#, a.status, b.member from v$log a, v$logfile b where a.group#=b.group# and a.status=’CURRENT’;

FIRST_CHANGE# STATUS
————- —————-
MEMBER
——————————————————————————–
1591747 CURRENT
/u01/app/oracle/oradata/TrainingDatabase/redo01.log

 

SQL> recover database until change 1591747;
Media recovery complete.
SQL> alter
database open resetlogs;
3
SQL> alter database open resetlogs;

Database altered.

DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

Oracle 10g to 11g Enhancements (Difference between Oracle 10g and 11g)

Difference between Oracle 10g and 11g 1- Enhanced Automatic Memory Management System Oracle 9i automated …

Leave a Reply

Your email address will not be published. Required fields are marked *

DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.
close-link
GET OUR LATEST CONTENT IN YOUR INBOX

SUBSCRIBE 
Your information will never be shared
close-link
Enquiry
Submit
close-link
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.
Subscribe