| Archivelogging and CPU as 100% [message #61607] |
Mon, 10 May 2004 09:12  |
Matthew Iskra
Messages: 16
Registered: April 2002
Location: Sacramento CA USA
|
Junior Member |
|
|
We are running UnixWare 7.1.3, Oracle 8.1.7.4.0. We had just completed a major database rearrangment of files and I was setting the init file to use archive logging. Some application problems came up, and due to time limitations production was brought up.
A day or two later I notice performance issues. Using rtpm I notice on cpu is at 100% utilization and the applications are slow. The programers and I go through our various tests - none of the usual suspects were involved. I then noticed that while archivelogging was set up, it was not enabled.
I was able to sneak a quick database bounce and alter the database for archive logging. Now, everything seems ok.
So if you have archive logging set up, but not turned on, you might suddenly have weird performance problems until you either enable archiving or disable the parameters in the init.
Anybody else see this?
--Matthew
|
|
|
|
| Re: Archivelogging and CPU as 100% [message #61609 is a reply to message #61607] |
Mon, 10 May 2004 12:47  |
Frank Naude
Messages: 4587
Registered: April 1998
|
Senior Member |
|
|
Hi,
This is standard Oracle behaviour. If the database is in ARCHIVELOG mode, and the log files are not archived (either manually or automatically), the database will eventually hang. You can monitor this with the "ARCHIVE LOG LIST" command.
Note that from Oracle 10g, archiving will be enabled automatically (to the flashback recovery area) when the database is switch to ARCHIVELOG mode. Example:
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 26
Current log sequence 28
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 778036 bytes
Variable Size 162537676 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 26
Next log sequence to archive 28
Current log sequence 28
Best regards.
Frank
|
|
|
|
| Not in Archivelog, but set up for it [message #61617 is a reply to message #61609] |
Tue, 11 May 2004 08:14 |
M. Iskra
Messages: 1
Registered: May 2004
|
Junior Member |
|
|
The database was enabled for archive logging in the initXXXX.ora file, and had valid areas to put the archive log files in. However, the database was altered to not be in archive logging via the command:
ALTER DATABASE NOARCHIVELOG;
I understand what happens if you have archive logging on and never save them. The database will hang when your archive log area is full. I think every Oracle DBA rediscovers that error sometime in their career.
As for my situation, I have the following facts:
Archive logging enabled and correct in initXXXX.ora
Archive logging disabled via svrmgrl statment
Log entry sample - 900:ARC0: media recovery disabled
One CPU at 100% utilization to an oracle process
Slow application performance
These went away when I did the command:
ALTER DATABASE ARCHIVELOG
I just find it weird behaviour and was curious if other people have had this behaviour before. It was rather unexpected to me; I have many other oracle databases (same OS & Oracle version) that run without archive logging that do not have this problem.
Thanks for the prompt reply, Mr. Naude.
--Matthew
|
|
|
|
| Re: Not in Archivelog, but set up for it [message #61971 is a reply to message #61617] |
Tue, 15 June 2004 01:05 |
hoke
Messages: 1
Registered: June 2004
|
Junior Member |
|
|
I have the same problem.
I tried restarting the database.
and also changed the following parameter in the init.ora.
log_archive_start = false
# log_archive_dest_1 = "LOCATION=F:oradbaadmintestarch"
# log_archive_format = "arc%S.%T"
At this moment the database is running smoothly
and is not consuming all cpu anymore.
hoke
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
