This would happen if Redo Log groups are added, what I call, "in-ordinately". If you add Group#n when the CURRENT group is not "n-1" (e.g. it is "n-2"), you will see Oracle switch "out of sequence".
Here's a simple demo :
These are my log files. Notice that GROUP#2 is CURRENT :
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 10 /oracle_fs/Databases/ORT24FS/redo01.dbf INACTIVE 50.00
2 11 /oracle_fs/Databases/ORT24FS/redo02.dbf CURRENT 50.00
3 9 /oracle_fs/Databases/ORT24FS/redo03.dbf INACTIVE 50.00
SQL>
I then add GROUP#4
SQL> alter database add logfile group 4 '/oracle_fs/Databases/ORT24FS/redo04.dbf' size 50M;
Database altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 10 /oracle_fs/Databases/ORT24FS/redo01.dbf INACTIVE 50.00
2 11 /oracle_fs/Databases/ORT24FS/redo02.dbf CURRENT 50.00
3 9 /oracle_fs/Databases/ORT24FS/redo03.dbf INACTIVE 50.00
4 0 /oracle_fs/Databases/ORT24FS/redo04.dbf UNUSED 50.00
SQL>
However, the next Log Switch (SEQUENCE#11 to #12) goes from GROUP#2 to GROUP#4 :
SQL> alter system switch logfile;
System altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 10 /oracle_fs/Databases/ORT24FS/redo01.dbf INACTIVE 50.00
2 11 /oracle_fs/Databases/ORT24FS/redo02.dbf ACTIVE 50.00
3 9 /oracle_fs/Databases/ORT24FS/redo03.dbf INACTIVE 50.00
4 12 /oracle_fs/Databases/ORT24FS/redo04.dbf CURRENT 50.00
SQL>
Another switch sets GROUP#3 as SEQUENCE#13 now :
SQL> alter system switch logfile;
System altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 10 /oracle_fs/Databases/ORT24FS/redo01.dbf INACTIVE 50.00
2 11 /oracle_fs/Databases/ORT24FS/redo02.dbf INACTIVE 50.00
3 13 /oracle_fs/Databases/ORT24FS/redo03.dbf CURRENT 50.00
4 12 /oracle_fs/Databases/ORT24FS/redo04.dbf ACTIVE 50.00
SQL>
I now add two Log Groups: 5 and 6
SQL> alter database add logfile group 5 '/oracle_fs/Databases/ORT24FS/redo05.dbf' size 50M;
Database altered.
SQL> alter database add logfile group 6 '/oracle_fs/Databases/ORT24FS/redo06.dbf' size 50M;
Database altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 10 /oracle_fs/Databases/ORT24FS/redo01.dbf INACTIVE 50.00
2 11 /oracle_fs/Databases/ORT24FS/redo02.dbf INACTIVE 50.00
3 13 /oracle_fs/Databases/ORT24FS/redo03.dbf CURRENT 50.00
4 12 /oracle_fs/Databases/ORT24FS/redo04.dbf ACTIVE 50.00
5 0 /oracle_fs/Databases/ORT24FS/redo05.dbf UNUSED 50.00
6 0 /oracle_fs/Databases/ORT24FS/redo06.dbf UNUSED 50.00
6 rows selected.
SQL>
Now, GROUP#5 becomes SEQUENCE#14 and GROUP#6 becomes SEQUENCE#15. Oracle has, this time, "jumped" from GROUP#3 to GROUP#5 !
SQL> alter system switch logfile;
System altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 10 /oracle_fs/Databases/ORT24FS/redo01.dbf INACTIVE 50.00
2 11 /oracle_fs/Databases/ORT24FS/redo02.dbf INACTIVE 50.00
3 13 /oracle_fs/Databases/ORT24FS/redo03.dbf ACTIVE 50.00
4 12 /oracle_fs/Databases/ORT24FS/redo04.dbf ACTIVE 50.00
5 14 /oracle_fs/Databases/ORT24FS/redo05.dbf CURRENT 50.00
6 0 /oracle_fs/Databases/ORT24FS/redo06.dbf UNUSED 50.00
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 10 /oracle_fs/Databases/ORT24FS/redo01.dbf INACTIVE 50.00
2 11 /oracle_fs/Databases/ORT24FS/redo02.dbf INACTIVE 50.00
3 13 /oracle_fs/Databases/ORT24FS/redo03.dbf ACTIVE 50.00
4 12 /oracle_fs/Databases/ORT24FS/redo04.dbf ACTIVE 50.00
5 14 /oracle_fs/Databases/ORT24FS/redo05.dbf ACTIVE 50.00
6 15 /oracle_fs/Databases/ORT24FS/redo06.dbf CURRENT 50.00
6 rows selected.
SQL>
I now drop and recreate GROUP#2 :
SQL> alter database drop logfile group 2;
Database altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 10 /oracle_fs/Databases/ORT24FS/redo01.dbf INACTIVE 50.00
3 13 /oracle_fs/Databases/ORT24FS/redo03.dbf ACTIVE 50.00
4 12 /oracle_fs/Databases/ORT24FS/redo04.dbf INACTIVE 50.00
5 14 /oracle_fs/Databases/ORT24FS/redo05.dbf ACTIVE 50.00
6 15 /oracle_fs/Databases/ORT24FS/redo06.dbf CURRENT 50.00
SQL> alter database add logfile group 2 '/oracle_fs/Databases/ORT24FS/redo02.dbf' size 50M;
Database altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 10 /oracle_fs/Databases/ORT24FS/redo01.dbf INACTIVE 50.00
2 0 /oracle_fs/Databases/ORT24FS/redo02.dbf UNUSED 50.00
3 13 /oracle_fs/Databases/ORT24FS/redo03.dbf ACTIVE 50.00
4 12 /oracle_fs/Databases/ORT24FS/redo04.dbf INACTIVE 50.00
5 14 /oracle_fs/Databases/ORT24FS/redo05.dbf ACTIVE 50.00
6 15 /oracle_fs/Databases/ORT24FS/redo06.dbf CURRENT 50.00
6 rows selected.
SQL>
What is the next switch ? It goes from GROUP#6 to GROUP#2 for SEQUENCE#16 ! :
SQL> alter system switch logfile;
System altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 10 /oracle_fs/Databases/ORT24FS/redo01.dbf INACTIVE 50.00
2 16 /oracle_fs/Databases/ORT24FS/redo02.dbf CURRENT 50.00
3 13 /oracle_fs/Databases/ORT24FS/redo03.dbf INACTIVE 50.00
4 12 /oracle_fs/Databases/ORT24FS/redo04.dbf INACTIVE 50.00
5 14 /oracle_fs/Databases/ORT24FS/redo05.dbf INACTIVE 50.00
6 15 /oracle_fs/Databases/ORT24FS/redo06.dbf ACTIVE 50.00
6 rows selected.
SQL>
Is it not very confusing now ?
SEQUENCE#10 is "redo01.dbf"
SEQUENCE#11 is no longer an online redo log !
SEQUENCE#12 is "redo04.dbf"
SEQUENCE#13 is "redo03.dbf"
SEQUENCE#14 is "redo05.dbf"
SEQUENCE#15 is "redo06.dbf"
SEQUENCE#16 is "redo02.dbf"
Anyone looking at the alert.log (but not noticing the DROP GROUP / ADD GROUP commands) and/or V$LOG would think that Oracle has "randomly" moved from red01.dbf to redo04.dbf than backwards to redo03.dbf and so on .... totally "out of sequence !"
(it becomes more confusing if the file names are not numbered -- eg if GROUP#1's, file is "/uA/oracle/redo/redo.dbf", GROUP#2's file is "/uX/oracle/redo/redo.dbf", GROUP#3's file is "/mnt/redo.dbf" and so on !! -- which is possible but very untidy !)
Technically, this mismatch doesn't matter. Oracle doesn't hardcode GROUP# and MEMBER names, when it looks at a Redo Log file, it checks the SEQUENCE# (and SCNs and Timestamps).
Things get more confusing with RAC when GROUPs span Threads. GROUP#1, GROUP#2, GROUP#3, GROUP#7,GROUP#9 could be in THREAD#1, while GROUP#4, GROUP#5, GROUP#6, GROUP#8 and GROUP#10 are in THREAD#2 ! (But you can't have the same GROUP# in two THREAD#s -- GROUP#s are Unique across the database).
Thus, in my non-RAC environment I can "skip" Groups 7 and 8 and move from GROUP#2 to GROUP#9 for going from SEQUENCE#16 to SEQUENCE#17, with the GROUP#9 file name not representing it's group at all :
SQL> alter database add logfile group 9 '/tmp/redoX.dbf' size 50M;
Database altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 10 /oracle_fs/Databases/ORT24FS/redo01.dbf INACTIVE 50.00
2 16 /oracle_fs/Databases/ORT24FS/redo02.dbf CURRENT 50.00
3 13 /oracle_fs/Databases/ORT24FS/redo03.dbf INACTIVE 50.00
4 12 /oracle_fs/Databases/ORT24FS/redo04.dbf INACTIVE 50.00
5 14 /oracle_fs/Databases/ORT24FS/redo05.dbf INACTIVE 50.00
6 15 /oracle_fs/Databases/ORT24FS/redo06.dbf INACTIVE 50.00
9 0 /tmp/redoX.dbf UNUSED 50.00
7 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 10 /oracle_fs/Databases/ORT24FS/redo01.dbf INACTIVE 50.00
2 16 /oracle_fs/Databases/ORT24FS/redo02.dbf ACTIVE 50.00
3 13 /oracle_fs/Databases/ORT24FS/redo03.dbf INACTIVE 50.00
4 12 /oracle_fs/Databases/ORT24FS/redo04.dbf INACTIVE 50.00
5 14 /oracle_fs/Databases/ORT24FS/redo05.dbf INACTIVE 50.00
6 15 /oracle_fs/Databases/ORT24FS/redo06.dbf INACTIVE 50.00
9 17 /tmp/redoX.dbf CURRENT 50.00
7 rows selected.
SQL>
Therefore, it (i.e. the GROUP#s you create and MEMBER names you assign) doesn't really matter. However, if you want to "prettify" things, ensure that you ADD GROUP#n when the CURRENT file is GROUP#n-1.
This is how I "cleanup" and "prettify" things :
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 18 /oracle_fs/Databases/ORT24FS/redo01.dbf CURRENT 50.00
2 16 /oracle_fs/Databases/ORT24FS/redo02.dbf INACTIVE 50.00
3 13 /oracle_fs/Databases/ORT24FS/redo03.dbf INACTIVE 50.00
4 12 /oracle_fs/Databases/ORT24FS/redo04.dbf INACTIVE 50.00
5 14 /oracle_fs/Databases/ORT24FS/redo05.dbf INACTIVE 50.00
6 15 /oracle_fs/Databases/ORT24FS/redo06.dbf INACTIVE 50.00
9 17 /tmp/redoX.dbf INACTIVE 50.00
7 rows selected.
SQL>
SQL> alter database drop logfile group 9;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL>
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 18 /oracle_fs/Databases/ORT24FS/redo01.dbf CURRENT 50.00
2 16 /oracle_fs/Databases/ORT24FS/redo02.dbf INACTIVE 50.00
SQL>
SQL> alter system switch logfile;
System altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 18 /oracle_fs/Databases/ORT24FS/redo01.dbf ACTIVE 50.00
2 19 /oracle_fs/Databases/ORT24FS/redo02.dbf CURRENT 50.00
SQL> alter database add logfile group 3 '/oracle_fs/Databases/ORT24FS/redo03.dbf' size 50M;
Database altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 18 /oracle_fs/Databases/ORT24FS/redo01.dbf ACTIVE 50.00
2 19 /oracle_fs/Databases/ORT24FS/redo02.dbf CURRENT 50.00
3 0 /oracle_fs/Databases/ORT24FS/redo03.dbf UNUSED 50.00
SQL> alter system switch logfile;
System altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 18 /oracle_fs/Databases/ORT24FS/redo01.dbf ACTIVE 50.00
2 19 /oracle_fs/Databases/ORT24FS/redo02.dbf ACTIVE 50.00
3 20 /oracle_fs/Databases/ORT24FS/redo03.dbf CURRENT 50.00
SQL> alter database add logfile group 4 '/oracle_fs/Databases/ORT24FS/redo04.dbf' size 50M;
Database altered.
SQL> @log_files;
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 18 /oracle_fs/Databases/ORT24FS/redo01.dbf ACTIVE 50.00
2 19 /oracle_fs/Databases/ORT24FS/redo02.dbf ACTIVE 50.00
3 20 /oracle_fs/Databases/ORT24FS/redo03.dbf CURRENT 50.00
4 0 /oracle_fs/Databases/ORT24FS/redo04.dbf UNUSED 50.00
SQL> alter system switch logfile;
System altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 18 /oracle_fs/Databases/ORT24FS/redo01.dbf ACTIVE 50.00
2 19 /oracle_fs/Databases/ORT24FS/redo02.dbf ACTIVE 50.00
3 20 /oracle_fs/Databases/ORT24FS/redo03.dbf ACTIVE 50.00
4 21 /oracle_fs/Databases/ORT24FS/redo04.dbf CURRENT 50.00
SQL>
SQL> alter database add logfile group 5 '/oracle_fs/Databases/ORT24FS/redo05.dbf' size 50M;
Database altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 18 /oracle_fs/Databases/ORT24FS/redo01.dbf ACTIVE 50.00
2 19 /oracle_fs/Databases/ORT24FS/redo02.dbf ACTIVE 50.00
3 20 /oracle_fs/Databases/ORT24FS/redo03.dbf ACTIVE 50.00
4 21 /oracle_fs/Databases/ORT24FS/redo04.dbf CURRENT 50.00
5 0 /oracle_fs/Databases/ORT24FS/redo05.dbf UNUSED 50.00
SQL> alter system switch logfile;
System altered.
SQL>
SQL> @log_files;
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 18 /oracle_fs/Databases/ORT24FS/redo01.dbf ACTIVE 50.00
2 19 /oracle_fs/Databases/ORT24FS/redo02.dbf ACTIVE 50.00
3 20 /oracle_fs/Databases/ORT24FS/redo03.dbf ACTIVE 50.00
4 21 /oracle_fs/Databases/ORT24FS/redo04.dbf ACTIVE 50.00
5 22 /oracle_fs/Databases/ORT24FS/redo05.dbf CURRENT 50.00
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> @log_files
Grp SEQUENCE# MEMBER Status Size
---- ---------- --------------------------------------------- --------- -------
1 23 /oracle_fs/Databases/ORT24FS/redo01.dbf ACTIVE 50.00
2 24 /oracle_fs/Databases/ORT24FS/redo02.dbf ACTIVE 50.00
3 25 /oracle_fs/Databases/ORT24FS/redo03.dbf CURRENT 50.00
4 21 /oracle_fs/Databases/ORT24FS/redo04.dbf ACTIVE 50.00
5 22 /oracle_fs/Databases/ORT24FS/redo05.dbf ACTIVE 50.00
SQL>
How did I do it ? I was very careful to use "ALTER SYSTEM SWITCH LOGFILE" to ensure that my CURRENT GROUP# was "n-1" before adding GROUP#n.
Note : The "ALTER DATABASE DROP LOGFILE" command didn't physically remove the logfiles. I used another terminal to run "rm redo0[3-6].dbf" after ensuring that they no longer belonged to the database. Be very very very careful when removing RedoLog files in this manner -- you don't want to remove a file at the OS level when the GROUP# is still "ACTIVE" or "CURRENT" or even "INACTIVE" or "UNUSED" in V$LOG --- the errors and error handling are different in each case (removing the CURRENT file requires a RESTORE and INCOMPLETE RECOVERY of the Database !).
FYI, this is the "log_files.sql" script I used :
SQL> !cat log_files.sql
col member format a45
col sz format 999.99 hea 'Size'
col group# format 999 hea 'Grp'
col status format a9 hea 'Status'
select l.group#, l.sequence#,f.member, l.status, l.bytes/1048576 sz
from v$logfile f, v$log l
where f.group#=l.group#
order by f.group#, f.member
/
SQL>
Another note : I have only 1 MEMBER per GROUP#. In your production database, if you follow "best practice" you probably have 2 or more MEMBERs per GROUP#.
So, brush up on your understanding of
THREAD#
GROUP#
MEMBER
SEQUENCE# !!
PS : Don't try these commands on your Production database until you have rehearsed the scenarios on a Test Database !
.
.
.
7 comments:
Hi
If I use "set new name" command in rman, do I always have to issue "switch datafile all;" command ?
I`ve just used duplicate command to duplicate a database and used "set new name" in run block , I didnt specify "switch datafile all" command , and it worked without any issues
In short, in which cases I should specify "switch datafile all" clause ?
Anonymous,
Your comment DOES NOT relate to this blog post.
Hemant K Chitale
Do you have a related thread that I can post this ?
Thanks in advance..
I am really curious about the answer
Anonymous,
When I've used RMAN's DUPLICATE DATABASE with the instance parameter "DB_CREATE_FILE_DEST", I've seen RMAN do it's own "SET NEWNAME" in the "contents of Memory Script". I only specified "DUPLICATE TARGET DATABASE TO DUPDB" and let RMAN generate the "SET NEWNAME" commands.
Later, I see RMAN do a "CATALOG CLONE DATAFILECOPY" and "SWITCH CLONE DATAFILE ALL"
Have you tried that ? Capture the "contents of Memory Script" that RMAN creates for each phase of the DUPLICATE DATABASE and see what it does.
Hemant K Chitale
If I dont use duplicate command and use normal rman restore with set new name, do I always have to issue "switch datafile all" clause?
Anonymous,
No.
If you restore datafiles to the same path, you do not need to rename them. So SET NEWNAME is not always necessary.
I cannot allow further conversation on this topic as it is not relevant to this blog post.
Hemant K Chitale
Post a Comment