Skip to Content

Relocate online redolog members with Oracle database open

Hey guys,

I want to relocate online redolog members with database open. I have Oracle on Linux Suse OS.

My current status is:


SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
1 /oracle/SID/origlogA/log_g11m1.dbf
1 /oracle/SID/mirrlogA/log_g11m2.dbf
2 /oracle/SID/origlogB/log_g12m1.dbf
2 /oracle/SID/mirrlogB/log_g12m2.dbf
3 /oracle/SID/origlogA/log_g13m1.dbf
3 /oracle/SID/mirrlogA/log_g13m2.dbf
4 /oracle/SID/origlogB/log_g14m1.dbf
4 /oracle/SID/mirrlogB/log_g14m2.dbf

But, on OS level mirrlogA and mirrlogB are symbolic links to origlogB and origlogA:


mirrlogA -> origlogB
mirrlogB -> origlogA

I need to create separate /oracle/SID/mirrlogA and /oracle/SID/mirrlogB filesystem, and the members to be located there.

In my opinion, I would try the following method:

Step A: delete member 2 of each group when the group is with status INACTIVE, by using drop sql statement "ALTER DATABASE DROP LOGFILE MEMBER"
For example group 1 in the following sql result:

SQL> select group#, archived, status from v$log;

GROUP# ARC STATUS
---------- --- ----------------
1 YES INACTIVE
2 YES ACTIVE
3 NO CURRENT
4 YES INACTIVE


Step B: create /oracle/SID/mirrlogA and /oracle/SID/mirrlogB filesystems
Step C: recreate member 2 of each group in the same logical location "/oracle/SID/mirrlogA" but new physical location.


My questions would be: is my approach correct? Do I need to perform some extra steps? Does this affect in a bad way the database?

If you have other methods to perform the relocation online, could you please post them?

Thank you,

Delia

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Nov 01, 2012 at 07:10 PM

    Hi Delia,

    your approach is completely correct, and can be carried out online.

    Do not forget to adjust the permissions of the newly created FS because i.g. they will belong to root.

    So you need to adjust to ownership orasid:dba.

    The action should not be executed while the logs are switching rapidly.

    If you are scared by running with only a single member for the time to do the switch,

    create a spare FS seperately and create a third member there first (just to be safe),

    before droping and relocating the second one in exactly the way you described.

    Drop the third member again, once you are done.

    Execute the entire procedure in QAS or Sandbox first to make yourself familiar with the processing.

    Volker

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 21, 2012 at 09:07 AM

    Hello 😊

    I applied the procedure on productive systems of all types (ABAP, Java, ABAP+Java) and it was successfull. The procedure is executed while Oracle database and SAP system are up and running.
    I executed the following steps:

    1. Check the redo log members of each group with SQL statement:

    SQL> select group#,status,member from v$logfile;

    2. Delete member 2 of each group. You can drop a redo log member only if it is not part of and active or current group. The group must be in status INACTIVE, like group 1 in example below:

    SQL> select group#, archived, status from v$log;

    GROUP# ARC STATUS

    ---------- --- ----------------

    1 YES INACTIVE

    2 YES ACTIVE

    3 NO CURRENT

    4 YES INACTIVE

    You want to drop member 2 of group 1, you must execute the following SQL statement:

    SQL> alter database drop logfile member '/oracle/SID/mirrlogA/log_g11m2.dbf';

    3. Mount needed filesystem. For example:

    /oracle/SID/mirrlogA

    /oracle/SID/mirrlogB

    4. In order to mirror existing online redo log files, adapt the following SQL command for each online redo log file that you need to be mirrored:

    SQL> alter database add logfile member ‘/oracle/SID/mirrlogA/log_g01m2.dbf’ to group 1;

    You can execute this command at any time, despite the status of the redo log group for which you add a member. It can be ACTIVE, INACTIVE or CURRENT.

    5. The new members will appear with status INVALID until the next swith logfile. In order to make the new log files valid, you need to run ‘alter system switch logfile’ sql command at least as many times as number of redo log group:

    SQL> select group#, archived, status from v$log;
    SQL> alter system switch logfile;

    6. Check that after the ‘alter system switch logfile’ sql command, the new log files have no longer the “INVALID” status:

    SQL> select group#,status,member from v$logfile;

    Have a beautiful day,

    Delia

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 01, 2012 at 03:23 PM
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 02, 2012 at 08:53 AM

    Hello,

    Thank you for your fast answers!

    @M. Abdul Jamil: Unfortunatelly the two notes refer to different aspects of mirroring
    - note 309526 refer to dropping the entire redo log group, and not members individually
    - note 1627481 refers to the size of redo log files, and not about dropping them

    A note more close to this topic is Note 491160 - Restore scenarios for lost files of oracle databases, but this did not really answered my doubt.

    @Volker Borowski: Thank you very much for your complete explanation! 😊 I will pay attention to the aspects you mentioned. I will start next week with some development systems (because we have multiple systems with the same situation) and afterwards, post the result online.

    Have a beautiful day,
    Delia

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Volker

      Thank you for the hint. I will check every system for the location of the control files. Still, so far, according to parameter control_files from initSID.ora, the control files are located in origlogA, origlogB and sapdata1. So, it should be ok from this point of view 😊

      Have a great day,

      Delia

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.