Home » RDBMS Server » Server Administration » Problems with Archivelogmode
Problems with Archivelogmode [message #216877] Tue, 30 January 2007 14:31 Go to next message
Rustican
Messages: 51
Registered: July 2006
Member
i'm running into a problem with Archivelogmode on my Oracle 10g Enterprise server. Archivelogmode was enabled but the disk ran out of space and the database hung.

I managed to recover the database and get it back up with Archivelogmode turned off. But when i try to turn Archivelogmode on again the database goes down.

Has anyone run into this issue before?

Report message to a moderator

Re: Problems with Archivelogmode [message #216878 is a reply to message #216877] Tue, 30 January 2007 14:33 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>again the database goes down
Being specific might be helpful.
Post the relevant part of alersid.log and tracefiles if any.

Report message to a moderator

Re: Problems with Archivelogmode [message #216882 is a reply to message #216878] Tue, 30 January 2007 14:56 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
Where is this file usually found? I'm using 10g Enterprise. I did a search for alertsid.log and it cam up with nothing.

Report message to a moderator

Re: Problems with Archivelogmode [message #216883 is a reply to message #216882] Tue, 30 January 2007 15:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Actually, the file is alert_<$ORACLE_SID>.log found in (had you created the database with oracle standards) $ORACLE_BASE/admin/<$ORACLE_SID>/bdump

[Updated on: Tue, 30 January 2007 17:09]

Report message to a moderator

Re: Problems with Archivelogmode [message #216887 is a reply to message #216883] Tue, 30 January 2007 15:20 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
Here's what I found below:


Tue Jan 30 14:24:18 2007
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL =TCP))'...
MMNL started with pid=12, OS id=4103
Tue Jan 30 14:24:18 2007
starting up 1 shared server(s) ...
Tue Jan 30 14:24:18 2007
ALTER DATABASE MOUNT
Tue Jan 30 14:24:22 2007
Setting recovery target incarnation to 2
Tue Jan 30 14:24:22 2007
Successful mount of redo thread 1, with mount id 544347730
Tue Jan 30 14:24:22 2007
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Tue Jan 30 14:24:22 2007
ALTER DATABASE OPEN
Tue Jan 30 14:24:23 2007
Thread 1 opened at log sequence 70
Current log# 3 seq# 70 mem# 0: /data3/oracle/oracle/product/10.2.0/oradata/orc l3/redo03.log
Successful open of redo thread 1
Tue Jan 30 14:24:23 2007
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Jan 30 14:24:23 2007
SMON: enabling cache recovery
Tue Jan 30 14:24:24 2007
Successfully onlined Undo Tablespace 1.
Tue Jan 30 14:24:24 2007
SMON: enabling tx recovery
Tue Jan 30 14:24:24 2007
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication fou nd)
Tue Jan 30 14:24:26 2007
Streams CAPTURE C001 started with pid=16, OS id=4114
Tue Jan 30 14:24:27 2007
Starting background process QMNC
QMNC started with pid=18, OS id=4119
Tue Jan 30 14:24:30 2007
Errors in file /data3/oracle/oracle/product/10.2.0/db_3/admin/orcl3/bdump/orcl3_ mmon_4101.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 99.40% use d, and has 12804096 remaining bytes available.
Tue Jan 30 14:24:30 2007
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Tue Jan 30 14:24:30 2007
Completed: ALTER DATABASE OPEN
Tue Jan 30 14:24:32 2007
Propagation Schedule for (STRMADMIN.STREAMS_QUEUE, ORCL3MIR.REGRESS.RDBMS.DEV.US .ORACLE.COM) encountered following error:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Tue Jan 30 14:24:34 2007
LOGMINER: Parameters summary for session# = 61
LOGMINER: Number of processes = 3, Transaction Chunk Size = 1
LOGMINER: Memory Size = 10M, Checkpoint interval = 10M
LOGMINER: session# = 61, preparer process P002 started with pid=24 OS id=4213
LOGMINER: session# = 61, reader process P000 started with pid=22 OS id=4209
LOGMINER: session# = 61, builder process P001 started with pid=23 OS id=4211
Tue Jan 30 14:24:35 2007
LOGMINER: Begin mining logfile: /data3/oracle/oracle/product/10.2.0/oradata/orcl 3/redo02.log
Tue Jan 30 14:24:37 2007
LOGMINER: End mining logfile: /data3/oracle/oracle/product/10.2.0/oradata/orcl3/ redo02.log
Tue Jan 30 14:24:37 2007
LOGMINER: Begin mining logfile: /data3/oracle/oracle/product/10.2.0/oradata/orcl 3/redo03.log
Tue Jan 30 15:04:36 2007
ALTER SYSTEM SET log_archive_dest='/database/ora_archive' SCOPE=SPFILE;
Tue Jan 30 15:04:36 2007
ALTER SYSTEM SET log_archive_format='%S.arc' SCOPE=SPFILE;
Tue Jan 30 15:04:36 2007
ALTER SYSTEM SET log_archive_start=TRUE SCOPE=SPFILE;
Tue Jan 30 15:09:06 2007
Starting background process EMN0
EMN0 started with pid=34, OS id=9046
Tue Jan 30 15:09:06 2007
Shutting down instance: further logons disabled
Tue Jan 30 15:09:07 2007
Stopping background process CJQ0
Tue Jan 30 15:09:07 2007
Stopping background process QMNC
Tue Jan 30 15:09:08 2007
Stopping background process MMNL
Tue Jan 30 15:09:09 2007
Streams CAPTURE C001 with pid=16, OS id=4114 stopped
Tue Jan 30 15:09:09 2007
Stopping background process MMON
Tue Jan 30 15:09:11 2007
Shutting down instance (immediate)
License high water mark = 20
Tue Jan 30 15:09:11 2007
Stopping Job queue slave processes
Tue Jan 30 15:09:11 2007
Job queue slave processes stopped
Tue Jan 30 15:09:22 2007
All dispatchers and shared servers shutdown
Tue Jan 30 15:09:25 2007
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
Tue Jan 30 15:09:37 2007
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
Tue Jan 30 15:09:46 2007
ALTER DATABASE CLOSE NORMAL
Tue Jan 30 15:09:48 2007
SMON: disabling tx recovery
SMON: disabling cache recovery
Tue Jan 30 15:09:48 2007
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 70
Successful close of redo thread 1
Tue Jan 30 15:09:49 2007
Completed: ALTER DATABASE CLOSE NORMAL
Tue Jan 30 15:09:49 2007
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Tue Jan 30 15:09:51 2007
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0

[Updated on: Tue, 30 January 2007 16:58] by Moderator

Report message to a moderator

Re: Problems with Archivelogmode [message #216903 is a reply to message #216887] Tue, 30 January 2007 17:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I have just edited your previous post and highlighted the possible areas of trouble.
If your archive destination is full, just do the recommended steps(highlighted) and start over.
In a remote case, this ***could be *** a bug (bug# 3639594 ) where the PMON gets a little confused about the status of ALTER SYSTEM command (Reference Metalink Bug note).

[Updated on: Tue, 30 January 2007 17:10]

Report message to a moderator

Re: Problems with Archivelogmode [message #216924 is a reply to message #216887] Tue, 30 January 2007 22:11 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
Hi,
addition of mahesh suggestion.
you have to increase "db_recovery_file_dest_size"
or
connect to rman
and give below command.
rman>report obsolete;
rman>delete obsolete;
rman>crosscheck backup;
rman>delete expired backup;


regards
Taj

Report message to a moderator

Re: Problems with Archivelogmode [message #217330 is a reply to message #216877] Thu, 01 February 2007 14:05 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
Need some help trying to startup mount the databse but i'm getting the following error.

ORA-32004: obsolete and/or deprecated parameter(s) specified
ORA-19905: log_archive_format must contain %s, %t and %r


I believe i'm getting it because of this parameter that listed in the above log dump:
ALTER SYSTEM SET log_archive_format='%S.arc' SCOPE=SPFILE;


Is there any way to change the server parameter file so that the log_archive_format can be set to: LOG_ARCHIVE_FORMAT = 'log%t_%s_%r.arc'


Thanks

Report message to a moderator

Re: Problems with Archivelogmode [message #217360 is a reply to message #217330] Thu, 01 February 2007 17:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
self explanative error.
Just issue 
ALTER SYSTEM SET log_archive_format='log%t_%s_%r.arc'  SCOPE=SPFILE;

Report message to a moderator

Re: Problems with Archivelogmode [message #217476 is a reply to message #217360] Fri, 02 February 2007 07:18 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
Unfortunately it looks like my SPFILE and PFILE are corrupt. Is there any way to recover from this?

Report message to a moderator

Re: Problems with Archivelogmode [message #217584 is a reply to message #217476] Fri, 02 February 2007 22:06 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
Hi,
check your alert<sid>.log file. there you will get all pfile parameter ( needed parameter for database startup.)
and create pfile from those parameters and startup your database after create spfile from pfile.

or one more file you have in $oraclehome/admin/pfile/init.ora
you can also startup your database from init.ora file


regards
Taj

Report message to a moderator

Previous Topic: ORA-1031 while creating a new db manually
Next Topic: import error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 02:38:14 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.