Lets check the online redo log configuration.
SQL> select group#, thread#, sequence#, bytes, members, archived,
status from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
1 1 4 52428800 1 NO CURRENT
2 1 2 52428800 1 NO INACTIVE
3 1 3 52428800 1 NO INACTIVE
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
------ ------- ------- ---------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
Renaming Redo LogFiles.
SQL> shutdown immediate
At the OS level issue the following commands to move the files.
[oracle@canada ~]$ mv /u01/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/oradata/orcl/redo01a.log
[oracle@canada ~]$ mv /u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo02a.log
[oracle@canada ~]$ mv /u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo03a.log
SQL> startup mount
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log',
'/u01/app/oracle/oradata/orcl/redo02.log',
'/u01/app/oracle/oradata/orcl/redo03.log'
to
'/u01/app/oracle/oradata/orcl/redo01a.log',
'/u01/app/oracle/oradata/orcl/redo02a.log',
'/u01/app/oracle/oradata/orcl/redo03a.log';
Database altered.
SQL> alter database open;
SQL>column member format a40
SQL> select * from v$logfile
GROUP# STATUS TYPE MEMBER IS_
----- ------- ------- ---------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03a.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01a.log NO
Adding Redo LogFile members.
# redo log files added individually.
SQL> alter database add logfile
member '/u01/app/oracle/oradata/orcl/redo01b.log' to group 1;
Database altered.
SQL> alter database add logfile
member '/u01/app/oracle/oradata/orcl/redo01c.log' to group 1;
Database altered.
# redo log files added in all at once.
SQL> alter database add logfile member
'/u01/app/oracle/oradata/orcl/redo02b.log',
'/u01/app/oracle/oradata/orcl/redo02c.log' to group 2;
Database altered.
SQL> alter database add logfile member
'/u01/app/oracle/oradata/orcl/redo03b.log',
'/u01/app/oracle/oradata/orcl/redo03c.log' to group 3;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
------ ------- ------- ---------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03a.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01a.log NO
1 INVALID ONLINE /u01/app/oracle/oradata/orcl/redo01b.log NO
1 INVALID ONLINE /u01/app/oracle/oradata/orcl/redo01c.log NO
2 INVALID ONLINE /u01/app/oracle/oradata/orcl/redo02b.log NO
2 INVALID ONLINE /u01/app/oracle/oradata/orcl/redo02c.log NO
3 INVALID ONLINE /u01/app/oracle/oradata/orcl/redo03b.log NO
3 INVALID ONLINE /u01/app/oracle/oradata/orcl/redo03c.log NO
9 rows selected.
Now i am going to put my database into archivelog mode.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 619360256 bytes
Fixed Size 1338280 bytes
Variable Size 398459992 bytes
Database Buffers 213909504 bytes
Redo Buffers 5652480 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
------ ------- ------- ---------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03a.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01a.log NO
1 INVALID ONLINE /u01/app/oracle/oradata/orcl/redo01b.log NO
1 INVALID ONLINE /u01/app/oracle/oradata/orcl/redo01c.log NO
2 INVALID ONLINE /u01/app/oracle/oradata/orcl/redo02b.log NO
2 INVALID ONLINE /u01/app/oracle/oradata/orcl/redo02c.log NO
3 INVALID ONLINE /u01/app/oracle/oradata/orcl/redo03b.log NO
3 INVALID ONLINE /u01/app/oracle/oradata/orcl/redo03c.log NO
9 rows selected.
SQL> select group#, thread#, sequence#, bytes, members, archived,
status from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- --------
1 1 4 52428800 3 NO CURRENT
2 1 2 52428800 3 YES INACTIVE
3 1 3 52428800 3 YES INACTIVE
As you can see in the v$logfile view some of the newly created log file
members are showing a status of INVALID.
In order to solve this problem execute the following command
3 or 4 times.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$logfile order by member;
GROUP# STATUS TYPE MEMBER IS_
------ ------- ------- ----------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/orcl/redo01a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01b.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01c.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02a.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02b.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02c.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03a.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03b.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03c.log NO
9 rows selected.
Adding a RedoLog File Group.
SQL>alter database add logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04a.log',
'/u01/app/oracle/oradata/orcl/redo04b.log',
'/u01/app/oracle/oradata/orcl/redo04c.log') size 50m;
Database altered.
SQL> select * from v$logfile order by member;
GROUP# STATUS TYPE MEMBER IS_
------ ------- ------- ----------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/orcl/redo01a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01b.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01c.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02a.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02b.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02c.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03a.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03b.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03c.log NO
4 ONLINE /u01/app/oracle/oradata/orcl/redo04a.log NO
4 ONLINE /u01/app/oracle/oradata/orcl/redo04b.log NO
4 ONLINE /u01/app/oracle/oradata/orcl/redo04c.log NO
12 rows selected.
SQL> select group#, thread#, sequence#, bytes, members, archived,
status from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- --------
1 1 8 52428800 3 YES ACTIVE
2 1 7 52428800 3 YES ACTIVE
3 1 9 52428800 3 NO CURRENT
4 1 0 52428800 3 YES UNUSED
Dropping redo log members.
SQL> alter database drop logfile member '/u01/app/oracle
/oradata/orcl/redo04c.log';
Database altered.
SQL> !rm /u01/app/oracle/oradata/orcl/redo04c.log;
SQL> select group#, thread#, sequence#, bytes, members, archived,
status from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- --------
1 1 8 52428800 3 YES ACTIVE
2 1 7 52428800 3 YES ACTIVE
3 1 9 52428800 3 NO CURRENT
4 1 0 52428800 2 YES UNUSED
Notice the members column for group 4 is now 2.
NOTE: If a group contains only one logfile then you cannot
drop that member.
Dropping Redo Log Group
SQL> alter database drop logfile group 4;
Database altered.
SQL> select group#, thread#, sequence#, bytes, members, archived,
status from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ---------
1 1 8 52428800 3 YES INACTIVE
2 1 7 52428800 3 YES INACTIVE
3 1 9 52428800 3 NO CURRENT
drop the files from the OS level also.
SQL> !rm /u01/app/oracle/oradata/orcl/redo04a.log;
SQL> !rm /u01/app/oracle/oradata/orcl/redo04b.log;
NOTE: You cannot drop a log group with CURRENT STATUS.
use the following command to make the other
group current.
SQL> alter system switch logfile;
NOTE: Also you cannot drop a log group with
ACTIVE status. Execute thefollowing command
to solve this problem.
SQL> alter system checkpoint;
Redo Logs Recovery
1) Restoring After Losing One Member of the Multiplexed
Group.
a) Identify the lost redo log file member.
b) Ensure that the online redomlog file is not part of
the current online log group.
c) drop the damaged member
d) add a new member to group
2) Recovering After Loss of All Members of the INACTIVE
Redo Log Group.
You are attempting to open your database but one of your
groups have been damaged and your database does not open.
a) After investigation you find that all the members in
Redo 1 have damaged.
b) Ensure that the database is in mount stage.
SQL> startup mount;
Next check the v$log view to make sure that the damaged
group is inactive. And determine whether it is archived.
SQL> select group#, thread#, sequence#, bytes, members,
archived, status from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ---------
1 1 8 52428800 3 YES INACTIVE
2 1 7 52428800 3 YES INACTIVE
3 1 9 52428800 3 NO CURRENT
Above as it can be seen our affected group 1 is inactive so it is
not needed for crash recovery. Therefore you can use the "clear logfile"
command to re-create all members of the group.
SQL> alter database clear logfile group 1;
If the log group is not archived execute the following command.
and after executing this command make a full backup of the database.
SQL> alter database clear unarchived logfile group 1;
3) Recovering After Loss of All members of the Active Redo
Log Group.
a) check the alert.log file for the damaged members.
You find that all the members of the redo log Group 1
have experienced media failure.
b) Go to mount stage
SQL> startup mount;
c) query the v$log to check the status.
SQL> select group#, thread#, sequence#, bytes, members, archived,
status from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ---------
1 1 8 52428800 3 YES ACTIVE
2 1 7 52428800 3 YES INACTIVE
3 1 9 52428800 3 NO CURRENT
If the status is active issue the following command.
SQL> alter system checkpoint;
If the checkpoint is successful the active group will show the
status of inactive. It means all the modified data buffers have been
written to disk.
If the checkpoint is not complete then you will have to do
incomplete media recovery.
Now if the status is inactive and log has been archived then
recreate the files by executing the following command.
SQL> alter database clear logfile group 1;
If the status is inactive and log group is not archived
then recreate the files by executing the following command.
SQL> alter database clear unarchived logfile group 1;
If the cleared log group has not been archived make a full backup
of your database.
4) Recovering After Loss of All Members of the Current
Redo Log Group.
You have three options in this case.
a) Perform an incomlete recovery up to last good SCN.
b) If flashback is enabled, flashback your database to last good scn.
c) If data guard is configured failover to your standby database.
hi this is very useful post for oracle dba.
ReplyDelete