Disable\Enable Archive Logs 2

[Michael42]

Hello,

In attempting to speed up an import I want to disable archive logs then enable them after the import.

I used this command to disable archive logs: ALTER SYSTEM ARCHIVE LOG STOP, but found that redo log files were being generated and my import stopped when the redo logs cycled from Redo4 to Redo1.

On an open database,
1. What are all the commands required disable archive log mode?

2. What are all the commands required to enable archive log mode?

Thanks,

Michael42

 

[SantaMufasa]

Michael,

Disabling ARCHIVELOG mode does not improve the speed of imports!!! (Archiving occurs asyncronously and consumes only the system resources/bandwidth of a "file copy", and only then when a log switch occurs...not much consumption of system resources.)

Since you cannot disable redo logging on an import, there really is no benefit to disabling ARCHIVELOG mode. And further, there are definite risks accompanying the disabling of ARCHIVELOG mode to NOARCHIVELOG mode:

Risks:

1) The database must not be OPEN (i.e., in MOUNT state) to place the database in NOARCHIVELOG mode and then to put the database back in ARCHIVELOG mode. Therefore your database activities (productivity) must stop twice to implement what you are trying to do.
2) All previous archived logs become unusable.
3) Media failure during your import (NOARCHIVELOG mode) means (depending upon the extent of the media failure) that you will not be able to recover your database, and, at best, only restore the database to your last full cold backup (since "hot backups" are useful only if the database is in ARCHIVELOG mode).

Even if you reinstate ARCHIVELOG mode immediately following your import, recoverability begins only at that moment of reinstatement. Therefore, to be of any value, you must also take a full database backup to provide any protection, thus negating any savings of resources from disabling ARCHIVELOG mode: Certainly, the resources you consume to do the full database backup exceed the (relatively miniscule) resources you consume during the copying of files (archiving) following a log switch.

Therefore, I highly discourage your disabling ARCHIVELOG mode for any purpose if your environment normally indicates its use.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[pete91z]

The only reason I can think of for someone wanting to disable archivelog mode is if they are importing a HUGE amount of data and do not have sufficient capacity for the amount of archived logs that will be generated. Therefore, if this is the case then as stated above a full cold backup needs to be taken before disabling archivelog mode and starting the import.

 

[Michael42]

SantaMufasa, pete91z,

Thanks for posting you have some really good points.

Perhaps then there is one area where disabling Archive Logs would be useful without risk...on a new database.

As pete91z mentioned, the one thing that can be avoided in this scenario is the generation of tons of archive logs during the import. I have had issues when doing really large imports where it would use up all the disk space alotted for Archive Logs.

Thanks again for you comments,

Michael42

 

[KenCunningham]

Michael, aside from the above points, you might also consider compressing your archive log files, after they're written, on a regular basis using whatever scheduling software is available for your system. We find that this saves a considerable amount of housekeeping and at least gives you time to react if things do start to go belly-up.

In unix-based systems, this would generally be done using compress or gzip and cron, but I guess in a Windows system you could use Winzip or similar.

 

[sem]

Are you performing direct export/import?

Regards, Dima

http://seminihin.narod.ru

 

[Turkbear]

Hi,
It is important to remember ( as SantaMufasa, aka Dave states in another thread in this forum - I cannot remember how to cite another thread, so a search will find it for you)
the archive log stop does not take the database out of archivelogmode, it just stops the ARCH process from writing new archive log files..Therefore, when all redo logs are full and need archiving to continue ( because you are still in archivelogmode) the system will halt and wait forever ...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Michael42]

sem,

>> Are you performing direct export/import?

Yes on export. I do not think you can use direct on an import. If you can please share the syntax.

Thanks,

Michael42

 

[sem]

Nothing to do with your syntax, AFAIK file exported with that option is imported the same way.

Regards, Dima

http://seminihin.narod.ru