Turn Archiving Off Safely whilst import is done..

[Sureshy]

Hi friends..

One of my colleagues needed to turn archiving off whilst he did a large import.

He set log_archive_start to false and bounced the server.

when the system restarted the database hung..

Does anybody know the corect procedure for turning archive mode off (safely) whilst a large import is taking place.
And then restarting arhive mode after the import has been done.

Also..

does anybody know why the database would have hung (what technically happened?) if the archiving was simply set to false and the database was bounced

Many Thanks

Suresh

[kkpareek]

Hi,

There is no reason for DB to hung in what your firend did. May be your online redo is large and DB was taking time in instance recovery. Or may be the shutdown was not clean & complete. You may have such problems if the shared mem & semaphores are not cleared. Try ipcs & icprm ( carefully).

The procedure of starting & stoping archiving is through thsi init.ora parameter only so what he did is right.

Kailash pareek

[marist89]

Did you ALTER DATABASE NOARCHIVELOG?

log_archive_start only signifies whether you want automatic archiving of your logs or not.

[gsprince]

As, Jeff noted it might be waiting for the manual archiving becaz the auto archival is disabled and the archivemode itself is not disabled.

Query the V$DATABSE (I guess its V$DATABASE LOG_MODE column) to see whether its really disabled.

[amar]

My 2c's:

I am not sure if I understood Suresh's posting clearly or not. However as I understood what he wants to know is "how to turn off archiving WHILE import is GOING ON".
Having said that, when Suresh said that the init.ora file was modified and the DB bounced, I assume that it was done with the abort option (otherwise it can't be done right? the import is still going on!).
So, the the DB was restarted, oracle was doing instance recovery (must have been a large table which was being imported) and so it seemed that the DB had hung.
As far as setting archiving off, Jeff has already pointed out what to do.

-amar

[Sureshy]

Hi Friends,

User did not 'alter database noarchivelog'

He changed log_archive_start to false and shut down (immediate) and then restarted the database. On restart the database hung for a little while.


I have told him that next time he needs to turn archivelog off to :

Shut down db.
startup mount exclusive
connect internal
alter database noarchivelog
alter database open

and to restart :

shut down db
startup mount exclusive
connect internal
alter database archivelog
alter database open


Does this sound right ?


Thanks for your help guys..

Suresh

[marist89]

Sounds reasonable to me.