Solved

how to delete a applied archivelog from standby database?

Posted on 2010-09-18
17
2,912 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
  • 7
  • 7
  • 2
  • +1
17 Comments
 
LVL 16

Expert Comment

by:rolutola
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
 
LVL 16

Expert Comment

by:rolutola
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Assisted Solution

by:rolutola
rolutola earned 450 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:rolutola
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 50 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:rolutola
rolutola earned 450 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:
rolutola earned 450 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:rolutola
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

705 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now