http://download.paragon-software.com/doc/Best_practices_Oracle.pdf

we are using rman with catalog for all backups

Take Weekly level 0 backup and daily incremental backups. Also take cumulative backups in between. Reduce retention period of backup files.

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
or

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Delete obsolete files

RMAN> DELETE OBSOLETE ;

we already have everythng in place, bkups r taken on a regular basis, online full, online cumulative and archivelogs too,

but during this period wht should one do:

a scenerio:

online full on every wednesday: 7 am
online cumulative everyday: 7 am except wednesday
logsbkup: everyday at 1:30 pm

but if after 1:30 archive logs got full, then only wht should be the best practice....command or script

because the next archive log cleanup is next day at 1:30 everythng is schedule and automated, this is all Unix with tivoli scheduling tool

How frequently do you do backups ? do you use oracle RMAN or simple OS scripts to do your backup ? do you have tapes attached ? what kind of tape management software you use to maintain tapes ?

And the big question to ask before you even plan a strategy - what is the recovery requirements for your Application ?

There is no one best practice. Everybody has their own best practice based on their business requirements. You can plan a strategy that will cover every situation or you can plan a simple strategy that will suffice the minimum need. But here is a raw outline of what you may want to do.

So your database is backed up 3 days a week. And you are required to keep a week worth of backups. Then you need to keep archives dated/timed same as your oldest backup on disk. That would be 8 days worth. So your disk should be planned out to keep archives for atleast 8 days.

And then how to maintain them - In UNIX, you can write a shell script and schedule it in cron that deletes archives older than 8 days. Same thing with Windows, schedule a bat scrip that keeps your archives clean.

Alternatively, if you have a huge backup mount point, you can move archive logs every 1 hr or so to this backup mount points. Clean them after 8 days.

techji, thnx for this good explanation, but u havn't read my last comments,

we are dong backups on a regular basis, and everythng is cool,

but only some time this happened that application start loading some data or somthng happend which filled up my whole archivlog destination, before the next cleanup which is next day at 1:30 pm, and due to this the db hanged.

can't even connect as sysdba...

I have scripts which cleaned up my archive logs, but they halted too, and nothing is happening.

In this situation what are best possible options and practices, keep in mind that can't even login as sysdba...!

Oh well you got everything you need from MohanKNair.

We have a script in the crontab which regularly checks all the disks and verifies how many are nearing 100% usage.  If any disk / slice crosses 90% the script sends an alert/email to the sysadmin and dba.

can u provide that script, I'll associate with my archive destination and set up at 75% full,
as soon as this happen it'll send an email and also execute the cleanup job for archive logs

Do you have any other backup mount points available ? if so then move the latest archives out to this location. The database gets back to normal. And then run a adhoc rman job to back the archives. Now it might get slightly tricky if you move archvie logs when rman doesnt find all the logs. It wont backup unless you tell it to skip inaccessible. And slowly as it starts backing you can move the archive logs back and run another adhoc rman job.

RMAN> backup archivelog all skip inaccessible delete input;

but in the middle of the night due to some load of application, archive logs got filled up, I've got a call from application that somethng is wrong at db, nothng is happening...I tried to connect as internal can't got in..

so u want me to move the archive log from there loc to some temp place, using mv command

and then db should be normal again,
and then wht ?
can it be possible u put a plan and step it out one after the another

thanks

Will get it from my computer. you will have to wait till tomorrow.

1) Create an RMAN script to backup the logs + controlfile only (kinda llike this):

# Backup Archived Logs
SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";

BACKUP
    FORMAT "${backupdir}/${sid}_${INCR_LVL}_arc_s%s_p%p_t%t"  
    ARCHIVELOG ALL
    DELETE INPUT;

RELEASE CHANNEL ch01;

# Control file backup
BACKUP
    FORMAT "${backupdir}/${sid}_${INCR_LVL}_ctl_s%s_p%p_t%t"
    CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
}
exit

2) Schedule in crontab (or task manager) the script to execute every hour:

# crontab every 15min example:
00,15,30,45 01-20 * * * /oracle/util/db_rman_backup_logs.ksh

As MikeOM_DBA  and techji suggested, run the command to backup archive logs to tape frequently, every one hour, and delete the archive logs from disk.

RMAN> backup archivelog all skip inaccessible delete input;

1. Add a new disk device to your computer for the archivelogs
2. Try to schedule a task to ftp the files in binary mode on another computer

Today the tape devices are not seen as reliable option (what will happen if the tape is out or full, or
the device only simulates recording???).

Hello anand_2000v,

Still waiting for your script, which checks the filesystem for space utilization.

can you pls assist, thanks