OraFAQ Forum: Backup & Recovery » Why Do Redo Log Remain Active Even After alter system archive log current

Home » RDBMS Server » Backup & Recovery » Why Do Redo Log Remain Active Even After alter system archive log current (11.2.0.4, SLES SP3 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Why Do Redo Log Remain Active Even After alter system archive log current [message #647469]

Fri, 29 January 2016 00:08

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear all,

this is what I have done,


SYS@ORCL>SELECT log.group#, logfile.member, log.status, log.archived FROM sys.v_$logfile logfile JOIN sys.v_$log log ON logfile.group#=log.group# ORDER BY log.group#, logfile.member;

    GROUP# MEMBER                                     STATUS   ARC              
---------- ------------------------------------------ -------- ---              
         1 /u02/app/oracle2/oradata/ORCL/redo01.log   INACTIVE YES              
         2 /u02/app/oracle2/oradata/ORCL/redo02.log   INACTIVE YES              
         3 /u02/app/oracle2/oradata/ORCL/redo03.log   CURRENT  NO               
         4 /u02/app/oracle2/oradata/ORCL/redo04_1.log UNUSED   YES              
         4 /u02/app/oracle2/oradata/ORCL/redo04_2.log UNUSED   YES              
         5 /u02/app/oracle2/oradata/ORCL/redo05_1.log UNUSED   YES              
         5 /u02/app/oracle2/oradata/ORCL/redo05_2.log UNUSED   YES              
         6 /u02/app/oracle2/oradata/ORCL/redo06_1.log UNUSED   YES              
         6 /u02/app/oracle2/oradata/ORCL/redo06_2.log UNUSED   YES              

9 rows selected.

SYS@ORCL>
SYS@ORCL>ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SYS@ORCL>
SYS@ORCL>SELECT log.group#, logfile.member, log.status, log.archived FROM sys.v_$logfile logfile JOIN sys.v_$log log ON logfile.group#=log.group# ORDER BY log.group#, logfile.member;

    GROUP# MEMBER                                     STATUS   ARC              
---------- ------------------------------------------ -------- ---              
         1 /u02/app/oracle2/oradata/ORCL/redo01.log   INACTIVE YES              
         2 /u02/app/oracle2/oradata/ORCL/redo02.log   INACTIVE YES              
         3 /u02/app/oracle2/oradata/ORCL/redo03.log   ACTIVE   YES              
         4 /u02/app/oracle2/oradata/ORCL/redo04_1.log CURRENT  NO               
         4 /u02/app/oracle2/oradata/ORCL/redo04_2.log CURRENT  NO               
         5 /u02/app/oracle2/oradata/ORCL/redo05_1.log UNUSED   YES              
         5 /u02/app/oracle2/oradata/ORCL/redo05_2.log UNUSED   YES              
         6 /u02/app/oracle2/oradata/ORCL/redo06_1.log UNUSED   YES              
         6 /u02/app/oracle2/oradata/ORCL/redo06_2.log UNUSED   YES              

9 rows selected.

SYS@ORCL>
SYS@ORCL>ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

SYS@ORCL>
SYS@ORCL>ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

SYS@ORCL>
SYS@ORCL>ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance ORCL (thread 1) 
ORA-00312: online log 3 thread 1: '/u02/app/oracle2/oradata/ORCL/redo03.log'

My question is why do redo log group 3 remains active even after I do a alter system archive log current?

thanks in advance!

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647473 is a reply to message #647469]

Fri, 29 January 2016 00:57

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ACTIVE means it is needed for instance recovery.
During instance recovery archived logs are not used only online ones.
Archived logs are used for media recovery.

You have to checkpoint if you want the active log(s) to become inactive not to archive it/them.

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647474 is a reply to message #647473]

Fri, 29 January 2016 01:19

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

what should be done initally?


SYS@ORCL>SELECT log.group#, logfile.member, log.status, log.archived FROM sys.v_$logfile logfile JOIN sys.v_$log log ON logfile.group#=log.group# ORDER BY log.group#, logfile.member;

    GROUP# MEMBER                                     STATUS   ARC              
---------- ------------------------------------------ -------- ---              
         1 /u02/app/oracle2/oradata/ORCL/redo01.log   INACTIVE YES              
         2 /u02/app/oracle2/oradata/ORCL/redo02.log   INACTIVE YES              
         3 /u02/app/oracle2/oradata/ORCL/redo03.log   CURRENT  NO               
         4 /u02/app/oracle2/oradata/ORCL/redo04_1.log UNUSED   YES              
         4 /u02/app/oracle2/oradata/ORCL/redo04_2.log UNUSED   YES              
         5 /u02/app/oracle2/oradata/ORCL/redo05_1.log UNUSED   YES              
         5 /u02/app/oracle2/oradata/ORCL/redo05_2.log UNUSED   YES              
         6 /u02/app/oracle2/oradata/ORCL/redo06_1.log UNUSED   YES              
         6 /u02/app/oracle2/oradata/ORCL/redo06_2.log UNUSED   YES

are you saying

ALTER SYSTEM archive log current;



SYS@ORCL>SELECT log.group#, logfile.member, log.status, log.archived FROM sys.v_$logfile logfile JOIN sys.v_$log log ON logfile.group#=log.group# ORDER BY log.group#, logfile.member;

    GROUP# MEMBER                                     STATUS   ARC              
---------- ------------------------------------------ -------- ---              
         1 /u02/app/oracle2/oradata/ORCL/redo01.log   INACTIVE YES              
         2 /u02/app/oracle2/oradata/ORCL/redo02.log   INACTIVE YES              
         3 /u02/app/oracle2/oradata/ORCL/redo03.log   CURRENT  NO               
         4 /u02/app/oracle2/oradata/ORCL/redo04_1.log UNUSED   YES              
         4 /u02/app/oracle2/oradata/ORCL/redo04_2.log UNUSED   YES              
         5 /u02/app/oracle2/oradata/ORCL/redo05_1.log UNUSED   YES              
         5 /u02/app/oracle2/oradata/ORCL/redo05_2.log UNUSED   YES              
         6 /u02/app/oracle2/oradata/ORCL/redo06_1.log UNUSED   YES              
         6 /u02/app/oracle2/oradata/ORCL/redo06_2.log UNUSED   YES              

9 rows selected.

SYS@ORCL>
SYS@ORCL>ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SYS@ORCL>
SYS@ORCL>SELECT log.group#, logfile.member, log.status, log.archived FROM sys.v_$logfile logfile JOIN sys.v_$log log ON logfile.group#=log.group# ORDER BY log.group#, logfile.member;

    GROUP# MEMBER                                     STATUS   ARC              
---------- ------------------------------------------ -------- ---              
         1 /u02/app/oracle2/oradata/ORCL/redo01.log   INACTIVE YES              
         2 /u02/app/oracle2/oradata/ORCL/redo02.log   INACTIVE YES              
         3 /u02/app/oracle2/oradata/ORCL/redo03.log   ACTIVE   YES              
         4 /u02/app/oracle2/oradata/ORCL/redo04_1.log CURRENT  NO               
         4 /u02/app/oracle2/oradata/ORCL/redo04_2.log CURRENT  NO               
         5 /u02/app/oracle2/oradata/ORCL/redo05_1.log UNUSED   YES              
         5 /u02/app/oracle2/oradata/ORCL/redo05_2.log UNUSED   YES              
         6 /u02/app/oracle2/oradata/ORCL/redo06_1.log UNUSED   YES              
         6 /u02/app/oracle2/oradata/ORCL/redo06_2.log UNUSED   YES

ALTER SYSTEM checkpoint;

right or wrong? now I can't really simulate because redo log group 3 is already inactive.

thank a lot!

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647475 is a reply to message #647474]

Fri, 29 January 2016 01:22

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

what should be done initally?

Michel Cadot wrote on Fri, 29 January 2016 07:57

...
You have to checkpoint if you want the active log(s) to become inactive not to archive it/them.

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647476 is a reply to message #647474]

Fri, 29 January 2016 02:06

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Many people think that a log switch forces a full checkpoint. It doesn't any more, as you have proved. Not since release 8. Some books give incorrect information about this. You should believe MC, not whatever else you may have read.

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647481 is a reply to message #647469]

Fri, 29 January 2016 06:39

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Just as an aside, I notice that your groups 1,2 and 3 are not multiplexed, while the rest are. Makes me wonder about the purpose of this exercise.
Are you trying to get rid of the non-multiplexed groups after having added 3 new multiplexed groups?
If so, that wasn't necessary, as you could have simply added a second member to each of the original groups.

If, on the other hand, you were also increasing the size of your redo groups by adding new, larger ones, then you are on the right track. Once created, the size of a redo cannot be changed, so the only way to effect a change is to add new groups with the new size, then delete the old.

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647518 is a reply to message #647481]

Sun, 31 January 2016 03:56

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear Ed, the purpose of this exercise is to test this on a test system before doing it on production. production has multiplexed redo logs. currently production is switching 9 times in an peak hour, since oracle recommendation is log switch every 20 minutes, it means 3 times in an hour. so I got to increase the redo log size by 3 times in actual production.

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647519 is a reply to message #647518]

Sun, 31 January 2016 04:08

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

9 times during peak hours is not big enough to change the size.
As a rule of thumb I use a switch at most every 5 minutes during peak hours unless this will lead to too big redo logs.
The size you want to choose is a balance between the frequency during peak hours, the frequency during low load hours and the size of the redo logs which is important for redo backup and standby database.
If your size is too big then during low charge you will switch less than one per hour which means you will loose as much work if you loose your current or not archive yet redo logs.
If the wait events related to switch logs are not a problem, you don't need to increase the size.
Forget about this Oracle recommendation which I think dates back to Oracle 7.

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647520 is a reply to message #647519]

Sun, 31 January 2016 04:13

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear Michel, are you saying I should only resize the redo log after experiencing more than 12(60/5) log switches during peak hour? I''ve only standard edition so don't have redo log advisor.

my current redo log size is 100M

many thanks in advance!

[Updated on: Sun, 31 January 2016 04:18]

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647521 is a reply to message #647520]

Sun, 31 January 2016 04:17

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What can I say more than what I already did?
If you want a figure I can't give you one, the rules I apply are those I already mentioned and it depends on the value you have for the parameters I gave:
- wait events related to switch logs
- frequency during peak hours
- frequency during quite idle hours
- size of the redo logs

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647522 is a reply to message #647518]

Sun, 31 January 2016 05:19

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

juniordbanewbie wrote on Sun, 31 January 2016 09:56

Dear Ed, the purpose of this exercise is to test this on a test system before doing it on production. production has multiplexed redo logs. currently production is switching 9 times in an peak hour, since oracle recommendation is log switch every 20 minutes, it means 3 times in an hour. so I got to increase the redo log size by 3 times in actual production.

As MC says,
Quote:

Forget about this Oracle recommendation which I think dates back to Oracle 7.

because, as I said,
Quote:

Many people think that a log switch forces a full checkpoint. It doesn't any more, as you have proved. Not since release 8

You need to think about why that recommendation was made, not just blindly follow (and worry) about it.

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647543 is a reply to message #647521]

Mon, 01 February 2016 22:25

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

I confirm that we need to do the following before we can drop the redo logs,

ALTER SYSTEM ARCHIVE LOG CURRENT;

ALTER SYSTEM CHECKPOINT;

If I'm looking at this factor
- wait events related to switch logs

when is it problembatic?

PERFSTAT@ORCL>
PERFSTAT@ORCL>--log file switch (private strand flush incomplete)
PERFSTAT@ORCL>--12345678901234567890123456789012345678901234567890
PERFSTAT@ORCL>
PERFSTAT@ORCL>SET LINESIZE 112
PERFSTAT@ORCL>
PERFSTAT@ORCL>column event format a50
PERFSTAT@ORCL>column sum_total_waited_micro format 09999999999999
PERFSTAT@ORCL>column sum_total_waits format 0999999999
PERFSTAT@ORCL>column avg_time_waited_micro format 0999999.9999
PERFSTAT@ORCL>
PERFSTAT@ORCL>
PERFSTAT@ORCL>SELECT event, sum(time_waited_micro) sum_total_waited_micro, sum(total_waits) sum_total_waits
  2  ,	round(sum(time_waited_micro)/sum(total_waits),4) avg_time_waited_micro
  3  FROM STATS$SYSTEM_EVENT WHERE event  IN
  4  ('log file sync',
  5  'log buffer space',
  6  'log file switch (archiving needed)',
  7  'log file switch (checkpoint incomplete)',
  8  'log file switch (private strand flush incomplete)',
  9  'log file switch completion',
 10  'switch logfile command'
 11  )
 12  GROUP BY event
 13  ORDER BY round(sum(time_waited_micro)/sum(total_waits),4) DESC;

EVENT                                              SUM_TOTAL_WAITED_MICRO SUM_TOTAL_WAITS AVG_TIME_WAITED_MICRO 
-------------------------------------------------- ---------------------- --------------- --------------------- 
log file switch completion                                 00004978075902      0000007547          0659609.8982 
switch logfile command                                     00001155864483      0000004338          0266451.0104 
log file switch (checkpoint incomplete)                    00000486932855      0000002485          0195948.8350 
log buffer space                                           00000210578167      0000001825          0115385.2970 
log file switch (private strand flush incomplete)          00000142563916      0000006615          0021551.6124 
log file sync                                              02079561458560      0183151033          0011354.3529

is there any magical figure that I should look for?
when should we increase redo log size or increase redo log group or both?

from https://docs.oracle.com/cd/E11882_01/server.112/e10803/config_db.htm#HABPT4823

it does not tell us whether to increase redo log size or increase number of redo log group or both to achieve a peak redo rate x 20 minutes

thanks

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647544 is a reply to message #647543]

Mon, 01 February 2016 22:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Changing the number of REDO log file groups does not change when they switch.
Changing the size of redo log file (assuming same amount of DML) changes the number of switches per fixed period of time.
Increasing the size of REDO log file, means that fewer switches will occur over a 24 hour period.
I try to maintain 3 - 6 REDO log files switches per 60 minutes at peak production activity.
BTW - you can choose to have scheduler job force REDO log file switch at 20 minute intervals during non-peak hours.

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647547 is a reply to message #647544]

Tue, 02 February 2016 00:44

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

you can choose to have scheduler job force REDO log file switch at 20 minute intervals during non-peak hours.

Or set archive_lag_target to let Oracle do it.

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647548 is a reply to message #647543]

Tue, 02 February 2016 00:48

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

is there any magical figure that I should look for?

No there is not, it depends only on your opinion like to answer the question "from how much weight a person is too fat?".
As I said, it depends on the global picture of your site.

By the way, telling you waited for X seconds without telling since when the mesure is taken is useless. It is not the same thing to have waited 10 seconds in the last minute or 10 seconds in the last day.

[Updated on: Tue, 02 February 2016 00:49]

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647550 is a reply to message #647548]

Tue, 02 February 2016 01:20

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

are you saying that I should tune my query to include when the snapshot is taken like the following?


SELECT to_char(snapshot.snap_time, 'YYYY-MM-DD HH24:MI:SS') ,event, sum(time_waited_micro), sum(total_waits),  round(sum(time_waited_micro)/sum(total_waits),4) 
FROM STATS$SYSTEM_EVENT se JOIn stats$snapshot snapshot 
ON se.SNAP_ID = snapshot.snap_id
WHERE se.event  IN
('log file sync',
'log buffer space',
'log file switch (archiving needed)',
'log file switch (checkpoint incomplete)',
'log file switch (private strand flush incomplete)',
'log file switch completion',
'switch logfile command'
) and snapshot.snap_time<SYSDATE and snapshot.SNAP_TIME>=SYSDATE-5/24/60
GROUP BY to_char(snap_time, 'YYYY-MM-DD HH24:MI:SS') ,event
ORDER BY round(sum(time_waited_micro)/sum(total_waits),4) DESC;

thanks

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647552 is a reply to message #647550]

Tue, 02 February 2016 01:24

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

For instance.

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647554 is a reply to message #647550]

Tue, 02 February 2016 01:42

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

You don't you run the spreport.sql script to get proper report? Much more useful than running that homemade query.

Report message to a moderator

Re: Why Do Redo Log Remain Active Even After alter system archive log current [message #647566 is a reply to message #647543]

Tue, 02 February 2016 06:25

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

juniordbanewbie wrote on Mon, 01 February 2016 22:25

If I'm looking at this factor
- wait events related to switch logs

when is it problembatic?

When the end users complain.

Quote:

is there any magical figure that I should look for?
when should we increase redo log size or increase redo log group or both?

thanks

You appear to have contracted a serious case of Compulsive Tuning Disorder.

Here's the steps I take to deal with "too many log switches", in the absence of any other problematic evidence:
1) Get a fresh cup of coffee
2) Go flirt with the secretary
3) Ask a co-worker what he thinks of the outcome of last week's game.
4) Check OraFAQ and OTN forums for any interesting threads.

Report message to a moderator

Previous Topic:	RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archiv
Next Topic:	RMAN-06053: unable to perform media recovery because of missing log

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 02:03:49 CST 2024