Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 6 September 2012

Managing Online Redo logfiles

SQL> select group#, thread#, sequence#, bytes, members, archived,
status from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
------ ------  --------- ----- ------- --- ----------
1           1     340   104857600   1 YES INACTIVE
2           1     341   104857600   1 NO  CURRENT
3           1     339   104857600   1 YES INACTIVE
SQL> select * from v$logfile;
GROUP# STATUS  TYPE    MEMBER                                  
------ ------- ------- ----------------------------------------
1           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO01.LOG
2           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO02.LOG
3           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO03.LOG
Renaming Redo LogFiles.
SQL> shutdown immediate;
Copy or move the files to be rename using any way
Copy D:\ORACLE\ORA92\MUJAZORC\REDO01.LOG D:\ORACLE\ORA92\MUJAZORC\REDO01a.LOG
Copy D:\ORACLE\ORA92\MUJAZORC\REDO02.LOG D:\ORACLE\ORA92\MUJAZORC\REDO02a.LOG
Copy D:\ORACLE\ORA92\MUJAZORC\REDO03.LOG D:\ORACLE\ORA92\MUJAZORC\REDO03a.LOG
SQL> Startup Mount;
SQL> alter database rename file 'D:\ORACLE\ORA92\MUJAZORC\REDO01.LOG',
'D:\ORACLE\ORA92\MUJAZORC\REDO02.LOG',
'D:\ORACLE\ORA92\MUJAZORC\REDO03.LOG'
to
'D:\ORACLE\ORA92\MUJAZORC\REDO01a.LOG',
'D:\ORACLE\ORA92\MUJAZORC\REDO02a.LOG',
'D:\ORACLE\ORA92\MUJAZORC\REDO03a.LOG';
SQL> alter database open;
SQL> select * from v$logfile
GROUP# STATUS  TYPE    MEMBER                                 
----- ------- ------- ----------------------------------------
1           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO01a.LOG
2           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO02a.LOG
3           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO03a.LOG
Adding Redo Logfile members
# redo log files added individually
SQL> alter database add logfile member 'D:\ORACLE\ORA92\MUJAZORC\REDO01b.LOG' to group 1;
SQL> alter database add logfile
member 'D:\ORACLE\ORA92\MUJAZORC\REDO01c.LOG' to group 1;
# redo log files added in all at once.
SQL> alter database add logfile member
'D:\ORACLE\ORA92\MUJAZORC\REDO02b.LOG',
'D:\ORACLE\ORA92\MUJAZORC\REDO02c.LOG' to group 2;
SQL> alter database add logfile member
'D:\ORACLE\ORA92\MUJAZORC\REDO03b.LOG',
'D:\ORACLE\ORA92\MUJAZORC\REDO03c.LOG' to group 3;
SQL> select * from v$logfile;
GROUP# STATUS  TYPE    MEMBER                                 
----- ------- ------- ----------------------------------------
1           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO01a.LOG
2           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO02a.LOG
3           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO03a.LOG
1           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO01b.LOG
2           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO02b.LOG
3           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO03b.LOG
1           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO01c.LOG
2           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO02c.LOG
3           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO03c.LOG
Note: If the newly created log file members are showing a status of INVALID. In order to solve this problem execute the “alter system switch logfile” 3 or 4 times.
Adding a Redo Logfile Group.
SQL>alter database add logfile group 4 ('D:\ORACLE\ORA92\MUJAZORC\REDO04a.LOG',
'D:\ORACLE\ORA92\MUJAZORC\REDO04b.LOG ',
'D:\ORACLE\ORA92\MUJAZORC\REDO04c.LOG’) size 50m;
SQL> select * from v$logfile order by member;
GROUP# STATUS TYPE    MEMBER                                 
----- ------- ------- ----------------------------------------
1             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO01a.LOG
1             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO01b.LOG
1             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO01c.LOG
2             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO02b.LOG
2             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO02a.LOG
2             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO02c.LOG
3             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO03a.LOG
3             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO03b.LOG
3             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO03c.LOG
4             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO04a.LOG
4             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO04b.LOG
4             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO04c.LOG
Dropping redo log members.
SQL> alter database drop logfile member 'D:\ORACLE\ORA92\MUJAZORC\REDO04c.LOG';
Then directly remove from OS level using any delete command.
D:\>ORACLE>ORA92>MUJAZORC> del REDO04c.LOG;
SQL> select group#, thread#, sequence#, bytes, members, archived,
status from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- --------
         1          1          7   52428800          3 YES ACTIVE
         2          1          8   52428800          3 YES ACTIVE
         3          1          9   52428800          3 NO  CURRENT
         4          1          0   52428800          2 YES UNUSED
Notice the member’s column for group 4 is now 2. If a group contains only one logfile then you cannot drop that member.
Dropping Redo Log Group
SQL> alter database drop logfile group 4;
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
Note: Drop the files from the OS level also. You cannot drop a log group with ‘CURRENT’ Status and also you cannot drop the log group with ‘ACTIVE’ status.
Execute the following command to change the status of group
SQL> alter system switch logfile;
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 redolog 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 has 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;
c) 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:
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 completed then you will have to do incomplete media recovery.
Now if the status is inactive and log has been archived then recreate the files:
SQL> alter database clear logfile group 1;
If the status is inactive and log group is not archived then recreate the files:
SQL> alter database clear unarchived logfile group 1;
Note: 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 incomplete 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.

Performing an incomplete recovery up to the last good SCN?
In this case an incomplete recovery is the best we can do. Here we will lose all transactions from the missing log and all subsequent logs. Suppose the error message indicates that members of log group 3 are missing. We don't have a copy of this file, so we know that an incomplete recovery is required.
If you have multiplexed copy of the missing or corrupted redo log files then there is no need to perform incomplete recovery. You just copy this redolog file from multiplexed location to corrupted location and try to open the database.
The first step is to determine how much can be recovered. In order to do this, we can query the V$LOG view when the instance is in mount state to find the system change number (SCN) that we can recover to
Note: The SCN is a monotonically increasing number that is incremented whenever a commit is issued.
SQL> Select first_change# from v$log where GROUP# = 3;
FIRST_CHANGE#
-------------
60341893
The FIRST_CHANGE# is the first SCN stamped in the missing log. This implies that the last SCN stamped in the previous log is 60341892 (FIRST_CHANGE#-1). This is the highest SCN that we can recover to.
In order to do the recovery we must first restore ALL datafiles to this SCN, followed by recovery (also up to this SCN). This is an incomplete recovery, so we must open the database resetlogs after we're done.
C:\>rman target /
RMAN> restore database until SCN 60341892;
Important Point:
1.      The entire database must be restored to the SCN that has been determined by querying v$log.
All changes beyond that SCN are lost. This method of recovery should be used only if you are sure that you cannot do better. Be sure to multiplex your redo logs, and (space permitting) your archived logs!
2.      The database must be opened with RESETLOGS, as a required log has not been applied. This resets the log sequence to zero, thereby rendering all prior backups worthless. Thus, the first step after opening a database RESETLOGS is to take a fresh backup.

0 comments:

Post a Comment