?
Solved

how to delete a applied archivelog from standby database?

Posted on 2010-09-18
17
Medium Priority
?
4,554 Views
Last Modified: 2013-12-18
Hi expert,
We are facing archive exhaust problem every alternate day on standby.
archivelog deletion policy is set on primary database as soon as the archive is applied it got deleted from the primary database however we are facing issue on standby database.
we want the same archive log deletion to be set on standby also.
Is there any way we can set this policy after setting this on primary database or can we set any automatic scripts or can do this through grid control.
Your assistance is much appreciated.

Thanks and Regards,
0
Comment
Question by:daniesh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 2
  • +1
17 Comments
 
LVL 16

Expert Comment

by:Richard Olutola
ID: 33710638
Options:

1. Allocate more space to the archivelog folder on standby node :)

2. Use flash recovery area for archivelogs on standby node so that space is automatically managed.

3. Create a script to delete older archivelogs on standby node. You could use this in conjunction with deletion poilcy on primary which only deletes after it's been APPLIED on standby.

R.
0
 

Author Comment

by:daniesh
ID: 33710812
Hi
Thanks for the update.
I have already mentioned that we have already applied standby archive log deletion policy on primary which deletes the archive log from primary after the log is applied to the standby.
the space allocated to flash recovery area is 40gb only for single standby database.
I want to apply the same archive log deletion policy standby also which we have applied to primary.

I found on article on google, they have set

run {
allocate channel for maintenance device type disk;
delete archivelog until time 'sysdate -5';
}

which will delete the archive log from standby which will 5 days old.

I think it is the good solution.

I want to know if i will run the above command will it delete the arhive log automatically or i have manually run the above command to delete the archive log.
If the above command is automatic , then we do not need human intervention to delete the archivelog from standby. the archivelog which is more than five days old will get delete automatically.

I need youe view on this.

Thanks
0
 
LVL 8

Expert Comment

by:gvsbnarayana
ID: 33710852
Hi,
    You will need to delete the archivelog from the standby database using RMAN. While doing so, do NOT connect to catalog (if you have one) and delete with the same command that you have given above. You can delete the logs that are applied. We schedule a script to delete the logs every twelve hours. We use the below one, same as what you mentioned above.
run {
allocate channel for maintenance device type disk;
delete archivelog until time 'sysdate -1/2';
}

HTH
Regards,
Badri.

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Richard Olutola
ID: 33710988
The script you found on google still needs to be scheduled either with OEM or with crontab. It will not run automatically because it is only a script.

R.
0
 

Author Comment

by:daniesh
ID: 33711222
Hi Badri,

The script you have scheduled using rman.
the script run automatically or we need to schedule the same script using grid conrtol or need to schedule it using cron.
Please let me know your view on this.

Thanks
0
 

Author Comment

by:daniesh
ID: 33711242
Hi Badri,

The script you have scheduled using rman.
the script run automatically or we need to schedule the same script using grid conrtol or need to schedule it using cron.
Please let me know your view on this.

Thanks
0
 
LVL 8

Expert Comment

by:POracle
ID: 33714847
Daniesh,

Script can't run automatically.

You can either schedule it using database scheduler or Operating Scheduler(Like cron for linux or windows scheduler ).

For Database scheduler use dbms_scheduler ( dbms_job for Oracle 9i ). You can use GUI tool like database controller or grind control also.
0
 

Author Comment

by:daniesh
ID: 33719184
Hi,

I configure a script in crontab. I am getting an error.
[oracle@shchorc07a rman_backups]$ more archivelogdeletionsscript
export ORACLE_SID=GEMSDG11
rman target /
cmdfile='archivelogdelete.rcv'

[oracle@shchorc07a rman_backups]$ more archivelogdelete.rcv
run
{
allocate channel for maintenance device type disk;
delete archivelog until time 'sysdate -6';
}
when i try to run the above scripts.

it get connect to the rman prompt however it doesnt execute the run command.

[oracle@shchorc07a rman_backups]$ sh -x archivelogdeletionsscript
+ export ORACLE_SID=GEMSDG11
+ ORACLE_SID=GEMSDG11
+ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Sep 20 14:43:17 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: GEMSPRD (DBID=3767189146, not open)

RMAN>

Can anybody send me the scripts if he already have configure it.

Thanks and Regards,
0
 
LVL 16

Assisted Solution

by:Richard Olutola
Richard Olutola earned 1800 total points
ID: 33719330
Try the statement on a single line as follows:

rman target / cmdfile=archivelogdelete.rcv

Also, ensure the rcv file is in the same location as the archivelogdeletionsscript script.

R.


0
 
LVL 16

Expert Comment

by:Richard Olutola
ID: 33719342
You could add a log file parameter to it:

rman target / cmdfile=archivelogdelete.rcv log archivelogdelete.log

R.

0
 

Author Comment

by:daniesh
ID: 33720093
Hi,
When i try using the way you asked me to. i am getting an error.

[oracle@shchorc07a rman_backups]$ sh -x archivelogdeletionsscript
+ export ORACLE_SID=GEMSDG11
+ ORACLE_SID=GEMSDG11
+ rman target / cmdfile=archivelogdelete.rcv

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Sep 20 16:21:24 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: GEMSPRD (DBID=3767189146, not open)

RMAN> run
2> {
3> allocate channel for
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "for": expecting one of: "channel_id, double-quoted-string, equal, identifier, single-quoted-string"
RMAN-01007: at line 3 column 18 file: archivelogdelete.rcv

Thanks
0
 
LVL 8

Assisted Solution

by:POracle
POracle earned 200 total points
ID: 33722565
Error is in your RMAN script:

Rewrite this statement and specify channel name.
allocate channel for maintenance device type disk;

Like this:
allocate channel C1 for maintenance device type disk;
0
 
LVL 16

Assisted Solution

by:Richard Olutola
Richard Olutola earned 1800 total points
ID: 33722873
No you do not need a channel id for this command.

The problem is this command cannot be used in a run block: it can only be used at the command prompt.

Let's have a think about another method to achieve your aim.

R.
0
 
LVL 16

Accepted Solution

by:
Richard Olutola earned 1800 total points
ID: 33723407
Try the following:

(forget the archivelogdelete.rcv file)

edit the archivelogdeletionsscript shell script as follows:

# start
export ORACLE_SID=GEMSDG11
rman target / << EOF

#allocate channel
allocate channel for maintenance device type disk;

#delete old archivelogs
delete archivelog until time 'sysdate -6';

EOF

# end of script

HTH.
0
 

Author Comment

by:daniesh
ID: 33727572
Hi All,

Thanks for all your help.
I am able to run the script.
Its running fine now.
I appreciate your help.

Thanks
0
 

Author Comment

by:daniesh
ID: 33839193
Hi rolutola,
User want the scripts to first check the primary and standby database are in sync.
If in sync then delete the that archived log which has been applied.
Can you please let if i can keep the condition in our archive log which we have configure.
If you have any scripts which will check the archived log applied on standby and that archivelog must be deleted using scripts.

Thanks
0
 
LVL 16

Expert Comment

by:Richard Olutola
ID: 33858914
I am afraid I've never done that. I guess that would be somewhat invloving to guery the views and compare the values. Heavy'ish script.

I'm in the middle of my first 11g RAC implementation so unfortunately I can't look into it right now.

R.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question