Relocate online redolog members with Oracle database open

[former_member184955]

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

[volker_borowski2 · Accepted solution]

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

[former_member184955]

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

[former_member184955]

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

[volker_borowski2]

Just another thing that did come to my mind ...

If you have a controlfile with one of theses symlinked locations you will not get away without downtime.

If your controlfiles are on origlogA, sapdata1 and oraarch, it will work.

If one of your controlfiles is on a mirrlog path you are busted.

So crosscheck if removing the symlinks might compromise one of your controlfile pathnames.

Volker

[former_member184955]

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

[former_member182034]

hi Delia,

Please go through the below SAP notes.

Note 309526 - Enlarging redo log files

Note 1627481 - Preemptive redolog switches in Oracle 11.2

Regards,