Saturday, September 8, 2012

How to recover from a loss of all redo log members of an INACTIVE group

Let's simulate a loss of all redo log members of an inactive group.
If you have completely lost one redo log group your database won't be able to open.
Next thing is to know the STATUS of the lost redo log group (we know it because we are simulating the loss, but...) and, just because this information is on V$LOG view, try to get also the ARCHIVED column.
Now if the redo log group lost was INACTIVE and archived (YES value) we can simply recreate all members with the "alter database clear logfile" command;
if it was INACTIVE and not archived (NO value) we'll execute the "alter database clear unarchived logfile" command.

Let's look at the example. Here is my redo log group state
SQL> select member, a.group#, a.status, b.status, a.archived  from v$log a, v$logfile b where  a.group# = b.group# order by a.group#, member;

MEMBER                                                           GROUP# STATUS           STATUS  ARC
------------------------------------------------------------ ---------- ---------------- ------- ---
/home/oracle/app/oracle/oradata/orcl/redo01.log                       1 INACTIVE                 YES
/home/oracle/app/oracle/oradata/orcl/redo01b.log                      1 INACTIVE                 YES
/home/oracle/app/oracle/oradata/orcl/redo02.log                       2 CURRENT                  NO
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 CURRENT                  NO
/home/oracle/app/oracle/oradata/orcl/redo03.log                       3 INACTIVE                 YES
/home/oracle/app/oracle/oradata/orcl/redo03b.log                      3 INACTIVE                 YES
Delete all members of redo log group 1 and kill your instance.
[oracle@localhost ~]$ rm /home/oracle/app/oracle/oradata/orcl/redo01*
[oracle@localhost ~]$ ps -ef |grep smon
oracle    8176     1  0 06:44 ?        00:00:03 ora_smon_orcl
oracle    9057  3164  0 08:10 pts/5    00:00:00 grep smon
[oracle@localhost ~]$ kill -9 8176
Try to open your database ...
SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             385878712 bytes
Database Buffers           62914560 bytes
Redo Buffers                6008832 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 9184
Session ID: 17 Serial number: 7
... and look at your alert log
...
ORA-00313: open failed for members of log group 1 of thread 
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo01.log'
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo01b.log'
USER (ospid: 9184): terminating the instance due to error 313
...
Start your database in MOUNT mode
SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             385878712 bytes
Database Buffers           62914560 bytes
Redo Buffers                6008832 bytes
Database mounted.
Query the V$LOG view
SQL> select member, a.group#, a.status, b.status, a.archived  from v$log a, v$logfile b where  a.group# = b.group# order by a.group#, member;

MEMBER                                                           GROUP# STATUS           STATUS  ARC
------------------------------------------------------------ ---------- ---------------- ------- ---
/home/oracle/app/oracle/oradata/orcl/redo01.log                       1 INACTIVE                 YES
/home/oracle/app/oracle/oradata/orcl/redo01b.log                      1 INACTIVE                 YES
/home/oracle/app/oracle/oradata/orcl/redo02.log                       2 CURRENT                  NO
/home/oracle/app/oracle/oradata/orcl/redo02b.log                      2 CURRENT                  NO
/home/oracle/app/oracle/oradata/orcl/redo03.log                       3 INACTIVE                 YES
/home/oracle/app/oracle/oradata/orcl/redo03b.log                      3 INACTIVE                 YES
The redo log group 1 is INACTIVE and ARCHIVED so simply issue the following command:
SQL> alter database clear logfile group 1;

Database altered.
Open your database
SQL> alter database open;

Database altered.
If you are in the situation to have lost an INACTIVE and NOT already ARCHIVED redo log group then your command should be the following:
SQL> alter database clear unarchived logfile group 1;

Database altered.
Next step is to perform a complete backup
RMAN> backup database;
That's all.

5 comments:

Anonymous said...

I'm extremely impressed together with your writing abilities and also with the layout in your weblog. Is this a paid topic or did you customize it yourself? Either way stay up the excellent quality writing, it is uncommon to peer a nice weblog like this one today..

Feel free to visit my blog ... deblocari usi

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose