Oracle 9i and archivelog

[Viger Østensen]

Hi,

I've turned on ARCHIVELOG on my Oracle 9i database in order to perform hot (online) backups of the DB with RMAN and now my disk are filling up rapidly with archive logs - why?

What's actually stored in the archive logs and why are there so many of them?

Regards,
- Viger -

[Steven E. Protter · Accepted solution]

Shalom,

Archive logs are transaction records of writes to the database. Log switching depends on number of logs and init.ora configuraiton.

More transactions, more logs.

The logs are needed to roll forward after recovering the database.

SEP

[Jeff_Traigle]

What's stored in them is every transaction the database processes. This is how it recovers the data during a restore... you restore the data files from your hot backup and then the archive redo logs are applied to regain data consistency. The more transactions you have, the more space you need to store the logs. I forget offhand when new ones are created, but I know there's a parameter that controls it. (Been a few years since my training and I actually had DBA responsibilities.) That would only control the number of files though, not the overall space taken. Nothing you can do about the space other than process fewer transactions. :)

[Viger Østensen]

Do I have to manage these files myself or does Oracle/RMAN take care of it?

It should only be required to the keep the archive logs since the last backup - or?


- Viger -

[Jeff_Traigle]

From this page describing an example of using RMAN, it appears you can configure RMAN to manage the archive redo logs.

http://www.idevelopment.info/data/DBA_tips/RMAN_9i/RMAN9_50.shtml

[Simon Wickham_6]

Hi,

What is you db block size? How many blocks/min does your average user change new, updates, deletes etc.

Also if your tablespace is in hot backup mode then this will generate lots of redos.
Or if you are using dictionary managed tablespaces with small extent size then this will require extent allocation very frequently and this will generate redos.

Regards,
Simon

[Simon Wickham_6]

Hi,

It may also be a good idea to run aquery against v$sysstats to see the list waits for the redo log buffer and a query against v$system_event to review incomplete log switches, etc.

col name form a30

select name, value
from v$sysstat
where name in ('redo log space requests',
'redo buffer allocation retries');

col event form a54

select *
from v$system_event
where event like '%log%'
order by event;

Hope this helps

Regards,
Simon

[Eric Antunes]

Hi Viger,

"It should only be required to the keep the archive logs since the last backup - or?"

Not since the last backup but since the last COLD backup.

As this strategy is choose (hot backups) is much more complex, I strongly encourage you to test a restore on another environement/server.

Best Regards,

Eric Antunes

[Simon Wickham_6]

Hi Viger,

As Eric said you need to check this so remove some of the old archive logs after you have performed a backup of the archivelogs.

Step 1. Backup archive logs to disk.
RMAN> run {
2> allocate channel dev1 type disk;
3> backup
4> format '/app/oracle/archback/log_%t_%sp%p'
5> (archivelog all delete input);
6> release channel dev1;
7> }

Step 2. To list all archivelog backups for the past 24 hours.
LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1';

Step 3. Restore back.
RMAN> run {
2> allocate channel dev1 type disk;
3> restore (archivelog low logseq 78311 high logseq 78340 thread 1 all);
4> release channel dev1;
5> }

Regards,
Simon

[Yogeeraj_1]

hi viger!

You have to manage you archived log by adding the appropriate command in your rman scripts!


As Eric has said above, by doing the RMAN backup you are only doing half the job! You must also test your restore procedure then you can be sure that what you are backing up is what you will require during a restore.

kind regards
yogeeraj

[Yogeeraj_1]

hi again,

below an example on how you do it in your rman script. If you need further assistance, do let us know.

run {
allocate channel fs1 type disk format='/backup/rman/al/al1_t%t_s%s_p%p';
set limit channel fs1 kbytes=100000;
backup filesperset 10 archivelog all delete input;
release channel fs1;
}

Note that i am backup up the archived log to disk in the above example.

kind regards
yogeeraj

[Viger Østensen]

Wow, you guys are going way too fast for me!

Let's rewind back a few steps here. I wanna do HOT backups, but what you tell me here, is that I need to do COLD backups on a regular basis and then use rman to backup my archive logs to restore the changes since the last cold backup?

Is this the best practice for doing hot backups of Oracle, or are there better ways?

Regards,
- Viger -

[Sanjay Kumar Suri]

In 24x7 environment, daily online (HOT) backups along with at least one backup of ARCHIVE log is recommended.

In our setup we take two copies of archive logs onto tape. Once backup are through archive files are deleted from disks.

As previous posts suggest one also need to test the backup data and the process of restore & recovery.

sks

[Eric Antunes]

Hi Viger,

RMAN is indeed the best practice for Hot backups.

But you should consider the following before making a decision about Hot or Cold backup:

- Necessary DBA skills (Recovering from hot backups is much more complex than with cold ones);

- Restore time (depends of the number of archive log files that needs to be applied);

- Maximum data you can loose (hours of users work);

- Downtime you can have;

- Database volatility (changing speed);

- Disk space available for archive logs and budget available to buy more if they are needed.


Best Regards,

Eric Antunes

[Gavin Clarke]

Hi Viger,

Cold backups are much easier to get your head around. Effectively it is just
shut the database down,
copy the files.

Hot backups are much more complex, we spent some time and effort in getting them to restore.

Eric, Your advice is as always marvellous however I have to mention that your statement,
"Not since the last backup but since the last COLD backup", seems to me to be misleading.

We don't do cold backups here any more, yet we were able to restore. I'm talking with our dba now about this.

My feeling is that the archive logs just before, during and after the hot backup are useful, the earlier archive logs can be discarded (although backing them up beforehand is always a good idea). We keep a few days worth of archive logs just in case one hot backup doesn't work, this then gives us the option to go back to the previous one. I'm assuming that you do daily hot backups.

What we did for a while was a cold backup at the weekend and hot backups during the week. When we did this we kept the archive logs back to the cold backup.

[Simon Wickham_6]

Hi,

A 24x7 environment is fine running with hot backups and is a necessary as most companies cannot afford any downtime. I have various clients who I have integrated RMAN with third-party Media Managers so there is no point in using cold backups if this is the option you desire and meets your buisness needs.

Have you set RMAN up with repository database? The RMAN repository database contains the schema that owns the tables, indexes, and PL/SQL packages that comprise the RMAN repository, or catalog.

Regards,
Simon

[Yogeeraj_1]

Hi viger!

Forget about COLD backup. Just do your RMAN hot backup. if possible, backup to disk first (then backup to tape). Both Backup and Recovery will be much faster!

How big is your database?

Above all YOU must test your recovery.

also read:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96572/toc.htm


kind regards
yogeeraj

[Viger Østensen]

Interresting reading. To me it seems possible to make a valid backup of the whole database by issuing:

RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE ALL INPUT

This will both backup my database AND clean out all archive logs.

The doc says:
1. Runs the ALTER SYSTEM ARCHIVE LOG CURRENT command.
2. Runs BACKUP ARCHIVELOG ALL.
3. Backs up files specified in BACKUP command.
4. Runs the ALTER SYSTEM ARCHIVE LOG CURRENT command.
5. Backs up any remaining archived logs generated during the backup.

Not quite sure I can add "DELETE ALL INPUT" to the command though, but "BACKUP ARCHIVELOG ALL DELETE ALL INPUT" is valid, so I was hoping they could be combined...

Regards,
- Viger -

[Yogeeraj_1]

hi again,

of course you can proceed this way.

In fact, in the same script you can include multiple commands.

#Backup the whole database
backup
tag Whole_database_hot
database;

#Switch out of the current logfile
sql 'alter system archive log current';

#Backup the archived logs
backup
archivelog all delete input
format '/backup/rman/full/fs1/al_%u.%p';


hope this helps too!

good luck and don't forget Recovery tests are most important!

kind regards
yogeeraj

[Darren Etheridge_2]

We backup our archivelogs 2 times a day and perform a cold backup in the evenings... In addition to all of this we do a BUSINESS COPY of our Oracle DB's at NOON... just to make sure... The business copy backup is great!! it takes only minutes to do and is VERY easy to restore from!! I would definetly recommend it!!

Darren

[Eric Antunes]

Viger,

Don't forget that a backup is only valid when you tested the restore. And id your backups are to a tape, there is always a chance (1%?) that your tape will be bad. So, be sure you have a plan B... :)

My plan A: cp via NFS to a backup server.

My plan B: tar to a tape 2 times a week.

Best Regards,

Eric Antunes

[Viger Østensen]

Hi all,

Thanks for your suggestions.

If I can afford to loose some data, could I then just do an

RMAN> BACKUP DATABASE

on a regular basis and forget about the archive logs?

Regards,
- Viger -

[Eric Antunes]

Hi Viger,

It depends how much data you can afford to loose because if you don't have the archivelogs when a recovery is needed you are going to lose many data...

I think you should follow Yogeeraj suggestion script. You can also use Oracle Backup Manager (from Enterprise Manager).

Best Regards,

Eric Antunes

[Viger Østensen]

But, I will only loose the changes that have been done to the database while the "BACKUP DATABASE" command was running?

Regards,
- Viger -

[Yogeeraj_1]

Hi viger!

Yes, you can do this but why would you want to do it?

If you do this, you will be able to recover only to the time when you performed the "backup database".

Noote that Backup of archived redolog is not that complicated nor time consuming if you do it more regularly (based on the number of logs generated).

kind regards
yogeeraj