Shutdown immediate taking a long time

[williey]

Trying to shutdown immediate and it is taking a long time.. prior to the shutdown the command "archive long stop" was issued to allow import to take place without generating logs.

Does manual switching of the log required? How do you do that?

found process 452412B4 pid=13 serial=4 dead
*** 2005-12-09 11:04:18.468
deleting process 452412b4 pid=13 seq=4
need redo log switch, current log full
deletion of process 452412b4 pid=13 seq=4 unsuccessful
*** 2005-12-09 11:04:28.531
found process 452412B4 pid=13 serial=4 dead
*** 2005-12-09 11:04:38.562
found process 452412B4 pid=13 serial=4 dead
*** 2005-12-09 11:04:48.593
found process 452412B4 pid=13 serial=4 dead
*** 2005-12-09 11:04:58.624
found process 452412B4 pid=13 serial=4 dead
*** 2005-12-09 11:05:08.655
found process 452412B4 pid=13 serial=4 dead
*** 2005-12-09 11:05:18.686
found process 452412B4 pid=13 serial=4 dead

 

[SantaMufasa]

Willie,

First,

Scenario 'A': if your database is not in ARCHIVELOG mode, then "archive log stop" has virtually no effect.

Scenario 'B': If your database IS in ARCHIVELOG mode, then "archive log stop" can actually cause your database to hang after a short (to a long) period of time.

"Archive log stop" does not turn of logging...redo logging (i.e., writing all database changes to your on-line redo log files) still occurs as normal. Under Scenario 'A' (NOARVHICE log) (above), there is then virtually no effect upon processing.

But, under Scenario 'B' (ARCHIVELOG mode), when an on-line redo log file becomes full, normally (when your instance parameter LOG_ARCHIVE_START=true) Oracle's ARCH process copies the just-filled on-line redo log file to the next archive log file using the next-available archive-log-file sequence number. Under your scenario, however, (i.e., "archive log stop"), that suspends the ARCH process from automatically copying the just-filled on-line redo log file to the next-available archive-log-file. Oracle, however, MUST NOT ALLOW on-line redo log file data to disappear since the database is in ARCHIVELOG mode. So, when your other on-line redo log groups become full and need to "rollover" to the group that is STILL WAITING FOR ARCHIVING, but hasn't been archived since you have turned off automatic archiving, then your database will HANG!!!.

If your database hangs under these circumstances, then the least-impact action to take is to reverse your previous instructions, using the "archive log start" command.

Now, to address your intention...turning off redo logging for work that doesn't need logging: Turning off logging usually occurs on a command-by-command basis. For example, if I wanted to reorganise a table (i.e., remove all the "Swiss cheese holes" resulting from DELETE or field shortening via UPDATE activities), I would issue the command:

ALTER TABLE my_table MOVE NOLOGGING;

or to rebuild a "Swiss cheesey" index:

ALTER INDEX my_index REBUILD NOLOGGING;

In each of these two examples, redo logging continues for all other database changes that do not have "NOLOGGING" specifications.

Unfortunately, as far as I know, there is no command to affect NOLOGGING for Oracle imports.

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.

 

[williey]

Thanks! I was able to get it going after turning the "archive log start".

As for the imports, I'll have to use "ALTER DATABASE NOARCHIVELOG;" command upon restarting the database.