Recovery of Online Redo log file (Windows OS)


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-------
Advertisements

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

%d bloggers like this: