This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jan 16, 2013 11:21 AM by 977635 RSS

dropping log members from DG primary / standby databases...

977635 Newbie
Currently Being Moderated
Hello.

I am running Oracle 11.2 (on Solaris) and have a question regarding redo log members.

Unfortunately, I do not have a test database that mimics production to test what I need to do, otherwise I would just do it in test and not need to ask this. Anyway...

I found that the production database has 3 members in each redo log group. Knowing that there is no real valid reason for having more than 2 members per log group, I would like to drop one of the log group members. So, my question regards the standby log members. And if there are any additional steps I need to take for the standby database.


For example, I have 9 redo log groups. 4 for primary, and 5 for standby.
This is good. No problems with this. I just want to reduce the number of members in each group from 3 to 2.

It appears (from http://psoug.org/reference/log_files.html) that I just need to specify the "standby" parameter in my syntax for dealing with the standby log members.
ALTER DATABASE DROP [STANDBY] LOGFILE MEMBER <logfile_member_path_and_name>;
Will this same action also take place automatically on the standby database?
I assume it would, but just wanted to confirm.
  • 1. Re: dropping log members from DG primary / standby databases...
    mseberg Guru
    Currently Being Moderated
    Hello;

    The may help :

    How to Drop/Rename Standby Redolog file in Oracle 11g

    http://neeraj-dba.blogspot.com/2011/10/how-to-droprename-standby-redolog-file.html

    Best Regards

    mseberg
  • 2. Re: dropping log members from DG primary / standby databases...
    TSharma-Oracle Guru
    Currently Being Moderated
    Drop Redo Logs on Standby

    1. Check the Status of the Online Redolog Group.

    Even if the online redo logs are not used on a physical standby database, the status within v$log will be updated through the standby control file. They are results of actions on the primary database eg. logswitches.


    SQL> SELECT GROUP#, STATUS FROM V$LOG;

    GROUP# STATUS
    ---------- ----------------
    1 CLEARING_CURRENT
    3 CLEARING
    2 CLEARING
    If Status is CLEARING_CURRENT or CURRENT then you cannot drop Online Redolog Group, please use "Sync Redo Logs on Primary and Standby" Section in this Case. You will get ORA-01623 if you try to drop a Redolog Group with Status CLEARING_CURRENT or CURRENT.


    You can also change this status by doing a ALTER SYSTEM SWITCH LOGFILE; on primary.


    For Status CLEARING, UNUSED, INACTIVE please follow below steps.

    2. Stop Redo Apply:

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
    3. Set STANDBY_FILE_MANAGEMENT to MANUAL.

    SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL'; 4. Clear the Online Redo Logfile Group:

    SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2; 5. Drop the Online Redo Logfile Group:

    SQL>ALTER DATABASE DROP LOGFILE GROUP 2; If you have skipped Step 4 then you will get ORA-01624 while droping the Online Redolog Group with Status CLEARING.

    6. Set STANDBY_FILE_MANAGEMENT to AUTO.

    SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
    7. Start Redo Apply:

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
  • 3. Re: dropping log members from DG primary / standby databases...
    977635 Newbie
    Currently Being Moderated
    Thank you for your response.
    The document you referenced, however, only seems to talk about dropping an entire redo log group.
    In my case, I only want to drop members of the log group to reduce the number of members from 3 to 2 for each group.
  • 4. Re: dropping log members from DG primary / standby databases...
    977635 Newbie
    Currently Being Moderated
    Hi TSharma,

    As I replied to mseberg, I only want to drop members of the log group to reduce the number of members from 3 to 2 for each group.

    Do you think I will need to deal with any issues just for dropping a member of the redo log group (and not the group itself)?
  • 5. Re: dropping log members from DG primary / standby databases...
    mseberg Guru
    Currently Being Moderated
    Hello;

    The step are the same.

    You use a different statement.
    ALTER DATABASE DROP LOGFILE MEMBER 'full_path_to_file_instead';
    Standby redo logs should have only one member. Redo on a standby is not used.

    Best Regards

    mseberg
  • 6. Re: dropping log members from DG primary / standby databases...
    977635 Newbie
    Currently Being Moderated
    Hi mseberg,

    You said:
    Standby redo logs should have only one member. Redo on a standby is not used.
    I guess I'm surprised by this statement, although I've never really considered it.

    So, for the primary database, I have always used multiplexed redo logs (one each going to two separate mount points).
    I know this is old school, but I still think it is valid. Although, with ASM, I'm not sure we need to multiplex, but then again, I dislike using ASM so I don't care about ASM.
    And even with ASM, I guess you could still use the standard file system for your redo logs.

    Anyway, for normal file systems, why would you not multiplex redo logs on the standby database.
    If we ever needed to switchover, would it only have one redo log per log group?
    I guess I'm confused how the standby uses redo logs when it is in standby mode verses primary mode.

    When I look at v$logfile in the standby database, I see the same configurations as primary, except the group # is different for the ONLINE logs.
    The standby logs in both primary and standby are configured with the same group numbers.
  • 7. Re: dropping log members from DG primary / standby databases...
    977635 Newbie
    Currently Being Moderated
    Also, another question on same subject:

    if we have our parameter for "standby_file_management" set to manual, will the standby database perform the same file operations as I run on the primary database?

    Why would they have set this parameter to manual? How safe is it to change this parameter to AUTO now?
  • 8. Re: dropping log members from DG primary / standby databases...
    mseberg Guru
    Currently Being Moderated
    Hello;

    I always set this to Auto because I want data file changes on the Primary to occur on the Standby too. It's simpler. You should be able to change it from Manual without issue.
    I guess I'm surprised by this statement, although I've never really considered it.
    A database in Standby mode does not generate Redo. So the Redo will only gets used when that database is in Primary mode and is generating redo.
    Think of a Standby database as a "Reader" database. It does not generate redo, it applys it.
    If we ever needed to switchover, would it only have one redo log per log group?
    No. It should have at least two members in case you run the Standby as a Primary. So the redo is idle in standby mode, but setup and ready to work if the mode changes.

    Best Regards

    mseberg
  • 9. Re: dropping log members from DG primary / standby databases...
    977635 Newbie
    Currently Being Moderated
    Mseberg.

    I see.

    So, with having my primary database RECOVERY MODE set to "MANGED REAL TIME APPLY", it uses the standby redo logs only for reading changes that are to be applied to the standby database.
    (SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;)

    Then, if ever the standby database is switched over to PRIMARY mode, then it used the standard redo logs as usual, and the standby redo logs are now used to transport changes back to the new standby database (which was primary before the switchover).

    Does this sound right?

    I think this makes sense to me now, and it makes sense now why we would only need one standby redo log per standby redo log group.
  • 10. Re: dropping log members from DG primary / standby databases...
    mseberg Guru
    Currently Being Moderated
    Not quite.

    Standby Redo are not use when the database is in Primary mode. They are used on the Standby side.

    So you have logs in both modes which are not used at any given moment.

    So when in Standby mode a database will use standby logs ( if you have them ) but not redo.

    In Primary mode a database will use redo logs, but not standby logs.

    In Data Guard document has a Redo Transport Services which may help.

    This is a good read too :

    http://www.pythian.com/news/581/oracle-standby-redo-logs/

    Why do we need standby redo log on Primary database.

    http://maleshg.wordpress.com/2012/02/25/standby-redo-logs/

    online redo logs vs standby redo logs
    Best Regards

    mseberg
  • 11. Re: dropping log members from DG primary / standby databases...
    977635 Newbie
    Currently Being Moderated
    Thanks again mseberg.

    So, I've deleting the third member of my redo log groups on the primary database.
    Now, all my redo log groups (including standby) have only two members.
    (I will reduce my standby to only one member, but not today. - taking only small changes one at a time)

    I had set STANDBY_FILE_MANAGEMENT to "AUTO" before deleting the redo log members, but when I look in my standby database, the log files that I deleted on the primary side are still there.

    Should the files have been deleted there too? Or am I just being impatient and I may need to wait a while before my changes to primary are applied to standby.
    Or, does the STANDBY_FILE_MANAGMENT also need to be set to AUTO on the standby database (which I did not do).

    Actually, I just checked, and my STANDBY_FILE_MANAGEMENT is already set to AUTO in my standby database.

    Now, I'm unsure what to do in my standby database. Do I delete the third redo log members, or wait until later to see if they get deleted automatically.
    And if I do delete them manually from the standby database, can I just use the same commands I did on primary?
    Also, would I have to take the standby database out of managed recovery mode?
  • 12. Re: dropping log members from DG primary / standby databases...
    mseberg Guru
    Currently Being Moderated
    Hello again;

    Should the files have been deleted there too?

    I probably would, but I would not make it very high on my list. If all goes well they will sit there 99 percent of the time and do nothing. Some rainy day I would say.

    Best Regards

    mseberg
  • 13. Re: dropping log members from DG primary / standby databases...
    977635 Newbie
    Currently Being Moderated
    I'm unsure what to do in my standby database. it would appear that even though I had standby_file_management set to AUTO, it did not delete the files as I would think it should.

    So, to delete the files manually from the standby database, can I just use the same commands I did on primary?
    Also, would I have to take the standby database out of managed recovery mode?


    LOL... it would appear I have to take the standby database out of recovery mode.

    SQL> ALTER DATABASE DROP STANDBY LOGFILE MEMBER '/opt/oracle/oradata4/PROD/REDO05C_STDBY.log';
    ALTER DATABASE DROP STANDBY LOGFILE MEMBER '/opt/oracle/oradata4/PROD/REDO05C_STDBY.log'
    *
    ERROR at line 1:
    ORA-01156: recovery or flashback in progress may need access to files

    That explains why the files were not automatically deleted when I deleted the files on the production side.
    But since the files are just redundant anyway, it seems Oracle would allow them to be deleted as long as we did not delete the last file.
  • 14. Re: dropping log members from DG primary / standby databases...
    mseberg Guru
    Currently Being Moderated
    OK;

    Having standby_file_management set to AUTO and the Redo question are not the same thing.

    Example

    If I have standby_file_management set to AUTO and I add a datafile or increase the size of an existing datafile the changes will also occur on the Standby side. This is good, saves me a ton of trouble.

    Redo Logs are a different animal. If you Drop, change size etc standby_file_management does not help you. In fact the document for resizing, will have you set standby_file_management to Manual and then back to Auto when you are done.

    Yes if you want to change either type of log on the Standby you have to stop recovery (MRP) on standby database.

    Best Regards

    mseberg
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points