Recovery of Online Redo log file

1- Check existing online Redo log file

SQL> select * from v$Logfile;
GROUP# STATUS  TYPE    MEMBER
2 ONLINE C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_91MVD8GZ_.LOG

 YES
 1 ONLINE C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_91MVD6TJ_.LOG

YES

2- Add one more Redo log group to database.

SQL> ALTER DATABASE ADD LOGFILE 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\Redo_-01_03.LOG' size 52428800;
Database altered.

3- Verify

SQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED
 2 FROM V$LOG;
GROUP# MEMBERS STATUS ARC
---------- ---------- ----
 1 1 CURRENT NO
 2 1 UNUSED YES
 3 1 UNUSED YES

4- Alter system switch logfile to use Redo logfile

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED
 2 FROM V$LOG;
GROUP# MEMBERS STATUS ARC
---------- ---------- ---------------- ---
 1 1 ACTIVE YES
 2 1 ACTIVE YES
 3 1 CURRENT NO
SQL> /

5- Note Redo log group # 3 is current Redo logfile. 

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

6- I have deleted manually the Redo logfile group#3.
SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size 1388352 bytes
Variable Size 620757184 bytes
Database Buffers 444596224 bytes
Redo Buffers 4591616 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 9996
Session ID: 5 Serial number: 1
7- Mount the DB

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1388352 bytes
Variable Size 620757184 bytes
Database Buffers 444596224 bytes
Redo Buffers 4591616 bytes
Database mounted.

8- Recover the database

SQL> recover database until cancel;
Media recovery complete.

9- Open the Database

SQL> alter database open resetlogs;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
--------Recovery Completed-------

About Sher khan

Senior Oracle DBA, Oracle 10g and 11g OCE and OCS, Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g and 11g. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in rman. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s