We help IT Professionals succeed at work.

archiving

sqlnewbie08
sqlnewbie08 used Ask the Experts™
on
we are running oracle 10g; not rman

first question...  if running hot backups and having archiving turned on, when can the archive logs generated be removed?  

second question:  we have a test environment running with NO backups; archiving is turned on.  why is archiving needed if no backups are being run ???

thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David VanZandtOracle Database Administrator III

Commented:
So if you are not using RMAN, and doing tablespace backups in hot mode, you will need a full cold backup plus all subsequent archive logs in order to roll forward to a point in time.

Your situation may be different, but in general, the purpose of a test bed is to test how changes may effect your production instance.  As in point one, archive logs are your only means to accomplish a point in time / SCN rollback.  Rollbacks are your only alternative to manually dropping and re-creating your test bed back to a pre-change state.

Author

Commented:
thank you for the info...

but my question is...  when can the archive logs be deleted?  i'm assuming it's when you 'see' in the alert log that they have been 'applied'... please confirm.

also... if you are NOT running any backups at all and have absolultely no plan to, then why even use archive logs ??

Commented:
"So if you are not using RMAN, and doing tablespace backups in hot mode, you will need a full cold backup plus all subsequent archive logs in order to roll forward to a point in time."

The answer by Mr dvz is actually wrong!! If you are running hot backups and your database is in archived log modes and are only doing hot backups (alter tablespace being backup) then all you need is

1.  The copy of the datafiles (*.dbf) from the hot backup.
2.  All the archived logs (few before the hot backup began) since the hot backup to roll forward in time

Then you can restore your database to when you took the hot backup.  RMAN is not required.

I have automated to do this every weekend to refresh my TEST/DEV database from production database.

Just delete all the archived logs + 3 before the hot backup was taken.  i.e if you archive log every 20 minutes and are archived log #12345 now and take a hot backup then I will delete  all the archived logs before 12340 (12339, 12338, 12337 and so forth). and I would keep all the archived logs since 12340 (12341, 12342, 12343, 12344, 12345, 12346, 12347, 12347..)

If you are not taking backups for your TEST databases but it will be still a good practice to keep it in archived log mode since it will be similar as your production database (to simulate and to fix any other issues)

Commented:
Regarding deleting the archived lots in TEST.  If you are not using or plan to use log mining, flashback database, flashback table, flashback query, etc. then  You can delete these archived logs any time you want (it is a TEST system without backup).
David VanZandtOracle Database Administrator III

Commented:
In your situation, my counsel is to retain all archive logs created since your last cold (and recoverable) backup.  Assuming of course you want to be able to restore a failed production instance.......

Granted, you don't have to run your test bed in archivelog mode if you don't want to.  To what extent are your customers willing to accept a test run in an environment that's configured differently from production?

Author

Commented:
i appreciate all the information, but that's not what i'm asking...

scenario...  
database is in archivelog mode.  large job is running creating tons of archive logs.
how do i know which ones i can delete to clear up the space.

i just need to know how to know what logs can be removed.
Oracle dba
Top Expert 2009
Commented:
you only need archivelogs when you want to restore to a certain point in time.
the point in time must be after a backup you have

i don't know why dvz keeps insisting on a cold backup, but this isn't necessary
a hot backup level 0 (+ archivelogs) is just as good


for large jobs you can't explicitly find what archivelogs were for this job as archivelogs contain *all* changes for that period

there is only a must to have a backup before the large job starts (cold is best, if possible)
so you can restore to the point in time before the large job started when it fails
when the job is successful you take a new backup

since you are probably running out of diskspace
i guess you could delete all the archivelogs while the large job is running
otherwise the database will hang up and wait until there is diskspace again

be sure the large job is programmed correctly
having an infinite loop with continuously modifying and resetting data can also produce large amounts of redo

Author

Commented:
agreed with your response...

one last scenario... what if I do have hot backups running in production and a large job causes many archivelogs.   when do I know which ones can be deleted/purged.

I'm assuming there must be a way to tell because if I need to do a restore, I don't want to remove logs that will be needed for the recovery.
Geert GOracle dba
Top Expert 2009

Commented:
using a timeline

> last archivelog nr 100 : time 10:00
> start backup (adds archivelog 101) time 11:00
> long job started at 12:00
  >> archivelog nr current: 200
> end backup at 13:00
  >> archivelog nr current: 300
> end long job at 14:00
  >> archivelog nr current: 400

to be able to restore before the job starts:
you need the previous backup and all archivelogs to 199

to be able to restore past the job:
you need this backup (11:00) and all archivelogs to 400
(or take a new backup after the job has finished and restore that backup)

for a online consistent backup, you need the archivelogs from the start of the backup till just past the end backup
Geert GOracle dba
Top Expert 2009

Commented:
if you can't keep all the archivelogs during the long running job (or high change rate)
then you can't restore to that point

decision on when to keep an archivelog is based on what point in time you need to restore to

if you don't need to restore in the middle of the long job, then you don't need to keep any archivelogs during the long running job
(but take a backup as soon as it's finished)

Author

Commented:
my confusion is this...

we had a long running job that was creating tons of archived logs...  we are NOT running backups at all in this 'test' environment.

the senior dba asked me if I knew when I can delete the archived logs at 'will'...

my confusion was that they can't be used any way because we don't run backups.

so, if in a proper environment with backups running, is there a view that can be checked or the alert log to 'see' ... or after a 'log switch' ??  to know which ones can be removed??
Geert GOracle dba
Top Expert 2009

Commented:
the senior dba is asking you ????

if you aren't backing up this db, then you don't need the archivelogs

the only case when you might use the archivelogs is for mining (searching for point in time of delete statements, etc) as stated by Bajwa

in general you want to have a test database with the same setup as your production database
> if the test database produces massive amount of archivelogs, then you want to see this before making the changes on the production database (hopefully the test database and production database are not on the same machine)
  >> in this case, you have to check if the massive amount is actually expected or not
> sometimes you need to know if producing the massive amount of archivelogs can have impact on performance
> or you want to know the amount of space required for the change, so you can size the production environment
Geert GOracle dba
Top Expert 2009

Commented:
just a sample of massive archivelogs
say someone needs to refresh a table periodically (and has never heard of a view, merge, ...)


-- initial setup: 
create table CLIENT_STATUS AS 
select c.client_no, c.name, c.status, 
  count(i.invoiceid) number_of_invoices, 
  sum(i.amount) total_on_invoices
from CLIENT c, INVOICE i
WHERE c.client_no = i.client_no
GROUP BY c.client_no;

Open in new window


periodic refresh code (say every 2 minutes)
DELETE FROM CLIENT_STATUS;

INSERT INTO CLIENT_STATUS 
  (client_no, name, status, number_of_invoices, total_on_invoices)
select c.client_no, c.name, c.status, 
  count(i.invoiceid) number_of_invoices, 
  sum(i.amount) total_on_invoices
from CLIENT c, INVOICE i
WHERE c.client_no = i.client_no
GROUP BY c.client_no;

Open in new window

Geert GOracle dba
Top Expert 2009

Commented:
in the proper environment:

you have a retention period (how long do you keep backups)
based on this, you can run rman command REPORT OBSOLETE;
or REPORT NEED BACKUP;


check here :
http://docs.oracle.com/cd/B19306_01/backup.102/b14194/rcmsynta049.htm#i1017379
Geert GOracle dba
Top Expert 2009

Commented:
if the senior dba was asking this to test you, then ok
otherwise i would ask what the "senior" stands for

if the TEST database is only for a test, then you don't need backups

if this is a database on which developers are busy,
then some day a developer will ask:
"I accidently dropped a complete schema/table from the database, can you recreate it ?"
> i wouldn't want to be the one responding:
"ugh no, we don't make backups of a 'TEST' database"