Re: Interesting lesson on ARCHIVELOG mode

IT's a little bit trickier to set this up. The only important thing you can't do
is to startup or shutdown the DB from OEM. I did not want to do that from OEM anyway for security reasons so I did not pursue this issue.

Let's talk when I come back because, come to think of it, it's better if OEM isn't allowed to startup/shutdown the server. I'd rather DBAs go into the unix server to startup or shutdown the DB. IT's more secure that way.

You might also need the dbsmp(check spelling) to avail of more features of OEM. Somehow, I'm getting a segmentation fault error on CSIDEV2. It used to work. I'll work on it when I come back.

At 05:58 AM 4/2/03 -0800, you wrote:
>Some of you on the list might find this interesting.
>
>I just wanted to relate a story with respect to an incident experienced in
>the last few days on one of our test databases. Environment is Compaq Tru64
>Unix / Oracle 8.1.7.4.
>
>A few days ago, I remember talking to a junior DBA who assists me in the
>Oracle area, concerning excessive space usage on one of the Unix machines
>running a test database environment. I noted that the database was running
>in ARCHIVELOG mode with automatic archiving (of course), and generating a
>great many archived logs since there was considerable activity on that
>instance/database. We discussed the matter and agreed that there was no
>need to have ARCHIVELOG mode turned on in this case. So I told my assistant
>DBA to go ahead and make the database NOARCHIVELOG, which I thought she
>understood.
>
>Yesterday, she comes to me with a host of problems she has been experiencing
>on that test database, one of which was many failed attempts to import a 2
>Million row table from another database's export. It seemed that the import
>would just hang after importing about 130,000 rows. She repeatedly
>cancelled the import, resorted to cycling the database, creating a another
>table with just a subset of the columns of the original, limiting the number
>of rows imported at one time, fooling with the "buffer" parameters of the
>import control file, trying SQL*LOADER, and so on. Quite frustrated, she
>came to me for advice.
>
>I had forgotten about the ARCHIVELOG mode issue a few days earlier, so I
>began scratching my head as I looked unsuccessfully for signs of trouble in
>alert logs and traces. I thought maybe a rollback segment had run out of
>room, lost its brains, or maybe temp space had become a problem. But again,
>no sign of any of these issues in alerts or traces. Suspecting database
>corruption, I took a full export to see if export would report any corrupted
>blocks. That worked flawlessly. I began to wonder if we should just start
>from scratch and recreate the database. Then something interesting became
>apparant.
>
>Looking at V$DATABASE, I noticed that the database was still in ARCHIVELOG
>mode! When I asked about this, it seems that she thought that simply
>commenting out the init.ora parameters:
> > log_archive_start=true
> > log_archive_dest=whatever
> > log_archive_format=whatever
>and then recycling the database would take care of the whole issue of
>ARCHIVELOG mode, making the database become NOARCHIVELOG mode. Well, guess
>what.....it didn't.
>
>The lesson learned was that with the database still in ARCHIVELOG mode and
>automatic archiving turned off, obviously enough DML would cause the
>database to hang whenever it did a log switch, awaiting us DBAs to manually
>archive the filled redo logs. Realizing this, of course we then did the
>prudent thing:
> > alter database noarchivelog
>and lived happily ever after.
>
>Had I continued to assume database corruption and just had her recreate the
>database, it WOULD have indeed solved the problem BUT ONLY because the
>database would have come up in NOARCHIVELOG mode. However, it certainly
>would have bothered me as to why the database had become corrupted in the
>first place.
>
>I am very happy to know what actually happened, that the database wasn't
>corrupted at all. It was just someone's misunderstanding in not realizing
>that "ARCHIVELOG mode" and "automatic archiving" are two related but totally
>different things!
>
>Jim Damiano
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: James Damiano
> INET: jadam_at_nhes.state.nh.us
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jerome Roa
  INET: jroa_at_uchicago.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).