Operating System - HP-UX
1748063 Members
5593 Online
108758 Solutions
New Discussion юеВ

Re: Oracle 9i and archivelog

 
SOLVED
Go to solution

Oracle 9i and archivelog

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 -
27 REPLIES 27
Steven E. Protter
Exalted Contributor
Solution

Re: Oracle 9i and archivelog

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Jeff_Traigle
Honored Contributor

Re: Oracle 9i and archivelog

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. :)
--
Jeff Traigle

Re: Oracle 9i and archivelog


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
Honored Contributor

Re: Oracle 9i and archivelog

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
--
Jeff Traigle
Simon Wickham_6
Regular Advisor

Re: Oracle 9i and archivelog

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
Regular Advisor

Re: Oracle 9i and archivelog

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
Honored Contributor

Re: Oracle 9i and archivelog

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
Each and every day is a good day to learn.
Simon Wickham_6
Regular Advisor

Re: Oracle 9i and archivelog

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
Honored Contributor

Re: Oracle 9i and archivelog

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)